Recentemente, un amigo meu achegouse a min para pedirme axuda na xeración de todas as frases posibles que consistan nun conxunto de palabras dadas. Problemas deste tipo poden xurdir ao compilar listas de palabras clave e frases para a publicidade en liña e a promoción de SEO, cando hai que pasar por todas as posibles permutacións de palabras nunha consulta de busca:
En matemáticas, esta operación chámase Produto cartesiano. A definición oficial é a seguinte: o produto cartesiano dos conxuntos A e B é o conxunto de todos os pares, cuxa primeira compoñente pertence ao conxunto A e a segunda compoñente pertence ao conxunto B. Ademais, os elementos dos conxuntos poden ser ambos. números e texto.
Traducido á linguaxe humana, isto significa que se no conxunto A temos, por exemplo, as palabras "branco" e "vermello", e no conxunto B "BMW" e "Mercedes", despois do produto cartesiano destes dous conxuntos get on the output é o conxunto de todas as posibles variantes de frases, composto polas palabras de ambas as listas:
- bmw branco
- bmw vermello
- Mercedes branco
- mercedes vermellas
... é dicir, só o que necesitamos. Vexamos un par de formas de resolver esta tarefa en Excel.
Método 1. Fórmulas
Comecemos coas fórmulas. Supoñamos que como datos iniciais temos tres listas de palabras orixinais nas columnas A, B e C, respectivamente, e o número de elementos de cada lista pode variar:
En primeiro lugar, imos facer tres columnas con índices, é dicir, números ordinais de palabras de cada lista en todas as combinacións posibles. A primeira fila de unidades (E2:G2) introducirase manualmente, e para o resto usaremos a seguinte fórmula:
A lóxica aquí é sinxela: se o índice da cela superior anterior xa chegou ao final da lista, é dicir, é igual ao número de elementos da lista calculado pola función COUNT (CONTA), entón reiniciamos a numeración. En caso contrario, aumentamos o índice en 1. Preste especial atención á fixación intelixente dos rangos con signos de dólar ($) para que poida copiar a fórmula cara abaixo e cara á dereita.
Agora que temos os números ordinais das palabras que necesitamos de cada lista, podemos extraer as propias palabras mediante a función Index (ÍNDICE) en tres columnas separadas:
Se non atopaches esta función no teu traballo antes, recoméndoche encarecidamente que a estudes polo menos en diagonal: axuda en moitas situacións e é útil nada menos (e aínda máis!) VPR (BÚSQUEDA V).
Ben, despois diso, só queda pegar os fragmentos resultantes liña por liña usando o símbolo de concatenación (&):
… ou (se tes a última versión de Excel) coa práctica función COMBINAR (UNIÓN DE TEXTO), que pode pegar todo o contido das celas especificadas a través dun carácter separador dado (espazo):
Método 2. A través de Power Query
Power Query é un poderoso complemento para Microsoft Excel que realiza dúas tarefas principais: 1. cargar datos en Excel desde case calquera fonte externa e 2. todo tipo de transformacións de táboas cargadas. Power Query xa está integrado en Excel 2016-2019, e para Excel 2010-2013 instálase como un complemento separado (podes descargalo desde o sitio web oficial de Microsoft gratuitamente). Se aínda non comezaches a usar Power Query no teu traballo, é hora de pensar niso, porque transformacións como as descritas anteriormente realízanse alí de xeito sinxelo e natural, en só un par de movementos.
En primeiro lugar, carguemos as listas de orixe como consultas separadas en Power Query. Para iso, para cada táboa, siga os seguintes pasos:
- Imos converter as táboas en "intelixentes" cun botón Formato como táboa aba casa (Inicio — Formato como táboa) ou atallo de teclado Ctrl+T. Cada táboa recibirá automaticamente un nome Táboa 1,2,3, XNUMX, XNUMX..., que, con todo, se pode cambiar se o desexa na pestana Construtor (Deseño).
- Despois de establecer a cela activa na táboa, prema o botón Da mesa (Da táboa) aba Data (Data) ou na pestana Consulta de enerxía (se o ten instalado como complemento separado para Excel 2010-2013).
- Na xanela do editor de consultas que se abre, seleccione o comando Inicio — Pechar e cargar — Pechar e cargar en… (Inicio — Pechar e cargar — Pechar e cargar en..) e despois a opción Só ten que crear unha conexión (Crear só conexión). Isto deixará a táboa cargada na memoria e permitirá acceder a ela no futuro.
Se fai todo correctamente, a saída no panel dereito debería ser tres solicitudes no modo Só conexión cos nomes das nosas táboas:
Agora fai clic co botón dereito na primeira consulta e selecciona o comando ligazón (Referencia)para facer unha copia actualizable do mesmo e despois engadir unha columna adicional aos datos mediante o comando Engadir unha columna ž - Columna personalizada (Engadir columna -ž Columna personalizada). Na xanela de entrada de fórmulas, introduza o nome da nova columna (por exemplo, Fragmento2) e unha expresión moi sinxela como fórmula:
= Táboa 2
... é dicir, noutras palabras, o nome da segunda consulta:
Despois de premer OK veremos unha nova columna, en cada cela da cal haberá unha táboa aniñada con frases da segunda táboa (podes ver o contido destas táboas se fai clic no fondo da cela xunto á palabra). Táboa):
Queda por expandir todo o contido destas táboas aniñadas usando o botón con dobres frechas na cabeceira da columna resultante e desmarcando Use o nome orixinal da columna como prefixo (Utiliza o nome da columna orixinal como prefixo):
... e obtemos todas as combinacións posibles de elementos dos dous primeiros conxuntos:
Ademais, todo é semellante. Engade outra columna calculada coa fórmula:
= Táboa 3
..., e despois expandir as táboas aniñadas de novo, e agora xa temos todas as opcións posibles para permutar palabras dos tres conxuntos, respectivamente:
Queda por seleccionar as tres columnas de esquerda a dereita, mantendo presionado Ctrl, e concatena os seus contidos separados por espazos mediante o comando Combina columnas (Combinar columnas) dende a pestana Transformación (Transformar):
Os resultados resultantes pódense descargar de novo na folla co comando xa coñecido Inicio — Pechar e cargar — Pechar e cargar en… (Inicio — Pechar e cargar — Pechar e cargar en..):
Se no futuro algo cambia nas nosas táboas de orixe con fragmentos, bastará con actualizar a consulta xerada facendo clic co botón dereito sobre a táboa resultante e escollendo o comando Actualizar e gardar (Actualizar) ou premendo o atallo do teclado Ctrl+alt+F5.
- Que é Power Query, Power Pivot, Power Map e Power BI e por que necesitan un usuario de Excel
- Crear un diagrama de Gantt en Power Query
- 5 formas de usar a función INDEX