Transformation de données sans effort avec la fonction TRANSPOSE dans Google Sheets

Lorsque l’on parle de transformation de données dans Google Sheets, la fonction TRANSPOSE est un outil puissant pour échanger les lignes et les colonnes. Cependant, ce n’est pas la seule option disponible. Vous pouvez également réaliser cette opération en utilisant :

• La commande Transpose : Similaire à la fonction TRANSPOSE, elle offre un contrôle renforcé grâce aux options de copier-coller.
• Les fonctions TOCOL et TOROW : Convertissent des lignes ou des colonnes spécifiques en nouvelles colonnes ou en nouvelles lignes, respectivement.

Chaque méthode a ses forces et ses faiblesses, ce qui les rend adaptées à différentes situations.

Dans ce tutoriel, nous allons nous concentrer sur la fonction TRANSPOSE et explorer en détail ses capacités, tout en abordant brièvement les autres méthodes pour une compréhension complète.

Fonction TRANSPOSE : Syntaxe, Arguments et Cas d’utilisation clés

Syntaxe :
TRANSPOSE(plage_ou_tableau)

Arguments :

• plage_ou_tableau : La plage ou le tableau de cellules contenant les données que vous souhaitez transposer. Il peut s’agir d’une seule cellule, d’une plage de cellules ou même d’une plage nommée.

Lorsque vous modifiez l’orientation des données, les changements suivants se produisent visuellement : La nième colonne devient la nième ligne et la nième ligne devient la nième colonne.

Par exemple, la troisième colonne se transforme en troisième ligne, et vice versa. L’application de la fonction TRANSPOSE deux fois maintient l’orientation d’origine.

Cas d’utilisation :

• Manipulation des données : La transposition des données peut aider à réorganiser les données pour une analyse plus facile, comme filtrer les lignes par les valeurs des colonnes ou vice versa. Cela vous permet de travailler avec vos données selon un axe différent.
• Présentation des données : Elle offre une flexibilité dans la présentation des données, ce qui facilite la création de graphiques ou de rapports de tableaux croisés dynamiques.
• Formatage des en-têtes de pivot QUERY :
  • Il s’agit d’un conseil plus avancé pour les utilisateurs de QUERY. Si vos données de pivot QUERY contiennent des dates dans la rangée supérieure et que vous souhaitez les formater comme des mois, des années-mois ou des années, dans la formule QUERY :
    • Déplacez la colonne de dates dans la clause GROUP BY au lieu de la clause PIVOT.
    • Formatez la colonne de dates pour l’affichage souhaité en utilisant la clause FORMAT.
    • Vous pouvez ensuite transposer les données résultantes en utilisant la fonction TRANSPOSE si nécessaire.
  • Vous pouvez trouver un tutoriel sur la façon de formater les lignes d’en-tête des requêtes QUERY ici : Comment formater la ligne d’en-tête des requêtes QUERY dans Google Sheets

Exemple de l’utilisation de la fonction TRANSPOSE dans Google Sheets et comparaison avec la commande Transpose

Données d’exemple :
Dans le tableau ci-dessous, les données sont initialement organisées en colonnes.

[Insérer l’image ici]

Modification de l’orientation des données :
Pour changer l’orientation des colonnes aux lignes, vous pouvez utiliser soit la fonction TRANSPOSE, soit la commande « Transpose » dans Google Sheets.

Tableau transposé :
[Insérer l’image ici]

Utilisation de la formule TRANSPOSE :
Si les données initiales sont dans la plage de cellules A1:G2, vous pouvez utiliser la formule :
=TRANSPOSE(A1:G2)

Utilisation de la commande Transpose :

• Copiez les données dans la plage A1:G2.
• Faites un clic droit sur n’importe quelle cellule en dehors de la plage A1:G2 pour ouvrir le menu contextuel.
• Sélectionnez « Collage spécial » > « Transposé ».

Vous pouvez également utiliser un raccourci clavier dans Google Sheets.

• Sur Windows : Ctrl + C pour copier la plage sélectionnée, et Alt + E + S + E pour appliquer le collage transposé.
• Sur Mac : Commande + C pour copier les données sélectionnées, et Contrôle + Option + E + S + E pour appliquer le collage transposé.

Choisir entre la fonction TRANSPOSE et la commande Transpose

Avant de choisir, il est important de prendre en compte les avantages et les inconvénients de chaque méthode :

Fonction TRANSPOSE :

• Mise à jour de la source : Les modifications apportées à la plage source sont reflétées dans la plage transposée.
• Formatage : Elle ne conserve pas le formatage des données source.
• Édition : Le résultat ne peut pas être directement modifié ; la formule sera cassée.

Commande Transpose :

• Mise à jour de la source : Les mises à jour apportées à la plage source ne seront pas reflétées dans les données transposées.
• Formatage des cellules : Elle conserve le formatage, y compris la couleur des cellules, le gras, l’italique, etc.
• Édition : Les données transposées peuvent être directement modifiées sans affecter la source.

Imbrication de fonctions TRANSPOSE

Comme mentionné précédemment, la transposition des données, la réalisation de manipulations, puis la transposition à nouveau est une pratique courante dans Google Sheets. Lorsque vous le faites, il est crucial d’éviter de transposer la colonne ou la ligne initiale contenant les libellés.

Supposons que nous souhaitons trier les données dans la plage A1:G2 par départements en utilisant la fonction SORT. Étant donné que nous pouvons trier les données par colonnes et non par lignes, et que les données de département se trouvent dans la deuxième ligne, nous devons transposer la plage B1:G2, effectuer le tri, puis la transposer à nouveau.

En plus de cela, vous pouvez utiliser la fonction HSTACK pour joindre les libellés de la table d’origine avec la nouvelle table.

Formule :
=HSTACK(A1:A2, TRANSPOSE(SORT(TRANSPOSE(B1:G2), 2, 1)))

[Insérer l’image ici]

Explication de la formule

La formule TRANSPOSE imbriquée ci-dessus peut être décomposée en quatre parties : Transposition interne, Tri, Transposition externe et Empilement.

Transposition interne (TRANSPOSE(B1:G2)):
La fonction TRANSPOSE la plus interne transpose les lignes et les colonnes des données d’origine dans la plage B1:G2.

Tri (SORT(…, 2, 1)):
La fonction SORT est ensuite appliquée aux données transposées pour trier le champ « Département ».

Transposition externe (TRANSPOSE(…)):
Une autre fonction TRANSPOSE est appliquée aux données triées. Cela transpose les données triées dans leur orientation d’origine.

Empilement (HSTACK(A1:A2, …)):
La fonction HSTACK empile horizontalement les données dans la plage de cellules A1:A2 (libellés) et les données triées transposées obtenues à partir des étapes précédentes.

TOCOL et TOROW : Des alternatives pour la transposition d’une seule ligne/colonne dans Google Sheets

Bien que TRANSPOSE soit un outil polyvalent pour échanger les lignes et les colonnes, TOCOL et TOROW offrent des approches alternatives pour traiter les données d’une seule ligne ou d’une seule colonne.

Dans la perspective de la transposition, vous pouvez utiliser ces fonctions avec une seule colonne ou une seule ligne, car elles sont initialement conçues pour transformer un tableau ou une plage de cellules en une seule colonne ou une seule ligne.

Ces fonctions peuvent ignorer les cellules vides et les erreurs, ce qui les rend idéales pour nettoyer les données avant une analyse plus poussée.

• TOCOL : Utilisez-la pour transformer des données horizontales unidimensionnelles en données verticales.
• TOROW : Utilisez-la pour transformer des données verticales unidimensionnelles en données horizontales.

Exemple :
Supposons que vous ayez des données dans la plage A1:A10 avec des cellules vides et des erreurs. Expérimentez avec les formules suivantes pour modifier l’orientation.

=TOROW(A1:A10, 3) crée une nouvelle ligne en sautant les cellules vides et les erreurs.

[Insérer l’image ici]

=TRANSPOSE(A1:A10) crée une nouvelle ligne avec toutes les données, y compris les cellules vides et les erreurs, ce qui peut ne pas être souhaitable.

Conseils supplémentaires

La fonction TRANSPOSE ne nécessite généralement pas le support de ARRAYFORMULA. Cependant, vous pouvez rencontrer des exemples combinant ces deux éléments, et la formule pourrait échouer si vous supprimez la fonction ARRAYFORMULA.

Cela se produit lorsque vous utilisez une expression à l’intérieur de la fonction TRANSPOSE plutôt qu’une plage physique. L’expression nécessite le support de ARRAYFORMULA pour se développer.

Par exemple, supposons un scénario utilisant SPLIT. Vous souhaitez diviser une chaîne séparée par des virgules et la transposer.

=TRANSPOSE(SPLIT(« Pomme, Orange, Mangue », « , »))

La formule ci-dessus divisera le texte et changera l’orientation d’une seule ligne à une seule colonne.

Cependant, cela peut laisser des caractères d’espacement avec chaque texte divisé. Pour résoudre ce problème, vous pouvez utiliser TRIM avec la combinaison ci-dessus.

=ARRAYFORMULA(TRANSPOSE(TRIM(SPLIT(« Pomme, Orange, Mangue », « , »))))

Dans ce cas, vous avez besoin de ARRAYFORMULA car TRIM nécessite son support pour renvoyer plusieurs valeurs.

Erreurs courantes

Voici les deux erreurs les plus courantes que vous pouvez rencontrer lors de l’utilisation de la fonction TRANSPOSE dans Google Sheets :

1. Erreur #REF!

• Cause : Cette erreur se produit lorsque la référence à la plage source est cassée.
• Solution : Vérifiez votre formule et assurez-vous que la plage source existe toujours à l’endroit prévu.

2. Erreur #N/A

• Cause : Cette erreur indique que TRANSPOSE a reçu plus d’une plage en entrée. Elle ne peut traiter qu’une seule plage ou une seule cellule en tant que données source.
• Solution :
  • Révisez votre formule et assurez-vous de n’utiliser qu’une seule plage, pas plusieurs plages séparées par des virgules ou d’autres opérateurs.
  • Si vous devez combiner des données provenant de plusieurs plages pour la transposition, envisagez d’utiliser des fonctions imbriquées ou d’autres approches pour créer une seule plage d’entrée.

J’espère que les informations ci-dessus vous aideront à résoudre les problèmes liés à cette fonction.