Excel数据分析基础操作（常见函数+周报制作）

前情提要：

本文章的内容来自B站"戴师兄数据分析启蒙课"的课堂内容，以下所有均为自己的课堂练习+笔记，想要源数据的小伙伴可以去B站搜索戴师兄的课进行获取哦~~

1、UV（Unique visitor）（去重）

是指通过互联网访问、浏览这个网页的自然人。访问网站的一台电脑客户端为一个访客。00:00-24:00内相同的客户端只被计算一次，一天内同个访客多次访问仅计算一次

2、PV（Page View）（不去重）

即页面浏览量或点击量，用户每1次对网站中的每个网页访问均被记录1个PV。用户对同一页面的多次访问，PV会被累计。

• CPC（Cost Per Click）
  每产生一次点击所花费的成本

3、环比与同比

年

• 2020年环比
• =（2020年数据-2019年数据）/2019年数据
• = 2020年数据/2019年数据 - 2019年数据/2019年数据
• = 2020年数据/2019年数据 - 1

月

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

日

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

总结

• 同比=（本期数-同期数）/同期数=本期数/同期数 - 1
• 环比=（本期数-上期数）/上期数 = =本期数/上期数 - 1

数据透视表

数据透视表中的筛选与切片器：数据透视表内置筛选只能在透视表内进行，切片器可以在数据透视表外的地方进行。
数据透视表相当于局部变量，切片器相当于全局变量

插入切片器

Excel常用函数

1、SUM求和函数

• SUM(number1,number2,…) SUM(列名) SUM(行名)
• SUM(单个或多个单元格) SUM(列的名称 如：A：A) SUM()行的名称(如：1:1)

2、SUMIF单条件求和函数

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

对于以上“二”中的数据填充，向下拖拽即可。注意：
我们直接引用B15单元格，输入=B15，然后向右拖拽，会变成C15，向下拖拽会变成B16

• =$B15
  • 现在，我们在列号B前添加 $，那么列就不会变了
  • 拖动句柄向右，列号不变，为$B15
  • 拖动句柄向下，行号会变，为$B16
• =B$15
  • 现在，我们在行号15前加$，那么行就不会变了
  • 拖动句柄向下，行号不变，为B$15
  • 拖动句柄向右，列号会变，为C$15

3、SUMIFS多条件求和函数

• SUMIFS(sum_range,[criteria_range],[criteria1],[criteria_range2],[criteria2],…)
• SUMIFS(用来求和的数值区域，条件1判断所在的区域1，条件1，条件2判断所在的区域2，条件2，…)

• 计算某一日期范围内的GMV

注意：

• 像“美团”这样的字符串是文本格式，需要加英文双引号
• 条件参数直接引用单元格或者使用函数则不需要英文双引号
• 大于等于符号也需要添加英文双引号""，并使用&才能与后面的条件值相连

4、快速计算日期的函数

• YEAR（serial_number）
  YEAR(日期)
• MONTH（serial_number）
  MONTH(日期)
• DAY（serial_number）
  DAY(日期)
• DATE（year,month,day）（日期组合函数）
  DATE(表示年份的数值，代表月份的数值，代表日份的数值)
• EDATE（start_date,months）
  返回一串日期，表示指示日期之前或之后的日期
  EDATE函数可以精准定位月份，加减多少月份都可以

注意：
永远不要用Excel的日期去存储日期（用字符串形式）

• 如何正确的求每个月的最后一天
  每个月第一天：date(year(日期),month(日期),1)
  每个月最后一天:date(year(日期),month(日期)+1,1)-1，即，先求出这个月为基础的下一个月的第一天，下个月的第一天-1即为这个月的最后一天。

5、sum函数和subtotal函数的区别

subtotal函数可以根据原数据的筛选进行求和，即根据原数据的变化而变化。

6、IF函数

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

• if函数嵌套使用

7、VLOOKUP函数

• VLOOKUP（lookup_value,table_array,col_index_num,[range_lookup]）
• VLOOKUP(要查找的数据，要查找的位置和要返回的数据的区域，要返回的数据在区域中的列号，返回近似匹配或精确匹配-指示为1/TRUE或0/FALSE)
• 只会返回查到的第一个值

• 模糊匹配
  “*”：代表不定数量的通配符
  “？”：（英文输入状态下）代替一个字符

• VLOOKUP去匹配数据透视表

8、index和match函数基于原数据自由取数和聚合运算

注意：
不要忘记锁定

• match找位置，index去位置取值
• MATCH（lookup_value,lookup_array,[match_array]）
• MATCH(查找项，查找区域)
• INDEX（array,row_num,column_num）
• INDEX(区域，行号，列号)
• index(数据区域，match(行查找项，inex数据区域的相关区域，0)，match（列查找项，indexB数据区域的相关区域，0）)，即index先横着找，再竖着找。
• index返回整列，行号=0

周报开发

确定框架

注意：
为了实现数据之间的联动，要多使用引用。

周报的指标

结果指标

• GMV
• 商家实收
• 到手率=商家实收/GMV
• 有效订单
• 无效订单
• 客单价=GMV/有效订单

过程指标

• 曝光人数
• 进店人数
• 进店转化率=进店人数/曝光人数
• 下单人数
• 下单转化率=下单人数/进店人数
• 营销占比=cpc总费用/GMV

1、日期之前的联动

其中，周几的显示在日期格式中有。

2、利用数据验证

实现效果如下：

实现过程：

3、计算指标

GMV

• 计算逻辑
  if（平台=全部，sumif（日期列，日期，GMV），sumifs（GMV,日期列，日期，平台列，平台））

=IF($H$5="全部",
SUMIF('拌客源数据1-8月'!$A:$A,$A13,'拌客源数据1-8月'!$J:$J),
SUMIFS('拌客源数据1-8月'!$J:$J,'拌客源数据1-8月'!$A:$A,$A13,'拌客源数据1-8月'!$H:$H,