STEP財務人員EXCEL培訓

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

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

3、ue_if_true表示當判斷條件為邏輯” 真(True)”時顯示的內容,如果忽略返回”True”;Value_if_false表示當判斷條件為邏輯”假(False)”時的顯示內容,如果忽略返回”False”.l應用示例: 一般性示例參考IF一般性應用表重點講涉及需同時滿足多個條件的判斷:如公司20132013年對工齡超過5 5年同時屬制造部門的員工進行加薪，具體示例見ExcelExcel常用函數(shù)實例操作-IF-IF示例: :功能講解公式= IF(AND(B2=制造部門,E25),Y,) 7 第二章 IF函數(shù)同時滿足多個條件的判斷思考：如果公司20132013年對工齡超過5 5年同時屬制造部門

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

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

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

7、考: :如果SUMSUM內的表格之間的位置發(fā)生變化會出現(xiàn)什么結果? ?功能講解公式=SUM(201101:201112!C7)UM(201101:201112!C7) 11 第二章 SUMIF函數(shù)l主要功能:計算符合指定條件的單元格區(qū)域內的數(shù)值和l使用格式:= SUMIFSUMIF（Range,Criteria,Sum_RangeRange,Criteria,Sum_Range）l參數(shù)說明:RangeRange代表條件判斷的單元格區(qū)域，即在哪個范圍內來尋找需要滿足的條件；CriteriaCriteria為指定條件表達式；Sum_RangeSum_Range代表需要計算的數(shù)值所在的單元格區(qū)域。l

8、應用示例: 由于此函數(shù)應用較為廣泛,故重點列舉此函數(shù)的五種常用用法來舉例。功能講解 12 第二章 SUMIF函數(shù)示例之一l對滿足條件列的求和：如對表名為SumifSumif對列求和中的數(shù)據(jù)按供應商名稱進行數(shù)量和金額匯總，則sumifsumif函數(shù)中的Range=BRange=B列，CriteriaCriteria即為需要進行匯總的各個供應商名稱=H1=H1，H2.H2.，而Sum_RangeSum_Range即為需要進行匯總的E E列和F F列功能講解公式=SUMIF(B:B,H2,E:E)SUMIF(B:B,H2,E:E) 13 第二章 SUMIF函數(shù)示例之二l對滿足條件行的求和：如對表名為

9、SumifSumif對行求和中的數(shù)據(jù)分別按20122012年度和20132013年度進行匯總，則sumifsumif函數(shù)中的Range=Range=第二行( (即$B$2:$AK$2)$B$2:$AK$2)，CriteriaCriteria即為需要進行匯總的各個年份=AN2=AN2或AO2AO2，而Sum_RangeSum_Range即為需要進行匯總的行, ,如第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對滿足模糊條件的值進行匯總：

10、如在表名為SumifSumif模糊條件匯總的表中，對料號包含F(xiàn)38F38物料進行匯總。則sumifsumif函數(shù)中的Range=ARange=A列，CriteriaCriteria為“F38F38* *”，而Sum_RangeSum_Range為需要進行匯總的列, ,如要匯總數(shù)量則為C C列，要匯總金額則為D D列。功能講解公式=SUMIF(A:A,F38SUMIF(A:A,F38* *,D:D),D:D)如果不如果不直接輸直接輸入入F38F38* *而用單而用單元格的元格的數(shù)值來數(shù)值來替換怎替換怎么弄？么弄？ 15 第二章 SUMIF函數(shù)示例之四l對滿足日期條件的值進行匯總：如在表名為Sum

11、ifSumif對日期條件匯總的表中，對日期大于20112011年5 5月5 5日的質量成本進行匯總。則sumifsumif函數(shù)中的Range=ARange=A列，CriteriaCriteria為“”&date(2011,5,5)”&date(2011,5,5)，而而Sum_RangeSum_Range為需要進行匯總的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

12、),C:C) 16 第二章 SUMIF函數(shù)示例之五l對滿足多個條件的值同時進行匯總：如在產(chǎn)成品差異分攤結轉表中，其他存貨金額包含了在制品、半制品、產(chǎn)成品和在途商品，現(xiàn)對這四個會計科目同時進行匯總。用科目代碼為滿足條件，則sumifsumif函數(shù)中的Range=BRange=B列，CriteriaCriteria為四個會計科目的代碼用數(shù)組表示“12410101”,“12420101”,“12430101”,“12900102” “12410101”,“12420101”,“12430101”,“12900102” ，而Sum_RangeSum_Range為需要進行匯總的D D列。此處由于是要多對

13、個會計科目同時進行求和，所以在sumifsumif函數(shù)結束后再需用sumsum函數(shù)對其進行匯總。功能講解公式= 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ù)示例之五功能講解 思考：模糊條件匯總與多個條件值同時進行匯總的關思考：模糊條件匯總與多個條件值同時進行匯總的關系系 18 第二章 Colu

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

15、需要得到其行號的單元格或單元格區(qū)域l應用示例:公式“=ROW(A6)”返回6，如果在C5單元格中輸入公式“=ROW()”，其計算結果為5功能講解 思考：Column和Row返回單元格的行和列號在實際上有什么用處？ 20 第二章 Trim函數(shù)l主要功能:除了單詞之間的單個空格外，清除文本中的所有的空格l使用格式:= TRIM(text)l參數(shù)說明:Text是需要清除其中空格的文本l應用示例:如果A1= FirstQuarterEarnings ，則公式“=TRIM(A1)”返回“FirstQuarterEarnings”，A1A1單元格內容中首未空格被剔除。功能講解 特別說明：此函數(shù)主要用在用特

16、別說明：此函數(shù)主要用在用VlookupVlookup檢索某個文本值是否一致檢索某個文本值是否一致時，由于要檢索的單元格文本含有人眼所無法直接判斷出的字符，時，由于要檢索的單元格文本含有人眼所無法直接判斷出的字符，導致導致VlookupVlookup檢索失敗的情況。檢索失敗的情況。 21 第二章 Clean函數(shù)l主要功能:刪除文本中不能打印的字符，主要是單元格在自動換行中留有空行的情況l使用格式:= CLEAN(text)l參數(shù)說明:Text為要從中刪除不能打印字符的任何字符串l應用示例:如Clean剔除自動換行空格表中A1=1=B2=Clean(A1)B2=Clean(A1)后剔除了自動換行時

17、所留下的空行, ,則顯示為功能講解 22 第二章 Indirect函數(shù)l主要功能:返回由文字串指定的引用, ,此函數(shù)立即對引用進行計算并顯示其內容。l使用格式:= INDIRECT( INDIRECT(ref_textref_text，a1)a1)l參數(shù)說明:Ref_textRef_text是對單元格的引用，此單元格可以包含A1A1樣式的引用、R1C1R1C1樣式的引用、定義為引用的名稱或對文字串單元格的引用;A1;A1為一邏輯值，指明包含在單元格ref_textref_text中的引用的類型。如果a1a1為TRUETRUE或省略，ref_textref_text被解釋為A1-A1-樣式的引用

18、。如果a1a1為FALSEFALSE，ref_textref_text被解釋為R1C1-R1C1-樣式的引用。l應用示例: 具體示例參見科目余額表-格式整理模板表。功能講解 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查詢表查詢表,使使其能根據(jù)所選擇的月份來查詢到