Comment transposer les références lors du remplissage automatique vers le bas ou la droite dans Excel ?
Lorsque vous travaillez avec des formules et le remplissage automatique dans Excel, le comportement par défaut est que les références de colonnes s'incrémentent lorsque vous faites glisser la poignée de remplissage horizontalement vers la droite, et les références de lignes s'incrémentent lorsque vous faites glisser verticalement vers le bas. Cependant, il peut être nécessaire de transposer des références — par exemple, référencer une plage verticale mais remplir horizontalement, ou vice versa. Cette opération est particulièrement utile pour réorganiser les dispositions des données ou créer des rapports dynamiques nécessitant la référence croisée des données de manière transposée. Cet article explorera diverses méthodes pratiques pour réaliser ce remplissage automatique transposé, y compris l'utilisation de formules, de macros VBA et d'outils pratiques comme Kutools. Consultez l'illustration ci-dessous pour un scénario typique de remplissage transposé.
Transposer les références en remplissant vers le bas ou la droite
Formule Excel – Utiliser INDEX ou DECALER pour construire manuellement des références transposées
Transposer les références en remplissant vers le bas ou la droite
Pour transposer une référence tout en remplissant sur plusieurs lignes ou colonnes, la fonction TRANSPOSE dans Excel peut être utilisée. Cette méthode convient lorsque vous souhaitez convertir des données disposées dans une colonne en une ligne (ou vice versa), avec la formule ajustant automatiquement la direction de la référence pendant le remplissage automatique.
Sélectionnez la plage où vous souhaitez appliquer la référence transposée. Entrez ensuite la formule suivante dans la barre de formule : =TRANSPOSE(Feuil2!$B$1:B12) puis appuyez sur Ctrl + Maj + Entrée simultanément (pour les versions antérieures à Microsoft 365 / Excel 2021). Dans les versions plus récentes qui prennent en charge les tableaux dynamiques, appuyez simplement sur Entrée ce qui est suffisant. Voir 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. Vous pouvez ajuster le nom de la feuille et la plage spécifique pour qu'elle corresponde à votre source de données réelle.
Astuce : Pour éviter les erreurs de formules, assurez-vous que la plage de destination pour les données transposées est dimensionnée correctement (par exemple, si votre source est une colonne de 12 lignes, votre plage de destination doit être une ligne de 12 colonnes).
Formule Excel – Utiliser INDEX ou DECALER pour construire manuellement des références transposées
Parfois, utiliser des formules matricielles comme TRANSPOSE n'est pas préférable, ou vous avez besoin d'un contrôle accru sur la façon dont les références sont transposées, surtout pour des données non contiguës ou lors de l'utilisation de versions plus anciennes d'Excel. Avec les fonctions INDEX et DECALER, vous pouvez référencer directement les données transposées en utilisant des opérations de remplissage standard, sans avoir besoin de Ctrl + Maj + Entrée.
Scénarios applicables : Préférable lorsque vous voulez une référence transposée un-à-un par cellule, par exemple, référencer la cellule A1 verticalement à B1 horizontalement (ou vice versa). Utile pour éviter les entrées matricielles ou pour des références dynamiques dans des feuilles de calcul complexes.
Avantages : Pas d'entrée matricielle requise, les formules sont simples à faire glisser et remplir.
Inconvénients potentiels : Peut être un peu plus complexe à configurer au départ ; soyez vigilant sur la manière dont vous ajustez les indices de ligne et de colonne.
Comment utiliser INDEX pour la référence transposée :
1. Supposons que vous ayez des données dans A1:A5 (verticalement), et vous souhaitez les remplir transposées dans B1:F2 (horizontalement). Sélectionnez la cellule B1 et entrez 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 incrémenter à mesure que vous avancez vers la droite, référençant ainsi A1, A2, ..., A5 à travers B1:F1.
Comment utiliser DECALER pour la référence transposée :
1. Supposons que vos données soient dans la ligne B1:F1 (horizontalement), et vous souhaitez les remplir verticalement dans la colonne G1:G5. Dans la cellule G1, entrez :
=OFFSET($B$1,0,ROW(A1)-1)2. Appuyez sur Entrée, puis faites glisser la formule vers le bas jusqu'à G5. Lorsque vous remplissez vers le bas, LIGNE(A1)-1 s'incrémente à partir de 0, décalant l'index de colonne de la fonction DECALER pour récupérer B1, C1, D1, etc.
Ajustez la plage de la formule ou la position de départ selon les besoins pour qu'elle corresponde à la disposition de vos données. Vérifiez toujours que les références sont correctes, surtout lorsque vous copiez ou déplacez des formules, afin d'éviter des incohérences de références.
Dépannage : Si vous obtenez une erreur #REF!, vérifiez à nouveau les plages de références et assurez-vous que la formule ne tente pas de référencer en dehors de votre ensemble de données cible. De plus, pour les grands ensembles de données, ces formules peuvent augmenter la charge de calcul du classeur.
En résumé, que vous choisissiez d'utiliser des formules, VBA ou un complément Excel dépend de la structure de vos données, de la fréquence à laquelle vous devez effectuer cette opération, et de votre aisance avec les fonctionnalités avancées d'Excel. Si une approche ne répond pas à vos besoins, essayez une autre méthode comme décrit ci-dessus. Si vous rencontrez des problèmes persistants de formule ou de référence, vérifiez encore vos formules pour les références de cellules verrouillées (utilisation du symbole $) et ajustez-les si nécessaire pour assurer une bonne direction lors de la transposition.
Découvrez la magie d'Excel avec Kutools AI
- Exécution intelligente : Effectuez des opérations sur les cellules, analysez les données et créez des graphiques, le tout piloté par des commandes simples.
- Formules personnalisées : Générez des formules adaptées pour rationaliser vos flux de travail.
- Codage VBA : Écrivez et implémentez du code VBA sans effort.
- Interprétation des formules : Comprenez facilement des formules complexes.
- Traduction de texte : Surmontez les barrières linguistiques dans vos feuilles de calcul.
Meilleurs outils de productivité pour Office
Améliorez vos compétences Excel avec Kutools pour Excel, et découvrez une efficacité incomparable. Kutools pour Excel propose plus de300 fonctionnalités avancées pour booster votre productivité et gagner du temps. Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte l’interface par onglets à Office, simplifiant considérablement votre travail.
- Activez la modification 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é de50 % et réduit des centaines de clics de souris chaque jour !
Tous les modules complémentaires Kutools. Une seule installation
La suite Kutools for Office regroupe les modules complémentaires pour Excel, Word, Outlook & PowerPoint ainsi qu’Office Tab Pro, idéal pour les équipes travaillant sur plusieurs applications Office.
- Suite tout-en-un — modules complémentaires Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un installateur, une licence — installation en quelques minutes (compatible MSI)
- Une synergie optimale — productivité accélérée sur l’ensemble des applications Office
- Essai complet30 jours — sans inscription, ni carte bancaire
- Meilleure valeur — économisez par rapport à l’achat d’add-ins individuels