¿Alguna vez te has enfrentado al desafío de convertir un rango de celdas en Excel a una sola columna? Si es así, estás en el lugar correcto. En este artículo, vamos a explorar la función TOCOL de Excel, una herramienta poderosa que te permitirá convertir fácilmente un rango en una sola columna. ¡No te lo pierdas!
Una forma sencilla de transformar una matriz o rango en una columna con la función TOCOL.
La capacidad de transponer datos de columnas a filas y viceversa existe en Excel desde hace bastante tiempo. Pero convertir un rango de celdas en una sola columna fue una tarea difícil de resolver. Ahora, eso finalmente está cambiando. Microsoft ha introducido una nueva función, llamada TOCOL, que puede realizar 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 función puede resolver fácilmente.
Cómo utilizar la función TOCOL en Excel: ejemplos de fórmulas
Para comprender mejor las posibilidades de la función TOCOL y las tareas que puede cubrir, veamos algunos ejemplos de fórmulas.
Transformar matriz en columna ignorando espacios en blanco y errores
Como habrás notado en el ejemplo anterior, la fórmula TOCOL predeterminada mantiene todos los valores de la matriz de origen, incluidas las celdas en blanco y los errores.
En la matriz resultante, las celdas vacías se representan con ceros, lo que puede resultar bastante confuso, especialmente si la matriz original tiene valores 0. La solución es saltar espacios en blanco. Para esto, establece el segundo argumento en 1:
=TOCOL(A2:C5, 1)
A ignorar erroresestablezca el segundo argumento en 2:
=TOCOL(A2:C5, 2)
Para excluir a ambos, espacios en blanco y erroresuse 3 para el ignorar argumento:
=TOCOL(A2:C5, 3)
Escanear matriz horizontal o verticalmente
Con el valor predeterminado escaneo_por_columna argumento (FALSO u omitido), la función TOCOL escanea la matriz horizontalmente por fila. Para procesar valores por columna, establezca este argumento en VERDADERO o 1. Por ejemplo:
=TOCOL(A2:C5, ,TRUE)
Observe que, en ambos casos, las matrices devueltas tienen el mismo tamaño, pero los valores están organizados en un orden diferente.
Combine múltiples rangos en una columna
Si está tratando con varios rangos no contiguos, primero puede combinar los rangos verticalmente en una sola matriz con la ayuda de la función VSTACK y luego usar TOCOL para transformar la matriz combinada en una columna.
Suponiendo que el primer rango es A2:C4 y el segundo rango es A8:C9, la fórmula toma esta forma:
=TOCOL(VSTACK(A2:C4, A8:C9))
Esta fórmula demuestra el comportamiento predeterminado: lee las matrices combinadas horizontalmente de izquierda a derecha como se muestra en la columna E de la imagen siguiente.
Para leer valores verticalmente de arriba a abajo, configure el 3tercero argumento de 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 única matriz apilada, no los rangos individuales originales.
Si su lógica empresarial requiere apilar los rangos originales horizontalmente en lugar de verticalmente, utilice la función HSTACK en lugar de VSTACK.
Para agregar cada matriz subsiguiente a la derecha de la matriz anterior y leer las matrices combinadas horizontalmente, la fórmula es:
=TOCOL(HSTACK(A2:C4, A8:C10))
Para agregar 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 valores ú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 diferentes (distintos) del rango A2:C7, la fórmula es:
=UNIQUE(TOCOL(A2:C7))
Además, puede ajustar la fórmula anterior en la función ORDENAR para organizar la matriz devuelta en orden alfabético:
=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 un par de formas alternativas de transformar un rango de celdas en una columna. Estas soluciones son bastante complicadas, pero de todos modos funcionan.
Para leer el rango por fila:
ÍNDICE(rangoCOCIENTE(FILA(A1)-1, COLUMNAS(rango))+1, MOD(FILA(A1)-1, COLUMNAS(rango))+1)
Para leer el rango por columna:
ÍNDICE(rangoMOD(FILA(A1)-1, FILAS(rango))+1, COCIENTE(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 con los 3tercero 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 los 3tercero argumento establecido en VERDADERO:
=TOCOL(A2:C5, ,TRUE)
A diferencia de TOCOL, las fórmulas alternativas deben ingresarse en cada celda donde desea que aparezcan los resultados. En nuestro caso, las fórmulas van a las celdas E2 (por fila) y G2 (por columna), y luego se copian hasta la fila 13.
Si las fórmulas se copian en más filas de las necesarias, aparecerá un mensaje #REF! El error aparecerá en las celdas “extra”. Para evitar que esto suceda, puedes anidar las fórmulas en la función SIERROR 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), "")
Observe que para que las fórmulas se copien correctamente, bloqueamos el rango usando referencias de celda 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 utilizar la función ÍNDICE para devolver el valor de una determinada celda en función de sus números relativos de fila y columna en el rango.
El numero de fila se calcula mediante esta combinación:
QUOTIENT(ROW(A1)-1, COLUMNS($A$2:$C$5))+1
COCIENTE devuelve la porción entera de una división.
Para numeradorusa FILA (A1) -1, que devuelve un número de serie de 0 en E2 (primera celda donde se ingresa la fórmula) a 11 en E13 (la última celda donde se ingresa la fórmula).
El denominador generado por COLUMNS($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 del COCIENTE para las primeras 3 celdas (E2:E4), verás que es igual a 0 (porque la parte entera de la división es cero). Sumar 1 da la fila número 1.
Para las siguientes 3 celdas (E5:E5), COCIENTE devuelve 1 y la operación +1 entrega la fila número 2. Y así sucesivamente.
En otras palabras, esta parte de la fórmula crea una secuencia numérica repetida como 1,1,1,2,2,2,3,3,3,4,4,4,… Cada número se repite tantas veces como haya columnas en su rango.
Para calcular el número de columnaconstruyes una secuencia numérica apropiada 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, MOD(FILA(A1)-1, COLUMNAS($A$2:$C$5))+1)
se convierte
MODO(1-1, 3)+1)
y devuelve 1.
En E3, MOD(FILA(A2)-1, COLUMNAS($A$2:$C$5))+1)
se convierte
MODO(2-1, 3)+1)
y devuelve 2.
Con los números de fila y columna establecidos, INDEX no tiene problemas para obtener el valor requerido.
En E2, ÍNDICE ($A$2:$C$5, 1, 1) devuelve el valor de 1calle fila y el 1calle columna del rango de referencia, es decir, de la celda A2.
En E3, ÍNDICE ($A$2:$C$5, 1, 2) devuelve el valor de 1calle fila y el 2Dakota del Norte 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 QUOTIENT para obtener el número de columna.
Así es como puedes usar la función TOCOL en Excel 365 y soluciones alternativas en versiones anteriores para transformar una matriz bidimensional en una sola columna. ¡Te agradezco por leer y espero verte en nuestro blog la próxima semana!
Error 403 The request cannot be completed because you have exceeded your quota. : quotaExceeded
Función TOCOL de Excel para convertir rango a una sola columna
En Excel, hay ocasiones en las que necesitamos convertir un rango de celdas en una sola columna. Una forma de lograr esto es utilizando la función TOCOL. En este artículo, exploraremos cómo usar la función TOCOL en Excel y responderemos algunas preguntas frecuentes al respecto.
¿Qué es la función TOCOL de Excel?
La función TOCOL de Excel es una función que nos permite convertir un rango de celdas en una única columna. Su sintaxis básica es la siguiente:
=TOCOL(rango)
El argumento “rango” representa el rango de celdas que deseamos convertir en una sola columna. La función TOCOL devolverá una matriz vertical con todos los valores del rango especificado.
¿Cómo se utiliza la función TOCOL?
Para utilizar la función TOCOL en Excel, sigue estos pasos:
- Selecciona la celda donde deseas que comience la columna resultante.
- Escribe la fórmula “=TOCOL(rango)” en la celda seleccionada, reemplazando “rango” con el rango de celdas que deseas convertir.
- Pulsa Enter para obtener el resultado.
Por ejemplo, si deseamos convertir el rango A1:A5 en una sola columna, seleccionamos una celda vacía, escribimos “=TOCOL(A1:A5)” y presionamos Enter. La función TOCOL devolverá el rango A1:A5 como una columna vertical.
Preguntas frecuentes sobre la función TOCOL
1. ¿Puedo utilizar la función TOCOL en conjunción con otras funciones de Excel?
Sí, la función TOCOL se puede utilizar en combinación con otras funciones de Excel. Por ejemplo, puedes aplicar funciones de texto, matemáticas o búsqueda a la columna resultante de la función TOCOL.
2. ¿La función TOCOL conserva el formato de las celdas originales?
No, la función TOCOL solo devuelve los valores de las celdas en una columna vertical, sin conservar el formato original de las celdas.
3. ¿Puedo utilizar la función TOCOL en versiones anteriores de Excel?
No, la función TOCOL se introdujo en versiones posteriores de Excel, por lo que no está disponible en versiones anteriores. Si utilizas una versión anterior de Excel, deberás buscar una solución alternativa para convertir un rango en una sola columna.
Conclusión
La función TOCOL de Excel es una herramienta útil para convertir rápidamente un rango de celdas en una sola columna. Con su sintaxis sencilla, podemos obtener una columna vertical con los valores del rango deseado. Sin embargo, debemos tener en cuenta que la función TOCOL no preserva el formato original de las celdas.
Para obtener más información sobre la función TOCOL y otras funciones de Excel, puedes visitar los siguientes recursos: