如何在 Excel 中建立動態的前 10 名（或前 n 名）清單？

作者Xiaoyang修改日期2025-07-25

在許多專案與業務流程中，經常需要根據個人、組織、產品或其他實體的表現或數值進行排名。「前幾名清單」能有效突顯表現最出色的項目，例如依成績排序的優秀學生、頂尖業務員，或是營收最高的部門。舉例來說，若您有一份學生成績表，並希望動態提取前 10 名高分者，用於頒獎、分析或追蹤教育成效（如下圖所示），Excel 的動態前 10 名（或前 N 名）清單功能將大有助益。透過建立此類動態清單，資料一旦更新，結果也會自動同步，不僅省去手動重新排名的繁瑣步驟，更能大幅降低人為錯誤的風險。本指南將介紹多種實用做法——包括公式、資料透視表與 VBA 巨集——協助您高效打造靈活、自動化的前 10 名或前 n 名清單，輕鬆應對各種數據分析需求。

建立動態的前 10 名或前 N 名清單

在 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 是該欄的第一個儲存格。請根據您的資料大小與位置調整這些儲存格參照。

套用公式以提取前 10 名數值

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 為數值清單的標題，用於 ROW 計算中的偏移量。
此公式能動態連結最高值與其對應姓名。若您的數值範圍包含重複值，COUNTIF 可確保每個相符姓名僅出現一次並附帶其分數。

使用公式取得相對項目

3. 提取第一筆結果後，選取 F2 儲存格中的公式，並向下拖曳填滿控點，將公式複製至所需列數，即可動態顯示所有前幾名項目對應的姓名及其匹配分數，進一步擴展您的結果。請參閱下圖：

拖曳並填滿公式至其他儲存格

透過 KUTOOLS AI 解鎖 Excel 的神奇功能

智慧執行：透過簡易指令，輕鬆執行儲存格操作、分析資料，並建立圖表！
自訂公式：打造專屬公式，讓您的工作流程更順暢！
VBA 編碼：輕鬆撰寫並套用 VBA 程式碼，立即提升工作效率！
公式解析：輕鬆掌握複雜公式！
文字翻譯：輕鬆打破試算表中的語言隔閡！

運用 AI 驅動的工具強化您的 Excel 能力。立即下載體驗前所未有的高效！

建立符合條件的動態前 10 名清單的公式

在某些分析任務中，您可能需要僅顯示符合特定條件的前幾名清單——例如將前幾名結果限定於特定群組、團隊或類別內。舉例來說，您可能希望從包含多個班級成績的完整資料表中，只找出「1 班」的前 10 名分數。以下是此情境的公式使用方法：

建立具條件篩選的動態前 10 名清單

1. 首先，從資料集中提取符合指定條件（例如「1 班」）的前 10 個最高分數。在目標儲存格（例如 J2）中輸入下列公式：

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

2. 輸入公式後，按下 Ctrl + Shift + Enter 將其確認為陣列公式，再向下拖曳填滿控點，即可自動填滿其他儲存格。此公式會傳回符合您指定條件（例如「1 班」的所有分數）的前 10 名最高分！

套用公式以根據條件提取前 10 名數值

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)))

在 Office 365 中使用公式建立動態前 10 名清單

請務必調整公式中的範圍，使其符合您的實際資料結構。請注意，若在陣列公式中使用大型區域，可能會影響效能。當重複值出現在前 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 名清單便會立即呈現，並動態即時更新——新增資料或修改分數後，排名立刻同步反映！

在 Office 365 中使用公式建立動態前 10 名清單

提示：

SORT 函數：

=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 名清單的公式

若您需要針對特定群組（例如「1 班」）提取前 10 名，這些進階的 Office 365 函數可在僅包含符合條件之列的前提下，輕鬆建立前 N 名清單。請將下方公式置於所需位置，並依需求調整範圍與條件儲存格：

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

在輸入公式後，只需按下 Enter 鍵，系統就會立即顯示根據指定條件篩選並排序的前 10 名清單，且每次修改資料或條件時都會自動更新。

另一種在 Office 365 中建立具條件篩選之動態前 10 名清單的公式

提示：

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 助手：基於以下內容徹底革新數據分析：智慧執行｜產生程式碼｜建立自訂公式｜分析資料並產生圖表｜呼叫增強函數……
	熱門功能：尋找、醒目提示或標記重複值｜刪除空白行｜合併列或儲存格而不遺失資料｜不使用公式的四捨五入……
	高級 LOOKUP：多重條件 VLookup ｜多重數值 VLookup ｜跨多個工作表 VLookup ｜模糊查找……
	高級下拉列表：快速建立下拉式清單｜相依式下拉式清單｜多選下拉式清單……
	欄位管理員：新增指定數量的欄位｜移動欄位｜切換隱藏欄位的可見狀態｜比較範圍與欄位……
	精選功能：網格聚焦｜設計視圖｜增強編輯欄｜工作簿與工作表管理員｜資源庫（自動文字）｜日期提取｜合併工作表｜加密／解密儲存格｜依清單傳送電子郵件｜超級篩選｜特殊篩選（篩選粗體儲存格／斜體／刪除線……） ......
	頂尖 15 工具組：12 文字工具（添加文本，刪除特定字符，……）｜ 50+ 圖表類型（甘特圖，……）｜ 40+ 實用公式（基於生日計算年齡，……）｜ 19 插入工具（插入二維碼，從路徑插入圖片，……）｜ 12 轉換工具（金額轉大寫，匯率轉換，……）｜ 7 合併和拆分工具（高級合併行，分割儲存格，……）｜……以及更多