さぁ、関数の最大の山場、ついにVLOOKUP関数に手を付けます。
面接でよく行われるやり取り。
「エクセル使えますか？」
「使えます！」
「どのくらいですか？VLOOKUP使えます？」
「・・・」
ここでの返答で、どう答えるかでだいたい判断されてます。
だからこそ、はっきりと「VLOOKUP使えます！」と言えるようにしておきましょう。
VLOOKUP関数をしっかり理解するためには、データベース構造が頭に入っていた方が早いと思います。
何がどうなっているのか分からないまま式を覚えるから自信を持って「使えます！」と言えないのです。
お店のレジを使ったことがある方、使ったこと無くても良く見ていれば気付く簡単なことですが、商品のバーコードを読み取ると品名と単価が自動で出てきます。
これは、商品マスタテーブルという商品の情報だけが記録されているデータベースから、必要な情報を呼び出しているということなんです。
POSシステムを作ったハンバーガー屋さんの商品になぞらえて考えてみましょう。
こんな情報が入っている表がまず必要です。
これを商品マスタテーブルと呼びます。
そして、ハンバーガーの注文が入ったらレジスターのハンバーガーのボタンをぽちっと押します。
そしたら、レシートに品名と単価が出て、数量を選ぶ。という手順です。
レシートに表示するのが、下記だとしてレジのボタンを押す回数をなるべく減らそうと思います。
入力しなければいけないのは、
1、何の商品なのか
2、数量
だけのはず。
じゃあ、何の商品なのかをボタンひとつで出るように表計算ソフトでもやるとするなら・・・。
セルに「1」を入れたら品名と単価は自動でハンバーガーと￥100と入ってほしい！
次に数量を入れたら、小計からお支払合計まで自動で表示！
この「品名と単価は自動で入ってほしい！」がVLOOKUPです。
単価と数量が入ればお支払合計まで自動で出るのは、ここまでの項で簡単に出来ますよね？
B列とC列にはあらかじめ式を入れておき、A列を入力したら表示されるのは、これと同じことです。
さぁここで問題です。
表が2つ存在してしまっているのに、どうやってやればいいの？
やり方としては2つです。
Ａ：商品マスタテーブルを邪魔にならない場所にまとめて、レシートの表を作る。
Ｂ：商品マスタテーブルを別のシートに作って、別のシートから参照できるようにする。
まずは、分かりやすいＡの方法から。
まずは、こういう構造で表を1シート内に2つ作ります。
そして、B9セルにVLOOKUP式を入れます。
=VLOOKUP($A9,$A$3:$C$5,2,0)
すると、セルに
#N/A
というエラー値が出ます。
そこで、A9セルに1～3の数字を入れます。
どうでしょう？
欲しかった品名が出ましたでしょうか？
VLOOKUP関数は
=VLOOKUP(検索値、範囲、列番号、検索の型）という4つの情報を必要とします。
検索値：A9セルの値で
範囲：A3からC5（商品マスタ部分）の範囲から、
列番号：2列目の品名を、
検索の型：完全一致（0）で返す。
という指定の結果が表示されるというわけです。
では、C9セルにVLOOKUP式を入れてみましょう。
=VLOOKUP($A9,$A$3:$C$5,3,0)
今度は単価が欲しいので、列番号が3になります。
この式がレシートの必要な範囲に入っていれば、Ａ列にIDの数字を入力するだけでどんどん品名と単価が自動で表示されるということです。
オートフィルタで広げておきましょう。
ところでこのお店は、3つしかメニューが無いのでしょうか？
そんな訳はないし、これからメニューが追加されることも、削除されることも、修正されることもあるでしょう。
そうすると、そのたびに商品マスタ部分の表をずらしてしまうため、指定範囲が変わってしまうのです。
そこで、分かり難くてもＢの方法が必要になってきます。
まず、シートという考え方を理解しましょう。
スプレッドシート画面の下部に「シート1」という表記があるかと思います。
その左側に「+」（シートを追加）ボタンがあるので、押してみましょう。
すると「シート2」というのが追加されたかと思います。
これがシートというものです。
表計算ソフトは複数のシートで構成されたブックという形式になっています。
ブックというのは最初に変更した
「無題のスプレッドシート」
の名称のところです。
ブック内の別シートは、計算式で参照できるようになっているので、表が2つなら2シート使って、シートを超えて参照することが出来ます。
ということで、改めてＢの方法で表を作ります。
このデータが入っているシート名を「商品マスタ」とします。
でレシート部のシート名を「レシート」とします。
では、レシート部のVLOOKUP式はどう書いたらいいのか、やってみましょう。
=VLOOKUP($A2,’商品マスタ’!$A:$C,2,0)
=VLOOKUP($A2,’商品マスタ’!$A:$C,3,0)
これで、商品マスタが変更されても大丈夫な構造になりました。
さぁ、ここまで出来た上で、自分で作ってみたい表を作ることが出来たら「VLOOKUP使えます！」「エクセル任せてください！」と、とりあえず宣言して大丈夫です。
おまけ情報その1
今回は分かりやすくするためにシート名を日本語で指定してますが、パソコンは英語を基準として作られているため参照リンクは英数表記にした方がベターです。
syouhinn_master
とかしておいた方がデータベースっぽいのでオススメです。
おまけ情報その2
商品マスタで、追加、削除、更新がしやすいのがＢの方法ですが、参照したままのデータを使う形の場合、更新してしまうと過去の情報も変更されてしまいます。
たとえば、ハンバーガーをセールで￥80にするから商品ID1の単価を修正更新してしまうと、過去の販売データも全て￥80になってしまいます。
これを解決する方法として、たとえばレシートの方に割引の列を入れてしまうとか、レシートの方でデータを参照するけど、参照されたデータは必ず値コピーをするようにさせるとか、色々あるんですがオススメはこれ。
違う商品として登録すること。
ハンバーガーとは別に「特別価格ハンバーガー￥80」の商品を登録してしまうのです。
すると、過去データも問題ないし、次のセールでも￥80もそれ以外の価格も使えるようになります。
マスタIDがJANコード基準で同じJANを使う時には、それが通じなかったりするんですけどね。
何言ってるか分からない方は、読み飛ばしちゃってください。
理解する必要は無い話ではあります。
コメント