如何依据另一列条件返回某列前X个唯一值？求批量自动化方案

一、Excel 365/2021 动态数组方案（推荐，适配大数据量） #

如果使用支持动态数组的Excel版本，用FILTER+SORT+TAKE组合可实现自动溢出结果，无需手动下拉填充。

1. 提取单个Size的Top 1（示例：Size为"S"的销量第一） #

在目标单元格输入公式：

=TAKE(SORT(FILTER(G:I,F:F="S"),2,-1),1)

• 逻辑说明：
  • FILTER(G:I,F:F="S")：筛选出所有Size为"S"的公司、销量、价格数据
  • SORT(...,2,-1)：按销量列（对应H列，即筛选结果的第2列）降序排序
  • TAKE(...,1)：提取排序后的首行（Top 1）

2. 批量提取所有Size的Top N（示例：Top 2） #

自动遍历所有唯一Size，批量输出对应Top数据：

=LET(
    uniqueSizes, UNIQUE(F:F),
    topN, 2,
    results, BYROW(uniqueSizes, LAMBDA(size,
        HSTACK(size, TAKE(SORT(FILTER(G:I,F:F=size),2,-1),topN))
    )),
    VSTACK({"Size","Top公司","Top销量","Top价格"}, results)
)

• 逻辑说明：
  • UNIQUE(F:F)：提取F列所有不重复的Size值
  • BYROW(...)：对每个Size执行筛选、排序、提取Top N的操作
  • HSTACK(size, ...)：将Size与对应Top数据合并为一行
  • VSTACK(...)：添加表头并堆叠所有结果，输入后自动溢出到下方和右侧

二、旧版Excel（无动态数组）方案 #

针对不支持动态数组的旧版本，用INDEX+MATCH+COUNTIF组合配合辅助列实现：

1. 添加排序辅助列（示例：J列） #

在J2单元格输入公式并下拉填充：

=COUNTIFS(F:F,F2,H:H,">"&H2)+1

• 逻辑说明：计算当前行在同Size分组内的销量排名（数值越小，销量越高）

2. 提取指定Size的Top N #

以提取Size为"M"的Top 1为例，在目标单元格输入数组公式（按Ctrl+Shift+Enter确认）：

=INDEX(G:G,MATCH(1,(F:F="M")*(J:J=1),0))

横向拖动公式可提取对应销量、价格；若要提取Top 2，将公式中J:J=1改为J:J=2即可。

3. 批量提取所有Size的Top N（VBA宏） #

通过宏实现全自动化，无需手动复制公式：

Sub ExtractTopSalesBySize()
    Dim ws As Worksheet
    Dim lastRow As Long, uniqueRow As Long
    Dim uniqueSizes As Collection
    Dim size As Variant
    Dim topN As Integer
    
    Set ws = ActiveSheet
    topN = 2 ' 可修改为需要提取的Top数量
    lastRow = ws.Cells(ws.Rows.Count, "F").End(xlUp).Row
    Set uniqueSizes = New Collection
    
    ' 收集所有唯一Size
    On Error Resume Next
    For i = 2 To lastRow
        uniqueSizes.Add ws.Cells(i, "F").Value, Key:=CStr(ws.Cells(i, "F").Value)
    Next i
    On Error GoTo 0
    
    ' 输出表头
    ws.Cells(1, "K").Value = "Size"
    ws.Cells(1, "L").Value = "Top公司"
    ws.Cells(1, "M").Value = "Top销量"
    ws.Cells(1, "N").Value = "Top价格"
    uniqueRow = 2
    
    ' 遍历每个Size提取Top N
    For Each size In uniqueSizes
        For n = 1 To topN
            ws.Cells(uniqueRow, "K").Value = size
            ws.Cells(uniqueRow, "L").Value = ws.Evaluate("INDEX(G:G,MATCH(1,(F:F=""" & size & """)*(J:J=" & n & "),0))")
            ws.Cells(uniqueRow, "M").Value = ws.Evaluate("INDEX(H:H,MATCH(1,(F:F=""" & size & """)*(J:J=" & n & "),0))")
            ws.Cells(uniqueRow, "N").Value = ws.Evaluate("INDEX(I:I,MATCH(1,(F:F=""" & size & """)*(J:J=" & n & "),0))")
            uniqueRow = uniqueRow + 1
        Next n
    Next size
End Sub

• 使用方法：按Alt+F11打开VBA编辑器，插入模块粘贴代码，修改topN值后回到工作表执行宏。

三、关键注意事项 #

• 大数据量下，动态数组公式效率远高于旧版数组公式，优先推荐Excel 365版本
• 若存在销量并列情况，上述公式会返回第一个匹配的记录；需处理并列排名可改用RANK.EQ调整辅助列逻辑
• 提前用TRIM(F:F)清理Size列的空格或格式差异，避免筛选失效

内容的提问来源于stack exchange，提问作者Çağlar