在不可排序列中获取各连续同值数据块的最小/最大行号

一、生成每个连续块的递增序号（Desired Order列） #

假设数据从第2行开始（A列为Values，C列为Desired Order），在C2单元格输入以下公式，下拉填充即可：

=IF(A2=A1, C1+1, 1)

逻辑说明：如果当前行的Values与上一行一致，就在上一行序号基础上加1；如果不一致，直接重置为1，实现每个新块从1开始编号。

二、获取每个连续块的最小/最大行号 #

通过添加辅助标记块ID的方式实现：

1. 添加块ID辅助列：
   在空白列（比如E列）的E2单元格输入公式，下拉填充：

   =IF(A2=A1, E1, E1+1)
   

   每个连续的同值块会被分配一个唯一ID，不同位置的同值块ID不同（比如第一个Value1块ID为1，第二个Value1块ID为4）。

2. 提取块的行号信息：

   • 对于Google Sheets，使用QUERY函数直接分组统计：

     =QUERY({A:A, E:E, ROW(A:A)}, "select Col1, min(Col3), max(Col3) where Col1 is not null group by Col1, Col2 label min(Col3)'最小行号', max(Col3)'最大行号'", 1)
     

   • 对于Excel，结合数组公式与UNIQUE函数：
     先提取所有唯一块的标识（按Ctrl+Shift+Enter执行数组公式）：

     =UNIQUE(FILTER(A:A&E:E, E:E<>""))
     

     再针对每个标识，分别计算最小/最大行号（均为数组公式，按Ctrl+Shift+Enter执行）：

     # 最小行号
     =MIN(IF((A:A=LEFT(G2,LEN(G2)-1))*(E:E=RIGHT(G2,1)), ROW(A:A), ""))
     # 最大行号
     =MAX(IF((A:A=LEFT(G2,LEN(G2)-1))*(E:E=RIGHT(G2,1)), ROW(A:A), ""))
     

补充说明 #

之前使用SUMPRODUCT或COUNTIF失效的核心原因是这类函数会统计所有历史出现的同值，无法区分连续块与非连续的同值分散块。必须通过判断当前行与上一行的Values是否一致，才能精准识别连续块的边界，实现正确的编号与统计。

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