Contar valores em falta
Suponha que tem duas listas. Para contar o número total de valores numa lista que não existem na outra, pode utilizar uma fórmula SOMAPRODUTO com a ajuda das funções CORRESP e É.N/D, ou a função CONTAR.SE.
Contar valores em falta com SOMAPRODUTO, CORRESP e É.N/D
Contar valores em falta com SOMAPRODUTO e CONTAR.SE
Contar valores em falta com SOMAPRODUTO, CORRESP e É.N/D
Para contar o número total de valores na lista B que estão em falta na lista A, tal como mostrado acima, comece por utilizar a função CORRESP para obter um vetor com as posições relativas dos valores da lista B na lista A. Sempre que um valor não existir na lista A, será devolvido um erro #N/D. A função É.N/D identifica esses erros #N/D e o SOMAPRODUTO conta o número total de ocorrências.
Sintaxe genérica
=SUMPRODUCT(--ISNA(MATCH(range_to_count,lookup_range,0)))
- intervalo_a_contar: O intervalo a partir do qual se contam os valores em falta. Refere-se aqui à lista B.
- intervalo_de_procura: O intervalo a comparar com o intervalo_a_contar. Aqui refere-se à lista A.
- 0: O tipo_de_correspondência 0 força a função CORRESP a realizar uma correspondência exata.
Para contar o número total de valores na lista B que estão em falta na lista A, copie ou introduza a fórmula seguinte na célula H6 e prima Enterpara obter o resultado:
=SOMAPRODUTO(--É.N/D(CORRESP()))F6:F8,B6:B10,0)))
Explicação da fórmula
=SUMPRODUCT(--ISNA(MATCH(F6:F8,B6:B10,0)))
- MATCH(F6:F8,B6:B10,0): A função tipo_de_correspondência 0 obriga a função CORRESP a devolver valores numéricos que indicam as posições relativas dos valores nas células F6 a F8 no intervalo B6:B10. Se um valor não existir na lista A, será devolvido um erro #N/D. Assim, os resultados aparecerão num vetor como este: {2;3;#N/D}.
- É.N/D()MATCH(F6:F8,B6:B10,0))=É.N/D(){2;3;#N/D}):A função É.N/D verifica se um valor corresponde ao erro «#N/D». Se for esse o caso, devolve VERDADEIRO; caso contrário, devolve FALSO. Assim, a fórmula É.N/D devolverá {FALSO;FALSO;VERDADEIRO}.
- SOMAPRODUTO(--)É.N/D()CORRESP(F6:F8;B6:B10;0))) = SOMAPRODUTO(--{FALSO;FALSO;VERDADEIRO}):O sinal duplo negativo converte os valores VERDADEIRO em 1 e os FALSO em 0:{0;0;1}. Depois, a função SOMAPRODUTO devolve a soma:1.
Contar valores em falta com SOMAPRODUTO e CONTAR.SE
Para contar o número total de valores na lista B que estão em falta na lista A, pode também utilizar a função CONTAR.SE para verificar se um valor existe ou não na lista A com a condição “=0”, já que será devolvido 0 sempre que um valor estiver em falta. O SOMAPRODUTO contará então o número total de valores em falta.
Sintaxe genérica
=SUMPRODUCT(--(COUNTIF(lookup_range,range_to_count)=0))
- intervalo_de_procura:O intervalo a comparar com o intervalo_a_contar. Aqui refere-se à lista A.
- intervalo_a_contar:O intervalo a partir do qual contar os valores em falta. Aqui refere-se à lista B.
- 0:O tipo_de_correspondência 0força a função CORRESP a realizar uma correspondência exata.
Para contar o número total de valores na lista B que estão em falta na lista A, copie ou introduza a fórmula seguinte na célula H6 e prima Enterpara obter o resultado:
=SOMAPRODUTO(--(CONTAR.SE()))B6:B10,F6:F8)=0))
Explicação da fórmula
=SUMPRODUCT(--(COUNTIF(B6:B10,F6:F8)=0))
- COUNTIF(B6:B10,F6:F8): A função CONTAR.SE conta o número de ocorrências dos valores das células entre F6 e F8 no intervalo B6:B10. O resultado surge num vetor com este formato: {1;1;0}.
- --()COUNTIF(B6:B10,F6:F8)=0)=--(){1;1;0}=0):O fragmento {1;1;0}=0 gera um vetor de VERDADEIRO e FALSO {FALSO;FALSO;VERDADEIRO}. O duplo sinal negativo converte, em seguida, os valores VERDADEIRO em 1 e os FALSO em 0. Assim, o vetor final é: {0;0;1}.
- SOMAPRODUTO()--()COUNTIF(B6:B10,F6:F8)=0)) = SOMAPRODUTO({0;0;1}): O SOMAPRODUTO devolve então a soma: 1.
Funções relacionadas
No Excel, a função SOMAPRODUTO pode ser utilizada para multiplicar duas ou mais colunas ou matrizes e, em seguida, obter a soma dos produtos. Na verdade, o SOMAPRODUTO é uma função útil que pode ajudar a contar ou somar valores de células com múltiplos critérios, tal como as funções CONTAR.SE.S ou SOMA.SE.S. Este artigo apresentará a sintaxe da função e alguns exemplos desta função SOMAPRODUTO.
A função CORRESP do Excel procura um valor específico num intervalo de células e devolve a sua posição relativa.
A função CONTAR.SE é uma função estatística do Excel usada para contar o número de células que atendem a um determinado critério. Suporta operadores lógicos (>, <, =) e os caracteres universais (? e *) para correspondência parcial.
Fórmulas relacionadas
Existem casos em que precisa de comparar duas listas para verificar se um valor da lista A existe na lista B no Excel. Por exemplo, tem uma lista de produtos e pretende verificar se os produtos da sua lista existem na lista fornecida pelo seu fornecedor. Para concluir esta tarefa, apresentamos abaixo três métodos — escolha o que preferir.
Este artigo foca-se nas fórmulas do Excel para contar as células que são exatamente iguais a uma cadeia de texto especificada ou parcialmente iguais à cadeia de texto indicada, conforme mostrado nas imagens seguintes. Em primeiro lugar, explica a sintaxe e os argumentos da fórmula e, depois, fornece exemplos para uma melhor compreensão.
Contar o número de células que não estão entre dois números dados
Contar o número de células entre dois números é uma tarefa comum no Excel, mas, em determinadas situações, poderá precisar de contar as células que **não** estão entre dois valores específicos. Por exemplo, imagine que tem uma lista de produtos com as vendas de segunda a domingo e pretende saber quantas células **não** se encontram entre um mínimo e um máximo definidos, tal como ilustrado na imagem seguinte. Neste artigo, apresentamos algumas fórmulas eficazes para resolver esta tarefa no Excel.
As Melhores Ferramentas de Produtividade para o Office
Kutools for Excel – Ajuda-o a destacar-se da multidão
Kutools for Excel Oferece mais de 300 funcionalidades,garantindo que tudo o que precisa está a apenas um clique…
Office Tab – Ative a leitura e edição com separadores no Microsoft Office (incluindo o Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduza centenas de cliques diários e diga adeus à tendinite do rato.
- Aumente a sua produtividade em 50 % ao visualizar e editar vários documentos simultaneamente.
- Traz uma navegação eficiente com separadores ao Office (incluindo o Excel), tal como no Chrome, Edge e Firefox.