Excel使いこなしの基礎と応用


Excelで自動入力・集計の見積書作成【5】

Excelで見積書の作成手順【4】で2箇所にドロップダウンリストを設定しました。残る3箇所目は取引する商品やサービスの詳細を入力する欄ですが、ここでリストから入力した内容に連動して「単価」の欄に数値が自動入力されるよう設定したいと思います。

見積書作成のプロセス


Step5.ドロップダウンリストとVLOOKUP関数の連動

step-arrow

【下準備】別表を用意し、セル範囲に名前を定義

商品リスト、或いは料金表などを別シートに作成しておき、見出しを除いた表のセル範囲に適当な名前を定義しておきます。 ▶名前を定義する方法を詳しく
料金表のセル範囲に名前を定義
管理しやすい名前を付けておきましょう。ここでは仮に「料金表」としました。
新しい名前ダイアログボックス1
もう一つ、ドロップダウンリスト用に「詳細」のセル範囲だけを選択して、名前を付けておくことにします。
制作内容に名前を定義
ここでは「制作内容」としました。
新しい名前ダイアログボックス2

まずは普通にデータの入力規則でドロップダウンリスト設定

見積書の「詳細」欄の最初の結合セルをクリックして選択し、
詳細の最初のセルを選択
データの入力規則」アイコンをクリック。
データの入力規則ボタン
「データの入力規則」ダイアログボックスの「設定」タブで「リスト」を選び、「元の値」に先ほど定義した名前(制作内容)を指定します。=を冒頭に付けるのを忘れずに!
リストの元の値に定義した名前を入力
「OK」した後、実際にドロップダウンリストが機能しているか確認して、
ドロップダウンリストの確認
フィルハンドルを下までドラッグして、オートフィルコピーします。
※罫線や背景色が消えないように、コピーした後横のアイコンをクリックして「書式なしコピー(フィル)」を選択
書式なしでオートフィルコピー
▶ドロップダウンリストの設定方法をもっと詳しく

単価のセルにVLOOKUP関数を入力する

単価の数値が入る一番上のセルを選択して、「数式」タブ「関数ライブラリ」の「検索/行列」リストから「VLOOKUP」をクリックします。
VLOOKUP関数のボタン
関数の引数」ダイアログボックスが開いたら、以下のように設定。
VLOOKUP関数の引数ダイアログボックス
1番上の「検索値」は見積書の「詳細」のセルをクリックして指定。
2番めの「範囲」にはこのページの最初で定義した名前を指定。
3番めの「列番号」には「料金表」における「単価」の位置。
4番目の「検索方法」はFALSEを指定。

スポンサーリンク

手動で入力するなら、式は「=VLOOKUP(B14,料金表,2,0)」です。
▶VLOOKUP関数の設定方法・関数の引数の入力方法をくわしく

「OK」を押せばドロップダウンリストと連動したVLOOKUP関数が機能しますが、このままだとエラー値が出るので、これを消します。▶#N/Aエラーを消す方法

最終的に単価のセルに入力される関数式は「=IFERROR(VLOOKUP(B14,料金表,2,0),"")」となります。

スポンサーリンク

単価のセルも下までフィルコピーしておきましょう。
試しに、「詳細」の欄にドロップダウンリストで入力してみると、
リストとVLOOKUP関数連動の機能を確認
入力内容と連動して、単価の欄に自動で数値が表示されました。

▶次は見積金額や合計のセルにSUM関数や計算式を入力して自動集計させます

Officeで作れるもの