Função do Excel que calcula a média excluindo primeiro todos os zeros e excluindo o valor mínimo e máximo

Question

Eu tenho cerca de 300 valores em uma planilha do Excel (muitos dos quais são zero) e quero calcular a média excluindo os zeros, o maior valor e o menor valor após zero. Qual seria uma fórmula apropriada?

Obrigado antecipadamente.

edit: O que eu tentei até agora

Supondo que meus valores estejam em A1: A300, fiz o seguinte:

=AVERAGEIFS(A1:A100, A1:A100, "<>0", A1:A100, "<>SMALL(A1:A100, COUNTIF(A1: A100,0)+1)", A1:A100, "<>LARGE(A1: A100, COUNTIF(A1: A100))")

Ele descarta com sucesso os zeros, mas não o máximo ou o mínimo.

    

Answer 1

Suponho que o resultado deve ser impresso em uma célula sem usar outras células para imprimir resultados provisórios. Isso provavelmente funciona apenas com fórmulas de matriz, código VBA ou consultas de banco de dados (resposta de vembutech ).

Fórmulas de matriz

Consulte Diretrizes e exemplos de fórmulas de matriz e Criar uma fórmula de matriz para detalhes.

Uma solução

No meu exemplo, os valores para média estão nas células de grade A1 a A11.

Esta fórmula resolve o problema:

=AVERAGE(IF((A1:A11=MAX(A1:A11))+(A1:A11=MIN(IF($A$1:$A$11=0,"",$A$1:$A$11)))+(A1:A11=0),"",A1:A11))

depois de inseri-lo, você precisa digitar CTRL + SHIFT + ENTER (não apenas ENTER , veja Crie uma fórmula de matriz ).

Idéia por trás dessa solução

A função AVERAGE (outra então AVERAGEA ) calcula o valor médio de todas as células que contêm um número. Células contendo uma string ou um booleano / lógico são ignoradas. O que faremos é: definir essas células para "" (uma string vazia), que igual a 0, igual ao valor mínimo da célula (0 excluído) ou igual ao valor máximo da célula. Basicamente, usamos essa fórmula:

=AVERAGE(IF(_TODO_,"",A1:A11))

Agora precisamos substituir o TODO por algum conteúdo (as três situações descritas). Essas três situações são testadas por:

A1:A11=MAX(A1:A11)
A1:A11=MIN(IF($A$1:$A$11=0,"",$A$1:$A$11))
A1:A11=0

O segundo teste é um pouco complicado: queremos excluir o '0' (zero) do cálculo do valor min. A função MIN ignora todos os valores não numéricos (por exemplo, uma string vazia ""). O IF($A$1:$A$11=0,"",$A$1:$A$11) define todas as células para "" que são iguais a zero. Todas as células restantes mantêm seus valores originais.

Agora combinamos os três testes. Isso é feito colocando colchetes ao redor dos três testes e combinando-os por um + , que representa um OR lógico.

(A1:A11=MAX(A1:A11))+(A1:A11=MIN(IF($A$1:$A$11=0,"",$A$1:$A$11)))+(A1:A11=0)

Este teste combinado é agora preenchido em nossa fórmula na parte superior, substituindo _TODO_ :

=AVERAGE(IF((A1:A11=MAX(A1:A11))+(A1:A11=MIN(IF($A$1:$A$11=0,"",$A$1:$A$11)))+(A1:A11=0),"",A1:A11))

Outra solução

Com base na abordagem do user3396592 também é possível escrever:

=AVERAGEIFS(A1:A11,A1:A11,"<>0",A1:A11,"<>"&MAX(A1:A11), A1:A11,"<>"&MIN(IF($A$1:$A$11=0,"",$A$1:$A$11)))

    

Answer 2

Espero que o formulário abaixo atenda a sua exigência

=QUERY(A:A,"select avg(A) where A > 0")

Digamos, por exemplo, se você tiver 100 números de zero linhas de 300, isso irá ignorar as 100 linhas e calcular a média das 200 linhas restantes.