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 vários critérios OR.
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.
Critério 2: 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.
Exemplo: contar usuários se o código do revendedor ou anoFósforos 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, aqui 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. Esses registros são contados como 1. E é assim que obtemos 7.
Como funciona?
Então, vamos dar uma olhada em como a fórmula é resolvida passo a passo e, em seguida, discutirei como ela funciona.
| =SUMPRODUTO(- (((Código = I3) + (Ano = K3))> 0)) |
| 1=>SUMPRODUTO(- (({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE;…} + {FALSE; FALSE; FALSE; TRUE; TRUE;…})> 0)) |
| 2=>SUMPRODUTO(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0)) |
| 3=>SUMPRODUTO(- ({VERDADEIRO; FALSO; VERDADEIRO; VERDADEIRO; VERDADEIRO; VERDADEIRO;…}) |
| 4=>SUMPRODUTO({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0}) |
| 5=>7 |
Na primeira etapa, o valor de I3 ("INKA") é comparado com cada célula no intervalo de código. Isso retorna uma matriz TRUE e FALSE. VERDADEIRO para cada partida. Para economizar espaço, não mostrei tudo VERDADEIRO-FALSO. Da mesma forma, o valor de K3 (2016) é combinado com cada célula no intervalo do ano.
Na próxima etapa, adicionamos essas duas matrizes que resultam em uma nova matriz de valores numéricos. Como você deve saber, TRUE é tratado como 1 e FALSE como 0 no Excel. Então, quando TRUE e TRUE são adicionados, obtemos 2 e o resto você pode entender.
Na próxima etapa, verificamos qual valor é maior que 0 no array. Isso novamente converte a matriz em uma matriz falsa verdadeira. Para cada valor 0 obtido, False e rest são convertidos em true. Agora, o número de valores TRUE no array é a nossa resposta. Mas como os contamos? Veja como.
Sinais negativos duplos (-) são usados para converter valores booleanos em 1s e 0s. Portanto, cada valor TRUE na matriz é convertido em 1 e FALSE em 0.
Na etapa final, o SUMPRODUCT resume essa matriz e obtemos nossa resposta como 7.
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ê queira ter dois critérios de Código faixa. 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 3 configurações de contagem de 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 uma linha.
Isso 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 estiverem 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 ocorreu, pois isso tornará 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.
Então, sim, cara, espero ter sido claro o suficiente e fazer sentido. Espero que sirva ao seu propósito de estar aqui. Se esta fórmula não resolveu seu problema, deixe-me saber seus requisitos na seção de comentários abaixo. Ficarei mais do que feliz em ajudá-lo de alguma forma. Você pode citar qualquer dúvida relacionada ao Excel / VBA. Até então continuar aprendendo, continue se destacando.
Como usar a função SUMPRODUCT no Excel: Retorna a soma após a multiplicação de valores em várias matrizes no Excel. Esta função pode ser usada para realizar várias tarefas. Esta é uma das funções mais versáteis.
COUNTIFS com intervalo de critérios dinâmicos : Para contar com faixa de critérios dinâmicos, simplesmente usamos a função INDIRETO. Esta função pode
COUNTIFS com OU para vários critérios : Conta células que têm vários critérios que correspondem usando a função OU. Para colocar uma lógica OR na função COUNTIFS, você não precisará usar a função OR.
Usando IF com funções AND / OR no Microsoft Excel : Essas funções lógicas são usadas para realizar cálculos de vários critérios. Com IF, as funções OR e AND são usadas para incluir ou excluir correspondências.
Como usar a função OR no Microsoft Excel : A função é usada para incluir todos os valores TRUE em vários critérios.
Como contar células que contêm isto ou aquilo no Excel no Excel : Para células que contêm isto ou aquilo, podemos usar a função SUMPRODUCT. Veja como você faz esses cálculos.
Artigos populares:
50 atalhos do Excel para aumentar sua produtividade | Torne sua tarefa mais rápida. Esses 50 atalhos farão você trabalhar ainda mais rápido no Excel.
Como usar a função VLOOKUP do Excel| Esta é uma das funções mais usadas e populares do Excel, que é usada para pesquisar valores em diferentes intervalos e planilhas.
Como usar o Excel Função CONT.SE| 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.
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.
