この記事では、同じブック内の「商品マスター」から「売上表」へ商品名と単価を取り出します。コピーして使える数式、完成結果、見つからない場合の確認方法まで、同じデータで追える手順です。

対応環境はMicrosoft 365版Excel、Excel 2021・2024です。Excel 2016・2019ではXLOOKUPを使えないため、後半のVLOOKUPによる方法を使ってください。以下は関数による参照の解説で、Copilotの契約は必要ありません。

別シートの値をそのまま表示する場合と、検索する場合

特定のセルを表示するだけなら、='商品マスター'!B2 で参照できます。ただし、この式が指しているのは「商品マスターのB2」です。売上表の商品コードに合う商品を探しているわけではありません。

毎月の売上表は、受注した順に商品が並び、同じ商品が複数回出てくることもあります。そのときは行の位置ではなく、商品コードを手がかりに結びます。たとえば売上表の2行目がP003でも、マスターのP003が4行目にあれば、4行目の商品名を取り出す必要があります。

同じ商品名でも規格が違う場合があるため、照合には商品を一意に区別するコードを使います。検索の前に、両方の表に同じ意味のコード列があるか確認してください。

作例:商品マスターと売上表を用意する

同じExcelブックに「商品マスター」「売上表」という2枚のシートを作ります。ここでは、説明用に作成した架空の商品データを使います。セル位置も数式の一部なので、まずは表の左上をA1にそろえて試してください。

売上表3行目の商品コードP003を商品マスター4行目で探し、ノートA5と単価220を取り出す図
図1|売上表とマスターは、行番号ではなく商品コードで結びます。

「商品マスター」のA1:C4へ、次の表を入力します。単価は円記号を含めず、500、120、220という数値で入力します。

行 A:商品コード B:商品名 C:単価
1 商品コード 商品名 単価
2 P001 コピー用紙A4 500
3 P002 クリアファイル 120
4 P003 ノートA5 220

「売上表」のA1:D5へ、次のように見出しと入力値を用意します。C列・D列のデータ部分は空欄にしておきます。P999は、マスターにないコードの扱いを確かめるための行です。

行 A:商品コード B:数量 C:商品名 D:単価
1 商品コード 数量 商品名 単価
2 P001 10 空欄 空欄
3 P003 8 空欄 空欄
4 P002 5 空欄 空欄
5 P999 2 空欄 空欄

P003とP002の順番は、商品マスターと入れ替えています。入力後に商品名だけを見て終わらせず、この2行がそれぞれ正しく対応しているかも確認します。

XLOOKUPで別シートの商品名を表示する

「売上表」のC2を選び、次の数式を入力してEnterキーで確定します。

=XLOOKUP(A2,'商品マスター'!$A$2:$A$4,'商品マスター'!$B$2:$B$4,"未登録")

C2に「コピー用紙A4」と表示されれば、A2のP001を別シートで検索できています。式は、左から次の4つの役割に分かれます。

数式の部分 この作例での役割
A2 売上表に入力された商品コード
'商品マスター'!$A$2:$A$4 商品コードを探す列
'商品マスター'!$B$2:$B$4 同じ行から取り出す商品名の列
"未登録" 商品コードが見つからないときに出す文字

検索範囲と取り出す範囲は、どちらも2行目から4行目です。コードのP001と商品名のコピー用紙A4が、同じ順番・同じ行に対応していることが大切です。見出しだけを片方に含めるなど、範囲の開始位置をずらさないでください。

XLOOKUPは、通常の使い方では完全に一致する値を探します。今回は一致モードを追加で指定せず、P001に一致するコードの商品名を取り出しています。近いコードを代わりに採用する設定にはしていません。

単価を表示し、下の行へコピーする

次に、「売上表」のD2へ単価用の数式を入力します。商品名の式から、取り出す範囲をB列からC列へ変えます。

=XLOOKUP(A2,'商品マスター'!$A$2:$A$4,'商品マスター'!$C$2:$C$4,"要確認")

D2には500と表示されます。C2:D2を選択し、選択範囲の右下にある小さな四角を5行目まで下へドラッグしてコピーします。完成結果は次のとおりです。

商品コード 数量 商品名の結果 単価の結果
P001 10 コピー用紙A4 500
P003 8 ノートA5 220
P002 5 クリアファイル 120
P999 2 未登録 要確認

ここでは、見つからない単価を0にせず「要確認」としています。未登録の商品をゼロ円の商品として集計してしまうことを避けるためです。この列を金額計算へ使う場合は、先に要確認の行を解消します。

コピーするときは、マスターの範囲を固定する

$A$2:$A$4 のように列・行へ付けた $ は、コピー先に合わせて参照範囲が動くのを防ぐ指定です。一方、最初の A2 は固定していないため、下へコピーするとA3、A4へ変わります。

たとえばC3の数式は、次の形になります。

=XLOOKUP(A3,'商品マスター'!$A$2:$A$4,'商品マスター'!$B$2:$B$4,"未登録")

探すコードだけがA3に変わり、マスターの範囲は同じです。C2だけ正しく、下の行で未登録が増える場合は、コピー後の数式を1つ開き、検索範囲まで動いていないか確認します。

なお、絶対参照は「新しい商品を自動で含める」という意味ではありません。商品マスターの5行目へ商品を追加しても、この式の対象は4行目までです。範囲を広げるときは、検索列と商品名・単価の列の末尾を一緒に変更してください。

C2からC3へ数式をコピーすると、検索値はA2からA3へ変わり、商品マスターのコード範囲と商品名範囲は絶対参照で変わらない図
図2|検索値だけを行に合わせて動かし、マスターの検索範囲と戻り範囲は固定します。

シート名にスペースがあるときの書き方

別シートの参照は シート名!セル範囲 で書きます。シート名が「商品 マスター」のようにスペースを含む場合は、シート名を半角のシングルクォート ' で囲みます。

=XLOOKUP(A2,'商品 マスター'!$A$2:$A$4,'商品 マスター'!$B$2:$B$4,"未登録")

文字を表示するための "未登録" と、シート名を囲む '商品 マスター' は別の記号です。手入力で分かりにくいときは、数式を入力する途中で参照先のシートをクリックし、範囲を選択すると、シート名やセル範囲を式へ入れられます。最後に $ の有無と範囲の位置を確認してください。

作例では、スペースのない「商品マスター」も同じ書き方で囲んでいます。実際のシート名と、数式に記載した名前を一致させることが必要です。

見つからない・値が違うときの確認順

「未登録」や #N/A は、検索するコードを先に確認

4番目の引数に「未登録」を指定した式では、P999のように一致するコードがない場合にその文字を表示します。この引数を省略すると、見つからない場合は #N/A になります。

どちらも最初に見るのは、売上表のコードがマスターの検索範囲内にあるかです。商品が別の行にあること自体は問題ありませんが、5行目以降へ追加され、式の対象から外れている場合は見つかりません。エラー表示だけを消す前に、マスターの登録漏れと参照範囲を確認します。

同じ商品コードが複数あると、先に一致した行を返す

商品マスターへ、もう1行「P002・クリアファイル旧規格・150」を追加してみます。検索範囲を追加行まで広げても、通常の検索では先に見つかるP002の120が返ります。重複しているという警告は出ません。

そのため、金額が表示されたことだけでは正しい単価と判断できません。商品コードごとに1行へそろえるか、規格・適用日など、何をもって1件を特定するかを決めます。単に後ろの行を使うだけで「現在の単価」になるとは限りません。金額が違う場合は、数式より先にマスター内の重複を確認してください。

見た目が同じでも、数値と文字列・空白を確認

取り込んだデータでは、片方の1001が数値、もう片方が文字列になっている場合があります。また、コードの末尾に空白が紛れ込むこともあります。セルの見た目だけで同一と決めず、両方の表でコードの入力形式と内容をそろえます。

「001」のような先頭ゼロに意味があるコードは、数値へ一括変換すると情報を失います。文字列として管理するなど、コードのルールを決めてからそろえてください。空白も、コードの一部なのか入力ミスなのかを確認し、元データと照合して修正します。検索式へ処理を追加し続ける前に、照合する2列を整える方が原因を追いやすくなります。

関数を入力できないときは、Excelの版と記号を確認

Excel 2016・2019ではXLOOKUPを使えません。共同作業でファイルを渡す場合は、受け取る人のExcelも確認します。対応版で数式を入力できないときは、関数名、括弧、区切りのカンマ、文字を囲む半角の引用符を見直してください。

未登録はコードと検索範囲、誤った単価はコードの重複、一致しないコードは数値と文字列や空白を確認する図
図3|未登録、重複、データの型を確認し、集計前に商品と単価を照合します。

Excel 2016・2019ではVLOOKUPで別シートを参照する

この作例は、商品コードがマスターの左端A列にあるため、VLOOKUPでも作れます。C2の商品名、D2の単価へ、それぞれ次の式を入力して下へコピーします。

=VLOOKUP(A2,'商品マスター'!$A$2:$C$4,2,FALSE)
=VLOOKUP(A2,'商品マスター'!$A$2:$C$4,3,FALSE)

2 は指定範囲の左から2列目の商品名、3 は3列目の単価を表します。末尾の FALSE は完全一致の指定です。省略すると近似一致になるため、商品コードの照合では省略しません。マスターにないP999は #N/A になります。

VLOOKUPでは、検索するコードを指定範囲の左端へ置く必要があります。XLOOKUPのように検索列と取り出す列を別々に指定する形とは違うため、別の表へ応用するときは列の配置も確認してください。

毎月の売上表に使う前の確認

最初に登録済みのP001、並び順が異なるP003、未登録のP999を照合し、期待した結果になるか確認します。コピー後の数式、追加商品の範囲、コードの重複も確認できたら、実務の表に置き換えます。

単価はマスターを参照しているため、マスターの変更が過去月の表へ影響する設計かどうかも確認が必要です。確定済みの売上を残す業務では、月ごとの単価の持ち方や、確定後に値を保存する手順を社内ルールに合わせます。関数の設定と、売上を確定する管理は分けて考えてください。

関連動画 · 約42分

ExcelとCopilotの活用を広げる

関数の作成から分析・グラフまで、Copilotの使い方を動画で解説します。