我们都知道当数据过多的时候,我们制作Excel图表就会显得非常的复杂,图表上面的内容就会特别多。Excel老玩家就会想到用切片器制作动态可变化的图表来显示。今天我们就来学习一下一个比......
2023-01-08
这里分享一个对INDEX函数+MATCH函数嵌套的小总结~~
首先呢,INDEX函数+MATCH函数嵌套的最基本格式如下:
index(序列1,match(值,序列2,0))
其含义为:
根据“值”在序列2中的位置,得出序列1中相同位置的值(模糊查找时,最后参数也可能是1、-1)。
这个看似和vlookup函数很像,但它不需要源数据的严格对齐,也不需要查找值必须在首列,更为灵活一些~
实例如下:
这样的反向查找,其实用VLOOKUP、LOOKUP、INDEX+MATCH都是可以做到的,3个公式分别如下:
VLOOKUP法:
=VLOOKUP(E3,IF({1,0},$C$2:$C$9,$B$2:$B$9),2,FALSE)
LOOKUP法:
=LOOKUP(1,0/($C$2:$C$9=E3),$B$2:$B$9)
INDEX+MATCH法:
=INDEX($B$2:$B$9,MATCH(E3,$C$2:$C$9,0))
这个基本实例中,序列1就是B2:B9,序列2是C2:C9,查找值是E3。以上,可见3者异同。
除了以上的基本情景,INDEX+MATCH嵌套也可能会用在其他的怪异需求中。
比如下面的例子:
要将B列内容各自重复C列指定的次数,形成一个新的序列。
首先,我们想到用INDEX来提取B列内容:
=INDEX($B$14:$B$17,X)
只是,这个X应该是什么呢?
它得是一个在下拉的时候,前6个取值是1,然后2个2,然后5个3,然后3个4,这样的序列。
我们自然想到,先将频次序列{6,2,5,3}变成累计序列{6,8,13,16},然后从0开始对它进行模糊查找,如下:
=MATCH(ROW(A1)-1,{6,8,13,16},1)
(下拉)
这时,得到了这样一个序列:
{#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,1,1,2,2,2,2,2,3,3,3}
这个序列,离我们想要的序列还有两点不同:1都变成了错误值,后面的取值也都少了1;据此可以在后面+1,前面增加IFERROR:
=IFERROR(MATCH(ROW(A1)-1,{6,8,13,16},1)+1,1)
(下拉)
此时,成功获得了想要的序列X,那么将它替换回原公式,即:
=INDEX($B$14:$B$17,IFERROR(MATCH(ROW(A1)-1,{6,8,13,16},1)+1,1) )
(下拉)
如此,目的实现。
最后,还有一个问题,即公式中的累计序列如何计算得到?
这可以用offset选定区域并求和获得:
=SUBTOTAL(9,OFFSET($C$14,,,ROW($1:$4),1))
(此为数组公式)
即将C14分别向下1、2、3、4格的区域求和。
用以上公式替代掉{6,8,13,16},则最终的公式为:
=INDEX($B$14:$B$17,IFERROR(MATCH(ROW(A1)-1,SUBTOTAL(9,OFFSET($C$14,,,ROW($1:$4),1)),1)+1,1))
以上是数组公式,需三键结束,下拉。
相关文章
我们都知道当数据过多的时候,我们制作Excel图表就会显得非常的复杂,图表上面的内容就会特别多。Excel老玩家就会想到用切片器制作动态可变化的图表来显示。今天我们就来学习一下一个比......
2023-01-08
在工作中,可能许多朋友都会碰到一个情况,那就是工作簿和工作表数据的合并操作。如何将上百个工作簿快速合并到一个表格中,许多朋友可能会觉得不可思议。今天我们就来教大家学习一......
2023-01-08
今天在这里为你分享5个Excel文本函数,这些拆分和组合函数,你一定会用上的。①LEFT函数公式:=LEFT(A2,1)在Excel表格中,需要想要拆分汉字,想从哪里开始就从那哪里开始。首先选定单元格......
2023-01-08
相信大家也和我一样,才开始看到Excel可以当做翻译软件的时候会很好奇,这究竟是怎样做到的?其实,这个方法并不是很难,它是由一个函数公式而制作出来的,好了,首先我们一起来看看成......
2023-01-08
函数可以说是所用快捷方法中最为简单的一种方法,为什么很多人认为函数用起来很难了?主要是因为它拥有很长的函数公式,记不住。其实不管是学Excel函数,还是学习其他的一些快捷方法......
2023-01-08