(農業)情報工学・課題　(2023/10/12)

エクセルの基礎

今回の課題は、エクセルを使って、次の解析を行う事である。

関数による計算
数式を使った計算
数式を使ったBMI計算
時系列データのグラフ化と解析
相関データのグラフ化と解析

今回の課題用ワークシートは、1から3まではここに用意した。
また、4と5はここに用意した。

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

１）【課題1】(関数による計算)の手順

基本統計量のシートを呼び出す。(全国の県民所得の一覧表である)
合計、平均、データ数、最大値、最小値を、関数を利用して求める。
ここでは、オートサム関数(Σ)と、その横の小さな▼を利用する。
　(関数を指定した後、計算範囲をマウスで指示すると、計算結果が得られる。)
関数は、文字列として、直接入力することも出来る。(例: =average(範囲), '='を忘れずに!)
「中央値」から「標準偏差」は、その他の関数を用いる。
(関数の挿入はオートサム関数の中から「その他の関数」を選択するか、「挿入」メニューから「関数」を選択することによって行う。
「メジアン（中央値）」の場合、その際、「統計」関数の中から、MEDIANを選択、さらに、数値１の範囲として、４７都道府県のデータを指定すると良い。)
(同様に、モード（最頻値）はMODE、分散はVAR.P、歪度はSKEW、標準偏差はSTDEV.Pである。)
課題1の黄色部分をすべて埋めること。なお、今回のデータは、母集団そのものである（標本では無い）ので、分散、標準偏差については、分母がNの関数を利用すること。すなわち、VAR, STDEVでは無く、VAR.P, STDEV.Pを用いる。

2）【課題2】(数式を使った計算)の手順

さらに、数式を使った計算を行う。
まず、都道府県の順位(ランク)付けを行う。ここは、関数である。
北海道の県民所得順位を求める。
数式バーからランク関数(統計関数グループ:Rank)を呼び出す。
数値は「北海道の県民所得」、範囲は「$都道府県全体」(ここは絶対参照)、順序は0(降順)。
絶対参照のセルアドレスは、コピー（オートフィル）しても、変わらない。指定方法は、Command + T (WinではＦ４)
得られた式を、オートフィルで沖縄県まで引っ張り、埋める。
各都道府県の偏差値を求める。
偏差値は、平均を50として、標準偏差ごとに10ポイント与えた値である。
計算式は、50+(数値 - 平均値)/標準偏差*10　となる。
(平均値と標準偏差の絶対指定を忘れないように)
北海道から沖縄まで求める。(東京の偏差値はいくつになりましたか?) (標準偏差(σ)は、分散の平方根である。） σ＝sqrt( Σ( (Xi - m)^2) )/ N )

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

数式を使ってBMIの計算を行う。
ここで、BMIとは、Body Mass Indexの略で、身長と体重の関係から太り過ぎかどうかを判定する数値である。
「BMIの計算」のワークシートを開く。
BMI=体重/(身長^2)である。ただし、身長はm表示。
BMIを求めてみよう。
次に、得られたBMIの数値に基づき、「やせ過ぎ」「標準」「太り過ぎ」を表示させるようにしたい。
（基準は、19未満で「やせ過ぎ」、19以上25未満が「標準」、25以上で「太り過ぎ」である。）
IF関数を用いる。（例：IF(条件、真の時、偽の時)となる。）
セルE2に、D2の数値が25以上で太り過ぎと表示する時であれば、=IF(D2>=25,"太り過ぎ","それ以外")となる。
今回は、条件が２段階あるので、IF関数を多重に用いる必要がある。
IFS関数を用いる手法もある。
自分のBMIも計算してみよう。

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

4）【課題4】時系列データのグラフ化と解析(シート「時系列」参照)

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

・国勢調査のデータ主要指標の中で、興味を持った時系列を選択してグラフ化する。
（引用元：第七十二回日本統計年鑑　令和5年（総務省統計局））

・グラフには、近似曲線を示すこと。

・データの傾向を分析（解釈：(1項目以上：2項目でも可)）し、その意味を考察（分析：(1項目以上：2項目でも可)）してみる。(解釈と考察は、エクセルシート内に、「テキストボックス」などで記述する。)

5）【課題5】相関データのグラフ化と解析(ワークシート「相関」[相関２]参照)

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

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

・都道府県データの中で、相関を示すと考えられるデータを選定し、エクセルでグラフ化せよ。
・教育用標準データセットの中で、相関を示すと考えられるデータを選定し、エクセルでグラフ化せよ。
（インターネットで、他の相関のありそうなデータを検索しても良い。）

・都道府県データと教育用標準データセットのどちらを用いても良い。

・グラフには、近似曲線を示すこと。

・グラフとデータを分析（解釈）し、その意味を考察すること。(1項目以上：2項目でも可)

・教育用標準データセットは全市町村になっているので、解釈が少し困難かもしれないが、面白い部分もある。

【課題の提出に当たって】

・エクセルで作成すること。(エクセルの課題である)

・今回の課題は、ITC-LMSの課題に提出する。

・課題1から課題5を提出する。

・課題１〜３は、ブック１(Excel1-1.xlsx)にそのまま記述して提出する。

・課題４と５は、新規作成したブックで作成する。（ファイル名は任意）

・提出期限は、10月30日とする。

健闘を祈る。

エクセル利用のTips

常にカーソルの形状に注意を払うこと。
選択ー白プラス（スイスの国旗）、移動ー手の形(Winでは↑+十字)、オートフィルー＋(黒プラス)
オートフィルを活用しよう。
連番、曜日、月データ。(カレンダーの作成をやってみよう)。
等差数列。序数の入力。
オートフィルでは、数式もコピーできる。（但し、相対指定になる）
書式なら、「セル」・セルの書式設定ー>配置タブの活用。
表示形式に注意。%、小数点以下、通貨、そして、日付と時刻（日付は　mm-dd　が便利）
計算式は、=で開始。四則演算は、+,-,*,/
関数は、使いすぎないように。
絶対指定と相対指定。（絶対指定はMacではCommand＋T、WindowsではF4）
エクセルの書式メニューは、「条件付き書式」が面白い。
(「書式」→「条件付き書式」→「数値の条件」→「書式」→「パターン」）
「基本統計量」の表で、順位がベスト５のところを「赤く」、ワースト５を「青く」するにはどうすれば良いか。
また、その時に、都道府県名の表示も変更する場合は？
データメニューは、並べ替え、フィルタ、集計、そして、ピポットテーブル。

(農業)情報工学・課題 (2023/10/12)