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

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

第4課の目標

↑経済情報処理(2017)ホームページに戻る

準備問題

コンビニの取り扱い商品について、税抜き価格×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 シート)。

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

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

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

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

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

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

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

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

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

10%のとき

25%のとき

練習問題1

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

Tips: Excelで長方形範囲を指定する

練習問題2(☆)

データファイル 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店舗の月間売上額(単位: 万円)を表している。

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

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

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

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

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

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

Step 2. 3店舗の売上割合を計算する

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

Step 2-1. 全店舗での売上高の計算

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

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

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

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

Step 2-2. 各店舗の売上割合を計算する

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

Step 2-2-1

白楽店の1月でのシェアは

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

Step 2-2-2

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

できあがりはこんな感じ

Step 2-2-3

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

Step 3. 白楽店の売上高とシェアを同じグラフに表示する

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

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

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

一部のグラフの種類を変更するには? → Excel編 Q41(2)

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

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

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

Step 3-2

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

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

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

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

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

練習問題5(☆): 19×19の計算表をつくる

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

Tips: いろいろな絶対参照

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

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

↑経済情報処理(2017)ホームページに戻る