利用自动化报表与可视化分析工具来提升财务BP的工作效率, 其背景是每月都需要加班到凌晨才能完成报表制作所面临的痛苦处境。
每到月末的时候, 财务人员小李就会产生焦虑感, 因为他需要从ERP系统中导出十几张数据表格, 把这些数据手动粘贴进Excel模板里面, 接着调整格式并且更新图表, 最后再复制到PPT文档中用于向管理层进行汇报, 这样的整套操作流程至少需要耗费两个工作日的时间。
有一次, 小李在进行复制粘贴的操作时候, 他不小心把一个具体部门的收入数据给贴在错误的行数上了, 因为这事儿, 导致那整一份的报告都被退回来要求重新做。在凌晨一点钟的那个办公室里, 他心里头暗暗地发了个誓, 说一定要把这个工作流程给实现自动化处理这个目标。
要是你这边曾经也有经历过类似这样的痛苦体验的话, 那么这个文章的内容就是专门为你而写的。关于痛点的分析是, 手工制作报表慢的原因到底在哪里?
在动手书写代码以前, 我们必须先对传统报表流程中的瓶颈进行拆解与说明: 在数据获取环节, 采用的方法为手动导出若干张 ERP 系统生成的报表, 这一过程占总耗时的百分之二十, 其核心痛点在于各种数据的格式并不统一, 需要进行反复的整理工作；在数据加工环节, 使用的方法是由 Excel 透视表结合各类公式构成的, 这部分耗时占据总比例的百分之三十五, 其中的核心难题是计算公式过于复杂, 极易出现错误, 且后续修改起来极为困难；在可视化呈现环节, 主要依靠人工方式来绘制图表, 这一步骤耗费了整整百分之二十五的时间, 其主要问题是样式的调整需要消耗大量精力, 而且目前缺乏可供用户交互的功能；在分发汇报环节, 操作方式是借助电子邮件并附带手动发送的动作, 此阶段占比同样为百分之二十, 存在的严重问题便是很容易发生漏发的状况, 导致文件版本陷入混乱不堪的泥潭。
上述内容可以看出一个清晰的事实, 即真正用于执行数据分析任务的时间不足百分之二十, 而高达百分之八十的时间都白白花费在了搬运和移动各类数据上面。这里就是自动化工具能够发挥其作用的地方, 也就是将那些重复性的劳动任务交由机器去完成, 然后把宝贵的时间资源返还给人员用于分析和思考。
关于方案设计这一部分, 我们制定了报表自动化全流程的架构体系。我们的目标是非常明确的, 那就是实现从原始数据一直到最终报告的一键生成操作。整个系统架构在整体上进行划分时, 一共被分成了四层:
数据获取层
数据处理层
可视化层
分发层
把ERP系统导出来的文件数据进行清洗, 然后用图表来呈现结果, 最后通过邮件或者企业微信把数据发送出去, 同时利用数据库连接来计算指标, 绘制出瀑布图和热力图, 并且设置定时调度任务来做这些工作。在技术选型和数据处理方面, 主要使用Excel来处理财务数据, 这就像一把万能的瑞士军刀。
输出格式为支持格式化的Excel报表。可视化方面提供交互式图表进行展示, 这种方式比静态图片更适合用于汇报演示。邮件分发功能依靠内置邮件库配合定时任务来实现。选型的总体原则是优先使用那些成熟且稳定的库, 而不是去追求最新最酷的工具。在财务的场景之下, 可靠性的意义胜过一切其他因素。
所谓的核心代码就是按照四个步骤来实现报表的自动化操作, 第一步是进行数据的获取与清洗工作, 这一步需要从多个不同的数据源里面读取原始的数据内容, 并且把这些原始数据合并在一起, 最终形成一份格式统一的分析底表。
as (: str, : str) -> pd.: """读取多张 ERP 导出表并合并为统一格式""" # 读取收入、成本、费用三张明细表 = pd.(f"{}/收入明细_{}.xlsx") cost = pd.(f"{}/成本明细_{}.xlsx") = pd.(f"{}/费用明细_{}.xlsx") # 统一列名，避免各表命名不一致 .(={"dept": "部门", "": "收入金额"}, =True) cost.(={"dept": "部门", "": "成本金额"}, =True) .(={"dept": "部门", "": "费用金额"}, =True) # 按部门汇总合并，使用 保证金额精度 = .merge(cost, on="部门", how="outer") = .merge(, on="部门", how="outer") .(0, =True) # 缺失值补零 一句话总结：多源数据合并的关键是统一列名和处理缺失值，这是后续所有计算的基础。
进入第二个实施阶段, 你需要去生成一份月度的经营情况报表, 并且要把最终的结果以Excel这种文件格式导出来。
这段代码片段似乎是从一个程序中截取出来的, 它的目的是生成一份月度经营分析的Excel报表。
在这个操作中, 首先创建了一个工作簿对象, 然后在工作簿中设置名为"月度经营总览"的工作表。
接下来, 针对表格的标题部分进行样式设置, 比如将字体加粗、调整颜色以及字号大小等, 同时也可能设定了单元格背景的图案为纯色填充。
所谓的部门, 还有那个收入的具体金额数, 再加上成本那边花的钱数, 以及费用方面支出的数目, 再就是毛利这个概念, 最后则是毛利率这一项。
对于在包含空白和数字一的一个元组列表里面的每一个称作列的变量, 进行下面的一系列操作, 首先把工作表里面行标是一并且列标等于当前被称为列的那个变量的单元格的值设置成空字符串, 然后对该单元格的字体格式进行设定, 也对它的填充样式进行设定, 接着调用一个名为判断空的函数来检查该单元格的值是否相当于空字符串, 接下来使用一个注释来说明接下来的目的是每读取一行数据都要写入到表格中以及计算毛利, 随后开始遍历一个叫做df的对象的方法所返回的由索引和每一行数据构成的元组中的每一个部分, 其中分别被分配给称作索引和行的变量, 最后把当前这一行数据的整体引用赋值给一个叫作的值的变量。
"收入金额"
- row
"成本金额"
= / row
"收入金额"
if row
"收入金额"
!= 0 else 0ws.(
row, row
"收入金额"
, row
"成本金额"
,row
"费用金额"
, ,
) # 毛利率列设置为百分比格式 for row in ws.(=2, =6, =6):for cell in row:cell. = '0.0%' wb.save() 一句话总结： 可以精确控制 Excel 的样式和格式，生成可直接用于汇报的专业报表。
第三步: 使用优采云来搭建管理驾驶舱, 这种被称为管理驾驶舱的东西, 它是一种用来把关键的经营指标都集中在一起展示的那种可视化看板, 这样做的目的是为了让管理层能够一眼就把全局的情况看清楚。
我们利用该平台创建了三个核心的图表, 这三个核心图表分别是用于展示收入趋势的折线图、用于展示部门利润变化的瀑布图, 以及用于展示费用结构的热力图。
3.创建用于展示接近12个月内收入变化情况的折线图, 代码编写的方式是通过进行一个特定的设定, 来定义函数作用的范围, 接着实例化绘图的对象为空的状态, 然后通过调用的方法传入相关的参数, 其中横坐标数据取自源数据的指定项, 纵坐标对应的数值也从指定的地方获取。
"收入金额"
设置模式为线条加标记, 并指定线条颜色, 同时设置线条宽度。接着给图表添加标题, 注明横轴代表月份纵轴表示金额单位为万元选用简洁的白色背景非常适合汇报。然后保存图表对象如图3.2所示的部门利润瀑布图能够清晰展示从收入到净利润各项变化的过程该图是财务汇报中最实用的图表之一。
函数定义为接受一个字典类型的参数, 用于创建部门利润的瀑布图, 首先初始化图表对象并使用go模块进行配置, 其中设置垂直方向的向量表示各项构成情况, 具体包含收入、成本、费用、税金以及净利润这几个部分。
"", "", "", "", "total"
,x=
"收入", "成本", "费用", "税金", "净利润"
,y=
""
, -,-
""
, -,
""
,={"line": {"color": "rgb(63, 63, 63)"}} )) fig.(title="利润瀑布图：从收入到净利润", ="") fig 3.3 费用结构热力图 . as pxdef ap(: pd.): """创建费用结构热力图，展示各部门费用分布""" # 构建透视表：行=部门，列=费用科目，值=金额 pivot = .(index="部门", ="费用科目", ="金额", ="sum" ) fig = px.(pivot,le="Blues",title="各部门费用结构热力图" ) fig 一句话总结： 的交互式图表让管理层可以自己缩放、筛选数据，比静态截图好用十倍。
执行第四步操作, 也就是自动化分发环节, 其核心在于定时将报告进行推送。既然报表已经制作完成, 那么需要保证相关的人员能够在准确无误的时间点上接收到这些内容, 于是我们就采用了相关技术来实现邮件的自动推送这一功能。
使用 email.mime.email.mime.base.email 模块中的相关功能, 向指定的接收人列表或者指定的个人邮箱地址发送报表邮件到对应的服务器地址。
在 SMTP自动发送的过程中, 首先创建了一个空的邮件对象。紧接着对这个邮件对象的内容进行了初始化的赋值操作, 然后继续对消息内容做后续的处理。
""
首先打开文件以便读取二进制数据, 接着把文件的完整内容打包成一个特定的邮件附件部分, 然后再把这个附件部分添加到待发送邮件的消息体当中, 随后建立到一个指定端口号的邮件服务器的安全连接并启动传输层安全性协议加密功能, 利用用户名和密码登录到该系统, 最后将组装好的整个邮件发送出去, 在此需要特别注意在生产环境运行场景下密码应当通过环境变量或者是专门的密钥管理服务来进行获取与存储, 绝对不可以直接把真实的密码写死在程序代码里面。
一句话的总结是, 让自动分发机制替报表去找合适的人, 这改变了以往人去主动找报表的做法, 这样做能够彻底解决漏发以及版本混乱这类问题。
关于踩坑的记录部分有两个真实的问题需要关注, 其中第一个坑的情况是这样的: 在ERP系统导出的文件中, 日期的格式被某种处理过程读取成了数字, 具体表现为数据变成了像44927这样的整数内容。
造成这一现象的缘由, 在于Excel这个软件内部是采用数字序列号的形式来保存日期数据的, 并且系统在默认设置下并不会自动将其转换为标准的日期格式。为了解决该问题, 我们需要在读取数据文件的时候明确地指定哪些列为日期列并执行转换的操作, 具体的代码表达式为: df = pd.(, =。
"日期列"
第二号坑, 就是图表在幻灯片里面展现出来的时候变成了一团空白。具体表现的情况是, 当你把这个统计图弄成图片的形式, 然后把它贴进演示文稿去插入的时候, 会发现其中有一些图画内容是缺东西的, 完全看不出来画了啥。
导致出现这个状况的原因, 是因为系统在选择默认的方式来处理导出的这一套操作机制时, 是需要依赖于某些特定软件库的支持的, 而这些库文件目前还没有被安装到你的设备里头去, 所以没办法正常渲染出图像。解决方案如下。首先, 安装静态导出引擎。
然后使用代码chart.png并设置scale参数为2, 以此确保图片具有高清效果。接着进行效果图对比, 分别对比自动化方式与手工方式的相关指标。在报表生成耗时方面, 手工方式需要两个工作日, 而自动化方式仅需十五分钟, 效率提升程度为百分之九十三。
在数据错误率方面, 手工方式约为百分之三到百分之五之间, 而在自动化方式下该数值小于一分之十个百分点, 实现了显著降低的效果。在可视化制作耗时方面, 手工方式需要半天时间, 自动化方式则可自动生成且效率提升百分之九十。
在报告分发方面, 手工方式为手动逐一发送, 而自动化方式为自动批量推送, 从而实现了完全自动化。在可复用性方面手工方式是每次都需要重新修改参数, 而自动化方式则是一次开发并且能够长期受益。总结来看本文的核心收获表明报表自动化并非遥不可及的事情使用加粗强调的三个库便能够覆盖百分之八十的报表自动化需求。
现在大家都觉得交互式可视化是大势所趋, 因为那种可以交互操作的图表, 比起传统的那种静态图片来, 会更适合给管理层做汇报的时候用。只有把全流程都做到闭环管理, 那才算是真正的提高效率, 必须要从获取数据一直到自动分发这两个环节都打通了, 形成了一条完整的全链路, 这样才有可能释放出最大的价值。
所以简单来说就是, 要把百分之八十的用来搬运报表的那些时间全都交出去还给系统或者别人, 然后把时间留给自己, 去真正做一些有价值的业务分析。
下一期的内容是预告, 我们会详细地讲解如何搭建财务分析模型, 这个模型可以从客户盈利分析开始讲起, 一直讲到投资回报测算, 这样能够让商业计划书中的分析能力再提高一个层次。
延伸阅读部分是这样的: 如果你觉得对 财务自动化的基础搭建还不够熟悉的话, 我们推荐你先去阅读本系列的姊妹篇, 也就是那个叫「财务自动化」的系列, 它会从环境搭建到数据处理基础, 一步一步地带着你入门。这是一个互动性的问题: 每个月有多少时间花费在制作报表这件事上?
最期待实现哪一个环节的自动化操作? 希望大家能够在评论区域来分享自己个人的实际经历。
