エクセルのデータの入力規則での設定

質問

エクセル2010です。
アンケート用のファイルを作成し、回答欄に、データの入力規則で、「リスト」を指定し、「元の値」を

1-はい,2-いいえ,3-未定

としました。
通常は、この三択以外は入力できないのですが、オートフィルでドラッグされると

1-はい
2-はい
3-はい
4-はい

となってしまいます。
もちろんフィルドラッグした人が、オートフィルのオプションで「セルのコピー」を選んでくれたらそうなりませんが、対象者が多いので期待できません。
オートフィルをできない設定にしようかとも思いましたが、設問数が多く、ユーザーの利便性を損ないます。
「はい」、「いいえ」、「未定」と、数字をいれなければいいじゃん！とは当然思いますが、上からの指示でどうしようもありません。
こうならないようにするにはどうすればよいのでしょうか？

回答No.4

kagakusuki · ベストアンサー

1、2、3等の数字の代わりに丸数字
（Excelにおいてセルに「=CHAR(11553)」、「=CHAR(11554)」、「=CHAR(11555)」と入力した際に現れる文字）
を使用されては如何でしょうか？

【参考URL】
　丸数字 - Wikipedia
　　http://ja.wikipedia.org/wiki/%E4%B8%B8%E6%95%B0%E5%AD%97

　丸数字は、このサイトで表示可能な文字ではないため、サイトのサーバーが勝手に( )付きの数字に変換してしまうのですが、
(1)-はい
(2)-いいえ
(3)-未定
という様な形式です。

お礼

丸付きの数字ならオートフィルしても数字が変わらないのですね！
これは使えそうです。
ありがとうございました！！

回答No.10

MarcoRossiItaly

＞……一つのセルで数字の入った選択肢から相手に選択させることが必要なのです。

とりあえずリストの側でどんな工夫をしても、回答用紙におけるオートフィルでの数字の増加を抑える術はありません。なのでリストの内容については、今回のご質問では全く考慮しなくてよいかと思います。

＞何故ならば、例では、1-はい,2-いいえ,3-未定　としましたが、実際はもっと別な言葉で、回答上疑問が出る可能性があり、問い合わせがあった場合、「それは一番です」とか「２番です」と答えるマニュアルも出来ているからです。

入力時にオートフィルをしても内容が変化せず、なおかつ数字と結合された文字列が必要ならば、やはり No.9 のように「はい」を入力させて「1-はい」を生成、がベストに思えます。

または No.8さんのように条件付き書式で表示してもいいですが、その場合、セルに入力されている値には数字が含まれず、表示で見せているだけの状態です。ですからテキスト形式で保存（拡張子「.txt」）とか PDF などに印刷でもしない限り、数字の部分はテキストとして残りません。

あるいは No.7 のようにコメントを添えるときに、オートフィル禁止ではなく「オートフィルを使う場合は Ctrl キーを押しながら行ってください」と伝えたり、No.7 の数式あるいは置換の機能を使って質問者さんが後から修正することになってしまいそうです。

申し訳ありませんが、お示しの条件を全てクリアできる方法は、これくらいしか思い付きません。

まあ各ユーザーがマクロを有効にするなら、ドラッグしても次のコードで自動的に修正しますが、そこまでする？というところですね。コードは、ワークシートのタブを右クリックして「コードの表示」で表示された画面に貼ってください。

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> 3 Then Exit Sub　'回答欄の列番号が 3（C 列）の場合のみ処理を続行

Dim cell As Range

Application.EnableEvents = False

For Each cell In Target
　If cell.Value Like "*-はい" Then
　　cell.Value = 1 & "-はい"
　ElseIf cell.Value Like "*-いいえ" Then
　　cell.Value = 2 & "-いいえ"
　ElseIf cell.Value Like "*-未定" Then
　　cell.Value = 3 & "-未定"
　End If
Next

Application.EnableEvents = True

End Sub

お礼

何度もありがとうございます。
今回は、(1)数字（たぶん化けるでしょうね。丸で囲んだ数字です。）で対応することで上司からOKがでました。

> 各ユーザーがマクロを有効にするなら

VBAも最初に考えたのですがエクセル2010だと、まず「編集を有効にする」をクリックしてもらい、次に「コンテンツを有効にする」をまたクリックしてもらわなくてはマクロが有効にならないのであきらめました。
回答者が何百人もいるのでとても説明しきれません。
もちろん、集まった回答の集計はマクロでやりますが。

回答No.9

MarcoRossiItaly

「1-」と「はい」に分けるのは禁止で、なぜか丸数字だと許されるという隠し条件があったのですね。回答というのは難しいものだなと、改めて思いました。

では、「はい」を入力させて、別の列に「1-はい」が表示されるという方式だったら、どうなのでしょう？次式とかでできます。

=match(a1,{"はい","いいえ","未定"},)&"-"&a1
あるいは
=choose(match(a1,{"はい","いいえ","未定"},),"1-はい","2-いいえ","3-未定")
あるいは
=hlookup(a1,{"はい","いいえ","未定";"1-はい","2-いいえ","3-未定"},2,)

お礼

> 「1-」と「はい」に分けるのは禁止で、なぜか丸数字だと許されるという隠し条件

後出しになってしまったようで申し訳ありません。
ただ、丸数字だと許されるかどうかはまだわかりませんが、休み明けに上司に理由を言ってそのように提案してみようと思ったのです。
上司からは、口頭で「必ず選択肢の頭に、いち、に、さんと数字をふること」という指定なのです。
そして他の方へのお礼でも書きましたが、一つのセルで数字の入った選択肢から相手に選択させることが必要なのです。
何故ならば、例では、1-はい,2-いいえ,3-未定　としましたが、実際はもっと別な言葉で、回答上疑問が出る可能性があり、問い合わせがあった場合、「それは一番です」とか「２番です」と答えるマニュアルも出来ているからです。

回答No.8

MackyNo1

エクセル2007以降のバージョンなら条件付き書式で対応するのが簡単かもしれません。

入力規則のリストは「はい」「いいえ」「未定」の3つを設定しておき、ホームタブの条件付き書式から「指定の値を含むセルだけを書式設定」を選択し、セルの値が「次の値に等しい」にして「はい」を入力し、「書式」から表示形式タブで「ユーザー定義」を選択し、値の欄に「"1-"@」と入力します。
同様に、「いいえ」に対しても新規ルールで条件付き書式を設定し、値の欄に「"2-"@」と設定します。

お礼

ありがとうございます。

回答No.7

MarcoRossiItaly

「数字が変化することを避けるため、オートフィルでの入力はしないでください」との注意書きを付す。理由も書いといてあげるのが親切ですね。オートフィルができる人は、だいたいオートフィルという用語も知っていると思われます。

全員が入力し終えたら、

=if(countif(a1,"*-はい"),"1-はい",)&if(countif(a1,"*-いいえ"),"2-いいえ",)&if(countif(a1,"*-未定"),"3-未定",)

といった数式で質問者さんが数字を正しく修正し、回答欄に値複写するとか。

No.1さんのおっしゃるように、オートフィルしづらい回答用紙にしておくことのも一つの方法と思います。まあアンケート結果の集計の手間が少し増えるということもあるし、特にお勧めではありませんが。

お礼

ありがとうございます。

回答No.6

cj_mover

より堅い方法として、Intersectを使った例。

Private Sub Worksheet_Change(ByVal Target As Range)
　　Const SLIST = "1-はい,2-いいえ,3-未定"
　　Const SREF = "C2:C51,F2:F51" ' 解答欄をＡ１形式Addressで指定
　　Dim r As Range
　　If Intersect(Target, Range(SREF)) Is Nothing Then Exit Sub
　　For Each r In Intersect(Target, Range(SREF))
　　　　If InStr(SLIST, r.Value) = 0 Then
　　　　　　MsgBox "オートフィルしないでコピーしてね"
　　　　　　Application.Undo　' ↑←　先にUndoするのもあり　お好みで
　　　　　　Exit For
　　　　End If
　　Next r
End Sub

　ＰＣスペックによっては通常の入力時にも微かに反応が遅いので
　入力速い人には多少ストレスがかかるかも。
　その代り、解答欄を絶対的に指定するのが容易。
ということで基本的には同じです。

お礼

何度もありがとうございます。

回答No.5

cj_mover

こんにちは。
ひとつ、ＶＢＡのイベントを使ったアイディア。
シートモジュールに

Private Sub Worksheet_Change(ByVal Target As Range)
　　Const SLIST = "1-はい,2-いいえ,3-未定"
　　Dim r As Range
　　If Target.Column <> 3 Then Exit Sub ' 解答欄の列（例示はC列）を排他指定
　　If Target.Row < 2 Or Target.Row > 51 Then Exit Sub ' 解答欄の行（例示は2:51）を排他指定
　　For Each r In Target
　　　　If InStr(SLIST, r.Value) = 0 Then
　　　　　　MsgBox "オートフィルしないでコピーしてね"
　　　　　　Application.Undo　' ↑←　先にUndoするのもあり　お好みで
　　　　　　Exit For
　　　　End If
　　Next r
End Sub

　普通に入力する人にはストレスなく普通の処理。
　オートフィルした人にだけ、コピーをうながすメッセージ。
　他、仕様変更の必要なし。

こんなの↑どうでしょう？
オートフィルしてもインクリメントしないように設定するのが簡単ですが
もしも、その設定をユーザーに変更されても困らないように、、、とか思ったもので。

とりあえず、以上です。

お礼

ありがとうございます。
VBAも考えたのですが、社内のエクセルが2010に統一されてからは、マクロ付きのファイルをマクロを有効にして使ってもらうのが大変になってしまい、二の足を踏んでおります。
2000のときは、起動時に「マクロを有効にする」を選んでくださいね。と書けばよかったものが、2010では何度もクリックしてもらわなければいけなくなってしまいました。

回答No.3

chonami

もう一つ、数字と、はい・いいえのセルが別でもいいなら逆転(?)発想で

入力規則には「はい、いいえ、未定」にしておいて、その左セルに答えによって「1-」「2-」「3-」と出るようにしておくとか。
（3つくらいならIFでも十分できますよね。）
上司の方がどうしても同じセルでないと駄目！とおっしゃるならできませんが(^_^;)

お礼

ありがとうございます。
一つのセルで
1-はい,2-いいえ,3-未定
から選択させることが必要なのです。
すみません。

回答No.2

sporespore

メニュー［ツール］－［オプション］をクリック
［オプション］ダイアログ－［数式］タブをクリック
「数式オートコンポリート」のチェックマークを外す。

お礼

オートフィルをさせない方法を質問したのではないのです。
ありがとうございました。

回答No.1

chonami

そもそも、アンケートは一問一答が基本ですので、オートフィルで回答する姿勢が問題でしょう。
アンケートの内容を重視するかユーザーの利便性を優先するかと言えば、やはりアンケートの内容に重きを置きますよね？
オートフィルできないようにしておくのが一番ではないでしょうか？

後は、設問ごとに空白行とかを入れておいて、フィルする事に抵抗を与えてみるとか？

お礼

> そもそも、アンケートは一問一答が基本ですので、オートフィルで回答する姿勢が問題でしょう。

まあ、普通のアンケートならそうなのですが、今回のはちょっと特殊でして、ほとんどが「1-はい」となるような質問で、それが何十行もあるのです。ですからオートフィルを禁止するのもちょっと・・・。
すみません。