実務で使えるExcel入門セミナー VLOOKUP関数で「列の数」を簡単に入力する方法です

VLOOKUP関数を使う時に「面倒だなぁ」と感じるのが、列番号の確認。1つずつ数える以外の方法も知っておくと役に立ちますよ。

スポンサーリンク

VLOOKUP関数の仕組み

VLOOKUP関数は「①探したいデータ」を指定すると(「B-02」とします)、

 
「②指定した範囲」の一番左の列に検索して、「①探したいデータ」に一致するデータがないかを探し、

 

スクリーンショット 2015 11 12 11 13 28 
(赤で囲んだ一番左の列に検索して、「B-02」がないか探す)

 

「①探したいデータ」(=B-02)が見つかったら、 
指定した数だけそのセル(「①探したいデータ(=B-02)」が入力されているセル)から右に進んだセルを表示します。

 

スクリーンショット 2015 11 12 11 22 03 
(上の画像の場合「」と指定すると、右に4つ分進んだセルの内容=「150」が表示される)

 

これがVLOOKUP関数の仕組みなのですが、最後の「指定した数だけ右に進んだセル」の指定で、「いくつ分にするか」の指定がちょっと面倒なんです。

 

上の例だと、「商品コード」の列を基準にして「単価」の列を表示させていて、

 

スクリーンショット 2015 11 12 11 47 44

 
これくらいの表ならまだ良いのですが、横に長い表だと正確に数えるのがちょっと面倒なんですよね。

 
そこで、この「右にいくつ分進むか」の指定を簡単にする方法を紹介します。

 

 

 

VLOOKUP関数の「列番号」の指定を簡単にする方法

3つの方法があります。

 

 

アルファベットと列番号の関係を覚えておく

最初は、アルファベットと列番号の関係を覚えておく方法です。

 
VLOOKUP関数の「右にいくつ分進むか」の指定が面倒なのは、Excelシートの列番号がアルファベットで表示されているのが一つの原因です。

 
もし、列番号が「1、2、3…」で表示されていれば、 
「基準になるセルの列番号」と「表示させたいセルの列番号」を確認して引き算すれば、「右にいくつ分進むか」がすぐに分かるからです。

 

スクリーンショット 2015 11 12 12 06 58

 

なので、アルファベット列番号の関係を事前に覚えておくと、「右にいくつ分進むか」の指定がかなりラクになります。

 
「A→B→C…」「1→2→3…」と順番に覚えるのも良いですが、それも面倒なので、基準になりそうなところだけをいくつか覚えておけば良いでしょう。

 
具体的には、「10列目=J」「15列目=O」「20列目=T」の3つくらいで十分でしょう。 
(じゅう(「J」yuu)の10、「T」wentyの20、と覚えると良いですよとアドバイスされたことがあります。15は…(笑))

 

 

COLUMN関数を使う

先ほどの「アルファベットと列番号の関係を覚えておく」で、「列番号が分かれば、引き算で”右にいくつ分進むか”の指定がラクになる」と説明しました。

 
Excelでは「指定したセルの列番号を表示してくれる」関数が用意されています。 
それがCOLUMN関数です。

 
なので、VLOOKUP関数の中でCOLUMN関数を使えば「右にいくつ分進むか」の指定が簡単になります。

 
COLUMN関数の使い方も簡単で、「=COLUMN(」のあとに、列番号を表示させたいセル番号を指定するだけです。

 

スクリーンショット 2015 11 12 12 39 11

 

より具体的にVLOOKUP関数の中でどのように使うかも示しておくと、

 

スクリーンショット 2015 11 12 12 51 47

 
こうなります。 
赤枠で囲んだところで「”右にいくつ分進むか”の指定」をしているのですが、COLUMN関数で「基準のセル」と「表示させたいセル」を指定して、引き算しているだけなのが分かると思います。

 
最後が「+1」になっているのは、VLOOKUP関数では「基準のセル」を「1番目」として数えるからです。

 

 

MATCH関数を使う

MATCH関数を使う方法です。

 
MATCH関数は、「①探したいデータ」を指定して範囲を指定すると、 
指定した範囲の中で「①探したいデータ」が何番目に出てくるかを表示してくれる関数です。

 
例として、「商品データ」の表にある1行目を範囲指定して、「L4セル」の「単価」を「①探したいデータ」に指定することにします。

 

スクリーンショット 2015 11 12 13 05 53

 

「MATCH関数」をこのように入力して、

 

スクリーンショット 2015 11 12 13 10 51 
(「L4」は「単価」と入力されているセル、「$I$4:$N$4」は表の1行目を指定、「0」は”検索の型”。検索の型は通常「0」でOKです。)

 

結果を見てみると、

 

スクリーンショット 2015 11 12 13 13 14

 
「4」と表示されました。 
指定した表の1行目の中で、「単価」のセルは4番目ですから、正しく表示されていることが分かります。

 
より具体的にVLOOKUP関数の中でどのように使うかも示しておくと、

 

スクリーンショット 2015 11 12 13 25 27

 
こうなります。 
表示させたいデータ(単価)のあるセルを「①探したいデータ」にして、表の1行目を「②範囲指定」すれば、”右にいくつ分進むか”を正しく表示させることができます。

 

 

 

まとめ

VLOOKUP関数で入力する”右にいくつ分進むか”の「列番号の指定」は、COLUMN関数やMATCH関数を使うとラクにできます。

 

おまけ

今日は日本代表のワールドカップ予選の日。 
気が抜けないゲームが続きますね。

タイトルとURLをコピーしました