Microsoft Excel 2003 函數應用 Microsoft MVP 王作桓.

Presentation on theme: "Microsoft Excel 2003 函數應用 Microsoft MVP 王作桓."— Presentation transcript:

1 Microsoft Excel 2003 函數應用 Microsoft MVP 王作桓

2 講師簡歷 Microsoft MVP(最有價值專家)、MCP 台灣微軟「Office2003實戰技巧」內部訓練資深講師
台灣微軟「資訊學園」、台灣微軟資訊展資深講師 台灣微軟大型客戶Microsoft Office 2003 T3 Training資深講師 宜蘭縣政府各機關學校Microsoft Office2003種子培訓指定講師 台灣金融研訓院Microsoft Office2003指定講座 台北縣政府各機關、法務部筆硯橫式公文系統特約講師 鴻海、南亞、華碩、友訊、中華顧問、經濟部…等各大企業特約講師 台北市公務人員訓練中心、中國生產力中心、交通部郵政訓練所講師 台北市各大電腦教育訓練中心特約講師 台北e大Microsoft Office系列課程特約講師 RunPC雜誌Microsoft Office整合應用撰稿人 著作： 軟體-花旗銀行信用卡管理系統、花旗銀行業務員薪資管理系統、 中信保險公司津貼卡管理系統 書籍-電腦技能丙級檢定軟體設計題解、電腦乙級檢定軟體應用題解 以及Microsoft Officeie系列叢書十餘種。

3 Agenda 函數的效率 函數的基本格式 輸入函數的方法 數學函數的應用技巧 條件式函數的應用技巧 字串函數的應用技巧 小數函數的應用技巧
查詢與檢視函數的應用技巧 日期函數的應用技巧 常用的財務函數 資料庫函數的應用技巧 陣列函數的應用技巧

4 函數的效率 「加總」公式 與 函數的比較 計算「標準差」公式 與 函數的比較 =D2+D3+D4+D5+D6+E2+E3+E4+E5+E6
=SUM(D2:E6) 計算「標準差」公式 與 函數的比較 =(((D2-D7)^2+(D3-D7)^2+(D4-D7)^2+(D5-D7)^2+ (D6-D7)^2)/7)^0.5 (註：D7為D2:D6的平均值) =STDEV(D2:D6)

5 函數的基本格式 必須以「=」開頭，加上括弧和引數 引數可以是：範圍、位址、數值、函數、範圍名稱、文字、邏輯值
=SUM(c2:h10,k5,100,average(m3:m6)) 以SUM為例，引數最多可達30個 有引數的函數 =SUM(c2:h10) 沒有引數的函數 =NOW()、=TODAY() Excel提供多達400個以上的函數

6 輸入函數的方法 直接在儲存格中輸入完整的函數內容 =sum(c2:h10) 先輸入指令和左括弧再拖曳範圍 =sum(
點選「插入/函數」，使用插入函數對話視窗

7 數學函數的應用技巧(一) 加總：=SUM(D3:D6) 相乘：PRODUCT(5, 4)=20 取整數=INT(123.579) =123
指定小數位數=ROUND( ,2) =123.58 無條件捨位=ROUNDDOWN( ,2) =123.57 無條件進位=ROUNDUP( ,2) =123.58 距陣相乘：=SUMPRODUCT(B6:E6,A3:D3) 求餘數：=MOD(9,2)=1 ,求商：=QUOTIENT(8,3)=2 倍數進位：=CEILING(17.3,0.5)=17.5 =TRUNC(17.3,0.5)=17 共有52個數學函數

8 數學函數的應用技巧(二) 亂數：=RAND()*5000+18000 =RANDBETWEEN(18000,50000) 必須啟動增益集
SUMIF(比對的範圍,條件,要加總的範圍) =SUMIF(D2:D10,”>= ”) =SUMIF(業務員姓名,F2,訂單金額)

9 邏輯判斷函數的應用技巧(一) =IF(條件,條件成立使用此值,不成立使用此值) =IF(D2>=90,” 優”,””)
=IF(考績>=90,” 優”,IF(考績>=80,” 甲”, IF(考績>=70,” 乙”, IF(考績>=60,” 丙”, ” 丁”)))) =IF(業績> ,業績*0.05,0) IF最多只能使用七層的巢狀套疊 共有6邏輯判斷函數

10 邏輯函數的應用技巧(二) =IF(AND(B7>=90,C7>=90,D7>=90),"優等","")
=IF(OR(B7<60,C7<60,D7<60),"加油","") =IF(NOT(E7>90) ,1,2)傳回TRUE 或FALSE IS相關函數(共有11個)，傳回TRUE 或FALSE ISNA() ISTEXT() ISBLANK() ISERROR()

11 字串函數的應用技巧(一) =LEFT(“台北*市政府”,2) =“台北” =RIGHT(“台北*市政府”,2) =“政府”
=LEN(“台北*市政府”) = 6 =FIND(“*”,“台北*市政府”) = 3 FIND()大小寫視為相異 SEARCH()大小寫視為相同 =MID(“台北*市政府”,FIND(“*”,“台北*市政府”)+1,10) =“市政府” 共有35個字串函數

12 字串函數的應用技巧(二) =EXACT(“台北”,“台北 ") =FALSE =TRIM(“台北 “) =“台北”
=TEXT( ,“$ #,##0 元整”) =$ 123,456,789 元整 =REPT("▲",ROUND(D3*100,0)) 字串比對 刪除字串右邊空白 套用指定格式並轉換成文字 重複字串

13 小數函數的應用技巧 =INT(123.579) =123 =TRUNC(123.579) =123
=ROUND( ,2) =123.58 =ROUNDDOWN( ,2) =123.57 =ROUNDUP( ,2) =123.58 =CEILING(15.6,0.5) =16 =FLOOR(15.6,0.5) =15.5 取整數 指定小數位數 倍數進位

14 查詢與檢視函數的應用技巧(一) VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) ) =VLOOKUP(A2,對照表,2,FALSE 或true) 使用true時，對照表中的資料必須由小到大排序 HLOOKUP() 只是資料排列的方向不同 LOOKUP(lookup_value,lookup_vector,result_vector ) =LOOKUP(A2,B2:B10,C2:C10) 對照表中的資料必須由小到大排序 共有16個查詢與檢視函數

15 查詢與檢視函數的應用技巧(二) MATCH(lookup_value,lookup_array,match_type)
match_type=1 找到<= lookup_value 的值 ,遞增排序 match_type=-1找到>= lookup_value 的值 ,遞減排序 INDEX(array,row_num,column_num) =INDEX(A1:H10,MATCH(D1,A1:A10,0),3) TRANSPOSE()

16 日期函數的應用技巧(一) Excel的日期範圍：1900/1/1-9999/12/31
NOW() = 2006/1/20 15:18 擷取目前日期和時間 TODAY() = 2006/1/20 擷取目前日期 DATEVALUE(日期) 將字串轉換成天數 =DATEVALUE("2005/12/31") =38717 =DATEVALUE(“2008/7/1”) -TODAY() =921 ="從今天到2008/7/1共經過"& DATEVALUE("2008/7/1") -TODAY() &"天" 共有21個日期函數

17 日期函數的應用技巧(二) DAY(TODAY()) =27 第幾天 MONTH(TODAY()) =12 第幾個月
YEAR(TODAY()) =2005 第幾年 WORKDAY(開始日期，經過天數,[假日]) 計算二指定日期之間的工作天(可扣除特別假) NETWORKDAYS(開始日期,結束日期,[假日]) 計算二指定日期之間的可工作天數 =NETWORKDAYS("2005/12/25","2006/1/25") =NETWORKDAYS(D1,D2)

18 日期函數的應用技巧(三) DATEDIF() 半年之後的票期(配合TEXT()取得中文日期格式) 生日、年資計算(計算到今天)
=DATEDIF(D3,TODAY(),"y") =17 (D3=1988/7/16) =DATEDIF(D3,TODAY(),“ym") =5 =DATEDIF(D3,TODAY(),“md") =11 =DATEDIF(D3,TODAY(),“m”) =209 個月 =DATEDIF(D3,TODAY(),“d”) =6373天 半年之後的票期(配合TEXT()取得中文日期格式) =“半年後到期日為： ” & TEXT(TODAY()+180,”ee年mm月dd日”) =TEXT(D16,"yyyy年mm月dd日")

19 統計函數的應用技巧 最大及最小值：=MAX(D3:D6) 、=MIN(D3:D6)
計數：=COUNT(A1:A10)、=COUNTA(A1:A10 COUNT() 計算數值儲存格的個數 COUNTA()計算除空白儲存格以外的儲存格個數 可用於自動偵資料庫的大小 =VLOOKUP(B2,INDIRECT("H1:I"&COUNTA(A:A)),2,FALSE) COUNTIF() RANK() 排名次 FREQUENCY() 計算頻率-此為陣列函數 =MODE(A2:A200)眾數 共有79個統計函數

20 常用的財務函數 PMT() 分期付款 PPMT() 計算每期支付的本金 IPMT() 計算每期支付的利息 FV() 零存整付
PV() 年金的現值 共有52個財務函數

21 資料庫函數的應用技巧 DSUM(資料庫，欄位，準則) DCOUNT(資料庫，欄位，準則) DAVERAGE(資料庫，欄位，準則)
DMAX(資料庫，欄位，準則) DMIN(資料庫，欄位，準則) SUBTOTAL(函數代號，範圍) 共有13個資料庫函數

22 陣列函數的應用技巧 為何叫作「陣列函數」？ 陣列函數的操作方式 常用的陣列函數 一般函數的陣列用法 此類函數經過運算會傳回一堆資料
函數在執行時必須按下Ctrl+Shift+Enter 常用的陣列函數 Frequency() Transpose() 一般函數的陣列用法 共有8個陣列函數

23 謝謝光臨 王作桓