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

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

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

各類圖案位置

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

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

統計圖表

原始資料如下

選取位置 > 插入 > 圖表

下一步(選取繪圖範圍)

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

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

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

下一步(最後一個步驟)

圓形圖結果如下

點兩下Pie Chart全圖出現圖樣

修改字型

摘要資訊如下

或按右鍵出現選項

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

座標軸

資料標籤

選項

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

字型

位置

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

字型

對齊方式

強調某區塊

挪出計算

練習 請做出下列長條圖

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

座標軸

副座標軸

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

更改刻度

Y 誤差線

資料標籤

數列順序

選項

混合統計表

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

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

加上趨勢線

趨勢線係數

趨勢線結果-1

趨勢線結果-2

Pivot (樞紐分析表)

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

例子

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

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

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

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

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

第三步驟之選項

下一步(拖曳欄位)

新工作表

選取年份分析

選取月份分析

選取姓名分析

按右鍵有選項可修改

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

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

樞紐分析圖

選取報表格式

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

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

拖曳到適當位置

Statistics Concept

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

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

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

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

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

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

平均差(Average Deviatoin) 平均差 AD= 例子: 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,…)

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

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

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

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

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

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

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

練習

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

盒鬚圖2

盒鬚圖3

盒鬚圖4

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

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

選取分析工具箱

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

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

分析工具箱安裝中…

資料分析 -> 敍述統計

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

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

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

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

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

產生0~500間亂數 30組

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

迴歸分析

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

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

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

所得與儲蓄 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=.94-.102(7)=.226 Y=a+bx=.226+.102x

簡單迴歸分析-1

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

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

簡單迴歸分析-結果

簡單迴歸分析-結果解釋 Y: 銷售量 X: 廣告量 Y=A+BX=359.51+2.98X 係數 標準誤 t 統計 P-值 下限 95% 上限 95% 截距 359.5086 47.60874 7.551316 6.6E-05 249.7227 469.2946 廣告量 2.978204 1.227821 2.425602 0.041486 0.146845 5.809564 Y: 銷售量 X: 廣告量 Y=A+BX=359.51+2.98X

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

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

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

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

多元回歸分析-結果 Y=A+BX1+CX2 =361.88+3.01X1-0.74X2 R 的倍數 0.65119 R 平方 0.42405 0.25949 標準誤 46.7827 觀察值個數 10 係數 標準誤 t 統計 P-值 下限 95% 上限 95% 截距 361.881 65.618 5.514952 0.000892 206.7186 517.043 20 3.01448 1.4572 2.068632 0.077373 -0.43133 6.460292 3 -0.74361 12.987 -0.05726 0.955941 -31.4541 29.9669

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

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

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

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

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

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

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

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

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

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

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

選擇FV函數

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

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

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

目標搜索對話方塊

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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%, 現值多少

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

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

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

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