Excel在财务中的应用 课件 项目三 资金业务管理

项目三资金业务管理任务一

资金对账单的设计目录01

对账单基本信息创建02

对账金额的汇总与查询任务一

资金对账单的设计800万元预计回收货款1430万元(采购款690万+工资350万+其他390万)预计总支出

（

5

0

0

万

元

+

8

0

0

万

元

)-1430万元

130万元案例导入：科柏达公司的资金困局截至2025年底，公司面临严重的资金紧张问题。资金缺口：130万元500万元银行存款

精准催收：加大催收力度，

目标回收至少100万元逾期账款。

计划还款：根据可用现金，按比例规划还款方案。

强化管理：提升资金管理的专业化与自动化。

定期分析：利用Excel进行深入的财务分析，

防范风险。“我们如何快速、准确地知道应该向哪些客户催收，以完成这100万的目标?”总经理决策：数据驱动的解决之道●

快速切换：通过下拉菜单选择任一客户。●

信息联动：自动显示客户基础信息。●总览全局：即时汇总总金额、已付、未付。●

明细清晰：自动罗列所有交易详情。我们的目标：一站式动态资金对账单核心功能●在

S18，S19单元格手动输入电话和地址。●

在

U18单元格使用函数=TODAY()自动生成日期。●

取消“Internet及网络路径替换为超链接”选项，规范录入邮箱。·复制“销售档案”表中的“对账单编号”列到M列。●使用「数据」选项卡->「删除重复值」功能。任务1：创建对账单基础框架操作二：创建唯一的对账单编号列表操作一：录入公司信息案留□

用

面

回

—+110%81113121094526371高级提示快捷键：Alt+D+L(数据验证

)

,F3

(选择名称

)

。1.

定义名称：选中M列唯一的对账单编号->「公式」->「根据所选内容创建」,选择“首行”。2.

设置验证：选中目标单元格U19-

>「数据」

「数据验证」。3.

配置来源：允许“序列”,来源输入“=对账单编号”。任务1:让对账单“动”起来(创建下拉菜单)核心技术：数据验证

(DataValidation)任务1:自动匹配客户信息——

VLOOKUP函数应用VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)根据U19单元格选中的“对账单编号”,自动从“销售档案”和“客户档案”工作表中查找并返回对应信息。=VLOOKUP($U$19,销售档案I$A$1:$J$18,2,0)=VLOOKUP(SS$23,客户档案!$A$1:$DS7,2,0)=VLOOKUP($S$23,客户档案|$A$1:$D$7,3,0)关键函数示例公式·

客户名称

(S23):·

联系人

(S24):·

地址

(U23):逻辑SUMIF(range,criteria,[sum_range])关键函数任务2:汇总对账总额——

SUMIF

函数应用在“销售档案”表中，计算每个“对账单编号”对应的总金额、回款总额、欠款总额。3.

修改求和区域，计算回款汇总和欠款汇总。1.在“销售档案”表中新建三列：金额汇总、回=SUMIF($A$2:$A$18,$M2

,$H$2:$H$18)2.在

N2

单元格输入公式：款汇总、欠款汇总。操作目标从我们在“销售档案”表创建的汇总列中，查找当前选中的对账单编号所对应的总金额、

已付款和未付款。任务3:在主界面展示汇总金额再次使用VLOOKUP

函数。示例公式方法逻辑金额：S30单元格中输入公式=VLOOKUP($U$19,销售档案!$M$1:$S$8,2,0)”已付款：T30单元格中输入公式=VLOOKUP($U$19,销售档案!$M$1:$S$8,3,0)”未付款：

U30单元格的公式为“=VLOOKUP

($U$19,销售档案!$M$1:$S$8,4,0)”。思考我们如何才能让Excel区分同一客户的第一笔、第二笔、第三笔交易?对账单编号：

2025-001一个客户(一个对账单编号)通常有多笔交易。标准的VLOOKUP

只能返回它找到的第一个匹配项。我们需要为每一笔交易创建一个独一无二的“身份证”——唯一主键

(PrimaryKey)。新的挑战：如何查询多笔交易明细?解决方案交易2交易3交易1问题目标

函数技巧：COUNTIFS(criteria_range1,criteria1)任务4:创建唯一主键(第1步)——COUNTIFS的妙用通过锁定起始行($B$2)

但不锁定结束行

(B2),我们创建了一个动态扩展的计数范围，从而实现了“滚动计数”的效果。=COUNTIFS(

$B$2:B2,B2

)为同一对账单编号下的每笔交易生成一个递增序号=COUNTIFS($B$2:B3,B3)->

范围：$B$2:B3=COUNTIFS($B$2:B4,B4)->

范围：$B$2:B4=COUNTIFS($B$2:82,B2)->

范围：$B$2:B2结果：为每个对账单编号生成递增序号(1,2,3.…)。任务4

:创建唯一主键(第2步)-拼接最终主键`TEXT(B2,"00")

将序号格式化为两位数(01,02),确保主键格式统一且专业。目标：将“对账单编号”和我们刚创建的“序号”合并成一个唯一的ID。=C2&"-"&TEXT(B2,"00")方法：使用`&连接符和`TEXT`函数。2025-001和2→2025-001-022025-001和1→2025-001-01结果例如：完整公式(以日期

R35

为

例

)

:=VLOOKUP($U$19&“-"&TEXT($Q35,"00”),销售档案!$A$1:$L$18,5,0)任务5:精准查询：VLOOKUP与主键的强强联合表单设计

销售档案==IFERROR(VLOOKUP($U$19&"-"&TEXT($Q35,"00"),销

售

档

案!$A$1:$L$18,5,0),"")任务6

:追求完美-使用IFERROR函数优化显示如果公式计算成功，则显示结果；如果出错(如`#N/A'),

则显示我们指定的内容(此处为空白"")

。如果一个客户只有3笔交易，但我们的明细表有10行，那么多余的行会显示#N/A丶错误，非常不美观。用IFERROR、函数“嵌套”我们原有的的VLOOKUP公式。Before

After升级后公式(以日期R35为例):解决方案IFERROR逻辑问题技能回顾：我们掌握的Excel“神器”COUNTIFS&TEXTVLOOKUP

(基础与高级应用)ExcelToolkit交互设计逻辑构建体验优化数据汇总数据查询数据准备删除重复值数据验证定义名称IFERRORSUMIF自动

化(Automation)将繁琐的手工核对变为自动化查询，极大提升效率。标准化(Standardization)统一的对账单格式，

减少沟通成本和错误。可视

化(Visualization)将枯燥的数据表格转化为直观的业务洞察界面。数

据

驱

动(Data-Driven)为催款、资金规划等决策提供及时、准确的数据支持。总结：从数据到决策的价值升华任务二

应收账款业务分析目录01

应收账款登记表的制作02

应收账款催款单的设计任务二

应收账款业务分析03

应收账款分类统计

04

应收账款账龄分析05

应收账款坏账计提核心素养：成为数据驱动的财务专家你手中的Excel,

不仅是表格工具，更是驱动商业决策的引擎。维护良好客户关系。

识，恪守财务职业操守。强化资金规划与风险意识，保障资金安全。培养以业务为导向的资金价值研判能力。树立“合规为先”的职业意提升跨部门沟通效率，维职业操守风险管控战略思维高效协同政策背景：党的二十大报告要求“主动防范化解风险”——这是财务人员的核心使命。应收账款作为核心流动资产，直接影响资金回流、

生产运营与供应链稳定。1.精准追踪

2.

风险预警3.高效回收

4.决策支持资金是企业的

“血液”,风控是管理的

“心脏”(Re-investment)销售(Sales)(Receivables)核心定位：应收账款现金回流Quote再投入(Cash)逻辑判断

(The

Brain)

容错处理

(TheShield)Excel

核心实操工具箱辅助功能

(The

Interface)统计汇总

(TheCalculator)SUMIFS:

多条件求和-自动汇总特定客户的欠款IFERROR:IFERROR(公式，错误返回值)-避免AND:AND(条件1,条件2)-多重条件锁定IF:IF

(条件，真值，假值)-决定款项状态条件格式：视觉预警(红灯)数据验证：下拉菜单(防错)#N/A报错，保持表格整洁实操模块一：构建应收账款登记表J5=G5+E5销售日期+账期应收金额-已还款金额应收日期剩余应收I5=F5-H5状态判定：多重逻辑嵌套=IF(H5>=F5,"已结清",IF(K5="催收","超期未结清","未结清"))嵌套逻辑就像层层过滤的漏斗，优先处理‘已结清’,再识别‘风险’。No

未到期If

Prompt=

'催收?If

Paid>=

Owed?超期末结清(Overdue)已结清)(Unsettled)StartYesYesNo量化风险：超期天数计算==IFERROR(IF(L5="已

结

清","",IF($L$2-J5>0,$L$2-J5,"")),"")格式检查

(Format

Check):

请确保单元格格式

设置为‘常规’或‘数值'(General/Number),

否则可能显示为日期乱码。

2.Calculation:

Today(SL$2)-DueDate(J5).3.

Validation:Only

show

if

result

>

0.Pre-check:Ifstatus

is

"已结清",leave

blank.INFORMATIONVISUAL设置规则：使用公式设置格式：填充红色选中区域：C5:M21步骤指引

(STEPS)=$K5="催收"可视化的风险预警利用条件格式让风险“一目了然”结果预览(RESULT)设置规则(SETUP)应收赔眈登记表数据验证

(Validation)在单元格E7设置“序列”

(List)>来源按F3选择名单。数据清洗

(Cleaning)复制客户名单→删除重复值(Remove

Duplicates)。定义名称

(Naming)将清洗后的名单定义为“客户名称”实操模块二：催款单设计——数据标准化标准化输入是自动化公式正确运行的前提。INFORMATION

VISUAL核心攻坚：

自动化欠款汇总

(SUMIFS)=SUMIFS(I:I,C:C,E7,K:K,"催收")只统计真正‘超期’的金额，避免误伤正常账期的款项。登记表!C列(客户名)

=催款单!E74

求和区域KeyTakeaway

Box条件区域2(Criteria2)条件区域1(Criteria

1)=SUMIFS('应收账款登记表和到期提醒'!$I$5:$I$21,'应收账款登记表和到期提醒'!$C$5:$C$21,催款单!E7,'应收账款登记表和到期提醒'!$K$5:$K$21,"催收")”)登记表!K

列(状态)(Sum

Range)(剩余应收)登记表!I列="催收"专业呈现：金额大写转换

核心公式：=TEXT(F8,"[DBNum2]G/通

用

格

式")&"元

整"

格式代码说明：'[DBNum2]'转换为中文大写，"G/通用格式”保持数值完整性"&"元整""添加后缀。信息

(Information)中文大写金额：用于财务票据、合同等正式文件中，防止篡

改。fx=TEXT(F8,"[DBNum2]G/

通用格式")&"元整"B

C

D

E

F请写一个Excel公式，将单元格F8的数字转换为中文大写全额。记不住格式代码?直接询问陆拾贰万肆仟元整AI智解陆拾贰万肆仟元整Visual624000(Modern

Tip)(大写)(数值)624000151310Al:1412115689F8A7修改任意客户的“已还款金额”,观察状态列是否自动变色?尝试为催款单增加

“超期天数”字段(提示：VLOOKUP)。使用透视表找出欠款最多的前3名客户。课堂测试

(Test)课程总结与实操任务“掌握数据，就是掌握风险的主动权。

”可视化：ConditionalFormatting自动化：SUMIFS,Data

ValidationKnowledge

Recap逻辑：

IF,AND,IFERROR实操模块三：应收账款分类统计数据透视表(Pivot

Table)—

海量数据的克星无需编写复杂公式，通过简单的点击即可建立强大的分析模型。放置位置(P5)选择数据源插入透视表Informationvisuale

BKBC姓B单R

-sr

l粮良阻5n串n拖拽即分析：多维数据透视信息

(Information)动态更新：右键刷新即可同步源数据。按状态看：多少钱已超期?按客户看：谁欠得最多?Visual优化技巧：在切片器设置中勾选“隐藏没有数据的项”,保持界面整洁。2.插入切片器→实现动态交互筛选进阶设置：日期组合与切片器交互1.右键日期→组合→选择“月”Element

1:DateGrouping

LogicElement2:SlicerInteraction月(

日期)

1月充计价值合计1月1,8002,04324310月12月11月4月1月9月7月8月1月=IF(AND(SF$3-SE8

>P$5,SF$3-SE8<=PS4,S08="未回"),

SN8,"")0天

30天

60天

180天实操模块四:应收账款账龄分析——逻辑构建区

间

1

区

间

2账龄区间：0-30天，31-60天，61-180天…当前日期

(F3):2025-12-26(关键!确保拖拽公式不出错)(大于下限且小于上限

)(当前日期-发票日期)时间差计算(仅统计未回款项)绝对引用状态过滤区间判断账龄分析——结果呈现与汇总ABCDEFG

H1单位名称日

期0-30天31-60天61-180天>180天2海格2025-12-051200.00Pivot

Summary管理层视图(汇总)3普诺弟2025-07-0513200.004普诺弟2025-03-071440.005东福2025-09-0423250.006东福2025-09-041260.007东福2025-09-057500.008东福2025-10-0843400.009东福2025-12-051,200.0010东福2025-09-05750.0011东福2025-07-051,440.0012海格2025-07-051,440.0013普诺弟2025-12-0513200.0014东福2025-09-0423250.00通过公式填充，将每一笔发票15东福2025-09-041260.00精准归入对应时间范围，形成清16东福2025-09-057500.00风险地图。17东福2025-10-0843400.0018总计数0-30天：

¥1,20031-60天：

¥1,44061-180天：

¥45,210

>180天：

¥43,400合计：

¥91,250实操模块五:应收账款坏账计提

—

数据准备1.

超级表

(SuperTable):

数据源增加时公式自动扩展。2.汇总行

(TotalRow):

一键获取各账龄区间总额，为计提做准备。会计原则：基于‘谨慎性原则’,对不同账龄资产预估损失。Visual

1:CreatingaSuperTableVisual2:TheTotal

Row11公式：=C4/$C$8坏账计提——

核心计算模型最终计提总额：31,406.50元此计算结果将直接用于填制记账凭证(借：

信用减值损失贷：坏账准备)。=Amount*Rate公式：(CellAmount/TotalAmount)任务三

应付账款业务处理目录01

应付账款的付款分析02

付款单的制作任务三

应付账款业务处理模块一：付款方案确定：业务背景与策略规则：欠款≤200,000元→全额支付(100%)

规则：欠款>200,000元→按剩余资金比例分配优先保护小微供应商公平分配剩余资金可用资金

(Cash)2,500,000.00

元当前债务

(Debt)6,924,686.25元偿债策略

(TheAlgorithm)资金缺口步骤1:全额支付逻辑

(小额优先)SupplierDebtB2

=IF(

B2<200000,B2,0

)筛选结果：

共3家小额供应商，

占用资金203,731.08元。If

Debt<

200,000?Pay

0(WaitforStep

2)YesPay

Full步骤2:按比例分配逻辑

(大额统筹)(The

Ratio)计算剩余资金池Excel

公式实现计算分配系数(The

Pot)区分大小额欠款=ROUND(F(B2<20000,B2

,B2/3),2)大额按三分之一计算保留两位小数

嵌套公式计算最终支付额核心目标应收账款

(AR)风险识别(坏账)应付账款(AP)资金合规(偿债)核心Excel工具Pivot

Table

(数据透视表),

Slicers(切片器)Complex

Formulas(IF/SUMIF/ROUND)逻辑难点AND函数构建时间区间加权比例分配与尾差调整易错点绝对引用($)缺失精度差异(分钱误差)Noto

Sans

SC核心知识点综合对比课堂实操练习&常见问题

(Q&A)●

Task1:应收：使用提供的明细表，制作带切片器的月度账龄透视表。●

Task

2:应付：假设可用资金为180万，

限额15万全金额支付，

计算分配方案。课后作业：提交Excel结果文件至教学平台。15分钟实操演练(Practice

Drill)常见问题(FAQ)A:

检查工作表保护状态或数据源是否包含合并单元格。A:

检查日期格式是否统一Q:

坏账计提比例能改吗?A:可以，应根据企业实际坏账率和会计政策调整。Q:

账龄公式显示为空?Q:

透视表字段拖不动?为

'YYYY-MM-DD'。1.确定字段

2.细化属性3.绘制草图4.Excel

实操模块二：财务付款单制作流程从内容设计到工具实现的完整闭环步骤1:字段定义与合规性要求负责人

→

财务

→

部门

→

申请人收款单位全称、账号、开户行人民币大写+小写金额日期、部门、项目名称结算方式、用途(严禁简写)(防篡改)年

月

日部门>(项目收款单位结算方式大写金额小写金额：¥用途签字申请人：申请人：部门：财务：财务：负责人：·

顶部：

标题+日期·

中部：收款方信息+

结算方式·

下部：金额(大写突出)+用途·

底部：签字审批栏步骤2:绘制布局草图付款单布局逻辑Excel

实操1:表头与框架搭建利用“合并后居中”构建区块，加粗标题字段字号20,加粗，

双下划线学称A2付款单C

Df

付款单对方方式够够MhANB6AB部

门C

DEFH项目名称JKL

M7全称结算方式8收款单位账号网银现金电汇

支票微信其结算方式9开户厅10金额人民币(大写)

仟佰拾

万

仟佰

拾元

角

分¥11用途日常运营费用建设其他费用Excel

实操2:金额栏规范设置技巧：利用空格调整字符间距，方便手工填写。添加货币符号¥,设置左对齐

，确保数字清晰。Excel

实操3:插入复选框交互元素插入矩形形状，模拟复选框体验口又弹

开傅

入等绝酗磁

口购琵报巩BR期

公名

855811

·A

A贺边腿田

下E8(Q)独用瓶缩-愿泵xisx

Excel8

BSB8

Tableau

形0)

PDF工妨井方式段

邓藏面附件然5重用袋8错软

醉元艳T&数字

俘式怕式地元格犯P凡R盛RL隐能错验发贬

.A6田上E致(P)A

B田

左

描

(

)田石框8(R)G

H

1

J

K

LMN1A

5田无用5(N)位款单田尼限保LA)年

月

目67部门田

外

朔

8

E

L

S

)叶阳RC)田

双

压

假

(

B

)Ⅲ和下形线(H)□上下程挑ID)田上健施孔甜下能8(C)

田上旺缓FDX下能镜(U)

施就助盟函

绝

陋

边

K

Y

)绝图K报骑格(G)翔特边图IEZ

盛

宋

幽

E(I

)组9(Y项员名称就单拉就雾方式8硕旅现金电汇文蒙值其的炖算方北31011121316[151617虚锁¥用途健设其他院用负责人部「1负责人申谓人田舞他边柜LM)

…1.确定字段|2,画鄢图3.制摩4,林本

参数⑨就馆计5$:24田圃

回85%□选中主体区域□设置“所有框线”□检查文字对齐(居中/左对齐)Excel

实操4:视觉标准化

(框线设定)2防止非相关人员篡改表格结构

保护后的工作表，

无法进行编辑Excel

实操5:文档保护与交付审阅->保护工作表->

设置密码最终成果：打印设置与

PDF

导出导出的清晰

PDF页面4C98

乙

②任务四

资金业务可视化设计目录01

资金业务数据透视表设计02

资金业务数据透视图设计任务四

资金业务可视化设计课程导入：从繁杂数据到精准决策透视图设计：能够制作组合图(双轴)、饼状图(结构)、堆积柱状图(分布)。资金业务数据量大、账龄结构复杂。纯数字表格难以快速捕捉风险点，决策效率低。背景学习目标

实战应用：独立完成应收账款的可视化分析。透视表搭建：掌握字段计算设置、占比分析与格式美化。·

行区域：拖入[单位名称]·

值区域：依次拖入各账龄字段(0-30天、31-60天、

61-180天、

180天以上)提示：确保选中数据源区域无空行。操作需在“数据透视表分析”或“设计”选项卡下进行。Step1:打开“资金业务可视化”工作表→

“插入”→“数据透视表”。基础搭建：创建数据透视表Step

2:

字段布局策略1.选中值区域中的账龄字段(如“一个月”)。2.

单击下拉框→选择“值字段设置”。3.计算类型：修改为“求和”。4

.

名称规范：修改自定义名称为标准格式(如“0~30天”)。核心设置：修正值字段计算类型Excel默认可能将数值统计为“计数”而非“求和”。注意：对所有账龄字段重复此操作。修正步骤：问题：1.新增字段：将[金额]字段两次拖入值区域。2.

重命名：分别命名为“应收合计”与

“占比”。3.

占比设置：打开[占比]的“值字段设置”→切换至“值显示方式”选项卡→选择“总计的百分比”。深度分析：添加“占比”透视字段概念：不仅关注绝对金额，更关注风险权重。操作步骤：1.单击“单位名称”右侧下拉框。2.

选择“其他排序选项”。3.设定规则：降序排列

(Z

到A)→

依据字段选择“占比”。优化排序：聚焦关键风险单位结果：列表将自动按欠款占比由高到低排列，重点一目了然。整理：双击行标签，重命名为“单位名称”。排序逻辑：谁是最大的欠款方?操作：进阶图表一：制作双轴组合图

(Combo

Chart)1.选中新透视表→

“插入”→

“图表”→“组合图”。2.

系列设置：·

应收合计：簇状柱形图(主坐标轴)·

占比：折线图(勾选次