接上篇,以线上外卖订单数据为例,讲解Excel中的vlookup、match与index函数。
vlookup函数
vlookup函数是查找函数,有近似匹配和精确匹配两种模式。
=VLOOKUP(要查找的数据,要查找的位置和要返回的数据的区域,要返回的数据在区域中的列号,返回近似匹配或精确匹配-指示为1/TRUE或0/FALSE)
例1.根据门店id查找门店名称。
模糊查询
模糊查询是无法精确查询下使用通配符进行查询的一种方法,一般会使用两种通配符:
*:代替不定数量的字符
?:(英文输入状态下)代替一个字符
vlookup函数基于聚合运算结果进行连接
需要引用数据透视表。
match与index函数
match
求某一个单元格在区域中行或列的位置。
=MATCH(查找项,查找区域,0)
例1.找出“蛙小辣火锅杯(龙阳广场店)”在平台门店名称这一列中处于第几行。
例2.找出“品牌名称”处于表头的哪一列。
index
在给定区域中,根据给出的行号和列号找对应单元格中的数据。行列号就需要用match函数进行查找。
=INDEX(区域,行号,列号)
index与match函数的嵌套
=index(数据区域,match(行查找项,index数据区域的相对区域,0),match(列查找项,index数据区域的相对区域,0))
例3.在表中有许多数据,如何只利用index和match函数返回表头数据?
=INDEX(A111:E126,MATCH(D111,D111:D126,0),MATCH(D111,A111:E111,0))
往右拖拽即可获得对应的表头数据。
例4.填充上图中的空白数据。
混合引用:第一个match锁定查找项的列(也就是index行锁字母),第二个match锁定查找项的行(也就是index列锁数字),其他都是绝对引用。
=INDEX('拌客源数据1-8月'!$A:$X,MATCH($B112,'拌客源数据1-8月'!$I:$I,0),MATCH(D$111,'拌客源数据1-8月'!$1:$1,0))
求后面的空白数据
=SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(G$111,'拌客源数据1-8月'!$1:$1,0)),'拌客源数据1-8月'!$I:$I,$B112)
代码解释:
第一部分:index的目的是找出用来求和的数值区域,当index的行为0时,返回的列可作为数据中的一整列。反之,当index的列为0时,返回的行可作为数据中的一整行。因此index行位置填0,返回的整列可视为sumifs的求和区域,match求出列号。
第二部分:条件判断所在的区域,即平台门店名称所在的列。
第三部分:条件,即“蛙小辣·美蛙火锅杯(宝山店)”所在的单元格。
PS:本文来源于b站戴师兄的数据分析自学课程,这篇博客是我自己的学习笔记,供大家一起参考学习哦~所用的资料在https://pan.baidu.com/s/1-jOwaXWArtv8h21B0-uWGg#list/path=%2F&parentPath=%2F这里,是戴师兄免费分享的学习资料,提取码是wsra。