Excel列表唯一值校验、计数及提取Top3的公式需求

Hey there, let's walk through exactly how to solve this Excel problem step by step—whether you're using the latest 365 with dynamic arrays or an older version, I’ve got you covered. We’ll break it down into three clear stages: extracting distinct values, counting their occurrences, and pulling the top 3 most frequent entries.

First, we need to isolate all unique entries from your original data range. Let’s assume your raw list lives in cells A2:A100 (adjust the range to match your actual data).

For Excel 365/2021 (Dynamic Array Support) #

This is the simplest approach. In an empty cell (say B2), enter:
=UNIQUE(A2:A100)
The formula will automatically "spill" all unique values into column B—no need to drag it down manually.

For Older Excel Versions (No Dynamic Arrays) #

Use this array formula (enter it with Ctrl+Shift+Enter instead of just Enter):
=INDEX($A$2:$A$100, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$100), 0))
Drag this formula down until you start seeing #N/A errors—those mean you’ve captured all distinct values.

Next, we’ll tally how many times each unique value appears in the original list. If your distinct values are in column B (starting at B2), enter this formula in C2 and drag it down to match your distinct values:
=COUNTIF($A$2:$A$100, B2)
This checks the original data range and counts every instance of the value in B2.

Finally, let’s pull the top 3 values with the highest counts. Again, we’ll cover both modern and older Excel versions:

For Excel 365/2021 (Dynamic Arrays) #

This is the most efficient method. If your distinct values are in B2:B[last row] and counts in C2:C[last row], enter this in an empty cell (like D2):
=TAKE(SORTBY(B2:B10, C2:C10, -1), 3)

• SORTBY(B2:B10, C2:C10, -1) sorts your distinct values in descending order based on their counts
• TAKE(..., 3) grabs just the first 3 entries from that sorted list

For Older Excel Versions #

Use a combination of INDEX, MATCH, and LARGE for each top spot:

• Top 1: =INDEX(B:B, MATCH(LARGE(C:C, 1), C:C, 0))
• Top 2: =INDEX(B:B, MATCH(LARGE(C:C, 2), C:C, 0))
• Top 3: =INDEX(B:B, MATCH(LARGE(C:C, 3), C:C, 0))
  Note: If there are ties (e.g., two values with the same count for 3rd place), this will return the first matching value it finds. For handling ties comprehensively, you’d need a more complex formula, but this works for the standard "top 3" use case.

Quick Example Walkthrough #

Using your sample context (adjusted for clarity):

• Original List (A2:A10): 3, 3, 3, 3, 2, 2, 2, 1, 4, 5
• Distinct Values (B2:B6): 3, 2, 1, 4, 5
• Counts (C2:C6): 4, 3, 1, 1, 1
• Top 3 (D2:D4): 3, 2, 1

内容的提问来源于stack exchange，提问作者Diogo Martins