Sumproduct脑栲葱蛸函数是个变通应用很强的函数!能够解决多种难题。本文介绍此函数在数值查找中的应用及工作原理。
一、方法
首先此方法只限无重复数值的数据区域,如A1:C3。
现在需要查找“3”在A1:C3区域中的行和列。
(1)在E8中输入公式=SUMPRODUCT((A1:C3=E5)*ROW(A1:C3)),得到行数
(2)在E9中输入公式=SUMPRODUCT((A1:C3=E5)*COLUMN(A1:C3)),得到列数
二、原理
我们知道Sumproduct函数是一个数组运算函数,Sumproduct(arry1,arry2,....),我们来讲公式分解一下,以行公式为例=SUMPRODUCT((A1:C3=E5)*ROW(A1:C3))
(1)A1:C3=E5,A1:C3区域每个值与E5做运算,并返回结果,假如A7:C9(返回TRE/FALSE,*运算时变为1/0),只有C8对应的位置逻辑为TURE(1)
(2)ROW(A1:C3)返回区域内数值所在的行,结果假如A12:C14
(3)A7:C9与A12:C14区域乘(对应位置相乘然后相加),得2,即为查找值“3”所在的行
列的原理相同,如A17:C19区域与A7:C9数组相乘,得3
三、总结
当然,以上所述只是模拟过程,实际运算在内存中,不会显示出来。之所以介绍这种方法,是因为我们常规的V/Hlookup,index&match,index&small&macth等函数或者组合函数受限于特定行或者列,对于大海捞针的事,干着没Sumproduct函数这么得心应手!
此方法返回的行/列,然后结合index,indirect,offset等函数,以进行深层次的应用。当然此方法缺点是只适合无重复数值的区域,一旦有重复数值,行或者列就会做+运算,导致返回错误结果。
Sumproduct函数用途多多,希望大家挖掘并分享!
