いまさら聞けないExcelの使い方講座

【Excel】XLOOKUP関数を応用してクロス表から簡単にデータを取得する方法

今井孝

2023年8月2日 06:55

「縦」「横」の交差するクロス表から簡単にデータを取り出すには？

XLOOKUP関数なら定番のINDEX & MATCH関数は不要

窓の杜から
ダウンロード

　「縦」「横」の交差する箇所に必要な情報を掲載する「クロス表」は、料金表や商品カタログのスペック表などでよく見かけます。目的の1箇所の情報を確認するには便利ですが、条件を切り替えながら該当のデータを抽出したり、一覧形式に集計したりする時には少し面倒です。

　例えば、以下のような料金表で「中学生」「1日パス」の料金は「4,800円」です。上端の見出しから「中学生」を探して、左端の「1日パス」を目で追ったと思います。では「大人」「ナイトパス」は？同じく目視で「大人」で「ナイトパス」と探すでしょう。

上端の見出しから「中学生」（①）、左端の「1日パス」（②）を探すと、料金は「4,800円」とわかります。

　この程度のサイズの表でも目視では時間がかかりますよね。できれば、セルG1とG2に入力した値から自動的に料金を取得できる仕掛けを作りたいところです。

　定番のテクニックでは、INDEX関数とMATCH関数を組み合わせます。詳しくは、以前に紹介した記事も参考にしてください。

セルG1とG2の値から料金を自動取得します。定番のINDEX関数とMATCH関数の組み合わせを使うと、「=INDEX(B2:D6,MATCH(G2,A2:A6,0),MATCH(G1,B1:D1,0))」（③）のようになります。

　INDEXとMATCHは従来からある関数なので、エクセルのバージョンの互換性を心配しなくてもいいのですが、数式が少し複雑に感じるかもしれません。もし、Microsoft 365のExcel、Excel 2021を使っているなら、XLOOKUP関数を使って、クロス表からデータを抽出する方法を覚えておくと便利です。

XLOOKUP関数の構文

　最初にXLOOKUP関数の構文を見てみましょう。6つの引数のうち、最初の［検索値］［検索範囲］［戻り範囲］の3つの引数を指定すれば動作します。引数の名前は、そのまま引数の役割です。今回紹介するテクニックでも［検索値］［検索範囲］［戻り範囲］しか使いません。

XLOOKUP関数の構文。検索文字列があるセル範囲を指定する［検索値］、検索対象のセル範囲［検索範囲］、対応する値を取得するセル範囲［戻り範囲］の3つを指定すれば動作します。

XLOOKUP関数をネストする

　以下の数式をセルG3に入力すると、セルG1とG2の値を参照してクロス表からデータを抽出可能です。

=XLOOKUP(G1,B1:D1,XLOOKUP(G2,A2:A6,B2:D6))

セルG3に「=XLOOKUP(G1,B1:D1,XLOOKUP(G2,A2:A6,B2:D6))」と入力します（④）

「中学生」「1日パス」の料金「4,800円」を抽出できました

　これだけでは何をやっているのかわかりにくいため、XLOOKUP関数の構文と見比べながら数式の内容を見ていきましょう。入力した数式は、XLOOKUP関数の引数として、XLOOKUP関数をネストしています。

　内側の数式は「XLOOKUP(G2,A2:A6,B2:D6)」です。セルG2の値を［検索値］、セルA2～A6が［検索範囲］となります。［戻り範囲］はセルB2～D6と指定しているので、結果はスピルで3列表示されるはずです。試しに「=XLOOKUP(G2,A2:A6,B2:D6)」と入力してみましょう。

セルG5に「=XLOOKUP(G2,A2:A6,B2:D6)」と入力しました（⑤）。［検索値］はセルG2、［検索範囲］がセルA2～A6、［戻り範囲］がセルB2～D6であることがわかります

結果はスピルとして、「一日パス」に該当する「5800」「4800」「2400」が並びました（⑥）

　内側のXLOOKUP関数の結果を「スピル」で取得しているのがポイントです。この結果を外側のXLOOKUP関数の引数［戻り範囲］に指定すると、［検索値］のセルG1の値を［検索範囲］のセルB1～D1から探して、{5800, 4800, 2400}という配列から該当するデータを返します。

　スピルで得られた配列（セルG5～I5）は「G5#」と表現できるので、「=XLOOKUP(G1,B1:D1,G5#)」と入力してみます。

セルG6に「=XLOOKUP(G1,B1:D1,G5#)」と入力しました（⑦）［検索値］はセルG1、［検索範囲］がセルB1～D1、［戻り範囲］が5G#（セルG5～I5）であることがわかります

該当の「4800」を抽出できました

　ここでは、横方向へスピルして「5800」「4800」「2400」と配列を取得しましたが、外側と内側のXLOOKUP関数の数式は逆でも構いません。以下のような数式です。

=XLOOKUP(G2,A2:A6,XLOOKUP(G1,B1:D1,B2:D6))

　この場合、内側の数式で得られる結果はスピルで、{4500, 4800, 4000, 4200, 4000}のような配列となります。

一覧表に埋め込む

　同様の数式を利用して、クロス表のデータを一覧表に埋め込むことも可能です。ただし、数式のコピー時にセル参照がズレないように、［検索範囲］と［戻り範囲］のセル範囲は“絶対参照”で指定してください。

B列とC列に入力された区分とチケットから料金を抽出することも可能です

　一覧表に入力された値に対応するデータをクロス表から抽出するには、先ほど入力した数式を調整して以下のようになります。

=XLOOKUP(B2,G1:I1,XLOOKUP(C2,F2:F6,G2:I6))

　しかし、このままオートフィルでコピーすると［検索範囲］と［戻り範囲］のセル範囲がズレてしまいます。絶対参照を使った数式を入力しましょう。

=XLOOKUP(B2,$G$1:$I$1,XLOOKUP(C2,$F$2:$F$6,$G$2:$I$6))

オートフィルで数式をコピーする場合、［検索範囲］と［戻り範囲］は絶対参照を使います

XLOOKUP関数のネストは応用範囲が広い

　ここでは、2つのXLOOKUP関数をネストしてクロス表からデータを抽出する方法を紹介しました。関数の結果をスピルとして取得することに慣れると単純なミスの防止にもつながります。スピルは既存の関数にも使えるので、ぜひ活用してください。