Funções CONT.SE e CONT.SES com condição OU

Este tutorial explica como trabalhar com as funções CONT.SE e CONT.SES do Excel para contar células, assim como utilizar as funções condicionais E e OU.

A principio, a função CONT.SE foi projetada para contar células com base em apenas um critério, enquanto a função CONT.SES avalia vários critérios com a lógica condicional E. Entretanto, uma tarefa pode exigir que trabalhemos com condições lógicas OU, que é usada quando várias condições são fornecidas na função.

Sendo assim, existem algumas soluções possíveis para esse tipo de tarefa, e este tutorial abrange cada uma delas em detalhe!

Para entender bem os exemplos, é necessário conhecer a sintaxe de cada uma dessas funções. Caso queira começar revisar o básico antes de começar a leitura, sugiro acessar os links abaixo que tem tudo explicadinho. Depois é só voltar aqui que a gente continua! Mas para quem já está afiado, vamos nessa!!

Função CONT.SE: conta células com apenas um critério;

Função CONT.SES: conta células com mais de um critério;

Contar células no Excel com condição OU

Esta seção aborda células simples de contagem. A função se refere a maneira mais fácil de contar células com dois valores diferentes, como demostrado nas fórmulas abaixo:

• Fórmula 1: CONT.SE + CONT.SE

A maneira mais fácil de contar células que tem um valor ou outro é escrever uma fórmula CONT.SE regular para contar cada item individualmente e adicionar os resultados:

= CONT.SE ( intervalo ; critério 1) + CONT.SE ( intervalo ; critério 2)

Como exemplo, descobriremos quantas células na coluna A contém as palavras “maçãs” ou “bananas”:

= CONT.SE ( A : A; "maçãs") + CONT.SE ( A: A; "bananas")

Uma boa prática é operar em intervalos, ao invés de colunas inteiras. Isso porque, operando em intervalos a fórmula passa a funcionar mais rapidamente. Entretanto, para poupar o trabalho de atualizar a fórmula sempre que as condições forem alteradas, a melhor maneira é digitar os itens de interesse em células predefinidas, como F1 e G1, fazendo referências as células. Por exemplo:

= CONT.SE ( A2: A10; F1 ) + CONT.SE ( A2: A10 ; G1)

Esta técnica funciona bem para alguns critérios, porém a adição de três ou mais funções CONT.SE pode tornar a fórmula muito complicada. Neste caso, o ideal é trabalhar com uma segunda alternativa. Acompanhe as demais fórmulas apresentadas a seguir.

• Fórmula 2: CONT.SE com matriz constante

Temos aqui uma versão mais compacta da fórmula SOMA.SE com condições OU no Excel.

= SOMA ( CONT.SE (intervalo; {critério 1 ; critério 2; critério 3; ... }))

A fórmula é constituída da seguinte maneira:

Primeiro aninhe todas as condições em uma constante da matriz, ou seja, os itens individuais separados por ponto-e-vírgula e a matriz entre chaves, como { “maças” ; “bananas” ; “limões”}.

Em seguida, inclua a constante da matriz no segundo argumento, que é o critério da função da fórmula CONT.SE  normal:

CONT.SE ( A2: A10 ; {"maçãs" ; "bananas" ; "limões"}).

Por fim, distorça a fórmula CONT.SE  na função SOMA. É necessário fazer isso porque, neste caso a função CONT.SE retornará três contagens individuais para “maças” , “bananas” e “limões”.

A fórmula completa é a seguinte:

= SOMA (CONT.SE (A2:A10; {"Maçãs";"Bananas";"Limões"}))

Para quem deseja fornecer os critérios como referências de intervalos, é necessário digitar as teclas Ctrl + Shift + Enter para torna-la uma fórmula de matriz. Por exemplo:

= SOMA (CONT.SE ( A2:A10 ; F1:H1))

Observe que as chaves na imagem abaixo, é a indicação mais evidente de uma fórmula de matriz no Excel:

• Fórmula 3: SOMARPRODUTO

Outra forma de contar células com lógica OU no Excel, é através da função SOMARPRODUTO.

= SOMARPRODUTO (1 * (intervalo = {critério1 ; critério2; critério3; ...}))

Para visualizar melhor a lógica, a mesma função pode ser escrita como:

= SOMARPRODUTO ((intervalo = critério1) + (intervalo = critério2) + ...)

A fórmula apresentada acima, testa cada célula do intervalo em relação a cada critério. A fórmula retorna VERDADEIRO se o critério for atendido, caso contrário retorna FALSO. Entretanto, é importante saber que o número de matrizes é igual ao número dos critérios.

Em seguida, os elementos da matriz na mesma posição são adicionados juntos, ou seja, do primeiro elemento em diante. Sendo assim, a operação de adição converte os valores lógicos em números, para que termine com uma matriz de 1 (um critério atendido) e 0 (nenhum critério atendido).

Como todos os critérios são testados nas mesmas células, não há como outro número aparecer na matriz resultante, apenas uma matriz inicial pode ter o resultado VERDADEIRO em uma posição específica, outras terão FALSO. Então, finalmente, a função SOMARPRODUTO adiciona os elementos da matriz resultante e torna possível obter a contagem desejada.

A primeira fórmula funciona de maneira semelhante, a diferença é que ela retorna uma matriz bidimensional de valores VERDADEIRO  e FALSO, que você multiplica por 1 para converter os valores lógicos em 1 e 0, respectivamente.

Aplicadas ao nosso conjunto de dados de amostra, as fórmulas assumem a seguinte forma:

= SOMARPRODUTO (1 * (A2:A10 = {"Maçãs" ; "Bananas" ; "Limões"}))

ou

= SOMARPRODUTO ((A2:A10 = "Maçãs") + (A2:A10 = "Bananas") + (A2:A10 = "Limões"))

Para obter uma solução ainda mais elegante, basta substituir a constante de matriz codificada por uma referência de intervalo, como apresentado abaixo:

= SOMARPRODUTO (1 * (A2:A10 = F1:H1))

Nota: A função SOMARPRODUTO é relativamente mais lenta que a função CONT.SE, razão pela qual essa fórmula é melhor para ser usada em conjuntos de dados menores.

Contar células no Excel com condição OU e E.

Ao trabalhar com grandes conjuntos de dados que tem relações de vários níveis entre os elementos, é provável que precise contar células com condições OU e E de cada vez.

Como exemplo, vamos obter uma contagem de quantas maçãs, bananas e limões foram “entregues” ao cliente. No entanto, serei cuidadosa em traduzir as condições desse exemplo para o idioma do Excel, isso porque, a explicação ficará mais clara e entendível para quem é iniciante. Acompanhe:

• Coluna A: “Maçãs” ou “Bananas” ou “Limões”
• Coluna C: “Entregue”

Por outro lado, é preciso contar as linhas que contenham as informações “Maçãs” na coluna A e “Entregue” na coluna C ou “Bananas” na coluna A e “Entregue” na coluna C ou “Limões” na coluna A e “Entregue” na coluna C.

Desse modo, a tarefa se resume em contar células com três condições OU (exatamente o que foi feito na tópico anterior!). A diferença, é que aqui trabalharemos com a função CONT.SES ao invés da função CONT.SE para avaliar o critério E em cada condição OU. Observe as fórmulas:

• Fórmula 1: CONT.SES + CONT.SES

Apesar de esta ser a fórmula mais longa, eu diria que é a mais fácil de entender.

= CONT.SES ( A2:A10 ; "Maçãs" ; C2:C10 ; "Entregue") + CONT.SES (A2:A10 ; "Bananas" ; C2:C10 ; "Entregue") + CONT.SES (A2:A10 ; "Limões" ; C2:C10 ; "Entregue")

A imagem abaixo mostra a mesma fórmula com referências de células:

= CONT.SES ( A2:A10 ; K1 ; C2:C10 ; K2) + CONT.SES (A2:A10 ; L1 ; C2:C10 ; K2) + CONT.SES (A2:A10 ; M1; C2:C10 ; K2)

• Fórmula 2: CONT.SES com matriz constante.

Uma fórmula CONT.SES mais compacta com as lógicas E/OU pode ser criada aninhando critérios OU em uma constante de matriz:

= SOMA ( CONT.SES ( A2:A10 ; {"Maçãs"; "Bananas"; "Limões"} ; C2:C10 ; "Entregue"))

Ao usar uma referência de intervalo para os critérios, é necessário uma fórmula de matriz, que é preenchida pressionando Crtl + Shift + Enter:

= SOMA ( CONT.SES ( A2:A10 ; F1:H1; C2:C10 ; F2))

Dica: Caso seja necessário, trabalhe com caracteres curingas nos critérios de qualquer uma das fórmulas demostradas acima. Por exemplo, para contar todos os tipos de bananas, seja “bananas verdes” ou “bananas maduras” aplique a seguinte fórmula:

= SOMA ( CONT.SES (A2:A10 ; { " Maçãs" ; "*Bananas*" ; "Limões"} ; C2:C10 ; " Entregue"))

De maneira bem semelhante, é possível criar uma fórmula para contar células com base em outros tipos de critérios. Por exemplo, para obter uma contagem de quantas maçãs ou bananas ou limões são entregues ao cliente, desde que o montante de cada produto supere R$ 200,oo, adicione mais um critério na função:

= SOMA ( CONT.SES (A2:A10 ; { " Maçãs" ; "*Bananas*" ; "Limões"} ; C2:C10 ; " Entregue" ; B2:B10 ; ">200"))

Ou então use a fórmula de matriz, que é inserida através das teclas Crtl + Shift + Enter:

= SOMA ( CONT.SES (A2:A10 ; F1:H1 ; C2:C10 ; F2 ; B2:B10 ; ">" &F3))

No exemplo anterior, aprendemos como testar um conjunto de condições OU. Mas, o que deve ser feito quando existir dois ou mais conjuntos e for necessário obter um total de todas as relações OU possíveis?

Dependendo de quantas condições for necessário manipular, podemos trabalhar com a função CONT.SES com uma constante de matriz ou a função SOMARPRODUTO com a função ÉNÚM e CORRESP. O primeiro é relativamente fácil de construir, mas é limitado a apenas 2 conjuntos de condições OU.

O último pode avaliar qualquer número de condições (um número razoável, é claro, dado o limite do Excel para 255 argumentos e 8192 caracteres para o comprimento total da fórmula), mas pode ser necessário algum esforço para entender a lógica da fórmula.

A melhor opção no entanto, é trabalhar com a função SOMASES.

Para acessar o conteúdo sobre operações lógicas no Excel, clique aqui.

Para o artigo não ficar tão grande e cansativo, trarei o contudo na próxima sessão. Fique ligado!

Conclusão

Neste artigo estudamos sobre o uso das funções CONT.SE e CONT.SES com condição lógica OU, que são ferramentas básicas importantes usadas para elaborar fórmulas complexas no Excel!

Eu sei que não é comum ler um artigo deste, como tutorial, e repetir para aprender, mas recomendo fortemente que você tome coragem de repetir todo o passo-a-passo. Eu penso que praticar é a melhor maneira de aprender e, embora pareça simples, é preciso se acostumar a navegar entre as telas e fórmulas do Excel para que no futuro você possa se tornar um usuário avançado.

Portanto, continue lendo mais sobre dicas de Excel aqui e sobre Excel básico aqui

Obrigada por ler até aqui!