使い方が難しくよく聞かれる関数の一つvlookup
わかりにくいのですが使えるようになると非常に便利な関数です
この関数を使い倒して効率化をはかりましょう
関数の説明は少なくし、実際に見積書を作りながら説明します
vlookupの基本
=vlookup(検索値,検索範囲,列番号,検索方法)
検索値：検索するセル、または値
検索範囲：検索値が入力されている列から２列以上を指定します
列番号：検索値が入力されている列から表示したい列が何列目にあるかを指定します
検索方法：TRUEかFALSEを指定します
TRUE:近似値。近い値を検索します
FALSE:完全一致
この後、使い方を説明していきますのでわからなくても安心してください
何はともあれまず使ってみましょう
見積書を作ろう
多くの方が見積書を作る際、テンプレートに直接記入して計算式を入れるという方法を使っていると思います
今回はテンプレートへ記入するのではなく、商品リストなどから検索して見積書を作ります
商品テーブル：商品名、品番、単価、在庫などのテーブル
割引率リスト：数量によって割引率を決める為、数量と割引率のリスト
を作り、
見積用データ抽出シート
で必要な情報を集めて
見積もりシート
で印刷用のレイアウトをするという流れになります
シート作成
必要になるシートを４つ作ります
見積書
見積用データ抽出
商品テーブル
割引リスト
テーブルの作成
商品テーブルから作ります
先ほど作った商品テーブルのシートへ下記の項目で作成します
商品ID：データベースではよく使われるID。重複しない番号
商品名：商品名
品番：商品の番号
メーカー：製造、または販売しているメーカー
購入先：商社やネットなど
単価：販売の単価
在庫：在庫数
※IDの必要性は都度変わります
例えば商品名が同じで違うものを識別するといった時には必要になります
細かくは割愛します
今回は将来、データベースへ移行する事を想定してIDを入れてあります
今回は文房具をリストにしました
次に割引率テーブルを作成します
割引率はこのように設定します
１から49個：０％
50から99個：５％
100から199個：１０％
200から499個：２０％
500から999個：２２％
1000個以上：２５％
注意！
この時、B列の数量は昇順で作る事
作成時にランダムで作ってしまった場合、その後昇順で並べ替えてください
見積用データ抽出シート作成
新しく見積用データ抽出のシートを新規に追加します
まず必要になる項目をピックアップして列べました
この並べ方は印刷した時に見やすい並びとしてあります
黄色のセルへ情報を入力すると白色のセルへデータが表示されるように作っていきます
関数は下記の様に入力します
品番：C４
=vlookup(B4,商品テーブル!B:C,2,false)
B4（入力された品名）をシート：商品テーブルのB列から探し、全く同じデータが見つかったら右２列、B列を含むのでB、CでC列のデータを表示させます
単価：E4
＝vlookup（B4,商品テーブル!B:F,5,false)
割引率：F4
=vlookup(D4,割引率テーブル!B:C,2,TRUE)
発注数によって割引率を決めます
D４（発注数）をシート：割引率テーブルのB列でD４以下の最大値を表示します
今回の割引率の場合、
D４＝49の場合49以下の最大値＝１で０％
D４＝50の場合50以下の最大値＝50で５％
割引率テーブル作成の注意点にも書きましたが、検索元の数量が昇順になっている必要があります
TRUEで検索をさせたい場合はデータを昇順に並べ替えると覚えてください
ここからは少し難しい話なので興味のある方だけ
検索範囲を上から検索するのでD４の値以上のデータが出てくると、そこで検索が止まり、その前のデータから探します
その為、昇順になっていないと今回の様な割引率は正しく適用されません
売値：G４
=round(E4(単価）-E4(単価）*F4（割引率）,0)
単価×割引率で小数点以下が出てしまうことがあります
なので今回はround関数で小数点以下を四捨五入します
これ以外にroundup関数、rounddown関数もありますので運用に合わせて使ってください
表示の設定で小数点以下を表示させない方法がありますが、
これをやってしまうと失敗の原因
表示されていないだけで、データとしては小数点以下の値があり、計算結果に影響を及ぼします
在庫：H４
=vlookup(B3,商品テーブル！B:G,6,false)
残数：I4
=H4-D4
今回は残数がマイナスになったら赤い文字で表示させたいので「セルの書式設定」→「表示形式」→「数値」で図の様に「-1234」を選択します
ここまで作成し、計算式をコピー、見積もりしたい商品、数量を入力するとこんな感じになります
品名、数量が入力されていないと#N/Aのエラーがデータよ目に入ってしまいます
入力欄の修正
出来た表を見ると入力欄がB列とD列で離れています
見た目はこの方が良いのですが、入力しやすくするにはC列とD列入れ替えておきます
こうしておくと入力がまとまっていて使いやすい入力用シートになります
エラー処理の追加
ここからエラー処理を追加します
２つのエラー処理を紹介します
エラー処理1は品名、発注数にデータが何もない場合に発生するエラー処理
エラー処理２は品名、発注数にデータがなかった場合と商品テーブルにない品名が入ったり、発注数が数値でなかった場合のエラー処理です
それぞれ説明していきます
エラー処理１ 品名、発注数が未入力の処理
品名、発注数にデータが何もない場合に発生するエラー処理です
エラー処理１を入れるとこんな結果が出ます
品番：C４
＝if(B4=””,””,vlookup(B4,商品テーブル!B:C,2,false))
商品B４に何も入力されていなかった場合（b４＝””）は何もデータがない（””）、そうでなかったら検索した結果（vlookup(B4,〜））を返します
この様な場合、if関数を使っていきます
if関数とは
もし（if)条件文と一致したら、この処理、そうでなかったらこの処理
と書きます
書く場合はこの様に書いていきます
=if(条件文,条件に一致した場合の処理,条件に一致しなかった場合の処理)
単価：E4
＝if(B4=””,””,vlookup(B4,商品テーブル!B:F,5,false))
割引率：F4
=if(C4=””,””,vlookup(C4,割引テーブル!B:C,2,false))
売値：G4
=if(or(E4=””,F4=””)=true,””,round(E4-E4*F4),0))
同じ様にif関数を使うのですが、売値の場合、単価と割引率に数値がないと計算出来ません
その為、or関数を使って、E4が空欄、又はF4が空欄の場合はデータなし（””）としています
or関数は条件１か条件２か・・・どれかの条件を満たした場合TRUE、そうでなければFALSEを返します
=or(条件１,条件２,・・・）
と書くとセルにはTRUEかFALSEが表示されます
在庫：H4
=if(B4=””,””,vlookup(B4,商品テーブル!B:G,6,false))
残数：I4
=if(or(C4=””,H4=””)=true,””,H4-C4)
こうすると品名、数量にデータがなければ表示されません
今回、if(セル=””,””,〜）を使いましたが、isblank関数を使っても同じことが出来ます
これを入れても商品テーブルに登録されていない商品や数量に数値ではないデータが入力された場合、エラーが発生します
エラー処理２ 品名、数量数が未入力、データが間違っている場合の処理
エラー処理１を行なっても、まだエラーが出ています
これは
商品テーブルに記載されていない品名がB4へ入力した場合検索結果がない
発注数へ数値以外が入力されていて割引率が検索出来ない、売値が計算出来ない、残数が計算出来ない
からエラーが表示されています
エラー処理２を入れた時の結果になります
ここまでエラー処理すれば表としては使えるものになってくるかと思います
（入力された発注数が数値以外の時、品番、単価、在庫を表示させない様にすると完璧ですが、この処理入れるとわかりにくくなるので今回は割愛します。知りたい方はお問い合わせください）
品番：D4
＝if(iserror(vlookup(B4,商品テーブル!B:C,2,false))=true,””,vlookup(B4,商品テーブル!B:C,2,false))
商品が未入力、又は商品デーブルにないデータが入力された場合、vlookupはエラーが表示されていました
エラー処理１と同様、if関数を使って、もし条件に一致する場合はデータなし（””）、そうでなければvlookupの結果としています
ここでの条件とはvlookup関数の結果がエラーかどうかです
そのエラーを調べるのがiserror関数なのです
iserror関数でセルに返ってきた値がエラーかどうか調べて、エラーの場合（=true)はデータなし（””）、そうでない場合はvlookupの結果を返します
単価：E4
＝if(iserror(vlookup(B4,商品テーブル!B:F,5,false))=true,””,vlookup(B4,商品テーブル!B:F,5,false))
割引率：F4
=if(iserror(vlookup(C4,割引テーブル!B:C,2,false))=true,””,vlookup(C4,割引テーブル!B:C,2,false))
売値：G4
=if(or(E4=””,F4=””)=true,””,round(E4-E4*F4),0))
=if(iserror(round(E4-E4*F4),0))=true,””,round(E4-E4*F4),0))
売値の場合、エラー処理２を書くと単価、割引率は数値かデータなし””のいずれかになります
その為、or関数、他のと同様、iserror関数のどちらでも使えます
在庫：H4
=if(iserror(vlookup(B4,商品テーブル!B:G,6,false))=true,””,vlookup(B4,商品テーブル!B:G,6,false))
残数：I4
=if(iserror(H4-C4)=true,””,H4-C4)
数式を保護しよう
作った数式を保護します
計算式（D4からI21)をロック、それ以外はロックを解除してシートの保護を掛けます
シートの保護についてはこちらの記事を参考にしてください
【実践Excel】数式・計算式が変わらないようにシートの保護
見積書を作ろう
ここまでで見積書のベースになる計算をしてきました
最終的な見積書を作ります
今回作りたいのはこのような見積書です
計算式について説明します
この帳票の中で数式が入っているのは黄色のセルの部分です
見積書へは見積用データ抽出シートへ入力し、計算した結果をそのまま代入します
※今回は説明を簡略化する為直接データを代入します
合計金額：D15
=L32
摘要：B18
=if(見積用データ抽出!B4=””,””,concatenate(見積用データ抽出！B4,” “,見積!C3)
摘要へ入れたいのは品名と品番です
品名、スーペース、品番となるように関数を書きます
concatenate(a,b,c)とすると文字を結合してくれ”abc”となるので今回はこれで作ります
a&b&cでも同じになります
数量：J18
=if(B18=””,””,見積用データ抽出！C４）
単価：L18
=if(B18=””,””,見積用データ抽出！G４）
合計
=if(and(J18<>””,L18<>””)=true,J18*L18,””)
<>とはNOTイコールです
J１８が””でなく、且つ、L１８が””でない場合、すなわちJ１８、L１８どちらにもデータが入っている場合という意味になります
まとめ
見積書を作りながらvlookupの使い方を説明してきました
いかがでしたか？
関数を使えるようになることも大切ですが関数を有効に使えるデータ作りの方がもっと大切です
ここを注意して作ってみてください
ちなみに今回は説明しやすいので見積書を題材にしました
しかし、今回の表で見積書を作ろうとすると非効率な部分があります
あくまで学習用としてください
見積、請求書などを効率良く作成する方法もコンサルいたします
お問い合わせください
注意点
例 =vlokup(A1,C1:E10,3,false)
1.一覧を作るときは検索したいデータを一番左へ
検索範囲（C1:E10）の一番左側に検索したいデータが入っている列を指定してください
この場合、C1からC10の範囲を検索します
そして最初に作るテーブルも関数が使える事を想定して作る必要があります
2.列番号は検索範囲の左側から数える
私も最初の頃は何列目かをよく間違えました
検索範囲（C1:E10）でE列のデータを表示したい場合、C=1列目、D＝２列目、E=３列目なので関数へ入力する列番号は３になります
大きな表になると列番号を数えるのに間違えたり、手間が増えますがあります
列を選択すると列数が表示されるのでこれを使うと便利
図の1048576R×4Cは選択されているのは1048576行×４列の意味です
これを使うと便利です
3.検索方法を想定したデータ、入力データ作り
完全一致で検索したい場合、例えば見ても見えないスペースが入っているだけで違うと認識されます
また、数字の文字列、数値の違いでも違うと認識します
検索元になるデータはtrim関数,asc関数,その他文字列処理の関数を駆使してルール化されたデータを作っておくことが大切です
きっと使いこなせる様になると必ずぶつかる、データがあるはずなのけ検索されないのは、この様な原因が多いです
検索元になるデータの作り込み、入力するデータのルール化が大切です
4.エラー処理を入れておく
検索方法 完全一致の場合、見つからないと”＃N/A”のエラーが表示されて表として見難いものになります
iserror関数はif関数などを使って表示されない様にしておくと見やすくなるし、計算も可能な表になります
エラーの値が入っていると容易に計算出来ませんので綺麗なデータが集まる様に発生しうるエラーを想定して対策を取っておきましょう
