Cerca parole chiave nel testo

La ricerca di parole chiave nel testo di origine è una delle attività più comuni quando si lavora con i dati. Diamo un'occhiata alla sua soluzione in diversi modi usando il seguente esempio:

Supponiamo che tu ed io abbiamo un elenco di parole chiave - i nomi delle marche di automobili - e una grande tabella di tutti i tipi di pezzi di ricambio, dove le descrizioni possono talvolta contenere uno o più di questi marchi contemporaneamente, se il pezzo di ricambio si adatta a più di un marca di auto. Il nostro compito è trovare e visualizzare tutte le parole chiave rilevate nelle celle vicine attraverso un determinato carattere separatore (ad esempio, una virgola).

Metodo 1. Query di alimentazione

Naturalmente, per prima cosa trasformiamo le nostre tabelle in dinamiche ("intelligenti") utilizzando una scorciatoia da tastiera Ctrl+T o comandi Home – Formatta come una tabella (Home — Formatta come tabella), assegna loro dei nomi (ad esempio Francobolliи Pezzi di ricambio) e caricare uno per uno nell'editor di Power Query selezionando nella scheda Dati: da tabella/intervallo (Dati — Da tabella/intervallo). Se disponi di versioni precedenti di Excel 2010-2013, in cui Power Query è installato come componente aggiuntivo separato, il pulsante desiderato sarà nella scheda Power Query. Se hai una nuova versione di Excel 365, quindi il pulsante Dalla tabella/intervallo chiamato lì ora Con foglie (Dal foglio).

Dopo aver caricato ogni tabella in Power Query, torniamo a Excel con il comando Home — Chiudi e carica — Chiudi e carica in... — Crea solo connessione (Home — Chiudi e carica — Chiudi e carica in… — Crea solo connessione).

Ora creiamo una richiesta duplicata Pezzi di ricambiofacendo clic destro su di esso e selezionando Richiesta duplicata (interrogazione duplicata), quindi rinomina la richiesta di copia risultante in I risultati e continueremo a lavorare con lui.

La logica delle azioni è la seguente:

1. Nella scheda Avanzate Aggiunta di una colonna scegli una squadra Colonna personalizzata (Aggiungi colonna — Colonna personalizzata) e inserire la formula = Marchi. Dopo aver cliccato su OK otterremo una nuova colonna, dove in ogni cella ci sarà una tabella nidificata con un elenco delle nostre parole chiave - marchi di case automobilistiche:

   

2. Utilizzare il pulsante con le doppie frecce nell'intestazione della colonna aggiunta per espandere tutte le tabelle nidificate. Allo stesso tempo, le righe con le descrizioni dei pezzi di ricambio si moltiplicheranno per un multiplo del numero di marchi e otterremo tutte le possibili coppie-combinazioni di "marca-pezzo di ricambio":

   

3. Nella scheda Avanzate Aggiunta di una colonna scegli una squadra Colonna condizionale (Colonna condizionale) e impostare una condizione per verificare l'occorrenza di una parola chiave (marca) nel testo di origine (descrizione della parte):

   

4. Per rendere la ricerca senza distinzione tra maiuscole e minuscole, aggiungi manualmente il terzo argomento nella barra della formula Confronta.OrdinalIgnoreCase alla funzione di verifica dell'occorrenza Testo.Contiene (se la barra della formula non è visibile, può essere abilitata nella scheda Review):

   

5. Filtriamo la tabella risultante, lasciando solo quelle nell'ultima colonna, cioè le corrispondenze e rimuoviamo la colonna non necessaria occorrenze.
6. Raggruppamento di descrizioni identiche con il comando Raggruppa per linguetta Trasformazione (Trasforma — Raggruppa per). Come operazione di aggregazione, scegli Tutte le linee (Tutte le righe). In uscita, otteniamo una colonna con tabelle, che contiene tutti i dettagli per ogni pezzo di ricambio, comprese le marche di case automobilistiche di cui abbiamo bisogno:

   

7. Per estrarre i voti per ogni parte, aggiungi un'altra colonna calcolata nella scheda Aggiunta di una colonna – Colonna personalizzata (Aggiungi colonna — Colonna personalizzata) e usa una formula composta da una tabella (si trovano nella nostra colonna Dettagli) e il nome della colonna estratta:

   

8. Facciamo clic sul pulsante con le doppie frecce nell'intestazione della colonna risultante e selezioniamo il comando Estrai valori (Estrai valori)per emettere timbri con qualsiasi carattere delimitatore desiderato:

   

9. Rimozione di una colonna non necessaria Dettagli.
10. Per aggiungere alla tabella risultante le parti scomparse da essa, dove non sono stati trovati marchi nelle descrizioni, eseguiamo la procedura per combinare la query Risultato con richiesta originale Pezzi di ricambio pulsante Combinare linguetta Casa (Home — Unisci query). Tipo di connessione - Giunto esterno a destra (giunto esterno destro):

    

11. Non resta che rimuovere le colonne extra e rinominare-spostare quelle rimanenti - e il nostro compito è risolto:

    
    

Metodo 2. Formule

Se hai una versione di Excel 2016 o successiva, allora il nostro problema può essere risolto in modo molto compatto ed elegante utilizzando la nuova funzione COMBINE (TESTOUNISCI):

La logica alla base di questa formula è semplice:

• Funzione RICERCA (TROVA) cerca a sua volta l'occorrenza di ogni marca nella descrizione corrente del pezzo e restituisce o il numero seriale del simbolo, a partire dal quale è stata trovata la marca, oppure l'errore #VALUE! se il marchio non è nella descrizione.
• Quindi utilizzando la funzione IF (SE) и EOSHIBKA (ERRORE) sostituiamo gli errori con una stringa di testo vuota “”, ei numeri ordinali dei caratteri con i nomi dei marchi stessi.
• L'array risultante di celle vuote e marchi trovati viene assemblato in una singola stringa attraverso un determinato carattere separatore utilizzando la funzione COMBINE (TESTOUNISCI).

Confronto delle prestazioni e buffering delle query di Power Query per l'accelerazione

Per il test delle prestazioni, prendiamo una tabella di 100 descrizioni di parti di ricambio come dati iniziali. Su di esso otteniamo i seguenti risultati:

• Tempo di ricalcolo per formule (Metodo 2) – 9 sec. quando copi per la prima volta la formula sull'intera colonna e 2 sec. a ripetuto (l'effetto tampone, probabilmente).
  
• Il tempo di aggiornamento della query Power Query (metodo 1) è molto peggiore: 110 secondi.
  

Certo, molto dipende dall'hardware di un determinato PC e dalla versione installata di Office e degli aggiornamenti, ma il quadro generale, credo, è chiaro.

Per velocizzare una query di Power Query, eseguiamo il buffer della tabella di ricerca Francobolli, perché non cambia nel processo di esecuzione della query e non è necessario ricalcolarlo costantemente (come fa di fatto Power Query). Per questo usiamo la funzione Tabella.Buffer dal linguaggio Power Query predefinito M.

Per fare ciò, apri una query I risultati e nella scheda Review premi il bottone Editor avanzato (Visualizza — Editor avanzato). Nella finestra che si apre, aggiungi una riga con una nuova variabile Marco 2, che sarà una versione bufferizzata della nostra directory del produttore di automobili, e utilizzerà questa nuova variabile più avanti nel seguente comando di query:

Dopo tale perfezionamento, la velocità di aggiornamento della nostra richiesta aumenta di quasi 7 volte, fino a 15 secondi. Tutt'altra cosa 🙂

• Ricerca di testo sfocato in Power Query
• Sostituzione di testo in blocco con formule
• Sostituzione di testo in blocco in Power Query con la funzione List.Accumulate