Como mencionei em muitos dos meus blogs, um SUMPRODUCT é uma função muito versátil e pode ser usado para vários fins. Neste artigo, veremos como podemos usar essa função para contar valores com múltiplos critérios ou.
Fórmula genérica de SUMPRODUCT para contar com vários critérios ou
| = SUMPRODUCT (- (((critérios1) + (critérios2) +…)> 0) |
Criteria1: Este é qualquer critério que retorna uma matriz TRUE e FALSE.
Criteria2: Este é o próximo critério que você deseja verificar. Da mesma forma, você pode ter quantos critérios desejar.
A fórmula genérica acima é frequentemente modificada para se adequar aos requisitos para contar com vários critérios OR. Mas a fórmula básica é esta. Primeiro veremos como isso funciona por meio de um exemplo e depois discutiremos outros cenários onde você precisará modificar um pouco esta fórmula.
Problema:
O preço em uma célula deve ser dividido com precisão em 5 pagamentos semelhantes. Ao simplesmente dividir o preço $ 88,04 por 5 e arredondar o resultado para duas casas decimais, obtemos pagamentos individuais de $ 17,61. No entanto, isso somaria um pagamento total de $ 88,05, que não é o valor exato com que começamos. Portanto, queremos calcular 5 números semelhantes que somam o valor total.
Solução:
Primeiro calcule o primeiro pagamento dividindo o preço por 5 e arredondando o resultado.
Use a função ROUND na seguinte fórmula:
= ROUND (B1 / 5,2)
Passo 2:
Calcule cada pagamento restante.
Use as funções ROUND, SUM e ROW na seguinte fórmula:
= RODADA (($ B $ 1-SOMA ($ B $ 2: B2)) / (5-LINHA () + LINHA ($ B $ 2)), 2)
Exemplo:
Contar usuários se o código do revendedor corresponder ou ano usando SUMPRODUCT
Portanto, aqui temos um conjunto de dados de vendedores. Os dados contêm muitas colunas. O que precisamos fazer é contar o número de usuários que possuem o código "INKA" ou o ano é "2016". Certifique-se de que se alguém tiver ambos (código como "inka" e ano de 2016) deve ser contado como 1.
Então, ouvi que temos dois critérios. Usamos a fórmula de SUMPRODUCT mencionada acima:
| = SUMPRODUCT (- (((Código = I3) + (Ano = K3))> 0)) |
Aqui, código e ano são intervalos nomeados. Isso retorna 7.
Nos dados, temos 5 registros de código INKA e 4 registros do ano 2016. Mas 2 registros têm "INKA" e 2016 como código e ano, respectivamente. E esses registros são contados como 1. E é assim que obtemos 7.
Vamos ver como isso funciona?
Então, vamos dar uma olhada em como a fórmula é resolvida passo a passo e, em seguida, discutirei como ela funciona.
-
- = SUMPRODUCT(- (((Código = I3) + (Ano = K3))> 0))
- SUMPRODUTO(- (({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE;…} + {FALSE; FALSE; FALSE; TRUE; TRUE;…})> 0))
- SUMPRODUTO(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0))
- SUMPRODUTO(- ({VERDADEIRO; FALSO; VERDADEIRO; VERDADEIRO; VERDADEIRO; VERDADEIRO;…})
- SUMPRODUTO({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})
7
Os sinais de duplo negativo (-) são usados para converter valores booleanos em 1s e 0s. Portanto, cada valor TRUE na matriz é convertido em 1 e FALSE em 0.
Adicionando mais critérios ou para contar usando SUMPRODUCT. Portanto, se você precisar adicionar mais critérios ou para contar, basta adicionar critérios usando o sinal + para a função.
Por exemplo, se você deseja adicionar outro critério à fórmula acima para que ela adicione o número de funcionários que venderam mais de 5 produtos. A fórmula SUMPRODUCT será simplesmente semelhante a esta:
| = SUMPRODUCT (- (((Código = I3) + (Ano = K3) + (Vendas> 5))> 0)) |
Simples! não é?
Mas digamos que você deseja ter dois critérios do intervalo de código. Digamos que você queira contar "INKB". Então como você faz isso? Um método é usar a técnica acima, mas isso seria repetitivo. Digamos que eu queira adicionar mais 10 critérios do mesmo intervalo. Nesses casos, essa técnica não é muito inteligente para contar com SUMPRODUCT.
Digamos que temos dados organizados assim.
Os códigos de critérios estão em uma linha I2: J2. A organização dos dados é importante aqui. A fórmula SUMPRODUCT para a configuração de contagem de 3 critérios OU será:
| = SUMPRODUCT (- (((Código = I2: J2) + (Ano = I3: J3))> 0)) |
Esta é a fórmula SUMPRODUCT para contar com vários critérios quando vários critérios de um intervalo são escritos em linha.
A fórmula acima retorna a resposta correta, que é 10.
Se você digitar qualquer ano em J3, a fórmula adicionará essa contagem também.
Isso é usado quando os critérios estão em uma linha. Funcionará quando os critérios em uma coluna para o mesmo intervalo? Não. Não vai.
Neste exemplo, temos vários códigos para contar, mas esses códigos de tipo são escritos em uma coluna. Quando usamos a fórmula de SUMPRODUCT acima, obtemos o erro ans # N / A. Não entraremos em detalhes sobre como esse erro ocorre, pois isso torna este artigo muito longo. Vamos ver como podemos fazer isso funcionar.
Para fazer essa fórmula funcionar, você precisa envolver os critérios de código na função TRANSPOSE. Isso fará com que a fórmula funcione.
| = SUMPRODUTO (- (((Código = TRANSPOR (H3: H4)) + (Ano = TRANSPOR (I3: I4)))> 0)) |
Esta é a fórmula para contar com várias condições ou no mesmo intervalo quando os critérios são listados em uma coluna.
Aqui estão todas as notas observacionais sobre o uso da fórmula.
Notas:
- A fórmula funciona para texto e números.
- Os valores de pesquisa podem ser fornecidos como referência de célula ou diretamente usando o símbolo de aspas (") na fórmula como argumentos.
Espero que você tenha entendido como dividir um valor em pagamentos iguais, evitando erros de divisão / arredondamento no Excel. Explore mais artigos sobre valor de pesquisa do Excel e funções do Excel 2019 aqui. Se você gostou de nossos blogs, compartilhe com seus amigos no Facebook. E você também pode nos seguir no Twitter e no Facebook. Gostaríamos muito de ouvir de você, diga-nos como podemos melhorar, complementar ou inovar nosso trabalho e torná-lo melhor para você. Escreva para nós no site de e-mail.
Use INDEX e MATCH para pesquisar o valor : Função INDEX & MATCH para pesquisar o valor conforme necessário.
Intervalo SUM com INDEX no Excel : Use a função INDEX para encontrar a SOMA dos valores conforme necessário.
Como usar a função INDEX no Excel : Encontre o INDEX de uma matriz usando a função INDEX explicada com um exemplo.
Como usar a função COMBINAR no Excel : Encontre o MATCH na matriz usando o valor INDEX dentro da função MATCH explicada com o exemplo.
Como usar a função LOOKUP no Excel : Encontre o valor de pesquisa na matriz usando a função LOOKUP explicada com um exemplo.
Artigos populares :
Como usar a função IF no Excel : A instrução IF no Excel verifica a condição e retorna um valor específico se a condição for TRUE ou retorna outro valor específico se FALSE.
Como usar a função PROCV no Excel : Esta é uma das funções mais usadas e populares do Excel, usada para pesquisar valores em diferentes intervalos e planilhas.
Como usar a função SUMIF no Excel : Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.
Como usar a função CONT.SE no Excel : Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar valores específicos. A função Countif é essencial para preparar seu painel.