简介这是一套面向银行、证券等金融从业者及财务、销售、人力管理人员的Excel高效应用培训资料由资深实战讲师韩小良编写聚焦数据管理与分析能力提升适用于贷款统计、经营分析、预算执行追踪等日常管理场景。资料按六大模块组织基础数据清洗与规范、公式与函数实战、表格可视化与条件格式标识、多工作簿快速汇总、数据透视表应用以及动态图表表达整体结构完整既讲操作技巧也强调分析思维。包内含1个PDF格式电子文档大小约249KB便于阅读和检索。已有156人浏览学习。书中收录授信品种、信用等级、行业等维度的统计分析报表编制方法并配有贷款周报月报、合同动态跟踪、两轴组合图等典型案例可帮助读者快速定位关键信息、提炼数据价值使分析报告更具说服力。1. Excel 金融数据分析最先要改的不是函数在金融机构做数据分析Excel 仍然比 Python 更适合大批业务人员。韩小良这门课程 PDF 给人最直接的印象是开头两章不教 VBA不堆公式而是反复处理非法日期、文本型数字、合并单元格这些“脏数据”。实际维护过贷款台账、授信周报、同业存款报表的人都有体会SUMIFS 和数据透视表一用就错问题往往出在源表结构而不是函数记不熟。Excel 效率专家和普通用户的差距多半在数据清洗与表格规范而不是背多少条公式。课程六个部分反复出现的重点其实就是数据标准化、条件求和、透视表周期统计、组合图表这些高频场景适合财务、客户经理、风险分析和信贷运营岗位参考。2. 表格数据标准化非法日期、文本型数字与合并单元格的处理金融系统导出的明细通常来自核心业务系统、信贷管理系统或资金系统导出后的表格往往给的是“让人看得懂”而不是“让 Excel 算得动”。从外部系统复制出来的金额可能带着空格和全角逗号客户编号被 Excel 自动转成科学计数法日期字段在单元格里显示为“2024.06.30”。这些问题不在源系统解决留在报表阶段处理每做一次周报就重复踩一次坑。2.1 三类脏数据的特征与判断我拿到一份新表不会直接插入透视表而是先检查几个位置列标题有没有重复、空行空列多不多、日期区域是否真的是日期类型、金额列能不能正常求和。一个快速检查技巧是选中金额列后看状态栏如果只显示计数而不显示求和那这一列基本是文本型数字。脏数据类型典型表现快速识别方法文本型数字左上角有绿色小三角SUM 结果明显偏小选中金额列状态栏无求和非法日期显示为 2024.06.30、20240630改格式无效ISNUMBER(A2)返回 FALSE合并单元格透视表行标签出现大量空白F5 打开定位选择“空值”定位特殊字符金额中夹带空格、换行符、不可见字符TRIM(CLEAN(A2))A2返回 TRUE这个表看起来简单但几乎每个信贷分析项目都会用到。第一列是现象第二列是肉眼看到的样子第三列是判断方法。把第三列的公式放到辅助列能很快定位修正后仍然异常的数据。例如文本型数字列里用ISNUMBER检查并配合条件格式标红比肉眼找小三角更快金额列里的空格从源头上看可能是财务软件导出时的千分位补位如果转换后仍有差异就回到系统导出的原始文件核对不要在 Excel 里反复替换。2.2 批量转换文本型数字的两种做法最常见的稳妥方式是用选择性粘贴。先复制任意空白单元格选中需要转换的金额列右键“选择性粘贴”操作里选“加”Excel 会将文本型数字与 0 相加强制转成数值。这样做不会破坏原有数字也不会像“分列”那样要求列宽。但遇到多列数据逐列操作仍然很慢。我更常用下面这个 VBA 宏把选区里“看起来像数字但实际上是文本”的单元格批量转成数值适合处理从信贷系统导出的多个工作表Sub ConvertTextToNumber() Dim rng As Range, cell As Range On Error Resume Next Set rng Selection.SpecialCells(xlCellTypeConstants) On Error GoTo 0 If rng Is Nothing Then MsgBox 当前选区没有常量单元格 Exit Sub End If For Each cell In rng If IsNumeric(cell.Value) And TypeName(cell.Value) String Then cell.Value CDbl(cell.Value) End If Next cell End Sub这段代码先通过SpecialCells(xlCellTypeConstants)只选常量单元格避免把公式结果误转。IsNumeric(cell.Value)判断单元格内容是否具备数字特征TypeName(cell.Value) String进一步确保当前是字符串类型二者同时成立才执行CDbl。如果单元格本来已经是数字直接跳过避免重复转换带来的精度问题。选中区域后按 F8 逐步执行能更清楚看到每个判断分支实际批量使用直接运行宏即可。注意如果原始文本是“1,234.56”这类带千分位的写法IsNumeric会返回 False此类数据需要先删除千分位分隔符再转换。2.3 修复非法日期与特殊字符文本型日期是最容易导致透视表月份乱排的原因。对“2024.06.30”这种用点号分隔的日期我一般不会只改单元格格式而是用公式生成一个真正的日期值。格式固定为年月日时可以直接按位置取数DATE(LEFT(A2,4), MID(A2,6,2), MID(A2,9,2))LEFT(A2,4)提取年份“2024”MID(A2,6,2)提取月份“06”MID(A2,9,2)提取日期“30”。注意 MID 的起始位置从 1 开始第 6 位是月份第一位。如果原始数据是“30.06.2024”这种日月年顺序公式要换成DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2))。两种顺序不能混用否则会把日和月颠倒。也可以尝试DATEVALUE(SUBSTITUTE(A2,.,-))但这个函数受 Windows 区域日期格式影响在部分办公电脑上会返回#VALUE!所以按位提取的公式更可控。去除金额列里的特殊字符可以用TRIM和CLEAN组合TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160),)))CHAR(160)是不换行空格很多从网页端复制的数据会带上。CLEAN删除前 32 个 ASCII 控制字符TRIM清理多余空格最后得到干净文本。处理完后用“选择性粘贴为数值”覆盖原列避免公式残留影响下次导入。2.4 合并单元格的取消与填充合并单元格在审批表里很直观但在数据分析中会引发严重问题。取消合并后只有左上角第一行有值其它行是空格透视表会把它们当“空白”类别统计。处理方法是选中合并区域取消“合并后居中”保持区域选中状态按 F5 打开定位条件选择“空值”在编辑栏输入等于上一个单元格比如 E3 空值输入E2最后按CtrlEnter批量填充。这样就把层级关系转成了重复的明细字段。如果要在每个分行名称后面保留分隔感可以再配合“按颜色排序”做视觉分组但数据表本身不要再使用合并单元格。提示数据标准化不是一次性的应该固化到每个月的操作流程里。建议在原始数据工作簿之外单独建一个“清洗”工作表保留一步步操作痕迹后续审计时能说清数据来源和修改过程。3. 授信与财务公式SUMIFS、INDEXMATCH 和日期函数实战课程第二部分的设计思路是“从业务问题出发选函数”不是列一张函数公式大全清单。在金融报表里高频任务无非三类按多条件汇总余额从合同台账带出利率和到期日以及判断是否逾期。这三个问题分别对应SUMIFS、INDEXMATCH/XLOOKUP、日期函数。3.1 用 SUMIFS 按客户、担保方式、逾期标志汇总如果还在用筛选后 AutoSUM很容易漏算隐藏行。SUMIFS对多个条件取交集公式写完以后不需要反复筛选。SUMIFS(贷款余额列, 客户名称列, A公司, 担保方式列, 抵押, 逾期标志列, 否)第一个参数贷款余额列是要加总的数值列之后每两个参数构成一组条件分别是条件区域和条件值。条件值为文本时需要加英文双引号引用单元格时去掉双引号例如$H$2表示客户名称写在 H2。条件区域必须与求和区域同行数否则返回#VALUE!。如果有多个条件要取“或”关系SUMIFS做不到应改用SUMPRODUCT或分别求和后相加。SUMIFS对文本比较不区分大小写但对前导空格敏感所以第 2 章的清洗步骤会直接影响求和结果。3.2 用 INDEXMATCH 带回合同台账字段VLOOKUP 只能按区域第一列查找返回右侧列的值当需要按“合同编号”查找并把“利率”“客户名称”“到期日”一次性带回我一般用INDEXMATCH组合。INDEX(合同台账!$A$1:$Z$1000, MATCH($A2, 合同台账!$B$1:$B$1000, 0), 3)MATCH($A2, 合同台账!$B$1:$B$1000, 0)在 B 列中精确查找当前行的合同编号返回行号INDEX根据行号和第三参数“3”从 A 到 Z 的区域里取对应值这里第三列是利率。如果查找不到MATCH返回#N/A可在外层套IFERROR显示“未签约”或 0。注意查找区域$B$1:$B$1000不能出现重复合同编号否则返回第一个匹配项这在合同台账重复时会导致利率带错不能只依赖函数解决还要先做去重检查。如果使用 Office 365 或 Excel 2021XLOOKUP更简洁XLOOKUP($A2, 合同台账!$B$2:$B$1000, 合同台账!$C$2:$C$1000, 未找到)参数分别是查找值、查找区域、返回区域、找不到时的值。XLOOKUP不需要返回列号也允许查找区域在返回区域右侧阅读和维护都更容易。3.3 日期函数贷款期限、到期日与逾期判断金融分析中日期函数的关键是计算两个日期之间的实际间隔。DATEDIF是 Excel 的隐藏函数输入时不出现在函数列表但可直接手写DATEDIF(放款日, 到期日, M)第三参数M返回整月数改成D返回天数Y返回整年数。需要注意DATEDIF在早期版本中的边界行为并不一致例如到期日是月末最后一天时计算结果可能比预期小一个月。如果遇到争议改用YEARFRAC(放款日, 到期日, 1)*12得到带小数的月份数再按业务口径四舍五入。逾期判断是财务管理中非常常见的场景。一个简单的动态公式如下IF(AND($D2TODAY(), $E2结清), 逾期, 正常)TODAY()返回当天日期公式每次打开文件都会重算因此判断结果会随时间变化适合做贷后监控。AND表示两个条件同时成立才会判定逾期。如果需要在月底固定考核最好把基准日放在单元格里例如$G$1公式写成$D2$G$1避免打开文件时日期漂移影响报表数字。3.4 用文本函数从复合字段提取产品类型信贷台账里经常出现“个人住房贷款-北京分行-20240630”这样的复合文本。用LEFT和FIND可以把第一段产品名拆出来LEFT(A2, FIND(-, A2)-1)FIND(-, A2)返回第一个连字符的位置减 1 后得到产品名长度LEFT截取左侧内容。如果要取第二段可以用MID加SUBSTITUTE组合或直接使用“数据到分列”按分隔符拆分。文本函数适合一次性构建辅助列如果源数据抽取逻辑不统一拆分结果要抽样验证避免把备注字段里的“-”也当成分隔符。业务场景推荐函数关键点多条件汇总贷款余额SUMIFS条件区域必须与求和区域同尺寸合同编号带出利率INDEXMATCH查找列不能有重复值普惠贷款利率版本兼容XLOOKUP需要 Office 365 / 2021计算贷款期限整月数DATEDIF第三参数为 M动态判定逾期IFANDTODAY基准日建议放单元格提取产品类型LEFTFIND注意分隔符统一4. 数据透视表与多工作簿汇总贷款统计报表怎么做才不返工课程第五部分把数据透视表定位成“贷款统计分析报表”的核心工具。要让透视表不返工功夫一半在透视表外把明细数据整理成一行一笔贷款、每列一个字段而不是一张带小计和多级标题的人工报表。原始数据中不能有合并单元格金额必须是数值日期必须是真正的日期否则透视表里会出现“空白”行和计数结果。4.1 把明细数据转换为表格区域选中明细区域任意单元格按CtrlT创建表表名称修改为“贷款明细”。这样做有三个直接好处新增数据行后透视表数据源自动扩展到表区域公式区域会自动填充筛选和排序不会破坏列对应关系。表名称在“表设计”选项卡左侧修改。创建透视表时数据源直接选“贷款明细”表而不是$A$1:$F$1000这类固定引用后续新增支行数据就不用再改范围。4.2 多维统计授信品种、信用等级、行业和企业性质课程里提到的授信品种统计、信用等级统计、行业和企业性质统计本质上都是同一个透视表操作只是行列字段不同。以“保证人信用等级”为例把“信用等级”拖到行区域“贷款余额”拖到值区域透视表默认对数值字段执行求和如果值区域显示“计数”说明该字段不是数值型或者源数据有文本型数字没有转换。此时回到第 2 章的清洗流程不需要在透视表里设置。下面是常用维度组合分析诉求行字段列字段值字段各授信品种余额授信品种无贷款余额借款人与保证人交叉借款人信用等级保证人信用等级贷款余额行业与企业性质交叉行业企业性质贷款余额分支行按周统计放款日期组合为7天机构贷款余额日期型字段放到行区域后右键“组合”可以选择“季度”“月”“日”。如果要做贷款周报选择“日”并将“天数”设置为 7而不是用“自然周”。这样遇到跨月周时不会被拆到两个月里报表口径更接近实际投放节奏。4.3 用切片器与日程表实现交互式筛选数据透视表分析能力很强但面对非技术人员交互式筛选最直接。选中透视表任意单元格在“数据透视表分析”选项卡中插入切片器勾选“授信品种”“区域”等字段再插入日程表并指向“放款日期”。用户可以通过切片器只看“抵押贷款”或只看某个分行的数据。多个切片器之间的关系是“与”即同时生效设计报表时不要放太多切片器一般两个就够。4.4 用 Power Query 快速合并同结构工作簿课程第四部分专门讲多个工作簿和工作表的汇总。金融场景里最常见的是各地支行通过邮件发来结构相同的 Excel总部需要合并成一张总表。最早的做法是 VBA 循环打开所有工作簿复制粘贴现在更推荐 Power Query。步骤如下新建一个“汇总.xlsx”把各支行的文件放进C:\Reports\202406文件夹在“数据”选项卡中选择“获取数据”从“来自文件”进入“从文件夹”点击“合并和转换数据”在预览窗口选择目标工作表Power Query 自动为每个文件生成一个查询并追加到一个表。Power Query 里对应的 M 查询代码大致如下let 源 Folder.Files(C:\Reports\202406), 筛选Excel Table.SelectRows(源, each [Extension] .xlsx), 保留关键列 Table.SelectColumns(筛选Excel, {Name, Content}), 添加数据 Table.AddColumn(保留关键列, 数据, each Excel.Workbook([Content], true)), 展开Data Table.ExpandTableColumn(添加数据, 数据, {Data}, {Data}), 展开行 Table.ExpandTableColumn(展开Data, Data, Table.ColumnNames(展开Data{0}[Data])) in 展开行Folder.Files(C:\Reports\202406)读取文件夹下所有文件Excel.Workbook([Content], true)第二参数true表示将每个工作表的第一行作为标题。Table.ColumnNames(展开Data{0}[Data])以第一个文件的列名作为基准如果其它文件多出空列或缺少列展开时会报错或产生空值。实际业务中我建议先删除最后两行展开步骤进入 Power Query 编辑器后用“选择列”和“追加”向导生成而不是直接粘贴 M 代码因为不同 Excel 版本生成的结构代码略有差异。上面代码用于理解原理动手操作时用界面点击更稳妥。4.5 批量刷新所有透视表当清洗数据和透视表都做好后每天更新数据只需要替换源数据并刷新。手动点击刷新容易漏表VBA 一键刷新所有工作表的透视表Sub RefreshAllPivots() Dim ws As Worksheet Dim pt As PivotTable For Each ws In ThisWorkbook.Worksheets For Each pt In ws.PivotTables pt.PivotCache.Refresh Next pt Next ws End SubPivotCache.Refresh会更新该缓存关联的所有透视表。如果同一个透视表数据源被多个透视表共享刷新缓存一次即可不需要对每个透视表执行RefreshTable后者重量更轻但可能漏掉缓存更新。将宏绑定到“数据更新”按钮或Workbook_Open事件时要注意如果源数据在关闭的工作簿中刷新会弹出连接窗口生产报表建议先打开源文件或提前做好数据管道。提示透视表数字格式不一定继承源数据建议在透视表值字段上右键“数字格式”统一为#,##0.00避免正式报告上出现 1234567.891 这种预设格式。5. 组合图、条件格式与动态交互把风险信号放到台面上金融类报告里图表的作用不是装饰而是让阅读者第一眼就看到异常。“贷款余额持续上升”用柱形图“不良率抬头”用折线图两个指标量纲不同放在同一张图里必须用次坐标轴。Excel 的做法是选中包含月份、贷款余额、不良率三列数据插入“组合图”在图表设计中将“贷款余额”设置为簇状柱形图“不良率”设置为折线图并勾选“次坐标轴”。次坐标轴范围默认从 0 开始如果不设置会挤压折线我一般把次坐标轴最小值设为 0最大值按不良率的两倍取整这样波动更明显。要让风险客户在表格里自动跳出来条件格式比人工标记更可靠。选中数据区域 A2:F100新建规则“使用公式确定要设置格式的单元格”公式写AND($D2TODAY(), $F2未结清)其中 D 列是到期日F 列是还款状态。公式中的$D2锁定列不锁定行向下填充时每一行都判断自己的到期日和还款状态。满足条件的整行会被标成浅红色而且随着TODAY()变化自动更新这就是课程里说的“动态跟踪管理合同”。如果担心TODAY()导致报表打开就变样可以把基准日放在一个单元格里公式引用该单元格。动态交互图表的实现也不需要写 VBA。把明细数据转成表格插入数据透视表再基于透视表插入柱形图随后插入切片器指向“客户经理”或“授信品种”。切片器点击之后数据透视表和图表会同步联动阅读者可以选择只看某个客户经理的余额趋势。这里的关键是图表必须基于透视表创建不能直接选普通区域否则切片器无法联动。多个图表共享同一个透视表时切片器连接要手动勾选避免只控制其中一个图。最后一个小技巧用“获取数据→自表格/区域”把明细载入数据模型再基于数据模型创建透视表这样多个表之间可以用关系关联适合把合同台账和放款明细分开存放的金融场景。遇到贷款余额按日累计的报表可以先在 Power Query 里做分组汇总再加载回透视表而不是把透视表当成万能计算器。本文还有配套的精品资源点击获取 金融Excel实战：数据清洗、透视表与动态报表技巧
简介这是一套面向银行、证券等金融从业者及财务、销售、人力管理人员的Excel高效应用培训资料由资深实战讲师韩小良编写聚焦数据管理与分析能力提升适用于贷款统计、经营分析、预算执行追踪等日常管理场景。资料按六大模块组织基础数据清洗与规范、公式与函数实战、表格可视化与条件格式标识、多工作簿快速汇总、数据透视表应用以及动态图表表达整体结构完整既讲操作技巧也强调分析思维。包内含1个PDF格式电子文档大小约249KB便于阅读和检索。已有156人浏览学习。书中收录授信品种、信用等级、行业等维度的统计分析报表编制方法并配有贷款周报月报、合同动态跟踪、两轴组合图等典型案例可帮助读者快速定位关键信息、提炼数据价值使分析报告更具说服力。1. Excel 金融数据分析最先要改的不是函数在金融机构做数据分析Excel 仍然比 Python 更适合大批业务人员。韩小良这门课程 PDF 给人最直接的印象是开头两章不教 VBA不堆公式而是反复处理非法日期、文本型数字、合并单元格这些“脏数据”。实际维护过贷款台账、授信周报、同业存款报表的人都有体会SUMIFS 和数据透视表一用就错问题往往出在源表结构而不是函数记不熟。Excel 效率专家和普通用户的差距多半在数据清洗与表格规范而不是背多少条公式。课程六个部分反复出现的重点其实就是数据标准化、条件求和、透视表周期统计、组合图表这些高频场景适合财务、客户经理、风险分析和信贷运营岗位参考。2. 表格数据标准化非法日期、文本型数字与合并单元格的处理金融系统导出的明细通常来自核心业务系统、信贷管理系统或资金系统导出后的表格往往给的是“让人看得懂”而不是“让 Excel 算得动”。从外部系统复制出来的金额可能带着空格和全角逗号客户编号被 Excel 自动转成科学计数法日期字段在单元格里显示为“2024.06.30”。这些问题不在源系统解决留在报表阶段处理每做一次周报就重复踩一次坑。2.1 三类脏数据的特征与判断我拿到一份新表不会直接插入透视表而是先检查几个位置列标题有没有重复、空行空列多不多、日期区域是否真的是日期类型、金额列能不能正常求和。一个快速检查技巧是选中金额列后看状态栏如果只显示计数而不显示求和那这一列基本是文本型数字。脏数据类型典型表现快速识别方法文本型数字左上角有绿色小三角SUM 结果明显偏小选中金额列状态栏无求和非法日期显示为 2024.06.30、20240630改格式无效ISNUMBER(A2)返回 FALSE合并单元格透视表行标签出现大量空白F5 打开定位选择“空值”定位特殊字符金额中夹带空格、换行符、不可见字符TRIM(CLEAN(A2))A2返回 TRUE这个表看起来简单但几乎每个信贷分析项目都会用到。第一列是现象第二列是肉眼看到的样子第三列是判断方法。把第三列的公式放到辅助列能很快定位修正后仍然异常的数据。例如文本型数字列里用ISNUMBER检查并配合条件格式标红比肉眼找小三角更快金额列里的空格从源头上看可能是财务软件导出时的千分位补位如果转换后仍有差异就回到系统导出的原始文件核对不要在 Excel 里反复替换。2.2 批量转换文本型数字的两种做法最常见的稳妥方式是用选择性粘贴。先复制任意空白单元格选中需要转换的金额列右键“选择性粘贴”操作里选“加”Excel 会将文本型数字与 0 相加强制转成数值。这样做不会破坏原有数字也不会像“分列”那样要求列宽。但遇到多列数据逐列操作仍然很慢。我更常用下面这个 VBA 宏把选区里“看起来像数字但实际上是文本”的单元格批量转成数值适合处理从信贷系统导出的多个工作表Sub ConvertTextToNumber() Dim rng As Range, cell As Range On Error Resume Next Set rng Selection.SpecialCells(xlCellTypeConstants) On Error GoTo 0 If rng Is Nothing Then MsgBox 当前选区没有常量单元格 Exit Sub End If For Each cell In rng If IsNumeric(cell.Value) And TypeName(cell.Value) String Then cell.Value CDbl(cell.Value) End If Next cell End Sub这段代码先通过SpecialCells(xlCellTypeConstants)只选常量单元格避免把公式结果误转。IsNumeric(cell.Value)判断单元格内容是否具备数字特征TypeName(cell.Value) String进一步确保当前是字符串类型二者同时成立才执行CDbl。如果单元格本来已经是数字直接跳过避免重复转换带来的精度问题。选中区域后按 F8 逐步执行能更清楚看到每个判断分支实际批量使用直接运行宏即可。注意如果原始文本是“1,234.56”这类带千分位的写法IsNumeric会返回 False此类数据需要先删除千分位分隔符再转换。2.3 修复非法日期与特殊字符文本型日期是最容易导致透视表月份乱排的原因。对“2024.06.30”这种用点号分隔的日期我一般不会只改单元格格式而是用公式生成一个真正的日期值。格式固定为年月日时可以直接按位置取数DATE(LEFT(A2,4), MID(A2,6,2), MID(A2,9,2))LEFT(A2,4)提取年份“2024”MID(A2,6,2)提取月份“06”MID(A2,9,2)提取日期“30”。注意 MID 的起始位置从 1 开始第 6 位是月份第一位。如果原始数据是“30.06.2024”这种日月年顺序公式要换成DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2))。两种顺序不能混用否则会把日和月颠倒。也可以尝试DATEVALUE(SUBSTITUTE(A2,.,-))但这个函数受 Windows 区域日期格式影响在部分办公电脑上会返回#VALUE!所以按位提取的公式更可控。去除金额列里的特殊字符可以用TRIM和CLEAN组合TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160),)))CHAR(160)是不换行空格很多从网页端复制的数据会带上。CLEAN删除前 32 个 ASCII 控制字符TRIM清理多余空格最后得到干净文本。处理完后用“选择性粘贴为数值”覆盖原列避免公式残留影响下次导入。2.4 合并单元格的取消与填充合并单元格在审批表里很直观但在数据分析中会引发严重问题。取消合并后只有左上角第一行有值其它行是空格透视表会把它们当“空白”类别统计。处理方法是选中合并区域取消“合并后居中”保持区域选中状态按 F5 打开定位条件选择“空值”在编辑栏输入等于上一个单元格比如 E3 空值输入E2最后按CtrlEnter批量填充。这样就把层级关系转成了重复的明细字段。如果要在每个分行名称后面保留分隔感可以再配合“按颜色排序”做视觉分组但数据表本身不要再使用合并单元格。提示数据标准化不是一次性的应该固化到每个月的操作流程里。建议在原始数据工作簿之外单独建一个“清洗”工作表保留一步步操作痕迹后续审计时能说清数据来源和修改过程。3. 授信与财务公式SUMIFS、INDEXMATCH 和日期函数实战课程第二部分的设计思路是“从业务问题出发选函数”不是列一张函数公式大全清单。在金融报表里高频任务无非三类按多条件汇总余额从合同台账带出利率和到期日以及判断是否逾期。这三个问题分别对应SUMIFS、INDEXMATCH/XLOOKUP、日期函数。3.1 用 SUMIFS 按客户、担保方式、逾期标志汇总如果还在用筛选后 AutoSUM很容易漏算隐藏行。SUMIFS对多个条件取交集公式写完以后不需要反复筛选。SUMIFS(贷款余额列, 客户名称列, A公司, 担保方式列, 抵押, 逾期标志列, 否)第一个参数贷款余额列是要加总的数值列之后每两个参数构成一组条件分别是条件区域和条件值。条件值为文本时需要加英文双引号引用单元格时去掉双引号例如$H$2表示客户名称写在 H2。条件区域必须与求和区域同行数否则返回#VALUE!。如果有多个条件要取“或”关系SUMIFS做不到应改用SUMPRODUCT或分别求和后相加。SUMIFS对文本比较不区分大小写但对前导空格敏感所以第 2 章的清洗步骤会直接影响求和结果。3.2 用 INDEXMATCH 带回合同台账字段VLOOKUP 只能按区域第一列查找返回右侧列的值当需要按“合同编号”查找并把“利率”“客户名称”“到期日”一次性带回我一般用INDEXMATCH组合。INDEX(合同台账!$A$1:$Z$1000, MATCH($A2, 合同台账!$B$1:$B$1000, 0), 3)MATCH($A2, 合同台账!$B$1:$B$1000, 0)在 B 列中精确查找当前行的合同编号返回行号INDEX根据行号和第三参数“3”从 A 到 Z 的区域里取对应值这里第三列是利率。如果查找不到MATCH返回#N/A可在外层套IFERROR显示“未签约”或 0。注意查找区域$B$1:$B$1000不能出现重复合同编号否则返回第一个匹配项这在合同台账重复时会导致利率带错不能只依赖函数解决还要先做去重检查。如果使用 Office 365 或 Excel 2021XLOOKUP更简洁XLOOKUP($A2, 合同台账!$B$2:$B$1000, 合同台账!$C$2:$C$1000, 未找到)参数分别是查找值、查找区域、返回区域、找不到时的值。XLOOKUP不需要返回列号也允许查找区域在返回区域右侧阅读和维护都更容易。3.3 日期函数贷款期限、到期日与逾期判断金融分析中日期函数的关键是计算两个日期之间的实际间隔。DATEDIF是 Excel 的隐藏函数输入时不出现在函数列表但可直接手写DATEDIF(放款日, 到期日, M)第三参数M返回整月数改成D返回天数Y返回整年数。需要注意DATEDIF在早期版本中的边界行为并不一致例如到期日是月末最后一天时计算结果可能比预期小一个月。如果遇到争议改用YEARFRAC(放款日, 到期日, 1)*12得到带小数的月份数再按业务口径四舍五入。逾期判断是财务管理中非常常见的场景。一个简单的动态公式如下IF(AND($D2TODAY(), $E2结清), 逾期, 正常)TODAY()返回当天日期公式每次打开文件都会重算因此判断结果会随时间变化适合做贷后监控。AND表示两个条件同时成立才会判定逾期。如果需要在月底固定考核最好把基准日放在单元格里例如$G$1公式写成$D2$G$1避免打开文件时日期漂移影响报表数字。3.4 用文本函数从复合字段提取产品类型信贷台账里经常出现“个人住房贷款-北京分行-20240630”这样的复合文本。用LEFT和FIND可以把第一段产品名拆出来LEFT(A2, FIND(-, A2)-1)FIND(-, A2)返回第一个连字符的位置减 1 后得到产品名长度LEFT截取左侧内容。如果要取第二段可以用MID加SUBSTITUTE组合或直接使用“数据到分列”按分隔符拆分。文本函数适合一次性构建辅助列如果源数据抽取逻辑不统一拆分结果要抽样验证避免把备注字段里的“-”也当成分隔符。业务场景推荐函数关键点多条件汇总贷款余额SUMIFS条件区域必须与求和区域同尺寸合同编号带出利率INDEXMATCH查找列不能有重复值普惠贷款利率版本兼容XLOOKUP需要 Office 365 / 2021计算贷款期限整月数DATEDIF第三参数为 M动态判定逾期IFANDTODAY基准日建议放单元格提取产品类型LEFTFIND注意分隔符统一4. 数据透视表与多工作簿汇总贷款统计报表怎么做才不返工课程第五部分把数据透视表定位成“贷款统计分析报表”的核心工具。要让透视表不返工功夫一半在透视表外把明细数据整理成一行一笔贷款、每列一个字段而不是一张带小计和多级标题的人工报表。原始数据中不能有合并单元格金额必须是数值日期必须是真正的日期否则透视表里会出现“空白”行和计数结果。4.1 把明细数据转换为表格区域选中明细区域任意单元格按CtrlT创建表表名称修改为“贷款明细”。这样做有三个直接好处新增数据行后透视表数据源自动扩展到表区域公式区域会自动填充筛选和排序不会破坏列对应关系。表名称在“表设计”选项卡左侧修改。创建透视表时数据源直接选“贷款明细”表而不是$A$1:$F$1000这类固定引用后续新增支行数据就不用再改范围。4.2 多维统计授信品种、信用等级、行业和企业性质课程里提到的授信品种统计、信用等级统计、行业和企业性质统计本质上都是同一个透视表操作只是行列字段不同。以“保证人信用等级”为例把“信用等级”拖到行区域“贷款余额”拖到值区域透视表默认对数值字段执行求和如果值区域显示“计数”说明该字段不是数值型或者源数据有文本型数字没有转换。此时回到第 2 章的清洗流程不需要在透视表里设置。下面是常用维度组合分析诉求行字段列字段值字段各授信品种余额授信品种无贷款余额借款人与保证人交叉借款人信用等级保证人信用等级贷款余额行业与企业性质交叉行业企业性质贷款余额分支行按周统计放款日期组合为7天机构贷款余额日期型字段放到行区域后右键“组合”可以选择“季度”“月”“日”。如果要做贷款周报选择“日”并将“天数”设置为 7而不是用“自然周”。这样遇到跨月周时不会被拆到两个月里报表口径更接近实际投放节奏。4.3 用切片器与日程表实现交互式筛选数据透视表分析能力很强但面对非技术人员交互式筛选最直接。选中透视表任意单元格在“数据透视表分析”选项卡中插入切片器勾选“授信品种”“区域”等字段再插入日程表并指向“放款日期”。用户可以通过切片器只看“抵押贷款”或只看某个分行的数据。多个切片器之间的关系是“与”即同时生效设计报表时不要放太多切片器一般两个就够。4.4 用 Power Query 快速合并同结构工作簿课程第四部分专门讲多个工作簿和工作表的汇总。金融场景里最常见的是各地支行通过邮件发来结构相同的 Excel总部需要合并成一张总表。最早的做法是 VBA 循环打开所有工作簿复制粘贴现在更推荐 Power Query。步骤如下新建一个“汇总.xlsx”把各支行的文件放进C:\Reports\202406文件夹在“数据”选项卡中选择“获取数据”从“来自文件”进入“从文件夹”点击“合并和转换数据”在预览窗口选择目标工作表Power Query 自动为每个文件生成一个查询并追加到一个表。Power Query 里对应的 M 查询代码大致如下let 源 Folder.Files(C:\Reports\202406), 筛选Excel Table.SelectRows(源, each [Extension] .xlsx), 保留关键列 Table.SelectColumns(筛选Excel, {Name, Content}), 添加数据 Table.AddColumn(保留关键列, 数据, each Excel.Workbook([Content], true)), 展开Data Table.ExpandTableColumn(添加数据, 数据, {Data}, {Data}), 展开行 Table.ExpandTableColumn(展开Data, Data, Table.ColumnNames(展开Data{0}[Data])) in 展开行Folder.Files(C:\Reports\202406)读取文件夹下所有文件Excel.Workbook([Content], true)第二参数true表示将每个工作表的第一行作为标题。Table.ColumnNames(展开Data{0}[Data])以第一个文件的列名作为基准如果其它文件多出空列或缺少列展开时会报错或产生空值。实际业务中我建议先删除最后两行展开步骤进入 Power Query 编辑器后用“选择列”和“追加”向导生成而不是直接粘贴 M 代码因为不同 Excel 版本生成的结构代码略有差异。上面代码用于理解原理动手操作时用界面点击更稳妥。4.5 批量刷新所有透视表当清洗数据和透视表都做好后每天更新数据只需要替换源数据并刷新。手动点击刷新容易漏表VBA 一键刷新所有工作表的透视表Sub RefreshAllPivots() Dim ws As Worksheet Dim pt As PivotTable For Each ws In ThisWorkbook.Worksheets For Each pt In ws.PivotTables pt.PivotCache.Refresh Next pt Next ws End SubPivotCache.Refresh会更新该缓存关联的所有透视表。如果同一个透视表数据源被多个透视表共享刷新缓存一次即可不需要对每个透视表执行RefreshTable后者重量更轻但可能漏掉缓存更新。将宏绑定到“数据更新”按钮或Workbook_Open事件时要注意如果源数据在关闭的工作簿中刷新会弹出连接窗口生产报表建议先打开源文件或提前做好数据管道。提示透视表数字格式不一定继承源数据建议在透视表值字段上右键“数字格式”统一为#,##0.00避免正式报告上出现 1234567.891 这种预设格式。5. 组合图、条件格式与动态交互把风险信号放到台面上金融类报告里图表的作用不是装饰而是让阅读者第一眼就看到异常。“贷款余额持续上升”用柱形图“不良率抬头”用折线图两个指标量纲不同放在同一张图里必须用次坐标轴。Excel 的做法是选中包含月份、贷款余额、不良率三列数据插入“组合图”在图表设计中将“贷款余额”设置为簇状柱形图“不良率”设置为折线图并勾选“次坐标轴”。次坐标轴范围默认从 0 开始如果不设置会挤压折线我一般把次坐标轴最小值设为 0最大值按不良率的两倍取整这样波动更明显。要让风险客户在表格里自动跳出来条件格式比人工标记更可靠。选中数据区域 A2:F100新建规则“使用公式确定要设置格式的单元格”公式写AND($D2TODAY(), $F2未结清)其中 D 列是到期日F 列是还款状态。公式中的$D2锁定列不锁定行向下填充时每一行都判断自己的到期日和还款状态。满足条件的整行会被标成浅红色而且随着TODAY()变化自动更新这就是课程里说的“动态跟踪管理合同”。如果担心TODAY()导致报表打开就变样可以把基准日放在一个单元格里公式引用该单元格。动态交互图表的实现也不需要写 VBA。把明细数据转成表格插入数据透视表再基于透视表插入柱形图随后插入切片器指向“客户经理”或“授信品种”。切片器点击之后数据透视表和图表会同步联动阅读者可以选择只看某个客户经理的余额趋势。这里的关键是图表必须基于透视表创建不能直接选普通区域否则切片器无法联动。多个图表共享同一个透视表时切片器连接要手动勾选避免只控制其中一个图。最后一个小技巧用“获取数据→自表格/区域”把明细载入数据模型再基于数据模型创建透视表这样多个表之间可以用关系关联适合把合同台账和放款明细分开存放的金融场景。遇到贷款余额按日累计的报表可以先在 Power Query 里做分组汇总再加载回透视表而不是把透视表当成万能计算器。本文还有配套的精品资源点击获取