Você pode não saber como usar a função OFFSET no Excel

Você sabia que a função OFFSET do Microsoft Excel pode ajudá-lo a fazer muitas outras coisas, não apenas referências de células? Abaixo estão maneiras exclusivas de usar OFFSET no Excel que você talvez não conheça.

A função OFFSET do Microsoft Excel é uma ferramenta flexível e poderosa. Devido à sua complexidade e número de funções semelhantes, infelizmente, esta função do Excel é subutilizada.

Esta função relativamente simples pode ajudar os usuários a economizar tempo e esforço. Ele simplifica a obtenção de pontos de dados precisos de um conjunto de dados mestre bem classificado. Também permite a criação simples de relatórios detalhados, visualizações interativas e painéis complexos.

Crie um conjunto de dados de tamanho personalizado

Um dos usos mais comuns da função OFFSET no Excel é coletar conjuntos de dados redimensionáveis. Com um grande conjunto de dados, pode-se querer considerar apenas uma pequena porção dos dados em um determinado momento.

Por exemplo, para fornecer um relatório automatizado dos totais de vendas dos funcionários, você precisa obter o total de uma determinada semana ou semanas.

Escrever uma função SUM diferente para cada total pode ser bastante fácil. No entanto, usar a função OFFSET será mais fácil de usar e mais rápido de reutilizar.

Primeiro, insira a data que deseja começar na célula B6. Em seguida, insira a quantidade de dias que deseja gerar o relatório em B7. A seguir, use as funções simples SUM e AVERAGE para obter mais dados para cada funcionário durante o período selecionado:

=SUM(OFFSET(B2, 0, B6 - 1, 1, B7))

=AVERAGE(OFFSET(B2, 0, B6 - 1, 1, B7)

Ajustar a data ou hora de início é tão simples quanto editar uma célula, mas você pode arrastar e redimensionar grupos de dados com muito mais facilidade.

Permite a criação de relatórios personalizados

Use o mesmo método acima para criar um relatório completo personalizando variáveis ​​com bastante facilidade. Você pode fazer com que o primeiro argumento na função OFFSET faça referência a outra planilha. Isso permite separar a entrada de dados e os relatórios.

A ação acima também torna possível arrastar dados selecionados de uma planilha para outra. Além disso, permite exibir apenas informações relevantes para o relatório atual. Configurar um relatório que mostre os totais diários usando os mesmos dados anteriores é bastante simples. Você pode fazer isso sem recuperar dados de datas não relacionadas.

A seguinte função de deslocamento colocada na célula B5 recuperará o número de dias especificado em B2. Inicia na semana selecionada em B1 e mostra apenas os dados do período relevante:

=OFFSET(Sheet1!B2, 0, (B1 - 1) * 7, 1, B2 * 7)

É possível restringir lançamentos em B1 e B2 e criar uma lista suspensa no Excel com semanas disponíveis através da validação de dados.

Paginação de planilha

A função OFFSET também pode possibilitar a paginação simples de planilhas, uma tarefa frequentemente associada ao uso da guia Excel Developer. Isto pode tornar o processamento de grandes grupos de dados mais simples e eficaz.

Ao trabalhar com planilhas contendo até 30.000 linhas de dados, pode ser difícil examinar todos os dados de uma só vez. Crie um menu suspenso que aceite um intervalo de números de 1 a 300, permitindo separar rapidamente os dados por página com 100 resultados por página.

A função OFFSET sempre tem seu ponto de ancoragem colocado na célula de dados superior. No entanto, o segundo argumento é o deslocamento vertical. Este valor é igual ao número da página selecionada menos 100. O deslocamento é horizontal, o terceiro argumento pode ser definido como 0.

O quarto argumento é definido como 100, o quinto argumento é o número de colunas no conjunto de dados. Este método torna a divisão e o gerenciamento de tabelas de dados extremamente fáceis.

Abaixo está um exemplo de dados paginados. Inclui 500 informações registradas de um sensor em uma planilha e um relatório paginado na outra planilha.

O relatório usa validação de dados na célula B1 para criar um menu suspenso com páginas dentro. A seguinte expressão na célula A3 cria o corpo do relatório:

=OFFSET(Sheet1!A2, (B1 - 1) * 100, 0, 100, 21)

Acima estão algumas maneiras de usar a função offset no Excel que poucas pessoas conhecem, mas que são muito úteis, principalmente quando você precisa processar grandes quantidades de dados.

Espero que o artigo seja útil para você.