STEP財(cái)務(wù)人員EXCEL培訓(xùn)

1、內(nèi)部培訓(xùn)教材Excel常用函數(shù)財(cái)務(wù)人員培訓(xùn)之 2 培 訓(xùn) 內(nèi) 容 簡(jiǎn) 介 常用函數(shù)基本功能講解第二章基本概念的理解第一章第三章函數(shù)綜合應(yīng)用示例 3 三個(gè)基本概念l 相對(duì)引用l 絕對(duì)引用l 混合引用基本概念第一章 4 基本概念相對(duì)引用：相對(duì)引用實(shí)際上是引用單元格的相對(duì)位置，即當(dāng)把公式復(fù)制到其他單元格時(shí)，行或者列都會(huì)發(fā)生改變。如當(dāng)前單元格顯示為A1，向下復(fù)制會(huì)自動(dòng)變成A2，向右復(fù)制向B1。絕對(duì)引用：絕對(duì)引用實(shí)際上是引用單元格的絕對(duì)位置，即當(dāng)復(fù)制公式時(shí)，行和列均不會(huì)發(fā)生改變。如當(dāng)前單元格顯示為$A$1,向下或向右復(fù)制單元格顯示的內(nèi)容不變，始終還是$A$1?；旌弦茫夯旌弦脤?shí)際上是行或者列當(dāng)中有一個(gè)

2、是絕對(duì)引用，另一個(gè)是相對(duì)引用。如單元格顯示為$A1（對(duì)列進(jìn)行絕對(duì)引用）,即復(fù)制公式到任何其他的單元格改變的是行而列號(hào)始終保持不變；或A$1（對(duì)行進(jìn)行絕對(duì)引用）, 即復(fù)制公式到任何其他的單元格改變的是列而行號(hào)始終保持不變。 小竅門：相對(duì)引用與絕對(duì)引用的快速切換鍵F4第一章 5 函數(shù)基本功能講解包含四方面內(nèi)容:l函數(shù)主要功能l函數(shù)使用格式l涉及參數(shù)說(shuō)明l基本用法示例第二章 6 第二章 IF函數(shù)l主要功能: 根據(jù)對(duì)指定條件的邏輯判斷的真假結(jié)果,返回相對(duì)應(yīng)的內(nèi)容l使用格式:=IF(Logical,Value_if_true,Value_if_false)l參數(shù)說(shuō)明:Logical代表邏輯判斷表達(dá)式;V

3、alue_if_true表示當(dāng)判斷條件為邏輯” 真(True)”時(shí)顯示的內(nèi)容,如果忽略返回”True”;Value_if_false表示當(dāng)判斷條件為邏輯”假(False)”時(shí)的顯示內(nèi)容,如果忽略返回”False”.l應(yīng)用示例: 一般性示例參考IF一般性應(yīng)用表重點(diǎn)講涉及需同時(shí)滿足多個(gè)條件的判斷:如公司20132013年對(duì)工齡超過(guò)5 5年同時(shí)屬制造部門的員工進(jìn)行加薪，具體示例見(jiàn)ExcelExcel常用函數(shù)實(shí)例操作-IF-IF示例: :公式= IF(AND(B2=制造部門,E25),Y,) 7 第二章 IF函數(shù)同時(shí)滿足多個(gè)條件的判斷思考：如果公司20132013年對(duì)工齡超過(guò)5 5年同時(shí)屬制造部門或研

4、發(fā)部門的員工進(jìn)行加薪，那公式怎么寫？ 8 第二章 Vlookup函數(shù)l主要功能: 在數(shù)據(jù)表的首列查找指定的數(shù)值,并由此返回?cái)?shù)據(jù)表當(dāng)前行中指定列處的數(shù)值l使用格式: Vlookup(lookup_value,table_array,col_index_num,range_lookup)l參數(shù)說(shuō)明:Lookup_value代表需要查找的數(shù)值；Table_array代表需要在其中查找數(shù)據(jù)的單元格區(qū)域；Col_index_num為在table_array區(qū)域中待返回的匹配值的列序號(hào)（當(dāng)Col_index_num為2時(shí),返回table_array第2列中的數(shù)值，為3時(shí)，返回第3列的值）；Range_lo

5、okup為一邏輯值，如果為TRUE或省略，則返回近似匹配值，也就是說(shuō)，如果找不到精確匹配值，則返回小于lookup_value的最大數(shù)值；如果為FALSE，則返回精確匹配值，如果找不到，則返回錯(cuò)誤值#N/A。 9 第二章 Vlookup函數(shù)出現(xiàn)#N/A的處理l應(yīng)用示例:一般性示例略，具體解決如果找不到目標(biāo)值則返回錯(cuò)誤值#N/A的情況。此時(shí)會(huì)涉及到另外一個(gè)函數(shù)ISNA，該函數(shù)用來(lái)判斷是否存在#N/A的錯(cuò)誤值，如果存在則返回一個(gè)自己想要設(shè)定的值。如查找公司員工所屬部門，如職工姓名在查找范圍內(nèi)不存在的情況下的處理。 公式公式=IF(ISNA(VLOOKUP(I1,A:B,2,0),=IF(ISNA(

6、VLOOKUP(I1,A:B,2,0),無(wú)此員工無(wú)此員工,VLOOKUP(I1,A:B,2,0),VLOOKUP(I1,A:B,2,0) 10 第二章 SUM函數(shù)l主要功能: 計(jì)算所有參數(shù)數(shù)值的和l使用格式:=SUMSUM（Number1,Number2Number1,Number2）l參數(shù)說(shuō)明:Number1Number1、Number2Number2代表需要計(jì)算的值，可以是具體的數(shù)值、引用的單元格（區(qū)域）、邏輯值等l應(yīng)用示例:一般性示例略, ,重點(diǎn)解決多個(gè)相同格式的工作表對(duì)同一單元格進(jìn)行求和的問(wèn)題， 如對(duì)20132013年度各月發(fā)生的現(xiàn)金流量表進(jìn)行按月匯總。思考: :如果SUMSUM內(nèi)的表

7、格之間的位置發(fā)生變化會(huì)出現(xiàn)什么結(jié)果? ?公式=SUM(201101:201112!C7)UM(201101:201112!C7) 11 第二章 SUMIF函數(shù)l主要功能:計(jì)算符合指定條件的單元格區(qū)域內(nèi)的數(shù)值和l使用格式:= SUMIFSUMIF（Range,Criteria,Sum_RangeRange,Criteria,Sum_Range）l參數(shù)說(shuō)明:RangeRange代表?xiàng)l件判斷的單元格區(qū)域，即在哪個(gè)范圍內(nèi)來(lái)尋找需要滿足的條件；CriteriaCriteria為指定條件表達(dá)式；Sum_RangeSum_Range代表需要計(jì)算的數(shù)值所在的單元格區(qū)域。l應(yīng)用示例: 由于此函數(shù)應(yīng)用較為廣泛,故

8、重點(diǎn)列舉此函數(shù)的五種常用用法來(lái)舉例。 12 第二章 SUMIF函數(shù)示例之一l對(duì)滿足條件列的求和：如對(duì)表名為SumifSumif對(duì)列求和中的數(shù)據(jù)按供應(yīng)商名稱進(jìn)行數(shù)量和金額匯總，則sumifsumif函數(shù)中的Range=BRange=B列，CriteriaCriteria即為需要進(jìn)行匯總的各個(gè)供應(yīng)商名稱=H1=H1，H2.H2.，而Sum_RangeSum_Range即為需要進(jìn)行匯總的E E列和F F列公式=SUMIF(B:B,H2,E:E)SUMIF(B:B,H2,E:E) 13 第二章 SUMIF函數(shù)示例之二l對(duì)滿足條件行的求和：如對(duì)表名為SumifSumif對(duì)行求和中的數(shù)據(jù)分別按201220

9、12年度和20132013年度進(jìn)行匯總，則sumifsumif函數(shù)中的Range=Range=第二行( (即$B$2:$AK$2)$B$2:$AK$2)，CriteriaCriteria即為需要進(jìn)行匯總的各個(gè)年份=AN2=AN2或AO2AO2，而Sum_RangeSum_Range即為需要進(jìn)行匯總的行, ,如第3 3行, ,第4 4行等公式= SUMIF($B$2:$AK$2,AN$2,$B3:$SUMIF($B$2:$AK$2,AN$2,$B3:$AK3)AK3) 14 第二章 SUMIF函數(shù)示例之三l對(duì)滿足模糊條件的值進(jìn)行匯總：如在表名為SumifSumif模糊條件匯總的表中，對(duì)料號(hào)包含F(xiàn)

10、38F38物料進(jìn)行匯總。則sumifsumif函數(shù)中的Range=ARange=A列，CriteriaCriteria為“F38F38* *”，而Sum_RangeSum_Range為需要進(jìn)行匯總的列, ,如要匯總數(shù)量則為C C列，要匯總金額則為D D列。公式=SUMIF(A:A,F38SUMIF(A:A,F38* *,D:D),D:D)如果不如果不直接輸直接輸入入F38F38* *而用單而用單元格的元格的數(shù)值來(lái)數(shù)值來(lái)替換怎替換怎么弄？么弄？ 15 第二章 SUMIF函數(shù)示例之四l對(duì)滿足日期條件的值進(jìn)行匯總：如在表名為SumifSumif對(duì)日期條件匯總的表中，對(duì)日期大于20112011年5 5

11、月5 5日的質(zhì)量成本進(jìn)行匯總。則sumifsumif函數(shù)中的Range=ARange=A列，CriteriaCriteria為“”&date(2011,5,5)”&date(2011,5,5)，而而Sum_RangeSum_Range為需要進(jìn)行匯總的C C列。思考: :如果要匯總大于2011.5.12011.5.1日且小于2011.7.12011.7.1日的值怎么寫? ?公式= SUMIF(A:A,&DATE(2011,5,SUMIF(A:A,&DATE(2011,5,5),C:C)5),C:C) 16 第二章 SUMIF函數(shù)示例之五l對(duì)滿足多個(gè)條件的值同時(shí)進(jìn)行

12、匯總：如在產(chǎn)成品差異分?jǐn)偨Y(jié)轉(zhuǎn)表中，其他存貨金額包含了在制品、半制品、產(chǎn)成品和在途商品，現(xiàn)對(duì)這四個(gè)會(huì)計(jì)科目同時(shí)進(jìn)行匯總。用科目代碼為滿足條件，則sumifsumif函數(shù)中的Range=BRange=B列，CriteriaCriteria為四個(gè)會(huì)計(jì)科目的代碼用數(shù)組表示“12410101”,“12420101”,“12430101”,“12900102” “12410101”,“12420101”,“12430101”,“12900102” ，而Sum_RangeSum_Range為需要進(jìn)行匯總的D D列。此處由于是要多對(duì)個(gè)會(huì)計(jì)科目同時(shí)進(jìn)行求和，所以在sumifsumif函數(shù)結(jié)束后再需用sumsum

13、函數(shù)對(duì)其進(jìn)行匯總。公式= SUM(SUMIF(SUM(SUMIF(科目余額表科目余額表-201213!B:B,12410101,12420101,12430101,12900102,-201213!B:B,12410101,12420101,12430101,12900102,科目科目余額表余額表-201213!G:G)-201213!G:G) 17 第二章 SUMIF函數(shù)示例之五 思考：模糊條件匯總與多個(gè)條件值同時(shí)進(jìn)行匯總的關(guān)思考：模糊條件匯總與多個(gè)條件值同時(shí)進(jìn)行匯總的關(guān)系系 18 第二章 Column函數(shù)l主要功能:返回給定引用的列標(biāo)l使用格式:= COLUMN(reference)l參數(shù)

14、說(shuō)明:Reference為需要得到其列標(biāo)的單元格或單元格區(qū)域。如果省略reference，則假定函數(shù)COLUMN是對(duì)所在單元格的引用。如果reference為一個(gè)單元格區(qū)域，并且函數(shù)COLUMN作為水平數(shù)組輸入，則COLUMN函數(shù)將reference中的列標(biāo)以水平數(shù)組的形式返回l應(yīng)用示例:公式“=COLUMN(A3)”返回1，公式=COLUMN(B3:C5)返回2 19 第二章 Row函數(shù)l主要功能:返回給定引用的行號(hào)l使用格式:= ROW(reference)l參數(shù)說(shuō)明:Reference為需要得到其行號(hào)的單元格或單元格區(qū)域l應(yīng)用示例:公式“=ROW(A6)”返回6，如果在C5單元格中輸入公

15、式“=ROW()”，其計(jì)算結(jié)果為5 思考：Column和Row返回單元格的行和列號(hào)在實(shí)際上有什么用處？ 20 第二章 Trim函數(shù)l主要功能:除了單詞之間的單個(gè)空格外，清除文本中的所有的空格l使用格式:= TRIM(text)l參數(shù)說(shuō)明:Text是需要清除其中空格的文本l應(yīng)用示例:如果A1= FirstQuarterEarnings ，則公式“=TRIM(A1)”返回“FirstQuarterEarnings”，A1A1單元格內(nèi)容中首未空格被剔除。 特別說(shuō)明：此函數(shù)主要用在用特別說(shuō)明：此函數(shù)主要用在用VlookupVlookup檢索某個(gè)文本值是否一致檢索某個(gè)文本值是否一致時(shí)，由于要檢索的單元格

16、文本含有人眼所無(wú)法直接判斷出的字符，時(shí)，由于要檢索的單元格文本含有人眼所無(wú)法直接判斷出的字符，導(dǎo)致導(dǎo)致VlookupVlookup檢索失敗的情況。檢索失敗的情況。 21 第二章 Clean函數(shù)l主要功能:刪除文本中不能打印的字符，主要是單元格在自動(dòng)換行中留有空行的情況l使用格式:= CLEAN(text)l參數(shù)說(shuō)明:Text為要從中刪除不能打印字符的任何字符串l應(yīng)用示例:如Clean剔除自動(dòng)換行空格表中A1=1=B2=Clean(A1)B2=Clean(A1)后剔除了自動(dòng)換行時(shí)所留下的空行, ,則顯示為 22 第二章 Indirect函數(shù)l主要功能:返回由文字串指定的引用, ,此函數(shù)立即對(duì)引用

17、進(jìn)行計(jì)算并顯示其內(nèi)容。l使用格式:= INDIRECT(ref_text INDIRECT(ref_text，a1)a1)l參數(shù)說(shuō)明:Ref_textRef_text是對(duì)單元格的引用，此單元格可以包含A1A1樣式的引用、R1C1R1C1樣式的引用、定義為引用的名稱或?qū)ξ淖执畣卧竦囊?A1;A1為一邏輯值，指明包含在單元格ref_textref_text中的引用的類型。如果a1a1為TRUETRUE或省略，ref_textref_text被解釋為A1-A1-樣式的引用。如果a1a1為FALSEFALSE，ref_textref_text被解釋為R1C1-R1C1-樣式的引用。l應(yīng)用示例: 具

18、體示例參見(jiàn)科目余額表-格式整理模板表。 23 第二章 Indirect函數(shù)公式= INDIRECT(SAPINDIRECT(SAP科目余額表科目余額表!F&ROW()!F&ROW()* *2+1)2+1) C列單元格公式列單元格公式 24 第二章 Indirect函數(shù)公式= VALUE(TRIM(INDIRECT(SAPVALUE(TRIM(INDIRECT(SAP科目余額科目余額表表!J&ROW()!J&ROW()* *2+1)2+1) D列單元格公式列單元格公式 25 第三章 根據(jù)上述所講到的函數(shù)根據(jù)上述所講到的函數(shù),建立一張建立一張Excel查詢表查詢表,使使其能根