Si vous êtes un de mes fidèles lecteurs, vous avez sûrement déjà appris la méthode la plus simple pour unpivoter un jeu de données, également appelé pivot inverse, dans Google Sheets.
Dans l’un de mes précédents articles sur l’aplanissement des jeux de données, l’un de mes lecteurs, M. S K Srivastava, a partagé cette merveilleuse idée de manière succincte. Je lui donne tout le crédit dans cet article.
Récemment, j’ai publié un article sur l’utilisation d’une fonction non officielle dans Google Sheets appelée FLATTEN. Nous pouvons utiliser cette fonction pour unpivoter (pivote inversement) un tableau dans Google Sheets.
Lecture recommandée : Comment utiliser la fonction FLATTEN dans Google Sheets.
Outre FLATTEN, nous n’avons besoin que de SPLIT et du signe & en tant qu’ArrayFormula pour cela.
Je suis sûr que c’est (la combinaison FLATTEN, Ampersand et SPLIT) la manière la plus simple de pivoter inversement un tableau sans utiliser de script Google Apps.
Note : Si vos données de pivot sont très volumineuses, comme plus de 1000 lignes (ce qui est très rare), cette formule risque de ne pas fonctionner, car la fonction SPLIT a généralement un problème avec un ensemble de données aussi volumineux.
La méthode la plus simple pour unpivoter un jeu de données dans Google Sheets
Données d’exemple pour le pivot inverse
Pour l’exemple, j’importe un tableau approprié pour notre test à partir de cette page Wiki en utilisant la formule IMPORTHTML suivante :
FORMULE : =array_constrain( IMPORTHTML( "https://en.wikipedia.org/wiki/International_wheat_production_statistics", "table",1 ),6,5 )
Pour votre information, Array_Constrain fait partie de la formule IMPORTHTML pour limiter la taille du tableau importé.
Données d’exemple importées :
Les données montrent la production mondiale de blé de l’année 2014 à 2017. Les noms des pays se trouvent dans la colonne A et les productions par année se trouvent dans les colonnes B à E.
Voyons comment unpivoter les données ci-dessus dans Google Sheets, c’est-à-dire les noms des pays dans la première colonne, les années dans la deuxième colonne et les quantités dans la troisième colonne, comme indiqué ci-dessous :
Formule pour unpivoter dans Google Sheets
Voici la formule simple (notez la cellule I2) pour pivoter inversement un tableau dans Google Sheets :
Formule pour unpivoter : =ARRAYFORMULA( split( flatten( A2:A6&"|"&B1:E1&"|"&B2:E6 ),"|" ) )
Si vous voulez des étiquettes de champ comme celles dans I2:K2 (veuillez vous référer à l’image ci-dessus), insérez ceci juste après le signe égal dans la formule et fermez la formule avec }.
Explication de la formule (comment utiliser la formule)
La fonction FLATTEN est la clé. À l’intérieur de la formule FLATTEN, combinez la première colonne de la plage (A2:A6) avec la première ligne de la plage (B1:E1), puis combinez le reste des données (B2:E6).
Le symbole de pipe (vous pouvez utiliser un autre caractère comme une onde) est inséré entre les données combinées aplaties pour faciliter la division.
Si vous souhaitez, vous pouvez modifier la plage combinée à l’intérieur de la fonction FLATTEN, par exemple, en combinant la première ligne de la plage (B1:E1) avec la première colonne de la plage (A2:A6), puis le reste des données (B2:E6).
Si vous suivez cela pour unpivoter l’ensemble de données ci-dessus dans Google Sheets, cela ressemblera à ce qui suit :
Pouvez-vous utiliser des plages ouvertes au lieu de plages fermées dans FLATTEN ?
Utiliser A2:A et B2:E au lieu de A2:A6 et B2:E6 peut causer des problèmes tels qu’une erreur #REF!, le gel de votre feuille ou l’insertion de centaines de lignes vides.
Ici, nous pouvons utiliser une technique que j’ai déjà utilisée avec succès avec MMULT – Utilisation appropriée de MMULT avec des lignes infinies dans Google Sheets.
Qu’est-ce que c’est ?
Remplacez A2:A6 par la plage dynamique (infinie/ouverte) :
array_constrain(A2:A,MATCH(2,1/(A2:A<>"")),1)
Remplacez B2:E6 par la plage dynamique (infinie/ouverte) :
array_constrain(B2:E,MATCH(2,1/(A2:A<>"")),4)
Il y a 4 colonnes dans la plage B2:E6. Le 4 dans la dernière partie de la formule représente ce nombre total de colonnes.
Comment inclure plus d’une colonne fixe dans un jeu de données pivot inverse dans Google Sheets ?
Dans l’exemple ci-dessus, nous n’avons qu’une seule colonne fixe, la colonne A qui contient le « Pays ».
Si nous le souhaitons, nous pouvons inclure plus d’une colonne fixe dans le jeu de données pivot inverse dans Google Sheets. Pour cela, nous devons modifier légèrement la formule ci-dessus. J’ai une méthode dynamique (formule) pour le faire.
Cela signifie que nous pouvons inclure n’importe quel nombre de colonnes fixes avec une légère modification de la formule de pivot inverse ci-dessus.
Voici mes nouvelles données d’exemple. J’ai inclus une colonne supplémentaire nommée « Continent » à notre ancien jeu de données d’exemple (veuillez vous référer à l’image n° 1 pour l’ancien jeu de données).
Si vous ignorez la colonne « Continent » B, selon notre formule de pivot inverse précédente, la formule (1 colonne fixe) pour cette plage serait la suivante :
=ARRAYFORMULA( split( flatten( A2:A6&"|"&C1:F1&"|"&C2:F6 ),"|" ) )
J’ai ignoré B2:B6 et, par conséquent, la sortie de pivot inverse serait similaire à l’image n° 2 ci-dessus.
Voyons comment inclure plusieurs colonnes fixes dans le jeu de données pivot inverse dans Google Sheets.
Étapes
Dans la formule ci-dessus, A2:A6 est notre unique colonne fixe. Mais ce que nous voulons, c’est A2:B6. Remplacer A2:A6 par A2:B6 ne fonctionnera pas. Et alors ?
Utilisez simplement cette formule à la place de A2:B6. Cela signifie que vous devez remplacer A2:A6 dans la formule précédente par la formule ci-dessous qui commence par « transpose ».
transpose(query(transpose(A2:B6&"|"),,9^9))
Donc, la formule pour pivoter inversement un jeu de données avec plus d’une colonne fixe (ici deux) serait la suivante :
=ARRAYFORMULA( split( flatten( transpose(query(transpose(A2:B6&"|"),,9^9))&"|"&C1:F1&"|"&C2:F6 ),"|" ) )
Vous pouvez maintenant facilement ajouter plus de colonnes fixes en modifiant A2:A6. Je veux dire A2:B6 comme ci-dessus pour deux colonnes, A2:C6 pour trois colonnes, et ainsi de suite.
Avantages du pivot inverse
Le principal avantage de pivoter inversement un jeu de données est qu’il apporte de la flexibilité pour manipuler les données avec des fonctions comme SUMIF, QUERY, FILTER, VLOOKUP, etc.
Voici deux exemples. Commençons par SUMIF.
Supposons que nous souhaitons calculer la production totale de blé dans le pays Chine pendant les années 2014-2017. Nous pouvons utiliser SUMIF comme ci-dessous (données selon l’image n° 3).
=sumif(J2:J21,"China",K2:K21)
Veuillez consulter la capture d’écran ci-dessus pour la plage utilisée dans SUMIF.
Note : Si vous utilisez » | » au lieu de « | » dans la formule du jeu de données pivot inverse, alors le SUMIF ci-dessus renverra 0 en raison d’une incompatibilité des critères.
Dans l’exemple suivant, j’utilise QUERY pour regrouper les pays et totaliser la production de blé.
=query({I1:K21},"Select Col2, Sum(Col3) group by Col2")
Résultat :
| Country | sum Qty. in Million MT |
|---|---|
| … | … |
Ici, n’hésitez pas à remplacer I1:K21 par la formule de jeu de données pivot inverse elle-même.
Voilà tout ce qu’il faut savoir sur la façon de pivoter inversement un jeu de données dans Google Sheets. Merci pour votre attention. Amusez-vous bien !
Exemple de feuille 300420
