試算表軟體 II 醫務管理暨醫療資訊學系 陳以德 副教授: 濟世CS 轉

Presentation on theme: "試算表軟體 II 醫務管理暨醫療資訊學系 陳以德 副教授: 濟世CS 轉"— Presentation transcript:

1 試算表軟體 II 醫務管理暨醫療資訊學系 陳以德 副教授: 濟世CS202-3 07-3121101 轉 2648-25

2 學習目標 繪圖 圖表編修 樞紐分析圖表 敍述統計 機率 檢定 迴歸 複利數學

3 繪圖兩大類 圖形 美工圖案 圖形檔案 快取圖案 組織圖 文字藝術師 統計圖表 直線圖 長條圖 橫條圖 折線圖 圓形圖 …

4 各類圖案位置

5 美工圖案 選取位置 > 插入 > 圖片 > 美工圖案

6 組織圖(2003 版,直接在Excel中處理) 選取位置 > 插入 > 圖片 > 組織圖

7 統計圖表

8 原始資料如下

9 選取位置 > 插入 > 圖表

10 下一步(選取繪圖範圍)

11 下一步(有三大控制選項) 第一項

12 有三大控制選項-2 第二項

13 有三大控制選項-3 第三項

14 下一步(最後一個步驟)

15 圓形圖結果如下

16 點兩下Pie Chart全圖出現圖樣

17 修改字型

18 摘要資訊如下

19 或按右鍵出現選項

20 點圖型兩下出現修正格式：圖樣

21 座標軸

22 資料標籤

23 選項

24 點圖例兩下出現修正格式：圖樣

25 字型

26 位置

27 點標題兩下出現修正格式：圖樣

28 字型

29 對齊方式

30 強調某區塊

31 挪出計算

32 練習 請做出下列長條圖

33 點圖型兩下出現修正格式：圖樣

34 座標軸

35 副座標軸

36 更改刻度 出現右側選項後, 再看情況修改

37 更改刻度

38 Y 誤差線

39 資料標籤

40 數列順序

41 選項

42 混合統計表

43 折線與長條圖混合 加入上網人口後, 先做好折線圖 在上網人口的折線圖上按右鍵 選圖表類型 改為長條圖即可

44 折線與長條圖混合(圖示)

45 加上趨勢線

46 趨勢線係數

47 趨勢線結果-1

48 趨勢線結果-2

49 Pivot (樞紐分析表)

50 樞紐分析表 將資料分類, 以利選擇個項進行分析 可依年度, 月份 可依姓名 其他分類方式 完成樞紐分析表後 自動選取相關資料進行分析

51 例子

52 分析張三, 李四, 王五銷售狀況 可依年度, 姓名或月份等來計算 選取位置 > 資料 > 樞紐分析表及圖報表

53 樞紐分析表及圖報表(第一步驟)

54 下一步(選取分析範圍, 第二步驟)

55 下一步 (設定是否放同一工作表,第三步驟) 此步驟還有版面配置及選項可供調整

56 第三步驟之版面配置 (可在此拖曳欄位,或按下一步再拖曳欄位)

57 第三步驟之選項

58 下一步(拖曳欄位)

59

60 新工作表

61 選取年份分析

62 選取月份分析

63 選取姓名分析

64 按右鍵有選項可修改

65 多欄位樞紐分析(原始資料)

66 練習：多欄位樞紐分析(輸出結果)

67 樞紐分析圖

68 選取報表格式

69 Excel 2010樞紐分析表, 圖 插入 ->樞紐分析表

70 選取要分析的範圍, 及輸出位置

71 拖曳到適當位置

72 Statistics Concept

73 和(sum) Sum(value1, value2, …) Sumsq(value1, value2, …)
計算總和 Sum=X1+X2+…+Xn Sumsq(value1, value2, …) 均方和Sumsq=X12+X22+…+Xn2

74 算術平均數(Arithmetic mean)
簡稱平均數 例子: 15, 26, 37, 58, 74 平均數=( )/5=42 Excel 函數: Average(value1, value2,…)

75 中位數(Median) 簡稱中數：取排序後中間的那位數字
例子: 15, 26, 37, 58, 74 (5+1)/2=3, 排序後第三位數: 37 若偶數位, 則中位數取中間兩個的平均 Excel 函數: Median(value1, value2,…)

76 眾數(Mode) 常以mo 表示 指次數出現最多的數值 Excel函數: Mode(value1, value2,…)

77 幾何平均數(Geometric mean)
例子: 15, 26, 37, 58, 74 Excel 函數: Geomean(value1, value2,…)

78 調和平均數(Harmonic mean) 調和平均數 例子: 15, 26, 37, 58, 74
Excel 函數: Harmean(value1, value2,…)

79 平均差(Average Deviatoin)
平均差 AD= 例子: 15, 26, 37, 58, 74 平均數=( )/5=42 平均差= (|15-42|+|26-42|+|37-42|+|58-35|+|74-35|)/5=18.6 Excel 函數：Avedev(value1, value2,…)

80 變異數(Variance) 變異數(Varp)= 變異數(Var)=

81 標準差(Standard Deviation)
標準差(Stdev)= Stdevp= 抽樣樣本，已先抽一個了，自由度減1

82 四分差(Quartile Deviation)
Quartile(Array, quart) quart=1 傳回第一個四分位數 quart=3 傳回第三個四分位數 四分差= Quartile(Array,3)-Quartile(Array,1) 全距 Quartile(Array,4)-Quartile(Array,0)

83 計算總數(Count) Count(value1, value2, …) Counta(value1, value2, …)
計算非空白總數 Countif(b2:b11,”男”) 計算符合條件總數 CountBlank(value1, value2, …) 計算空白總數

84 排名 Rank (b5,b2:b11) Large (b2:b11,5) Small (b2:b11,5) B5在B2到B11中排第幾
求出第5大的數 Small (b2:b11,5) 求出第5小的數

85 去除極端值後均數(Trimmean) Trimmean(b2:b11, .2) 共10個數 10*.2=2 最大值, 最小值各去掉一個
再計算平均數

86 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

87 練習

88 盒鬚圖1 Q1 Max Min Q3 62.35 91.2 51.40 4

89 盒鬚圖2

90 盒鬚圖3

91 盒鬚圖4

92 機率分佈介紹 http:/probstat.nuk.edu.tw/Definition/Test3.asp
進階應用 機率分佈介紹

93 由增益集->安裝分析工具

94 選取分析工具箱

95 Excel 2010 分析工具箱 Office 圖示 -> 右鍵 -> 自訂快速存取工具列
選 增益集 -> 分析工具箱-VBA 按「執行」

96 Excel 2010 分析工具箱 選取 勾選分析工具箱 分析工具箱 – VBA 再按 「是」

97 分析工具箱安裝中…

98 資料分析 -> 敍述統計

99 數值產生 模擬丟一均質銅板15次的結果 Bernoulli Distribution 工具-> 資料分析 -> 亂數產生器

100 丟一均質銅板15次的結果(1)

101 丟一均質銅板15次的結果(2)

102 丟一均質銅板15次的結果(3)

103 丟一均質銅板15次的結果(4)

104 產生0~500間亂數 30組

105 抽樣 試從1000組元素中，抽樣80組

106 產生1000組數據 A1設1, A2設2， 然後拉到1000 常用編輯填滿  數列 對話框選欄, 終止值填1000

107 從1000組元素中，抽樣80組 分析工具箱要安裝，在資料才有資料分析 資料資料分析抽樣 填入相關資料如下

108 從1000組元素中，抽樣80組結果

109 練習 資料分析 -> 繪製直方圖 試產生1000組常態分配的數據 設定每0.5為一組界
註: 標準常態分配99.7%的值介於-3到3之間

110

111 產生常態分配數據-1 工具-> 資料分析 -> 亂數產生器

112 產生常態分配數據-2 工具-> 資料分析 -> 直方圖

113 敍述統計-判斷是否為常態分配(1) 判斷48,52,55,57,58,60,61,62,64, 65,66,68,69,70,72,73,75,78,82是否為常態分配

114 判斷是否為常態分配(2)

115 判斷是否為常態分配(3)

116 判斷是否為常態分配(4) 峰度=Kurt :g2=0常態, >0高峰 偏態=Skew :g1=0常態, >0正偏(落於低分)

117 判斷是否為常態分配(5)

118 判斷是否為常態分配(6)

119 判斷是否為常態分配(7) 平均數=中位數=全距中點=65 四分位距=72-48=14為標準差1.33倍
68.42%資料落在平均數左右各1個標準差 (56.04,73.96)間 100%的資料落在平均數左右各2個標準差 (47.09,82.91)間 服從常態分配

120 NDL每人平均工資偏低檢定 半導體廠每日平均工資 2320, 標準差 25, 若 NDL雇用120平均工資2100, 試問在0.01的顯著 水準下, NDL是否工資偏低? 故拒絕H0, 表示HDL工資偏低

121 z 檢定(兩母體期望值差檢定) μ1-μ2 檢定統計量為

122 z 檢定(兩母體平均差檢定)-1 假定相同, 先求出期中, 期末變異數

123 z 檢定(兩母體平均差檢定)-2 工具 -> 資料分析

124 z 檢定(兩母體平均差檢定)-3 輸入相關數據

125 z 檢定(兩母體平均差檢定)-4

126 z 檢定(兩母體平均差檢定)-5 標準 z 統計量=-0.243 雙尾 z 臨界值: 1.96
且p-value 0.40>0.05 所以接受假定

127 F 檢定(兩常態母體變異數檢定) 假定H0:σ12=σ22, 故檢定統計量為

128 F 檢定(兩常態母體變異數檢定)-1 分析期中期末變異數是否相等(假定相等)

129 F 檢定(兩常態母體變異數檢定)-2 輸入相關數據

130 F 檢定(兩常態母體變異數檢定)-3

131 F 檢定(兩常態母體變異數檢定)-4 標準F統計量=1.853 單尾臨界: 3.44 -3.44<1.853<3.44
且p-value 0.20>0.05 所以接受假定

132 t 檢定 成對母體平均數差異檢定 兩母體平均數差的檢定, 假設變異數相等 兩母體平均數差的檢定, 假設變異數不相等

133 t 檢定(成對母體平均數差異檢定)-1 分析兩大賣場商品價格是否有差異 工具->資料分析

134 t 檢定(成對母體平均數差異檢定)-2 填入相關資料

135 t 檢定(成對母體平均數差異檢定)-3

136 t 檢定(成對母體平均數差異檢定)-4 左尾 t 臨界值< t 統計量 <右尾 t 臨界值 < < 且 p value 0.56 > α=0.05 故接受假定, 兩家賣無顯著差異

137 t 檢定 分析學生期中, 期末分數是否有差異 (兩母體平均數差的檢定, 假設變異數相等) (兩母體平均數差的檢定, 假設變異數不相等)
方法同前, 資料分析選對項目即可

138

139 迴歸分析

140 迴歸分析公式 y=a+bx SSxy SSxx SSyy

141 平方相關係數 R2 平方和 殘差SSE= (xi, yi) (xi, ŷi) 迴歸SSR= 總和SSyy=

142 平方相關係數 R2 SSE/SSyy=> 殘差項占總離度的比例 SSR=SSyy- SSE
R2=SSR/SSyy=1-SSE/Ssyy R2愈靠近1, 代表離趨勢線愈近

143 所得與儲蓄 b=5.1/50=.102, a=.94-.102(7)=.226 Y=a+bx=.226+.102x 家庭 所得(X)
儲蓄(S) A 3 .3 -4 -.64 2.56 16 B 5 1 -2 .06 -.12 4 C 6 .7 -1 -.24 .24 D 9 1.5 2 .56 1.12 E 12 1.2 .26 1.3 25 平均 7 .94 sum=5.1 sum=50 b=5.1/50=.102, a= (7)=.226 Y=a+bx= x

144 簡單迴歸分析-1

145 簡單迴歸分析-2 工具->資料分析, 選迴歸

146 簡單迴歸分析-3 填入相關係數

147 簡單迴歸分析-結果

148 簡單迴歸分析-結果解釋 Y: 銷售量 X: 廣告量 Y=A+BX=359.51+2.98X 係數 標準誤 t 統計 P-值 下限 95%
上限 95% 截距 6.6E-05 廣告量 Y: 銷售量 X: 廣告量 Y=A+BX= X

149 迴歸變異分析-解釋能力 SSR: 迴歸變異 SSE: 殘差 SST=SSR+SSE R2=SSR/SST 介0與1間 當R2愈接近1,
表示此一分析結果 愈能解釋迴歸模型 迴歸統計 R 的倍數 R 平方 調整的 R 平方 標準誤 觀察值個數 10

150 多元回歸分析(原始資料)

151 多元回歸分析-1 工具->資料分析->迴歸

152 多元回歸分析-2 填入相關係數

153 多元回歸分析-結果 Y=A+BX1+CX2 =361.88+3.01X1-0.74X2 R 的倍數 0.65119 R 平方 0.42405
標準誤 觀察值個數 10 係數 標準誤 t 統計 P-值 下限 95% 上限 95% 截距 65.618 20 1.4572 3 12.987

154 年金終值與年金現值 年金終值 年金現值

155 (1) 單筆定存的複利計算 本金 計算期滿時 本金與利率 的總和 固定利率

156 計算利息 月利率=年利率/12個月 當月利息=本金*月利率 次月的本金=上月的本金與利息小計 次月的 本金

157 第二個月的單月利息與存款小計 向下拖曳 填滿第四列

158 填滿十二個月的資料 期滿領回金額

159 (2) 定期定額儲蓄複利計算 每月初固定 存入一萬元, 連續12個月 期滿領回 金額

160 計算利息 月利率=年利率/12個月=1.713%/12 當月利息=本金*月利率 次月本金=10,000+上月本金與利息小計

161 第二個月的當月利息與存款小計 向下拖曳 填滿第四列

162 填滿十二個月的資料 12個月後 可拿回的總 金額

163 (3) 使用FV函數計算未來值 計算投資的未來值 FV(Rate, Nper, Pmt, Pv, Type)
1: 期初存款,期初付款 0: 期末存款,期末付款

164 應用FV計算固定利率定存利潤 選擇公式 存款請用負號 用FV公式 計算定存利潤

165 選擇FV函數

166 填寫參數 每一期利率 (月利率) 期數 每期存入金額 現值 (目前已存金額) 期初存款

167 工具目標搜尋 希望兩年後有100萬存款 已知道定存的利率 每個月該存多少錢才能 達到目標?

168 善加利用FV的工作表 想知道的金額 兩年,請改為 24期 目標金額設為 100萬

169 目標搜索對話方塊

170 算出每月需存入的金額 每月需存入40,928元,兩年後才有100萬的存款

171 範例二:機動利率定存的利潤分析 將10萬定存一年 採用機動利率計算 屆時可領回多少錢?

172 (1)單筆定存機動利率計算 單筆存款金額 以現在的 機動利率 值為參考

173 計算複利 1 本金*(機動利率/12) 2 本金+ 當月利息 3 次月本金=上月存款小計

174 填滿12個月的資料 一年後領回的金額

175 (2)利用函數計算 FVSCHEDULE(Principal, Schedule) Principal: 單筆存款金額總值

176 建立資料 這些都是年利率, 公式計算需使用 月利率

177 先算出月利率 轉換為月利率 運用FVSCHEDULE()

178 選擇FVSCHEDULE()函數 選財務函數

179 工具增益集 若Excel中無法使用 FVSCHEDULE(), 請點選“分析工具”

180 輸入參數 本金 機動月利率的範圍

181 計算出定存利潤 10萬元定存一年後 可領回的金額

182 PMT投資或貸款的每期付款額 PMT(rate,nper,pv,fv,type) Rate 各期的利率 Nper 期數
Ex. 貸100萬, 年利率1.45%, 七年內還清, 每月要還 =PMT(1.45%/12, 7*12, ) =-$12,526.33

183 年金現值 Example Ex. 貸100萬, 年利率10%, 一年要還多少?
=PMT(10%,10, )=162,745.39

184 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千, 每月末應付

185 PMT 貸款與存款 PMT(rate,nper,pv,fv,type) 每月存6102, 10年後有100萬
貸款100萬, 每月要還11102 =PMT(6%/12,10*12, )=11102

186 FV年金終值 FV(rate,nper,pmt,pv,type) Rate 各期的利率 Nper 期數
Ex. 一年存10萬, 年利率10%, 十年後有多少? =FV(10%, 10, , , 0)

187 年金終值 Example Ex. 一年存10萬, 年利率10%, 十年後有多 少? =FV(10%, 10, , , 0)

188 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年 後報酬

189 PV投資的現值 PV(rate,nper,pmt,fv,type) Rate 各期的利率 Nper 期數
Fv   最後一次付款完成後，所能獲得的現金餘額 (年金終值)。如果省略 fv 引數，會自動假定為 0 (例如貸款的年金終值是 0 ) Type   為 0或省略(期末) 或 1 (期初) Ex.=PV(1.45%/12, 12*7, , , 0) =-$999,999.83

190 PV Example =PV(7.5%/12, 2*12, 250, , 0)= 每月底繳250, 年利率6%, 2年後報酬的現值, 也就是 現在可以貸多少 =PV(6%/52, 4*52, 50, , 1)= 每週初繳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%, 現值多少

191 「存款」與「保險」之差異 將50萬存在銀行為期10年，年利率6.25%，計算 每個月可領回的本利和：以每月領回相同的金 額，並在相同的條件下向保險公司購買60萬保 險年金合約，試評估這兩個方案? =PMT(6.25%/12,10*12, )=5614 每期領回金額 =PV(6.25%/12,10*12,5614)=500000 以上述計算所得到之每月領回金額，推算投資保險 的年金現值

192 兩個範例 固定利率的利潤分析 機動利率定存的利潤分析 單筆定存的複利計算 定額定存的複利計算 使用FV()計算未來值
用目標搜尋反推每月應存的金額 機動利率定存的利潤分析 單筆定存的機動利率計算 使用函數FVSCHEDULE()

193 範例一: 固定利率的利潤分析 12個月的定期存款,一開始即存入五萬元, 採用固定利率 欲知道12個月後這五萬元可產生多少利息?

194 Summery 繪圖 圖表編修 樞紐分析圖表 敍述統計 – 平均、標準差等等 機率 – 離散、連續型分配 檢定 – F, Z, T 檢定
迴歸 複利數學 – 貸款、利息