Excel多条件按地区筛选Top X账户的公式实现需求

架构老林

2026-7-17

批量筛选各地区Top X账户的Excel公式方案

逻辑说明

先锁定当前地区内「Special?=Y」的所有账户，占满对应名额后，剩余名额从该地区「Special?≠Y」的账户里按$列降序取前N个（N=地区目标X - 该地区特殊账户总数），符合条件的在新增列标记Y。

适用Excel 365/2021（动态数组）的公式

假设：

数据工作表为Sheet1：A列=地区标识，B列=Special?，C列=$金额，D列用于标记Top X
配置表为Sheet2：A列=地区标识，B列=对应Top X数量

在Sheet1!D2输入以下公式，下拉填充即可：

=LET(
    地区, A2,
    目标数, XLOOKUP(地区, Sheet2!A:A, Sheet2!B:B, 0),
    同区特殊数, COUNTIFS(Sheet1!A:A, 地区, Sheet1!B:B, "Y"),
    同区非特殊排名, RANK.EQ(C2, FILTER(Sheet1!C:C, (Sheet1!A:A=地区)*(Sheet1!B:B<>"Y")), 0),
    剩余名额, MAX(0, 目标数 - 同区特殊数),
    IF(OR(B2="Y", AND(B2<>"Y", 同区非特殊排名<=剩余名额)), "Y", "")
)

兼容旧版Excel（无动态数组）的公式

在Sheet1!D2输入以下公式，按Ctrl+Shift+Enter作为数组公式确认后下拉填充：

=IF(
    OR(
        B2="Y",
        AND(
            B2<>"Y",
            RANK.EQ(C2, IF((Sheet1!$A:$A=A2)*(Sheet1!$B:$B<>"Y"), Sheet1!$C:$C), 0)
            <=MAX(0, VLOOKUP(A2, Sheet2!$A:$B, 2, FALSE)-COUNTIFS(Sheet1!$A:$A, A2, Sheet1!$B:$B, "Y"))
        )
    ),
    "Y",
    ""
)