在Excel列表中提取连续数字组 [英] Extract groups of consecutive numbers in Excel list

问题描述

让我们以该列表为例

"1,2,3,8,9,10,11,12,55,56,57"

"1,2,3,8,9,10,11,12,55,56,57"

如您所见，我有一个列表，该列表通常以1为增量增加，但通常会跳过.

As you can see, I have a list which generally increases in increments of one, but which often skips ahead.

在此示例中，我有三组连续的数字，分别是1-3、8-12和55-57.

In this example, I have three groups of consecutive numbers, which are 1-3, 8-12, and 55-57.

我需要一个给出两个列表的公式.每个组中的第一个数字，最后一个中的一个.

I need a formula which gives me two lists. One for the first number in each group, and one for the last.

例如，第一个列表将是1、8和55.

For example, the first list would be 1, 8, and 55.

另一个列表是3、12和57.

The other list would be 3, 12, and 57.

任何帮助将不胜感激.

推荐答案

您也可以使用数组公式:

You can also do it with array formulas:

每个小组的开始

=A2

在(例如)C2和

=IFERROR(SMALL(IF(A$3:A$13-A$2:A$12>1,A$3:A$13),ROW()-ROW(A$2)),"")

在C3中并拉下来.

每组结束时

=IFERROR(SMALL(IF((A$3:A$13-A$2:A$12>1)+(A$3:A$13=""),A$2:A$12),ROW()-ROW(A$1)),"")

必须使用 Ctrl Shift Enter

可以进一步完善以适应可变数量的行.

Could be refined further to work with variable number of rows.

这篇关于在Excel列表中提取连续数字组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！