函数与公式 I 如何标记所有连续5个大于10的数字

在图中，A1:O1单元格区域为随机数字，要求在A3:O3单元格区域将A1:O1单元格区域中所有连续5个大于10的数字都标记出来。例如，E1单元格的数字为11，E1:I1单元格区域5个数字都大于10，因此E1单元要标记。D1单元格的数字为9，在包含D1单元格的所有连续5个单元格区域A1:E1、B1:F1、C1:G1、D1:H1中没有任何一组5个单元格数字都大于10，因此D1不需要标记。 

标记所有连续5个大于10的数字

在A3单元格中输入以下公式，并向右复制到O3单元格。

=COUNT(0/(COUNTIF(OFFSET (A1,,{-4,-3,-2,-1,0},1,5),'>10')=5))

公式向右复制时A1会依次变成B1，C1，D1，…，O1。

返回值大于0的表示对应第一行的单元格包含在某组连续5个单元格数字都大于10的单元格区域中。

“OFFSET(A1,,{-4,-3,-2,-1,0},1,5)”部分以A1作为引用基准，偏移0行，向左分别偏移4列、3列、2列、1列、0列，取1行5列，生成包含A1单元格在内的5个单元格区域引用。当OFFSET函数返回的引用超出工作表边缘时返回错误值#REF!。

COUNTIF函数判断生成的5个单元格区域引用中的数字是否大于10。如果生成的某个单元格区域引用中大于10的单元格数量等于5，表示OFFSET函数的第一参数单元格包含在该组连续5个单元格数字都大于10的单元格区域中。

以F1单元格为例，“COUNTIF(OFFSET(F1,,{-4,-3,-2,-1,0},1,5),'>10')”部分返回值如下。

{3,4,4,5,5}

表示B1:F1单元格区域中大于10的数字个数是3，C1:G1单元格区域中大于10的数字个数是4，D1:H1单元格区域中大于10的数字个数是4，E1:I1单元格区域中大于10的数字个数是5，F1:J1单元格区域中大于10的数字个数是5。

“COUNTIF(OFFSET(F1,,{-4,-3,-2,-1,0},1,5),'>10')=5”部分表示COUNTIF函数返回的数组元素等于5返回TRUE，否则返回FALSE。返回值如下。

{FALSE,FALSE,FALSE,TRUE,TRUE}

用0除以COUNTIF函数返回数组，返回值如下。

{#DIV/0!,#DIV/0!,#DIV/0!,0,0}

最后，COUNT函数返回数组中数字的个数为2，表示F1单元格包含在两组连续5个单元数字都大于10的单元格区域中。

这些小技巧，你都了解了吗？