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).

Astuce. Si vous souhaitez convertir rapidement une plage en une seule colonne ou ligne (ou vice versa) plusieurs fois, ou si les plages sont grandes et que les formules deviennent difficiles à gérer, vous pouvez accomplir cela efficacement à l'aide de l'utilitaire Transformer la plage de Kutools for Excel. Cet outil offre une interface simple et flexible pour transformer les plages, minimisant ainsi le risque d'erreurs ou de correspondances incorrectes des formules. La fonctionnalité est entièrement opérationnelle pendant 60 jours. Veuillez télécharger et essayer gratuitement.

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.

Améliorez vos capacités Excel avec des outils alimentés par l'IA. Téléchargez maintenant et découvrez une efficacité sans précédent !