戴戴戴师兄-数据分析课程笔记（第二讲）

总目录

课程地址http://xn--https-my2iwds253at07a//www.bilibili.com/video/BV1ZM4y1u7uF/?spm_id_from=333.337.search-card.all.click&vd_source=9ddee47105d74cb0e3521e3b8ff974de%E2%80%8B

第一讲：系统认识数据分析

第二讲：Excel基础操作

第三讲：Tableau数据可视化与仪表盘搭建

第四讲：SQL从入门到实践

第五讲：Python

---

目录

前言

1. Excel基本概念

2. Excel理解数据

3. Excel常用函数

3.1 SUM

3.2 SUMIF

 3.3 SUMIFS

3.4 SUBTOTAL

3.5 IF

3.6 VLOOKUP

3.7 MATCH

3.8 INDEX

3.9 总结

4. Excel实操-周报制作过程

4.1 输入标题

4.2 输入日期/星期/时间范围

4.3 指标 （结果指标+过程指标）

4.4 数据验证

4.5 GMV

4.6 使用INDEX和MATCH优化4.5

4.7 使用4.6的结果优化公式

4.8 自动填充

4.9 过程指标

4.10 计算总计

4.11 计算营销占比总计

4.12 格式修改

4.13 周累计

4.14 制作迷你图

4.15 周环比

4.16 添加目标

4.17 业务进度

4.18 设置周环比的格式

4.19 标记特殊周平均值

4.20 美化

附录：Excel快捷键大全

参考目录

---

前言

本文详细记录了b站“戴戴戴师兄”的数据分析课程第二讲的授课内容，主要内容包括：Excel的基本概念、常用函数、及实际操作示例。

---

1. Excel基本概念

*对源数据进行备份是所有工作之前的必备步骤*

列名：称作“表头”

结果指标：以“产出”为导向，代表业务的最终结果

业务指标：以“投入”为导向，代表业务的实际过程

2. Excel理解数据

ctrl+shift+L：将工作表改为筛选模式，快速弄清每列数据都有哪些种类

切片器（Slcer）vs 透视表（PivotTable）自带的筛选功能：

        切片器：可以在透视表之外的地方进行筛选

        自带筛选：只能在透视表中进行筛选

新建窗口：复制当前工作簿，作为新窗口展示，方便跨工作表使用数据。注意，本操作并未实际复制任何数据，只是为了方便使用。

日期和数字可以互相替换，即1 = 1990-1-1

3. Excel常用函数

3.1 SUM

SUM(numer1,number2,...)

SUM(列名)

SUM(行名)

SUM(单个或多个单元格)

SUM(列的名称 如A:A)

SUM(行的名称 如1:1)

SUM(’其他工作表‘!A:A) -- 使用非当前工作表中的数据

3.2 SUMIF

SUMIF(range, criteria, sum_range)

 3.3 SUMIFS

SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2,...)

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

同比=（本期数-同期数）/同期数=本期数/同期数 - 1

如：

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

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

日期函数：

YEAR(serial_number) YEAR(日期)

MONTH(serial_number) MONTH(日期)

DAY(serial_number) DAY(日期)

DATE(year,month,day)

DATE(代表年份的数值，代表月份的数值，代表日份的数值)

常见用法：

每个月的第一天：

DATE(YEAR(日期),MONTH(日期),1)

每个月最后一天：

DATE(YEAR(日期),MONTH(日期)+1,1)-1

* 永远不要用Excel的日期格式存储日期，否则导入数据库时难以识别

注意：在公式中使用运算符(<>=)的时候要用""将其包裹，并在之后用&连接具体的条件内容。

3.4 SUBTOTAL

SUBTOTAL(function_num,sum_range)

其中function_num代表想要进行的操作：SUM,AVG,...等

SUBTOTAL vs SUM:

SUBTOTAL:会根据源数据的筛选进行求和，对源数据筛选之后，SUBTOTAL的结果也会相应地发生改变

SUM:直接求和

3.5 IF

IF(logical_test, value_if_true, value_if_false)

IF函数可以嵌套使用

3.6 VLOOKUP

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

VLOOKUP(要查找的数据，要查找的位置和要返回的数据的区域，要返回的数据在区域的列号，返回近似匹配或精确匹配，指示为1/TRUE或0/FALSE)

lookup_value：想要查找的数据

table_array：想要查找的数据+想要返回的数据，注意想要查找的数据列需要在第一列

col_index_num：想要返回的数据来自于table_array的哪一列

range_lookup = 0：精确匹配

range_lookup = 1：近似匹配

注意：

① VLOOKUP只会返回第一个符合条件的数值

② VLOOKUP中可以使用*和?来在精确匹配中实现模糊匹配的功能，格式为：lookup_value&"*" 即以“lookup_value”开头的任意长度的文本

③ VLOOKUP可以匹配数据透视表中的内容，原因在于透视表能做的事情相对有限，如果需要做汇总周报的话不方便使用，所以可以提前将需要的透视表分别做好，然后在制作周报时进行引用。但使用时要注意对table_array的行列进行锁定， 不然table_array可能发生改变导致报错

3.7 MATCH

查找某个单元在表中的行或者列的位置

MATCH(lookup_value,lookup_array,[]match_tupe])

MATCH(查找项,查找区域,0)

0：精确匹配

注意：

MATCH不支持合并单元格

3.8 INDEX

查找表格或区域中的值或值的引用

INDEX(arrary,row_num,column_num)

INDEX(区域,行号,列号)

INDEX和MATCH的嵌套：

INDEX(数据区域,match(行查找项,indexA数据区域的相对区域,0),match(列查找项,indexB数据区域的相对区域,0)

为什么使用INDEX和MATCH：

为了让Excel可以自动更新单元格位置，方便使用自动填充柄

注意：

① INDEX,MATCH可以和SUMIFS结合使用，用于需要求和的项目的查找和计算

② INDEX想要返回整列数据的话，row_num设置为0；INDEX想要返回整行数据的话，column_num设置为0

3.9 总结

4. Excel实操-周报制作过程

4.1 输入标题

20年8月第二周xx事业部

4.2 输入日期/星期/时间范围

只需要填写第一个，然后从第二个开始引用，使用自动填充

星期引用日期，将格式改为星期模式

时间段依旧使用引用，这样方便同步改动

4.3 指标 （结果指标+过程指标）

4.4 数据验证

数据→数据验证→允许：序列→来源：“全部,美团,饿了么”（使用英文逗号分隔）

4.5 GMV

IF(平台=全部,SUMIF(日期列,日期,GMV)),SUMIFS(GMV,日期列,日期,平台列,平台)

注意：

要调整绝对引用和相对引用($)

4.6 使用INDEX和MATCH优化4.5

4.7 使用4.6的结果优化公式

4.8 自动填充

手动计算到手率和客单率

4.9 过程指标

使用GMV的公式，只需要替换指标的位置即可

手动计算进店转化率和下单转化率 

手动计算营销占比

GMV：直接引用单元格

cpc总费用：修改GMV公式，将公式中的C12替换为“cpc总费用”

4.10 计算总计

数值类：选中单元格，alt+=可以直接求和，或者使用工具栏中的快速求和

计算类：从下往上拖拽单元格，进行公式计算

4.11 计算营销占比总计

8-10至8-16的cpc总费用/8-10至8-16的GMV

将cpc总费用中的SUMIF改外SUMIFS，同时时间条件判定改为大于等于当周第一天，小于等于当周最后一天

完整公式↓

4.12 格式修改

抹去小数点，使用%格式

4.13 周累计

曝光人数、进店转化率、下单转化率，直接引用单元格

4.14 制作迷你图

选中数据想要展示的数据→插入→折线（迷你图）→位置范围：选中想要插入的单元格→确定→标记

4.15 周环比

有效订单、商家实收、到手率

有效订单：

本周有效订单：直接引用

上周有效订单：引用本周周cpc总费用公式，将”cpc总费用“改为”有效订单“单元格，再将日期减去7天

商家实收：

更改引用的单元格即可

到手率：

上周商家实收：在计算商家实收环比时已经计算出上周的商家实收

上周GMV：复制上周商家实收公式，将引用的商家实收的单元格换为GMV的单元格

4.16 添加目标

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

4.17 业务进度

业务进度 = 截至目前整个月的GMV/目标

本月第一天：DATE(YEAR($A$13),MONTH($A$13),1)

整个月GMV：将本周GMV中的时间判定起点改为本月第一天，重点改为本周最后一天（因为是业务进度）

添加进度条：

开始→样式→条件格式→新建规则→样式格式：数据条→类型最大最小值：都改为数字，最小值为0，最大值为1→设置颜色，填充：渐变，可以合并单元格使得进度条更为清晰

4.18 设置周环比的格式

Step1：开始→样式→条件格式→新建规则→只包含以下内容的单元格设置格式→单元格值，大于，0→单击格式→设置字体颜色：绿色

Step2：开始→样式→条件格式→新建规则→只包含以下内容的单元格设置格式→单元格值，小于或等于，0→单击格式→设置字体颜色：红色

Step3：新建图标

开始→样式→条件格式→新建规则→基于各自值设置所有单元格的格式→格式样式：图标集→选择图标：>0，=0，<0→类型改为数字

Step4：双击格式刷，对商家实收和到手率的环比进行格式设置

4.19 标记特殊周平均值

开始→样式→条件格式→新建规则→使用公式确定要设置格式的单元格→输入公式=$C13<AVERAGE($C$13:$C$19)→格式：下划线，加粗

4.20 美化

① 视图：取消勾选网格线

② 合并单元格+居中标题+放大字号

③ 周累计、周环比、结果指标、过程指标添加颜色+加粗

④ 曝光人数、进店转化率、下单转化率、有效订单、商家实收、到手率对齐+加粗

⑤ 平台、业务进度、目标添加边框

⑥ 列标题增加颜色条

⑦ 结果指标、过程指标加外框线

⑧ 统一字体

附录：Excel快捷键大全

---

参考目录

本文参考了以下几篇文章或使用了文章中的插图，特此感谢。

[1] Excel 中的键盘快捷方式

[2] 史上最全最常用Excel 快捷键！瞬间提高工作效率！不用再OT啦

[3] bilibili用户@凯凯爱睡觉 的视频截图