Excel在財(cái)務(wù)管理中的應(yīng)用課件：Excel的常用函數(shù)

Excel的常用函數(shù)3.1Excel函數(shù)的基礎(chǔ)知識(shí)3.1.1函數(shù)的介紹函數(shù)是數(shù)學(xué)領(lǐng)域中的一種對(duì)應(yīng)關(guān)系。如果一個(gè)量依賴于另一個(gè)量,后一個(gè)量發(fā)生變化時(shí),前一個(gè)量也隨著變化,那么前一個(gè)量就是后一個(gè)量的函數(shù)。簡單地說,甲隨著乙變,甲就是乙的函數(shù)。Excel函數(shù)與公式既有區(qū)別又相互聯(lián)系。公式是一系列運(yùn)算的總稱,它以等號(hào)開始,等號(hào)后面可以是函數(shù)、運(yùn)算符、常量、單元格、數(shù)組等元素(詳見第1章)。Excel函數(shù)是指系統(tǒng)預(yù)先定義的特殊公式,它有固定的函數(shù)名稱、語法及參數(shù)等。除了Excel內(nèi)置函數(shù),使用者也可以自定義函數(shù)。熟練使用常用公式和函數(shù),能夠快速完成對(duì)數(shù)據(jù)的特定處理。掌握常用函數(shù)的使用方法,對(duì)財(cái)務(wù)人員高效完成數(shù)據(jù)處理、分析工作具有重要意義。Excel2019函數(shù)庫中共有14大類,491種函數(shù),功能強(qiáng)大,能夠滿足日常工作的需要。本書主要根據(jù)財(cái)務(wù)人員的一般工作需求,著重介紹日期和時(shí)間函數(shù)、數(shù)學(xué)和三角函數(shù)、統(tǒng)計(jì)函數(shù)、邏輯函數(shù)以及查找與引用函數(shù)這5大類中的常用函數(shù)。3.1.2函數(shù)的輸入輸入函數(shù)有兩種方法。第一種方法,選中需要通過內(nèi)置函數(shù)得到運(yùn)算結(jié)果的單元格,直接輸入“=函數(shù)名”即可。在輸入“=”及函數(shù)名稱時(shí),系統(tǒng)會(huì)自動(dòng)提示相關(guān)函數(shù)。如圖3-1所示,計(jì)算資產(chǎn)總計(jì)時(shí),單擊需要得到運(yùn)算結(jié)果的B3單元格,輸入“=su”時(shí),系統(tǒng)會(huì)提示相關(guān)函數(shù),單擊求和函數(shù)“SUM”即可輸入函數(shù)。圖3-13.1.2函數(shù)的輸入第二種方法,選中單元格,在“公式”選項(xiàng)卡下“函數(shù)庫”組中單擊“插入函數(shù)”選項(xiàng),或在編輯欄中單擊“fx”選項(xiàng),選擇需要的函數(shù),最后單擊“確定”選項(xiàng),如圖3-2所示。圖3-23.1.2函數(shù)的輸入Excel函數(shù)的格式為“函數(shù)名(參數(shù)1,參數(shù)2,…,[參數(shù)n])”。其中,[]表示可以缺省的參數(shù)。在“插入函數(shù)”對(duì)話框中,會(huì)提示每個(gè)函數(shù)的格式及含義,如圖3-3所示。圖3-33.1.3函數(shù)返回的錯(cuò)誤信息Excel函數(shù)的格式為“函數(shù)名(參數(shù)1,參數(shù)2,…,[參數(shù)n])”。其中,[]表示可以缺省的參數(shù)。在“插入函數(shù)”對(duì)話框中,會(huì)提示每個(gè)函數(shù)的格式及含義,如圖3-3所示。圖3-33.1.3函數(shù)返回的錯(cuò)誤信息在Excel函數(shù)的實(shí)際運(yùn)用中,如果輸入錯(cuò)誤將不能顯示正確的計(jì)算結(jié)果,系統(tǒng)還會(huì)返回一個(gè)錯(cuò)誤信息,用來警告或說明錯(cuò)誤類型。常見的八種返回錯(cuò)誤信息如表3-1所示。了解常見的函數(shù)返回錯(cuò)誤信息,能夠及時(shí)找到并更正錯(cuò)誤的輸入方式,完成對(duì)數(shù)據(jù)的處理。3.2日期和時(shí)間函數(shù)3.2.1日期和時(shí)間函數(shù)的基本設(shè)定如第1章所述,Excel將日期和時(shí)間存儲(chǔ)為“日期和時(shí)間型數(shù)據(jù)”,即可用于計(jì)算的序列號(hào)。當(dāng)單元格格式為“常規(guī)”時(shí),系統(tǒng)自動(dòng)將時(shí)間和日期函數(shù)結(jié)果用日期和時(shí)間格式顯示,格式默認(rèn)與控制面板設(shè)置中指定的日期和時(shí)間格式相同。當(dāng)單元格格式為“數(shù)字”時(shí),日期和時(shí)間以序列號(hào)的形式顯示。Windows系統(tǒng)下的Excel默認(rèn)使用“1900日期系統(tǒng)”,也就是說,每個(gè)自然數(shù)(系列號(hào))代表一天,1900年1月1日的序列號(hào)是1,1900年1月2日的序列號(hào)是2,以此類推,9999年12月31日的序列號(hào)是2958465。同時(shí),Excel將時(shí)間也記錄成具有唯一性的序列號(hào),即把24小時(shí)設(shè)為1,可細(xì)分至秒。例如,上午10點(diǎn)17分58秒的序列號(hào)可以這樣求得:“10點(diǎn)”的序列號(hào)=10÷24=0.416666667(保留9位小數(shù),下同);“17分”的序列號(hào)=17÷(24×60)=0.011805556;“58秒”的序列號(hào)=58÷(24×60×60)=0.000671296;三者相加得到10點(diǎn)17分58秒的序列號(hào)為0.429143519。3.2.2

DATE和YEAR、MONTH、DAY函數(shù)DATE函數(shù)能夠返回給定日期的序列號(hào),其語法為:DATE(year,month,day)。year為代表年份的四位整數(shù),取值范圍為1900~9999,可以是數(shù)值、單元格引用或者公式。如果year介于0到1899之間(含0和1899),系統(tǒng)會(huì)將該值與1900相加來計(jì)算年份。例如,在單元格中輸入“=DATE(111,5,2)”,將返回“2011年5月2日”,其中2011年是由1900+111所得。如果year小于0或大于等于10000,系統(tǒng)將返回錯(cuò)誤值“#NUM!”。month為代表月份的整數(shù),取值范圍為1~12,可以是數(shù)值、單元格引用或者公式。如果month大于12,則給定年份加1,月份為month值減去12。例如,在單元格中輸入“=DATE(2010,17,2)”,將返回“2011年5月2日”。如果month小于1,則給定年份減1,月份為12減去month的絕對(duì)值。例如,在單元格中輸入“=DATE(2011,-3,5)”,將返回“2010年9月5日”。3.2.2

DATE和YEAR、MONTH、DAY函數(shù)day為代表日的整數(shù),取值范圍為1~31,可以是數(shù)值、單元格引用或者公式。如果day大于指定月份的天數(shù),則day從指定月份的第一天開始累加該天數(shù)。例如,在單元格中輸入“=DATE(2011,1,38)”,將返回“2011年2月7日”。如果day小于1,則day從指定月份的第一天開始遞減該天數(shù),再加上1天。例如,在單元格中輸入“=DATE(2008,1,-15)”,將返回“2007年12月16日”。3.2.2

DATE和YEAR、MONTH、DAY函數(shù)【例3-1】如圖3-4所示,數(shù)據(jù)內(nèi)容為人員入職信息表中部分人員的入職年份、月份和日期,要求:在E列相應(yīng)位置顯示員工的入職時(shí)間。

圖3-43.2.2

DATE和YEAR、MONTH、DAY函數(shù)【操作方法】單擊E8單元格,在編輯欄輸入“=DATE(B8,C8,D8)”,按“Enter”鍵,此時(shí)E8單元格顯示結(jié)果為“2010/7/31”。用填充柄向下拉,可以在E列填充入職時(shí)間。若將E8單元格格式改為“數(shù)值”,如圖3-5所示,可以看到E8單元格中將顯示“2010/7/31”的序列號(hào)“40390”。

圖3-53.2.2

DATE和YEAR、MONTH、DAY函數(shù)YEAR函數(shù)能夠返回給定日期對(duì)應(yīng)的年份數(shù)值。其語法為YEAR(serial_number)。serial_number為給定的日期,可以是日期,日期的序列號(hào),單元格引用或結(jié)果為日期、日期序列號(hào)的函數(shù)或公式?！纠?-2】如圖3-6所示,數(shù)據(jù)內(nèi)容為人員入職信息表中部分人員的入職時(shí)間,要求在C列相應(yīng)位置顯示人員的入職年份。

圖3-63.2.2

DATE和YEAR、MONTH、DAY函數(shù)【操作方法】單擊C2單元格,在編輯欄輸入“=YEAR(B2)”,按“Enter”鍵,此時(shí)C2單元格顯示結(jié)果為2010。用填充柄向下拉,可以在C列填充入職年份。與YEAR函數(shù)類似,MONTH和DAY函數(shù)都能夠返回給定日期對(duì)應(yīng)的月份數(shù)值和日數(shù)值,在此不再贅述。3.3數(shù)學(xué)和三角函數(shù)3.3.1匯總函數(shù)、條件匯總函數(shù)與多條件匯總函數(shù)1.匯總函數(shù)匯總函數(shù)SUM的功能是計(jì)算單元格區(qū)域中所有數(shù)值的和,其語法為SUM(number1,[number2],…)。number為要求和的數(shù)值,系統(tǒng)允許設(shè)置1~255個(gè)。number可以是輸入的數(shù)據(jù),也可以是單元格引用、數(shù)組或結(jié)果為數(shù)值的公式。輸入的參數(shù)中,數(shù)值文本能夠被系統(tǒng)智能地視為數(shù)值型;邏輯型TRUE會(huì)被視為1,FALSE會(huì)被視為0;日期時(shí)間型會(huì)被系統(tǒng)按照日期或時(shí)間的序列數(shù)進(jìn)行運(yùn)算;非數(shù)值文本則報(bào)錯(cuò)“#NAME?”。因此,除了非數(shù)值文本外,參數(shù)都可以使用SUM函數(shù)進(jìn)行計(jì)算。如果參數(shù)為數(shù)組或單元格引用,則只有其中的數(shù)值被計(jì)算,數(shù)組或引用中的空白單元格、邏輯值或文本將被忽略,但包含零值的單元格將被計(jì)算在內(nèi),如果引用的單元格中不包含數(shù)值則返回0。3.3.1匯總函數(shù)、條件匯總函數(shù)與多條件匯總函數(shù)2.條件匯總函數(shù)條件匯總函數(shù)SUMIF的功能是對(duì)滿足條件的單元格求和,且將條件應(yīng)用于某一區(qū)域,并對(duì)其他區(qū)域中的對(duì)應(yīng)值求和。其語法為SUMIF(range,criteria,[sum_range])。range為給定的單元格區(qū)域,可以是數(shù)值、數(shù)值文本、數(shù)組和單元格引用,空值或非數(shù)值文本將被忽略。criteria為給定的判斷條件,可以是數(shù)值、表達(dá)式、單元格引用、文本或函數(shù)。任何文本條件或任何含有邏輯符號(hào)或運(yùn)算符的條件都必須使用雙引號(hào),如果條件為數(shù)值,則無須使用雙引號(hào)。例如,條件可以表示為27、“27”、“>=27”、C5、“太陽集團(tuán)”或TODAY()。在條件中還可以使用通配符,即問號(hào)(?)和星號(hào)(*)。問號(hào)匹配任意單個(gè)字符;星號(hào)匹配任意字符序列。如果要表示實(shí)際的問號(hào)或星號(hào),則在字符前鍵入波形符(~)。如果條件中的單元格為空,函數(shù)會(huì)將其視為0值。3.3.1匯總函數(shù)、條件匯總函數(shù)與多條件匯總函數(shù)sum_range為實(shí)際求和區(qū)域。sum_range缺省,則函數(shù)對(duì)range所給定的單元格(即條件單元格)求和。sum_range參數(shù)與range參數(shù)的大小和形狀可以不同,需要求和的實(shí)際單元格通過以下方法確定:使用sum_range參數(shù)中左上角的單元格作為起始單元格,包括與range參數(shù)大小和形狀相對(duì)應(yīng)的單元格,如表3-2所示。3.3.1匯總函數(shù)、條件匯總函數(shù)與多條件匯總函數(shù)【例3-3】如圖3-7所示,A1至C9單元格區(qū)域顯示的是某公司主營業(yè)務(wù)收入和其他業(yè)務(wù)收入的明細(xì)表。要求:計(jì)算出銷售額大于10000元的收入之和,并填入C11單元格。

圖3-73.3.1匯總函數(shù)、條件匯總函數(shù)與多條件匯總函數(shù)【操作方法】第一步,單擊需要顯示結(jié)果的C11單元格;第二步,輸入“=SUMIF(C2:C9,">10000")”,按“Enter”鍵確認(rèn)。本例中“=SUMIF(C2:C9,">10000")”表示在C2至C9單元格區(qū)域中,計(jì)算數(shù)值大于10000元的收入和,其中,“C2:C9”代表需要計(jì)算的單元格區(qū)域;“">10000"”代表判定條件為數(shù)值大于10000。由于判定條件中包含邏輯符號(hào),因此,需要使用雙引號(hào)。3.3.1匯總函數(shù)、條件匯總函數(shù)與多條件匯總函數(shù)【例3-4】如圖3-8所示,A1至C9單元格區(qū)域顯示的是某公司主營業(yè)務(wù)收入和其他業(yè)務(wù)收入的明細(xì)表。要求:計(jì)算出收入類型為主營業(yè)務(wù)收入的所有產(chǎn)品銷售額之和,并填入C11單元格。

圖3-83.3.1匯總函數(shù)、條件匯總函數(shù)與多條件匯總函數(shù)【操作方法】第一步,單擊需要顯示結(jié)果的C11單元格;第二步,輸入“=SUMIF(A2:A9,"主營業(yè)務(wù)收入",C2:C9)”,按“Enter”鍵確認(rèn)。本例中,“=SUMIF(A2:A9,"主營業(yè)務(wù)收入",C2:C9)”表示當(dāng)A2至A9單元格區(qū)域內(nèi)容為“主營業(yè)務(wù)收入”時(shí),對(duì)C2至C9區(qū)域內(nèi)的對(duì)應(yīng)值求和。判定條件為文本型格式,因此,需要使用雙引號(hào)。3.3.1匯總函數(shù)、條件匯總函數(shù)與多條件匯總函數(shù)3.多條件匯總函數(shù)多條件匯總函數(shù)SUMIFS用于對(duì)一組給定條件的單元格求和,即計(jì)算滿足多個(gè)條件的參數(shù)的總和。其語法為SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],…)。sum_range為要求和的單元格區(qū)域。criteria_range1為criteria1的測(cè)試區(qū)域,即criteria_range1和criteria1是用于搜索某個(gè)區(qū)域是否符合特定條件的搜索對(duì)。一旦在該區(qū)域中找到了符合特定條件的項(xiàng)目,將計(jì)算sum_range區(qū)域中的相應(yīng)值的和。3.3.1匯總函數(shù)、條件匯總函數(shù)與多條件匯總函數(shù)【例3-5】如圖3-9所示,A25至C33單元格區(qū)域顯示的是某公司主營業(yè)務(wù)收入和其他業(yè)務(wù)收入的明細(xì)表。要求:計(jì)算主營業(yè)務(wù)收入下,由張明銷售的所有產(chǎn)品銷售額之和,填入D36單元格。

圖3-93.3.1匯總函數(shù)、條件匯總函數(shù)與多條件匯總函數(shù)【操作方法】第一步,單擊需要顯示結(jié)果的D36單元格;第二步,輸入“=SUMIFS(D26:D33,A26:A33,"主營業(yè)務(wù)收入",C26:C33,"張明")”,按“Enter”鍵確認(rèn)。本例中,“=SUMIFS(D26:D33,A26:A33,"主營業(yè)務(wù)收入",C26:C33,"張明")”表示求A26至A33單元格區(qū)域內(nèi)為“主營業(yè)務(wù)收入”,且C26至C33單元格區(qū)域內(nèi)為“張明”時(shí),D26至D33單元格對(duì)應(yīng)的數(shù)值之和。其中,D26:D33表示求和區(qū)域;A26:A33表示第一個(gè)條件的測(cè)試區(qū)域;"主營業(yè)務(wù)收入"表示第一個(gè)測(cè)試條件;C26:C33表示第二個(gè)條件的測(cè)試區(qū)域;"張明"表示第二個(gè)測(cè)試條件。3.3.2乘積函數(shù)與乘積和函數(shù)1.乘積函數(shù)乘積函數(shù)PRODUCT的功能是返回給定參數(shù)的乘積,其語法為PRODUCT(number1,[number2],…)。number為乘數(shù),允許設(shè)置1~255個(gè),可以是單元格引用或數(shù)組,也可以是結(jié)果為數(shù)值的公式。例如,編輯輸入“=PRODUCT(A1:A5,4,C4)”時(shí),系統(tǒng)將返回“A1*A2*A3*A4*A5*4*C4”的值。3.3.2乘積函數(shù)與乘積和函數(shù)2.乘積和函數(shù)乘積和函數(shù)SUMPRODUCT的功能是返回給定數(shù)組中對(duì)應(yīng)數(shù)值的乘積和,其語法為SUMPRODUCT(array1,array2,[array3],…)。array為需要計(jì)算元素乘積和的數(shù)組,允許設(shè)置2~255個(gè)。數(shù)組參數(shù)必須具有相同的范圍,否則函數(shù)將返回錯(cuò)誤值“#VALUE!”,例如,輸入=SUMPRODUCT(C1:C10,D2:D7),由于范圍大小不同,系統(tǒng)將返回錯(cuò)誤提示。另外,系統(tǒng)會(huì)將非數(shù)值型的數(shù)組元素作為0處理。SUMPRODUCT函數(shù)有以下幾種常用方法:(1)求幾個(gè)區(qū)域內(nèi)所有對(duì)應(yīng)元素乘積和:SUMPRODUCT(區(qū)域1*區(qū)域2*區(qū)域3…)。(2)求區(qū)域內(nèi)所有元素的和:SUMPRODUCT(區(qū)域)。(3)多條件計(jì)數(shù):SUMPRODUCT((條件1)*(條件2)*(條件3)…)。(4)多條件求和:SUMPRODUCT((條件1)*(條件2)*(條件3)*…*求和區(qū)域)。3.3.2乘積函數(shù)與乘積和函數(shù)【例3-6】如圖3-10所示,A2至E20單元格區(qū)域?yàn)槟硢挝皇程檬杖朊骷?xì)表。要求:計(jì)算三個(gè)食堂學(xué)期內(nèi)普通餐(飯卡)收入,填入A23單元格。圖3-103.3.2乘積函數(shù)與乘積和函數(shù)【操作方法】第一步,選中需要顯示結(jié)果的A23單元格;第二步,輸入“=SUMPRODUCT((C3:C20="學(xué)期內(nèi)")*(B3:B20="飯卡")*D3:D20*E3:E20)”,按“Enter”鍵確認(rèn)。本例中,“=SUMPRODUCT((C3:C20="學(xué)期內(nèi)")*(B3:B20="飯卡")*D3:D20*E3:E20)”表示當(dāng)C3至C20區(qū)域內(nèi)容為“學(xué)期內(nèi)”,且B3至B20區(qū)域內(nèi)容為“飯卡”時(shí),返回D3至D20單元格區(qū)域與E3至E20單元格區(qū)域內(nèi)對(duì)應(yīng)元素的乘積和。其中,(C3:C20="學(xué)期內(nèi)")代表?xiàng)l件1;(B3:B20="飯卡")代表?xiàng)l件2;D3:D20代表需計(jì)算的區(qū)域1;E3:E20代表需計(jì)算的區(qū)域2。3.3.3舍入函數(shù)1.ROUND函數(shù)ROUND函數(shù)能夠?qū)?shù)字四舍五入到指定位數(shù),其語法為ROUND(number,num_digits)。number為要四舍五入的數(shù)字;num_digits為要進(jìn)行四舍五入運(yùn)算的位數(shù)。【例3-7】如圖3-11所示,B2至F2單元格區(qū)域?yàn)?015—2019年某公司A產(chǎn)品的銷售收入?，F(xiàn)對(duì)B2至F2單元格內(nèi)數(shù)據(jù)四舍五入并保留兩位小數(shù),將結(jié)果填列在B3至F3單元格內(nèi)。圖3-113.3.3舍入函數(shù)【操作方法】第一步,單擊B3單元格;第二步,輸入函數(shù)“=ROUND(B2,2)”;第三步,將鼠標(biāo)放在B3單元格右下角,使用填充柄功能,向右拉至F3單元格,即將函數(shù)填充至F3單元格(如圖3-12所示)。ROUNDDOWN函數(shù)的功能是朝著靠近零值的方向向下舍入數(shù)字,其語法為ROUNDDOWN(number,num_digits)。number為需要向下舍入的數(shù)字;num_digits為要將數(shù)字舍入到的位數(shù)。圖3-123.3.3舍入函數(shù)【例3-8】在前文ROUND函數(shù)示例的基礎(chǔ)上,將圖3-11中B2至F2單元格內(nèi)數(shù)據(jù)向下舍入并保留兩位小數(shù),將結(jié)果填列在B4至F4單元格區(qū)域內(nèi)。圖3-133.3.3舍入函數(shù)【操作方法】第一步,單擊B4單元格;第二步,輸入函數(shù)“=ROUNDDOWN(B2,2)”;第三步,將鼠標(biāo)放在B4單元格右下角,使用填充柄功能,向右拉至F4單元格,即將函數(shù)填充至F4單元格(如圖3-13所示)。ROUNDDOWN(B2,2)代表將B2單元格中的數(shù)字進(jìn)行向下舍入運(yùn)算,保留2位小數(shù)。對(duì)比“=ROUND(B2,2)”和“=ROUNDDOWN(B2,2)”的結(jié)果可以看出,與ROUND函數(shù)不同,ROUNDDOWN函數(shù)在進(jìn)行運(yùn)算時(shí),無論下一位數(shù)字是否大于等于5,均做向下舍入運(yùn)算。3.3.3舍入函數(shù)3.ROUNDUP函數(shù)ROUNDUP函數(shù)的功能是朝著遠(yuǎn)離零值的方向向上舍入數(shù)字,其語法為ROUNDUP(number,num_digits)。number為需要向上舍入的數(shù)字;num_digits為要將數(shù)字舍入到的位數(shù)。ROUNDUP函數(shù)的具體操作與ROUNDDOWN函數(shù)相同,在此不再贅述。3.3.3舍入函數(shù)4.INT函數(shù)INT函數(shù)的功能是將數(shù)字向下舍入到最接近的整數(shù),其語法為INT(number)。number為需要向下舍入取整的數(shù)字?！纠?-9】在前文ROUNDDOWN示例的基礎(chǔ)上,將B2至F2單元格內(nèi)數(shù)據(jù)向下舍入至最近整數(shù),將結(jié)果填列在B5至F5單元格內(nèi)。3.3.3舍入函數(shù)【操作方法】第一步,單擊B5單元格;第二步,輸入“=INT(B2)”;第三步,將鼠標(biāo)放在B5單元格右下角,使用填充柄功能,向右拉至F5單元格,即將函數(shù)填充至F5單元格(如圖3-14所示)。TRUNC函數(shù)的功能是截取數(shù)字,其語法為TRUNC(number,[num_digits])。number為需要截尾取整的數(shù)字;參數(shù)num_digits為指定取整精度的數(shù)字;num_digits為選填參數(shù),默認(rèn)值為0,當(dāng)num_digits為0時(shí),截取number的整數(shù)部分,當(dāng)num_digits為1時(shí),截取至number的第一位小數(shù),以此類推。圖3-143.3.3舍入函數(shù)【例3-10】在前文INT函數(shù)示例的基礎(chǔ)上,將B2至F2單元格內(nèi)數(shù)據(jù)截取整數(shù),將結(jié)果填列在B6至F6單元格區(qū)域內(nèi);將B2至F2單元格內(nèi)數(shù)據(jù)截取至第三位小數(shù),將結(jié)果填列在B7至F7單元格區(qū)域內(nèi)?！静僮鞣椒ā康谝徊?單擊B6單元格;第二步,輸入“=TRUNC(B2)”;第三步,將鼠標(biāo)放在B6單元格右下角,使用填充柄功能,向右拉至F6單元格,即將函數(shù)填充至F6單元格(如圖3-15所示)。圖3-153.3.3舍入函數(shù)繼續(xù)進(jìn)行第7行的操作:第一步,單擊B7單元格;第二步,輸入“=TRUNC(B2,3)”;第三步,將鼠標(biāo)放在B7單元格右下角,使用填充柄功能,向右拉至F7單元格,即將函數(shù)填充至F7單元格(如圖3-16所示)。由結(jié)果可知,TRUNC函數(shù)與RUOND函數(shù)的不同在于:TRUNC僅對(duì)數(shù)據(jù)進(jìn)行截取,而不進(jìn)行四舍五入的運(yùn)算。圖3-163.3.3舍入函數(shù)下面給出一個(gè)使用舍入函數(shù)的綜合實(shí)例。【例3-11】圖3-17為使用Excel制作的差旅費(fèi)報(bào)銷單,“報(bào)銷總額”欄后的S14單元格通過SUM函數(shù)能夠計(jì)算得出交通費(fèi)金額、伙食補(bǔ)助的金額和其他補(bǔ)助的金額合計(jì)數(shù)。要求:制作報(bào)銷總額的中文大寫。圖3-173.3.3舍入函數(shù)【操作方法】第一步,將單位“元”“角”“分”分別輸入在合并單元格M14-M15、O14-O15、Q14-Q15當(dāng)中,此時(shí)合并單元格G14-L15表示報(bào)銷總額的整數(shù)部分,合并單元格N14-N15表示報(bào)銷總額的第一位小數(shù),合并單元格P14-P15表示報(bào)銷總額的第二位小數(shù)。第二步,在合并單元格G14-L15中輸入“=TRUNC(S14,0)”,表示截取S14單元格中數(shù)字的整數(shù)部分。第三步,在合并單元格N14-N15中輸入“=TRUNC(S14,1)*10-TRUNC(S14,0)*10”。其中,“TRUNC(S14,1)”表示截取S14單元格中的數(shù)字并保留一位小數(shù),此時(shí)得到數(shù)據(jù)“876.6”;TRUNC(S14,1)*10能夠得到數(shù)據(jù)“8766”;“TRUNC(S14,0)*10”表示截取S14單元格中數(shù)據(jù)的整數(shù)部分并擴(kuò)大十倍,此時(shí)得到數(shù)據(jù)“8760”;因此,通過公式“=TRUNC(S14,1)*10-TRUNC(S14,0)*10”能夠單獨(dú)得到第一位小數(shù)。3.3.3舍入函數(shù)第四步,在合并單元格P14-P15中輸入“=TRUNC(S14,2)*100-TRUNC(S14,1)*100”。與第三步同理,得到S14單元格數(shù)據(jù)的第二位小數(shù),如圖3-18所示。圖3-183.3.3舍入函數(shù)第五步,設(shè)置單元格格式為“數(shù)字-特殊-中文大寫數(shù)字”,如圖3-19所示。圖3-193.4統(tǒng)計(jì)函數(shù)3.4.1均值函數(shù)、條件均值函數(shù)和多條件均值函數(shù)1.均值函數(shù)均值函數(shù)AVERAGE的功能是返回給定參數(shù)的算術(shù)平均值,其語法為AVERAGE(number1,[number2],…)。number表示要計(jì)算算數(shù)平均值的數(shù)字,可以為數(shù)字、單元格引用或單元格區(qū)域,允許設(shè)置1~255個(gè)。如果參數(shù)為數(shù)組或引用,則只有其中的數(shù)值將被計(jì)算,數(shù)組或引用中的空白單元格、邏輯值或文本將被忽略,但包含零值的單元格將被計(jì)算在內(nèi),如果引用中不包含數(shù)值則報(bào)錯(cuò)。3.4.1均值函數(shù)、條件均值函數(shù)和多條件均值函數(shù)2.條件均值函數(shù)和多條件均值函數(shù)與匯總函數(shù)SUM、條件匯總函數(shù)SUMIF和多條件匯總函數(shù)SUMIFS的關(guān)系類似,Excel除了內(nèi)置均值函數(shù)AVERAGE外,還有條件均值函數(shù)AVERAGEIF和多條件均值函數(shù)AVERAGEIFS。條件均值函數(shù)AVERAGEIF的功能是返回某個(gè)區(qū)域內(nèi)滿足給定條件的所有單元格的算術(shù)平均值,其語法為AVERAGEIF(range,criteria,[average_range]);多條件均值函數(shù)AVERAGEIFS的功能是返回滿足多個(gè)條件的所有單元格的算術(shù)平均值,其語法為AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2,criteria2],…)。AVERAGEIF和AVERAGEIFS函數(shù)的使用方法與SUMIF和SUMIFS函數(shù)的使用方法類似。3.4.1均值函數(shù)、條件均值函數(shù)和多條件均值函數(shù)【例3-12】如圖3-20所示,A1至E10單元格區(qū)域?yàn)槟彻井a(chǎn)品的部分信息。要求:計(jì)算保修期至少三年且有贈(zèng)品的洗衣機(jī)的平均價(jià)格,并將結(jié)果填入E12單元格。圖3-203.4.1均值函數(shù)、條件均值函數(shù)和多條件均值函數(shù)【操作方法】第一步,單擊需要顯示結(jié)果的單元格,即E12單元格;第二步,輸入“=AVERAGEIFS(B2:B10,C2:C10,"洗衣機(jī)",D2:D10,">=3",E2:E10,"是")”,按“Enter”鍵。本例中,測(cè)試條件超過1個(gè),因此選擇AVERAGEIFS函數(shù)?！?AVERAGEIFS(B2:B10,C2:C10,"洗衣機(jī)",D2:D10,">=3",E2:E10,"是")”代表當(dāng)C2至C10單元格區(qū)域內(nèi)容為“洗衣機(jī)”、D2至D10單元格區(qū)域數(shù)值不小于3、E2至E10單元格區(qū)域內(nèi)容為“是”時(shí),計(jì)算B2至B10對(duì)應(yīng)單元格內(nèi)數(shù)值的算術(shù)平均值。其中,B2:B10代表計(jì)算區(qū)域;C2:C10代表測(cè)試區(qū)域1;"洗衣機(jī)"代表測(cè)試條件1;D2:D10代表測(cè)試區(qū)域2;">=3"代表測(cè)試條件2;E2:E10代表測(cè)試區(qū)域3;"是"代表測(cè)試條件3。3.4.2單元格統(tǒng)計(jì)函數(shù)、條件單元格統(tǒng)計(jì)函數(shù)和多條件單元格統(tǒng)計(jì)函數(shù)1.單元格統(tǒng)計(jì)函數(shù)單元格統(tǒng)計(jì)函數(shù)COUNT的功能是計(jì)算包含數(shù)字的單元格個(gè)數(shù)以及參數(shù)列表中包含數(shù)字的個(gè)數(shù),其語法為:COUNT(value1,[value2],…)。value表示給定的一組參數(shù),允許設(shè)置1~255個(gè),可以是輸入的一組數(shù)據(jù),也可以是單元格引用或數(shù)組。這些參數(shù)可以包含或引用各種類型的數(shù)據(jù),但只有數(shù)字類型的數(shù)據(jù)才被計(jì)算在內(nèi)。若要計(jì)算邏輯值、文本值或錯(cuò)誤值的個(gè)數(shù),可以使用COUNT函數(shù)。3.4.2單元格統(tǒng)計(jì)函數(shù)、條件單元格統(tǒng)計(jì)函數(shù)和多條件單元格統(tǒng)計(jì)函數(shù)2.條件單元格統(tǒng)計(jì)函數(shù)和多條件單元格統(tǒng)計(jì)函數(shù)與匯總函數(shù)SUM、條件匯總函數(shù)SUMIF和多條件匯總函數(shù)SUMIFS的關(guān)系類似,Excel除了內(nèi)置單元格統(tǒng)計(jì)函數(shù)COUNT外,還有條件單元格統(tǒng)計(jì)函數(shù)COUNTIF和多條件單元格統(tǒng)計(jì)函數(shù)COUNTIFS。條件單元格統(tǒng)計(jì)函數(shù)COUNTIF的功能是返回滿足某個(gè)條件的單元格的數(shù)量,其語法為COUNTIF(range,criteria);多條件單元格統(tǒng)計(jì)函數(shù)COUNTIFS的功能是返回滿足多個(gè)條件的所有單元格的數(shù)量,其語法為COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2],…)。要注意的是,COUNTIFS函數(shù)中,每個(gè)測(cè)試條件區(qū)域都必須與criteria_range1具有相同的行數(shù)和列數(shù)。COUNTIF和COUNTIFS函數(shù)的使用方法與SUMIF和SUMIFS函數(shù)的使用方法類似。3.4.2單元格統(tǒng)計(jì)函數(shù)、條件單元格統(tǒng)計(jì)函數(shù)和多條件單元格統(tǒng)計(jì)函數(shù)【例3-13】如圖3-21所示,A1至E10單元格區(qū)域?yàn)槟彻井a(chǎn)品的部分信息。要求:計(jì)算保修期至少三年且有贈(zèng)品的洗衣機(jī)數(shù)量,并將結(jié)果填入E13單元格。圖3-213.4.2單元格統(tǒng)計(jì)函數(shù)、條件單元格統(tǒng)計(jì)函數(shù)和多條件單元格統(tǒng)計(jì)函數(shù)【操作方法】本例要求的測(cè)試條件超過1個(gè),因此選擇COUNTIFS函數(shù)。第一步,單擊需要顯示結(jié)果的E13單元格;第二步,輸入“=COUNTIFS(C2:C10,"洗衣機(jī)",D2:D10,">=3",E2:E10,"是")”,按“Enter”鍵確認(rèn)。這一函數(shù)表示當(dāng)C2:C10單元格區(qū)域內(nèi)容為“洗衣機(jī)”、D2:D10單元格區(qū)域數(shù)值不小于3且E2:E10單元格區(qū)域內(nèi)容為“是”時(shí),計(jì)算B2:B10對(duì)應(yīng)單元格的個(gè)數(shù)。其中,C2:C10代表測(cè)試區(qū)域1;"洗衣機(jī)"代表測(cè)試條件1;D2:D10代表測(cè)試區(qū)域2;">=3"代表測(cè)試條件2;E2:E10代表測(cè)試區(qū)域3;"是"代表測(cè)試條件3。3.4.3排位函數(shù)1.MAX函數(shù)和MIN函數(shù)MAX函數(shù)的功能是返回一組值中的最大值,其語法為MAX(number1,[number2],…);MIN函數(shù)的功能是返回一組值中的最小值,其語法為MIN(number1,[number2],…)。這兩個(gè)函數(shù)非常相似。number1是必填參數(shù),后續(xù)數(shù)字是可選的,允許設(shè)置1~255個(gè)參數(shù)。【例3-14】如圖3-22所示,A1至E10單元格區(qū)域?yàn)槟彻井a(chǎn)品的部分信息。要求:(1)找到所有產(chǎn)品的最高價(jià)格并填入D12單元格;(2)找到所有產(chǎn)品的最低價(jià)格并填入D13單元格。圖3-223.4.3排位函數(shù)2.LARGE函數(shù)和SMALL函數(shù)LARGE函數(shù)的功能是返回給定參數(shù)中第k個(gè)最大值,其語法為LARGE(array,k);SMALL函數(shù)的功能是返回給定參數(shù)中第k個(gè)最小值,其語法為SMALL(array,k)。這兩個(gè)函數(shù)非常相似。array代表給定的數(shù)組或數(shù)據(jù)區(qū)域。如果數(shù)組為空,則函數(shù)返回錯(cuò)誤值“#NUM!”。k為返回值在數(shù)組或數(shù)據(jù)區(qū)域中的排名(由小到大排列)。如果k≤0,或k大于數(shù)據(jù)區(qū)域中的參數(shù)個(gè)數(shù),則函數(shù)返回錯(cuò)誤值“#NUM!”。3.4.3排位函數(shù)【例3-15】如圖3-23所示,A1至E10單元格區(qū)域?yàn)槟彻井a(chǎn)品的部分信息。要求:(1)將9種產(chǎn)品價(jià)格從大到小排序,在D14單元格填入排名第三的價(jià)格;(2)將9種產(chǎn)品價(jià)格從小到大排序,在D15單元格填入排名第四的價(jià)格。圖3-233.4.3排位函數(shù)【操作方法】第一步,單擊需要顯示結(jié)果的D14單元格;第二步,輸入“=LARGE(B2:B10,3)”并按“Enter”鍵確認(rèn);第三步,單擊需要顯示結(jié)果的D15單元格并輸入“=SMALL(B2:B10,4)”,按“Enter”鍵確認(rèn)。3.4.3排位函數(shù)3.RANK函數(shù)RANK函數(shù)的功能是返回給定參數(shù)在列表中的排位,其語法為RANK(number,ref,[order])。number為給定的列表中需要排位的數(shù)值。可以是單元格引用,也可以輸入相應(yīng)的數(shù)值型數(shù)據(jù)。ref為數(shù)字列表,number必須在其中。ref中的非數(shù)值型數(shù)據(jù)將被忽略。order為排位方式。若order為零或缺省,則系統(tǒng)按降序排位;若order不為零,則系統(tǒng)按升序排位。需要注意的是,RANK函數(shù)對(duì)重復(fù)數(shù)值的排位相同,但重復(fù)數(shù)值的存在將影響后續(xù)數(shù)值的排位。3.4.3排位函數(shù)【例3-16】如圖3-24所示,A1至E10單元格區(qū)域?yàn)槟彻井a(chǎn)品的部分信息。要求:(1)將9種產(chǎn)品價(jià)格按升序排列,在D16單元格填入D產(chǎn)品的價(jià)格排位次序;(2)將9種產(chǎn)品價(jià)格按降序排列,在D17單元格填入D產(chǎn)品的價(jià)格排位次序。圖3-243.4.3排位函數(shù)【操作方法】第一步,單擊需要顯示結(jié)果的D16單元格;第二步,輸入“=RANK(B5,B2:B10,1)”并按“Enter”鍵確認(rèn);第三步,單擊需要顯示結(jié)果的D17單元格,輸入“=RANK(B5,B2:B10)”并按“Enter”鍵確認(rèn)。本例中,在將9種產(chǎn)品價(jià)格按升序排列,并求D產(chǎn)品價(jià)格的排位次序時(shí),order參數(shù)寫為1;在將9種產(chǎn)品價(jià)格按降序排列,并求D產(chǎn)品價(jià)格的排位次序時(shí),order參數(shù)可以為0,也可缺省。3.5邏輯函數(shù)邏輯函數(shù)Excel2019內(nèi)置的邏輯函數(shù)共有11個(gè),本書針對(duì)財(cái)務(wù)人員常用的條件函數(shù)進(jìn)行詳細(xì)講解。條件函數(shù)IF的功能是根據(jù)給定條件進(jìn)行邏輯判斷并返回給定結(jié)果,其語法為IF(logical_test,value_if_true,[value_if_false])。logical_test為用以進(jìn)行判斷的邏輯表達(dá)式。value_if_true為當(dāng)logical_test結(jié)果為真(TRUE)時(shí)返回的值。若value_if_true為空,則logical_test為TRUE時(shí)返回0。value_if_false為當(dāng)logical_test結(jié)果為假(FALSE)時(shí)返回的值。若value_if_false缺省,即value_if_true后沒有逗號(hào),則logical_test為FALSE時(shí)返回邏輯型數(shù)據(jù)FALSE;若為空,即value_if_true后有逗號(hào),則logical_test為FALSE時(shí)返回0。例如,IF(A1="財(cái)務(wù)部門",1,2)表示如果A1單元格的內(nèi)容是“財(cái)務(wù)部門”,則IF函數(shù)返回1,否則返回2。value_if_true和value_if_false可以是函數(shù)或公式,也可以是函數(shù)和公式的嵌套。關(guān)于嵌套,Excel默認(rèn)最多可以使用64個(gè)IF函數(shù)作為value_if_true和value_if_false進(jìn)行嵌套。如果參數(shù)包含數(shù)組,則在運(yùn)算執(zhí)行時(shí)數(shù)組中的每一個(gè)元素都將計(jì)算。邏輯函數(shù)【例3-17】如圖3-25所示,A1至D4單元格區(qū)域?yàn)槟彻静糠猪?xiàng)目預(yù)算執(zhí)行情況。要求:如果實(shí)際金額大于預(yù)算金額,則剩余預(yù)算顯示0;如果實(shí)際預(yù)算小于預(yù)算金額,則剩余預(yù)算顯示預(yù)算金額與實(shí)際金額的差。圖3-25邏輯函數(shù)【操作方法】第一步,選中D2單元格;第二步,輸入“=IF(C2>B2,0,B2-C2)”并按“Enter”鍵確認(rèn);第三步,將光標(biāo)移至D2單元格右下角,當(dāng)光標(biāo)顯示為十字形即呈填充柄狀態(tài)時(shí),拖動(dòng)鼠標(biāo)拉至D4單元格。本例中,“=IF(C2>B2,0,B2-C2)”代表如果C2單元格數(shù)值大于B2單元格數(shù)值,則返回0,否則返回B2單元格與C2單元格數(shù)值的差。單元格引用采用相對(duì)引用,可以直接使用填充柄完成D3和D4單元格的填充。邏輯函數(shù)【例3-18】如圖3-26所示,A1至C6單元格區(qū)域?yàn)槟彻句N售人員績效評(píng)價(jià)表的部分內(nèi)容。要求:當(dāng)本月銷售額大于等于200000元時(shí),銷售人員績效評(píng)價(jià)為A級(jí);當(dāng)本月銷售額小于20000元而大于等于100000元時(shí),銷售人員績效評(píng)價(jià)為B級(jí);當(dāng)本月銷售額小于100000元時(shí),銷售人員績效評(píng)價(jià)為C級(jí)。按照這樣的評(píng)價(jià)規(guī)則,將本月銷售人員績效評(píng)價(jià)結(jié)果顯示在“績效評(píng)價(jià)”那一列。圖3-26邏輯函數(shù)【操作方法】第一步,選中C2單元格;第二步,輸入“=IF(B2>=200000,"A",(IF(B2>=100000,"B","C")))”并按“Enter”鍵確認(rèn);第三步,將光標(biāo)移至C2單元格右下角,當(dāng)光標(biāo)顯示為十字形即呈填充柄狀態(tài)時(shí),拖動(dòng)鼠標(biāo)拉至C6單元格。本例中,“=IF(B2>=200000,"A",(IF(B2>=100000,"B","C")))”為嵌套的IF函數(shù),代表當(dāng)B2單元格數(shù)值大于等于200000時(shí),返回A;否則判斷B2單元格數(shù)值是否大于等于100000,如果B2單元格數(shù)值大于等于100000,則返回B;否則意味著B2單元格數(shù)值小于100000,此時(shí)返回C。3.6查找與引用函數(shù)邏輯函數(shù)列查找函數(shù)VLOOKUP的功能是搜索表區(qū)域內(nèi)首列滿足條件的元素,確定待檢索單元格在區(qū)域中的行序號(hào),再進(jìn)一步返回選定單元格的值,其語法為VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)。lookup_value為要在數(shù)據(jù)表第一行中查找的數(shù)值,可以是數(shù)值、文本、邏輯值或引用。table_array為查找值所在的區(qū)域。col_index_num為table_array中待返回的匹配值的列序號(hào)。col_index_num為1時(shí),返回table_array第一列的數(shù)值;col_index_num為2時(shí),返回table_array第二列的數(shù)值,以此類推。如果col_index_num小于1,函數(shù)返回錯(cuò)誤值“#VALUE!”;如果col_index_num大于table_array的行數(shù),函數(shù)返回錯(cuò)誤值“#REF!”。range_lookup是一個(gè)邏輯值,用