連動するドロップダウンリストを数式を使って作成する



連動するドロップダウンリストを数式を使って作成する       Topへ

  1. リスト表示するデータをテーブルで作成します。ここでは、Sheet2に入力することにします。
    A列には 最初に選択するリストを入力しています。具体的には地方名を入力しています。
    B列以降には各地方に属する都道府県名を入力しています。
  2. 入力規則のリストでドロップダウンリストを作成します。
    Sheet1のB3セルを選択します。
  3. [データ]タブの[データの入力規則]を選択します。
  4. データの入力規則で[設定]タブを選択します。
    入力値の種類で「リスト」を選択します。
    元の値には =OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A2:$A100),1) と数式を入力します。
  5. B3セルに入力規則のリストが設定できました。
  6. 2つ目の連動するリストを作成します。
    C3セルを選択します。
    [データ]タブの[データの入力規則]を選択します。
    データの入力規則で[設定]タブを選択します。
    入力値の種類で「リスト」を選択します。
    元の値には =OFFSET(Sheet2!$A$2,0,MATCH(B3,Sheet2!$B$1:$I$1,0),COUNTA(OFFSET(Sheet2!$A$2,0,MATCH(B3,Sheet2!$B$1:$I$1,0), 20,1)),1) と数式を入力します。
    MATCH関数の詳細な使い方は Excel(エクセル)基本講座:MATCH関数の使い方 をご覧ください。
  7. C3セルにB2セルの値に連動した入力規則のリストが設定できました。
  8. リストが自動で伸びるのを確認します。
    中国地方にデータを追加しました。
  9. 入力規則のリストにデータが追加されたのが確認できました。
    ドロップダウンリストがデータの増減に対応して変化します。

データ数の計算をシートで行って、数式をちょっと簡単にする

  1. COUNTAの部分がわからない、わかりにくい場合はシート内でデータ数を計算します。
    Sheet2の1行目に =COUNTA(A3:A20) としてデータ数を計算します。
  2. すると、B3セルの入力規則の元の値は =OFFSET(Sheet2!$A$3,0,0,COUNTA(Sheet2!$A3:$A101),1) となります。
  3. C3セルの入力規則の 元の値には =OFFSET(Sheet2!$A$3,0,MATCH(B3,Sheet2!$B$2:$I$2,0),INDEX(Sheet2!B1:I1,MATCH(B3,Sheet2!B2:I2,0)),1) と入力します。
    変わったのは、データ数をSheet2の1行目から探し出しているところです。INDEX(Sheet2!B1:I1,MATCH(B3,Sheet2!B2:I2,0)) としています。
HomeExcelの基本操作の目次|連動するドロップダウンリストを数式を使って作成する