Recentment, un amic meu es va acostar a mi amb una sol·licitud d'ajuda amb la generació de totes les frases possibles consistents en un conjunt de paraules donades. Problemes d'aquest tipus poden sorgir a l'hora d'elaborar llistes de paraules clau i frases per a la publicitat en línia i la promoció SEO, quan cal passar per totes les possibles permutacions de paraules en una consulta de cerca:
En matemàtiques, aquesta operació s'anomena Producte cartesià. La definició oficial és la següent: el producte cartesià dels conjunts A i B és el conjunt de tots els parells, el primer component dels quals pertany al conjunt A i el segon component pertany al conjunt B. A més, els elements dels conjunts poden ser tots dos. números i text.
Traduït al llenguatge humà, això vol dir que si al conjunt A tenim, per exemple, les paraules "blanc" i "vermell", i al conjunt B "BMW" i "Mercedes", llavors després del producte cartesià d'aquests dos conjunts get on the output és el conjunt de totes les variants possibles de frases, format per les paraules d'ambdues llistes:
- bmw blanc
- bmw vermell
- Mercedes blanc
- mercedes vermelles
... és a dir, just el que necessitem. Vegem un parell de maneres de resoldre aquesta tasca a Excel.
Mètode 1. Fórmules
Comencem amb les fórmules. Suposem que com a dades inicials tenim tres llistes de paraules originals a les columnes A, B i C, respectivament, i el nombre d'elements de cada llista pot variar:
Primer, fem tres columnes amb índexs, és a dir, nombres ordinals de paraules de cada llista en totes les combinacions possibles. La primera fila d'unitats (E2:G2) s'introduirà manualment, i per a la resta utilitzarem la fórmula següent:
La lògica aquí és senzilla: si l'índex de la cel·la superior anterior ja ha arribat al final de la llista, és a dir, és igual al nombre d'elements de la llista calculat per la funció COUNT (COUNTA), després reiniciem la numeració. En cas contrari, augmentarem l'índex en 1. Presteu especial atenció a la fixació intel·ligent dels rangs amb signes de dòlar ($) perquè pugueu copiar la fórmula cap avall i cap a la dreta.
Ara que tenim els nombres ordinals de les paraules que necessitem de cada llista, podem extreure les mateixes paraules mitjançant la funció ÍNDEX (ÍNDEX) en tres columnes separades:
Si no heu trobat aquesta funció abans al vostre treball, us recomano que l'estudieu almenys en diagonal: ajuda en moltes situacions i és útil no menys (i fins i tot més!) VPR (VISUALITZACIÓ).
Bé, després d'això, només queda enganxar els fragments resultants línia per línia utilitzant el símbol de concatenació (&):
... o (si teniu l'última versió d'Excel) amb la pràctica funció COMBINA (TEXTJOIN), que pot enganxar tot el contingut de les cel·les especificades mitjançant un caràcter separador determinat (espai):
Mètode 2. Mitjançant Power Query
Power Query és un potent complement per a Microsoft Excel que realitza dues tasques principals: 1. carregar dades a Excel des de gairebé qualsevol font externa, i 2. tot tipus de transformacions de taules carregades. Power Query ja està integrat a Excel 2016-2019, i per a Excel 2010-2013 s'instal·la com a complement independent (pots descarregar-lo gratuïtament des del lloc web oficial de Microsoft). Si encara no heu començat a utilitzar Power Query en el vostre treball, és hora de pensar-hi, perquè transformacions com les descrites anteriorment es fan allà de manera fàcil i natural, en només un parell de moviments.
Primer, carreguem les llistes d'origen com a consultes separades a Power Query. Per fer-ho, per a cada taula, seguiu els passos següents:
- Convertim les taules en "intel·ligents" amb un botó Format com a taula llengüeta Home (Inici — Format com a taula) o drecera de teclat Ctrl+T. Cada taula rebrà automàticament un nom Taula 1,2,3..., que, però, es pot canviar si es desitja a la pestanya constructor (Disseny).
- Després d'haver establert la cel·la activa a la taula, premeu el botó De la taula (De la taula) llengüeta dades (Data) o a la pestanya Power Consulta (si el teniu instal·lat com a complement independent per a Excel 2010-2013).
- A la finestra de l'editor de consultes que s'obre, seleccioneu l'ordre Inici — Tancar i carregar — Tancar i carregar a... (Inici — Tancar i carregar — Tancar i carregar a..) i després l'opció Només cal crear una connexió (Crear només connexió). Això deixarà la taula carregada a la memòria i permetrà accedir-hi en el futur.
Si ho feu tot correctament, la sortida al tauler dret hauria de ser tres sol·licituds en el mode Només connexió amb els nostres noms de taula:
Ara feu clic amb el botó dret a la primera consulta i seleccioneu l'ordre Enllaç (Referència)per fer-ne una còpia actualitzable i, a continuació, afegir una columna addicional a les dades mitjançant l'ordre Afegir una columna ž - Columna personalitzada (Afegeix una columna -ž Columna personalitzada). A la finestra d'entrada de la fórmula, introduïu el nom de la nova columna (per exemple, Fragment2) i una expressió extremadament senzilla com a fórmula:
= Taula 2
... és a dir, en altres paraules, el nom de la segona consulta:
Després de fer clic OK veurem una nova columna, a cada cel·la de la qual hi haurà una taula imbricada amb frases de la segona taula (podeu veure el contingut d'aquestes taules si feu clic al fons de la cel·la al costat de la paraula). Taula):
Queda per ampliar tot el contingut d'aquestes taules imbricades utilitzant el botó amb fletxes dobles a la capçalera de la columna resultant i desmarcant Utilitzeu el nom de la columna original com a prefix (Utilitzeu el nom de la columna original com a prefix):
... i obtenim totes les combinacions possibles d'elements dels dos primers conjunts:
A més, tot és semblant. Afegeix una altra columna calculada amb la fórmula:
= Taula 3
..., i després torneu a expandir les taules imbricades, i ara ja tenim totes les opcions possibles per permutar paraules dels tres conjunts, respectivament:
Queda per seleccionar les tres columnes d'esquerra a dreta, mantenint premudes Ctrl, i concatenar els seus continguts separats per espais mitjançant l'ordre Combina columnes (Combina les columnes) de la pestanya Transformació (Transformar):
Els resultats resultants es poden tornar a descarregar al full amb l'ordre ja familiar Inici — Tancar i carregar — Tancar i carregar a... (Inici — Tancar i carregar — Tancar i carregar a..):
Si en el futur alguna cosa canvia a les nostres taules font amb fragments, n'hi haurà prou amb actualitzar la consulta generada fent clic amb el botó dret a la taula resultant i escollint l'ordre. Actualitza i desa (Actualització) o prement la drecera del teclat Ctrl+Alt+F5.
- Què és Power Query, Power Pivot, Power Map i Power BI i per què necessiten un usuari d'Excel
- Creació d'un diagrama de Gantt a Power Query
- 5 maneres d'utilitzar la funció INDEX