Excel日报自动化

一. 需求分析

• 通报企业每日运营的一些关键指标（包括销售量，销售额，购买人数，订单数，毛利额，件单价，连带率，环比等）的完成情况，并将日报模板化，自动化。

二. 做日报的流程

• 从公司数据库中提取数据到Excel表中；
• 在Excel中对数据进行相应的处理并绘制图表；
• 把绘制好的图表复制到Word文档中，编辑通报正文，最后以邮件正文的形式发出。
• 一般通报的指标、内容都基本固定，所以我们可以把日报模板化，自动化。

三. 日报自动化原理

• 通过VBA语句，从数据库自动提取前一日相应的关键指标数据，并自动追加放置在一张名为 “数据源” 表中的相应位置，实现一键自动提取数据（注意：本日记中，因为这一步骤篇幅较长，我用预设全年日期，手动添加数据的方式模拟了这一过程）。
• 在数据转化区中，根据制定的日期条件，动态引用 “数据源” 表中相应的数据，并自动绘制图表、组合通报文字。
• 在日报正文区中，引用相应的组合好的通报文字与绘制的图表。
• 通过控件选择需要通报的日期，并自动生成相对应日期的日报正文。

四.日报自动化的实现过程

首先要规划好日报所需的数据，建立数据模板。在”日报.xlsx”文件中，准备三张表。

• ”数据源“表：用于存放每日通报所需的关键指标数据
• ”数据转化“表：用于动态引用 ”数据源” 表中的数据，并进行相应的数据转化，最后自动绘制图表，组合通报文字
• “日报正文”表：引用 “数据转化” 表中相应组合好的通报文字与绘制好的图表，并呈现日报。

以下是具体实现过程

---

步骤1.（建立 “数据源”表）

1. 通过"开始"菜单栏- "编辑窗口"- "填充"- "序列"功能预设全年日期；
2. 根据你自己定的数据模板提取相应的数据，表中毛利额以及它之前的所有列均为提取字段，月累计及年累计为计算字段，其余的为辅助字段；
3. 计算字段的计算过程如下： 年累计用函数 =SUM($C$2:C2) 计算。月累计需借助辅助字段K，用函数 =SUM(INDIRECT(K2):C2) 计算每月1号到当前日期的总和，其中 =INDIRECT(K2) 是对每月1号单元格的引用；

4. 辅助字段的计算过程如下： 月份用函数 =MONTH(A2) 计算。 每月1号用函数 =IF(I2=I1,J1,ROW(I2)) 计算。 每月1号销售额对应的单元格用函数 ="C"&J2 计算

   
   

步骤2.（建立 “数据转化”表）

1. 创建日期控件:

• 选择 ‘开发工具’ 选项卡 - ‘控件’区域中 - 选择 ‘插入’ - 选择 ’组合框(窗体控件)‘；
• 拖动鼠标，创建控件，接着鼠标右键选择 ’设置控件格式‘；
• 在 ’数据源区域‘ 中选择 ’数据源‘ 表中的日期列，在’单元格链接‘选择 C2单元格，‘下拉显示项数’默认就可以，点击确定，完成创建。

2. 创建数据动态引用：

• 设置标题；
• 采用OFFSET函数动态引用数据。选中A6:H12单元格，填入函数 =OFFSET(数据源表!A1, C2-6, 0, 7, 8)，按住shift+ctrl+enter键完成数据引用；
• 用函数 =F6/C6 计算毛利率（毛利额/销售额）。

3. 创建报告数据表：

• 设计报告表格式；
• 用INDEX和MATCH函数组合的方式引用当日和昨日各指标的值。当日指标值采用函数 =INDEX($B$12:$I$12, 1, MATCH($A17, $B$5:$I$5, 0))函数计算，昨日指标值采用函数 =INDEX($B$11:$I$11, 1, MATCH($A17, $B$5:$I$5, 0))函数计算；
• 创建表，存储月份和销售额目标； 4.月累计销售额用函数 =INDEX($B$12:$I$12, 1, MATCH($A25, $B$5:$I$5, 0)) 计算，月完成目标采用函数 =TEXT(B25/VLOOKUP(MONTH(A12)&"月", $I$17:$J$28, 2, 0), "0.00%") 计算； 5.计算环比（=当日/昨日-1）； 6.计算连带率（销售量/购买人数），计算件单价（销售额/销售量）

4. 创建报告标题及正文：

• 拟定报告标题及正文内容格式；
• 直接引用报告数据表中数据；
• 用 =CONCATENATE函数拼接数据。

5. 生成图表：

• 从 ”数据转化“ 表中引用制作图表所需的数据，制作图表。（注：这里的图表比较简单，我就不多介绍了）。

步骤3.（建立 “通报正文”表）

建立好日报模板，日期控件从 "数据转化" 表中直接复制过来（这里注意检查控件的数据源引用区域是否正确），数据也是直接引用，图表直接复制粘贴即可。日报正文如下，报告很简陋，有时间再美化了。

五. 参考资料

• 《谁说菜鸟不会数据分析》
• 小蚊子数据分析-Excel报告自动化

六. 总结

一直期待能将数据分析带到传统中小制造业，帮助他们加快转型升级的步伐。我认为传统制造业转型升级，得数据先行。目前最要的任务还是脚踏实地先把技能get到先吧，然后再谈梦想！！！