如何在Excel列中查找首个≥65的实例并按规则重置标记

假设你的数据列是A2:A100（从第二行开始，第一行为表头），在相邻列（比如B列）的B2单元格输入以下公式，下拉填充即可实现需求：

支持 LET 函数的 Excel 版本（365/2021+） #

=LET(
    curr_val, A2,
    rng_up, $A$2:A1,
    start_val, XLOOKUP(TRUE, rng_up>=65, rng_up, "", 0, -1),
    is_new_start, IF(ROW()=2, curr_val>=65, AND(curr_val>=65, curr_val=start_val)),
    current_start, IF(is_new_start, curr_val, IF(start_val="", "", start_val)),
    IF(AND(curr_val>=65, current_start<>"", curr_val<current_start), 1, 0)
)

公式逻辑说明 #

1. rng_up：引用当前行以上的所有数据，用于定位最近的区间起始值
2. start_val：通过XLOOKUP向上查找最近的≥65的值，即上一个区间的起始标记值
3. is_new_start：判断当前单元格是否为新区间的起点：
   • 若为第二行，只要值≥65就是第一个区间的起点
   • 其他行，当值≥65且等于上一个区间的起始值时，触发重新标记，成为新区间起点
4. current_start：确定当前单元格所属区间的起始标记值，新起点则用当前值，否则沿用之前的起始值
5. 最终判断：当当前值≥65且小于所属区间的起始标记值时返回1，其余情况返回0

不支持 LET 函数的旧版 Excel #

使用嵌套公式（功能一致，可读性稍差）：

=IF(AND(A2>=65, A2<IF(AND(A2>=65, OR(ROW()=2, A2=XLOOKUP(TRUE, $A$2:A1>=65, $A$2:A1, "", 0, -1))), A2, XLOOKUP(TRUE, $A$2:A1>=65, $A$2:A1, "", 0, -1))), 1, 0)

验证示例 #

假设Data列数据为：50, 70, 66, 70, 68, 65, 70, 67，对应的标记结果为：0, 0, 1, 0, 1, 0, 0, 0

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