制造cpk数据分析怎么做 excel做cpk分析

前情提要：

本文章的内容来自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,$H$5))

• 写出以上GMV的初始函数后，利用index-match函数对其中相关列进行替换（方便实现联动）

#时间列
=INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$1:$1,0))
#求和列（GMV列）
=INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$12,'拌客源数据1-8月'!$1:$1,0))
#平台列
=INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("平台i",'拌客源数据1-8月'!$1:$1,0))

将以上的三个表达式分别替换GMV的初始函数，得：

=IF($H$5="全部",
SUMIF(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$1:$1,0)),$A13,
INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$12,'拌客源数据1-8月'!$1:$1,0))),
SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(C$12,'拌客源数据1-8月'!$1:$1,0)),
INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$1:$1,0)),$A13,
INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("平台i",'拌客源数据1-8月'!$1:$1,0)),$H$5))

利用以上函数求出GMV列后进行拖拽，即可求出剩下的指标。其中：

• 到手率=商家实收/GMV;
• 客单价=GMV/有效订单；
• 进店转化率=进店人数/曝光人数
• 下单转化率=下单人数/进店人数
• 营销占比=cpc总费用/GMV
  求出结果如下图所示：

计算总计

注意：

• 一周的营销占比的总计=这一周的cpc总费用/这一周的GMV，而不是单纯的将这一周每天的营销占比相加

=IF($H$5="全部",
SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("cpc总费用",'拌客源数据1-8月'!$1:$1,0)),#cpc总费用列
#计算一周
INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$1:$1,0)),">="&$A13,#日期大于A13的列
INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$1:$1,0)),"<="&$A19),#日期小于A19的列
SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("cpc总费用",'拌客源数据1-8月'!$1:$1,0)),#cpc总费用列
#计算一周
INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$1:$1,0)),">="&$A13,#日期大于A13的列
INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$1:$1,0)),"<="&$A19,#日期小于A19的列
INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("平台i",'拌客源数据1-8月'!$1:$1,0)),$H$5))/C20

• 数值类的求和可以从左往右拖拽，但是要计算有除法公式的，需要从上往下拖拽。（即求和后再从上一单元格往下拖拽一次）

插入迷你图

计算周环比

计算有效订单

#此函数由计算营销占比的函数变化而来，只需要修改日期以及表头的引用即可，同时也将sumif函数改为sumifs函数
=A9/
IF($H$5="全部",
SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$8,'拌客源数据1-8月'!$1:$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$1:$1,0)),">="&($A13-7),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$1:$1,0)),"<="&($A19-7)),
SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$8,'拌客源数据1-8月'!$1:$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$1:$1,0)),">="&($A13-7),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$1:$1,0)),"<="&($A19-7),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("平台i",'拌客源数据1-8月'!$1:$1,0)),$H$5))
-1

计算商家实收

计算商家实收与计算有效订单类似，函数都非常相像，重点放在修改表头的引用

#与上一个函数对比可知修改的地方不多
=C9/
IF($H$5="全部",
SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($C$8,'拌客源数据1-8月'!$1:$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$1:$1,0)),">="&($A13-7),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$1:$1,0)),"<="&($A19-7)),
SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($C$8,'拌客源数据1-8月'!$1:$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$1:$1,0)),">="&($A13-7),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$1:$1,0)),"<="&($A19-7),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("平台i",'拌客源数据1-8月'!$1:$1,0)),$H$5))
-1

计算到手率

注意：

• 到手率周环比=本周周环比/上周周环比-1
• 上周到手率=上周商家实收/上周GMV
• 到手率周环比=本周到手率/(上周商家实收/上周GMV)-1

#这部分函数虽然看起来很长，但是大部分都是重复的（不用感到孩怕）
#因为前面计算商家实收周环比时已经计算好了上周的商家实收，我们将计算上周商家实收的函数中关于商家实收的引用"$C$8"改为“$C$12”就可以计算上周的GMV
=E9/
(IF($H$5="全部",
SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($C$8,'拌客源数据1-8月'!$1:$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$1:$1,0)),">="&($A13-7),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$1:$1,0)),"<="&($A19-7)),
SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($C$8,'拌客源数据1-8月'!$1:$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$1:$1,0)),">="&($A13-7),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$1:$1,0)),"<="&($A19-7),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("平台i",'拌客源数据1-8月'!$1:$1,0)),$H$5))/
IF($H$5="全部",
SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($C$12,'拌客源数据1-8月'!$1:$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$1:$1,0)),">="&($A13-7),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$1:$1,0)),"<="&($A19-7)),
SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($C$12,'拌客源数据1-8月'!$1:$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$1:$1,0)),">="&($A13-7),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$12,'拌客源数据1-8月'!$1:$1,0)),"<="&($A19-7),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("平台i",'拌客源数据1-8月'!$1:$1,0)),$H$5)))
-1

计算目标

=IF($H$5="全部",20000,IF($H$5="美团",150000,50000))

计算业务进度

• 业务进度=整个GMV（这个月）/ 目标
• 利用DATE函数计算出这个月的第一天

设置进度条

字体格式

图标格式

GMV格式

周报最后结果呈现

总结

以上的周报做好后，后续需要更新数据，只需要将对应位置的数据更新即可，因为整个报表都是联动的。