Neste artigo, aprenderemos como obter a média com vários critérios no Excel.
Problema?
Por exemplo, temos uma grande lista de dados e precisamos encontrar a média do preço ou valor dado alguns critérios. Os critérios podem ser aplicados em qualquer coluna dos dados.
Como resolver o problema.
Agora faremos uma fórmula da função. Aqui nos são fornecidos os dados e precisamos encontrar a MÉDIA dos números com alguns critérios
Fórmula genérica:
= AVERAGEIFS (intervalo_média, intervalo1, critério1, [intervalo2, critério2], [intervalo3, critério3], …)
- Intervalo_média: intervalo de valores onde a média precisa ser avaliada
- intervalo1: primeiro intervalo onde o critério1 é aplicado.
- critérios1: primeiros critérios aplicados no intervalo1.
- intervalo2: segundo intervalo onde o critério1 é aplicado.
- critérios2: segundo critério aplicado no intervalo2.
- intervalo3: terceiro intervalo onde o critério1 é aplicado.
- critérios3: terceiro critério aplicado no intervalo3.
Notas: não forneça encontro diretamente para a função. Use a função DATE ou use a referência de célula para o argumento de data no Excel, pois o Excel lê a data apenas na ordem correta.
Exemplo:
Tudo isso pode ser confuso de entender. Portanto, vamos testar essa fórmula executando-a no exemplo mostrado abaixo.
Aqui, fornecemos os dados de A1: D51. Precisamos encontrar a MÉDIA da quantidade recebida de acordo com diferentes critérios.
As condições são as seguintes.
- A data deve ser anterior a 31/01/2019.
- Cidade "Boston"
- Produto definido como "Cenoura"
- A quantidade deve ser maior que 50 (> 50).
As 4 condições ou critérios mencionados acima precisam ser atendidos. A função AVERAGEIFS pode nos ajudar a extrair a média com critérios diferentes.
Use a fórmula:
= AVERAGEIFS (Quantidade, data_de_pedida, "<=" & F4, cidade, "Boston", produto, F5, Quantidade, F6)
Quantidade: intervalo nomeado usado para a matriz D2: D50
Order_date: intervalo nomeado usado para a matriz A2: A50
Cidade: intervalo nomeado usado para a matriz B2: B50
Produto: intervalo nomeado usado para C2: matriz C50
F4: referência de célula usada para o critério 1
F5: referência de célula usada para o critério 2
F6: referência de célula usada para o critério 3
Explicação:
- data_de_pedida, "<=" & F4primeiro critério que sugere data anterior a 31/01/2019 em F4.
- cidade, "Boston" segundo critério em que cidade corresponde a "Boston"
- produto, F5 onde o produto corresponde a Cenoura na célula F5
- Quantidade, deve estar sob os critérios em Célula F6 ou seja, maior que 50.
Aqui, A2 é dado como referência de célula e intervalos nomeados dados como rng (D2: D51) e data do pedido (A2: A51).
Como você pode ver no instantâneo acima, a MÉDIA da quantidade em janeiro acaba sendo 54.00. Como existe apenas um valor que satisfaz todas as condições.
Exemplo 2:
Aqui fornecemos os dados de A1: D51. Precisamos encontrar a MÉDIA da quantidade recebida de acordo com diferentes critérios.
As condições são as seguintes.
- A data deve ser anterior a 27/02/2019.
- Cidade "Nova York"
- O produto deve ser definido como "Chocolate Chip".
- A quantidade deve ser inferior a 50 (<50).
As 4 condições ou critérios mencionados acima precisam ser atendidos. A função AVERAGEIFS pode nos ajudar a extrair a média com critérios diferentes.
Use a fórmula:
= AVERAGEIFS (quantidade, data do pedido, "<=" & F4, cidade, "Nova York", produto, F5, Quantidade, F6)
Quantidade: intervalo nomeado usado para a matriz D2: D50
Order_date: intervalo nomeado usado para a matriz A2: A50
Cidade: intervalo nomeado usado para a matriz B2: B50
Produto: intervalo nomeado usado para C2: matriz C50
F4: referência de célula usada para o critério 1
F5: referência de célula usada para o critério 2
F6: referência de célula usada para o critério 3
Explicação:
- data_de_pedida, "<=" & F4primeiro critério que sugere data antes de 27/02/2019 na célula F4.
- cidade, "Boston" segundo critério em que cidade corresponde a "Boston"
- produto, F5 onde o produto corresponde a Cenoura na célula F5
- Quantidade, deve estar sob os critérios em Célula F6 ou seja, menos de 50.
Aqui, A2 é dado como referência de célula e intervalos nomeados dados como rng (D2: D51) e data do pedido (A2: A51).
Como você pode ver no instantâneo acima, a MÉDIA da quantidade em janeiro acaba sendo 33.33… . Como existe apenas um valor que satisfaz todas as condições.
Aqui estão algumas observações sobre o uso da fórmula abaixo.
Notas:
- A função retorna #VALUE! erro se o argumento para a função não for numérico.
- A função retorna #VALUE! erro se nenhum valor corresponder aos critérios fornecidos.
- A fórmula aceita operadores como =, <, =, <= &.
- Não forneça o argumento de data diretamente para a função. Use a função DATE ou use a referência de célula para o argumento de data no Excel, pois o Excel lê a data apenas na ordem correta.
Espero que este artigo sobre como calcular a média com vários critérios no Excel seja explicativo. Encontre mais artigos sobre as funções SUMPRODUCT aqui. Por favor, compartilhe sua consulta abaixo na caixa de comentários. Nós iremos atendê-lo.
Como usar a função AVERAGEIFS no Excel
Como usar a função DAVERAGE no Excel
Como destacar células acima e abaixo do valor médio
Ignore zero na média dos números
Calcular a média ponderada
Diferença média entre listas
Validação de entradas de texto
Crie uma lista suspensa no Excel com cores
Artigos populares
Editar uma lista suspensa
Referência absoluta no Excel
Se com formatação condicional
Se com curingas
Vlookup por data
Junte-se ao nome e sobrenome no excel
Contar células que correspondam a A ou B
Converter polegadas em pés e polegadas no Excel 2016
50 Atalho do Excel para aumentar sua produtividade