2015/09/09 公開

MATCH関数とVLOOKUP関数の合わせ技!人材管理に役立つEXCEL VLOOKUP術[vol.2]

Pocket

Excelシート上の参考表から必要なデータを検索し、指定した箇所に表示させることのできるVlookup関数。人材管理の業務を効率化する上で欠かせないメソッドです。VOL.1ではVlookup関数の基礎を説明しましたが、今回はより実務的な使い方、すなわちMATCH関数と組み合わせる方法をご紹介いたします。

横に長い参考表を使う際は、Vlookup関数とMATCH関数を組み合わせよう!

Vlookup関数は、参考表の項目が横に長くなった場合に、数式の列番号を変える作業が面倒であるという弱点があります。

例えば以下の図で、社員名簿の「社員No.」に入力することで、参考表に従って社員名簿の「氏名」「所属No.」「所属名」「エリア」「支店」が埋まっていくよう設定したい場合

図1

以下のように「氏名」を記入するセル(黄色の枠で囲ったB3のセル)に赤枠内の数式を組みこんだ後、「所属No.」のセル(C3の緑色のセル)には列番号を変えた数式を埋めなければなりません。

図2

図3

?(矢印)の指す列番号、すなわち取り出したいデータが参考表の何列目にあるかという番号を、(氏名なら?、所属No.なら?)いちいち変える作業は大変手間です。

そこで役に立つのがVlookup関数とMATCH関数の合わせ技。項目ごとに列番号を変化させなくても、一つの数式をすべてのセルにコピペするだけで表が自動的に埋まる便利な方法です。

Vlookup関数の列番号の部分に、MATCH関数を挿入しよう!

MATCH関数とは、目当てのデータが参考表の何列目にあるか数値でフィードバックしてくれるというもの。これをVlookup関数の列番号の部分に組み入れると、自動的に欲しいデータのある列を探し出して結果を表示してくれます。

例えば、
40

B3に埋め込む数式の列番号の部分に4と入力するかわりに、MATCH関数を入れておけば、「氏名」と書かれたB2のセルの項目が参考表(黄色の枠内、A12からi12のセル)の何列目にあるのか探し出し、社員名簿に「溝田優里」と表示してくれます。

数式を横のセルにコピペしていっても、列番号を変える必要が無いのがポイント!

では、具体的にどのような数式となるのでしょう。

図5

Vlookupの部分は前回説明した通りなので割愛しますが、とりあえず緑の枠で囲った「$A3」の部分の「$」を忘れないようにしましょう。(「絶対セル番地」を示す$のマークをつけることで、数式を他のセルにコピー&ペーストした際、直後の値が変わらないようにできます。)

こちらがMATCH関数の部分を抜き出した数式。定義は以下の通りです。

図6

ポイントは、?の値である「B$2」の「B」の部分に「$」マークを付けないこと。こうすることで、数式を右にコピペした時に、セルに合わせて探したい検索値が「C$2」、「D$2」と変化していきます。以下のようなイメージ。

図7

図8

数式を埋め込むセルに合わせて、赤い矢印で示した検索値が変わっていくのが分かるでしょう。

最後にできた数式を全てのセルにコピー&ペーストすれば完成!
図9

社員番号を入力していくだけで、自動的「氏名」「所属No.」「所属名」「エリア」「支店」が表示されるようになりました。

何も入力していない部分にはエラーコード「#N/A」が出る状態ですので、消したい場合はIF関数を組み込みましょう。方法は「意外に簡単!必ず効率がアップする人材管理に役立つExcel Vlookup術[vol.1]」をご覧ください。

関数を組み合わせれば、業務も圧倒的に効率化!

使い場所に悩むMATCH関数ですが、Vlookup関数と組み合わせることで、より効率的にデータが抽出できるようになります。一見数式は複雑になりますが、組む式は一つで良いので、手間もかかりません。

また一つ業務のスピードが上がったのではないでしょうか。次回もお楽しみに。

マネたまご マネたまをフォローすれば最新記事をお届けします!
運営会社 | Copyright © kaonavi, inc. All Rights Reserved.