Excel2013対応
---topics:vlookup関数---

其ノ8 vlookupの練習 その1

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

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

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

取扱い商品データ toriatukai_data
※1 取扱い商品データ toriatukai_data
最新価格データ data_saisinnkakaku
※2 最新価格データ data_saisinnkakaku

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

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

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

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

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

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

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

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

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

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

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

なぜわざわざコピーしたのか。理由はいくつかあります。
実は、vlookup関数を使うだけなら、2コ別々のExcelでも問題ないんです。 だけどわざわざ1枚のブックにしたのは、元となるデータを残しておくという意味が1つ。 複製しておけば、「最新価格データ」本体は触らないまま残しておけますからね。 それに、後の章で改めて語りますが、vlookupを使うためには加工が必要な場合があるんですよ。 そういう時に、元データをダイレクトでいじっちゃわないで済むように、という意味合いがあります。
もう1コ意味があって、それは、誤操作を避けるためです。 2つExcelを開いている状態だと、触っちゃいけない方に触っちゃうかもしれない。 今回のケースだと、逆の手順、つまり「最新価格データ」の方に関数入れようとしちゃうかもしれないじゃないですか。 まあ、さすがに途中で気付くと思うけど、でも「ヒヤッ」とする瞬間です。 よけいなアプリケーションを開いてると、どうしてもそういう危険な可能性は、ある。
「1件の重大事故の背後には、30の軽微な事故、そして300のヒヤリハットがある」との格言(ハインリッヒの法則)もありますので、 「ヒヤッ」「おおっと」の要因はできるだけ減らしておきたい。 たまにいませんか?フォルダとかExcelとか10コくらい開いてる人。あれ見てて、「怖いな」と僕は思うんです。 ちがうフォルダに入れちゃったり、ちがうExcelいじっちゃったりしないのかな、と。 机の上もPCも、整理整頓が一番です。 私は、個人的なルールとして、「同時に開く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

もう1コのシートの方を選択
※11 もう1コのシートの方を選択

で、A列からD列までをがばーっ!と選択します※12。 列選択でがばーっ!ですよ。

注文番号~価格までを範囲に含める
※12 注文番号~価格までを範囲に含める

範囲を指定したらまた「,(カンマ)」ね。
次に指定するのは「欲しいのは左から何番目?」です。 これは何のことかと言うと、要するに、「※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)森田表計算