Transcript 文書處理軟體介紹

試算表軟體 II
基礎科學教育中心
陳以德 助理教授: 濟世CS912
07-3121101 轉 2648-25
[email protected]
學習目標
 繪圖
 圖表編修
 樞紐分析圖表
 敍述統計
 機率
 檢定
 迴歸
 複利數學
繪圖兩大類
 圖形
 統計圖表
1. 美工圖案
1. 直線圖
2. 圖形檔案
2. 長條圖
3. 快取圖案
3. 橫條圖
4. 組織圖
4. 折線圖
5. 文字藝術師
5. 圓形圖
6. …
3
各類圖案位置
4
美工圖案
選取位置 > 插入 > 圖片 > 美工圖案
5
組織圖(2003 版,直接在Excel中處
理)

選取位置 > 插入 > 圖片 > 組織圖
6
統計圖表
7
原始資料如下
8
選取位置 > 插入 > 圖表
9
下一步(選取繪圖範圍)
10
下一步(有三大控制選項)
第一項
11
有三大控制選項-2
第二項
12
有三大控制選項-3
第三項
13
下一步(最後一個步驟)
14
圓形圖結果如下
15
點兩下Pie Chart全圖出現圖樣
16
修改字型
17
摘要資訊如下
18
或按右鍵出現選項
19
點圖型兩下出現修正格式：圖樣
20
座標軸
21
資料標籤
22
選項
23
點圖例兩下出現修正格式：圖樣
24
字型
25
位置
26
點標題兩下出現修正格式：圖樣
27
字型
28
對齊方式
29
強調某區塊
學院分佈圖
商學院
電資院
理學院
理學院
文學院
工學院
文學院
工學院
電資院
商學院
30
挪出計算
學院分佈圖
工學院
文學院
其它
理學院
電資院
文學院
商學院
工學院
電資院
理學院
商學院
31
練習
 請做出下列長條圖
32
點圖型兩下出現修正格式：圖樣
33
座標軸
34
副座標軸
35
更改刻度
出現右側選項後, 再看情況修改
36
更改刻度
%
FireFox 與 IE 市佔率
100
80
60
40
20
0
99
90
70
45
10
2004
2005
2006
2007
2008
年度
IE
Firefox
37
500
450
400
350
300
250
200
150
100
50
0
Y 誤差線
38
資料標籤
39
數列順序
40
選項
41
混合統計表
42
折線與長條圖混合
 加入上網人口後, 先做好折線圖
 在上網人口的折線圖上按右鍵
 選圖表類型 改為長條圖即可
43
折線與長條圖混合(圖示)
44
加上趨勢線
45
趨勢線係數
46
趨勢線結果-1
47
趨勢線結果-2
48
Pivot (樞紐分析表)
49
樞紐分析表
 將資料分類, 以利選擇個項進行分析
 可依年度, 月份
 可依姓名
 其他分類方式
 完成樞紐分析表後
 自動選取相關資料進行分析
50
例子
51
分析張三, 李四, 王五銷售狀況
 可依年度, 姓名或月份等來計算
 選取位置 > 資料 > 樞紐分析表及圖報表
52
樞紐分析表及圖報表(第一步驟)
53
下一步(選取分析範圍, 第二步驟)
54
下一步
(設定是否放同一工作表,第三步驟)
此步驟還有版面配置及選項可供調整
55
第三步驟之版面配置
(可在此拖曳欄位,或按下一步再拖曳欄位)
56
第三步驟之選項
57
下一步(拖曳欄位)
58
59
新工作表
60
選取年份分析
61
選取月份分析
62
選取姓名分析
63
按右鍵有選項可修改
64
多欄位樞紐分析(原始資料)
65
練習：多欄位樞紐分析(輸出結果)
66
樞紐分析圖
67
選取報表格式
68
Excel 2010樞紐分析表, 圖
 插入 ->樞紐分析表
69
選取要分析的範圍, 及輸出位置
70
拖曳到適當位置
71
Statistics Concept
72
和(sum)
 Sum(value1, value2, …)
 計算總和 Sum=X1+X2+…+Xn
 Sumsq(value1, value2, …)
 均方和Sumsq=X12+X22+…+Xn2
73
算術平均數(Arithmetic mean)
X 1  X 2  ...  X n
 簡稱平均數 X 
n
 例子: 15, 26, 37, 58, 74
平均數=(15+26+37+58+74)/5=42
 Excel 函數:
Average(value1, value2,…)
74
中位數(Median)
 簡稱中數：取排序後中間的那位數字
 例子: 15, 26, 37, 58, 74
(5+1)/2=3, 排序後第三位數: 37
 若偶數位, 則中位數取中間兩個的平均
 Excel 函數:
Median(value1, value2,…)
75
眾數(Mode)
 常以mo 表示
 指次數出現最多的數值
 Excel函數:
Mode(value1, value2,…)
76
幾何平均數(Geometric mean)

幾何平均數 G  n X 1  X 2  ...  X n

例子: 15, 26, 37, 58, 74
G  5 15  26  37  58  74  36.173

Excel 函數:
Geomean(value1, value2,…)
77
調和平均數(Harmonic mean)

1
調和平均數 H 
1 1 1
1
(   ...  )
n x1 x2
xn

例子: 15, 26, 37, 58, 74
1
H
 30.69177
1 1
1
1
1
1
( 

  )
5 15 26 37 58 74

Excel 函數:
Harmean(value1, value2,…)
78
平均差(Average Deviatoin)
n
 平均差 AD=
| X
i
X |
1
n
 例子: 15, 26, 37, 58, 74
平均數=(15+26+37+58+74)/5=42
平均差=
(|15-42|+|26-42|+|37-42|+|58-35|+|74-35|)/5=18.6
 Excel 函數：Avedev(value1, value2,…)
79
變異數(Variance)
n
 變異數(Varp)=
2
(
X

X
)
 i
1
n
n
 變異數(Var)=
2
(
X

X
)
 i
1
n 1
 假定它的引數串列是整個母群體。如果觀測資料代
表該母群體的抽樣樣本，則應該使用 Var 函數。
80
標準差(Standard Deviation)
n
2
(
X

X
)
 i
 標準差(Stdev)=
1
n 1
n
 Stdevp=
2
(
X

X
)
 i
1
n
 抽樣樣本，已先抽一個了，自由度減1
81
四分差(Quartile Deviation)
 Quartile(Array, quart)
quart=1 傳回第一個四分位數
quart=3 傳回第三個四分位數
 四分差=
Quartile(Array,3)-Quartile(Array,1)
2
 全距
Quartile(Array,4)-Quartile(Array,0)
82
計算總數(Count)
 Count(value1, value2, …)
 計算總數
 Counta(value1, value2, …)
 計算非空白總數
 Countif(b2:b11,”男”)
 計算符合條件總數
 CountBlank(value1, value2, …)
 計算空白總數
83
排名
 Rank (b5,b2:b11)
 B5在B2到B11中排第幾
 Large (b2:b11,5)
 求出第5大的數
 Small (b2:b11,5)
 求出第5小的數
84
去除極端值後均數(Trimmean)
 Trimmean(b2:b11, .2)
共10個數
10*.2=2
最大值, 最小值各去掉一個
 再計算平均數
85
Subtotal
 SubTotal(Function#, b2:b11)
1.Average 7.Stdev
2.Count
8.Stdevp
3.Counta
9.Sum
4.Max
10.Var
5.Min
11.Varp
6.Product
86
練習
87
盒鬚圖1
Q1
Max
62.35
Min
91.2
Q3
51.40
4
88
盒鬚圖2
100
90
80
70
60
50
Ряд1
40
30
20
10
0
1
2
3
100
90
4
80
70
60
Ряд1
50
Ряд2
40
Ряд3
Ряд4
30
20
10
0
1
89
盒鬚圖3
90
盒鬚圖4
91
進階應用
機率分佈介紹
http:/probstat.nuk.edu.tw/Definition/Test3.asp
92
由增益集->安裝分析工具
93
選取分析工具箱
94
Excel 2010 分析工具箱
Office 圖示 -> 右鍵 ->
自訂快速存取工具列
選 增益集 -> 分析工具箱-VBA
按「執行」
95
Excel 2010 分析工具箱 選取
 勾選分析工具箱
分析工具箱 – VBA
再按 「是」
96
分析工具箱安裝中…
97
資料分析 -> 敍述統計
98
數值產生
 模擬丟一均質銅板15次的結果
 Bernoulli Distribution
 工具-> 資料分析 -> 亂數產生器
99
丟一均質銅板15次的結果(1)
100
丟一均質銅板15次的結果(2)
101
丟一均質銅板15次的結果(3)
102
丟一均質銅板15次的結果(4)
103
產生0~500間亂數 30組
抽樣
 試從1000組元素中，抽樣80組
產生1000組數據
 A1設1, A2設2， 然後拉到1000
 常用編輯填滿  數列
對話框選欄, 終止值填1000
從1000組元素中，抽樣80組
 分析工具箱要安裝，在資料才有資料分析
 資料資料分析抽樣
填入相關資料如下
從1000組元素中，抽樣80組結果
練習
 試產生1000組常態分配的數據
 設定每0.5為一組界
 資料分析 -> 繪製直方圖
 註: 標準常態分配99.7%的值介於-3到3之間
109
110
產生常態分配數據-1
 工具-> 資料分析 -> 亂數產生器
111
產生常態分配數據-2
 工具-> 資料分析 -> 直方圖
112
敍述統計-判斷是否為常態分配(1)
 判斷48,52,55,57,58,60,61,62,64,
65,66,68,69,70,72,73,75,78,82是否為常態分配
113
判斷是否為常態分配(2)
114
判斷是否為常態分配(3)
115
判斷是否為常態分配(4)
峰度=Kurt :g2=0常態, >0高峰
4
2


n(n  1)
3(n  1)

 xi  x  
g2  

 

(
n

1
)(
n

2
)(
n

3
)
std
(
n

2
)(
n

3
)




i


偏態=Skew :g1=0常態, >0正偏(落於低分)
n
 xi  x 
g1 



(n  1)( n  2) i  std 
3
116
判斷是否為常態分配(5)
117
判斷是否為常態分配(6)
118
判斷是否為常態分配(7)
 平均數=中位數=全距中點=65
 四分位距=72-48=14為標準差1.33倍
 68.42%資料落在平均數左右各1個標準差
(56.04,73.96)間
 100%的資料落在平均數左右各2個標準差
(47.09,82.91)間
 服從常態分配
119
NDL每人平均工資偏低檢定
 半導體廠每日平均工資 2320, 標準差 25, 若
NDL雇用120平均工資2100, 試問在0.01的顯著
水準下, NDL是否工資偏低?
H 0 :   2320, H1 :   2320
250
X *  2320  2.32
 2267  2220
120
 故拒絕H0, 表示HDL工資偏低
120
z 檢定(兩母體期望值差檢定)
 μ1-μ2 檢定統計量為
z
( X 1  X 2 )  ( 1   2 )
1
2
n1

2
2
n2
121
z 檢定(兩母體平均差檢定)-1
 假定相同, 先求出期中, 期末變異數
122
z 檢定(兩母體平均差檢定)-2
 工具 -> 資料分析
123
z 檢定(兩母體平均差檢定)-3
 輸入相關數據
124
z 檢定(兩母體平均差檢定)-4
125
z 檢定(兩母體平均差檢定)-5
 標準 z 統計量=-0.243
 雙尾 z 臨界值: 1.96
 左尾z臨界值<標準z統計量<右尾z臨界值
-1.96<-0.243<1.96
 且p-value 0.40>0.05
 所以接受假定
126
F 檢定(兩常態母體變異數檢定)
2
 xi  1 

 / n1

2
1 
1 / v1
i 1 
F 2

2
n
2
 2 / v2
 x j  2 

 / n2

2 
j 1 
n1
假定H0:σ12=σ22, 故檢定統計量為
2
n1
F
 x   
i 1
 x
i
2
n2
j 1
/ n1
1
j
  2  / n2
2
s1
 2
s2
127
F 檢定(兩常態母體變異數檢定)-1
 分析期中期末變異數是否相等(假定相等)
128
F 檢定(兩常態母體變異數檢定)-2
 輸入相關數據
129
F 檢定(兩常態母體變異數檢定)-3
130
F 檢定(兩常態母體變異數檢定)-4
 標準F統計量=1.853
 單尾臨界: 3.44
 -3.44<1.853<3.44
 且p-value 0.20>0.05
 所以接受假定
131
t 檢定
 成對母體平均數差異檢定
 兩母體平均數差的檢定, 假設變異數相等
 兩母體平均數差的檢定, 假設變異數不相等
132
t 檢定(成對母體平均數差異檢定)-1
 分析兩大賣場商品價格是否有差異
工具->資料分析
133
t 檢定(成對母體平均數差異檢定)-2
 填入相關資料
134
t 檢定(成對母體平均數差異檢定)-3
135
t 檢定(成對母體平均數差異檢定)-4
 左尾 t 臨界值< t 統計量 <右尾 t 臨界值
-2.31 < 0.599 < 2.31
且 p value 0.56 > α=0.05
故接受假定, 兩家賣無顯著差異
136
t 檢定
 分析學生期中, 期末分數是否有差異
 (兩母體平均數差的檢定, 假設變異數相等)
 (兩母體平均數差的檢定, 假設變異數不相等)
 方法同前, 資料分析選對項目即可
137
138
迴歸分析
139
迴歸分析公式
y=a+bx
SSxy
n
b
 ( x  x )( y  y )
i
i 1
n
i
2
(
x

x
)
 i
a  y  bx
i 1
SSxx
n
SSyy
2
(
y

y
)
 i
(x, y)
i 1
140
平方相關係數 R2
平方和
n
2
ˆ
殘差SSE=  ( yi  yi )
i 1
n
2
ˆ
(
y

y
)
迴歸SSR=  i
(xi, yi)
i 1
(xi, ŷi)
n
( xi , y )
總和SSyy=  ( yi  y )
2
i 1
141
平方相關係數 R2
 SSE/SSyy=> 殘差項占總離度的比例
 SSR=SSyy- SSE
 R2=SSR/SSyy=1-SSE/Ssyy
 R2愈靠近1, 代表離趨勢線愈近
142
所得與儲蓄
家庭 所得
(X)
A
3
B
5
C
6
D
9
E
12
平均 7


儲蓄
(S)
.3
1
.7
1.5
1.2
.94
X  X
-4
-2
-1
2
5
S  S ( X  X )( S  S ) ( X  X ) 2
-.64
.06
-.24
.56
.26
2.56
-.12
.24
1.12
1.3
sum=5.1
b=5.1/50=.102, a=.94-.102(7)=.226
Y=a+bx=.226+.102x
143
16
4
1
4
25
sum=50
簡單迴歸分析-1
144
簡單迴歸分析-2
工具->資料分析, 選迴歸
145
簡單迴歸分析-3
填入相關係數
146
簡單迴歸分析-結果
147
簡單迴歸分析-結果解釋
係數
標準誤
t 統計
截距
359.5086
47.60874
7.551316
廣告量
2.978204
1.227821
2.425602
下限
95%
上限
95%
下限
95%
上限
95%
6.6E-05
249.7227
469.2946
249.7227
469.2946
0.041486
0.146845
5.809564
0.146845
5.809564
P-值
Y: 銷售量
X: 廣告量
Y=A+BX=359.51+2.98X
148
迴歸變異分析-解釋能力
迴歸統計
R 的倍數
0.650983
R 平方
0.423778
調整的 R 平方
0.351751
標準誤
43.77142
觀察值個數
10
SSR: 迴歸變異
SSE: 殘差
SST=SSR+SSE
R2=SSR/SST 介0與1間
當R2愈接近1,
表示此一分析結果
愈能解釋迴歸模型
149
多元回歸分析(原始資料)
150
多元回歸分析-1
 工具->資料分析->迴歸
151
多元回歸分析-2
 填入相關係數
152
多元回歸分析-結果
R 的倍數
0.65119
R 平方
0.42405
調整的 R 平方
0.25949
標準誤
46.7827
觀察值個數
Y=A+BX1+CX2
=361.88+3.01X1-0.74X2
10
標準誤
t 統計
P-值
下限
95%
上限
95%
下限
95%
上限
95%
361.881
65.618
5.514952
0.000892
206.7186
517.043
206.7186
517.043
20
3.01448
1.4572
2.068632
0.077373
-0.43133
6.460292
-0.43133
6.460292
3
-0.74361
12.987
-0.05726
0.955941
-31.4541
29.9669
-31.4541
29.9669
係數
截距
153
年金終值與年金現值
 年金終值
 年金現值
(1  i )  1
i
n
1
1
n
(1  i )
i
154
(1) 單筆定存的複利計算
本金
計算期滿時
本金與利率
的總和
固定利率
155
計算利息
 月利率=年利率/12個月
 當月利息=本金*月利率
 次月的本金=上月的本金與利息小計
次月的
本金
156
第二個月的單月利息與存款小計
向下拖曳
填滿第四列
157
填滿十二個月的資料
期滿領回金額
158
(2) 定期定額儲蓄複利計算
每月初固定
存入一萬元,
連續12個月
期滿領回
金額
159
計算利息
 月利率=年利率/12個月=1.713%/12
 當月利息=本金*月利率
 次月本金=10,000+上月本金與利息小計
160
第二個月的當月利息與存款小計
向下拖曳
填滿第四列
161
填滿十二個月的資料
12個月後
可拿回的總
金額
162
(3) 使用FV函數計算未來值
 計算投資的未來值
 FV(Rate, Nper, Pmt, Pv, Type)
 Rate: 每一期存款利率 (月利率)
 Nper: 期數
 Pmt: 分期存款的金額
 Pv: 現值,現在已存的金額 (預設為0)
 Type:給付款項的時間是期初或期末
 1: 期初存款,期初付款
 0: 期末存款,期末付款
163
應用FV計算固定利率定存利潤
選擇公式
存款請用負號
用FV公式
計算定存利潤
164
選擇FV函數
165
填寫參數
每一期利率
(月利率)
期數
每期存入金額
現值
(目前已存金額
期初存款
166
工具目標搜尋
 希望兩年後有100萬存款
 已知道定存的利率
 每個月該存多少錢才能
達到目標?
167
善加利用FV的工作表
想知道的金額
兩年,請改為
24期
目標金額設為
100萬
168
目標搜索對話方塊
169
算出每月需存入的金額
每月需存入40,928元,兩年後才有100萬的存款
170
範例二:機動利率定存的利潤分析
 將10萬定存一年
 採用機動利率計算
 屆時可領回多少錢?
171
(1)單筆定存機動利率計算
單筆存款金額
以現在的
機動利率
值為參考
172
計算複利
1
本金*(機動利率/12)
2
本金+
當月利息
3
次月本金=上月存款小計
173
填滿12個月的資料
一年後領回的金
174
(2)利用函數計算
 FVSCHEDULE(Principal, Schedule)
 Principal: 單筆存款金額總值
 Schedule:特定的月利率範圍
175
建立資料
這些都是年利率,
公式計算需使用
月利率
176
先算出月利率
轉換為月利率
運用FVSCHEDULE()
177
選擇FVSCHEDULE()函數
選財務函數
178
工具增益集
若Excel中無法使用
FVSCHEDULE(),
請點選“分析工具”
179
輸入參數
本金
機動月利率的範圍
180
計算出定存利潤
10萬元定存一年後
可領回的金額
181
PMT投資或貸款的每期付款額
 PMT(rate,nper,pv,fv,type)
 Rate 各期的利率
 Nper 期數
 Pv 未來各期年金現值的總和
 Fv 年金終值。如省略，會自動假定為 0
 Type 為 0或省略(期末) 或 1 (期初)
 Ex. 貸100萬, 年利率1.45%, 七年內還清, 每月要還
=PMT(1.45%/12, 7*12, 1000000)
=-$12,526.33
182
年金現值 Example
 Ex. 貸100萬, 年利率10%, 一年要還多少?
=PMT(10%,10,-1000000)=162,745.39
1
1
10
(1  0.1)
100 
 16.2745
0.1
183
PMT Example
 =Pmt(7.5%/12, 2*12, 5000, 0, 1)
 貸5千, 年利率7.5%, 2年還清, 每月初應付
 =Pmt(6%/52, 4*52, 8000, 0, 0)
 貸8千, 年利率6%, 4年還清, 每週末應付
 =Pmt(5%, 10*1, 6000, 0, 0)
 貸6千, 年利率5%, 10年還清, 每年末應付
 =Pmt(8%/12, 3*12, 5000, 1000, 0)
 貸5千, 年利率8%, 3年只還4千, 每月末應付
184
PMT 貸款與存款
 PMT(rate,nper,pv,fv,type)
 每月存6102, 10年後有100萬
 =PMT(6%/12,10*12,0,1000000)=6102
 =Fv(6%/12,10*12,6102)=999,991.77
 貸款100萬, 每月要還11102
 =PMT(6%/12,10*12,1000000)=11102
185
FV年金終值
 FV(rate,nper,pmt,pv,type)
 Rate 各期的利率
 Nper 期數
 Pmt 各期所應給付或所能取得的固定金額
 Pv 現值或一系列未來付款的目前總額
 Type 為 0或省略(期末) 或 1 (期初)
 Ex. 一年存10萬, 年利率10%, 十年後有多少?
=FV(10%, 10, 100000, , 0)
186
年金終值 Example
 Ex. 一年存10萬, 年利率10%, 十年後有多
少?
 =FV(10%, 10, 100000, , 0)
(1  0.1)  1
100,000  1,593,742.46
0.1
10
187
Fv Example
 =FV(7.5%/12, 2*12, -250, -5000, 1)
 一開始存5000, 之後每月初存250, 年利率7.5%, 兩
年後報酬
 =FV(6%/52, 4*52, -50, -8000, 0)
 一開始存8000, 之後每週末存50, 年利率 6%, 4年
後報酬
188
PV投資的現值
 PV(rate,nper,pmt,fv,type)
 Rate 各期的利率
 Nper 期數
 Pmt 各期所應給付或所能取得的固定金額
 Fv 最後一次付款完成後，所能獲得的現金餘額
(年金終值)。如果省略 fv 引數，會自動假定為 0
(例如貸款的年金終值是 0 )
 Type 為 0或省略(期末) 或 1 (期初)
 Ex.=PV(1.45%/12, 12*7, 12526.33, , 0)
=-$999,999.83
189
PV Example
 =PV(7.5%/12, 2*12, 250, , 0)=5555.61
 每月底繳250, 年利率6%, 2年後報酬的現值, 也就是
現在可以貸多少
 =PV(6%/52, 4*52, 50, , 1)=9252.07
 每週初繳50, 年利率6%, 4年後報酬的現值
 =PV(5.25%, 10*1, 100, , 0)=762.88
 每年底繳100, 年利率5.25%, 10年後報酬的現值
 = PV(8%, 5, 0, -750, 1)=510.44
 五年後750元, 年利率8%, 現值多少
190
「存款」與「保險」之差異
 將50萬存在銀行為期10年，年利率6.25%，計算
每個月可領回的本利和：以每月領回相同的金
額，並在相同的條件下向保險公司購買60萬保
險年金合約，試評估這兩個方案?
 =PMT(6.25%/12,10*12,-500000)=5614
 每期領回金額
 =PV(6.25%/12,10*12,5614)=500000
 以上述計算所得到之每月領回金額，推算投資保險
的年金現值
191
兩個範例
 固定利率的利潤分析
 單筆定存的複利計算
 定額定存的複利計算
 使用FV()計算未來值
 用目標搜尋反推每月應存的金額
 機動利率定存的利潤分析
 單筆定存的機動利率計算
 使用函數FVSCHEDULE()
192
範例一: 固定利率的利潤分析
 12個月的定期存款,一開始即存入五萬元,
 採用固定利率
 欲知道12個月後這五萬元可產生多少利息?
193
Summery
 繪圖
 圖表編修
 樞紐分析圖表
 敍述統計 – 平均、標準差等等
 機率 – 離散、連續型分配
 檢定 – F, Z, T 檢定
 迴歸
 複利數學 – 貸款、利息