Excelで仕入リストを作成する際、「仕入先名」と「商品CD」を入力するだけで、自動的に商品名や単価を表示できるようにしたいと考える方は多いです。
特に、仕入先ごとに別ファイルで商品マスタを管理している場合、単純なVLOOKUPでは対応しづらく、「どの会社のファイルを見るか」を自動で切り替える工夫が必要になります。
この記事では、Excelで仕入先別の商品マスタを自動参照し、商品名と単価を自動入力する方法を、初心者にもわかりやすく解説します。
やりたいことの整理
例えば、次のような状況を想定します。
| 仕入先 | 商品CD | 商品名 | 単価 |
|---|---|---|---|
| A社 | 113160 | りんご | 250 |
| B社 | 113160 | りんご | 240 |
同じ商品CDでも、会社によって単価が異なるため、「商品CDだけ」で検索すると正しい値を取得できません。
そのため、「仕入先名＋商品CD」の組み合わせで検索する必要があります。
おすすめは「マスタ統合方式」
最も管理しやすく、Excel初心者にもおすすめなのは、各社のデータを1つのマスタ表にまとめる方法です。
例えば、次のような一覧表を作ります。
| 仕入先 | 商品CD | 商品名 | 単価 |
|---|---|---|---|
| A社 | 113160 | りんご | 250 |
| A社 | 110180 | みかん | 130 |
| B社 | 113160 | りんご | 240 |
| B社 | 113170 | みかん | 120 |
この形式なら、XLOOKUPやFILTER関数などを使って簡単に検索できます。
XLOOKUP関数を使う方法
Excel365やExcel2021以降なら、XLOOKUPが便利です。
例えば、入力欄が以下の場合:
- A2：仕入先名
- B2：商品CD
商品名を表示する数式:
=XLOOKUP(A2&B2,マスタ!A:A&マスタ!B:B,マスタ!C:C,”未登録”)
単価を表示する数式:
=XLOOKUP(A2&B2,マスタ!A:A&マスタ!B:B,マスタ!D:D,”未登録”)
これは「仕入先名」と「商品CD」を連結して検索しています。
VLOOKUPしか使えない場合の方法
古いExcelではXLOOKUPが使えないため、補助列を作ります。
マスタ側に「検索キー」という列を追加し、以下のように入力します。
=A2&B2
すると、「A社113160」のようなキーが作れます。
その後、VLOOKUPを使用します。
=VLOOKUP(A2&B2,マスタ範囲,3,FALSE)
これで商品名を取得できます。
別ファイルを自動切替したい場合
質問のように「A社.xlsx」「B社.xlsx」など別ファイルになっている場合は、INDIRECT関数を使う方法があります。
ただし注意点があります。
- 参照元ファイルを開いておく必要がある
- ファイル名変更に弱い
- 動作が重くなりやすい
例:
=VLOOKUP(B2,INDIRECT(“‘[“&A2&”.xlsx]Sheet1’!A:D”),2,FALSE)
この方法は便利ですが、実務ではエラー原因になりやすいため、大量データにはあまりおすすめされません。
実務ではPower Queryも便利
もし仕入先ファイルが多い場合は、Power Queryで複数ファイルを統合する方法もあります。
Power Queryなら、各社のExcelファイルを自動的に1つのマスタにまとめられるため、更新作業が非常に楽になります。
特に毎月データが増える場合や、仕入先が増える場合には効果的です。
まとめ
Excelで「仕入先名」と「商品CD」から商品名・単価を自動表示するには、まずデータの管理方法を整理することが重要です。
最もおすすめなのは、各社の商品マスタを1つに統合し、「仕入先＋商品CD」で検索する方法です。
Excel365ならXLOOKUP、古いExcelならVLOOKUP＋補助列、複数ファイル管理ならINDIRECTやPower Queryが活用できます。
まずは小規模なマスタ統合から始めると、管理もしやすく、将来的な拡張にも対応しやすくなります。
コメント