Cada semana, un sistema CRM genera un nuevo archivo de Excel, que tiene la marca de fecha en su nombre de archivo, pero se coloca en una carpeta en una ubicación fija (una URL de sharepoint).
Este archivo es bastante grande y normalmente contiene entre 25 y 30 mil filas. A partir de esto, necesito crear una nueva tabla, relevante para mi propósito.
Las filas relevantes para mi propósito deben coincidir con una (o más) palabras clave/frases clave, de una lista de 30 a 40 de dichas palabras clave/frases clave. Sin embargo, esta lista de palabras clave/frases clave también crece, pero lentamente, añadiendo nuevas palabras clave una vez cada pocos meses.
¿Existe alguna forma de automatizar esta tarea manual, tediosa y algo propensa a errores?
Respuesta1
Debido a que es necesario verificar tres columnas diferentes (que se supone que son B, Dy ), una función parece apropiada:FOR
=OR(ISNUMBER(MATCH(B1,keyarray,0)),
ISNUMBER(MATCH(D1,keyarray,0)),
ISNUMBER(MATCH(F1,keyarray,0)))
de modo que se devolverá una coincidencia para cualquiera de las tres columnas TRUE. Coloque esto en una columna 'auxiliar' en la primera fila ocupada por datos (digamos Fila 1, o ajuste y B1arriba en consecuencia).D1F1
MATCHcomprueba si, por ejemplo, el valor de B1existe en keyarray, donde ese es el nombre del rango que contiene la lista de palabras clave/frases clave (~30-40 en número); no es necesario que esté en la misma hoja o libro de trabajo. pero si no, se debe especificar la ruta completa y se recomienda que el "otro" libro de trabajo esté abierto al aplicar la fórmula a un nuevo lote de datos.
0fuerza una coincidencia exacta únicamente (alternativamente, -1para el valor más pequeño que sea mayor o igual que B1, o 1para el más grande).
MATCHdevuelve la ubicación del valor encontrado en la matriz (de lo contrario, con el parámetro 0, #N/A). Eso es numérico, por lo que ISNUMBERbusca un número (cualquier número) para excluir #N/Aresultados.
Por lo tanto, siempre que cualquiera de B1, D1o F1esté en keyarrayel resultado, será TRUE; de lo contrario FALSE.
Para copiar la fórmula en más de 25.000 a 30.000 filas de forma conveniente, donde algunas contienen celdas fusionadas, coloque algo (digamos "fin") en la intersección de la última fila ocupada y la columna "ayudante" (para evitar ampliar innecesariamente el tamaño de la hoja de cálculo). ). Copie la celda que contiene la fórmula, seleccione la celda inmediatamente debajo de ella y luego Ctrl+Shift+Down/ Pegue para llenar la columna auxiliar hacia abajo para todas las filas ocupadas, sin continuar más allá de la última fila ocupada y sobrescribiendo el 'fin'.
Filtre por la columna 'ayudante' TRUE, seleccione todas las columnas ocupadas, copie y pegue en una nueva hoja/libro de trabajo. Elimine filas en blanco en una nueva hoja/libro de trabajo y guárdelas. (También puede optar por eliminar la columna 'ayudante' de la fuente).
Asegúrese de que cuando agregue ocasionalmente elementos al keyarrayrango nombrado cubra las adiciones.
Respuesta2
Esta es una tarea mayor que puede resolverse de muy diversas maneras. Pero en resumen, SÍ, puedes automatizar esto.
Sólo para empezar:
¿Está absolutamente seguro de que su sistema CRM no puede proporcionar esta información que desea extraer directamente?
Por lo general, se basan en un sistema de base de datos, como SQL, y como ya está extrayendo datos de él, es posible que pueda modificar esta salida para adaptarla a sus necesidades.
Ahora a las posibilidades con excel:
- Importando los datos base de tu CRM
es posible que pueda establecer una conexión de datos con su CRM
puede crear un código VBA para importar el archivo más nuevo en su carpeta o para actualizar una conexión de datos establecida a este nuevo archivo
puede abrir el archivo más nuevo a través de VBA y copiar los datos deseados en su hoja de trabajo específica
...
- Si aún es necesario, filtrado de los datos.
Le sugiero que busque filtros y filtros avanzados, vlookup, countifs y ejemplos de código vba. Hay bastantes preguntas aquí y en el manejo del filtrado de múltiples condiciones de stackoverflow, pero tendría que proporcionar más detalles para resolver esto correctamente.
como sugerencia: dividiría esta tarea para obtener los datos base y solo si aún es necesario filtrarlos. A mi modo de ver, debería ser posible obtener los datos filtrados mucho antes en el proceso. O dime lo contrario :)