S047｜Excel関数によるデータ抽出方法｜IF、ROW、IFERROR、INDEX、SMALL、ROW

Excel2019/2016で、条件を満たすデータを別表から抽出するなら、IF+ROW関数で該当するデータの表無いの位置を求めて、その位置をもとにIFERROR+INDEX+SMALL+ROW関数で抽出する数式を作成しましょう。

例題１｜

【数式】=IF(A11=$A$3,ROW(A10)-9,””)

1. 抽出元のF列のセルを選択し、「=IF(」と入力する。
2. ［論理式］…表の「伝票No」が伝票検索の「伝票No」である場合の条件式を入力する。
3. ［値が真の場合］…条件を満たす場合に表示する値として「ROW(A10)-9」を入力する。
4. ［値が偽の場合］…条件を満たさない場合に表示する空白［””］を入力して、「Enter」キーで数式を確定する。
5. 数式を必要な分だけ複写する。
6. データを求める抽出先のセル（A6セル）を選択し、「=IFERROR(」と入力する。
7. ［値］…INDEX関数で、表のF列に求めた表内の番目が小さい方から順番に「商品ID」を抽出する数式を入力する。
8. ［エラーの場合の値］…エラーでない場合に表示する空白［””］を入力して、「Enter」キーで数式を確定する。
9. 数式を必要な分だけ複写して、横方向の複写はINDEX関数の引数［列番号］を抽出する列番号にそれぞれ変更する。

【数式】=IFERROR(INDEX($B$11:$E$14,SMALL($F$11:$F$14,ROW(A10)-9),1),””)

1. 抽出元のF列のセルを選択し、「=IF(」と入力する。
2. ［論理式］…表の「伝票No」が伝票検索の「伝票No」である場合の条件式を入力する。
3. ［値が真の場合］…条件を満たす場合に表示する値として「ROW(A10)-9」を入力する。
4. ［値が偽の場合］…条件を満たさない場合に表示する空白［””］を入力して、「Enter」キーで数式を確定する。
5. 数式を必要な分だけ複写する。
6. データを求める抽出先のセル（A6セル）を選択し、「=IFERROR(」と入力する。
7. ［値］…INDEX関数で、表のF列に求めた表内の番目が小さい方から順番に「商品ID」を抽出する数式を入力する。
8. ［エラーの場合の値］…エラーでない場合に表示する空白［””］を入力して、「Enter」キーで数式を確定する。
9. 数式を必要な分だけ複写して、横方向の複写はINDEX関数の引数［列番号］を抽出する列番号にそれぞれ変更する。

数式の解説

「=IF(A11=$A$3,ROW(A10)-9,“”)」の数式は、表の「伝票No」（A11セル）が伝票検索の「伝票No」である場合に表内の番目を求め、違う場合は空白［””］を求める。数式をコピーすることで、伝票検索の「伝票No」に該当する表の行にだけ表内の番目が付けられる。

「SMALL($F$11:$F$14,ROW(A10)-9)」の数式は、表のF列に作成された表内の番目の小さい方から1番目の値を抽出する。INDEX関数の引数の［行番号］に組み合わせて数式を作成すると、F列の番目「1」の行にある1列目の商品IDが抽出される。数式をコピーすると、次の行にはROW関数で求められた行が「2」で指定された数式になるので、F列の行番号の小さい方から2番目の値である、番目「2」の行にある1列目の商品IDが抽出される。

こうして他の行列に数式をコピーすると、伝票Noに該当する「商品ID」～「数量」が表からすべて抽出される。

IFERROR関数の引数［値］にINDEXの数式の数式を組み合わせているため、抽出するデータがない行でもエラー値が表示されずに空白で求められる。

Excelデータダウンロード

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