【Excel】エクセルでコードを入力して商品名を表示する方法

Excelで商品コードを入力したとき、対応する商品名を自動表示できれば、見積書、注文書、在庫管理表、売上データの作成がぐっと速くなります。

手入力による商品名の表記ゆれや入力ミスも抑えやすく、コードを共通キーとして商品マスタと明細表をつなげられる点が大きな利点です。

この記事では、1行目を見出し行としたサンプルデータを使い、コード入力から商品名を表示する数式、エラー対策、入力規則、実務での管理方法まで解説していきます。

最初に商品マスタの配置と検索式の考え方を理解すると、表の列が増えても応用しやすくなります。

 

商品コードから商品名を表示する基本設定

それではまず、商品コードを入力すると商品名を返す基本設定について解説していきます。

商品コード	商品名	単価
A001	ボールペン 黒	120
A002	ノート A4	280
A003	付箋 黄色	180

 

商品マスタと入力表の分離

商品名を自動表示するには、まず商品コードと商品名の対応表である商品マスタを作成します。

商品マスタは同じブック内の別シートに置く方法がわかりやすく、シート名を商品マスタとすると管理しやすいでしょう。

商品マスタの1行目には商品コード、商品名、単価、分類などの見出しを入れ、2行目以降に商品情報を登録します。

明細表ではA列に入力用の商品コード、B列に自動表示する商品名を配置する構成が基本です。

商品コードは商品名ではなく、商品を一意に識別するための値として扱います。

同じ商品名でも色や容量が異なる商品がある場合は、商品ごとに異なるコードを付ける必要があります。

例えば黒のボールペンをA001、青のボールペンをA002とすれば、検索式は商品ごとの違いを正確に判定できます。

 

XLOOKUP関数による商品名表示

Microsoft 365やExcel 2021以降を利用している場合は、XLOOKUP関数で商品名を表示する方法が便利です。

明細表のA2セルに商品コードを入力し、B2セルに次の数式を入力します。

この式では、A2の商品コードを商品マスタシートのA2からA100で探し、同じ行のB列にある商品名を返します。

最後の二重引用符は、該当するコードがない場合に空白を表示する指定です。

XLOOKUP関数は完全一致で検索するため、商品コード検索に適しています。

商品マスタの件数が100件を超える場合は、参照範囲を実際の最終行まで広げるか、後述するテーブル機能を利用します。

数式を入力したB2セルの右下にあるフィルハンドルを下へドラッグすると、下の行にも数式をコピーできます。

入力表に空行が多いときも、空白表示の指定を入れておけば、未入力行にエラーが並ぶことを避けられます。

 

数式コピーと表示確認

数式を下の行へコピーしたら、A2セルからA4セルにA001、A002、A003を順番に入力して動作を確認します。

B列にはボールペン 黒、ノート A4、付箋 黄色のように、商品マスタに登録した商品名が表示されます。

コードを入力しても商品名が表示されない場合は、入力コード、参照範囲、シート名の3点を確認しましょう。

特に商品コードの前後に空白が含まれていると、見た目が同じでも別の文字列として扱われることがあります。

検索結果を確認するときは、存在するコードと存在しないコードの両方を試すと安心です。

空白の明細行に商品名が表示されないこと、誤ったコードを入力しても不要なメッセージが出ないことも確認項目です。

 

VLOOKUP関数を使った商品名の検索

続いては、従来のExcelでも利用しやすいVLOOKUP関数による商品名検索を確認していきます。

入力表のセル	入力内容	表示結果
A2	A001	商品コード
B2	VLOOKUP数式	ボールペン 黒

 

VLOOKUP関数の数式構造

VLOOKUP関数を使う場合、明細表のB2セルには次の数式を入力します。

最初のA2は検索値であり、利用者が商品コードを入力するセルです。

商品マスタ!$A$2:$C$100は検索する表全体で、左端のA列に商品コードがある必要があります。

3番目の引数である2は、指定範囲の左から数えて2列目の商品名を返す指定です。

FALSEは完全一致検索を意味し、コードが完全に一致した場合だけ商品名を表示します。

商品コードの検索では、FALSEを省略せずに指定することが大切です。

IFERROR関数は、該当コードがないときに表示されるエラーを空白へ置き換える役割を持ちます。

入力途中の空欄や、まだ商品マスタに登録していないコードがある表でも、見た目を整えやすくなります。

 

列番号と参照範囲の設定

商品名だけでなく単価も表示したい場合は、C列に別のVLOOKUP関数を入力します。

この式の列番号は3なので、商品マスタ範囲の3列目である単価を返します。

商品マスタの列を途中で追加すると、VLOOKUP関数の列番号がずれることがあります。

例えば商品コードと商品名の間に分類列を挿入した場合、商品名を返す列番号は2から3へ変更が必要です。

列構成が頻繁に変わる管理表では、VLOOKUP関数よりXLOOKUP関数のほうが保守しやすい場合があります。

ただし、古いExcelファイルを他の担当者と共有する場合には、互換性の高いVLOOKUP関数が役立つこともあります。

 

完全一致検索とエラー処理

商品コード検索では、近い値ではなく同じ値だけを見つける完全一致検索が必要です。

VLOOKUP関数の第4引数をTRUEにすると近似一致になり、商品コードでは誤った商品を返す危険があります。

そのため、FALSEまたは0を指定することを習慣にしましょう。

該当しないコードに対して空白ではなく確認メッセージを表示したい場合は、IFERROR関数の最後を次のように変えられます。

この表示は、入力担当者に商品コードの再確認を促したいときに便利です。

一方で、印刷用の帳票ではメッセージが目立つため、空白表示とするほうが使いやすい場合もあります。

 

商品マスタをテーブル化する管理方法

続いては、商品マスタをExcelテーブルに変換して、商品追加時にも検索範囲を自動で広げる方法を確認していきます。

商品コード	商品名	カテゴリ
A001	ボールペン 黒	筆記具
A004	クリアファイル	事務用品

 

テーブルへの変換手順

商品マスタの範囲内にある任意のセルを選択し、挿入タブからテーブルを選びます。

表示された範囲確認画面では、商品マスタ全体が選ばれていることと、先頭行をテーブルの見出しとして使用する設定を確認します。

OKを選ぶと、見出し行にフィルターボタンが付いたテーブルへ変換されます。

テーブルデザインタブでテーブル名を商品マスタ表のように変更しておくと、数式が読みやすくなります。

テーブルは最終行の下へ商品を入力すると、自動的に範囲を拡張する仕組みです。

テーブル名は数式で使用するため、日本語でも英語でも、誰が見ても用途を判断できる名称にすることが大切です。

 

構造化参照を使うXLOOKUP関数

テーブル名を商品マスタ表に設定した場合、明細表のB2セルには次のような数式を入力できます。

商品マスタ表[商品コード]は、商品マスタ表の中にある商品コード列だけを参照する書き方です。

商品マスタ表[商品名]は、検索結果として返す商品名列を表します。

新しい商品をテーブルの末尾へ追加しても、数式の参照範囲は自動的に拡張されます。

固定のA2からA100という範囲を見直す手間を減らせる点が、テーブル化の大きなメリットです。

商品数が増える可能性がある在庫一覧、仕入先一覧、顧客マスタなどでは、テーブル化を最初から採用すると管理が安定します。

 

商品追加後の動作確認

商品マスタ表の最終行の下に、新しい商品コードA005と商品名を追加します。

次に明細表のコード入力セルへA005を入力し、新しい商品名が表示されれば設定は完了です。

表示されない場合は、追加した行がテーブルの色付き範囲に含まれているかを確認します。

テーブルの外側に入力してしまった場合は、テーブルデザインタブからテーブルのサイズ変更を行えます。

商品追加のたびに検索式を修正しなくてよい状態が、実務で使いやすい商品マスタの目安です。

 

商品コード入力時のエラー対策

続いては、商品コードを入力しても商品名が出ないときに確認したいエラー対策を解説していきます。

入力コード	想定される原因	確認内容
A001	正常	商品名が表示される
A 001	空白混入	スペースを削除する
001	形式違い	先頭文字を確認する

 

文字列と数値の形式統一

商品コードが00123のように先頭のゼロを含む場合は、コード列を文字列として扱うことが重要です。

数値として入力すると00123が123へ変わり、商品マスタ側の00123と一致しない場合があります。

商品マスタと明細表のコード列を選択し、セルの書式設定で表示形式を文字列にしてから入力すると安全です。

すでに入力済みの値が数値になっている場合は、TEXT関数で桁数をそろえる方法もあります。

この式はA2の数値を5桁の文字列として表示するため、00123のようなコード形式を維持できます。

商品コードは計算用の数値ではなく識別子であるため、文字列として管理する考え方が基本です。

 

余分な空白と見えない文字の除去

コピー貼り付けした商品コードでは、前後の空白や改行などの見えない文字が混入することがあります。

見た目が同じA001でも、末尾に空白があると検索関数は別の値として判定します。

このような場合は、TRIM関数を使って空白を取り除いた値を検索できます。

TRIM関数は文字列の先頭と末尾にある余分な空白を削除し、単語間に連続する空白も1つに整えます。

Web画面や外部システムからコードを貼り付ける業務では、あらかじめTRIM関数を組み込むとトラブルを減らせるでしょう。

 

商品マスタ未登録コードの確認

検索できない理由が入力ミスではなく、商品マスタへの登録漏れであることもあります。

明細表の隣に確認列を作り、商品名が空白なのにコードが入力されている行を判定すると、未登録コードを探しやすくなります。

A2にコードがあり、B2の商品名が空白の場合だけ商品マスタを確認と表示する数式です。

この確認列をフィルターで絞り込めば、登録漏れの候補だけを一覧で確認できます。

エラーを隠すだけでなく、未登録コードを業務改善の情報として残すことも有効です。

 

プルダウン入力と複数項目の連動表示

続いては、商品コードの入力ミスを減らし、商品名や単価などを連動表示する実務的な設定を確認していきます。

商品コード	商品名	単価	数量
A001	ボールペン 黒	120	3

 

入力規則によるプルダウンリスト

商品コードを直接入力する代わりに、プルダウンリストから選べるようにすると入力ミスを減らせます。

明細表のコード入力範囲を選択し、データタブにあるデータの入力規則を開きます。

入力値の種類をリストにし、元の値として商品コード一覧の範囲を指定します。

商品マスタをテーブル化している場合は、名前の管理で商品コード列に名前を付けて、その名前を入力規則の元の値として利用すると管理しやすくなります。

プルダウンから選択させる方法は、似たコードの打ち間違いを防ぐのに効果的です。

商品数が少ない場合は見やすい一方、商品数が非常に多い場合はコードの一部を入力して検索関数で表示する運用のほうが速いこともあります。

 

単価と分類の同時取得

商品名のほかに単価、分類、在庫数などを表示したい場合も、同じ商品コードを検索値として数式を増やします。

商品マスタ表に単価列があるなら、C2セルには次のような数式を入力できます。

商品名の数式と検索値は同じですが、戻り範囲を商品マスタ表[単価]に変えることで単価を取得できます。

分類も必要なら、戻り範囲を分類列に変更した数式を作成します。

コードを1つ入力するだけで複数の情報を表示できるため、明細作成の時間短縮につながります。

数量をD列へ入力し、金額をE列に表示する場合は、単価と数量を掛け算する数式を設定します。

この式なら単価または数量が未入力の間は空白となり、入力がそろった時点で金額を表示できます。

 

数式列の保護と運用

商品名や単価の数式が入ったセルを誤って上書きすると、自動表示の仕組みが崩れてしまいます。

入力するコード列と数量列だけをロック解除し、シートの保護を設定すると、数式列を保護できます。

セルの書式設定にある保護タブで入力セルのロックを外してから、校閲タブのシート保護を実行します。

保護後もロック解除したセルには入力できるため、利用者は通常どおり商品コードと数量を入力できます。

入力者が複数いるファイルほど、数式列の保護が安定した運用につながります。

商品マスタを更新する担当者を決め、コードの採番ルールと変更履歴も残しておくと、後から調査しやすくなります。

 

まとめ エクセルでコードを入力して商品名を表示する方法

Excelでコードを入力して商品名を表示するには、商品コードと商品名を登録した商品マスタを準備し、明細表から検索関数で参照します。

新しいExcelを利用している場合は、検索列と戻り列を指定しやすいXLOOKUP関数が便利です。

幅広いExcelバージョンで共有する場合には、完全一致を指定したVLOOKUP関数も活用できます。

商品コード列を文字列として統一し、重複をなくすことが、正確な検索結果を得るための土台です。

商品マスタをテーブル化すれば、新商品を追加したときにも検索範囲が自動で広がります。

さらにプルダウン入力、未登録コードの確認列、数式セルの保護を組み合わせると、入力ミスと管理負担を減らせるでしょう。

まずは商品コードと商品名の2列だけで小さな商品マスタを作り、XLOOKUP関数で表示するところから始めてみましょう。