跟著勇哥學做財務報表2

本篇文章您將看到以下內容

目錄

⊙銀行存款餘額調節表

⊙會計科目表

⊙憑證錄入

1

銀銀行存款餘額調節表

銀行存款餘額調節表可做為銀行存款科目的附列資料保存。該表主要目的是在於核對企業賬目與銀行賬目的差異，也用於檢查企業與銀行賬目的差錯。調節後的餘額是該企業對賬日銀行實際可用的存款數額。

銀行存款餘額調節表，是在銀行對賬單餘額與企業賬面餘額的基礎上，各自加上對方已收、本單位未收賬項數額，減去對方已付、本單位未付賬項數額，以調整雙方餘額使其一致的一種調節方法。

銀行存款餘額調節表是一種對賬記錄的工具，並不是憑證;如果餘額相等，則一般沒錯;否則可能存在未達款項，或者記錄錯誤。

設計注意點

1. 需要區分清楚銀行對企業 企業對銀行兩個方向
2. 區分清楚哪些項應該是加 哪些項應該是減 這樣可以幫助新人了解業務
3. 金額需要加上千分符這樣更符合財務的閱讀習慣

如何設置千分符

2

會計科目表

上圖是會計科目表

這張表體現了表格欄位原子化，盡量用縱表的設計理念(Excel表設計的注意點).這部分我們將學習到如下內容

1. 數據有效性

2. 條件格式
3. 單元格格式
4. 科目級別自動判斷
5. 賬戶科目拼裝技巧
6. 動態數據區域求和

數據有效性

細心的朋友可以看到當我們點擊餘額方向的時候，自動會有提示信息出來。這個主要是使用了數據有效性。下面將演示下這個技巧的使用方法，詳細的信息可以參見(數據有效性)

條件格式

大家可以發現，餘額方向為借的數據 其中的A:G列的字體都是顯示為紅色，這主要是使用條件格式設置的。詳細的條件格式可以參見(大話Excel之條件格式)

注意：因為是A：G列都要設置為紅色，所以在設置公式的時候D列一定要做絕對引用，這樣保證A到G列都是和相應行D列的內容相比。

單元格格式

仔細觀察這張財務報表，你會發現。數值為0的單元格竟然被顯示為了-。這樣的好處是讓財務人員不至於看到滿眼的0.如何去實現這個呢？一種簡單的方法就是用if公式去判斷，第二種方式是藉助單元格設置。參考 單元格格式設置

藉助下面這段代碼就可以將0值設置為-

_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * "-"??_ ;_ @_

科目級別自動判斷

因為財務的科目是分等級的，從這張表我們可以看出根據科目代碼的長度對應相應的科目等級 4位->1 6位->2 8位->3

有了上述規則。我們可以快速判斷出科目級別，而不需要自己去手動填寫。手動填寫不但會出錯，而且效率也低下。

方法1： if(len(A5) = 4,1,(if(len(A5)=6,2,8))

方法2： int((len(A5) - 4) /2 ) + 1

方法一 使用if嵌套 這種方法比較傳統

方法二 採用數學方法，這種方法比較討巧

賬戶科目拼裝技巧

從報表中我們可以看出，H列是根據B，C列的數據拼裝起來。但是對於1級科目C列的內容為空。這種情況就得特殊處理

=IF(C5="",B5,CONCATENATE(B5,"_",C5))

CONCATENATE 函數是將相應的內容串起來

更多的文本函數參見文本處理，其實很簡單

動態數據區域求和

我們都知道，模塊的好處是 數據發生變化後。原先寫的公式不需要更新，可以自動算出結果。要想達到這個目的求和的數據區域就得做成動態的。

以本報表為例，如何實現動態數據求和呢？

實現思路如下

1.使用offset函數找到對應的條件區域、求和區域

2.在此基礎上使用sum函數 =SUMIF(OFFSET($D5,,,COUNTA($D:$D)-1),"=1",OFFSET(F$5,,,COUNTA($D:$D)-1))

COUNTA($D:$D) 表示從D5開始有多少個非空行，也就是我們的條件區域的高度。

這裡為了公式簡潔，可以將 條件區域和求和區域定義為名稱

sumif函數 公式函數之sumifs

offset函數

OFFSET($D5,,,COUNTA($D:$D)-1)

以D5單元格為原點，行列不偏移。目標區域是1列N行的區域

N行是根據COUNTA($D:$D)-1 計算而來

offset函數邏輯

注意

上圖行列偏移都是正數，表示向下 向右 偏移。當然偏移也支持負數，則表示向上、向左偏移

3

憑證錄入

下表是憑證錄入表

在本部分，你將學習到如下內容

1.利用數據有效性，實現下拉列表

2.反向數據查詢

3.對篩選後的數據進行統計

利用數據有效性，實現下拉列表

在本例中，憑證種類和賬戶名稱都是採用下拉列表來實現。這種實現方法首先可以簡化用戶的操作。其次可以提升數據的準確性，下面將介紹下拉列表的實現方式。

方法1序列指定數據區域 實現下拉列表

注意 數據與數據之間一定要用英文, 隔開

方法2 序列+名稱實現下拉列表

分為兩步，第一步根據科目表的數據自動生成對應的賬戶信息。這樣就起到了模板的作用。第二步 在序列部分像引用公式一樣 引用名稱。

=OFFSET(會計科目表!$H$4,1,,COUNTA(會計科目表!$A:$A)-1) 這個公式就是根據數據的範圍 自動生成一個科目區域，實現了動態。

逆向數據查詢

大家可以看到，我們需要根據用戶選擇的賬戶名稱從科目表中找出對應的編號，逆向數據查詢的技巧很多，本例將給大家介紹如何使用index實現反向查詢。

更多逆向查詢可以參見(逆向查找的那些套路)

=INDEX(科目代碼,MATCH($J5,賬戶查詢,))

先使用MATCH($J5,賬戶查詢,)找到對應的賬戶名稱出現在哪一行(公式函數之Match),這裡match的第三個參數使用了默認參數表示是精確匹配

在使用index函數在科目代碼中找對應行的數據，這裡第三個參數為空。因為科目代碼是一列N行，自動會找對應行的數據。如果科目代碼為1行N列 則會找對應列的數據。

對篩選後的數據進行統計

在數據核對的時候，我們經常需要根據不同的篩選條件進行數據比較。有種方法是使用多條件統計，比如比較平均值就得使用Averageifs 比較合計值就得使用sumifs，比較出現數量就得使用countifs.其實在excel中，有個萬能函數subtotal

根據參數的不同，可以計算相應的統計結果

參數說明如下

因為我們按照不同的篩選條件對賬，因此可以使用subtotal，這樣可以不用記住那麼多函數，只需要查詢下每個參數對應的含義即可。方便又快捷