Contidos
Buscar palabras clave no texto fonte é unha das tarefas máis comúns cando se traballa con datos. Vexamos a súa solución de varias maneiras usando o seguinte exemplo:
Supoñamos que ti e eu temos unha lista de palabras clave (os nomes de marcas de coches) e unha gran táboa de todo tipo de pezas de recambio, onde as descricións ás veces poden conter unha ou varias marcas deste tipo á vez, se a peza de recambio cabe máis dunha. marca de coche. A nosa tarefa é atopar e mostrar todas as palabras clave detectadas nas celas veciñas mediante un carácter separador determinado (por exemplo, unha coma).
Método 1. Power Query
Por suposto, primeiro transformamos as nosas táboas en dinámicas ("intelixentes") usando un atallo de teclado Ctrl+T ou comandos Inicio - Formato como unha táboa (Inicio — Formato como táboa), ponlles nomes (por exemplo Selosи Pezas de reposición) e cargue un por un no editor de Power Query seleccionando na pestana Datos: da táboa/rango (Datos: da táboa/rango). Se tes versións anteriores de Excel 2010-2013, onde Power Query está instalado como un complemento separado, o botón desexado estará na pestana Consulta de enerxía. Se tes unha nova versión de Excel 365, entón o botón De Táboa/Range chamado alí agora Con follas (Da folla).
Despois de cargar cada táboa en Power Query, volvemos a Excel co comando Inicio — Pechar e cargar — Pechar e cargar en… — Só crear conexión (Inicio — Pechar e cargar — Pechar e cargar en… — Só crear conexión).
Agora imos crear unha solicitude duplicada Pezas de reposiciónfacendo clic co botón dereito sobre el e seleccionando Solicitude duplicada (Consulta duplicada)e, a continuación, renomee a solicitude de copia resultante a Os resultados e seguiremos traballando con el.
A lóxica das accións é a seguinte:
- Na pestana Avanzado Engadindo unha columna escolle un equipo Columna personalizada (Engadir columna — Columna personalizada) e introduza a fórmula = Marcas. Despois de facer clic en OK obteremos unha nova columna, onde en cada cela haberá unha táboa aniñada cunha lista das nosas palabras clave: marcas de fabricantes de automóbiles:
- Use o botón con frechas dobres na cabeceira da columna engadida para expandir todas as táboas aniñadas. Ao mesmo tempo, as liñas con descricións de pezas de recambio multiplicaranse por un múltiplo do número de marcas, e obteremos todos os pares-combinacións posibles de "recambio-marca":
- Na pestana Avanzado Engadindo unha columna escolle un equipo Columna condicional (Columna condicional) e establece unha condición para comprobar a aparición dunha palabra clave (marca) no texto fonte (descrición da parte):
- Para que a busca non distinga entre maiúsculas e minúsculas, engade manualmente o terceiro argumento na barra de fórmulas Comparar.OrdinalIgnoreCase á función de verificación de ocorrencias Texto.Contén (se a barra de fórmulas non está visible, pódese activar na pestana revisión):
- Filtramos a táboa resultante, deixando só unhas na última columna, é dicir, coincidencias e eliminamos a columna innecesaria Ocorrencias.
- Agrupación de descricións idénticas co comando Agrupar por aba Transformación (Transformar — Agrupar por). Como operación de agregación, escolla Todas as liñas (Todas as filas). Na saída, obtemos unha columna con táboas, que contén todos os detalles de cada peza de recambio, incluídas as marcas dos fabricantes de automóbiles que necesitamos:
- Para extraer as cualificacións de cada parte, engade outra columna calculada na pestana Engadir unha columna - Columna personalizada (Engadir columna — Columna personalizada) e use unha fórmula formada por unha táboa (están situadas na nosa columna detalles) e o nome da columna extraída:
- Facemos clic no botón con dobres frechas na cabeceira da columna resultante e seleccionamos o comando Extraer valores (Extraer valores)para emitir selos con calquera carácter delimitador que desexe:
- Eliminando unha columna innecesaria detalles.
- Para engadir á táboa resultante as partes que desapareceron da mesma, onde non se atoparon marcas nas descricións, realizamos o procedemento para combinar a consulta Resultado coa solicitude orixinal Pezas de reposición botón Combinar aba casa (Inicio — Combinar consultas). Tipo de conexión - Unión exterior dereita (Unión exterior dereita):
- Todo o que queda é eliminar as columnas adicionais e cambiar o nome e mover as restantes, e a nosa tarefa está resolta:
Método 2. Fórmulas
Se tes unha versión de Excel 2016 ou posterior, o noso problema pódese resolver dun xeito moi compacto e elegante usando a nova función COMBINAR (UNIÓN DE TEXTO):
A lóxica detrás desta fórmula é sinxela:
- función BUSCA (BUSCAR) busca á súa vez a aparición de cada marca na descrición actual da peza e devolve o número de serie do símbolo, a partir do cal se atopou a marca, ou o erro #VALOR! se a marca non está na descrición.
- Despois emprega a función IF (SI) и EOSHIBKA (ISERROR) substituímos os erros por unha cadea de texto baleira “”, e os números ordinais dos caracteres polos propios nomes de marca.
- A matriz resultante de celas baleiras e de marcas atopadas reúnese nunha única cadea a través dun carácter separador determinado mediante a función COMBINAR (UNIÓN DE TEXTO).
Comparación de rendemento e búfer de consultas de Power Query para acelerar
Para probas de rendemento, tomemos unha táboa de 100 descricións de pezas de reposición como datos iniciais. Nela obtemos os seguintes resultados:
- Tempo de recálculo por fórmulas (Método 2) – 9 seg. cando copias por primeira vez a fórmula a toda a columna e 2 seg. en repetidas (afecta a amortiguación, probablemente).
- O tempo de actualización da consulta de Power Query (Método 1) é moito peor: 110 segundos.
Por suposto, moito depende do hardware dun PC en particular e da versión instalada de Office e das actualizacións, pero o panorama xeral, creo, é claro.
Para acelerar unha consulta de Power Query, imos almacenar na táboa de busca Selos, porque non cambia no proceso de execución da consulta e non é necesario recalculara constantemente (como fai Power Query de facto). Para iso usamos a función Táboa.Buffer desde a linguaxe Power Query incorporada M.
Para iso, abra unha consulta Os resultados e na pestana revisión prema o botón Editor avanzado (Ver — Editor avanzado). Na xanela que se abre, engade unha liña cunha nova variable Marcos 2, que será unha versión almacenada no búfer do noso directorio de fabricantes de automóbiles e use esta nova variable máis tarde no seguinte comando de consulta:
Despois deste perfeccionamento, a velocidade de actualización da nosa solicitude aumenta case 7 veces, ata 15 segundos. Unha cousa ben diferente 🙂
- Busca de texto difuso en Power Query
- Substitución de texto masivo con fórmulas
- Substitución de texto masivo en Power Query coa función List.Acumulate