神奈川大学　経済学部
2013年度経済情報処理II

第7課 同じセルの値を複数の計算式から参照する（絶対参照）

第7課の目標

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

準備問題

単価×数量で小計を計算し、さらに小計×5%で消費税額を計算しなさい（データは data03.xlsx の　準備問題　シート）

番号 商品名 単価 数量 小計 消費税額
1 2GB USBメモリ 6580 32
2 2GB SDメモリーカード 4780 13
3 2GB メモリースティックPRO 4980 23
4 2GB xDピクチャーカード 7980 11

1. 複数の計算式で同じセルの値を参照する計算式

例題1. 消費税率が変わった時にラクができるワークシートを作成する

消費税率は2013年9月には5%であるが、昨年度の国会で消費税引き上げ法案が可決したことにより2014年4月には8%、2015年10月には10%に引き上げられる予定である。そうなると、準備問題のように計算式の中に変わるかもしれない 5%という値を埋め込んでおくことはあまり望ましくない。そこで、下の図のように商品名、商品単価、数量が表として与えられており、さらに消費税率が特定セル（下の図の場合はB1セル）に入力されている場合に、小計をもとに消費税額を計算することを考えてみよう（データは data03.xlsx の 例題1 シート）。

A B C D E F
1 消費税率 5%
2 番号 商品名 単価 数量 小計 消費税額
3 1 2GB USBメモリ 6580 32
4 2 2GB SDメモリーカード 4780 13
5 3 2GB メモリースティックPRO 4980 23
6 4 2GB xDピクチャーカード 7980 11

Step 1. 1行分の計算式を作成する

小計のE3セルに入力すべき内容は、C3セルの内容（単価）とD3セルの内容（数量）を掛けたものであるから、

さらに、消費税額の F3セルは、上で計算した小計 E3セルの値と、消費税率 B1セルの値を掛けたものであるから、

と書きたいところだが、ここは以下のように書く。$記号がミソ。

※$（ドル記号）は、Shiftを押しながら4のキー（テンキーではなく、アルファベットが並んでいる方の）を押すと入力できる

Step 2. 計算式をコピーする

できあがりはこんな感じ。

Step 3. コピーされた計算式を確認しておく

この例題で新しい要素は、Step1で入力した $B$1という、セル参照である。この参照は計算式をコピーしても調整されないため、いつも同じセルを参照できるという特徴がある。実際にF4〜F6のセルに入っている計算式を表示してみると以下のようになっているはず。

Step 4. 消費税率を8%と10%に変えて、消費税額が意図したとおりに変わるかどうかを確認する。

8%のとき

10%のとき

練習問題1

データファイル data03.xlsx の　消費税 シートに入っているデータを使って、C1セルに入っている消費税率を絶対参照して消費税額を計算する計算式を作成しなさい。また、実際にC1セルに入っている消費税率を 8%, 10%, 15%に変えてみて、消費税額が自動的に再計算されていることを確認しなさい。

2. 市場占有率の計算とグラフ（絶対参照の応用例）

例題2. 以下の表はきつね食品の営業資料である。このデータを使って冷凍うどん市場の分析を行う

冷凍うどん売上高（億円） 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
きつね食品 10 12 15 9 8 7 7 6 7 8 9 12
たぬき物産 8 8 8 8 8 8 8 8 8 8 8 8
いなり産業 10 12 14 16 18 20 22 24 26 28 30 32

Step 1. きつね食品の売上高を棒グラフにしてみる。データは data03.xlsx の　冷凍うどん市場 シートに入っている

グラフを作成するには？　→ Excel編 Q37

まず、きつね食品の売上高を棒グラフにしてみよう。使うグラフの種類は「集合縦棒」を使う。タイトルとラベルは以下のように指定する

できあがりはこんな感じ。

これだけ見ると、きつね食品の冷凍うどんは寒い時期には売れるが夏はあまり売れないという（あまりおもしろみのない）結論が出そうである。しかし、他社との比較でどうなっているかも調べたいので、市場占有率（シェア）もグラフ化してみる。

Step 2. 各社の市場占有率を計算する

ある会社（Aとしよう）の市場占有率は、A社の売上高÷市場全体での売上高　で計算できる。そこで、まず各月の市場全体での売上高を計算する

Step 2-1. 市場全体での売上高の計算

各月の市場全体での売上高を計算するためには、月ごと（つまり、列方向）に売上高を足しあわせればよい。

合計を求めるには？　→ Excel編 Q19

全体に占める割合を求めるには　→　Excel編 Q18

ここまで計算するとこんな感じ

Step 2-2. 各社の市場占有率を計算する

既に各月の市場全体での売上高は計算済みなので、各社の売上高を市場合計で割れば市場占有率が計算できる。上の図のようにデータが入っている場合できるかぎり計算式の入力回数を減らして、コピーでラクをすることを考える。

Step 2-2-1

きつね食品の1月でのシェアは

という計算式で求める。（=B2/$B$5ではないことに注意）

Step 2-2-2

残りのシェアを計算するために、B8セルの計算式を3社の1〜12月のセルに計算式コピーする

できあがりはこんな感じ

Step 2-2-3

コピーの結果セルに入っている計算式が正しいかどうか、いくつか抽出して確認しておく。以下のようになっていれば多分OK。

Step 3. きつね食品の売上高とシェアを同じグラフに表示する

離れた範囲のデータを使ってグラフを作成するには？　→ Excel編 Q38 Column

まず、集合縦棒でグラフを作ってみるとこんな感じになる。

時間的にシェアがどのように変動しているか直観的にわかりやすくするため、シェアのグラフをマーカー付き折れ線グラフにしてみよう。

一部のグラフの種類を変更するには？　→ Excel編 Q39

残念ながら、売上高とシェアでは絶対的な大きさが違いすぎるので、同じ縦軸で表示しようとするとシェアが横軸に張り付いてしまって変化がよくわからない。そこで、シェアにはグラフの右側の軸（第2軸）を使って縦軸を分けてみる。

大きさの異なるデータを表示するには？　→　Excel編 Q42

グラフタイトルや凡例を入れて見栄えをよくしよう

この作業でできるグラフはこんな感じ

Step 3-2

このままだと、縦棒も折れ線も両方とも「きつね食品」になっていて訳がわからないので、系列の名前を「売上高」「市場占有割合」に書き換える。縦棒が売上高、折れ線が市場占有割合である。

系列の名前、項目軸ラベルを変えるには　→　Excel編 Q40

練習問題3. 例題4のデータで、たぬき物産、いなり産業についても同様のグラフを作成しなさい

練習問題4: 19×19の計算表をつくる

インドでは日本で言う九九の代わりに、19×19までの積をすべて覚えるという。このために 19×19の表をつくりなさい。
（ヒント）絶対参照は、列番号、あるいは行番号だけに $をつけると、コピーの方向によって参照先をコントロールできる

Tips: いろいろな絶対参照

Tips: Excel での内部表現から相対参照と絶対参照を理解する

確認問題
ブック data03.xlsx の　携帯電話契約数　シートには、わが国の携帯電話キャリア4社の月末契約数（2007年1月〜2013年8月）が入力してある。このデータを使って、以下の作業を行いなさい。
(1)月ごとの総契約台数を計算し、その合計台数を使って各社の月末シェアを計算しなさい。
(2)あなたが使っている携帯電話のキャリアの月末シェアを折れ線で、契約台数を縦棒で表した2軸上の折れ線と縦棒グラフを作成しなさい（携帯電話を持っていない場合は、ソフトバンクのデータを用いて作成しなさい）。

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

©2013, Hiroshi Santa OGAWA
このページにアダルトコンテンツ、XXXコンテンツ類は一切含まれていません。暴力反対.