En el mundo de la hoja de cálculo, Excel se destaca como una herramienta indispensable. No sólo permite organizar y analizar datos de manera eficiente, sino que también ofrece una amplia gama de funciones y características para facilitar aún más el trabajo. En este artículo, vamos a explorar una forma eficaz de combinar rangos y matrices en Excel utilizando las funciones VSTACK y HSTACK. Estas funciones son excelentes aliadas cuando se trata de fusionar datos provenientes de diferentes partes de una hoja de cálculo, ¡así que prepárate para descubrir cómo ahorrar tiempo y mejorar tu flujo de trabajo en Excel!
El tutorial muestra cómo fusionar múltiples matrices en Excel 365 vertical y horizontalmente usando las funciones VSTACK y HSTACK.
Combinar dos o más rangos ha sido tradicionalmente una tarea desalentadora en Excel. ¡Ya no más! Las dos funciones introducidas recientemente en Excel 365, VSTACK y HSTACK, han hecho el trabajo increíblemente fácil. Ahora, fusionar rangos y matrices, incluso los dinámicos cuyas fuentes son de tamaño variable, es tan fácil como proporcionar un par de referencias de rango a una fórmula.
Cómo combinar matrices en Excel: ejemplos de fórmulas
Con las funciones VSTACK y HSTACK combinar rangos en Excel es realmente fácil y directo. Los siguientes ejemplos le darán algo de inspiración para empezar y le mostrarán enfoques de mejores prácticas.
Apilar matrices horizontal o verticalmente
Dependiendo de la estructura de sus matrices originales y el diseño de su hoja de trabajo, puede preferir combinar rangos vertical u horizontalmente.
Para fusionar varios rangos en uno solo pila horizontal, utilice la función HSTACK. En este caso, las gamas se apilan una al lado de la otra. Por ejemplo:
=HSTACK(B3:B7, B12:B16, B21:B25)
Para combinar varios rangos en uno solo pila vertical, utilice la función VSTACK. En este caso, los rangos se colocan uno debajo del otro. Por ejemplo:
=VSTACK(B3:B7, B12:B16, B21:B25)
Para una mejor visualización, estamos combinando rangos incluidos sus encabezados. En hojas de trabajo reales, normalmente solo se unirían datos sin encabezados de columna.
Fusionar rangos y constantes de matriz
En situaciones en las que a los encabezados de columna o a algunos valores les faltan los rangos originales, puede proporcionar los datos faltantes en una constante de matriz directamente a la función VSTACK o HSTACK. Por ejemplo:
=VSTACK({"Item","Color","Size"}, A2:C6, A10:C14)
Como resultado, obtendrás dos rangos apilados verticalmente uno debajo del otro.
Combine varias tablas de Excel en una
Además de matrices y rangos, las funciones VSTACK y HSTACK también pueden fusionar varias tablas de Excel en una. Dado que las tablas de Excel se expanden automáticamente para incluir nuevos datos, esta es una solución perfecta para conjuntos de datos de tamaño variable. Simplemente, proporcione referencias de tablas a VSTACK y HSTACK, y no tendrá que preocuparse por actualizar sus fórmulas sin importar cuántas filas/columnas nuevas de datos se agreguen a las tablas de origen.
=VSTACK(Table1, Table2)
Fusionar matrices dinámicas
Si las matrices que está combinando son dinámicas, es decir, devueltas por fórmulas de matriz dinámica, puede unir dichas matrices utilizando el operador de rango de derrame, que es una etiqueta hash (#) agregada a la dirección de la celda superior izquierda en el rango de derrame.
Por ejemplo, para combinar dos matrices generadas por la función ÚNICA en la captura de pantalla siguiente, puede usar esta fórmula:
=VSTACK(E3#, E12#)
Donde E3 es la celda superior de la primera matriz y E12 es la celda superior de la segunda matriz.
El beneficio de este enfoque es que con cualquier cambio en los datos de origen, Excel recalculará y actualizará automáticamente las 3 matrices.
Manejar celdas en blanco
En caso de que los rangos de fuentes contengan celdas vacías, la matriz devuelta tendrá ceros en lugar de espacios en blanco. Para solucionar este problema, puede anidar su función VSTACK o HSTACK en el primer argumento de SUSTITUIR y decirle que reemplace ceros con cadenas vacías («»). Por ejemplo:
=SUBSTITUTE(VSTACK(A3:B7, A12:B16), 0, "")
Si sus matrices originales contienen valores cero, sustituya cadenas vacías en lugar de ceros para evitar reemplazar absolutamente todos los ceros en la matriz devuelta. Por ejemplo:
=SUSTITUIR(VSTACK(A3:B7, A12:B16), «», «»)
Sustituir una cadena vacía por una cadena vacía puede parecer ilógico, pero funciona. En una matriz apilada, Excel muestra ceros en lugar de espacios en blanco sólo para una mejor visualización; de hecho esas celdas están vacías.
Combina rangos y elimina filas vacías
Cuando hay filas o columnas en blanco en los rangos de fuentes, la matriz resultante tendrá filas o columnas llenas de ceros en su lugar. Para eliminar dichas filas y/o columnas, puede filtrar los espacios en blanco con la ayuda de la función FILTRO.
La idea general es suministrar la salida VSTACK o HSTACK al formación argumento de la función FILTRO y utilícelo para filtrar entradas no vacías (<>«»).
Esencialmente puede haber tres escenarios básicos:
Filtrar espacios en blanco según la columna clave
Para filtrar todas las filas en las que una columna en particular no está en blanco, marque esa columna específica. Por ejemplo, para combinar filas que tienen cualquier valor en la columna A, la fórmula en D4 es:
=FILTER(VSTACK(A3:B7, A12:B16), VSTACK(A3:A7, A12:A16)<>"")
Eliminar filas absolutamente en blanco
Para eliminar filas vacías que no tienen un solo valor, cada columna debe verificarse individualmente. Para hacerlo, en el incluir argumento de la función FILTRO, usted proporciona un criterio separado para cada columna y usa el signo más (+) para probarlos con la lógica OR.
Por ejemplo, para eliminar sólo filas absolutamente en blanco y mantener aquellas que tienen un valor en cualquier columna, la fórmula en G4 es:
=FILTER(VSTACK(A3:B7, A12:B16), (VSTACK(A3:A7, A12:A16)<>"")+(VSTACK(B3:B7, B12:B16)<>""))
Para obtener más información, consulte Cómo filtrar con múltiples criterios OR.
Eliminar todos los espacios en blanco
Para filtrar filas que tienen al menos una celda vacía en cualquier columna, vuelva a verificar cada columna individual en busca de espacios que no estén en blanco, pero esta vez pruebe los criterios con la lógica AND usando el asterisco.
.
=FILTER(VSTACK(A3:B7,A12:B16),(VSTACK(A3:A7,A12:A16)<>"")*(VSTACK(B3:B7,B12:B16)<>""))
Por ejemplo, para eliminar filas que tienen espacios en blanco en la columna A o B, la fórmula en E16 es:
Combine rangos y elimine filas vacías.
- Consejos:
- Aunque las funciones VSTACK y HSTACK devuelven ceros en lugar de celdas vacías, esto se debe únicamente a motivos de presentación. Como no hay datos en esas celdas, usamos <>«» para los criterios de FILTRO. El criterio <>0 también funcionará.
- Para reemplazar ceros con cadenas vacías en la matriz resultante, use la función SUSTITUIR como se explica en el ejemplo anterior.
Este enfoque resulta útil para fusionar matrices de variables que pueden aumentar de tamaño a medida que se agregan nuevos datos. Simplemente, apile más filas/columnas de las que actualmente están llenas de datos y filtre las vacías.
Combina rangos sin duplicados
Para combinar solo registros únicos de dos o más rangos, use las funciones de apilamiento junto con UNIQUE.
=UNIQUE(HSTACK(B2:E4, H2:K4), TRUE)
Por ejemplo, para apilar los rangos B2:E4 y H2:K4 horizontalmente sin duplicados, la fórmula en C8 es:
El valor lógico VERDADERO en el segundo argumento de UNIQUE le dice a la función que compare datos entre columnas, no entre filas.
Combina rangos de Excel sin duplicados.
Unir y ordenar matrices a la vez
Al fusionar matrices grandes, como toque final, es posible que desee ordenar la salida. Esto es bastante fácil de hacer anidando HSTACK o VSTACK en otra función de matriz dinámica: SORT.
=SORT(HSTACK(B2:E4, H2:K4), 1, 1, TRUE )
En nuestro caso, la fórmula toma esta forma:
- Desglosando la fórmula, esto es lo que tenemos:
- HSTACK(B2:E4, H2:K4) es la matriz a ordenar.1 en el segundo argumento es el índice de clasificación, es decir, la matriz está ordenada por la primera fila (Año
- ).
- 1 en el tercer argumento indica el orden de clasificación ascendente.
Fusionar y ordenar matrices en Excel.
Combinando los mismos rangos de varias hojas: fórmula 3D
Para combinar el mismo rango de celdas de diferentes hojas de trabajo, use una referencia 3D en su fórmula HSTACK o VSTACK.
- Para crear una fórmula 3D VSTACK para los conjuntos de datos siguientes, esto es lo que debe hacer:
- En la celda más a la izquierda del rango de destino (A3), comience a escribir la fórmula: =VSTACK(
- Haga clic en la pestaña de la primera hoja de trabajo. Mientras sostiene el Cambio
- haga clic en la pestaña de la última hoja de trabajo.
- Selecciona el rango que quieres combinar en todas las hojas. Escriba el paréntesis de cierre y presione la tecla Ingresar
llave.
=VSTACK('Store 1:Store 3'!A2:C6)
La fórmula completa tendrá el siguiente aspecto: Si es probable que sus conjuntos de datos originales se expandan en el futuro, puede incluir más filas de las que actualmente están llenas de datos y luegofiltrar filas vacías
=FILTER(VSTACK('Store 1:Store 3'!A2:C100), VSTACK('Store 1:Store 3'!A2:A100)<>"")
Fórmula 3D VSTACK para combinar los mismos rangos de diferentes hojas
Así es como se combinan matrices en Excel 365 usando las funciones VSTACK y HSTACK. ¡Te agradezco por leer y espero verte en nuestro blog la próxima semana!
Combine rangos y matrices en Excel: funciones VSTACK y HSTACK
Si eres usuario de Excel y quieres aprender a combinar rangos y matrices de manera eficiente, estás en el lugar correcto. En este artículo, te mostraremos cómo utilizar las funciones VSTACK y HSTACK para lograrlo.
¿Qué son las funciones VSTACK y HSTACK en Excel?
VSTACK y HSTACK son dos de las funciones más útiles en Excel cuando se trata de combinar rangos y matrices. Estas funciones permiten agrupar y concatenar datos de manera vertical (VSTACK) u horizontal (HSTACK).
¿Cómo utilizar la función VSTACK?
Para utilizar la función VSTACK en Excel, sigue los siguientes pasos:
- Selecciona el rango o matriz que deseas combinar verticalmente.
- Escribe la función VSTACK seguida de un paréntesis abierto «(«.
- Pega el rango o matriz seleccionada dentro del paréntesis.
- Cierra el paréntesis y presiona Enter.
A continuación, se muestra un ejemplo de cómo utilizar la función VSTACK:
=VSTACK(A1:A5)
Esta función combinará verticalmente los valores del rango A1:A5 en una única columna.
¿Cómo utilizar la función HSTACK?
Si en lugar de combinar datos verticalmente, prefieres hacerlo horizontalmente, la función HSTACK es tu mejor opción. A continuación, te explicamos cómo utilizarla:
- Selecciona el rango o matriz que deseas combinar horizontalmente.
- Escribe la función HSTACK seguida de un paréntesis abierto «(«.
- Pega el rango o matriz seleccionada dentro del paréntesis.
- Cierra el paréntesis y presiona Enter.
Por ejemplo:
=HSTACK(A1:D1)
Esta función combinará horizontalmente los valores del rango A1:D1 en una única fila.
Conclusiones
En resumen, las funciones VSTACK y HSTACK en Excel son herramientas poderosas que te permiten combinar rangos y matrices de forma rápida y sencilla. Ya sea que necesites agrupar datos vertical u horizontalmente, estas funciones te ahorrarán tiempo y esfuerzo en tus tareas de análisis de datos.
No dudes en experimentar con estas funciones y descubrir todas las posibilidades que ofrecen. ¡Aprovecha al máximo Excel y conviértete en un experto en combinar datos!
Para obtener más información sobre estas funciones, puedes consultar la documentación oficial de Microsoft.