會計常用的130個函數(shù)公式1、文本與百分比連接公式如果直接連接，百分比會以數(shù)字顯示，需要用Text函數(shù)格式化后再連接="本月利潤完成率為"&TEXT(C2/B2,"0%")2、賬齡分析公式用lookup函數(shù)可以劃分賬齡區(qū)間=LOOKUP(D2,G$2:H$6)如果不用輔助區(qū)域，可以用常量數(shù)組=LOOKUP(D2,{0,"小于30天";31,"1~3個月";91,"3~6個月";181,"6-1年";361,"大于1年"})3、屏蔽錯誤值公式把公式產(chǎn)生的錯誤值顯示為空公式：C2=IFERROR(A2/B2,"")說明：如果是錯誤值則顯示為空，否則正常顯示。4、完成率公式如下圖所示，要求根據(jù)B的實際和C列的預(yù)算數(shù)，計算完成率。公示：E2=IF(C3<0,2-B3/C3,B3/C3)5、同比增長率公式如下圖所示，B列是本年累計，C列是去年同期累計，要求計算同比增長率。公示：E2=(B2-C2)/IF(C2>0,C2,-C2)6、金額大小寫公式=TEXT(LEFT(RMB(A2),LEN(RMB(A2))-3),"[>0][dbnum2]G/通用格式元;[<0]負[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2),2),"[dbnum2]0角0分;;整")

7、多條件判斷公式公式：C2=IF(AND(A2<500,B2="未到期"),"補款","")說明：兩個條件同時成立用AND,任一個成立用OR函數(shù)。8、單條件查找公式公式1：C11=VLOOKUP(B11,B3:F7,4,FALSE)9、雙向查找公式公式：=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))說明：利用MATCH函數(shù)查找位置，用INDEX函數(shù)取值10、多條件查找公式公式：C35=Lookup(1,0/((B25:B30=C33)*(C25:C30=C34)),D25:D30)11、單條件求和公式公式：F2=SUMIF(A:A,E2,C:C)12、多條件求和公式=Sumifs(c2:c7,a2:a7,a11,b2:b7,b11)13、隔列求和公式公式H3:=SUMIF($A$2:$G$2,H$2,A3:G3)如果沒有標題，那只能用稍復(fù)雜的公式了。=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)

14、多表相同位置求和公式：b2=SUM(Sheet1:Sheet19!B2)說明：在表中間刪除或添加表后，公式結(jié)果會自動更新。15、兩條查找相同公式公式:B2=COUNTIF(Sheet15!A:A,A2)說明：如果返回值大于0說明在另一個表中存在，0則不存在。16、兩表數(shù)據(jù)多條件核對如下圖所示，要求核對兩表中同一產(chǎn)品同一型號的數(shù)量差異，顯示在D列。公式：D10=SUMPRODUCT(($A$2:$A$6=A10)*($B$2:$B$6=B10)*$C$2:$C$6)-C1017、個稅計算工資表調(diào)整工資表原個稅列變?yōu)?列，分別是累計應(yīng)繳預(yù)扣所得額、累計稅額、本月應(yīng)扣繳稅額。（列標題大家自已命名吧）公式設(shè)置1月工資表公式：D2（累計所得額）=B2-C2-5000E2（本月累計稅額）=5*MAX(0,D2*{0.6;2;4;5;6;7;9}%-{0;504;3384;6384;10584;17184;36384})F2(本月應(yīng)扣繳額)=E22月工資表公式：D2（累計所得額）=B2-5000-C2+IFERROR(VLOOKUP(A2,'1月工資表'!A:D,4,0),0)注：使用vlookup查找上月累計應(yīng)扣應(yīng)繳所得額E2（本月累計稅額）=5*MAX(0,D2*{0.6;2;4;5;6;7;9}%-{0;504;3384;6384;10584;17184;36384})注：公式同上月F2(本月應(yīng)扣繳額)=E2-IFERROR(VLOOKUP(A2,'1月工資表'!A:F,5,0),0)注：本月應(yīng)扣=

本月累計應(yīng)扣應(yīng)繳個稅-

上月累計數(shù)其他月份工資表同2月，只需要把公式中引用的工作表改為上一月即可。如制作4月份工資表，公式中的2月改為3月即可。18、會計科目截取公式一級科目：=LEFT(B3,FIND("-",B3)-2)二級科目：=MID(B3,FIND("-",B3)+2,FIND("-",B3&"-",FIND("-",B3)+1)-FIND("-",B3)-3)三級科目：=MID(B3,FIND("-",B3&"-",FIND("-",B3)+1)+2,100)19、多表同一位置匯總公式多個工作表如果格式完全相同，可以用sum函數(shù)的多表求和功能。如下圖所示，要求在匯總表里設(shè)置合計公式，匯總前19個工作表B列的和。B2=SUM(Sheet1:Sheet19!B2)20、中國式排名公式公式:C12=SUMPRODUCT(($D$4:$D$9>=D4)*(1/COUNTIF(D$4:D$9,D$4:D$9)))21、先進先出公式提醒：該公式屬超高難度公式，不建議新手使用和費力的去理解，僅供excel中高水平用戶參考使用。定義名稱：Lj=SUBTOTAL(9,OFFSET(!$E$2,,,ROW()-1))-SUBTOTAL(9,OFFSET(!$B$2,,,ROW(!$B$1:$B9)))G3公式：{=SUMPRODUCT(IF(lj>$B$3:B3,$B$3:B3,lj),IF(lj>0,$C$3:C3))-SUM($G$2:G2)}22文本、日期與百分比連接要求：下面為日期與文本進行連接。E2單元格輸入公式為（&表示連接）：=TEXT(A2,"yyyy-mm-dd")&B2&TEXT(C2,"0.00%")注：如果使用簡單的連接而不定義格式的話那么就像D列一樣出現(xiàn)這樣的數(shù)字格式，日期與時間的本質(zhì)是數(shù)值，所以會出現(xiàn)這樣的問題。

23

IF條件判斷例：在下面的題目中，如果性別為“男”則返回“先生”，如果為“女”，則返回女士。在E2單元格中輸入公式：=IF(D2="男","先生","女士")，然后確定。說明：在Excel中引用文本的時候一定要使用英文狀態(tài)下的半角雙引號。以上公式判斷D2如果是男，則返回先生，否則那一定就是女，返回女士。

24合同到期計算計算合同到期是財務(wù)工作中一個最常見的用法。在D2單元格中輸入公式：=EDATE(B2,C2)，然后確定。注意：第二個參數(shù)一定是月份的數(shù)量，比如2年那么就是24個月。25VLOOUP查找函數(shù)查找姓名對應(yīng)的銷售額。在F3單元格中輸入公式=VLOOKUP(E3,$A$2:$C$9,3,0)，按Enter鍵完成。如下圖所示：26條件求和例：求下面的1月的1組的數(shù)量總計，在E9單元格中輸入公式：=SUMIFS(G2:G7,A2:A7,"1月",B2:B7,"1組")，確定填充即可。注：以上函數(shù)支持通配符，同時對于條件要注意加上英文狀態(tài)下的半角單引號。

27帶有合格單元格的求和合并單元格的求和，一直是一個比較讓新手頭疼的問題。選中D2:D13單元格區(qū)域，然后在公式編輯欄里輸入公式：=SUM(C2:C13)-SUM(D3:D14)，然后按<Ctrl+Enter>完成，如下圖所示：注：一定要注意第二個SUM函數(shù)的區(qū)域范圍要錯位，不然就報錯。

28帶有小計的單元格求和在表中帶有小計是許多領(lǐng)導(dǎo)的最愛的一個風(fēng)格，但是對于做表的人來說絕對一個是很難受的過程，那么帶有小計的單元格到底怎么樣求和呢。在C9單元格里是輸入公式：=SUM(C2:C8)/2，按Enter鍵完成。如下圖所示：注意：這里是自用了小計與求和的過程是重復(fù)計算了上面的數(shù)據(jù)，所以再除以2就可以得到不重復(fù)的結(jié)果，也正是想要的結(jié)果。29VLOOKUP賬齡分析在D2單元格中輸入公式：=VLOOKUP(TODAY()-B2,{0,"0-30天";30,"30-60天";60,"60-90天";90,"90天以上"},2,1)，按Enter鍵后向下填充。如下圖所示：最后同上一個方法一樣插入數(shù)據(jù)透視表即可。注：使用VLOOKUP函數(shù)的最后一個參數(shù)為1時為模糊查找的原理進行查詢。結(jié)果。30多工作表求和下表中是4個月的業(yè)績統(tǒng)計，每個工作表的里面的張成的位置都是一樣的，求張成的1-4月的提成統(tǒng)計。在F5單元格中輸入公式：=SUM('1月:4月'!C2)按Enter鍵完成填充。如下圖所示：31金額大寫轉(zhuǎn)化如下圖所示，將A列的數(shù)字轉(zhuǎn)換成財務(wù)大寫數(shù)字。在B2單元格中輸入公式，然后向下填充即可。=TEXT(TRUNC(ABS(ROUND(A2,2))),"[DBNum2]")&"元"&IF(ISERR(FIND(".",ROUND(A2,2))),"",TEXT(RIGHT(TRUNC(ROUND(A2,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(A2,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A2,2),3))=".",TEXT(RIGHT(ROUND(A2,2)),"[DBNum2]")&"分","整")32票據(jù)金額拆分將下面的金額拆分至對應(yīng)的單位的單元格中去。在D6單元格中輸入公式：=IF($C6,LEFT(RIGHT("￥"&$C6/1%,COLUMNS(D:$N))),"")按Enter鍵完成后，向右向下填充。33交叉查找在H2單元格中輸入公式：=VLOOKUP($G2,$A$2:$D$9,MATCH(H$1,$A$1:$D$1,0),0)，按Enter鍵完成后向下向右填充。注：一定要鎖定VLOOKUP函數(shù)的第一個參數(shù)的列號，MATCH函數(shù)的第一個參數(shù)的行號，這樣才能得到正確的結(jié)果。

34屏蔽錯誤FERROR函數(shù)是屏蔽錯誤值的一個函數(shù)。在E2單元格中輸入的公式查詢的時候出現(xiàn)了一個錯誤，此時想把這個公式屏蔽為空白，那么就可以在E2單元格中輸入公式：=IFERROR(VLOOKUP(C2,$I$3:$K$7,3,0),"")，確定后向下填充。注意：該函數(shù)先判斷第一參數(shù)是否為錯誤值，如果為錯誤值則返回為定義的第二個參數(shù)，如果不是錯誤值，那么繼續(xù)地返回其本身。

35四舍五入函數(shù)功能：將某個數(shù)字四舍五入為指定的位數(shù)語法：ROUND(number,num_digits)在E2、單元格中分別輸入公式：=ROUND(D2,2)，在F2單元格中分別輸入公式：=ROUND(D2,0)，在G2單元格中分別輸入公式：=ROUND(D2,-2)注：如果num_digits大于0（零），則將數(shù)字四舍五入到指定的小數(shù)位；如果num_digits等于0，則將數(shù)字四舍五入到最接近的整數(shù)；如果num_digits小于0，則在小數(shù)點左側(cè)進行四舍五入。一、員工信息表公式1、計算性別(F列)

=IF(MOD(MID(E3,17,1),2),"男","女")2、出生年月(G列)

=TEXT(MID(E3,7,8),"0-00-00")3、年齡公式(H列)=DATEDIF(G3,TODAY(),"y")4、退休日期

(I列)=TEXT(EDATE(G3,12*(5*(F3="男")+55)),"yyyy/mm/ddaaaa")5、籍貫(M列)=VLOOKUP(LEFT(E3,6)*1,地址庫!E:F,2,)注：附帶示例中有地址庫代碼表6、社會工齡(T列)=DATEDIF(S3,NOW(),"y")7、公司工齡(W列)=DATEDIF(V3,NOW(),"y")&"年"&DATEDIF(V3,NOW(),"ym")&"月"&DATEDIF(V3,NOW(),"md")&"天"8、合同續(xù)簽日期(Y列)=DATE(YEAR(V3)+LEFTB(X3,2),MONTH(V3),DAY(V3))-19、合同到期日期(Z列)=TEXT(EDATE(V3,LEFTB(X3,2)*12)-TODAY(),"[<0]過期0天;[<30]即將到期0天;還早")10、工齡工資(AA列)=MIN(700,DATEDIF($V3,NOW(),"y")*50)11、生肖(AB列)=MID("猴雞狗豬鼠?；⑼谬埳唏R羊",MOD(MID(E3,7,4),12)+1,1)二、員工考勤表公式1、本月工作日天數(shù)(AG列)=NETWORKDAYS(B$5,DATE(YEAR(N$4),MONTH(N$4)+1,),)2、調(diào)休天數(shù)公式(AI列)=COUNTIF(B9:AE9,"調(diào)")3、扣錢公式(AO列)婚喪扣10塊，病假扣20元，事假扣30元，礦工扣50元=SUM((B9:AE9={"事";"曠";"病";"喪";"婚"})*{30;50;20;10;10})三、員工數(shù)據(jù)分析公式1、本科學(xué)歷人數(shù)=COUNTIF(D:D,"本科")2、辦公室本科學(xué)歷人數(shù)=COUNTIFS(A:A,"辦公室",D:D,"本科")3、30~40歲總?cè)藬?shù)=COUNTIFS(F:F,">=30",F:F,"<40")四、其他公式1、提成比率計算=VLOOKUP(B3,$C$12:$E$21,3)2、個人所得稅計算假如A2中是應(yīng)稅工資，則計算個稅公式為：=5*MAX(A2*{0.6,2,4,5,6,7,9}%-{21,91,251,376,761,1346,3016},)3、工資條公式=CHOOSE(MOD(ROW(A3),3)+1,工資數(shù)據(jù)源!A$1,OFFSET(工資數(shù)據(jù)源!A$1,INT(ROW(A3)/3),,),"")注：A3:標題行的行數(shù)+2,如果標題行在第3行，則A3改為A5工資數(shù)據(jù)源