A função OFFSET do Excel retardará o cálculo da pasta de trabalho. Existe uma alternativa melhor: uma sintaxe incomum de INDEX.
Esta é uma dica de nicho. Existe uma função surpreendentemente flexível chamada OFFSET. É flexível porque pode apontar para um intervalo de tamanhos diferentes que é calculado instantaneamente. Na imagem abaixo, se alguém alterar o menu suspenso # Qtrs em H1 de 3 para 4, o quarto argumento de OFFSET garantirá que o intervalo se expanda para incluir quatro colunas.
Os gurus das planilhas odeiam OFFSET porque é uma função volátil. Se você for para uma célula completamente não relacionada e inserir um número, todas as funções OFFSET serão calculadas. Mesmo que essa célula não tenha nada a ver com H1 ou B2. Na maioria das vezes, o Excel é muito cuidadoso para perder tempo apenas calculando as células que precisam ser calculadas. Mas, uma vez que você introduz o OFFSET, todas as células OFFSET, mais tudo o downline do OFFSET, começa a calcular após cada mudança na planilha.
Eu estava julgando as finais do ModelOff de 2013 na cidade de Nova York quando alguns de meus amigos da Austrália apontaram uma solução alternativa bizarra. Na fórmula abaixo, há dois pontos antes da função INDEX. Normalmente, a função INDEX mostrada abaixo retornaria o 1403 da célula D2. Mas quando você coloca dois pontos em cada lado da função INDEX, ele começa a retornar o endereço da célula D2 em vez do conteúdo de D2. É incrível que funcione.
Por que isso importa? INDEX não é volátil. Você obtém todas as vantagens da flexibilidade de OFFSET sem os recálculos demorados repetidamente.
Aprendi essa dica pela primeira vez com Dan Mayoh da Fintega. Obrigado ao Access Analytic por sugerir este recurso.
Assistir vídeo
Transcrição de vídeo
Aprenda Excel com o podcast, episódio 2048 -: INDEX substituirá um OFFSET volátil!
Ei, estou fazendo um podcast de todas as minhas dicas deste livro, clique no “i” no canto superior direito para acessar a lista de reprodução!
Tudo bem, OFFSET é uma função incrível! OFFSET nos permite especificar uma célula do canto superior esquerdo, e então usar variáveis para definir quantas linhas abaixo, quantas linhas além, e então definir uma forma, certo. Então, aqui, se eu quiser somar, ou fazer uma média de, digamos 3/4, esta fórmula aqui vai dar uma olhada na fórmula. O OFFSET diz que estamos começando de B2, começando de Q1, vamos descer 0, passar de 0, a forma terá 1 linha de altura e será H1, em outras palavras, 3 células de largura, certo. Portanto, neste caso, tudo o que estamos realmente fazendo é mudar quantas células estamos adicionando, estamos sempre começando em B2, ou B3 ou B4 conforme copio, e então ele decide quantas células de largura. Certo, OFFSET é uma coisa legal, ele faz todos os tipos de funções incríveis, mas aqui está o incômodo, é volátil!O que significa que mesmo que algo não esteja na cadeia de cálculo, o Excel vai demorar para recalcular, o que vai atrasar as coisas, tudo bem.
Essa versão incrível de INDEX, certo, normalmente se eu pedir o INDEX dessas 4 células, 3, ela vai retornar o número 1403. Porém, quando eu coloco dois pontos antes ou depois do INDEX, algo muito diferente acontece, vamos dar uma olhada nesta fórmula aqui. Então, índice das 4 células, qual eu quero, eu quero a terceira, mas você vê que há um: bem aqui. Então, sempre iremos de B2: E2, e eu mostrarei a vocês se formos para Fórmulas, Avaliar Fórmula, certo, então bem aqui ele vai calcular o número 3. E aqui, o ÍNDICE com o seguinte: para ele, em vez de nos dizer 1403, que é como INDEX normalmente calcularia, ele retornará $ D2 e, em seguida, AVERAGE fará a média desses 3. Absolutamente incrível, a maneira como isso funciona e o benefício adicional, não é volátil, certo,e isso pode até ser usado para substituir um OFFSET incrivelmente complexo.
Portanto, aqui com este OFFSET, nos baseamos nesses valores. Se eu escolher Q2 e Central, tudo bem, essas fórmulas estão usando MATCH e COUNTIF para descobrir quantas linhas abaixo, quantas colunas, qual altura, qual largura. E então o OFFSET aqui está usando todos esses valores para descobrir a mediana. Faremos apenas um teste para ter certeza de que está funcionando, então = MEDIAN daquela faixa azul ali, melhor ser 71, certo, e escolheremos outra coisa aqui, Q3 e Oeste, então. Pressione F2, vou apenas arrastar e redimensionar para reescrever a fórmula, pressione Enter e está funcionando com o OFFSET.
Bem, aqui, usando um INDEX, eu realmente tenho dois pontos no meio com um INDEX no lado esquerdo e um INDEX no lado direito, observe isso ser calculado na fórmula de avaliação. Então ele começa, vai avaliar, avaliar e bem aqui que INDEX está prestes a transformá-lo em um endereço de célula. Então H16 é o primeiro, West, Q3, e acima do lado direito irá avaliar, acoplar mais, e então à direita dela ele muda para I20. Assim, o cool, cool não volátil para substituir um OFFSET usando a função INDEX. Tudo bem, esta dica e muitas outras neste livro, clique no “i” no canto superior direito para comprar o livro.
Tudo bem, recapitulando: OFFSET, função incrível e flexível, depois de dominar, você pode fazer todos os tipos de coisas. Aponte para uma célula variável superior esquerda, aponte para um intervalo que tem uma forma variável, mas é volátil, e eles calculam a cada cálculo. O Excel geralmente faz um cálculo inteligente ou recalcula as células que precisam ser calculadas, mas com OFFSET sempre será calculado. Em vez de OFFSET você pode usar INDEX: ou: INDEX ou mesmo INDEX: INDEX, sempre que INDEX tiver dois pontos ao lado, ele retornará um endereço de célula em vez do valor dessa célula. E o benefício é que o INDEX não é volátil!
OK, quero agradecer a sua visita, nos vemos na próxima vez para outro netcast do!
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2048.xlsm
