同じセルの値を別の計算式から参照する

例題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

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

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

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

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

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

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

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

確認問題
値引き計算：　「夏物バーゲン」での値引き価格を求める
『夏物バーゲンが始まりました！　全品、定価から30%offのセールです。』
商品ごとの定価はブック data03.xlsx の夏物バーゲンシートに入っているので、その値を用いてそれぞれの商品の販売価格を求めなさい。
（1）全品30%offのとき、販売価格=（1−値引き率）×定価＝（1−30%）×定価＝（1−0.3）×定価となる。C1セルに入っている値引き率と、B5セルに入っている定価を用いて、トートバックの販売価格を求める計算式をC5セルに入力しなさい。
（2）その他の製品の販売価格は(1）で作成したC5セルの計算式をコピーすることによって楽に入力できる。絶対参照を使って工夫して入力しなさい。

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 の　冷凍うどん市場シートに入っている

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

（上級）消費税計算：　消費税の税率が変わったときに、税込み価格を計算する
消費税率は3%から5%、8%と段階的に引き上げられてきており、来年度には10%への引き上げが予定されている。しかしながら、日本の深刻な財政赤字の状況を考えると、さらに高い消費税率が必要となる可能性も否定できない。
ブック data03.xlsx の　税率ごとのランチ価格　シートには、いくつかのメニューとその税抜き価格が入っている。消費税率がそれぞれ3%、5%、8%、10%、15%、20%、25%、30%の場合の税込み価格を計算しなさい。
(1)消費税込み支払額＝税抜き価格＋消費税額＝税抜き価格＋税抜き価格×消費税率　で定義される。たとえば、税率3%の場合にAランチを購入すると、800円＋800円×3%＝800円＋800円×0.03＝800円＋24円＝824円が税込み支払額となる。C4セルに入力済の消費税率と、B5セルに入力済みのAランチ税抜き価格を使って、この計算を行う計算式を作成しなさい。
(2)上で作成した計算式をコピーして、残りの商品と消費税率について税込み支払額を計算したい。まず、（Bランチ、3%）と（Aランチ、5%）に計算式をコピーして、計算結果が期待された値（それぞれ1236円、840円）になっているか確認しなさい。期待された値になっていない場合は、（1）で作成した計算式中の、どのセル参照を固定したらいいか考えて、（1）の計算式を変更しなさい。正しい答が得られるまで（1）と（2）を繰り返しなさい。
（3）計算式をコピーして、全てのケースでの税込み支払額を計算しなさい。※チェックポイント：　（ラーメンライス、30%）は546円である。

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

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

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

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

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

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

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