FILTERが使えないExcelでは、条件に一致した行の位置を補助列へ記録し、SMALLで1番目・2番目・3番目の位置を取り出します。その位置をINDEXへ渡すと、該当する明細を順番に表示できます。
無料Excelサンプルで一緒に試す
A3の伝票Noに一致する明細をA6:D8へ表示する例題です。元表はA11:E14、位置番号の補助列はF列です。
Microsoft 365で利用できます。Excel 2019・2016向けの操作も本文で説明しています。
補助列へ一致行の位置を書く
A3に検索する伝票Noを入力します。F11に次の式を入れ、F14までコピーします。
=IF(A11=$A$3,ROW()-ROW($A$11)+1,"")一致した行には元表内の位置1～4、不一致には空文字列が入ります。元のサンプルのROW(A10)-9も先頭行で1を返しますが、開始セルを使って差を取ると表を移したときの意味を読み取りやすくなります。
1番目の一致を取得する
A6に次の式を入力します。
=IFERROR(INDEX($B$11:$E$14,SMALL($F$11:$F$14,ROWS($A$6:A6)),COLUMNS($A$6:A6)),"")SMALLの第2引数は1から始まり、INDEXの列番号も1です。そのため、最初の一致行の商品IDが返ります。A3が1000ならA001が最初の結果です。
右と下にコピーする
A6をD6まで右へコピーし、必要な件数分だけ下へコピーします。右方向ではCOLUMNSが1・2・3・4、下方向ではROWSが1・2・3と変わります。
元表にぶつからない空き領域へ出力してください。例題では3件の結果がA6:D8に収まりますが、結果が増える実務表では抽出先を別シートにする方法が安全です。
該当件数を超えると空白になる
SMALLは実際の数値の個数を超えると#NUM!になります。この式ではIFERRORで空白へ置き換えています。ただしIFERRORは他のエラーも隠すため、作成直後は一時的に外して参照範囲が正しいか確認すると原因を調べやすくなります。
2条件に増やすのは補助列側
伝票NoがA3、種類がB3という2条件なら、F11を次のようにします。
=IF((A11=$A$3)*(D11=$B$3),ROW()-ROW($A$11)+1,"")抽出側のINDEX＋SMALLはそのまま使えます。条件判定と抽出を分けるので、どの行が対象かをF列で目視確認できます。この補助列方式の式は通常のEnterで確定できます。
Microsoft 365ならFILTERも選べる
=FILTER(B11:E14,A11:A14=A3,"該当なし")FILTERは結果の件数に応じて自動展開します。INDEX＋SMALL方式は出力先の式を必要な行までコピーする点が異なります。古いExcelを使う相手と共有する場合は、対応版を確認して方法を選んでください。
無料Excelサンプルで復習する
A3の伝票Noに一致する明細をA6:D8へ表示する例題です。元表はA11:E14、位置番号の補助列はF列です。
Microsoft 365で利用できます。Excel 2019・2016向けの操作も本文で説明しています。
コメント
コメント一覧 （1件）
[…] INDEX＋SMALLで複数一致を順に抽出｜Excel 2019・2016の補助列方式 […]