Excelでメンテフリーなプルダウンリストを設定する
Excelを使っていて、頻繁に使う機能として入力規則→リスト機能があります。個人差なく、決まったデータを入力してほしい場合には、プルダウンリストとして設定しておくことで、望んだ結果が得やすくなります。
これはデータベースとして運用する場合に効果的です。すでにデータベースを運用している人もいるでしょう。
しかし、プルダウンリストの選択肢の追加・変更が多いと、一概に便利とは言えなくなります。修正箇所が多いと、それだけでひと手間です。
この修正作業がなくなる、メンテナンスフリーの設定ができたらよいなと思うことはないでしょうか。実は、プルダウンリストの設定方法によりメンテナンスフリーにできます。
ここでは、「プルダウンリストの設定の基本」「関数を使ったメンテナンスフリーの方法」「テーブルを使ったメンテナンスフリーの方法」について詳しく解説します。
Contents
選択肢をプルダウンリストに設定する基本
Excelでデータ管理をするとき、いくつかの決まった項目だけを手早く入力したいことがあります。このときに便利なのが「入力規則→リスト」機能です。
例えば、下図のA1セルに「A」「B」「C」の文字列をリストから選んで入力するには、まず「データタブ」クリック→データツールグループの「データの入力規則」クリック→プルダウンリストから「データの入力規則」をクリックします。
すると、下図の「データの入力規則」ダイアログが開くので、「設定」タブ→入力値の種類の下のプルダウンリストから「リスト」選択してクリックします。
入力値の種類に「リスト」を選ぶと、下図のダイアログになります。元の値に「A,B,C」と入力してください。図は、入力が終わったあとのようすです。この状態で「OK」をクリックすると、設定完了です。
ワークシートでは、下図のようにプルダウンリストから「A」「B」「C」の3つの項目が選択できるようになります。
データの入力規則の「元の値」に入力するフォーマットは、選択肢にしたい各要素を「,（コンマ）」で区切ります。
フォーマット
[選択肢1],[選択肢2],…,[選択肢n]
将来に渡って、変更がないリストであれば、この方法で問題ありません。
シートのデータを選択することもできる
また、すでにシートに入力されたデータをプルダウンリストの選択肢にすることも可能です。下の図のように、E列に入力された「A」「B」「C」をA1セルのプルダウンリストの選択肢にしてみましょう。
データの入力規則ダイアログを開いて、入力値の種類に「リスト」を選ぶのは、前項と同じです。違うのは、元の値に数式として「=$E$1:$E$3」と入力することです。手入力でも可能ですが、マウスを使うと簡単に入力できます。
まず、元の値のテキストボックス内をクリックしてカーソルを移動させます。その状態で、シートの「E1:E3」をドラッグします。ドラッグしている間は、データの入力規則ダイアログボックスが消え、下のテキストボックスのみ表示されます。ドラッグをやめるとデータの入力規則ダイアログボックスが表示されます。
元の値に入力できたらOKをクリックします。そうすると、下図のようにA1セルに「E1:E3」のデータがプルダウンリストの選択肢として設定されます。
前項で説明した「元の値にコンマ区切りで入力する方法」は、時間がかかります。実用上は、シート上にすでにデータ入力されていることが多いので、その部分を参照する事が多いです。
これでは項目の増減で手がかかる
ただし、この方法では要素の増があった場合に手間がかかります。例えば、下図のようにABCに続いてE4セルにDを追加した場合を見てみます。単にE4セルに「D」と入力しただけでは、A1セルのプルダウンリストに「D」は追加されません。
「D」の追加を反映させるためには、前項の手順を行う必要があります。
あなたが、一人で使うExcelシートである場合は、このような設定をしていても特に問題ありません。変更があったとき、あなたがすこし面倒な思いをするだけです。
しかし、複数の人が使うExcelシートで、しばしば選択肢の追加変更が予想される場合は、少々問題があります。Excelシートを使う複数の人が、あなたと同等かそれ以上のExcelスキルを持つとは限らないからです。
場合によっては、せっかくあなたが効率化したExcelシートが、メンテナンスできないという理由で捨て去られることもありえます。そこで、選択肢の増減に対応したメンテナンスフリーのプルダウンリストが作れないかを考えなければなりません。
メンテフリーのプルダウンリストその1（関数を用いる）
実は、増減に対応したメンテナンスフリーのプルダウンリスト設定方法は、下図のように列全体を参照することです。元の値に「=$E:$E」と入力して、E列全体を参照します。シートの列番号「E」をクリックしても図と同様の入力状況になります。
この設定をしておけば、下図のように空欄とデータが追加になっても問題なく対応できます。
ただし、列全体を参照する方法だと、タイトルが付いている場合に、そのタイトルもプルダウンリストの選択肢に表示されてしまいます。実用上問題ないかもしれませんが、少しブサイクです。
このようなときに使えるのが、OFFSET関数とCOUNTA関数を用いる方法です。データの入力規則ダイアログの元の値に数式を入力できることを見て、関数も入力できるのではないかと考えた人がいるかも知れません。その推測は、大正解です。
具体的には、下図のように「=OFFSET($E$2,0,0,COUNTA($E:$E)-1,1)」と入力します。関数名やカッコは手入力する必要があります。小文字で問題ありません。セルの参照は、手入力だけでなく、シートをクリック・ドラッグすることで入力することができます。
このOFFSET関数を用いることで、下図のようにタイトルを省いてプルダウンリストに表示させることができます。
もちろん、データが追加になったとしても問題なくリアルタイムに反映されます。
このOFFSET関数を使った方法の唯一の弱点は、データを追加するときに空欄を作るとうまくプルダウンリストに反映されないことです。下図では、「C」の下に2つの空白セルを置いて「D」を入力しています。A1セルのプルダウンリストには、「D」がなく、「（空白）」が代わりに表示されているのがわかります。
この点に注意すれば、メンテナンスから解放される有効な方法です。
メンテフリーのプルダウンリストその2（テーブルを用いる）
メンテフリーのプルダウンリストを作成するもう一つの方法は、テーブル機能を用いることです。テーブルを使うには、テーブルに設定したいセルを範囲指定して、「挿入」タブクリック→テーブルグループ「テーブル」をクリックします。
すると下図の「テーブルの作成」ダイアログが開きます。範囲指定したセルが参照されていることを確認します。また、先頭行にタイトルを設定している場合は、「先頭行をテーブルの見出しとして使用する」チェックボックスにチェックを入れます。今回の例は、タイトルがあるのでチェックします。
設定が終わったら「テーブルの作成」ダイアログのOKボタンをクリックします。するとテーブルに設定されたセルが、テーブルの初期書式に自動で設定されます。下図では、青基調の書式に設定されました。
このテーブル設定された状態で、データの入力規則からリストの設定をします。リストの設定方法は、ここまでの操作と同様です。このとき、下図のようにテーブルのタイトルを除外して元の値に入力します。シートをドラッグして入力すると、数式の参照と見た目は変わらずに自動で入力されます。
テーブルを使ってプルダウンリストの設定が終わった図が、下図です。これまで同様にプルダウンリストの設定がされています。
この状態で、E5セルに「D」を入力すると、テーブルの範囲が自動で広がります。そして、A1セルのプルダウンリストにも「D」が追加して表示されます。
一方、テーブルの最下行から空欄を作ってデータを入力するとどうなるでしょうか。下図では、「E」を1行空けて入力しています。A1セルのプルダウンリストには、「E」は追加されていません。
また、テーブルの範囲が自動で広がって再設定されていない事もわかります。これは、青基調の書式設定がなされていないことからも明確です。
このように、テーブル機能と入力規則を組み合わせれば、メンテナンスフリーのプルダウンリストを作成し運用することができます。
まとめ
ここまで、メンテナンスフリーのプルダウンリストの設定方法について解説してきました。
リストの設定は、Excelを使う上で高頻度で利用されるテクニックです。特に、データベースとして運用する場合は、入力者の誤入力を防ぐために絶対に必要な手法と言えます。
しかし、メンテナンスが必要になると通常業務を圧迫します。また、あなたが未来永劫、常にメンテナンスに携われたら良いですが、そうはならないはずです。したがって、メンテナンスがいらない作りにしておく必要があります。
ここで紹介した方法は内容は少し高度ですが、一度覚えてしまえば応用がしやすいテクニックです。そして、Excelの詳しいあなたに問い合わせの来る頻度が目に見えて減るはずです。
