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

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

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

aa38d51bacc1065b283d7b3e5f3d9e64.png

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

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

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

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

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

53f159cd17c5e64cfac18f57e32dfca2.png

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

★ 用户规模(新增用户数、订购用户数)。

★ 业务规模(订单数、业务收入)。

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

一、建立数据模板

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

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

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

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

建立“数据源”表

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

debb225cb4441c8bbf07eebb57c1371c.png

1、建立“数据转化”表

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

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

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

d0e4bdfff642a95b9d3c2109a4bfe0e0.png

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

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

444e29c1d127faaf6398e6fe799ec192.png

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

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

03e0e2e2131c28ad635f582f5790eaa9.png

数据动态引用

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函数,如图所示,单击【确定】按钮。

ee1a1ac884baef3ee9addd651c6295f1.png

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

18eb6ffd6c12111fdc0260af29b977b6.png

★ 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万;”,其他通报正文的组合与此同理,我就不再重复介绍了。直接用鼠标拖动填充柄复制单元格公式完成设置。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值