第7回の授業内容と学習目標

講義＋実習：Excelによるグラフの調整と関数の利用

グラフの調整ができる
シートの移動とコピーができる
シートをまたいだ計算ができる
絶対参照と相対参照を使い分けることができる
絶対参照を用いた関数を使うことが出来る

Excelの利用（2）

以下の作業は、このexcelブックを用いること。シートを新規に追加し、名前を「練習1」とすること。

練習1

左下の表のデータを「月」の部分から「15000」の部分までドラッグしてコピーし、新たに追加したシート（練習1）に貼り付けコピーし、帯グラフを2種類（「積み上げ縦棒」と「100%積み上げ縦棒」）作成しなさい（以下の条件を満たすこと）。

グラフタイトルがあること
凡例があること（「グラフ要素を追加」機能を用いる）
グラフの「塗りつぶし」を「パターン」にし、ユニバーサルデザインに配慮した形式にしなさい。

月ごとの支出（2014年・円）
月	飲食費	交通費	被服費	その他
1月	50000	12000	10000	10000
2月	35000	10000	250	8000
3月	45000	18000	8000	12000
4月	55000	24000	12000	8000
5月	35000	30000	250	8000
6月	30000	20000	250	8000
7月	32000	20000	1000	10000
8月	30000	10000	250	10000
9月	32000	18000	250	8000
10月	30000	20000	250	8000
11月	28000	20000	250	10000
12月	55000	28000	10000	15000

シートの移動とコピー

シートの移動：あるブックにあるシートを他のブックに移動すること（もとのブックには残らない）。
シートのコピー：あるブックにあるシートを他のブックにコピーすること（もとのブックにも残る）。
別ブックの末尾に移動したい場合
- 移動させたいシートを選択する。
- 移動させたい先のブックも開いておく。
- シート名を右クリックし【移動またはコピー】→【移動先ブック】を選択→【（末尾へ移動）】→【OK】。

帯グラフの作成

シートyukichiの、1月から3月の支出金額を帯グラフにする。
A2からF5をドラッグして選択。
【挿入】→【横棒】→【2D横棒】→【100%積み上げ横棒】。
行と列の関係が不適切なので、【グラフツール】→【デザイン】→【データ】→【行と列の切り替え】で調整。
1-3月の並び方が不適切なので、軸の部分を右クリックし、【軸の書式設定】→【軸のオプション】→【軸を反転する】で調整。
【2D横棒】のうちの【積み上げ横棒】でも同じことをし、できあがるグラフの違いを確かめること。
余裕のある人は、【グラフツール】→【デザイン】→【グラフのレイアウト】を試してみることで、様々な装飾の工夫があることを確かめること。

行と列の入れ替え

上記のようにグラフの行と列の入れ替えではなく、元データの行と列を入れ替えることもできる。
入れ替えたい部分をドラッグして選択（この場合は、A2セルからH5セル）して、【コピー】する。
周囲に十分な広さがある空白のセルをクリックする。
【ファイル】→【貼り付け】→【▼】→【形式を選択して貼り付け】→【行列を入れ替える】にチェックを入れる。
データをコピーして新しく別の形式に変えて貼り付ける、という考え方である。【形式を選択して貼り付け】は今後も使う。

シートをまたいだ作業

串刺し計算（合算する場合）

1つのブック内にある複数のシートの数値を指定して計算することが出来る。
「calc」というシートにおいて、「jan」というシートから「mar」というシートの間にあるシート全ての、「b2セル」にある値の合計を求める場合。
- 計算結果を出力したいセルに、= s u m ( j a n : m a r ! b 2 )と入力する（見やすいように文字間に半角スペースを入れている）。
留意点
- どのシートでも特定の値が同一のセルに入るように、各シートの規格・使い方を事前に吟味する必要がある。

複数シートでの同一処理

1つのブック内にある複数のシートの同じセルに、同じ計算式（などのデータ・数値・書式設定）を入れることができる。
「jan」・「feb」・「mar」の3つのシートにおいて、ともに、「B6セル」に、「B2からB5セルの数値の合計値」を出力したい場合。
- 「jan」シートを開いている状態で、ctrlキーを押しながら「feb」・「mar」のシート名をクリック。
- 「jan」シートで、通常通りの計算手順を行う。
- シート名を再びクリックし、シートの同時選択状態を解除する（これをしないと以降の作業も他のシートに影響を及ぼす）。
セルの色の変更などの、計算以外の処理にも用いることが出来る。

絶対参照と相対参照：数式のオートフィルの特徴

セルをコピーすると、通常は、「あるセルとあるセルの関係」がコピーされる。

練習2：絶対参照の練習

シート「yukichi」のG列に、各月の支出の合計金額を、関数とオートフィルを使って算出する
シート「yukichi」のH列に、各月の支出項目間の平均金額を、関数とオートフィルを使って算出する
シート「yukichi」の、1月と2月の間、2月と3月の間に、それぞれ空白行を1行挿入する。
空白行には、各項目の額が、その月の支出全体の中でどの程度の割合を占めているのかを計算した結果を表示させたい。
B4セルに、=B3/G3と入力し、オートフィルでF4セルまで引っ張る。どうなるか？
エラーメッセージが出ているセルの数式バーを確認する。
- 【#DIV/0!】とは、数式または関数が0または空のセルで除算されている、という意味。口語訳すると、数が入っていないセルで割り算しようとしている、ということ。
割られる数と割る数の関係

本来：割られる数はB→C→D…と変わっていって欲しい（相対的）・割る数は常にH3で変わって欲しくない（絶対的）。
現実：割る数も割られる数も変わっていってしまっている。

オートフィルを用いると、【セルとセルの関係】ごとコピーされてしまう。B4セルには、【1コ÷5こ右】という式が入っており、それがそのままコピーされたので、割る数が空白セルになってしまった。
割る数を一定に固定する（絶対的にする）必要がある。
=B3/G3ではなく、= B 3 / $ G $ 3 にする（数式バー上で、「G」と「3」の間で【F4】を押す）。その後でオートフィル。
毎回変わって欲しいセルには何もしない（相対参照）・毎回同じでいて欲しい数には$マークをつける（絶対参照）。
正しく求められた値を、パーセント表示で小数第1位まで表示する。
- 【セルの書式設定】→【表示形式】→【パーセンテージ】

**東京の各月降水量と年間降水量に占める割合（2019年）**
月	降水量（ml）	割合	判定
1月	16.0
2月	42.0
3月	117.5
4月	90.5
5月	120.5
6月	225.0
7月	193.0
8月	110.0
9月	197.0
10月	529.5
11月	156.5
12月	76.5
合計
平均

課題1：関数と絶対参照による数値の処理とグラフ作成

新たなシートを挿入して、そのシートの名前を「降水量」とすること。
上記の表（東京の各月降水量と年間降水量に占める割合）のデータをドラッグして貼り付け、緑色のセルに年間降水量を表示する（sumを使用）。
ピンク色のセルに降水量の平均を表示する（averageを使用）。
オレンジ色になっているセルに、「その月の降水量が年間降水量に占める割合」を求める式を入れる。
このとき、年間降水量は絶対参照にする。オートフィル機能を使って各月降水量と、その量が年間降水量に占める割合を求める。
「割合」の数値は、パーセンテージ表記で小数第1位まで表記する。
月の列と割合の列のみを使って、「年間の降水量に対して各月の降水量が占める割合」を表現する円グラフを書く。
円グラフには、それにふさわしいタイトルをつける。

課題2：絶対参照を用いた関数

いくつかのセルに入力された値を、別のセルに入力されている一定の基準値に基づいて処理し、その結果をさらに別のセルに表示する場合などに用いる。
練習2で挿入したシートにある表を用いて、各月の降水量に応じて、その月が「乾燥／普通／湿潤」のどれであるかを判定し、それぞれの数を数える。
「判定」にはvlookup、「数える」にはcountifという関数を用いる。

右の方に判定基準の表をコピーして貼り付ける。

**各月降水量の判定基準**
範囲	最低値	判定
0ml以上90ml未満	0	乾燥
90ml以上150ml未満	90	普通
150ml以上	150	湿潤

1月の分の判定をする（vlookupと絶対参照）…各月の降水量の値を、青色のセルの基準に従って判定する、と考える。
- 「判定」の列の「1月」の行のセル（黄色のセル）に、関数の挿入から、「検索/行列」の中にあるvlookupを入れる。
- 【検索値】とは、判定対象の値。この場合は、16.0が入力されているセルのこと。範囲をドラッグすると「a1:b3」のような表示になるが、それぞれの列番号と行番号の間でｆ4キーを押し、絶対参照に切り替える。
- 【範囲】とは、基準の値。この場合は、0から湿潤が入力されている2列3行のセルのこと。
- 【列番号】とは、基準が入力されている部分のうち、判定結果として表示する値・文字が入力されている列のこと。この場合は、2。
それを、オートフィルによって12月までドラッグして全月の判定を行う。
この方法で成績判定を行う教員もいる（獲得点数と基準表を用いて）。
判定結果を数える（countifと絶対参照）…「数」の列の「1月」の行（赤の一番上のセル）のセルに、「1つ左のセルに入っているのと同じ言葉が、特定の場所にいくつあるか」を表示する、と考える。
- countifで、特定の【検索範囲】に特定の【検索条件】と合致する値がいくつあるかを求める。
- 「数」の列の最初の行のセルに、関数の挿入から、「統計」の中にあるcountifを入れる。
- 【範囲】とは、探したい値・文字が入力されている可能性のある範囲。この場合は、「判定」の列の1月から12月までのセルのこと。絶対参照に切り替える。
- 【検索条件】とは、探したい値・文字のこと。この場合は、「乾燥」が入力されているセルのこと。
それを、オートフィルによって「湿潤」の行までドラッグして判定を行う。

課題3：絶対参照の応用：数えられるのは数値だけではない

K-LMSのアナウンスメント上で共有した「アンケートの回答」を【ダウンロード】して保存する。
保存したファイルを開き、【編集を有効にする】をクリックする。
B2セルをクリックし、【表示】→【ウィンドウ】→【ウィンドウ枠の固定】を行う。下にスクロールしても先頭の行と左の列は見出しとして常に見えているようにすることができる。
D195-201セルをドラッグし、V列までオートフィルする。
B196セルに、countif関数を用いてB2セルからB193セルの間に、「履修した」という語がいくつあるかを表示する。

B196セルに表示した結果を、下や右のセルにも効率的に適用するにはどうすれば良いか？
得られた数値を使ってグラフを作成するにはどうすれば良いか？

気象データはいずれも「気象統計情報」（2020年6月4日アクセス）より

補足：絶対参照と相対参照

オートフィルを使って計算式や関数を使う時には、「絶対参照と相対参照」に注意する必要がある。
下の図では、飲食費や被服費やその他の支出が、1ヶ月の合計支出額のうちのどのくらいの割合を占めるかを計算しようとしている。

式は、［個々の項目の金額（B3からB5セル）］÷［1ヶ月の合計支出額（B6セル）］になればよい。
つまり、C3セルにおいては【=b3/b6】、C4セルにおいては【=b4/b6】、C5セルにおいては【=b5/b6】、である。
しかし、【=b3/b6】という一見正しい式を、その下のセルにオートフィルでコピーするとエラーが出る。
なぜか？　C4セルを見ると、【=b4/b7】という式が入っている。C5には、【=b5/b8】という式が入っている。
【割られるセル】は毎回変わって欲しいが、【割るセル】は変わっては困る。
さて、C3セルには、【そのセルから見て一つ左のセル÷そのセルから見て一つ左かつ三つ下のセル】という式が入っている。
そのため、C4セルに行った時には、【=b4/b7】という式が入ってしまう。
なぜなら、C4セルから見れば、【一つ左のセル】はb4セルであり、【一つ左かつ三つ下のセル】はb7セルだからである。
では、どうするか。【割られるセル】は相対的に変わるべきであり、【割るセル】は絶対的に同じでないと困る。
そのため、【そのセルから見て一つ左のセル÷B6セル】という式にしたい。
そこで、【割るセル】を【絶対参照】の機能を使って固定する（一回一回相対的にズレていってしまわないようにする）。
【割るセル】の数式バーをクリックして、列記号と行番号の間、この場合は「B」と「6」の間で【F4】を押す。
すると、【＄】マークが現れるので【Enter】キーを押し、再びオートフィルを行と、正しく入力される。
そこで、式を見ると、【割られるセル】は相対的に別のセルに変わっており（相対参照）、【割るセル】は絶対的に同じセルが指定されている（絶対参照）。
ここで、上で、【割られる数】や【割る数】ではなく【割られるセル】や【割るセル】と書かれていることに注意する。

エクセルの計算は、【セルとセルの関係】を指定することで行われる。

人間が行うのは、【セルとセルの関係を正しく指定すること】である。

トップページへ