今回は、ExcelのINDIRECT関数を利用して、状況に応じて選択肢が変わる動的なリストを作成する手順やコツを紹介します。
INDIRECT関数と動的リストがもたらす変化
Excelでデータ入力用のフォーマットを作る際、あらかじめ用意された項目から選べるようにするドロップダウンリストは多くの方が活用している機能かと思われます。
そのリストを一歩進め、最初に選んだ項目に連動して次のリストの選択肢が変化する仕組みが動的リストと呼ばれるものです。
この仕組みを実現するために中心となるのが、INDIRECT関数になります。
INDIRECT関数は、入力された文字列をただの文字としてではなく、実際のセルの場所や定義された範囲の名前としてExcelに認識させる働きを持っています。
この関数をうまく組み込むことで、手作業でリストを切り替える手間を省き、自動的に連動する入力フォームを作り上げることが可能になります。
日々の入力業務の中で、商品カテゴリと具体的な品名をセットで選ぶようなケースや、都道府県を選んだ後にその地域の市区町村を選ぶようなケースで、この機能が力を発揮する場面は多いといえます。
選択肢が自動的に絞り込まれることで、画面上がすっきりとし、操作に迷いにくくなる効果も期待できます。
動的リストを導入する具体的なメリット
動的リストをフォーマットに組み込むことで、入力作業におけるいくつかの課題を解決する手助けになります。
- 入力間違いや選び間違いを未然に防ぎやすくなる
- 多くの選択肢から目的のものを探す時間が短縮される
- フォーマット全体の見やすさと使い勝手が向上する
- 入力担当者の負担が和らぐ
たとえば、数百種類ある商品名の中から正しいものを一つ探すのは骨の折れる作業となります。
しかし、大分類、中分類と絞り込んでいくことができれば、最終的な選択肢は数個から十数個程度に収まることが多く、選ぶ作業がスムーズになります。
また、存在しない組み合わせを誤って入力してしまうリスクも減らせるため、後からデータを集計したり分析したりする際の手直し作業も少なくなる傾向があります。
動的リスト作成に向けた下準備：データの整理
動的リストを正しく動作させるためには、最初のデータ整理がとても大切な工程となります。
思いつきでリストを作り始めるのではなく、どのような階層構造にするかをあらかじめ整理しておくと、後の設定が進めやすくなります。
リスト用のデータを別の場所へまとめる
入力用のシートとは別に、リストの元となるデータを管理するための専用シートを用意しておくと、管理がしやすくなります。
1行目に大きな分類となるカテゴリ名を横に並べて入力し、その下の列にそれぞれのカテゴリに属する具体的な項目を縦に並べていくレイアウトが、視覚的にも分かりやすく管理しやすい形といえます。
あとから項目が追加されたり削除されたりした際にも、この一覧表をメンテナンスするだけで済むような状態を作っておくことが理想的です。
名前に使用できない文字への配慮
後述する名前の定義を行う際、Excelの仕様により、名前に使用できない文字やルールがいくつか存在します。
- 数字から始まる名前は使用できない
- スペース（空白）は使用できない
- ハイフンなどの一部の記号は使用できない
- セル参照と同じ名前は使用できない
もしカテゴリ名にスペースが含まれている場合、Excel側で自動的にアンダースコアに変換される仕組みがあります。
しかし、この変換が起こると、後でINDIRECT関数を使用した際に参照元の文字と定義された名前が一致しなくなり、リストがうまく機能しなくなる原因になりがちです。
そのため、リストに使う項目名には最初からスペースや特殊な記号を含めないよう、あらかじめ配慮してデータを作っておくことがトラブルを防ぐコツとなります。
リストの階層を深くする場合の考え方
大分類、小分類だけでなく、さらに中分類を加えた3段階の動的リストを作ることも可能です。
その場合は、大分類の選択肢から中分類のリストを呼び出し、さらに中分類の選択肢から小分類のリストを呼び出すという、リレーのような仕組みを作っていくことになります。
階層が深くなればなるほど、名前の定義を管理する手間は増えていきますが、その分だけ入力時の絞り込みはより細かいものになります。
複数の階層を持つリストを作成する際は、一覧表をシートごとに分けるなど、管理者が混乱しないようなファイル構成にしておくことが大切になるといえます。
動的リストの要となる名前の定義
データの整理ができたら、それぞれの項目グループに対して名前の定義を行っていきます。
ここでの名前が、後でINDIRECT関数を使って呼び出す際の合言葉のような役割を果たします。
効率的な名前の定義方法
一つひとつの範囲を選択して名前を手入力していくこともできますが、項目が多い場合は少し時間がかかってしまいます。
そのような時に役立つのが、選択範囲から作成という機能になります。
- カテゴリ名（見出し）と、その下の項目をすべて含めた範囲を選択する
- 数式タブの中にある選択範囲から作成を選ぶ
- 上端行だけにチェックを入れてOKを押す
この操作を行うだけで、一番上の見出しに入力されている文字を名前として、その下のセル範囲が一括で定義されるようになります。
手入力による文字の打ち間違いを防ぐ意味でも、この機能を活用することは有効な手段といえます。
INDIRECT関数を使った入力規則の実践設定
準備がすべて整ったら、実際にリストを表示させたいシートで設定を行っていきます。
ここでは、1つ目のセルで大分類を選び、2つ目のセルで小分類を選ぶという状況を想定して手順を進めます。
1段階目：大分類のリストを作成する
まずは連動の起点となる、大分類のリストを設定します。
- 大分類を入力させたいセルを選択する
- データタブからデータの入力規則を開く
- 入力値の種類をリストに変更する
- 元の値の入力欄に、大分類が入力されているセル範囲を指定する
これで、最初のセルに大分類の選択肢が表示されるようになります。
ここまでは、通常のドロップダウンリストを作る手順と同じです。
2段階目：INDIRECT関数で連動させる
続いて、大分類の選択に合わせて変化する、小分類のリストを設定します。
ここが動的リストを作るうえでの一番のポイントになります。
- 小分類を入力させたいセルを選択する
- 同じようにデータの入力規則を開き、種類をリストにする
- 元の値の入力欄に「=INDIRECT(大分類を入力するセル)」という形で数式を入力する
たとえば、大分類を入力するセルがA2であれば、「=INDIRECT(A2)」と入力することになります。
この設定により、ExcelはA2のセルに入力されている文字と同じ名前を持つ範囲を探し、それをリストの選択肢として表示する動作を行うようになります。
設定時、大分類のセルが空欄のままだとエラーメッセージが表示されることがありますが、そのまま設定を続けて問題ないケースがほとんどです。
動的リストを運用するための便利な工夫とテクニック
一度作った動的リストを、より長く、手間なく使い続けるためのヒントをいくつか紹介します。
テーブル機能を取り入れて範囲の拡張を自動化する
業務の中で新しい商品や新しい項目が増えることはよくあることです。
通常の設定では、リストの元データの下に新しい項目を追加しても、自動的にはリストに反映されず、再度名前の定義の範囲を修正する手間がかかります。
これを防ぐために役立つのがテーブル機能の活用です。
リストの元データとなる範囲をあらかじめテーブル化しておくと、そのテーブルのすぐ下に新しいデータを入力した際、自動的にテーブルの範囲が広がります。
これに連動して名前の定義の範囲も自動で拡張されるため、メンテナンスの手間を減らすことが可能になります。
将来的にデータが増えることが予想される場合は、最初からテーブルを組み合わせて作っておく形がお勧めといえます。
条件付き書式で未選択セルを目立たせる
リストから項目を選ぶ際、大分類だけを選んで小分類を選び忘れてしまうといった入力漏れが発生することもあります。
こうした入力漏れを防ぐためのヒントとして、条件付き書式を組み合わせる手法が挙げられます。
たとえば、大分類が入力されているのに小分類が空欄になっているセルに対して、背景色を薄い赤色で塗りつぶすような設定をしておく形です。
色で視覚的に未入力であることを伝えることで、入力担当者自身が気づきやすくなり、結果としてデータの欠落を防ぐ効果が期待できます。
INDIRECT関数を使う際の注意点とエラー対応
うまくリストが表示されない場合に、確認しておきたいポイントをまとめました。
- 選んだメイン項目の文字と、定義した名前が1文字でも違っていないか
- 前後に不要なスペースが混じっていないか
- 入力規則の元の値に入力したセル参照がずれていないか
特に、別のシステムからデータをコピーして貼り付けた場合などには、目に見えない半角スペースが文字の最後に紛れ込んでいることがあります。
INDIRECT関数はこのような小さな違いにも敏感に反応するため、リストが連動しない時は、参照元の文字を再確認してみることが解決への近道となることが多いです。
また、リストを設定したセルを下の行にコピーして使う場合、INDIRECT関数の中のセル参照が絶対参照になっていると、すべての行で同じセルを参照してしまいます。
行ごとに正しく連動させるためには、列だけを固定する複合参照に直しておくといった配慮も必要になります。
まとめ
ExcelのINDIRECT関数を用いて、自動で選択肢が切り替わる動的リストを作成する手順や運用の工夫について紹介しました。
データの準備や名前の定義など、最初は少し手間に感じる部分もあるかもしれませんが、一度設定を済ませてしまえば、その後の入力作業はスムーズに進むようになります。
データの追加にも対応しやすいようにテーブル機能などと組み合わせながら、ご自身の業務フォーマットに合わせた形で取り入れてみてはいかがでしょうか。
日々の入力作業をより快適にするためのヒントとして、役立てていただければ幸いです。