Recientemente, un amigo mío se acercó a mí con una solicitud de ayuda con la generación de todas las frases posibles que consisten en un conjunto de palabras dadas. Pueden surgir problemas de este tipo al compilar listas de palabras clave y frases para la publicidad en línea y la promoción de SEO, cuando necesita pasar por todas las posibles permutaciones de palabras en una consulta de búsqueda:
En matemáticas, esta operación se llama producto cartesiano. La definición oficial es la siguiente: el producto cartesiano de los conjuntos A y B es el conjunto de todos los pares, cuyo primer componente pertenece al conjunto A y el segundo componente pertenece al conjunto B. Además, los elementos de los conjuntos pueden ser tanto números y texto.
Traducido al lenguaje humano, esto significa que si en el conjunto A tenemos, por ejemplo, las palabras "blanco" y "rojo", y en el conjunto B "BMW" y "Mercedes", luego del producto cartesiano de estos dos conjuntos tenemos get on the output es el conjunto de todas las posibles variantes de frases, formado por las palabras de ambas listas:
- bmw blanco
- bmw rojo
- mercedes blanco
- mercedes rojo
… es decir, justo lo que necesitamos. Veamos un par de formas de resolver esta tarea en Excel.
Método 1. Fórmulas
Comencemos con las fórmulas. Supongamos que como dato inicial tenemos tres listas de palabras originales en las columnas A, B y C, respectivamente, y el número de elementos en cada lista puede variar:
Primero, hagamos tres columnas con índices, es decir, números ordinales de palabras de cada lista en todas las combinaciones posibles. La primera fila de unidades (E2:G2) se ingresará manualmente, y para el resto usaremos la siguiente fórmula:
La lógica aquí es simple: si el índice en la celda anterior superior ya ha llegado al final de la lista, es decir, es igual al número de elementos en la lista calculado por la función COUNT (CONTARA), luego reiniciamos la numeración. De lo contrario, aumentamos el índice en 1. Preste especial atención a la inteligente fijación de los rangos con signos de dólar ($) para que pueda copiar la fórmula hacia abajo y hacia la derecha.
Ahora que tenemos los números ordinales de las palabras que necesitamos de cada lista, podemos extraer las palabras mismas usando la función ÍNDICE (ÍNDICE) en tres columnas separadas:
Si no ha encontrado esta función en su trabajo antes, le recomiendo encarecidamente que la estudie al menos en diagonal: ayuda en muchas situaciones y es útil no menos (¡y aún más!) VPR (VLOOKUP).
Bueno, después de eso, solo queda pegar los fragmentos resultantes línea por línea usando el símbolo de concatenación (&):
… o (si tiene la última versión de Excel) con la práctica función COMBINAR (ENTRAR POR TEXTO), que puede pegar todo el contenido de las celdas especificadas a través de un carácter separador dado (espacio):
Método 2. A través de Power Query
Power Query es un potente complemento para Microsoft Excel que realiza dos tareas principales: 1. cargar datos en Excel desde casi cualquier fuente externa y 2. todo tipo de transformaciones de tablas cargadas. Power Query ya está integrado en Excel 2016-2019, y para Excel 2010-2013 se instala como un complemento independiente (puede descargarlo del sitio web oficial de Microsoft de forma gratuita). Si aún no has empezado a utilizar Power Query en tu trabajo, entonces es hora de que lo pienses, porque transformaciones como las descritas anteriormente se realizan allí de forma fácil y natural, en tan solo un par de movimientos.
Primero, carguemos las listas de fuentes como consultas separadas en Power Query. Para ello, para cada tabla, realice los siguientes pasos:
- Convirtamos las mesas en mesas "inteligentes" con un botón Formatear como una tabla de la pestaña. Inicio (Inicio — Formatear como tabla) o atajo de teclado Ctrl+T. A cada mesa se le asignará automáticamente un nombre. Tabla1,2,3…, que, sin embargo, se puede cambiar si se desea en la pestaña Constructor (Diseño).
- Habiendo establecido la celda activa en la tabla, presione el botón De la mesa (De la mesa) de la pestaña. Datos (Fecha) o en la pestaña Power Query (si lo tiene instalado como un complemento separado para Excel 2010-2013).
- En la ventana del editor de consultas que se abre, seleccione el comando Inicio — Cerrar y cargar — Cerrar y cargar en… (Inicio — Cerrar y cargar — Cerrar y cargar a...) y luego la opcion Solo crea una conexión (Crear solo conexión). Esto dejará la tabla cargada en la memoria y permitirá acceder a ella en el futuro.
Si hace todo correctamente, la salida en el panel derecho debería ser tres solicitudes en el modo Solo conexión con nuestros nombres de tabla:
Ahora haga clic derecho en la primera consulta y seleccione el comando Enlace (Referencia)para hacer una copia actualizable y luego agregar una columna adicional a los datos a través del comando Agregar una columna ¾ – Columna personalizada (Agregar columna - ¼ de columna personalizada). En la ventana de entrada de fórmula, ingrese el nombre de la nueva columna (por ejemplo, Fragmento2) y una expresión extremadamente simple como fórmula:
=Tabla2
… es decir, en otras palabras, el nombre de la segunda consulta:
Después de hacer clic en OK veremos una nueva columna, en cada celda de la cual habrá una tabla anidada con frases de la segunda tabla (puede ver el contenido de estas tablas si hace clic en el fondo de la celda junto a la palabra Mesa):
Queda por expandir todo el contenido de estas tablas anidadas usando el botón con flechas dobles en el encabezado de la columna resultante y desmarcando Utilice el nombre de la columna original como prefijo (Utilice el nombre de la columna original como prefijo):
… y obtenemos todas las combinaciones posibles de elementos de los dos primeros conjuntos:
Además, todo es similar. Agregue otra columna calculada con la fórmula:
=Tabla3
…, y luego expanda las tablas anidadas nuevamente, y ahora ya tenemos todas las opciones posibles para permutar palabras de los tres conjuntos, respectivamente:
Queda por seleccionar las tres columnas de izquierda a derecha, sosteniendo Ctrly concatenar sus contenidos separados por espacios usando el comando Fusionar columnas (Fusionar columnas) de la pestaña (Transformar):
Los resultados resultantes se pueden volver a descargar en la hoja con el comando ya familiar Inicio — Cerrar y cargar — Cerrar y cargar en… (Inicio — Cerrar y cargar — Cerrar y cargar a...):
Si en el futuro algo cambia en nuestras tablas de origen con fragmentos, bastará con actualizar la consulta generada haciendo clic derecho en la tabla resultante y eligiendo el comando Actualizar y guardar (Actualizar) o presionando el atajo de teclado Ctrl+otro+F5.
- Qué es Power Query, Power Pivot, Power Map y Power BI y por qué necesitan un usuario de Excel
- Crear un diagrama de Gantt en Power Query
- 5 formas de usar la función ÍNDICE