s028｜クロス集計表からデータを抽出する方法【XLOOKUP関数、INDEX関数、MATCH関数】｜Excel関数によるデータ抽出方法

クロス集計表から、指定の行・列の項目が交差する位置にあるデータを抽出するには、XLOOKUP関数なら引数［戻り範囲］にもう1つXLOOKUP関数を組み合わせるだけで可能。

例題１｜クロス表から検索抽出する

1. 「数量」を求めるセル（C3セル）を選択し、「=XLOOKUP(」と入力する。
2. ［検索値］…クロス表の列見出しの検索値A3セルを選択する。
3. ［検索範囲］…検索するクロス表の列見出しのセル範囲を選択する。
4. ［戻り範囲］…「XLOOKUP(」と入力する。
5. ［検索値］…クロス表の行見出しの検索値B3セルを選択する。
6. ［検索範囲］…検索するクロス表の行見出しのセル範囲を選択する。
7. ［戻り範囲］…抽出するセル範囲を選択する。
8. ［見つからない場合］［一致モード］［検索モード］…省略して、「Enter」キーで数式を確定する。

【数式】=XLOOKUP(A3,B5:D5,XLOOKUP(B3,A6:A9,B6:D9))

数式の解説

「XLOOKUP(B3,A6:A9,B6:D9)」の数式は、B3セルの「ペパーミント」がある行の数量のセル範囲を選択する。この抽出したセル範囲をもう1つのXLOOKUP関数の引数［戻り範囲］に組み合わせて、A3セルの「倉庫B」を検索値にして抽出する数式を作成することで、A3セルの「倉庫B」とB3セルの「ペパーミント」が交差する数量「40」が抽出される。

Excelデータダウンロード

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

例題２｜クロス表から検索抽出する

1. 「数量」を求めるセル（C3セル）を選択し、「=INDEX(」と入力する。
2. ［参照］…抽出するセル範囲を選択する。
3. ［行番号］…MATCH関数で検索値のB3セルがクロス表の何行目にあるかを求める数式を入力する。
4. ［列番号］…MATCH関数で検索値のA3セルがクロス表の何列目にあるかを求める数式を入力して、「Enter」キーで数式を確定する。

【数式】=INDEX(B6:D9,MATCH(B3,A6:A9,0),MATCH(A3,B5:D5,0))

数式の解説

「MATCH(B3,A6:A9,0)」のB3セルの「ペパーミント」が表の何行目にあるかを求め、「MATCH(A3,B5:D5,0)」の数式は、A3セルの「倉庫B」が表の何列目にあるかを求める。求めた番目をINDEX関数の引数の［行番号］［列番号］に組み合わせて数式を作成すあることで、A3セルの「倉庫B」とB3セルの「ペパーミント」が交差する数量「40」が抽出される。

Excelデータダウンロード

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

応用１｜

行見出しが複数列のクロス表から抽出する場合は、XLOOKUP関数なら

❶［担当者］行見出しをすべて［&］で結合して［検索値］と［検索範囲］に指定して数式を作成しましょう。

INDEX関数の数式では、❷まず行見出しをすべて［&］で結合した列を作成しておく。❸［行番号］のMATCH関数の［検索値］には、行見出しをすべて［&］で結合して指定し、❹［検査範囲］には、作成した列を指定して数式を作成しましょう。

Excelデータダウンロード

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