Excel相关操作

最新推荐文章于 2024-07-15 18:51:11 发布

几窗花鸢

最新推荐文章于 2024-07-15 18:51:11 发布

阅读量434

点赞数

分类专栏：数据分析文章标签：数据分析 excel

本文链接：https://blog.csdn.net/weixin_44911248/article/details/132946783

版权

数据分析专栏收录该内容

7 篇文章 0 订阅

订阅专栏

文章目录

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