Excel: Calcule estatísticas (como uma média) de todos os dados diferentes de zero

Question

Eu tenho uma matriz de dados que contém alguns zeros no final (embora eu gostaria de não assumir que eles estão no final) e calcular estatísticas sobre aqueles que não são zero. Eu gostaria de NÃO usar as fórmulas Ctrl-Shift-Enter (que são praticamente manteníveis) e poder usar qualquer função de estatísticas (como QUARTILE) em vez de recodificar a função média por SUM () / COUNT ().

Aqui está um exemplo para deixar mais claro: meus dados em A1: A10 contém (1,2,3,4,5,6,7,8,0,0). Eu quero obter os quartis, assim como abaixo, mas dinamicamente calcular o "A1: A8"

=QUARTILE(A1:A8, n) // ignoring the zeros

Claro que eu poderia conseguir isso assumindo que os zeros estão no final apenas construindo o primeiro argumento para QUARTILE () com

"A1" & ":" & ADDRESS( x + COUNTIF(A1:A10; "<>0") )

mas, novamente, gostaria de poder ter zeros dentro da minha matriz, não apenas no final.

Ou eu vou ter que mergulhar fundo no VBA?

    

Answer 1

Algumas das funções podem ser substituídas por IF ou IFS variants, e. SUMIF , SUMIFS

por exemplo. um MÉDIA pode ser calculado usando =SUMIF(A1:A10,"<>0")/COUNTIF(A1:A10,"<>0") e SUMIFS pode usar mais de 1 critério, portanto, pode ser útil para consultas mais complexas

Uma lista das funções que podem usar critérios de seleção em seus cálculos:

• AVERAGEIF
• AVERAGEIFS
• SUMIF
• SUMIFS
• COUNTIF

A outra abordagem seria usar uma coluna auxiliar, que tenha =IF(A1=0,"",A1) para que os espaços em branco substituam os zeros e, portanto, não afetem as funções estatísticas