プルダウンリストとは？
あらかじめ単語を登録しておき、リストから選択してセルに表示させる機能です。
▼ プルダウンリストのメリット
- 繰り返し入力する手間を省く
- 入力ミスを回避する
- 表記揺れを防ぐのでデータとして扱いやすい
表記揺れとは、ひらがな・カタカナや送り仮名などの違いで、対象は同じでも表記の仕方にズレが生じる事です。
(*’ω’*)σ たとえば「りんご」と「リンゴ」はExcelでは別物として集計されてしまいます。
➡ プルダウンリストで「りんご」に統一しておけば正確にデータ分析ができるという訳です。
プルダウンリスト作成方法
プルダウンリストの作成方法は2通りあります。
- 表示項目をリストから選択する方法
🔹Excelのシート上に表示したい内容のリストを作成し、プルダウンに反映させる方法
🔹リストをテーブル化しておくと編集もしやすく、一般的によく使われる方法 - 表示項目を直接入力する方法
🔹ブック内にリスト部分を作成したくない場合にオススメの方法
🔹データ量が少ない場合は、簡単で使いやすい方法
🔹表示したい項目が多い場合は、手入力が少し大変
表示項目をリストから選択
あらかじめ別の場所にリストを作っておきます。（下図ではF3からF8がリストです）
- データタブを選択
- プルダウンリストを設定したい範囲をドラッグして選択
（↑複数のセルに設定したい場合。1つのセルに設定する場合は該当セルを選択） - データの入力規則をクリック
- 入力値の種類からリストを選択
- 元の値の右側のアイコンをクリック
- 表示させたいリストをドラッグして選択
- セル範囲が表示されるので右側のアイコンをクリック
- OKをクリック
▼ プルダウンリスト設定完了
- セルを選択すると右側に▼が出る
- ▼をクリックしてリスト表示
- セルに表示させたい項目を選択
このように、選ぶだけで必ず既定の単語が入力されるので、手間やミスが減り、データとしても扱いやすくなるというメリットがあります。
リストを別のシートに作成したい場合
入力するシート上にリストを載せたくない場合は、別のシートにリストを作成しておいてもプルダウンは利用可能です。
▼ 入力用シートをSheet1、リスト用シートSheet2に分けた場合
リストに設定する範囲を選択する際に、シートを切り替えるだけでOKです。
意図せず余計な部分を触ると「元の値」がうまく指定されないので、その場合は一度「キャンセル」して、やり直してみましょう。
表示項目を直接入力
表示させる単語をダイアログに直接入力していく方法です。
リストを使った時と同じ手順で「データタブ」➡「プルダウンを設定したいセルを選択」➡「データの入力規則をクリック」➡「入力値の種類に”リスト”を選択」まで進めます。
「元の値」の部分に、単語を半角カンマ「,」で区切って直接入力します。
- 表示項目が数ない場合は手早く作業できて簡単
- 入力中に方向キー「← →」を押すとバグるので注意
項目が全て入力できたら「OK」をクリックして完了です。
※ちなみにプルダウンリストを設定したセルには登録した単語以外は入力できません。
プルダウンリストの更新
表示項目の変更や追加をする際、元の値を修正したり、リストの参照範囲を設定し直すのは少々面倒に感じます。
▼ プルダウンリスト更新を楽にする方法
- 元データのリストをテーブル化しておく
- OFFSET関数とCOUNTA関数を用いる
元データのリストをテーブルにしておく
1. リストの中のセルをどれか1つ選択
2. 挿入タブを選択
3. テーブルをクリック
4. ダイアログボックスの先頭行をテーブルの見出しとして使用するにチェックしてOK
5. リストがテーブルになった
このあと先程と同様にデータタブ→プルダウンを設定したい範囲を選択→データの入力規則→リスト→元の値にテーブルを設定→OKと作業します。同じ手順です。
では、部署に企画部を追加してみます。
6. 海外事業部の下に企画部と入力→自動でテーブルが延長される
7. リストに企画部が追加された
使い勝手が良くなるので、初めにリストをテーブル化しておく事をおすすめします。
OFFSET関数とCOUNTA関数を使用する
関数を使って参照範囲が可変するリストを設定することができます。
元の値に =OFFSET($F$2,0,0,COUNTA($F:$F)-1,1) と入力します。
- =OFFSET（基準,行数,列数,[高さ],[幅]）
基準で設定したセルより、指定された行数と列数だけ移動した位置にあるセルの
参照値を返す関数。 - =COUNTA（値1,[値2…]）
範囲に含まれる空白ではないセルの個数を返す関数。
これでOKをクリックすれば完了です。
テーブルの場合と同じようにF8に企画部と追加すると、プルダウンリストにも追加されます。
テーブルの方が手軽ですが、これから関数を覚えたい！という方はぜひチャレンジしてみて下さい。
2段階プルダウンリスト
大分類・小分類のように2段階構造のデータをプルダウンリストに設定します。
リストはショートカットキー「 Ctrl + T 」でテーブル化して、テーブル名を「社員情報」とします。
1段階目のリスト登録です。
テーブルの「見出し部分だけ」を部署名のプルダウンリストに設定します。
元の値はテーブル名と見出しの文字以外全て半角で入力します。
なぜこう書くのかは深堀りするととめどないので割愛します。コピペして使えます ↓
テーブル名の変更だけ忘れずに！
=INDIRECT(“社員情報[#見出し]”)
イコール INDIRECT カッコ ダブルクォーテーション テーブル名 角カッコ シャープ 見出し 角カッコとじ ダブルクォーテーション カッコとじ
2段階目のリスト登録です。
テーブルの見出しを参照してリスト表示するように設定します。
こちらも書き方はこういうものという事で。
簡単に言うと、角カッコでテーブルの一部を取出しているイメージです。
テーブル名と1段階目で使用したセルだけ書き換えを忘れずに！
=INDIRECT(“社員情報[“&H3&”]”)
イコール INDIRECT カッコ ダブルクォーテーション テーブル名 角カッコ ダブルクォーテーション アンド 1段階目のセル アンド ダブルクォーテーション 角カッコとじ ダブルクォーテーション カッコとじ
同様の手順で3段階でリストを設定することも可能です。
(*’ω’*)σ データ量が多い場合は、段階構造にすると使いやすいフォームが作成可能です。