☰

我想在 Excel 中创建一个范围

tag-icon • tag-icon microsoft-excel
我想在 Excel 中创建一个范围

我有以下单元格：

    Number  Band    Color
1   10    A          A1
2   20    A          A1
3   30    A          A1
4   40    B          A1
5   50    B          A2
6   60    C          B1
7   70    C          B1
8   80    A          B1
9   90    A          A1
10  100   B          A1

我想创建如下的数据“范围”/摘要：

Start  End  Band    Color
10     30   A        A1
40     40   B        A1
50     50   B        A2
60     70   C        B1

我可以手动完成此操作，但数据有数千个单元格，因此看起来很繁琐。是否有内置函数可以帮助实现此操作？

答案1

我们可以使用两个辅助列来实现这一点。我分别在 D2 和 E2 中输入以下公式：

=IF(OR(B2<>B1,C2<>C1),A2,"")
=IF(OR(B2<>B3,C2<>C3),A2,"")

然后复制数据集的长度。

在此处输入图片描述

这给了我一个列表，我可以使用 SMALL() 公式来获取开始和结束。

在 G2 中我输入：

=IFERROR(SMALL(D:D,ROW(1:1)),"")

然后复制一列并向下复制直到得到空白单元格。

然后我们在 INDEX/MATCH 中使用这些数字来返回其他信息。

在 I2 中我输入：

=IF($G2<>"",INDEX(B:B,MATCH($G2,$A:$A,0)),"")

复制一列并向下复制，直到得到空白。

我可以用 VLOOKUP 做同样的事情：

=VLOOKUP($G2,$A:$C,COLUMN(B:B),FALSE)

在此处输入图片描述

答案2

您的问题能否再解释一下，包括模式和要使用的工具。我假设您说“函数”时想要的是工作表公式，而不是 VBA 代码。您是只查看每个波段/颜色对的第一个实例，还是要查找连续匹配的波段和颜色集？如果是后者，您的表格实际上将包含以下三行：

Start  End  Band    Color
10     30   A        A1
40     40   B        A1
50     50   B        A2
60     70   C        B1
80     80   A        B1
90     90   A        A1
100    100  B        A1

如果您想要这个更长的表格，您可以创建两个新行（在此工作表中或新工作表中），并且假设 A1 中有“数字”，请使用以下代码：

单元格 D2=IF(NOT(AND($B1=$B2,$C1=$C2)),IFERROR(MAX(INDIRECT("D1:D"&ROW(D2)-1))+1,1),0)

细胞E2=IF(NOT(AND($B2=$B3,$C2=$C3)),IFERROR(MAX(INDIRECT("E1:E"&ROW(E2)-1))+1,1),0)

将代码延伸到表格的整个列长度。结果如下。

Number Band Color Start End
10     A    A1    1     0
20     A    A1    0     0
30     A    A1    0     1
40     B    A1    2     2
50     B    A2    3     3
60     C    B1    4     0
70     C    B1    0     4
80     A    B1    5     5
90     A    A1    6     6
100    B    A1    7     7

从这里，只需查找每组的编号（1、2、3、...）并找到行号即可获取您想要的信息。如果您将开始/结束列放在首位，则可以使用vlookup它。

如果您只想要每对的第一次出现（但为什么第 8 行被忽略了？），则需要更多逻辑。这可能需要额外的列，但额外的逻辑相当简单。

相关内容