如何在Excel中輕鬆按絕對值對數據進行排名?
在Excel中按絕對值對數據進行排名是統計分析、財務建模和數據比較任務中的常見需求。儘管Excel具有內置的排名函數,但它們並不直接支持絕對值排名。本綜合指南將引導您完成幾種優化的方法,以高效地完成此任務。

使用公式按絕對值對數據進行排名
當您需要根據數字的絕對值(忽略其為正或負)進行排名時,以下公式可能會對您有所幫助。
✅公式1:使用SUMPRODUCT函數(適用於所有Excel版本)
此方法使用SUMPRODUCT函數,該函數適用於所有版本的Excel。
請在想要顯示結果的空白單元格中輸入以下公式,然後向下拖動以填充剩餘單元格,並按降序顯示基於絕對值的所有排名,如下方截圖所示。
=SUMPRODUCT(--(ABS(A2)<ABS($A$2:$A$13)))+1
- ABS(A2):返回當前單元格A2的絕對值。
- ABS($A$2:$A$13):返回範圍A2:A13中所有數字的絕對值。
- ABS(A2)<ABS($A$2:$A$13):比較當前單元格的絕對值是否小於範圍內的每個值,返回一系列TRUE/FALSE結果。
- --(...):將TRUE/FALSE值轉換為1和0(TRUE = 1,FALSE = 0)。
- SUMPRODUCT(...):將所有1加起來,計算有多少值的絕對值大於當前值。
- +1:從1開始排名,表示最高絕對值獲得排名1。
- 要根據絕對值按升序排列值,您可以使用以下公式:
=SUMPRODUCT(--(ABS(A2)>ABS($A$2:$A$13)))+1
- 在舊版Excel中,您需要按Ctrl + Shift + Enter以將公式作為數組公式輸入。在Excel 365或Excel 2021中,僅需按Enter即可。
✅公式2:使用XMATCH和SORT函數(適用於Excel 365和Excel 2021+)
如果您使用的是Excel 365或Excel 2021,則可以利用強大的動態數組函數(如SORT和XMATCH)按數字的絕對值進行排名。
請在想要顯示結果的空白單元格中輸入以下公式,然後向下拖動以填充剩餘單元格,並按降序顯示基於絕對值的所有排名,如下方截圖所示。
=XMATCH(ABS(A2), SORT(ABS($A$2:$A$13),,-1))
- ABS(A2):返回當前單元格A2的絕對值。
- ABS($A$2:$A$13):將範圍A2:A13中的所有值轉換為其絕對值。
- SORT(...,, -1):將這些絕對值按降序排列(-1表示降序)。
- XMATCH(...):在排序列表中找到當前值絕對值的位置——該位置代表其排名。
要根據絕對值按升序排列值,您可以使用以下公式:
=XMATCH(ABS(A2), SORT(ABS($A$2:$A$13),,1))
使用Kutools for Excel按絕對值對數據進行排序
在Excel中按絕對值對數據進行排序並未通過標準排序工具直接支持。儘管公式可以完成此任務,但需要額外的列且容易出錯。使用Kutools for Excel,您可以輕鬆地在幾次點擊內基於絕對值對數據進行排序——無需公式,毫無麻煩。
安裝Kutools for Excel後,請執行以下操作:
- 選擇包含要排序值的列。然後,點擊 Kutools Plus > 排序 > 高級排序 在Excel功能區中。
<
- 在 高級排序 對話框中,配置排序設置:
- 從列部分選擇要排序的列標題;
- 從“排序依據”下拉菜單中,選擇“絕對值”;
- 在“順序”部分,選擇“小到大”。如果需要降序,也可以選擇“大到小”。
- 一切設置好後,點擊 確定 按鈕。
現在,列表中的值已根據其絕對值進行排序,同時保留了其原始符號不變。這使您可以輕鬆識別按大小的最小或最大值,無論它們是正數還是負數。
- 我的數據包含標題:如果您的數據包括標題行,請確保勾選此框;
- 區分大小寫:僅適用於文本;如果是數字排序,可以保持未選中狀態。
- 空白儲存格在前面:如果您希望空白單元格出現在排序列表的頂部,請選中此選項。
🔚 結論
在Excel中按絕對值對數據進行排名或排序可以提供更深入的洞察,特別是在分析值的大小而不考慮其符號時。本指南介紹了多種方法來滿足不同用戶的需求:
- 使用SUMPRODUCT函數實現適用於所有Excel版本的可靠排名公式。
- 在Excel 365和2021+中利用XMATCH和SORT實現動態、現代化的公式。
- 對於最快且最方便的解決方案,Kutools for Excel提供了一鍵式方法,可按絕對值對數據進行排序而無需編寫任何公式。
選擇最適合您Excel版本和工作流程的方法——無論您偏好基於公式的控制還是像Kutools這樣的自動化工具的效率。如果您有興趣探索更多Excel技巧,我們的網站提供了數千個教程,幫助您掌握Excel。
最佳 Office 辦公效率工具
🤖 | Kutools AI Aide:徹底革新數據分析,基於智能執行|生成程式碼|創建自訂公式|分析數據並生成圖表|調用 Kutools Functions… |
熱門功能:查找、選取項目的背景色或標記重複值 | 刪除空行 | 合併列或單元格且不丟失資料 | 四捨五入... | |
高級 LOOKUP:多條件查找|多值查找|多表查找|模糊查找... | |
高級下拉列表:快速創建下拉列表 | 關聯下拉列表 | 多選下拉列表 ... | |
列管理器: 添加指定數量的列 | 移動列 | 切換隱藏列的可見狀態 | 區域與列比較 ... | |
精選功能:網格聚焦 | 設計檢視 | 增強編輯欄 | 工作簿及工作表管理器 | 資源庫 (自動文本) | 日期提取器 | 合併資料 | 加密/解密儲存格 | 按清單發送電子郵件 | 超級篩選 | 特殊篩選(篩選粗體/傾斜/刪除線 ...)... | |
前15 大工具集:12 款文本工具(添加文本,刪除特定字符,...)|50+ 種圖表 類型(甘特圖,...)|40+ 實用 公式(基於生日計算年齡,...)|19 款插入工具(插入QR码,按路徑插入圖片,...)|12 款轉換工具(金額轉大寫,匯率轉換,...)|7 款合併和分割工具(高級合併行,分割儲存格,...)| ...以及更多 |
利用 Kutools for Excel 大幅提升你的 Excel 技能,感受前所未有的高效體驗。 Kutools for Excel 提供超過300 項高級功能,助你提升效率並保存時間。 點此查看你最需要的功能...
Office Tab 為 Office 帶來標籤式介面,讓你的工作更加輕鬆
- 啟用 Word、Excel、PowerPoint 的標籤式編輯和閱讀功能
- 在同一個視窗的標籤中打開和創建多個文件,而不是在新窗口中分開開啟。
- 可提升你50% 的工作效率,每天為你大量減少滑鼠點擊次數!