錦子老師

澄澄：「請問錦子老師，想請問有一筆資料例如:20 95 65 77 55 32 99，我想找出最大值99，然後把99的前五格32 55 77 65 95連同99加總該怎麼做呢?

資料是是一列一列的，然後如果有重複值我想找格數較後面的該如何處理呢?

例如:20 95 65 77 55 32 99 99，想取最後面99與前五格加總，也就是65 77 55 32 99 99」

錦子老師：「這一般都是建立輔助欄來幫忙運算的。」

點取M1儲存格輸入公式：

=Sum(Offset(A1,0,If(Sumproduct(Large((A1:J1=Max(A1:J1))*Column((A1:J1)),1))<6,0,Sumproduct(Large((A1:J1=Max(A1:J1))*Column((A1:J1)),1))-6),1,If(Sumproduct(Large((A1:J1=Max(A1: J1))*Column((A1:J1)),1))<6,Sumproduct(Large((A1:J1=Max(A1:J1))*Column((A1:J1)),1)),6)))

將公式複製到M2:M12。

【公式說明】

首先將A1:J1儲存格範圍的最大值擷取出來(Max(A1:J1))，然後再比對最大值有幾個並抓取位於最右邊的那個欄位(Large((A1:J1=Max(A1:J1))*Column((A1:J1)),1))，如果該欄位編號小於6，則填入0，否則填入該欄位減6(If(Sumproduct(Large((A1:J1=Max(A1:J1))*Column((A1:J1)),1))<6,0, Sumproduct(Large((A1:J1=Max(A1:J1))*Column((A1:J1)),1))-6))。

透過OFFSET函數來抓取要運算的範圍，從A1儲存格移動0列，移動上一段落算出的欄，再框選1列，若算出的欄位小於6(Sumproduct(Large((A1:J1=Max(A1: J1))*Column((A1:J1)),1))<6)，則框選到該欄位(Sumproduct(Large((A1:J1=Max(A1:J1))*Column((A1:J1)),1)))，否則框選6欄(If (Sumproduct(Large((A1:J1=Max(A1: J1))*Column((A1:J1)),1))<6,Sumproduct(Large((A1:J1=Max ( A1:J1))*Column((A1:J1)),1)),6))。

最後用SUM函數算出框選範圍中數值總和。

M1儲存格的公式也可以更改為：

=Sum(Indirect(Address(Row(),If((Match(2,1/($A1:$J1=Max($A1:$J1)))-5)>0,Match(2,1/($A1: $J1=Max($A1:$J1)))-5,1))):Indirect(Address(Row(),Match(2,1/($A1:$J1=Max($A1:$J1))))))

再按CTRL+SHIFT+ENTER鍵完成陣列公式輸入。

這是由網友海綿寶寶提供的。

以上就是今天主要學習的知識點，希望對大家有所幫助~~有什麼問題歡迎留言，我會儘量及時的給大家答覆~~

更多相關影片教學：請點我