エクセル分析マニュアル

エクセルを使った統計量の計算、グラフの作り方
-1-
平均値等の求め方
アルファベット順に
並んでいるので、
下にスクロール
どこか適当な
セルを選択して、
「挿入―関数…」を選択
求めたいもの
平均値を
求めたい
データ
関数
平均値
AVERAGE
標準偏差
STDEV
最高値
MAX
最低値
MIN
中央値
MEDIAN
第一四分位点 PERCENTILE
第三四分位点 PERCENTILE
Vicype co. ltd • Proprietary and Confidential
エクセル分析マニュアル
-2-
ヒストグラムの作り方 １
１．ヒストグラムの元になるデータを入力する
（例の場合なら$A$3から$A$34まで）
２．エクセルシートのどこかにヒストグラムのデータの区切り方を入力し
ておく
（例えば、１０日ごとにデータを区切りたい場合は$C$3から
$C$14のように）
３．ヒストグラムを作る
－メニューバーで [ツール] – [分析ツール…] を選択する
－「ヒストグラム」を選択して、「ＯＫ」をクリックする
－「入力範囲」の右の箱にカーソルを置いてから、エクセルシートで
ヒストグラムにしたいデータの範囲を選択する
（例の場合なら$A$4から$A$34を選択すると、自動的に箱の中に
$A$4:$A$34と入力される）
－同じやり方で、「データ区間」に、データの区切り方を入力した範囲
を選択する
（例の場合なら$C$4から$C$14を選択する）
－「出力先」に、ヒストグラムのデータを作りたい場所の左上のセル
を選択する
（例の場合なら$E$3を選択する）
－「グラフ作成」にチェックを入れる
－「ＯＫ」をクリックする
Vicype co. ltd • Proprietary and Confidential
エクセル分析マニュアル
-3-
ヒストグラムの作り方 ２
Vicype co. ltd • Proprietary and Confidential
エクセル分析マニュアル
-4-
パレート図の作り方 １
１．パレート図の元になるデータを入力する
（例の場合なら$A$3から$B$10まで）
２．データを大きい順に並べ直す
－先ほど作ったデータの中のどこかのセルを選択する
－メニューバーで [データ] – [並べ替え…] を選択する
－「最優先されるキー」で数値データの入っている列のタイトルを
選択する （例の場合なら”データ数”）
－「降順」をチェックする
－「ＯＫ」をクリックする
３．合計を計算する
－B11を選択する
－「SUM(B4:B10)」と
入力する
（または∑ボタンを
クリックする）
Vicype co. ltd • Proprietary and Confidential
エクセル分析マニュアル
-5-
パレート図の作り方 ２
４．累積データを計算する
－C4に「=B4」と入力する
－C5に「=C4+B5」と入力する
－C5からC10を選択し、メニューバーで [編集] – [フィル] – [下方
向へコピー] を選択する
（C10の値がB11と同じことを確認）
５．累積％を計算する
－D4に「=C4/$B$11」と入力する
－D4からD10を選択し、メニューバーで [編集] – [フィル] – [下方
向へコピー] を選択する
（C10の値が１になっていることを確認）
Vicype co. ltd • Proprietary and Confidential
エクセル分析マニュアル
-6-
パレート図の作り方 ３
６．グラフを作る
－[ Ctrl ]キーを押しながら、A3からB10までと、D3からD10までを
選択
－メニューバーで [挿入 ] – [グラフ．．．] を選択する
－「ユーザー設定」のタブで「２軸上の折れ線と縦棒」を選択、「次
へ＞」をクリックする
－「次へ＞」をクリックする
－タイトル等を適当に入力して「次へ＞」をクリック
－ 「新しいシート」か「オブジェクト」を選んで「完了」をクリック
Vicype co. ltd • Proprietary and Confidential
エクセル分析マニュアル
-7-
パレート図の作り方 ４
1
0.9
0.8
0.7
0.6
0.5
0.4
0.3
0.2
0.1
0
80
70
60
50
40
30
20
10
他
の
そ
所
ま
ち
－右側の縦軸をダブルクリックする
－「目盛」のタブで、最小値と最大値の左側のチェックをはずす
－最大値に、「１」を入力する
－「OK」をクリックする
必
要
書
データ数
累積％
住
添
付
が
モ
い
レ
い
類
日
求
請
名
前
ふ
請
り
が
な
ま
ち
が
モ
レ
求
日
モ
瞭
不
明
鑑
印
レ
0
７．軸を調整する
－左側の縦軸をダブルクリックする
－「目盛」のタブで、最小値と最大値の左側のチェックをはずす
－最大値に、データ数の合計（B11の値）を入力する
－「OK」をクリックする
Vicype co. ltd • Proprietary and Confidential
エクセル分析マニュアル
-8-
ｔ検定
アルファベット順に
並んでいるので、
ｔまでスクロール
どこか適当な
セルを選択して、
「挿入―関数…」を選択
Aグループ
のデータ
TTEST
を選択
Bグループ
のデータ
Vicype co. ltd • Proprietary and Confidential
エクセル分析マニュアル
-9-
等分散検定
アルファベット順に
並んでいるので、
fまでスクロール
どこか適当な
セルを選択して、
「挿入―関数…」を選択
Aグループ
のデータ
FTEST
を選択
Bグループ
のデータ
Vicype co. ltd • Proprietary and Confidential
エクセル分析マニュアル
- 10 -
カイ二乗検定 １
どこか適当なセルに
元データを入力
縦、横を合計
全体２００人中で
の合格・不合格の
割合を計算
「講義」「eラーニング」
「講義」「
各グループの人数に
合格・不合格の全体の
割合をかけて
期待値を計算
Vicype co. ltd • Proprietary and Confidential
エクセル分析マニュアル
- 11 -
カイ二乗検定 ２
アルファベット順に
並んでいるので、
ｃまでスクロール
どこか適当な
セルを選択して、
「挿入―関数…」を選択
元データ
CHITEST
を選択
計算した
期待値
Vicype co. ltd • Proprietary and Confidential
エクセル分析マニュアル
- 12 -
相関分析、回帰分析 １
元データを選択して、
「挿入―グラフ…」
を選択
Vicype co. ltd • Proprietary and Confidential
エクセル分析マニュアル
- 13 -
相関分析、回帰分析 ２
グラフ上の点を１点、
右クリックして
「近似曲線の追加
…
…」を選択
Vicype co. ltd • Proprietary and Confidential
エクセル分析マニュアル
- 14 -
Excelで「Box Plotに近いグラフ」を作る（1/6）
元データ（シート左）から
・中央値
・第1四分位点
・最小値
・最大値
・第3四分位点
を計算し、表を作成する
※計算方法は次ページ
Vicype co. ltd • Proprietary and Confidential
エクセル分析マニュアル
- 15 -
Excelで「Box Plotに近いグラフ」を作る（2/6）
「Quartile」関数で
・中央値
・第1四分位点
・最小値
・最大値
・第3四分位点
をすべて計算できる！！
戻り値に以下を入れればOK！
・2：中央値
・1：第1四分位点
・0：最小値
・4：最大値
・3：第3四分位点
Vicype co. ltd • Proprietary and Confidential
エクセル分析マニュアル
- 16 -
Excelで「Box Plotに近いグラフ」を作る（3/6）
作成した表を選択して
挿入→グラフ
Vicype co. ltd • Proprietary and Confidential
エクセル分析マニュアル
- 17 -
Excelで「Box Plotに近いグラフ」を作る（4/6）
Vicype co. ltd • Proprietary and Confidential
エクセル分析マニュアル
- 18 -
Excelで「Box Plotに近いグラフ」を作る（5/6）
紫の棒グラフにカーソルを合わ
せて
右クリック→グラフの種類
Vicype co. ltd • Proprietary and Confidential
エクセル分析マニュアル
- 19 -
Excelで「Box Plotに近いグラフ」を作る（6/6）
12.00
10.00
8.00
6.00
4.00
2.00
0.00
A
B
C
D
＜注意点＞
・折れ線グラフが中央値
・異常値の*（アスタリスク）がない
Vicype co. ltd • Proprietary and Confidential
エクセル分析マニュアル
- 20 -
© 2012 株式会社ヴィサイプ
本資料は弊社スタッフによるプレゼンテーション等を伴う説明を行うことを意図して作成されており
ます。
本資料の一部または全部を、①弊社の書面による許可なくして社外に再配布すること、②社内へ
の説明目的以外の目的で複写、写真複写、あるいはその他いかなる手段において複製すること
を禁じます。
- 21 -