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

エクセルの応用

今回の課題は、エクセルを使って、実際のデータを対象にした処理を行ってみる。
  1. ソーティングと抽出
    データ:不動産情報(小田急線・京王線沿線)
  2. データの連結(CSVデータなので、右クリックで「保存」してから利用する。)
    データ1:イネ品種と形質データ
    データ2:イネ品種の解説データ
    これは、イネのゲノム全域と形質との分析を行ったZhaoの研究データである。http://www.ricediversity.org/data/index.cfm
  3. エクセルデータの編集
エクセルの少し進んだ使い方をマスターしてほしい。

1)ソーティングとフィルタリング(抽出)

ソーティングとは並べ替えの事である。エクセルにはソート機能とフィルタ機能があるので、まずその機能を学習する。
サンプルデータとして不動産情報を用いたので、次の操作をしてみよう。
  1. 賃料でソートする。(賃料の列に移動し、A->Zアイコンをクリック)
    元に戻すのは、物件番号でソート。(または、[元に戻る」ボタン)
  2. 築年月の新しい順番にクリック。(ソートアイコンのZ->Aをクリック)
  3. 複数の条件でソーティングする。「データ」メニューから「並べ替え」を選び、条件を複数指定する。
    例:最寄り駅で並べ替え、条件を追加し、駅徒歩で並べ替えてみよう。
  4. フィルタを有効にする。(漏斗アイコンのクリック、又は、「データ」メニューの「フィルタ」をクリック)
  5. 行見出しで、いろいろと試して見る。
    金額の欄では、○○以上、○○以下。などの表示が可能である。また、フィルタのクリアをすると、全数表示できる。
  6. 沿線を「京王線」にして、賃料が8万円台の物件を表示する。
    賃料8万円台とは、8万円以上9万円未満である。
  7. 駅からの徒歩10分未満で、賃料と管理費を合計して8万円未満の物件を捜してみよう。
    管理費の右側に「月額」という列を挿入する。そして月額の欄では、賃料+管理費を計算する。フィルタはその月額に対して実施する。
【課題1】

2)データの結合と欠測値の取り扱い

Excelで非常に強力なVlookup関数を利用する。これは、表参照関数と呼ばれるもので、データベースの検索キーの様に利用する事ができる。また、欠測値のある場合の平均値などを計算する。
利用データは、イネのゲノム全域で形質との関連分析を行った際に用いたZhaoの研究データである。http://www.ricediversity.org/data/index.cfm但し、今回利用するのはゲノム部分では無く、品種名と品種毎の形質(表現形)の部分である。 データ1:イネ品種と形質データ
データ2:イネ品種の解説データ
データ2には、データ1に含まれている品種の解説が入っている。共通IDは、NSFTVIDである。そのため、NSFTVIDに基づいて、データ1の表にデータ2の表から、Accession.Name(品種名)とCountry.of.origin(原産国名)を取り入れる。その手順を以下に示す。
  1. データ1とデータ2をエクセルで開く。
  2. データ1のC2セルで「ウィンドウ枠の固定」(ウィンドウメニューから)を行う。(便利なので・・)
  3. データ1をエクセルのブックとして「保存」する。データ処理の途中に誤操作やPCの不調でデータを失う確率を減少できる。なお、Excelブック(xlsx)として保存する。
  4. データ1のAM列とAN列の見出しを、それぞれ、Accession.Name Country.of.originとする。(データ2からコピペする。)
  5. データ2をキー列の要素で昇順にソートしておく。(具体的にはNSFTVID列でA->Z とソートすることである。)
  6. データ1のAccession.Nameを、NSFTVIDをキーにして、データ2から取り込む。
    即ち、AM2のセルで、Vlookup関数を用いる事になる。【検索値】はデータ1のNSFTVID値、【範囲】がデータ1のNSFTVID列とAccession.Name列、Country.of.origin列である。【列】が、今回利用するAccession.Name列なので、2。なお、範囲は絶対指定すること。
    具体例:=VLOOKUP(B2,Variety.csv!$C$1:$E$414,2, false)
  7. AM2のセルが正常に表示されたら、オートフィルで、最下行まで式をコピー。
  8. AN2は、データ2から引用するのが、Country.of.origin列なので、列番号が3となる。
    具体例:=VLOOKUP(B2,Variety.csv!$C$1:$E$414,3, false)
  9. 同様に最下行までオートフィルする。
  10. 最後に数的形質の統計量を取ってみよう。平均、最大、最小、データ個数などを計算する。
    ここでは欠測値があるのでデータ個数や平均を計算する時はcountif()やAverageif()関数を用いる。条件式には ">0" などを用いる。
【課題2】
【課題3】 平成28年度産のコメに関する、次の3つのエクセルのワークシートがある。 このうち、01のワークシートを加工して、次のようにせよ。(Vlookupを用いると良いが、やり方は各人にまかせる) なお、食味ランキングは、都道府県が細分されている場合があるが、01のワークシートで産地が分割されていない場合には、その都道府県産のものはすべて特Aとして扱うことにする。
この表の中身を吟味して、米の銘柄、価格、ランキングに関して、自分なりの分析を加えよ。また、分析の結果、得られた知見(エビデンス)を述べ、それに対する、自分なりの意見や感想を書き込む。ワークシート内にテキストボックスで書き込むと良い。

【例】ランキング1位のコシヒカリについて、産地ごとの価格ランキングを出す。
得られた知見:「新潟魚沼産の価格は他の産地より○○%高かった。」
意見・感想:「新潟では、他の銘柄米を入手するのは難しいかもしれない。粘りの多い米でチャーハンを作るのは大変そうだ。」

【課題の実施に当たって】 健闘を祈る。
【参考】
  1. 様々なグラフの作成
  2. ピボットテーブルの利用
  3. グループ化と3D集計と統合
  4. マクロの実例