これまでExcelを用いて様々なデータを入力し、計算する方法を学んできた。 計算対象となるデータは自分で入力する必要があることも多いが、他から与えられることも多い。 例えば様々なウェブサイトにある情報を自分で解析したいとか、実験装置が出力するデータを解析する場合などがある。 今回は様々な外部から得られるデータを活用する方法を学ぶ。
気象庁のウェブサイトでは様々な気象に関するデータを提供している。 今回は2022年5月のデータをExcelに読み込み、気温の変化をグラフにしてみる。
気象庁のウェブサイトから「各種データ・資料」-「過去の気象データ検索」とたどって検索ページを開く。 または http://www.jma.go.jp/jma/menu/menureport.htmlを開く。
「都道府県・地方を選択」をクリックして愛知県名古屋市を選択する。 2022年と5月をクリックしてから「2022年5月の日ごとの値を表示」をクリックする。
指定した地点のデータが表示される。
開いたウェブページの「名古屋 2022年...」ぐらいのところから「値欄の記号の説明」ぐらいまでをドラッグして選択する。 次いでCtrl + Cキーを押してクリップボードにコピーする。 または選択範囲の上で右クリックして「コピー」を実行する。
Excelを起動して新しいワークシートを作成し、適当な場所に貼り付ける(Ctrl + v)。
ウェブサイトからコピーしてくるとリンクが貼られている場合がある。 そのままにしておいてもよいが、青字になっていたり下線が引かれたりしているので書式を整えるのを邪魔することがある。 リンクを削除するには「ハイパーリンクの削除」を行う必要がある。
リンクが設定されているセルを選択し、右クリックで現れるメニューから「ハイパーリンクの削除」を実行する。
セルが様々に結合されていて、グラフの作成を邪魔することがある。 セルが結合を解除するには1.結合されているセルを選択し、2.セルの書式設定(Ctrl + 1)を開き、3.「配置」タブの「セルを結合する」のチェックをはずす。
一日の最高気温と最低気温を折れ線グラフにしてみる。 今回のデータは欠損値がないので折れ線グラフにするのが適当であるが、数字がないときには日付をX軸にした散布図にしたほうがよい。
風向きに関するデータがあるのでこれを解析(データをまとめて何かの意味を見出すこと)してみる。 最大風速を与えた風向の分布を調べることにする。
最大風速の風向を選択し、ピボットテーブルを使って風向ごとの回数を集計する。 今回は新規ワークシートにピボットテーブルを作成する。
行ラベルに風向を設定し、データの個数を表示する。
ピボットテーブルは文字コード順に並べるので一般的な北からの順に並ばない。 このままでは図にしにくいので、16方位のデータを作成する。
様々な実験装置は取得したデータをコンピュータのファイルとして様々に出力する。 それぞれの専用ソフトウェアでないと中をみることのできないファイルも多いが、多くのソフトはテキストファイルとして出力する機能を備えている。 数値データはCSV(comma-separated values)形式出力されることが多い。 これは表の項目を,(カンマ)で区切ったもので、どのようなコンピュータでも中をみることができる。 ここではExcelでCSVファイルを開き、データの解析を行う方法を学ぶ。
ここを右クリックして、リンク先を保存する。 保存先はどこでもよいが、自分でわかるようにしておく。
CSVファイルはダブルクリックするとExcelが起動することが多いが、異なる場合もあるので注意が必要。 Excelのメニューの「ファイル」-「開く」を実行し、先に保存したファイルを開く。
今回の検量線は0.5から100までと幅広くとっており、その間は等比になっている。 このような場合に直線回帰を行うと値の大きなものにふられてしまう。 そこで、等比データを等差データに変換するために対数をとってから計算を行う必要がある。
まず表の見出しをつける。 続いてDNA量、蛍光強度の対数をLOG10関数で計算する。
対数値の範囲を選択し、散布図でグラフを描く。 近似直線を追加し、検量線が適当であることを確認する。
検量線が対数で計算されているので、サンプルの蛍光量も対数に変換する。
TREND関数を使ってDNA量を計算する。
対数を常数に変換する(POWER関数を使うとxyを計算できる)。
コンピュータでデータを処理するにはCSV形式のほうが都合がよいが、人がみるときには読みにくくなることが多い。 人が見やすいようにスペースなどで位置合わせをしたデータも存在する。 ここではこれらを効率よくExcelに読み込む方法を学ぶ。
下記はSNPマーカー名(右側)とその読み取り回数(左側)を集めたデータである。
311074 ch12_47185932 301345 ch08_53071240 107428 ch12_8309601 78089 ch12_6315071 64574 ch03_60795648 61223 ch09_47863015 58415 ch03_52728146 50903 ch12_64414472 49783 ch10_60869013 44880 ch11_24360964 43210 ch04_2306023 39317 ch04_38317083 34478 ch02_40339207 33516 ch04_48955360 32876 ch02_7815242 30657 ch08_27117621 30447 ch07_19100312 28775 ch04_5577461 28237 ch03_51391923 25238 ch04_53699366
まず上記のデータ(20行)を選択し、Ctrl + cキーを押してクリップボードにコピーする。 そのままExcelに貼り付けると左の数値部分と右のマーカー名が同じセルにはいって集計することができない。
一旦メモ帳を起動してそれに貼り付けてから、それをコピーしてExcelに貼り付けると「テキストファイルウィザード」を使用することができる。
今回のデータは位置が空白でそろえてあるデータなので、「スペースによって右または左に揃えられた固定長フィールドのデータ」を選択して、「次へ」をクリックする。
Excelが推測した区切り位置でよければ次へをクリックする。
データ型の変更が必要ならば行う。今回は必要ないのでそのまま「完了」をクリックしてウィザードを終了する。
上記のように見た目が整えられたデータを関数を使って計算や集計に適したデータに変換することもできる。 マーカー名のch12の部分と_(アンダーバー)以降の数字を以下のようにして分割する。
Q2.xlsxをダウンロードし、その中にある問題を解決しワークシートを完成させよ。 課題に使用しているデータは以下からダウンロードしてください。
それぞれの問題を解けたときには評価の欄に1を、部分的に解けた場合は0.5を入力してください。