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

第4課の目標

第3課での例では計算式が参照しているセルは計算式をコピーしたときに自動的に調整されていた
しかし、場合によっては計算式をコピーしても同じセルの値を参照し続けていて欲しいことがある
ここでは、計算式をコピーしても同じセルを参照し続けられる方法（絶対参照）を学ぶ

準備問題

コンビニの取り扱い商品について、税抜き価格×8%で消費税額を計算し、さらに税抜き価格＋消費税額で税込み価格を計算しなさい（データはtask1-3.xlsx の　準備問題　シート）

商品コード	商品名	税抜き価格	消費税額	税込み価格
1	いなり寿司	230
2	おかかおにぎり	110
3	うめぼしおにぎり	110
4	鶏五目おにぎり	110
5	シーチキンマヨネーズおにぎり	110
6	とんかつ弁当	450
7	からあげ弁当	450
8	ハンバーグ弁当	500
9	幕の内弁当	370
10	カレーライス	400
11	中華丼	370
12	コーラ	160
13	缶コーヒー	120
14	オレンジ	160
15	紅茶飲料	160
16	スポーツ飲料	160
17	緑茶	160
18	烏龍茶	160
19	ジャスミン茶	160
20	チョコレート	100
21	ビスケット	430
22	クラッカー	300
23	ポテトチップ	180
24	トルティーヤチップ	150
25	飴	120
26	ビッグフランク	143
27	アメリカンドッグ	100
28	からあげ	47
29	コロッケ	82
30	フレンチフライ	112
31	肉まん	112
32	あんまん	112
33	ピザまん	112
34	いれたてコーヒー	108

1. 消費税がそのうち10%に上がるらしいです

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

消費税率は2017年9月には8%であるが、そのうちに10%に上がる予定である。日本の財政赤字の額から考えると、将来的には25%を超えるような消費税率が必要となると推定されていることもあり、今から作るシステムで消費税率を固定の値として計算式の中に組み込んでおくのはあまり賢くない。そこで、下の図のように消費税率を特定セル（下の図の場合はB1セル）に入力しておき、消費税額を計算することを考えてみよう（データはtask1-3.xlsx の例題1 シート）。

消費税額のD4セルに入力すべき内容は、C4セルの内容（税抜き価格）とB1セルの内容（消費税率）を掛けたものであるから、

と書きたいところだが、ここは以下のように書く。$記号を列番号と行番号の前につけるのがミソ。

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

この例題で新しい要素は、Step1で入力した $B$1という、セル参照である。このように、行番号や列番号に$をつけた形式のことを絶対参照と呼ぶ。絶対参照は計算式をコピーしても調整されないため、いつも同じセルを参照できるという特徴がある。各自、D5〜D35に入力されている計算式を確認しておきなさい。

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

データファイル task1-3.xlsx の　消費税シートには、白楽店の4月売上データと、C1セルに消費税率が入力されている。
(1)この消費税率を絶対参照して各取引で発生する消費税額を計算する計算式をD4セル〜D722セルに作成しなさい（必ず計算式のコピーを用いること。手作業で700件以上入力すると日が暮れる）。
(2)白楽店で4月分として納税する消費税額の合計を計算する計算式をE1セルに入力しなさい（ヒント：SUM()関数）
(3)C1セルに入っている消費税率を 10%, 15%に変えてみて、消費税額が自動的に再計算されていることを確認しなさい。

データファイル task1-3.xlsx の AKB48 シートには、AKB48総選挙の1回目および2回目のデータが入っている。
(1)1回目総選挙および2回目総選挙のそれぞれの総選挙について、合計得票数を計算しなさい。
(2)(1)で計算した1回目、2回目の合計得票数から、1回目から2回目への合計得票数の伸び率を計算しなさい（ヒント：伸び率は（2回目合計得票数−1回目合計得票数）÷1回目合計得票数　で計算できる）
(3)(2)で計算した合計得票数の伸び率と1回目の得票数から、各メンバーの2回目総選挙での予想得票数を計算しなさい。
(4)(3)で計算した予想得票数と、2回目総選挙で各メンバーが実際に得た得票数との差を計算しなさい。
(5)(4)の結果の正負から、「1回目と2回目の間に人気が上がったメンバーと、それほど上がらなかったメンバー」について考察しなさい。

2. コンビニ店舗間の月間売上比較

例題2. 以下の表はコンビニ3店舗の月間売上額（単位: 万円）を表している。

Step 1. 白楽店の売上高を棒グラフにしてみる。データは task1-3.xlsx の　月間売上シートに入っている

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

これだけ見ると、白楽店は寒いときと暑いときには売れないが、それ以外の時期は売れるというかなり特徴的な季節変動を示していることがわかる。他のお店もこんな変動をしているのかどうかも調べてみたいので、売上割合（シェア）もグラフ化してみる。

ある店舗（Aとしよう）の売上割合は、店舗Aの売上高÷全店舗での売上高　で計算できる。そこで、まず各月の全店舗での売上高を計算する

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

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

という計算式で求める。（=B2/$B$5ではないことに注意）。今回は月が変われば全店舗合計（列）も変わるので、行番号は固定だが、列番号は固定してはいけない。

残りのシェアを計算するために、B9セルの計算式を各店舗の1〜12月のセルに計算式コピーする

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

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

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

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

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

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

寒い時期にコンビニの売り上げが下がるのかと思ったが、シェアを見ると白楽店の売上額が減っている月はシェアも下がっている。ということは、他の店舗では「寒いから売れない」「暑いから売れない」みたいなことは発生していないことを意味する。つまり、白楽店は白楽店固有の理由で2月、3月、8月、9月の売上が下がっていることがわかる。

練習問題3. 例題2のデータで、みなとみらい店、反町店についても同様のグラフを作成しなさい

練習問題4（☆☆）. 白楽店で見られた大きな季節変動の理由について、他店舗と比較して考察しなさい。

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

確認問題
ブック task1-3.xlsx の　AKB総選挙　シートには、第1回総選挙から第6回総選挙までの6回すべてで得票があったメンバーの得票数が入っている。一番下の行に入っている「全投票数」は、各回での全メンバーの得票数合計が入っている（つまり、上の方に入っているメンバーの得票数合計ではない）。このデータを用いて以下の作業を行いなさい。
(1)各メンバーの得票数が、総選挙ごとの全得票数に占める割合を計算しなさい。
(2)あなたが好きなメンバーの得票数を棒グラフで、（1）で計算した得票割合を折れ線グラフで表した2軸グラフを作成しなさい。軸ラベルなどはわかりやすいように調整すること（リストの中に好きなメンバーがいない場合は、指原莉乃のデータを使って作業しなさい）

	1月	2月	3月	4月	5月	6月	7月	8月	9月	10月	11月	12月
白楽店	1168	214	239	1197	1192	1196	1275	221	216	1279	1110	1192
みなとみらい店	1732	1544	1720	1604	1758	1651	1677	1733	1596	1693	1673	1748
反町店	434	426	469	442	473	418	458	452	459	455	453	460

第4課 絶対参照を使った計算式

1. 消費税がそのうち10%に上がるらしいです

2. コンビニ店舗間の月間売上比較

第4課絶対参照を使った計算式