2006年度経済情報処理
例題0. 共通の項目で表をつなぎ合わせるとは？
以下のような表1と表2に入っているデータを、表1と表2に共通している項目「会社コード」でつなぎ合わせ、表3のようなデータをつくること。表3の空欄①〜②に入るべきデータを表1と表2から探して手作業でいれてみよう。手作業でできないことをコンピュータにやらせることはできない。
表1. 会社名・会社所在地・従業員数
| 会社コード | 会社名 | 所在地 | 従業員数 |
|---|---|---|---|
| 1000 | たぬき産業 | 武蔵野 | 10 |
| 1010 | きつね物産 | 四国 | 30 |
| 2022 | かわうそ旅行 | 米国ハワイ州 | 20 |
| 2031 | サンタ興業 | フィンランド | 500 |
表2. 業種・売上高・純利益
| 会社コード | 業種 | 売上高 | 純利益 |
|---|---|---|---|
| 1000 | うどん製造・販売 | 1000 | 20 |
| 1005 | 油揚げ製造 | 5444 | 50 |
| 1010 | 冷凍うどん製造 | 6000 | 100 |
| 2010 | 旅行代理店 | 300 | 6 |
| 2022 | 旅行業 | 20000 | 200 |
| 2031 | 観光エージェント | 56000 | 5000 |
表3. 会社名・従業員数・純利益
| 会社コード | 会社名 | 従業員数 | 純利益 |
|---|---|---|---|
| 1000 | たぬき産業 | 10 | 20 |
| 1010 | きつね物産 | 30 | 100 |
| 2022 | かわうそ旅行 | 20 | ① |
| 2031 | サンタ興業 | 500 | ② |
①に相当する会社コードは、一番左の列を見て「2022」であることがわかる。
純利益のデータは表2に載っているので、表2から会社コードが2022である行を選び、純利益の列を読めばよい。つまり①には200が入る。
同様に②は5000である。
このように、複数の表をつなぎ合わせるために用いる共通の項目を「キー項目」と呼ぶ。同じ表の中に2つ同じキー項目の値があると結合結果が変になるので、原則としてキー項目は表の中で行を特定できるような値を付けておくことが望ましい。
例題1. Excel関数を使って表の連結を行う
上で行った作業は確かに手作業でもできるが、表の行数が1000行もあるとかなり辛い作業になる。更に、転記ミスの可能性も高くなる。機械的にできることはコンピュータにやらせるのが吉。そこで、VLOOKUPという関数を使ってこの作業を自動化してみよう。
ここでは、上で手作業でやった例を VLOOKUPで行ってみよう。データは join_tables.xls に入っている。
Step 1. 表1から「会社コード」「会社名」「従業員数」の列を表3にコピーする。
この部分は表1に含まれているデータそのままなので、単に
セル A22 ← =A4
セル B22 ← =B4
セル C22 ← =D4
と入力して、あとは必要なだけコピーすればよい。
Step 2. 以下の計算式を入力する。いちいち入力せず、コピーを使ってラクをすることを考えること
セル D22 ← =VLOOKUP($A22,$A$12:$D$17,4,FALSE)
セル D23 ← =VLOOKUP($A23,$A$12:$D$17,4,FALSE)
セル D24 ← =VLOOKUP($A24,$A$12:$D$17,4,FALSE)
セル D25 ← =VLOOKUP($A25,$A$12:$D$17,4,FALSE)
できあがりはこんな具合。確かに純利益のデータが正しく入力できていることを確認しておこう。
例題では呪文のような計算式を説明せずに入力したが、ヘルプで VLOOKUPを調べると次のように書いてある。
書式
VLOOKUP(検索値,範囲,列番号,検索の型)
VLOOKUPは 4つの引数を取る。最初の引数は「検索値」と書いてあるが、要するに調べたい表のキー項目として使う値を入れろということである。上の例題では、ここには会社コードが入っているセルに対する参照が入力されていたので、1000であればたぬき産業の行をまず検索してほしいということを意味する。
2番目の引数は「範囲」である。これは、調べたい表全体を範囲として指定する。この際、キー項目が選択した範囲の一番左側の列になるよう指定することに注意。VLOOKUPは、「検索値」で指定された値と、「範囲」の一番左の列の値を比較して、一致した行を探す。
3番目の引数は、「列番号」である。これは、1番目と2番目の引数で、範囲の中の行が1つ決まったら、その行の中の何列目のデータをVLOOKUPの戻り値として使うかを指定する。キー項目の列が１列目で、順次右に行くに従い列番号は増える。
4番目の引数は「検索の型」であるが、キー項目を使って表をつなぎ合わせる場合は FALSE を指定すると覚えておけばよい（それ以外のケースは9.2であつかう）
例題1で使ったケースのセル D22には
=VLOOKUP($A22,$A$12:$D$17,4,FALSE)
と書いてあった。これを図示すると以下のようになる。
1つずつ説明すると
上の例では、検索範囲（,$A$12:$D$17,）の一番左の列で値が 1000なのは 「1000 うどん製造販売 1000 20」の行である。この行の中で4列目の値は「20」なので、関数の戻り値としては 20を返すことになる。
練習問題 1. 自動車保険の等級と割引率
自動車保険は、新規加入時は年齢、その後は事故履歴によって等級とよばれるランク付けと、等級に対応した保険料割引が行われるシカケになっている。1年間無事故であれば等級は1等級上がり、1回事故を起こす毎に3等級落ちる。
hoken.xls は新規に自動車保険に加入した人の等級と割引率がどのように変化していくかをシミュレートするためのブックである。加入時の年齢と、その年に事故を起こした回数（年間事故回数）を入力すると、保険等級が将来どのように変化し、等級に応じた割引率を表示するようにしたい。割引率の列に、VLOOKUPと 等級と割引率 シートを使って割引率を表示する数式を入力しなさい。
練習問題2. 表を整形する
平成12年国勢調査 第１次基本集計 神奈川県 「年齢（各歳）・男女別人口、年齢別割合、平均年齢および年齢中位数
から、下のような表（縦に地域、横に5歳階級の総人口）を作成しなさい。ただし、手作業でデータをコピーするのではなく、VLOOKUP関数を使って作成すること。
| 0〜4歳 | 5〜9歳 | 10〜14歳 | 15〜19歳 | 20〜24歳 | …… | 70〜74歳 | |
| 14 神 奈 川 県 | 404339 | 385843 | 394049 | 478756 | 629994 | ||
| 市 部 | 388554 | 368142 | 374001 | 455600 | 604818 | ||
| 郡 部 | 15785 | 17701 | 20048 | 23156 | 25176 | ||
| 100 横 浜 市 | 163388 | 153671 | 157597 | 187668 | 242649 | ||
| 101 鶴 見 区 | 12044 | 10781 | 11075 | 12878 | 17864 | ||
| 102 神 奈 川 区 | 8637 | 7925 | 8343 | 11311 | 18260 | ||
| 103 西 区 | 2849 | 2682 | 2805 | 3526 | 5577 | ||
| 104 中 区 | 4579 | 4740 | 4857 | 5209 | 7009 | ||
| 105 南 区 | 7812 | 7377 | 7820 | 9207 | 12559 | ||
| 106 保 土 ケ 谷 区 | 9069 | 8933 | 9141 | 11163 | 15559 | ||
| 107 磯 子 区 | 7337 | 6855 | 7336 | 8901 | 10898 | ||
| 108 金 沢 区 | 8646 | 9291 | 10400 | 13472 | 15496 | ||
| …… | |||||||
| 424 藤 野 町 |
例題2. ショッピングクレジットのリボ払いで、利用残高から月々の支払額を計算する
epos.xls の シート 標準コース は丸井のショッピングクレジットをリボ払いにした際に、利用残高に応じて月々の支払金額が変わるシステム（標準コース）の利用残高と支払額の対応関係を示している。たとえば、利用残高が13万円なら、100001〜200000の間に入るので月々の支払額は 1万円である。この表を使って、利用残高を入力すると毎月の支払額を自動的に計算してくれる計算式を作りたい。
このような問題を処理する一つの方法は、IF関数を沢山使うことであるが、表が複雑になったときには IF関数では難しくなってくる。ここでは、VLOOKUPの近似値を検索する機能を使ってこの問題を処理してみる。
シート 月支払額 のセル B1にはショッピングクレジットの利用残高が入力されている。この利用残高に応じてシート 標準コース にある利用残高と月支払額の表を検索する。そのためには、シート 月支払額の セル B2に
=VLOOKUP(B1,標準コース!$A$4:$D$14,4,TRUE)
と入力すればよい。この計算式のなかで新しい点は
の2つ。順次説明する。
まず、範囲指定にシート名がついている表現であるが、これは、「計算式が入力されているシート以外のシートの内容を参照する」ことを Excelに伝えるものである。 Excelでは、どのシートにも行と列があり、 $A$1 というセル番地が表すセル（シート左上のセル）は、各シートごとに1つずつ存在する。同じシートの中では、シート名を省略しても混乱しないが、他のシートの内容を参照したいときには、単に $A$1 と書いても区別ができない。
そのため、シートの名前をまず書き、区切り文字として ! （感嘆符）を入れて、その後にシート内でのセル番地を入力するという形式を用いる。実際に入力する際は、手でシート名から入力するよりは、参照したいシートを見出しタブをクリックしてアクティブにし、直接範囲指定した方が確実である。
次に、VLOOKUPの第4引数が TRUEとなっている件。VLOOKUPは第4引数がTRUEのときは「検索値 < 検索範囲の一番左の列の値」となる行のうち、検索範囲の一番左の列の値が最大となる行を検索する。ヘルプには
TRUE を指定するか省略すると、検索値と完全に一致する値、またはその近似値が返されます。検索値が見つからない場合は、検索値未満の最大値が使用されます。
と書いてある。言葉だけでは分かりづらいので例を挙げよう。下のような表を検索範囲として第１引数が 20、第４引数が TRUEである VLOOKUPを実行すると、
という検索を行う。ここで重要なことは、
の2点。VLOOKUPは検索範囲の全体を見て「検索値未満の最大」を求めている訳ではない。単に越えた行の１行上の行を返すだけ。
このため、VLOOKUPの第４引数を TRUEにして用いる場合は、検索範囲全体が一番左の列の値の昇順に整列されている必要がある。
| 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関数を使うこと）。また、課税所得を横軸、平均所得税率を縦軸に取って図示しなさい（グラフの形式は各自適切であると思う形式を選択すること）。
練習問題5. リボルビング払いは実際にどれくらい払うのか
epos.xls のシート 定額コース支払 、 標準コース支払 、 長期コース支払 は、シート 利用履歴 に入っているショッピングクレジットの利用をリボルビング払いで支払った場合に、実際にどの程度の支払を行うかを計算するためのワークシートである。定額コース支払 は完成しているが、 標準コース支払 と 長期コース支払 は月支払額の列が入力されていない。
標準コース支払 で使う残高と支払額の関係は 標準コース に、長期コース支払 で使う残高の支払額の関係は 長期コース に入力済みである。これらのデータと VLOOKUP関数、および 定額コース支払 の月支払額の列に入力されている計算式を参考にして、標準コース支払 および 長期コース支払 を完成させなさい。
練習問題6. リボ払いに関する以下の事例と練習問題4の定額コース支払をみて、定額リボ払い利用に関するあなたの意見をまとめなさい
事例： 「リボ払い： 買い物50万円、返済総額125万円超に」（2006年11月22日、毎日新聞）
©2006, Hiroshi Santa OGAWA
このページにアダルトコンテンツ、XXXコンテンツ類は一切含まれていません。暴力反対.