Quem sou eu

São Paulo, São Paulo, Brazil

quinta-feira, 24 de setembro de 2009

Tirar uma média desconsiderando células vazias (valor nulo)

Vc precisa preparar um relatório com a média de número alunos por escola e por período. Vc tem uma base de dados com todas estas informações, conforme o modelo abaixo:



Note que algumas lojas (em cinza na figura) não estavam abertas durante todos os períodos. Portanto, quando tirarmos a média, devemos desconsiderar estes casos para não distorcer a média total. Neste exemplo, calcularemos tanto a média por período (coluna F da figura acima), quanto a média por escola (linha 21 da figura acima).
Se utilizarmos a fórmula MÉDIA e selecionarmos as células dos período de 2006 à 2009, podemos ter alguns problemas, já que esta fórmula soma os valores destas células e divide pela quantidade de células selecionadas, conforme demonstrado abaixo:



Vemos que a Escola 14 (em vermelho na figura acima) ficou com a média totalmente distorcida, pois a soma das células de 364 (0+0+0+364), sendo que este total foi dividido por 4, enquanto que o correto seria desconsiderar os períodos zerados e calcular apenas o último período.
Para que o Excel dê este tratamento, precisamos utilizar outra fórmula: o CONT.SE. Para cada escola, somaremos todos os períodos e pediremos que o Excel divida pela quantidade de células que são diferentes de zero. Veja abaixo como devem ficar as fórmulas na coluna “Média dos 4 Anos” (não estranhe a figura abaixo, pois utilizei uma função no Excel que mostra as fórmulas de todas as células ao invés de apenas o resultado – CTRL + Shift + ` ):



Apenas fazendo um parêntese, eu aconselho para aqueles que costumam utilizar a tela de argumentos de fórmulas (quando seleciona a fórmula diretamente por Inserir>Função) a se acostumarem a escrever a fórmula diretamente na célula, pois para as fórmulas um pouco mais “cabeludas” facilita bastante, já que mais de uma fórmula pode ser utilizada na mesma célula (com no nosso exemplo acima, onde usamos tanto a fórmula de SOMA, quanto CONT.SE).
O resultado final da planilha deve ser o seguinte:



Espero que tenha ajudado. Qualquer dúvida é só escrever.

10 comentários:

  1. Colegas boa noite, tenho uma planilha com 31 tabelas iguais(se referem a cada dia do mês), entre cada tabela há uma linha vazia e mesclada. Em cada tabela os itens se repetem e as quantidades podem ser iguais ou diferentes depende da produção do dia. Assim explicado peço ajuda para somar estas quantidades do mesmo item que se repete em todas ou algumas células automaticamente envolvendo as 31 tabelas.
    OBS: Nesta formula nao podera contar as células vazias. fico no aguardo.

    ResponderExcluir
  2. Cara muito obrigado, sua dica deu certinho em minha planilha!!!!
    Vlw

    ResponderExcluir
  3. Beleza deu certo... Vlw, mas quando utilizei a formatação condicional do 10 primeiros ele apliaca a todas as células com o =soma../cont.se....
    Como eu consigo concertar isso?? No =media dava certinho...

    att

    ResponderExcluir
  4. Estas fórmulas não estão funcionando quando as células não estão na sequência.No meu caso, são duas células: Uma na H6 e a outra na O6 e, quando uma delas é nula, eu não posso dividir por 2 e sim por 1. Como devo fazer??

    ResponderExcluir
    Respostas
    1. Ou cria uma função (VBA) ou copia os valores para im intervalo em sequencia.

      Excluir
  5. OTIMO, OBRIGADO POR POSTAR A FORMULA, COM PESSOAS ASSIM COMO VC, NOS AJUDA A RESOLVER NOSSAS DIFICULDADES.

    ResponderExcluir
  6. Saudações, belo trabalho. Aprendi bastante mas ainda tenho uma dúvida.
    Vocês saberiam dizer se existe uma fórmula onde eu faço a média, excluindo os valores do extremo, máximo e mínimo? Exemplo de notas:
    1) 100
    2) 8
    3) 7
    4) 7
    5) 0

    Gostaria de excluir os lançamentos que considero "fora do normal" e fazer a média, que no exemplo daria (8+7+7)/3.

    Cheguei nesse blog procurando pelo desvio padrão mas acho que ainda não seria isso.

    PS: Tenho uma fórmula onde uso a matemática mas ela só leva em consideração o máximo e o mínimo, que no meu caso não é tão útil.
    =(SUM(W1:W5)-MAX(W1:W5)-MIN(W1:W5))/(COUNT(W1:W5)-2)

    Gostaria de fazer algo bem mais elegante.

    Se alguém ajudar eu agradeço muito.

    ResponderExcluir
  7. Obrigado!
    Me ajudou muito!
    Forte abraço!

    ResponderExcluir