Buscar palabras clave en el texto

La búsqueda de palabras clave en el texto de origen es una de las tareas más comunes cuando se trabaja con datos. Veamos su solución de varias maneras usando el siguiente ejemplo:

Supongamos que usted y yo tenemos una lista de palabras clave (los nombres de las marcas de automóviles) y una tabla grande de todo tipo de piezas de repuesto, donde las descripciones a veces pueden contener una o varias de esas marcas a la vez, si la pieza de repuesto se ajusta a más de una. marca de coche. Nuestra tarea es encontrar y mostrar todas las palabras clave detectadas en celdas vecinas a través de un carácter separador dado (por ejemplo, una coma).

Método 1. Consulta de poder

Por supuesto, primero convertimos nuestras mesas en dinámicas (“inteligentes”) usando un atajo de teclado Ctrl+T o comandos Inicio – Formato como tabla (Inicio — Formatear como tabla), póngales nombres (por ejemplo Sellosи refacciones) y cargar uno por uno en el editor de Power Query seleccionando en la pestaña Datos: de la tabla/rango (Datos: de la tabla/rango). Si tiene versiones anteriores de Excel 2010-2013, donde Power Query está instalado como un complemento separado, entonces el botón deseado estará en la pestaña Power Query. Si tiene una versión nueva de Excel 365, entonces el botón Desde tabla/rango llamado allí ahora con hojas (De la hoja).

Después de cargar cada tabla en Power Query, volvemos a Excel con el comando Inicio — Cerrar y cargar — Cerrar y cargar a… — Solo crear conexión (Inicio — Cerrar y cargar — Cerrar y cargar a… — Solo crear conexión).

Ahora vamos a crear una solicitud duplicada refaccioneshaciendo clic derecho sobre él y seleccionando Solicitud duplicada (Consulta duplicada), luego cambie el nombre de la solicitud de copia resultante a Los resultados y seguiremos trabajando con él.

La lógica de las acciones es la siguiente:

1. En la pestaña Avanzado Agregar una columna elige un equipo columna personalizada (Añadir columna — Columna personalizada) e ingrese la fórmula = Marcas. Después de hacer clic en OK obtendremos una nueva columna, donde en cada celda habrá una tabla anidada con una lista de nuestras palabras clave: marcas de fabricantes de automóviles:

   

2. Use el botón con flechas dobles en el encabezado de la columna agregada para expandir todas las tablas anidadas. Al mismo tiempo, las líneas con descripciones de repuestos se multiplicarán por un múltiplo del número de marcas, y obtendremos todos los pares-combinaciones posibles de "repuesto-marca":

   

3. En la pestaña Avanzado Agregar una columna elige un equipo columna condicional (columna condicional) y establezca una condición para verificar la aparición de una palabra clave (marca) en el texto fuente (descripción de la parte):

   

4. Para que la búsqueda no distinga entre mayúsculas y minúsculas, agregue manualmente el tercer argumento en la barra de fórmulas Comparar.OrdinalIgnoreCase a la función de verificación de ocurrencia Texto.Contiene (si la barra de fórmulas no está visible, entonces se puede habilitar en la pestaña Revisar):

   

5. Filtramos la tabla resultante, dejando solo unos en la última columna, es decir, coincidencias y eliminamos la columna innecesaria ocurrencias.
6. Agrupación de descripciones idénticas con el comando Agrupar por de la pestaña. (Transformar — Agrupar por). Como operación de agregación, elija Todas las lineas (Todas las filas). A la salida, obtenemos una columna con tablas, que contiene todos los detalles de cada pieza de repuesto, incluidas las marcas de los fabricantes de automóviles que necesitamos:

   

7. Para extraer calificaciones para cada parte, agregue otra columna calculada en la pestaña Agregar una columna: columna personalizada (Añadir columna — Columna personalizada) y use una fórmula que consiste en una tabla (se encuentran en nuestra columna Detalles) y el nombre de la columna extraída:

   

8. Hacemos clic en el botón con flechas dobles en el encabezado de la columna resultante y seleccionamos el comando Extraer valores (Extraer valores)para generar sellos con cualquier carácter delimitador que desee:

   

9. Eliminar una columna innecesaria Detalles.
10. Para agregar a la tabla resultante las piezas que desaparecieron de la misma, donde no se encontraron marcas en las descripciones, realizamos el procedimiento de combinación de la consulta Resultado con solicitud original refacciones . Combinar de la pestaña. Inicio (Inicio — Combinar consultas). Tipo de conección - Unión exterior derecha (Unión exterior derecha):

    

11. Todo lo que queda es eliminar las columnas adicionales y cambiar el nombre, mover las restantes, y nuestra tarea está resuelta:

    
    

Método 2. Fórmulas

Si tiene una versión de Excel 2016 o posterior, nuestro problema se puede resolver de una manera muy compacta y elegante utilizando la nueva función. COMBINAR (ENTRAR POR TEXTO):

La lógica detrás de esta fórmula es simple:

• Función BUSCAR (ENCONTRAR) busca la aparición de cada marca en la descripción actual de la pieza y devuelve el número de serie del símbolo, a partir del cual se encontró la marca, o el error #¡VALOR! si la marca no está en la descripción.
• Luego usando la función IF (SI) и EOSIBKA (ESERROR) reemplazamos los errores con una cadena de texto vacía "", y los números ordinales de los caracteres con los propios nombres de marca.
• La matriz resultante de celdas vacías y marcas encontradas se ensambla en una sola cadena a través de un carácter separador dado usando la función COMBINAR (ENTRAR POR TEXTO).

Comparación de rendimiento y almacenamiento en búfer de consultas de Power Query para Speedup

Para las pruebas de rendimiento, tomemos una tabla de 100 descripciones de piezas de repuesto como datos iniciales. En él obtenemos los siguientes resultados:

• Tiempo de recálculo por fórmulas (Método 2) – 9 seg. cuando copia la fórmula por primera vez en toda la columna y 2 seg. en repetidas ocasiones (efectos de amortiguación, probablemente).
  
• El tiempo de actualización de la consulta de Power Query (Método 1) es mucho peor: 110 segundos.
  

Por supuesto, mucho depende del hardware de una PC en particular y de la versión instalada de Office y las actualizaciones, pero creo que el panorama general es claro.

Para acelerar una consulta de Power Query, almacenemos en búfer la tabla de búsqueda Sellos, porque no cambia en el proceso de ejecución de la consulta y no es necesario recalcularlo constantemente (como lo hace Power Query de facto). Para esto usamos la función Tabla.Búfer del lenguaje Power Query integrado M.

Para hacer esto, abra una consulta Los resultados y en la pestaña Revisar presiona el botón Editor avanzado (Ver — Editor avanzado). En la ventana que se abre, agregue una línea con una nueva variable marco 2, que será una versión almacenada en búfer de nuestro directorio de fabricantes de automóviles, y use esta nueva variable más adelante en el siguiente comando de consulta:

Después de tal refinamiento, la velocidad de actualización de nuestra solicitud aumenta casi 7 veces, hasta 15 segundos. Algo muy diferente 🙂

• Búsqueda de texto difuso en Power Query
• Reemplazo masivo de texto con fórmulas
• Reemplazo de texto masivo en Power Query con la función List.Accumulate