2011年度経済情報処理II

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

第15課の目標

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

準備問題

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. 自動車保険の等級と割引率

自動車保険は、新規加入時は年齢、その後は事故履歴によって等級とよばれるランク付けと、等級に対応した保険料割引が行われるシカケになっている。1年間無事故であれば等級は1等級上がり、1回事故を起こす毎に3等級落ちる。

data07.xlsx自動車保険 シートは新規に自動車保険に加入した人の等級と割引率がどのように変化していくかをシミュレートするためのシートである。すでに加入時の年齢と、その年に事故を起こした回数(年間事故回数)を入力すると、保険等級がどのように変化するかの計算式は入れてある。このシートに、等級に応じた割引率を表示するようにしたい。割引率の列に、VLOOKUP等級と割引率 シートを使って割引率を表示する数式を入力しなさい。

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

3. 表を検索する

例題2. ショッピングクレジットのリボ払いで、利用残高から月々の支払額を計算する

data07.xlsx の シート 標準コース は丸井のショッピングクレジットをリボ払いにした際に、利用残高に応じて月々の支払金額が変わるシステム(標準コース)の利用残高と支払額の対応関係を示している。たとえば、利用残高が13万円なら、100001〜200000の間に入るので月々の支払額は 1万円である。この表を使って、利用残高を入力すると毎月の支払額を自動的に計算してくれる計算式を作りたい。

このような問題を処理する一つの方法は、IF関数を沢山使うことであるが、表が複雑になったときには IF関数では難しくなってくる。ここでは、VLOOKUPの近似値を検索する機能を使ってこの問題を処理してみる。

シート 月支払額 のセル B1にはショッピングクレジットの利用残高が入力されている。この利用残高に応じてシート 標準コース にある利用残高と月支払額の表を検索する。そのためには、シート 月支払額の セル B2に 

=VLOOKUP(B1,標準コース!$A$4:$D$14,4,TRUE)

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

こと。

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

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

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

  1. 1行目は左端が 0 < 20 であるから、次の行を見る
  2. 2行目は左端が 12 < 20 であるから、次の行を見る
  3. 3行目は左端が 35 > 20 である。検索値を越えてしまったので、1行前、すなわち 2行目が「検索値未満の最大値」であると考える
  4. 2行目の指定された列の値を、関数の値として戻す

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

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

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

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

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

0 1
12 2
35 3
63 4

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

下の表を検索範囲とし、第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関数を使うこと)。また、課税所得を横軸、平均所得税率を縦軸に取って図示しなさい。ただし、平均所得税率は、所得税額÷課税所得で計算すること。(グラフの形式は各自適切であると思う形式を選択すること)。

資料出所: 国税庁平成19年分 所得税の改正のあらまし

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

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

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

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

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