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

• 「入力規則」の「リスト」については、「基本操作」で説明しましたが、動的にプルダウンリストの内容を変更する方法をご紹介します。 計算式との組み合わせでこのような機能が得られます。
  なお、ここでは列を列番号(数値)で参照する必要から、「R1C1参照形式」を使用します。
  ※作成が完了してからは「A1参照形式」に戻しても構いません。
  
  (画像をクリックすると、このサンプルがダウンロードできます)
  「関東地方」のシートには１行目に関東地方の県名、２行目以降にはその県の市名がリスト化されています。
• ここで「Sheet1」の方に「県」と「市」の入力項目を作成し、「入力規則」で「リスト」を設定します。
  
  「データ」メニューの「入力規則」の「入力値の種類」で「リスト」を選択し、リストにしたい参照範囲を「元の値」に登録します。
  
  この参照範囲「関東地方!R1C1:R1C7」は入力規則に直接入力してしまう方法の他、セルに持たせておいて参照させることもできます。
  これが参照範囲を「可変」にするための方法でもあります。
  その方法とは、
  
  「Sheet1」のD2セル(今回は列を数字で参照するのでR1C1参照形式にしているのでR2C4)に、 「関東地方!R1C1:R1C7」という「関東地方」の県名のセル範囲の参照名をセットしてあるので、 参照名の文字列から実際にその範囲を参照するINDIRECT関数の式を「入力規則」の「元の値」にセットするだけです。 これで県名のプルダウンリストは機能します。
  このサンプルでは「県」のリストは固定ですから「関東地方」シートの県名リストを直接登録しても問題ありませんが、ここでの方法であれば「関東地方」シートの県名リストとの同期が明確になります。
  
• 問題は、市の「リスト」の処理ですが、「入力規則」の「リスト」の「元の値」への設定は県のそれと同様で、「Sheet1」上のD4(R4C4)セルをINDIRECT関数で参照するだけです。
  
  市の「リスト」は「関東地方」シートでは、県ごとに列が分かれています。そこで、選択した県により市のリストの範囲を書き換えてしまおうというのです。
• まず、D5セル(R5C4)に次の計算式をセットし、県のリストで指した時の列番号を判定します。
  
  「6」となるので「関東地方」シートの「東京都」の列であることが確認できます。 ユーザーフォームのコンボボックスであればプルダウンリストの「何番目を選択したのか」がインデックス値(位置を表わす数値)で取り出せるのですが、 セルの入力規則では選択値(文字列：県名)しか取り出せないのでMATCH関数で存在位置を取り出して列番号として利用しています。
  なお、「関東地方」シートの「県」リストのセル範囲がINDIRECT関数のままになっていますが、 セル上の計算式では入力規則のように他シートへの参照ができないわけではないので、
  

  
  =IF(R2C2<>"",MATCH(R2C2,関東地方!R1C1:R1C7,0),0)
  

  としてやる方が良いかも知れません。
  
• 次にD4セル(R4C4)に次の式をセットし、市の参照範囲を完成させます。
  
  県が選択されていない場合エラーにならないようにIF関数で判断の上、D5セルに算出してある列番号を組み付けて参照範囲の式を「文字列」で作成します。この時、県によって市の数が違うわけですから、COUNTA関数で列ごとの市の数をカウントしています。
  この例では「東京都」を選択していますが、「東京都」は「関東地方」シートの６番目の列にあります。この式には参照範囲の「C」の後ろの２カ所とCOUNTA関数の計算範囲の「C」の後ろの２カ所で合計４カ所に「東京都」の列番号である「６」を表わさなければならず、D5セル(R5C4)の計算式をこのように別セルで計算しておいた方が効率的でしかも判りやすい(間違いにくい)です。
  もう一つポイントは、COUNTA関数の参照範囲を１行目(県名)にしてしまうことです。これは市の件数には１件多くなってしまいますが、実際欲しいのは参照範囲の最終行なので、このようにすれば後で「＋１」する必要がありません。
  これで、B4セルの「入力規則」に「リスト」で県と同じようにINDIRECT関数でD4セル(R4C4)を参照させれば完成です。
  このように中間計算結果を置くセルの列を「作業列」と呼びます。「作業列」は実際の運用では見えなくても良いので一般的には非表示とします。
  今回は参照セル範囲(列方向を含め)を動的に変更させるので「R1C1参照」形式で表示させていますが、 計算式の投入完了後は通常の「A1参照」形式に戻して構いません。
  
  

「作業列」を使わないサンプル(Excel2007(*.xlsx)以降)

上のサンプルでは可変セル参照式を視覚的に説明するために「作業列」を利用しましたが、「作業列」を使わないサンプルも作ってみようと思います。 この方法では「INDIRECT関数」も使用しません。

このように、従来のD列の中間計算セルは全くありません。
「県」の「入力規則」の「リスト」では先に説明したように上から３番目の画像のように計算式を設定すればよく、D2セルの計算式は不要になります。

ですが、D4セル、D5セルの計算式を排除するとなると「市」の「入力規則」の「リスト」の計算式はやや複雑になります。 「県」によって参照列が変異する他、県によって市の数が異なるため、参照する行数も変異させなければなりません。

式が長く画像には収まりません。整理して改行をつけて表現すると、

=IF(R2C2<>""
   ,OFFSET(関東地方!R2C1
          ,0
          ,MATCH(R2C2,関東地方!R1C1:R1C7,0)-1
          ,COUNTA(OFFSET(関東地方!R2C1
                        ,0
                        ,MATCH(R2C2,関東地方!R1C1:R1C7,0)-1
                        ,1000
                        ,1
                        )
                 )
          ,1
          )
   ,R1C1
   )

このようになります。
さて、先頭のIF関数から最深のMATCH関数まで最大で５層のネストになってしまうわけですが、これにより、今回の目的は実現できました。

普段あまり使うことがない「OFFSET関数」が２回も登場します。 実は「OFFSET関数」は参照するセル範囲を動的に変異させる時に「行列位置」と「セル範囲の大きさ」一度に変えることができる非常に便利な関数なのです。
「OFFSET関数」の引数は５つあり、以下のようになります。
①起点セル（参照範囲の左上セル）
②行方向オフセット（ズレ）量
③列方向オフセット（ズレ）量
④行方向の高さ（行数）
⑤列方向の幅（列数）
※②、③は起点セルからのズレがない時はゼロになります。
２回登場する「OFFSET関数」の後（下）方のものは選択した「県」の中の「市」の行数をカウントさせるためのセル範囲の設定ですが、 起点セルは２回の「OFFSET関数」で同じで「関東地方!R2C1」です。また、行方向オフセットはともにゼロです。
列方向オフセットはともに、

MATCH(R2C2,関東地方!R1C1:R1C7,0)-1

で「関東地方」シートの１行目で「県」を探すMATCH関数から１を差し引いています。１を差し引くのはゼロ起算だからです。
行方向の高さは「1000」としており1000行分のセル範囲をCOUNTA関数に渡して実際のデータ個数をカウントさせています。

そして、このCOUNTA関数の結果が先（上）方の「OFFSET関数」の行方向の高さとなって、入力規則の「リスト」の行数として正しく現われるようになっています。