【数据分析】excel基础操作 | 基础函数 | 大厂周报

2024/4/24

一、周报讲解

1. 标题：XX年XX月第X周

2. 平台筛选：全部/饿了么/美团

3. 日期联动：通过第二行+1后拖拽实现 / 其他位置直接等于该单元格(如时间区间)，第一个改变–>其他随之改变

4. 任务目标

5. 完成进度

6. 本周关键指标：
   过程指标（以“投入”为导向，代表业务的实际过程）
   结果指标（以“产出”为导向，代表业务最终结果）
   进店转化率=进店人数/曝光人数
   下单转化率=下单人数/进店人数（变化趋势图）

7. 下划线/加粗突出显示（快速查看异常）

二、基础概念

1. 备份：右键sheet名–>建立副本（隐藏）-----重要
2. 行列数据、表头标签（列名），一列=一个字段
3. 功能区

• 完成整个作业后，所有功能区按钮的熟悉

三、理解数据

1 看量级

2 筛选（Ctrl+shift+L）粗略熟悉各列数据

对数据保持好奇：
有的是空格与的是点（不同/异常）—（原因：关店重开后更好的收集数据）

3 详细识别各列表头及其数据

1. 曝光量=门店在网页上被多少人看到

2. 访问量=多少人点击进入店铺页面（仅线上）
   门店的曝光量、访问量、下单量与曝光人数、进店人数、下单人数区别：
   人数一般（）更少，是对平台量去重得到的结果，相当于UV和PV的区别

3. PV=page view 用户浏览量，同一用户多次访问浏览量累加
   UV=unique visitors 浏览量，给定时间内访问网页的用户数量（同一个用户不累计）

4. GMV=gross merchandise volume商品交易总额（一般包含拍下未支付订单金额）
   GMV=销售额+取消订单金额+拒收订单金额+退货订单金额
   销售额=访客数（UV）×全店成交转化率×客单价

5. CPC=Cost Per Click每产生一次点击所花费的成本，原指单次广告的点击成本
   在此与付费相关的数据
   Cpc总费用=当天广告投放总花费
   CPC曝光量、Cpc访问量=广告投放给门店带来的曝光量和访问量

6. 补贴=红包补贴金额

• “不要默认数据是正确的，尝试发现问题”

4 插入数据透视表

右边面板选择要展示字段-双击重命名

1. 添加新字段：
   点击数据 --> “数据透视表分析” --> “字段、项目和集”
   单均实收=商家实收/有效订单
   cpc单次点击费用=cpc总费用/cpc访问量

2. “数据透视表分析” --> 插入切片器（平台i—可以选择美团或者饿了么）=筛选器（复制到外部工作表中，外部工作表中的选择与原工作表联动，对外部工作表无影响）

3. “数据透视表分析” --> 数据透视图（右键可更改类型：折线图、柱形图…）
   “数据透视表分析” --> “数据透视图” --> “组合图”（柱子+折线展示）
   ----组合图要求展示字段>2个

切片器在这个sheet中均可用（对表格内容/可视化图都是联动的）

数据透视表作用
数据透视表的介绍和使用

四、常用函数

1 Sum求和（行、列、区域）

1. 公式： Sum(number1,number2,…)
2. 非相连区域 “,” 隔开，完成后回车得结果，相连区域直接成块选择–>回车
3. 基础操作- - -冻结窗格：
   视图-新建窗口（复制窗口的作用）
   视图-冻结窗格（冻结首行或者首列-表头不动），选择某个单元格冻结时=则其上半部分单元格区域被冻结

2 Sumif 单条件求和

1. 公式 ：
   Sumif(range,criteria,[sum_range])----sum_range放在后
   Sumis(条件所在的区域，条件，[用来求和的数值区域])

2. 基础操作- - -$ 绝对引用(左右拖拽时固定行/列)
   相同公式可拖拽实现：
   直接引用=B15，向下B16，向右C15
   =$ B15(列不变，向下$ B16，向右$B15)
   =B$15(行不变，向下B$15，向右C$15)

$加在谁前面就固定谁（行=数字，列=字母）
快速锁定快捷键：先按fn+esc，在按F4 / 直接按fn+F4

3. 基础操作- - -日期默认
   1默认1900-1-1，可实现日期相加减

3 Sumifs 多条件求和

1. 公式：
   Sumifs(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2])
   Sumifs(求和区域，条件区域1，条件1，条件区域2，条件2…)
   与sumif()区别：sum_range放在前（因为有好几个判断标准，但只有一个sum_range）

2. 环比
   环比：与上一个统计周期相比
   环比=（本期数-上期数）/上期数=本期数/上期数-1
   如2020年与2019年（年环比），2020年7月与2020年6月（月环比），2020年7月1日与2020年6月30日（日环比）

3. 同比
   同比：与上一个统计周期的同期相比
   同比=（本期数-同期数）/同期数=本期数/同期数-1
   如2020年与2019年（年同比），2020年7月与2019年6月（月同比），2020年7月1日与2019年7月1日（日同比）

环比和上期比较，日环比=和上一天相比；
日同比，看按照什么的统计周期计算同期：
周（上一周的这一个星期几），月（上个月的这天），年（上年的这个月这天）

4. 计算日期：
   YEAR(), MONTH(), DAY()
   日期组合DATE（YEAR(), MONTH(), DAY()）

每个月第一天=date(year,month,1)
每个月最后一天（下个月第一天-1）= date(year,month+1,1)-1

日期区间用大于等于/小于等于计算时，在sumifs中是两个条件
需要都选择条件所在区域（可能是重复的区域）==条件区域1，大于某日，条件区域2，小于某日，条件区域三，"全部/美团/饿了么"所在单元格 ----三个条件

格式 “美团”，“>=”&

4 Sum与Subtotal的区别

1. 公式： Subtotal(function_num,ref1,ref2,…)返回数据列表的分类汇总
2. 区别： Subtotal()计算的数值，随着类别筛选而改变

5 If函数

1. 公式 ：
   =if(logic_test,[value_if_true],[value_if_false])， value_if_false默认返回False
   =if(逻辑条件，逻辑成立返回值，逻辑错误返回值)

2. If嵌套：
   在value_if_true/false中进行嵌套（逻辑）

6 Vlookup函数和数据透视表聚合

1. 公式：
   =vlookup(lookup_value,table_array,col_index_num,[range_lookup])
   =vlookup(查找条件，查找条件和要返回数据所在区域，要返回的数据在选定区域中的列数从1开始，近似1TRUE/精确0FALSE匹配)

精确查找-注意空格
Vlookup返回查找到的第一个值

2. 模糊查询：
   通配符 “*” 代替不定数量的字符
   占位符 “?” 代替一个字符
   单元格与字符之间用&连接

“b*” b开头的字符，不限长度
"b??"b开头的字符，长度为3

Vlookup用于一个表格1（已有部分数据，也即查找的根据）到另外一个表格2中查找数据，lookup_value单元格不能来自于表格2区域

3. 基于聚合运算结果连接
   选定区域 --> 插入数据透视表（现有工作表–点击选择插入位置）
   聚合： 同一个类别多行变成一行（在“值字段设置” 求和/平均）

聚合透视表中只能计算一种对应数值（门店ID对应的GMV），但周报（无透视表）中可能需要多个数值（门店GMV+平台GMV），因此在周报中直接vlookup引用透视表的值

注意引用区域是否能平移

2024/4/24

7 Index与match函数

根据表头自动取数

1. match（）函数：
   Match(lookup_value, lookup_array,[match_type])
   Match(查找项，查找区域，0) 返回单元格在区域中的位置
2. index（）函数：
   Index(array,row_num,column_num)
   Index(区域，行号，列号) 返回该区域内的单元格数据
3. 混合函数：
   Index(数据区域，match（行查找项，index数据区域的相对区域，0），match（列查找项，index数据区域的相对区域，0）)

注意：是否GMV、人数需要求和（有些能直接取数/有些需要取数并求和如GMV）

4. index返回整行/列：
   行位置=0，返回整列；列位置=0，返回整行

常用函数总结

五、大厂周报开发

1. 搭建框架
   时间+标题+小看板+结果指标+过程指标
   日期/星期直接引用+公式（更改时自动更改–效率）

2. 指标
   结果指标：GMV、商家实收、到手率（商家实收/GMV）、有效订单、无效订单、客单价（GMV/有效订单）
   过程指标：曝光人数、进店人数、进店转化率（进店人数/曝光人数）、下单人数、下单转化率（下单人数/进店人数）、营销占比（cpc总费用/GMV）

3. 添加平台筛选：
   数据–>数据验证–>允许：序列，来源：填写时用英文逗号隔开（实现筛选：全部/美团/饿了么）

4. GMV计算
   If(平台=全部，sumif(日期列，日期，GMV)，sumifs(GMV，日期列，日期，平台列，平台))

分别求出日期列、GMV列、平台列替换（可根据cpc总费用）
日期与平台已经锁定，可只替换GMV列（用index、match–拖拽可实现横竖表格更改）
Index（原表范围，0，match(“日期”，查找区域=全区，0)）—返回列

5. Cpc总费用=单个分开求+sum，或者
   cpc总费用=index、match、sumifs（一周内的日期条件）

6. 格式修改

7. min图（周累计）
   周累计（写好列名后直接引用）：曝光人数+进店转化率+下单转化率，累计=总计
   一周变化缩略图：选择区域–>插入折线–>选择位置–>标记

8. 周环比：有效订单+商家实收+到手率
   上周的数值：根据cpc总费用修改，或者，在对应类型下（如有效订单）修改日期条件（单日变为一周）

2024.04.25

9. 业务进度=截止目前整个月的GMV/目标

10. 添加条件格式
    【业务进度】数据条：
    点击数据–>条件格式–格式样式：数据条，最大1/小0值：数字，可更改颜色和填充方式（渐变），更改数据格式（百分比）

【周环比】颜色/箭头指示升降：
点击数据–>条件格式–>新建规则–>规则类型：“只为包含以下…“，大于0格式绿色，小于等于0时红色
点击数据–>条件格式–>新建规则–>规则类型：“基于各自值设置…”，格式样式：图标集，格式：数字，选择图标并设置
可格式刷复制格式

【指标】将低于周平均的突出显示（加粗下划线）：
选定区域 --> 条件格式 --> 新建规则 --> 类型：“使用公式确定…”，公式：平均值average（），设置显示格式

11. 样式修改
    视图 --> 取消网格线
    标题：居中 + 放大加粗
    加粗-加颜色-加边框

下列问题是否解决：

• 1. excel基础知识
• 2. 数据透视表
• 3. 函数使用和函数大全
• 4. 周报业务逻辑
• 5.大厂数据周报开发
• 6. 使用函数嵌套填充