使用LARGE函數為指定範圍內的數值分類

2020-07-14 14:34:24

使用陣列公式可以對單列中的數值進行分類，並按照從高到低或從低到高的順序進行排列。要實現上述功能，需要使用LARGE函數巢狀INDIRECT函數及ROW函數。使用陣列為指定範圍內的數值分類時，如果遇到未包含數值的單元格，則會返回錯誤值“#NUM!”。

為了避免返回錯誤值，可以使用IF函數進行判斷，未包含數值的返回空單元格。為指定範圍中的數值進行分類的操作步驟如下所述。本例的原始資料如圖1所示。

圖1　原始資料

步驟1：選中B3:B15單元格區域，然後在編輯列中輸入“=IF()”，如圖2所示。

圖2　輸入公式

步驟2：在公式的括號中輸入“ISERR(LARGE(A3:A16,ROW(INDIRECT("1:"&ROWS(A3:A16))))),”，作為IF函數的第一個引數，如圖3所示。

圖3　輸入IF函數的第一個引數

技巧點撥：

在IF函數的第一個引數中，LARGE函數用於對指定單元格區域中的數值進行排序，然後使用ISERR函數去除因空白單元格而返回的錯誤值。

步驟3：繼續輸入“"",”作為IF函數的第二個引數，如圖4所示。

圖4　輸入IF函數的第二個引數

步驟4：最後輸入“LARGE(A3:A16,ROW(INDIRECT("1:"&ROWS(A3:A16))))”，作為IF函數的第三個引數，如圖5所示。

圖5　輸入IF函數的第三個引數

技巧點撥：

第二個參數列示如果指定的單元格區域中有未包含數值的單元格，返回空白單元格。在IF函數的第三個引數中，INDIRECT函數用於對指定的陣列進行計算，並根據IF函數判斷結果值使用LARGE函數返回分類後的數值。

步驟5：按“Ctrl+Shift+Enter”組合鍵，將公式轉換為陣列公式，並顯示分類結果，如圖6所示。

圖6　計算結果

技巧點撥：在上述範例中，如果確定陣列中未包含空白單元格，可以將公式簡化為“=LARGE(A3:A15,ROW(INDIRECT("1:"&ROWS(A3:A15))))”，如圖7所示。

圖7　未包含空白單元格的公式