Baixe a apostila de exemplo
Já discutimos em outros artigos sobre como existem funções como OFFSET e INDIRECT que são voláteis. Se você começar a usar muitos deles em uma planilha ou tiver muitas células dependentes da função volátil, poderá fazer com que seu computador gaste um tempo perceptível fazendo recálculos toda vez que você tentar alterar uma célula. Em vez de ficar frustrado com o fato de seu computador não ser rápido o suficiente, este artigo explorará maneiras alternativas de resolver as situações comuns que as pessoas usam OFFSET e INDIRECT.
Substituindo OFFSET para criar uma lista dinâmica
Depois de aprender sobre a função OFFSET, é um equívoco comum que é a única maneira de retornar um resultado com tamanho dinâmico usando os últimos dois argumentos. Vejamos uma lista na coluna A onde nosso usuário pode decidir mais tarde adicionar itens adicionais.
Para fazer uma lista suspensa na célula C2, você pode definir um intervalo nomeado com uma fórmula volátil como
= OFFSET ($ A $ 2, 0, 0, COUNTA ($ A: $ A) -1, 1)
Com a configuração atual, isso certamente retornaria uma referência ao intervalo A2: A5. No entanto, há outra maneira de usar o INDEX não volátil. Para fazer isso, pense em escrever uma referência ao intervalo de A2 a A5. Ao escrever “A2: A5”, não pense nisso como um único dado, mas sim como um “Ponto inicial” e “Ponto final” separados por dois pontos (por exemplo, Ponto inicial: Ponto final). Em uma fórmula, tanto o Ponto Inicial quanto o Ponto Final podem ser resultados de outras funções.
Esta é a fórmula que usaremos para criar a faixa dinâmica usando a função INDEX:
= $ A $ 2: ÍNDICE ($ A: $ A, CONTAGEM ($ A: $ A))
Observe que afirmamos que o Ponto de Partida para este intervalo sempre será A2. No outro lado dos dois pontos, estamos usando INDEX para determinar onde o EndingPoint deve estar. O COUNTA determinará que existem 5 células com dados na coluna A e, portanto, nosso INDEX criará uma referência para A5. A fórmula, portanto, é avaliada da seguinte forma:
= $ A $ 2: ÍNDICE ($ A: $ A, CONTAGEM ($ A: $ A)) = $ A $ 2: ÍNDICE ($ A: $ A, 5) = $ A $ 2: $ A5
Usando esta técnica, você pode construir dinamicamente uma referência a qualquer lista, ou mesmo uma tabela bidimensional usando a função INDEX. Em uma planilha com uma abundância de funções OFFSET, substituir os OFFSETs por INDEX permitirá que seu computador comece a funcionar muito mais rápido.
Substituindo INDIRETO por nomes de planilhas
A função INDIRETO geralmente é chamada quando as pastas de trabalho foram projetadas com dados espalhados por várias planilhas. Se você não pode obter todos os dados em uma única planilha, mas não deseja usar uma função volátil, pode ser capaz de usar CHOOSE.
Considere o seguinte layout, onde temos dados de vendas em 3 planilhas diferentes. Em nossa folha de resumo, selecionamos de qual trimestre gostaríamos de visualizar os dados.
Nossa fórmula em B3 é:
= ESCOLHER (CORRESPONDÊNCIA (B2, D2: D4, 0), Outono! A2, Inverno! A2, Primavera! A2)
Nesta fórmula, a função MATCH vai determinar qual área queremos retornar. Em seguida, isso informa à função CHOOSE qual dos intervalos a seguir deve ser retornado como resultado.
Você também pode usar a função CHOOSE para retornar um intervalo maior. Neste exemplo, temos uma tabela de dados de vendas em cada uma de nossas três planilhas.
Em vez de escrever uma função INDIRETA para construir o nome da planilha, você pode deixar CHOOSE determinar em qual tabela fazer a pesquisa. No meu exemplo, já nomeei as três tabelas tbFall, tbWinter e tbSpring. A fórmula em B4 é:
= PROCV (B3, ESCOLHER (CORRESPONDÊNCIA (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0)
Nesta fórmula, o MATCH vai determinar que queremos os 2WL item de nossa lista. CHOOSE pegará aquele 2 e retornará a referência para tbWinter. Finalmente, nosso VLOOKUP poderá completar a busca na tabela dada, e descobrirá que o total de vendas de Banana no inverno foi de $ 6.000.
= VLOOKUP (B3, CHOOSE (MATCH (B2, D2: D4, 0), tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, CHOOSE (2, tbFall, tbWinter, tbSpring), 2, 0) = VLOOKUP (B3, tbWinter, 2, 0) = 6000
Essa técnica é limitada pelo fato de que você deve preencher a função CHOOSE com todas as áreas das quais deseja buscar um valor, mas oferece o benefício de evitar uma fórmula volátil. Dependendo de quantos cálculos você precisa fazer, essa habilidade pode ser muito valiosa.
