Anteriormente, fiz uma pergunta sem criar uma conta e recebi uma boa resposta do usuário Jeorje, mas não forneci muitos detalhes e não consegui responder ao comentário anterior.
Por causa disso, decidi tentar uma segunda tentativa com mais detalhes, exemplos da planilha com a qual estou trabalhando e a capacidade de continuar respondendo aos comentários.
Estou procurando criar um sistema que permita a saída do número de vezes que uma palavra-chave ou frase foi incluída em um parágrafo de texto ou no título de um documento dentro de um intervalo de datas que pode ser alterado.
A fórmula que estou usando atualmente para criar uma saída na célula D1 contando a quantidade de entradas dentro do intervalo de datas é:
=COUNTIFS('Main Sheet'!A:A,">="&Trends!C1,'Main Sheet'!A:A,"<="&Trends!A1)
A fórmula que estou usando atualmente para criar uma saída na célula H2 contando a quantidade de entradas incluindo a palavra-chave da célula G2 é:
=(SUM((LEN('Main Sheet'!H:H)-LEN(SUBSTITUTE((LOWER('Main Sheet'!H:H)),LOWER(G2),"")))/LEN(G2)))
Eu queria saber se havia uma maneira de combinar essas duas fórmulas para criar o efeito desejado que estou procurando ou se havia uma terceira fórmula que seria capaz de combinar os dados dessas duas fórmulas para criar o efeito desejado . Se estou completamente errado, gostaria de saber isso também.
Muito obrigado!
Imagem da Folha Principal
Imagem da Folha de Tendências
Responder1
Acredito que o seguinte resolverá o problema:
==SUM(IFERROR(IF( 'Main Sheet'!A:A >= Trends!C1, IF( 'Main Sheet'!A:A <= Trends!A1, FIND( G2, 'Main Sheet'!H:H ), NA() ), NA() ), " " ))
(Coloquei muitos espaços extras para fazer com que os elementos se destaquem um pouco melhor. Cada conjunto tem dois espaços, então você pode ter Find and Replacedois espaços sem nenhum e todos os espaços que coloquei serão removidos facilmente.)
A versão da fórmula que elaborei (usando as células da MINHA planilha de teste) funcionou bem em diversas variações. Em seguida, coloquei as referências de suas duas fórmulas onde elas pertenciam, substituindo as minhas. Não citei algumas folhas e assim por diante para testar se fiz isso perfeitamente... então, se você achar que falhou, dedique um pouco de tempo para ter certeza de que copiei e colei corretamente primeiro, você sabe, deixei de fora um personagem ou o que quer que seja.
A dificuldade que tive foi IF()matar a capacidade SPILL, por exemplo, vai matar a criação de uma {FALSE;FALSE,TRUE,FALSE}string no funcionamento interno do Excel. Ou parece que esse é o mecanismo. Deixa você com uma única célula sendo MUITO testada. Porém, como vocês podem ver, nem sempre, e finalmente encontrei uma combinação que não estragou tudo.
Basicamente, eu costumava FIND()obter uma string {} no funcionamento do Excel. Ele procura o valor que você deseja, talvez "água", e fornece o número do caractere na string em que começa. Então você poderia obter "5" ou "2.184" Mas em uma string, internamente, assim: {"5";"2184"} e COUNT()os verá como dois itens, NÃO cinco dígitos e, portanto, cinco itens.
Em qualquer caso, as falhas na correspondência do intervalo de datas produzirão erros #NA, enquanto as falhas na localização do valor de pesquisa nas strings de pesquisa produzirão erros #VALUE. Os sucessos produzirão o número do caractere em que encontrou o primeiro caractere da string de pesquisa. Portanto, você terá uma sequência {} de erros e valores numéricos que o Excel apresenta para a IFERROR()função. Sua saída são os valores, é claro, para não erros e um espaço para quaisquer erros reais. Você poderia usar um conjunto de aspas duplas (""), mas gosto do espaço porque força o resultado a ser considerado como texto em todas as circunstâncias, para que nunca possa escorregar de alguma forma, talvez seja considerado 0 pelo Excel, e contado. COUNT()só gosta de números, então forçá-los a ser sempre texto garante que eles nunca possam aumentar o resultado.
Em seguida, COUNT()simplesmente conta essa sequência e retorna o número de locais que correspondem a todos os três itens, as datas de início e término (o intervalo de datas) e a ocorrência da sequência de pesquisa nas células de pesquisa.
Espero ter tido a ideia certa aqui!