問題描述

今天要講的，是關(guān)于賬務(wù)追蹤，表格結(jié)構(gòu)設(shè)計(jì)的問題，如果你是財(cái)務(wù)人員，一定不要錯(cuò)過。

小櫻同學(xué)，是個(gè)剛?cè)肼?1 年的職場新人，也是一個(gè)財(cái)務(wù)的新手，公司業(yè)務(wù)量非常的大，每天都有處理不完的賬務(wù)追蹤數(shù)據(jù)，下面是她向我求助的表格，大家猜猜，她的問題是什么？

——-思考時(shí)間——

-——-思考時(shí)間——

-——-思考時(shí)間——-

圖片太寬，沒看出來嗎？

我再來幾張局部的特寫。

以上這些數(shù)據(jù)，從 A 列開始，一直往右排列，排到了 AR 列，共計(jì) 44 列，天吶！

這樣你的思路應(yīng)該清晰了一些吧，小櫻同學(xué)反饋的問題是：

??數(shù)據(jù)列太多，各個(gè)數(shù)據(jù)列查看起來很麻煩。

??開票、收款等記錄，橫向排布，統(tǒng)計(jì)麻煩。每次老板讓匯總數(shù)據(jù)的時(shí)候，比如按月統(tǒng)計(jì)收款金額，和待還款的合同有哪些？每次都要一個(gè)個(gè)篩選，篩到晚上 11 點(diǎn)。

做財(cái)務(wù)的你，是不是也遇到過類似的問題，是不是也有這樣的老板？

不過拉登師傅告訴你：小櫻同學(xué)反饋的問題，只是表面的現(xiàn)象，根本的原因是表格結(jié)構(gòu)設(shè)計(jì)錯(cuò)誤。

錯(cuò)誤地把數(shù)據(jù)表，用匯總表的結(jié)構(gòu)，設(shè)計(jì)出來。

只滿足了閱讀的需求，沒有考慮到數(shù)據(jù)統(tǒng)計(jì)的過程，這也是大部分人，設(shè)計(jì)表格的一個(gè)通??！

問題分析

接下來，我們來仔細(xì)看一看，這個(gè)表格的問題，到底是什么？

??數(shù)據(jù)層次混作一談。

??關(guān)聯(lián)字段被拆分，失去了對應(yīng)關(guān)系。

沒明白吧，沒有關(guān)系，下面是詳細(xì)的說明。

▋數(shù)據(jù)層次混作一談

我們先思考一下，這個(gè)財(cái)務(wù)表格的目的是什么？

是針對廠商的貨款進(jìn)行追蹤，大致的流程是這樣的：

針對第 3 個(gè)環(huán)節(jié)，財(cái)務(wù)在追賬的過程中，有可能需要跟進(jìn)很多次，才能完成把所有的款項(xiàng)都追回來。

相應(yīng)的形成多筆的開票、收款記錄：

在這個(gè)過程中，有兩個(gè)數(shù)據(jù)信息流：合同和收款。

合同數(shù)據(jù)

每個(gè)合同代表一個(gè)訂單，我們可以根據(jù)合同的編號(hào)，建立一個(gè)清單，記錄所有廠商的訂單信息，這些信息包括：訂單日期、合同金額、商品名稱、商品數(shù)量等信息。

這一點(diǎn)在原表格中，是沒有問題的。

收款數(shù)據(jù)

收款對應(yīng)著合同中的金額，但是針對金額較大的合同，廠商可能無法一次付清，這樣同一個(gè)合同，可能會(huì)有多筆的收款記錄。

同樣的，我們可以對這些多筆收款記錄，建立一個(gè)清單，記錄收款的信息，這些信息包括：收款日期、收款金額、收款對應(yīng)的發(fā)票、收款方式等等。

它的結(jié)構(gòu)，與合同清單應(yīng)該是一樣的，一行數(shù)據(jù)代表一次收款記錄，收款信息對應(yīng)第 1 行的字段。

但是問題表格中，在設(shè)計(jì)的時(shí)候，犯了兩個(gè)嚴(yán)重的錯(cuò)誤。

? 每 1 筆的收款記錄，本應(yīng)該隨著行縱向延展的，被設(shè)計(jì)成了橫向的列數(shù)據(jù)，導(dǎo)致無法針對收款記錄進(jìn)行篩選。

??一個(gè)合同對應(yīng)多筆收款記錄，這種 1 對多的多級(jí)數(shù)據(jù)，被設(shè)計(jì)到了同 1 行中；數(shù)據(jù)的統(tǒng)計(jì)方向發(fā)生交叉，合同是縱向延展，而收款記錄是橫向延展，給數(shù)據(jù)統(tǒng)計(jì)造成了障礙。

上面這兩個(gè)問題，總結(jié)成一點(diǎn)，就是：用閱讀的思維，把統(tǒng)計(jì)數(shù)據(jù)設(shè)計(jì)成了，一個(gè)匯總表格。

▋關(guān)聯(lián)字段被拆分，失去對應(yīng)關(guān)系

既然錯(cuò)誤是把數(shù)據(jù)，設(shè)計(jì)成了閱讀型的匯總表格，那么正確的，統(tǒng)計(jì)型的數(shù)據(jù)表格，應(yīng)該是什么樣子的呢？

你要記住一點(diǎn)，面向數(shù)據(jù)統(tǒng)計(jì)的表格設(shè)計(jì)，都是縱向的行數(shù)據(jù)。

這類表格通常是一個(gè)一維的數(shù)據(jù)表，它有兩個(gè)永遠(yuǎn)都不會(huì)變的特征：

? 第 1 行，永遠(yuǎn)都是標(biāo)題（也叫字段）。

?

? 從第 2 行開始，下面的每一行數(shù)據(jù)（注意是行，是自上而下的，不是列），代表一條記錄：

?

每 1 條記錄里，都完整了保存了每個(gè)字段的信息。

?

每 1 列，是這個(gè)字段（比如說金額）包含的所有收款信息的金額。

如果你了解過 ACCESS，SQL 等數(shù)據(jù)庫知識(shí)，你應(yīng)該對這類表格結(jié)構(gòu)也不陌生。

在這個(gè)方面，問題表格又犯了一個(gè)錯(cuò)誤，相同字段的數(shù)據(jù)（比如金額），因?yàn)槭湛钣涗浀臋M向設(shè)計(jì)，被分割到不同的列當(dāng)中。連基本的篩選都無法實(shí)現(xiàn)。

解決方案

要解決這么多的問題，最最關(guān)鍵的，就是要梳理清楚數(shù)據(jù)信息的類別。

然后我們按照下面的步驟，一步步修改表格。

? 分析數(shù)據(jù)的層級(jí)

? 根據(jù)數(shù)據(jù)層級(jí)，建立數(shù)據(jù)統(tǒng)計(jì)型表格

? 根據(jù)數(shù)據(jù)表格，建立透視表，輸出閱讀型數(shù)據(jù)

▋分析數(shù)據(jù)層級(jí)

正如前面我們所分析的，這個(gè)賬務(wù)追蹤表格的數(shù)據(jù)，就分為兩類：合同記錄和收款記錄。

因?yàn)闊o論是合同還是收款記錄，都會(huì)有多筆的記錄，同時(shí)合同數(shù)據(jù)，是收款記錄的上一級(jí)，一個(gè)合同可以對應(yīng)多筆收款記錄。

所以我們把分別為合同、收款記錄，建立單獨(dú)的表格。

▋建立數(shù)據(jù)統(tǒng)計(jì)型表格

所謂的數(shù)據(jù)統(tǒng)計(jì)型表格，就是簡單的一維表格（你可以翻看前面的文章，回憶一下一維表格的特點(diǎn)）。

我們把所有對應(yīng)的信息，都橫向的放在數(shù)據(jù)標(biāo)題中，設(shè)計(jì)出下面的兩個(gè)表格。

這樣數(shù)據(jù)全部設(shè)計(jì)成了縱向的延展，就可以輕松的使用篩選、統(tǒng)計(jì)公式，或者透視表來統(tǒng)計(jì)數(shù)據(jù)了。

▋輸出閱讀型數(shù)據(jù)

回過頭來，再看問題的表格，大概可以猜測出，老板想要的信息了。

??輸出合同匯總表格，可以快速篩選出，待還款的合同，以及對應(yīng)的單位。

?

? 輸出按發(fā)票統(tǒng)計(jì)的，收款狀況。

?

??根據(jù)時(shí)段，輸出統(tǒng)計(jì)每個(gè)月、或者每年的收款狀況。

針對上面的這個(gè) 3 個(gè)需求，現(xiàn)在只需要使用 sumifs 函數(shù)，以及透視表技巧，就可以快速的的統(tǒng)計(jì)出來了。

不啰嗦了，咱們直接看輸出后的結(jié)果。

改善輸出

接下來，就是閱讀型數(shù)據(jù)的輸出結(jié)果。

▋合同匯總表

【合同匯總表】中，使用 SUMIFS 函數(shù)，以【收款明細(xì)】表為數(shù)據(jù)源，動(dòng)態(tài)匯總每個(gè)合同的收款狀態(tài)，如果收款 100%，就會(huì)自動(dòng)標(biāo)記為綠色。

▋統(tǒng)計(jì)各合同應(yīng)收、預(yù)收狀態(tài)

使用透視表，以【收款明細(xì)】為數(shù)據(jù)源，只要折疊或展開「單位名稱」字段，就可以輕松的，按單位、或者按合同統(tǒng)計(jì)，當(dāng)前應(yīng)收款，或預(yù)收款的狀態(tài)。

圖中，紅色表示預(yù)收款，黑色表示待收款額度。

▋按時(shí)段統(tǒng)計(jì)收款狀況

同樣的，基于【收款明細(xì)】表創(chuàng)建數(shù)據(jù)透視表，使用「創(chuàng)建組」功能，可以輕松的是實(shí)現(xiàn)，按月、按年統(tǒng)計(jì)收款金額。

今天的案例呢，邏輯上有點(diǎn)復(fù)雜，但處理這類問題的中心思想很簡單。

降低數(shù)據(jù)的維度，減少數(shù)據(jù)方向交叉，盡量使用一維表。

好了以上就是今天的全部內(nèi)容，再會(huì)！

案例下載


▋問題表格

你可以在后臺(tái)，回復(fù)【0510】獲取本節(jié)練習(xí)文件，然后自己動(dòng)手，嘗試著去修改表格。

如果你還想了解更多的 Excel 知識(shí)，更輕松高效地解決這類問題，?我推薦你參加《3 天 Excel 集訓(xùn)營》！

為期 3 天的課程專為職場人準(zhǔn)備，全部基于職場真實(shí)表格案例設(shè)計(jì)，還有很多超實(shí)用 Excel 技巧教學(xué)。

每天學(xué)習(xí)大概?30 分鐘，你也有可能成為 Excel 高手！

3 天 Excel 集訓(xùn)營

提升效率的好幫手

數(shù)據(jù)可視化的助力
原價(jià)?99?元

現(xiàn)在僅需?1?元

保存下方圖片掃碼即可報(bào)名

??????

↑↑↑

現(xiàn)在報(bào)名免費(fèi)領(lǐng)取

35 個(gè)常用函數(shù)說明

……

*廣告