s029｜クロス表の見出しとデータを入れ替えた別のクロス表を作成【XLOOKUP関数、TEXT関数、IFNA関数、INDEX関数、MATCH関数】｜Excel関数によるデータ抽出方法

クロス表のデータを見出しにした別のクロス表を作成したいときは、XLOOKUP関数の引数［検索範囲］にXLOOKUP関数を組み合わせた数式を作成しよう。

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

1. 作成するクロス表のセル（B8セル）を選択し、「=XLOOKUP(」と入力する。
2. ［検索値］…抽出先の行見出しのセル範囲を選択して、「F4」キーを1回押して「$」記号をつけ絶対参照にする。
3. ［検索範囲］…XLOOKUP関数で、TEXT関数で抽出した列見出しの日付の曜日をもとにクロス表から抽出する数式を入力する。
4. ［戻り範囲］…抽出する「名前」のセル範囲を選択して、「F4」キーを1回押して「$」記号をつけ絶対参照にする。
5. ［見つからない場合］…［””］（空白）を入力する。
6. ［一致モード］［検索モード］…省略して、「Enter」キーで数式を確定する。
7. 数式をほかの列に複写する。

【数式】=XLOOKUP($A$8:$A$9,XLOOKUP(TEXT(B7,”aaa”), $B$2:$D$2,$B$3:$D$5,””),$A$3:$A$5,””)

数式の解説

「XLOOKUP(TEXT(B7,“aaa”),$B$2:$D$2,$B$3:$D$5,“”)」の数式は、日程表の日付の曜日が抽出元のクロス表の曜日と一致する列にある場所名のセル範囲を抽出する。この抽出したセル範囲をもう1つのXLOOKUP関数の引数［検索範囲］に組み合わせて、A8～A9セルの場所名を検索値にして抽出する数式を作成することで、場所名と日程に該当する「氏名」が抽出される。

Excelデータダウンロード

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

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

1. 作成するクロス表（B8セル）を選択し、「=IFNA(」と入力する。
2. ［値］…「INDEX(」と入力する。
3. ［参照］…抽出するセル範囲を選択して、「F4」キーを1回押して「$」記号をつけ絶対参照にする。
4. ［行番号］…MATCH関数で行見出しのA8セルが抽出元のクロス表の何行目にあるかを求める数式を入力する。
5. ［列番号］［領域番号］…省略する。
6. IFNA関数の［NAの場合の値］…［””］（空白）を入力して、「Enter」キーで数式を確定する。
7. 数式を必要な分だけ複写する。

【数式】=IFNA(INDEX($A$3:$A$5,MATCH($A8,INDEX($B$3:$D$5,,MATCH(TEXT(B$7,”aaa”),$B$2:$D$2,0)),0)),””)

数式の解説

「MATCH($A8,INDEX($B$3:$D$5,,MATCH(TEXT(B$7,“aaa”),$B$2:$D$2,0)),0)」の数式は、A8セルの場所名が抽出元のクロス表の何行目にあるかを求める。この行の番目をINDEX関数の引数［行番号］に組み合わせて「=IFNA(INDEX($A$3:$A$5,MATCH($A9,INDEX($B$3:$D$5,,MATCH(TEXT(B$7,“aaa”),$B$2:$D$2,0)),0)),“”)」の数式を作成することで、場所名と日程に該当する「氏名」が抽出される。

Excelデータダウンロード

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