財務工作常用Excel公式集錦及解析

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

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

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

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

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

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

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

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

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

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

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

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

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