Monstros

Monstros aparecem sempre quando você menos espera. São aqueles imprevistos cuja possibilidade de existir você sempre avalia por baixo. Mas na hora H, a lei de Murphy aparece para lembrar que os monstros existem.

Meus monstros chamam-se Planilhas-Excel-Mal-Feitas. Ora direis, planilha são monstros? Pois sim, são verdadeiros monstros aquelas planilhas que são construídas com tabelas de um banco de dados carregados no Excel.

Aplaudo a esperteza dos desenvolvedores do Excel. Conseguem milagres na hora de perdoar a ignorância do usuário. Ponto a favor deles, qualquer pessoa com algumas sinapses ativas acaba resolvendo o problema dos monstros.

Aparentemente o Excel consegue abrir uma planilha sem precisar carrega-la totalmente na memória. Já sai mostrando algo sem mesmo ter terminado de ler o arquivo. Realmente é fantástico, pois assim a impressão de rapidez mascara os arquivos monstruosos, cheios de tabelas e fórmulas de consolidação de dados. O Excel só vai reclamar na hora de movimentar a planilha. Quando chega neste momento, começam as dores.

Mas descubro logo que no meu BrOffice, estas planilhas irão dar problemas sérios. Não tenho como evitar... para abrir a planilha, o BrOffice tem de descompactar o XML por inteiro. Planilhas Excel de 20 a 50 MB causarão problemas na certa. Já causam. Mesmo quando reduzidas pelo zip, continuam monstruosas na hora de abrir e de movimentar os dados. Fora o tempo de descompactação.

Grandes males, Grande remédios. Olho de que são feitas estas planilhas. Observo que tabelas de banco de dados são inseridas em cada folha. Cálculos são feitos para selecionar dados com a função SE. Contas são feitas em cima da seleção obtida. Formulas são repetidas “ad nauseam” para encher células com os resultados intermediários e finalmente acumulados na primeira folha mostrando os dados consolidados.

Maravilha.

Não conhecem “SOMASE” ou “SOMARPRODUTO”.

Para que servem os dados intermediários? Por que não podemos economizar fórmulas, tempo de cálculo e espaço de memória?

Num orçamento com QUANTIDADE x PREÇO = TOTAL PARCIAL, costumamos somar os totais parciais para obter a soma final. Precisamos dos totais parciais? Por que senão, eu já sei o resultado:

TOTAL = SOMARPRODUTO( QUANTIDADE ; PREÇO )

E economizo, “N” formulas da minha planilha, reduzindo seu tamanho e todo o “overhead” para exibir os totais parciais.

Numa seleção de dados com acumulação e classificação, chega-se a seguinte formula:

F1 = SE(A1=condição;B1;0) e depois soma-se na coluna F.

Pois o resultado será

RESULTADO = SOMASE ( SELEÇÂO; ”condição” ; ACUMULAÇÂO )

Duas simples alterações e uma redução significativa de tamanho na planilha.

A título e exemplo, um monstro de 19MBytes com 32 folhas (1 para cada dia do mês e uma na frente consolidando), cada folha com 150 linhas por 50 colunas, cheias de formulas de teste SE, resultou no seguinte arquivo:

• A planilha XLS foi convertida tal qual em formato ODS: Tamanho 2,5 Mbytes
• A planilha ODS foi re-escrita usando as técnicas acima: Tamanho 300 KBytes.

Sumiu o monstro.

Xô!