Excel相关操作

4. Excel

4.1 周报业务逻辑讲解
image-20230917145350122
  • 在这种周报,可以根据平台和日期筛选所有数据,联动的展示目标完成的进度
  • 转化率和变化趋势
    • 进店转化率=进店人数/曝光人数
    • 下单转化率=下单人数/进店人数
  • 结果指标和过程指标
    • 结果指标:以“产出”为导向,代表业务的最终结果
    • 过程指标:以“过程”为导向,代表业务的实际过程
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】

    image-20230917160617246

  • 注意PV和UV的重要概念区别
    image-20230915143942299

4.4 数据透视表+图
  • 新建窗口,可以方便对源数据进行引用

    image-20230917163733479

    通过视图的新建窗口,可以新建出源数据的另一份窗口,方便我们进行编写函数时的区域选择,而不需要来回切换工作表

  • 数据透视表+切片器

    • 插入数据透视表
    • 选中源数据中任意一个单元格,点击插入,数据透视表,确定,新建一张新的数据透视表
    image-20230917152148539 image-20230917152217108
    • 此时会新起一个sheet工作表,里面包含了数据透视表
  • 拖拽字段进行计算和重命名

    • 将文本型字段拖到行、数值型字段拖到值,透视表就会自动进行运算

    • 双击字段段名称,可以重命名,并且选择计算的类型,Excel默认的计算方式是求和

      在右侧会出现透视表的字段,我们将字段拖到行、值等位置

      image-20230917152541643
  • 创建新的字段

    • 选中数据透视表,点击数据透视表分析选项卡,进入功能区,在计算部分找到宇段、项目和集,点击插入计算字段

    • 计算两个字段单均实收和cpc单次点击费用

      • 单均实收二商家实收/有效订单
      • cpc单次点击费用=CpC总费用/cpc访问量
      image-20230917154103932
  • 插入切片器

    • 在数据透视表分析功能区,在筛选部分找到插入切片器,选择字段作为切片器

    • 点击切片器的选项,数据就会自动地根据切片器的选项进行计算透视表的内容

    image-20230917154331575
    • 除了切片器筛选,还可以直接将字段拖拽到筛选

      image-20230917154514234
    • 注意:透视表本身的筛选只能在透视表内使用,而切片器可以复制到任意工作表使用,以实现对透视表的筛选

  • 数据透视图

    • 点击功能区的工具-数据透视图就可以直接插入图表,默认插入柱状图

      image-20230917154647493

    • 右键绘图区,可以【更改图表类型】

    • 选择【组合图】,可以直观地展现两个数值

    image-20230917154749438
    • 一张透视表可以插入多张透视图
    • 都可以通过切片器来控制,这样就可以用切片器轻松实现图表的联动
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.)

注意编写函数锁定单元格过程:

加美元符号可以在拖动句柄的时候锁定

相对引用:随便拖拽会改变

混合引用:行或列某个区域会改变

绝对引用:行和列都不变

image-20230915153719504

在sumifs中加条件判断需要在 >=的类似符号中加“”,并且,并且不能连接的公式条件前需要加“&”符号

image-20230915170122005

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

image-20230915160104847 image-20230915160232803

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

image-20230915164810568
sum和subtotal的区别
  • SUBTOTAL(function_num,ref1,[ref21…)

  • SUBTOTAL(指定函数,选择区域1,[选择区域21.)

    sum对指定区域求和后无法随筛选改变, 随便对源数据进行筛选,subtotal都可 以跟随筛选进行改变,相当于一个自动根据筛选进行的求和

    image-20230917160626721

    此时如果将源数据的平台i筛选为美团,sum函数不会改变,但是subtotal会改变

    image-20230917160728323

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,直接不达标
    image-20230917161112398
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号"*"

    image-20230917162051302
  • 如果我们想要匹配以b开头且一共三个字符所对应的数值,那就加上&“??”,后面有几位字符符就加几个问号

    image-20230917162124344

    需要注意的是,如果有多个数值满足匹配条件,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

  • 0
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值