Excel自动化数据报表制作

一、基本概念

1.表格组成：工作簿-工作表-行/列-单元格
2.功能区
（空闲时可逐一进行了解）鼠标悬停在功能区某一按钮上即会出现该功能注释

二、基础操作

• 源数据备份
  拿到一份数据之后，第一步操作进行备份，以免对数据造成不可还原的修改。
  操作：选中源数据工作表-【右键】选择【移动或复制工作表】-选中【移至最后】并勾选【创建副本】-选中副本-【右键】【重命名】为“源数据备份”
  

三、理解数据

1.数据量级

• 选中整行：整个数据一共24列
  

• 选中整列：整个数据一共562行，除去表头数据实际一共521行
  

2.数据类型

注:该报表使用的数据为线上外卖门店的数据

• 日期:指的是一家外卖门店当天所有营业额所归属的日期；
• 品牌ID:相当于品牌身份证号
• 品牌名称:门店所在品牌名称
  Ctrl+Shift+L快速筛选，可以快速了解门店品牌数、排序、筛选等；
• 门店ID:相当于门店的身份证号
• 门店名称
• 城市
• 平台:平台的拼音格式
• 平台i:平台的中文格式
• 平台门店名称
  注：同样一家店，存在不一样的格式（如:拌客干拌麻辣烫(武宁路店)→拌客干拌麻辣烫（武宁路店）→拌客·干拌麻辣烫(武宁路店)，以增加空格、点的形式区分店名），原因是该门店存在关店重开的情况，每次关店重开后会更换平台门店名称以此与之前的门店做区分，更方便的收集数据。
• GMV:全称为GrossMerchandiseVolume即商品交易总额，是一段时间内的成交总额的意思，多用于电商行业，一般包含拍下未支付订单金额（未刨除各项补贴的表面金额）。
• 商家实收:去除平台补贴、商家补贴、红包、满减、配送费、平台抽成等等后，当天实际可以进入银行卡/平台账户收入里的金额
• 门店曝光量:指的是该门店在外卖平台被用户看到的次数
• 门店访问量:用户进入该门店的次数
• 门店下单量
• 无效当订单
• 有效订单
• 曝光人数：对应去重的曝光量
• 进店人数：对应去重的访问量
• 下单人数：对应去重的下单量
• cpc总费用:CPC(Cost Per Click)每产生一次点击所花费的成本。在这个报表中cpc总费用指的是当天广告投放的总花费
• cpc曝光量:广告投放为门店带来的曝光量
• cpc访问量:广告投放为门店带来的访问量
• 商户补贴:商家补贴金额
• 平台补贴：平台补贴金额

3.数据含义

环比

环比=(本期数/上期数)/上期数=本期数/上期数-1

1)年环比

2020年环比=(2020年数据-2019年数据)/2020年数据=2020年数据/2019年数据-1

2)月环比

2020年7月环比=2020年7月数据/2020年6月数据-1

3)日环比

2020年7月1日环比=2020年7月1日数据/2020年6月30日数据-1

同比

同比=(本期数-同期数)/本期数=本期数/同期数-1

1)月同比

2020年7月同比=2020年7月数据/2019年7月数据-1

2)日同比

2020年7月1日的月同比=2020年7月1日数据/2020年6月1日数据-1
2020年7月1日的周同比=2020年7月1日数据/2020年6月24日数据-1

四、常用函数

1.sum：可以对整行、整列、一个区域进行求和

2.sumif：单条件求和

sumif(range,criteria,[sum_range])
sumif(条件判断所在的区域,条件,[用来求和的数值区域])

3.sumifs：多条件求和

sumifs(sum_range,[criteria_range1],[criteria1],[criteria_range2],[criteria2],..)
sumifs(用来求和的数值区域,条件1判断所在的区域1,条件1,条件2判断所在的区域1,条件2,...)

例：求整月美团平台的GMV

=sumifs(GMV列,平台列,"美团",日期列,">="&每月第一天,日期列,"<="&每月最后一天)

注：条件参数直接引用单元格或者使用函数不需要英文双引号，若使用如"美团"这样的字符串做条件参数，需要加英文双引号；以及大于等于号也需要添加英文双引号，并且使用&才能与后面的条件值相连。

4.subtotal：根据筛选求和、平均等等（11个函数,以数字区分，9为sum函数）

subtotal(function_num,ref1,[ref2],...)
subtotal(指定函数，选择区域1，[选择区域2],...)

例如：

subtotal(9,GMV列)=sum(GMV列) 仅当全选时该等式成立。

5.Year:提取日期的年，以数字格式显示

YEAR(serial_number) YEAR(日期)

6.Month:提取日期的月

MONTH(serial_number) MONTH(日期)

7.DAY:提取日期的天

DAY(serial_number) DAY(日期)

8.DATE:组合年月日

DATE(year,month,day) DATE(代表年份的数值，代表月份的数值，代表日份的数值)

与YEAR()、MONTH()、DAY()组合，可用来求得上一年、上月、上周、昨天、下一年、下个月、下周、明天等对应日期。例如：

每个月的第一天=DATE(year(日期),month(日期),1)；
每个月的最后一天=下个月的第一天-1=DATE(year(日期),month(日期)+1,1) -1

注：不要使用excel里的日期格式存储日期，建议使用字符串形式存储日期，否则将表格导入数据库会出现其他问题

8.if：逻辑判断

if(logical_test,value_if_true,[value_if_false])
if(逻辑比较条件,结果成立时返回的值,[结果不成立时返回的值])
[value_if_false]:该参数选填，没有该参数时，返回False

例：利用if函数嵌套，判断A、B是否为0

if(A=0,if(B=0,"AB都为0","A等于0，B不等于0"),if(B=0,"A不等于0，B等于0","AB都不等于0"))