专栏名称: 零一

零一自己的公微号，不是机器人哦，是真人哦！O(∩_∩)O~

自动化的Excel+PPT报告

零一 · 公众号 · · 2017-07-07 21:33

正文

大家好，我叫刘攀，今年19岁，是老四老师的不肖学生。老师要求我写文章分享，真的是硬着头皮写的，第一次就这样贡献出来了，我想我明天睡醒还是个小鲜肉的。

分享点什么好呢？我想了很久都不知道怎么办，问了老师，老师就说了一句让我分享有用的东西。可是老师教的所有的东西都有用啊，我觉得这肯定是老师的套路。我说，眼睛进了沙子，听不见。然后，我就被送进了医院，现在躺在床上一本正经的胡说八道。

我今天要分享的是能解放运营双手的东西，运营的双手应该有更重要的东西要把握。其实我做的是订单+用户的日报，设计思路是用VAB代码将数据从数据库中提取出来，然后通过函数来处理数据，整个过程也可以用Power Query来实现。可以用这个思路拓展到周报和月报。下面是具体的步骤。

这篇文章用到的工具有，数据库（本篇例子用的Access2016），Excel，VBA

解决问题：通过报表自动化解放你的双手！

一、上传数据到数据库

先把数据整理汇总，可以放在MySQL、Access或者工作簿中。

1，先要有订单数据和用户数据！

2，将数据上传到数据库！

二、制作Excel日报表模板

把数据清洗成报表所需的形式。

(1，先打开一个新的Excel，我们需要3个页!确定我们要分析的内容（我分析的是订购用户数和订单数）添上日期！然后加上之前3天的数据!

2，累计用户数=之前累计的+新增的用户！累计订购用户，累计订单数和累计业务收入都是同理！

3，到转化区域页，插入→插入控件→把控件插入到表格上!

4，鼠标右键点击控件→设置控件格式

5，到数据源→数据源区域→选中2016/9/1按Ctrl+Shift+↓（下）

6，单元格链接→G5（控件前面一个单元格就行）→确定

7，点击控件→选择一个日期

8，在表的左边的第一行标上自己想要的数据标题，然后选中标题下的4行5列→公式→插入函数

9，OFFSET→确定

10，Reference→数据源！B1（点击一下日期标题）

11，Rows：G6(控件制定链接数字)→Cols填0→Height填4→Width填5（因为我们选中的4行5列）注：所有步骤操作完成后不能直接按确定！一定要按：Ctrl+Shift+Enter（这样按才会出现数据）

12，修改日期格！鼠标右击→设置单元格格式

13，日期→2012/3/14→确定

14，选中日期和新增增用户数→插入→推荐图标.15，所有图标→柱形图→确定

16，注：这里一定要选选中日期后在按Ctrl选中订购用户数！（不按这个步骤来可能会出错）

选中数据→插入→推荐的图表（和上一步一样插入柱形图就行了）

17，把4个数据都做一个自己想要的图表！（我们做的这个图是要给老板看的）18，在数据转化区的右边做一个和数据相对应的表格

19，我们需要把前面表格实时的数据转移到后面的表格！（例如：当日新增用户数，=B5）

20，以同样的道理把昨日的数据也给他转移到后面！

21，用今天的新增用户数减去昨天的新增用户数的差除以昨天的新增用户数！算出新增用户数的环比！（同理算出其他数据的环比）

22，和之前第13步一样，右击鼠标→设置单元格格式→百分比→小数位数→2→确定

23，累计用户：=数据源当天的累计用户数！累计订购用户数，累计订单数和累计业务收入也是同理!

24，这里分为两个部分！红色的为纯文本的！另一部分是需要转化的！（每一段都是要占用一个单元格的！

24，转化区：115，和前面第19步一样直接用“=“转化过来就行了!

=IF(N3>0,"上升",IF(N3<0,"下降","持平"))

IF是判断，N3是新增用户的环比！（这个函数的意思是：如果环比大于0那么就显示上升，如果环比小于0就显示下降，如果环比等于0就显示持平）

25，因为我们前面已经给他打上了上升和下降的标签！后面只需要一个没有正负号的百分比！（函数的意思;把新增用户环比转变为保留一位小数，不带符号的文本！）

=TEXT(ABS(N3),"0.0%")

26，因为我们这里累计用户数过万了，所以为了方便阅读我们把累计用户数除以1万，然后在后面一个单元格价格单位万！（函数的意思：把累计用户数除以1万保留小数点后一位数）

=TEXT(O3/10000,"0.0")

27，以相同的道理把当日订购用户，当日新增订单数和当日业务收入改成以万为单位就行了（数据量小可以不用该，直接“=“过来就行了）其他的和上面一样就行了！

28，把上面转化区的文本合并起来！（CONCATENATE函数的意思将多个文本字符串合并成一个！例如=CONCATENATE（A1，B1））

29，在日报告区插入个控件!要是不记得了看前面的第3步！

30，控件插入之前都有写过炒作完全一样！唯一不同的就是两个控件不在同一个表格（注：后面这个控件的单元格链接一定要选择到上一个控件的单元格链接位置！这样才能实现控件加图的联动!）

31，把之前在报告区所写的“='过来！然后把报告区相应的图复制过来！

32，和上一步一样把4个数据的报告和图表转换过来!

三、用VBA提取数据库数据

用VAB实现日报所需数据的提取

1，开发工具→Visual Basic（打开VBA)

2，鼠标右击日报→插入→模块

3，写代码（数据库的原文件一定要和日报放在一起代码才能运行）

Sub 每日数据提取()
'声明变量类型
Dim AdoConn As New ADODB.Connection '定义一个变量AdoConn，用来链接数据库，以及关闭数据库
Dim D1 As Date
Dim D2 As Date
Dim N As Integer
Dim MyData As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String

'InputBox函数打开输入对话框，输入日期，并赋值给D1
D1 = InputBox("请输入需要提取的日期，比如：2016-9-4", "提取日期")
D2 = D1 + 1
'定位第3列，第一个空单元格的行数，并赋值给N
N = ActiveSheet.Range("C1").End(xlDown).Row + 1

MyData = ThisWorkbook.Path & "\ribao.accdb" '数据库文件和日报在一个路径

'建立数据裤链接,并打开指定路径的数据库
With AdoConn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open MyData

End With
'根据我们的实际要求，编写拉取数据SQL语句

strSQL1 = "SELECT count(用户ID) FROM 用户明细 WHERE 注册日期< # " & D2 & " # AND 注册日期 >= # " & D1 & " #"
strSQL2 = "SELECT count(用户ID) FROM (SELECT DISTINCT 用户ID FROM 订单明细 WHERE 订购日期< # " & D2 & " # AND 订购日期 >= # " & D1 & " #)"
strSQL3 = "SELECT count(订单编号) FROM 订单明细 WHERE 订购日期< # " & D2 & " # AND 订购日期 >= # " & D1 & " #"
strSQL4 = "SELECT sum(订购金额) FROM 订单明细 WHERE 订购日期< # " & D2 & " # AND 订购日期 >= # " & D1 & " #"

'从数据库中拉取数据，并将数据写入到Excel工作表中
ActiveSheet.Cells(N, 3).CopyFromRecordset AdoConn.Execute(strSQL1)
ActiveSheet.Cells(N, 4).CopyFromRecordset AdoConn.Execute(strSQL2)
ActiveSheet.Cells(N, 5).CopyFromRecordset AdoConn.Execute(strSQL3)
ActiveSheet.Cells(N, 6).CopyFromRecordset AdoConn.Execute(strSQL4)
End Sub

4，因为代码里面设计到一个包，所以我们需要导入一个包！

点开工具→引用→找到Microsoft ActiveX Data Objects 2.8 Libray（打上勾）→确定5，到数据源：开发工具→插入→选择第一个控件插到表格旁边！

6，按钮1上右击鼠标→制定宏

7，每日数据提取→确定

8，点击按钮运行！→2016-9-4→确定（提取数据库数据）

四、保存文件

这一步一定要看，不然后悔

1，点击Excel左上角的文件

2，另存为→这台电脑→桌面→保存类型→Excel启用宏的工作簿→确定（桌面上就有了你的日报模板）

3，有的电脑是禁用宏的！所以我们要设置一下！

点击Excel左上角的文件→选择→信任中心→信任中心设置→宏设置→禁用所有宏，并发出通知！（这里不建议启用所有宏）→确定

4，每次用日报他就会发出警告！点击启用类容就行！

五、将报表生成PPT文件

将表格的数据导入到PPT，并生成PPT

1、先设计好一个PPT的样式

每一页PPT的元素都有名字，通过调用PPT的页码+元素的方式来修改PPT上的内容。在PPT中的开始选项卡，找到排列-选择窗格，就可以看到右边有选择的侧边栏，对应的元素名字就在右侧了。

2、用VBA将Excel中的内容复制到PPT，代码可以写在PPT或Excel中，下例给出部分代码示例，是在Excel中写过程调用PPT。

Sub PPT报告数据更新()

Dim objPPT As Object '定义ppt对象，用于新建PPt文件的命令赋值
Dim objPrs As Object '定义PPT对象，用于打开ppt文件的命令赋值
Dim objChart As Object '定义PPT对象型变量，用于PPT图形的赋值
Application.ScreenUpdating = False'禁止Excel程序的刷新，使得所有的程序都在后台完成
'新建PPT文件对象
Set objPPT = CreateObject("Powerpoint.application")
Set objPrs = objPPT.presentations.Open(ThisWorkbook.Path & "\月度经营分析报告.pptx", , , msoFalse)
'更新第一张ppt的标题
objPrs.Slides(1).Shapes("标题").TextFrame.TextRange.Text = Worksheets("数据转化区").Range("P2").Value
'获取第二张ppt上的名为图表1的图形的数据表
Set objChart = objPrs.Slides(2).Shapes("图表1").Chart.ChartData
'激活图形数据表
objChart.Activate
'将数值粘贴到ppt图表数据区
objChart.Workbook.Sheets(1).Range("B1").PasteSpecial Paste:=xlPasteValues
'关闭图形数据
objChart.Workbook.Close
'释放图表变量
Set objChart = Nothing
'保存ppt
objPrs.Save
'关闭ppt
objPrs.Close
'退出ppt文件
objPPT.Quit
Application.ScreenUpdating = True
Set objPPT = Nothing
Set objPrs = Nothing
MsgBox "PPT报告数据更新完毕！"
End Sub

用VBA+函数可以做好很多事情，Excel的报表部分也可以用Power Query来实现，会更加简洁便利，但是我现在不说。这是跟老师学的套路，他总是先教我们一个又笨又累的方法，然后在后面再教一个两三步就能解决问题的方法，他说这是为了打基础，我就呵呵不说话。基础同学都被打了两个月了（；´д｀）ゞ

纳尼？看完手开始抖了？今晚别研究得太晚了，要让双手休息下ψ(._. )>

快速问答

问：零一工具箱的下载地址在哪？

答：https://pan.baidu.com/s/1c1HlyLi#list/path=%2F

问：零一工具箱是否收费？

答：零一工具箱是一款提供给电商运营轻量级的免费工具。

问：零一会是一个什么样的机构？

答：零一会是一个专业数据人才培养基地。

问：零一会有哪些业务？

答：培训孵化——通过系统的培训，一对一小班指导培养数据化运营人才，毕业后就业仍然会享受信息和知识的互通。

人才认证与输送——学习期满颁发权威机构专业证书，为企业输送数据分析师和数据化运营人才。

电商运营——为企业提供代运营服务，提高店铺业绩。