从228页开始，9.7 章节，大概过了一遍本章节的内容。感觉更多是围绕着怎么使用excel来搭建分析报告。excel其实用好了还是蛮牛逼的，部门一个做分析的同事用它来做报表和演示，显示每个Q的一些趋势，这个大老板都很关注的内容。不哔哔了，继续。

9.7.1 案例背景

在业务方团队都在应用画像系统进行渠道营销时，渠道运营人员希望每天能自动收到监测报表来告诉自己目前应用在各业务系统中每个人群的后续访问，下单等转化情况，以便对运营策略有所优化调整，是的运营更加有的放矢。于是画像团队的开发人员面向各业务方搭建了一套人群效果监测的自动化报表。

9.7.2 逻辑梳理

在搭建人群监测报告时，主要分为两个阶段，首先从相关的Hive表中提取数据，关联分析成一张宽表，然后整理好一份Excel 报表模板，最后通过自动报表邮件每天定时跑Hive任务，将数据插入到Excel 报表模板中然后自动发送。

下面通过一个案例一次了解各个环节的实现方式。

1.数据提取

某负责对接用户主动主动外呼营销的运营人员，在画像系统上线人群到各业务系统后，后续需要进一步监控该部分人群的访问、下单等转化情况。

根据业务方圈定的规则，人群‘10003’ ‘10004’ 创建后上线到了邮件营销系统（见下图）

业务方圈定目标人群用于外呼系统

写表dw.user_group_info

1. insert into 表语法：参考

INSERT INTO TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;

2. 向表里 插入数据(以overwrite -覆盖方式写）， 分区为 当日的分区 partition(data_data = 'data_date") insert overwrite table (select from ** where **) 从后表中查询数据并插入表里 dw.user_group_info表。

insert overwrite table . 覆盖式插入。

---insert overwrite 会覆盖已经存在的数据，假如原始表使用overwrite 上述的数据，先现将原始表的数据remove，再插入新数据。最后表的数据 只有新插入的这一条记录。

3.insert overwrite table ** partition () 插入表分区

INSERT INTO TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;

Insert可以插入表或分区，如果表是分区表，则Insert时需要指定插入到哪个分区

2.报表自动化

整体来说，做好一份个性化的Excel数据管理末班需要分三步走：第一步需要根据分析的内容及指标，设计好报告的呈现内容与呈现形式，即根据分析维度搭建数据报告的框架。可以先手动设计好报告的版式；第二步厘清指标之间的逻辑关系，明确报告的呈现内容，设计报告的内容实现逻辑，建立数据源表和数据转化表；第三步按照设计调整报告元素及格式，设计自动化流程。通过从数据源表导入数据即可在报告正文页得到最终呈现的结果。实现流程如下所示。

excel 报告自动化流程

数据仓库：对企业业务数据集日志数据等多个异构数据源集成存储的结构化集成环境，需要数据分析师使用HQL语言从数据库中提取数据；原始数据表：适用于存放每次同胞所需关键指标数据的汇总表，一般用HQL语言经过初步的数据清洗及数据预加工（如汇总、排序、离散、格式转换等）从数据库中提取出来。中间转化数据表：用来动态引用数据源中的数据，并进行相应的数据转化、指标计算、图标绘制及通报文字组合等工作；excel日报正文：根据分析框架，组织引用“数据转化区域”中相应组合好的数据、通报文字及绘制好的图标，以一定格式呈现出来。

接下来会通过一份Excel 运营数据日报来举例说明个性化数据管理报告是如何创建的。

从数据源表到数据转化表

本小节将对上一节中介绍的数据源表和数据转化表的创建过程展开详细介绍。Excel 原始数据sheet 用于存放从数据仓库中提取的原始数据和经过二次计算得到的数据。一般分为原始提取数据、计算数据和辅助数据3个区域。如下图所示：

Excel 原始数据表

提取数据区域用于存放从数据仓库中原始提取出来的数据；辅助数据区域用于存放一些临时的参数，作为辅助列便于计算另一列数据；计算后数据区域用于存放经过计算得到的指标数据。

Excel 中间转化数据表的创建是整个Excel自动化管理中最为关键的一环。通过对数据源表建立动态的数据引用，引用数据源表的相关信息。在数据转化表中主要用到了日期空间、MATCH函数、TEXT函数、OFFSET函数和INDEX函数。下面我们分4步详细讲解数据转化表的建立过程。

第一步，设置日期控制单元

借助日期控制单元我们可以选择查看目标日期的数据，通过引用控制可以自动调整相应数据列变化。

首先我们打开Excel表格，在“开发工具”的“插入”选项卡下面的“表单控件”中选择第二个选项组合框，如下图所示，在表格的空白区域拖拽鼠标即可生成控件。

选择日期控制单元控件

好吧， 我仔细找了下我的 office 360 excel，没看到开发工具项目

右击该日期控件选择“设置控件格式”命令，如下图所示，在弹出的“设置控件格式”对话框中点击“数据源区域”选项的按钮，进入到数据源表中选择对应的日期。

如下图所示，按回车键确认操作。然后设置“单元格链接”，即当我们选择空间日期后存放相应数值的位置。

选择数据源表中的日期

这里我们放在数据转化表日期空间的旁边，如下图所示：

日期空间的使用

至此，当我们在数据转化表的日期空间中选择对应的日期时，旁边单元格即可显示该日期对应其在数据源表中的位置。接下来我们将通过excel表格函数建立起数据与日期控件之间的关系，是的改变选择日期时，对应的数据列做成相应变化。

第二步，从原始数据表动态引用数据

这里我们通过excel 函数从原始表中截取报告所需的部分数据，而截取的时间点，由上步骤的日期控制单元所控制。时间长度可根据业务需要将进行调整。这里我们详细讲解数据动态引用所涉及的函数。

OFFSET函数以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域，该函数的格式如下：

OFFSET(reference，rows,cols,height,width)

OFFSET函数是偏移单元引用的函数，这个偏移不是原始单元格内容的偏移，只是引用单元格的地址发生了变化。该函数中的各参数释义如下：

reference: 是偏移的基点，作为偏移量的引用区域，必须为对单元格或项链单元格区域的引用；rows: 是偏移的行数（其中正数表示向下偏移，负数表示向上偏移，0表示不偏移）cols：是偏移的列（其中正数表示向右偏移，负数表示向左偏移）height: 是所要返回的引用区域的行数。width:是所要返回的引用区域的列数。

当第四和第五参数是正数的时候，单元格引用区域是以那个位移单元格为左上角。如果不想改变引用区域的大小，第四、第五参数可以省略。在单元格录入公示后需要同时按下Shift+Ctrl+Enter键，组合完成输入，输入后编辑显示公式用大括号括起来，此时表示公式起效了。如下图所示：

OFFSET函数使用方式

仔细看了下上面这张图，其实就是对源表做一个数据整理，抽取部分数据到目标表里，抽取的函数就是reference 函数。上面图描述逇很清楚了，比如要找到2017/1/9 日这天的数据，他所在的日期位置是9行。 那么offset(数据源表，rows，cols,height,width)， rows 就指定为9-6，拿这一周的数据（1/3 到1/9 的数据）。另外指定只取源表里面第七列，只取从这里开始的第一列。

当记录日报流水数据时，数据是从杭商不断向下增加得，所以应固定单元格的上方。

INDEX 返回指定位置的内容，该函数的格式如下：

INDEX（array,row-num,column-num）

array： 表示要查找数据的区域，其返回值为单元格区域或数组。row-num: 要查找数据所在的行号。colomn-num: 要查找数据所在的列号。

看到234页，明天继续。