いきなり答える備忘録

G Suite・Microsoft 365・LibreOfficeなどに関するメモと日々の実験

(Gスプレッドシート)クロス表からデータを抽出する

 Googleスプレッドシートで、検索値に基づいて行と列を特定し、その行と列が交差するセルにあるデータを取得する方法についてです。

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

手順

抽出の考え方

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

f:id:accs2014:20200814003119p:plain:w700

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

INDEX関数とMATCH関数を用いる方法

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

f:id:accs2014:20200814003137p:plain:w780

 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と指定する(→完全一致検索)の忘れると違う結果になりますので注意が必要です。
 

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

f:id:accs2014:20200814003140p:plain:w700

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

DGET関数を用いる方法

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

f:id:accs2014:20200814003123p:plain:w750

 J3セル

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

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

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

 

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

f:id:accs2014:20200814003129p:plain:w700

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

f:id:accs2014:20200814003133p:plain:w700

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