2017年度経済情報処理II

第10課　範囲名とVLOOKUP関数

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

• 準備問題で扱ったデータは、店舗名の代わりに「店舗コード」という数字が入力されていた。
• POSシステムの中では全て店舗コードで扱われているらしい。
• でも、数字のままだとどこの店だかよくわからないのでやっぱり名前にしておきたい
• 本部に聞いたら、「店舗マスタシートに入ってるので勝手にやれ」とのこと。

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

• 店舗マスタからとりあえず手作業でデータを持ってきてみる
• 手でできないことをExcelにやらせると、何が起こってるか分からない

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

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

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

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

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

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

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

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

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

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

Step 4. あとは繰り返し

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

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

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

• Excel画面の左上一等地にある謎の領域が気になったことはなかっただろうか？ 　
• 名称は「名前ボックス」なのだけれど、普段はセル番地みたいなものが表示されているだけ。そんなものは、アクティブセルの位置を見れば分かるので、画面の一等地にボックスを配置する理由などどこにもない。
• 実は、Excelはセルや範囲に名前を付けて計算式の中から使うことができる。
• 後で計算式を見直した時に、=SUM(B2:F123)　と書いてあるより =SUM(月間売上金額）と書いてある方が意味が分かりやすい。このやり方を覚えよう。
• 別シートに入っているデータを使いたい時には特に便利。

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

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

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

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

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

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

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

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

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

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

=AVERAGE(身長)

計算結果が表示される。

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

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

• task04.xlsxブックの、名前練習帳シートに含まれている体重データを表す範囲に、体重という名前をつけて、平均を計算する計算式をセルC28に入力しなさい。
• 検算用にオートSUMツールを使って、セルC27に平均を計算しなさい。
• セル C29に、C28とC29の差を計算する計算式を入力し、答が0になる（C27とC28の内容が一致している）ことを確認しなさい。

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

• task04.xlsxブックの、店舗マスタシートの範囲 A1:B4 に、店舗名という名前をつける。
• task04.xlsxブックの、商品マスタシートの範囲 A1:G35 に、商品情報という名前をつける。
• task04.xlsxブックの、分類マスタシートの範囲 A1:B8 に、分類名という名前をつける。

例題2. 名前の編集

• ありがちなことだけど、名前をつけるときに入力ミスしてしまうのは人の常
• 名前ボックスで編集中ならいいのだけれど、一旦名前をつけてしまうと名前ボックスでは編集できない
• ……というわけで、編集方法もやっておこう。

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

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

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

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

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

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

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

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

→

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

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

• 名前ボックスの中で名前を編集するとどうなるの？　→　名前の編集にはなりません。編集前の名前と、編集後の名前の両方ができるだけ。
• 要らなくなった名前を削除するにはどうするの？　→　「名前の管理」ダイアログで、編集のかわりにをクリックすれば削除できます。ただし、名前を削除した場合、削除された名前を参照している計算式は片っ端から #NAME エラーになるので、削除は慎重に。

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

• 今回本部から送られてきたもう一つのデータ、商品別販売数シートには商品名が入っていなかった。
• しかも、商品別販売数シートの中のデータの並びは、販売量が多いものほど上になるように整列されていて、商品コードの順になっていない。
• さらに、表に載っていない商品もあるので、単純に「整列してコピペ」みたいな方法では解決できそうもない。
• ……手作業ではやりたくありません。

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

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

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

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

Step 1. VLOOKUP関数を入力する

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

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

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

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

こんな感じになるはず

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

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

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

• task04.xlsxの商品別販売数シートに、新たに「カロリー」という列を追加し、VLOOKUP関数を使って商品マスタシートに入っているカロリーの情報を求める計算式を入力しなさい（ヒント：　カロリーの情報は、範囲商品情報の左から3列目に入力されている）
• task04.xlsxの商品別販売数シートに、新たに「価格」という列を追加し、VLOOKUP関数を使って商品マスタシートに入っている価格の情報を求める計算式を入力しなさい（ヒント：　価格の情報は、範囲商品情報の左から4列目に入力されている）

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

• task04.xlsxの商品別販売数シートに、新たに「分類コード」という列を追加し、VLOOKUP関数を使って商品マスタシートに入っている商品の分類コードの情報を求める計算式を入力しなさい（ヒント：　分類コードの情報は、範囲商品情報の左から6列目に入力されている）。
• task04.xlsxの商品別販売数シートに、新たに「分類名」という列を追加し、VLOOKUP関数を使って分類マスタシートに入っている分類名の情報を求める計算式を入力しなさい（ヒント：　分類名の情報は、範囲分類名の左から2列目に入力されている）

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）ホームページに戻る

©2017, Hiroshi Santa OGAWA
このページにアダルトコンテンツ、XXXコンテンツ類は一切含まれていません。暴力反対.