計算式(リスト選択(入力規則)のリスト内容の件数や対象が変わる場合の対応)

「県」を選択すると、「市」のリストは選択した県の中のリストに自動的に変化する例です。計算式の応用でこのように動的にリストの内容を変更させることができます。

こんな機能です．．．東京都の「市」のリスト

シート上に「県」と「市」のセルがあります。それぞれを選択して右端の▽マークをクリックすると、プルダウンリストが表示されて、選択入力ができます。

この画像は先に「県」のセルで「埼玉県」が選択されていて、「埼玉県」の「市」がプルダウンリストに表示され「さいたま市」を選択入力したのですが、「県」のセルを「東京都」に変更すると「市」のプルダウンリストも連動してこのように「東京都」の「市」に換わるという動作になるものです。以降でこの動作や仕組みについて説明します。

基本的な操作と動的変更の状態を説明します。

「データの入力規則」の「リスト」については「基本操作」で説明しましたが、ここでは動的にプルダウンリストの内容が変更されるという動作について説明します。
まず、基本的な操作を画像で説明します。
まず、「県」のセルを選択して右端の▽マークをクリックすると、このように関東地方の「県」のプルダウンリストが表示されます。

(画像をクリックすると、このサンプルがダウンロードできます)
プルダウンリストから所望する「県」を選択するだけで、「県」のセルへの入力が完了します。
続いて、「市」のセルを選択して右端の▽マークをクリックすると、選択入力されていた「県」に従属する「市」のプルダウンリストが表示されます。

同様にプルダウンリストから所望する「市」を選択するだけで、「市」のセルへの入力が完了します。
これだけなら「基本操作」で説明していたことでもできるような気がします。
「動的変更」と言っているのは、ここからの動作です。

一旦は「埼玉県」「さいたま市」が選択入力されていたのですが、「県」を「東京都」に変更してみます。
すると、

「市」のセルは前回「さいたま市」が選択入力されていたので「さいたま市」のままですが、「市」のプルダウンリストは「東京都」の「市」に置き換わっていて、これから選択できるのは「東京都」の「市」になるということです。

プルダウンリストの要素と呼び出し方は．．．．

このワークブックには「関東地方」というシートがあります。

このシートには、１行目に横並びに関東地方の「県」が並んでいて、

２行目以降には１行目にある「県」に従属する「市」が縦に並んで収容されています。これらがプルダウンリストに表示されている内容です。
「市」の数は「県」によって異なるので、列ごとに最終行位置も異なっています。
「関東地方」シートは説明用に表示させていますが、内容の改廃がほとんどないシートなので、シートを非表示にしておいても問題ありません。
それでは「データの入力規則」の設定を見てみましょう。まずは「県」のセルです。

プルダウンリストの要素となる「元の値」はこのように「関東地方」シートの１行目の「県名」の範囲で、セル範囲としては「横並び」になります。
特に関数などは使われておらず、固定的な内容のプルダウンリストになります。

次が「市」のセルの「データの入力規則」です。

この数式が「プルダウンリストの動的変更」を担っています。画像では右側が切れていて解りにくいので改行を入れて見やすくすると、


=IF($B$2<>""
   ,OFFSET(関東地方!$A$2
          ,0
          ,MATCH($B$2,関東地方!$A$1:$G$1,0)-1
          ,COUNTA(OFFSET(関東地方!$A$2
                        ,0
                        ,MATCH($B$2,関東地方!$A$1:$G$1,0)-1
                        ,1000
                        ,1
                        )
                 )
          ,1
          )
   ,$A$1
   )

このようになります。関数の左カッコ、各引数のカンマ、最後の右カッコを同じ桁位置にしています。
以下の関数を使用しています。

関数	概略説明
IF関数	比較判断を行ない、肯定時と否定時に分けます。引数は以下の通りです。 ① 比較判断式 ② 肯定時の値(または処置式) ③ 否定時の値(または処置式)
OFFSET関数	セル参照の位置と範囲(サイズ)を変更します。引数は以下の5個で、サイズに関わる4番目以降は省略できます。 ① 基準となるセルまたはセル範囲 ② 移動する行方向の行数で｢0｣なら行方向は移動せず、マイナスなら上へ移動します ③ 移動する列方向の列数で｢0｣なら列方向は移動せず、マイナスなら左へ移動します ④ サイズを変更する場合の行数 ⑤ サイズを変更する場合の列数
MATCH関数	検査範囲(セル範囲)から検査値を探して、その相対位置(n番目)を返します。引数は｢検査値｣｢検査範囲｣｢照合の型｣で｢照合の型｣は省略できます。｢照合の型｣で｢0｣を指定すると完全一致となり、検査範囲の並び順は問いません。
COUNTA関数	セル範囲での空白でないセルの個数を返します。引数は｢対象セル範囲｣のみ。

次から数式の構成と関数の組み合わせを説明します。

一番先頭(外側)に位置する「IF関数」は「県」のセルに入力があるか、の判定です。
入力があれば「市」のプルダウンリストの生成を行なわせ、入力がなければ「市」のプルダウンリストは空にするように動作します。
ここでは「県」のセルに不正な値があるかなどはチェックしていません。「データの入力規則」の「リスト」の設定側でリスト設定以外の値は「禁止」としているので、入力があればプルダウンリストの生成は可能だと判断できている訳です。
なお、この「IF関数」の否定側の値「$A$1」はプルダウンがある側のシートの左上角のセルで「空文字列」にあたります。
「OFFSET関数」が今回の要件でのメインとなる関数です。
プルダウンリストを生成する参照先となる「関東地方」シートのセル範囲の位置とサイズをこの関数で制御しています。
第１引数の「基準セル」は「関東地方」シートのA列２行目(「水戸市」のセル)です。これは起点として固定です。
第２、３引数が「基準セル」からの行方向、列方向の移動数です。今回は縦への移動はありませんので第２引数はゼロ固定です。
第３引数が列方向(横方向)の移動数で、ここで「MATCH関数」を呼び出して、１行目の県名を探して移動数を求めます。
第４、５引数が移動先での行方向、列方向の「サイズ」です。今回は列方向(横方向)は１列固定なので１固定です。
第４引数で選択した「県」に従属する「市」の数をセットします。先頭側の「OFFSET関数」と「COUNTA関数」に従属した方の「OFFSET関数」では、この第４引数だけが異なっており、「COUNTA関数」に従属した方の「OFFSET関数」では1000固定として、一旦大きい値でセル範囲の有効数をカウントして先頭側の「OFFSET関数」に渡すようにしています。
「MATCH関数」は都道府県による列方向の移動数を算出させています。
「関東地方」シートの１行目を横に検索し、一致する列の相対番号を取得します。
「OFFSET関数」の第３引数に渡す際は「移動数」なので、「茨城県(A列)」で一致する場合は「0」とするため1を差し引いています。
「COUNTA関数」は「市」のプルダウンリストの縦方向のサイズに当たります。
機能は「空白でないセルの個数」ですが、「関東地方」シートには途中の「抜け行」はないのでセル範囲の「行数」として問題ありません。
「OFFSET関数」に「MATCH関数」を絡めた数式のサブセットが似た内容で２つあり無駄なように見えますが、「都道府県」の列を決めてから行数範囲を算出する方法として、２回発行せざるを得ないということです。
「VLOOKUP関数」などでの単なる値検索であれば、縦方向を大きな値で範囲を広く取っておくだけでも機能しますが、このプルダウンリストの用途だと、広く取ってしまうとプルダウンリストの下にブランク行が付いて見えてしまいます。