Note: The other languages of the website are Google-translated. Back to English

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 1

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)))

  • range_to_count: O intervalo a partir do qual contar os valores ausentes. Aqui se refere à lista B.
  • intervalo_de_procura: O intervalo para comparar com o range_to_count. Aqui se refere à lista A.
  • 0: O match_type 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)))

contar valores ausentes 2

Explicação da fórmula

=SUMPRODUCT(--ISNA(MATCH(F6:F8,B6:B10,0)))

  • CORRESP(F6:F8,B6:B10,0): O match_type 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/A}.
  • ISNA (CORRESP(F6:F8,B6:B10,0)) = ISNA ({2;3;#N/A}): 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 range_to_count. Aqui se refere à lista A.
  • range_to_count: O intervalo a partir do qual contar os valores ausentes. Aqui se refere à lista B.
  • 0: O match_type 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))

contar valores ausentes 3

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

Função SUMPRODUCT do Excel

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.

Função Excel MATCH

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.

Função CONT.SE do Excel

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

Encontre valores ausentes

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.

Contar células iguais a

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 for Excel - ajuda você a se destacar da multidão

Você gostaria de completar seu trabalho diário de forma rápida e perfeita? O Kutools for Excel traz 300 recursos avançados poderosos (combinar pastas de trabalho, somar por cor, dividir o conteúdo da célula, converter data e assim por diante ...) e economizar 80% do tempo para você.

  • Projetado para 1500 cenários de trabalho, ajuda você a resolver 80% dos problemas do Excel.
  • Reduza milhares de cliques de teclado e mouse todos os dias, alivie seus olhos e mãos cansados.
  • Torne-se um especialista em Excel em 3 minutos. Não é mais necessário se lembrar de fórmulas dolorosas e códigos VBA.
  • Avaliação gratuita ilimitada de 30 dias. Garantia de devolução do dinheiro em 60 dias. Atualização e suporte gratuitos por 2 anos.
Faixa de opções do Excel (com Kutools para Excel instalado)

Guia Office - Habilitar leitura e edição com guias no Microsoft Office (incluindo 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 (incluindo Excel), assim como o Chrome, Firefox e o novo Internet Explorer.
Captura de tela do Excel (com guia Office instalado)
Comentários (0)
Ainda não há classificações. Seja o primeiro a avaliar!
Não há comentários postados aqui ainda
Deixe o seu comentário
Postando como convidado
×
Avalie esta postagem:
0   Personagens
Locais sugeridos

Siga-nos

Copyright © 2009 - www.extendoffice.com. | Todos os direitos reservados. Distribuído por ExtendOffice. | | | Mapa do site
Microsoft e o logotipo do Office são marcas comerciais ou marcas registradas da Microsoft Corporation nos Estados Unidos e / ou em outros países.
Protegido por Sectigo SSL