(農業)情報工学・課題 (2016/10/06)

エクセルの基礎

今回の課題は、エクセルを使って、次の解析を行う事である。
  1. 関数による計算
  2. 分析ツールによる計算
  3. 数式を使った計算
  4. 数式を使ったBMI計算
  5. 時系列データのグラフ化と解析
  6. 相関データのグラフ化と解析
今回の課題用ワークシートは、1から4まではここに用意した。
また、5と6はここに用意した。

エクセルの学習書などでは、エクセルの計算機能として「オートサム」から入り、それから各種の関数の使い方を学習する例が多い。このようにエクセルでは関数を使った計算機能は重要であるが、四則演算機能も極めて重要である。課題1から4は、その両方を活用して、都道府県別の県民所得の分析とBMIの計算をしてみる。

1)課題1(関数による計算)の手順

2)課題2(分析ツールの利用)の手順

3)課題3(数式を使った計算)の手順

4)課題4(数式を使ったBMI計算)の手順



ここからは、別ファイル(ブック)を用いる。

5)時系列データのグラフ化と解析(シート「時系列」参照)

時系列データとは、米価の推移、農業人口の変化、農家一戸あたりの耕地面積などのように、時間的な推移を示すデータの事である。
時間の推移による変化は、さまざまな要因によって変化するため、長期間にわたって予測するのは困難であるが、短期的には過去数年間の推移が持続するだろうといったモデルの元に、予測が可能である。そのため、グラフ化した後、数年後の価格を予測して、外挿グラフを作成することができる。(ただし、あまり先のことを予測することはできない。)
予測については、「データ」から「近似曲線の追加」を行う。いろいろな種類の近似曲線の描画が可能であるが、内容を理解してから適用する必要がある。
サンプル事例では、過去170年間の米価の推移を扱った。さすがに、こういったデータで、直線近似は無謀であるので、移動平均を用いてみた。
また、戦後のデータに限っては、3次曲線で近似してある。

【課題5】
・時系列を示すデータをインターネットなどから検索し、エクセルでグラフ化せよ。
(世界の人口、大気中の二酸化炭素の濃度、平均気温の推移など、○○の推移、などで検索すると良い。)
・データ数は10以上あるものを選択すること。
・グラフには、近似曲線を示すこと。
・データの傾向を分析(解釈)し、その意味を考察すること。(2項目以上)
・【追加】国勢調査のデータ国勢指標データの中で、興味を持った時系列を選択してもよい。

6)相関データのグラフ化と解析(ワークシート「相関」[相関2]参照)

相関データとは、2つの変量が関連を持つ(あるいは、持つかどうかが問題になっている)データの事である。身長と体重、所得と米価など、関連のありそうなデータ群の分析を行う場合に利用する。
(実は時系列データも、「時間」と「価格」などの2変量データであるが、ここでは取り扱わない)
通常「相関」とは、変数(y)と変数(x)の間に直線的な関係が成立することを言う。(単純相関)
すなわち、y=Ax+Bの式で示せる関係の事である。もっとも、現実のデータは、さまざまな誤差を含んでいるため、完璧に直線的な関係になることは無い。その近似度合いを「相関係数(R)」で示している。相関係数は、ー1から1の値を持ち、1が完全な相関。0は無相関、マイナスの値が負の相関である。エクセルでは、相関係数を2乗した値、決定係数(R2)を表示させることができる。

サンプルの相関では、都道府県データから、総人口と世帯数(相関が強いにきまっているが。。)をプロットしてみた。
案の定、決定係数が0.98ときわめて高い値となった。
また、サンプルの相関2では、太田市場でのトマトの入荷量と価格との関係をプロットしてみた。
近似は、線形1次の近似である。決定係数が0.49であるが、相関係数としては、マイナス0.7程度なので、負の相関があると見なして良い。

【課題6】
都道府県データの中で、相関を示すと考えられるデータを選定し、エクセルでグラフ化せよ。
(インターネットで、他の相関のありそうなデータを検索しても良い。)
・データ数は10以上あるものを選択すること。(都道府県の場合にはデータが47あるので問題は無い。)
・グラフには、近似曲線を示すこと。
・グラフとデータを分析(解釈)し、その意味を考察すること。(2項目以上)

【課題の提出に当たって】
・エクセルで作成すること。(エクセルの課題である)
・今回の課題は、ITC-LMSの課題に提出する。
・課題1から課題6を提出する。複数のブックになっても良い。
・時系列の課題と相関の課題は、同一ブック内の、別ワークシートとすること。ワークシートには、適宜名前を付ける。
 ファイル名は、大文字でも小文字でも良いが、必ず、半角英数にすること。
・提出期限は、11月30日とする。
健闘を祈る。
エクセル利用のTips
  1. 常にカーソルの形状に注意を払うこと。
    選択ー白プラス(スイスの国旗)、移動ー手の形(Winでは↑+十字)、オートフィルー+(黒プラス)
  2. オートフィルを活用しよう。
  3. 連番、曜日、月データ。(カレンダーの作成をやってみよう)。
    等差数列。序数の入力。
  4. オートフィルでは、数式もコピーできる。(但し、相対指定になる)
  5. 書式なら、「セル」・セルの書式設定ー>配置タブの活用。
  6. 表示形式に注意。%、小数点以下、通貨、そして、日付と時刻(日付は mm-dd が便利)
  7. 計算式は、=で開始。四則演算は、+,-,*,/
  8. 関数は、使いすぎないように。
  9. 絶対指定と相対指定。(絶対指定はMacではCommand+T、WindowsではF4)
  10. エクセルの書式メニューは、「条件付き書式」が面白い。
    (「書式」→「条件付き書式」→「数値の条件」→「書式」→「パターン」)
    「基本統計量」の表で、順位がベスト5のところを「赤く」、ワースト5を「青く」するにはどうすれば良いか。
    また、その時に、都道府県名の表示も変更する場合は?
  11. データメニューは、並べ替え、フィルタ、集計、そして、ピポットテーブル。