財(cái)務(wù)工作常用Excel公式集錦及解析

1、財(cái)務(wù)工作常用Excel公式集錦及解析第一季科目余額表及明細(xì)賬常用公式一、按科目級次篩選需求背景在財(cái)務(wù)日常工作中，經(jīng)常需要將科目余額表或其他代碼按層級進(jìn)行篩選，比如篩選出總賬科目、篩選出二級科目。解決方案由于科目代碼的格式都是固定的，比如總賬4個(gè)字符長度，二級7個(gè)字符長度。因而，這個(gè)需求實(shí)際上就是按字符個(gè)數(shù)篩選。我們通常是用LEN函數(shù)構(gòu)造輔助列計(jì)算字符個(gè)數(shù)，再對輔助列進(jìn)行篩選。實(shí)際上一個(gè)小技巧就可幫助我們輕松按字符個(gè)數(shù)篩選：選中表格，然后點(diǎn)擊自動篩選，再在篩選搜索框中輸入“?”（英文半角狀態(tài)下輸入），即可篩選出4個(gè)字符長度的記錄。要篩選其他長度的記錄，以此類推。具體操作詳見操作演示知識點(diǎn)解釋“?

2、”是通配符，代表單個(gè)字符。所以在篩選搜索框里輸入幾個(gè)問號就代表篩選幾個(gè)字符的記錄，對字母、漢字、數(shù)字、字符均有效。我們將此知識點(diǎn)進(jìn)一步拓展，可以按字符個(gè)數(shù)求和，比如對所有總賬科目（字符長度為四個(gè)）進(jìn)行求和的公式：=SUMIF(A2:A22,"?",C2:C22)二、判斷科目是否為最末級需求背景日常工作中我們將科目余額表導(dǎo)出，通常包含了第一級到最后一級，要分別篩選總賬科目、二級科目等可以使用前文中的技巧，那如果要篩選或標(biāo)注出最末級科目，該怎么辦呢？解決方案可以使用輔助列法。輔助列可以化繁為簡。先使用公式判斷是否為最末級，然后篩選出該輔助列為“最末級“的記錄行，公式如下：=IF

3、(LEN(A2)>=LEN(A3),"最末級",“”)知識點(diǎn)解釋在“偷懶的技術(shù)：打造財(cái)務(wù)Excel達(dá)人中說過“要設(shè)計(jì)一套功能強(qiáng)大的財(cái)務(wù)工作表，更需要的是表格設(shè)計(jì)過程中的邏輯思維和函數(shù)的拓展應(yīng)用能力”，在編寫公式前，先不要忙著琢磨用什么函數(shù)，而應(yīng)該分析數(shù)據(jù)的規(guī)律，總結(jié)出規(guī)律后，再編寫公式。粗一看，要判斷科目是否為最末級，感覺無從下手，但是我們分析一下科目余額表就可發(fā)現(xiàn)，同一個(gè)總賬科目下越是明細(xì)級的科目，其代碼越長（廢話，這個(gè)財(cái)務(wù)人都知道）。也就是說，如果用本行的科目代碼字符數(shù)與下一行的相比，如果字符數(shù)與下一行相等（同級）或比它多（更明細(xì)級），它就是最末級的（前提條件是科

4、目余額表要按科目先后順序排列）。說明：本案例如果使用高級篩選，在F2單元格輸入篩選條件公式=LEN(A2)>=LEN(A3)，再以其為條件篩選，可篩選出大部分符合條件的記錄。由于條件公式中的A3按要求應(yīng)該為$A$3，但是如果寫成這樣，就與需求不符了，故寫成A3，這樣一來就不符合高級篩選“條件公式中除記錄的第一行外的所有其他引用要求是絕對引用”這一條件，因而最后一行未篩選出，存在小小的瑕疵，因而不適合使用高級篩選。三、分離科目代碼和科目名稱需求背景：某些財(cái)務(wù)軟件導(dǎo)出的科目余額表中是“1122.01.898偷懶的技術(shù)“這樣的格式，需要將代碼和名稱分離，或者遇到不規(guī)范的數(shù)據(jù)，如人名與手機(jī)號“龍

5、逸凡18X12345678”，需要將數(shù)字和漢字分離為二列。解決方案如果科目代碼長度一致，或者代碼和名字中有某個(gè)固定分隔符，則可使用“分列”功能來分離科目代碼和名稱，如果沒有，則需要使用下面的公式：取科目代碼=LEFT(A2,2*LEN(A2)-LENB(A2)取科目名稱=RIGHT(A2,LENB(A2)-LEN(A2)這個(gè)公式不太完善，當(dāng)名稱中有字母或數(shù)字時(shí)，則公式結(jié)果會出錯(cuò)，比如B16、B17單元格，這種情況下提取科目代碼需使用下面的數(shù)組公式：=LEFT(A2,MIN(IF(MID(A2,ROW($1: $99),1)>="a",ROW($1: $99),99)-

6、1)輸入完后需要同時(shí)按Ctrl+Shift+Enter三鍵輸入如果電腦上安裝了Excel2013以后的版本，也可使用快速填充，具體操作詳見下面的操作演示：知識點(diǎn)解釋第一個(gè)公式中的Len函數(shù)：計(jì)算字符數(shù)。LENB函數(shù)：計(jì)算字節(jié)數(shù)，一個(gè)漢字為二個(gè)字節(jié)。所以可以用LENB-LEN計(jì)算字符串中漢字的個(gè)數(shù)。由于公式是根據(jù)漢字個(gè)數(shù)來分離數(shù)字和漢字，所以，當(dāng)名稱中包含字母或數(shù)字，以及數(shù)字和漢字相互夾雜時(shí)，公式提取的結(jié)果就不符合要求。第二個(gè)公式的設(shè)計(jì)思路是逐個(gè)截取字符串中的每一個(gè)字符，判斷其是否為字母或漢字，即是否為”a”之后的字符（在Excel里，如按升序排列，則字母在數(shù)字后，漢字在字母后，也就是19az吖

7、（）?。▃uò），這也是它們的大小順序）。公式使用ROW($1: $99)生成1到99的常量數(shù)組，寫成99只是為了保證大于等于字符串的長度，根據(jù)實(shí)際情況也可改成50，30等。MID(A2,ROW($1: $99),1)是依次截止A2單元格的第1個(gè)，第2個(gè)。第99個(gè)。截取出的字符如果是字母或漢字，則給其字符所在位置的順序號，否則，給它的順序號是99。再用MIN函數(shù)來提取第一個(gè)字母、漢字的位置。最后用LEFT來截取左邊的數(shù)字和字符組成的代碼。同理，如果要提取科目名稱，用下列公式即可（別忘了最后用三鍵輸入）：=RIGHT(A2,LEN(A2)-MIN(IF(MID(A2,ROW($1: $

8、99),1)>="a",ROW($1: $99),99)+1)當(dāng)然，使用上述公司還得有個(gè)前提，即避免分錄名稱的第一個(gè)字為數(shù)字，比如“1小組”、“2車間”等等，而應(yīng)采用“一小組”、“二車間”的命名方式。四、填寫完整的科目名稱需求背景：財(cái)務(wù)軟件導(dǎo)出的科目余額表為了簡潔，在科目名稱列，只保留了本級科目的名稱，本級之前的名稱都沒保留，這么做的弊端是篩選時(shí)無法根據(jù)名稱來篩選本科目下某個(gè)級次科目，并且當(dāng)明細(xì)科目較多，而我們記不住科目代碼時(shí)，無法知曉其總賬科目是什么。解決方法用公式寫出完整科目名稱。在C2單元格編制下面的公式：=IFNA(VLOOKUP(LEFT(A2,-LOOKU

9、P(0,1-FIND(".",A2,ROW($1: $20),A$1:C1,3,)&"-"&B2,B2)然后下拉填充即可。知識點(diǎn)解釋：這個(gè)公式很經(jīng)典，有二個(gè)關(guān)鍵思路，1、找出某符號最后一個(gè)的位置。2、利用前面行公式的計(jì)算結(jié)果。ROW($1: $20)生成1到20的常量數(shù)組（假設(shè)代碼長度都在20以內(nèi)）。FIND(".",A2,ROW($1: $20)依次從第1位、第2位第20位開始查找"."符號。如果沒有查找到則出錯(cuò)。此公式將生成一個(gè)由錯(cuò)誤值及"."所在位置組成的序列。1-FIND

10、(".",A2,ROW($1: $20)將生成錯(cuò)誤值與0,-1,-2.組成的序列。-LOOKUP(0,1-FIND(".",A2,ROW($1: $20)此公式在序列中查找0，根據(jù)其查找原理將返回最后一個(gè)"."前面那個(gè)字符的位置。如果對這點(diǎn)還是不太理解，請閱讀深入理解LOOKUP：LOOKUP函數(shù)的查找原理、公式-LOOKUP(1,-LEFT(A1,ROW($1: $10)詳解。五、取會計(jì)科目的最末級科目名稱需求背景財(cái)務(wù)工作中有時(shí)候需要做前面“填寫完整的科目名稱“的逆向操作，在完整會計(jì)科目中取最末級科目的名稱，類似的需求還有取物料代碼

11、1.01.15.38189的最后一節(jié)。解決方案編制下面的公式=IFNA(RIGHT(B2,LEN(B2)+LOOKUP(0,-FIND("-",B2,ROW($1: $22),B2)當(dāng)然，這里我們也可使用“快速填充”功能來提取最末級科目。知識點(diǎn)解釋關(guān)于此公式的解釋參見前文。六、將上面行的科目代碼及科目名稱往下填充需求背景某些財(cái)務(wù)軟件批量導(dǎo)出多個(gè)科目的明細(xì)賬時(shí)，只有科目的第一行顯示了科目代碼和本級明細(xì)科目的名稱，無法進(jìn)行正確篩選，當(dāng)該科目明細(xì)賬較多時(shí)，也影響閱讀。解決方法將本科目第一行的科目代碼和科目名稱填充到本明細(xì)科目下所有的空白單元格。選定所有明細(xì)賬的第一行到最后一行A1

12、：B22單元格區(qū)域，按F5調(diào)出定位對話框，點(diǎn)擊“定位條件”，然后雙擊“空值”選項(xiàng)（直接雙擊某選項(xiàng)，等同于點(diǎn)選后該選項(xiàng)再去點(diǎn)“確定”），即可選定A1：B22區(qū)域中所有的空白單元格。此時(shí)，請勿點(diǎn)擊鼠標(biāo)。直接鍵入“A3”（或鍵入號后，按一下向上的箭頭），然后按住ctrl不放，敲擊Enter，即可在所有空白單元格鍵入公式。具體操作見下面的操作演示知識點(diǎn)解釋F5功能鍵在Excel中是定位功能的快捷鍵，定位功能是精確制導(dǎo)的武器，它可根據(jù)單元格的屬性來選擇單元格。常用于選擇數(shù)字單元格、公式單元格、空白單元格、沒有隱藏的單元格。公式“A1“中A1的引用類型是相對引用，將公式填充到其他單元格時(shí)，公式中的A1會自

13、動根據(jù)所在位置變更為相應(yīng)的單元格，比如B3單元格公式會變成“B2”，A4單元格公式會自動變動“A3”。關(guān)于定位功能和單元格的引用類型的詳細(xì)介紹及更多精彩應(yīng)用請參閱“偷懶”的技術(shù)：打造財(cái)務(wù)Excel達(dá)人。七、篩選包含某科目的憑證需求背景有時(shí)候?yàn)榱瞬僮鞣奖悖覀儗{證序時(shí)簿導(dǎo)出為Excel，但Excel中篩選時(shí)無法象財(cái)務(wù)軟件一樣根據(jù)某會計(jì)科目按憑證進(jìn)行查詢篩選，只能按分錄篩選。解決方案使用輔助列，在I5單元格編制下面的公式，下拉填充，然后篩選出值為指定會計(jì)科目的行。=IF(D7<>D6,VLOOKUP($B$1&"*",OFFSET(F7,0,0,COUNTIF(D7: $D$301,D7),1),1,0),I6)知識點(diǎn)解釋首先判斷本行是否為本張憑證的第一行D7<>D6，如果是，則用VLOOKUP查找出本張憑證中OFFSET(F5,0,0,COUNTIF(D5: $D$301,D5),1)以B1單元格開頭$B$1&"*"的會計(jì)科目。如果要查找包含B1單元格會計(jì)科目的憑證，則將查找目標(biāo)改為"*"&$B$1&