エクセル 絞込み選択できるリストを作成(名前の定義を使用しない方法)

エクセルでは、データ入力規則で絞込み選択できるリストを作成することができます。その方法には2つの方法があります。名前の定義を使用してデータ入力規則にINDIRECT関数を使う方法と、名前の定義を使用しないで、データ入力規則にOFFSET関数とMATCH関数を使う方法があります。ここでは、名前の定義を使用しないで絞込みリストを作成する方法を紹介します。

 

 

絞込み選択できるリストを作成

 

リスト作成の概要

①絞込みリストの説明

1.2パターンの絞込みリストを作成

2.使用するシート名:

  1つ目のパターン: ワイン1、リスト1

  2つ目のパターン: ワイン2、リスト2

①リスト一覧表の項目が横方向(列)にあります。シート名を「リスト1」とします。

②リスト一覧表の項目が縦方向(行)にあります。シート名を「リスト2」とします。

 

②絞込みリストの手順

1.1つ目のプルダウンリストを作成

2.「リスト1」シートから2つ目のプルダウンリストを作成

3.「リスト2」シートから2つ目のプルダウンリストを作成

データの入力規則

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

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

1段階のプルダウンリストの作成方法は、2パターンとも同じです。

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

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

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

元の値(S)    :ワインA,ワインB,ワインC

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

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

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

 

リスト一覧表の項目が横方向(列)にある場合

「リスト1」シートから、2つ目のプルダウンリストの作成しましょう。

 

2つ目のリスト作成

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

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

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

元の値(S)    :=OFFSET(リスト1!$A$1,MATCH(A2,リスト1!$A$2:$A$4,0),1,1,4)

この式を説明しましょう。

「MATCH(A2,リスト1!$A$2:$A$4,0)」

上の式はOFFSET関数の「行」を取得しています。

OFFSET関数の幅「4」は、項目の最大の数を指定します。

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

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

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

 

リスト一覧表の項目が縦方向(行)にある場合

「リスト2」シートから、2つ目のプルダウンリストの作成しましょう。

 

2つ目のリスト作成

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

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

元の値(S)    :=OFFSET(リスト2!$A$1,1,MATCH(A2,リスト2!$B$1:$D$1,0),4,1)

この式を説明しましょう。

「MATCH(A2,リスト2!$B$1:$D$1,0)」

上の式でOFFSET関数の「列」を取得しています。

OFFSET関数の高さ「4」は、項目の最大の数を指定します。

関数の説明

OFFSET関数

セルまたはセル範囲を基準として、基準から指定された行数と列数だけシフトした位置にある、セルまたはセル範囲の参照を返します。行数と列数を指定して範囲を返すこともできます。

OFFSET関数の書式と引数

OFFSET(基準, 行数, 列数, [高さ], [幅])

基準 基準となるセルまたは隣接するセル範囲を指定します。
行数 基準の左上隅のセルを、上方向または下方向へシフトする行数を指定します。行数が正の場合、下方向へ負の場合上方向へシフトします。
列数 基準の左上隅のセルを、左方向または右方向へシフトする列数を指定します。行数が正の場合、右方向へ負の場合左方向へシフトします。
高さ 省略できます。参照の行数を指定します。
省略できます。参照の列数を指定します。

OFFSET関数の使用例

F2に式を入力します。

F2 =OFFSET(A1,2,3,1,1)

F2の式は、セル「A1」を基準として「A1」から行数「2」、列数「3」シフトしたセルの参照が返されます。

結果は、「D3」のセル「8」が返されます。

MATCH関数

指定した範囲内から検査値を探しだし、その値の相対的な位置を返します。

MATCH関数の書式と引数

MATCH(検査値, 検査範囲, [照合の型])

検査値 検索する値を指定します。
検査範囲 検索するセルの範囲を指定します。
照合の型 -1、0、1 の数値のいずれかを指定します。省略した場合は自動的に1が選択されます。1は、検査値以下の最大の値を返します。この場合、検査範囲を昇順で並べ替えする必要があります。0は、検査値と一致する一番最初の値を返します。行の場合は左から、列の場合は上から最初の値を返します。-1は、検査値以上の最小の値を返します。この場合、検査範囲を降順に並べ替えする必要があります。

MATCH関数の使用例

F2に式を入力します。セル範囲「A2:A4」から、請求会社「マツダテック」の位置を求めます。

F2 =MATCH(D2,A2:A4,0)

セル範囲「A2:A4」で「マツダテック」は3番目です。

結果は「3」を返します。

名前の定義を使用する場合はINDIRECT関数を使用

「名前の定義」を使用して絞込み選択できるリストを作成する場合は、INDIRECT関数を使用します。今回は「名前の定義」を使用しない方法を紹介しましたが、OFFSET関数とMATCH関数を組み合わせる式が難しい、複雑と思う場合は名前の定義を使用した方がよいでしょう。

こちらの記事もご覧ください。⇒エクセル 絞込み選択できるリストを作成する方法!INDIRECT関数を使用