関数の使い方

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

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

 

 

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

3段階のリストの概要

①絞込みリストの説明

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

①「輸入」シート

②「リスト」シート

１．使用するシート名：　輸入、リスト

２．１つ目のプルダウンリスト（輸入会社）：「A社」「B社」「C社」が選択できます。

３．２つ目のプルダウンリスト（輸入先）　：

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

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

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

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

 

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

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

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

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

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

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

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

③データの入力規則

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

 

 

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

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

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

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

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

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

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

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

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

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

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

 

 

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

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

 

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

メッセージウィンドウ

 

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

 

 

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

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

１段階目の項目名と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つ目の項目名を結合させてつけましょう。

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

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

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

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

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

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

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

４．以上で、３段階のリストを連動させるリストが完成しました！お疲れ様でした。

 

 

関数の説明

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つ目のリスト項目に重複がない場合)

RECOMMENDこちらの記事も人気です。