Google Sheets : comment rendre dynamique la transformation d’un tableau avec plusieurs colonnes d’en-têtes en une table de données exploitables (unpivot partie 2) ?

Dans l’article précédent, nous avons vu comment transformer un tableau avec plusieurs colonnes d’en-têtes en une table de données exploitables. Nous allons voir aujourd’hui une méthodologie pour rendre dynamique cette transformation. Pour rappel, “unpivot” permet […]

Laisser une évaluation

2 888
personnes ont consulté cet article

5 minutes

Rédigé par Pierre Le Moing - il y a 3 ans et modifié le 24/07/2023 à 16:41

Ce que vous allez découvrir

Pour réaliser cette opération, nous allons mettre en œuvre plusieurs fonctions et fonctionnalités, en plus des fonctions précédemment utilisées ;
- Plage nommée
- COLONNE() et LIGNE()
- ADRESSE(), REGEXEXTRACT() et JOIN()
- MAX(), FILTER() et INDIRECT()
- TRANSPOSE()
- QUERY()
Étape 1 : nommer la cellule du début de la base de données à transformer
Étape 2 : récupérer les numéros de ligne et de colonne de la plage nommée i.e. “début_plage_data” afin de trouver la plage de la première colonne ainsi que la plage de la première ligne
Étape 3 : trouver la dernière cellule non vide de la première colonne (A) ainsi que la dernière cellule non vide de la première ligne (1)
Étape 4 : entrées manuelles pour indiquer le nombre d’en-tête(s) ainsi que le nombre de colonnes fixes
Étape 5 : reconstitution dynamique des différentes zones
Étape 6 : application de la formule vue dans le premier article concernant ce sujet en utilisant la fonction INDIRECT
Étape 7 (optionnelle) :

Google Sheets : comment rendre dynamique la transformation d’un tableau avec plusieurs colonnes d’en-têtes en une table de données exploitables (unpivot partie 2) ?

Pour réaliser cette opération, nous allons mettre en œuvre plusieurs fonctions et fonctionnalités, en plus des fonctions précédemment utilisées ;
– Plage nommée
– COLONNE() et LIGNE()
– ADRESSE(), REGEXEXTRACT() et JOIN()
– MAX(), FILTER() et INDIRECT()
– TRANSPOSE()
– QUERY()

Rappel du besoin :

Préalable :
– Avoir vu et compris l’article précédent

Prérequis :
– La ligne d’en-tête, ligne n° 1 dans notre cas, ne doit contenir rien d’autre que les données que nous souhaitons voir affichées ; autrement dit, les cellules H1:ZZ1 ou plus, doivent être vides
– La première colonne, colonne A dans notre cas, ne doit contenir rien d’autre que les données que nous souhaitons voir affichées ; autrement dit, les cellules A7:A (plage semi-ouverte), doivent être vides.

Rappel : comme indiqué sur le schéma, le tableau de base est constitué de trois zones :

Zone 1 : les colonnes fixes, c’est à dire celles que vous voulez garder en l’état.
Zone 2 : les en-têtes que vous souhaitez “basculer” en une seule colonne, les “Années”.
Zone 3 : toutes les valeurs pour chaque année, que vous souhaitez “redistribuer” en une seule colonne, les “Valeurs”.

Étape 1 : nommer la cellule du début de la base de données à transformer

Cette opération très simple laissera pourtant la possibilité aux futurs utilisateurs de la base de données, d’insérer, à gauche de la colonne A, une ou plusieurs colonnes ainsi qu’au-dessus de la ligne 1 une ou plusieurs lignes sans pour autant perturber le fonctionnement/l’automatisation de cette opération.

Étape 2 : récupérer les numéros de ligne et de colonne de la plage nommée i.e. “début_plage_data” afin de trouver la plage de la première colonne ainsi que la plage de la première ligne

Pour ce faire, nous allons utiliser :

=COLONNE(début_plage_data) et =LIGNE(début_plage_data)

Puisque nous sommes pour l’instant en A1, les résultats seront donc respectivement 1 (Colonne A) et 1 (ligne 1). Nous allons aussi nommer ces deux cellules : PC pour la Première Colonne et PL pour la Première ligne, sous-entendu de la base de données.
Pour déterminer l’adresse de cette cellule, nous allons utiliser =ADRESSE(PL;PC;4) ; Le troisième argument de cette fonction, le 4, indique que la référence est relative au niveau de la ligne et de la colonne ; le résultat sera donc A1. Dans exemple, le résultat est dans la cellule M6.

Nous allons maintenant utiliser une expression régulière pour obtenir la lettre de cette cellule :

=REGEXEXTRACT(M6;"[A-Z]+") ;

le résultat est bien sûr A (situé en M4).

Nous allons reconstituer la plage de la première colonne de notre base de données en utilisant =join(« : »;M4;M4) ; le résultat est A:A (situé en N4).
Nous allons aussi reconstituer la plage de la première ligne de notre base de données en utilisant =join(« : »;PL;PL) ; le résultat est 1:1 (situé en N5).

Étape 3 : trouver la dernière cellule non vide de la première colonne (A) ainsi que la dernière cellule non vide de la première ligne (1)

Nous allons mettre en œuvre la fonction FILTER :

=max(filter(ROW(indirect(N4));indirect(N4)&lt;>""))

Explications : nous appliquons la fonction FILTER pour trouver la ligne (row) de la première colonne (plage située en N4) qui n’est pas vide i.e. différent de vide (<> » »). Nous pouvons maintenant encapsuler tout cela dans un MAX() pour obtenir le numéro de la dernière ligne non vide de la base de données i.e. la ligne 6 dans notre cas. Le résultat est stocké dans la cellule O5 (plage nommée “DL” Denière Ligne).

Avec le même principe, =max(filter(column(indirect(N5));indirect(N5)<> » »)), nous allons obtenir le numéro de la dernière colonne non vide i.e. 7 (colonne G) dans notre cas. Le résultat est stocké dans la cellule O4 (plage nommée “DC” Dernière Colonne).

Étape 4 : entrées manuelles pour indiquer le nombre d’en-tête(s) ainsi que le nombre de colonnes fixes

Le nombre de ligne d’en-tête(s)de la zone 2 sont les en-têtes que vous souhaitez “basculer” en une seule colonne, “Années” : cette donnée est stockée dans la cellule L7 (plage nommée “nb_entête”).
Le nombre de colonnes fixes correspond au nombre de colonnes de la zone 1, c’est à dire trois dans notre cas. Cette donnée est stockée dans la cellule L8 (plage nommée “nb_col_fixes”).

Étape 5 : reconstitution dynamique des différentes zones

Zone 1 : commence à la PL (Première ligne) + nb_entête et PC (Première colonne) i.e. A2 (cellule L9)
Cette zone 1, se termine à + deux colonnes (nb_col_fixes -1) et va jusqu’à la dernière ligne non vide i.e. C6 (cellule M9).
La plage A2:C6 est reconstituée en N9 avec, =join(« : »;L9;M9).

Zone 2 : les en-têtes que nous souhaitons “basculer” en une seule colonne, “Années”, commencent à la première ligne / première colonne + le nombre de colonnes fixes, =address(PL;PC+nb_col_fixes;4), soit D1, valeur stockée en L10. Cette même zone se termine à la première ligne / dernière colonne, =address(PL;DC;4), soit G1, valeur stockée en M10.
La plage D1:G1 est reconstituée en N10 avec, =join(« : »;L10;M10).

Zone 3 : cette dernière commence en première ligne + nombre d’en-tête / première colonne + nombre de colonnes fixes, =address(PL+nb_entête;PC+nb_col_fixes;4),soit D2, valeur stockée en L11. Elle se termine en dernière ligne / dernière colonne, =address(DL;DC;4), soit G6, valeur stockée en M11.

La plage D2:G6 est reconstituée en N11 avec, =join(« : »;L11;M11).

À cette étape, il faut se poser une question primordiale : pour une base de données existante que nous souhaitons “unpivoter” dynamiquement, combien de fois le nombre de colonnes fixes sera variable ?

D’après notre “ami” Wilfredo (loi de pareto), dans seulement 20% des cas.

Donc pour 80% d’entre nous, le travail est quasiment fini ; bonne nouvelle.

Comme indiqué dans l’article précédent, s’il y a plusieurs colonnes fixes, il faut alors toutes les associer ; traduction pour la Zone 1, c’est à dire la plage A2:C6, il faut donc la “découper” en trois sous-zones i.e. A2:A6, B2:B6 et C2:C6, toujours dans notre cas de figure. Il va sans dire que la dernière ligne non vide DL, la ligne 6 ici, doit être détectée automatiquement.

A2:A6  =ADRESSE(PL+nb_entête;PC;4)&amp;":"&amp;ADRESSE(DL;PC;4) > localisé en N14

B2:B6  =ADRESSE(PL+nb_entête;PC+1;4)&amp;":"&amp;ADRESSE(DL;PC+1;4) > localisé en N15

C2:C6  =ADRESSE(PL+nb_entête;PC+2;4)&amp;":"&amp;ADRESSE(DL;PC+2;4) > localisé en N16

Étape 6 : application de la formule vue dans le premier article concernant ce sujet en utilisant la fonction INDIRECT

En effet, nous allons maintenant ré-utiliser exactement la même formule que précédemment, en remplaçant les différentes zones par INDIRECT(N14) dans notre cas pour la zone A2:A6.

Formule finale :

=ArrayFormula(split(flatten(indirect(N14)&amp;"🔹"&amp;indirect(N15)&amp;"🔹"&amp;indirect(N16)&amp;"🔹"&amp;indirect(N10)&amp;"🔹"&amp;indirect(N11));"🔹"))

Étape 7 (optionnelle) :

Pour les 20% restants, nous allons utiliser la fonction QUERY d’une façon un petit peu détournée.

Contrainte : vérifier qu’il n’y a pas d’espace à l’intérieur de toutes les cellules des colonnes fixes.
Si besoin, établir un tableau de correspondance entre “Alpha A” et “Alpha_A” ou utiliser la fonction SUBSTITUE() pour remplacer tous les espaces par des “_” par exemple. Cette contrainte est nécessaire pour toutes les cellules de la Zone 1, i.e. pour les Fournisseurs, Produits et Catégorie en ce qui nous concerne.

Nota : la solution présentée ci-après est une des possibilités permettant de rendre dynamique la construction de la Zone 1, quel que soit le nombre de colonnes fixes, mais il en existe très certainement d’autres ; à vous de jouer, c’est votre challenge. J’ai hâte de voir les résultats !

Rappel : la fonction QUERY nécessite une plage de données, une requête et en option, le nombre de ligne d’en-têtes de la base de données ; ce nombre est 1 par défaut.

Quand il y a plusieurs lignes d’en-têtes, la fonction QUERY les associe dans une même cellule avec un espace entre chaque chaîne de caractères.

Exemple : si la 1ère ligne d’en-tête est “Titre_1” et la 2ème “Titre_2”, alors le résultat sera “Titre_1 Titre_2”.

Vous me voyez venir avec mes gros sabots ; alors que QUERY est capable d’associer des chaînes de caractères séparées par un espace, SPLIT sera quant à lui capable de les séparer.

Avant tout, nous allons commencer par transposer l’ensemble de cette fameuse Zone 1 afin de basculer tous les éléments de la sous-zone colonne A2:A6 en ligne puis ensuite ceux de la sous-zone colonne B2:B6 dans la ligne suivante et enfin ceux de la sous-zone C2:C6 dans la dernière ligne de cette opération.

Nota : pour faciliter la lecture et la compréhension, j’ai utilisé la notation A2:C6, mais en fait il faut utiliser la fonction INDIRECT(N9), là où est stockée cette zone reconstituée.

Maintenant, nous allons donc encapsuler le précédent TRANSPOSE à l’intérieur d’un QUERY, sans requête mais avec un très grand nombre de lignes d’en-têtes, par exemple 100.
Nous pourrions tout aussi bien prendre 9^9 (9 puissance 9) ce qui représenterait 387 420 489 lignes ; ça laisse un peu de marge !

Voici le résultat :

Nous allons finalement remettre tout ça en une seule colonne en faisant appel à FLATTEN (nous pourrions tout aussi bien utiliser TRANSPOSE dans ce cas là) et en même temps nous allons SUBSTITUE les espaces (“ “) par notre🔹; voici le résultat :

La décomposition de toutes les formules précédentes n’est que purement pédagogique mais absolument pas nécessaire, voire au contraire !

Voici donc (enfin !) la formule finale qui faut écrire dans une seule cellule après avoir reconstitué les 3 zones, c’est à dire juste après environ le milieu de l’étape 5 :

=ArrayFormula(split(flatten(flatten(query(transpose(indirect( &nbsp; N9)&amp;"🔹");;100))&amp;"🔹"&amp;indirect(N10)&amp;"🔹"&amp;indirect(N11));"🔹"))

Dorénavant,votre base de données peut-être mise à jour avec autant de lignes et de colonnes que vous le souhaitez, le unpivot fera toujours le job en dynamique.

Dans tous les cas, n’hésitez pas à nous contacter pour vous accompagner dans votre formation ou pour des projets spécifiques autour des outils Google.

Besoin d'un peu plus d'aide sur Sheets ?

Des formateurs sont disponibles toute l'année pour vous accompagner et optimiser votre utilisation de Sheets, que ce soit pour votre entreprise ou pour vos besoins personnels !

Découvrir nos formations Sheets