リモートで働く管理人のTwitter @2013ume Follow me

Excel 商品名を入れたら金額を反映させたい!VLOOKUP関数で解決

私服で働く人のイラスト(女性) Excel

 

仕事でExcelもたまに使っているうめ @2013ume です。

 

VLOOKUP関数を使い商品を選択したら金額を反映させ、自動で計算する方法を書いています。

たとえば「ひまわり」を選んだら横のセルにポヨンっと価格が表示され、連動して設定した計算をしてくれるような感じです。

 

商品は一つではなく複数あると仮定して、プルダウンメニュー(ドロップダウンメニュー)を使用しています。

 

ExcelはOffice 2016を使用。

 

 

プルダウンメニューで商品リストを使いやすく

プルダウンメニュー

プルダウンメニューとは商品などをリストにして選択しやすくしたものです。

後から商品を追加することも可能、商品名もそのつど手入力しなくて良いので便利です。

 

プルダウンメニューの設定方法

プルダウンメニューの設定方法は、値を手入力する方法とマウスでドラッグして範囲選択する方法の2つあります。

設定が簡単なので今回は範囲選択する方法で解説しています。

 

1.商品名を入力する

商品リスト

まず商品名をセルに入力します。

プルダウンを表示させたいセルを選びます。(上記図では11Bにしています。)

 

2.データの入力規則を選ぶ

データタブ

セルを選んだらデータタブの<データの入力規則>をクリック。

 

3.データの入力規則を設定

データの入力規則画面

<入力値の種類>をリストに変更します。

<元の値>にカーソル(|)を持ってきたら、商品リストを左クリックしながらドラッグ。(ドラッグした範囲=緑の点線で囲まれたセル)

ドラッグした範囲が<元の値>のところに表示されます。

後で商品を追加する予定がある場合、セルを多めに選択しておくと範囲を設定し直す手間がはぶけます。

 

 

VLOOKUP関数について

VLOOKUP関数は

=VLOOKUP(検索値,範囲,列番号,[検索方法])

という引数(ひきすう)です。

 

VLOOKUP関数の引数設定(基本)

オレンジで囲んだところがリスト。商品名と単価です。

図では11行目に計算表を作成します。

VLOOKUP関数を使って、プルダウンメニューの商品名を選んだら<単価>と<金額>が反映されるように設定します。

 

1.掛け算を入力

掛け算を設定

今回は単価と数量を掛け算したら金額へ表示されるようにしたいので11Eへ=C11*D11と入力します。

 

2.VLOOKUP関数を選ぶ

検索/行列 VLOOKUP

VLOOKUP関数を設定したいセルを選んだら数式タブの<検索/行列>→<VLOOKUP>

 

3.VLOOKUP関数の引数を入力する

VLOOKUP設定画面

VLOOKUP関数の引数を設定する画面です。

検索値から検索方法まで入力すると

=VLOOKUP(検索値,範囲,列番号,[検索方法])

をセルに設定できます。

では検索値から順に説明していきますね!

 

 

プルダウンメニュー

検索値とは何を調べたいか?ですから今回は花ですね。プルダウンメニューのあるB11です。

 

 

タイトル

範囲はリスト(花と単価)のB2:C9。左クリックでドラッグして範囲を選択できます。

列番号もリストを見ます。(左から何列目かを入力)1列目は花の種類、2列目が単価なので「2」です。

最後の検索方法ですが、0(FALSE)完全に一致する、1(TRUE)近似値のどちらかを選びます。今回は検索値(花の名前リスト)と一致する場合の単価なので完全に一致する「0」となります。

入力したら<OK>

VLOOKUP関数の設定ができました。

 

4.計算表の確認

VLOOKUP関数設定後

正しく設定されているか確認してみましょう。

プルダウンメニューで「桜」を選ぶと単価3500、そして金額も正しく計算され7000となっています。

正しく設定できました。

 

データと計算表をシート別にして見やすくする

基本の設定方法では1つのシートにリストと計算表を作りましたがデータが多いと窮屈ですね。

Excelのシートが違ってもVLOOKUP関数は使えるので便利。‪( ˘ω˘ )‬

シートを分けると見た目もスッキリします。

データと計算表を別々にした場合の方法を説明します。とはいえ方法はほぼ同じです。

入力方法のみ説明していますので、わからなくなったらVLOOKUP基本編をご覧ください。

 

1.シートを増やす

エクセルシート

+をクリックしてシートを増やしリストと計算表に分けます。

シート名をダブルクリックするとシート名を変更できます。

 

2.別シートのプルダウンメニューを作る方法

データの入力規則画面

<入力値の種類>はリスト。

<元の値>を一度クリックしてシート名<リスト>にあるB列の商品名を左クリックしながらドラッグします。

自動的に範囲が表示され、違うシートを選んだので<元の値>にリスト!がつきました。

(ちなみにリスト!の後ろの$は絶対参照です。絶対参照について詳しくお知りになりたい場合は絶対参照と相対参照などで検索してみてください。)

 

3.別シートのVLOOKUP関数の引数を設定する方法

VLOOKUP関数の引数

検索値にVLOOKUP関数を表示させたいセル番地を入れます。

範囲はシート名<リスト>の(花と単価)を左クリックしながらドラッグし指定。

列番号はリストを左から数えて何番目か、検索方法は絶対一致の0でしたね。

 

4.別シートを使った計算表を確認

VLOOKUP関数の設定できました

B17にプルダウンメニューを設定しました。

カーネーションを選ぶと単価が2400、そして数量2にすると金額が自動的に計算されました。

VLOOKUP関数の設定完了です。٩( ‘ω’ )و

 

 

エラー表示の際の設定IFERROR関数

プルダウンの空白を選択

IFERROR関数を使ってエラー時の表示を設定します。

上図では<単価>と<金額>がエラー#N/Aになっていますね。

最初にプルダウンメニューを設定した時に、後から商品名を追加できるように空白のセルを作っておきました。

先ほど確認した時、計算表は正しく表示されたのでVLOOKUP関数の設定はうまくいっています。

ところがプルダウンメニューの<桜>の下(カーソルが指す青の部分)を選ぶと

まだ何も入力していない箇所なので#N/A=該当がありません、と表示されてしまいます。

このままでも問題ないですが、見た目が良くないのでエラーが出た時の表示をIFERROR関数を使って指定しようと思います。

 

IFERROR関数の設定方法

1.VLOOKUPの引数へIFERRORをつけ足す

VLOOKUP関数

エラーが表示されたC11のオレンジの四角で囲んだところにVLOOKUP関数の引数が表示されるので下記の赤字部分を追加します。

=IFERROR(VLOOKUP(B11,B2:C9,2,0),”正しく入力して”)

「””」の間に文字を入力すればエラーの時セルに表示されます。

11Eの<金額>もエラーになっているので同じようにIFERROR関数を使います。

 

2.掛け算にIFERRORをつけ足す

IFERROR関数

11Eのセルの掛け算がオレンジの四角で囲んだところへ表示されるので赤字部分を追加します。

=IFERROR(C11*D11,””)

「””」の間に何も入力しなければ空白になります。

 

 

まとめ

プルダウンメニューとVLOOKUP、IFERROR関数を使って商品名を入れたら金額を反映させ計算させる方法をご説明しました。

VLOOKUP関数は設定すると、その後の作業がとても楽です。

もっと幅広い使い方を知りたい場合は「VLOOKUP 使用例」「VLOOKUP 近似値」などと検索すると良いかと思います。

 

1度設定すると商品などを追加する以外ほったらかしですが、時間が経過すると設定そのものを忘れてしまうので自分の覚書にもしたく記事にしました。

自分で作ったExcelの引数を見てもどうやって設定したか思い出せないんですよね(´ε`;)ウーン…

VLOOKUP関数設定からのIFERROR関数の流れを覚えていないせいだと思い、1記事ですべての流れを説明しました。

個別に調べるとわりに時間かかるもんなのです。

コメント