2017年度経済情報処理II

第10課 範囲名とVLOOKUP関数

第10課の目標

↑経済情報処理(2017)ホームページに戻る

準備問題

1. 店舗マスタシートを参照しました

例題0. 共通の項目で表をつなぎ合わせるとは?

月間販売数シートに入っているデータは以下のような感じ

販売個数
店舗コード 7月 8月 9月
3 2668 2640 2595
2 9710 9918 9269
1 7428 1288 1246

この中の「店舗コード」というデータが、店舗名と対応しているらしい。本部が言ってた店舗マスタシートに入っているデータは以下のような感じ

店舗コード 店舗名
1 白楽
2 みなとみらい
3 反町

この2つの表を使ってやりたいことは、以下のように「月間販売数シートに入っているデータに店舗名を埋め込んだ表」を作ること。

販売個数
店舗コード 店舗名 7月 8月 9月
3 2668 2640 2595
2 9710 9918 9269
1 7428 1288 1246

とりあえず、手作業でやってみよう。

Step 1. 月間販売数の表から店舗コードを探す

1行目を見ると、店舗コードは3。

販売個数
店舗コード 店舗名 7月 8月 9月
3 2668 2640 2595
2 9710 9918 9269
1 7428 1288 1246

Step 2. 店舗コードに対応する店舗名を探す

この店舗コード3に対応する店舗名を店舗マスタで探すと、「反町」だと分かる。

店舗コード 店舗名
1 白楽
2 みなとみらい
3 反町

Step 3. 月間販売数の表の店舗名列に、「反町」と書き込む

  販売個数
店舗コード 店舗名  7月 8月 9月
3 反町  2668 2640 2595
2   9710 9918 9269
1   7428 1288 1246

Step 4. あとは繰り返し

残りの2店舗についても同じような作業をやれば、できあがり

販売個数
店舗コード 店舗名 7月 8月 9月
3 反町 2668 2640 2595
2 みなとみらい 9710 9918 9269
1 白楽 7428 1288 1246

このように、複数の表をつなぎ合わせるために用いる共通の項目を「キー項目」と呼ぶ。この場合は、「店舗コード」がキー項目。

2. 名前をつけてデータを扱いやすくしよう

例題1. セル範囲に名前をつけて、計算式から使ってみる

task04.xlsxブックの、名前練習帳シートに含まれている身長データを表す範囲に、身長という名前をつけて、計算式のなかから使ってみたい。

Step 1. データ範囲を選択する

アクティブシートを名前練習帳シートに切り替えて、身長データの入っている範囲(B2:B26)を選択する

Step 2. 「名前ボックス」をつかって、選択した範囲に名前をつける

         ↓
「名前ボックス」にマウスポインタを合わせてクリックすると、編集モードになる
         ↓

         ↓
範囲につけたい名前、この場合は身長を、直接入力する。
         ↓

         ↓
Enterキーを押して確定すると、こういう表示に変わる。これで、名前練習帳シートの範囲(B2:B26)に身長という名前がついた。
         ↓

Step 3. 範囲につけた名前を使って計算をしてみる

セル B28 に以下の計算式を入れてみる。

=AVERAGE(身長)

計算結果が表示される。

Step 4. ついでに、検算用にB27にオートSUMツールを使って平均を入れておく。

練習問題1 範囲に名前をつけて計算に使ってみる

練習問題2 範囲に名前をつけてみる(ここで付けた名前を例題3で使うのでスキップしないこと)

例題2. 名前の編集

Step 1. 範囲に名前をつけてみる

task04.xls ブックの名前練習帳シートのなかで、学籍番号を表す範囲に学生証番号という名前をつけてみる。

Step 2. 気が変わったので、名前を変えてみる

やっぱり、見出しに「学籍番号」と書いてある列にわざわざ違う名前をつけるのは筋が悪い。そこで、名前を素直に学籍番号に変えてみる。

(1)数式-定義された名前からをクリックすると、以下のようなダイアログが開く

※表示されている名前は、これまでにどの程度の練習問題を解いたかで異なる可能性があります。

(2)名前を編集したい行(この場合は学生証番号)を選択して、をクリック

「名前の編集」というダイアログが開く。ここで名前を学籍番号に書き替えてOKをクリック

(3)名前が書き替えられている

Tips 名前の編集とか削除とか

3. 商品マスタを参照して……手作業でやりたくありません

例題3. VLOOKUP関数を使って表の連結を行う

上で行ったような作業は確かに機械的に手作業でもできるが、表の行数が増えるとどんどん辛い作業になる。更に、転記ミスの可能性も高くなる。
機械的にできることはコンピュータにやらせるのが吉。そこで、VLOOKUPという関数を使ってこの作業を自動化してみよう。

検索値をもとにデータから値を抽出するには? → Excel編 Q28

今回のケースでは、検索するキーは商品別販売数シートに入っており、検索される表は商品マスタシートに入っている。

Step 1. VLOOKUP関数を入力する

VLOOKUP関数は以下のような形式になっている

VLOOKUP(検索キー, 検索範囲, 列番号, 検索方法)

こんなものをいきなり見せられてもわけがわからないと思うので、とりあえず、以下の計算式を入力する。ここで、練習問題2で設定した名前が役に立つ。

セル B2 ← =VLOOKUP(A2,商品情報,2,FALSE)

こんな感じになるはず

Step 2. 計算式を B3〜B21にコピーする

できあがりはこんな感じ。別シートにある表と、今見ているシートを何回も行ったり来たりしなくても、あっと言うまにできあがり。
これ知らないで手作業で連結してたら完全な給料泥棒、ってくらい簡単にできてしまう。

練習問題3 いろいろなデータをVLOOKUPで調べてみる

練習問題4. もっといろいろなデータを VLOOKUPで調べてみる(☆)

Tips: VLOOKUP関数の結果が #N/A となってしまう場合の処理

VLOOKUP関数の第4引数が FALSE になっている場合、第2引数で指定される検索範囲に第1引数で与えられるキーが存在しなければ VLOOKUP関数は #N/A というエラーを返す。#N/A は数値ではないため、このエラーが出てしまうとその後の計算は全部 #N/Aになってしまう。このエラーを計算式で処理するためには ISNA関数を使う。

ISNA関数は、引数を1つとり、引数が #N/A の場合は TRUE、そうでなければ FALSE という論理値を返す。例えば、練習問題3のように「データがあればそのデータ、なければ0を返す」という処理をしたい場合は、IF関数の第1引数に ISNA関数を入れて、VLOOKUP関数の返値によって処理を変えればよい。

Tips: VLOOKUP関数は何をやっているのか?

まとめの問題
task04.xlsxブックの、まとめの問題シートには、来店者の属性×商品グループでの販売個数を集計した表が入っている。この表を使って以下の操作をしなさい。
1. シート 属性マスタ に入っている属性コードと性別、年齢階級の情報を使って、まとめの問題シートのB4〜B13に年齢階級を、C4〜C13に性別を表示する計算式を入力しなさい。
2. シート分類マスタに入っている分類コードと分類名の情報を使って、まとめの問題シートのD3〜J3に、D2〜J2に入っている分類コードに対応する分類名を表示する計算式を入力しなさい。

できあがりイメージはこんな感じ(※問題とは違うデータを使っているので、あくまでイメージです)

↑経済情報処理(2017)ホームページに戻る