エクセルでは、データ入力規則で絞込み選択できるリストを作成することができます。その方法には2つの方法があります。名前の定義を使用してデータ入力規則にINDIRECT関数を使う方法と、名前の定義を使用しないで、データ入力規則にOFFSET関数とMATCH関数を使う方法があります。ここでは、名前の定義を使用しないで絞込みリストを作成する方法を紹介します。
目次
リスト作成の概要
①絞込みリストの説明
１．２パターンの絞込みリストを作成
２．使用するシート名：
１つ目のパターン： ワイン1、リスト1
２つ目のパターン： ワイン2、リスト2
①リスト一覧表の項目が横方向(列)にあります。シート名を「リスト1」とします。
②リスト一覧表の項目が縦方向(行)にあります。シート名を「リスト2」とします。
②絞込みリストの手順
１．１つ目のプルダウンリストを作成
２．「リスト1」シートから２つ目のプルダウンリストを作成
３．「リスト2」シートから２つ目のプルダウンリストを作成
③データの入力規則
「データ」タブ→「データツール」グループ→「データの入力規則」
1段階のプルダウンリストを作成
１段階のプルダウンリストの作成方法は、２パターンとも同じです。
１．「A2」のセルを選択して、データの入力規則をクリックします。
データの入力規則に、次のように入力しましょう。
入力値の種類(A) ：リスト
元の値(S) ：ワインA,ワインB,ワインC
２．入力が終わったら「OK」ボタンをクリックします。
３.「A2」をプルダウンリストを作成したい行までコピーします。コピーした行までプルダウンリストが作成されます。リストを作成した範囲を選択してから、データの入力規則をクリックして、設定してもいいです。
リスト一覧表の項目が横方向(列)にある場合
「リスト1」シートから、2つ目のプルダウンリストの作成しましょう。
こちらの記事もご覧ください。⇒エクセル 3段階のリストを連動させる方法(2つ目のリスト項目に重複がない場合)
２つ目のリスト作成
１．「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」は、項目の最大の数を指定します。
２．入力が終わったら「OK」ボタンをクリックします。
３.「A2」をプルダウンリストを作成したい行までコピーします。
リスト一覧表の項目が縦方向(行)にある場合
「リスト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関数を使用
