---topics:vlookup関数---

其ノ11 vlookupの練習 その1

「Excelは初心者?それとも中級者?」みたいな聞き方、僕はしないように心掛けています。 初心者/中級者の基準が不明瞭ですし、受け取り手によって意味も異なってくると思うんですよ。 「sum関数使えたら中級者」って人もいれば、「コード書けなきゃ初心者」と考える人もいるかもしれない。
相手のExcelの力量を知りたいとき、僕が聞くのはこれだけです。 「vlookupできる?
vlookupは便利な関数です。 これを覚えると、できることが一気に増えます。 僕自身、覚えたのはvlookup1コなのに、Excelの能力、略してExcel力(りょく)は2倍にも3倍にも増えたような気がしました。
これから何回かにわたって扱っていきます。 しっかり練習して、ぜひ身に着けてください。

では、下↓からダウンロードを。今回ファイルが2つあります。 2つともダウンロードしてください。 1コにまとめてZIPファイルにしようかとも思ったんですが、ZIPって解凍めんどくさいときあるよね。 なので、お手数ですが。

中身はこんな↓感じです※1 ※2

取扱い商品データ kiso11_toriatukai_data.xlsx
※1 取扱い商品データ kiso11_toriatukai_data.xlsx
最新価格データ kiso11_data_saisinnkakaku.xlsx
※2 最新価格データ kiso11_data_saisinnkakaku.xlsx

※1の「取扱い商品データ」は、ある会社で扱う予定の商品一覧、だと思ってください。 アイテム数は199点あります。 こちらのデータは「価格」列が入っていませんね。
それに対して、最新の価格情報が支給されたもの、それが※2の「最新価格データ」です。 価格改定って年に何回かあったりするので、その都度新しいデータが支給されることはめずらしいことではありません。 ただし、※2の「最新価格データ」は723件あります。
で、課題の方なんですが、取扱い商品データ」の「価格」欄を埋めてほしいんですよ、最新価格データ」の方を参照して※3

価格を埋める
※3 価格を埋める

こういう↑感じです。
 ・
 ・
けど、2つのExcelは、件数も異なるし、並び順も違う。 だから、1件1件探さないといけません。
商品の特定には注文番号をキーにして良い、つまり注文番号を軸に商品を探し特定するのですが、例えばこの作業を、ctrl+Fの検索でやろうとすると、こう↓なります。

1コ目の商品の注文番号を…
※4 1コ目の商品の注文番号を…
「最新価格データ」Excelの中で検索して…
※5 「最新価格 」Excelの中で検索して…
該当の価格をコピペする
※6 該当の価格をコピペする

…これをあと198回やるのか…。 うーん、できないことはないんだけど、でもけっこうな手間ですよね。
こういうケースで力を発揮するのがvlookup関数なんです。

vlookup関数の式はこんなかたちです。

=vlookup(検索値 , 検索範囲 , 欲しいのは左から何番目? , FALSE)

が、これだけじゃいまいちイメージ湧かないと思うので、とりあえず実際にやってみましょうか。 習うより慣れろ、です。
あ、式のかたち(↑)、頭の片隅でも良いので置いといてくださいね。 ふせんにメモってPCのモニタにでも貼っておいてくれると、後がラクかな。

ハイ、ではさっそく、と行きたいところなんですが、関数を入れる前にやっておきたいことがありまして。 今回「最新価格データ」と「取扱い商品データ」の2つのExcelがあるんですが、これを1コのブックにしましょう。
「最新価格データ」の表全体をコピって、「取扱い商品データ」のsheet2にもってきてください※7 ※8

「最新価格データ」全体をコピって…
※7 「最新価格」データ全体をコピって…
「取扱いデータ」に新しいシートを作成、そこに貼る
※8 「取扱いデータ」に新しいシートを作成、そこに貼る

シート名も元に合わせておきましょう。

なぜわざわざコピーしたのか。 理由はいくつかあります。
実は、vlookup関数を使うだけなら、2コ別々のExcelでも問題ないんです。
だけどわざわざ1枚のブックにしたのは、元となるデータを残しておくという意味が1つ。 複製しておけば、「最新価格データ」本体は触らないまま残しておけますからね。 それに、後の章で改めて語りますが、vlookupを使うためには加工が必要な場合があるんですよ。 そういう時に、元データをダイレクトでいじっちゃわないで済むように、という意味合いがあります。
もう1コ意味があって、それは、「同時にたくさんのExcelを開く」という悪癖を避けるためです。 まあ、2つ同時ぐらいならさほど問題はないんですが、これが3つも4つも同時に開くようになると混乱の元でしかありません。 たまにいませんか? フォルダとかExcelとか10コくらい開いてる人。 あれはダメな見本です。 同時に作業するものは、できるかぎり少ない方がよい。
私は、個人的なルールとして、「同時に開くExcelは1コまで。2コ開く必要があるときも、ただちに作業を終わらせてすぐ閉じる」「開くフォルダは現在使用してる1コだけ」と決めています。 みなさんも、仕事における断捨離について、考えてみてください。

と、いうことで、コピーが済んだらもう「最新価格」Excelは不要なので、閉じておきましょうね。

ハイ、ということで今、1コのExcelに「取扱いデータ」シートと「最新価格」シートがある状態だと思います。
では、「取扱いデータ」シートの方を選択してください。 空いている「価格」列に関数を入れていきましょう。
まず、「=vlookup(」と入力してください※9。 あたまのカッコ「(」までです。

いよいよvlookup入ります
※9 いよいよvlookup入ります

次に「検索値」を設定します。
vlookup関数っていうのはおおまかに言うと、「指定範囲のなかである値を検索して、目的の値を拾う」という構造をしています。
最終的に欲しいのは「 "最新価格シート"の"価格"」ですね。 だから、(vlookup関数のかたち、思い出してください)関数が2番目に要求している検索範囲には、「「最新価格」のシート」を設定します。
で、vlookupは、その範囲を、1番目の引数で指定した値で検索してくれるんです。 ちょうど、ctrl+Fの検索、あんな感じです。 ただ、ctrl+Fの検索は指定の順番が範囲→検索値なのに対して、vlookupは検索値の指定が先、という違いはありますが。
とりあえず探したいのは、1コ目の注文番号「D59465」です。 なので、「A2」セルを選択※10してください。
マウスで選択しても、左←を3回でも良いですが、僕は左←3回派かな。 マウスだと、ズレてちがう行の注文番号を選択しちゃう危険性が高くなるかな、と思うので。

「検索値」にはA2セルを選択
※10 「検索値」にはA2セルを選択

「A2」セルを選択したら、「,(カンマ)」。 カンマを入れると、2番目の引数「範囲」が太字になります。
次に、 範囲を選択します。 検索したい範囲は「最新価格」シートでしたよね。 なので、「最新価格」シートの方を選択してください※11
で、A列からD列までをがばーっ!と選択します※12。 列選択でがばーっ!ですよ。

もう1コのシートの方を選択
※11 もう1コのシートの方を選択
注文番号~価格までを範囲に含める
※12 注文番号~価格までを範囲に含める

範囲を指定したらまた「,(カンマ)」ね。
次に指定するのは「欲しいのは左から何番目?」です。 これは何のことかと言うと、要するに、「指定した範囲のなかで、最終的に欲しいデータは、左から数えて何番目ですか?」ということなんです※13

欲しいのは左から何番目?
※13 欲しいのは左から何番目?

我々が最終的に欲しいのは「価格」です。 「価格」は左から4番目にありますね。 なので、引数は「4」と手入力してください。 入力したら「,(カンマ)」ね。

で、最後は決まり文句です。 falseと入力してください※14。 もう決まり文句なので、覚えちゃうように。
実はココに、「TRUE」を入れることもできます。 「TRUE」だとまた一味違ったvlookupになるんですが、「TRUE」の方はまず使いません。 なので、FALSE一本で覚えちゃってください。 TRUE/FALSEの機能の違いはここでは説明しませんので、興味のある方は各自で調べてください、お手数ですが。
falseを入れたらとじかっこ「)」。 これでvlookup関数の入力は終わりです※15

決まり文句
※14 決まり文句
決まった…
※15 決まった…

「5600」が表示されました。 コレ、何かというと、「最新価格」の「D59465」の価格です。 つまり、vlookupは、
 ・A2セルの値つまり注文番号「D59465」を、
 ・「最新価格」シートのA:D列(正確には一番左のA列)から探して、
 ・その左から4番目の値をもってきてくれるわけです。
※4~※6の検索で行った仕事と比較してみてください。 同じ仕事ですよね。
合点していただけましたら(納得できない方もとりあえず)、入れた式をコピって「価格」列全部に貼りつけてましょう※16

全部埋めた
※16 全部埋めた

できましたら、貼りつけた式を上から順に眺めてみてください。 1コ目の引数が、A2、A3、A4…と動いてますよね? つまり、注文番号を上から順に次々と検索してくれてるってことなんです。
※4~※6の検索&コピペに比べて、圧倒的に早いですよね。

でも念のため、本当にちゃんと情報を引っ張って来れてるのか、確かめてみましょうか。
試しに、50行目の「XY8344」、調べてみましょう。 「XY8344」は、vlookupの結果では「3600」です※17

ちゃんとデータ持って来れてるかな?
※17 ちゃんとデータ持って来れてるかな?

こいつをctrl+Fで検索すると…※18

OKでした
※18 OKでした

大丈夫でしたね。 もし何だったら、みなさんの方でも調べてみてください。
とりあえず今回はここまでにしましょう。 式のかたち、これから何度も出てきますので、しっかり覚えておいてください。


内容的に、ちょこっとプラス。
別シートを対象としたvlookupにおいてやってしまいがちなことについて、一言申し添えておきたいな、と。
下↓の画面をご覧ください※19

2コ目の引数「検索範囲」まで入れたところ
※19 2コ目の引数「検索範囲」まで入れたところ

vlookupの2コ目の引数「検索範囲」まで入力したところです(上の解説の※12と同じです)。
検索範囲は隣のシート(「最新価格」シート)でした。
で、これから、3つ目の引数「欲しいのは左から何番目?」を入力しようというところ(上で言うところの※13)なのですが、このとき、元のシート「取扱いデータ」に戻っちゃうと…※20

シート名「取扱いデータ!」が引数に入ってしまう
※20 シート名「取扱いデータ!」が引数に入ってしまう

シート名「取扱いデータ!」が引数に入ってしまう。
本当は「4」だけ入力したかったのに…。
なので、こうなっちゃうと、余計な文字情報「取扱いデータ!」をわざわざ削除してから、「4」と入力しなければならない。
こういうこと、ありませんか?

これはですね…、検索範囲で隣のシートを選択したので、「元のシートに戻らなきゃ!」という心理が働いた結果です。 数式中「!」で示されるのは、参照されてるシート名です(この場合だと「取扱いデータ!」)。 Excelは、数式の範囲指定の際にシート間を移動してしまうと参照シート名が自動入力されてしまうので、こうなっちゃうんです。

なので、対策としては、元のシートの方に戻らないで、そのまま数式入力を続ける※21のが良いでしょう。

元のシートに戻らないで、そのまま「4」「false」と続ける
※21 元のシートに戻らないで、そのまま「4」「false」と続ける

…まあ、元のシートに戻ったとしても、シート名「取扱いデータ!」をBackspaceで削除してやれば良いだけの話なんですけどね。
この章をやってみて「なんかうまくいかないなー」という人のために、書き添えておきます。 以上。

Copyright(C)森田表計算