Não Usar Colunas Inteiras em Fórmulas

Por que não devemos usar colunas inteiras em fórmulas no Excel? Neste artigo você aprenderá detalhes de porque não usar.

1. Introdução

Usar colunas inteiras referenciadas em fórmulas de planilhas no Excel é tecnicamente uma má prática. Apesar de “funcionar”, esse hábito traz uma série de efeitos colaterais que afetam desempenho, confiabilidade, manutenção e escalabilidade das planilhas. Esta péssima prática é muito difundida entre os usuários e é responsável por uma série de danos às planilhas que nem sempre ocorrem de imediato.

Fórmulas como =SOMA(D:D) ou =FILTRO(A:C;A:A="Não") podem afetar bastante os trabalhos. Podemos citar a sobrecarga desnecessária de cálculo. Quando é usado algo como =SOMA(D:D) ou =FILTRO(A:C; A:A="Não") o Excel é obrigado a processar todas as linhas possíveis da coluna, e não apenas as que contêm dados. Isto pode comprometer sensivelmente a performance, exigindo mais uso de hardware para retornar os resultados. Como uma planilha em versões desde o Excel 2007 possui 1.048.576 linhas por coluna, mesmo que a fórmula seja colocada em apenas 500 linhas, o Excel avalia tudo.

Com o passar do tempo, é notado recálculo mais lento, travamentos frequentes, uso excessivo de memória etc. Planilhas que “demoram para responder” e em arquivos grandes teremos então um gargalo sério. Degradando drasticamente a performance teremos uma perda insuportável de produtividade, deixando o uso do Excel de fazer sentido.

A própria abertura e fechamento das pastas de trabalho passa a tomar muito mais tempo do que o esperado. Isso já é sinal de que há algo de errado que pode ser corrigido/otimizado.

Este artigo, além de abominar e reprovar esta prática, indica saídas e alternativas para extinguir esta prática e os problemas dela derivados.

2. Alternativas, recomendações e boas práticas

Uma primeira alternativa, que é de simples implementação, é a delimitação do intervalo para apenas o intervalo delimitado e realmente necessário. Usa-se, nestes casos, o mínimo aceitável. Uma alternativa para a fórmula =SOMA(D:D) é =SOMA(D2:D1000), que agora, em vez de processar 1.048.576 células, fica mais breve ao processar 999 células. Assim teremos uma fórmula expressivamente mais leve.

Este cenário ainda exige manutenção manual se o volume crescer, com dados sendo acrescentas por ocuparem novas linhas.

2.1 Conversão dos dados para o objeto tabela (desde o Excel 2007)

Esta recomendação é mandatória e praticamente obrigatória nos trabalhos realizados com o Excel. Acesse o artigo disponível em https://www.guiadoexcel.com.br/como-aplicar-e-remover-o-formato-de-tabela-no-excel/ e saiba como implementar as tabelas sempre que necessário e possível.

Com as tabelas, teremos inúmeras vantagens, que podem ser apresentadas neste outro artigo https://www.guiadoexcel.com.br/vantagens-do-formato-de-tabela-no-excel/. Especialmente os itens b) e c) deste artigo elencam os benefícios do trabalho com fórmulas.

2.2 Função APARARINTERVALO (Excel 365)

Recentemente a Microsoft inovou no Excel, disponibilizando a função APARARINTERVALO. Esta função é capaz de reconhecer os limites dos dados e reduzir o volume, retornando apenas o que interessa.

Para melhor compreensão, tomemos como base os seguintes dados, que ocupam o intervalo de B2 a K22:

Em nosso caso, as fórmulas convencionais poderiam até usar o intervalo B:K para poder contemplar todos os dados. Porém, quando isso é realizado através da função APARARINTERVALO, teremos um resultado mais preciso, performático e delimitado, conforme visualizamos na imagem seguinte.

Agora, podemos continuar nossas fórmulas usando o resultado de APARARINTERVALO, como o exemplo da imagem a seguir, que classifica as datas em ordem decrescente.

Fazer referência a um intervalo tratado com APARARINTERVELO pode ser uma excelente alternativa sempre que desejamos corrigir um problema de desempenho causado por referências grandes que geram cálculos em excesso.

Para obter os mesmos efeitos de APARARINTERVALO, a Microsoft implementou novos operadores para intervalos, além de : e ; historicamente conhecidos. Os novos operadores são:

a) .:.

b) .:

c) :.

O operador citado no item a), por exemplo, faz um “corte” completo dos dados. Veja na imagem seguinte o resultado da fórmula =B.:.B.