=DESLOC(A1;3;2) retorna a célula 3 linhas abaixo e 2 colunas à direita de A1, que é C4. Dê a ela também uma altura e uma largura e ela retorna um intervalo inteiro, que é o principal uso da DESLOC: totais e médias sobre um intervalo que se move ou cresce. A DESLOC se chama OFFSET em inglês, e a tabela mostra a fórmula assim: =OFFSET(A1,E2,F2). Você também pode digitar as fórmulas em português, com ponto e vírgula.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Rows | Cols | Result | |
| 2 | Apple | Fruit | $1.20 | 3 | 2 | $0.80 | |
| 3 | Pear | Fruit | $1.50 | ||||
| 4 | Carrot | Vegetable | $0.80 | ||||
| 5 | Bread | Bakery | $2.40 | ||||
| 6 | Milk | Dairy | $1.10 |
=DESLOC(A1;E2;F2)3 linhas abaixo e 2 à direita de A1 cai em C4, o preço de Carrot, $0.80. Coloque 0 em Cols para ver o nome Carrot, ou 5 em Rows para a linha de Milk. Linhas e colunas podem ser negativas para subir ou voltar, e passar da borda de cima ou da lateral da planilha dá #REF!.
Sintaxe da DESLOC
=OFFSET(reference, rows, cols, [height], [width])
reference: a célula inicial (ou intervalo).rows,cols: quanto se mover. 0 significa ficar.height,width: o tamanho do intervalo a retornar, contado a partir da célula de destino. Se ficarem de fora, valem o tamanho dereference.
Sozinha em uma célula, uma DESLOC que retorna várias células se espalha no Excel 365; versões antigas costumam mostrar #VALOR! (#VALUE! em inglês; a tabela mostra os nomes de erro em inglês). Dentro de SOMA, MÉDIA, CONT.NÚM ou MÁXIMO, ela funciona como um intervalo.
Somar as últimas N linhas
O trabalho clássico da DESLOC: um total que sempre cobre as linhas mais recentes, quantas linhas forem acrescentadas. A CONT.NÚM (COUNT em inglês) descobre quantos valores existem, a DESLOC desce até o primeiro dos últimos N, e a altura pega N linhas.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Last N | Total | ||
| 2 | Jan | 4,200 | 3 | 14,900 | ||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
=SOMA(DESLOC(B1;CONT.NÚM(B2:B13)-E2+1;0;E2;1))São 7 valores, então a DESLOC começa 7-3+1, 5 linhas abaixo de B1, em B6, e pega 3 linhas: de May a Jul, 14,900. Digite 4900 em B9 (agosto) e o total passa para Jun, Jul e Aug, porque a CONT.NÚM agora encontra 8. O intervalo B2:B13 deixa espaço para o resto do ano. A coluna não pode ter células vazias no meio, senão a CONT.NÚM conta a menos e a janela cai no lugar errado.
Uma média móvel
Arrastada por uma coluna, a DESLOC com um deslocamento de linha negativo dá a cada linha uma janela com as linhas de cima: aqui, a média do mês atual e dos dois anteriores.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | 3-month average |
| 2 | Jan | 4,200 | |
| 3 | Feb | 3,900 | |
| 4 | Mar | 4,800 | 4,300 |
| 5 | Apr | 5,100 | 4,600 |
| 6 | May | 4,600 | 4,833 |
| 7 | Jun | 5,300 | 5,000 |
| 8 | Jul | 5,000 | 4,967 |
=MÉDIA(DESLOC(B4;-2;0;3;1))C4 tira a média de B2:B4 (de Jan a Mar), 4,300. Cada linha abaixo desce a janela uma linha. Mude o 3 para 6 e o -2 para -5 para uma média de seis meses (nesse caso, comece a fórmula na linha 7). Este caso em particular nem precisa da DESLOC: =MÉDIA(B2:B4) arrastada para baixo a partir de C4 faz o mesmo, porque as referências relativas já se movem. A DESLOC vale a pena quando o tamanho da janela vem de uma célula.
Por que o ÍNDICE costuma ser a melhor escolha
A DESLOC é volátil: o Excel recalcula toda DESLOC depois de qualquer edição em qualquer lugar da pasta de trabalho, já que não tem como saber de antemão para quais células ela vai apontar. Uma planilha com milhares delas fica lenta. O ÍNDICE também retorna uma referência, e um intervalo escrito como início:ÍNDICE(...) cresce do mesmo jeito sem ser volátil:
=SUM(OFFSET(B2, 0, 0, E2, 1)) first E2 rows, volatile
=SUM(B2:INDEX(B2:B13, E2)) same rows, not volatile
No Excel em português: =SOMA(DESLOC(B2;0;0;E2;1)) e =SOMA(B2:ÍNDICE(B2:B13;E2)). As duas leem as primeiras E2 linhas da coluna. A DESLOC também é mais difícil de auditar: Rastrear Precedentes e os contornos coloridos que o Excel desenha enquanto você edita a fórmula mostram a célula inicial e os argumentos, não o intervalo que a DESLOC acaba retornando. Use a DESLOC para um modelo rápido ou o intervalo de um gráfico; prefira o ÍNDICE em pastas grandes. A página do ÍNDICE fala mais sobre retornar intervalos, e a INDIRETO é a outra função de referência volátil.
Prática: total dos primeiros N meses
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | First N | Total | ||
| 2 | Jan | 4,200 | 4 | |||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
Sua vez: Em F2, use a DESLOC dentro da SOMA para totalizar os primeiros N meses, em que N está em E2.
Perguntas frequentes
O que a DESLOC faz no Excel?
Ela retorna uma referência que fica a um certo número de linhas e colunas de uma célula inicial, com tamanho opcional. =DESLOC(A1;3;2) é a célula 3 linhas abaixo e 2 colunas à direita de A1, que é C4.
Como somo as últimas N linhas no Excel?
Comece pelo cabeçalho e desça até o primeiro dos últimos N valores: =SOMA(DESLOC(B1;CONT.NÚM(B2:B100)-N+1;0;N;1)). A CONT.NÚM descobre quantos valores existem, e a altura N pega essa quantidade de linhas. Só funciona quando a coluna não tem lacunas.
Por que a DESLOC é volátil?
O Excel recalcula toda DESLOC depois de qualquer mudança na pasta de trabalho, porque as células para onde ela aponta só são conhecidas depois que ela roda. Em pastas grandes, isso deixa tudo mais lento. Um intervalo montado com ÍNDICE, como B2:ÍNDICE(B2:B100;N), faz o mesmo trabalho sem ser volátil.
Quais são os argumentos da DESLOC?
DESLOC(ref; lins; cols; [altura]; [largura]): a célula inicial, quantas linhas para baixo (negativo é para cima), quantas colunas para a direita (negativo volta) e, opcionalmente, o tamanho do intervalo a retornar.
