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

  • このエントリーをはてなブックマークに追加
  • LINEで送る
Excel表からクロス抽出する方法

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

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

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

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

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

1.クロス抽出とは

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

クロス抽出

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

クロス抽出

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

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

2.クロス抽出を実現する2つの関数

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

2-1.INDEX関数の使い方

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

INDEX関数の使い方

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

=INDEX(B4:M40,7,3)

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

INDEX関数の説明

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

2-2.MATCH関数の使い方

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

2-2-1.MATCH関数の書き方

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

MATCH関数の書き方

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

引数1 検索条件(検査値)

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

引数2 検索範囲(検査範囲)

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

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

引数3 検索方法(照合の種類)

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

2-2-2.MATCH関数の実例

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

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

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

MATCH関数の使い方

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

3.INDEXとMATCHでクロス抽出する方法とコツ

3-1.クロス抽出の方法

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

INDEXとMATCHによるクロス抽出

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

=INDEX(B4:M40,7,3)

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

INDEX関数の説明

3-2.クロス抽出の入力スピードを上げるコツ

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

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

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

シート全体指定の方法

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

列全体指定の方法

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

列全体を選択

行全体の指定の方法

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

4.INDEX関数とMATCH関数の留意点

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

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

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

留意点① マイナーな関数である

経理に限らずかなり有名なVLOOKUPに対して、INDEXやMATCHはマイナーな関数です。自分では理解できていても他人には理解できないことをしてしまうと、業務がブラックボックス化し、担当替えで苦労することになります。SUMIFやVLOOKUPでできることはなるべくそれらの関数に任せましょう。

留意点② 入力が複雑である

使いだすとよくわかるのですが、馴染みのない2つの関数を組み合わせる作業は意外と大変です。1文字入力ミスしただけで動かなくなりますので、結構時間が掛かります。

その点、SUMIFやVLOOKUPの入力しやすさには敵いません(特にSUMIFは本当に入力しやすいですね)。この辺の使い勝手も考慮すると、やはりINDEXとMATCHはクロス抽出専用の「奥の手」にしておくのがいいでしょう。

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

経理の業務効率が5倍になるExcel活用の全ノウハウ

  • このエントリーをはてなブックマークに追加
  • LINEで送る

他社さんと匿名で意見交換しませんか?

株式会社経理救援隊では、経理に携わる皆さまの「他社さんはどんなことをしているのか?」という疑問にお答えするため、匿名アンケートによる情報交換会を組織しております。

無料の匿名アンケート会員に登録して、生の現場情報を交換しましょう。ぜひご参加ください。

詳しく読む

SNSでもご購読できます。

スポンサードリンク

コメント

コメントを残す

*

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください