高級辦公自動化-excel

教學內容第一節(jié)Excel常用操作技巧第三節(jié)數據管理與分析第四節(jié)數據的圖示化表達第二節(jié)Excel函數與應用案例Page1第二節(jié)Excel函數與應用案例教學知識點求和相關函數(Sum、SumIf)文本函數(Mid、Concatenate、SubStitute、Replace和EXACT)統(tǒng)計函數（Count、CountIf、Max、Min、Rank）條件函數與條件格式（If）查詢與

函數(LookUp、HlookUp、VlookUp)財務函數(PMT、IPMT、FV、PV和SLN)日期時間函數（Date、Year、Month、Day和Today）Page2第二節(jié)Excel函數與應用案例SumIf：按指定條件對若干單元格求和語法：=SumIf（Range，Criteria，Sum_range）說明：Range：條件判斷區(qū)域Criteria：條件表達式Sum_Range：需要求和的單元格區(qū)域例：=SUMIF(A2:A11,"A產品",C2:C11)案例：在

銷售表中統(tǒng)計個銷售員的總銷總額Page3第二節(jié)Excel函數與應用案例文本函數Mid函數：從文本指定的位置起返回指定長度的文本語法：=Mid(text,start_num,num_chars)說明：text：指定文本start_num：起始位置Num_chars：返回字符的個數Page4第二節(jié)Excel函數與應用案例文本函數Concatenate函數：將多個文本連接在一起，顯示在一個單元格中語法：=Concatenate(text1,

text2,…)Page5第二節(jié)Excel函數與應用案例文本函數Substitute函數：替換文本中某個特定文本字符串語法：=Substitute(text1,

old_text,new_text,instance_num)說明：text1：原始文本old_text：將被替換的字符串new_text：替換old_text的新字符串Instance_num：若文本中有多組相同的old_texts，可

以使用該參數來指定被替換的字符串是文本中的第幾組，若不指定instance_num值，文本中所有的old_text都會被替換。Page6第二節(jié)Excel函數與應用案例文本函數Replace函數：將文本中某幾位字符以新的字符替換語法：=Replace(old_text,start_num,num_chars,new_text)說明：Old_text：原始文本start_num：替換的起始位置num_char：多少個字符被替換new_text：用來替換的新字符串Page7第二節(jié)Excel函數與應用案例文本函數Exact函數：比較兩個文本字符串是否相同語法：=Exact(text1,text2)說明：相同返回True，不相同返回False此外，常用的文本函數還有Lower、Upper更改大小寫文本函數應用案例Page8第二節(jié)Excel函數與應用案例If函數與條件格式條件函數語法:=If(Logical_Test,value_True,value_False)注意：條件函數與條件格式的區(qū)別條件函數的嵌套案例分析Page9第二節(jié)Excel函數與應用案例統(tǒng)計函數Count：統(tǒng)計區(qū)域內數字的個數案例：統(tǒng)計銷售表中，

“A產品”銷售的 的條數=COUNT(IF(A2:A12=A2,C2:C12))統(tǒng)計A2:A12區(qū)域中值為A2，在C2:C12中的數字的個數說明：輸完公式后，按Ctrl+shift+Enter鍵

‘數組CountIf：統(tǒng)計區(qū)域內滿足特定條件的單元格個數語法：=CountIf(Range,Criteria)案例：按條件進行統(tǒng)計Page10第二節(jié)Excel函數與應用案例Rank：返回一個數值在一批數中的位置語法：=Rank（number，range，order）說明：Number：參加排序的數值Range：排序的范圍Order：0為升序，非0為降序案例：按

大小進行統(tǒng)計函數還有CountBlank、Max和Min等常用函數Page11第二節(jié)Excel函數與應用案例查詢與

函數LookUp：根據條件查詢，分為向量型和數組型向量型：在單行或單列中查找指定的值，返回第二個單行或單列相同位置的值語法：=LookUp(value,lookup_vector,result_vector)說明：value：表示要搜索的值Lookup_vector：要搜索的值所在的區(qū)域Result_vector：返回值所在區(qū)域案例：根據員工的

查詢銷售情況Page12第二節(jié)Excel函數與應用案例LookUp：根據條件查詢，分為向量型和數組型數組型：在數組的第一行或第一列查找指定的值，返回數組最后一行或最后一列內同一位置的值語法：=look_up(lookup_value,array)案例：根據員工的 查詢銷售情況Page13第二節(jié)Excel函數與應用案例Hlookup：在表格首行查找指定的值，返回表格指定行的同一列上的值語法：=Hlookup(value,array,index)說明：value：表示在數據表的第一行中要查找的值Array：查找的數據表Index：表示返回數據表中第幾行的值，如index=1表示返回第一行的值案例：統(tǒng)計每個銷售員的提成比例Page14第二節(jié)Excel函數與應用案例VLookup：在表格或數組的首列查找指定的值，返回表格當前行中其他列的值語法：=VLookup(value,array,col_index,True/False)說明：Value：表示要查找的值Array：表示查找的數據表Col_index：返回數據表第幾列值，如Col_index=2，返回第2列的值True/False：表示返回近似值還是精確值Page

案1例5

：填充商品的價格第二節(jié)Excel函數與應用案例財務函數PMT函數：基于固定利率及等額分期付款方式，返回的每期付款額語法：=PMT(rate,nper,pv,fv,type)說明：Rate：

利率nper：

的時間Pv：

的本金Fv：未來值（最后一次付款后的現金余額），可選Type：各期的付款時間是在期初還是期末，可選應用案例Page16第二節(jié)Excel函數與應用案例IPMT：用于固定利率及等額分期付款方式，返回

在某一給定期限內的利息金額語法：=IPMT(rate，per,nper,pv,fv,type)說明：per用于計算利息數額的期數，其值在1-nper之間案例：某公司向銀行5萬元，期限是5年，年利率為

6%，請計算該公司每年的償還金額和每年償還的利息金額.Page17第二節(jié)Excel函數與應用案例FV：用于固定利率及等額分期付款方式，返回某項投資的未來值語法：=FV(rate,nper,pmt,fv,type)rate：各期利率nper：總投資期pmt：各期應支付的金額Pv：本金type：付款時間是在期初（1）還是在期末（0）應用案例：

用于某項投資5萬元，年利率6%，并在接下來的5年中每年再投資5000元，5年后應得到的金額Page

是1多8

少？第二節(jié)Excel函數與應用案例PV：用于計算某項投資所需的金額語法：=FV(rate,nper,pmt,pv,type)rate：

利率nper：總投資期pmt：各期應支付的金額fv：未來值type：付款時間是在期初（1）還是在期末（0）應用案例：某項投資1.5萬元，投資年限10年，回報年利率是15%，那么預計投資多少金額？Page19第二節(jié)Excel函數與應用案例SLN：用于計算設備

、每月、每年的折舊值語法：=SLN(cost,salvage,life)cost：資產原值salvage：資產在折舊末期的價值，即資產殘值Life：資產的使用應用案例：某

擁有固定資產5萬元，使用10年后的資產殘值為8000元，那么每天、每月、每年固定資產的折舊值是多少？Page20第二節(jié)Excel函數與應用案例日期時間函數DATE函數：計算某一特定日期的系列語法：=Date(year,month,day)說明：year：指定的年份month：月份day：月份中的第幾天注意：若輸入的月份大于12，將從指定年份一月份開始往上累加，如date(2008,14,2)，返回2009-2-2若輸入的天數大于該月的最大天數，則從指定月數的第一天往上累加，如date(2008,1,35)，返回2008-2-4Page21第二節(jié)Excel函數與應用案例日期時間函數DAY函數：返回指定日期所對應月份中的第幾天語法：=Day(serial_number)說明：Serial_number：指定的日期或數值例：Day（2008-1-1），返回值為1

Day（39448），返回值為1，系統(tǒng)日期將1900-1-1對應于序列號1，后面的日期都是相對于這個序列號進行累加拓展：Year函數、Month函數Page22第二節(jié)Excel函數與應用案例日期時間函數TODAY函數：返回系統(tǒng)當前的日期語法：=TODay()應用案例——設備定期提醒：小王從事設備管理工作，有近千臺機械設備需要定期進行精度檢測，每天小王都要翻閱“設備鑒定臺賬”來尋找“到期”的設備，耗時耗力，請你運用所學的知識幫助小王建立一本“設備鑒定臺賬”，實現設備“到期”自動提醒功能。Page23第二節(jié)Excel函數與應用案例數據表概念字段：數據表中的列稱為字段，列標題稱為字段名記錄：數據表中的每一行稱為一條記錄數據表的幾點說明一個工作表一般只創(chuàng)建一個數據表同一列的數據類型相同避免使用空行或空列記錄單的使用Page24第二節(jié)Excel函數與應用案例數據庫函數數據庫函數的語法：=函數名(database,field,criteria）說明：database：數據field：指定函數所使用的數據列criteria：包含給定條件的單元格區(qū)域數據庫函數的特點數據庫函數均有上述三個參數數據庫函數幾乎都是以D開頭，將字母D去掉，就是Excel中其他類型的函數。如DCountCountPage25第二節(jié)Excel函數與應用案例數據庫函數Dcount：返回數據庫中滿足指定條件的記錄字段中包含數值單元格的個數

DGet：用于從列表或數據庫列中提取符合指定條件的單個值DAverage：計算列表或數據庫的列中滿足指定條件的數值的平均值DMax：返回列表或數據庫的列中滿足指定條件的最大值DSum：返回列表或數據庫的列中滿足指定條件的數字之和數據庫函數案例Page26教學內容第二節(jié)Excel函數與應用案例第一節(jié)Excel常用操作技巧第四節(jié)數據的圖示化表達第三節(jié)數據管理與分析Page27第三節(jié)數據管理與分析教學知識點數據排序篩選分類匯總

模擬運算表單變量求解數據 表(圖)Page28第三節(jié)數據管理與分析數據排序普通排序自定義排序案例：對商品銷售表進行排序Page29第三節(jié)數據管理與分析篩選：只顯示滿足特定條件的記錄，不符合條件的記錄隱藏起來自動篩選案例：對商品銷售表進行篩選高級篩選說明：高級篩選首先要構造一個條件區(qū)域，注意條件區(qū)域不能與數據表連接，必須空出一行或一列條件區(qū)域構造的規(guī)則為：同一列中的條件是“或”關系，同一行的條件是“與”關系Page30第三節(jié)數據管理與分析分類匯總分類匯總是對數據表指定的行或列中的數據進行匯總統(tǒng)計說明：進行分類匯總前，必須對匯總的類進行排序分類匯總可以完成不同的計算：求和、平均值等案例：統(tǒng)計銷售表中各個類別的商品銷售數量Page31第三節(jié)數據管理與分析案例：對每個類別的不同品牌商品的銷售金額與利潤金額進行匯總分析：該題是按類別進行匯總，還是按品牌進行匯總思路：先對類別進行分類匯總，然后在此基礎上再對