基于消费额提取Top20唯一旅行者的Excel公式优化需求

问题根源 #

原公式通过LARGE提取消费额后，XLOOKUP仅匹配首个符合该消费额的行，导致相同消费额的旅行者被重复提取同一人信息。

解决方案 #

通过给消费额添加唯一标识（行号小数），让相同消费额的条目拥有唯一排序值，确保LARGE能依次选中所有不同的旅行者，再用XLOOKUP匹配对应信息。

修正后的公式 #

旅行者姓名

=IFERROR(IF($Z$5="ALL",
    XLOOKUP(LARGE($D$3:$D$50000 + ROW($D$3:$D$50000)/100000, $R3), $D$3:$D$50000 + ROW($D$3:$D$50000)/100000, $C$3:$C$50000),
    XLOOKUP(LARGE(IF($A$3:$A$50000=$Z$5, $D$3:$D$50000 + ROW($D$3:$D$50000)/100000), $R3), IF($A$3:$A$50000=$Z$5, $D$3:$D$50000 + ROW($D$3:$D$50000)/100000), IF($A$3:$A$50000=$Z$5, $C$3:$C$50000))
), "")

经理姓名

=IFERROR(IF($Z$5="ALL",
    XLOOKUP(LARGE($D$3:$D$50000 + ROW($D$3:$D$50000)/100000, $R3), $D$3:$D$50000 + ROW($D$3:$D$50000)/100000, $B$3:$B$50000),
    XLOOKUP(LARGE(IF($A$3:$A$50000=$Z$5, $D$3:$D$50000 + ROW($D$3:$D$50000)/100000), $R3), IF($A$3:$A$50000=$Z$5, $D$3:$D$50000 + ROW($D$3:$D$50000)/100000), IF($A$3:$A$50000=$Z$5, $B$3:$B$50000))
), "")

部门

（已对应原公式中消费额与部门范围的行偏移调整行号）

=IF($Z$5="ALL",
    XLOOKUP(LARGE($D$4:$D$50001 + ROW($D$4:$D$50001)/100000, $R3), $D$4:$D$50001 + ROW($D$4:$D$50001)/100000, $A$3:$A$50000),
    XLOOKUP(LARGE(IF($A$3:$A$50000=$Z$5, $D$4:$D$50001 + ROW($D$4:$D$50001)/100000), $R3), IF($A$3:$A$50000=$Z$5, $D$4:$D$50001 + ROW($D$4:$D$50001)/100000), IF($A$3:$A$50000=$Z$5, $A$3:$A$50000))
)

原理说明 #

• 给消费额加上ROW()/100000，利用行号的微小小数差异，让相同消费额的不同条目拥有唯一排序值（例如$100+3/100000=100.00003，$100+4/100000=100.00004）。
• LARGE会按组合值大小依次提取，确保相同消费额的旅行者被逐个选中。
• XLOOKUP匹配唯一组合值，返回对应行的信息，避免重复。
• 若使用非Excel 365版本，需按Ctrl+Shift+Enter将公式作为数组公式输入。

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