一張應(yīng)收款賬齡統(tǒng)計(jì)表逼哭了多少會(huì)計(jì)人？

?

作者：老菜鳥來源：部落窩教育發(fā)布時(shí)間：2018-10-26 11:53:04點(diǎn)擊：5665

分享到:

收藏收藏人氣：1人

版權(quán)說明：原創(chuàng)作品，禁止轉(zhuǎn)載。

編按：

業(yè)務(wù)越繁忙的公司應(yīng)收賬款就越龐大，統(tǒng)計(jì)應(yīng)收賬款賬齡就成為了一部分會(huì)計(jì)人員的噩夢(mèng)，尤其是會(huì)計(jì)新人的噩夢(mèng)。本篇教程將提供一個(gè)完美的解決之道：Lookup函數(shù)與透視表結(jié)合實(shí)現(xiàn)賬齡分檔統(tǒng)計(jì)。該方法對(duì)按數(shù)字大小進(jìn)行分檔（分區(qū)間）的所有統(tǒng)計(jì)均有效。如果你曾為此類統(tǒng)計(jì)工作而哭泣，那么微笑吧。

致那些努力工作，努力生活的人：微笑是一種力量，就是一種生活藝術(shù)！

從事財(cái)務(wù)工作的伙伴對(duì)于賬齡統(tǒng)計(jì)表一定不陌生。財(cái)務(wù)部門根據(jù)時(shí)間段匯總每個(gè)客戶的應(yīng)收賬款金額生成應(yīng)收款賬齡統(tǒng)計(jì)表，然后交給相關(guān)業(yè)務(wù)部門去催款。通常財(cái)務(wù)系統(tǒng)導(dǎo)出的數(shù)據(jù)并不能直接滿足需要，還需要使用Excel來二次加工，這個(gè)過程所耗費(fèi)的時(shí)間就因人而異了。

我們來看個(gè)實(shí)際的例子：

系統(tǒng)導(dǎo)出的數(shù)據(jù)可能有很多列，這里我們只保留了需要的三列數(shù)據(jù)，要根據(jù)這個(gè)數(shù)據(jù)源做出下圖所示的匯總表：

我不知道各位是如何完成這個(gè)匯總表的，但是我見過有人這樣做：首先篩選一個(gè)客戶，再篩選該客戶30天以內(nèi)的交易信息，選擇對(duì)應(yīng)的金額，從Excel右下角的狀態(tài)欄看合計(jì)金額，然后填入?yún)R總表對(duì)應(yīng)的位置；再篩選該客戶31~60天的交易信息……如此四次，完成一家客戶的數(shù)據(jù)匯總……

在她看來，自己已經(jīng)操作得非常熟練了，完成這樣一張表基本上一上午就能做完，而那些新來的人有時(shí)候一天還做不好呢。

假如懂得一些函數(shù)的運(yùn)用，再會(huì)用透視表的話完成這個(gè)匯總表三分鐘應(yīng)該就夠了，不信的話看看動(dòng)畫演示！

整個(gè)操作就兩步：

第一步：通過公式把各筆交易欠款天數(shù)分成需要的4檔。公式如下：

=LOOKUP(NOW()-B2,{0,31,61,91},{"欠款0-30天","欠款31-60天","欠款61-90天","欠款90天以上"})

第二步：利用透視表生成賬齡表。

選中整個(gè)數(shù)據(jù)，插入透視表，客戶簡(jiǎn)稱作為行，賬齡作為列，應(yīng)收賬款金額作為值。

下面重點(diǎn)為各位伙伴解釋一下第一步的公式。

=LOOKUP(NOW()-B2,{0,31,61,91},{"欠款0-30天","欠款31-60天","欠款61-90天","欠款90天以上"})

（1）公式目的

公式目的只有一個(gè)，就是根據(jù)賬期（當(dāng)天日期與交易日期之間的天數(shù)）進(jìn)行分類。實(shí)現(xiàn)這種分類的公式有多種，當(dāng)前采用LOOKUP函數(shù)公式只是其中的一種。其他比較常見的是用IF函數(shù)來實(shí)現(xiàn)，但是公式會(huì)比較長(zhǎng)，容易出現(xiàn)錯(cuò)誤。

（2）LOOKUP函數(shù)公式的用法

結(jié)合本例我們來解釋一下LOOKUP函數(shù)的用法。

LOOKUP函數(shù)常用格式=（查找值，查找區(qū)域，返回值）。

以本例來說，我們添加兩個(gè)輔助列，E列“日期區(qū)間下限”，F(xiàn)列“顯示內(nèi)容”。E列作為查找區(qū)域，F(xiàn)列是要返回的值。

然后公式可以這樣寫：=LOOKUP(NOW()-B2,$E$2:$E$5,$F$2:$F$5)

很明顯可以看到函數(shù)是由三個(gè)參數(shù)構(gòu)成的。

第一參數(shù)：NOW()-B2。這部分表示當(dāng)天日期與交易日期之間的間隔天數(shù)，也就是查找值。

第二參數(shù)：$E$2:$E$5，查找區(qū)間，當(dāng)前區(qū)間中的數(shù)據(jù)是要查找的日期區(qū)間的下限值。比如我們需要找0-30天，下限就是0，要找31-60天，下限就是31，以此類推。第二參數(shù)必須以升序的方式存在，LOOKUP會(huì)自動(dòng)判斷第一參數(shù)屬于哪一個(gè)對(duì)應(yīng)的區(qū)間。（為何只寫下限值即可？如果不理解的可以查看教程《LOOKUP函數(shù)用法全解（上）——LOOKUP函數(shù)的5種用法》《LOOKUP函數(shù)用法全解（下）——LOOKUP函數(shù)的二分法原理》）

第三參數(shù)：$F$2:$F$5，返回值，對(duì)應(yīng)查找區(qū)間要實(shí)際顯示的內(nèi)容。LOOKUP在判斷出賬期屬于哪個(gè)區(qū)間后，會(huì)顯示出對(duì)應(yīng)的內(nèi)容。

使用這個(gè)公式要注意兩個(gè)地方：第二參數(shù)的區(qū)間只需要提供下限，并且是升序排列的方式。

（3）取消輔助列的公式用法

在實(shí)際使用的時(shí)候，我們可以不添加輔助列，這時(shí)候就需要把具體數(shù)據(jù)以常量數(shù)組的方式寫在公式中。也就是用{0,31,61,91}取代$E$2:$E$5 ，用{"欠款0-30天","欠款31-60天","欠款61-90天","欠款90天以上"}取代$F$2:$F$5。