Como usar fórmulas de matriz para ignorar erros no Excel

Para encontrar o valor médio de um intervalo contendo valores de erro – como # DIV / 0! Ou #NAME? – use as funções AVERAGE, IF e ISNUMBER juntas em uma fórmula de matriz. Às vezes, esses erros são gerados em uma planilha incompleta e esses erros serão eliminados posteriormente com a adição de novos dados. Se precisar encontrar o valor médio dos dados existentes, você pode usar a função AVERAGE junto com as funções IF e ISNUMBER em uma fórmula de matriz para fornecer a média enquanto ignora os erros.

Encontre a média em um intervalo

Observação: a fórmula abaixo só pode ser usada com um intervalo contíguo.

O exemplo abaixo usa a seguinte fórmula de matriz para encontrar a média para o intervalo D1 a D4.

• = MÉDIA (SE (ISNUMBER (D1: D4), D1: D4))

Nesta fórmula,

• ISNUMBER: teste para ver se todos os dados no intervalo D1: D4 são números – retorna TRUE ou FALSE apenas
• SE: inclui os valores que são números no intervalo a ser calculado pela média
• AVERAGE: encontra o valor médio (média aritmética) para todos os números no intervalo D1 a D4

Fórmulas CSE

Normalmente, ISNUMBER testa apenas uma célula de cada vez. Para contornar essa limitação, um CSE ou fórmula de matriz é usado, o que resulta na fórmula que avalia cada célula no intervalo D1 a D4 separadamente para ver se ela atende à condição de conter um número. As fórmulas de matriz são criadas pressionando as teclas Ctrl, Shift e Enter no teclado ao mesmo tempo, uma vez que a fórmula foi digitada. Devido às teclas pressionadas para criar a fórmula de matriz, às vezes são chamadas de CSE fórmulas.

Fórmula de matriz MÉDIA IF

1. Insira os seguintes dados nas células D1 a D4: 10, #NOME ?, 30, # DIV / 0!
   1. Como estamos criando uma fórmula aninhada e uma fórmula de matriz, precisaremos digitar a fórmula inteira em uma única célula da planilha.
   2. Depois de inserir a fórmula NÃO aperte o Entrar no teclado ou clique em uma célula diferente com o mouse, pois precisamos transformar a fórmula em uma fórmula de matriz.
2. Clique na célula E1 – o local onde os resultados da fórmula serão exibidos
3. Digite o seguinte: = MÉDIA (SE (ISNUMBER (D1: D4), D1: D4))

Criando a Fórmula de Matriz

1. Pressione e segure o Ctrl e Mudança teclas do teclado
2. aperte o Entrar tecla no teclado para criar a fórmula de matriz
3. A resposta 20 deve aparecer na célula E1, pois esta é a média para os dois números no intervalo 10 e 30
4. Ao clicar na célula E1, a fórmula de matriz completa {= AVERAGE (IF (ISNUMBER (D1: D4), D1: D4))}
   1. pode ser visto na barra de fórmulas acima da planilha

Substituindo MAX, MIN ou MEDIAN por AVERAGE

Devido à semelhança na sintaxe entre a função AVERAGE e outras funções estatísticas, como MAX, MIN e MEDIAN, essas funções podem ser substituídas na fórmula de matriz AVERAGE IF acima para obter resultados diferentes. Para encontrar o maior número do intervalo,

• = MAX (SE (ISNUMBER (D1: D4), D1: D4))
  • Para encontrar o menor número do intervalo,
• = MIN (SE (ISNUMBER (D1: D4), D1: D4))
  • Para encontrar o valor mediano no intervalo,
• = MEDIAN (SE (ISNUMBER (D1: D4), D1: D4))

Tal como acontece com a fórmula MÉDIA SE, as três fórmulas acima também devem ser inseridas como fórmulas de matriz.