Como transpor referências ao utilizar o preenchimento automático para baixo ou para a direita no Excel?
Ao trabalhar com fórmulas e o preenchimento automático no Excel, o comportamento predefinido é que as referências de coluna aumentem à medida que arrasta a alça de preenchimento horizontalmente para a direita e que as referências de linha aumentem ao arrastar verticalmente para baixo. Contudo, por vezes poderá precisar de transpor essas referências — por exemplo, ao referenciar um intervalo vertical mas preencher horizontalmente, ou vice‑versa. Esta abordagem revela-se especialmente útil ao reorganizar esquemas de dados ou ao criar relatórios dinâmicos que exijam referências cruzadas em formatos transpostos. Este artigo apresenta vários métodos práticos para alcançar esse preenchimento automático transposto, incluindo fórmulas, macros VBA e ferramentas convenientes como o Kutools. Veja abaixo uma ilustração de um cenário típico de preenchimento automático transposto:
Transpor referência ao preencher para baixo ou para a direita
Fórmula do Excel – Utilize ÍNDICE ou DESLOCAMENTO para construir manualmente referências transpostas
Transpor referência ao preencher para baixo ou para a direita
Para transpor uma referência ao preencher através de linhas ou colunas, utilize a função TRANSPOR do Excel. Este método é ideal quando pretende converter dados dispostos numa coluna numa linha (ou vice-versa), com a fórmula a ajustar automaticamente a direção da referência à medida que utiliza o preenchimento automático.
Selecione o intervalo onde pretende aplicar a referência transposta. Na Barra de Fórmulas, introduza a seguinte fórmula:=TRANSPOR(Folha2!$B$1:B12) e, em seguida, prima simultaneamente Ctrl + Shift + Enter (em versões anteriores ao Microsoft 365 / Excel 2021). Nas versões mais recentes, que já suportam matrizes dinâmicas, basta premir Enter. Veja a imagem abaixo:
Nesta fórmula, B1:B12 representa o intervalo que pretende transpor de um arranjo vertical para um horizontal. Pode ajustar o nome da folha e o intervalo específico de acordo com o seu intervalo de origem real.
Dica: Para evitar erros de fórmula, certifique-se de que o intervalo de destino para os dados transpostos tem as dimensões apropriadas (por exemplo, se a origem tiver uma coluna com 12 linhas, o intervalo de destino deverá ter uma linha com 12 colunas).
Fórmula do Excel – Utilize ÍNDICE ou DESLOCAMENTO para construir manualmente referências transpostas
Por vezes, não é desejável utilizar fórmulas de matriz como TRANSPOR, ou talvez precise de maior controlo sobre a forma como as referências são transpostas — especialmente com dados não contíguos ou ao trabalhar em versões mais antigas do Excel. Com as funções ÍNDICE e DESLOCAMENTO, pode referenciar diretamente dados transpostos utilizando operações normais de preenchimento, sem precisar de Ctrl + Shift + Enter.
Cenários aplicáveis: Ideal quando precisa de uma referência transposta um-para-um por célula — por exemplo, ao transpor a célula A1 na vertical para B1 na horizontal (ou vice-versa). É especialmente útil para evitar entradas de matriz ou para criar referências dinâmicas em folhas de cálculo complexas.
Vantagens: Não exige introdução de matriz; as fórmulas são simples de arrastar e preencher.
Desvantagens potenciais: Pode ser ligeiramente mais complexo configurar a fórmula inicial; preste especial atenção à forma como ajusta os índices de linha e coluna.
Como utilizar ÍNDICE para referências transpostas:
1. Imagine que tem dados em A1:A5 (vertical) e pretende preenchê-los transpostos em B1:F1 (horizontal). Selecione a célula B1 e introduza a seguinte fórmula:
=INDEX($A$1:$A$5,COLUMN(A1))2. Prima Enter, depois arraste a fórmula para a direita até à célula F1. A fórmula utiliza COL(A1) para incrementar à medida que se move para a direita, referenciando assim A1, A2, ..., A5 ao longo de B1:F1.
Como utilizar DESLOCAMENTO para referências transpostas:
1. Imagine que os seus dados estão na linha B1:F1 (horizontal) e pretende preenchê-los verticalmente na coluna G1:G5. Na célula G1, introduza:
=OFFSET($B$1,0,ROW(A1)-1)2. Prima Enter e, de seguida, arraste a fórmula para baixo até G5. À medida que preenche para baixo, LIN(A1)-1 incrementa a partir de 0, deslocando o índice de coluna da função DESLOCAMENTO para obter B1, C1, D1, etc.
Ajuste o intervalo da fórmula ou a posição inicial conforme necessário para se alinhar ao seu esquema de dados. Verifique sempre se as referências estão corretas — especialmente ao copiar ou mover fórmulas — e evite incompatibilidades nas referências.
Resolução de problemas: Se obtiver um erro #REF!, verifique novamente os intervalos de referência e certifique-se de que a fórmula não está a tentar referenciar fora do seu conjunto de dados. Além disso, em conjuntos de dados grandes, estas fórmulas podem aumentar significativamente a carga de cálculo do livro.
Em resumo, a escolha entre utilizar fórmulas, VBA ou um suplemento do Excel depende da estrutura dos seus dados, da frequência com que realiza esta operação e do seu nível de conforto com funcionalidades avançadas do Excel. Se um método não corresponder às suas necessidades, experimente outro dos métodos descritos acima. Caso encontre problemas persistentes nas fórmulas ou referências, verifique cuidadosamente se existem referências de células fixas (utilização do símbolo)$) e ajuste-as conforme necessário para garantir a direcionalidade correta ao transpor.
Desbloqueie a Magia do Excel com KUTOOLS AI
- Execução Inteligente: Realize operações em células, analise dados e crie gráficos — tudo impulsionado por comandos simples.
- fórmulas personalizadas: Crie fórmulas personalizadas para simplificar os seus fluxos de trabalho.
- Programação VBA: Escreva e implemente código VBA com facilidade.
- Interpretação de Fórmulas: Compreenda facilmente fórmulas complexas.
- Tradução de Texto: Elimine barreiras linguísticas nas suas folhas de cálculo.
As Melhores Ferramentas de Produtividade para o Office
Potencie as suas competências no Excel com Kutools para Excel e experimente uma eficiência como nunca antes.Kutools para Excel Oferece mais de 300 funcionalidades avançadas para impulsionar a produtividade e Economizar Tempo.Clique aqui para obter a funcionalidade de que mais precisa...
Office Tab Traz uma interface com separadores para o Office e torna o seu trabalho muito mais fácil
- Ative a edição e leitura com separadores no Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novos separadores da mesma janela, em vez de em janelas separadas.
- Aumente a sua produtividade em 50 % e elimine centenas de cliques do rato todos os dias!
Todos os suplementos Kutools — num único instalador.
Kutools for Office reúne suplementos para Excel, Word, Outlook e PowerPoint, além do Office Tab Pro, sendo a solução ideal para equipas que trabalham em várias aplicações do Office.
- Pacote tudo-em-um— suplementos para Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Um instalador, uma licença— configuração em minutos (pronto para MSI)
- Funciona melhor em conjunto— produtividade simplificada em todas as aplicações do Office
- Teste gratuito de 30 dias com todas as funcionalidades— sem registo, sem cartão de crédito
- Melhor relação qualidade-preço— poupe face à compra individual dos suplementos