テーブルで作る二段階リスト【2025年ExcelFansアドベントカレンダー】

～～～入力規則を使ってメイン項目からサブ項目を確実に絞り込む～～～

Copyright © 2025 LWP 山中 一弘 本資料は、出典を明記いただければ、商用・非商用を問わず、ご自由に複製・改変・再配布していただけます。なお、著作権表示は改変せず、そのまま記載してご利用くださいますようお願いいたします。

要約

本稿は、Excelテーブル「メニュー」（列：区分・料理名ほか）を前提に、1段目で区分を選ぶと2段目の料理名候補が自動で絞り込まれる「二段階リスト（連動ドロップダウン）」を、入力規則（データの入力規則）のリスト機能だけで実装する手順を解説します。成立の要点は、テーブルが「区分」でソートされ、同一区分が必ず連続して並んでいること（連続性）であり、この前提が崩れると候補の混入・欠落が起きます。

実装は「連続ブロック抽出」を用い、(1) XMATCHで選択区分の先頭位置を求め、(2) COUNTIFで件数（高さ）を求め、(3) OFFSET（またはINDEX:INDEX）で“範囲参照”を生成して入力規則に渡します。入力規則で安定運用するため、候補は配列ではなく範囲参照で返す点がポイントです。参照は構造化参照を文字列としてINDIRECTで評価する形を採用しており、入力規則に渡しやすい一方、テーブル名・列名変更に自動追従しないため、名称は固定運用を基本とします。

第1章 結論：この形で動きます

最初に完成形を示します。テーブル「メニュー」が「区分」でソートされ、同一区分が連続している場合、入力規則（データの入力規則）のリスト元に所定の式を設定するだけで、二段階リスト（区分→料理名）が動きます。ポイントは、2段目の候補を「配列」ではなく「範囲参照」として返すことです。

この章は、Excelの入力規則（データの入力規則）の「リスト」を使い、区分→料理名の二段階リストをOFFSETで成立させる最短形を提示します。対象読者は、業務でマスタ表（テーブル）からプルダウン入力をさせたい一方で、補助列や複雑な名前定義を増やしたくない実務者です。この章を読むと、区分の選択に連動して料理名候補が切り替わる仕組みを、完成数式と成立条件（同一区分が連続していること）を含めて説明できるようになります。

1.1 対象テーブル「メニュー」のデータ例

前提は、Excelのテーブル（構造化参照）として「メニュー」という名前の表があり、列は少なくとも「区分」「料理名」の2列を持つことです。本手法は、2段目（料理名）の候補をOFFSETで「連続した範囲参照」として切り出すため、同一区分の行が表の中で連続している必要があります。

ここでいう「区分が連続している」とは、同じ区分が途中で途切れず、1つの連続ブロックとして並んでいる状態です。例えば、前菜が複数行続き、次にスープが複数行続き、次に主菜が複数行続く、という並びです。反対に、主菜→副菜→主菜のように同一区分が途中で再登場する並びだと、開始位置から件数分を切り出した範囲に別区分が混ざり、ドロップダウン候補が意図どおりになりません。したがって、テーブル「メニュー」は「区分」でソートし、同一区分を必ず連続にしておきます。

また、区分列に重複があっても、入力規則のドロップダウンリストは一意化された候補として表示される前提でよいため、区分の候補を作るための補助列や一意化の前処理は不要です。

1.2 入力規則に入れる数式

この節は、入力規則（データの入力規則）の「元の値」に設定する完成数式を、区分（1段目）と料理名（2段目）に分けて提示します。以降、区分の入力セルをI3、料理名の入力セルをJ3とし、J3側の入力規則は「I3で選ばれた区分に応じて」候補が切り替わる前提で説明します。セル参照（I3）は実際の配置に合わせて置き換えてください。

区分（1段目）の式は次のとおりです。

=INDIRECT("メニュー[区分]")

入力規則の「元の値」には構造化参照（メニュー[区分]）を直接書けないため、INDIRECTで文字列から参照に変換して渡します。これにより、テーブル「メニュー」の区分列が候補範囲として認識され、ドロップダウンに表示されます。区分列に同じ値が複数行あっても、ドロップダウンには一意化された候補が表示される前提でよいので、余計な処理は不要です。

料理名（2段目）の式は次のとおりです。

=OFFSET(INDIRECT("メニュー[料理名]"),XMATCH(I3,INDIRECT("メニュー[区分]"))-1,0,COUNTIF(INDIRECT("メニュー[区分]"),I3))

この式も、入力規則の「元の値」では構造化参照を直接書けないため、メニュー[料理名]とメニュー[区分]の双方をINDIRECTで参照化します。そのうえで、XMATCH(I3,INDIRECT("メニュー[区分]"))が、選択された区分（I3）が区分列に最初に現れる位置（1始まり）を返します。OFFSETの行オフセットは0始まりで数える必要があるため、-1して位置を行オフセットに変換します。最後にCOUNTIF(INDIRECT("メニュー[区分]"),I3)で、その区分が何件あるか（連続ブロックの高さ）を数え、その件数分だけ縦方向の範囲参照として切り出します。幅は省略しているため、基準参照（メニュー[料理名]）と同じ1列になります。

1.3 なぜ二段階リストになるか

二段階リストが成立する理由は、2段目の候補（料理名）を「選択区分の先頭位置（開始位置）」から「その区分の件数（件数）」だけ切り出した「範囲参照」として入力規則に渡しているからです。OFFSETは値の配列ではなく範囲参照を返せるため、開始位置と件数が決まれば、区分の選択に連動して候補範囲を切り替えられます。ここで区分が連続していることが前提になるのは、件数分の切り出しが「連続ブロック」を想定しており、途中で区分が途切れると切り出し範囲に別区分が混入するためです。

2. 前提条件とデータ設計

この章は、二段階リスト（区分→料理名）をOFFSETで成立させるための設計要件を固定します。本手法は、入力規則が受け取れる候補が「セル範囲（範囲参照）」に限られること、かつ2段目候補を「連続ブロック」として切り出すことに依存します。したがって、データ設計の前提が崩れると、数式自体が正しくても候補が壊れます。この章を読むと、壊れる条件と壊れ方を説明でき、運用上の禁止事項（ソート崩れ、命名変更など）を設計要件として明文化できます。

2.1 区分でソートされ、同一区分が連続していること

最重要の前提は、テーブル「メニュー」が「区分」でソートされ、同一区分が必ず連続していることです。本手法の2段目は、区分の先頭位置をXMATCHで特定し、その位置からCOUNTIFで数えた件数ぶんだけOFFSETで範囲を切り出します。ここで切り出される範囲は「連続した行」を前提にしているため、同一区分が途中で途切れる配置は設計として禁止します。

この前提は「推奨」ではなく「要件」です。運用で区分以外の列で並べ替える可能性がある場合は、並べ替えを禁止する、または必ず区分で再ソートする運用ルールを明文化します。テーブルの行追加が発生する場合も、追加後に区分で再ソートされることを前提にします。

2.2 連続していないと何が起きるか

区分が連続していない場合、壊れ方は大きく3種類に分かれます。どれも数式の誤りではなく、連続ブロック前提が崩れた結果として発生します。

混入は、本来は別区分である行が、OFFSETで切り出された範囲に含まれてしまう現象です。例えば主菜が途中で途切れ、主菜の行数（COUNTIF）が多いまま先頭位置から件数分を切り出すと、主菜ブロックの後ろにある別区分（副菜など）が候補として混ざります。ドロップダウンに「区分と一致しない料理名」が出るため、入力ミスを誘発します。

欠落は、同一区分が複数箇所に分散しているときに、2つ目以降のブロックが候補に入らない現象です。XMATCHは区分の先頭位置（最初の一致）を返すため、後続の同一区分は切り出し開始点に含まれません。その結果、同一区分の料理名が一部表示されない状態になります。

ずれは、混入と欠落が組み合わさった実務上の見え方です。候補が「本来あるべき集合」から外れ、別区分が混ざり、必要な候補が抜けるため、ユーザーは原因を入力規則や数式の問題と誤認しやすくなります。したがって、ソート崩れを検知できない運用は事故に直結します。

2.3 入力規則の制約：候補は「セル範囲（範囲参照）」

入力規則（データの入力規則）の「リスト」は、候補を「セル範囲」として受け取ります。ここで必要なのは、値の配列ではなく、ワークシート上の範囲参照です。二段階リストが成立する核心は、2段目候補をOFFSETで「範囲参照」として返し、入力規則に渡している点にあります。

また、入力規則では構造化参照（メニュー[区分]、メニュー[料理名]）を直接指定できないため、INDIRECTで文字列から参照に変換して渡します。これにより、テーブル列を候補範囲として扱えます。区分列に重複があっても、ドロップダウン表示は一意化される前提でよいため、区分候補のために別途UNIQUE相当の加工をする必要はありません。

2.4 命名固定：テーブル名・列名を変えない

本手法はINDIRECTで参照を作るため、テーブル名と列名は設計要件として固定します。INDIRECTは文字列を参照に変換する関数であり、参照先の名前変更に対して自動追従しません。したがって、テーブル名「メニュー」や列名「区分」「料理名」を変更すると、数式が参照を解決できず、入力規則が機能しなくなります。

運用上、名称変更が起こり得る場合は、名称変更を禁止するか、名称変更時に入力規則の式も同時に更新する手順を用意します。特に、ユーザーがテーブル名を自動生成のまま運用しているケース（例：Table1など）は変更されやすいため、導入時点で必ず意味のある、変更されにくいテーブル名に変更します。

3. 数式を分解して理解する

この章は、二段階リストの2段目（料理名）がどのように「連続ブロック」として切り出されるのかを、式の部品ごとに分解して説明します。対象読者は、完成形は動いたが、なぜ動くのか、どの前提が崩れると壊れるのかを説明できない実務者です。この章を読むと、INDIRECT、XMATCH、COUNTIF、OFFSETがそれぞれ何を返し、最終的に入力規則が受け取れる「範囲参照」になるまでの論理を、引数レベルで説明できるようになります。

3.1 構造化参照とINDIRECT

テーブル「メニュー」の列を参照するとき、通常のセル参照ではなく構造化参照（例：メニュー[区分]）を使うと、列範囲を意図どおりに指定できます。しかし、入力規則（データの入力規則）の「元の値」には、構造化参照を直接書けません。入力規則が要求するのは、最終的に「セル範囲（範囲参照）」として解決できる指定です。

そこでINDIRECTを使い、"メニュー[区分]"や"メニュー[料理名]"という文字列を、実際の範囲参照に変換します。ここでの要点は、INDIRECTは「参照を返す」関数であり、入力規則が受け取れる形に整形するための変換器として使っていることです。区分の候補はINDIRECT("メニュー[区分]")だけで成立し、区分列に重複があってもドロップダウン表示は一意化される前提でよいので、別途の重複除去は不要です。

3.2 XMATCH：一致ブロックの開始位置を確定

2段目の候補を切り出すには、まず「どこから切り出すか」を決める必要があります。そこでXMATCHを使い、選択された区分が区分列に最初に現れる位置を求めます。

例では、区分の入力セルをI3とし、次の式を使います。

この戻り値は、区分列の中でI3と一致する最初の位置です。ここで取得しているのは「ブロックの先頭行」です。XMATCHが先頭行を返す性質を利用しているため、同一区分が分散している配置だと「最初のブロック」しか基準にできません。この点が、連続性を要件として固定する理由に直結します。

3.3 COUNTIF：ブロックの高さ（件数）を確定

開始位置が決まっても、何行ぶん切り出すかが決まらなければ範囲は作れません。そこでCOUNTIFを使い、選択された区分が区分列に何件あるかを数えます。

この戻り値が「ブロックの高さ（件数）」になります。ただし、COUNTIFは表の中で同一区分が何回出現するかを数えるだけで、出現位置が連続しているかどうかは判定しません。したがって、同一区分が分散していると、件数は全体件数として大きくなり、後述のOFFSETが別区分を含む範囲を作ってしまいます。

3.4 OFFSET：開始位置＋高さで「範囲参照」を確定

OFFSETは、基準となる参照から、行・列方向にずらし、指定した高さと幅を持つ「範囲参照」を返します。入力規則が受け取れるのはこの「範囲参照」なので、OFFSETが最終成果物を作る役割を持ちます。完成形は次のとおりです。

=OFFSET(INDIRECT("メニュー[料理名]"),XMATCH(I3,INDIRECT("メニュー[区分]"))-1,0,COUNTIF(INDIRECT("メニュー[区分]"),I3))

ここで、INDIRECT("メニュー[料理名]")は料理名列全体の参照です。OFFSETの第2引数（行数）は、どこから切り出すかを指定しますが、OFFSETは「0が先頭行」を意味します。一方、XMATCHは一致位置を「1から数える」ため、先頭行を0に合わせる補正として-1が必要になります。

第3引数（列数）は0で、同じ列から切り出すことを示します。第4引数（高さ）はCOUNTIFで求めた件数です。幅（第5引数）は省略しており、基準参照と同じ1列になります。これにより、料理名列のうち「先頭位置から件数ぶん」だけの縦長の範囲参照が生成されます。

3.5 成立条件の論理：なぜ「連続性」が必要なのか

本手法の成立条件は、同一区分が連続していることです。理由は単純で、OFFSETが作る範囲は「連続した行」しか表現できないからです。XMATCHは先頭位置しか返さず、COUNTIFは全件数しか返しません。したがって、分散した区分を「飛び飛びの範囲」として切り出すことは、この設計のままではできません。

連続性が崩れると、OFFSETは「先頭位置から全件数ぶん」という連続範囲を作るため、途中にある別区分が混入します。同時に、後半に分散している同一区分が別区分の後ろにある場合は、候補の欠落や意図しないずれとして観測されます。つまり、式が正しくても、データ設計の前提が崩れると必ず壊れます。

3.6 入力規則目線の要点：配列ではなく範囲参照を返す

入力規則（リスト）は、候補を「セル範囲」として扱う仕組みです。したがって、設計判断の核心は、2段目候補を配列として計算するのではなく、OFFSETで「範囲参照」を生成して渡すことにあります。これにより、入力規則が期待する形式に合わせて候補を切り替えられます。

この設計は、入力規則の制約に合わせて「返すべきもの」を範囲参照に固定した点に価値があります。逆に、動的配列関数で候補を作っても、それを入力規則にそのまま渡せない場合があるため、入力規則の仕様に従って範囲参照で組み立てることが、実装としての安定性につながります。

4. 運用・保守・トラブル対応

この章は、二段階リスト（区分→料理名）を現場運用で壊さないための最低限のルールと、壊れたときに原因を切り分ける手順だけに絞って整理します。対象読者は、仕組みは完成したが、利用者の操作やデータ更新で壊れることを前提に保守したい実務者です。この章を読むと、守るべき設計要件を運用ルールとして明文化でき、典型的な症状から原因に最短で到達できます。

4.1 運用ルールとチェックリスト

運用ルールは、数式の品質ではなくデータ設計要件を守るための制約として定義します。最低限のチェック項目は次の3点です。

1点目は連続性の維持です。テーブル「メニュー」は常に「区分」でソートされ、同一区分が連続している状態を維持します。区分以外の列での並べ替えは、連続性を破壊する操作として原則禁止します。やむを得ず並べ替える場合は、作業後に必ず区分で再ソートします。

2点目は名称固定です。テーブル名「メニュー」と列名「区分」「料理名」を変更しません。INDIRECTは文字列参照なので、名称変更に自動追従しません。名称が変わると、入力規則の元の値が参照を解決できず、動作が停止します。

3点目は空白禁止です。区分列と料理名列の途中に空白を作らない運用にします。空白があると、候補の欠落、意図しない空欄候補の混入、ブロック判定の誤解釈が起こり得ます。特に区分列の空白は、XMATCHが一致位置を返せない原因になり、#N/Aの直接原因になります。

4.2 症状別トラブルシュート

トラブル対応は、症状から前提違反を逆引きして原因を特定します。最短ルートは次のとおりです。

混入（別区分の料理名が候補に出る）は、区分の連続性が崩れた可能性が最優先です。テーブル「メニュー」が区分でソートされ、同一区分が連続しているかを確認します。次に、同一区分が表内で分散していないかを確認します。分散していれば、COUNTIFで数えた件数ぶんを先頭から切り出すため、途中の別区分が範囲に混ざります。

空（料理名の候補が出ない、または空欄だけになる）は、I3（区分セル）の値が区分列に存在しない可能性が最優先です。入力値の表記ゆれ（全角半角、前後スペース、別表記）を確認します。次に、区分列に空白がないかを確認します。さらに、I3が空欄の場合は、COUNTIFが0となり、OFFSETが高さ0の範囲を作れないため候補が出ません。運用として、区分未選択時は料理名を選ばせない設計にします。

#N/Aが出る場合は、XMATCHが一致を見つけられていない状態です。原因は、区分セルの値が区分列に存在しない、区分列が期待している範囲を参照できていない、またはINDIRECTが参照を解決できていない、のいずれかです。まず、=XMATCH(I3,INDIRECT("メニュー[区分]"))をセル上で単独評価し、一致位置が返るかを確認します。返らない場合は、入力値と区分データの一致性が原因です。

参照崩れ（入力規則が設定できない、候補が不正、式がエラーになる）は、名称変更が最優先です。テーブル名と列名が「メニュー」「区分」「料理名」のままかを確認します。次に、入力規則の元の値に手入力した式にタイプミスがないかを確認します。INDIRECTの文字列は完全一致が必要なので、括弧や全角記号の混入も原因になります。

4.3 変更が発生したときの対応

変更対応は、影響範囲を最小化するために確認順を固定します。確認は次の順で行います。

最初に命名を確認します。テーブル名と列名が変わっていないかを確認し、変わっている場合は元の名前に戻すか、INDIRECTの文字列を変更後の名前に合わせて更新します。INDIRECTは自動追従しないため、この工程を省略すると必ず壊れます。

次に列追加の影響を確認します。区分列と料理名列が存在し、列名が一致し、データの途中に空白がないことを確認します。列追加自体は構造化参照の対象列名が維持される限り致命傷にはなりませんが、列名変更やデータ崩れが同時に起きやすい点が実務上のリスクです。

最後にシート構成変更の影響を確認します。入力セル（I3、J3）の位置変更や、参照する区分セルの移動がある場合、2段目の式の参照先が変わるため、入力規則の元の値、または名前の定義の参照式を更新します。特に名前の定義で管理している場合は、参照セルが固定（I3固定）なのか、行展開に追従させるのかを仕様として決め、変更時に一致するよう調整します。

4.4 最終チェック（運用で守るべき3点）

最終チェックは、運用で必ず守るべき点を3つに絞って確認します。

1つ目は、区分でソートされ、同一区分が連続していることです。これが崩れると混入・欠落・ずれが発生します。

2つ目は、テーブル名と列名を固定し、変更しないことです。INDIRECTが参照を解決できなくなると動作が停止します。

3つ目は、区分列と料理名列に空白を作らないことです。空白は一致失敗や候補欠落の直接原因になります。