エクセルを使っていて、入力したい内容が定型的ならプルダウンリストが便利です。項目数が少なければ簡単に作成できて編集もすぐに行えます。途中でリストを増やしたり、後のメンテナンス性を想定して設計すしておくと、あとで管理するときが楽です。
エクセルの参考書によってやりたい事は同じでも使う関数が異なることがよくあります。プルダウンを使いつつ、その後編集することを考えた方法を紹介します。
サンプルでは、カテゴリを選ぶと次の選択肢の内容（項目）が連動します。たとえばB3セルに「OS」をプルダウンから選択するとC～G列に対応する範囲がリストとして表示されます。
プルダウンリストの作り方
プルダウンは大項目と小項目で2つ作成します。まず、全ての項目を並べたシートを準備しておきます。サンプルでは結果を表示したいシートとは別に「データ」という名称のシートを用意しました。式をコピペするなら大項目は縦、小項目は横に記述してください。
親項目の設定
大項目（サンプルだと「カテゴリ））の数は、シンプルにまとめるとすっきりします。
プルダウンを表示したいセルを選択し、「データ」タブ内にある「データの入力規則」をクリックします。入力値の種類を「リスト」、元の値に「親項目のセル範囲」を指定します。
このとき、後で行の追加や削除に対応できるように1行多めに選択してください。
$B$3:$B$6」です。大項目はこれでプルダウンが表示されます。
小項目の設定
大項目に対応した行を選択するために、小項目はOFFSET関数とMATCH関数を組み合わせて設定します。
まず、プルダウンを作成したいセルを選択し「データ」タブ内にある「データの入力規則」をクリックします。
入力値の種類を「リスト」にし、元の値に以下の式を入れます。
$B$2,MATCH(B3,データ!$B$3:$B$6,0),1,1,5)これをコピペしやすいように書くと次の式になります。
緑字は別シートにあるセル、赤字は結果を表示したいセルです。赤字のプルダウン表示セルは相対参照です。
解説
OFFSET(基準,行数,列数,高さ,幅)
OFFSET関数は基準セルに対して、離れたどの範囲のセルを参照するのか指定します。
基準:
基準セルは、もし大項目に見出しをつけていたらそのセルを選びます。見出し（サンプルなら「カテゴリ」の部分を指す）がなければ選択したい範囲の1つ上のセルを指定します。
行数、列数:
基準セルから移動する行数、列数を指定します。「MATCH(プルダウン表示セル,大項目の範囲,0),1」の記述が該当します。
行数は大項目の結果を反映して行を指定するためにMATCH関数を使います。MATCH関数は後ほど解説します。
高さ、幅:
高さと幅は大項目1つあたりの小項目数を指定します。サンプルなら1行5列です。
MATCH(検索値, 検索範囲, [照合の種類]
MATCH関数はセルの値を照合して照合にあった結果の座標を返す関数です。
検査値は相対参照です。検索範囲は大項目の記述範囲、照合の種類は0（完全一致）です。
関数でいくつか指定するポイントを図でまとめておきます。
まとめ
プルダウンの連携について、書籍やネットでは名前の定義をしてINDIRECTを使うものが多いですが追加したとき再設定に少し手間がかかります。関数は見た目複雑でも大項目は追加・削除で終わり、範囲の設定も簡単に変更できるのが利点です。
応用として、入力をプルダウンで選択したらVLOOKUP関数でデータを吐き出すようにすれば、より実用性が増えます。ただし、VLOOKUP関数の注意点は検索範囲を左の1列目にしてからなので、もし任意の列から検索する場合はINDEX関数とMATCH関数を組み合わせで行いましょう。
参考
