錦子老師

張雯雯：「錦子老師，如下圖所示，A1~Q1為ㄧ數列,請各位協助如何求出最大值與次大值及位置，感恩!」

錦子老師：「這有三種狀況，但公式會大同小異，下面我們來看看。

以42為準，由左到右排列

這種方式是以42有二個，不論最大值及次大值都是42，而且要抓取同一值左邊欄位(為大)到右邊欄位(為小)的數值位置。

點取B3儲存格輸入公式：

=QUOTIENT(SUMPRODUCT(LARGE((A1:Q1)*100+100-COLUMN(A1:Q1),1)),100)

點取B3儲存格輸入公式：

=100-MOD(SUMPRODUCT(LARGE((A1:Q1)*100+100-COLUMN(A1:Q1),1)),100)

點取B3儲存格輸入公式：

=QUOTIENT(SUMPRODUCT(LARGE((A1:Q1)*100+100-COLUMN(A1:Q1),2)),100)

點取B3儲存格輸入公式：

=100-MOD(SUMPRODUCT(LARGE((A1:Q1)*100+100-COLUMN(A1:Q1),2)),100)

【公式解說】

(A1:Q1)*100+100-COLUMN(A1:Q1) 將A1:Q1儲存格中的值乘上100(是為了方便給每個值一個獨立的代表好分辯大小)加上100滅A1:Q1儲存格的欄號(由為了統計同一值時讓左邊欄位的為最大右邊欄位小)，設計出各個值所在位置代表數值，方便統計。

LARGE((A1:Q1)*100+100-COLUMN(A1:Q1),1) 傳回(A1:Q1)*100+100-COLUMN(A1:Q1)算出的最大值。

QUOTIENT(SUMPRODUCT(LARGE((A1:Q1)*100+100-COLUMN(A1:Q1),1)),100) 傳回LARGE((A1:Q1)*100+100-COLUMN(A1:Q1),1)計算的結果除以100的整數部份。

MOD(SUMPRODUCT(LARGE((A1:Q1)*100+100-COLUMN(A1:Q1),1)),100) 傳回LARGE((A1:Q1)*100+100-COLUMN(A1:Q1),1)計算的結果除以100的餘數部份。

以42為準，由右到左排列

這種方式是以42有二個，不論最大值及次大值都是42，而且要抓取同一值右邊欄位(為大)到左邊欄位(為小)的數值位置。

點取B3儲存格輸入公式：

=QUOTIENT(SUMPRODUCT(LARGE((A1:Q1)*100+COLUMN(A1:Q1),1)),100)

點取B3儲存格輸入公式：

=MOD(SUMPRODUCT(LARGE((A1:Q1)*100+COLUMN(A1:Q1),1)),100)

點取B3儲存格輸入公式：

=QUOTIENT(SUMPRODUCT(LARGE((A1:Q1)*100+COLUMN(A1:Q1),2)),100)

點取B3儲存格輸入公式：

=MOD(SUMPRODUCT(LARGE((A1:Q1)*100+COLUMN(A1:Q1),2)),100)

以42為最大值，38為次大值

這種方式是以42有二個，皆為最大值，而次大值就變為38。

點取B3儲存格輸入公式：

=QUOTIENT(SUMPRODUCT(LARGE((A1:Q1)*100+COLUMN(A1:Q1),1)),100)

點取B3儲存格輸入公式：

=MOD(SUMPRODUCT(LARGE((A1:Q1)*100+COLUMN(A1:Q1),1)),100)

點取B3儲存格輸入公式：

=LARGE(A1:Q1,COUNTIF(A1:Q1,B3)+1)

點取B3儲存格輸入公式：

=MATCH(D3,A1:R1,0)

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

部落格相關範例