Excel で VLOOKUP を使用してデータをマッピングする方法 (4 つの簡単な方法)

マッピング データ Excel の不可欠な要素です .そのため、データをマッピングするための便利な方法をいくつか知っておく必要があります 多くの時間を節約し、ワークフローを改善できます。このことを念頭に置いて、この記事では 4 を示します。 データをマッピングする便利な方法 Excel で VLOOKUP .さらに、値の N 番目のオカレンスを取得する方法、および VLOOKUP を使用してエラーを非表示にする方法についても説明します。 関数。

以下のリンクから練習用ワークブックをダウンロードできます。

Excel で VLOOKUP を使用してデータをマッピングする 4 つの方法

この記事では、VLOOKUP を組み合わせます MATCH を使用した関数 、COUNTIF 、間接 、IF 関数 地図データへ .それでは早速、飛び込みましょう！
ここでは、Microsoft Excel 365 を使用しています。 必要に応じて他のバージョンを使用することもできます。

方法-1 :VLOOKUP 関数を使用して Excel でデータをマッピングする

最も明白な方法から始めましょう。つまり、VLOOKUP 関数を使用します。 Excel でデータをマッピングします。それでは始めましょう。
B4:D14 に示されているデータセットを考慮して 細胞。ここで、データセットは従業員 ID のリストを示しています の名前 、および 部門

歩数 :

• 最初に G5 に移動します セルに次の式を入力してください。

=VLOOKUP(G4,B5:D14,3,FALSE)

この式では、G4 セルは ID を参照します 1008 と B5:D14 セルの範囲は ID を表します 、名前 、および部門

数式の内訳:

• VLOOKUP(G4,B5:D14,3,FALSE) → テーブルの一番左の列の値を検索し、指定した列から同じ行の値を返します。こちら G4 ( lookup_value 引数) は B5:D14 からマッピングされます (table_array 引数) 配列。次、3 (col_index_num 引数) は、ルックアップ値の列番号を表します。最後に、FALSE (range_lookup 引数) は完全一致を指します ルックアップ値の.
  • アウトプット → マーケティング

最終的に、結果は以下の画像のようになります。

方法-2 :VLOOKUP および MATCH 関数を使用したデータのマッピング (双方向 VLOOKUP)

2 番目の方法では、VLOOKUP を結合します そしてマッチ 特定の行と列の交点に値をマップする関数。これは、双方向 VLOOKUP とも呼ばれます。 .それでは、実際に見てみましょう。
販売リストがあると仮定します B4:E12 に表示されるデータセット 細胞。ここにアイテムのリストがあります および 販売数 1月 、2 月 、および 3 月 .

歩数 :

• 最初に、アイテムを選択します そして月 、たとえば、テレビを選択しました そして3月 それぞれ。
• 次に、H6 に移動します セルに以下の式を入力してください。

=VLOOKUP(H4, B6:E10, MATCH(H5, B5:E5, 0), FALSE)

ここでは、H4 と H5 セルは 項目 を参照します と 月 それぞれ B5:E5 列ヘッダーを表します。

数式の内訳:

• MATCH(H5, B5:E5, 0) → 指定された値に一致する配列内の項目の相対位置を返します。ここで、H5 lookup_value です 3月を参照する引数 .続いて、 B5:E5 lookup_array を表します 値が一致する引数。最近、0 オプションの match_type です 完全一致を示す引数 基準.
  • アウトプット → 4
• VLOOKUP(H4, B6:E10, MATCH(H5, B5:E5, 0), FALSE) →
  になる
  • VLOOKUP(H4, B6:E10, 4, FALSE) → ここでは、 H4 ( lookup_value 引数) は B6:E10 からマッピングされます (table_array 引数) 配列。次、4 (col_index_num 引数) は、ルックアップ値の列番号を表します。最後に、FALSE (range_lookup 引数) は完全一致を指します
  • 出力 → 243

最後に、結果は下の図のようになります。

方法-3 :VLOOKUP および COUNTIF 関数を使用してデータをマッピングする

データをマッピングする別の方法 Excel に COUNIF 関数を組み込むことです。 VLOOKUP 内 関数。シンプルで簡単です。次の手順に従うだけです。
ベストセラーの本について考えてみましょう B4:C11 に表示されるデータセット 細胞。このデータセットには、ベストセラーの名前があります とその価格 はそれぞれ米ドルです。

歩数 :

• まず、F5 に移動します セルに次の式を入力してください。

=IF(COUNTIF(B5:B9,F4),VLOOKUP(F4,B5:C9,2,TRUE),0)

この式では、B5:C9 セル範囲はベストセラーブックを表します と 価格 列。対照的に、F4 セルはベストセラーを指します (ここでは、House of Wisdom です )

数式の内訳:

• COUNTIF(B5:B9,F4) → 指定された条件を満たす範囲内のセルの数をカウントします。こちら、B5:B9 範囲です ベストセラー書籍を参照する引数 .続いて F4 基準を表します 一致した値の数を返す引数。
  • アウトプット → 1
• VLOOKUP(F4,B5:C9,2,TRUE) → ここで F4 ( lookup_value 引数) は B5:C9 からマッピングされます (table_array 引数) 配列。次、2 (col_index_num 引数) は、ルックアップ値の列番号を表します。最後に、TRUE (range_lookup 引数) は近似一致を指します ルックアップ値の.
  • アウトプット → 25
• IF(COUNTIF(B5:B9,F4),VLOOKUP(F4,B5:C9,2,TRUE),0) →
  になる
  • IF(1,25,0) → 条件が満たされているかどうかをチェックし、 TRUE の場合は 1 つの値を返します FALSE の場合は別の値 .ここに 1 logical_test です IF を促す引数 25 を返す関数 (value_if_true 引数) それ以外の場合は 0 を返します (value_if_false 引数）
  • アウトプット → $25

その結果、結果は以下のスクリーンショットのようになります。

方法-4 :Excel で VLOOKUP および INDIRECT 関数を使用してデータをマッピングする

データをマッピングすることもできます INDIRECT を組み合わせて Excel で と VLOOKUP 機能。それでは、手順を見ていきましょう。
食料品リストがあるとしましょう B4:I10 に示されているデータセット 細胞。データセットは価格を示しています 同じアイテムの 3 で 米国の都市 .

歩数 :

• まず B5 に行きます セル>> データをクリック タブ>> 次に Data Validation を押します ドロップダウン。

データ検証が開きます ダイアログ ボックス。

• 次に、許可 ドロップダウンでリストを選択 ソースのオプション>> フィールドで、B6:B10 を選択します セル>> OK をクリックします ボタン。

• 3 番目に、B6:C10 を選択します セル>> 数式に移動 タブ>> 名前の定義をダブルクリックします オプション

名前の編集が開きます

• ここに適切な名前を入力します (この場合、Boston ) データ範囲>> OK をクリック ボタン。

同様の方法で、名前付き範囲を定義します アトランタ .

同様に、名前付き範囲を定義します デンバー .

• 4 番目に、C14 に移動します セル>> データ タブ をクリックし、[データ検証] をクリックします。 ボタン。

• 同様に、リストを選択します オプション>> 名前付き範囲 を入力します 以下のスクリーンショットに示すとおりです。

次に、 アイテム を選択します 場所 ドロップダウンから。たとえば、Tomato を選択しました そしてアトランタ

• その後、D14 で 次の式のセル タイプ

=VLOOKUP(B14,INDIRECT(C14),2,FALSE)

こちらはB14 とC14 セルはアイテムを指しています と場所

数式の内訳:

• 間接(C14) → テキスト文字列で指定された参照を返します。こちらC14 ref_text です 名前付き範囲を参照する引数 ボストン .
• VLOOKUP(B14,INDIRECT(C14),2,FALSE) → こちら B14 ( lookup_value 引数) は 名前付き範囲 からマッピングされます。 間接(C14) それが table_array です 口論。次、2 (col_index_num 引数) は、ルックアップ値の列番号を表します。最後に、FALSE (range_lookup 引数) は完全一致を指します
• アウトプット → $1.2

その後、上記の手順を完了すると、結果は以下の図のようになります。

さらに、複数のワークシートにデータがある場合にも、この方法を使用できます。 マッピングしたい ルックアップに基づく特定のワークシートから

VLOOKUP 関数を適用して N 番目のオカレンスを取得する

文房具の販売があるとします B4:D13 に示されているリスト 下のセル。ここで、同じ顧客が 2 番目または 3 番目に購入した商品を知りたいとします。この問題を解決するために、COUNTIF を使用できます。 と VLOOKUP 結果を取得する関数。それでは、プロセスを詳しく見てみましょう。

歩数 :

• まず、列 B に列を挿入します ヘッダーの名前を Helper Column に変更します>> B5 セルに以下の式を入力してください。

=C5&COUNTIF($C$5:C5, C5)

📄 注: デフォルトでは VLOOKUP 関数は左から右に見えます。

こちらがC5 セルは名前を参照しています ジョン と COUNTIF 関数は John の出現をカウントします 指定された範囲 $C$5:C5 から .最後に、アンパサンド (&) 演算子はテキストと数値を結合します。

• 次に、名前を入力します とインスタンス 、たとえば、ここでは Julie です 、3>> H6 へ セルに次の式を入力してください。

=VLOOKUP(H4&H5, B5:E13, 3, FALSE)

この式では、H4 と H5 セルは名前を示します とインスタンス .

上記の手順を完了すると、結果は次のスクリーンショットのようになります。

VLOOKUP 関数と IF 関数を使用して #N/A エラーを非表示にする

ISNA を使用できます と VLOOKUP IF 関数の関数 #N/A を非表示にする 無効なルックアップ値の場合のエラー 与えられます。それでは、以下のプロセスを実演させてください。

歩数 :

• 最初に G5 に移動します セルに次の式を入力してください。

=IF(ISNA(VLOOKUP(G4, B5:D14,3,FALSE)), "",VLOOKUP(G4, B5:D14,3,FALSE))

この式では、G4 セルは ID を参照します 1008 と B5:D14 セルの範囲は ID を表します 、名前 、および部門

数式の内訳:

• ISNA(VLOOKUP(G4, B5:D14,3,FALSE)) → 値が #NA かどうかを確認します 、そして TRUE を返します または FALSE .こちら G4 ( lookup_value 引数) は B5:D14 からマッピングされます (table_array 引数) 配列。次、3 (col_index_num 引数) は、ルックアップ値の列番号を表します。最後に、FALSE (range_lookup 引数) は完全一致を指します ルックアップ値の.
  • 出力 → FALSE
• IF(ISNA(VLOOKUP(G4, B5:D14,3,FALSE)), “”,VLOOKUP(G4, B5:D14,3,FALSE)) →
  になる
  • IF(FALSE, “”,VLOOKUP(G4, B5:D14,3,FALSE)) → 条件が満たされているかどうかをチェックし、 TRUE の場合は 1 つの値を返します および別の値の場合。ここでは、 FALSE logical_test です IF を促す引数 VLOOKUP から値を返す関数 関数 (value_if_true 引数) それ以外の場合は空白を返します “” (value_if_false 引数）
  • アウトプット → HR

最終的に、下の図に示す結果が得られるはずです。

これに加えて、無効な ID を入力すると、 番号 (ID 1012 )、式は #N/A の代わりに空白の値を返します 以下のスクリーンショットに示されているエラーです。

練習セクション

プラクティスを提供しています 各シートの右側にセクションがあるので、自分で練習できます。必ずご自身で行ってください。

結論

Excel でデータをマッピングする方法に関するすべての方法を希望します VLOOKUP を使用 では、Excel スプレッドシートにそれらをより効果的に適用するように求められます。ご質問やご意見がございましたら、コメント欄でお知らせください。または、この Web サイトで Excel 関数に関連する他の記事を確認することもできます。