Cerca paraules clau al text

Cercar paraules clau al text font és una de les tasques més habituals quan es treballa amb dades. Vegem la seva solució de diverses maneres utilitzant l'exemple següent:

Suposem que tu i jo tenim una llista de paraules clau (els noms de les marques de cotxes) i una gran taula de tot tipus de recanvis, on les descripcions de vegades poden contenir una o diverses marques d'aquest tipus alhora, si la peça de recanvi s'adapta a més d'una. marca de cotxe. La nostra tasca és trobar i mostrar totes les paraules clau detectades a les cel·les veïnes mitjançant un caràcter separador determinat (per exemple, una coma).

Mètode 1. Power Query

Per descomptat, primer convertim les nostres taules en dinàmiques ("intel·ligents") mitjançant una drecera de teclat Ctrl+T o ordres Inici – Format com a taula (Inici — Format com a taula), doneu-los noms (per exemple Segellsи Refacciones) i carregueu un per un a l'editor de Power Query seleccionant a la pestanya Dades: de la taula/interval (Dades: de la taula/interval). Si teniu versions anteriors d'Excel 2010-2013, on Power Query està instal·lat com a complement independent, el botó desitjat estarà a la pestanya Power Consulta. Si teniu una versió nova d'Excel 365, el botó De Taula/Range trucada allà ara Amb fulles (Del full).

Després de carregar cada taula a Power Query, tornem a Excel amb l'ordre Inici — Tancar i carregar — Tancar i carregar a... — Només per crear connexió (Inici — Tancar i carregar — Tancar i carregar a... — Només crea connexió).

Ara creem una sol·licitud duplicada Refaccionesfent clic amb el botó dret sobre ell i seleccionant Sol·licitud duplicada (Consulta duplicada)i, a continuació, canvieu el nom de la sol·licitud de còpia resultant a Els resultats i seguirem treballant amb ell.

La lògica de les accions és la següent:

1. A la pestanya Avançat Afegint una columna triar un equip Columna personalitzada (Afegeix columna: columna personalitzada) i introduïu la fórmula = Marques. Després de fer clic a OK obtindrem una nova columna, on a cada cel·la hi haurà una taula imbricada amb una llista de les nostres paraules clau: marques de fabricants d'automòbils:

   

2. Utilitzeu el botó amb fletxes dobles a la capçalera de la columna afegida per ampliar totes les taules imbricades. Al mateix temps, les línies amb descripcions de recanvis es multiplicaran per un múltiple del nombre de marques, i obtindrem tots els parells-combinacions possibles de "recanvis-marca":

   

3. A la pestanya Avançat Afegint una columna triar un equip Columna condicional (Columna condicional) i establiu una condició per comprovar l'aparició d'una paraula clau (marca) al text font (descripció de la part):

   

4. Per fer que la cerca no distingeix entre majúscules i minúscules, afegiu manualment el tercer argument a la barra de fórmules Compare.OrdinalIgnoreCase a la funció de verificació d'ocurrències Text.Conté (si la barra de fórmules no és visible, es pot activar a la pestanya Crítica):

   

5. Filtrem la taula resultant, deixant només les a l'última columna, és a dir, coincidències i eliminem la columna innecessària Ocurrències.
6. Agrupant descripcions idèntiques amb l'ordre Agrupar per llengüeta Transformació (Transforma — Agrupa per). Com a operació d'agregació, trieu Totes les línies (Totes les files). A la sortida, obtenim una columna amb taules, que conté tots els detalls de cada recanvi, incloses les marques dels fabricants d'automòbils que necessitem:

   

7. Per extreure les qualificacions de cada part, afegiu una altra columna calculada a la pestanya Afegir una columna: columna personalitzada (Afegeix columna: columna personalitzada) i utilitzeu una fórmula que consta d'una taula (es troben a la nostra columna Detalls) i el nom de la columna extreta:

   

8. Fem clic al botó amb fletxes dobles a la capçalera de la columna resultant i seleccionem l'ordre Extreu valors (Extreu valors)per sortir segells amb qualsevol caràcter delimitador que vulgueu:

   

9. Eliminació d'una columna innecessària Detalls.
10. Per afegir a la taula resultant les parts que n'han desaparegut, on no s'han trobat marques a les descripcions, realitzem el procediment per combinar la consulta. Resultat amb petició original Refacciones botó Combinar llengüeta Home (Inici — Combina les consultes). Tipus de connexió - Unió exterior dreta (Unió exterior dreta):

    

11. Només queda eliminar les columnes addicionals i canviar el nom i moure les restants, i la nostra tasca està resolta:

    
    

Mètode 2. Fórmules

Si teniu una versió d'Excel 2016 o posterior, el nostre problema es pot resoldre d'una manera molt compacta i elegant mitjançant la nova funció COMBINA (TEXTJOIN):

La lògica darrere d'aquesta fórmula és senzilla:

• function CERCAR (TROBAR) cerca l'aparició de cada marca al seu torn a la descripció actual de la peça i retorna el número de sèrie del símbol, a partir del qual s'ha trobat la marca, o bé l'error #VALOR! si la marca no està a la descripció.
• A continuació, utilitzeu la funció IF (SI) и EOSHIBKA (ISERROR) substituïm els errors per una cadena de text buida “”, i els números ordinals dels caràcters amb els mateixos noms de marca.
• La matriu resultant de cel·les buides i marques trobades s'agrupa en una sola cadena mitjançant un caràcter separador determinat mitjançant la funció COMBINA (TEXTJOIN).

Comparació de rendiment i memòria intermèdia de consultes de Power Query per accelerar

Per a les proves de rendiment, prenem una taula de 100 descripcions de peces de recanvi com a dades inicials. Sobre ell obtenim els següents resultats:

• Temps de recàlcul per fórmules (Mètode 2) – 9 seg. quan copieu per primera vegada la fórmula a tota la columna i 2 segons. en repetides (afectes d'amortiment, probablement).
  
• El temps d'actualització de la consulta de Power Query (Mètode 1) és molt pitjor: 110 segons.
  

Per descomptat, depèn molt del maquinari d'un ordinador concret i de la versió instal·lada d'Office i de les actualitzacions, però el panorama general, crec, és clar.

Per accelerar una consulta de Power Query, emmagatzemem la taula de cerca Segells, perquè no canvia en el procés d'execució de la consulta i no és necessari recalcular-lo constantment (com ho fa de facto Power Query). Per a això fem servir la funció Taula.Buffer des del llenguatge Power Query integrat M.

Per fer-ho, obriu una consulta Els resultats i a la pestanya Crítica prem el botó Editor avançat (Veure — Editor avançat). A la finestra que s'obre, afegiu una línia amb una nova variable Marc 2, que serà una versió de memòria intermèdia del nostre directori de fabricants d'automòbils, i utilitzeu aquesta nova variable més endavant a l'ordre de consulta següent:

Després d'aquest refinament, la velocitat d'actualització de la nostra sol·licitud augmenta gairebé 7 vegades, fins a 15 segons. Una cosa ben diferent 🙂

• Cerca de text difusa a Power Query
• Substitució de text massiu amb fórmules
• Substitució de text massiu a Power Query amb la funció List.Acumulate