見積書や発注書で、品名を選ぶたびに商品の一覧を開いて型番と単価を写していませんか。
この記事では、品名をプルダウンで選ぶと、その行の型番と単価が自動で入る表を作ります。使うのは「データの入力規則」と XLOOKUP だけです。マクロは使いません。
この記事で分かること
- 品名の列にプルダウン（ドロップダウンリスト）を作る方法
- 選んだ品名をもとに、型番と単価を1本の数式で引く方法
- まだ選んでいない行に
#N/Aを出さない書き方 XLOOKUPが使えない環境で、VLOOKUPで同じことをする方法
完成形はこうなります。
完成した見積書。品名を選んだ3行に型番・単価・金額が入り、下の2行は空白のまま
A列の品名だけを選び、D列の数量だけを入力しています。B列・C列・E列はすべて数式です。
どんな場面で使うか
- 見積書・発注書・納品書の明細を作る
- 備品の貸出表や作業日報で、品名から型番を引く
- 商品の一覧（マスター）を見ながら手で写していて、転記ミスが起きる
1つ目のプルダウンで選んだ内容によって、2つ目の選択肢を絞り込みたい場合は、連動プルダウンの記事（末尾の関連記事）を参照してください。この記事は、1つ選んだら同じ行のほかの列が埋まるほうを扱います。
基本説明
サンプルファイル（記事末尾）を使います。シートは2枚です。
「商品マスター」シートには、選ばせたい商品の一覧が入っています。
「商品マスター」シート（A1:D8）。品名・型番・単価・単位の4列に7商品
A列の品名がプルダウンの選択肢になり、B列の型番とC列の単価が自動入力される値です。
「見積書」シートが、これから数式を入れる表です。
「見積書」シート（A1:E6）。見出しと、D2:D4の数量だけが入った状態
D列の数量だけが入っています。A列にプルダウンを作り、B列・C列・E列を数式で埋めていきます。
仕組み
やることは2つです。
- A列に「商品マスター」の品名だけを選べるプルダウンを作る
- B列・C列に「A列の品名をマスターで探して、型番と単価を返す」数式を入れる
プルダウン自体には、隣のセルを埋める機能はありません。選んだ値をキーにして数式で引くので、2つを組み合わせます。
手順
ここからは「見積書」シートで操作します。
ステップ1 品名の列にプルダウンを作る
- 「見積書」シートの A2:A6 を選ぶ
- 「データ」タブ →「データの入力規則」を開く
- 「入力値の種類」で「リスト」を選ぶ
- 「元の値」の欄をクリックし、「商品マスター」シートの A2:A8 をドラッグで選ぶ
- 「OK」を押す
「元の値」には次のように入ります。
=商品マスター!$A$2:$A$8
「データの入力規則」ダイアログ。入力値の種類が「リスト」、元の値が =商品マスター!$A$2:$A$8
A2を選ぶと右に「▼」が出て、7つの品名から選べます。
A2のプルダウンを開いた状態。ボールペンから付せんまで7つの品名が並ぶ
A2で「ボールペン」を選んでおきます。
ステップ2 型番と単価をXLOOKUPで引く
Excel 2019 以前で XLOOKUP が使えない場合は、このステップ2〜4の代わりに、後ろの「例：VLOOKUPしか使えない場合の書き方」を読んでください。
B2に次の数式を入れます。
=XLOOKUP(A2,商品マスター!$A$2:$A$8,商品マスター!$B$2:$C$8)- 1つ目
A2… 探す値（選んだ品名） - 2つ目
商品マスター!$A$2:$A$8… 探す場所（マスターの品名の列） - 3つ目
商品マスター!$B$2:$C$8… 返す値（型番と単価の2列）
B2にXLOOKUPを入れた状態。B2にBP-100、C2に120が入り、数式バーにB2の数式
B2に「BP-100」、C2に「120」が入ります。返す値を2列にしたので、C2には数式を入れなくても結果がはみ出して入ります（スピル）。
ステップ3 下の行へコピーする
B2を選び、右下の小さな四角（フィルハンドル）を B6 までドラッグします。
B2をB6までコピーした状態。品名が空の3〜6行目のB列に #N/A が並ぶ
品名をまだ選んでいない3〜6行目に #N/A が出ます。空のセルを探しても、マスターに見つからないためです。
数式の範囲に $ を付けたのは、下へコピーしてもマスターの範囲がずれないようにするためです。
ステップ4 未選択の行を空白にする
B2の数式を、次のように書き換えます。
=IF(A2="","",XLOOKUP(A2,商品マスター!$A$2:$A$8,商品マスター!$B$2:$C$8))「A2が空なら空白、そうでなければXLOOKUP」という意味です。IFERROR で囲んでも #N/A は消えますが、マスターに無い品名まで空白になり、間違いに気づけなくなります。この表では未選択の行だけを空白にしたいので IF にしています。書き換えたら、ステップ3と同じく B6 までコピーします。
IFで囲んだ後の状態。2行目だけ型番と単価が入り、3〜6行目は空白
3〜6行目の #N/A が消えます。
ステップ5 金額を計算する
- A3で「A4ノート」、A4で「ステープラー」を選ぶ
- E2に次の数式を入れ、E6 までコピーする
=IF(A2="","",C2*D2)
E列に金額が入った状態。E2:E4が1,200・1,400・1,960、数式バーにE2の数式
金額は 1,200・1,400・1,960 になります。冒頭の完成形と同じ状態です。
実務で使うときの注意
XLOOKUPとVLOOKUPのどちらで作った場合も当てはまります。
- マスターの単価を直すと、過去の見積書の単価も変わります。 数式がいつもマスターを見に行くためです。発行した見積書は、コピーして「値の貼り付け」で数式を値に置き換えて残してください
- マスターの9行目以降に商品を足しても、プルダウンにもXLOOKUPにも出てきません。 範囲を
$A$2:$A$8で止めているためです。商品が増える表は、マスターをテーブルにしておくと範囲を直さずに済みます。XLOOKUPとテーブルの組み合わせはXLOOKUP関数とテーブルの記事に、プルダウンの元の値を増減に合わせる方法は動的なドロップダウンリストの記事にあります
例：VLOOKUPしか使えない場合の書き方
XLOOKUP は Excel 2019 以前では使えません。その場合は VLOOKUP で1列ずつ引きます。
B2（型番）:
=IF(A2="","",VLOOKUP(A2,商品マスター!$A$2:$C$8,2,FALSE))C2（単価）:
=IF(A2="","",VLOOKUP(A2,商品マスター!$A$2:$C$8,3,FALSE))VLOOKUP は1つの数式で1つのセルしか埋められないので、C列にも数式を入れます。B2:C2 を選び、右下のフィルハンドルを6行目までドラッグしてコピーします。E列の金額は、ステップ5と同じです。
2・3… 範囲の左端（品名の列）から数えて何列目を返すかFALSE… 完全に一致するものだけを探す。省略すると、違う商品の値が返ることがあります
VLOOKUPに書き換えた状態。結果はXLOOKUPと同じで、数式バーにB2のVLOOKUPの数式
結果は XLOOKUP のときと同じです。2つの関数の違いはVLOOKUPとXLOOKUPの違いの記事で詳しく比べています。
よくあるエラー
下の行のプルダウンだけ、選択肢がずれる・空欄が混ざる
原因: 「元の値」が =商品マスター!A2:A8 のように $ の無い参照になっています。入力規則の参照は、下のセルほど1行ずつずれます。
直し方: A2:A6 を選び直し、「元の値」を =商品マスター!$A$2:$A$8 にします。欄をクリックしてからマスターの範囲をドラッグすると、$ 付きで入ります。
コピーした下の行で、品名を選んでも #N/A になる
原因: XLOOKUPの範囲に $ がありません。コピーすると、探す場所が A3:A9・A4:A10 とずれていき、上のほうの商品が範囲から外れます。
直し方: B2の数式の範囲を $A$2:$A$8・$B$2:$C$8 にしてから、B6 までコピーし直します。
B列に #SPILL! が出る
原因: 結果を入れるC列のセルに、値が入っています。手で単価を打ったセルが残っているときなどです。
直し方: C列のその行の値を消します。C列には何も入れないのが正しい状態です。
貼り付けた品名だけ #N/A になる
原因: プルダウンを使わずに貼り付けた品名に、空白や全角・半角の違いがあります。見た目は同じでも、マスターの品名と一致しません。
直し方: プルダウンから選び直します。原因の見分け方はVLOOKUPが一致しない原因の記事で解説しています。
まとめ
- プルダウンは「データの入力規則」の「リスト」で作り、元の値はマスターの品名の列にする
XLOOKUPの返す値を2列にすると、型番と単価が1本の数式で入る- 未選択の行は
IF(A2="","",…)で空白にする - 範囲には
$を付ける。入力規則の元の値も同じ XLOOKUPが無い環境ではVLOOKUPで1列ずつ引く
まずはサンプルの「見積書」シートで、品名を選び替えて型番と単価が変わるのを確かめてみてください。
関連記事
- 【Excel】連動プルダウン（2段階ドロップダウン）を作る方法｜INDIRECT関数で簡単設定
- 【Excel 関数】連動プルダウンをXLOOKUPで進化させる — INDIRECT不要の実践パターン
- 【Excel】動的なドロップダウンリストを作成する方法
- 【Excel】XLOOKUP関数とテーブルを使用した簡単なデータ検索
- 【Excel 関数】VLOOKUPとXLOOKUPの違い — 乗り換えるタイミングと書き換えの手順
- 【Excel】VLOOKUPが一致しない原因と直し方 — 空白・全角半角・型の違いを見分ける
- 【Excel】テーブル機能は普通の表と何が違うのか — 構造化参照と自動拡張の仕組み
対応バージョン: Microsoft 365 または Excel 2021 以降（VLOOKUPの書き方は Excel 2019 以前でも使えます）
サンプルファイル
記事中で使用しているサンプルデータはこちらからダウンロードできます。
