获取低于给定阈值的最长连续单元格集的第一个单元格的列标题

Question

让我们首先解决现有两个公式的问题。

你的 y* 公式=FREQUENCY(A3:I3,K3)实际上计算的是数据低于以下值的次数，或等于，阈值。仅计算值以下阈值，并假设数据仅由整数值组成，则需要使用以下公式：。=FREQUENCY(A3:I3,K3-1)

FREQUENCY严格来说， z* 公式中的部分应该FREQUENCY(IF(A3:I3<K3, COLUMN(A3:I3)), IF(A3:I3>K3, COLUMN(A3:I3)))用代替>=。>在完整的 z* 公式中，您只提取计数的最大值，这实际上没有什么区别。但是，在更复杂的公式中使用时，它可能无法正常工作。例如，我的解决方案公式无法正确使用>（对于最长序列之前的值等于阈值的极端情况）。

修正后的 z* 公式为：

{=MAX(FREQUENCY(IF(A3:I3<K3,COLUMN(A3:I3)),IF(A3:I3>=K3,COLUMN(A3:I3))))}

使用这个修正公式作为基础可以得到以下解决方案（数组输入N3），它提取最长序列的第一个单元格的列标题：

{=INDEX(1:1,IFERROR(SMALL(IF(A3:I3>=K3,COLUMN(A3:I3)),MOD(MAX(10^5*FREQUENCY(IF(A3:I3<K3,COLUMN(A3:I3)),IF(A3:I3>=K3,COLUMN(A3:I3)))+ROW(INDEX(N:N,1):INDEX(N:N,COUNT(IF(A3:I3>=K3,))+1))-1),10^5))+1,COLUMN(A3:I3)))}

解释：

上述公式的美化版本如下：

{=
INDEX(
  (1:1),
  IFERROR(
    SMALL(
      IF(A3:I3>=K3,COLUMN(A3:I3)),
      MOD(
        MAX(
          10^5*FREQUENCY(IF(A3:I3<K3,COLUMN(A3:I3)),IF(A3:I3>=K3,COLUMN(A3:I3)))
          +ROW(INDEX(N:N,1):INDEX(N:N,COUNT(IF(A3:I3>=K3,))+1))-1+IF(1,,"N:N needs to match the column of the cell this formula is entered into")
        ),
        10^5
      )
    )+1,
    COLUMN(A3:I3)
  )
)}

该公式的工作方式是修改FREQUENCY()“bin”计数，使其也包含 bin 索引。然后从对应于最长序列的 bin 计数中提取索引，并与一起使用SMALL()以获取该 bin 的下限阈值。此阈值是紧挨着最长序列的第一个单元格之前的单元格的列号。最后，第一个单元格的列号与一起使用INDEX()以获取第一个单元格的标题。

对于您提供的示例：

FREQUENCY(IF(A3:I3<K3,COLUMN(A3:I3)),IF(A3:I3>=K3,COLUMN(A3:I3)))→ {1;0;4;0;0}，箱计数数组
10^5*{1;0;4;0;0}→ {100000;0;400000;0;0}，缩放后的 bin 计数数组
COUNT(IF(A3:I3>=K3,))→4比箱数少一（这计算了间隔阈值，但箱数比这多一）
因此→ →是缩放后的 bin 计数数组的索引，ROW(INDEX(N:N,1):INDEX(N:N,COUNT(IF(A3:I3>=K3,))+1))-1ROW(INDEX(N:N,1):INDEX(N:N,5))-1{0;1;2;3;4}{100000;0;400000;0;0}
{100000;0;400000;0;0}+{0;1;2;3;4}→ {100000;1;400002;3;4}，修改后的 bin 计数数组
MAX({100000;1;400002;3;4})→ 400002，最长序列修改后的 bin 计数
MOD(400002,10^5)→ 2，阈值数组中最长序列 bin 的下限阈值的秩（bin 计数数组的索引对应于阈值数组中下限阈值的秩）
IF(A3:I3>=K3,COLUMN(A3:I3))→ {FALSE,2,3,FALSE,FALSE,FALSE,FALSE,8,9}，阈值数组
SMALL({FALSE,2,3,FALSE,FALSE,FALSE,FALSE,8,9},2)+1→ 3+1→ 4，最长序列的第一个单元格的列号（SMALL()忽略布尔值；对应于一个箱的下限阈值是该箱第一个单元格之前的单元格的列号）
IFERROR(SMALL(…)+1,COLUMN(A3:I3))是必需的，因为第一个 bin 没有下限，并且如果最长序列对应于第一个 bin（即，最长序列从数据范围的第一个单元格开始），我们得到SMALL({…},0)+1→ #NUM!。IFERROR()捕获此错误，并COLUMN(A3:I3)返回第一个单元格的列号。
INDEX((1:1),4)→ 4，最长序列第一个单元格的列标题

笔记：

美化的公式确实可以起作用。
(1:1)需要使用周围的括号来强制1:1将其保持在自己的一行上。
ROW(INDEX(column,1):INDEX(column,…))用来代替更常见的公式ROW(INDIRECT("1:"&…))，因为它是非易失性的，并且在删除行/列时仍然有效。（column当然，前提是设置为输入公式的单元格的列。）
IF(1,,"comment")是内联注释。（该值始终为零，因此对公式没有净影响。）
如果您只希望显示第一个单元格的列号，则可以通过删除最外层的来简化公式INDEX()。

警告：

如果存在多个最长序列，则上述公式返回最后的最长序列。可以修改公式以返回第一个最长序列的起始标题，方法是在修改 bin 计数时使用 bin 索引的十进制补码而不是普通索引：

{=INDEX(1:1,IFERROR(SMALL(IF(A3:I3>=K3,COLUMN(A3:I3)),10^5-MOD(MAX(10^5*频率(如果(A3:I3<K3,COLUMN(A3:I3)),如果(A3:I3>=K3,COLUMN(A3:I3)))+10^5-(行（索引（N：N，1）：索引（N：N，COUNT（如果（A3：I3> = K3，））+1））-1））,10^5))+1,COLUMN(A3:I3)))}

Answer 1