
スプレッドシートやExcelで入力の省力化と間違いを防止するために入力規則を設定してプルダウンから選択させることは良く使われる手法です。
プルダウンが1つだけではなくて、上のシートのように2つ設定する場合もあります。上記の場合だと入力作業者の手間を考えて最初の「メーカー」を選択すると「車種」では選択したメーカーのみの車種が表示されてその中から選択させるようにしたいと考えます。
ExcelだとINDIRECT関数を使うことにより比較的簡単に実現することができるのですがスプレッドシートでは同じ手が使えません。
今回はスプレッドシートで複数のプルダウンリストを連動させる方法について解説します。
プルダウン表示のデータ準備
今回は都道府県を選択すると対応する市区町村が連動するようにします。
準備として、総務省の「全国地方公共団体コード」より「都道府県コード及び市区町村コード」のExcelファイルをダウンロードします。

「都道府県コード及び市区町村コード」シートを追加して左に都道府県、右に市区町村のデータを貼り付けました。都道府県は「市区町村名」が空白の行をフィルタ、市区町村は「市区町村名」が空白以外の行をフィルタして抽出しています。
入力シートの準備
実際にプルダウンを利用して入力していくシートを作成します。

今回は想定として会社名と会社が所在する都道府県と市区町村を入力するということとしました。
1番目のプルダウンとなる「都道府県」はデータの入力規則で「プルダウン(範囲内)」を選択して「都道府県コード及び市区町村コード」シートのB2:B48を選択します。これで47都道府県がプルダウンリストに表示されるようになります。
作業シートの作成
1番目のプルダウンで選択した都道府県に対応する市区町村のみ表示させるための「作業シート」を作成します。

『A2』セルに以下の数式を入力します。
=TRANSPOSE(QUERY('都道府県コード及び市区町村コード'!G:H,"select H where G='"&'入力シート1'!B2&"'"))
数式の意図を解説していきます。
QUERY('都道府県コード及び市区町村コード'!G:H,"select H where G='"&'入力シート1'!B2&"'")
このQUERYは入力シートの『B2』セルで指定された都道府県に対応する市区町村だけを抽出する式です。
このままだと縦に抽出された市区町村が表示されますので、横に表示されるようにTRANSPOSE関数を使っています。
ここで「入力シート」の『B2』セルに「北海道」を入れてみましょう。

北海道に対応する市区町村が表示されます。北海道以外を入力して市区町村が変更されることを確認してみてください。
つまり、2番目のプルダウンに表示するデータとしてこの数式の出力結果を利用することになります。
「入力シート」の各行に対応して用意する必要がありますので、『A2』セルのフィルハンドルを必要な行数分ドラッグします。
ドラッグすることで下記の赤字部分が行に対応した数字に連続的に変化します。
QUERY('都道府県コード及び市区町村コード'!G:H,"select H where G='"&'入力シート1'!B2&"'")
入力シートの2番目のプルダウン連動設定

2番目のプルダウンリストを設定する範囲を選択します。次にデータの入力規則で「プルダウン(範囲内)」を選択して「作業シート」のA2:GC2を選択します。右側の列をどこまで選択するかは都道府県で一番市区町村が多い北海道(185の市区町村が存在します)を基準にします。他のケースでも一番多いケースを基準にすれば良いです。「完了」をクリックします。
次に『C2』セルを選択します。データの入力規則でさきほど追加したルールをクリックして編集モードにします。

上の図にある「条件」の範囲が絶対参照になっているのを[$]を削除して相対参照にします。編集が終わったら「完了」をクリックします。
上記の編集を行うことによりプルダウンリストに表示する範囲が行に対応した「作業シート」の行を参照するようになります。
コメント