Imagine que temos alguns números de vendas para uma empresa:
E que desejamos encontrar os números totais do ano até o momento. Podemos adicionar um menu suspenso como:
Para que possamos especificar o mês atual. Portanto, agora queremos calcular o acumulado do ano para março. O formato mais simples seria ter fórmulas que se estendessem por todo o intervalo:
E então apenas mudaríamos as fórmulas a cada mês.
No entanto, o Excel permite outra abordagem. Poderíamos configurar um intervalo dinâmico cujo tamanho varia de acordo com o mês em que estamos. À medida que alteramos o mês na lista suspensa, o tamanho do intervalo muda.
Portanto, para o mês de março, o intervalo é de 3 colunas, e para o mês de junho, seria de 6 meses.
O tamanho do intervalo é determinado pelo mês. Uma maneira de formular isso é usar a função Mês:
= Mês (c8)
Onde c8 é o endereço da célula do nosso menu suspenso. No entanto, o método preferido é usar a função MATCH para determinar a posição dos meses atuais em todos os meses em nosso relatório:
CORRESPONDÊNCIA (c8, $ c $ 3: $ j $ 3,0)
Onde:
• c8 é o endereço da célula do mês atual
• C3: J3 é o endereço de todos os nossos meses
• 0 é para garantir uma correspondência exata
Agora podemos especificar o tamanho de nossa faixa dinâmica pela função OFFSET, que tem 5 argumentos:
= OFFSET (referência, linhas, colunas, altura, largura)
Onde:
• A referência é o canto superior esquerdo do nosso intervalo dinâmico - célula C5 - a primeira célula que queremos somar
• Linhas - o número de linhas abaixo de nossa célula base - este é 0
• Cols - o número de cols na nossa base call - é 0
• A largura de nossa faixa dinâmica - que é 3 neste caso. No entanto, como desejamos que o intervalo varie por mês, colocaremos nossas fórmulas MATCH aqui
• Esta é a altura da nossa faixa dinâmica, que é 1
Portanto, nossas fórmulas OFFSET são:
= OFFSET (c5,0,0, MATCH (c8, $ c $ 3: $ j $ 3,0), 1)
Finalmente, precisamos dizer ao Excel para SOMAR isso para fornecer as fórmulas completas como:
= SUM (OFFSET (c5,0,0, MATCH (c8, $ c $ 3: $ j $ 3,0), 1))
Nós temos:
Agora, se alterarmos o mês no menu suspenso, o valor correto do ano até a data passa por:
Como esta é uma atualização automática, esta abordagem tem as seguintes vantagens:
• Não há necessidade de alterar as fórmulas a cada mês
• Como há menos mudanças nas fórmulas, menos espaço para erros
• A planilha pode ser usada por alguém com conhecimento limitado do Excel - eles podem apenas alterar a lista suspensa e não se incomodar com fórmulas
