Excelで商品名や区分をプルダウンから選び、単価、税率、割引率、担当者などの情報を自動表示できれば、入力作業を大幅に減らせます。
手入力を減らす仕組みは、入力規則によるリストと、XLOOKUP関数、VLOOKUP関数、IF関数などの計算式を組み合わせることで作成できます。
プルダウンは選択ミスを防ぎ、計算式は選択結果に応じた数値や文字列を返す役割です。
この記事では、1行目を見出し行としたサンプルデータを使い、プルダウンと数式を連動させる基本から応用までを解説します。
作成の流れは、候補一覧を用意し、入力規則でプルダウンを作成し、検索関数で対応する値を取り出す順番です。
エクセルでプルダウンと計算式を連動させる手順
それではまず、プルダウンで選んだ商品名に応じて単価を自動表示する基本手順について解説していきます。
| 商品名 | 単価 | 数量 | 金額 |
|---|---|---|---|
| りんご | 120 | 3 | 360 |
| みかん | 80 | 5 | 400 |
| バナナ | 150 | 2 | 300 |
商品マスター表の準備
連動の元になる一覧表を、同じシートまたは別シートに作成します。
ここでは別シートのA1からB4に、商品名と単価を入力したものとします。
検索に使う商品名は重複させず、表記ゆれも作らないことが重要です。
例えば「りんご」と「リンゴ」が混在すると、数式が一致する候補を正しく見つけられない場合があります。
商品マスターシートのA列を商品名、B列を単価にすると、後の数式を読みやすく管理できます。
作業用シートでは、A列に商品名、B列に単価、C列に数量、D列に金額を置く構成が分かりやすいでしょう。
1行目は見出しとして使用し、実際の入力は2行目から始めます。
入力規則によるプルダウンリスト
次に、作業用シートのA2セルを選択し、データタブからデータの入力規則を開きます。
設定タブの入力値の種類でリストを選び、元の値に商品マスターの一覧範囲を指定します。
たとえば商品名が商品マスターシートのA2からA4にある場合、元の値には商品マスター!$A$2:$A$4を指定します。
ドロップダウンリストから選択するにチェックが入っていることも確認しましょう。
OKを押すと、A2セル右側の矢印から商品名を選択できるようになります。
元の値を直接入力する方法もありますが、候補が増える可能性がある場合は、セル範囲を参照する方法が管理しやすくなります。
XLOOKUP関数による単価の自動表示
B2セルには、A2で選択した商品名に対応する単価を返す数式を入力します。
=XLOOKUP(A2,商品マスター!$A$2:$A$4,商品マスター!$B$2:$B$4,””)
最初のA2は検索値であり、プルダウンで選択した商品名を表します。
次の範囲は商品名を探す列、3番目の範囲は一致したときに返す単価の列です。
最後の空欄指定は、商品名が未選択の場合にエラーではなく空白を返すための設定です。
数式をB2に入力した後は、フィルハンドルで必要な行までコピーします。
【操作のポイント】商品マスターの参照範囲には$を付け、オートフィルしても参照先がずれないようにします。
金額計算に連動させる数式
続いては、プルダウンで表示された単価と数量を掛け合わせ、金額まで自動計算する方法を確認していきます。
| 商品名 | 単価 | 数量 | 金額 |
|---|---|---|---|
| りんご | 120 | 3 | 360 |
| みかん | 80 | 5 | 400 |
| バナナ | 150 | 2 | 300 |
掛け算による金額計算
D2セルには、単価が入るB2セルと数量を入力するC2セルを掛ける数式を設定します。
=B2*C2
これで、A2のプルダウンを変更して単価が変わると、D2の金額も自動的に再計算されます。
数量だけを変更した場合も同様に、金額は直ちに更新されます。
商品名、単価、数量、金額が一本の流れで連動する状態になります。
単価を直接入力する必要がなくなるため、転記ミスや古い価格の入力を防ぎやすくなります。
未選択時を空白にするIF関数
商品を選んでいない段階で、金額欄に0と表示させたくないこともあります。
その場合は、IF関数でA2が空白かどうかを判定します。
=IF(A2=””,””,B2*C2)
A2が空欄なら空白を返し、商品名が選ばれている場合だけ単価と数量を掛け算する式です。
数式内の””は、画面上では何も表示しない空文字列を意味します。
見積書や注文一覧では、未入力行を空白にする設計が見た目の整頓にも役立ちます。
数量が未入力の場合も空白にしたいなら、=IF(OR(A2=””,C2=””),””,B2*C2)のようにOR関数を組み合わせます。
消費税や割引を含む計算
金額に消費税を加える場合は、掛け算の結果に税率を乗算します。
=IF(A2=””,””,B2*C2*1.1)
10パーセントの消費税を含めるなら、1.1を掛ける計算です。
税率を固定値ではなくF1セルに入力しておけば、=B2*C2*(1+$F$1)のように一括変更できます。
税率や割引率など、将来変わる数値はセル参照にする設計が実務では便利です。
【操作のポイント】計算式をコピーする前に、固定する税率セルには絶対参照の$を付けます。
複数項目を自動表示する検索関数
続いては、商品名の選択に合わせて単価だけでなく、分類や在庫数など複数の情報を返す方法を確認していきます。
| 商品名 | 分類 | 単価 | 在庫数 |
|---|---|---|---|
| りんご | 果物 | 120 | 35 |
| みかん | 果物 | 80 | 42 |
| バナナ | 果物 | 150 | 18 |
XLOOKUP関数で複数列を返す設定
商品マスターに商品名、分類、単価、在庫数がA列からD列まで並んでいるとします。
作業用シートでA2の商品名を選択した後、B2からD2に対応する情報を表示させます。
=XLOOKUP(A2,商品マスター!$A$2:$A$4,商品マスター!$B$2:$D$4,””)
Microsoft 365やExcel 2021以降では、この数式をB2に入力すると、B列からD列へ結果が自動的に展開されます。
複数の戻り列を指定できる点は、XLOOKUP関数の大きな利点です。
結果が表示される予定のC2やD2には、あらかじめ文字や数値を入力しないようにします。
展開先に入力済みデータがあると、スピル範囲にデータがありますというエラーになる場合があります。
Excel画面での数式入力
数式を入力する画面では、検索値となるセル、検索する列、返す列の順番を意識すると間違いを減らせます。
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 商品名 | 分類 | 単価 | 在庫数 |
| 2 | りんご ▼ | 果物 | 120 | 35 |
| 3 |
赤枠のA2がプルダウンの選択セルであり、B2からD2が数式の戻り値を表示する範囲です。
検索値のA2を変更するだけで、関連する3項目をまとめて更新できます。
見積書、商品台帳、社員名簿、部署別の申請書などで活用しやすい構成です。
VLOOKUP関数を使う場合
古いバージョンのExcelでは、XLOOKUP関数が利用できないことがあります。
その場合はVLOOKUP関数で同じような連動を作成できます。
=IFERROR(VLOOKUP(A2,商品マスター!$A$2:$D$4,3,FALSE),””)
3は、指定範囲の左端から数えた3列目の単価を返すという意味です。
FALSEは完全一致を指定する設定であり、プルダウンの選択値を検索するときに適しています。
VLOOKUP関数は検索列が表範囲の一番左にある必要があるため、商品名は左端列に配置しましょう。
【操作のポイント】新しいExcelを使える場合は、列番号を数えなくてよいXLOOKUP関数を優先すると修正しやすくなります。
別シート参照と名前定義の活用
続いては、候補リストを別シートで管理し、追加や修正に強いプルダウンを作る方法を確認していきます。
| 管理用シート | 作業用シート | 連動内容 |
|---|---|---|
| 商品マスター | 売上入力 | 商品名と単価 |
| 担当者マスター | 申請一覧 | 氏名と部署 |
| 区分マスター | 経費精算 | 区分と税率 |
入力規則で別シートを参照する方法
入力規則の元の値に別シートの範囲を直接入力すると、Excelの設定によっては参照を受け付けないことがあります。
その場合は、商品マスターのA2からA4を選択し、数式タブの名前の定義から商品一覧という名前を登録します。
入力規則の元の値には、=商品一覧と入力します。
名前定義を使うと、入力規則の設定画面が短くなり、参照先も判別しやすくなります。
名前は、商品一覧、担当者一覧、経費区分一覧のように内容が分かる日本語または英数字で付けると便利です。
テーブル化による候補追加への対応
商品マスターの範囲を選択し、挿入タブからテーブルを作成すると、商品追加時の管理が楽になります。
テーブル名を商品マスターに変更し、商品名列に分かりやすい見出しを付けます。
通常の固定範囲では、5件目の商品を追加したときに入力規則や数式の範囲を見直す必要があります。
一方でテーブルを使うと、行を追加したときに表の範囲が自動的に広がるため、メンテナンスの手間を減らせます。
マスターを頻繁に更新する運用では、固定セル範囲よりテーブル形式が向いています。
INDIRECT関数を使った連動プルダウン
大分類を選んだ後に、その分類に対応する小分類だけを表示したい場合は、連動プルダウンを作成できます。
たとえばA2で果物または飲料を選び、B2では選んだ分類に含まれる商品だけを表示する構成です。
=INDIRECT(A2)
A2に果物と入力されており、果物という名前の定義範囲を作成している場合、B2の入力規則でその範囲を参照できます。
INDIRECT関数はセルに書かれた文字を範囲名として扱える関数です。
【操作のポイント】大分類と名前定義の文字列を完全に同じにし、全角半角や余分な空白にも注意します。
エラー表示を防ぐ入力チェック
続いては、プルダウンと計算式を実務で使いやすくするためのエラー対策と入力チェックを確認していきます。
| 表示 | 主な原因 | 対処の考え方 |
|---|---|---|
| #N/A | 検索値が見つからない | IFERRORで表示を整える |
| #VALUE! | 文字列を計算している | 数量欄の入力規則を確認する |
| 0 | 未入力セルを計算した | IF関数で空白時を分岐する |
IFERROR関数によるエラー処理
商品マスターに存在しない値を検索した場合、XLOOKUP関数やVLOOKUP関数ではエラーが表示されることがあります。
数式全体をIFERROR関数で囲むと、エラー時の見た目を整えられます。
=IFERROR(XLOOKUP(A2,商品マスター!$A$2:$A$4,商品マスター!$B$2:$B$4),””)
エラーが発生しなければ検索結果を表示し、エラーが発生した場合は空白を表示する式です。
エラーを隠すだけでなく、検索値が存在しない原因も確認する姿勢が大切です。
プルダウンからしか選べない設定であれば、マスター削除や表記変更を疑うとよいでしょう。
数量入力の制限
数量欄に文字列やマイナスの数値が入ると、金額計算の信頼性が下がります。
C2からC100を選択し、データの入力規則で整数または小数点数を設定します。
条件を0より大きい、または0以上にすれば、不適切な値の入力を防げます。
選択式の項目だけでなく、数量のような自由入力欄にもルールを設定すると表全体が安定します。
入力時メッセージに「数量を半角数字で入力してください」と書けば、利用者への案内にもなります。
数式コピーと参照位置の確認
数式を下方向へコピーした後は、2行目と3行目をクリックして参照セルを確認します。
行ごとにA2、A3、A4と変わる部分は相対参照、マスター範囲や税率セルのように固定する部分は絶対参照です。
たとえば、=XLOOKUP(A2,商品マスター!$A$2:$A$4,商品マスター!$B$2:$B$4,””)では、A2だけが行に応じて変化します。
$の付け忘れは、オートフィル後に検索範囲がずれる代表的な原因です。
【操作のポイント】F4キーを押すと、選択中のセル参照を相対参照、絶対参照、複合参照の順に切り替えられます。
まとめ エクセルでプルダウンと計算式を連動させる方法
エクセルでプルダウンと計算式を連動させるには、まず商品名や区分などのマスター表を準備し、入力規則で選択リストを作成します。
選択された値に対応する単価や分類を表示するには、XLOOKUP関数を使う方法が分かりやすく、複数項目の取得にも対応しやすい方法です。
金額は単価と数量を掛け算し、IF関数を加えることで未選択時の不要な0表示も防げます。
旧バージョンのExcelではVLOOKUP関数でも対応できますが、検索列の位置に制約があるため、利用できる環境ならXLOOKUP関数が便利でしょう。
候補が増える表ではテーブル化や名前定義を使い、入力規則の範囲を更新しやすくすることもポイントです。
さらにIFERROR関数、数値の入力規則、絶対参照を活用すれば、エラーや入力ミスの少ない実用的な入力フォームになります。
まずは商品名と単価のような小さな連動から作成し、慣れてきたら分類、在庫、担当者、税率などへ対象を広げていきましょう。
