2013年度経済情報処理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
data07.xlsxの成績評価 シートには受験者の受験者番号が入っている。また試験1、試験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関数は何をやっているのか？
例題2. 試験の得点から成績評価を決める
data07.xlsxの 標語 シートには、得点範囲と成績評価（標語）の関係を表している。点数から標語に変換する方法としては、IF関数で頑張るという方法もあるが、ここでは表を直接つかう方法を採る。
標語 シートのB列には各学生の得点が入っている。この点数を使って、右の方にある得点範囲と標語の表を検索する。とりあえず、
セル C2 ← =VLOOKUP(B1,$G$2:$I$6,3,TRUE)
と入力すればよい。この計算式のなかで新しい点は
こと。これを実行するとこんな感じ。
VLOOKUPは第4引数がTRUEのときは「検索値 < 検索範囲の一番左の列の値」となる行のうち、検索範囲の一番左の列の値が最大となる行を検索する。ヘルプには
TRUE を指定するか省略すると、検索値と完全に一致する値、またはその近似値が返されます。検索値が見つからない場合は、検索値未満の最大値が使用されます。
と書いてある。言葉だけでは分かりづらいので例を挙げよう。下のような表を検索範囲として第１引数が75、第４引数が TRUEである VLOOKUPを実行すると、
| 得点範囲 | 標語 | |
| 0 | 59 | 不可 |
| 60 | 69 | 可 |
| 70 | 79 | 良 |
| 80 | 89 | 優 |
| 90 | 100 | 秀 |
という検索を行う。ここで重要なことは、
の2点。VLOOKUPは検索範囲の全体を見て「検索値未満の最大」を求めている訳ではない。単に越えた行の１行上の行を返すだけ。
このため、VLOOKUPの第４引数を 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通りのケースについて
を計算しなさい（VLOOKUP関数を使うこと）。また、課税所得を横軸、平均所得税率を縦軸に取って図示しなさい。ただし、平均所得税率は、所得税額÷課税所得で計算すること。（グラフの形式は各自適切であると思う形式を選択すること）。
| 課税される所得金額 | 税率 | 控除額 |
|---|---|---|
| 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関数、および 定額コース支払 の月支払額の列に入力されている計算式を参考にして、標準コース支払 および 長期コース支払 を完成させなさい。
©2013, Hiroshi Santa OGAWA
このページにアダルトコンテンツ、XXXコンテンツ類は一切含まれていません。暴力反対.