Como transpor referências ao preencher automaticamente para baixo/direita no Excel?

Ao trabalhar com fórmulas e preenchimento automático no Excel, o comportamento padrão é que as referências de coluna sejam incrementadas à medida que você arrasta a alça de preenchimento horizontalmente para a direita, e as referências de linha sejam incrementadas quando você arrasta verticalmente para baixo. No entanto, às vezes pode ser necessário transpor referências — por exemplo, referenciar um intervalo vertical, mas preencher horizontalmente, ou vice-versa. Essa operação é especialmente útil ao reorganizar layouts de dados ou criar relatórios dinâmicos que exigem cruzamento de dados de forma transposta. Este artigo explorará vários métodos práticos para realizar esse preenchimento automático transposto, incluindo fórmulas, macros VBA e ferramentas convenientes como o Kutools. Veja a ilustração abaixo para um cenário típico de preenchimento automático transposto:

Transpor referências ao preencher para baixo ou para a direita

Fórmula do Excel – Use ÍNDICE ou DESLOCAMENTO para construir manualmente referências transpostas

Transpor referências ao preencher para baixo ou para a direita

Para transpor uma referência enquanto preenche linhas ou colunas, a função TRANSPOR no Excel pode ser utilizada. Esse método é adequado quando você deseja converter dados organizados em uma coluna em uma linha (ou vice-versa), com a fórmula ajustando automaticamente a direção da referência conforme você preenche.

Selecione o intervalo onde deseja aplicar a referência transposta. Insira a seguinte fórmula na barra de fórmulas: =TRANSPOR(Planilha2!$B$1:B12) e depois pressione Ctrl + Shift + Enter simultaneamente (para versões anteriores ao Microsoft 365 / Excel 2021). Em versões mais recentes que suportam matrizes dinâmicas, basta pressionar Enter é suficiente. Veja a captura de tela abaixo:

Nesta fórmula, B1:B12 representa o intervalo que você deseja transpor de um intervalo vertical para uma disposição horizontal. Você pode ajustar o nome da planilha e o intervalo específico para se adaptar à sua fonte de dados real.
Dica: Para evitar erros na fórmula, certifique-se de que o intervalo de destino para os dados transpostos esteja dimensionado adequadamente (por exemplo, se sua fonte for uma coluna com 12 linhas, seu intervalo de destino deve ser uma linha com 12 colunas).

Dica. Se você quiser converter rapidamente um intervalo em uma única coluna ou linha (ou vice-versa) várias vezes, ou se os intervalos forem grandes e as fórmulas se tornarem complicadas, pode realizar isso de forma eficiente usando a ferramenta Transformar Intervalo do Kutools para Excel. Essa ferramenta oferece uma interface simples e flexibilidade para transformar intervalos, minimizando o risco de erros ou incompatibilidades nas fórmulas. O recurso está totalmente funcional por 60 dias. Baixe e experimente gratuitamente.

Fórmula do Excel – Use ÍNDICE ou DESLOCAMENTO para construir manualmente referências transpostas

Às vezes, usar fórmulas de matriz como TRANSPOR não é preferível, ou você precisa de mais controle sobre como as referências são transpostas, especialmente para dados não contíguos ou ao trabalhar em versões antigas do Excel. Com as funções ÍNDICE e DESLOCAMENTO, você pode referenciar dados transpostos diretamente usando operações de preenchimento padrão, sem precisar de Ctrl + Shift + Enter.

Cenários aplicáveis: Preferível quando você deseja uma referência transposta individual por célula, por exemplo, referenciando a célula A1 verticalmente para B1 horizontalmente (ou vice-versa). Útil para evitar entradas de matriz ou para referências dinâmicas em planilhas complexas.

Vantagens: Não é necessário entrada de matriz, as fórmulas são simples de arrastar e preencher.
Possíveis desvantagens: Pode ser um pouco mais complicado configurar a fórmula inicial; preste muita atenção em como você ajusta os índices de linha e coluna.

Como usar o ÍNDICE para referências transpostas:

1. Suponha que você tenha dados em A1:A5 (vertical) e deseja preenchê-los transpostos em B1:F1 (horizontal). Selecione a célula B1 e insira a seguinte fórmula:

=INDEX($A$1:$A$5,COLUMN(A1))

2. Pressione Enter, depois arraste a fórmula para a direita até a célula F1. A fórmula usa COLUNA(A1) para incrementar à medida que você move para a direita, referenciando assim A1, A2, ..., A5 em B1:F1.

Como usar o DESLOCAMENTO para referências transpostas:

1. Suponha que seus dados estejam na linha B1:F1 (horizontal), e você deseja preenchê-los verticalmente na coluna G1:G5. Na célula G1, insira:

=OFFSET($B$1,0,ROW(A1)-1)

2. Pressione Enter, depois arraste a fórmula para baixo até G5. Ao preencher para baixo, LINHA(A1)-1 será incrementado a partir de 0, alterando o índice de coluna da função DESLOCAMENTO para buscar B1, C1, D1, etc.

Ajuste o intervalo da fórmula ou a posição inicial conforme necessário para corresponder ao layout dos seus dados. Sempre verifique a referência correta, especialmente ao copiar ou mover fórmulas, para evitar incompatibilidades de referência.

Solução de problemas: Se você receber um erro #REF!, verifique novamente os intervalos de referência e certifique-se de que a fórmula não está tentando referenciar fora do seu conjunto de dados de destino. Além disso, para grandes conjuntos de dados, essas fórmulas podem aumentar a carga de cálculo da pasta de trabalho.

Em resumo, se você escolher usar fórmulas, VBA ou um complemento do Excel depende da estrutura dos seus dados, da frequência com que você precisa realizar essa operação e do seu nível de conforto com recursos avançados do Excel. Se uma abordagem não atender às suas necessidades, tente outro método conforme descrito acima. Se você encontrar problemas persistentes de fórmulas ou referências, verifique novamente suas fórmulas quanto a referências de células bloqueadas (uso de símbolos $) e ajuste conforme necessário para a direção correta ao transpor.

Desbloqueie a Magia do Excel com o Kutools AI

• Execução Inteligente: Realize operações de células, analise dados e crie gráficos — tudo impulsionado por comandos simples.
• Fórmulas Personalizadas: Gere fórmulas sob medida para otimizar seus fluxos de trabalho.
• Codificação VBA: Escreva e implemente código VBA sem esforço.
• Interpretação de Fórmulas: Compreenda fórmulas complexas com facilidade.
• Tradução de Texto: Supere barreiras linguísticas dentro de suas planilhas.

Potencialize suas capacidades no Excel com ferramentas alimentadas por IA. Baixe Agora e experimente uma eficiência como nunca antes!