Função "DESLOC" ignorando células com erro ou vazias

Question

Olá!

Estou tentando criar um intervalo dinâmico através da função "DESLOC" em combinação com funções de contagem ("CONT.VALORES"; "CONT.NÚMEROS", etc).

O problema é que no intervalo de valores que esta função irá me trazer, há células com erro, por exemplo:

89

#N/D

76

#N/D

#N/D
#N/D

35

Eu quero que o "DESLOC" me retorne somente um intervalo com os números, ignorando os erros (sem deixar células vazias entre os números).

É possível?

Aguardo uma resposta.

Obrigado!!

Answer 1

Como você diz que usou a segunda fórmula, isso significa que está usando o Excel 365. Nesse caso, use qualquer uma das fórmulas a seguir para extrair os dados que está procurando (veja a tela de impressão).

• Para extrair números e textos de um determinado intervalo. Ambas as fórmulas a seguir são equivalentes, mas recomendo que você use a primeira fórmula, pois ela é usada apenas uma vez na fórmula de intervalo A2:A20. As fórmulas foram aplicadas às células C2 e D2, respectivamente.

=LET(

     **range;A2:A20;**

     **FILTRO(range;NÃO(ÉERROS(ÉERROS(range)/(range<>""))))**

)

ou

=FILTRO(A2:A20;NÃO(ÉERROS(ÉERROS(A2:A20)/(A2:A20<>""))))

• Para extrair apenas textos de um determinado intervalo. A fórmula é aplicada à célula E2.

=FILTRO(A2:A20;ÉTEXTO(A2:A20))

• Para extrair apenas números de um determinado intervalo. A fórmula é aplicada à célula F2.

=FILTRO(A2:A20;ÉNÚM(A2:A20))

Todas as fórmulas acima ignoram os casos em que há mensagens de erro #N/D nas células e também ignoram as células vazias.

Se eu respondi sua pergunta, por favor, não esqueça de marcar esta resposta!

Att.

Answer 2

A função DESLOC é uma função volátil e por este motivo eu não recomendo o uso desta função.

Se você deseja extrair apenas números de uma string que contém números e erros em células diferentes, pode usar a seguinte fórmula (consulte a tela de impressão). Aplique esta fórmula à célula C2 e arraste-a para baixo.

=SEERRO(ÍNDICE(SEERRO($A$2:$A$10;"")/($A$2:$A$10<>"");CORRESP(0;CONT.SE($C$1:$C1;SEERRO($A$2:$A$10;""))+($A$2:$A$10="");0));"")

Se você usa o Office 365, pode usar esta fórmula mais simples:

=FILTRO(A2:A10;ÉNÚM(A2:A10))

Espero ter ajudado. Caso isso responda à sua pergunta, "Sim", para outros usuários podem se beneficiar.

Att.

Answer 3

Muito obrigado, era exatamente o que eu precisava!

Utilizei a segunda fórmula que você mostrou por ser bem mais simples.

Mas e se fosse texto ao invés de números no exemplo que eu dei? Como eu extrairia os textos ignorando os erros (sem deixar células vazias entre os textos)? Exemplo:

Banana

#N/D

Maça

#N/D

#N/D
#N/D

Laranja

Eu tentei utilizar o raciocínio que você explicou, utilizando outras fórmulas no lugar de "ÉNÚM", mas não deu certo.

É que eu estou tentando criar um gráfico dinâmico e preciso que as categorias também variem de forma automática.