Si vous êtes l’un de mes lecteurs réguliers, vous avez sans doute déjà appris la manière la plus simple de désempiler un ensemble de données, appelée également pivot inversé, dans Google Sheets.
La raison en est que l’un de mes lecteurs, M. S K Srivastava, a partagé cette merveilleuse idée de manière succincte dans l’un de mes précédents articles sur l’aplatissement des ensembles de données. Je lui donne donc tout le crédit qu’il mérite 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 désempiler (pivot inversé) un tableau dans Google Sheets.
À lire : Comment utiliser la fonction FLATTEN dans Google Sheets.
En plus de FLATTEN, nous avons seulement besoin 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 réaliser un pivot inversé d’un tableau sans utiliser Google Apps Script.
Remarque : Si vos données pivot sont très importantes, par exemple 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 de cette taille.
La méthode la plus facile pour désempiler un ensemble de données dans Google Sheets
Exemple de données pour le pivot inverse
Pour cet exemple, j’importe un tableau adapté à notre test depuis cette page Wiki en utilisant la formule IMPORTHTML suivante :
=array_constrain( IMPORTHTML( "https://fr.wikipedia.org/wiki/Statistiques_internationales_de_production_de_blé", "table",1 ),6,5 )Pour votre information, Array_Constrain fait partie de la formule IMPORTHTML permettant de restreindre la taille du tableau importé.
Données importées pour l’exemple :
Image n°01
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 désempiler ces données 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 de désempilage dans Google Sheets
Voici la formule simple (insérez-la dans la cellule I2) pour désempiler un tableau dans Google Sheets :
=ARRAYFORMULA( split( flatten( A2:A6&"|"&B1:E1&"|"&B2:E6 ),"|" ) )Si vous souhaitez des étiquettes de champ comme celles de la cellule 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, nous avons combiné la première colonne de la plage (A2:A6) avec la première ligne de la plage (B1:E1) puis nous avons combiné le reste des données (B2:E6).
Le symbole de pipe (vous pouvez utiliser tout autre caractère comme un tilde) est inséré entre les deux pour faciliter la division des données combinées et aplaties.
Si vous souhaitez, vous pouvez modifier la plage combinée dans la formule FLATTEN, par exemple, combinez 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 ces étapes pour désempiler l’ensemble de données ci-dessus dans Google Sheets, cela ressemblerait à ce qui suit :
Image n°03
Pouvons-nous utiliser des plages ouvertes plutôt que des plages fermées dans la fonction FLATTEN ?
Utiliser A2:A et B2:E au lieu de A2:A6 et B2:E6 peut poser certains problèmes, par exemple une erreur #REF! dans la formule, 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 ? Eh bien, remplacez A2:A6 par la plage dynamique (infinie/ouverte) suivante :
array_constrain(A2:A,MATCH(2,1/(A2:A<>""),1),1)Replacez B2:E6 par la plage dynamique (infinie/ouverte) suivante :
array_constrain(B2:E,MATCH(2,1/(A2:A<>""),1),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 l’ensemble de données désempilé dans Google Sheets ?
Dans l’exemple ci-dessus, nous n’avons qu’une seule colonne fixe, à savoir la colonne A qui contient le « Pays ».
Si nous le souhaitons, nous pouvons inclure plusieurs colonnes fixes dans l’ensemble de données désempilé dans Google Sheets. Pour cela, nous devons légèrement modifier la formule ci-dessus. J’ai une manière dynamique (formule) de le faire.
C’est-à-dire, nous pouvons inclure n’importe quel nombre de colonnes fixes avec une modification mineure de la formule de pivot inversé ci-dessus.
Voici mon nouvel ensemble de données d’exemple. J’ai inclus une colonne supplémentaire appelée « Continent » dans notre ancien ensemble de données d’exemple (veuillez vous référer à l’image n°01 pour l’ancien ensemble de données).
Image n°04
Si vous excluez la colonne « Continent » B, conformément à notre formule précédente de pivot inversé, la formule (pour une colonne fixe) pour cette plage serait la suivante :
=ARRAYFORMULA( split( flatten( A2:A6&"|"&C1:F1&"|"&C2:F6 ),"|" ) )J’ai donc exclu B2:B6 et, par conséquent, la sortie désempilée serait similaire à l’image n°02 ci-dessus.
Voyons maintenant comment inclure plusieurs colonnes fixes dans l’ensemble de données désempilé dans Google Sheets.
Étapes
Dans la formule ci-dessus, A2:A6 est notre seule et unique colonne fixe. Mais ce que nous voulons, c’est A2:B6. Remplacer A2:A6 par A2:B6 ne fonctionnera pas. Alors, que faire ?
Il vous suffit d’utiliser cette formule à la place de A2:B6. Cela signifie que vous devez remplacer A2:A6 dans la formule ci-dessus par la formule suivante qui commence par « transpose » :
transpose(query(transpose(A2:B6&"|"),,9^9))La formule pour désempiler un ensemble de données avec plus d’une (ici deux) colonnes fixes serait donc la suivante :
=ARRAYFORMULA( split( flatten( transpose(query(transpose(A2:B6&"|"),,9^9))&"|"&C1:F1&"|"&C2:F6 ),"|" ) )
Image n°05
Vous pouvez maintenant facilement ajouter davantage de colonnes fixes en modifiant A2:A6. Par exemple, A2:B6 pour deux colonnes, A2:C6 pour trois colonnes, etc.
Avantages du pivot inversé
Le principal avantage du pivot inversé d’un ensemble de données est qu’il apporte une flexibilité pour manipuler les données avec des fonctions telles que SUMIF, QUERY, FILTER, VLOOKUP, etc.
Voici deux exemples. Commençons par SUMIF.
Supposons que nous voulions calculer la production totale de blé dans le pays Chine au cours des années 2014 à 2017. Nous pouvons utiliser SUMIF comme ceci (données selon l’image n°03) :
=sumif(J2:J21,"Chine",K2:K21)Veuillez consulter la capture d’écran ci-dessus pour la plage utilisée dans SUMIF.
Remarque : Si vous utilisez » | » au lieu de « | », la formule de l’ensemble de données désempilé retournera 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 :
| Pays | Qté. en millions de tonnes |
|---|---|
| China | 268 |
| India | 97 |
| Russia | 85 |
| … | … |
Ici, vous pouvez librement remplacer I1:K21 par la formule de l’ensemble de données désempilé elle-même.
C’est tout ce qu’il y a à savoir sur la manière de désempiler un ensemble de données dans Google Sheets. Merci de votre attention. Profitez-en !
Feuille d’exemple 300420
Ressources :
- Comment utiliser la fonction GETPIVOTDATA dans Google Sheets.
- Créer un rapport d’analyse d’âge à l’aide d’une table pivot dans Google Sheets.
- Détail des données dans un tableau croisé dynamique dans Google Sheets [Regroupement par date].
- Comment mettre en forme l’en-tête de la table pivot dans Google Sheets.
- Comment pivoter plusieurs colonnes dans une requête dans Google Sheets.
- Tout sur le champ calculé dans une table pivot dans Google Sheets.
- Comment trier les colonnes des totaux dans un tableau croisé dynamique dans Google Sheets.
- Comment trier les colonnes d’un tableau croisé dynamique dans l’ordre personnalisé dans Google Sheets.
- Extraire les lignes total et grand total d’un tableau croisé dynamique dans Google Sheets.
- Comment filtrer les 10 premiers éléments dans un tableau croisé dynamique de Google Sheets.
