Una manera fácil de convertir una matriz o un rango en una columna con la función TOCOL.
La capacidad de transponer datos de columnas a filas y viceversa ha existido en Excel durante algún tiempo. Pero convertir un rango de celdas en una sola columna fue una tarea difícil de descifrar. Ahora, eso finalmente está cambiando. Microsoft introdujo una nueva función, llamada TOCOL, que puede hacer una transformación de matriz a columna en un abrir y cerrar de ojos. A continuación se muestra una lista de tareas que esta nueva característica puede manejar fácilmente.
Cómo usar la función TOCOL en Excel – fórmulas de ejemplo
Para comprender mejor las posibilidades de la función TOCOL y qué tareas puede cubrir, echemos un vistazo a algunas fórmulas de ejemplo.
Convierta matriz en columna ignorando espacios y errores
Como habrá notado en el ejemplo anterior, la fórmula TOCOL predeterminada conserva todos los valores de la matriz de origen, incluidas las celdas vacías y los errores.
En la matriz resultante, las celdas vacías están representadas por ceros, lo que puede ser bastante confuso, especialmente si la matriz original tiene valores 0. La solución es saltar sobre espacios vacíos. Para hacer esto, establezca el segundo argumento en 1:
=TOCOL(A2:C5, 1)
a ignorar erroresestablezca el segundo argumento en 2:
=TOCOL(A2:C5, 2)
Para descartarlos a ambos, espacios en blanco y erroresusa 3 para ignorar argumento:
=TOCOL(A2:C5, 3)
Escanea la matriz horizontal o verticalmente
con defecto escanear_por_columna argumento (FALSO u omitido), la función TOCOL escanea la matriz horizontalmente fila por fila. Para procesar valores por columna, establezca este argumento en VERDADERO o 1. Por ejemplo:
=TOCOL(A2:C5, ,TRUE)
Tenga en cuenta que en ambos casos las matrices devueltas tienen el mismo tamaño, pero los valores se organizan en un orden diferente.
Combinar múltiples rangos en una columna
Si está tratando con múltiples rangos no contiguos, primero puede combinar los rangos verticalmente en una sola matriz usando la función VSTACK y luego usar TOCOL para convertir la matriz combinada en una columna.
Suponiendo que el primer rango es A2:C4 y el segundo rango es A8:C9, la fórmula tiene la siguiente forma:
=TOCOL(VSTACK(A2:C4, A8:C9))
Esta fórmula demuestra el comportamiento predeterminado: lee las matrices concatenadas horizontalmente de izquierda a derecha, como se muestra en la columna E de la imagen a continuación.
Para leer valores verticalmente de arriba a abajo, configure 3calle argumento TOCOL a VERDADERO:
=TOCOL(VSTACK(A2:C4, A8:C9), ,TRUE)
Tenga en cuenta que en este caso, la fórmula primero devuelve valores de la columna A de ambas matrices, luego de la columna B, y así sucesivamente. La razón es que TOCOL escanea una sola matriz apilada, no los rangos individuales originales.
Si su lógica comercial requiere que los rangos originales se apilen horizontalmente en lugar de verticalmente, use la función HSTACK en lugar de VSTACK.
Para sumar cada matriz posterior a la derecha de la matriz anterior y leer las matrices combinadas horizontalmente, la fórmula es:
=TOCOL(HSTACK(A2:C4, A8:C10))
Para sumar cada matriz subsiguiente a la derecha de la matriz anterior y escanear las matrices combinadas verticalmente, la fórmula es:
=TOCOL(HSTACK(A2:C4, A8:C10), ,TRUE)
Extraiga valores únicos de un rango de varias columnas
La función ÚNICA de Excel puede encontrar fácilmente elementos únicos en una sola columna o fila, así como devolver filas únicas, pero no puede extraer valores únicos de una matriz de varias columnas. La solución es usarlo junto con la función TOCOL.
Por ejemplo, para extraer todos los valores distintos (diferentes) del rango A2:C7, la fórmula es:
=UNIQUE(TOCOL(A2:C7))
Además, puede incluir la fórmula anterior en la función ORDENAR para ordenar alfabéticamente la matriz devuelta:
=SORT(UNIQUE(TOCOL(A2:C7)))
Cómo convertir rango a columna en Excel 365 – 2010
En las versiones de Excel donde no se admite la función TOCOL, existen algunas formas alternativas de convertir un rango de celdas en una columna. Estas soluciones son bastante complicadas, pero funcionan de todos modos.
Para leer el rango por fila:
ÍNDICE(rangoQOTENTE(FILA(A1)-1, COLUMNAS(rango))+1, MODO(FILA(A1)-1, COLUMNAS(rango))+1)
Para leer el rango por columna:
ÍNDICE(rangoMODO(FILA(A1)-1, FILAS(rango))+1, QOTENTE(FILA(A1)-1, FILAS(rango))+1)
Para nuestro conjunto de datos de muestra, las fórmulas son las siguientes:
Para escanear el rango horizontalmente de izquierda a derecha:
=INDEX($A$2:$C$5, QUOTIENT(ROW(A1)-1, COLUMNS($A$2:$C$5))+1, MOD(ROW(A1)-1, COLUMNS($A$2:$C$5))+1)
Esta fórmula es equivalente a la función TOCOL por 3calle argumento establecido en FALSO u omitido:
=TOCOL(A2:C5)
Para escanear el rango verticalmente de arriba a abajo:
=INDEX($A$2:$C$5, MOD(ROW(A1)-1, ROWS($A$2:$C$5))+1, QUOTIENT(ROW(A1)-1, ROWS($A$2:$C$5))+1)
Esta fórmula es comparable a la función TOCOL con 3calle argumento establecido en VERDADERO:
=TOCOL(A2:C5, ,TRUE)
A diferencia de TOCOL, se deben ingresar fórmulas alternativas en cada celda donde desea que aparezcan los resultados. En nuestro caso, las fórmulas van en las celdas E2 (por fila) y G2 (por columna), luego se copian en la fila 13.
Si las fórmulas se copian en más líneas de las necesarias, un #REF! el error aparecerá en las celdas «extra». Para evitar esto, puede anidar fórmulas en la función IFERROR de esta manera:
=IFERROR(INDEX($A$2:$C$5, QUOTIENT(ROW(A1)-1, COLUMNS($A$2:$C$5))+1, MOD(ROW(A1)-1, COLUMNS($A$2:$C$5))+1), "")
Tenga en cuenta que para que las fórmulas se copien correctamente, bloqueamos el rango usando referencias de celdas absolutas ($A$2:$C$5). En su lugar, puede utilizar un rango con nombre.
Cómo funcionan estas fórmulas
A continuación se muestra un desglose detallado de la primera fórmula que organiza las celdas por fila:
=INDEX($A$2:$C$5, QUOTIENT(ROW(A1)-1, COLUMNS($A$2:$C$5))+1, MOD(ROW(A1)-1, COLUMNS($A$2:$C$5))+1)
La idea es usar la función ÍNDICE para devolver el valor de una celda en particular en función de sus números relativos de fila y columna en el rango.
El numero de fila se calcula por esta combinación:
QUOTIENT(ROW(A1)-1, COLUMNS($A$2:$C$5))+1
COCIENTE devuelve la parte entera de una división.
Para numeradoruse ROW(A1)-1, que devuelve un número de serie desde 0 en E2 (la primera celda donde se ingresó la fórmula) hasta 11 en E13 (la última celda donde se ingresó la fórmula).
El denominador traído por COLUMNAS ($ A $ 2: $ C $ 5)) es constante e igual al número de columnas en su rango (3 en nuestro caso).
Ahora, si revisas el resultado de la COTIZACIÓN para las primeras 3 celdas (E2:E4), verás que es igual a 0 (ya que la parte entera de la división es cero). Sumar 1 da como resultado la fila número 1.
Para las siguientes 3 celdas (E5:E5), COCIENTE devuelve 1 y la operación +1 devuelve el número de fila 2. Y así sucesivamente.
En otras palabras, esta parte de la fórmula crea una secuencia repetitiva de números, como 1,1,1,2,2,2,3,3,3,4,4,4,… Cada número repite tantos veces, ya que hay columnas en su rango.
Calcular número de columnaconstruya una secuencia apropiada de números usando la función MOD:
MOD(ROW(A1)-1, COLUMNS($A$2:$C$5))+1
Como hay 3 columnas en nuestro rango (A2:C5), la secuencia debe ser 1,2,3,1,2,3,…
La función MOD devuelve el resto después de la división.
EN E2, MODO (FILA (A1) -1, COLUMNAS ($ A $ 2: $ C $ 5)) + 1)
SE CONVIERTE
MOD(1-1, 3)+1)
y devuelve 1.
EN E3, MODO (FILA (A2) -1, COLUMNAS ($ A $ 2: $ C $ 5)) + 1)
SE CONVIERTE
MOD(2-1, 3)+1)
y devuelve 2.
Con los números de fila y columna establecidos, INDEX no tiene problemas para recuperar el valor requerido.
En E2, ÍNDICE ($ A $ 2: $ C $ 5, 1, 1) devuelve el valor de 1Calle fila y 1Calle columna en el rango de referencia, es decir, desde la celda A2.
En E3, ÍNDICE ($ A $ 2: $ C $ 5, 1, 2) devuelve el valor de 1Calle fila y 2cuando columna, es decir, de la celda B2.
Etcétera.
La segunda fórmula que escanea el rango por columna funciona de manera similar. La diferencia es que usa MOD para obtener el número de fila y COCIENTE para obtener el número de columna.
Así es como puede usar la función TOCOL en Excel 365 y las soluciones en versiones anteriores para transformar una matriz bidimensional en una sola columna. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!
