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

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

Posso fazer isso manualmente, mas os dados estão na casa de milhares de células, portanto parece tedioso. Existem funções integradas para ajudar a facilitar isso?

Answer 1

Podemos fazer isso com duas colunas auxiliares. Coloquei 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.

Isso me deu uma lista na qual posso usar a fórmula SMALL() para obter o início e o fim.

No G2 coloquei:

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

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

Então usamos esses números em um INDEX/MATCH para retornar as outras informações.

Em I2 coloquei:

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

Copiei uma coluna e desci até ficar em branco.

Eu poderia ter feito o mesmo com VLOOKUP:

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

Answer 2

Sua pergunta poderia ter um pouco mais de explicação, tanto do padrão quanto das ferramentas que você deseja usar. Presumirei quando você disser "função" que deseja fórmulas de planilha, não código VBA. Você está olhando apenas para a primeira instância de cada par de banda/cor ou está procurando um conjunto consecutivo de bandas e cores correspondentes até o fim? Neste último caso, sua tabela incluiria mais três linhas, como segue:

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 quiser esta tabela mais longa, você pode criar duas novas linhas (nesta planilha ou uma nova) e, supondo que você tenha "Número" em 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 por todo o comprimento 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 que você procura. Se você colocar as colunas Início/Fim primeiro, poderá usar vlookuppara isso.

Se você quiser apenas a primeira aparição de cada par (mas então por que a linha 8 foi 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.