初心者がExcelを勉強しようとしたとき、VLOOKUP関数という言葉を聞いたことがある方も多いでしょう。VLOOKUP関数はExcelの中でも特に便利な使い方の一つと言えます。しかし、VLOOKUP関数ってなんでしょうか？どんな使い方をするもので、どんなことに役立つのか、それを本当に知らなきゃいけないのか？という気持ちも芽生えてくるのは無理もありません。VLOOKUP関数を使うことは、Excel初心者にとって、時には高い壁のように感じることもあるのです。
Excelは、これから会社がどう進んでいけばいいのかを考えるサポートをするために作られた、仕事とは切っても切れないものです。数式、関数、グラフを駆使すれば、その集計結果があらゆるストーリーを語りかけてくれるでしょう。そしてその中でもVLOOKUP関数を使いこなすことは重要で、VLOOKUP関数を使えることが、Excelができる人のひとつの基準になっています。でもVLOOKUP関数の使い方を学ぶことは、一見ハードルの高い壁を乗り越えるようなものかもしれません。しかし、VLOOKUP関数の奥深さに立ち向かうためには、まずその扉を開けることから始めなければなりません。
VLOOKUP関数が苦手な人というのは、Excelにある原理を理解していない人です。つまり、その原理を理解すればVLOOKUP関数は攻略できるのです。
この原理はVLOOKUP関数だけのものではなく、Excel全体に影響しているものなので、この原理さえきちんと抑えておけば、Excelのあらゆることが面白いように理解出来てきます。
この記事では、その原理を丁寧に説明し、その上でVLOOKUP関数の有効性について理解を深めていきます。
Excelの原理
Excelには、使う上で絶対に守らなければならない原理があるのですが、しかし、その原理に従わなくても使えてしまうので、それが初心者にとっては混乱の元になっています。たくさんある中でも今回は２つ紹介します。「適切な計算方法」と「使いやすい一覧表」というお話です。
適切な計算方法
例えば、セルに100、200、300と入力されているのに、その合計欄には「=100+200+300」と入力しています。
何が間違っているのでしょうか。全く何も間違ってはいません。数学的には。しかし、この方法はExcelとしたら0点です。Excelはセルに値が入力されているのであれば、そのセル参照を使って計算しなければならないという、守らなくてはいけない原理があります。
なぜそうするか、それは、もし、「100」が本当は「150」だったらセルに入力されている「100」を「150」に書き換えたうえに、計算式上の「100」を「150」に変える必要があるからです。
セル参照を使った計算式であれば、セルの値を変えるだけで計算の結果も変わります。
たったひと手間だけ、と思うかもしれません。しかし、そのひと手間がビジネスでは命取りになるかもしれません。2か所を直すということは、1箇所だけ直してもう１か所を直さない可能性があるということです。これがビジネスで起きた場合、大きな損失が発生します。
以上を考えると「=B2+B3+B4」といった計算式になります。
これなら大丈夫でしょうか。実はこれもExcel的には0点です。
ここではよく考えなければならない大事な原理があります。今回は「合計」を求めるのです。それに対し、この式は「足し算」をしています。言っている意味はどちらも同じに聞こえますか？
Excelでは、足し算は「+」で値同士を連結します。合計は、合計を求める関数を使い「=SUM(B2:B4)」とします。
この違いです。何が違うでしょう。これは日本語にすれば明確にわかってきます。足し算では「B2足すB3足すB4」です。合計は「B2からB4の合計」です。足し算の場合はセルをひとつずつ計算するのに対し、合計は範囲で指定できるのです。つまり、合計というのはより大きい範囲に入力されている値を合算するのに対し、足し算は数個の値を合計するという違いがあります。
今回は3つのセルなのでそのどちらかというのははっきりしないようにも見えます。しかし、今回の場合は「販売金額1」から「販売金額3」までの同じ「販売金額」という種類の値の合算です。足し算は違う種類の値を数個足し合わせるという意味の違いがあります。
このように、表内の計算値が、何を表しているのかで、どんな計算方法を使うのか意識して考え、何が一番正しいことなのかを判断するのが、Excelの原理なのです。
使いやすい一覧表
Excelはデータをグループに分けて集計することによって、一見見えていない傾向をわかりやすくするものです。
次の表は、ひとつの記録の形式です。
人間が見れば同じ列でも上段と下段で違うものが入っているというのがわかります。しかし、Excelにとっては、A列が販売日なのか単価なのかわかりません。B列は商品なのか個数なのかわかりません。このようにExcelは1つの列には1つの項目しか入っていないとしか認識できないと考えるのが基本です。
確かにExcelでも無理をして変化球で上段と下段を分ける方法がありますが、それはExcelに無理をさせることになり、間違いの元になります。「無理すればできるじゃん」と考えるのはどんどん使いにくくしできることもできなくしていく発想なのです。
次の左右の2つの表はExcelとして正しい表の例です。
左側の表は、いつ何を何個売っていくら売り上げたというのがわかる販売一覧表です。これだけだと8月11日の商品Ａの売上金額が1番高いのはわかりますが、どの商品が、一番売上金額が高かったかというのは、売り上げた商品ごとに分類し、その合計を求めなければなりません。Excelにはそのグループごとに仕分けし合計などを求める、集計する機能や関数がたくさんあります。その機能を使って作成したのが右の表です。
商品Aが一番売れていて、商品Cが一番売れていないことがわかります。
このようにExcelで扱われる表は、集計の元になる表と、集計した結果の表の2通りがあります。今回はこの2つのうちの、元になる表についてのお話です。
Excelでの集計は、グループに分けて行います。「商品A」だけを探してその合計を求めます。そのためには、グループに分けるための商品名は1列の中に入っていなければ、探すことができません。また、合計する値も1つの列に入っていなければどこを合計するのかわかりません。つまり同じ種類のものは1つの列にまとまっていなければExcelは集計できないのです。また商品に対しての金額といったペアになるものは同じ行に入っていなければいけません。そして見やすいように1行目にはそれぞれの列が何を表しているかの項目名がある。これが一覧表で絶対に守らなければならない原理です。
言い換えれば、１つの項目は1列に、1件のデータは1行にということになります。元になる表はこうなっていなければならないのです。
このような表は、フィルターや並べ替えができる表です。また、このような表の構造に必ずするようにExcelにはテーブル機能が用意されています。
この原理は、あくまで集計の元になる表が対象で、それ以外の表はどんな形でも自由です。
VLOOKUP関数
「VLOOKUP関数」をここで説明しましょう。VLOOKUP関数は、2つの表の情報をくっつけるのに利用します。しかしこれだとVLOOKUP関数の説明としては不十分で、もっと正確に言うと、一覧表から値を探し、見つかった値と関連付いている値を求める、ということになります。2つの表の情報をくっつけるというのはその結果できることなのですね。
次の例であれば、左の販売一覧表に対して、右側に商品単価の一覧表があります。
この場合、青枠のC列の単価は空欄なので、赤枠のA列の商品名を、黄色枠のG2からH4のセル範囲から調べて入れていけばいいのですが、人がそれを1つのセルごとにやるのは大変ですよね。そこで使うのがVLOOKUP関数です。
求める範囲は青枠です。赤枠の商品名を探す値なので「検索値」、黄色枠の商品単価一覧表が探される範囲なので「検索範囲」です。検索範囲の2列目が今回ほしい単価の列番号です。
VLOOKUP関数で「検索値」を探すのは「検索範囲」の一番左の列からです。そう決まっています。
この場合、次の関数の形となります。
=VLOOKUP(検索値,検索範囲,列番号,FALSE)
この形に実際のセルをあてはめていくと次のようになります。数式はセルC2に入れるものとします。
=VLOOKUP(B2,$G$2:$H$4,2,FALSE)
検索値はセルB2ですね。一覧表範囲はG2からH4です。今回入力する計算式は下にコピーしますので、一覧表範囲は下にずれないように絶対参照にするための「$」が付いています。列番号は2です。最後のFALSEは、ちょっと説明すると混乱の元なので、今はお約束でそう入ると思えておいてください。
このようにVLOOKUP関数を使うことで、本来は1つずつ一覧表を目で見て探して該当の値を入れていくという作業から解放されるのです。
このVLOOKUP関数では、検索範囲の表は少なくとも、使いやすい一覧表の構造になっていないとせっかくのVLOOKUP関数が使えないのです。
VLOOKUP関数を適切に使い単価を求めた結果、すべての販売に対し、単価と数量を掛け合わせた値を求めることができるのです。
しかし、仕事として考えると、この単価×数量を求めることがゴールではありません。つまりこのような場合には、VLOOKUP関数だけでは仕事を完結できないのです。
SUMIF関数
ここで、関数をもう一つ勉強しておくとExcelの理解が深まります。そのために紹介するのがSUMIF関数です。
SUMIF関数は、一覧表から指定した値を探し、その値の合計値を求める関数です。その結果、グループごとに分けた合計値の一覧を求めることができるのです。
この表であれば、黄色枠のI2からI4の範囲に、緑枠のG2からG4に合わせた商品ごとの合計値を求めます。商品は赤枠のB2からB13の範囲から探し、青枠のE2からE13の範囲の探したい商品と一致している行の値の合計を求めます。商品Aなら赤のマーカー部だけの合計を求めることができます。便利ですね。
これにより、集計を行うことができます。
SUMIF関数の使い方は、次の通りです。
=SUMIF(検索対象範囲,検索範囲,合計範囲)
まずセルI2に計算式を求め、I3とI4にその計算式をコピーしますが、そのセルI2に入る計算式は次の通りです。
=SUMIF($B$2:$B$13,G2,$E:$13)
まず、検索対象範囲ですが、今回は一覧表から商品を探すので、B2からB13の範囲です。この範囲は下にコピーしても位置が変わらないように絶対参照にしています。セルI2において、検索する商品は同じ行のセルG2です。セルG2は下にコピーしても同じ行を見てほしいので絶対参照ではありません。そして合計範囲は合計する値は計なのでE2からE13の値をこれもコピーしても移動したくないので絶対参照にします。
VLOOKUP関数と混乱しないでほしいのは、一覧表の範囲全体を選ばないことです。VLOOKUP関数は列番号で指定するので全体を指定しますが、SUMIF関数は範囲を列ごとで指定します。大事なポイントです。
また、この場合、集計されるのが左側の販売の一覧表です。この一覧表はVLOOKUP関数と同じように正しい一覧表の形式になっていなければなりません。逆にきちんとした一覧表の形式にすれば、VLOOKUP関数でも使えるし、SUMIF関数でも使える形になるということです。
これが使いやすい一覧表の構造に絶対にすべき理由なのです。
VLOOKUP関数の代わりにSUMIF関数を使っていいのか
Excelでは、はじめのVLOOKUP関数の代わりに、SUMIF関数を使うことができます。
セルC2に次の計算式を作成し、下にコピーします。
=SUMIF($G$2:$G$4,B2,$H$2:$H$4)
商品であるセルB2を商品単価一覧表の商品の項目であるG2からG4の範囲から探し、単価であるH２からH4の対応した行の合計を求めることで単価を求めることができるのです。これは商品単価一覧表に一つの商品が1つしか記録されていないので、合計と言っても1行だけの合計になるので、商品単価一覧表に書いてある単価が表示されている、というカラクリです。
もし、VLOOKUP関数が難しくてSUMIF関数を使っている場合、そのやりかたは間違いだと思います。
理由は実はたくさんあります。
-
VLOOKUP関数は探す関数で、SUMIF関数は合計を集計する関数なので適切な計算方法とは言えない
-
SUMIF関数を使った例では一覧表の中の数値しか求められない。文字列は合計できない
-
もし商品単価一覧表に同じ商品が2つ記録されてしまったら商品単価が2倍になる
-
VLOOKUP関数は改良され高速化したので大量のデータの処理が大幅に早くなった
Excelの原理として、適切な計算方法法を使うこと、想定外のデータが入ることも想定する、間違ってデータが入ることも想定する、大量のデータを処理することも想定しその中で一番早い方法を採用する、ということを絶対に守る必要があって、そのためにはSUMIF関数をVLOOKUP関数の代わりに使ってはいけないのです。
どうしても使わなければいけないケースもあるかもしれないですが、それでも絶対に守らなくてはいけないことは守る意識を持ちたいです。
そうしないとせっかくのパソコンを使っている意味がなくなってしまうからです。
まとめ
VLOOKUP関数を使える人がExcelを得意と言える理由は、次の通りです。
まず、VLOOKUP関数は正しい表を参照しないと正しく動作しないため、VLOOKUP関数を使える人は正確なデータ構造と表の形を把握しています。
また、絶対参照の正しい使い方を理解しており、関数を正確に設定できます。
更に、VLOOKUP関数を選べているということは、目の前の課題に対して適切な関数を選択し、効果的な方法で問題を解決する能力を持っているでしょう。
そして、これらを知っているだけではなく、何が正しいことなのかを常に考える意識こそが、Excelを正しく使うことに繋がるのです。
どうですか？Excel初心者には少し厳し目のアドバイスだったかもしれません。でも、Excelをなんかうまく使えてないなというときは、このアドバイスに一回耳を傾けてみて下さい。
Excelはパソコンで動作しているものですが、パソコンは使いやすくなってきたとは言え、まだ、原理を知らない、守らないという人にとっては使いにくい道具です。
しかし、人間の側からすこしだけ歩み寄ること、パソコンは手足のように動き始めるのです。パソコンは守りごとを守ってこそ思い通りに動く、そういった道具なのです。
車はアクセルを踏まないと進みません。安全に進むためには手でアクセルを押すのではなく、足で踏むことが必要なのです。車は人間が何も教えられなくてもなんとなく動かすことができるものなのかもしれません。パソコンにはパソコンらしいお約束があって、それは勉強をしたり経験をしたりすることや、どこかでひらめくことで気付いていくといったもの。不幸にもその気付きが訪れない場合は、パソコンは苦手なままなのです。
そこで、この文章を捧げます。この文章は、世の中がパソコンを動かすために必要なのだけど、広められていないエッセンスを盛り込みました。
ぜひ、パソコンが苦手という方は、この文章を読んで、なにか気付くチャンスを得てください。
