Por que minha função CONTENT do Excel sempre retorna um valor de “0”?

Question

Eu tenho uma pasta de trabalho do Excel em que cada célula na coluna B contém uma fábula curta (história). Essas células (que são formatadas como "Geral") contém valores de texto grandes por padrões de planilha: todos, exceto um, ultrapassam o limite de 255 caracteres para exibição de texto, uma célula contém mais de 3.000 caracteres, e eu acho que a maioria tem entre 500 e 700 caracteres. A segunda ilustração abaixo mostra essa célula.

Em outra pasta de trabalho, tenho (meta) dados sobre as fábulas. Em particular, estou interessado na frequência de ocorrência de algumas palavras. Na ilustração imediatamente abaixo, A coluna C contém uma lista das palavras em que estou interessado. A coluna B contém o número total de ocorrências da palavra correspondente (contando múltiplas ocorrências dentro de uma fábula); é irrelevante para essa questão. Eu também quero contar o número de fábulas em que cada palavra aparece (pelo menos uma vez). Eu chamo isso de "Dispersão" (veja explicação no comentário abaixo). Eu tenho usado Ctrl + F ("Localizar e substituir") e clicando em "Localizar tudo", que relata uma contagem das células que contêm a string de pesquisa (veja a segunda ilustração abaixo). Eu fiz esse de cada vez, para cada palavra, e inseriu manualmente os números na coluna N.

(provavelmentevocêpodepularesteparágrafo).Euprecisodecontagensparainstânciasdepalavrasúnicaseinteiras,enãodeformasderivadasdepalavrasraiz(nemmesmoplurais).Porexemplo,minhacontagempara"animal" precisa retornar contagens para apenas "animal", não "animales" ou qualquer outra variação. Logo no início, percebi que simplesmente procurando por uma palavra poderia levar a uma contagem falsa porque incluiria palavras que continham a palavra que eu estava procurando. Consegui contornar isso preenchendo meus termos de pesquisa com espaços no início e no final - na Coluna E (por exemplo, "animal"), que contém =" "&C2&" " - e garantir que a coluna contra a qual estou verificando essas palavras também isole essas palavras. Onde quer que um sinal de pontuação caísse ao lado da última ou primeira letra de uma palavra em uma fábula, inseri um espaço para eliminar qualquer tal adjacência. Por exemplo, "todo esto, porque siendo" se tornou "todo esto, porque siendo". (Isso foi parcialmente inspirado pelo comentário do JNevill sobre essa questão: O COUNTIF do Excel não funciona .) As minhas pesquisas em Ctrl + F retornaram para minhas contagens individuais das palavras que eu procurei.

Claro que isso é entediante, demorado e propenso a erros, por isso comecei a pensar se alguma fórmula não seria capaz de fazer a mesma coisa, mas mais rápido. Algumas postagens / páginas da web sugeriram que a função COUNTIF pode ser capaz de fazer isso, então eu tenho experimentado com isso, mas até agora, minhas tentativas falharam todas as vezes. Na ilustração acima, a coluna M contém

=COUNTIF('[OTHER WORKBOOK.xlsx]SHEET'!$B:$B,E2)

e como você pode ver, ele está exibindo um "0" quando deveria estar igualando o número na Coluna N (ie, a contagem encontrada usando Ctrl + F ). (Como dito acima, a coluna E contém a string que estou procurando, qual é a palavra que estou procurando, preenchido com espaços no início e no fim.) Cada configuração que usa a função COUNTIF que tentei falhou.

Abaixo está uma imagem da célula B23 na minha planilha de fábulas (ou seja, uma fábula). A vista é parcialmente obstruída por uma caixa de diálogo "Localizar e substituir", mostrando que 13 células (fábulas) contêm a palavra "animal" (ou seja, eles contêm a string "animal", com espaços iniciais e finais) pelo menos uma vez.

(Vocêpodeprecisar ampliar sua visão da imagem para ver isso claramente.)

A última coisa que vou mostrar a você é o que acontece quando eu uso a função COUNTIF . Eu acho que isso pode ser a chave para que eu não consiga fazer isso funcionar, porque assim que eu adicionar o intervalo da coluna necessária, antes mesmo de pressionar Enter , Eu vejo #VALUE!; #VALUE!; … ao lado do intervalo. Parece assim:

QuandopressionoEnter,Eureceboo"0" que você vê para as células nessa coluna.

O que estou fazendo de errado? Como posso contar o número de fábulas em que cada palavra aparece? (pelo menos uma vez)?

Se você não pode dizer pelas imagens, estou usando o Excel 2007.

    

Answer 1

@ fixer1234 está certo - COUNTIF conta as células iguais a um valor, não células que contenham uma string. Para isso, você precisa usar FIND ou SEARCH . (Eles são idênticos, exceto FIND é sensível a maiúsculas e minúsculas e SEARCH não faz distinção entre maiúsculas e minúsculas. Eu suponho que você quer o insensível a maiúsculas e minúsculas.)

Comece fazendo

=SEARCH(E2, '[OTHER WORKBOOK.xlsx]SHEET'!B1)

Isso irá procurar o valor de E2 (no seu exemplo, "animal") na célula B1 da outra planilha. Se esse valor de string estiver presente nessa célula, isso retornará a localização do a primeira ocorrência da string de pesquisa no texto da célula (com o primeiro caractere sendo 1). Se a string não estiver presente, ela retornará #VALUE! .

Em seguida, faça

=IF(ISERROR(SEARCH(E$2, '[OTHER WORKBOOK.xlsx]SHEET'!B1)), 0, 1)

Isso será avaliado como 1 se a string estiver presente e 0 se não for. O próximo passo é:

=SUM(IF(ISERROR(SEARCH(E2, '[OTHER WORKBOOK.xlsx]SHEET'!$B:$B)), 0, 1))

Isso soma a fórmula anterior ao longo da coluna B da outra planilha, dando-lhe a contagem que você quer. Note que o acima é uma fórmula de matriz . Isso significa que, para fazê-lo funcionar, você deve digitar Ctrl + Deslocar + Enter depois de digitar a fórmula. Agora você pode colocar isso na célula M2 e arrastar para baixo.

Você realmente não precisa ter a coluna E - você pode lidar com isso na sua fórmula SEARCH :

=SUM(IF(ISERROR(SEARCH(" "&C2&" ", '[OTHER WORKBOOK.xlsx]SHEET'!$B:$B)), 0, 1))

Eu testei isso no Excel 2013, mas já fiz coisas assim antes, e espero que esta solução funcione no Excel 2007. (E eu testei com células com mais de 750 caracteres, e com um nome de arquivo de pasta de trabalho que contenha um espaço.)

P.S. Eu não sei porque você tem aqueles #VALUE! de erros na caixa de diálogo “Argumentos da função”; funcionou para mim:

(testei mesmo que a minha resposta não use COUNTIF .) Você tem a outra pasta de trabalho aberta enquanto faz isso?

    

Answer 2

Estou tendo problemas para ver suas capturas de tela, mas, como você está tentando contar a saída de outras funções, tente usar a função value () em sua instrução countif. Value () dirá ao excel para ver a saída (o que você vê nas células) em vez da função subjacente que você gravou nelas.

Sua instrução countif seria algo como "= countif (valor (intervalo da [outra planilha]), critérios)"

    

Answer 3

Se a string longa estiver em B2 e a palavra que você está procurando estiver em C2, a seguinte fórmula fornecerá a contagem de quantas vezes a palavra está na string

• = + IFERROR ((LEN (B2) -LEN (SUBSTITUTO (B2, C2, ""))) / LEN (C2), "")

As partes são

1. Iferror (opcional) deixa em branco se houver um erro
2. Len (b2) comprimento da célula original
3. LEN (SUBSTITUTE (B2, C2, "") exclui todas as ocorrências do padrão. Isso será mais curto se estiver nos dados
4. Len (c2) comprimento do padrão se 6 caracteres de comprimento e houver 3 ocorrências, em seguida, 18/6 resultará em 3

Answer 4

Você pode usar COUNTIF () para contar se uma sequência contém outra, explorando o uso de curingas, conforme fornecido pela Microsoft. O asterisco (*) corresponde a zero ou mais caracteres, enquanto o ponto de interrogação (?) Corresponde a um único caractere.

Então, em vez de =COUNTIF('[OTHER WORKBOOK.xlsx]SHEET'!$B:$B,E2) use =COUNTIF('[OTHER WORKBOOK.xlsx]SHEET'!$B:$B,"*"&E2&"*") .

Os asteriscos permitem qualquer outro texto antes e depois da sua string. Como sua string já tem espaços iniciais e finais, não deve haver problemas com plurais e outras formas de palavras.