Crear una combinación cruzada (todas las combinaciones) a partir de 2 columnas en Excel: Guía completa
Al trabajar con dos listas en Excel —como nombres de productos y tamaños, regiones y representantes comerciales, o estudiantes y cursos— es posible que necesite generar todas las combinaciones posibles entre ambas. Esta operación se conoce como cruce cruzado (también llamado producto cartesiano). Este tutorial incluye instrucciones paso a paso, ventajas e inconvenientes, y ejemplos reales, para que pueda elegir el método que mejor se adapte a su flujo de trabajo.
¿Qué es una combinación cruzada?
Una combinación cruzada (también conocida como producto cartesiano) es una operación que genera todas las combinaciones posibles entre dos listas. En Excel, esto implica emparejar cada elemento de la Lista A con todos los elementos de la Lista B, creando una matriz completa de combinaciones.
Las combinaciones cruzadas son extremadamente útiles en muchos escenarios reales de datos, como:
Variantes de productos
Combina colores × tallas × estilos para crear un catálogo completo de productos.
Análisis de ventas
Cree combinaciones de regiones × comerciales × trimestres.
Planificación y programación
Genere todas las combinaciones posibles de empleados × turnos o estudiantes × cursos.
Pruebas y simulaciones
Genere combinaciones de escenarios para modelado, previsión o validación.
Ejemplo:
Si dispone de:
El resultado del cruce cruzado será:
Realizar una combinación cruzada en Excel
Excel ofrece varias formas de crear una combinación cruzada, y el método ideal depende de la versión de Excel que utilice, de su nivel de comodidad con fórmulas o herramientas, y del tamaño de sus datos. A continuación, le presentamos cuatro métodos prácticos y eficientes: desde fórmulas sencillas hasta herramientas más avanzadas como Power Query y VBA. Cada enfoque tiene sus propias ventajas, así que podrá elegir el que mejor se adapte a su flujo de trabajo, al volumen de datos y a sus necesidades de automatización.
Método 1: Combinación cruzada mediante fórmula (Excel 365)
1.Prepare sus datos. Coloque su primera lista en una columna (por ejemplo, A2:A5 para Productos) y la segunda en otra (por ejemplo, C2:C5 para Colores).
2. Introduzca la siguiente fórmula en una celda vacía donde desee obtener el resultado y pulse Intro. La fórmula se expandirá y devolverá al instante todas las combinaciones generadas. Consulte la captura de pantalla:
=TEXTSPLIT(TEXTJOIN(",", TRUE, A2:A5 & "|" & TRANSPOSE(C2:C5)), "|", ",")
Explicación de esta fórmula:
- A2:A5 & «|» & TRANSPONER(C2:C5): Combina cada valor de la columna A con todos los valores de la columna C.
- UNIRCADENAS(",", VERDADERO, …): Combina todos los pares en una única cadena de texto larga, separada por comas.
- DIVIDIRCADENA(…, «|», ","): Divide nuevamente el texto en una tabla de dos columnas.
En resumen, la fórmula crea pares A|C, los combina en una única cadena de texto y luego los divide nuevamente en una tabla estructurada de dos columnas, generando así todas las combinaciones posibles.
Consejos:
Además de la fórmula mostrada anteriormente, puede usar la siguiente para obtener el mismo resultado.
=LET(a,A2:A5,b,C2:C5,
MAKEARRAY(ROWS(a)*ROWS(b),2,
LAMBDA(r,c,
IF(c=1, INDEX(a, 1+INT((r-1)/ROWS(b))), INDEX(b, 1+MOD(r-1, ROWS(b))))
)
))
Ventajas
- Totalmente dinámico
- Sin columnas auxiliares
- Se actualiza automáticamente cuando cambian las listas de origen
Inconvenientes
- Requiere Excel 365
- La fórmula parece compleja para principiantes
✨ Listar todas las combinaciones: ¡Genere todas las combinaciones posibles con un solo clic!
¿Cansado de escribir fórmulas complejas para obtener todas las combinaciones posibles? Con Kutools para Excel, podrá generar al instante todas las combinaciones de varias columnas o valores: ¡sin fórmulas, sin Power Query, solo unos pocos clics!
Método 2: Combinación cruzada mediante Power Query
Power Query ofrece un enfoque limpio y sin código compatible con todas las versiones modernas de Excel. Le permite crear una combinación cruzada mediante pasos rápidos e intuitivos, sin necesidad de escribir fórmulas. Siga las instrucciones paso a paso que aparecen a continuación.
Paso 1: Cree tablas para cada conjunto de datos de columnas
1.Seleccione la primera lista de datos, haga clic en Insertar > Tabla, y en el cuadro de diálogo Crear tabla, haga clic en Aceptar. Así obtendrá la primera tabla.
2. En la pestaña Diseño de tabla, asigna un nombre significativo a la tabla para facilitar su referencia posterior.
3. Repita los mismos pasos para convertir otra columna de datos en una tabla y asígnele un nombre.
Paso 2: Importe las tablas y cárguelas como conexiones
1.Seleccione la primera tabla y haga clic en Datos>Desde tabla o rango, consulte la captura de pantalla:
2. En la ventana del Editor de Power Query, haga clic en Cerrar y cargar > Cerrar y cargar desde la pestaña Inicio.
3. Aparecerá el cuadro de diálogo Importar datos. Seleccione la opción Solo crear conexión y haga clic en Aceptar.
4. Aparecerá el panel Consultas y conexiones a la derecha, mostrando que se ha añadido una consulta únicamente como conexión.
5. Repita los mismos pasos para cargar la segunda tabla como una consulta de conexión únicamente, y verá que aparece junto a la primera en el panel Consultas y conexiones.
Paso 3: Cree una consulta de referencia y una columna personalizada
1. Haga clic con el botón derecho en la consulta que desee usar como primera tabla en el cruce cruzado y seleccione Referencia.
2.En la ventana del Editor de Power Query, vaya a la pestaña Agregar columnay haga clic en Columna personalizada, consulte la captura de pantalla:
3. En el cuadro de diálogo Columna personalizada, en el campo Fórmula de columna personalizada, escriba el nombre de la otra tabla que desea usar para el cruce cruzado. A continuación, haga clic en el botón Aceptar.
Nota:
Si el nombre de su consulta contiene espacios (por ejemplo, Color del producto), debe encerrarlo entre la sintaxis #«Nombre de la consulta» al introducirlo en el campo de fórmula de columna personalizada. Por ejemplo, para Color del producto, escriba #«Color del producto».
4. Aparecerá una nueva columna personalizada; haga clic en el botón Expandir para mostrar su contenido.
5. En el panel expandido, asegúrese de que esté marcada la casilla correspondiente al nombre de la columna que desea unir. A continuación, haga clic en Aceptar.
6.Ahora verá todas las combinaciones generadas a partir de las dos tablas.
Paso 4: Cargue los datos en la hoja de cálculo
Vaya a la pestaña Inicio, haga clic en Cerrar y cargar > Cerrar y cargar. La tabla con todas las combinaciones se cargará en una hoja de cálculo nueva.
→
Ventajas
- Gestiona grandes volúmenes de datos: ofrece un rendimiento excelente incluso con miles de filas.
- Reutilizable y actualizable: si añade más datos al rango de origen, basta con actualizar la consulta para que los resultados se actualicen automáticamente.
Inconvenientes
- Ligeramente más pasos
- Requiere conocimientos básicos de Power Query
Método 3: Combinación cruzada mediante Tabla Dinámica
Este método es indirecto, pero puede resultar sorprendentemente eficaz cuando necesite generar todas las combinaciones sin escribir fórmulas. Es especialmente útil para usuarios que prefieren un enfoque visual basado en clics o que trabajan con versiones de Excel que no admiten fórmulas de matrices dinámicas.
1. Cree dos tablas independientes para las listas de datos y asígneles nombres siguiendo los mismos pasos descritos en Paso 1 del Método 2.
2. Seleccione la tabla que desee usar como primera columna. A continuación, vaya a la pestaña Insertar y haga clic en Tabla dinámica. Consulte la captura de pantalla:
3. En el cuadro de diálogo Tabla dinámica a partir de una tabla o rango, seleccione Hoja de cálculo existente, elija la ubicación de la tabla dinámica, marque la casilla Agregar estos datos al modelo de datos y, a continuación, haga clic en Aceptar.
4. Cuando aparezca el panel Campos de la tabla dinámica a la derecha, marca la casilla correspondiente al nombre de la columna de la tabla y se añadirá automáticamente al área Filas.
5. A continuación, cambie a la pestaña Todos del panel Campos de la tabla dinámica, seleccione la tabla que desee usar como segunda columna de su cruce cruzado y active su nombre de columna para añadirla al área Filas. ¡Y listo! Obtendrá la tabla dinámica tal como se muestra en la siguiente captura de pantalla:
6. Haga clic en una celda de la Tabla Dinámica, vaya a la pestaña Diseño, seleccione Diseño del informe > Mostrar en forma tabular y obtendrá la Tabla Dinámica en formato tabular. Consulte la captura de pantalla:
7. Siga haciendo clic en Diseño del informe > Repetir todas las etiquetas de elementos para mostrar todos los elementos en cada fila.
8. Por último, haga clic en Totales generales > Desactivado para filas y columnas.
Ahora, la tabla dinámica muestra una lista clara de todas las combinaciones, sin filas ni columnas de resumen.
Ventajas
- No requiere fórmulas ni Power Query
- Muy fácil e intuitivo
- Ideal para análisis rápidos
Inconvenientes
- No es dinámico
- Requiere acciones manuales
- La salida no está vinculada a los datos originales
Método 4: Combinación cruzada mediante función definida por el usuario (Excel 365 / Excel 2021 y posteriores)
Si necesita generar con frecuencia todas las combinaciones posibles entre dos listas, una función definida por el usuario (UDF) le ofrece una solución limpia, reutilizable y altamente flexible. Con Excel 365 o Excel 2021, la salida de matriz expandida hace que los resultados sean totalmente dinámicos, brindándole una forma potente y cómoda de automatizar operaciones de combinación cruzada.
1. Pulse Alt + F11 para abrir el editor de VBA.
2. A continuación, haga clic en Insertar > Módulo y copie y pegue el siguiente código en el módulo en blanco.
Function CrossJoin(list1 As Range, list2 As Range)
'Updateby Extendoffice
Dim arr1, arr2, result()
Dim i As Long, j As Long, r As Long
arr1 = list1.Value
arr2 = list2.Value
ReDim result(1 To UBound(arr1, 1) * UBound(arr2, 1), 1 To 2)
r = 1
For i = 1 To UBound(arr1, 1)
For j = 1 To UBound(arr2, 1)
result(r, 1) = arr1(i, 1)
result(r, 2) = arr2(j, 1)
r = r + 1
Next j
Next i
CrossJoin = result
End Function
3. Vuelva a la hoja de cálculo de Excel, introduzca la siguiente fórmula, pulse Intro y Excel mostrará automáticamente todas las combinaciones.
=CrossJoin(A2:A5, C2:C5)
Conclusión
En resumen, realizar una combinación cruzada en Excel ofrece múltiples soluciones flexibles y eficientes, permitiéndole elegir el método más adecuado según sus necesidades específicas y su entorno de trabajo:
- La fórmula utiliza fórmulas de matrices dinámicas en Excel 365 para generar resultados al instante, sin necesidad de programación, lo que la convierte en la opción perfecta para usuarios ligeros que prefieren soluciones basadas en fórmulas nativas.
- Power Query ofrece un proceso claro y reutilizable para gestionar grandes conjuntos de datos, lo que lo convierte en la opción ideal para la limpieza de datos y la generación automatizada de informes.
- El método Tabla Dinámica puede ser menos directo, pero resulta muy eficaz e intuitivo para un público familiarizado.
- La función definida por el usuario en VBA ofrece el máximo nivel de personalización y es ideal para escenarios que requieren integración en código de macros complejo.
Las mejores herramientas de productividad para Office
Potencie sus habilidades en Excel con Kutools para Excel y experimente una eficiencia como nunca antes.Kutools para Excel ofrece más de 300 funciones avanzadas para aumentar su productividad y Ahorrar tiempo.Haga clic aquí para obtener la función que más necesita...
Office Tab aporta una interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil
- Active la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abra y cree varios documentos en nuevas pestañas dentro de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumente su productividad en un 50 % y elimine cientos de clics del ratón cada día!
Todos los complementos de Kutools en un solo instalador.
Kutools for Office es la suite que incluye complementos para Excel, Word, Outlook y PowerPoint, además de Office Tab Pro, ideal para equipos que trabajan en distintas aplicaciones de Office.
- Suite integral— complementos para Excel, Word, Outlook y PowerPoint + Office Tab Pro
- Un instalador, una licencia— configuración en minutos (compatible con MSI)
- Rendimiento mejorado en conjunto— productividad optimizada en todas las aplicaciones de Office
- Prueba gratuita de 30 días con todas las funciones— sin registro ni tarjeta de crédito
- La mejor relación calidad-precio— ahorre frente a la compra individual de complementos