ちちくろ目線 
こんにちは、ちちくろです♪
前回に・・リストを使ってタイピングの少ないExcelワークシートの説明をしたのですが・・。
もう少しタイピングを減らすことはできないか・・そう思ったのですが・・。
そこで・・単価をタイピング入力するのではなく・・別の単価表から取り込むのは・・。
単価をタイピングミスすると・・結構大変なことになるかも・・。
タイピングミスを無くすには・・タイピングを減らすことが近道ですよね♪
そこで今回は、リストから品名を選択すると・・単価を取得できるそんな方法をご紹介いたします♪
目次
VLOOKUP関数
今回、主に使用する関数は・・VLOOKUP関数・・。
VLOOKUP関数とは・・セル範囲の縦方向から指定されたデータを検索し・・指定した横方向のデータを取り出すことが出来る・・。
そんな関数です。
一般的な単価表って・・品名の横に単価が記載されているので・・品名を検索しその横の単価を取り込もうと負いことです。
それではVLOOKUP関数の使い方を次項でご説明します♪
使い方
まずは・・VLOOKUP関数の使い方を説明します・・説明するために図のようなワークスートを作ります。
セル範囲H2～J6を使って果物の単価表を作成し・・セルA2に品名を書き込むとセルB2に単価が表示される・・そんなワークシートを例にご説明しようかと・・。
セルB2にVLOOKUP関数の数式を書き込みます・・VLOOKUP関数の書き方は “=VLOOKUP(検索する値,検索する範囲,列,検索方法)”
検索する値とは・・セル範囲の縦に検索する値でこの値は都度変化する必要が有るため・・主に参照先セルを指定することになります。
検索する範囲とは・・検索する表の範囲を指定します・・通常は絶対参照で指定することに・・セル範囲を名前で指定することも可能・・。
列は・・検索したセルから横方向に移動する数・・。
検索方法は・・0の場合FALSで 1の場合はTRUEとなります・・より詳しく説明すると・・。
FALSの場合は・・検索地と完全に一致したセルを検索し・・TRUEの場合は検索する値に近いセルを検索します・・。
通常はFALSで使用するのが一般的・・。
使用例
VLIIKUP関数の使用例を説明・・。
セルA2に果物の品名を書き込むとセルB2に単価が表示される・・そんなワークシートを作る場合・・。
H2～J7に果物の品名と単価を記入・・①
そして・・B2にVLOOKUP関数を書き込む・・②
B2のVLOOKUP関数の内容は・・ “=VLOOKUP(A2,H2:I7,2,0)”というもの・・。
VLOOKUP関数の内容を詳しく説明すると・・。
検索する値は・・A2なのでセルA2の値で・・検索する範囲は・・H1:I7なのでセル範囲A1～I7・・列は・・2なので2列移動するということに・・。
最後に検索する方法 は・・0なので・・FALS なり ・・完全に一致するセルを検索するということになります。
A2に果物の品名を書き込むと・・③
動作を見てみる
それでは・・ワークシートの動作を見てみることに・・。
セルA2にリンゴと記入すると・・①
VLOOKUP関数がセルH２を検索し・・②
2列目の50を選択しセルB2に表示される・・③
ということに・・。
続いて・・。
セルA2にバナナと記入すると・・①
VLOOKUP関数がセルH3を検索し・・②
2列目の70を選択しセルB2に表示される・・③
このようにA2のセルを打ち換えると・・B2の単価が変化します。
これがVLOOKUP関数です♪
VLOOKUP関数で単価を取込
そこで・・VLOOKUP関数を使うと品名を入力するだけで単価を記入するようにしようかと・・。
単価表を作れば簡単にできそうですよね♪
重要なのは単価表の管理
VLOOKUP関数で単価記入する場合・・単価表を厳格に管理する必要が有ります・・。
単価が変更になったら・・単価表も変更しておかないと・・チョット大変なことになるので・・。
そのためには管理しやすい単価表を作ることが重要なのでは・・？
ただ単調に・・ランダムに品名と単価を記載するよりも・・カテゴリごとに分類して管理したほうが良いと思うのですが・・。
VLOOKUPとINDIRECT
単価表の管理を容易にするために・・VLOOKUP関数とINDIRECT関数を併用してみることに・・。
ここでは前回明細書のワークシートを使って説明していこうかと・・。
なにそれ・・？という方は・・下のリンクからどうぞ・・。
dataシートの変更
まずは・・dataのワークシートを変更していきます・・。
各カテゴリの・・品名の横に単価を記入していきます。
各カテゴリの【果物】・【野菜】・【鮮魚】・【精肉】の隣に・・単価の列を作成・・。
そして、各品名に単価を記入 していきます♪
全て記入が終わったら・・単価表に名前を付けるようにします・・。
名前の付け方は・・カテゴリ名＋単価としようかと・・。
カテゴリ【果物】の場合は・・D2～E20を選択し・・名前ボックスに（【果物】単価）と書き込みます・・。
同様に【野菜】・【鮮魚】・【精肉】もセル範囲に名前を付けるようにしましょう♪
数式
メインのワークシートに戻り・・数式を記入していきます。
単価の列・・F列の数式は、 “=IF(OR(ISBLANK(D2),LEFT(D2,1)=”【”,D2=”小計”),””,VLOOKUP(D2,INDIRECT(CONCAT(K2,”単価”)),2,0))”と書き込むことに・・。
IF関数の各部の説明を・・。
OR(ISBLANK(D2),LEFT(D2,1)=”【”,D2=”小計”),””,とは・・セルD2が空白もしくは1文字目が”【”もしくは”小計”ならば空白にしなさいということに・・。
そして・・上記以外の場合は ・・。
VLOOKUP(D2は・・VLOOKUP関数でセルD2の値を・・
セル範囲は・・,INDIRECT(CONCAT(K2,”単価”))と・・これは・・CONCAT関数でK２の文字に単価を付け足したものを・・INDIRECT関数でVLOOKUP関数に返すということ・・。
最後に・・,2,0))というのは・・2列目のデータを取り出しなさいということと・・検索方法は完全に一致で検索する世いうことに・・。
これで完了♪
誤動作
動作を確認することに・・すると・・。H列とI列がおかしいことに・・。
H列の数式は・・”=E2*F2″というただの数式・・。
単価のF列に数式が入ったことで・・計算が出来ないのでエラーになったようです・・。
そこで・・H列の数式を変更・・。
変更内容は・・”=IF(ISBLANK(E2),0,E2*F2)”というもの・・。
この数式の内容は・・。
IF(ISBLANK(E2),0で・・数量のE列が空白なら０にするということ・・。
それ以外の場合は・・E2*F2 E2とF2を掛け算するというものに・・。
すると
図のように・・。
動作確認
改めて動作を確認を・・。
A列からG列のセルに注目してください♪
セルD2にカテゴリ【果物】を選択し・・セルD3にリストからリンゴを選択すると・・単価F３のセルに100が表示されます♪
さらに・・リンゴの数量5をE3のセルに記入すると・・価格の列G３のセルには500が表示するように・・。
続けて表の4行目に品名はみかんを書き込み・・数量は50を・・5行目に品名はブドウ・・数量は2と書き込みます・・。
最後に品名に小計を選択すると・・G6のセルにカテゴリ【果物】の合計金額が表示されるようになります♪
これなら・・明細書を作るときにタイピングを少なくすることが出来ますよね♪
まとめ
単価は日々の変動は少ないので単価表を作って管理するのが良いかと・・。
しかし・・単価表を厳格に管理することが重要・・厳格に管理するには・・見やすく来ることが大事・・。
品名をカテゴリ別に分けた管理するのが良いかと・・そのうえで価格の変動があれば都度修正し・・品目が増えても対応しやすくすることが大事かと思います♪
それらの管理をキッチリとしさえすれば・・Excelでタイピングミスを防げること思うのですが・・いかがでしょう。
今回の方法は・・私が現役時代に試していた方法です・・もっと良い方法があるよという方はコメントで教えてくだされば幸いです。
この方法を採用される方は・・自己責任でお願いしますね♪
