EXCEL技巧：制作简易“应收账款账龄分析表”

在“应收账款明细表”中设置以下公式：如图
3
制作简易“应收账款账龄分析表”
1、 先填制“应收账款明细表”（以流水账的形式填），如图1
E
剧旅 叫|
字矢
H
对齐方式
A e CD ■ 应收账裁明拙表 2境报日期； 网15-广吕 OB
客户收款 应收日期账龄 4 &公司 6泥，3&9.M 2015-4-35 5 E 公司 ^91,320. DO 2015-&-12 173,930. DO ZUlb-7^4 411, 705. DC 2015-3-22
9。

4。

归,DO 2015-5-11 525, S91. DO 2015-L-2
S55,207. DC 2015-7-25
328,31fi. M 2015-1-3 732, 471. DC 2015-6-j
623, S44. DC 2015-6-13
116,270. DC 2015-8-31 仑59, 5M, 1C 2015-3-14 ^39, 707. DO Z01U-5-ZS 界塞 4审，DO 2015-3-1 43Q, 17土 M 2015^25 937-4BA DC 2015-7-12 421, 930. DO 2015 6 8 67Z 086,00 2015-10-27 935勇90-如 2015-3-22 793司15. DC 2015-1-30 叫。

（XL DO 2015-6-1
n
寻司司司
司
司
司司5
司^
司浏司
_n 洞洞司 L
v -i - \ i
-
I s ︑土︑■./!■ \
i V
i - X
3 I -3 \4kjf X l x w
i 也也 w
也 i i i % 龙 £ 也 i i —i K -D E A A
B B 4
C F P G H J —I J K J L L
D T 35-0123456782、 25 26 27 再作一个“账龄区间表”，如图2 （图
1）
I
睑收账敦明m 表 2单报日或I ：
油5-卜日
&9 名 369. 00 361. 331*00 2015-4-Z6 2C15-?-lE
175, 93P. 00 203S-F-4
Uh 705, 00 905, 002. 00 2C15-3-3E 2015-5-11
52^ E9Lg 20] 5-L-E
BGfMQT. 00 ZQ15-T-26
花E,316. S 203 5-1-^
W, 4TL 00 201 5-5-S
623, R4k 00 2015-S-13
“易 WTQ. Q0 2C15-3-3L
66% BOB. 0Q zoi&-?-ii
时;％ 707, 00 也 15-5 -2E
舞& 457, OP 29] 5-3-L
130, L"3, 00 20L5-7-2D
33 F,48 田 00 2C15-T-12
431, 960. 0U 2QJ 5-3-3
fi7?, Dfik 00 2015T0-方
昵岛可g 00 2015-3^22
793, 31b. 00 201&-J-30
应睁款 应收日期 账龄
5P J DOO. 00
2015-6-1
吾
司司习司司
-rl ，
^司司司司司司0，国伺司司
司司
司
\ 占
\■■.
■ A
\
A
\■■.■ %-
A \
s ・ L \A .X -A f c l -A \/f
X -A
-M x -i t x .\ .db '--K "-■II 一
一
41 S 2i /■ I 一
- 4 J 2么■-cl
i ——'眯学■叵问 -1010■:
未到期
30
30^60
网 过沁
顷1
90^120
mF 。

180*360
3 EC
380^720
720 E2Q
（图
2）
默面 Ji!
刑齐方式 "
数字
SUM
^LOOKUP($B$2-Ci, $F$4:$F$1% $G$4:$G$12)
A _
B
匚
1 ▲ D
E _
F G
市收账款明细表
2填报日期：=ODAY()]公式1 3 J 吝户 应收款 692,洗9・ 00 &31, 330. 0。

173, 930. 00 411, 7D5. 00 905, 002. 00 525, 331. 00
8S&. 2D7. 00 32& 316. 00 732, 00
62& 844. 00 n§, 2T0? 00 6无 5D6. 00 昭％ 7)7, 00 23G, 457.
00 伽,173, 00 g, 4M. 00 421,. 930. 00 &72, 0B6. 00 函 590?00 793, 315. 00
50, 000. 00
司司司司司司司司司司司司司司司司司司司司司
^4
公
公公公公公公公^4公^4公^^4
公^4
公^一 B - C 、
D ・ A A JB JB J A JG JF 1G g w JI JJ K -JJ JL
L
4一5|67一史
9一0一
1一23J 一一应收日期 败龄 账龄区间
2015-4-26 =LDUK[JF($B$2-C4 $网4; $F$1 % $G$4; $G$12) 2015-9-12 公式2 o 0*3。

2015-T-4 30 30^60
20L 5-3-22
60 60"90
2015-5-11
91) 90^120 2015-1-2 120 120^180 2015-7-26 180 130™8S0 2015-1-3 3的
360"Y20 2015-6-5 720 >=720 2015-6-13 20L3-8-31 2015-9-14 20L5-5-2S 2015-3-1
20L5-7-25
201 Fi-7-1 2
2015-8-8 2015-10-27 20L5-3-22 20LS-1-30 2015-6-1 (图3)
4、公式设置完后，得到以下结果，如图 4 1 〜剑炒一国
细圭
2境招日朋，Q2015-7-8) 3 )客户 应收日朗
692. 365. 00 201f5-4-2e. 5B ：I, Z3H. OU 2(H5-g-12
17^ 93D. 00 2015-0 111,705.00
3015-^22
905, QD2H 00 203 5-5-11 525, S9L00 201^-1-2. 8能.ZOh 00 Z01S-7-2E 328, 316.00 2015-1-3 732, 47L. OD 2015-£-5 623, S41 (00)
2015-6-13 110,270. 00 2015-0^31 G59, 506. CO 2015-5-14
939l 7DR 00
2015-5-20
29E r 00
2015-3-1
430* I FM m 2(115-7-25 037, IBB,. 00 203
5-7-12
121,380.00 201E-£-f
G72, Q3G, 00 2015
10-27
935,590.00 2015-3-22
793,315.0020 J
5-1-30.
hu, 000. OU
201E-E-1
生
5&F 891O 11
■ 一 --3 5 6 7
R 9 o T J - M 一 一︱i
111司
司
司司司司司司司司司司司
一巨_Q _n __n
_
p
=T n M 公公公公公公公公公公公公公公公公公公公公公 A E C B E A
A B
B A
C T G G H I J X
w
未到期
Q 'SO
90"120
3。

飞。

180~360
未到期 I 80^360
0^30
未到期 未到期 3矿6Q
1«
期期期期20
广到到到到C 1 而未未未未凯120^180
3D~B0
9R I
(图4)
5、到此，账龄分析表的基础数据基本做好了，但是，表中只做了 24行，如果以后还要接着往下登记，希望能在分析表 中能自动添加进去，要怎么办呢？有一个简单的办法是，将明细“插入表格”功能，这样往下增加的记录就会自动添加 到表格区域
中，如图 5
A
E CD
2 填报E 期：
选定明细表，点“插入”-“表” 如图6
日 I C I D
一-应收白期
M 陷-一二 2015-4-2e
60"00
!
20i 5-'J-12
耒到旋!
20L5-?-<
0*30 ；
3015-3-22 9D*L 依 :
2匚15-5-11
3C"fid
:
20L5-1-2
L 日矿 360 :
2015-7-J6 未到期
2015-1-3 1@C"36Q ； 20L5-6-5
30^60
: 2015-6-13
CT3D
：
2015-8-31 末到期 2015-9-14
未到期
2C 15-3-28
30^60
:
2015 3-1 ISO^ISD
:
2015-7-25 未到期 2013-7^12 未到朋: 20L5-S-S
未到期 ZQ15-1O-Z7
未到既
2015-3-22 9。

七2（ 2015-1-30
120^180
:
A3
■
闩容户
（图
5）
应吵敢 应收日期
rnk=4・
R92,涌.00
2Til5-+-2fi 60 勺 Cl
531» 330. 00
2015 9 12 末堂［明
173,530.00
2015-7-i 『30 4.11, T05. DO
2015-3-22 叫F D 903, 002. 00 2TH5-j-n 30间 525,031.00 2015-1-2 180^360
8^207*00
2015-7-26
未量期
32B, 316. 00
2015-1-3
180^360
732, <71. 00
2015-6-5 30"60 溢 Uli. uO
3015-6-18
0^30
LIB, g. 00 2U15）-d-^l
未到M 659, 500. 00
2^15-5-14 未到的
939. 707.00
2015-5-2A 3/G0
29G, 457. Q0 2015-3-1 1.上0*」
4的 173. 00
3015-7-25 未我崩 537,486,00 2fil5-7-12 末争削 431, 980,00
2015-8-8 未款期 672, 086. 00 Z01&-LP-27
帮期
9】昭 59Q. 00
2015-1-22
90^120
793,315.00 2015-1-30 izo~iao
50. 000. 00 2O'l 5~6—l
30 "60
户厂 L
I L 3
4 成 （= U B 公
C
公 口公 蚣 X M
E A ' B 公 讼 讼
F 公 G
公 C 公
G 7
f g ID 11 r *5 13 14= :n a
“■ _ U
:/ IB n 2)
31 雹 23 21
?□一
n -巳一巨 司司=口 司一n —u 司一
r5_司一n-n 言-司 一一 司亘
令公公公为公公会公会公会公会公丞公公公公鸯
tan9101112131415161718152021襄
2sm
的& 339. 00 5Sl f
330. 30 1?3. 930, 00 4LL,我S. 00
905,。

口w. io
诫 8S1. DO 865,
2C7, 00 3ZB, 31% g
732,471^00 623, 844. 00 113. 270. DO G63, 506. 00 939, TOT.
00 29B, 457. 00 43D, 173. JO 9SL 488. 00 421, 990. 00 t>7Z t OE6. JCi 935,豌 00 793, 31E. Q0
so^onc^ao
叫醇区间 -10000 未到期
0 0^30
3：
削W
6J
9,i
有“12「
12?
L 就JSD 130
LBO'360
那】
部。

％0
720 >720
一酒目&]肇如j
6、勾选“表包含标题”确定。

如图乙形成一张右下角带有“小三角”的表格区。

这样,
（图
6）
只要在明细下方添加数据，表格就会自动扩充区域。

twwnsH-
E A A B B 旬司司司司司司司司
公公公公公公公公公
G c H I J K J L E
应收账教明细表
?ni ^-7-a
&92, 369.如
2015-4-26 60^90 581, 330. 00 2015-9-12 未到期
173, 2015-7-4 ok
411, 705. 00 2015-3-22 90^120 905, 002, 00 2015-5-11 30^60 525, 891. 00 Z015-1^2 180"3&0
865, 207. 00 2015-7-26 未到期
328f 316. 00 2015-1-3 180"3G0 732, 471. 00 2015-6-5 30^60 623, 844, 00 2015-6-13 0，v
30
118, 20in-8-31
未到期 6S* 50G.。

0 2015-9-14
未到期
939, 707. 00 2015-5-28 30^60 296, 457. 00 2015-3-1 120^180
430, 173, 00 2015-7-25 未到期 93L 438- 00 2015-7-12 未到期 421. 930. 00 2015-3-8
未到期 &72, 086.。

0 2015^10-27
未到期
935, 590.如 2015-3-22 90*120 793, 315.。

0 2015-1-30 120^180 50, 000. CI0 Z015-6-1 30^60 I
（图7）
,如图8
7、选定图7的表区域，插入“数据透视表” -J
:FtH QsA^?rw5S
Z
Ml HI
宣
J J 诚
«
亦 骸3 空片共左宜住驮 SmiartArt
AS
- A 客户
忘 B C
T
开日胡 应也嚣莪明串农 ； 2DT 5-7-S 1
60 "90 未
到匪 。

侦 构"】30 2U1W-26 Z015-5-1Z 3015-T-4 ZW5-3-2Z 掩£ 369. 00 5Bl r 350. TO
IIS ； B3O. DC
411. TOfS.OO 司司q
l
f ?
司司
司
n 司
；司司司n
司71司 母含公志公公£
公公或<|
公公公
卷也心 奇
A 4 E T J A 匚 F .
-F G 3
会公齿£匚狷,IM , > J 成为m.QQ ：I ：5,、：，. ■:: C2B, 31 i. C)
732; 471. C O
E23P B44.Q0 11B; 270. 00 E5鸟削& 00 始鸟7OT . AC 4L '. i t^ LTS. 00
物.泗,00 M 雾 086. 00 y-... ■..
^315.(0 50.
noo. ro
W 叫亍Mil 3015-1-3 201^7-26
2015-1-3 2015 &3 2015-G-13 2015-8-31 2015-^14 worn 官 2CH ・1 W1S-7■跆 aois-B-B 2Q15-
1O-3T 2015-3-22 ZgL^^SQ
301S-fi-l
3Q 飞。

iBQ^aep
未到明 1BC'3£：＞ 3Q"1iO
OF
未利朗 未剖匪 30 "60
异
"
■IK
未刊薜 未，昨 未可匪
的"1网
8)
得到图9效果:
t E 匚
U t 1
2 9IREIHR- 20L5-7-9
3（ra. oo 3015^4-36
M T K
ia[,3?L o
20] S-S-T2
—札胡
17E r 3：0. .0Ml 5-7-4
0^30
illj 705. Otl 2O15-3-Z2 方“1河
g05,002.00201^-5-11部飞。

乂
2B1&-1-2 180^60
3G5,2i ：7. UOECI15-F-BE
=到期
由黑队&如a>i5-i-a i 修
Q MO
,母 _.,4H 一一。

201b-&-6 打"M G2 \8H. 00 20] 6-6-13 ^2fi-i3-ri
三节期
由一"5 一土加2015-5-14米争期 .明乳一加2015-5-3 30^611
若％「7一门口CO15-3-1I ；O"IBD
432 LT 一如2015-7-^
ffl 937,4S3. i ：02O15-7-L2
户到期 灯的
2015-8-9 集型.期 押。

崩.加
2015-10-2? 正5朋
93S, QW. 00
ZOLO-3-Q
9T12Q
T93,315.00 2015-1-50 I 割"l B&
5. 'JJ0.O0
2»15-6-1
£.「叫-
图9就是简易的账龄分析表了，这时可以测试一下，增加明细后，刷新一下分析表，新增的数据也就自动汇总到分析表
中去了，如图10
： A
E
.
D E
1
应收曝■明细表
Inli
司司司司司司旬司司司司fl / mH 公
会公公舍公公公公公公“.H 前
~30
30*60 仙飞。

1"蜘
G23844
6轮3
575891
&65-2O7 2707311
7：
S2E-1S
531^30 1642117 17I93U
I18Z7O 29223D 431103
4I1T05
wgm
&5950B
6595：»6 S5S4W
12561^4
颂％ oom
TO74B8 9^7^90 935S90
421%&
1 357570
&颓86 &T208&
SOOOD
rgssiEj
S43SIB
797774 M3T1R0
1347295
lOBWU
R54Z0T E 舵HO
IgO^AlT
（图9）
2 S t 5 & ,7 R & in 11
5 6 7 8 1—1 1X 1- -1X
埴报日期： ana-TT
692. 3院.00 20L5-4-26 6C 90 E-CDFAACbA
58L 33L.00 1T& 930. 00 <illj 705. 00
知冗皿.0Q
5瓯
301.00
8A5.
207. no 3
调
31G. 00 732. 471. IJU 623 844. 00 2015-^-12 未到期
2D15-7-4 *
2015-3-22 90*120 2015-5-11 3。

“而 2D15-1-2 16C~360 20L5-7-26 未到朝
2015-1-3 100^300 2D15-6-5 30*^60
20L5-E 13 。

侦
118」M 00 659d H0£.00 9蹈■ ■
全第 457. Oti
2015-9-31 未到期
2D15-3-1
2015-7-25 未到期
30"60
未到期 93T j
00
2UL5-7-12 未到 期 4土，3SC.OO 2D15-6-8 未争 朝 6性』OBE. 00
2015-10^27 未到职 班岛帝0. 0口
2。

1亍卜Z2 虻 20 ■793」MIE. 00
2015-1-30 1涌~ 18。

Ro. onij. on ______ z ntrris--
_3D1J
司司司司副司司司司司司urT=l'.
司一=5'_la _巨□
. ■会盛安鹤公*&仰€帼*4|会公登*
公妥公整 A B c D F A 1
_B E A r FccnnlJEJLL
直
■口二巨
.
︹司=□.司=口
J5 K公司GOOj 000. 00 20L5-6-10 0~30 J
2K
27（图10）新增K公司应收账款60万，到期2015 — 6- 10 ,账龄为0~30天，再将账龄分析表刷新一下，就自动添加了这条记录了。

如图11
icrosoft Excel •
圄开发工具
应收账款账龄分析表二
求和项：应收款列标签H
行标签00*30 A公司
B公司
C公司
D公司
E公司
F公司
G公司
H公司
I公司
J公司
K公司
L公司623844
173930
30*60
732471
905002
939707
总计
50000
60“9090^120 120^180 180*360 未到期总
692369525891
328316
865207
581330
118270
411705
935590
659506
296457
430173
937488
421980
672086
793315
1397774 2627180 692369 1317295 1089772 854207 4686010 1。