Excel按Distance列大于10的条件分组返回Measure范围及Value最值

实现方式判断 #

Excel 365、2021及以上版本无需使用VBA，直接用原生公式即可满足需求；2019及更早版本因不支持动态数组、LET/XLOOKUP等函数，需要使用VBA实现。

注意：公式无需读取单元格填充色，直接复用你设置条件格式的判断逻辑（Distance列值>10）即可识别目标行。

公式实现（适用365/2021+版本） #

先统一假设数据结构：表头在第1行，数据从第2行开始，A列为Measure列、B列为Value列、C列为Distance列，E/F/G列为目标计算列。

E列（范围文本展示）公式 #

在E2单元格输入以下公式，下拉填充即可：

=IF(C2<=10,"",
 LET(
  pre_green_row,IFERROR(XLOOKUP(1,(C$1:C1>10)*1,ROW(C$1:C1),0),1),
  start_row,pre_green_row+1,
  end_row,ROW()-1,
  start_val,INDEX(A:A,start_row),
  end_val,INDEX(A:A,end_row),
  IF(start_row=end_row,start_val,start_val&"-"&end_val)
 )
)

F列（分组最小值）公式 #

在F2单元格输入以下公式，下拉填充即可：

=IF(C2<=10,"",
 LET(
  pre_green_row,IFERROR(XLOOKUP(1,(C$1:C1>10)*1,ROW(C$1:C1),0),1),
  start_row,pre_green_row+1,
  end_row,ROW()-1,
  MIN(INDEX(B:B,start_row):INDEX(B:B,end_row))
 )
)

G列（分组最大值）公式 #

在G2单元格输入以下公式，下拉填充即可：

=IF(C2<=10,"",
 LET(
  pre_green_row,IFERROR(XLOOKUP(1,(C$1:C1>10)*1,ROW(C$1:C1),0),1),
  start_row,pre_green_row+1,
  end_row,ROW()-1,
  MAX(INDEX(B:B,start_row):INDEX(B:B,end_row))
 )
)

旧版Excel VBA实现说明 #

如果使用无LET/XLOOKUP函数的旧版Excel，可通过自定义VBA函数或者遍历行数据的方式实现，逻辑和上述公式一致：逐行遍历识别Distance>10的行，记录上一个标记行的位置，计算对应区间的范围、最值即可。

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