FILTERが使えないExcelでは、条件に一致した行の位置を補助列へ記録し、SMALLで1番目・2番目・3番目の位置を取り出します。その位置をINDEXへ渡すと、該当する明細を順番に表示できます。
練習用Excelファイル（無料ダウンロード）
A3の伝票Noに一致する明細をA6:D8へ表示する例題です。元表はA11:E14、位置番号の補助列はF列です。Microsoft 365で利用できます。Excel 2019・2016向けの操作も本文で説明しています。
Excelファイル（.xlsx）／登録不要／数式・練習データ入り
目次
補助列へ一致行の位置を書く
A3に検索する伝票Noを入力します。F11に次の式を入れ、F14までコピーします。
ポイント一致した行には元表内の位置1～4、不一致には空文字列が入ります。元のサンプルのROW(A10)-9も先頭行で1を返しますが、開始セルを使って差を取ると表を移したときの意味を読み取りやすくなります。
まず動かしてみよう｜この記事のサンプルをその場で試せます
練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。
1番目の一致を取得する
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を次のようにします。
ポイント抽出側のINDEX＋SMALLはそのまま使えます。条件判定と抽出を分けるので、どの行が対象かをF列で目視確認できます。この補助列方式の式は通常のEnterで確定できます。
Microsoft 365ならFILTERも選べる
ポイントFILTERは結果の件数に応じて自動展開します。INDEX＋SMALL方式は出力先の式を必要な行までコピーする点が異なります。古いExcelを使う相手と共有する場合は、対応版を確認して方法を選んでください。
練習用Excelファイル（無料ダウンロード）
A3の伝票Noに一致する明細をA6:D8へ表示する例題です。元表はA11:E14、位置番号の補助列はF列です。Microsoft 365で利用できます。Excel 2019・2016向けの操作も本文で説明しています。
Excelファイル（.xlsx）／登録不要／数式・練習データ入り
次に読むと理解が深まる記事
- FILTER関数で3つ以上の複数条件を指定｜AND・ORと全件抽出
- 条件リストに一致する行を抽出｜FILTER＋MATCHで複数候補を検索
- Excelの#SPILL!（スピル）エラーを直す方法｜FILTERで原因を確認
参考：Microsoft公式ドキュメント INDEX ／ SMALL ／ IFERROR
INDEX＋SMALLのよくある質問
=IFERROR(INDEX(…),””) のようにIFERRORで囲むのはなぜですか？
該当件数を超えた行で、SMALLが #NUM! になるからです。IFERRORで空白へ置き換えています。ただしIFERRORは他のエラーも隠すため、作成直後は一時的に外して、参照範囲が正しいか確認します。
FILTERが使えない古いバージョンのExcelで、一致した行を上から順番に抽出するには？
補助列に一致行の位置を書き、SMALLで小さい順に取り出してINDEXへ渡します。補助列は =IF(A11=$A$3,ROW()-ROW($A$11)+1,"") です。この補助列方式の式は、通常のEnterで確定できます。
コメント