第9回 - 2004/12/6

統計処理をExcelで行う

実習用のデータの入ったファイルを取り込む（ダウンロード）

実習で使うデータを、以下の手順でダウンロードして、Excelで開いてください。以下の手順は、Internet Explorerを使った場合です。

1. まず、ここを右ボタンでクリックする
2. メニューから「対象をファイルに保存」を選択する
3. 保存する場所を指定するダイアログボックスが表示される
4. たとえば、「デスクトップ」を指定して、「保存」ボタンをクリックする
5. 保存後、デスクトップに「売上データ」というファイルが取り込まれている
6. これをダブルクリックすると、Excelで開くことができる

• ファイルの保存場所は、保存さえできればどこでもかまわない
• ファイルは、圧縮せずにそのままアップロードされている

Excelのデータ処理機能

Excelは表形式の文書を効率的に作成することができますが、そのことを利用してさまざまなデータ集計に利用されています。データ集計で使えるいろいろな機能を紹介します。統計に関連することも出てきますが、統計学の知識がないとわかりづらい機能もあります。

Excelにあるデータ集計の機能はいくつかのパターンに分かれますが、それらをおおむね分類すると次のようになります。

• 数式を利用したデータ処理（今日、説明をします）
• 集計機能を利用したデータ処理（3回目の講義で説明しました）
• アドインの機能を利用したデータ処理（何度かに分けて説明をします）

数式を作ることも、基本的ではありますが、集計であることに変わりはありません。比較的、小規模なデータなら、数式だけで処理されることはよくあります。

次の「集計機能」は、リスト形式にデータをワークシートに入力したとき、それらを元データとしてさまざまな集計処理ができるような機能を指します。「データベース機能」などと呼ばれることもあります。

さらに、アドイン（追加ソフト）として、統計処理やソルバーという線形計画法に基づくデータ処理を行うなど、多彩な機能が利用できます。ただし、このジャンルの機能は、Excelの使い方というよりも、統計や線形計画そのものを理解した上でないとつかえないかもしれません。

データ集計でよく利用される関数

以下、代表的な関数の、一般的な使い方を説明します。実習用データブックの「集計に使う関数」というシートを開いて、実際に関数を入力しながら、関数の使い方を説明します。→デモ

• 引数にはセル範囲を指定する。カンマで区切って複数の範囲を指定することも可能
• 空欄のセルはデータとして考慮しない
• 偏差値を求める関数はない。次の式で求めることができる
  偏差値＝((得点-平均点)/標準偏差)×10+50

作成結果の画面ショット

分析ツールの「基本統計量」

これを知ってしまうと、まじめに式を入力する気が薄れてしまうけど、教えてしまおう…

• 平均：これはおなじみのもの
• 標準誤差：標準偏差をデータ数の平方根で割ったもの
• 中央値 （メジアン）：データを順番に並べたときの中央値
• 最頻値 （モード）：ヒストグラム化したときのいちばん頻度の高いもの
• 標準偏差：おなじみですが、分析ツールは、標本標準偏差を求めていることに注意
• 分散：これもおなじみ
• 尖度：分布のとがり具合で、データの4乗値との関連性がある
• 歪度：分布のゆがみ具合で、データの3乗値との関連性がある
• 範囲：最大-最小
• 最小
• 最大
• 合計
• 標本数
• 信頼区間(95.0%)：この値はちょっと難しい。信頼区間とは、平均値をある幅で考えたとき、その幅に入る確率を95%などとなるようにするための幅を求めるもの。一般には、標準誤差*1.96（95%の場合）なのだけども、計算が合わない。ここで求められているのは、スチューデントのt分布に従った値に応じた信頼区間である。その意味では、信頼できる区間であると「信頼」していいのであるが、理屈をきちんと知ろう。（ホーエルの初等統計学のp147参照）

本日の演習

練習問題9-1

apinfo6_trial.xlsの成績表のデータについて、合計得点のヒストグラムの表とグラフを作成せよ。10点ごとに区分けすること。

練習問題9-2

日立市の気象と天気予報というページがある。気象観測データというリンクの先に今月の月別データというところがある。適当な月の情報を表示すると、そこには、毎日の平均気温や湿度などが一覧表になっている。この表をExcelのワークシートとして使えるようにしなさい。ただし、月平均の行は削除すること。

練習問題9-3

6-2で作成したワークシートで次の数値を求めましょう。

• その月の平均気温
• その月の最高気温。また、それは何日か
• その月の最低気温。また、それは何日か
• その月の総降水量
• その月の総日照時間

求めた結果は、ページに答えが掲載されているので、それと突き合わせて確認をしておくこと。

練習問題9-4

9-2で作成したワークシートにある、日ごとの平均湿度を折れ線グラフで描くこと。

解答

9-1

ヒストグラムを、分析ツールで作成するとします。たとえば、次のように、I列にヒストグラムの得点範囲を入力します。

分析ツールで次のように指定します（「出力先」は$K$1だけでいいです）

ヒストグラムがかかれます。大きさや位置などは適当に調節しました。「データ区間」の数値は、その数以下であることに注意しましょう。データ区間が200とは、190〜200ということです。

9-2/9-3

単にブラウザのウインドウにある表を、Excelのワークシートに貼り付けて、後は手作業でいらんものを削除すればいいでしょう。なお、ボタンがはいってしまったときは、右ボタンでクリックして「切り取り」を選ぶといいでしょう。

ある月の平均気温は正しいかどうかは難しいがB列の「日平均」の平均値をAverage関数で求めます。その月の最高気温はC列の最高気温のいちばん大きな値をMax関数で求めます。最低気温は同様に、D列の最小値をMin関数で求めます。

総降水量は日ごとの降水量の合計なのでL列のSumを求めます。総日照時間は同様にN列のSumです。

計算は単純ですが、日本語で問われている意味と、実際に設定しないといけない数式の関連付けを正しく理解してください。

9-4

A列とB列のデータでグラフを描くので、ワークシートはとりあえずそのままでいいのですが、この場合A5:A6のセルが結合されており、６行目以降のAとB列を選択するということができません。その場合、A5:A6のセルを選択して、「書式」メニューの「セル」を選び、さらに「配置」のタブを選択して、「セルを結合する」のチェックをはずしてセル結合をやめます。A6に「日」と文字を入れておきました。

そして、A6:B36を選択して、グラフを描きます。

グラフで難しいのは、系列の設定でしょう。最初は、A列、B列、それぞれ系列であると思ってグラフを作成しているので、思ったグラフにはなりません。まず、系列一覧のところでリストにある「日」という系列（A列）を削除します。リストで選択して「削除」ボタンをクリックすればいいでしょう。

「日平均」つまりB列の系列だけが残ります。さらに、「項目軸ラベルに使用」という設定箇所で、A列のデータ、つまり範囲としては、A7:A36を指定します。

これで図を描けばいいはずです。