2013年度経済情報処理II

第13課 複数の表をつなぎ合わせて使う

第13課の目標

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

準備問題

1. 複数の表を連結するということ

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

以下のような表1と表2に入っているデータを、表1と表2に共通している項目「会社コード」でつなぎ合わせ、表3のようなデータをつくること。表3の空欄①〜②に入るべきデータを表1と表2から探して手作業でいれてみよう。手作業でできないことをコンピュータにやらせることはできない。(data07.xlsx 手作業 シート)。

表1. 会社名・会社所在地・業主

会社コード 会社名 所在地 業種
1000 たぬき産業 武蔵野 うどん製造・販売
1005 いなり食品 大阪 油揚げ製造
1010 きつね物産 四国 冷凍うどん製造
2010 タヒチ トラベル 仏領ポリネシア 旅行代理店
2022 かわうそ旅行 米国ハワイ州 旅行業
2031 サンタ興業 フィンランド 観光エージェント

表2. 従業員数・売上高・純利益

会社コード 従業員数 売上高 純利益
1000 10 1000 20
1010 20 6000 100
2022 300 20000 200
2031 600 56000 5000

表3. 会社名・従業員数・純利益

会社コード 会社名 従業員数 純利益
1000 たぬき産業 10 20
1010 きつね物産 30 100
2022 かわうそ旅行 20
2031 サンタ興業 500

表1と表2に含まれている会社が同じならばそのままコピーして貼り付ければOKだが、表1には表2に含まれない会社も入っているためそのまま貼り付けると行がずれてしまう。そこで丁寧にチェックしていくことになる。

Step 1. 表3から必要な会社コードをチェック

①に相当する会社コードは、一番左の列を見て「2022」であることがわかる。

Step 2 表2から会社コードに対応するデータを探す

純利益のデータは表2に載っているので、表2から会社コードが2022である行を選び、純利益の列を読めばよい。つまり①には200が入る。

同様に②は5000である。

このように、複数の表をつなぎ合わせるために用いる共通の項目を「キー項目」と呼ぶ。同じ表の中に2つ同じキー項目の値があると結合結果が変になるので、原則としてキー項目は表の中で行を特定できるような値を付けておくことが望ましい。

2. Excelを使って表の連結を行う

例題1. Excel関数を使って表の連結を行う

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

ここでは、業種(中分類)が食品製造であるような企業について、企業情報を整理した表を作成する作業を VLOOKUPで行ってみよう。データは data07.xlsx VLOOKUPで結合 シート(*1)に入っている。

(*1) VLOOKUPで結合 シートは説明のため1枚のシートに3種類の表を入れているが、実際のデータではこのような表の作り方はしない。別の種類の表は別のシートにが大原則。

Step 1. 表1から「業種(中分類)」が「食品製造業」である会社を選んで、その会社コードを表3のA列にコピーする

特定のデータを抽出するには? → Excel編 Q49

Step 2. 会社名の情報を表1から得る

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

とりあえず、以下の計算式を入力する。その際、できる限りコピーでラクをできるように考えること

セル B24 ← =VLOOKUP(A24,$A$4:$D$9,2,FALSE)
セル B25 ← =VLOOKUP(A25,$A$4:$D$9,2,FALSE)
セル B26 ← =VLOOKUP(A26,$A$4:$D$9,2,FALSE)

こんな感じになるはず

Step 3. 従業員数・純利益の情報を表2から得る

ここも、以下の計算式を入力する。できる限りコピーでラクをできるように考えることも同様。

セル C24 ← =VLOOKUP(A24,$A$14:$D$19,4,FALSE)
セル C25 ← =VLOOKUP(A25,$A$14:$D$19,4,FALSE)
セル C26 ← =VLOOKUP(A26,$A$14:$D$19,4,FALSE)

セル D24 ← =VLOOKUP(A24,$A$14:$D$19,4,FALSE)
セル D25 ← =VLOOKUP(A25,$A$14:$D$19,4,FALSE)
セル D26 ← =VLOOKUP(A26,$A$14:$D$19,4,FALSE)

できあがりはこんな具合。確かに純利益のデータが正しく入力できていることを確認しておこう。

練習問題1. data07.xlsx VLOOKUPで結合 シートを使って以下の作業を行いなさい

  1. 表3と似たような構造で、旅行業について整理した表4を作成しなさい

練習問題2

data07.xlsx成績評価 シートには受験者の受験者番号が入っている。また試験1試験2シートにはそれぞれ1回目および2回目の試験の結果が入っている。

  1. 受験者番号をキーにして試験1試験2シートから各受験者の点数を取り出して成績評価シートに入れなさい。
  2. 試験1、試験2の結果を平均して「総合評価点」を計算しなさい。

練習問題3

練習問題2の結果「総合評価点」が60点未満の受験者については、救済レポート シートに入っている救済レポートの点数を加算して最終評点を計算しなさい。

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関数は何をやっているのか?

3. 表を検索する

例題2. 試験の得点から成績評価を決める

data07.xlsxの 標語 シートには、得点範囲と成績評価(標語)の関係を表している。点数から標語に変換する方法としては、IF関数で頑張るという方法もあるが、ここでは表を直接つかう方法を採る。

標語 シートのB列には各学生の得点が入っている。この点数を使って、右の方にある得点範囲と標語の表を検索する。とりあえず、

セル C2 ← =VLOOKUP(B1,$G$2:$I$6,3,TRUE)

と入力すればよい。この計算式のなかで新しい点は

こと。これを実行するとこんな感じ。

VLOOKUPは第4引数がTRUEのときは「検索値 < 検索範囲の一番左の列の値」となる行のうち、検索範囲の一番左の列の値が最大となる行を検索する。ヘルプには

TRUE を指定するか省略すると、検索値と完全に一致する値、またはその近似値が返されます。検索値が見つからない場合は、検索値未満の最大値が使用されます。

と書いてある。言葉だけでは分かりづらいので例を挙げよう。下のような表を検索範囲として第1引数が75、第4引数が TRUEである VLOOKUPを実行すると、

得点範囲 標語
0 59 不可
60 69
70 79
80 89
90 100
  1. 1行目は左端が 0 < 75 であるから、次の行を見る
  2. 2行目は左端が 60 < 75 であるから、次の行を見る
  3. 3行目は左端が 70 < 75 であるから、次の行を見る
  4. 4行目は左端が 80 > 75 である。検索値を越えてしまったので、1行前、すなわち 3行目が「検索値未満の最大値」であると考える
  5. 3行目の指定された列の値を、関数の値として戻す。この場合は標語が欲しいので3列目を指定しておけば「良」がVLOOKUP関数の値になる。

という検索を行う。ここで重要なことは、

  1. VLOOKUPの比較は上の行から1行ずつチェックしていく
  2. 「4行目で越えちゃったから 1行前が検索値未満の最大だよね」となっている

の2点。VLOOKUPは検索範囲の全体を見て「検索値未満の最大」を求めている訳ではない。単に越えた行の1行上の行を返すだけ。

このため、VLOOKUPの第4引数を TRUEにして用いる場合は、検索範囲全体が一番左の列の値の昇順に整列されている必要がある。

データを昇順・降順に並べ替えるには? → Excel編 Q48

0 1
12 2
35 3
63 4

練習問題4 未整列データでの検索

下の表を検索範囲とし、第3引数が2, 第4引数が TRUE の VLOOKUP関数の戻り値は、検索値が 20の時いくつとなるか。まず予想してから実際に VLOOKUP関数を用いて確認しなさい。

12 2
0 1
35 3
63 4

練習問題4 所得税の計算[発展]

下の表は、所得税を計算するベースとなる課税所得と税率および控除額を表している。この表を使って

課税所得が100万円、200万円、300万円、500万円、700万円、900万円、1200万円、1500万円、1800万円、2100万円の10通りのケースについて

  1. 所得税額
  2. 課税所得から所得税を引いた残りの所得
  3. 課税所得に占める所得税の割合(平均所得税率)

を計算しなさい(VLOOKUP関数を使うこと)。また、課税所得を横軸、平均所得税率を縦軸に取って図示しなさい。ただし、平均所得税率は、所得税額÷課税所得で計算すること。(グラフの形式は各自適切であると思う形式を選択すること)。

所得税の速算表 (平成25年4月1日現在)
課税される所得金額 税率 控除額
195万円以下 5% 0円
195万円を超え 330万円以下 10% 97,500円
330万円を超え 695万円以下 20% 427,500円
695万円を超え 900万円以下 23% 636,000円
900万円を超え 1,800万円以下 33% 1,536,000円
1,800万円超 40% 2,796,000円

(注) 例えば「課税される所得金額」が700万円の場合には、求める税額は次のようになります。
 700万円×0.23-63万6千円=97万4千円

資料出所: 国税庁 タックスアンサー「所得税の税率」

練習問題5. リボルビング払いは実際にどれくらい払うのか[難問]

epos.xlsx のシート  定額コース支払 、 標準コース支払 、 長期コース支払 は、シート 利用履歴 に入っているショッピングクレジットの利用をリボルビング払いで支払った場合に、実際にどの程度の支払を行うかを計算するためのワークシートである。定額コース支払 は完成しているが、 標準コース支払 と 長期コース支払 は月支払額の列が入力されていない。

標準コース支払 で使う残高と支払額の関係は 標準コース に、長期コース支払 で使う残高の支払額の関係は 長期コース に入力済みである。これらのデータと VLOOKUP関数、および 定額コース支払 の月支払額の列に入力されている計算式を参考にして、標準コース支払 および 長期コース支払 を完成させなさい。

まとめの問題
data07.xlsx
のデータを使って以下の作業を行いなさい
1. シート 顧客リスト の「2007年上期」の列を、シート 購入履歴 に入っている購入金額をVLOOKUP関数を使って読み出して使って完成しなさい
2. シート 優良顧客優遇 に入っているノベルティを 2007年上期の購入金額を基準に顧客に送りたい。シート 顧客リスト に新しい列を加えて、VLOOKUP関数をつかって顧客ごとに送るべきノベルティを表示しなさい。

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