自动化报表开发

1.报表框架

1)整体分布

一共分为四个部分：标题、小看板（含目标）、结果指标、过程指标

2)时间信息

①日期日期只需填写一个，其他日期均再次日期的基础上引用，可以极大提升效率，更改一个日期可以联动更新整个工作表的数据。过程指标中的日期也是通过引用该日期实现。

②星期同样引用上一个日期，更改格式即可

③数据时间同样进行日期的引用

2.指标梳理

1)结果指标

与商家收入的相关指数GMV、商家实收、到手率(商家实收/GMV)、有效订单、无效订单、客单价(GMV/有效订单)

2)过程指标

商家经营情况的直观体现曝光人数、进店人数、进店转化率(进店人数/曝光人数)、下单人数、下单转化率(下单人数/进店人数)、营销占比(cpc总费用/GMV)

3.联动筛选

1)下拉筛选器在小看板中目标位置，设置了一个可供切换平台的筛选器，实际上它是一个条件验证，其他函数通过判断当前所选平台来计算对应数据。因此需要先设置该筛选器，后续判断一律引用该筛选器单元格，实现切换平台联动更新数据表。

对应操作：【数据】→【数据验证or数据有效性】→选择【序列】→输入【全部,美团,饿了么】注意使用英文逗号→【确定】

4.条件求和

1)if、sumif、sumifs函数嵌套逻辑

求和GMV指标，需判断当前平台以及日期筛选。if函数判断当前平台是全部、美团还是饿了么，当平台为全部时，无需进行平台筛选，使用sumif函数按日期筛选GMV求和即可；当平台为美团或饿了么时，需使用sumifs函数同时进行平台筛选及日期筛选GMV求和。

true

false

平台=全部

sumif 筛选日期求和

sumifs 筛选平台及日期求和

2)条件求和函数实现

为了实现最大的灵活程度，使用index根据表头查找对应源数据列，只编写一此，通过向下向右拖拽即可自动填充其他求和项。

• 日期列

INDEX('源数据'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0))11

• GMV列（求和列）

• 平台列

INDEX('源数据表'!$A:$X,0,MATCH('平台i','源数据表'!$1:$1,0))11

• 完整函数实现

将该函数应用到过程指标中的【曝光人数】，只需将求和列中的GMV单元格修改为曝光人数对应的单元格即可，其他不变。

INDEX('源数据表'!$A:$X,0,MATCH(C$24,'源数据表'!$1:$1,0))11

• 【曝光人数】完整函数实现

• 商家实收、有效订单、无效订单均可通过GMV指标右拉实现数据自动填充；
• 到手率=商家实收/GMV，客单价=GMV/有效订单，这两个指标进行简单除法运算即可；
• 进店人数、下单人数均可通过曝光人数指标右拉实现数据自动填充；
• 进店转化率=进店人数/曝光人数、下单转化率=下单人数/进店人数，这两个指标同样进行简单除法运算即可；
• 营销占比=cpc总费用/GMV。但是当前数据报表中没有展示cpc总费用的数据，需要额外进行计算，计算方式同求GMV的函数实现完全一致，只需将GMV单元格C12修改为'cpc总费用'(注意中文字符串需加上英文格式双引号)即可，再引用结果指标中已经计算好的GMV数据即可。完整函数实现如下：

=IF($H$5='全部',SUMIF(INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),$A13,INDEX('源数据表'!$A:$X,0,MATCH('cpc总费用','源数据表'!$1:$1,0))),SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH('cpc总费用','源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),$A13,INDEX('源数据表'!$A:$X,0,MATCH('平台i','源数据表'!$1:$1,0)),$H$5))/C3111

3)总计计算

• GMV、商家实收、有效订单、无效订单指标的总计，使用sum函数计算即可；
• 总计的到手率、客单价依旧使用简单除法计算；
• 曝光人数、进店人数、下单人数指标的总计，同样使用sum函数计算即可；
• 总计的进店转化率、下单转化率依旧使用简单除法计算；
• 总计的营销占比=一周的cpc总费用总计/一周的GMV总计。GMV总计可以从结果指标中引用C20单元格，cpc总费用总计需额外进行计算。计算一周的cpc总费用，只需要再计算一天cpc总费用函数的基础上，将单个的日期筛选修改为两个日期的大小判断，大于等于当周的第一天，小于等于当周的最后一天，通过引用日期列的开始日期单元格A13、结束日期单元格A19即可。逻辑如下

• cpc总费用总计完整函数实现

IF($H$5='全部',SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH('cpc总费用','源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),'>='&$A13,INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),'<='&$A19),SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH('cpc总费用','源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),'<='&$A13,INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),'<='&$A19,INDEX('源数据表'!$A:$X,0,MATCH('平台i','源数据表'!$1:$1,0)),$H$5))11

• 总计的营销占比完整函数实现

5.迷你图

小看板中周累计的曝光人数、进店转化率、下单转化率直接引用过程指标数据即可。插入迷你图操作：【选中整周数据】→菜单【插入】→功能区【折线】→选中迷你图放置区域→【确定】→勾选【标记】

6.同比/环比计算

周环比=本周数据/上周数据-1

小看板中周环比的本周有效订单、商家实收、到手率直接引用过程指标数据即可。上周的数据可以通过日期偏移7天来求得，可以利用计算cpc总费用的总计来进行修改，逻辑如下：

if(平台=全部,sumifs(有效订单列,日期列,'>='&开始日期-7,日期列,'<='&结束日期-7),sumifs(有效订单列,日期列,'>='&开始日期-7,日期列-7,'<='&结束日期,平台列,平台))11

• 上周的有效订单（F12单元格)完整函数实现：

• 周环比有效订单完整函数实现(本周有效订单单元格A9)：

=A9/IF($H$5='全部',SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH(F$12,'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),'>='&A13-7,INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),'<='&$A$19-7),SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH(F$12,'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),'>='&A13-7,INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),'<='&$A$19-7,INDEX('源数据表'!$A:$X,0,MATCH('平台i','源数据表'!$1:$1,0)),$H$5))-111

• 周环比商家实收的函数实现与周环比有效订单的函数实现逻辑一致，仅需把函数中的有效订单单元格A9改为商家实收单元格D12即可，完整函数实现如下（本周商家实收单元格C9）：

• 上周的到手率需要通过上周的商家实收和上周GMV进行计算

到手率周环比=本周到手率/上周到手率-1上周到手率=上周商家实收/上周GMV>因此，到手率环比=本周到手率/(上周商家实收/上周GMV)-112341234

• 上周的商家实收已在周环比商家实收中计算过，可直接复制函数使用

• 上周的GMV与上周的商家实收的函数实现逻辑一致，仅需把函数中的商家实收单元格D12改为GMV单元格C12即可

IF($H$5='全部',SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH($C$12,'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),'>='&A13-7,INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),'<='&$A$19-7),SUMIFS(INDEX('源数据表'!$A:$X,0,MATCH($C$12,'源数据表'!$1:$1,0)),INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),'>='&A13-7,INDEX('源数据表'!$A:$X,0,MATCH($A$12,'源数据表'!$1:$1,0)),'<='&$A$19-7,INDEX('源数据表'!$A:$X,0,MATCH('平台i','源数据表'!$1:$1,0)),$H$5))11

• 周环比到手率完整函数实现（本周到手率单元格E9）：

7.进度条

1)添加目标当平台等于全部时，目标为20W；当平台等于美团时，目标为15W；当平台为饿了么时，目标为5W。函数实现如下

=IF($H$5='全部',200000,IF($H$5='美团',150000,50000))11

2)计算业务进度

• 利用DATE、YEAR、MONTH函数求本月的第一天（本周第一天单元格A13）

DATE(YEAR($A$13),MONTH($A$13),1)11

• 求本月第一天至本周最后一天GMV与求上周GMV逻辑一致，将日期判断修改为大于等于本月第一天，小于等于本周最后一天即可，完整函数实现如下：

3)添加进度条菜单栏【开始】→功能区【条件格式】→【新建规则】→格式样式【数据条】→最小值/最大值【数字】→值【0】至【1】→选择填充类型、填充颜色→【确定】

8.图标指示

为小看板中的周环比添加图标指示。正数显示为绿色，同时绿色箭头向下；负数或零时显示为红色，同时红色箭头向下。设置条件格式如下：菜单栏【开始】→功能区【条件格式】→【新建规则】→选择规则类型【只包含一下内容的单元格设置格式】→【单元格值】【大于】【=0】→【格式】→【字体】→【颜色】

• 分别设置字体当数值大于0绿色、小于等于0黄色：
• 设置图标
  菜单栏【开始】→功能区【条件格式】→【新建规则】→格式样式【图标集】→选择对应图标→值【>=】【0】→类型【数字】→【确定】

所有条件设置如下，利用格式刷使该条件格式应用在其他环比数据上

9.突出显示

• 标记结果指标中GMV低于平均值的数据
  选中条件判断区域→菜单栏【开始】→功能区【条件格式】→【新建规则】→选择规则类型【使用公式确定要设置的单元格】→设置公式，判断C13-C19整列数据→【格式】→【字体】→下划线【单下划线】→字形【粗体】→【确定】

10.细节美化

1)标题居中、字体加粗放大2)关键字、关键数据加粗3)表头添加背景颜色、修改字体颜色4)添加外框线