如何在 Excel 中創建動態的前 10 名或前 N 名列表？

Author Xiaoyang Last modified 2025-08-23

在許多項目和商業流程中，通常需要根據個人、組織、產品或其他實體的表現或數值進行排名。一個「頂尖列表」可以用來突出顯示表現最高的條目，例如成績優異的學生、銷售額最高的銷售員或收入最多的部門。例如，你可能有一張學生成績表，想要動態提取前 10 名得分者以進行獎勵、分析或監控教育成果，如下方截圖所示。在 Excel 中創建動態的前 10 名或前 N 名列表可以讓你在數據變更時自動看到更新的結果，節省時間並減少手動排名帶來的錯誤。本指南介紹了幾種實用的解決方案——包括公式、數據透視表和 VBA 宏——幫助你構建動態的前 10 名或前 N 名列表，以有效滿足各種數據分析需求。

create dynamic top10 or n list

在 Excel 中創建動態的前 10 名列表

使用公式創建動態的前 10 名列表
使用帶條件的公式創建動態的前 10 名列表

在 Office 365 中創建動態的前 10 名列表

使用公式創建動態的前 10 名列表
使用帶條件的公式創建動態的前 10 名列表

使用數據透視表創建動態的前 10 名列表

數據透視表：自動交互式顯示前 N 結果

使用 VBA 創建動態的前 10 名列表

VBA 宏：自動生成並刷新前 N 列表

在 Excel 中創建動態的前 10 名列表

在 Excel 2019 及更早版本中，創建動態的前 10 名（或前 N 名）列表涉及結合公式同時提取最高值及其相關的名字或 ID。這種解決方案廣泛應用於希望列表隨著數據變化自動更新的情況。以下操作概述了如何使用經典 Excel 公式實現這一目標。這些公式提供了靈活性，且不需要特殊的 Excel 插件，但與某些現代動態陣列函數相比，設置過程稍顯複雜。

使用公式創建動態的前 10 名列表

1. 首先，你需要從你的值範圍中提取前 10 個值。在空白單元格（例如 G2）中輸入以下公式。輸入公式後，拖動填充柄向下生成你的動態前 10 值列表。請參閱截圖：

=LARGE($B$2:$B$20,ROWS(B$2:B2))

注意：這裡，B2:B20 是分數或值的範圍，而 B2 是該列中的第一個單元格。根據你的數據大小和位置調整這些單元格引用。

apply a formula to extract the top10 values

2. 接下來，要顯示與這些最高值相關的名字（或 ID），請在 F2 單元格中輸入以下公式。這是一個陣列公式，因此在輸入後，按 Ctrl + Shift + Enter 確認。此公式會找到與你剛剛提取的最高值對應的名字：

=INDEX($A$2:$A$20,SMALL(IF($B$2:$B$20=G2,ROW($B$2:$B$20)-ROW($B$1)),COUNTIF($G$2:G2,G2)))

參數解釋:
- A2:A20 是提取名字的範圍；
- B2:B20 是分數或值的範圍；
- G2 是上述公式中的最高值；
- B1 是值列表的標題，用於在行計算中進行偏移。
此公式將最高值與其名字動態連結。如果你的值範圍包含重複值，COUNTIF 會確保每個匹配的名字只出現一次及其分數。

use a formula to get relative item

3. 提取第一個結果後，選擇 F2 單元格中的公式並向下拖動填充柄以將公式複製到所需行數。這將擴展你的結果以動態顯示所有頂尖條目的名字，匹配這些分數。請參閱截圖：

drag and fill the formula to other cells

使用 Kutools AI 解鎖 Excel 的魔法

智能執行：執行單元格操作、分析數據並創建圖表——所有這些都由簡單的指令驅動。
自訂公式：生成量身定制的公式，簡化您的工作流程。
VBA 編碼：輕鬆編寫和實現 VBA 代碼。
公式解釋：輕鬆理解複雜的公式。
文本翻譯：打破電子表格中的語言障礙。

通過人工智能工具增強您的 Excel 能力。立即下載，體驗前所未有的效率！

使用帶條件的公式創建動態的前 10 名列表

在某些分析任務中，你可能需要一個僅顯示符合特定標準條目的頂尖列表——例如限制頂尖結果為特定組、團隊或類別。例如，你可能希望僅從包含多個班級成績的總數據表中找出「一班」的前 10 名成績。以下是針對此情境使用公式的步驟：

create a dynamic top10 list with criteria

1. 首先，從數據集中提取符合指定條件（如「一班」）的前 10 個值。在目標單元格（例如 J2）中輸入以下公式：

=LARGE(IF($B$2:$B$25=$F$2,$C$2:$C$25),ROW(I2)-ROW(I$1))

2. 輸入公式後，按 Ctrl + Shift + Enter 確認為陣列公式，然後向下拖動填充柄以填充其他單元格。該公式將返回符合你所選條件（如「一班」的所有分數）的最高 10 個值。

apply a formula to extract the top10 values based on criteria

3. 要列出這些頂尖值在條件下的相應名字，將以下公式複製並粘貼到 I2 單元格中，並按 Ctrl + Shift + Enter 確認為陣列公式。然後，根據需要向下填充以生成完整的名單。

=INDEX($A$2:$A$25,SMALL(IF(($C$2:$C$25=J2)*($B$2:$B$25=$F$2),ROW($C$2:$C$25)-ROW($C$1)),COUNTIF(J2:$J$2,J2)))

use a formula to create a dynamic top10 list in Office365

務必根據實際數據設置調整公式中的範圍。請注意，使用大型數據範圍和陣列公式可能會降低性能。如果頂尖 10 名中出現重複值，該公式將適當處理重複分數並在分數相同的情況下給出多名學生的名字。

在 Office 365 中創建動態的前 10 名列表

雖然早期版本的 Excel 需要結合多個函數與陣列公式，Office 365（和 Excel 2021）引入了動態陣列函數，如 INDEX、SORT、SEQUENCE 和 FILTER，極大地簡化了工作流程。這些函數使得構建動態的前 10 名列表更加容易，減少錯誤，特別適用於頻繁增長或變化的表格。如果你處於數據不斷更新的環境中，這些函數可以簡化你的分析並加快業務決策速度。

使用公式創建動態的前 10 名列表

要在 Office 365 中提取並顯示動態的前 10 名列表，請在所需的輸出單元格中輸入以下公式。你只需根據需要調整範圍和數字，公式會在數據更改時自動顯示最新的前 10 名結果。

=INDEX(SORT(A2:B20,2,-1),SEQUENCE(10),{1,2})

只需按下 Enter 鍵。完整的前 10 名列表立即出現並將保持動態，因此添加的數據或修改的分數會立即反映在排名中。

use a formula to create a dynamic top10 list in Office365

提示:

排序函數：

=SORT(array, [sort_index], [sort_order], [by_col])

array：要排序的範圍。
[sort_index]：要排序的列號。對於典型的成績表，這通常是第二列。
[sort_order]：使用 1 表示升序或 -1 表示降序。要獲得最高分，請使用 -1。
[by_col]：是否按列（TRUE）還是按行（FALSE 或省略）排序。

例如：SORT(A2:B20,2,-1) 按第二列降序排列 A2:B20。

SEQUENCE 函數：

=SEQUENCE(rows, [columns], [start], [step])

rows：返回的行數，例如前 10 名列表的 10。
[columns]：（可選）返回的列數。
[start]：（可選）起始值。
[step]：（可選）增量值。

SEQUENCE(10) 生成數字 1 至 10，讓 INDEX 選擇前 10 個排序結果。

結合這些，=INDEX(SORT(A2:B20,2,-1),SEQUENCE(10),{1,2}) 為你提供了一個動態的兩列前 10 名列表。

使用帶條件的公式創建動態的前 10 名列表

如果你需要提取特定群組（例如「一班」）的前 10 名，這些高級 Office 365 函數可以在包含符合條件的行時創建前 N 名列表。將以下公式放在所需位置，並根據需要調整範圍和條件單元格：

=INDEX(SORT(FILTER(A2:C25,B2:B25=F2),3,-1),SEQUENCE(10),{1,3})

輸入公式後，只需按下 Enter 鍵。篩選並排名的前 10 名列表即刻顯示，每次修改數據或條件時都會更新。

another formula to create a dynamic top10 list with criteria in office365

提示:

FILTER 函數：

=FILTER(array, include, [if_empty])

array：要篩選的單元格範圍。
include：包含條件（例如等於某個班級）。
[if_empty]：（可選）當沒有結果符合條件時顯示的內容。

=FILTER(A2:C25,B2:B25=F2) 返回 B 列匹配 F2 值的那些行。

使用數據透視表創建動態的前 10 名列表

數據透視表：自動交互式顯示前 N 結果

另一種創建動態前 N 名列表的方法是使用 Excel 的數據透視表功能。這種方法特別適合大數據集、交互式分析（如快速更改頂尖項目的數量或應用篩選器），或者你想避免複雜公式時。數據透視表易於使用並且在數據變更時自動更新，非常適合與他人共享的儀表板或報告。

使用數據透視表創建動態前 N 名列表的步驟：

點擊數據表內任意位置，然後轉到插入 > 數據透視表。
在數據透視表對話框中，選擇放置數據透視表的位置，然後點擊確定。
將你的「名字」（或類似標識符）字段拖到行區域。
將你的「分數」（或值列）拖到值區域。它通常默認為「求和」或「計數」——對於頂尖列表，你通常希望「求和」或「最大值」。如有必要，通過右鍵單擊並選擇匯總值方式更改值字段計算。
通過右鍵單擊值並選擇排序 > 從大到小排序，對「分數」列進行降序排序。
要限制為前 N 結果，點擊行標籤上的下拉箭頭，選擇值篩選器 > 前 10...，設置數字（例如前 10）和篩選依據的字段，然後點擊確定。

你的數據透視表現在顯示動態的前 10 名（或你指定的任何 N）。要更改前 N，只需重新訪問篩選設置。如果數據變更，刷新數據透視表即可立即更新排名。

這種方法的優勢包括快速設置、簡單排序和交互調整。然而，除非包含在行或值區域，否則數據透視表無法自動添加來自其他列的相應行。高級用戶可以進一步自定義報告，通過分組、創建切片器或將前 N 篩選器納入儀表板。

使用 VBA 創建動態的前 10 名列表

VBA 宏：自動生成並刷新前 N 列表

使用 VBA 宏非常適合處理大量或頻繁更新的數據，其中需要自動提取和刷新動態的前 N 名列表。宏是減少重複任務並確保一致性的理想工具。你可以創建一個例程，在每次運行時對數據進行排序並僅將前 N 行複製到特定位置。

使用 VBA 宏創建動態的前 N 名列表的步驟：

點擊開發工具 > Visual Basic 打開 VBA 編輯器。（如果看不到開發工具選項卡，轉到文件 > 選項 > 自定義功能區並啟用「開發工具」。）
在 VBA 窗口中，點擊插入 > 模塊以添加新模塊。
在模塊中粘貼以下 VBA 代碼：

Sub ExtractTopNList()
'Updated by Extendoffice 2025/7/24
    Dim DataRange As Range
    Dim OutputRange As Range
    Dim N As Integer
    Dim ws As Worksheet, tempWS As Worksheet
    Dim xTitleId As String
    Dim LastCol As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = ActiveSheet
    Set DataRange = Application.InputBox("Select the full data range to analyze (including headers)", xTitleId, ws.UsedRange.Address, Type:=8)
    Set OutputRange = Application.InputBox("Select the top-left cell of the output area", xTitleId, "", Type:=8)
    N = Application.InputBox("How many top items to extract? (Enter a positive integer)", xTitleId, 10, Type:=1)
    
    If DataRange Is Nothing Or OutputRange Is Nothing Or N < 1 Then Exit Sub
    
    ' Create a temporary worksheet to avoid sorting original data
    Set tempWS = Worksheets.Add(After:=Worksheets(Worksheets.Count))
    DataRange.Copy tempWS.Range("A1")
    
    ' Determine last column for sorting key
    LastCol = DataRange.Columns.Count
    
    ' Sort in temporary sheet
    tempWS.UsedRange.Sort Key1:=tempWS.Cells(1, LastCol), Order1:=xlDescending, Header:=xlYes
    
    ' Copy headers and top N rows to output
    tempWS.Rows(1).Copy Destination:=OutputRange
    tempWS.Range("A2").Resize(N, LastCol).Copy Destination:=OutputRange.Offset(1, 0)
    
    ' Optional: Delete temporary sheet
    Application.DisplayAlerts = False
    tempWS.Delete
    Application.DisplayAlerts = True
    
    Application.CutCopyMode = False
End Sub

4. 要執行宏，確保數據正確地佈局在帶有標題的表格中。按下 F5 或點擊 VBA 編輯器中的按鈕。你將被提示：

選擇你的數據範圍（包括標題以便正確排序）。
選擇輸出單元格以粘貼結果。
輸入數字 N（例如，10 表示前 10 名）。

宏將把前 N 個條目（包括標題）複製到你指定的位置。

首次測試時建議在備份或副本中使用。如果發生錯誤（例如選擇了錯誤的範圍），重新運行並確保範圍和數據佈局正確。

此解決方案非常適合自動化重複的報告任務、創建儀表板或快速更新前 N 報告而無需手動公式或排序。你可以進一步定制 VBA 腳本以實現更複雜的排名邏輯，例如按特定列排序或將結果導出到另一個工作簿。

故障排除：如果宏未按預期工作，檢查數據表是否包含正確的標題，修正數據類型以避免排序問題，並確保在每個提示中準確選擇單元格引用。始終在運行宏之前保存你的工作，以防止意外的數據更改。

總之，Excel 支持各種方法生成和維護動態的前 N 名列表——從傳統公式到強大的 Office 365 函數、交互分析的數據透視表和高級自動化的 VBA 宏。選擇最適合你的工作流程和數據規模的方法。使用公式對於大多數手動分析有效，Office 365 函數提供最大的簡便性和功能，數據透視表非常適合快速靈活的摘要，VBA 特別適合自動化大型、重複的排名任務。始終驗證公式或代碼的完整性，並根據項目進展調整單元格引用以匹配數據結構的任何變化。

最佳 Office 生產力工具

🤖	Kutools AI 助手：以智能執行為基礎，革新數據分析｜生成程式碼｜創建自訂公式｜分析數據並生成圖表｜調用 Kutools 增強函數…
	熱門功能：查找、選取項目的背景色或標記重複值｜刪除空行｜合併列或單元格且不遺失數據｜四捨五入（免公式）...
	高級 LOOKUP：多條件 VLookup｜多值 VLookup｜多表查找｜模糊查找...
	高級下拉列表：快速創建下拉列表｜依賴型下拉列表｜多選下拉列表...
	列管理器：添加指定數量的列｜移動列｜切換隱藏列的顯示狀態｜比較區域及列...
	精選功能：網格聚焦｜設計檢視｜增強編輯欄｜工作簿及工作表管理器｜資源庫（快捷文本）｜日期提取器｜合併資料｜加密/解密儲存格｜按列表發送電子郵件｜超級篩選｜特殊篩選（篩選粗體/傾斜/刪除線...）...
	15 大工具集：12 項文本工具（添加文本、刪除特定字符…）｜50+ 儀表類型（甘特圖等）｜40+ 實用公式（基於生日計算年齡等）｜19 項插入工具（插入QR码、根據路徑插入圖片等）｜12 項轉換工具（金額轉大寫、匯率轉換等）｜7 項合併與分割工具（高級合併行、分割儲存格等）｜...及更多