文章目录
4. Excel
4.1 周报业务逻辑讲解
![image-20230917145350122](https://img-blog.csdnimg.cn/img_convert/4f0748a469f97fa369a7b626a3f87f61.png)
- 在这种周报,可以根据平台和日期筛选所有数据,联动的展示目标完成的进度
- 转化率和变化趋势
- 进店转化率=进店人数/曝光人数
- 下单转化率=下单人数/进店人数
- 结果指标和过程指标
- 结果指标:以“产出”为导向,代表业务的最终结果
- 过程指标:以“过程”为导向,代表业务的实际过程
4.2 基础概念
-
源数据备份
- 点击源数据工作表,右键工作表,点击移动或复制,选择(移至最后),勾选建立副本,然后点击确
定 - 接下来双击复制的工作表,重命名为源数据备份,右键源数据备份,点击隐藏
- 这是拿到所有Excel表格都要进行的第一步操作,使用任何数据都要养成备份的好习惯,以免对数据造成不可还原的修改
- 如果想取消隐藏,只需要右键任一工作表,点击取消隐藏,选择工作表即可
- 点击源数据工作表,右键工作表,点击移动或复制,选择(移至最后),勾选建立副本,然后点击确
-
Excel的基本单位
- 单元格
- 在单元格中我们可以填写各种文本、数值、或公式
-
行和列
- 单元格横向会组成一行数据,每行数据在最左侧都会有自己的数字格式索引标签
- 单元格竖向会组成一列数据,每列数据在最上方会有自己宇母格式的索引标签
- 并且每列数据还会有自己的列名,这个列名也叫做表头,相当于SQL和Tableau等高级工具里的宇
段名称
-
工作表
- 点击加号后可以新建的一个个Sheet
-
工作簿
- •工作表会共同组成一个工作薄,也就是我们打开的.xisx文件
-
总结一下
- 一个工作薄可以包含多个工作表 ,工作表里又包含许许多多由单元格组成的行和列
-
Excel的功能区和菜单
- 功能区里包含了不同的选项卡,对各种功能进行了分类
- 例如:点击文件,就可以新建、保存另存为、打印工作薄,还能导出为PDF格式
- 在左下方的选项可以对Excel进行各种设置,还能添加各种功能区和用于高级操作的加载项
4.3 练习数据熟悉
-
数据量级
- 分别点击A和1,出现小箭头则表示选取整列或整行,在右下角我们就能看到对应的数值
- 分别是24列和562行,去掉表头就是561行
-
每一列数据的含义和数据类型
-
日期:一家外卖门店当天营业数据所归属的日期
-
品牌ID:这家门店所在品牌在数据库的唯一识别码,相当于品牌的身份证号
-
品牌名称:门店所在的品牌名称
-
门店D:门店的身份证号
-
城市:上海市
-
平台:拼音格式
-
平台:中文字符格式
-
平台门店名称:关店重开,每次会换一个平台门店名称,以此与之前作区分
-
GMV:门店当天的营业额,也就是所有用户点外卖时的订单金额总和
-
商家实收:商家当天实际收入进账的金额,需要将GMV减去各种红包补贴、配送费和平台抽成等费用
-
店曝光量:门店当天在外卖平台被用户看到了多少次
-
访问量:点击进入了多少次
-
下单量:最后下单的订单数
-
无效订单:下单后又退款或取消的订单数,它和有效订单相加就等于门店下单量
-
曝光人数:门店被用户看到的人数
-
进店人数:门店进店的人数(线上统计)
-
下单人数:门店的下单人数
-
cpc:单次广告点击成本
-
cpc曝光量和访问量:广告投放为门店带来的曝光量和访问量
-
•商家补贴和平台补贴:商家和平台的红包补贴金额
-
-
ctrl+shift+L:表格进行筛选模式,可以升序、降序、筛选】【mac上也是control+⬆️+L】
-
注意PV和UV的重要概念区别
4.4 数据透视表+图
-
新建窗口,可以方便对源数据进行引用
通过视图的新建窗口,可以新建出源数据的另一份窗口,方便我们进行编写函数时的区域选择,而不需要来回切换工作表
-
数据透视表+切片器
- 插入数据透视表
- 选中源数据中任意一个单元格,点击插入,数据透视表,确定,新建一张新的数据透视表
- 此时会新起一个sheet工作表,里面包含了数据透视表
-
拖拽字段进行计算和重命名
-
将文本型字段拖到行、数值型字段拖到值,透视表就会自动进行运算
-
双击字段段名称,可以重命名,并且选择计算的类型,Excel默认的计算方式是求和
在右侧会出现透视表的字段,我们将字段拖到行、值等位置
-
-
创建新的字段
-
选中数据透视表,点击数据透视表分析选项卡,进入功能区,在计算部分找到宇段、项目和集,点击插入计算字段
-
计算两个字段单均实收和cpc单次点击费用
- 单均实收二商家实收/有效订单
- cpc单次点击费用=CpC总费用/cpc访问量
-
-
插入切片器
-
在数据透视表分析功能区,在筛选部分找到插入切片器,选择字段作为切片器
-
点击切片器的选项,数据就会自动地根据切片器的选项进行计算透视表的内容
-
除了切片器筛选,还可以直接将字段拖拽到筛选
-
注意:透视表本身的筛选只能在透视表内使用,而切片器可以复制到任意工作表使用,以实现对透视表的筛选
-
-
数据透视图
-
点击功能区的工具-数据透视图就可以直接插入图表,默认插入柱状图
-
右键绘图区,可以【更改图表类型】
-
选择【组合图】,可以直观地展现两个数值
- 一张透视表可以插入多张透视图
- 都可以通过切片器来控制,这样就可以用切片器轻松实现图表的联动
-
4.5 常用函数
sum
- SUM(number1,number2,.)SUM(列名)SUM(行名)
- SUM(单个或多个单元格)SUM(列的名称 如:A:A) SUM(行的名称 如:1:1)
sumif
- SUMIF (range, criteria, [sum_range])
- SUMIF(条件判断所在的区域,条件,[用来求和的数值区域])
sumifs
- SUMIFS(sum_range, [criteria_range1], [criteria1], [criteria_range2], [criteria2]…)
- SUMIFS(用来求和的数值区域,条件1判断所在的区域1,条件1,条件2判断所在的区域2,条件2.)
注意编写函数锁定单元格过程:
加美元符号可以在拖动句柄的时候锁定
相对引用:随便拖拽会改变
混合引用:行或列某个区域会改变
绝对引用:行和列都不变
在sumifs中加条件判断需要在 >=的类似符号中加“”,并且,并且不能连接的公式条件前需要加“&”符号
环比是与上一个相邻的时间单位相比,同比是与上一年或者上一个月的相同的时间单位进行比较
![image-20230915160104847](https://img-blog.csdnimg.cn/img_convert/2ea9e7708ddc7591fd7e7eadf12965d3.png)
![image-20230915160232803](https://img-blog.csdnimg.cn/img_convert/aa6e739c97845034dfaa35f54ff80095.png)
永远不要使用excel的日期格式来存储日期,导入数据库会出问题
![image-20230915164810568](https://img-blog.csdnimg.cn/img_convert/8ce98058856881fa4442c88ebea8b42b.png)
sum和subtotal的区别
-
SUBTOTAL(function_num,ref1,[ref21…)
-
SUBTOTAL(指定函数,选择区域1,[选择区域21.)
sum对指定区域求和后无法随筛选改变, 随便对源数据进行筛选,subtotal都可 以跟随筛选进行改变,相当于一个自动根据筛选进行的求和
此时如果将源数据的平台i筛选为美团,sum函数不会改变,但是subtotal会改变
if函数&嵌套
-
IF(logical_test, value_if _true, [value_if_false])
-
IF(逻辑比较条件,结果成立时返回的值,[结果不成立时返回的值])
-
[lvalue_if_false]:该参数选填,没有该参数时,返回值False
- 第一个参数是我们的判断条件
- 第二个参数是判断为真时返回的结果
- 第三个参数是判断为假时返回的结果,也可以不填,不填就会返回False
-
这是一个非常实用的函数,可以让你基于各种条件进行灵活的计算
-
if嵌套
- 举例:如果我们要以月GMV大于10万且cpc费用少于5千的为达标,那么应该如何写计函数呢
- E80:=1F(C80>100000,1F(D80<5000,“达标””不达标”),“不达标”)
- 首先,=if(选GMV单元格大于100000
- 然后逗号,嵌套if判断cps,if(D80<5000,“达标”,“不达标”)
- 最后,如果GMV小于10W,直接不达标
vlookup
-
VLOOKUP(lookup_value,table_array,col_index_num, [range_lookup])
-
VLOOKUP(要查找的数据、要查找的位置和要返回的数据的区域、要返回的数据在区域中的列号、返回近似匹配或精确匹配 一 指示为 1/TRUE 或 O/FALSE)
- 第一个参数:匹配数据的依据
- C96:=VLOOKUP(B96,拌客源数据1-8月"!D:E,2,FALSE)
- 我们现在是按门店1D去匹配门店名称,那门店1D就是我们匹配数据的依据
- 第二个参数:我们要告诉Excel从哪里去找我们要匹配的数据
- 注意这里要选定一个区域,并且这个区域的第一列一定要是我们第一个参数所在的列
- 此处我们只要选定D列和E列即可
- 第三个参数:我们需要匹配的数据到底在选定区域的第几列
- 我们只选了两列,门店名称这个要匹配的数据所在的是第二列,我们填2就好了
-
第四个参数:匹配模式
- 如果填1或选TRUE就是模糊匹配,但我们一般不会直接用这个模糊匹配
- 此处,我们只要填0或选FALSE进行精确匹配即可
注意:**查找的数据和查找的位置中的数据必须完全没有差异才能匹配,**比如数据类型相同、不能有多余的空格等
-
vlookup模糊查询
- 通配符
- *:代替不定数量的字符
- ?:(英文输入状态下)代替一个字符
- 通配符
-
如果想要正确地实现模糊匹配,只需要在作为匹配条件的数值后加上通配符即可
-
如果我们想要匹配以a开头的数值,那就加上&英文双引1号"*"
-
如果我们想要匹配以b开头且一共三个字符所对应的数值,那就加上&“??”,后面有几位字符符就加几个问号
需要注意的是,如果有多个数值满足匹配条件,vookuD只会返回匹配到的第一个数值,毕竟只有一个单元格,只能放下一个数值
vlookup函数如果使用拖拽的方式,其默认区域会下移,因此需要利用$符号固定其区域- 如D11,$D$11表示既锁定行又锁定列,而$列只锁定行
match
- 函数的功能是,在你选定的行或列里查找你给出的数值,然后返回数值在行列内的位置
- MATCH(lookup_value, lookup_array, [match_type])
- MATCH(查找项,查找区域,0)
- match的目的是让Excel找出你想要计算的数值的位置,假如查找区域为一列,则返回在这列中的第几行,若查找区域为行,则返回第几列
- 这样就不需要像vlookup一样手动输入数据在哪一列,match会自己去找
index
-
函数的功能是,返回选定区域中行号和列号交叉选中的数据
-
INDEX (array, row_num, column_num)
-
INDEX(区域,行号,列号)
-
index和match组合自由匹配获取数据
- 匹配文本数据
- 让index在源数据中根据match返回的位置找到对应的数据
- index(数据区域,match(行查找项,index数据区域的相对区域,0),match(列查找项,indexB数据区域的相对区域,0))
- match找到你想要的数据的位置,index负责返回这个位置的数值
-
匹配聚合运算数据
- 如果要进行聚合运算的话,只需要和sumifs 函数一起使用即可
- 因为index除了通过行列位置返回一个单元格的数值,还可以返整行和整列
- 比如,行位置为0,返回整列;列位置为0,返回整行;
- 这样就可以让match识别所需计算的列所在的位置
- 然后让index函数返回位置上的整列给sumifs函数用于计算即可
- 先用index写出计算所用到的列
- 准备好聚合运算所用到的列,接下来我们将这一列作为sumifs进行计算的列
- 最后,回车,我们的函数就可以实现基于列名和条件的自动求和啦,随便拖拽都可以计算
- 可以说,index和match掌握得好就不需要手写数值在哪一列,它们会自动匹配,可以少写非常多的vlookup和sumifs函数,少做非常多的数据透视表
总结
-
sum:求和
- SUM(number1,number2, .) SUM(列名)SUM(行名)
- SUM(单个或多个单元格)SUM(列的名称 如:A:A)SUM(行的名称 如:1:1)
-
sumif:单条件求和
- SUMIF (range, criteria, [sum_range])
- SUMIF(条件判断所在的区域,条件,[用来求和的数值区域])
-
sumifs:多条件求和
- SUMIFS(sum_range, [criteria_range1], [criteria1], [criteria_range2], [criteria2]…)
- SUMIFS(用来求和的数值区域,条件1判断所在的区域1,条件1,条件2判断所在的区域2,条件2…)
-
subtotal:根据筛选求和
- SUBTOTAL (function_num, ref1, [ref2],…)
- SUBTOTAL(指定函数,选择区域1,[选择区域21,.)
-
if:逻辑判断
- IF (logical_test, value_if_true, [value_if_false])
- IF(逻辑比较条件,结果成立时返回的值,「结果不成立时返回的值))
- [value_if_false]:该参数选填,没有该参数时,返回值False
-
vlookup:连接匹配数据
- VLOOKUP(lookup_value,table_array,col_index_num, [range_lookup])
- VLOOKUP (要查找的数据、要查找的位置和要返回的数据的区域、要返回的数据在区域中的列号、返回近似匹配或精确匹配 - 指示为 1/TRUE 或 O/FALSE)
-
match:查找数值在区域中的位置
- MATCH(lookup_value, lookup_array, [match_type])
- MATCH(查找项,查找区域,0)
-
index:根据区域的位置返回数值
-
match、index一起使用:自动根据列名查找数据
- index(数据区域,match(行查找项,index数据区域的相对区域,0),match(列查找项,indexB数据区域的相对区域,0))
-
sumifs、match、index一起使用:自动根据列名的数据和条件进行求和
- =sumifs(index(数据区域,match(行查找项;index数据区域的相对区域,0),match(列查找项,indexB
数据区域的相对区域,0)),条件1判断所在的区域1,条件1,条件2判断所在的区域2,条件2…)
- =sumifs(index(数据区域,match(行查找项;index数据区域的相对区域,0),match(列查找项,indexB
4.6 周报搭建
搭建细节见:【课程2.0】Excel基础操作|大厂周报制作|常用函数_哔哩哔哩_bilibili 2:16:00