财務要掌握的表格函數?前段時間寫的 财務工作常用Excel公式集錦及解析(第一季） - Excel偷懶的技術 - 知乎專欄，有朋友說看不懂那些複雜的公式，比如根據導出的科目餘額表，補全完整科目名稱的公式：，今天小編就來聊一聊關于财務要掌握的表格函數?接下來我們就一起去研究一下吧!

财務要掌握的表格函數

前段時間寫的 财務工作常用Excel公式集錦及解析(第一季） - Excel偷懶的技術 - 知乎專欄，有朋友說看不懂那些複雜的公式，比如根據導出的科目餘額表，補全完整科目名稱的公式：

=IFNA(VLOOKUP(LEFT(A8,-LOOKUP(0,1-FIND(“.”,A8,ROW($1:$20)))),A$1:C7,3,)&“-”&B8,B8)現做一個詳細解析：

　公式思路：

我們觀察科目代碼的規律可以發現，所有明細科目的代碼都是在上一級科目代碼後順序添加“.01”、“.02”......明細代碼，也就是說前面的部分都與上一級相同。

因而，我們隻要截取前面相同部分的代碼，然後用vlookup查找引用上級科目的完整科目名稱（C列），再添加上本級科目的名稱即可（C列），比如：“1122.01.020.09”科目代碼，要補全其科目名稱，隻需先截取出其上一級科目代碼“1122.01.020”，然後使用Vlookup函數，查找引用上一級科目名稱“應收賬款-重點客戶-北京”,然後補上“-”和本級科目名稱“北京華章公司”即可。

具體編制方法：

那如何截取出“1122.01.020.09”科目代碼的“1122.01.020”呢？

如果明細科目代碼有規律的話，比如都是兩位，可以使用Left函數編制公式：

=left(A8,len(A8)-3)

來截取，但是由于明細科目代碼有二位的，也有三位的，所以不能使用此公式，隻能先查找最後一個“.”的位置，然後根據其位置來截取上級科目代碼。

那如何查找最後一個“.”的位置呢？

可以使用公式下面的公式來查找

＝FIND(“.”,A8,ROW($1:$20))

ROW($1:$20)會生成1到20的常量數組：

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}

此公式的意思是：依次從A8單元格的第1位、第2位…第20位開始查找“.”符号，查找到就返回其位置數，如果沒有查找到，就會出錯，返回#VALUE!。

因而，此公式将生成一個由錯誤值及“.”所在位置組成的序列：

{5;5;5;5;5;8;8;8;12;12;12;12;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

從上面的序列可以看出，12就是“1122.01.020.09”代碼中最後“.”的位置數，那如何從上面序列中取到這個值呢？它在序列中的順序号并不固定，但有一點是确定的，就是“除去錯誤值#VALUE!，它就是最後的值”了。所以我們可以利用Lookup的特點來取到這個值。

LOOKUP有三個特點：

1、會忽略錯誤值

2、要求查找對象按升序排列，并且按二分法來查找3、如果 LOOKUP 找不到與“查找值”相等的數，它會使用“應查找區域”中小于或等于 “查找值”的“最大值”（注意：不是查找對象中的最大值）因而可以編制下面的公式：

＝LOOKUP(0,1-FIND(“.”,A2,ROW($1:$20)))

這個公式為什麼能剛好取得最後一個非錯誤值呢？

這是由于

1-FIND(“.”,A2,ROW($1:$20))會生成下面的序列 ：

{-4;-4;-4;-4;-4;-7;-7;-7;-11;-11;-11;-11;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

忽略掉錯誤值，相當于{-4;-4;-4;-4;-4;-7;-7;-7;-11;-11;-11;-11}，根據二分法，LOOKUP先取最中間那個值（－7）進行判斷，－7小于查找對象零值（0），由于LOOKUP要求按升序排列，所以它很傻很天真地認為“右邊的數比－7大，隻有在右邊才有可能找到0值”，LOOKUP就會按二分法在右邊繼續查找零值。

由于右邊的數實際上都是小于零的，所以，LOOKUP會一直查找，直到查找到最後一個－11，根據規則“查找對象按升序排列”，這個－11它是最一個位置了，肯定就是最大值了，所以LOOKUP最終返回－11。

然後，我們再在公式前加個負号将其轉換為正數，然後使用Left函數截止左邊的11位代碼：

＝LEFT(A8,-LOOKUP(0,1-FIND(“.”,A8,ROW($1:$20))))就可得到上級科目代碼“1122.01.020”了，

再使用VLOOKUP查找引用得到上級科目名稱，公式：

＝VLOOKUP(“1122.01.020”,A$1:C7,3,)&“-”&B8也就是：

＝VLOOKUP(LEFT(A8,-LOOKUP(0,1-FIND(“.”,A8,ROW($1:$20)))),A$1:C7,3,)&“-”&B8大家要注意本公式所在的單元格是C8，公式的查找範圍是A$1:C7,這是混合引用，查找範圍的起始行是固定的（A$1)，查找範圍的最末行不是固定的（C7），會随着下拉填充公式時，自動變為C8、C9、C10.......，也就是随着公式下拉填充，會自動将前面已經補充完整的會計科目名稱包含進來，這個借用前面公式計算結果的公式的編制思路值得大家學習借鑒。

如果沒查找到，那就是新科目，新科目直接引用其科目名稱即可=IFNA(VLOOKUP(“1122.01.020”,A$1:C7,3,)&“-”&B8,B8)即最終公式：

=IFNA(VLOOKUP(LEFT(A8,-LOOKUP(0,1-FIND(“.”,A8,ROW($1:$20)))),A$1:C7,3,)&“-”&B8,B8)總結：

本公式有二點值得仔細揣摸學習：

1、找到最後一個特定字符的公式：-LOOKUP(0,1-FIND(“.”,A8,ROW($1:$20)))2、借用前面公式的計算結果。

來源：财務第一教室，作者：龍逸凡，版權歸原作者所有，如多練會計轉載該文涉及版權等問題，請作者立馬與多多聯系，多多會在第一時間協商處理。

多練會計微信專注分享最接地氣的會計實務，财會幹貨，稅務知識等。

關注多練會計微信，收獲更多會計幹貨！拿更多會計課程優惠！

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!