データの検索と抽出に力を発揮するVLOOKUP関数ですが、元データが必ずしも転記先と同じ形で作られていないことがあります。
そんなときはMATCH関数と組み合わせてVLOOKUP関数の式を完成させて効率よくデータを抽出しましょう。
Excel関数の効率化にはデータの形を統一することが重要
Excelを効率よく使うコツの１つに、データの形式を揃えておくことが挙げられます。
仕事で使うエクセルのデータは、毎月同じものであることが多いです。
在庫管理であれば、縦に「商品コード」、
横に「受入日」「単価」「数量」「合計額」「仕入先」「払出日」「数量」「合計額」「売り先」などの項目を作り、
受払データをとって、それを毎月更新していくような方法です。
(このようなイメージ)
このような表を作るときに気をつけたいのが、形式を統一すること。
毎月作られる在庫データなどのファイルは、事前に集計するための式を組んでおいて、現場でカウントした集計結果のデータが届けば、それを貼り付けるだけで作業が完了するような状態にしています。
下の図を見て下さい。
赤枠で囲ったセルには、SUMIF関数が入力されています。
SUMIF関数は条件を満たすデータを合計する関数ですが、ここでは「”仕入明細”シートのG列を検索して、左のセル（B5）の社名と一致したら、同じ行のＦ列の数値を合計しなさい」という指示になっています。
ということは、”仕入明細”シートにはG列に”仕入先”が、F列には”仕入合計”が、来るように元データを貼り付けなければ、正しい集計ができなくなるのです。
このように、会社で毎月作られるようなファイルは、同じ形で元データを作ってくれることを前提に作業しているのが通常です。
形式が変わってしまうと、元々組んでいた式を変更することになり余計な手間がかかりますし、何よりミスの原因になります。
形式を統一しておけばそのようなリスクを避けられるので、みだりに変更しないようにしましょう。
横に長いデータを検索するのに便利な「MATCH関数」
これまで見てきたように、毎月作られるExcelファイルで一度式を入力してしまえば、記録された元データを貼り付けるだけで、作業が済んでしまうことになります。
それでも、最初にファイルを作るときは、そうも行きません。
正確さを期して、慎重に式を入力することになります。
VLOOKUP関数は式の要素が多く、1度で正確な式を組むのはなかなか難しいです。
特に、3番目に入力する「どの列のデータを表示させるか」を意味する列番号を決めるのが面倒です。
そんな時に便利なのが、MATCH関数です。
MATCH関数は、指定したデータが、指定した範囲の中で何番目にあたるかを教えてくれる関数です。
次の表を見て下さい。
指定するデータを”バルセロナ”、指定範囲を青枠で囲んだセルにします。
指定範囲の1番目のセル（左から数えます）は「B1セル」で、”バルセロナ”は「G1セル」なので列の順番で数えると6番目。
すると、MATCH関数の結果は”６”を表示するという仕組みです。
具体的にどのように入力しているかを見てみます。
MATCH関数の要素は３つ。
①は検索値（＝指定するデータ）です。
例に出した関数では「B1セル」になっていて、「B1セル」を見てみると”バルセロナ”が入っています。
これで「”バルセロナ”に一致するデータを見つけなさい」と指示していることになります。
②は指定範囲です。
”$B$2:$G$2”と入力されていて「B2セルからG2セルまでを範囲としなさい」と言う指示です。
赤枠で囲まれているところです。
③は検索の型です。
”０”が入力されていますが、これは「完全に一致した場合だけデータを表示しなさい」という指示です。
ここは、あまり気にせず”0”としておけばOKです。
①〜③を指定すれば、②の範囲で「左から数えて何番目に①と一致するデータが存在するか」を答えてくれます。
「VLOOKUP関数」と「MATCH関数」を組み合わせて使う
「MATCH関数」の仕組みが分かると、「VLOOKUP関数」の入力が効率的になります。
VLOOKUP関数についても少し説明しておくと、
=VLOOKUP(①$G20, ②$B$4:$M$16,③8, ④0)
このような式で表されて、検索条件に合うデータを抽出する関数です。
①は検索値（＝検索条件）です。
これに合うデータがあるかどうかを探します。今回は商品コードで検索します。
②検索範囲です。
この範囲を対象にして、検索値に合うデータがあるかどうかを探します。
検索するのは指定範囲の中で最も左に来る列で、上の式の場合はB列を検索することになります。
③は列番号です。
検索値と一致するデータが見つかったとき、②で指定した範囲の一番左の列から何番目の列のデータを表示するかを指定します。
上の式の場合は、一番左の列がB列で列番号が”８”。B列から数えて8番目の列ですから、I列のデータを抽出することになります。
④は検索の型です。
”０”は検索値と完全に一致した場合にだけ結果を表示せよ、と言う意味です。
以上を踏まえて、VLOOKUP関数の中にMATCH関数を無味合わせて使います。早速、入力した内容をを見て下さい。
ポイントは、MATCH関数の③−１検索値を”行だけを絶対参照（＝コピー＆ペーストしても、行だけは固定する方法）”で指定することです。
ここでは、VLOOKUP関数全体の説明は省き、組み込んだMATCH関数のみ説明していきます。
まず、ポイントとなる③−１の検索値に注目します。
入力内容は、「H$19」となっていて、列のHには”$”がなく19行目を示す”19”にだけ”$”がついています。
このように検索値を指定すると、このセルをコピー＆ペーストしたときに、行は19行目で固定されますが、列は移動範囲に合わせて変化します。
これが先述した”行だけを絶対参照にする”意味です。
たとえば、最初に入力したセルの右斜め下に、この式をコピー＆ペーストしてみます。
行は”19”のままですが、列が”I”となり、最初のセルの”H”から一つ移動していることがおわかりいただけると思います。
さらにMATCH関数が示す結果も見てみましょう。
H19セルに入力されているのが「払出日」、I19セルに入力されているのが「払出数量」ですから、
③-2で指定した「$B$4:$M$16」の範囲で何番目にあたるかを確認すると「払出日」が8番目、「払出数量」が9番目です。
VLOOKUP関数に組み込んだMATCH関数を抜き出して、それぞれのセルで表示させてみると
「払出日」が８、「払出数量」が9と表示されて、正しく列番号を指定しているのが分かります。
このようにして1つのセルで、表の先頭行にある項目に合わせて、検索値を指定するように、”行だけを絶対参照”にして入力できれば、
あとはコピー＆ペーストで正しく列番号を指定できるのです。
元データをにらみながら「”払出日”は左から8番目にあるから列番号は８」のように、列番号を数えて指定する必要がなくなるので、効率よく入力できます。
説明が前後しましたが、③−２は「検索範囲の指定」で、元データで項目を表示している、先頭行を指定します。
検索範囲はどのセルでも共通していますので、”行も列も絶対参照”になるように指定します（「$B$4:$M$4」となっていて、列を表す”B”、”M”の左、行を表す”4”の左に$がついていることを確認して下さい）。
③ー３は検索の型です。”０”として完全に一致する場合のみ表示するようにしています。
完全に一致するものがなければエラーとなります。
以上のようにして一つのセルにMATC関数を組み込んで、コピー＆ペーストしてできあがったのが次の表です。
項目ごとに列番号を指定しなくても、「MATCH関数」を使った「VLOOKUP関数」を入力して、コピー＆ペーストするだけで、表全体を仕上げることができました。
まとめ
Excelの関数は組み合わせて使うことで威力を増します。
テクニック知ることで発想も豊かになりますから、新しいExcel関数の技術をどんどん吸収して行きましょう。
おまけ
最近はアウトプットに偏った時間の使い方をしているので、インプットを増やせるように時間管理を見直す予定です。両方並び立って初めて効果的な仕事ができますからバランスには気を配っています。