経理の奥の手！Excel表からデータをクロス抽出する方法

経理高速化のコツは、可能な限り「判断」と「作業」を機械に任せてしまうことです。Excelはそれを実現する力強い味方であり、使いこなせば経理は確実に高速化していきます。

さて、経理の仕事では、表の中から特定のデータを探し出す作業があります。特定の勘定科目の数値を抜き出したり、顧客リストの中から特定の顧客の住所を取り出すといった作業です。
このような「抽出」作業をExcelで自動化する方法として、SUMIF関数とVLOOKUP関数を紹介しました。詳細は以下をご覧ください。

▶経理を高速化するSUMIF関数の基本と５つの超便利機能
▶経理の文書作成を高速化する【VLOOKUP関数】の使い方

今回はそれを踏まえて、この２つの関数にはできない複雑な抽出方法をご紹介します。

その抽出方法とは、「クロス抽出」。登場する関数はINDEX関数とMATCH関数です。

１．クロス抽出とは

まず、クロス抽出とは何かを確認しましょう。以下の「月次予算一覧表」をご覧ください。

上記の表の中から、関数を使って「６月」の「雑給」の金額を抽出しようと思います。
これの作業を自動化しようとすると、第3行の中から「6月」の列を探し出し、A列の中から「雑給」の行を探し出して、その行列がクロスするセルを抽出するという関数が必要になります。

実は、この抽出方法はSUMIFもVLOOKUPもできません。行か列の片方を検索対象にしてしまうと、もう片方は固定しておかなければならないのです。つまりSUMIFで「雑給」を探そうとすると（行を検索対象にすると）、何月の雑給かは指定しなければいけません。

そこで、行も列も検索対象とするクロス抽出を行うには、２つの関数を組み合わせる必要があります。

２．クロス抽出を実現する２つの関数

クロス抽出のためには、INDEX関数とMATCH関数を使用します。

２－１．INDEX関数の使い方

INDEX関数とは、表の中の１つのセルのデータを拾いだす関数です。拾うセルの指定は、「何行目×何列目」と、行と列の番号で指定します。

たとえば、表の範囲をB4:M40、うち上から7列目、左から3列目のセルと指定するには、以下のとおりとなります。

=INDEX(B4:M40,7,3)

その結果、以下のように該当セルを探し出し、その値である18,350を返してくれます。

しかし、上記のとおりINDEX関数では行番号と列番号を調べて指定しなければならないという問題があります。行番号と列番号を目で見て探していては何の意味もありません。そこで、それぞれの番号を自動取得するためにMATCH関数を使います。

２－２．MATCH関数の使い方

MATCH関数は、指定した範囲内のどこに指定した値があるかを検索し、その行番号または列番号を教えてくれる関数です。まずは書き方から確認しましょう。

２－２－１．MATCH関数の書き方

MATCH関数は以下のように入力します。

以下引数の内容を確認しましょう。

引数１　検索条件（検査値）

探したい文字列や数値を入力します。検索条件は数式やセル参照にすることもできますので、連続コピーして条件を変えていくことも可能です。

引数２　検索範囲（検査範囲）

検索対象となるデータ範囲を指定します。

行番号を取得したいときは１列のセル範囲（Ex. A4:A40）、列番号を取得したいときは１行のセル範囲（Ex. B3:M3）で入力します。複数の行×複数の列の範囲検索は無効になります。

引数３　検索方法（照合の種類）

検索範囲内に検索条件に合致するセルが見つからなかった場合の対処法を指定します。１、０、－１のいずれかの数値を入れますが、１か－１を選ぶと近しい内容のセルが、０を選ぶとエラー値が返されます。勝手に違うセルを選ばれてしまっては困りますので、必ず０を入力しておきましょう。

２－２－２．MATCH関数の実例

今回の例に合わせ、MATCH関数で「６月の列番号」を取得してみましょう。数式は以下のとおりです。

=MATCH(“6月”,B3:M3,0)

これは「B3からM3を調べて、❝６月❞が左から何番目にあるか調べる」という数式となっています。以下のように列番号を探し出してくれます。

これにより列番号が取得できました。
同じようにA列を検索対象範囲として「雑給」と合致するセルを探せば、行番号も取得できます。

３．INDEXとMATCHでクロス抽出する方法とコツ

３－１．クロス抽出の方法

上記を組み合わせると、INDEX関数とMATCH関数で「６月」の「雑給」をクロス抽出する数式は以下の形になります。

上記式では、先にそれぞれのMATCH関数が計算され、それぞれ「７」と「３」が返されます。
その結果をINDEX関数に反映させると、

=INDEX(B4:M40,7,3)

という計算式になります。つまりB4:M40の範囲内から縦７行目×横３行目のセル内容を取得するという式内容になり、下図のように該当数値18,350を拾ってきてくれます。

３－２．クロス抽出の入力スピードを上げるコツ

ただでさえ馴染みの薄いINDEX関数とMATCH関数ですから、これを組み合わせるとなると結構時間が掛かります。入力スピードをアップさせるコツとして、範囲指定ではシート全体、列全体、行全体を指定しましょう。
それぞれ全体入力をした場合は以下の数式になります。

=INDEX( 1:1048576 , MATCH( “雑給” , A:A , 0 ), MATCH( “6月” , 3:3 , 0 ) )

これにより、「シート全体から、A列が❝雑給❞の行番号と、第３行が❝６月❞の列番号を探し、それぞれクロスするセルを抽出する」という意味になります。

シート全体指定の方法

「1:1048576」と入力します。マウスで選択する場合はシート左上の三角形をクリックします（下図）。

列全体指定の方法

A列全体を指定するには、「A:A」と入力します。マウスで選択する場合は列番号の「A」をクリックします（下図）。

行全体の指定の方法

第３行全体を指定するには、「3:3」と入力します。マウスで選択する場合は行番号の「３」をクリックします（下図）。

４．INDEX関数とMATCH関数の留意点

以上のように、SUMIF関数やVLOOKUP関数ではできない表のクロス抽出が、INDEX関数とMATCH関数ならできてしまいます。経理の場面では案外使うことがありますので、ぜひ覚えておきましょう。

ところで、データを抽出することに関しては、SUMIF関数やVLOOKUP関数ができることは、すべてINDEX関数とMATCH関数の組合せで代用できてしまいます（集計はできませんが）。特にVLOOKUP関数の弱点である「検索範囲は抽出対象列よりも左でなければならない」という点に関しては、INDEXとMATCHならまったく問題になりません。

そんな便利なINDEX関数とMATCH関数とはいえ、VLOOKUP関数の代わりに頻繁に使うのはのはあまりお勧めしません。理由は以下のとおりです。