Comment transposer une référence lorsqu’on la recopie vers le bas ou vers la droite dans Excel ?
Lorsque vous utilisez des formules et la recopie automatique dans Excel, le comportement par défaut consiste à incrémenter les références de colonnes en faisant glisser la poignée de recopie vers la droite, et les références de lignes en la faisant glisser vers le bas. Il arrive toutefois que vous ayez besoin de transposer ces références — par exemple, faire référence à une plage verticale tout en recopiant horizontalement, ou inversement. Cette approche s’avère particulièrement utile pour réorganiser la présentation de vos données ou créer des rapports dynamiques nécessitant des références croisées sous forme transposée. Cet article vous présente plusieurs méthodes efficaces pour réaliser cette recopie automatique transposée, notamment à l’aide de formules, de macros VBA et d’outils pratiques comme Kutools. Consultez l’illustration ci-dessous pour un exemple typique de recopie automatique transposée :
Transposer une référence lors de la recopie vers le bas ou vers la droite
Transposer une référence lors de la recopie vers le bas ou vers la droite
Pour transposer une référence lors de la recopie sur des lignes ou des colonnes, utilisez la fonction TRANSPOSE d’Excel. Cette méthode est idéale pour transformer des données organisées en colonne en ligne (ou inversement) : la formule ajuste automatiquement la direction de la référence au fur et à mesure de la recopie.
Sélectionnez la plage où vous souhaitez appliquer la référence transposée. Saisissez la formule suivante dans la barre de formule :=TRANSPOSE(Feuil2!$B$1:B12), puis appuyez simultanément sur Ctrl + Maj + Entrée (pour les versions antérieures à Microsoft 365 / Excel 2021). Dans les versions récentes prenant en charge les tableaux dynamiques, il suffit d’appuyer sur Entrée. Consultez la capture d’écran ci-dessous :
Dans cette formule, B1:B12 représente la plage que vous souhaitez transposer d’une disposition verticale à une disposition horizontale. Adaptez le nom de la feuille et la plage exacte en fonction de votre plage source réelle.
Conseil : Pour éviter les erreurs de formule, assurez-vous que la plage de destination pour les données transposées soit correctement dimensionnée (par exemple, si votre source contient une colonne de 12 lignes, votre plage de destination doit comporter une ligne de 12 colonnes).
Formule Excel – Utilisez INDEX ou DECALER (OFFSET) pour construire manuellement des références transposées
Parfois, l’utilisation de formules matricielles telles que TRANSPOSE n’est pas souhaitée, ou vous avez besoin d’un contrôle plus précis sur la manière dont les références sont transposées — notamment pour des données non contiguës ou lorsque vous travaillez avec d’anciennes versions d’Excel. Grâce aux fonctions INDEX et DECALER (OFFSET), vous pouvez faire référence directement à des données transposées à l’aide d’opérations de recopie standard, sans avoir à appuyer sur Ctrl + Maj + Entrée.
Scénarios applicables : Idéal lorsque vous souhaitez établir une correspondance un à un entre les cellules transposées, par exemple, faire référence à la cellule A1 en vertical depuis B1 en horizontal (ou inversement). Particulièrement utile pour éviter les formules matricielles ou pour créer des références dynamiques dans des classeurs complexes.
Avantages : Aucune saisie matricielle requise ; les formules sont simples à recopier.
Inconvénients potentiels : La configuration initiale de la formule peut être légèrement plus complexe ; veillez à ajuster correctement les indices de ligne et de colonne.
Comment utiliser INDEX pour les références transposées :
1. Supposons que vos données se trouvent en A1:A5 (verticalement) et que vous souhaitiez les recopier transposées en B1:F1 (horizontalement). Sélectionnez la cellule B1 et saisissez la formule suivante :
=INDEX($A$1:$A$5,COLUMN(A1))2. Appuyez sur Entrée, puis faites glisser la formule vers la droite jusqu’à la cellule F1. La formule utilise COLONNE(A1) pour s’incrémenter automatiquement au fur et à mesure que vous la déplacez vers la droite, faisant ainsi référence successivement à A1, A2, ..., A5 dans la plage B1:F1.
Comment utiliser DECALER (OFFSET) pour les références transposées :
1. Supposons que vos données se trouvent sur la ligne B1:F1 (horizontalement) et que vous souhaitiez les recopier verticalement dans la colonne G1:G5. Dans la cellule G1, saisissez :
=OFFSET($B$1,0,ROW(A1)-1)2. Appuyez sur Entrée, puis faites glisser la formule vers le bas jusqu’à G5. Lors de la recopie vers le bas, LIGNE(A1)-1 s’incrémente à partir de 0, déplaçant ainsi l’indice de colonne de la fonction DECALER (OFFSET) pour récupérer successivement B1, C1, D1, etc.
Adaptez la plage de la formule ou sa position de départ selon vos besoins pour qu’elles correspondent parfaitement à la structure de vos données. Vérifiez toujours l’exactitude des références, surtout lorsque vous copiez ou déplacez des formules, afin d’éviter toute incohérence.
Dépannage : Si vous obtenez une erreur #REF!, vérifiez attentivement les plages de référence et assurez-vous que la formule ne fait pas référence à des cellules situées en dehors de votre ensemble de données cible. Par ailleurs, avec de grands jeux de données, ces formules peuvent alourdir considérablement la charge de calcul du classeur.
En résumé, le choix entre l’utilisation de formules, de VBA ou d’un complément Excel dépend de la structure de vos données, de la fréquence à laquelle vous effectuez cette opération et de votre maîtrise des fonctionnalités avancées d’Excel. Si une méthode ne répond pas à vos besoins, testez une autre approche décrite ci-dessus. En cas de problèmes persistants liés aux formules ou aux références, vérifiez attentivement vos formules pour repérer d’éventuelles références de cellules verrouillées (utilisation du symbole)$) et ajustez-les si nécessaire afin d’obtenir la directionnalité correcte lors de la transposition.
Libérez la magie d’Excel avec Kutools IA
- Exécution intelligente : effectuez des opérations sur les cellules, analysez des données et créez des graphiques, le tout à partir de commandes simples.
- formules personnalisées : créez des formules sur mesure pour rationaliser vos flux de travail.
- Programmation VBA : Écrivez et implémentez du code VBA en toute simplicité.
- Interprétation de formules : Décryptez facilement les formules complexes.
- Traduction de texte : Franchissez les barrières linguistiques directement depuis vos feuilles de calcul.
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez l’édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de 50 % et vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels