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函數用途多多,希望大家挖掘並分享!