いきなり答える備忘録

Google Workspace・Microsoft 365・LibreOfficeなどに関するメモ

(Gスプレッドシート)クロス表から検索・抽出する

 [※2022年12月更新]
 Googleスプレッドシートで、検索値に基づいて行と列を特定し、その行と列が交差するセルにあるデータを取得する方法についてです。クロス抽出などと呼ばれます。

  • XLOOKUP関数を使ってクロス表からデータを抽出できます。
  • INDEX関数とMATCH関数を組み合わせる古くからの方法もあります。
  • 実はDGET関数だけでも抽出できます。式はコンパクトになりますが、検索値にマッチするデータが複数ある場合にエラーを生じます。

抽出の考え方

 次の画像のB2:F7の範囲にある表は「売上集計表」と名付けられたクロス表です。B列(B3:B7)には店名が並んでおり、2行目(C2:F2)には4月~7月の月名が並んでいます。
 店名と月を特定してその行と列が交差するセルを参照すれば、その店舗のその月の売上高を知ることができます。これがクロス表からの抽出の考え方です。

 そこで以下では、検索する店名をH3セルに、月をI3セルに入力したうえで、J3セルに式を入力して売上高を検索する例を試してみます。

XLOOKUP関数を使う方法

 比較的新しい関数であるXLOOKUP関数を使う方法です。式のネスト(入れ子)は必要ですが、この関数だけで抽出ができます。
 画像ではJ3セルに次のような式を入力しています。

 J3セル

=XLOOKUP(I3,C2:F2,XLOOKUP(H3,B3:B7,C3:F7))

 まず内側の「XLOOKUP(H3,B3:B7,C3:F7)」により、「C3:F7の中で『B3:B7内での大阪支店の位置』に対応する行(つまり4行目)」を得ることができます。
 これによりC3:C7の範囲のうち4行目の内容、つまり「20125」「20694」「21055」「25118」という4つの数字の配列が得られます。
 さらにこの結果に対して外側のXLOOKUP関数を適用することにより、「『20125』『20694』『21055」『25118』の中で『C2:F2内での5月の位置』に対応する列(つまり2列目)」を得ることができます。
 4つの数字のうち2列目(2番目)の値は「20694」ですので、最終的にこの値が得られることとなります。


 結果は次のとおりです。
 「大阪支店」の行にあり、かつ「5月」の列にある数値、つまり「20694」が取得できています。

INDEX関数とMATCH関数を使う方法

 Excelでも多用されるオーソドックスな方法です。
 画像ではJ3セルに次のような式を入力しています。

 J3セル

=INDEX(C3:F7,MATCH(H3,B3:B7,0),MATCH(I3,C2:F2,0))

 INDEX関数は「=INDEX(表の範囲,表内の行番号,表内の列番号)」とすることで、表内のその位置にある値を抽出する関数です。この手の抽出にはまさに打ってつけです。
 ただしここでは行番号や列番号を直接指定するのではなく、店名と月から番号を特定する必要があります。そこでMATCH関数を用いて「B3:B7の範囲において『大阪支店』が何番目にあるか」と「C2:F2の範囲において『5月』が何番目にあるか」を求めています。
 2つのMATCH関数はそれぞれ「4」と「2」という結果を返しますので、上記の式は次のように解釈されます。

=INDEX(C3:F7,4,2)

 なお、MATCH関数の第3引数を0と指定する(→完全一致検索)の忘れると違う結果になりますので注意が必要です。
 

 結果は次の画像のようになります。
 正しい結果「20694」が得られていることがわかります。

 INDEX関数の代表的な利用例ではありますが、MATCH関数との組み合わせで理解する必要があるためなかなか複雑で、初心者泣かせなのが難点です。

DGET関数を使う方法

 データベース関数の一種であるDGET関数を用いる方法です。
 データベース関数はとっつきにくく敬遠される傾向にありますが、Googleスプレッドシートでは引数の特性により扱いやすくなっており、簡潔に結果を得ることができます。
 画像ではJ3セルに次のように入力しています。

 J3セル

=DGET(B2:F7,I3,{B2;H3})

 DGET関数の詳しい解説は公式解説(こちら)をご覧いただくとして、Googleスプレッドシートの場合は検索欄(ここではH列)に見出しをつける必要がない(見出し自体は必要だが上記式中の「{B2;H3}」のように見出しと検索値の組み合わせを関数内で作れる)ため、検索欄のつくり方が制約されないのがポイントです。
 上記の式は次のように解釈されます。これにより「『5月』の列にあり、かつ『店名が大阪支店』の行にある」セルの値が抽出されます。

=DGET(B2:F7,"5月",{"店名";"大阪支店"})

 

 そして結果はこうなります。「20694」と、正しい結果が得られていることがわかります。

 
 さて、この方法の場合、INDEX関数を用いる方法と違ってB列に見出しが必要(つまりB2セルに値が入力されていることが必要)なのではないかと思えますが、実は次の画像のように空白でも正しく機能します。
 さらに言えばSPARKLINE関数で斜線を引いているような場合でも問題ありません。

 この方法のデメリットとしては、検索する値が複数存在する場合(この例では「大阪支店」が2つ以上存在する場合)にエラーになることが挙げられます(他の方法ではこのエラーが起きません)。用途によっては問題になりませんが、とにかくたくさんあるもののうち最初にマッチするものを抽出したい、といった場合には使えません。