Anteriormente hice una pregunta sin crear una cuenta y recibí una buena respuesta del usuario Jeorje, pero no proporcioné muchos detalles y no pude responder a su comentario antes.
Por eso decidí intentar una segunda vuelta con más detalles, ejemplos de la hoja con la que estoy trabajando y la capacidad de continuar respondiendo a los comentarios.
Estoy buscando crear un sistema que permita generar la cantidad de veces que se incluyó una palabra clave o frase en un párrafo de texto o en el título de un documento dentro de un rango de fechas que se pueda cambiar.
La fórmula que estoy usando actualmente para crear una salida en la celda D1 contando la cantidad de entradas dentro del rango de fechas es:
=COUNTIFS('Main Sheet'!A:A,">="&Trends!C1,'Main Sheet'!A:A,"<="&Trends!A1)
La fórmula que estoy usando actualmente para crear una salida en la Celda H2 contando la cantidad de entradas que incluyen la palabra clave de la Celda G2 es:
=(SUM((LEN('Main Sheet'!H:H)-LEN(SUBSTITUTE((LOWER('Main Sheet'!H:H)),LOWER(G2),"")))/LEN(G2)))
Me preguntaba si había alguna manera de combinar estas dos fórmulas para crear el efecto deseado que estoy buscando o si había una tercera fórmula que pudiera combinar los datos de estas dos fórmulas para crear el efecto deseado. . Si estoy completamente equivocado, también me gustaría saberlo.
¡Muchas gracias!
Imagen de la hoja principal
Imagen de la hoja de tendencias
Respuesta1
Creo que lo siguiente funcionará:
==SUM(IFERROR(IF( 'Main Sheet'!A:A >= Trends!C1, IF( 'Main Sheet'!A:A <= Trends!A1, FIND( G2, 'Main Sheet'!H:H ), NA() ), NA() ), " " ))
(Puse muchos espacios adicionales para que sus elementos se destaquen un poco mejor. Cada conjunto tiene dos espacios, por lo que puedes tener Find and Replacedos espacios sin ninguno y todos los espacios que puse se eliminarán fácilmente).
La versión de la fórmula que elaboré (usando las celdas de MI hoja de cálculo de prueba) funcionó muy bien en muchas variaciones. Luego puse las referencias de tus dos fórmulas donde pertenecían, reemplazando las mías. No nombré un par de hojas y demás para comprobar que lo hice perfectamente... así que si descubres que falla, dedica un poquito de tiempo a asegurarte de haber copiado y pegado correctamente primero, ya sabes, omití un personaje o lo que sea.
La dificultad que tuve fue IF()que parece eliminar la capacidad SPILL, por ejemplo, eliminará la creación de una {FALSE;FALSE,TRUE,FALSE}cadena en el funcionamiento interno de Excel. O parece que ese es el mecanismo. Te deja con una sola celda que se prueba MUCHO. Sin embargo, como puedes ver, no siempre, y finalmente encontré una combinación que no arruinó las cosas.
Básicamente, solía FIND()obtener una cadena {} en el funcionamiento de Excel. Busca el valor que desea, quizás "agua", y le proporciona el número de carácter en la cadena en la que comienza. Entonces podría obtener "5" o "2,184" pero en una cadena, internamente, así: {"5";"2184"} y COUNT()los verá como dos elementos, NO cinco dígitos y, por lo tanto, cinco elementos.
En cualquier caso, si no se coincide con el rango de fechas se producirán errores #NA, mientras que si no se encuentra el valor de búsqueda en las cadenas de búsqueda se producirán errores #VALUE. Los éxitos producirán el número del carácter en el que encontró el primer carácter de la cadena de búsqueda. Entonces tendrás una {} cadena de errores y valores numéricos que Excel presenta a la IFERROR()función. Su salida son los valores, por supuesto, para los que no son errores y un espacio para los errores reales. En su lugar, podría usar un conjunto de comillas dobles (""), pero me gusta el espacio porque obliga a que el resultado se considere como texto en todas las circunstancias, por lo que nunca puede fallar de alguna manera, tal vez Excel lo considere un 0. y cuenta. COUNT()Solo le gustan los números, por lo que obligarlos a ser siempre texto garantiza que nunca puedan agregar nada al resultado.
Luego COUNT()simplemente cuenta esa cadena y devuelve el número de lugares que coincidieron en los tres elementos, las fechas de inicio y finalización (el rango de fechas) y la aparición de la cadena de búsqueda en las celdas de búsqueda.
¡Espero haber tenido la idea correcta aquí!