☰

quiero crear un rango en excel

tag-icon • tag-icon microsoft-excel
quiero crear un rango en excel

Tengo las siguientes celdas:

    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

Quiero crear un "rango"/resumen de los datos de la siguiente manera:

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

Puedo hacer esto a mano, pero los datos están en miles de celdas, por lo que parece tedioso. ¿Hay funciones integradas para ayudar a facilitar esto?

Respuesta1

Podemos hacer esto con dos columnas auxiliares. Puse las siguientes fórmulas en D2 y E2 respectivamente:

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

Luego copió la longitud del conjunto de datos.

ingrese la descripción de la imagen aquí

Esto me dio una lista en la que puedo usar la fórmula SMALL() para obtener el inicio y el final.

En G2 pongo:

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

Luego copié una columna y bajé hasta que obtuve celdas en blanco.

Luego usamos esos números en un ÍNDICE/COINCIDENCIA para devolver la otra información.

En I2 pongo:

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

Copié en una columna y hacia abajo hasta que obtuve espacios en blanco.

Podría haber hecho lo mismo con BUSCARV:

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

ingrese la descripción de la imagen aquí

Respuesta2

A tu pregunta te podría explicar un poco más, tanto el patrón como las herramientas que quieres utilizar. Asumiré que cuando diga "función" que desea fórmulas de hoja, no código VBA. ¿Está mirando solo la primera instancia de cada par de banda/color o está buscando bandas y colores coincidentes consecutivos establecidos hasta el final? Si es lo último, su tabla en realidad incluiría tres filas más, como se muestra a continuación:

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

Si desea esta tabla más larga, puede crear dos filas nuevas (ya sea en esta hoja o en una nueva) y, suponiendo que tenga "Número" en A1, use el siguiente código:

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

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

Extienda el código a lo largo de toda la columna de su tabla. El resultado será el siguiente.

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

Desde aquí, simplemente busque el número de cada conjunto (1,2,3,...) y busque el número de fila para obtener la información que busca. Si coloca las columnas Inicio/Fin primero, puede usarlas vlookuppara esto.

Si solo desea la primera aparición de cada par (pero entonces, ¿por qué se omite la fila 8?), necesitará un poco más de lógica. Esto puede requerir una columna adicional, pero la lógica adicional será bastante simple.

información relacionada