エクセル 3段階のリストを連動させる方法(2つ目のリスト項目に重複がある場合)

エクセル、プルダウンリストが3段階に分かれているとします。2つ目のリスト項目に、重複がある場合の 3段階のリストを連動させる方法を紹介します。1つ目のリスト項目は全て違いますが、2つ目のリスト項目が重複しているとき、2つ目のリスト項目に重複がある場合と、重複が無い場合とでは、違う方法で3段階の絞込リストを作成できます。

 

 

連動した3段階のリストを作成

3段階のリストの概要

①絞込みリストの説明

下表を使って説明していきます。

①「輸入」シート

②「リスト」シート

1.使用するシート名: 輸入、リスト

2.1つ目のプルダウンリスト(輸入会社):「A社」「B社」「C社」が選択できます。

3.2つ目のプルダウンリスト(輸入先) :

  A社→アメリカ、カナダ、イタリア

  B社→メキシコ、アメリカ、インド

  C社→カナダ、フィリピン、アメリカ

ここで紹介する 3段階のリストを連動させる方法は、1段階に連動させる2段階のリスト「輸入先」の項目に、重複がある場合です。

 

②絞込みリスト作成の手順

1.「リスト」シートに、3段階のプルダウンリストの基本となるリストを作成

2.データの入力規則で1段階のプルダウンリストを作成する

3.2つ目の絞込み選択リストの範囲に名前をつける

4.2つ目の絞込み選択リストを作成する

5.3つ目の絞込み選択リストの範囲に名前をつける

6.3つ目の絞込み選択リストを作成する

③データの入力規則

「データ」タブ→「データツール」グループ→「データの入力規則」

 

 

3段階のプルダウンリストの基本となるリストを作成

「リスト」シートに、次のようなリストを作成します。

リストを作成したら、次に1段階のプルダウンリストを作成しましょう。

1段階のプルダウンリストを作成

1段階のプルダウンリストを作成します。

1.「A2」のセルを選択して、データの入力規則をクリックします。

データの入力規則に、次のように入力しましょう。

入力値の種類(A) :リスト

元の値(S)    :A社,B社,C社

2.入力が終わったら「OK」ボタンをクリックします。

3.「A2」をプルダウンリストを作成したい行までコピーします。コピーした行までプルダウンリストが作成されます。リストを作成した範囲を選択してから、データの入力規則をクリックして、設定してもいいです。

2つ目のリストの範囲に名前をつける

2つ目の絞込み選択リストの範囲に、「名前の定義」で名前をつけていきましょう。

名前の定義

「数式」タブ→「定義された名前」グループ→「名前の定義」

 

1.2つ目のリストの範囲に「リスト」シートから名前をつけていきます。

 

2.「名前の定義」をクリックします。

「新しい名前」ダイアログボックスに次のように入力しましょう。

名前(N)   : A社

参照範囲(R) : =リスト!$B$2:$D$2

参照範囲(R)ボックスで「B2:D2」の範囲を選択すると自動で範囲が入力されます。

または、「B2:D2」の範囲を選択してから「名前の定義」をクリックすると、自動で範囲が入力されます。

同様に、他2つの項目にも名前をつけていきましょう。

名前(N):B社  参照範囲(R):=リスト!$F$2:$H$2

名前(N):C社  参照範囲(R):=リスト!$J$2:$L$2

名前は必ず、2つ目のリスト項目が入っている1段階の項目名をつけましょう。

 

 

名前ボックスから名前をつける方法

数式バーの左にある「名前ボックス」に、直接「名前」を入力しても、名前を定義できます。

例えば、セル範囲「B2:D2」に「名前ボックス」から名前をつけてみましょう。

1.セル範囲「B2:D2」を選択します。

2.名前ボックスに「A社」と入力してEnterキーで確定します。

3.セル範囲「B2:D2」を選択すると名前ボックスに「A社」と表示されるのを確認しましょう。

こちらの記事もご覧ください。⇒エクセル ドロップダウンリストを作成する方法

データの入力規則で2段階のプルダウンリストを作成

1.「B2」のセルを選択して、データの入力規則をクリックします。

データの入力規則に、次のように入力しましょう。

入力値の種類(A) :リスト

元の値(S)    :=INDIRECT($A2)

2.入力が終わったら「OK」ボタンをクリックします。

入力後「OK」で確定した時、次のようなメッセージが表示されたら、「はい」をクリックします。

メッセージウィンドウ

 

3.「B2」をプルダウンリストを作成したい行までコピーします。コピーした行までプルダウンリストが作成されます。リストを作成した範囲を選択してから、データの入力規則をクリックして、設定してもいいです。

 

 

3つ目のリストの範囲に名前をつける

3つ目の絞込み選択リストの範囲に、名前をつけていきましょう。

1段階目の項目名と2つ目の項目名を結合させた名前をつける

1.3つ目のリストの範囲に「リスト」シートから名前をつけていきます。

2.「名前の定義」をクリックします。または、名前ボックスからセルの範囲を選択して、名前をつけていきます。

2つ目の絞込み選択リストの範囲に、「名前の定義」から名前をつけた要領と同じです。

次のように名前をつけていきましょう。

セル範囲「B3:B5」:「A社アメリカ」 「C3:C5」:「A社カナダ」 「D3:D5」:「A社イタリア」

セル範囲「F3:F5」:「B社メキシコ」 「G3:G5」:「B社アメリカ」 「H3:H5」:「B社インド」

セル範囲「J3:J5」:「C社カナダ」 「K3:K5」:「C社フィリピン」 「L3:L5」:「C社アメリカ」

名前は必ず、1段階目と連動する2つ目の項目名を結合させてつけましょう。

データの入力規則で3段階のプルダウンリストを作成

1.「C2」のセルを選択して、データの入力規則をクリックします。

データの入力規則に、次のように入力しましょう。

入力値の種類(A) :リスト

元の値(S)    :=INDIRECT($A2&$B2)

2.入力が終わったら「OK」ボタンをクリックします。

3.「B2」をプルダウンリストを作成したい行までコピーします。コピーした行までプルダウンリストが作成されます。

4.以上で、3段階のリストを連動させるリストが完成しました!お疲れ様でした。

 

 

関数の説明

INDIRECT関数

指定した文字列が表す参照を返します。

INDIRECT関数の書式

INDIRECT(参照文字列, [参照形式])

参照文字列:セルの参照を表す文字列を指定します。

INDIRECT関数の使用例

説明
C1 =INDIRECT(A3)   参照文字列にA3を指定します。A3のセルはB3です。B3を参照します。「10」を返します。
C2 =INDIRECT(“B”&B2) 参照文字列に”B”&B2を指定します。B2は「1」です。B&1でB1を参照します。「3」を返します。

エクセル、3段階のリストを連動させる方法は他にもあります

3段階のリストを連動させる方法は、他にもあります。2つ目のリスト項目に重複がない場合です。セル範囲につける名前が違いってきます。それによって「データの入力規則」に設定するINDIRECT関数の式も違ってきます。ここで紹介した方法より簡略化できます。2つ目のリスト項目に重複がない場合この方法をお試しください。

こちらの記事もご覧ください。⇒エクセル 3段階のリストを連動させる方法(2つ目のリスト項目に重複がない場合)