Aさん レコード入力時、他の表のデータを転記するのを手作業で行うのは大変です。。
検索やコピペでなるべく手早くやっていますが、レコード数が多いと時間もかかりますし、たまに作業ミスしますし・・・。
もっと楽に早く転記する良い方法はありませんかね？
森田 その場合は、関数の「VLOOKUP」を活用すると良いですよ！
では、VLOOKUPの使い方について解説していきますね。
はじめに
この記事は関数の概要を把握していることが前提です。
関数の概要の詳細は以下の記事をご参照ください。
はじめに この記事は数式の概要を把握していることが前提です。 参考記事 数式の概要の詳細は以下の記事をご参照ください。 関数とは 関数とは、数式の構成要素の1つであり、固有の計算/処理の機能がセットされた数式のことです。 …
別表からのデータ転記を自動化したい場合は「VLOOKUP」が有効
実務でデータ集計/分析を行う際、1つの表でデータが足りるケースは案外少ないもの。
また、部署や商品等の基本情報を別表にまとめ、レコード入力の効率や精度向上を図ることも多いです。
こうした場合、別表のデータを元データの表側へ転記する作業（=データ転記）が必要となります。
このデータ転記は、別表の「主キー」を基準に別表のフィールド単位のデータを取得していきますが、手作業で行う場合は「検索→コピペ」をレコードの数だけ繰り返し行うことになってしまいます。
主キーとは、そのレコードが一意（重複していない）であることを示すためのコードや番号のこと（代表例として、社員番号や商品コードなど）。
よって、レコード数や転記作業の頻度が多ければ多いほど、工数もヒューマンエラーの発生リスクも比例的に高まります。
こんな場合、関数の「VLOOKUP」を使うことでデータ転記を自動化し、転記作業を迅速かつ正確に行うことが可能になります。
VLOOKUPは「ブイ・ルックアップ」と読む。
VLOOKUPを使うことで、指定した主キーに対応する転記対象のデータを一瞬でセル上へ転記できます。
VLOOKUPの方が人間よりも高速で処理できる上、チェック箇所も減るため、エラーの発生確率を下げることも可能です。
VLOOKUPの構文
VLOOKUPの構文は以下の通りです。
=VLOOKUP(検索値,範囲,列番号,[検索方法])
指定された範囲の1列目で特定の値を検索し、指定した列と同じ行にある値を返します。テーブルは昇順で並べ替えておく必要があります。
| 引数名 | 必須 | データ型 | 説明 |
|---|---|---|---|
| 検索値 | ○ | すべて | 「範囲」の範囲内で検索したい値を示す単一セルを指定します。 |
| 範囲 | ○ | 数値 | 検索する対象のテーブル/セル範囲を指定します。 ※左端が「検索値」の列であり、戻り値にしたい列を含む必要があります。 |
| 列番号 | ◯ | 数値 | 「範囲」の左端から何列目を戻り値にしたいか、整数を指定します。 ※左端は「1」 |
| 検索方法 | － | 論理 | 「近似一致」と「完全一致」のどちらで検索するか指定します。 ※TRUEまたは1：近似一致、FALSEまたは0：完全一致 |
引数「検索方法」を省略した場合、近似一致で検索される。
引数「検索値」が引数「範囲」の左端の列にない場合、エラー値「#N/A」が表示。
引数「列番号」が引数「範囲」の列数を超える数値の場合、エラー値「#REF!」が表示。
引数「列番号」が「1」未満の数値や文字列の場合、エラー値「#VALUE!」が表示。
【参考】引数「検索方法」は「完全一致」が基本
前述の通り、VLOOKUPの引数「検索方法」は省略できますが、実務では「完全一致」で検索することが大原則です。
よって、この引数は省略せず、「FALSE」か「0」のいずれかを指定すると覚えておきましょう。
なお、実務では近似一致で検索するケースはほぼありませんが、数値の表を参照した条件分岐処理に役立ちます。
実務で似たような作業を行うケースがあり、かつ完全一致の検索に慣れたら挑戦すること。
VLOOKUPの近似一致の詳細については以下の記事をご参照ください。
はじめに 本題に入る前に、この記事がおすすめな人を挙げてみます。 データの転記・集約作業が多い人 エクセルを使う頻度が高い人 事務職の人 ちなみに、最低限「VLOOKUP関数の基礎」を把握している想定での記事となります。 …
【参考】VLOOKUPは「検索/行列関数」
あくまで参考情報となりますが、VLOOKUPはリボン「数式」タブの関数ライブラリの「検索/行列」に分類されています。
実際にVLOOKUPを活用する際は、以下で解説しているように直接入力で挿入していきましょう。
VLOOKUPの使用結果イメージ
VLOOKUPを使い、データ転記を行うイメージは以下の通りです。
今回は「商品コード」列に対応する「商品マスタ」シートの「商品名」列の値を転記しました。
VLOOKUPを使う場合、1レコードにつき1つの関数を使います。
ベースの数式をセットしたら、他のセルへペーストしましょう。
なお、今回は「商品名」列以外に「単価」列も「商品マスタ」シートから転記したいため、D2セルの数式をコピペで使いまわせるように引数「検索値」は複合参照（列のみ絶対参照）にしています。
また、引数「範囲」はどのVLOOKUPの数式でも同じセル範囲（A～C列）で参照を固定するため、絶対参照にしています。
絶対参照/相対参照の詳細については以下の記事をご参照ください。
関数の数式を直接入力できるようになってきました！ もっと関数を実務で役立てていくために、他に覚えておいた方が良いことはありますか？？ …
【参考】同じ主キーで複数列の転記を行う場合、引数「列番号」を手修正する
VLOOKUPで同じ主キーを基準に、別表の複数列を転記したい場合、引数「列番号」のみ手修正が必要となります。
上記のイメージで言えば、ベースとなる数式（D2セル）を「単価」列の1レコード目となるE2セルへコピペし、引数「列番号」の部分を該当の整数へ書き換えて「Enter」キーで確定しましょう。
今回で言えば、「単価」列は「商品マスタ」シートの左端から3列目なので「3」にしています。
後は、この数式をコピーし、同じ列の他のセルへペーストしましょう。
同じ主キーで複数列の転記を行う際、1回のコピペで済むようにしたい場合、引数「列番号」へMATCHを組み合わせることで手修正が不要となります。
このテクニックの詳細は以下の記事をご参照ください。
はじめに この記事はVLOOKUPとMATCHの詳細を把握していることが前提です。 参考記事 VLOOKUPとMATCHの使い方の詳細は以下の記事をご参照ください。 VLOOKUPでの複数列の転記は引数「列番号」の手修正 …
【参考】別表は「マスタ」がおすすめ
転記するデータは、「マスタ」という表形式にすることをおすすめします。
マスタとは、管理対象のデータが一意にまとまった表のことです。
左端の列は主キーを用意。
今回の例で言えば、一意の商品データがまとまった「商品マスタ」のようなイメージとなります。
なぜ、VLOOKUPで参照する別表をマスタにすることを推奨するかと言うと、VLOOKUPの仕様上、別表の上から下へ主キーを検索しますが、最初にHITしたレコードが転記対象になります。
よって、別表に特定の主キーが重複しており、レコードによって転記対象のデータが異なる場合、一番上のものしか転記されないことになり、転記ミスの原因となります。
なので、そうしたリスクの回避するため、別表はマスタが最適なのです。
さらに、マスタの別表はテーブルにしておくと、VLOOKUPの数式の可読性が上がります。
また、テーブルにしておけば、マスタ側で適宜追加されたレコードがあっても、そのレコードもVLOOKUPの検索対象になるので、転記漏れのリスクがなくなります。
マスタをテーブルにしない場合は、同じようにマスタのレコード追加に対応できるよう、マスタのシートの列全体（A:C等）を指定しましょう。
テーブルの詳細については以下の記事をご参照ください。
はじめに 本題に入る前に、この記事がおすすめな方を挙げてみます。 Excelでデータの集計/分析作業を行うこと …
VLOOKUPの数式の挿入手順
上記の結果を得るための手順は以下の通りです。
- 関数を挿入するセルを選択
※今回はD2セル - 「=vl」等と入力
- サジェストから「VLOOKUP」を選択し、「Tab」キーで確定
- 主キーのセルを選択
※今回はC2セル（列のみ絶対参照） - コンマ（,）を入力
- 転記したいデータのシートへ移動
※今回は「商品マスタ」シート - 転記したいテーブル/セル範囲を選択
※今回はA～C列（絶対参照） - コンマ（,）を入力
- 転記したい列番号を入力
※今回は「2」 - コンマ（,）を入力
- 「FALSE」を選択 or 「0」を入力
※今回は「0」 - 「Enter」キーで確定
- 1行目の数式をコピーし、以降のセルへペースト
※今回はD3~D11セルへペースト
手順②の際にIMEを半角英数モードにすること。
テーブルの場合、手順⑬は不要（全レコードへ数式が自動的にコピーされる）。
【応用】VLOOKUPの困りごと「あるある」事例7選
VLOOKUPに慣れてくると、実務で遭遇する困りごとの「あるある」が大きく7つあります。
- VLOOKUPで同じ主キーで複数列の転記を行う際、1回のコピペで済ませたい！
- VLOOKUPの検索対象の別表にレコード追加した際、いちいち引数「範囲」を修正するのが面倒。。
- VLOOKUPの検索対象の別表の左端以外に主キーの列がある場合、どうしたら良い？
- 主キーを含め、VLOOKUPで2つ以上の条件で検索したい！
- VLOOKUPで主キーが検索対象の左端の列に確実にあるのに、なぜかエラーになってしまう。。
- 条件に応じてVLOOKUPの検索対象の別表を切り替えたい！
- VLOOKUPの数式がなぜかエラーになってしまう・・・。
実際に実務で困った際は以下の記事もご参照ください。
はじめに 本題に入る前に、この記事がおすすめな人を挙げてみます。 データの転記・集約作業が多い人 エクセルを使う頻度が高い人 事務職の人 ちなみに、最低限「VLOOKUP関数の基礎」を把握している想定での記事となります。 …
はじめに 本題に入る前に、この記事がおすすめな人を挙げてみます。 VLOOKUP関数を使う頻度が高い人 VLOOKUP関数に限らず、関数のエラー解消に困っている人 事務職の人 ちなみに、最低限「VLOOKUP関数の基礎」 …
サンプルファイルで練習しよう！
可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。
サンプルファイル_ワークシート関数_VLOOKUP.xlsx
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）
ブックを開いたら、次の手順を実施してください。（今までの解説のまとめです）
- 関数を挿入するセルを選択
※今回はD2セル - 「=vl」等と入力
- サジェストから「VLOOKUP」を選択し、「Tab」キーで確定
- 主キーのセルを選択
※今回はC2セル（列のみ絶対参照） - コンマ（,）を入力
- 転記したいデータのシートへ移動
※今回は「商品マスタ」シート - 転記したいテーブル/セル範囲を選択
※今回はA～C列（絶対参照） - コンマ（,）を入力
- 転記したい列番号を入力
※今回は「2」 - コンマ（,）を入力
- 「FALSE」を選択 or 「0」を入力
※今回は「0」 - 「Enter」キーで確定
- 1行目の数式をコピーし、以降のセルへペースト
※今回はD3~D11セルへペースト
本記事の解説と同じ結果になればOKです！
さいごに
いかがでしたでしょうか？
VLOOKUPは別表からのデータ転記の自動化に役立つ関数の一つです。
また、業界/業種問わず使用頻度が非常に高い関数であり、VLOOKUPを使えるか否かがExcelスキルを測る物差しになることも多いため、ぜひこの機会に習得することを強くおすすめします！
なお、VLOOKUP以外にもExcelでのデータ整形の各種テクニックを拙著で解説していますので、こちらも参考にしてみてください。
ご参考になれば幸いですm(_ _)m
森田 個人的にExcelにハマるきっかけになったのが、このVLOOKUPで思い入れも強いです。
この関数を用いて、複数のデータを一瞬で転記する様子はまさに「快感」でしたね。
1人でも多くの人にこの便利さを知ってほしいです。
なお、VLOOKUPとセットで覚えた方が良い関数は、特定のデータが左から何列目かを数値で返すことができる「MATCH」です。
また、VLOOKUP以上に柔軟なデータ転記が可能な「INDEX」や「XLOOKUP」もVLOOKUPに慣れたら挑戦してみてください。
