2010年度経済情報処理II
| A | B | C | |
|---|---|---|---|
| 1 | 会社名 | 売上高 | 市場占有率 |
| 2 | きつね食品 | 192 | ? |
| 3 | たぬき物産 | 168 | ? |
| 4 | いなり商事 | 224 | ? |
例題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つ同じキー項目の値があると結合結果が変になるので、原則としてキー項目は表の中で行を特定できるような値を付けておくことが望ましい。
例題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で結合 シートを使って以下の作業を行いなさい
練習問題 2. 自動車保険の等級と割引率
自動車保険は、新規加入時は年齢、その後は事故履歴によって等級とよばれるランク付けと、等級に対応した保険料割引が行われるシカケになっている。1年間無事故であれば等級は1等級上がり、1回事故を起こす毎に3等級落ちる。
data07.xlsx の 自動車保険 シートは新規に自動車保険に加入した人の等級と割引率がどのように変化していくかをシミュレートするためのシートである。すでに加入時の年齢と、その年に事故を起こした回数（年間事故回数）を入力すると、保険等級がどのように変化するかの計算式は入れてある。このシートに、等級に応じた割引率を表示するようにしたい。割引率の列に、VLOOKUPと 等級と割引率 シートを使って割引率を表示する数式を入力しなさい。
Tips: VLOOKUP関数は何をやっているのか？
例題2. ショッピングクレジットのリボ払いで、利用残高から月々の支払額を計算する
data07.xlsx の シート 標準コース は丸井のショッピングクレジットをリボ払いにした際に、利用残高に応じて月々の支払金額が変わるシステム（標準コース）の利用残高と支払額の対応関係を示している。たとえば、利用残高が13万円なら、100001〜200000の間に入るので月々の支払額は 1万円である。この表を使って、利用残高を入力すると毎月の支払額を自動的に計算してくれる計算式を作りたい。
このような問題を処理する一つの方法は、IF関数を沢山使うことであるが、表が複雑になったときには IF関数では難しくなってくる。ここでは、VLOOKUPの近似値を検索する機能を使ってこの問題を処理してみる。
シート 月支払額 のセル B1にはショッピングクレジットの利用残高が入力されている。この利用残高に応じてシート 標準コース にある利用残高と月支払額の表を検索する。そのためには、シート 月支払額の セル B2に
=VLOOKUP(B1,標準コース!$A$4:$D$14,4,TRUE)
と入力すればよい。この計算式のなかで新しい点は
こと。
VLOOKUPは第4引数がTRUEのときは「検索値 < 検索範囲の一番左の列の値」となる行のうち、検索範囲の一番左の列の値が最大となる行を検索する。ヘルプには
TRUE を指定するか省略すると、検索値と完全に一致する値、またはその近似値が返されます。検索値が見つからない場合は、検索値未満の最大値が使用されます。
と書いてある。言葉だけでは分かりづらいので例を挙げよう。下のような表を検索範囲として第１引数が 20、第４引数が TRUEである VLOOKUPを実行すると、
という検索を行う。ここで重要なことは、
の2点。VLOOKUPは検索範囲の全体を見て「検索値未満の最大」を求めている訳ではない。単に越えた行の１行上の行を返すだけ。
このため、VLOOKUPの第４引数を 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通りのケースについて
を計算しなさい（VLOOKUP関数を使うこと）。また、課税所得を横軸、平均所得税率を縦軸に取って図示しなさい。ただし、平均所得税率は、所得税額÷課税所得で計算すること。（グラフの形式は各自適切であると思う形式を選択すること）。
資料出所： 国税庁「平成19年分 所得税の改正のあらまし」
練習問題5. リボルビング払いは実際にどれくらい払うのか[難問]
epos.xlsx のシート 定額コース支払 、 標準コース支払 、 長期コース支払 は、シート 利用履歴 に入っているショッピングクレジットの利用をリボルビング払いで支払った場合に、実際にどの程度の支払を行うかを計算するためのワークシートである。定額コース支払 は完成しているが、 標準コース支払 と 長期コース支払 は月支払額の列が入力されていない。
標準コース支払 で使う残高と支払額の関係は 標準コース に、長期コース支払 で使う残高の支払額の関係は 長期コース に入力済みである。これらのデータと VLOOKUP関数、および 定額コース支払 の月支払額の列に入力されている計算式を参考にして、標準コース支払 および 長期コース支払 を完成させなさい。
©2010, Hiroshi Santa OGAWA
このページにアダルトコンテンツ、XXXコンテンツ類は一切含まれていません。暴力反対.