cuntenutu
A ricerca di e parolle chjave in u testu fonte hè unu di i travaglii più cumuni quandu u travagliu cù dati. Fighjemu a so suluzione in parechje manere cù l'esempiu seguente:
Supponemu chì voi è eiu avemu una lista di parole chjave - i nomi di e marche di vitture - è una grande tavola di ogni tipu di pezzi di ricambio, induve e descrizioni ponu qualchì volta cuntene una o parechje marche di tali marche à una volta, se a parte di ricambio si adatta à più di una. marca di vittura. U nostru compitu hè di truvà è di vede tutte e parolle chjave rilevate in e cellule vicine attraversu un caratteru di separatore datu (per esempiu, una virgola).
Metudu 1. Power Query
Di sicuru, prima turnemu i nostri tavulini in dinamica ("intelligente") utilizendu una scurciatoia di tastiera Ctrl+T o cumandamenti Home - Formate cum'è una tavola (Casa - Formate cum'è Table), dà li nomi (per esempiu francobolliи Spare parts) è caricate unu per unu in l'editore Power Query, selezziunate nantu à a tabulazione Dati - Da Table / Range (Dati - Da Table/Range). Se avete versioni più vechje di Excel 2010-2013, induve Power Query hè stallatu cum'è un add-in separatu, allora u buttone desideratu serà nantu à a tabulazione. dumanda di putenza. Se tenete una nova versione di Excel 365, allora u buttone Da Table/Range chjamatu quì avà Cù foglie (Da u fogliu).
Dopu avè caricatu ogni tavula in Power Query, vultemu in Excel cù u cumandimu Casa - Chjude è carica - Chjama è carica à... - Crea una cunnessione solu (Casa - Close & Load - Close & Load to... - Crea solu cunnessione).
Avà criemu una dumanda duplicata Spare partsclicchendu u dirittu nantu à ellu è selezziunate Duplicate dumanda (Domanda duplicata), poi rinominate a dumanda di copia resultanti à i risultati è continueremu à travaglià cun ellu.
A logica di l'azzioni hè a seguente:
- In a tabulazione Avanzata Aghjunghjendu una colonna sceglie una squadra Colonna persunalizata (Aggiungi colonna - Colonna persunalizata) è entre in a formula = marche. Dopu à cliccà nantu OK averemu una nova colonna, induve in ogni cellula ci sarà una tavola nidificata cù una lista di e nostre parole chjave - marche di automaker:
- Aduprate u buttone cù doppia freccia in l'intestazione di a colonna aghjuntu per espansione tutte e tavule nidificate. À u listessu tempu, e linee cù descrizzioni di pezzi di ricambio si multiplica per un multiplu di u numeru di marche, è avemu da ottene tutte e pussibuli coppie-combinazioni di "spare part-marca":
- In a tabulazione Avanzata Aghjunghjendu una colonna sceglie una squadra Colonna cundizionale (colonna cundizionale) è stabilisce una cundizione per verificà l'occurrence di una keyword (marca) in u testu fonte (descrizzione parte):
- Per fà a ricerca insensibile à u casu, aghjunghje manualmente u terzu argumentu in a barra di formula Compare.OrdinalIgnoreCase à a funzione di verificazione di l'occurrence Testu.Contene (se a barra di formula ùn hè micca visibile, allora pò esse attivata nantu à a tabulazione recensione):
- Filtremu a tavula resultanti, lascendu solu in l'ultima colonna, vale à dì partite è sguassate a colonna innecessaria Avvenimenti.
- Agrupazione di descrizioni identiche cù u cumandamentu Gruppi da Insignia Mutazioni (Trasforma - Gruppu per). Cum'è una operazione di aggregazione, sceglite Tutte e linee (tutte i file). À l'output, avemu una colonna cù tavule, chì cuntene tutti i ditaglii per ogni pezza di ricambio, cumprese e marche di l'automobilisti chì avemu bisognu:
- Per estrae qualificazioni per ogni parte, aghjunghje una altra colonna calculata nantu à a tabulazione Aghjunghjite una Colonna - Colonna Personalizzata (Aggiungi colonna - Colonna persunalizata) è aduprate una furmula custituita da una tavula (si trovanu in a nostra colonna Details) è u nome di a colonna estratta:
- Cliccate nantu à u buttone cù freccia doppia in l'intestazione di a colonna resultanti è selezziunate u cumandamentu Estratti i valori (Extraire i valori)per fà stampi cù qualsiasi carattere delimitatore chì vulete:
- Eliminazione di una colonna inutile Details.
- Per aghjunghje à a tavola resultanti e parti chì sò sparite da ellu, induve ùn sò micca stati truvati marchi in e descrizioni, eseguimu a prucedura per cumminà a quistione. Result cù a dumanda originale Spare parts buttone Cumbattinu Insignia Home (Home — Cume e dumande). tipu di cunnessione - Uster Join Right (Unisce à l'esterno ghjustu):
- Tuttu ciò chì resta hè di caccià e culonne extra è rinominà-spostate i restanti - è u nostru compitu hè risoltu:
Metudu 2. Formule
Sè vo avete una versione di Excel 2016 o più tardi, allora u nostru prublema pò esse risolta in una manera assai compacta è elegante cù a nova funzione CUMBINA (TEXTJOIN):
A logica daretu à sta formula hè simplice:
- funzioni SEARCH (TROVA) cerca l'occurrence di ogni marca in turnu in a descrizzione attuale di a parte è torna sia u numeru di serie di u simbulu, partendu da quale a marca hè stata truvata, o l'errore #VALORE! se a marca ùn hè micca in a descrizzione.
- Dopu aduprà a funzione IF (SI) и EOSHIBKA (ISERROR) rimpiazzà l'errore cù una stringa di testu vacante "", è i numeri ordinali di i caratteri cù i nomi di marca stessi.
- L'array resultante di cellule viote è marche trovate hè assemblata in una sola stringa attraversu un caratteru di separatore datu utilizendu a funzione CUMBINA (TEXTJOIN).
Comparazione di prestazioni è Power Query Query Buffering per Speedup
Per a prova di rendiment, pigliamu una tavola di 100 descrizzioni di pezzi di ricambio cum'è dati iniziali. In questu, avemu i seguenti risultati:
- Tempu di recalculazione per formule (Metudu 2) - 9 sec. quandu prima copiate a formula à a colonna sana è 2 sec. à ripetutu (affetti di buffering, probabilmente).
- U tempu d'aghjurnamentu di a query Power Query (Metudu 1) hè assai peghju - 110 seconde.
Di sicuru, assai depende di u hardware di un PC particulari è a versione installata di Office è l'aghjurnamenti, ma a stampa generale, pensu, hè chjaru.
Per accelerà una query Power Query, bufferemu a tabella di ricerca francobolli, perchè ùn cambia micca in u prucessu di l'esekzione di a dumanda è ùn hè micca necessariu di ricalculate constantemente (cum'è Power Query de facto). Per questu avemu aduprà a funzione Table.Buffer da a lingua integrata Power Query M.
Per fà questu, apre una dumanda i risultati è nantu à a tabulazione recensione premi u buttone Editor avanzatu (Vede - Editor avanzatu). In a finestra chì apre, aghjunghje una linea cù una nova variabile Marcu 2, chì serà una versione buffered di u nostru repertoriu di l'automaker, è utilizate sta nova variabile più tardi in u cumandimu di quistione:
Dopu un tali raffinamentu, a veloce di l'aghjurnamentu di a nostra dumanda aumenta da quasi 7 volte - finu à 15 seconde. Una cosa assai diversa 🙂
- Ricerca di testu fuzzy in Power Query
- Sustituzione di testu in massa cù formule
- Sustituzione di testu in massa in Power Query cù a funzione List.Accumulate