s027｜どの列を検索対象にしても抽出する方法【FILTER関数、XLOOKUP関数、INDEX関数、SUMPRODUCT関数、ROW関数、COLUMN関数】｜Excel関数によるデータ抽出方法

検索値に電話番号を入力しても、名前を入力しても、該当するデータを表から抽出したい。そんな時は、検索値があるデータの列を抽出してから、検索値と一致する行のデータを抽出する数式を作成して求めてみましょう。

例題１｜抽出範囲から抽出する

1. 求めるセル（A6セル）を選択し、「=FILTER(」と入力する。
2. ［配列］…抽出する表のセル範囲を選択する。
3. ［含む］…XLOOKUP関数でA2セルの検索する列見出しがある列から抽出したデータがA3セルの検索値である場合の条件式を入力する。
4. ［空の場合］…［””］（空白）を入力して、「Enter」キーで数式を確定する。

【数式】＝FILTER(A9:D12,XLOOKUP(A2,A8:D8,A9:D12)=A3,””)

数式の解説

「=FILTER(A9:D12,XLOOKUP(A2,A8:D8,A9:D12)=A3,“”)」の数式は、検索するA2セルの「種類」と一致した列にあるデータがA3セルの「レモングラス」である場合の条件を満たす、A9セル～D12セルのデータを抽出する。結果、常にA2セルの列見出しのA3セルの値に該当するデータが抽出される。

Excelデータダウンロード

以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample1.xlsx  

例題２｜抽出範囲から抽出する

1. 求めるセル（A6セル）を選択し、「=INDEX(」と入力する。
2. ［参照］…抽出するセル範囲を選択して、「F4」キーを1回押して「$」記号をつけ絶対参照にする。
3. ［行番号］…SUMPRODUCT関数とROW関数でA3セルの検索値が表の何行目にあるかを求める数式を入力する。
4. ［列番号］…「COLUMN(A2)」と入力して、「Enter」キーで数式を確定する。
5. 数式を必要な分だけ複写する。

【数式】=INDEX($A$9:$D$12,SUMPRODUCT(($A$9:$D$12=$A$3)*ROW($A$1:$A$4)),COLUMN(A2))

数式の解説

「SUMPRODUCT(($A$9:$D$12=$A$3)*ROW($A$1:$A$4))」の数式は、表のデータがA3セルの「レモングラス」である場合に表内の行の番目が求められる。INDEX関数の引数［行番号］に組み合わせて数式を作成することで、種類「レモングラス」1列目の商品IDが抽出される。数式をコピーすることで、抽出する列番号が「2」「3」…と指定されるので、種類「レモングラス」に該当するデータが抽出される。結果、常にA3セルの値に該当するデータが抽出される。

Excelデータダウンロード

以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample2.xlsx