Excel YouTube

動画あり!Excel関数のVLOOKUPの使い方|指定の列と同じ行にある値を返す

 

この記事を読むメリット

データ入力が速度が飛躍的にアップし、エクセル(Excel)に自信のある人が必ず使っているのが、VLOOKUP(ブイルックアップ)関数です。この記事では、ビジネスの現場で転記・集計を実現する最も重要なExcel関数、VLOOKUP関数を、自信を持って「使えます!」と言えるよう、VLOOKUP関数の基本的な使い方はもちろん、ビジネスの現場でも対応できる(応用できる)テクニックを紹介します。

  • VLOOKUP関数の使い方がわかる
  • VLOOKUP関数をビジネスで使える水準にまでマスターする中で、エクセル(Excel)のエッセンスをたくさん学ぶことができる

 

Excel関数のVLOOKUP(ブイルックアップ)関数の使い方

VLOOKUP関数というのは、データ(検索値)を上から下へ順番に探していき、該当のデータ(検索値)が見つかったら、その検索値の横(行)にある(調べたい、知りたい)データを抽出する、こんなことをしてくれる関数です。

 

この記事で、VLOOKUP関数をしっかりと学びマスターすることで、同時にエクセル(Excel)のエッセンスをたくさん学ぶことができます。

 

では、さっそく始めましょう。

今、右側の表(購入履歴)のH4セルに、にんじんの価格を引っ張ってきたいとします。

野菜10品目取引価格表H4に価格を入力したい

 

にんじんの価格をどこから引っ張ってきたいかというと、B3からE13の中からです。

この表で言えば、B5セルに[にんじん]があるので、「その隣の隣、3列目にある106円という値(D5セル)を引っ張ってきて、H4セルに表示してね」と指示するわけです。

では、さっそくVLOOKUP関数で106円を引っ張ってみましょう。

 

H4セルに、[=VL]と入力すると、[VLOOKUP]という表示が現れます。VLOOKUPを選択している状態で、[Tab]キーを押します。

すると、4つ引数(ひきすう)、[検索値][範囲][列番号][検索方法]が表示されます。

この4つの引数のうち、大事なのは最初の3つです。この3つをしっかり押さえておけば、VLOOKUP関数を使えるようになります。

VLOOKUP関数の引数

 

まずは、1つ目の引数、[検索値]とは何か。[検索値]とは、エクセル(Excel)に、何を調べるのかを指定する、教えてあげる項目です。今回は、[にんじん]ですよね。[にんじん]は、G4セルに入っているので、G4セルをマウスでクリックして選択すればOKです。

 

続いて、2つ目の引数、[範囲][範囲]と書いてありますが、[検索範囲]のことです。要は、どこを調べるのかを指定する、教えてあげる項目です。今回は、[B3からE13]の表の中からなので、[B3からE13]を選択します。選択したら、[F4]キーを押して絶対参照にしておくのがセオリーです。

[B3からE13]を選択した直後に[F4]キーを押します。すると、[$B$3:$E$13]となります。これでOKです。

 

この[範囲]([検索範囲])にデータを加えたりすることがない場合は、これでよいのですが、将来、データを加える可能性がある場合は、以下で紹介する、[テーブル]を使うのがセオリーとなります。

 

続いて、3つ目の引数、[列番号]。何を、どこから調べるのかというのを指定できたら、次は、対象となるデータ(ここでは、にんじん)が見つかったときに、何列目のデータを引っ張ってくればいいのか、というのを教えてあげます。今回、引っ張りたい、「価格」は、表の3列目にあるので、[3]という値を入力します。

 

そして、最後、4つ目の引数、[検索方法][検索方法]については、とりあえず、完全一致を示す、[FALSE]または、[FALSE]を示す[0]を入力してください。

 

これで、[=VLOOKUP(G4,$B$3:$E$13,3,0)]という式が完成します。

VLOOKUP関数完成

 

[Enter]で式を確定すると、「106円」という値を引っ張ってこれました。

にんじん価格抽出成功

 

商品名が[大根]ならば、37円。

大根価格抽出成功

 

商品名が[ホウレンソウ]ならば、294円と、商品名のところを変えるだけで、簡単に価格を引っ張れるので、費用合計を算出することができます。

ホウレンソウ価格抽出成功

 

また、商品名の欄に上から、[にんじん]、[大根]、[ホウレンソウ]、[レタス]、[トマト]と入力し、

複数の商品価格抽出

 

H4セル~H8セルを選択して、

H4セルからH8セル選択

 

下にコピー&ペーストするショートカットキー[Ctrl]+[D]と押すと、VLOOKUP関数がコピーされ、それぞれの価格を簡単に抽出できます。

H4~H8にVLOOKUP関数

 

繰り返しになりますが、VLOOKUP関数で大事なのは最初の3つの引数です。まずは、この3つの引数に、何を入れればよいのかを把握してください(練習問題が必要な場合は、コメントをお寄せください)。

 

それと、VLOOKUP関数を使用するときの大事なポイントがあります。それは、VLOOKUP関数の[検索値]は、[範囲]([検索範囲])の一番左側にないといけない、ということです。

今回で言えば、[商品名]が一番左にないといけないというわけです。仮に、[範囲]([検索範囲])を、B列ではなくA列にしたら、つまり、[$B$3:$E$13]ではなく、[$A$3:$E$13]にしたらどうなるでしょうか。

検索範囲にA列を含めると

 

試しにやってみると、エラーとなってしまいます。

エラーが返ってきます

 

VLOOKUP関数のポイントは、[検索値]を必ず[範囲]の中の一番左の列にしなければならない、ということです。

 

IFERROR(イフエラー)関数との組み合わせでエラー表示させない

VLOOKUP関数を使っていると、[#N/A]などのエラーが返される場面に遭遇することがあります。

エラーが返されるよくあるパターンとしては、指定した[検索値]が[範囲]([検索範囲])に存在しないときです。

例えば、[にんじん]ではなく、[ニンジン]などとしてしまったときです。[範囲]([検索範囲])である左の表には、[にんじん]はあるので、[にんじん]のときは、きちんと106円という価格を抽出できていますが、カタカナの[ニンジン]は存在しないので、エラーを返してしまいます。

ニンジンはエラー

 

このように、エラーが返されたとき、実務では、IFERROR関数を組み込みます。IFERROR関数を用いて、何も表示しなくするか、何かの文字列を表示するかという、2つのパターンの対応をします。

 

1つ目のパターン、何も表示しなくするには、IFERROR関数を、VLOOKUP関数の前に組み込みます。IFERROR関数は、「もし、エラーだったら、どうするか。」「もし、エラーだったら、どういう値を表示するのか」を指定してあげる関数です。ですから、IFERROR関数の引数は、[値][エラーの場合の値]の2つとなります。

IFERROR関数の引数

 

具体的には、先ほど完成した、[=VLOOKUP(G4,$B$3:$E$13,3,0)]という式のVLOOKUP関数を、IFERROR関数で囲みます。

 

この場合のIFERROR関数の第1引数、[値]の部分は、VLOOKUP関数です。

第2引数の[エラーの場合の値]の部分は、[空欄]を示す[""]を入力して、[=IFERROR(VLOOKUP(G4,$B$3:$E$13,3,0),"")]とすれば完成です。

 

数式や関数の中で文字列を扱うときは、文字列を、[""](ダブルクォーテーション)で囲み、["文字列"]というふうにします。したがって、[空欄]を[""](ダブルクォーテーション)で囲むと、[空欄]になるというわけです。

 

さて、エラーが返されていた先ほどのものも、

ニンジンはエラー

 

IFERROR関数を組み込むことで、

IFERROR関数の組込み

 

H5セルの欄を空欄で表示することができました。

エラーが気になる

 

ただし、今度は、J5セルが[#VALUE!]というエラー表示になり、気になります。このJ5セルには、[=H5*I5]という数式が入っていますので、

J5セルの数式

 

この数式もIFERROR関数で囲み[=IFERROR(H5*I5,"")]としてあげます。

J5セルもIFERROR関数で囲む

 

すると、エラー表示を解消することができます。

J5セルのエラー表示解消

 

では、もう1つのパターン、何らかの文字列を表示するには、どうすればよいのでしょうか。答えは簡単、先ほど、[空欄]を示す[""]としたところを["何らかの文字列"]とすればいいのです。

 

例えば、[何らかの文字列][商品名が違います]とする場合には、["何らかの文字列"]のところを["商品名が違います"]として、[=IFERROR(VLOOKUP(G4,$B$3:$E$13,3,0),"商品名が違います")]とすれば完成です。

IFERROR関数で文字列表示

 

見事、H5セルが[商品名が違います]と表示されました。

IFERROR関数で文字列表示成功

 

VLOOKUP関数以外のときでも、エラーが返されたら、IFERROR関数を組み込むことで解消できることを覚えておいてください。IFERROR関数の第1引数は、数式やVLOOKUP関数などの関数です。そして、第2引数は、エラー値が返されたときにどうするか、を指定してあげます。

 

VLOOKUP関数の[第1引数]徹底攻略!

VLOOKUP関数を使いこなす最大のポイントは、[検索値]である第1引数をどう扱うかどう扱えるかです。そこで、この章では、第1引数の攻略にフォーカスします。

 

VLOOKUP関数の第1引数に入力規則のリストを設定する

入力規則リストを設定することによって、ドロップダウンリストというのができ、そこから選べるようになります。

 

このドロップダウンリストを設定しておくと、第3者にファイルを渡すときに、VLOOKUP関数を壊さずに入力してくれる、第3者のインプットがスムーズになるといったメリットを享受できます。

 

今、H4セルには[=VLOOKUP(G4,$B$3:$E$13,3,0)]という式が入っているとします。

VLOOKUP関数完成

 

第1引数の[検索値]であるG4に、[範囲]である[$B$3:$E$13]にない文字列、例えば、[ニンジン]と入力すると、エラーが表示されることはさきほど確認しました。

 

こういった[表記の揺れ]を未然に防ぐために、G4セル~G9セルに入力できる値を、

G4~G9を選択

 

B4~B13の商品名しか入力できないようにしておきます。

B4~B13を選択

 

まずは、入力規則を設定したい場所、今回は先ほど示した、G4~G9を選択します。

G4~G9を選択

 

そして、[データ]タブの[データツール]グループにある[データの入力規則]ボタンをクリックします。

データの入力規則

 

すると、[データの入力規則]ダイアログボックスが開きます。

[データの入力規則]ダイアログボックス

 

この[データの入力規則]ダイアログボックスの[設定]タブにある[入力値の種類(A):]の[すべての値]となっている部分をクリックし、[リスト]を選択。

[データの入力規則-リスト]の選択

 

次に、[元の値(S):]の欄の空白部分をクリックし、シートの[B4~B13]をマウスでドラッグすると、[=$B$4:$B$13]という絶対参照になった形になります。この状態で[OK]を押します。

[データの入力規則]B4~B13を選択

 

[G4~G9]のところで、ドロップダウンリスト選択のショートカットキー[Alt]+[↓](下矢印)と押すと、商品名がリスト化されて表示されます。[G4~G9]には、ドロップダウンリストで表示されるものしか入力できませんので、ニンジンのような表記の揺れを防ぐことができます。

商品名がリスト化

 

なお、エクセル(Excel)で、[下向き矢印]の表示はすべて、この[Alt]+[↓](下矢印)で表示させることができますですので、覚えておいてください。

 

ワイルドカード[*]を使う

インターネットやホストコンピューターなどから入手したデータには、文字列の後ろにスペースがくっついていることがあります。これが原因で、VLOOKUP関数がうまくいかないエラーが返されるといったことがあります。この章では、こういうときの解消法を紹介します。

 

今、左の価格表のC4セル~C11セルに、右のマスターデータから価格を入力したいとします。

C4~C11に価格を入力したい

 

VLOOKUP関数を使ってうまくいきそう見えますが、実は、マスターデータのコード[A 101]や[A 102]の後ろには[半角のスペース]がくっついて、[A 101 ]や[A 102 ]となっており、そのままではエラーが返ってきてしまいます。

こういった場合、半角スペースを[置換]を使って取り除きたいところですが、そうしてしまうと、[A]の後ろの半角スペースも取り除かれてしまうので、コード自体が変わってしまいます。

文字列の後ろにスペース

 

このようなときに、半角スペースがくっついたデータをそのままの状態で扱うには、[*(ワイルドカード)]を使うのが有効です。

[*(ワイルドカード)]とは、「何と決まっているわけではないけれど、とにかく何らかの文字列」を意味する特殊文字のことです。

使い方は、[検索値]の後ろに[&"*"]をくっつけて、[検索値&"*"]とするだけ。

具体的には、C4セルに、[=VLOOKUP(B4&"*",$E$3:$F$8,2,0)]と入力します。

検索値にワイルドカード

 

あとは、[Ctrl]+[Enter]で完成です。半角スペースがあっても、なくても、きちんと表示してくれます。

C4セル~C11セルに価格を入力できました

 

VLOOKUP関数で表示される[0]を空欄で表示する

今、C4セル~C11セルには、VLOOKUP関数が入っています。具体的には、C4セルでは、[=VLOOKUP(B4,$E$3:$F$8,2,0)]という感じです。

今はうまく表示できている

 

今はうまく表示できていますが、マスターデータのF5セルが空白になった場合を考えます(退職・転職などをイメージしてみてください)。

すると、C4セルやC11セルがゼロという表示になりました。このゼロの表示はカッコ悪いので、空白表示にしたいところです。

F5セルに値なし

 

空白表示にするには、いくつかの方法がありますが、最も簡単なのは、VLOOKUP関数の後ろに[&""]をくっつける方法です。

具体的には、C4セルに入力されている[=VLOOKUP(B4,$E$3:$F$8,2,0)]を、[=VLOOKUP(B4,$E$3:$F$8,2,0)&""]とします。

VLOOKUP関数に&""

 

これで、C4セル、C11セルのゼロが表示されなくなります。

ゼロを非表示にすることが

 

LEFT関数、RIGHT関数、MID関数、FIND関数などの文字列操作関数を組み込む

ビジネスの現場では、VLOOKUP関数の検索値(第1引数)に、LEFT関数RIGHT関数MID関数FIND関数などの文字列操作関数を組み込むことで対応できる場合が多々あります。

そこでこの章では、事例を交えながら解説していきます。

 

LEFT関数を使う事例

VLOOKUP関数の第1引数、[検索値]に、LEFT関数を使う事例を紹介します。

今、E3セル~F6セルに示した表をもとにして、C4セル~C8セルに給与を入力する場合を考えます。

 

部長が500,000円、課長が400,000円、係長が300,000円ということですから、B列にあるコードの最初の2文字を[検索値]にするわけです。

こういうときに、文字列の先頭から指定された文字数の文字を返すLEFT関数を使います。

 

LEFT関数は、LEFT(文字列、文字数)です。

C4~C8を選択

 

ここでは、B列の文字列の先頭から2文字を抽出するので、C4セルに、[=LEFT(B4,2)]と入力します。

C4セルにLEFT関数

 

すると、部長と返されます。これで、VLOOKUP関数の第1引数、[検索値]ができました。あとは、この[検索値]をVLOOKUP関数で囲めば完成です。

LEFT関数成功

 

第1引数は[LEFT(B4,2)]、第2引数[範囲]には、[E3:F6]を絶対参照にした[$E$3:$F$6]、第3引数[列番号]は2列目を示す[2]、第4引数は完全一致を示す[0(もしくは、FALSE)]です。

したがって、[=VLOOKUP(LEFT(B4,2),$E$3:$F$6,2,0)]という式になります。

LEFT関数入りVLOOKUP関数

 

今、C4セルからC8セルが選択されているので、[Ctrl]+[Enter]と押せば、一気に入力できます。

LEFT×VLOOKUPで抽出

 

RIGHT関数を使う

RIGHT関数は、文字列の末尾から指定された文字数の文字を返す関数です。引数は、LEFT関数と同じ、RIGHT(文字列、文字数)です。事例は、LEFT関数と同じようになるので割愛します。

 

MID関数とFIND関数を組み合わせて使う

LEFT関数の事例では、ハイフンの位置が3列目にありました。しかし、ビジネスの現場では、そんなに整ったデータではない場合もしばしば存在します。

そういったときに役に立つ、MID関数とFIND関数を組み合わせて使う方法を紹介します。

 

今、B4セル~B8セルには、次のようなデータが入っています。さきほどと同じように、引っ張りたいのは、[部長][課長][係長]のところです。ですが、ハイフンの位置がバラバラで、LEFT関数やRIGHT関数では抽出できません。

C4セルからC8セル選択

 

こんなときには、まず、ハイフンの位置を特定します。ハイフンの位置の特定には、ある検索文字列(今回はハイフン)がセルの中の何番目にあるのかを検索するFIND関数を使います。

FIND関数は、FIND(検索文字列、対象、[開始位置])です。※[開始位置]は省略可

 

では、B列のハイフンの位置をFIND関数で調べてみましょう。

検索文字列[-(ハイフン)]、対象はB4セルです。ですから、C4セルには[=FIND("-",B4)]と入力します。※[-(ハイフン)]は文字列なので、ダブルクォーテーションで囲みます。

C4セルにFIND関数

 

すると、ハイフンの位置である[3]が返されます。B4セルの[部長]の開始位置は、ハイフンの次の文字、[4]文字目です。

この[4]文字目は、このFIND関数で返される値に[+1]することで求めることができます。

 

したがって、MID関数開始位置は、[FIND関数で返される値+1]で求めることができます。

C4セルに3が返された

 

MID関数は、MID(文字列、開始位置、文字数)ですから、文字列は[B4]、開始位置は[FIND("-",B4)+1]、文字数は2文字を示す[2]となり、[=MID(B4,FIND("-",B4)+1,2)]と入力すれば完成です。

C4にMID関数とFIND関数

 

[Ctrl]+[Enter]と押すと、見事、[部長][課長][係長]が抽出できました。

あとは、この[=MID(B4,FIND("-",B4)+1,2)]第1引数として、VLOOKUP関数をつくればOKです。

MID関数とFIND関数で値抽出

 

このように、MID関数は、FIND関数とワンセットで使うことが多いです。ワンセットとして使うと覚えてもいいと思います。

 

VALUE関数、[×1][+0]で検索値を数値化する

今、C4セル~C11セルにマスターデータから、名前を入力したいとします。

ただし、マスターデータのコードは[数値]、[検索値]となるB4セル~B11セルのコードは[文字列]です。

 

このような場合、[数値]と[文字列]を区別するVLOOKUP関数では、エラーを返してしまうので、[検索値]をマスターデータの形式である[数値]に合わせてあげないといけません。

C4セル~C11セルに名前を入力したい

 

[検索値]をVLOOKUP関数の中で、数値化する方法主に3つあるので、順を追って紹介します。

 

VALUE関数を用いる

[検索値]を数値化する最も一般的な方法は、VALUE関数を用いるものです。[検索値]をVALUE関数で囲むイメージです。

具体的には、B4セルに、[=VLOOKUP(VALUE(B4),$E$3:$F$8,2,0)]と入力します。[検索値]である[B4]をVALUE関数で囲んで、[VALUE(B4)]としています。

検索値にVALUE関数

 

これだけで、うまくいきます。

VALUE関数でうまくいった

 

検索値に1を掛ける

[検索値]を数値化するもう1つの方法は、[検索値]に1を掛けるというものです。VALUE関数を知らなくても数値化できるんですよ。

具体的には、B4セルに、[=VLOOKUP(B4*1,$E$3:$F$8,2,0)]と入力します。[検索値]である[B4]に1を掛けて、[B4*1]としています。

検索値に1を掛けた

この方法でも、うまくいきます。

VALUE関数でうまくいった

 

検索値にゼロを足す

[検索値]を数値化するさらにもう1つの方法は、[検索値]にゼロを足すというものです。上記の[検索値]に1を掛けると発想は同じです。

具体的には、B4セルに、[=VLOOKUP(B4+0,$E$3:$F$8,2,0)]と入力します。[検索値]である[B4]にゼロをを足して、[B4+0]としています。

検索値にゼロを足した

この方法でも、うまくいきます。

VALUE関数でうまくいった

 

COUNTIF関数によるナンバリングで重複データを克服する

VLOOKUP関数は、第1引数の[検索値]に、同じ文字や数字が複数ある場合(重複データがある場合)、一番上の文字や数字しか引っ張ってこれないという弱点があります。

この弱点、重複データがある場合の解決法を紹介します。

今、F4セル~F8セルに、左の会員名簿の表から会員IDを引っ張ってきたいとします。

F4セルからF8セル選択

 

この場合に、単純にVLOOKUP関数を使うと、

F8セルにVLOOKUP関数

 

鈴木さんが3人いるにも関わらす、一番上の会員IDだけを表示してしまいます。三村さんも2人いますが同様です。

単純にVLOOKUPを使ってはうまくいかない

 

この鈴木さんや三村さんのように、重複データがある場合には、VLOOKUP関数第1引数を工夫する必要があります。

工夫の一例として有効なのが、COUNTIF関数によるナンバリングです。

 

COUNTIF関数の範囲指定の妙

説明をわかりやすくするために、B列とC列の間に1列挿入し、説明用としました。

説明用の列の挿入

 

そして、C4セルに、[=COUNTIF($B$4:B4,B4)]というCOUNTIF関数を入力してみます。

COUNTIF関数は、COUNTIF(範囲、検索条件)ですから、この式の意味は、[B4セル~B4セルの間に、B4(ここでは鈴木さん)が何個あるかを調べる]というものになります。

COUNTIF範囲指定の妙

 

ちなみに、C5セルは[=COUNTIF($B$4:B5,B5)]となり、[B4セル~B5セルの間に、B5(ここでは三村さん)が何個あるかを調べる]というものになります。

C5セルはCOUNTIF($B$4:B5,B5)

 

この範囲指定の妙により、1個目には1、2個目には2、3個目には3とナンバリングすることができるようになります。

 

次に、このナンバリングを利用して、B4セルとC4セルを文字列結合の[&(アンパサンド)]を用いて結合します。

具体的には、C4セルを[=B4&COUNTIF($B$4:B4,B4)]とし、

C4にB4&COUNTIF関数

[Ctrl]+[Enter]と押すと、1個目の名前と2個目、3個目の名前を区別した新しい文字列を作ることができます。

B列とC列を文字列結合

 

ここで、右側の表の基準の欄を、鈴木、鈴木、鈴木、三村、三村から、鈴木1、鈴木2、鈴木3、三村1、三村2とできれば、VLOOKUP関数で会員IDを抽出することができます。

基準を変更したい

 

これは、先ほど紹介した、COUNTIF関数によるナンバリングのテクニックを使えば可能です。

ですから、G4セルを、まずは[=COUNTIF($F$4:F4,F4)]とします。

G4にCOUNTIF関数

 

次に、F4セルとG4セルを、先ほどと同じように[&(アンパサンド)]で結合します。

F列とG列の結合

 

この式で、鈴木1、鈴木2、鈴木3、三村1、三村2という感じにできるので、これを、VLOOKUP関数の第1引数[検索値]にした、[=VLOOKUP(F4&COUNTIF($F$4:F4,F4),$C$3:$D$11,2,0)]とすれば、

G4にVLOOKUP関数

見事に、会員IDを入力することができます。

G列に会員ID入力成功

 

少し複雑なようですが、要は、COUNTIF関数によるナンバリングを使っているだけなので、考え方はシンプルです。

 

VLOOKUP関数の[第2引数]徹底攻略!

第2引数にテーブルを設定する

データが増える、後から追加されていくと、設定していたVLOOKUP関数[範囲]を伸ばさないといけなくなる、ということになります。

 

この問題を解決する1つの手段として、VLOOKUP関数第2引数[範囲]テーブル機能を設定する方法があります。テーブル機能は、[範囲]を自動拡張してくれる便利ツールです。

 

今、VLOOKUP関数の第2引数[範囲][$B$3:$E$13]となっているとします。

VLOOKUP関数完成

 

この状態で、14行目以降に「ジャガイモ」などのデータが追加されていっても、追加されたデータは、[範囲]としては認識されません。

 

このようなことが起きないように、データが追加されたら[範囲]が自動的に拡張されるテーブル機能を設定するべきです。

 

やり方は、[$B$3:$E$13]を選択し、テーブルの作成のショートカットキー[Ctrl]+[T]と押します。

テーブル設定範囲選択、テーブル作成

 

すると、[テーブルの作成]ダイアログボックスが表示されるので、OKを押します。

テーブル作成ダイアログボックス

 

[テーブル]が設定されました。

テーブルが設定された

 

リボンにも、[テーブルデザイン]というのが表示されるようになります。

テーブルデザインの追加

 

 

なお、[テーブルデザイン][プロパティ]グループの[テーブル名:]は、今は[テーブル1]となっています。

テーブル名の変更

 

 

この[テーブル1]を、[英語(アルファベット)]の名称に変えます。ここでは、[YasaiーPrice](好きな名称にしてください)としてみます。

テーブル名Yasai-Price

 

 

ここで改めて、さきほどのVLOOKUP関数の第2引数[範囲]を選択します。

第2引数にテーブル

 

そして、[YasaiーPrice]の頭文字である[Y]と入力すると、[YasaiーPrice]というのが表示されるようになっています。

テーブル名を[英語(アルファベット)]にしておくと、このオートコンプリート機能が使えるようになります。

範囲にYasai-Price

 

[YasaiーPrice]を選択している状態で、[Tab]を押すと、VLOOKUP関数の第2引数に、[YasaiーPrice]という名称にしたテーブルが設定されます。

 

ここで、14行目(B14セル)に[ジャガイモ]と入力してみると、テーブルが自動で伸びるのが確認できます。以降、データを追加するたびにテーブルが自動的に伸びていきます。

テーブルが自動伸びる

 

VLOOKUP関数の[第3引数]徹底攻略!

[列番号]修正のひと手間を省くCOLUMN関数とCOLUMNS関数

VLOOKUP関数の第3引数[列番号]は、1,2,3・・・というような固定値にするより、列の移動・削除などに合わせて、数値が変わるようにしておくとメンテする手間などが省けます。

 

列の移動・削除などに合わせて、数値が変わるようにするには、列番号を返してくれる[COLUMN関数]指定したセル範囲などの列数を返してくれる[COLUMNS関数]を使います。

 

COLUMN関数を使う場合

今、C10セルには、[=VLOOKUP($B10,$B$2:$F$7,2,0)]というVLOOKUP関数が入っています。第3引数の[列番号]は、[2]という固定値になっています。

第3引数が固定値[2]

 

この状態で、C10セルの数式を、D10、E10、F10に、コピーすると、すべてのセルが[鈴木]となってしまいます。

第3引数が固定値の弊害

 

このような状態を解消するためには、第3引数に、[COLUMN関数]を導入します。

[COLUMN関数]は、列番号を返す関数で、引数を指定しないで[=COLUMN()]とすると、A列は[1]、B列は[2]、C列は[3]という値を返してきます。

 

今、C10セルの第3引数は[2]、D10セルの第3引数は[3]、E10セルの第3引数は[4]としたいので、[列数-1]を意味する、[COLUMN()-COLUMN($A$2)]とします。

 

[COLUMN()-1]とした方が簡単ですが、数式の中に固定値を含めておくのは、あまりおすすめではありません。

なお、[$A$2]と、2行目を指定しているのは、2行目が表頭だからです。表頭が削除されることはないからです。

 

これをVLOOKUP関数に代入して、C10セルを[=VLOOKUP($B10,$B$2:$F$7,COLUMN()-COLUMN($A$2),0)]とし、

COLUMN関数導入

 

先ほどのようにC10セル、D10セル、E10セルにコピーすると、今度はうまくいきます。

COLUMN関数導入で成功

 

VLOOKUP関数の第3引数のように、引数に列番号を指定するときは、COLUMN関数を導入することを検討してください。

 

COLUMNS関数を使う場合

今度は、[COLUMNS関数]を使ってみます。[COLUMNS関数]は、指定したセル範囲などの列数を返してくれる関数です。

例えば、[=COLUMNS(B9:C9)]2列を指定すれば[2]を、

COLUMNS(B9:C9)

 

[=COLUMNS(B9:D9)]3列を指定すれば[3]を返します。

COLUMNS(B9:D9)

 

今、例示で示したように指定すれば、C10セルの第3引数を[2]に、D10セルの第3引数を[3]にできます。

よく見比べてみると、[B9]の部分は変化していません。一方、[C9]は[D9]と変化しています。

 

これを絶対参照を使って表現して、C10セルの第3引数には、[COLUMNS($B$9:C9)][COLUMNS($B10:C10)]などとすれば、うまくいくことがわかります。

 

この[COLUMNS($B10:C10)]などを、VLOOKUP関数に代入して、[=VLOOKUP($B10,$B$2:$F$7,COLUMNS($B10:C10),0)]とすれば完成です。

COLUMNS導入で成功

 

COLUMNS関数、COLUMN関数、どちらでもうまくいきます。自分が使いやすい方を、VLOOKUP関数の第3引数に使えばいいと思います。

 

VLOOKUP関数の[第4引数]徹底攻略!

以下の表のE4セル~E8セルにVLOOKUP関数を用いて、歩合給を入れていきます。

この場合、VLOOKUP関数の第4引数には、完全一致[FALSE]ではなく、近似一致[TRUE]を使います。

 

この歩合給は、前月の売上(表で言えば、C列)に応じて決定されるものとします。そして、基準額と歩合給は、歩合給決定表によるものとします。

 

例えば、B5セルの三村さんは、前月の売上が15,000円(C5セル)なので、歩合給決定表の10,000以上100,000円未満の5,000円(I6セル)になる、という具合です。

歩合給入力前

 

基本的なVLOOKUP関数の式は、完全一致(FALSEや0)のときと同じです。

C列が[検索値]、歩合給決定表が[範囲]、歩合給決定表の歩合給が[列番号]です。

 

異なるのは、完全一致を表す[FALSE]や[0]ではなく、近似一致を表す[TRUE]や[1]第4引数にすることです。

したがって、[=VLOOKUP(C4,$H$4:$I$8,2,TRUE)]という式になります。

近似一致

 

あとは、E4セルの数式をE8セルまでコピーすると完成です。達増さんは、前月の売上が3,000円(C6セル)なので歩合給は500円(E6セル)、村井さんは、230,000円(C7セル)なので50,000円(E7セル)、佐竹さんは、120,000円(C8セル)なので50,000円(E8セル)と、きちんと入力されています。

歩合給入力後

 

このように、近似一致は、範囲を持つ値を指定するときに使います。具体的には、この例のような歩合給を決めたり、学生の評定を決めたりするときなどです。

 

そして、大事なポイントが2つあります。1つは、参照する値は[正の値]であることです。[負の値]を指定してしまうとエラーが返ってきます。

もう1つは、基準額を昇順(値が増えていくように)で並べておく必要があります。今回の表で言えば、H4セルからH8セルの部分です。

 

おすすめ記事

参考文献

-Excel, YouTube
-

© 2021 まなびっと Powered by AFFINGER5