Di recente, un mio amico si è rivolto a me con una richiesta di aiuto nella generazione di tutte le possibili frasi costituite da un insieme di parole date. Problemi di questo tipo possono sorgere quando si compilano elenchi di parole chiave e frasi per la pubblicità online e la promozione SEO, quando è necessario passare attraverso tutte le possibili permutazioni di parole in una query di ricerca:
In matematica, questa operazione è chiamata prodotto cartesiano. La definizione ufficiale è la seguente: il prodotto cartesiano degli insiemi A e B è l'insieme di tutte le coppie, la cui prima componente appartiene all'insieme A, e la seconda componente appartiene all'insieme B. Inoltre, gli elementi degli insiemi possono essere entrambi numeri e testo.
Tradotto in linguaggio umano, questo significa che se nell'insieme A abbiamo, ad esempio, le parole “bianco” e “rosso”, e nell'insieme B “BMW” e “Mercedes”, allora dopo il prodotto cartesiano di questi due insiemi abbiamo get on the output è l'insieme di tutte le possibili varianti di frasi, composto dalle parole di entrambe le liste:
- Bmw bianca
- Bmw rossa
- Mercedes bianca
- mercedes rossi
... cioè proprio quello di cui abbiamo bisogno. Diamo un'occhiata a un paio di modi per risolvere questo compito in Excel.
Metodo 1. Formule
Cominciamo con le formule. Assumiamo che come dati iniziali abbiamo tre elenchi di parole originali nelle colonne A, B e C, rispettivamente, e il numero di elementi in ogni elenco può variare:
Per prima cosa, creiamo tre colonne con indici, cioè numeri ordinali di parole da ciascuna lista in tutte le possibili combinazioni. La prima riga di unità (E2:G2) verrà inserita manualmente e per il resto utilizzeremo la seguente formula:
La logica qui è semplice: se l'indice nella cella precedente superiore ha già raggiunto la fine della lista, cioè è uguale al numero di elementi della lista calcolato dalla funzione COUNT (CONTA), quindi riavviamo la numerazione. Altrimenti, aumentiamo l'indice di 1. Prestare particolare attenzione alla correzione intelligente degli intervalli con il simbolo del dollaro ($) in modo da poter copiare la formula in basso ea destra.
Ora che abbiamo i numeri ordinali delle parole di cui abbiamo bisogno da ciascuna lista, possiamo estrarre le parole stesse usando la funzione INDICE (INDICE) in tre colonne separate:
Se non hai mai riscontrato questa funzione nel tuo lavoro, ti consiglio vivamente di studiarla almeno in diagonale: aiuta in molte situazioni ed è utile non meno (e anche di più!) VPR (CERCA.VERT).
Bene, dopodiché, non resta che incollare i frammenti risultanti riga per riga usando il simbolo di concatenazione (&):
... oppure (se hai l'ultima versione di Excel) con la pratica funzione COMBINE (TESTOUNISCI), che può incollare l'intero contenuto delle celle specificate tramite un determinato carattere separatore (spazio):
Metodo 2. Tramite Power Query
Power Query è un potente componente aggiuntivo per Microsoft Excel che esegue due attività principali: 1. caricamento di dati in Excel da quasi tutte le origini esterne e 2. tutti i tipi di trasformazioni di tabelle caricate. Power Query è già integrato in Excel 2016-2019 e per Excel 2010-2013 è installato come componente aggiuntivo separato (puoi scaricarlo gratuitamente dal sito Web ufficiale di Microsoft). Se non hai ancora iniziato a utilizzare Power Query nel tuo lavoro, allora è il momento di pensarci, perché trasformazioni come quelle descritte sopra vengono eseguite lì in modo semplice e naturale, in un paio di movimenti.
Innanzitutto, carichiamo gli elenchi di origine come query separate in Power Query. A tale scopo, per ogni tabella, eseguire i seguenti passaggi:
- Trasformiamo i tavoli in quelli “intelligenti” con un pulsante Formatta come una tabella linguetta Casa (Home — Formatta come tabella) o scorciatoia da tastiera Ctrl+T. Ad ogni tabella verrà assegnato automaticamente un nome Tabella1,2,3…, che, tuttavia, può essere modificato se lo si desidera nella scheda Costruttore (Design).
- Dopo aver impostato la cella attiva nella tabella, premere il pulsante Dal tavolo (Dalla tabella) linguetta Dati (Data) o nella scheda Power Query (se è installato come componente aggiuntivo separato per Excel 2010-2013).
- Nella finestra dell'editor di query che si apre, seleziona il comando Home — Chiudi e carica — Chiudi e carica in... (Home — Chiudi&Carica — Chiudi&Carica in..) e poi l'opzione Basta creare una connessione (Crea solo connessione). Ciò lascerà la tabella caricata in memoria e ne consentirà l'accesso in futuro.
Se fai tutto correttamente, l'output nel pannello di destra dovrebbe essere tre richieste nella modalità Solo connessione con i nomi dei nostri tavoli:
Ora fai clic con il tasto destro sulla prima query e seleziona il comando Link (Riferimento)per farne una copia aggiornabile, quindi aggiungere una colonna aggiuntiva ai dati tramite il comando Aggiunta di una colonna ¾ – Colonna personalizzata (Aggiungi colonna -¾ Colonna personalizzata). Nella finestra di immissione della formula, inserisci il nome della nuova colonna (ad esempio, Frammento2) e un'espressione estremamente semplice come formula:
=Tabella2
… cioè, in altre parole, il nome della seconda query:
Dopo aver fatto clic su OK vedremo una nuova colonna, in ogni cella di cui ci sarà una tabella nidificata con le frasi della seconda tabella (puoi vedere il contenuto di queste tabelle se fai clic sullo sfondo della cella accanto alla parola Table):
Resta da espandere tutto il contenuto di queste tabelle nidificate utilizzando il pulsante con le doppie frecce nell'intestazione della colonna risultante e deselezionando Usa il nome della colonna originale come prefisso (Utilizza il nome della colonna originale come prefisso):
… e otteniamo tutte le possibili combinazioni di elementi dai primi due insiemi:
Inoltre, tutto è simile. Aggiungi un'altra colonna calcolata con la formula:
=Tabella3
…, quindi espandi nuovamente le tabelle nidificate – e ora abbiamo già tutte le possibili opzioni per permutare le parole rispettivamente dai tre set:
Resta da selezionare tutte e tre le colonne da sinistra a destra, tenendo premuto Ctrle concatenarne i contenuti separati da spazi usando il comando Unisci colonne (Unisci colonne) dalla scheda Trasformazione (Trasformare):
I risultati risultanti possono essere scaricati nuovamente sul foglio con il già noto comando Home — Chiudi e carica — Chiudi e carica in... (Home — Chiudi&Carica — Chiudi&Carica in..):
Se in futuro qualcosa cambia nelle nostre tabelle sorgenti con frammenti, allora basterà semplicemente aggiornare la query generata cliccando con il tasto destro sulla tabella risultante e scegliendo il comando Aggiorna e salva (Ricaricare) o premendo la scorciatoia da tastiera Ctrl+altro+F5.
- Che cos'è Power Query, Power Pivot, Power Map e Power BI e perché hanno bisogno di un utente di Excel
- Creazione di un diagramma di Gantt in Power Query
- 5 modi per utilizzare la funzione INDICE