エクセルの商品マスタ作成を自動転記で入力効率化&検索100万件でも楽々

エクセルの商品マスタ作成を自動転記で入力効率化&検索100万件でも楽々

エクセルの商品マスタの管理運用をラクにしたい!

商品マスタを作るとき
■ 自動転記でラクしたい
■ 入力ミスや表記揺れを無くしたい
■ 変更点を一括更新したい
商品を検索するとき
■ 膨大な点数でも簡単に取り出したい
■ 変更点をリアルタイムに更新したい
■ 商品マスタをサーバーにおいて共有したい

この記事ではエクセルの商品マスタの管理をVLOOKUP関数も用いて効率化する方法を解説します。
VLOOKUPのメリットや注意点もあわせて参考にしてください。

本文中の解説図はクリックで拡大表示します。別途ページでPDFファイルも参照可能です。
≫ PDFファイル参照ページはこちら

【商品マスタを作る】

解説内容詳細へ
自動転記の仕組みを理解したい2-1.
基本的な数式を作りたい2-2.
転記する項目を増やしたい2-3.
別ファイルから転記したい2-4.
仕入先の件数を増やしたい2-5.
作成時にVLOOKUPを使う
メリットを知りたい
3章
作成時にVLOOKUPを使う
注意点を知りたい
4章

【商品を検索する】

解説内容 詳細へ 
検索する仕組みを理解したい5-1.
基本的な数式を作りたい5-2.
別ファイルを検索する数式を作りたい5-3.
数式をカスタマイズしたい5-4.
検索でVLOOKUPを使う
メリット 知りたい
6章
検索でVLOOKUPを使う
注意点 知りたい
7章

この記事を読めば、商品マスタ作成時の単純入力作業を大幅に削減できます。完成後も社内で共有し有効活用してください。
そうすればよりクリエイティブな仕事に時間をまわせるようになります。時短にもつながり、残業を減らして早く帰れることでしょう。

VLOOKUPを基礎から応用まで完璧にマスターしたい方は、次の記事も参照ください。
≫ 超図解!VLOOKUP|基礎から応用まで完璧マスター&便利技20選

マイクロソフト公式サイトの解説を参照したい方はこちら
≫ Microsoft|Officeのサポート~VLOOKUP関数

1. VLOOKUPの基礎知識と処理のイメージ

VLOOKUPとは、指定した範囲から特定の値を探し出し、紐づくデータを返す関数です。

処理の流れをイメージすると下図のようになります。

エクセルVLOOKUPの使い方【商品マスタ編】VLOOKUPの処理のイメージ
VLOOKUPの処理の流れのイメージ(画像はクリックで拡大)

≫ 解説図のPDFファイル

【VLOOKUPの処理のイメージ】
検索値を所定のセルに入力
検索値と一致するものを、検索範囲の最左列を上から順に探す
検索値と一致する値を発見!
その行の左から○列目を返す(○は式で指定)
検索結果として表示

VLOOKUPの数式は次のとおりです。

=VLOOKUP( 検索値 , 検索範囲 , 列番号 , 検索方法 )

項目 内容 入力例
検索値 データを探し出す手掛かりとなる値 A1
検索範囲 探したいデータが入っている表 A:D (列全体で指定)
A1:D4(長方形で指定)
列番号 左から何列目を返すか 2
検索方法 一致した場合のみ返すか、近似値でも返すか FALSE(完全一致)
TRUE(近似値)

各項目の基礎知識についてさらに詳しく知りたい方はこちら
≫ VLOOKUPの使い方|3.VLOOKUPはどうやって使うの?|各項目の解説

マイクロソフト公式サイトの解説を参照したい方はこちら
≫ Microsoft|Officeのサポート~VLOOKUP関数

2. エクセルの商品マスタをラクに作れるVLOOKUPの使い方

この章では、エクセルの商品マスタを作る際に、VLOOKUPの自動転記を使ってラクにする方法を解説します。お急ぎの方は下記チャートより、目的に応じたリンクを辿ってください。

目的別チャート詳細へ
自動転記の仕組みを理解したい2-1.へ
基本的な数式を作りたい2-2.へ
転記する項目を増やしたい2-3.へ
別ファイルから転記したい2-4.へ
仕入先の件数を増やしたい2-5.へ

2-1. 仕入先名が自動転記される仕組み

仕入先コードを手掛かりに仕入先名が自動転記される仕組みは、次のとおりです。

エクセルVLOOKUPの使い方【商品マスタ編】仕入先が自動転記される仕組み
仕入先名が自動転記される仕組み(画像はクリックで拡大)

≫ 解説図のPDFファイル

【VLOOKUPを用いた自動転記の流れ】
仕入先コードを所定の欄に入力する
仕入先リストから一致する仕入先コードを探す
見つけたコードに対応する仕入先名を返す
所定の欄に仕入先名が自動転記される

仕入先名の一番上のセル「H2」の数式は次のとおりです。

【セル「H2」の数式】
=VLOOKUP(G2,A:B,2,FALSE)

G2 仕入先コードを入力するセル
A:B 仕入先リストの範囲(※1)
2 2列目のデータを返せ
FALSE 一致する場合のみ返せ(※2)

(※1) A~B列の最下行まで検索範囲に含まれます。
指定方法には、長方形で囲む方法もあります。
≫ 範囲指定の方法について詳しく知りたい方はこちら

(※2) 「TRUE」にしてしまうと一致するコードがなくても、近い値を見つけて返してしまいます。仕入先の転記間違いを防ぐためにも、「FALSE」を選びましょう。
≫ 「FALSE」「TRUE」の選び方について詳しく知りたい方はこちら

2-2. 基本的な数式の作り方

エクセルVLOOKUPの使い方【商品マスタ編】仕入先コードを入力すると仕入先名が自動転記される数式の作り方
仕入先コードを入力すると仕入先名が自動転記される数式の作り方(画像はクリックで拡大)

≫ 解説図のPDFファイル

この章では、商品マスタに仕入先名が自動転記される数式の作り方を解説します。

数式の作成手順
商品マスタのG列に仕入先コードを入れるとH列に仕入先名が自動転記される数式

商品マスタの仕入先名の一番上のセル「H2」に作成する数式は、次のとおりです。

仕入先名の一番上のセル「H2」の数式
=VLOOKUP(G2,A:B,2,FALSE)

仕入先リストと商品マスタが同じシートにあるシンプルなパターンです。
別シートや別ファイルにする場合はこちら ≫ 2-4.

(1) 数式を作成するセルを選択し関数作成用のツールを開く

数式の作成手順

数式を作成するセルを選択し、メニュー欄にある「fx」をクリックします。

(2) VLOOKUP関数を検索して呼び出す

数式の作成手順

「関数の挿入」ダイアログが開くので、「関数の検索」の欄に「VLOOKUP」と入力し、検索して呼び出します。

(3) 仕入先コードを入力する場所を指定する

数式の作成手順

「関数の引数」ダイアログが開くので、「検索値」の欄をクリックします。
参照先の仕入先コードのセルを選択します。
このとき「検索値」の欄に「G2」が入力されたことを確認してください。

(4) 仕入先リストを指定する

数式の作成手順

「関数の引数」ダイアログの「範囲」の欄をクリックします。
仕入先リストの列の番地A~Bをドラッグで範囲指定します。
このとき「範囲」の欄に「A:B」が入力されたことを確認してください。

(5) 「列番号」と「検索方法」を直接入力する

「列番号」と「検索方法」を下記のとおり入力します。

列番号2
検索方法FALSE

以上です。

各項目の設定について詳しく知りたい方はこちら ≫ 2-1.

2-3. 自動転記する項目を追加する方法

エクセルVLOOKUPの使い方【商品マスタ編】仕入先の自動転記する項目を追加する方法
自動転記する項目を追加する方法(画像はクリックで拡大)

≫ 解説図のPDFファイル

自動転記する項目を新たに追加する方法を解説します。具体的には、仕入先リストに「担当者名」を追加し、商品マスタへも自動転記できるようにします。

数式のカスタマイズの仕方

ここで作成する数式は、次のとおりです。

仕入先名の一番上「H2」
=VLOOKUP($G2,$A:$C,2,FALSE)

担当者名の一番上「I2」
=VLOOKUP($G2,$A:$C,3,FALSE)

担当者名の一番下「I6」
=VLOOKUP($G6,$A:$C,3,FALSE)

(1) 検索値に「$」を付けて仕入先コードの指定を固定する

数式のカスタマイズの仕方

数式の1つ目の項目のうち、列にだけ「$」を付けて「$G2」にします。数式をコピーしても、引き続き仕入先コードのあるG列を指定できるようにするためです。

(変更前)
=VLOOKUP(G2,A:B,2,FALSE)
↓↓↓
(変更後)
=VLOOKUP($G2,A:B,2,FALSE)

変更理由:「$」を付けることで隣へコピーしても変化しないようにするため

もし「$」がないと、隣にコピーしたときに「G2」→「H2」と変化してしまいます。これでは仕入先コードのあるセルを正しく指定できません。
一方、行番号の「2」には「$」を付けません。下にコピーしたときに「G3」「G4」・・・と変化していってほしいからです。

変化する項目/しない項目

VLOOKUPでは数式を移動/コピーした際に、変化する項目としない項目があります。項目ごとに確認しながら進めましょう。

項目移動/コピーしたときの変化
検索値変化する(ただし$がある項目は変化しない)
検索範囲変化する(ただし$がある項目は変化しない)
列番号変化しない
検索方法変化しない

(2) 仕入先リストの範囲指定を拡張し「$」を付ける

数式のカスタマイズの仕方

仕入先リストの項目を増やしたので、範囲指定を「A:B」から「A:C」へと拡張します。

(変更前)
=VLOOKUP($G2,A:B,2,FALSE)
↓↓↓
(変更後)
=VLOOKUP($G2,A:C,2,FALSE)

変更理由:仕入先リストの項目追加にあわせて検索範囲を拡張

さらに「$」を付けて「$A:$C」とします。数式をコピーしても引き続きA~C列を指定できるようにするためです。

(変更前)
=VLOOKUP($G2,A:C,2,FALSE)
↓↓↓
(変更後)
=VLOOKUP($G2,$A:$C,2,FALSE)

変更理由:「$」を付けることで隣へコピーしても変化しないようにするため

(3) 数式をコピーした後、列番号の指定を「担当者名」のある列に変更する

数式のカスタマイズの仕方

仕入先名のセル「H2」にある数式を、隣の担当者名のセル「I2」へコピーします。この操作では数式は変化しません。「検索値」と「検索範囲」の列に「$」を付けたからです。

同時に列番号の指定を「2」から「3」に変更します。これにより返す列を2列目の「仕入先名」から、3列目の「担当者名」へと変更できます。

(変更前)
=VLOOKUP($G2,$A:$C,2,FALSE)
↓↓↓
(変更後)
=VLOOKUP($G2,$A:$C,3,FALSE)

変更内容:返す列を「仕入先名」から「担当者名」に変更

(4) 数式を最下行までコピーする

数式のカスタマイズの仕方

メンテした2つの数式を最下行までコピーします。このとき、検索値の「$G2」の数字部分だけが、「$G3」 「$G4」・・・「$G6」へと変化していることを確認しましょう。

以上が、仕入先に新たに仕入先「担当者名」を追加する方法です。

「$」は数式を移動/コピーしたときにできるだけ手を加えなくて済むようにするためのものです。「$」の有無は計算結果には直接影響しません。
「$」をうまく使えば変更箇所が少なくなり、ミスも減り、省力化できます。膨大な数をコピーする際は、「$」は必須アイテムです。

2-4. 別ファイルから自動転記する方法

エクセルVLOOKUPの使い方【商品マスタ編】別ファイルを検索範囲にする場合の数式
別ファイルを検索範囲にする場合の数式(画像はクリックで拡大)

≫ 解説図のPDFファイル

別ファイルにある仕入先リストからも自動転記できます。数式は次のとおりです。別ファイルを開いているか閉じているかで、見え方が異なるので注意してください。

【セル「H2」の数式】
仕入先リストを開いているとき
=VLOOKUP(G2,[仕入先管理.xlsx]仕入先リスト!A:B,2,FALSE)

仕入先リストを閉じているとき)(※)
=VLOOKUP(G2,‘X:¥購買部¥[仕入先管理.xlsx]仕入先リスト’!A:B,2,FALSE)

(※)仕入先リストのファイルのプロパティ
ドライブ名 : X
フォルダ名 : 購買部
ファイル名 : 仕入先管理.xlsx
シート名  : 仕入先リスト

記述方法について詳しくはこちら ≫ 参照ファイルの記述ルールと注意点

数式の作り方は下記のようになります。基本的な部分は、仕入先リストが同じシートにある場合と変わりません。ファイルの取り扱い部分だけが異なります。

エクセルVLOOKUPの使い方【商品マスタ編】別ファイルにある仕入先リストから自動転記できる数式の作り方
別ファイルにある仕入先リストから自動転記できる数式の作り方(画像はクリックで拡大)

≫ 解説図のPDFファイルはこちら

(1) 仕入先リストのファイルを先に開く

数式の作成手順

仕入先リストのファイルをあらかじめ開いておきましょう。
次にある関数作成用ツールを開いてしまうと、他のエクセルファイルを開く操作ができなくなるからです。

(2) 数式を作成するセルを選択し関数作成用のツールを開く

数式の作成手順

商品マスタ内の数式を作成するセルを選択し、メニュー欄にある「fx」をクリックします。

(3) VLOOKUP関数を検索して呼び出す

数式の作成手順

「関数の挿入」ダイアログが開くので、「関数の検索」の欄に「VLOOKUP」と入力し、検索して呼び出します。

(4) 仕入先コードの入力場所を指定する

数式の作成手順

「関数の引数」ダイアログが開くので、「検索値」の欄をクリックします。
仕入先コードの入力場所となるセルを選択します。
このとき「検索値」の欄に「G2」が入力されたことを確認してください。

(5) 仕入先リストを指定する

数式の作成手順

「関数の引数」ダイアログの「範囲」の欄をクリックします。
仕入先リストの列の番地A~Bをドラッグで範囲指定します。


このとき「範囲」の欄に「[仕入先管理.xlsx]仕入先リスト!A:B」が入力されたことを確認してください。

別シートを指定する際は、関係のないセルに触れないようにしましょう。
うっかり触れると、そのセルまで数式に組み込まれてしまうからです。

(6) 「列番号」と「検索方法」を直接入力する

数式の作成手順

「列番号」と「検索方法」を下記のとおり直接入力します。

列番号2
検索方法FALSE

各項目の設定について詳しく知りたい方はこちら ≫ 2-1.

以上です。

参照ファイルの記述ルールと注意点

数式に別シートや別ファイルを組み込む場合、次のように記述します。

シートの場所 数式での記述の仕方
別シートシート名の後に「!」を付ける
別ファイルファイル名を「 [ ] 」で囲む
別フォルダフォルダの区切りに「」を挿入
別ドライブドライブ名の後に「:」を付ける
閉じられた別ファイルドライブ名、フォルダ名、ファイル名までをひっくるめて「」で囲む

注意すべき点は、フォルダやドライブの情報は参照するファイルを閉じたときだけ現れることです。

数式を作成している間は仕入先リストを開いているので、ドライブ名やフォルダ名は出ていません。仕入先リストだけ先に閉じると出てきて、開くと再び消えてしまいます

数式の変化

 

ドライブ名やフォルダ名は数式を作る際には入力しないため、ファイルを閉じたときに突然出てくると戸惑うかもしれません。見た目が違いますが、両者は同じ数式です。

2-5. 仕入先の件数を増やす方法

最下行までOK

仕入先の件数を追加したい場合は、仕入先リストに行を追加するだけでOKです。行間に挿入してもいいですし、末尾に追加しても構いません。

仕入先リストの範囲指定で、「A:B」のように列全体を指定しておけば、行が増えても数式は変更ありません。

検索範囲の指定方法の違いについて、詳しく知りたい方はこちら
≫ VLOOKUPの使い方|検索範囲の指定方法には「セル指定」と「列指定」がある

VLOOKUPを基礎から応用まで完璧にマスターしたい方は、次の記事もあわせて参照ください。
≫ VLOOKUPの使い方|基礎から応用まで完璧マスター&便利技20選を超図解!

スキルに自信がついたら新しい世界に踏み出そう!|転職サイト登録なび
転職活動で成功するGmailの作り方(1人で複数アドレス作成もOK)|転職サイト登録なび

3. エクセルの商品マスタの作成でVLOOKUPを使うメリット

この章では、エクセルの商品マスタの作成にVLOOKUPを使うメリットを解説します。

  1. 同じ文字の繰り返し入力を減らせる
  2. 入力文字の表記を統一できる(表記揺れを防げる)
  3. 一括変更に漏れなく対応できる(社名変更など)
  4. データの情報共有ができる
  5. 作業の分担・効率化が図れる

3-1. 同じ文字の繰り返し入力を減らせる

VLOOKUPを使えば、同じ文字を繰り返し入力するのを減らせます。仕入先名や担当者名などが何度出てきても、仕入先コードを入力するだけで仕入先リストから自動転記できます。

3-2. 文字の表記を統一できる(表記揺れを防げる)

リストから転記するので、文字の表記を統一できます。いわゆる「表記揺れ」を防げるわけです。

参考に表記揺れの具体例を紹介します。

ABC(全角)ABC(半角)
システム(全角)システム(半角)
オフィス(カタカナ)OFFICE(英字)
アンド(カタカナ)&(全角記号) &(半角記号)
株式会社(漢字)(株) ㈱(略語)

3-3. 一括変更に漏れなく対応できる(社名変更など)

仕入先情報の変更にも、漏れなく簡単に対応できます。
手作業の場合、該当データをすべて手で変更する必要があります。VLOOKUPを使っていれば、仕入先リストを変更するだけで、商品マスタの仕入先名を漏れなく一括更新できます。担当者の交代なども同様です。

変更前の古い社名や新旧の担当者名が混在するといった事態を防ぐことができます。

3-4. データの情報共有ができる

VLOOKUPは検索範囲として社内ネットワーク上の共有ファイルを指定することができます
例えば購買部門が新規の仕入先情報を入手していち早く仕入先リストに入力すれば、リアルタイムに社内で情報共有できます。

3-5. 作業の分担・効率化が図れる

商品マスタと仕入先リストを分けることで、それぞれ別の人が同時にメンテできます。業務分担による効率化が図れるわけです。
ファイルが一つだと誰かが開いているときに「読み取り専用」になってしまい、編集できません。しかし別々のファイルであればその心配はありません。ネットーワークでつながれていれば、拠点が離れていても分担可能です。

あなたを待っている会社がある
|転職サイト登録なび
転職活動で成功するGmailの作り方|1人で複数アドレス作成もOK

4. エクセルの商品マスタの作成でVLOOKUPを使う際の注意点

商品マスタの作成でVLOOKUPを使う際、幾つか注意点があります。

  1. 仕入先リストのフォーマットに一定のルールが必要
  2. 仕入先リストに間違いがあると被害が大きくなる
  3. 仕入先リストが壊れたら商品マスタも壊れる

4-1. 仕入先リストのフォーマットに一定のルールが必要

仕入先リストのフォーマットには、一定のルールが求められます。

仕入先リストの主なルール

  • 一番左の列に仕入先コードが縦に並んでいること
  • 一行ごとに一組のデータであること
  • 仕入先コードに重複がないこと
  • 仕入先コードは昇順に並んでいること
  • 結合セルがないこと

特に仕入先コードは重複しないようにしましょう。なぜならVLOOKUPは同じコードが2つ以上あっても、一番上のデータしか返さないからです。

重複がないかを一括チェックする方法を知りたい方はこちら
≫ VLOOKUPの使い方|6-4.重複を「条件付き書式」で一括チェック

特にメンテの担当者を分けている場合は要注意です。
仕入先リストに重複があってもエラーにはならないため、商品マスタの担当者は重複の存在に気付きにくいのです。発見が遅れてせっかくの業務分担による効率化が台無しになる恐れがあります。

VLOOKUPの検索範囲の表の作り方について、基本的なルールを知りたい方はこちら
≫ VLOOKUPの使い方|4-1.検索範囲となる表の作成上の基本ルール

4-2. 仕入先リストに間違いがあると被害が大きくなる

仕入先リストに間違いがあると、そのデータが転記されるすべてのファイルに間違いが伝播します。自動転記されるので間違いが何倍にも膨れ上がってしまうわけです。
また仕入先リストが完成すると、以後は自動転記に頼ってしまい、細かな注意を払わなくなります。さらに間違いに気付きにくくなります。

VLOOKUPは便利なだけに、ミスしたときのリスクも大きくなります。仕入先リストの作成には、特に注意を払いましょう。

4-3. 仕入先リストが壊れたら商品マスタも壊れる

仕入先リストが壊れたら、商品マスタも同時に壊れます。リアルタイムな機能がかえってあだとなるわけです。
原因として、仕入先リストの一部あるいは全部を削除してしまったり、ファイルを別の場所に移動させてしまったり、などがあげられます。

入力済みのデータを固定させたい場合は、セルをコピー&「値貼り付け」して、数式を値に変えてしまいましょう。
ただし仕入先リストとの連携はなくなり、仕入先リストの更新情報が反映されなくなります。今後変更がないデータだけに適用しましょう。

「値貼り付け」の仕方

該当セルをコピーした後、同じセルを指定したまま貼り付けします。その際「貼り付けオプション」で「形式を選択して貼り付け」から「値」を選択します。

転職して、私、成長したかな?
|転職サイト登録なび
転職活動で成功するGmailの作り方|1人で複数アドレス作成もOK

5. エクセルの商品マスタからVLOOKUPを使ってデータを取り出す方法

この章では、エクセルで作成した商品マスタから、VLOOKUPを使って必要な情報を取り出す方法を解説します。

お急ぎの方は下記のチャートより、目的に応じたリンクを辿ってください。

目的別チャート詳細へ
検索する仕組みを理解したい5-1.へ
基本的な数式を作りたい5-2.へ
別ファイルを検索する数式を作りたい5-3.へ
数式をカスタマイズしたい5-4.へ

5-1. 商品マスタから商品コードを手掛かりにデータを取り出す仕組み

エクセルVLOOKUPの使い方【商品マスタ編】商品コードを手掛かりに単価を取り出す仕組み
商品コードを手掛かりに単価を取り出す仕組み(画像はクリックで拡大)

≫ 解説図のPDFファイル

上の図は、商品マスタから商品コードを手掛かりに検索し、「単価」を取り出す仕組みです。

単価を表示させるセル(H2)の数式は、次のとおりです。

=VLOOKUP(G2,$A:$D,3,FALSE)

G2 :商品コードはセルG2に入力する
$A:$D :商品マスタの場所はA列からD列で囲まれた範囲(※1)
3 :商品マスタの3列目に必要なデータがある
FALSE :完全一致した場合のみデータを返せという意味(※2)

(※1)「$」をつけずに「A:D」とすることもできます。ただし、数式を移動/コピーすると同時にずれてしまいます。ミスを防ぐためにも「$」をつけておきましょう。
≫ 「$」について詳しく知りたい方はこちら

また、「$A$1:$D$4」のようにセル番地での範囲指定もできます。ただし、商品マスタの行数を増やすと、数式もあわせて変更する必要があります。
その点「$A:$D」のように列で指定しておけば、A~D列の最下行まで指定範囲に含まれるので、範囲の変更は不要になります。
≫ 範囲指定の仕方について詳しく知りたい方はこちら

(※2) 「FALSE」か「TRUE」かの二択になりますが、ここでは必ず「FALSE」を選びましょう。

「TRUE」にしてしまうと完全一致しなくても、近似値を見つけて返してしまいます。
商品検索においては、商品コードが異なれば別商品と考えるべきです。したがって、完全一致した場合のみデータを返す「FALSE」を選択しましょう。
≫ 「FALSE」「TRUE」について詳しく知りたい方はこちら

5-2. VLOOKUPの使い方1:商品マスタが同じシートにある場合

エクセルVLOOKUPの使い方【商品マスタ編】商品マスタと数式が同じシートにある場合の作り方
商品マスタと数式が同じシートにある場合の作り方 (画像はクリックで拡大)

≫ 解説図のPDFファイル

上図は、VLOOKUPの使い方の手順です。数式と商品マスタが同じシート内にある場合を想定しています。

あらかじめ商品コードの入力欄にコードを入れておけば、数式をつくりながらチェックができて便利です。

数式の作り方をステップ順に確認して進めたい方はこちら
≫ 2-2.基本的な数式の作り方

5-3. VLOOKUPの使い方2:商品マスタが別ファイルにある場合

エクセルVLOOKUPの使い方【商品マスタ編】商品マスタが別ファイルにある場合の作り方
商品マスタが別ファイルにある場合の数式の作り方 (画像はクリックで拡大)

≫ 解説図のPDFファイル

上図は、商品マスタが別ファイルにある場合の数式の作り方です。ステップ順に確認したい方はこちらを参照してください。
≫ 2-4.別ファイルから自動転記する方法

例えば下記の場所にある商品マスタを参照する場合の数式は下記になります。

ドライブ名 : X
フォルダ名 : 購買部
ファイル名 : 商品管理.xlsx
シート名  : 商品マスタ

=VLOOKUP(A2,‘X:\購買部[商品管理.xlsx]商品マスタ’!$A:$D,3,FALSE)

別ファイルにある商品マスタを検索する場合の記述ルールについて詳しくはこちら
≫ 参照ファイルの記述ルールと注意点

5-4. VLOOKUPのカスタマイズの仕方

次に、カスタマイズの仕方を解説します。

エクセルVLOOKUPの使い方【商品マスタ編】商品マスタの検索範囲を拡張する
商品マスタの検索範囲を拡張する方法 (画像はクリックで拡大)

≫ 解説図のPDFファイル

上図は、各項目に変更を加えたときの結果を整理したものです。必要に応じて自分なりにアレンジすることで、より理解が深まりイメージがわきやすくなります。

商品マスタに新たに「仕入先」の項目を追加する方法

商品マスタに新たに「仕入先」を追加する方法を解説します。

【変更手順】
商品マスタの右側に「仕入先」の列(E列)を追加する
数式の検索範囲の指定を拡張する($A:$D→$A:$E)
返す列の番号を変更する(4→5)

変更後の数式は次のとおりです。

変更内容:検索範囲を拡張し、返す列番号を変更

(変更前)
=VLOOKUP(F3,$A:$D,4,FALSE)
↓↓↓
(変更後)
=VLOOKUP(F3,$A:$E,5,FALSE)

項目の追加をステップ順に確認して進めたい方はこちら
≫ 2-3.自動転記する項目を追加する方法

商品マスタの商品点数を増やす方法

商品マスタの商品点数を増やす場合は、商品マスタに行を追加するだけです。間に挿入しても、末尾に追加しても構いません。

その際、一番左の列に入力する商品コードは、既存の商品コードと重複してはいけません。その他の項目も表のフォーマットに合わせて入力します。

なお、商品マスタの範囲指定については、列全体を指定しているので、行が増えても数式は変更ありません。

変更内容:商品点数を増やす

(変更不要)
=VLOOKUP(F3,$A:$D,4,FALSE)

$A:$D:列全体指定なので行追加しても変更不要

検索範囲の指定方法の違いについて、詳しく知りたい方は下記を参照ください。
VLOOKUPの教科書|検索範囲の指定方法には「セル指定」と「列指定」がある

転職してやっと活躍できる場を見つけました
|転職サイト登録なび
転職活動で成功するGmailの作り方|1人で複数アドレス作成もOK

6. エクセルの商品マスタの検索でVLOOKUPを使うメリット

エクセルの商品マスタの検索でVLOOKUPを使うと、次のメリットがあります。

  1. 膨大な商品点数でも簡単に検索可能(max100万点!)
  2. 商品マスタを更新したら、検索結果もリアルタイムに反映される
  3. データの情報共有ができる

6-1. 商品マスタに膨大な商品点数があっても簡単に検索可能

エクセルVLOOKUPの使い方【商品マスタ編】100万行の商品マスタでも検索可能
100万行の商品マスタでも検索可能 (画像はクリックで拡大)

≫ 解説図のPDFファイル

商品マスタに膨大な商品点数があっても、同じ式で簡単に検索できます。Max100万点まで可能です。

販売終了になった商品でも商品マスタに残しておけば、いつでも取り出すことが可能です。もし、クレーム対応で過去の商品を至急調べなければならないときでも、商品コードがわかれば即時に探し出せます。

横方向は16000列

ちなみに横方向へはどのぐらいまで検索できるかというと、列の数は約16000まで可能です。

エクセルVLOOKUPの使い方【社員名簿編】列の数が1万超でも検索可能
列の数が1万超でも検索可能(画像はクリックで拡大)

≫ 解説図のPDFファイル

なお、列番号のアルファベットの増え方は下記のようになります。

A B C・・・X Y Z AA AB AC・・・ZX ZY ZZ AAA AAB AAC・・・XFB XFC XFD

6-2. 商品マスタを更新したら検索結果もリアルタイムに反映される

商品マスタを更新すると、検索結果もリアルタイムに反映されます。

商品名を訂正したり単価を変更したりしても、商品マスタをメンテすればすぐに反映されます。手入力にありがちな「変更が漏れてて古い単価で印刷してしまった」といったミスを減らすことができるのです。

6-3. データの情報共有ができる

VLOOKUPは、社内ネットワーク上の共有ファイルを検索できます。

例えば、購買部門がサーバーにある商品マスタを最新の状態にしてくれたら、営業マンは常に最新情報をもとに見積作成できるわけです。

7. エクセルの商品マスタの検索でVLOOKUPを使う際の注意点

商品マスタの検索でVLOOKUPを使う際、幾つかの注意点があります。

  1. 検索値である商品コードより左側にあるデータは返せない
  2. 検索結果は同時に2つ以上返せない
  3. 商品コードが横方向に並ぶ表は検索できない

7-1. 必要なデータが商品コードより左側にあると返せない

商品マスタにおいて、検索値である商品コードより左側にあるデータは返せません。商品コードは、商品マスタの一番左の列にする必要があります。

列番号の指定をマイナスにして反対側のデータを返そうとしても、エラーになります。

商品マスタの作成について、基本的なルールを知りたい方はこちら
≫ VLOOKUPの使い方|4-1.検索範囲となる表の作成上の基本ルール

7-2. 検索結果は同時に2つ以上返せない

商品マスタの中に同じ商品コードがあっても、1つしか返せません。一番上にあるものを処理したら、下に同じ商品コードがあっても無視されます。
商品コードは重複しないようにしましょう。

商品コードに重複がないかチェックする方法はこちら
≫ VLOOKUPの使い方|6-4.重複を「条件付き書式」で一括チェック

7-3. 商品コードが横方向に並ぶ表は検索できない

商品マスタのフォーマットにおいて、商品コードが横方向に並んでいる表は検索できません。必ず、商品コードは縦に並んでいる必要があります。

VLOOKUPの先頭の「V」は、Vertical(垂直)の意味です。

転職サイトは複数登録してベストな会社を見つけたい|転職サイト登録なび
転職活動で成功するGmailの作り方|1人で複数アドレス作成もOK

まとめ|VLOOKUPで入力作業や検索を大幅に省力化できる

このように、商品マスタの管理運用にVLOOKUPを使えば劇的に効率化できます。膨大な商品点数があっても、一発で必要なデータを取り出すことができます。

商品マスタを作るとき
自動転記で入力作業が効率化
入力ミスや表記揺れを減らせる
変更点があれば一括更新できる
商品を検索するとき
膨大な点数でも簡単に取り出せる
変更点はリアルタイムに更新できる
マスタをサーバーにおいて情報共有できる

あわせてVLOOKUPのメリットや注意点も解説しました。

再確認したい方は、下記から該当箇所へ移動してください。

【商品マスタを作る】

解説内容詳細へ
自動転記の仕組みを理解したい2-1.へ
基本的な数式を作りたい2-2.へ
転記する項目を増やしたい2-3.へ
別ファイルから転記したい2-4.へ
仕入先の件数を増やしたい2-5.へ
作成時にVLOOKUPを使う
メリット 知りたい
3章へ
作成時にVLOOKUPを使う
注意点 知りたい
4章へ

【商品を検索する】

解説内容詳細へ
検索する仕組みを理解したい5-1.へ
基本的な数式を作りたい5-2.へ
別ファイルを検索する数式を作りたい5-3.へ
数式をカスタマイズしたい5-4.へ
検索でVLOOKUPを使う
メリット 知りたい
6章へ
検索でVLOOKUPを使う
注意点 知りたい
7章へ

本文中の解説図はクリックで拡大表示します。別途ページでPDFファイルも参照可能です。
≫ PDFファイル参照ページはこちら

この記事を読めば、商品マスタ作成時の単純入力作業を大幅に削減できます。完成後も社内で共有して有効活用してください。
そうすればよりクリエイティブな仕事に時間をまわせるようになります。時短にもつながり、残業を減らして早く帰れるようになることでしょう。

VLOOKUPを基礎から応用まで完璧にマスターしたい方は、次の記事もあわせて参照ください。
≫ VLOOKUPの使い方|基礎から応用まで完璧マスター&便利技20選を超図解!

エクセルでお困りのことがありましたら、『エクセルの窓口』で検索してください。

エクセルの窓口検索

ブックマークお気に入りに登録していただけたら幸いです。

スキルに自信がついたら新しい世界へ飛び出そう!
転職サイト登録なび
転職サイト登録なび