Contar valores ausentes
Supondo que você tenha duas listas, para contar o número total de valores em uma lista que não existem em outra lista, você pode usar um SUMPRODUCT fórmula com a ajuda de JOGO e funções ISNA, ou o COUNTIF função.
Contar valores ausentes com SUMPRODUCT, MATCH e ISNA
Contar valores ausentes com SUMPRODUCT e COUNTIF
Contar valores ausentes com SUMPRODUCT, MATCH e ISNA
Para contar o número total de valores na lista B que estão faltando na lista A como mostrado acima, você pode primeiro usar a função MATCH para retornar uma matriz da posição relativa dos valores da lista B na lista A. Se um valor não existir na lista A, um erro #N/A será retornado. A função ISNA identificará os erros #N/A e SUMPRODUCT contará o número total de erros.
Sintaxe genérica
=SUMPRODUCT(--ISNA(MATCH(range_to_count,lookup_range,0)))
- intervalo_para_contagem: O intervalo a partir do qual contar os valores ausentes. Aqui se refere à lista B.
- intervalo_de_procura: O intervalo para comparar com o intervalo_até_contagem. Aqui se refere à lista A.
- 0: A tipo_correspondência 0 força MATCH a realizar uma correspondência exata.
Para contar o número total de valores na lista B que estão faltando na lista A, copie ou insira a fórmula abaixo na célula H6 e pressione Entrar para obter o resultado:
=SOMAPRODUTO(--ISNA(CORRESP(F6: F8,B6: B10, 0)))
Explicação da fórmula
=SUMPRODUCT(--ISNA(MATCH(F6:F8,B6:B10,0)))
- CORRESP(F6:F8,B6:B10,0): A tipo_correspondência 0 força a função MATCH a retornar valores numéricos que indicam as posições relativas dos valores através das células F6 para F8 no intervalo B6: B10. Se um valor não existir na lista A, um erro #N/A será retornado. Então, os resultados estarão em uma matriz como esta: {2;3;#N/D}.
- ISNA (CORRESP(F6:F8,B6:B10,0)) = ISNA ({2;3;#N/D}): ISNA trabalha para descobrir se um valor é um erro “#N/A” ou não. Se sim, a função retornará um TURE; Caso contrário, retornará um FALSE. Então, a fórmula ISNA retornará {FALSO;FALSO;VERDADEIRO}.
- SOMAPRODUTO(--ISNA (CORRESP(F6:F8,B6:B10,0))) = SOMAPRODUTO(--{FALSO;FALSO;VERDADEIRO}): O sinal negativo duplo converte TRUEs em 1s e FALSEs em 0s: {0; 1; 0}. Em seguida, a função SUMPRODUCT retorna a soma: 1.
Contar valores ausentes com SUMPRODUCT e COUNTIF
Para contar o número total de valores na lista B que estão faltando na lista A, você também pode usar a função CONT.SE para saber se existe um valor na lista A ou não com a condição “=0”, pois um 0 será gerado se um valor estiver faltando. O SUMPRODUCT contará então o número total de valores ausentes.
Sintaxe genérica
=SUMPRODUCT(--(COUNTIF(lookup_range,range_to_count)=0))
- intervalo_de_procura: O intervalo para comparar com o intervalo_até_contagem. Aqui se refere à lista A.
- intervalo_para_contagem: O intervalo a partir do qual contar os valores ausentes. Aqui se refere à lista B.
- 0: A tipo_correspondência 0 força MATCH a realizar uma correspondência exata.
Para contar o número total de valores na lista B que estão faltando na lista A, copie ou insira a fórmula abaixo na célula H6 e pressione Entrar para obter o resultado:
=SOMAPRODUTO(--(CONT.SE(B6: B10,F6: F8)=0))
Explicação da fórmula
=SUMPRODUCT(--(COUNTIF(B6:B10,F6:F8)=0))
- CONT.SE(B6:B10,F6:F8): A função CONT.SE conta o número de ocorrência das células através F6 para F8 no intervalo B6: B10. O resultado estará em um array como este: {1; 1; 0}.
- - (CONT.SE(B6:B10,F6:F8)= 0) = - ({1; 1; 0}= 0): O snippet {1;1;0}=0 gera uma matriz TRUE e FALSE {FALSO;FALSO;VERDADEIRO}. O sinal negativo duplo então transforma TRUEs em 1s e FALSEs em 0s. A matriz final é assim: {0; 0; 1}.
- SUMPRODUCT (- (CONT.SE(B6:B10,F6:F8)= 0)) = SUMPRODUCT ({0; 0; 1}): O SUMPRODUCT então retorna a soma: 1.
Funções relacionadas
No Excel, a função SUMPRODUCT pode ser usada para multiplicar duas ou mais colunas ou matrizes e obter a soma dos produtos. Na verdade, o SUMPRODUCT é uma função útil que pode ajudar a contar ou somar os valores das células com vários critérios, como a função COUNTIFS ou SUMIFS. Este artigo irá apresentar a sintaxe da função e alguns exemplos para esta função SUMPRODUCT.
A função MATCH do Excel procura um valor específico em um intervalo de células e retorna a posição relativa do valor.
A função CONT.SE é uma função estatística do Excel usada para contar o número de células que atendem a um critério. Ele suporta operadores lógicos (<>, =,> e <) e os curingas (? E *) para correspondência parcial.
Fórmulas Relacionadas
Há casos em que você precisa comparar duas listas para verificar se um valor da lista A existe na lista B do Excel. Por exemplo, você tem uma lista de produtos e deseja verificar se os produtos em sua lista existem na lista de produtos fornecida por seu fornecedor. Para realizar essa tarefa, listamos aqui três maneiras abaixo, fique à vontade para escolher a que você quiser.
Este artigo se concentrará nas fórmulas do Excel para contar as células que são exatamente iguais a uma string de texto especificada ou parcialmente igual à string de texto fornecida, conforme as capturas de tela mostradas abaixo. Em primeiro lugar, explicará a sintaxe e o argumento da fórmula, o fornece exemplos para você entender melhor.
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 para nós no Excel, mas, em certos casos, você pode querer contar as células não entre dois números dados. Por exemplo, eu tenho uma lista de produtos com as vendas de segunda a domingo, agora, preciso obter o número de células que não está entre os números baixo e alto específicos, conforme a imagem mostrada abaixo. Neste artigo, vou apresentar algumas fórmulas para lidar com essa tarefa no Excel.
As melhores ferramentas de produtividade para escritório
Kutools para Excel - ajuda você a se destacar na multidão
Kutools para Excel possui mais de 300 recursos, Garantindo que o que você precisa está a apenas um clique de distância...
Guia Office - Habilite leitura e edição com guias no Microsoft Office (inclui Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduza centenas de cliques do mouse para você todos os dias, diga adeus à mão do mouse.
- Aumenta sua produtividade em 50% ao visualizar e editar vários documentos.
- Traz guias eficientes para o Office (inclui Excel), assim como Chrome, Edge e Firefox.
