高級辦公自動化-excel

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

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

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

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

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

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

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

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

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

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

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

案1例5

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

利率nper：

的時(shí)間Pv：

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

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

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

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

是1多8

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

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

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

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

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

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

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