Excelのクロス抽出は、行見出しと列見出しが交差するセルを取り出す方法です。商品と倉庫で在庫数を調べる場合などに使います。XLOOKUPの入れ子、またはINDEX＋MATCHで求められます。
練習用Excelファイル（無料ダウンロード）
XLOOKUP・INDEXとMATCH・複数条件の3シート入り。複数条件シートの「カモミール／2100／倉庫B」は21です。黄色い検索条件を変更して試せます。ボタンをクリック／タップして保存し、Excelで開いてください。
Excelファイル（.xlsx）／登録不要／数式・練習データ入り
目次
商品×倉庫のクロス表から数量を取り出す
サンプルではA3に「倉庫B」、B3に「ペパーミント」を指定し、C3に数量40を表示します。B5:D5が倉庫の見出し、A6:A9が商品名、B6:D9が数量です。
まず動かしてみよう｜この記事のサンプルをその場で試せます
練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。
XLOOKUPでクロス抽出する数式
C3に次の式を入力します。
内側はペパーミントの行を取り出し、外側はその行から倉庫Bの列を選びます。数量の戻り範囲には見出しを含めません。数式を下へコピーしても元の表が動かないよう、検索範囲に $ を付けています。
INDEX＋MATCHで同じ結果を求める
ポイント1つ目のMATCHは商品が何行目か、2つ目は倉庫が何列目かを調べます。両方の末尾を0にして完全一致にします。INDEXの範囲は数量だけなので、行番号・列番号はその範囲を基準に数えます。
対応バージョンXLOOKUPはMicrosoft 365・Excel 2024・2021、INDEX＋MATCHはExcel 2019・2016でも利用できます。
練習用Excelファイル（無料ダウンロード）
XLOOKUP・INDEX＋MATCH・複数条件の3シートが入っています倉庫と商品を書き換えて、交点の数量が変わるのを確認できます。商品名・単価・倉庫の複数条件のシートも入っています。
Excelファイル（.xlsx）／登録不要／数式・練習データ入り
商品名と単価の2条件で行を選び、倉庫で列を選ぶ
応用の表では、A6:A11に商品名、B6:B11に単価、C5:E5に倉庫名、C6:E11に数量を置きます。検索条件はA3＝倉庫、B3＝商品名、C3＝単価です。D3へ次の式を入力します。
ポイント2つの行条件が両方一致した場所だけ1になります。単価列を数量の戻り範囲に含めないことが大切です。C5:E5とC6:E11の列位置をそろえます。
古いExcelでは検索用の補助列を作る
F6に次の式を入力し、F11までコピーします。区切り文字「/」が商品名などに含まれないことを前提にしています。
D3では補助列から行番号を探します。
注意単純に文字をつなげると別の組み合わせが同じ文字列になることがあります。区切りを入れる方法でも、区切り文字が元データに含まれていないかを確認してください。
練習用Excelファイル（無料ダウンロード）
倉庫や商品を、表にない名前に変えて、エラーの出方も試せます。XLOOKUP対応のMicrosoft 365・Excel 2024・2021で開いてください。
Excelファイル（.xlsx）／登録不要／数式・練習データ入り
クロス抽出ができない・エラーになるときの確認表
| 症状 | 見直す場所 |
|---|---|
| #N/A | 商品名・倉庫名・単価が存在するか、型や空白が一致するか |
| #VALUE! | XLOOKUPの検索範囲と戻り範囲の行数・列数 |
| 隣の倉庫の数量が出る | 見出し行と数量表の開始列が同じか |
| 同じ条件なのに値が違う | 商品名や条件の組み合わせが重複していないか |
ポイント未登録時の表示だけを変えたい場合は、完成した検索式をIFNA(検索式,"該当なし")で囲めます。元の数量セルの#N/Aも置き換わるため、初めは元の式で原因を確認します。
クロス検索と複数行の抽出は違う
練習用Excelファイル（無料ダウンロード）
XLOOKUP・INDEXとMATCH・複数条件の3シート入り。複数条件シートの「カモミール／2100／倉庫B」は21です。黄色い検索条件を変更して試せます。ボタンをクリック／タップして保存し、Excelで開いてください。
Excelファイル（.xlsx）／登録不要／数式・練習データ入り
仕様の確認：Microsoft公式・XLOOKUP関数
コメント