Eu quero criar um intervalo no excel

Question

Eu tenho as seguintes células:

    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

Eu quero criar um "intervalo" / resumo dos dados da seguinte forma:

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

Eu posso fazer isso manualmente, mas os dados estão nos milhares de células, portanto, parece entediante. Existem funções embutidas para ajudar a facilitar isso?

    

Answer 1

Podemos fazer isso com duas colunas auxiliares. Eu coloco as seguintes fórmulas em D2 e E2 respectivamente:

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

Em seguida, copiei o comprimento do conjunto de dados.

IssomedeuumalistanaqualeupossousarafórmulaSMALL()paraobteroinícioeofim.

NoG2eucoloco:

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

Em seguida, copiei uma coluna e desci até obter células em branco.

Em seguida, usamos esses números em um INDEX / MATCH para retornar as outras informações.

Em I2 eu coloquei:

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

Copiada em uma coluna e descida até que eu receba espaços em branco.

Eu poderia ter feito o mesmo com o VLOOKUP:

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

    

Answer 2

Sua pergunta poderia lhe dar um pouco mais de explicação, tanto do padrão quanto das ferramentas que você deseja usar. Eu vou assumir quando você diz "função" que você quer fórmulas de folha, não código VBA. Você está olhando apenas para a primeira instância de cada par de banda / cor ou está procurando por bandas e conjuntos de cores consecutivos até o fim? Se o último, sua tabela incluiria mais três linhas da seguinte maneira:

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

Se você quiser esta tabela mais longa, você pode criar duas novas linhas (nesta folha ou em uma nova) e, supondo que você tenha "Número" na A1, use o seguinte código:

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

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

Estenda o código no comprimento total da coluna da sua tabela. O resultado será o seguinte.

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

A partir daqui, basta procurar o número de cada conjunto (1,2,3, ...) e encontrar o número da linha para obter as informações a seguir. Se você colocar as colunas Início / Fim primeiro, poderá usar vlookup para isso.

Se você quer apenas a primeira aparição de cada par (mas por que a linha 8 é deixada de fora?), você precisará de um pouco mais de lógica. Isso pode exigir uma coluna adicional, mas a lógica extra será bastante simples.