Microsoftが提供する「Excel」は、国内の9割以上の企業や自治体が導入する表計算ソフトで、ビジネスの現場で最も広く使われているツールの一つです。

計数管理やデータ分析、レポートの作成など、情報を定量的にコミュニケーションする共通言語として、社内外問わず多岐にわたる業務で活用されています。そのため、Excelの使い方を多くの社員がなるべく早いうちに身に付けておくことが、組織全体のIT基礎力向上につながることは間違いありません。

一方でExcelは、その機能の多さや"計算"・"分析"という難しいテーマで用いられる性質から、Excelに不慣れな方にとっては習得までに一定の時間がかかることも事実です。

そこでこの記事では、Excelを実務で求められるスキルに絞って解説します。
否が応でもExcelを使わざるを得ない環境にいる方はもちろん、社員の皆さんにスキルを届ける立場の人事研修担当の方や先輩社員にとっても、「どこからどんな順番で伝えればよいのだろう？」という疑問を解消できる内容ですので、ぜひご一読ください。 

↓本記事では、下記動画の第3章「現場でVLOOKUP関数をとことん使い倒す」テーマを中心に取り上げます↓

登録者40万人以上のYouTubeメディア「ユースフル／スキルの図書館」を運営するYouseful株式会社では、Excelスキルの習得を通じた「数字を読む力」や「データリテラシー」の向上を推進するためのeラーニングコンテンツの提供や個社研修を承っています。

はじめてExcelに触れる新入社員様向けの初級講座から、高度なデータ活用にコミットした実務直結の徹底活用コースまで、現在非常に多くのクライアント企業様からお引き合いをいただいております。
講師の数にも限りがありますので、まずはお気軽に無料相談フォームよりお問い合わせください。

＞詳しい資料のダウンロードはこちら

本記事は日々の業務で一定程度Excelに触れたことのある方向けに解説しています。もしはじめてExcelを使う方は、入門編の知識とテクニックをご紹介したコンテンツをご覧になってから本記事を読んでいただくと、より理解が進みやすくなります。

＞初心者向けExcel超入門テーマはこちら

それでは、早速始めていきましょう。

Day.17｜VLOOKUP関数の基本

VLOOKUP関数は、データを垂直（縦方向）に調べる関数です。
皆さんが日常生活の中で名簿を受け取ったとしましょう。左端に並んだ氏名のリストを上から順に眺めていき、ご自身の名前を見つけたら視線を横方向に移して所属グループや担当エリアといった情報を拾いにいきますよね。

VLOOKUP関数もこれと同じ動きをしてくれるのです。
この役割を機能してくれるためにVLOOKUP関数へ与える要素は大きく3つです。実際のサンプルを見ながら具体的な使い方を見ていきましょう。

VLOOKUP関数の使い方

海産物の情報が蓄積されたマスターデータから、商品名を指示したらその仕入値を取得するケースを取り上げます。今回は数式を以下のように入力します。

=VLOOKUP(G3,$B$2:$E$27,4,0)

• 第１引数【検索値】｜検索するデータを指示します。 
• 第２引数【範囲】｜データをどの範囲から探してくるかを指示します。基本的にはマスターデータのセル範囲を参照します。※このとき、検索値を探す列が範囲の左端にくるように参照することがポイントです。
• 第３引数【列番号】｜マスターデータから検索値が見つかったら、その検索値に関するどの情報を取得するかを数値で指示します。検索値が位置する列を含めて、左から●番目の列にある情報を返すかを指定するのです。 

ここまで指示した要素により、VLOOKUP関数が機能します。
また最後に、検索方法を設定できます。

• 第４引数【検索方法】｜マスターデータにおいて検索値と完全に一致する値を取得するか、近しい値を取得するかを指示します。完全に一致する値（完全一致）はFALSE、近しい値（近似一致）はTRUEを適用します。VLOOKUP関数では完全一致のFALSE（または「0」）とする場合が多いです。

このステップを踏むことで、Excelは「ぶり」の仕入値をリストから探し出し、数式を入力したセルに表示します。結果、"552円"という仕入値が得られます。

これにより商品ごとの仕入値を一瞬で確認でき、数量をかけて調達コスト合計などを簡単に求めることができます。商品名を変更するだけでその商品の情報も連動して出力されるため、大量の商品データを集計するときの作業効率が大幅に向上するのです。 

それでは、VLOOKUP関数の基本的な使い方をおさえたところで、この関数の効力を実務で最大限発揮する様々な機能を一気にご紹介していきます。

Day.18｜IFERROR関数でエラー対策

VLOOKUP関数で検索した値がマスターデータに存在しなかった場合はエラーが返ります。
こうしたエラーをそのまま残しておくと、その後工程で上手く計算ができなかったり資料の見栄え悪くなったりしてしまいます。このような事態を想定して、あらかじめVLOOKUP関数をIFERROR関数に入れ込んでおくことをおススメします。

IFERROR関数の使い方

IFERROR関数の構成は非常にシンプルで、【=IFERROR(値, エラーの場合の値)】と記述するだけで済みます。第1引数の値には前述で入力したVLOOKUP関数をそのまま適用すればよいので、今回は以下のように書き上げることになります。

①エラー時に"商品名が違います"と返す場合

=IFERROR(VLOOKUP(G3,$B$2:$E$27,4,0),"商品名が違います")

②エラー時に空白を返す（何も表示しない）場合

=IFERROR(VLOOKUP(G3,$B$2:$E$27,4,0),"")

エラーメッセージを表示せずワークシート上でデータをキレイに表現するために、空白を返す処理が一般的です。

結果、ひらがなで"えび"と検索すべきところを誤って"海老"入力してしまった場合でも、エラーが返らずに空白が表示されます。サンプルの場合は後工程で費用合計の計算をしているため、その数式にもIFERROR関数を適用することで表の見栄えを整えることができます。

Day.19｜ドロップダウンリストで入力ミス防止

IFERROR関数によるエラー対策はVLOOKUP関数に限らずExcel仕事の広範囲で有効なアクションですが、そもそも管理者が指定したデータしか入力できないようにしておければ便利です。
誤入力や検索値の不一致などを発生させない仕組みづくりとして、入力規則（ドロップダウンリスト）機能をご紹介します。

入力規則の使い方

VLOOKUP関数で参照している検索値（商品名）をリストから選択できるように設定していきます。具体的な手順は以下の通りです。

1. ドロップダウンリストを適用したい範囲を選択する。
2. 「データ」タブ→「データツール」グループ→「データの入力規則」を選択する。
3. 入力値の種類を「リスト」に変更する。
4. 元の値にリストから選択する項目が入力されている範囲を選択する。※今回の場合はマスターデータにおける商品名の範囲。

結果、選択していた範囲にドロップダウンリストが表示され、指定した項目（商品名）から検索値を選ぶことができます。

このドロップダウンリストは、セルの右下に表示される下向き三角マークをクリックするか、キーボードの【Alt】+【↓】を押すことで開けます。

これによりデータ入力時の手間が省け、たとえ複数のメンバーがアクセスするファイルでも入力ミスを防ぐことができるのです。

Day.20｜テーブルで参照範囲を自動拡張

VLOOKUP関数の第2引数に参照している範囲には、日を追うごとにデータが追加されていきます。通常のセル範囲ではその都度、参照先の数式を手直しする必要がありますが、範囲にテーブルを設定することで、新しいデータが追加されても自動的に範囲を拡張できます。

テーブル化

サンプルの商品マスターデータにテーブルを設定します。テーブルは以下の手順で適用します。

1. テーブルを設定したい範囲を選択する。
2. キーボードの【Ctrl】+【T】を押す。 
3. 対象範囲がしっかり指定されていることと、"先頭行をテーブルの見出しとして使用する"にチェックが入っていることを確認したら「OK」で決定する。

これにより、表がテーブル形式に変換されます。

テーブルのテクニック

テーブルを適用したら、以下の要領でテーブル名を設定した上でVLOOKUP関数を更新します。

1. テーブル範囲のいずれかのセルをアクティブにする。
2. 「テーブルデザイン」タブ→「プロパティ」グループ→テーブル名に任意の名前をつける。※このとき、"PriceList"など英語で名前を付けておくと、"p"まで入力したときに選択肢として表示されるオートコンプリート機能が使える。
3. VLOOKUPの第2引数に、そのテーブル名を入力する。 

 結果、たとえ新しいデータが追加されたとしても、VLOOKUP関数はそのデータも対象範囲と認識して追いかけていってくれるのです。

Day.21｜COLUMN関数で列番号を自動調整

VLOOKUP関数の第3引数（列番号）に数値を入力すると、関数を横方向へコピーしても同じ列になる情報を拾ってしまったり、列の追加・削除など表が加工されたときに参照する列がズレてしまったりします。
こんなときはベタ打ちの数値を都度手直しするのではなく、COLUMN関数を使って列番号が自動調整される仕組みが理想的です。

COLUMN関数の使い方

COLUMN関数は、参照セルの列番号を数値で返す関数です。A列は「1」、B列は「2」といったイメージです。VLOOKUPの第3引数に組み込む場合は、以下のように記述します。

=VLOOKUP($B10,$B$3:$F$7,COLUMN(C$2)-COLUMN($A$2),0)

ポイントは【COLUMN(C$2)-COLUMN($A$2)】の部分です。
サンプルでは表がB列から始まっているため、C列「3」からA列の「1」行分を差し引きます。A列（A2セル）は絶対参照にして固定します。

これにより、数式を入力したC列のVLOOKUP関数においては「2」が列番号として認識されます。数式を横方向にコピーした場合、D列「4」からA列の「1」行分を差し引けば「3」が列番号に自動更新されるということです。

結果、それぞれの列で取得したいデータを正しく表示してくれます。

Day.22｜近似一致検索

これまでVLOOKUP関数の第４引数には「FALSE」または「0」を指示して完全一致検索を行ってきましたが、ここでもう一つの検索方法のユースケースにも触れておきます。近似一致という検索方法です。 

近似一致検索の使い方

サンプルは、当月の売上実績をもとにした歩合制の給与テーブルです。売上金額ごとに設定された基準値から、翌月の歩合給を判定します。

数式の修正点としては、これまで「FALSE」または「0」と入力していた第４引数を、「TRUE」または「1」に置き換えるだけです。

これにより、たとえば売上が300,000円の担当者であれば、基準額100,000～1,000,000円に対応する歩合給50,000円が適用されるのです。

 
■「完全一致」と「近似一致」の違い 

「完全一致」が検索値と完全に一致するデータのみを対象にするのに対して、「近似一致」は検索値に最も近い値を持つデータを見つけ出すという根本的な違いがあります。検索したいデータの性質によって使い分けましょう。

Day.23｜別シートから範囲を参照

本記事のサンプルは、同一画面で解説が完結するよう、VLOOKUP関数を入力する表と検索対象のマスターデータを同じワークシート上に配置しています。

ただし実務においては、いずれか一方のデータが削除されたり、入力されているデータの文字数が異なるのに列幅を揃えなくてはならなかったりと、双方が同じワークシートに存在していると様々な不都合が生じます。

このようなケースでは、マスターデータや分類表を同じファイルの別のシートに構えておき、そこから検索対象範囲として参照することをおススメします。

別シートの範囲を参照する

前述の近似一致検索で使用した歩合給決定表を別のシートに配置し、VLOOKUP関数の第２引数として参照してみます。

=VLOOKUP(C4,VTable!$B$4:$D$9,3,1)

第２引数の範囲が「VTable!$B$4:$D$9」のように、シート名に「!」マークがついた表記になります。これが、別シートのデータを参照している状態です。

同じファイルの別シートから参照するメリットは以下の通りです。

• ソースデータのシートを分けることで情報が整理され、メインのワークシートがすっきりして可読性が高まる。
• いずれかの表を加工しているときに、誤って情報を変更したり削除したりしてしまうリスクを低減できる。
• 別シートにデータを集中させることで、そのデータを更新するだけで関連する全ての情報が自動的にアップデートできる。

Day.24｜検索値に文字列操作関数を応用する

文字列操作関数には、長い文字列から必要なデータや情報を取り出す役割を持っている関数があります。基本的なものとしては以下の４つです。

これらの関数をVLOOKUP関数の第１引数に組み合わせると、複雑なデータセットから特定の情報のみを取得できるため、検索の幅が広がります。

文字列操作関数の応用

従業員IDの先頭２文字にあたる基準コードを検索して、該当の給与金額を取得したいとなったとします。
左から２文字分を照合する必要があるため、ここではLEFT関数を使います。

=VLOOKUP(LEFT(B3,2),$E$2:$F$5,2,0)

結果、従業員IDから先頭２文字分を取り出したデータを検索値として活用してくれます。

（参考）文字列操作関数と文字列操作関数の組み合わせ

実務では、取得したいデータの開始位置や文字数が異なるケースも多くあります。
たとえば文字数がバラバラな従業員IDの中間にある４ケタの値を取得したいとなったときには、以下の要領で指示します。

=MID(B3,FIND("-",B3)+1,4)

FIND関数で"-"が左から何番目に位置しているかを調べ、それに+1した値をMID関数の開始位置として応用すれば、それぞれの４ケタの値を取得できるということです。

VLOOKUP関数で複雑な管理コードを検索しなければならないシーンで、ぜひ試してみてください。

Day.25｜重複データの克服

名簿や顧客リストを管理していると、「田中」…「田中」など同じ名前や値が繰り返し登場することがあります。
VLOOKUP関数は検索値を検索対象範囲の上から順に調べていき、最初に見つかったデータのみを取得する性質があるため、重複するデータが存在している場合に期待した情報を取得できなくなります。

このようなケースでは、COUNTIF関数を使ってデータをナンバリングする手法が効果的です。

COUNTIF関数の使い方

サンプルは従業員マスターデータです。名前の列には重複するデータが含まれているとします。（実際に、田中さんや山田さんが複数登場します） 
こんなときは作業列を追加して、先頭D4セルに以下の数式を入力します。

=COUNTIF($C$4:C4,C4)

COUNTIF関数は第１引数に参照した範囲の中に、第２引数で指定した値がいくつ含まれるかを数える関数です。第１引数の範囲の先頭のみを絶対参照にすることで、この数式を下にコピーしたときにカウント対象範囲が拡張されていきます。

結果、"その時点"で特定の氏名が何回登場するかを返してくれます。上から見ていったときに、初めて登場した山田さんには「1」、2回目に登場した山田さんには「2」を表示する、といったイメージです。

あとは名前の列と作業列でナンバリングした値を左端のID列で結合すれば、ユニークな従業員情報が生成でき、VLOOKUP関数で求めていた個人の情報を取得することができるのです。

Day.26｜INDEX関数×MATCH関数

ここまで学習してきたVLOOKUP関数は、検索値が検索対象範囲の左端列に配置する必要があったり、検索値が見つかっても右側のデータしか取得できないといった制約がありました。

しかし実務では、検索値の左側のデータを拾いたいケースも多くあります。このようなシーンで、効果的に情報を抽出してくれる２つの関数をご紹介します。この関数はセットで組み合わせて使うと絶大な効力を発揮します。

INDEX関数の使い方

最初に演習するINDEX関数は、参照範囲において指定した行番号と列番号が交差する位置にあるデータを取得する関数です。都市間の距離をまとめたマトリクス表をサンプルに、実際のユースケースを見ていきましょう。

ここでは、大阪－福岡間の距離を求めます。

=INDEX(B2:I9,4,6)

参照した範囲の先頭に位置するB2セルから数えて、調べたい項目がそれぞれ行方向と列方向で何番目にあるかを確認してみます。
縦軸の見出しにおいて大阪は上から「4」行目にあり、横軸の見出しにおいて福岡は左から「6」列目にあります。これらの情報を、INDEX関数の第２引数と第３引数に入力します。

結果、G5セルにある"610"Kmが返ります。

ただし、行番号と列番号を固定値で指定してしまうと、調べたい項目が変わったときに数式を都度手直しする必要があります。この課題を解消してくれるのが、MATCH関数というわけです。

MATCH関数の使い方

MATCH関数は、検索値が検索範囲において何番目に位置するかを数値で返す関数です。
先ほどINDEX関数を記述するときに大阪や福岡の位置を目視で確認しましたが、この動作をMACTH関数が担ってくれるのです。

MATCH関数をINDEX関数の第２引数と第３引数に入れ込むと以下のような構成になります。

=INDEX(B2:I9,MATCH(C11,B2:B9,0),MATCH(C12,B2:I2,0))

※MATCH関数の第３引数「照合の種類」については、完全一致の「FALSE」または「0」に設定することがポイントです。

INDEX関数単体で取得したのと同じく、G5セルにある"610"Kmが返ります。

調べたい都市名もセル参照で記述することで、出発地や目的地が変更になった場合でも検索結果がダイナミックに更新される仕組みを構築できるのです。

まとめ 

いかがでしたか？
今回はExcel実務活用の第一歩として、VLOOKUP関数の効力を最大化する便利なテクニックをまとめてご紹介しました。初学者の方はもちろん、Excelを既に一定程度使える方も、重要な基本スキルが身に付いているか、改めて本記事でおさらいしておきましょう。

一方で、Excelはあくまで表計算を行うための手段であり、ツールそのものをスピーディーに使いこなすこと自体にはあまり意味がありません。
それよりもまず、ものごとを主観的な感想ではなく客観的事実として「数字」で説明することを習慣づけ、全員が定量的なデータ（＝共通認識・目標）をもとに一丸となって事業の成長を目指していくことが、ビジネスパーソンとしての価値向上、ひいては企業組織の競争力向上につながるのです。

• 「現場で求められるExcelスキルを、組織にどう浸透させれば良いかわからない」
• 「社員の成長に伴走してくれる事業者を探している」
• 「実務に役立つ学びを大人数に提供したい」
• 「予算内で効果のある研修を実施したい」

といったお悩みを持たれている方は、ぜひ無料相談フォームへお問い合わせください。

ユースフルでは、Microsoft×AI領域の実務活用に長けたコンサルタントチームが、貴社の課題や状況にあわせて最適な研修をご提案しております。実施可否が決定していなくても構いませんので、お気軽にご相談ください。

＞ 詳しい資料のダウンロードはこちら