Excel。VLOOKUP関数。その8 テーブルを範囲に使うと拡張性が大幅アップ

Excel。VLOOKUP関数。その8　
テーブルを範囲に使うと拡張性が大幅アップ

VLOOKUP関数+名前の定義+テーブル

VLOOKUP関数を説明したものって、色んなテキストをみても、
IF+VLOOKUP関数を使ってエラーを表示しないとかは、多くありますが、
どんなものでも、現在の商品数までで、VLOOKUP関数をつかって、ピックアップするというのは、かわりはないように思います。

ただ、現場でVLOOKUP関数を使うとすると、当然のことながら、
商品数が増えるという事を考えてあげないといけないわけです。

要するに、VLOOKUP関数の範囲で設定したリストに商品アイテムが増えるたびに、いちいち、VLOOKUP関数を修正しているのは、面倒なので、何かアイディアはないかな？ということが、
あまり書かれていないように思います。

そこで、今回は、前回紹介した、名前の定義にさらにテーブルを設定すると、
拡張性がアップして、
商品アイテムが増えた場合でもVLOOKUP関数を修正しなくてもよくなります。

まず、名前の定義もしてない、ただ単に、VLOOKUP関数を設定している場合、
商品アイテムが増えた場合は、どこを修正することになるかというと、
ズバリ、範囲を全部修正しないといけないわけですね。

つづいて、名前を定義してあった場合はというと、
今度は、この名前の定義をされている範囲を直してあげる必要があるわけです。

また、関数を直接的に修正しないで済むのと、VLOOKUP関数の数式がある場所が多い場合は、この名前の定義でも十分に修正個所が少なくなります。

ただ、どっちにしても、商品アイテムが増えれば、その範囲を修正しないといけないわけですね。

ところが、名前の定義とテーブルを使うと、わかりやすく、しかも、なにも修正しないで、
ただ、商品アイテムを追加すればいいだけになるのです。

では、早速やってみましょう。まず確認をしておきましょう。

C4には、=IF(B4="","",VLOOKUP(B4,リスト,2,FALSE))　
という数式が設定されています。
そして、B4には、入力規則のリストが設定されていましたね。

それでは、商品マスターシートに移動しましょう。
A1:C5に表がありますね。
これをテーブルとして設定していきます。
A1を範囲選択して、挿入タブに移動して、テーブルをクリックしましょう。

テーブルに設定するときは、ワザワザ範囲設定をしなくても、Excelがルールに基づき、
範囲選択をしてくれます。

あとは、OKボタンをクリックしましょう。

これで、テーブルになりました。

まぁ、テーブルのデザインとかは、お任せするとして、
では、このテーブルに新しいアイテムを普通に追加してみたいと思います。
今回は、
商品コードがBR-500
商品名がミカン
単価が180円

入力が出来たら、納品書シートに戻ります。
早速、B4をクリックして、入力規則を確認してみましょう。
なんと、BR-500が入っていますよね。

ここには、商品コードという名前が設定されていて、
この商品コードと入力規則のコラボレーションテクニックだったのですが、
テーブルにしたことによって、商品コードの範囲が自動的に拡大された為に、
BR-500が入ってきたわけです。

当然、BR-500を選択してみると、商品名に、ミカンと表示されましたね。

C4の数式はいじってないことが確認できますね。
=IF(B4="","",VLOOKUP(B4,リスト,2,FALSE))　

どうです。ここまで設定すると、VLOOKUP関数の修正をすることなく、
ただアイテムを追加しても、全部何も修正することがない、
拡張性と効率面を考慮した資料が出来るわけです。

実際の仕事はこういう所まで、必要になりますよね。
ですが、どうしてもテキストは断片的なものになってしまいます。

これは、致し方ない所もあります。

ですので、私が担当する講義では、企業研修であれ、職業訓練であれ、
現場レベルのノウハウを取り入れるようにして、講義している訳です。

次回は、VLOOKUP関数の範囲が複数だったらどうしたらいいのか？
ということに関して以前書いてみましたが、再度、書いてみたいと思います。

Excel。VLOOKUP関数。その7 別のブックに範囲がある。ブック間VLOOKUP関数

Excel。VLOOKUP関数。その7　
別のブックに範囲がある。ブック間VLOOKUP関数

ブック間のVLOOKUP関数

VLOOKUP関数に関して色々書いておりますが、検索される範囲が別シートというのは、
テキストにしろ、色んな研修や、集中講座でも紹介しますが、
ファイル間というか、ブック間でVLOOKUP関数をすることは出来ますか？
という質問もよく聞きますので、

今回は、ブック間でのVLOOKUP関数を紹介します。

まず、準備ですが、VLOOKUPというフォルダに、納品書というファイルと、
商品マスターというブックが入っております。

そして、納品書ファイルには、

納品書があります。そして、商品マスターファイルには、

商品リストがあります。このリストが、VLOOKUP関数の範囲にあたります。
今回紹介するのは、この納品書ファイルと商品マスターファイルの間で
VLOOKUP関数が使えるのか？ということですので、早速挑戦してみましょう。

まずは、2つのExcelファイルは開いた状態にしておきます。
なお、エラー表示をさせないためにIF関数+VLOOKUP関数を以前にご紹介しておりますが、
今回は煩雑になるのを避けるため、VLOOKUP関数のみで、紹介していきますので、

IF関数+VLOOKUP関数でも、OKです。

では、納品書ファイルのB4にBR-200という商品コードを事前に入力しておきます。
C4をクリックして、VLOOKUP関数のダイアログボックスを表示しましょう。

検索値には、B4と入力して、範囲のボックスにカーソルを表示してある状態です。
この範囲が、商品マスターファイルにあるので、商品マスターファイルに移動して、
商品リストのデータのA2:C5を範囲選択していきます。

範囲には、
[商品マスター.xlsx]商品マスター!$A$2:$C$5
列番号は、2
検索方法は、完全一致で false
これで、OKボタンをクリックしましょう。
そうすると、納品書ファイルの納品書のC4には、リンゴと表示されております。

商品コードを別の商品コードに変えてみましょう。ちゃんと商品名が変わることが確認できますね。
ということで、ブック間でのVLOOKUP関数は出来るということがわかりましたが、
困ったこともいくつかあります。

それは、前回まで様々紹介してきたテクニックが使えないという事。
つまり、入力ミスや効率化に拡張性に関してが、このブック間では出来ないのです。

まず、商品コードのところに【入力規則のリスト】を設定したくても、出来ません。
では、名前の定義を使えばと思いますが、この名前の定義もブック間では出来ません。

なので、ブック間でのVOOKUP関数は出来ることは出来るけど、
基本的なVLOOKUP関数のみしか出来ない訳ですね。

そして、一度保存して、再度納品書ファイルのみを開いてみましょう。
すると、メッセージが表示されます。

これは、他のブックとリンクしているけど、リンクしますか？ということを聞いてきています。
ですので、使う場合は、最新の状態であるほうがいいので、更新するボタンをクリックする。

を選んだ方がいいでしょう。

さらに、商品マスターファイルを開いていない状態ですと、
C4に設定してあるVLOOKUP関数の数式が開いている状態と異なっていますので、
確認してみます。

商品マスターファイルを閉じてある場合。

=VLOOKUP(B4,'D:\VLOOKUP\[商品マスター.xlsx]商品マスター'!$A$2:$C$5,2,FALSE)

商品マスターファイルを開けてある場合。

=VLOOKUP(B4,商品マスター.xlsx!$A$2:$C$5,2,FALSE)
この差はなんなのかというと、閉じてあるときは、そのファイルのある場所。
つまりパスが表示されるわけです。

なので、数式の内容が違っているように見えるわけですが、一緒ですので、ご安心ください。

まぁ、ブック間でもVLOOKUP関数が出来るということ。がわかりましたが、
ちょっと実務的ではないかも。

Excel。VLOOKUP関数。その6 範囲に名前の定義を使って拡張性を高める

Excel。VLOOKUP関数。その6　
範囲に名前の定義を使って拡張性を高める

VLOOKUP関数+名前の定義

IF関数+VLOOKUP関数のネストによって、エラーも回避することが出来るようになりまいたが、
前回にも書きましたように、現場では、この数式を作って終わりではなくて、使っていくわけですね。そこで、商品アイテムが増えた場合のことを考えておかないと、
実際には便利に使えるとまではいえませんね。

商品アイテム数が増えた場合、VLOOKUP関数のどこを修正しなければならないのかというと、
範囲が該当するわけです。今回使用している納品書のように、
VLOOKUP関数が一か所ならば、修正個所も一つで済むわけですが、
これが、複数個所あった場合、いちいち、それを全部直すというのは、大変ですし、
効率的にも、またミスを発生することからも、出来れば、敬遠したい作業です。

そこで、今回ご紹介するのは、VLOOKUP関数の範囲の箇所に名前の定義を使って、
名前を設定して、それを使って、VLOOKUP関数を作ることによって、
商品アイテム数が増えた時、すなわち拡張性を持たせた、
VLOOKUP関数の数式を作っていきます。

まず、Excelファイルの状況を確認しておきましょう。

納品書シートには、納品書があり、
C列の商品名には、IF関数+VLOOKUP関数の数式が設定されています。
C4には、
=IF(B4="","",VLOOKUP(B4,商品マスター!$A$2:$C$5,2,FALSE))
というIF関数+VLOOKUP関数が設定されています。

また、商品マスターシートには、範囲である。商品リストがあります。

前回は、商品コードに名前の定義を行いましたが、今回は、範囲に名前の定義をしていきます。

まず、商品マスターシートに移動して、
A2:C5までを範囲選択して、名前ボックスをクリックして、リストと入力しましょう。

そうしたら、納品書シートに戻って、C4に設定されている数式を修正していきましょう。

折角、IF関数+VLOOKUP関数が設定されていますので、一から数式を作るのは、
もったいないので、今回は、修正していくことにします。

C4をクリックして、fxの関数の挿入ボタンをクリックすると、IF関数のダイアログボックスが表示されていきます。

数式バーのVLOOKUPという文字の上にマウスを持っていき、クリックすると、ダイアログボックスが、VLOOKUP関数のダイアログボックスに変わります。

高度な関数や、複雑なネスト構造の関数をダイアログボックスで修正する場合には、
数式バーの関数名をクリックしますと、その関数のダイアログボックスが表示されるようになっていますので、数式バーで手入力で修正するのが、苦手な方や、初心者の方は、
こちらのダイアログボックスのやり方を覚えておくといいでしょう。

そうしましたら、範囲をいったん削除して、範囲のボックスを空にしておきます。

数式タブの定義した名前にある、【数式で使用】から、リストを選択しましょう。
範囲に、リストと入力されたことを確認してOKボタンをクリックしましょう。

これによって、今後商品アイテム数が増えた場合は、関数の範囲を修正する必要はなく、
名前の定義で設定した、名前の範囲を修正してあげるだけで、大丈夫になります。

名前の定義の範囲を変更するには、名前の管理をクリックして、ダイアログボックスを使って、
修正していきます。

しかし、いちいち、商品アイテム数が増えたら、この名前の管理を使って、
修正をするのは面倒ですし、やはりミスや効率化としては、どうなのかな？と。

そこで、テーブルを使ったら…ということで、テーブルを使ったテクニックを紹介していきます。
と、その前に、VLOOKUP関数はブック間でも出来るのかを確認してみたいと思います。

Excel。VLOOKUP関数。その5 入力規則は名前の定義でどのバージョンでも使用可能

Excel。VLOOKUP関数。その5　
入力規則は名前の定義でどのバージョンでも使用可能

VLOOKUP関数+入力規則+名前の定義

前に、VLOOKUP関数の検索値のところに、入力規則のリストを使うと、入力ミスを防げたり、
入力の効率を改善出来たりするお話をしました。

ただ、その方法だと、Excel2010以降でないと使えない訳です。そこで名前の定義を使って、Excel2007以前でも出来る方法をご紹介したいと思います。

今回ご紹介する名前の定義は、今後紹介していく拡張性への”布石”ですので、
まずは、名前の定義を使っての【入力規則のリスト】は、知っておきたいスキルの1つです。

このスキルは、職業訓練でも、企業研修でも、必ずと言っていいほど、ご紹介しております。

さて、前回のVLOOKUP関数で紹介したファイルを引き続き使って紹介していきます。

納品書シートに納品書があって、B列は商品コード。
このB4:B19に入力規則のリストが設定しております。

C列は商品名。このC4:C19には、IF+VLOOKUP関数の数式が設定されています。
C4には、
=IF(B4="","",VLOOKUP(B4,商品マスター!$A$2:$C$5,2,FALSE))
という数式が設定されています。
そして、もう一枚のシートがありまして、VLOOKUP関数の範囲で使っているデータが、
商品マスターシートにあります。

さて、納品書マスターのB列に設定されている、【入力規則のリスト】を確認してみましょう。
B4:B19を範囲選択して、データタブのデータの入力規則をクリックしましょう。

元の値には、
=商品マスター!$A$2:$A$5
と入力されています。この元の値に名前の定義を使ってアレンジすることによって、
Excel2007以前でもリストが使えるようになります。

では、この【入力規則のリスト】の設定をクリアしておきますので、
すべてクリアボタンをクリックして、OKボタンをクリックしておきましょう。

それでは、まず名前の定義をやっていきます。
商品マスターシートに移動します。
そして、A2:A5を範囲設定し、名前ボックスをクリックします。

このA2:A5の範囲の名前、ニックネームみたいなものを設定します。
わかりやすいように、今回は、【商品コード】で設定します。

これで、A2:A5に、商品コードという名前が設定されました。
そして、この名前を使って【入力規則のリスト】を設定してきましょう。

納品書シートに戻り、B4:B19を範囲選択して、データタブの入力規則をクリックして、
データの入力規則ダイアログボックスを表示しましょう。

入力値の種類をリストにして、元の値をクリックします。
そうしたら、数式タブし、定義された名前にある、【数式で使用】から商品コード選択します。

選択すると、元の値に、=商品コード　と表示されます。あとはOKボタンをクリックして、完成です。

たった、これだけなのですが、これが拡張性ということを考えた場合、
有効なテクニックになるわけです。

B4をクリックして確認してみましょう。
ちゃんと、リストになっていますよね。

では、先程から出ている、【拡張性】とはどういうことなのでしょうか？
テキストや講義などでは、どうしてもVLOOKUP関数を作れること。
または、エラーを表示させないように、IF関数+VLOOKUP関数のネストの紹介ということが多いのですが、現場レベルにおいて、商品が増えないということは考えにくいと思われます。

つまり、商品が増えた場合、どれだけ修正個所を少なくすることができるのか？
という事が大切になるわけです。

ただ、IF関数+VLOOKUP関数が出来ました。パチパチパチ…という訳にはいかないのです。

修正個所を少なくするという事は非常に大切な事で、
修正する箇所が多いとそれだけ、修正時にミスを起こしてしまう可能性が高くなってしまいます。

今回の【入力規則のリスト】では、前回ご紹介したExcel2010以降の範囲選択による方法でも、
今回ご紹介したExcel2007以前の方法でも、
商品が増えた場合の作業量的としては大差がありませんが、
関数を修正する場合は、その差が出てきますので、

次回は、IF+VLOOKUP関数の、範囲に名前の定義を使って、確認していきたいと思います。

Excel。VLOOKUP関数。その４ #N/Aエラーを回避する為のIF関数

Excel。VLOOKUP関数。その４　
#N/Aエラーを回避する為のIF関数

VLOOKUP関数+IF関数

前回までVLOOKUP関数の作り方をご紹介してきましたが、前回までですと、ちょっと実務で使うのには、ちょっと困ったことが発生してしまうのです。

まずは、VLOOKUP関数をオートフィルで下方向に連続コピーをしてみましょう。そうすると、#N/Aエラーが表示されてしまいました。

#N/Aは、エヌエーと読みます。正式には、No Assign （ノー・アサイン）で、値がないというエラーなのですが、何の値が無いのかというと、商品コード。つまり検索値が空白の為に出たエラーなのです。こんなエラーが表示しているまま、この納品書をお客様に送っていいわけがありませんよね。かといって、エラーを表示させないために、作成する都度に、商品名にある数式を消したり、コピーしたりを繰り返すというのは、あまりにも効率的ではありませんし、商品名を入力した時にミスをしないために、VLOOKUP関数を使ったのにも関わらず、これでは、意味がありませんね。

ですので、今回は、この#N/Aエラーを表示させないようにするためには、どうしたらいいのかをご紹介していきます。

さて、紹介の前に、商品コードを入力しておかないと、この#N/Aエラーが表示されてしまう訳です。つまり、VLOOKUP関数を最初に紹介した時に、商品コードはダミーでいいので入力しておきましょう。と書いたのは、空白のままVLOOKUP関数を作成すると、このエラーが発生してしまうので、慣れていない方ですと、VLOOKUP関数の作成でミスったのか？それともVLOOKUP関数は大丈夫で、検索値が空白でエラーが表示されているのか？わからないからです。

まず、始める前に、このようなエラーを表示させないためにする方法は、IF関数を使うと解決します。
商品コードが空白だったら、空白。そうでなかったら、VLOOKUP関数で検索する
という数式を作ればいいわけです。
IF+VLOOKUP関数を作っていきます。
せっかく作った数式ですが、C4:C19の数式を削除しましょう。
そうしたら、C4をクリックして、IF関数のダイアログボックスを表示しましょう。

論理式は、商品コードは空白ですか？という事ですので、
B4=””
真の場合は、商品コードは空白だったら、空白という事ですので、
“”
そして、偽の場合。ここにVLOOKUP関数を作っていきます。
偽の場合のボックスにカーソルがあるのを確認して、名前ボックスの▼をクリックして、
VLOOKUP関数を選びましょう。無い場合は、その他の関数をクリックして、VLOOKUP関数のダイアログボックスを表示させましょう。

今度は前回同様にVLOOKUP関数を作っていきます。
検索値は、B4。
範囲は、商品マスターシートに移動して範囲選択しますので、
商品マスター!$A$2:$C$5
列番号は、2ですね。商品マスターに移動して確認した場合は、シート名がボックス内に入力されていますので、いったん消して、2だけを入力するんでしたね。
最後は、検索方法は、falseですね。
あとは、OKボタンをクリックして、完成ですね。

数式は、
=IF(B4="","",VLOOKUP(B4,商品マスター!$A$2:$C$5,2,FALSE))
これで、オートフィルでコピーしてもエラーが表示されなくなりましたね。

Excel。VLOOKUP関数。その３ 検索値は入力規則を使うのが便利

Excel。VLOOKUP関数。その３　
検索値は入力規則を使うのが便利

VLOOKUP関数+入力規則

VLOOKUP関数の講習をするなかで、職業訓練であっても、マンツーマン型であっても、集中講義であっても、説明を追加するものがありまして、それが、今回紹介する入力規則を伝票類に追加していくことです。

前回までVLOOKUP関数を使って作った、納品伝票を改めて見てみましょう。

検索値に範囲から検索するコードや番号を入力することによって、VLOOKUP関数が動いて、マッチングしたデータが参照してくるわけですね。
現場では、このような納品伝票などの伝票類でVLOOKUP関数を使うケースが多いのですが、そもそも、なぜVLOOKUP関数を使ったのかというと、手入力を極力避け、入力ミスを減らし、作業効率を高めるためでした。

検索値に入力するデータが、入力文字数が長くしかも、ちょっとわかりにくいケースだと、検索値のデータ入力をミスしたり、作業効率も悪化しかねません。

そこで、簡単にして、作業効率も下げない方法。それが入力規則を使う事なのです。

今回はB4:B19までに入力規則の設定をしていきます。
まずは、B4:B19まで範囲選択をします。
データタブのデータツールにある、データの入力規則をクリックしましょう。

そうすると、データの入力規則ダイアログボックスが表示されてきますね。

まずは、エラーメッセージから今回は修正します。
エラーメッセージは、該当するデータ以外を入力した時に、そのデータを入力させない、あるいは、注意を促したりすることが出来ます。

今回は、スタイルを停止にして、タイトルとエラーメッセージにコメントをいれておきましょう。スタイルには、停止のほかに、注意と情報があります。
では、今度は、設定タブに移動しましょう。

入力値の種類はリストにします。
そして、元の値は、
商品マスターのシートに移動して、
範囲（リスト）の商品コード。A2:A5までを範囲選択します。
元の値には、
=商品マスター!$A$2:$A$5
と入力されたことを確認しましょう。
すると、納品書のシートに戻ってきました。
商品コードB4をみると、▼がありますので、クリックしてみましょう。

商品コードを選択できるようになっていますね。
ここで、B4にZ55と適当な商品コードを入力してみましょう。

確認してくださいという、メッセージボックスが表示されてきました。これは、先程のデータの入力規則ダイアログボックスのところのボックスに自分で入力した文字が表示されてきていますので、確認しておきましょう。これにより、入力ミスを防げますし、作業効率も改善できると思います。
ちなみに、▼は、Alt＋↓キーでリストから選択することもできますので、キーボードでも入力することが出来ますよ。

なお、ちょっと話は戻りますが、データの入力規則の設定。

元の値についてですが、今回のように別のシートにあるデータを使用する場合。
Excel2010以降でないと、範囲選択では設定できません。
Excel2007以前は、名前の定義を使って、範囲に名前を定義をする必要がありますので、注意が必要です。
詳しくは、以前書いた
http://infoyandssblog.blogspot.jp/2013/07/excelexcel2010.html
をご覧ください。

まぁ、実は、名前の定義を最終的にはすることになるのですが…それは、オイオイで。

Excel。VLOOKUP関数。その２ 範囲が別シートの時の作り方

Excel。VLOOKUP関数。その２　範囲が別シートの時の作り方

VLOOKUP関数

前回に続いて、今回はVLOOKUP関数のお話をしていきたいと思います。
VLOOKUP関数は、前回もお話ししましたように、派遣会社さんのスキルチェックでも、
実際の業務でも、結構頻度良く登場するこのVLOOKUP関数。
前回は、基本の基本という事で、その動きや作り方をご説明していきました。

今回ご紹介するのは、範囲（データ）が別のシートにある場合のVLOOKUP関数の作成方法です。

基本的には、前回の内容と対して差はありませんが、VLOOKUPが苦手な人にとっては、
結構引っかかりやすい所でもありますので、しっかり習得していきましょう。

では、下記のようなExcelファイルがあります。

 

今回は納品書というシートには納品書の伝票があって、
商品マスターというシートには市商品リストがあります。

そして、動きとしては、納品書の商品コード欄に商品コードを入力したら、
商品マスターの商品リストにある、商品名を参照してくるというVLOOKUP関数を作っていきたい訳です。

それでは、納品書シートのB4にBR-100と入力して、
納品書シートのC4にVLOOKUP関数を作成していきましょう。

C4をクリックして、関数挿入ダイアログボックスから、
VLOOKUP関数のダイアログボックスを表示しましょう。

検索値ですが、これは、商品コードを入力したら商品名を参照してほしい訳でしたね。
ですので、今回は、商品コードである、B4ですね。

つづいて、範囲ですが、ここが前回と少し異なるところですね、前回は、
同じシート内にデータ（範囲）があったので、良かったのですが、
今回は別のシートにありますから、商品マスターというシートに移動しないといけないわけです。

では、範囲のボックスにカーソルがあるのを確認して、商品マスターシートに移動しましょう。

商品マスター!　

と表示されていますが、これは、商品マスターシートのという意味です。

では、範囲選択をしていきます。範囲は、A2:C5ですね。
見出し行は含めないでおきましょう。
そして、前回も紹介しましたが、オートフィルでVLOOKUP関数の数式を下方向に連続コピーすることを考えると、範囲が動いてしまっては、都合が悪いのですよね。

ですから、ここは、絶対参照を設定します。範囲選択をした直後にF4キーを押します。

結果、範囲は、
商品マスター!$A$2:$C$5
と表示されました。

列番号のボックスをクリックすると、納品書シートに戻ってきます。
続いては列番号ですね。前回詳しくご説明しましたが、この列番号が曲者でして、わかりにくい。

この列番号は、

先ほど設定した範囲の中で、参照したいデータは、左から何列目にありますか？

ということでしたね。
では、ここで、商品マスターシートに移動して、
左から何列目にあったのかを確認することにしましょう。

今回は商品名を参照したいので、この商品名は左から２列目にありますので、
列番号は2ということがわかりました。

で、ここでポイント！

今確認の為に商品マスターに移動しました、列番号のボックスには、
商品マスター!
とすでに入力されていますので、ここで、
商品マスター!2と入力してしますと、エラーになってしまいます。

この列番号は数値以外は入力できませんので、今回の様にシートを移動して確認した場合は、
必ず入力されてあるシート名を削除してから、列番号の数値を入力する必要があります。

ですので、列番号は　2　となります。

あとは、検索方法は、完全一致ですから、falseと入力しましょう。

最後に、OKボタンをクリックして完成ですね。

Excel。VLOOKUP関数を改めて、勉強してみよう。基本編

Excel。VLOOKUP関数を改めて、勉強してみよう。基本編

VLOOKUP関数でシート内にリストがある

初心者さんのExcel講座でも、派遣会社さんのスキルチェックでも、
職業訓練のExcel講座でも、どこでも、おなじみのスキルとして紹介されているVLOOKUP関数。

入力ミスを防ぐためにも、また作業効率をアップするためにも、
知っているといい関数なのですが、ちょっと苦手・ちょっとわかりにくい。
という声もよく聞きますので、
今回は、VLOOKUP関数を使って求めるセルとデータの範囲（リスト）がある、
VLOOKUP関数の入り口ともいえる、基本的な動きを基本編として、ご紹介していきましょう。

まずは、下記のようなシートがあります。

B3に商品コードを入力すると、C3にE2:F8の範囲あるデータから、
商品名を検索して表示するというのが、VLOOKUP関数の動きです。

講座の時も必ずお話しすることなのですが、VLOOKUP関数をと身構えてしまって、
算出したいセルでないところをアクティブにしたまま、
VLOOKUP関数を作ってしまう方がいらっしゃいます。

特に初心者の方に多く見られる傾向ですので、あわてないで、
算出するセルはココだな。と確認してから、作っていきましょう。

まず、B3にS01とサンプルでいいので、入力しておきます。これが非常に大切です。

次に、C3をクリックします。
そして、fxの関数挿入ボタンをクリックして、

関数挿入ダイアログボックスを表示します。

そして、VLOOKUP関数ダイアログボックスを表示しましょう。

最初に検索値から説明していきます。
この検索値…。VLOOKUP関数に慣れていないと、
いったい何のことなのか全く理解できないものの一つですね。

この検索値というのは、この後に設定する範囲の中から、検索するもののことです。

コンビニエンスストアにあるPOSレジをイメージすると、わかりやすくなるかもしれませんね。
レジで、商品のバーコードを、ピッってやると、レジの液晶パネルに、
商品名と値段が表示されますよね。

それとVLOOKUP関数は同じ動きなわけです。

最初に、商品のバーコードをピッとするわけですから、
今回表で、最初にアクションするのは、”商品コードを入力”するわけですね。

つまり、検索値は、B3。ですね。

続いて、範囲を説明します。
範囲は、簡単に言ってしまえば、リスト。
データのことですね。商品リストなどが該当します。

検索値を元にして、検索する先をExcelに教えてあげるわけですね。
ポイントですが、商品名だけを範囲にしてはいけません。

商品コードを検索して、商品名を探すわけです。

ですので、この範囲は、リストのデータを範囲選択していきます。
2行目の見出し行を含めても構いませんが、データのところだけを選択していきましょう。

範囲は、E3:F8を範囲選択します。

今回はこのままでもいいのですが、見積書や請求書など、
このVLOOKUP関数をオートフィルを使って下に数式を連続コピーしていくと、
この範囲も連動して、ずれてしますので、基本的にExcelでの関数の範囲は、
絶対参照を設定しておきましょう。

ですので、範囲は、$E$3:$F$8
絶対参照は、E3:F8を範囲選択した直後にF4キーを押すと、“$“が自動的に設定されます。

３番目は、列番号。これもわかりにくいと言われている箇所ですね。
この列番号は、正確には、範囲で選んだデータ（リスト）の中で、

左から何列目のデータを

参照すればいいの？ということなのです。

つまり、範囲で選択したデータの列は、商品コードと商品名で、
そのうち、商品名を今回は参照したいので、左から２列目なので、

列番号は　2　と入力します。

よくここで、A列だから1。B列だから2。というように思っている人がいますが、
”範囲の中”ですので、気を付けましょう。

最後は、検索方法。
これは、近似値ならTURE。完全一致ならFALSE。のどちらかを入力します。

今回は商品コードと商品名を完全一致したいわけですから、FALSEと入力します。
FALSEではなく、数字の0でもOKです。

なお、よくテキストに省略してもいいようなことを書いてあるものもありますが、修正をした際に、
入力されていないためエラーになることがありますので、
基本的に省略しないで、FALSEと入力することをお勧めします。

あとは、OKボタンをクリックしてみましょう。

すると、C3にリンゴと表示されましたね。商品コードをリスト内の別のコードにしてみましょう。
商品名も自動的に変わったのが確認できましたね。

これが、

VLOOKUP関数の基本の基本の作り方です。

今回は、基本の基本でしたので、
次回は、見積書をサンプルにして、範囲が別のシートにある場合の紹介をしていきます。