X’masはもうすぐそこに

家計簿にも使える!Excel・スプレッドシートでのVLOOKUP関数使い方

VLOOKUP関数 苦手 家計簿

こんにちは。諭吉です。

私は仕事でパソコンをよく使います。現代社会においてはほとんどの方がそうなのではないでしょうか。

Excelやスプレッドシートを使うのですが、初めは正直言ってちんぷんかんぷんでした。

そんな私がまずつまずいた、便利な関数の1つである「VLOOKUP関数」について紹介したいと思います。

挫折した私が説明するので、「そんなところまで説明しなくてもわ、わかるよぉ!!」というくらいまで落とし込んで説明しますね(笑)

ゆきち

誰もが苦手意識を感じる関数。

今日はまず使えるととっても便利な「VLOOKUP関数」を紹介するで~!

VLOOKUP関数とは

簡単に言うと、VLOOKUPとは、指定した範囲の中から、ある手がかりを基に欲しいデータを引っ張って来てくれる関数です。

どんな時に使うの?使うとどうなるの?

まず、すーごく簡単な例でいきましょう。

例えば、家計簿を作りたいとします。お母さんが毎日必死に手書きしているので、Excelで家計簿シートをつくってあげたい!というのでもよいでしょう。

そんな時、「いちいち商品名を書いて、金額を書いて、何個買ったか書いて、それを電卓で計算して・・・」

と、かなり手間ですよね。

それを、ある番号(商品コードNo.)を表に入力するだけで、一発で商品名と単価を表示させちゃおう!という時に使えちゃいます。

ゆきち

では、具体的にやり方を見てみましょう。

基本的にExcelもスプレッドシートも同じやり方、同じ数式です。

VLOOKUPの具体的な使い方

VLOOKUPを使うには、情報を引っ張るための、なにかキーとなるものをまず決める必要があります。

今回は、商品の名前とその値段を羅列した「商品リスト」を作り、商品1つ1つに番号(商品コードNo.としましょう)を振っていき、その番号をキーにしたいと思います。

そのキーとなる番号だけを家計簿に入力すると、自動で商品名と単価が表示されるようにしていくのです。

  1. まず引っ張りたい情報を入力し、それぞれの商品に番号をわりふった商品リストを作成する
  2. 情報を引っ張る先の家計簿を作成する
  3. キーを入れたら表示してほしいところに、数式を入力する

以上3点です。それでは実際にやっていきます。

①商品リストを作ります。これは今後のデータベースとなるものなので、都度作っていきましょう。必要な情報としては、今回の場合だと、スーパーで売っている品物の名前と、単価です。そして、それぞれの商品に番号を振っていきます。

No.の部分の1番上のセルに「1」と入力し、そのセルの右下にカーソルを合わせると、+(十字ボタン)にカーソルが変わります。それをそのまま一番下のセルまで引っ張ると、値がコピーができます。しかしそのまま引っ張ると左図のようにすべて1になってしまいます。

注意
この時、引っ張りながらCtrlキーを押すと、1,2,3・・・と変わるよ!

そうすると、上の表のように順次番号がふれます。

次は単価を入力していきましょう。

ここは手作業になりますが、一度リストを作ってしまえば、今後は番号だけを家計簿に入力するだけで自動で情報が入力されるようにできます。

②実際に関数を入力していく

家計簿の表をつくります。

そして、その家計簿の「かったもの」の欄に先ほどのリストから商品名を引っ張ってきたいので、このセル(ここではB4・C4(セルを結合しています)のセル)に数式を入力していきます。

右のように、fxと書かれているところに、「=VLOOKUP」と入力します。

途中まで入れると、自動で勝手に判断してVLOOKUPという文字を表示してくれています。

VLOOKUPの具体的な数式は以下のとおりです。

=VLOOKUP(キーとするものが書かれたセルの番号,情報を引っ張ってくる先の範囲, その範囲上で引っ張りたい情報が書かれている列の数,0または1(FALSEもしくはTRUE)

今回の場合に当てはめて考えると、

番号をキーに商品名を入れたい

  • =VLOOKUP(番号を入力するセル番号,リストを書いているセル番号,商品名が書かれている列,1)

商品名を引っ張ってきたいセルはB,C列の4です。そして今回キーにしたい番号を入力しているセルはA4です。リストの範囲はJ3からM12まで、そのリストの中で商品名は左から2列目にあります。

一番後ろの「1」というのは、また後程説明いたします。

では実際に、結合しているB4・C4のセルの数式欄に数式を入力してみましょう。数式は上記の通りあてはめると、

(fx)=VLOOKUP(A4,J3:M12,2,1)

(キー(コードNo.),リストの範囲,商品名はリストの左から2列目,完全一致)

となります。

そして、A列に該当の数字を入れてみましょう。

今回は「鶏肉」という商品名を引っ張ってきたいので、リストには「鶏肉はNo.5」と記載しているので、A4のセルに「5」と入力します。

すると、無事B・C列に「鶏肉」の文字が!

同様に下にも数式を入れていけばOKです。

先ほどのNo.のときと同様、セル右下にカーソルを合わせて引っ張ってコピーしてもいいです。

但し、その時注意する点としては、数式の数値がずれると困るところの前に$マークをいれることです。

(fx)=VLOOKUP($A4,$J$3:$M$12,2,1)

商品リストはずれると困るので、アルファベットと数字の両方の前に$を入れます。また、番号を入力するセルはすべてA列なので、Aの前にも$マークを入力します。

同様に、単価を入力したいところに数式を入れます。

注意
単価は左から4列目(J,K,L,M列のM)なので、

数式は=VLOOKUP($A4,$J$3:$M$12,4,1)となります。

ゆきち

単価は商品リストの3列めなので、「3」と入力すべき用に思えますが、商品名のセルが、K列とL列を結合して3列になっているだけなので、正確には4列めの「4」と入力しています。

完全一致と近似値

数式の最後に「1または0、もしくはFALSEまたはTRUE」と入力しましたよね。

これは、「情報を引っ張ってくるためのキーとなっている「商品コードNo.」が、リストに記載のあるNo.と全く完全一致したときだけ情報を引っ張ってきてほしいか完全一致

それとも「リストに記載のあるNo.に近しい番号をA列に入力した場合でも、それに該当するであろう情報を引っ張ってきてほしいか近似値」の違いです。

前者の場合、1または「FALSE」を、後者の場合は0または「TRUE」で指定します。

 

今回は、A4に「5」と入力して、それと完全に一致した番号の情報(商品リストにおけるNo.「5」の鶏肉)を引っ張ってきてほしいので、1と入力しました。

これで完成!

リストが別シートにある場合

Excelは下のタブをクリックすると、新しいシートが作成できます。

先ほどのように、同じシート内にリストがなくて、別シートにリストがある場合の範囲の選び方は、

(例:商品名を引っ張りたい リストを記載しているシート名が「商品シート」の場合)

=VLOOKUP(A4,商品シート!A2:D11,2,1)

と、リストを記載してあるシートの名称と、リストの範囲が範囲の部分に入ります

手入力してもいいですが、=VLOOKUP(A4, まで入力してから、リストのあるシートへ移動して範囲をドラッグで選択すれば、自然とこの状態で式に入力されます。

おまけ

家計簿の欄に、「単価」と「合計」そして「総計」の欄を作りました。買った個数を手入力すると、「単価×個数」を計算して自動入力される数式を合計のセル(F列)に入力しました。

掛け算の数式

  • (fx)=セル*セル
  • 今回の場合だと=D4*E4 (下まで引っ張ってコピーする場合は=$D4*$E4)

合計を出す場合は足し算の数式をいれればOKです^^

足し算の数式

  • =セル+セル
  • 今回の場合だと総計のセルがF9なのでF9に=F4+F5+F6+F7+F8 を入力。もしくは沢山のセルを足す場合は「SUM関数」を使って (fx)=SUM(F4:F8) としてもOK)

ゆきち

これも覚えておけば、もう電卓いらずですね!

まとめ

  • VLOOKUP関数とは、キーをもとにして、ある範囲(リスト)から情報を引っ張ってくる数式
  • =VLOOKUP(キーとなるセル(検索値),範囲,列番号,検索方法(近似値か完全一致か)

VLOOKUP関数を上手に利用して、楽に日頃の家計簿をつけちゃいましょ♪

きょうも きみのしごとりょくが 1ポイント あがった!