Excelファイルは1人で作業する際にも使いますが、一つのファイルをシェアしながら使うこともあります。Excel上で複数の人にデータ入力してもらうのに備えておくと便利なのがドロップダウンリストです。
Excelではアクシデントがつきもの ドロップダウンリストはエラー対策にもなります
人事情報の変更や勤務時間の入力、会議のスケジューリングなど、複数の人にExcelへのデータ入力をお願いすることがあります。
ファイルを作成した人以外はどのような意図でファイルが作られたのか、ファイルがどのような仕組みになっているかが分からず、全く想定していないようなデータを入力して、ファイル全体が機能しなくなることも。
Excelはきちんと機能するように作る事が第一ですが、それに加えて、エラーが出ないような対策を取ることも重要です。
そのためには、入力してもらいたいデータを選択肢として示すドロップダウンリストは有効です。
たとえば、人事情報をExcelに入力してもらう際、所属部署の記入欄をドロップダウンリストにしておけば、名称が統一されて後の集計が効率的に行えます。
ドロップダウンリストにある統一された名称を指定すれば、ピボットテーブルやSUMIF関数を使った集計が簡単にできるのです。
「使う人にとって便利」と言うだけでなく、エラー対策としても優れていますので、積極的に取り入れてみて下さい。
作り方は簡単です、早速、やってみましょう。
「データ入力規則」から「リスト」を選んで、データを入力するだけ！
ドロップダウンリストを作成します。
サンプルとして、CountDownJapan14/15の日程である12月28日から31日までを選択肢としたドロップダウンリストを作ってみます。
１．ドロップダウンリストを作りたいセルを指定します
２．Excelの上部にある「データ」タブから「データツール」にある「データの入力規則」をクリックして、リストから「データの入力規則」を選びます
３．「データの入力規則」画面が開くので「入力値の種類」のリストから「リスト」を選択
４．「元の値」の欄が現れるので、そこにリストにするデータを入力します。
ここで大事なポイントがあります。それは、リストにするデータはカンマ（半角の”,”です）でつなぐこと。カンマでつながないと、1つの選択肢として横に広がるだけで、ドロップダウンになりません。
全角の読点（”、”）でつなぐと…
1つの選択肢になって、横に広がるだけ。
カンマ（半角の”,”）でつなぐと…
ドロップダウンリストとして表示されました。
「リストにするデータをカンマでつなぐ」ことに注意して入力しましょう。
リストデータの入力を簡単に Excelシートにデータ入力→セル範囲指定
ドロップダウンリストの作り方は以上です。
ですが、リストをいちいち「元の値」の欄に入力するのは面倒です。欄が小さいですし、選択肢の数が増えると修正もしにくくなります。
そこでもっと便利な入力方法を使いましょう。
Excelシートにデータ入力してセル範囲指定することで、リストを作ります。
１．ドロップダウンリストにしたいデータをExcelシートに入力します
ー２．から４．までは先ほどと同じですー
２．ドロップダウンリストを作りたいセルを指定します
３．Excelの上部にある「データ」タブから「データツール」にある「データの入力規則」をクリックして、リストから「データの入力規則」を選びます
４．「データの入力規則」画面が開くので「入力値の種類」のリストから「リスト」を選択
ーここまでは先ほどと同じー
５．「元の値」の欄が現れるので、１．で入力したドロップダウンリストのデータがあるセルをドラッグして指定します
６．ドロップダウンリストができました
セル範囲に名前をつけ、「INDIRECT関数」を使ってドロップダウンリストにする
Excelシートにデータ入力してセル範囲指定することによってデータ入力を簡素化する方法を見てきましたが、もう一つ、ドロップダウンリスト入力を簡単にする方法があります。
それが「セル範囲に名前をつける」方法です。
たとえば、CountDownJapan14/15の開催日毎の出演アーティストのリストにfirst（12月28日の出演アーティスト）、second（12月29日の出演アーティスト）、third（12月30日の出演アーティスト）、forth（12月31日の出演アーティスト）、と名前をつけて、
（上のようにセルの範囲に名前をつける）
”first”と指定すれば、12月28日の出演アーティストのリストがドロップダウンリストに表示されるようにします。
Excelシートにあるセルには「A1」や「D12」など番地によって名前がつけられています。これと同じように特定のセル範囲について、私たちユーザーが名前をつけることができます。
それが、「名前の定義」です。
「名前の定義」をしておくと、通常の「A1」や「D12」のようなセル番地と同じように、指定した名前を範囲指定に使うことができます。
「名前の定義」の方法は簡単です。
１．名前をつけたいセルをドラッグして範囲指定する
２．Excel上部の「ファイル」タブの下にある「名前ボックス」にセル範囲に名付けたい名前を入力してEnterキーをおす。
これで、名前の定義（セル範囲に名前をつける）ができました。
セル範囲に名前をつけたら、先ほどの「元の値」の欄に、セル範囲に名付けた名前（first）を入力すれば完成です。ドロップダウンリストができあがります。
ですが、ここで一つ問題があります。
それは”first”は文字であるということ。ドロップダウンリストに文字を入力してしまうと、入力されたデータ自体（ここでは”first”）がリストになってしまいます。
”first”を単なる文字データではなく、A1やD12といった”セル範囲”のデータに変換する必要があるということです。
それを可能にするのが「INDIRECT関数」です。
使い方は簡単です。INDIRECT関数を選択してセル範囲に名付けた名前を入力すれば、文字データをセル範囲指定のデータへと変換してくれます。
「元の値」の欄にINDIRECT関数を使って名前をつけたセル範囲を指定します。
ここでは、”first”を直接入力するのではなく、上の例と同様に、セルに”first”と入力した上で、セル（C4セル）を指定する方法をとります。
INDIRECT関数によって、”first”が文字からセル範囲指定のデータへと変換されて、リストとして表示されるようになりました。
セル範囲につけた名前をドロップダウンリストにして、2段階のリストを作ります
先ほど説明した「セル範囲に名前をつける」方法を使って2段階のリストを作ります。
年末に行われる「CountDownJapan14/15」のアーティスト別の出演時間を表示する表を作ってみます。
目標は、
①Dateでドロップダウンリストから開催日を選ぶ
②出演アーティストリストに名付けた、セル範囲の名前を表示させる
③②のセル範囲の名前によってドロップダウンリストを表示させる
④③でアーティストを選ぶと、スタートの時間が表示される
④についてはVLOOKUP関数を使用します。
なお、「2段階のリスト」の”2段階”というのは、最初のドロップダウンリストの選択肢によって、次のドロップダウンリストの選択肢の内容が変化するようなリストのことを言います。
上の例で言うと、①の日程をどの日にするかによって③の出演アーティストのリストが変化するようになっています。
順を追って、見ていきましょう。
１．”Date”で12/28-31の日程を選べるようにドロップダウンリストを作る
２．１．で日程を選択すると”Artist”で該当する日の出演アーティストがリスト表示される。
これには、各日程の出演者リストを作成して「セル範囲に名前をつける」ことで対応します。
セル範囲の名前には使える文字が決められていて、数字を入れることができないので、「12/28(SUN)」のような日付を入力できません。
そこで、一旦、セル範囲の名前をfirst、second、third、forthとしておきます。
このままではDateで「12/28(SUN)」が選択されても、Artistで出演者リストをリスト表示できません。
「12/28(SUN)」がfirstとなるように変換する必要があります。
様々な方法がありますが、ここではIF関数を使う方法をとります。
「もし、セルB4が”12/28(SUN)”なら”first”と表示、そうでなければ、次のIF関数の式へ進む」
のようにして
12/28(SUN)→first
12/28(MON)→second
12/28(TUE)→third
12/28(WED)→forth
と表示されるようにします。
C4セルでは、Dateで選択した日付に対応する出演者リストにつけた、セル範囲の名前（first,second,third,forth）が表示されることになりました。
３．Artistを示すB5セルで出演者リストを表示。
B5を指定して、「データの入力規則」から「リスト」を選び、出演者リストのセル範囲が表示されるC4を指定すれば完了です。
ただし、先述の通り、文字だけでは入力された内容がそのままリストになるだけですから、セル範囲指定のデータへ変換する必要があります。INDIRECT関数を使った入力です。
その結果
12/29,30ともに該当する出演者リストがArtistを表示するB5セルで表示できるようになりました。
４．出演者と出演時間の表を作り、VLOOKUP関数で出演時間を表示
次のような表を作ります。
途中で切れていますが、全アーティストのデータを入力してあります。
B6セルにVLOOKUP関数の式を入力。
具体的には、上の表の範囲を範囲指定して、B5セルで指定されるアーティストと一致したら、隣にあるStart Timeを表示するセルを表示するように入力します。
実際に12月28日出演のアーティスト「きゃりーぱみゅぱみゅ」を指定してみましょう。
正しく表示されましたね。
ファイルをオープンにする場合は、見栄えも考えて不要なデータは非表示にしておけばなおよいでしょう。
まとめ
単純なドロップダウンリストの作成も、関数と組み合わせることによって、幅広い使い方ができることが分かりました。
関数や機能を単体で使うのではなく、組み合わせて使うことを常に考えておくとExcelの用途は大きく広がります。
＜おまけ＞
CountDownJapan14/15のチケットがほぼ完売しています。
初日の1日券がかろうじて残っているようなので興味のある方は是非。
私は31日に参戦します。現地組の皆さん、楽しみましょうね。