Excel按±10%范围分组相似值并返回指定统一值的高效公式实现求助

嘿，这个需求我之前帮别人处理过，刚好可以给你几个高效的解法，分新版和老版Excel两种情况来说，先假设你的数据在A2:A10区域（对应你给出的示例数值：20,20.5,21,21.5,23,23.6,24,24.8,25.4）：

一、现代Excel（365/2021及以后，支持动态数组） #

这种版本用LAMBDA和动态数组函数会非常高效，不需要手动下拉公式，一步就能得到所有结果。

1. 直接返回每个值对应的组统一值（以向上取整到0.5的组平均值为例） #

在B2单元格输入下面的公式，它会自动溢出填充到所有行：

=LET(
    data, A2:A10,
    // 定义函数：筛选当前值所在±10%范围的所有数值
    get_group, LAMBDA(v, FILTER(data, ABS(data - v) / v <= 0.1)),
    // 定义函数：计算组内平均值并向上取整到最近的0.5
    get_result, LAMBDA(v, ROUNDUP(AVERAGE(get_group(v)) / 0.5, 0) * 0.5),
    // 对每个数据应用上述逻辑
    MAP(data, get_result)
)

如果你想指定返回组内的某个值（比如组内最大值21），只需要把get_result里的AVERAGE(get_group(v))换成MAX(get_group(v))就行，修改后的公式：

=LET(
    data, A2:A10,
    get_group, LAMBDA(v, FILTER(data, ABS(data - v) / v <= 0.1)),
    get_result, LAMBDA(v, MAX(get_group(v))),
    MAP(data, get_result)
)

2. 先标记组号，再计算统一值（适合需要查看分组情况的场景） #

在B2输入组号公式（自动溢出）：

=SCAN("", A2:A10, LAMBDA(a, v, IF(ISNUMBER(XMATCH(v, FILTER(A2:A10, ABS(A2:A10 - INDEX(A2:A10, a)) / INDEX(A2:A10, a) <= 0.1))), a, COUNTA(UNIQUE(TAKE(B$1:B1, ROW()-1)))+1)))

然后在C2输入统一值公式，下拉填充到所有行：

=ROUNDUP(AVERAGE(FILTER(A$2:A$10, B$2:B$10=B2))/0.5,0)*0.5

二、老版本Excel（不支持动态数组，比如2019及以前） #

老版本只能用数组公式，输入完成后需要按Ctrl+Shift+Enter确认（不要直接回车，公式会自动加上大括号{}）。

在B2输入公式，然后下拉填充：

=ROUNDUP(AVERAGE(IF(ABS(A$2:A$10-A2)/A2<=0.1,A$2:A$10,""))/0.5,0)*0.5

如果要返回组内最大值，把公式改成：

=MAX(IF(ABS(A$2:A$10-A2)/A2<=0.1,A$2:A$10,""))

同样按Ctrl+Shift+Enter确认输入。

小提醒 #

• 这里的±10%是基于当前值计算的（即值v的范围是v*0.9到v*1.1），刚好符合你需求里“组内最小值和最大值相差不超过10%”的要求，因为组内的每个值都会互相覆盖在这个范围内。
• 如果你的数据里包含0，需要额外加个判断避免除以0的错误，不过你的示例都是正数，应该不用操心这个~

备注：内容来源于stack exchange，提问作者Andy C