Eu tenho uma planilha com dados neste formato:
User | Group | ParentGroup | ID
joe | US fed grp | fed | 123
bill | dev | IT | 234
jim | UK fed grp | fed | 345
Eu preciso de uma fórmula que pesquise as colunas Group e ParentGroup em toda a planilha e forneça o total de linhas contadas de linhas que contenham 'fed'
No exemplo acima, a fórmula retornará 2 (não 4, que é o número de vezes 'alimentado' aparece em toda a planilha). 2 é o número de linhas que "alimentado" aparece.
Estou tentando
=COUNTIF(1:4, "*" & "fed" & "*")
mas está retornando 4 (eu quero 2)
Não há necessidade de uma coluna auxiliar:
=SUMPRODUCT(--ISNUMBER(SEARCH("fed",Group & ParentGroup)))
E você pode substituir uma referência de célula por "fed", se quiser.
Resposta em uma célula:
=COUNTIF(B:C, "*fed*") - COUNTIFS(B:B, "*fed*", C:C, "*fed*")
Isso contará o número de células que contêm fed e, em seguida, subtrairá uma para cada linha que contenha duas células com fed . Isso é bastante simples, mas não vai escalar bem (por exemplo, você tem 6 colunas e deseja contar o número de linhas que contêm fed em qualquer delas)
Atualização: versão escalonável
Isso estava realmente me incomodando, então eu investiguei mais e criei este fórmula de matriz :
=SUM(IF(ISNUMBER(SEARCH("fed", B1:B4)) +
ISNUMBER(SEARCH("fed", C1:C4)),
1, 0))
Nota Importante: Esta é uma fórmula de matriz, então você precisa pressionar CTRL + ALT + ENTER , em vez de simplesmente digitar ENTER ou clicar fora da célula. Você pode dizer se fez certo porque a fórmula mostrará { e } ao redor (e o resultado sempre será 0 se você não o fizer).
Agora você pode expandir isso para usar qualquer número de colunas adicionando outro + ISNUMBER(SEARCH("fed", X1:X4)) à função. Você pode especificar a pesquisa na coluna inteira usando X:X , em que X é a letra da coluna, mas dependendo do sistema que pode começar a ser executado lentamente. Eu tive um atraso notável pesquisando a coluna inteira, mas mudar para X1:X100 fez com que parecesse instantâneo. Acredito que você precisará tornar o intervalo o mesmo para todas as colunas (por exemplo, você não pode ter B:B e C1:C10 ), mas não testei.
Aqui está como eu resolvi isso.
Se na coluna B for Group, a coluna C é ParentGroup e em I2 é a palavra que você está procurando, no seu caso, fed.
Faça outra coluna chamada FED e insira essa fórmula e puxe-a para baixo da folha.
= SE (OR (EXACTO (B2; $ I $ 2); EXACTO (C2; $ I $ 2)); 1; 0)
Com isso, você obterá o número 1 mesmo se houver poucos "alimentados" em cada linha.
Em seguida, some a coluna FED.
Enviei a solução.
Acredito que Davidenko esteja correto ao sugerir que você precisa de uma coluna auxiliar para isso.
Sugiro que a fórmula em, digamos, cell S2 , poderia ser algo como
=OR(ISNUMBER(SEARCH("fed", B2)), ISNUMBER(SEARCH("fed", C2)))
Qual será a avaliação para TRUE se B2 ou C2 contiver "alimentado", sem distinção entre maiúsculas e minúsculas.
Substitua SEARCH por FIND se você quiser um teste com distinção entre maiúsculas e minúsculas.
Se você quiser que o texto procurado venha de uma célula, você pode fazer isso com, por exemplo,
=OR(ISNUMBER(SEARCH($I$2, B2)), ISNUMBER(SEARCH($I$2, C2)))
Se você precisar verificar as colunas A e D também, adicione-as.
Um rápido & atalho sujo seria
=ISNUMBER(SEARCH("fed", B2&C2))
mas isso contaria as linhas em que o valor na coluna B terminava com "f"
e o valor na coluna C começou com "ed". Para consertar isso, vá com algo como
=ISNUMBER(SEARCH("fed", B2&"|"&C2))
S acima), arrastando-a para baixo e faça algo como
=COUNTIF(S:S, TRUE)
Como alternativa, use um dos truques comuns para converter os valores na coluna S para 1s e 0s,
e então SUM (como Davidenko sugeriu).
=COUNTIFS(B:B,"fed",C:C,"fed")
assumindo que ambas as colunas têm o valor "fed"
Tags microsoft-excel