▷ Modelos Dinâmicos e Estáticos no Excel

Bem vindo ao meu novo artigo! Neste texto pretendo escrever sobre as diferenças entre o modelos "Estáticos" e "Dinâmicos". Por ser um assunto pouco divulgado, vou explicar suas características principais.

Quando você começar o desenvolvimento de uma solução em uma pasta de trabalho do MS Excel, lembre-se que elas serão finitas! Finitas porque o Excel possui 1.048.576 linhas e 16.384 colunas. Devido a essa limitação é comum e necessário adaptar nossas fórmulas e funções quando inserimos registros adicionais (linhas ou colunas). Esse modelo é o "estático". Vamos trabalhar com dois exemplos:

Exemplo da fórmula SOMA

Neste caso, é comum selecionar apenas o intervalo desejado mas, algum dia precisaremos aumentar o intervalo para incluir mais valores para serem somados. Em outros casos alguns usuários selecionam a coluna inteira (A:A) mas não se preocupam com o desempenho dos cálculos.

Uma aplicação com o PROCV (desculpe mas odeio essa fórmula, recomendo nunca utilizar!) tem uma área limitada de busca (argumento matriz_tabela). Nessa situação como você aumentaria mais um mês no seu intervalo de dados?

Este é um exemplo clássico! Você faz uma tabela utilizando o PROCV (VLOOKUP em inglês) e coloca na validação de dados todos os seus valores deixando no mês correto. Mas, é necessário alterar o mês e adicionar mais registros. Como fazer? Essa é uma dúvida muito comum nos fóruns de discussão. Utilizando esse método será necessário sempre ficar adaptando os seus códigos, ajustar os números das colunas, área dos intervalos e assim sucessivamente ocasionando muitos erros, falhas de atenção e análise e outros mais.

Por isso que temos que desenvolver modelos "dinâmicos" onde o próprio MS Excel irá se ajustar de maneira automática sem que você precise toda a hora ficar arrumando/ajustando fórmulas, perdendo literalmente "tempo". A complexidade de criar um modelo estático é muito mais simples do que criar um modelo dinâmico, onde (nos modelos dinâmicos) teremos que envolver muito mais fórmulas, muito mais raciocínio, onde muitas vezes mudar até a estratégia de resolução para que funcione de maneira "dinâmica".

Para isso apresento uma das fórmulas "supremas" (no meu ponto de vista), que é o grandioso DESLOC (OFFSET em inglês)

Como que ela funciona?

A função DESLOC funciona conforme indico na imagem acima. Você precisa de uma célula "zero inicial". Eu sempre utilizo a célula A1 (pois é raro o usuário tentar inserir uma linha acima da linha 1 e colocar mais uma coluna antes da coluna A), e podemos deslocar quantas linhas e quantas colunas, mas lembrando que esta fórmula começa com um ponteiro de descolamento em 0, outras fórmulas como PROCV, CORRESP começam com 1 e isso que confunde bastante os usuários. Sem contar com os intervalos facultativos de altura e largura.

E porque ela é "suprema"?

Porque você não precisa selecionar nenhum intervalo manualmente! Você poderá deixar tudo dinâmico!

Vamos a algumas aplicações!

Irei demostrar outras formulas (que eu considero) "supremas" em outros artigos, mas vamos a estes exemplos elegantes. Só um detalhe: todos os modelos abaixo não foram desenvolvidos com VBA (Visual Basic for Application). Tudo foi criado somente com fórmulas dentro do próprio MS Excel!

Validação de Dados Dinâmica Simples

=DESLOC($A$1;0;0;CONT.VALORES(A:A);1)

ou em inglês

=OFFSET($A$1;0;0;COUNTA(A:A);1)

OBS: Caso queira ver a validação de dados tripla dinâmica com a utilização do DESLOC, só clicar aqui!

Agora vamos a outras aplicações, com outras fórmulas para deixar nossos processos dinâmicos.

Como resultar a ultima linha de um intervalo?

=PROC(2;1/(A:A<>"");LIN(A:A))

ou em inglês

=LOOKUP(2;1/(A:A<>"");ROW(A:A))

Pois sempre que o usuário for inserir alguma linha, seja aonde for, a fórmula irá se adaptar, sem que seja necessário a manutenção na fórmula.

Agora podemos potencializar essa aplicação mostrando realmente a última linha, segue

=DESLOC($A$1;0;0;PROC(2;1/(A:A<>"");LIN(A:A));1)

ou em inglês

=OFFSET($A$1;0;0;LOOKUP(2;1/(A:A<>"");ROW(A:A));1)

Construção de um Intervalo Dinâmico

O que seria um intervalo dinâmico? Primeiro vamos entender como funciona a estrutura de Linhas e Colunas dentro do Excel

Se você pegar um intervalo qualquer =A1:A5 e apertar F9, você irá ver que o Excel irá resultar {0;0;0;0;0}, então vemos que o ";" (ponto e virgula) é o que separa as linhas dentro do Excel.

Se pegar outro intervalo de colunas qualquer =A1:E1 e apertar F9, você irá ver que o Excel irá resultar {0\0\0\0\0}, então podemos deduzir que para separar as colunas o Excel utiliza da "\" (barra invertida).

Sempre que vejo funções com intervalos como A1:A10000 ou até mesmo A:A imagino que o usuário quer "se prevenir" e deixar intervalos grandes para que se for necessário adicionar mais registros. O Excel já estará contemplando os intervalos nas fórmulas e funções e com isso realizarpa os devidos cálculos. O grande problema disso é a velocidade, pois o Excel "armazena" todos esses espaços vazios para fazer as contas e muitas estratégias de resolução ficam inviáveis com esse viés.

Para resolver essse problema e potencializar muito seus resultados recomendo utilizar a próxima fórmula "suprema" chamada ÍNDICE

Vamos ao exemplo abaixo:

O que sempre é feito é selecionar dentro dos intervalos a área A:A, onde resulta {"Baldini";"Fabio";"Trovato";"Alessandro";0;0;0;0;0;0, ... ,0} , mas iremos demonstrar outra forma onde o Excel irá reduzir somente para os intervalos preenchidos, vamos lá

=ÍNDICE(A:A;1;1):ÍNDICE(A:A;CONT.VALORES(A:A);1)

Ou em Inglês

=INDEX(A:A;1;1):INDEX(A:A;COUNTA(A:A);1)

Realizando a fórmula no intervalo acima o Excel irá só resultar ={"Baldini";"Fabio";"Trovato";"Alessandro"}

Onde irá processar somente os intervalos necessários, e caso aumente algum nome na sua listagem será acrescentada em nossa "matriz".

Pronto, agora podemos ir para o último exemplo deste artigo,

Como resultar os valores não duplicados, de maneira ordenada crescente em um intervalo?

=SEERRO(PROCV(AGREGAR(15;6;CONT.SES(ÍNDICE($A$1:$A$19;1;1):ÍNDICE(
$A$1:$A$19;CONT.VALORES($A$1:$A$19);1);"<="&ÍNDICE($A$1:$A$19;1;1)
:ÍNDICE($A$1:$A$19;CONT.VALORES($A$1:$A$19);1));AGREGAR(15;6;SE(
FREQUÊNCIA(CORRESP(ÍNDICE(MENOR(ÍNDICE(CONT.SES(ÍNDICE($A$1:$A$19
;1;1):ÍNDICE($A$1:$A$19;CONT.VALORES($A$1:$A$19);1);"<="&ÍNDICE(
$A$1:$A$19;1;1):ÍNDICE($A$1:$A$19;CONT.VALORES($A$1:$A$19);1));0)
;ÍNDICE(LIN(INDIRETO("1:"&CONT.VALORES($A$1:$A$19)));));0);ÍNDICE(
MENOR(ÍNDICE(CONT.SES(ÍNDICE($A$1:$A$19;1;1):ÍNDICE($A$1:$A$19;
CONT.VALORES($A$1:$A$19);1);"<="&ÍNDICE($A$1:$A$19;1;1):ÍNDICE(
$A$1:$A$19;CONT.VALORES($A$1:$A$19);1));0);ÍNDICE(LIN(INDIRETO(
"1:"&CONT.VALORES($A$1:$A$19)));));0);0);LIN(INDIRETO("1:"&
CONT.VALORES(ÍNDICE(CONT.SES(ÍNDICE($A$1:$A$19;1;1):ÍNDICE(
$A$1:$A$19;CONT.VALORES($A$1:$A$19);1);"<="&ÍNDICE($A$1:$A$19;1;1)
:ÍNDICE($A$1:$A$19;CONT.VALORES($A$1:$A$19);1));0))))); ÍNDICE(LIN(
INDIRETO("1:"&CONT.VALORES(ÍNDICE(CONT.SES(ÍNDICE($A$1:$A$19;1;1)
:ÍNDICE($A$1:$A$19;CONT.VALORES($A$1:$A$19);1);"<="&ÍNDICE(
$A$1:$A$19;1;1):ÍNDICE($A$1:$A$19;CONT.VALORES($A$1:$A$19);1));0)
)));0);"");LINS($A$1:A1)));ESCOLHER({2\1};ÍNDICE($A$1:$A$19;1;1)
:ÍNDICE($A$1:$A$19;CONT.VALORES($A$1:$A$19);1);ÍNDICE(CONT.SES(
ÍNDICE($A$1:$A$19;1;1):ÍNDICE($A$1:$A$19;CONT.VALORES($A$1:$A$19)
;1);"<="&$A$1:$A$19);0));2;0);"")

ou em inglês

=IFERROR(VLOOKUP(AGGREGATE(15;6;COUNTIFS(INDEX($A$1:$A$19;1;1):
INDEX($A$1:$A$19;COUNTA($A$1:$A$19);1);"<="&INDEX($A$1:$A$19;1;1)
:INDEX($A$1:$A$19;COUNTA($A$1:$A$19);1));AGGREGATE(15;6;IF(
FREQUENCY(MATCH(INDEX(SMALL(INDEX(COUNTIFS(INDEX($A$1:$A$19;1;1)
:INDEX($A$1:$A$19;COUNTA($A$1:$A$19);1);"<="&INDEX(
$A$1:$A$19;1;1):INDEX($A$1:$A$19;COUNTA($A$1:$A$19);1));0);
INDEX(ROW(INDIRECT("1:"&COUNTA($A$1:$A$19)));));0);INDEX(SMALL(
INDEX(COUNTIFS(INDEX($A$1:$A$19;1;1):INDEX($A$1:$A$19;COUNTA(
$A$1:$A$19);1);"<="&INDEX($A$1:$A$19;1;1):INDEX($A$1:$A$19;COUNTA(
$A$1:$A$19);1));0);INDEX(ROW(INDIRECT("1:"&COUNTA($A$1:$A$19)));))
;0);0);ROW(INDIRECT("1:"&COUNTA(INDEX(COUNTIFS(INDEX($A$1:$A$19
;1;1):INDEX($A$1:$A$19;COUNTA($A$1:$A$19);1);"<="&INDEX(
$A$1:$A$19;1;1):INDEX($A$1:$A$19;COUNTA($A$1:$A$19);1));0)))))
; INDEX(ROW(INDIRECT("1:"&COUNTA(INDEX(COUNTIFS(INDEX(
$A$1:$A$19;1;1):INDEX($A$1:$A$19;COUNTA($A$1:$A$19);1);"<="&INDEX(
$A$1:$A$19;1;1):INDEX($A$1:$A$19;COUNTA($A$1:$A$19);1));0))));0)
;"");ROWS($A$1:A1)));CHOOSE({2\1};INDEX($A$1:$A$19;1;1):INDEX(
$A$1:$A$19;COUNTA($A$1:$A$19);1);INDEX(COUNTIFS(INDEX($A$1:$A$19
;1;1):INDEX($A$1:$A$19;COUNTA($A$1:$A$19);1);"<="&$A$1:$A$19)
;0));2;0);"")

Sobre gráficos com intervalos dinâmicos irei descrever em outro artigo

Lembre sempre quando for desenvolver alguma planilha do MS Excel, seja para ajudar alguém, seja para melhorar ela, tente sempre fazer ela dinâmica, para que não necessite eternamente de manutenção caso necessite aumentar um registro seja em linha ou em coluna.

Se você gostou, clique em curtir ou compartilhe esse artigo.

E uma vez mais vamos a um desafio! Caso este artigo tenha 100 compartilhamentos vou disponibilizar os arquivos para download!

Abraços e espero que você tenha gostado deste artigo!

Fabio BALDINI

Frase do dia: Srinivasa Ramanujan - "Uma equação não significa nada para mim a não ser que expresse um pensamento de Deus."

Agradecimentos a parte ao meu grande amigo e Mestre Alessandro Trovato, pelas revisões, dicas, que eu agradeço muito.