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

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

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

Excelの利用（2）

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

シートの移動とコピー

• シートの移動：あるブックにあるシートを他のブックに移動すること（もとのブックには残らない）。
• シートのコピー：あるブックにあるシートを他のブックにコピーすること（もとのブックにも残る）。
• 別ブックの末尾に移動したい場合
  
  • 移動させたいシートを選択する。
  • 移動させたい先のブックも開いておく。
  • シート名を右クリックし【移動またはコピー】→【移動先ブック】を選択→【（末尾へ移動）】→【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で挿入したシートにある表を用いて、各月の降水量に応じて、その月が「乾燥／普通／湿潤」のどれであるかを判定し、それぞれの数を数える。
• 「判定」にはvlookup、「数える」にはcountifという関数を用いる。

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

各月降水量の判定基準

範囲	最低値	判定	数
0ml以上90ml未満	0	乾燥
90ml以上150ml未満	90	普通
150ml以上	150	湿潤

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

気象データはいずれも「気象統計情報」（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】キーを押し、再びオートフィルを行と、正しく入力される。
• そこで、式を見ると、【割られるセル】は相対的に別のセルに変わっており（相対参照）、【割るセル】は絶対的に同じセルが指定されている（絶対参照）。
• ここで、上で、【割られる数】や【割る数】ではなく【割られるセル】や【割るセル】と書かれていることに注意する。

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

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