EXCEL在會(huì)計(jì)中應(yīng)用,公式及幾個(gè)常用函數(shù)

《Excel在會(huì)計(jì)中的應(yīng)用》—函數(shù)的使用首頁(yè)引入案例—如何進(jìn)行工資處理？2021/6/282引入案例—工資計(jì)算公式2021/6/283本講內(nèi)容一、EXCEL公式的構(gòu)成與單元格的引用二、EXCEL函數(shù)的構(gòu)成三、EXCEL函數(shù)的使用四、SUM、AND、IF、SUMIF四個(gè)常用函數(shù)五、公式與函數(shù)使用實(shí)例—工資處理2021/6/284一、Excel公式的構(gòu)成1、公式必須以“=”等號(hào)開(kāi)始2、公式是由單元格引用、運(yùn)算符、數(shù)值、字符、函數(shù)等組成的能進(jìn)行計(jì)算的式子3、公式中使用的均為英文狀態(tài)下的符號(hào)

如：+非＋*非×

,非，()非（）

""非“”舉例：C6公式“=C3+C4+C5-2+sum(b3:b6)”

B6公式“=sum(B2:B5)”2021/6/285一、Excel公式的構(gòu)成1.單元格引用是指公式中輸入單元格地址時(shí)，表示該單元格中的內(nèi)容參加運(yùn)算。當(dāng)其中的數(shù)據(jù)發(fā)生變化時(shí)，公式則自動(dòng)重新計(jì)算。2.運(yùn)算符有算術(shù)運(yùn)算符（+-*/%^()

）、字符運(yùn)算符（&表示連接合并）、比較運(yùn)算符（=>>=<<=<>）三類。3.函數(shù)函數(shù)式一個(gè)預(yù)先定義好的特定計(jì)算公式，對(duì)一個(gè)或多個(gè)參數(shù)進(jìn)行計(jì)算并得出一個(gè)或多個(gè)計(jì)算結(jié)果，此結(jié)果叫做函數(shù)值。2021/6/286一、Excel公式的構(gòu)成—舉例：K3公式：=P3*50J3公式：=SUM(E3:G3)-H3A5公式：=10^2結(jié)果：1001.算術(shù)運(yùn)算如果A2=10，則公式“=A2>20”的返回值為FALSE,而公式“=A2<20”的返回值為T(mén)RUE3.比較運(yùn)算公式：="河北省"&"邢臺(tái)市"

結(jié)果：河北省邢臺(tái)市

如果B2中有字符串"工資"

，C2="計(jì)算表"則公式：=B2&"的"

&C2結(jié)果為？2.字符運(yùn)算2021/6/287一、Excel公式—單元格的引用公式中單元格或單元格區(qū)域的引用相對(duì)于包含公式的單元格的相對(duì)位置

例：在C1單元格公式=A1+B1，把公式復(fù)制到C2單元格公式將自動(dòng)變?yōu)?A2+B2，因?yàn)镃2相對(duì)與C1只是行號(hào)增加了1，單元公式也這樣變化相對(duì)引用絕對(duì)引用引用單元格的行標(biāo)和列標(biāo)前都加上$符號(hào)，相對(duì)引用就變?yōu)榻^對(duì)引用。當(dāng)復(fù)制公式時(shí)希望引用位置不變，那么可以使用絕對(duì)引用。

例：在C1單元格公式=$A$1+$B$1，把公式復(fù)制到C2單元格公式將仍為=$A$1+$B$1

，因?yàn)楣絾卧脼榻^對(duì)引用，復(fù)制不會(huì)變化?；旌弦弥辉谝脝卧竦男袠?biāo)或列標(biāo)前加$符號(hào)，即為混合引用。當(dāng)復(fù)制公式時(shí)，相對(duì)引用部分隨公式位置的變而變化，絕對(duì)引用部分不隨公式位置的變化而變化。如：在C1單元格公式=$A1+B$1，把公式復(fù)制到D2單元格公式將自動(dòng)變?yōu)?$A2+C$1(較難理解）小技巧：在編輯公式時(shí)選中單元格地址，按F4功能鍵，可改變單元格引用類型如：$J$15→J$15

→$J15→J15

→$J$15

2021/6/288

二、Excel函數(shù)的構(gòu)成1、函數(shù)由函數(shù)名及參數(shù)構(gòu)成，其表達(dá)式如下：函數(shù)名（參數(shù)1，參數(shù)2，參數(shù)3，…）2、EXCEL提供了十類，數(shù)百個(gè)函數(shù)，用戶可在公式中使用函數(shù)有財(cái)務(wù)函數(shù)、日期與時(shí)間、數(shù)學(xué)與三角、統(tǒng)計(jì)、查找與引用、數(shù)據(jù)庫(kù)、文本、邏輯、信息、工程函數(shù)共十類，還可自定義函數(shù)函數(shù)舉例：=SUM(C2:C11,10,20)表示對(duì)C2:C11中的數(shù)值以及10、20求和2021/6/289二、Excel函數(shù)—參數(shù)的約定函數(shù)參數(shù)約定1、函數(shù)參數(shù)要用()括起來(lái)，且()前后都不能有空格2、函數(shù)中參數(shù)多于一個(gè)時(shí)，必須用英文，號(hào)隔開(kāi)3,、參數(shù)除引用單元格位置外，還可以是數(shù)、文本、邏輯值、數(shù)值、公式、或其它函數(shù)等4、給定的參數(shù)必須能產(chǎn)生一個(gè)有效值舉例：=SUM(C2:C11,10,A1)2021/6/2810三、Excel函數(shù)的使用1、函數(shù)單獨(dú)使用

單擊粘貼函數(shù)按鈕

fx，用函數(shù)向?qū)л斎雴我缓瘮?shù)。2、公式中引用函數(shù)

可將函數(shù)放在其他函數(shù)或公式中使用，形成帶函數(shù)的公式。2021/6/2811三、Excel函數(shù)的使用—輸入函數(shù)1、函數(shù)向?qū)О粹ofx2、公式編輯框直接輸入2021/6/2812四、常用的四個(gè)函數(shù)格式：SUM(Number1,Number2,…)功能：將指定為參數(shù)的所有數(shù)字相加

SUM求和函數(shù)格式：IF(logical_test,[value_if_true],[value_if_false])功能：當(dāng)指定條件的計(jì)算結(jié)果為T(mén)RUE，將返回某個(gè)值；否則返回另一個(gè)值。

IF判斷函數(shù)格式：SUMIF(range,criteria,[sum_range])

功能：可以對(duì)區(qū)域中符合指定條件的值求和。SUMIF判斷求和格式：AND(logical1,logical2,...)功能：所有參數(shù)的邏輯值為真時(shí)，返回TRUE；否則返回FALSE。AND邏輯函數(shù)2021/6/28131、Sum求和函數(shù)語(yǔ)法：格式：SUM(number1,[number2],...])例如：=SUM(F4:I4)-J4參數(shù)：1、number1必需。想要相加的第一個(gè)數(shù)值參數(shù)。2、number2,,...可選。想要相加的2到255個(gè)數(shù)值參數(shù)。說(shuō)明：1、如果參數(shù)是一個(gè)數(shù)組或引用，則只計(jì)算其中的數(shù)字。數(shù)組或引用中的空白單元格、邏輯值或文本將被忽略。2、如果任意參數(shù)為錯(cuò)誤值或?yàn)椴荒苻D(zhuǎn)換為數(shù)字的文本，Excel將會(huì)顯示錯(cuò)誤。2021/6/28141、Sum求和函數(shù)—輸入2021/6/28151、Sum求和函數(shù)—舉例1、=SUM(A2:A4)表示將a2:a4區(qū)域各數(shù)相加5、=sum(A2,A3,A4)表示將A2、A3、A4三個(gè)單元格中數(shù)相加4、=SUM(A2:A4,15)表示將A2:A4區(qū)域各數(shù)與15相加3、=SUM(3，2)表示將3和2相加2、=SUM(G:G)表示將G列的所有數(shù)相加2021/6/28162、AND邏輯與函數(shù)語(yǔ)法：格式：AND(logical1,logical2,...)例如：AND(P4>1500,P4<=4500)參數(shù)：Logical1,logical2,...表示待檢測(cè)的1到30個(gè)條件值，各條件值可為T(mén)RUE或FALSE。說(shuō)明：1、參數(shù)必須是邏輯值TRUE或FALSE,或者包含邏輯值的數(shù)組或引用。2、如果數(shù)組或引用參數(shù)中包含文本或空白單元格，則這些值將被忽略。3、如果指定的單元格區(qū)域內(nèi)包括非邏輯值，則AND將返回錯(cuò)誤值#VALUE!。

2021/6/28173、IF判斷函數(shù)語(yǔ)法：格式：IF(logical_test,[value_if_true],[value_if_false])IF(邏輯條件,[條件為真時(shí)的值],[為假時(shí)的值])舉例：A1=8000，則=IF(A1<10000,10%,15%)返回10%參數(shù)：1、logical_test必需。計(jì)算結(jié)果可能為T(mén)RUE或FALSE的任意值或表達(dá)式。可使用任何比較運(yùn)算符。2、value_if_true可選。logical_test參數(shù)的計(jì)算結(jié)果為T(mén)RUE時(shí)所要返回的值。3、value_if_false可選。logical_test參數(shù)的計(jì)算結(jié)果為FALSE時(shí)所要返回的值。說(shuō)明：最多可以使用64個(gè)IF函數(shù)作為value_if_true和value_if_false參數(shù)進(jìn)行嵌套，以構(gòu)造更詳盡的測(cè)試。

小技巧：在公式編輯框中按atl+回車可輸入換行符2021/6/28184、SUMIF條件求和函數(shù)語(yǔ)法：格式：SUMIF(range,criteria,[sum_range])

例如：=SUMIF(B2:B5,"俊元",C2:C5)

參數(shù)：1、range必需。用于條件計(jì)算的單元格區(qū)域。2、criteria必需。用于確定對(duì)哪些單元格求和的條件，例如，條件可以表示為32、“>32”、B5、32、“蘋(píng)果”。3、sum_range可選。要求和的實(shí)際單元格。如果省略，Excel會(huì)對(duì)在范圍參數(shù)中指定的單元格求和。

說(shuō)明：可以在criteria參數(shù)中使用通配符（包括問(wèn)號(hào)?

和星號(hào)*）。問(wèn)號(hào)匹配任意單個(gè)字符；星號(hào)匹配任意一串字符。

如：“1???”“1002*”2021/6/2819五、本講實(shí)例：工資表2021/6/2820五、本講實(shí)例：工資計(jì)算公式2021/6/2821五、本講實(shí)例：工資計(jì)算J4公式=Q4*50K4公式=SUM(F4:I4)-J4P4公式=IF((K4-L4)>3500,K4-L4-3500,0)2021/6/2822五、工資計(jì)算(公式舉例找問(wèn)題？）J4公式：=5*50=Q4×50K4公式：SUM（F4,G4，H4，I4)-J42021/6/2823五、工資計(jì)算—代扣稅公式=IF(P4<=1500,P4*3%,IF(P4<=4500,P4*10%-105,IF(P4<=9000,P4*20%-555,IF(P4<=35000,P4*25%-1005,IF(P4<=55000,P4*30%-2755,IF(P4<=80000,P4*35%-5505,P4*45%-13505))))))是否應(yīng)改為?IF(AND(P4<=4500,P4>1500),P4*10%-105,2021/6/2824五、知識(shí)拓展—自定義函數(shù)2021/6/2825五、應(yīng)發(fā)工資按部門(mén)匯總（SUMIF函數(shù))2021/6/2826五、借助Excel幫助文件2021/6/2827總結(jié)：正確輸入EXCEL公式公式必須以=開(kāi)始。不管是單純的公式還是更高級(jí)的函數(shù)使用，都需要以=為開(kāi)始標(biāo)記，否則，所有的公式只是字符，而不能完成計(jì)算功能1、以=開(kāi)始正確使用函數(shù)。所有公式中的函數(shù)都是以英文小括號(hào)()

成對(duì)出現(xiàn)，不能少了左邊或者右邊，使用函數(shù)時(shí)可以自己輸入也可使用插入函數(shù)的方法，但不可拼寫(xiě)錯(cuò)誤2、正確使用函數(shù)公式以鍵盤(pán)enter