Excel相关操作

4. Excel

4.1 周报业务逻辑讲解

• 在这种周报，可以根据平台和日期筛选所有数据，联动的展示目标完成的进度
• 转化率和变化趋势
  • 进店转化率=进店人数/曝光人数
  • 下单转化率=下单人数/进店人数
• 结果指标和过程指标
  • 结果指标：以“产出”为导向，代表业务的最终结果
  • 过程指标：以“过程”为导向，代表业务的实际过程

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中加条件判断需要在 >=的类似符号中加“”,并且，并且不能连接的公式条件前需要加“&”符号

​

环比是与上一个相邻的时间单位相比，同比是与上一年或者上一个月的相同的时间单位进行比较

永远不要使用excel的日期格式来存储日期，导入数据库会出问题

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…)

4.6 周报搭建

搭建细节见：【课程2.0】Excel基础操作|大厂周报制作|常用函数_哔哩哔哩_bilibili 2:16:00