根据电脑分辨率自动调整窗体大小_EXCEL日报自动化_1

一般日报都较为简单，主要是通报企业运营的一些关键指标的完成情况，通报的指标、内容都基本固定，所以我们可以把日报模板化、自动化。

Excel日报自动化的原理，如图

① 通过VBA语句，从数据库自动提取前一日相应的关键指标数据，并自动追加放置在一张名为“数据源”表中的相应位置，实现一键自动提取数据。

② 在数据转化区中，根据指定的日期条件，动态引用“数据源”表中相应的数据，并自动绘制图表、组合通报文字。

③ 在日报正文区中，引用相应的组合好的通报文字与绘制的图表。

④ 通过控件选择需要通报的日期，并自动生成相对应日期的日报正文。

我们仍以“用户明细”、“订购明细”表为日报数据源，介绍Excel日报自动化的实现。如图所示，这是我们日报的最终展现结果，只要一键提取数据，并选择需要通报的日期，Excel就会自动引用相应数据，并生成相应的日报，连通报文字都组合好，省时又省力。

纵览这份日报，我们可以看到这份日报分为两大部分：

★ 用户规模（新增用户数、订购用户数）。

★ 业务规模（订单数、业务收入）。

这两部分相当于报告的分析框架，不论是专题分析报告，还是月报、周报、日报，每份报告都需要有层次清晰的分析框架，以便阅读者一目了然，正确、快速地理解报告内容。

一、建立数据模板

建立数据模板，实现Excel日报自动化，在“日报.xls”文件中需要准备三张表。

★ “数据源”表：用于存放每日通报所需的关键指标数据。

★ “数据转化”表：用于动态引用“数据源”表中相应的数据，并进行相应的数据转化，最后自动绘制图表，组合通报文字。

★ “日报正文”表：根据分析框架，组织引用“数据转化”表中相应的组合好的通报文字与绘制好的图表，并呈现日报。

建立“数据源”表

首先我们需要建立日报所需的“数据源”表，把日报需要通报的关键指标都整理进来，如图所示，关键指标有“新增用户数”、“订购用户数”、“订单数”、“业务收入”、“累计订购用户数”、“累计用户数”、“累计订单数”、“累计业务收入”。

1、建立“数据转化”表

★ 动态引用“数据源”表中相应的数据，并自动绘制图表。

★ 动态引用“数据源”表中相应的数据，并进行相应的数据转化，以及通报文字的自动组合。

“数据转化”表如图所示，主要有“图表数据区”、“柱形图”、“通报数据区”、“通报数据转化区”、“通报正文区”、“日期下拉选择控件”六大要素组成，进一步可归纳为控件、图表、文字通报三大类。

后续的图表、文字通报的数据动态引用都需要根据控件来选择调用。

STEP 01 在“数据转化”表中，单击【开发工具】选项卡，在【控件】组中，单击【插入】按钮，在弹出的【表单控件】中选择【组合框(窗体控件)】，这时鼠标变为“十”字形状。在希望放置窗体控件的位置按下鼠标左键不放，拖动鼠标画出一个矩形，这个矩形代表了该窗体控件的大小，对窗体控件大小满意后放开鼠标左键，这时就出现一个下拉窗体控件，如图所示。

STEP 02 选中刚插入的下拉窗体控件，单击鼠标右键，选择【设置控件格式】。

STEP 03 在弹出的【设置控件格式】对话框中，设置【数据源区域】范围为“数据源!$B$5：$B$126”，也就是通报的日期范围，然后设置【单元格链接】范围为“数据转化!$F$2”，也就是选择相应日期时，会输入对应的数值，【下拉显示项数】如无特殊需求，可采用默认的下拉显示项数，单击【确定】按钮，如图所示。

数据动态引用

OFFSET函数以指定的引用为参照系，通过给定偏移量得到新的引用，返回的引用可以是一个单元格或单元格区域，并且可以指定返回的行数或列数。

OFFSET函数的用法如下：

OFFSET (Reference, Rows, Cols, [Height], [Width])

OFFSET函数的参数解释如下。

★ Reference：作为偏移量参照系的引用区域，Reference必须为对单元格或相连单元格区域的引用；否则，OFFSET返回错误值 “#VALUE!”。

★ Rows：相对于偏移量参照系的左上角单元格，上（下）偏移的行数，行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。

★ Cols：相对于偏移量参照系的左上角单元格，左（右）偏移的列数，列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。

★ Height：高度，即所要返回的引用区域的行数，Height必须为正数。

★ Width：宽度，即所要返回的引用区域的列数，Width必须为正数。

STEP 01 用鼠标选中图中 “数据转化”表的A3：E6单元格区域。

STEP 02 单击编辑栏左边的【插入函数】按钮，在弹出的【插入函数】对话框的【查找与引用】类别中找到OFFSET函数，如图所示，单击【确定】按钮。

STEP 03 在弹出的【函数参数】设置对话框中，分别对每个参数依次进行设置，如图所示。

★ Reference参数设置为“数据源!B1”，即以“数据源!B1”单元格为引用参照系。

★ Rows参数设置为单元格“F2”，也就是下拉窗体控件输出的数值n，即向下偏移n行。

★ Cols参数设置为“0”，即向右偏移0列，也就是不对列进行偏移，直接取“数据源!B1”单元格所在的B列。

★ Heigh、Width参数分别设置为“4”、“5”，即所要返回的引用区域为一个4行5列的单元格区域。

这几个参数综合起来的解释就是以“数据源!B1”单元格为引用参照系，向下偏移n行，不偏移列，引用4行5列的单元格区域数据。

STEP 04 设置完【函数参数】对话框中各个参数后，按下【Ctrl+Shift+Enter】组合键，即可得到根据控件输出数值调用的数据区域。

文字通报组合

我们要准备“通报数据区”、“通报数据转化区”、“通报正文区”三个数据区域。

★ “通报数据区”与“图表数据区”作用类似，就是实现根据控件所输出的数值去引用相应的数据。

★ “通报数据转化区”就是将“通报数据区”转化为所需要的文本格式，并在各数据之间插入相应的连接词语，以便下一步进行文字的连接组合，例如订购用户数28284，可将其转化为订购用户数2.8万。

★ “通报正文区”就是将“通报数据转化区”已转化好的数据及连接词语，依次连接组合起来，形成一句完整的通报正文。

（1）通报数据区

现在我们先完成“通报数据区”的数据引用，因为“图表数据区”已经引用了一部分数据，所以可以直接引用我们所需的数据，各个区域的数据设置如下。

★ 当日、昨日数据我们可分别直接引用“图表数据区”B6：E6、B5：E5的数据。

★ 环比数据可通过公式“当日/昨日-100%”计算得到。

★ 累计数据则需要采用VLOOKUP函数根据控件输出的数值，从“数据源”表匹配相应的数据，例如累计用户数的数据匹配公式为：“=VLOOKUP($F$2+3,数据源!A：H,8,0)”，其他累计数据调用方式以此类推，进行设置调用。

（2）通报数据转化区

现在我们来看“通报数据转化区”的设置，关键就是数据的文本转化，各个区域的数据设置如下。

★ 根据数值大小，将当日、累计数据转化为带有相应单位的文本数据。例如订购用户数28284转化为订购用户数2.8万，我们可采用TEXT函数，在单元格J12输入“=TEXT(J4/10000,"0.0")”，这样即可把数值转化为需要的本文格式。

★ 将环比数据进行文本转化，例如可在单元格N12输入“=TEXT(ABS(L4),"0.0%")”，因为环比可能上升，也可能出现下降，所以要先对环比数据取绝对值，然后再进行文本转化。

★ 用If函数对环比数据进行判断，根据结果相应赋予“上升”、“下降”、“持平”的文字，例如我们可在单元格L12输入“=IF(L4>0,"上升",IF(L4<0,"下降","持平"))”。

★ 在各个转化后的文本数据之间加入连接词语，为下一步通报正文连接组合做准备，使其形成一句完整的通报正文。

（3）通报正文区

最后就是将“通报数据转化区”已转化好的文本数据及连接词语，依次连接组合起来，形成一句完整的通报正文，我们可采用CONCATENATE文本连接函数或者“&”文本连接符进行连接，如果需要连接的单元格较多时，建议采用CONCATENATE文本连接函数，这样做简单方便快捷，不容易出错。

例如我们可在单元格I19输入“=CONCATENATE(I12,J12,K12,L12,M12,N12,O12,P12)”，这样就连接组合成一句通报正文：“1.2、当日订购用户数：2.8万，环比前日上升27.6%，累计订购用户数4.6万；”，其他通报正文的组合与此同理，我就不再重复介绍了。直接用鼠标拖动填充柄复制单元格公式完成设置。