Sabemos que a função COUNTIFS no Excel pode contar com vários critérios. Leva argumentos como um par de intervalo de critérios e critérios. Podemos alterar os critérios dinamicamente, fornecendo a referência da célula, mas não podemos alterar a coluna de critérios dinamicamente. Bem, não diretamente, mas podemos. Isso é o que aprendemos com as fórmulas do Excel com antecedência. Fazer coisas no Excel que não podem ser feitas normalmente. Vamos ver como.
Vamos aprender pelo exemplo.
Contexto
Elaborei aqui um dado de vendas realizado em diferentes meses do ano pelos nossos consultores de vendas. Eles vendem diferentes modelos de nosso produto, genericamente denominados model1, model2 e assim por diante. O que precisamos fazer é contar as vendas de modelos diferentes em meses diferentes de forma dinâmica.
Na célula I2 escolheremos o mês. Na célula I2 escolheremos o modelo. Esses valores podem ser alterados. E a contagem também deve mudar. A função COUNTIFS deve procurar a coluna do mês que será o intervalo de critérios. Em seguida, ele procurará o modelo naquela coluna de meses.
Portanto, aqui os critérios e intervalo_critérios são variáveis. Então, como tornamos a coluna variável em COUNTIFS? Aqui está como?
Usando intervalo nomeado para coluna variável ou intervalo de critérios
Fórmula Genérica
= COUNTIFS (INDIRECT (named_range), critérios)
Primeiro, nomeie cada coluna de acordo com seus títulos. Para fazer isso, selecione a tabela e pressione CTRL + SHIFT + F3 e nomeie as colunas de acordo com a linha superior. Leia sobre isso aqui.
Portanto, Intervalo B3: B11, C3: C11, D3: D11 e E3: E11 são denominados janeiro, fevereiro, março e abril, respectivamente.
Escreva esta fórmula em I4.
= COUNTIFS (INDIRETO (I2), I3)
Agora, se você alterar o mês em I4, a contagem dos respectivos meses do Modelo4 será exibida em I4.
Como funciona?
A fórmula é simples. Vamos começar de dentro.
INDIRETO (I2): Como sabemos, a função INDIRETA converte a referência do texto em referência real. Nós o fornecemos I2. I2 contém abril. Como temos o intervalo E3: nome E11 abril, INDIRETO (I2) se traduz em E3: E11.
A fórmula simplificada para = COUNTIFS (E3: E11, I3). COUNTIFS conta o que estiver em I3 no intervalo E3: E11.
Quando você muda o mês, a coluna muda dinamicamente. Isso é chamado de COUNTIFS com colunas variáveis. No gif, usei alguma formatação condicional com base em outra célula.
A fórmula também pode funcionar com a função countif. Mas se você quiser ter várias condições, use a função COUNTIFS.
Gráfico de colunas de criativos que inclui totais
Criar gráfico de sobreposição no Excel 2016
Executar gráfico de Pareto e análise no Excel
Executar gráfico em cascata no Excel
Minigráficos do Excel: os gráficos minúsculos na célula
Gráfico do velocímetro (medidor) no Excel 2016