Index+Match查询引用技巧解读,绝对的黄金搭档
查询引用,用的最多的应该是Vlookup或Lookup函数,其实,除了这两个函数之外,查询引用还有一组黄金搭档就是Index+Match。
一、查询引用黄金搭档Index+Match:Index函数。
功能:在特定的单元格区域中,返回行列交叉处的值或引用。
Index函数有两种用法
(一)数组形式。
语法结构:=Index(数组,行号,[列号]),当省略[列号]时默认为第一列。
目的:返回指定行、列交叉处的值。
方法:
在目标单元格中输入公式:=INDEX(B3:E9,I3,J3)、=INDEX(C3:D8,I4,J4)。
解读:
从上述示例的对比中可以得出,行、列交叉处的值是相对于第一个参数数据范围而言的,如=INDEX(B3:E9,1,1)的值为“键盘”,而=INDEX(C3:D8,1,1)的值为“16230”。
(二)引用形式。
语法结构:=Index(数组1,数组2……,行号,[列号],[区域值]),区域值指的是指定数据中的第X个数组,[列号]、[区域值]省略时默认为1。
目的:返回第2个区域中行列交叉处的值。
方法:
在目标单元格中输入公式:=INDEX((B3:E9,C3:D9),I3,J3,2)。
解读:
从公式中看出,数据范围有两个,分别为B3:E9,C3:D9,I3和J3是行和列,最后一个参数“2”为指定的数据范围,暨行、列是相对于C3:D9而言的,对B3:E9无效。
二、查询引用黄金搭档Index+Match:Match函数。
功能:提取指定值在指定范围中的相对位置。
语法结构:=Matct(查询值,数据范围,[匹配模式]),其中匹配模式分为-1(大于)、0(精准)、1(小于)三种。
目的:提取“商品”的相对行数。
方法:
在目标单元格中输入公式:=MATCH(H3,B3:B9,0)。
解读:
返回的结果是相对于指定的数据范围而言的,如果数据范围不同,则相同的值会返回不同的结果。
三、查询引用黄金搭档Index+Match:查询引用。
(一)单列查询。
目的:查询“商品”的“销售额”。
方法:
在目标单元格中输入公式:=INDEX(B3:E9,MATCH(I3,B3:B9,0),4)。
解读:
1、在数据范围B3:E9范围中,找出行为MATCH(I3,B3:B9,0),列为4交叉处的值。
2、=MATCH(I3,B3:B9,0)定位I3在B3:B9中的相对位置。
(二)多列查询。
目的:根据“商品”名称查询对应的“销量”等其他信息。
方法:
在目标单元格中输入:=INDEX($B$3:$F$9,MATCH($I$3,$B$3:$B$9,0),MATCH(J$2,$B$2:$F$2,0))。
解读:
1、在数据范围$B$3:$F$9中,返回行为MATCH($I$3,$B$3:$B$9,0),列为MATCH(J$2,$B$2:$F$2,0)交叉处的值。
2、因为数据要跨列引用,所以部分参数要绝对或混合引用,原则为不变的为“绝对”,变化的为“相对”,根据实际情况灵活对待。
结束语:
本文从Index函数和Match函数本身的功能出发,对其进行巧妙组合,实现查询引用的功能,其基本思路就是用Match函数确定查询值的位置,然后用Index函数进行提取。对于使用技巧,你Get到了吗?
INDEX函数的使用方法
昨天我们分享了MATCH函数的使用方法,在Excel中查找引用是高频函数,不论你从事什么工作都会经常用到。今天我们就来分享一下专门引用的函数,它就是INDEX。
INDEX表示在给定的单元格区域中,返回特定行列交叉处单元格的值或引用。它最多由4个参数,先来看简单一点的,3个参数,分别是:array, row_num, [column_num],分别表示数据区域,行数,列数。当我们只在一行或一列中引用时,只需要其中两个参数,而且会根据选中的区域,自动匹配到行或者列。举个例子,请看下图:
我们输入=INDEX(C2:C11,3),返回的就是在C2至C11的这一列中第三个单元格的内容。输入=INDEX(A2:E2,2),返回的是A2到E2这一行第二个单元格的内容。
当数据区域多行或多列的时候,就要用到三个参数了,例如:
我们输入=INDEX(A2:E27,8,3),表示在A2到E27这一区域中第8行第3列的单元格的内容,向下拖拽,就成了相对引用了,即区域不断下移,后两个参数保持不变。当然你可以将区域锁定,然后借助ROW和COLUMN函数来替代第二个和第三个参数,实现拖拽填充的效果。
对于四个参数,Excel是这么显示的:reference, row_num, [column_num], [area_num],分别表示数据区域,行号,列号,区域号,这里是用于多个不连续区域的引用,请看下面示意图:
我们输入=INDEX((A2:B27,D2:F27),6,2,1),表示在这两个不连续的区域中,引用第一个区域中的第6行第1列的单元格内容,而输入=INDEX((A2:B27,D2:F27),8,1,2)则表示在第二个区域中引用第8行第1列单元格的内容。注意这里的不连续区域不管有几个,都要用括号括起来,否则会出错。
好了,INDEX函数的基本用法就是这么多,你掌握了吗?
在Excel中判定等级,除了用If函数外,还有5种易学易懂的技巧
在Excel中,依据一定的标准去判定等级是常见的操作,除了传统的If函数外,还有5种方法,易学易懂易掌握。
一、Excel等级判定:If函数法。
功能:根据指定的条件返回指定的值或执行指定的操作。
语法结构:=If(判断条件,条件为真时的返回值或函数公式,条件为假时的返回值或函数公式)。
目的:判断销售额的“等级”情况。
方法:
在目标单元格中输入公式:=IF(F3>600,\”优秀\”,IF(F3>500,\”良好\”,IF(F3>300,\”及格\”,\”不及格\”)))。
解读:
1、If函数除了单独使用外,还可以嵌套应用,但缺点是嵌套层级较多时,容易出错。
2、如果要使用If函数判定等级,要按照一定的顺序去判定条件。
二、Excel等级判定:Ifs函数法。
功能:检查是否满足一个或多个条件并返回与第一个TRUE条件对应的值。
语法结构:=Ifs(判断条件1,返回值1,判断条件2,返回值2……)。
目的:判断销售额的“等级”情况。
方法:
在目标单元格中输入公式:=IFS(F3>600,\”优秀\”,F3>500,\”良好\”,F3>300,\”及格\”,F3<=300,\”不及格\”)。
解读:
Ifs函数避免了If函数的嵌套。相对于If函数来说,简单直观易理解。只需要保证判断条件和返回值一一对应即可。
三、Excel等级判定:Vlookup函数法。
功能:返回指定范围中和指定条件相对应的值。
语法结构:=Vlookup(查询值,查询范围,返回列数,[匹配模式])。其中0位精准模式,1位模糊模式。
目的:判断销售额的“等级”情况。
方法:
在目标单元格中输入公式:=VLOOKUP(F3,$J$3:$K$6,2,1)。
解读:
1、公式=VLOOKUP(F3,$J$3:$K$6,2,1)中F3为需要查询的值,$J$3:$K$6为查询范围,2为返回列数,即返回K列的值。
2、当Vlookup函数采用模式查询方式时,如果在数据范围中找不到对应的值,则会自动向下兼容查询,即匹配小于当前值的最大值,并返回对应的值。
四、Excel等级判定:Lookup函数法。
功能:返回指定数据范围中符合条件的值。
语法结构1:=Lookup(查询值,数据范围)。
语法结构2:=Lookup(查询值,查询值所在行或列,[返回值所在行或列])。
Lookup函数有两种语法结构,分别为向量形式和数组形式,语法结构1为数组形式、语法结构2为向量形式。
目的:判断销售额的“等级”情况。
方法:
在目标单元格中输入公式:=LOOKUP(F3,$J$3:$K$6)或=LOOKUP(F3,$J$3:$J$6,$K$3:$K$6)。
解读:
1、使用数组形式时,查询值所在的范围必须在数据区域的第一行或第一列,返回值所在的范围必须在数据区域的最后一行或最后一列。
2、使用向量形式时,如果查询值和返回值在同一个区域,则可以省略返回值范围。
五、Excel等级判定:Choose+Match组合函数法。
Choose函数功能:根据指定的值,筛选出对应的值或执行相应的操作。
语法结构:=Choose(索引值,返回值1或表达式1,[返回值2或表达式2]……[返回值N或表达式N])。
Match函数功能:返回指定值在指定范围中的相对位置。
语法结构:=Match(定位值,数据范围,匹配类型),其中匹配类型有三种类型,分别为1(小于)、0(精准)、-1(大于)。
目的:判断销售额的“等级”情况。
方法:
在目标单元格中输入公式:=CHOOSE(MATCH(F3,$J$3:$J$6,1),K$3,K$4,K$5,K$6)。
解读:
公式中首先用Match函数获取当前值在$J$3:$J$6中的相对位置,并作为Choose函数的第一个参数,然后根据参数值返回对应的等级。
六、Excel等级判定:Index+Match组合函数法。
Index函数功能:返回指定范围中行列交叉处的值。
语法结构:=Index(数据范围,行,[列])。当省略“列”时,默认的值为1。
目的:判断销售额的“等级”情况。
方法:
在目标单元格中输入公式:=INDEX(K$3:K$6,MATCH(F3,J$3:J$6,1))。
解读:
公式中利用Match函数获取当前值在J$3:J$6中的相对行数,并作为Index函数的第二个参数,最后返回指定的行和第一列交叉处的值。
结束语:
本文从实际出发,对等级判断的6种技巧做了详细的解读,对于使用技巧,需要在掌握函数或公式基础功能的基础上再融会贯通,如果在学习的过程中,有任何疑问,可以在留言区留言讨论哦!
本文作者及来源:Renderbus瑞云渲染农场https://www.renderbus.com
文章为作者独立观点不代本网立场,未经允许不得转载。