【Excel】目視とコピペでクロス表から特定条件のデータを抽出するのは非効率！

①INDEX関数とMATCH関数を組み合わせる

　INDEX関数とMATCH関数をそれぞれ単独で使う人は限られるでしょう。今回の例のように、クロス表から縦・横の交差する位置のデータを取得する時に組み合わせて使われます。

　INDEX関数は、セル範囲から「行」と「列」の交差する位置の値を求めます。MATCH関数は、検索値がセル範囲の何番目にあるかを返します。構文は以下の通りです。

INDEX関数の構文。［配列］（セル範囲）から［行番号］と［列番号］の交差する位置の値を求めます。先頭行・列を「1」として数えます

MATCH関数の構文。［検索値］が［検索範囲］の何番目にあるかを求めます。［検索範囲］の先頭のセルを「1」として数えた値を返します。［照合の種類］は「0」で完全一致として検索します。「1」または省略で［検索値］以下の最大値、「-1」で［検索値］以上の最小値を一致とみなします

　以下の例では、利用時間（デイ・ナイト）と会員種別（ゴールド・シルバー・一般）のクロス表に金額が入力されています。一覧表の「会員種別」列と「利用時間」列に入力されたデータから、クロス表で該当のデータを取り出したい状況です。

利用時間（①）と会員種別（②）のクロス表が用意されています。交差する位置の金額を一覧表の「金額」列（③）に入力したいと思います

　まずはINDEX関数で「ゴールド」会員で「デイ」利用の金額を取得してみます。該当のデータは、セルG2～I3の範囲で1行目1列目ですから、「=INDEX(G2:I3,1,1)」という数式で目的の金額「1,900」を取得できます。

セルD2に「=INDEX(G2:I3,1,1)」と入力（④）した状態です。正しく「1,900」と取得できています

　取得した金額は正しいですが、いちいち“何行目”“何列目”の数値を指定するのは現実的ではありませんよね。そこで、検索値の位置情報をMATCH関数で調べて、INDEX関数の引数とするわけです。

　検索値とする「ゴールド」「デイ」のデータは一覧表に入力済みです。これらの位置をMATCH関数で取得すると、「ゴールド」は「MATCH(B2,G1:I1,0)」、「デイ」は「MATCH(C2,F2:F3,0)」となります。INDEX関数に指定する引数の順番を間違えないように注意してください。

セルD2の数式を「=INDEX(G2:I3,MATCH(C2,F2:F3,0),MATCH(B2,G1:I1,0))」と修正しました。「MATCH(C2,F2:F3,0)」（⑤）と「MATCH(B2,G1:I1,0)」（⑥）で「デイ」の行番号と「ゴールド」の列番号を取得しています。セルC2に入力された「デイ」はセルF2～F3の範囲で1番目（行目）、セルB2に入力された「ゴールド」はセルG1～I1の範囲で1番目（列目）です

一覧表に入力されたデータに該当するデータをクロス表から取得できました

　この数式を下方向にコピーすることを考えて、参照方式を“絶対参照”に切り替えると以下のようになります。MATCH関数の引数［検索値］となる「C2」と「B2」以外はすべて絶対参照です。

セル参照を絶対参照に切り替えて「=INDEX($G$2:$I$3,MATCH(C2,$F$2:$F$3,0),MATCH(B2,$G$1:$I$1,0))」としました。MATCH関数の引数［検索値］となる「C2」と「B2」以外はすべて絶対参照です

②VLOOKUP関数とMATCH関数を組み合わせる

　VLOOKUP関数とMATCH関数を組み合わせて、クロス表からデータを取得するテクニックもよく使われます。同じサンプルを利用するので結果も同じですが、数式の構造が異なります。

先ほどと同じサンプルです。利用時間（⑦）と会員種別（⑧）のクロス表が用意されており、交差する位置の金額を一覧表の「金額」列（⑨）に入力します

VLOOKUP関数の構文。［検索値］を元に［範囲］の左端の列を探して、［範囲］の［列番号］（何列目か）と同じ行にある値を返します。［検索の型］には検索方式を指定します。「FALSE」は完全一致、「TRUE」は近似一致で検索します。

　例えば「ゴールド」会員で「デイ」利用の金額をVLOOKUP関数で取得することを考えると、引数［列番号］の指定で困るはずです。

セルC2の値（デイ）を［検索値］として［範囲］をセルF1～I3と指定しました（⑩）が、［列番号］を指定できません

　［列番号］は会員種別（B列の値）によって切り替わるため、VLOOKUP関数の［列番号］に固定の数値は指定できませんよね。ここでMATCH関数の出番です。

セルD2に「=VLOOKUP(C2,F1:I3,MATCH(B2,F1:I1,0),FALSE)」と入力しました。「MATCH(B2,F1:I1,0)」（⑪）がポイントです。MATCH関数の結果をVLOOKUP関数の引数［列番号］として利用するため、セルF1～I1と指定します

一覧表に入力されたデータに該当するデータをクロス表から取得できました

　MATCH関数の結果の数値が、VLOOKUP関数の［列番号］になるのがポイントです。セルF1を含めて「MATCH(B2,F1:I1,0)」とすると、「ゴールド」（セルB2）は2番目、つまり結果は「2」となります。VLOOKUP関数の［範囲］（F1:I3）の2列目は「ゴールド」列ですよね。

　下方向へのコピーを考えた時の数式は以下の通り。VLOOKUP関数の［検索値］（C2）とMATCH関数の［検索値］（B2）は相対参照のまま、残りの引数はすべて“絶対参照”にしておきます。

セル参照を絶対参照に切り替えて「=VLOOKUP(C2,$F$1:$I$3,MATCH(B2,$F$1:$I$1,0),FALSE)」としました。VLOOKUP関数の［検索値］（C2）とMATCH関数の［検索値］（B2）以外はすべて絶対参照です

関数ごとに処理を切り分けて考えよう

　INDEX関数とMATCH関数、VLOOKUP関数とMATCH関数の組み合わせは、クロス表のデータを取得する定番のテクニックです。複雑な数式に見えますが、関数ごとに処理を分けて考えると理解できるはずです。