ExcelのVLOOKUP関数とMATCH関数で給与データを「クロス抽出」する方法

 

現在給与計算業務は請け負っていません。

お客さんが自分で給与計算できるようアドバイスしたり、給与計算ソフトを提案したりはしていますが。

ただ年末調整業務は引き受けています。

 

給与データを取り込みたい！

こちらで年末調整業務を進めるときに必要になるのが毎月の給与データ。

いつ、いくら支給していくら社会保険料と所得税を天引きしているのか。

これらの数値はすべて手入力しており、正直

「うーん、非効率だなぁ…」

と感じていました。

ただお客さんの中には給与計算ソフトを使っていたり、Excelを使っていたりする方が結構いらっしゃいます。

ここから取り出したデータをExcelに特定の形式で連動させることさえできれば年末調整ソフトへ取込むマクロを先日作ることができました。

このマクロを使えば一瞬でデータを取り込むCSVファイルを作成できるように。

ただ、その特定の形式に連動させる方法がなかなか思いうかびませんでした。

お客さんによって給与計算ソフトはまちまちだし、Excelで集計している方の場合それこそ形式が千差万別。

あるお客さんにはこれでうまくいってもこっちのお客さんではうまくいかない…

そんなケースが続きましたが、苦労の甲斐あってどのお客さんでも連動できる方法が見つかりました。

それは関数のVLOOKUPとMATCHを組み合わせる方法。

「クロス抽出」って呼ばれたりするようです。

INDEXとMATCHを組み合わせた方法もありましたが、自分はふだん使い慣れているVLOOKUPを使った方がしっくりきました。

今回の記事ではこのVLOOKUP関数とMATCH関数を組み合わせた方法を紹介します。

 

「クロス抽出」とは

「クロス抽出」とはなにか。

冒頭の写真を例にします。

左側と右側のシートは別々のシートです。

右側のシートの「奥　勇夫」さんの行(4行目)と「課税支給額」の列(C列)がクロスするC4セルの値「310,000」を左側シートのC2セルへ連動させるのが「クロス抽出」。

特定の「行」と「列」がクロスした部分を抽出するから「クロス抽出」なんですね。

個人的にはなんとなく「クロス検索」の方がしっくりきますが。

 

 

「1月31日」シート(連動元シート)の支給控除一覧表から「集計」シート(連動先シート)へ連動

シート名は各シートの下の方に表示されます。

「1月31日」シートが「連動元シート」です。

タイトルが「給与支給控除一覧表」となっているとおり給与支給日の1月31日にいくら支給して、いくら控除したかが一覧になっています(氏名・金額はすべて仮のものです)。

「1月31日」シートがこれです。

 

この列の中の「課税支給額」、「社保料合計」、「所得税」の3列が連動させたい列です。

 

「連動先シート」になる「集計」シートがこれです。

 

「集計」シートの「課税支給額」、「社保料合計」、「所得税」の3列に「1月31日」シートの各列から数字を連動させるのが目標です。

「集計」シート上で上の行から1月・2月・3月・・・と順に数字を並べていくことができれることができれば、あとはマクロを使って年末調整ソフトにデータを取り込むことができます。

 

VLOOKUP関数とは

連動のベースとなる関数がVLOOKUP。

いろんなところから引っ張ってきたデータを活用するには欠かせない大事な関数です。

自分が使っている関数の中で最も重要といっても過言ではありません。

 

「集計」シートの「課税支給額」に「1月31日」シートの数値を連動させるためには「集計」シートのC2セルへVLOOKUP関数を入れます。

 

それぞれの引数が何を意味するのか見てみます。

「$」は数式を前後左右にコピーするときに参照先が変わらないようつけたものです。

このつけ方を説明すると記事が長くなってしまうので今回は省略します。

引数自体はどれも「$」がなくても有効です。

 

このVLOOKUP関数がどう動くかを見ていきます。

①の引数で検索する値は「集計」シートのA2セルに入力してある「奥　勇夫」と指定。

②の引数で「1月31日」シートのA:K列を指定すると「1月31日」シートの左端列であるA列の中で「奥　勇夫」を検索してくれます。

 

その結果「奥　勇夫」は4行目にあることを見つけてくれます。

③の引数で列番号を「3」と指定すると、A列からK列の中で3列目にある「310,000」を見つけてくれます。

④の引数には基本的に「FALSE」を入れる、と最初はあまり深く考えずに覚えてしまってもいいかなと思います。

 

見つかった「1月31日」シートの「310,000」はVLOOKUP関数を入れた「集計」シートのC2セルに連動されます。

 

 

VLOOKUP関数の「引数③：列番号」にMATCH関数を入れる

MATCHはVLOOKUPと比べてややマイナーな印象です。

自分も普段の実務でVLOOKUPはバリバリ使ってますがMATCHはあまり使う機会がなく。

実際、今回の事例で使おうとしたときもネットや以前勉強したとき使ったMOSのテキストで復習しないとやり方を思い出せませんでした(^^;)

 

MATCHで入力する引数は3種類。

上の図のように引数を入れるとMATCH関数は「7」を返します。

「1月31日」シート内にある給与支給控除一覧表の中で「社保料合計」は左から「7列目」にあるからです。

…と書いてもなかなかイメージしづらいかもしれません。

正直このMATCHはVLOOKUP以上にとっつきづらいです。

私は何度も

「「検索値」が「検索範囲(特定の行または列)」の左端（または上端）から何番目にあるか」

といった内容を何度も唱えてむりやり覚えましたが、時間が経つとやっぱり忘れてしまいます。

3番目の引数は通常完全一致しか指定しないので「0」を入れる！と丸暗記でOKです。

 

このMATCHを先ほどのVLOOKUPの3番目の引数部分に入れます。

今度は「集計」シートのD2セルの「社保料合計」に関数を設定して、「1月31日」シートのG4セルの「44,330」を連動させます。

 

「1月31日」シートの中でG3セルにある「社保料合計」が左端列から何列目にあるかをMATCH関数が見つけてくれます。

左端列を「1列目」として「7」列目であることを読み取ることでMATCHは「7」という数値を返すイメージです。

これで「1月31日」シートの4行目の「奥　勇夫」さんの「社保料合計」である「44,330」を「集計」シートのD2セルに連動させることができました。

 

 

ここまで数式を作り上げることができれば、あとは従業員が100人いようが1,000人いようが数式を前後左右にコピーするだけで対応できます。

月が変わったときはシート参照部分の「1月31日」が変わってしまうので修正する必要がありますが。

この修正もINDIRECT関数とTEXT関数を使えば修正不要にできるのですが、文章量が多くなりすぎてしまうのでこの辺の説明も今回は省略します。

INDIRECT関数とTEXT関数を使った参照の連動化もとても便利だと思うので近いうちに紹介したいですね。

 

未経験の方はまずVLOOKUP関数にチャレンジしてほしい

慣れないうちは関数の中に関数を入れるとどういう動作になるのかイメージしづらく、かなりとっつきづらいのですが慣れてくるとExcel上でできることの幅が広がります。

VLOOKUPもMATCHもそれぞれ引数を複数設定する必要があるので今回紹介した方法は初心者向けとは言い難いです。

それでも「クロス抽出」は汎用性がかなり高いと思うので興味をもった方はぜひチャレンジしてみていただければ。

未経験の方の場合まずは適当な数値を入れた表を使ってVLOOKUP関数からチャレンジしてみてほしいです。

引数に何を入れればどう数値が変わるのかを体感してしまうのが関数を習得する一番の近道だと思います。

特にVLOOKUP関数の一連の流れが習得できたときはExcelでできることのイメージが大きく広がること請け合いです！