☰

我想在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)),"")

然後複製一列並向下複製，直到出現空白儲存格。

然後我們在索引/匹配中使用這些數字來傳回其他資訊。

在 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 中有“Number”，請使用以下程式碼：

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 行？），您將需要更多的邏輯。這可能需要額外的列，但額外的邏輯將相當簡單。

相關內容