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

• 「入力規則」の「リスト」については、「基本操作」で説明しましたが、動的にプルダウンリストの内容を変更する方法をご紹介します。計算式との組み合わせでこのような機能が得られます。
  なお、ここでは列を数字で参照する必要から、「R1C1参照形式」を使用します。
  
  「関東地方」のシートには１行目に関東地方の県名、２行目以降にはその県の市名がリスト化されています。
• ここで「Sheet1」の方に「県」と「市」の入力項目を作成し、「入力規則」で「リスト」を設定します。
  
  「データ」メニューの「入力規則」の「入力値の種類」で「リスト」を選択し、リストにしたい参照範囲を「元の値」に登録します。
  
  しかし、「関東地方」シートの参照範囲を直接式を入力すると、
  
  このようなエラーになります。
  しかし、これは「入力規則」の「リスト」に設定する時の制限のようです。
• 「入力規則」の「リスト」の参照先だけを自分のシート(この場合は「Sheet1」)にしておいて,その参照先に他のシートのセル範囲を設定しておくと、結果的に他シートを参照することができます。その方法とは、
  
  「Sheet1」のD2セル(今回は列を数字で参照するのでR1C1形式にしているのでR2C4)に、「関東地方!R1C1:R1C7」という「関東地方」の県名のセル範囲の参照名をセットしてあるので、参照名の文字列から実際にその範囲を参照するINDIRECT関数の式を「入力規則」の「元の値」にセットするだけです。
  これで県名のプルダウンリストは機能します。
• 問題は、市の「リスト」の処理ですが、「入力規則」の「リスト」の「元の値」への設定は県のそれと同様で、「Sheet1」上のD4(R4C4)セルをINDIRECT関数で参照するだけです。
  
  (↑上の画像をクリックすると実際のExcelシートが開きます)
  市の「リスト」は「関東地方」シートでは、県ごとに列が分かれています。そこで、選択した県により市のリストの範囲を書き換えてしまおうというのです。
• まず、D5セル(R5C4)に次の計算式をセットし、県のリストで指した時の列番号を判定します。
  
• 次にD4セル(R4C4)に次の式をセットし、市の参照範囲を完成させます。
  
  県が選択されていない場合エラーにならないようにIF関数で判断の上、D5セルに算出してある列番号を組み付けて参照範囲の式を「文字列」で作成します。この時、県によって市の数が違うわけですから、COUNTA関数で列ごとの市の数をカウントしています。
  この例では「東京都」を選択していますが、「東京都」は「関東地方」シートの６番目の列にあります。この式には参照範囲の「C」の後ろの２カ所とCOUNTA関数の計算範囲の「C」の後ろの２カ所で合計４カ所に「東京都」の列番号である「６」を表わさなければならず、D5セル(R5C4)の計算式をこのように別セルで計算しておいた方が効率的でしかも判りやすい(間違いにくい)です。
  もう一つポイントは、COUNTA関数の参照範囲を１行目(県名)にしてしまうことです。これは市の件数には１件多くなってしまいますが、実際欲しいのは参照範囲の最終行なので、このようにすれば後で「＋１」する必要がありません。
  これで、B4セルの「入力規則」に「リスト」で県と同じようにINDIRECT関数でD4セル(R4C4)を参照させれば完成です。