|
從事財務(wù)工作的伙伴對于賬齡統(tǒng)計表一定不陌生。財務(wù)部門根據(jù)時間段匯總每個客戶的應(yīng)收賬款金額生成應(yīng)收款賬齡統(tǒng)計表，然后交給相關(guān)業(yè)務(wù)部門去催款。通常財務(wù)系統(tǒng)導(dǎo)出的數(shù)據(jù)并不能直接滿足需要，還需要使用Excel來二次加工，這個過程所耗費的時間就因人而異了。 我們來看個實際的例子： 系統(tǒng)導(dǎo)出的數(shù)據(jù)可能有很多列，這里我們只保留了需要的三列數(shù)據(jù)，要根據(jù)這個數(shù)據(jù)源做出下圖所示的匯總表： 我不知道各位是如何完成這個匯總表的，但是我見過有人這樣做：首先篩選一個客戶，再篩選該客戶30天以內(nèi)的交易信息，選擇對應(yīng)的金額，從Excel右下角的狀態(tài)欄看合計金額，然后填入?yún)R總表對應(yīng)的位置；再篩選該客戶31~60天的交易信息……如此四次，完成一家客戶的數(shù)據(jù)匯總…… 在她看來，自己已經(jīng)操作得非常熟練了，完成這樣一張表基本上一上午就能做完，而那些新來的人有時候一天還做不好呢。 假如懂得一些函數(shù)的運用，再會用透視表的話完成這個匯總表三分鐘應(yīng)該就夠了，不信的話看看動畫演示！ 整個操作就兩步： 第一步：通過公式把各筆交易欠款天數(shù)分成需要的4檔。公式如下： =LOOKUP(NOW()-B2,{0,31,61,91},{'欠款0-30天','欠款31-60天','欠款61-90天','欠款90天以上'}) 第二步：利用透視表生成賬齡表。 選中整個數(shù)據(jù)，插入透視表，客戶簡稱作為行，賬齡作為列，應(yīng)收賬款金額作為值。 下面重點為各位伙伴解釋一下第一步的公式。 =LOOKUP(NOW()-B2,{0,31,61,91},{'欠款0-30天','欠款31-60天','欠款61-90天','欠款90天以上'}) （1）公式目的 公式目的只有一個，就是根據(jù)賬期（當(dāng)天日期與交易日期之間的天數(shù)）進行分類。實現(xiàn)這種分類的公式有多種，當(dāng)前采用LOOKUP函數(shù)公式只是其中的一種。其他比較常見的是用IF函數(shù)來實現(xiàn)，但是公式會比較長，容易出現(xiàn)錯誤。 （2）LOOKUP函數(shù)公式的用法 結(jié)合本例我們來解釋一下LOOKUP函數(shù)的用法。 LOOKUP函數(shù)常用格式=（查找值，查找區(qū)域，返回值）。 以本例來說，我們添加兩個輔助列，E列“日期區(qū)間下限”，F(xiàn)列“顯示內(nèi)容”。E列作為查找區(qū)域，F(xiàn)列是要返回的值。 然后公式可以這樣寫：=LOOKUP(NOW()-B2,$E$2:$E$5,$F$2:$F$5) 很明顯可以看到函數(shù)是由三個參數(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會自動判斷第一參數(shù)屬于哪一個對應(yīng)的區(qū)間。（為何只寫下限值即可？如果不理解的可以查看教程《LOOKUP函數(shù)用法全解（上）——LOOKUP函數(shù)的5種用法》《LOOKUP函數(shù)用法全解（下）——LOOKUP函數(shù)的二分法原理》） 第三參數(shù)：$F$2:$F$5，返回值，對應(yīng)查找區(qū)間要實際顯示的內(nèi)容。LOOKUP在判斷出賬期屬于哪個區(qū)間后，會顯示出對應(yīng)的內(nèi)容。 使用這個公式要注意兩個地方：第二參數(shù)的區(qū)間只需要提供下限，并且是升序排列的方式。 （3）取消輔助列的公式用法 在實際使用的時候，我們可以不添加輔助列，這時候就需要把具體數(shù)據(jù)以常量數(shù)組的方式寫在公式中。也就是用{0,31,61,91}取代$E$2:$E$5 ，用{'欠款0-30天','欠款31-60天','欠款61-90天','欠款90天以上'}取代$F$2:$F$5。 這里學(xué)到的LOOKUP公式屬于一種自動分類套路公式。學(xué)會了它，再配合透視表的簡單操作，很多原本非常麻煩的分類統(tǒng)計工作都可以變得非常簡單。大家一定要了解、掌握這種方法！ 獲取上帶公式的應(yīng)收賬款Excel表格 |
|
|
