[Google Sheets] Transformer un tableau en liste exploitable plus facilement

Bonjour,

Comment concilier ergonomie et efficacité ?

Il est plus facile de rentrer des données sous forme de tableau. Prenons l'exemple d'une équipe dont on tient à jour un tableau de "présence" avec ces codes

CP=congés payés, EF=événement de famille, F=formation, Mat=maternité, P=présence, RTT

On conçoit que mettre ces données sur un tableau structuré est plus facile que de rentrer une liste (et évite les redondances ou indomptabilités s'il advenait que quelqu'un mette 2 codes pour le même jour et la même personne). Pourtant, si on veut accéder à ces données, faire des analyses, des statistiques, des cumuls, il serait plus intéressant de travailler sur une pseudo-base de données (qui est plutôt disons-le une liste).

Les formules de Google Sheets permettent de passer de l'un à l'autre. Pour cela on utilisera les fonctions suivantes :

columns
flatten
mod
query
roundup
rows
sequence
transpose
vlookup

je donne la solution tout de suite, tab étant le nom du tableau, sachant je donnerai des explications ci-dessous (excellent moyen de découvrir ou revisiter ces fonctions)

=query({
arrayformula(vlookup(arrayformula(ROUNDDOWN(flatten(sequence(rows(tab);columns(tab);0;1))/columns(tab))*columns(tab)+1);{flatten(sequence(rows(tab);columns(tab);1;1))\flatten(tab)};2;0))\
arrayformula(vlookup(mod(flatten(sequence(rows(tab);columns(tab);0;1));columns(tab))+1;{flatten(sequence(rows(tab);columns(tab);1;1))\flatten(tab)};2;0))\
flatten(tab)
};"select * where Col1 is not null and Col2 is not null and Col3 is not null";0)

https://docs.google.com/spreadsheets/d/1CgPHVY_PHgS_DoX1ifkXWOyTrmr7ggcxMyU5udcODE4/copy

Je précise que l'on trouve des solutions comme celle de BenCollins https://www.benlcollins.com/spreadsheets/unpivot-in-google-sheets/. La démarche est en effet similaire mais le résultat auquel on parvient ici est un peu plus simple et plus souple de mise en place.

FLATTEN

Cette fonction permet de mettre en colonne la plage de données, la prise en compte des cellules est la même que la lecture latine : de gauche à droite et de haut en bas en dessinant un Z

=flatten(tab)

On aura donc le résultat suivant de la plage citée plus haut

on a tous les éléments de la 3ème colonne (Valeurs), y compris la première cellule vierge en haut à gauche, et un peu plus ... on verra plus tard comment les cellules Noms et Jours seront supprimées.

SEQUENCE

sequence est une fonction qui permet de remplir un tableau de valeurs

=SEQUENCE(lignes; colonnes; début; pas)

le nombre de lignes du tableau tab est =rows(tab) soit 8 et le nombre de colonnes =columns(tab) soit 6 ... pour alléger la lecture on utilisera le résultat 8 et 6

=sequence(8;6;1;1)

donne donc

ce qui permet un repérage des valeurs transformées par FLATTEN. On peut ainsi construire un tableau de correspondance en juxtaposant via \ les 2 colonnes

={flatten(sequence(8;6;1;1))\flatten(tab)}

Dernier élément fondamental (on passera ensuite au mixage des éléments de "base") : repérer les en-têtes de colonnes et "en-têtes" de ligne.

Les en-têtes de colonnes (ici les jours) sont les éléments

1    2    3    4    5    6

c'est donc le reste de la division par 6 des valeurs précédentes, soit avec la fonction modulo

=arrayformula(mod(sequence(8;6;0;1);6)+1)

Les "en-têtes" de ligne (ici les noms) sont les éléments

1    7    13    19    25    31    37    43

qui s'obtiendront par la valeur entière de la division des valeurs précédentes par 6, soit :

=arrayformula(ROUNDDOWN(sequence(8;6;0;1)/6)*6+1)

on a tous les éléments pour combiner via des RECHERCHEV/VLOOKUP ... à suivre

Dernière étape ... utiliser les ingrédients vus plus haut :

en A et B : la liste des repères et des données de toutes les colonnes et lignes

=flatten(tab)

en D : la liste mise en colonne par flatten des repères des en-têtes de colonnes ... d'où en E les en-têtes elle-même issues de A,B par VLOOKUP

=arrayformula(vlookup(
mod(flatten(sequence(8;6;0;1));6)+1;
{flatten(sequence(8;6;1;1))\flatten(tab)};
2;0))

en G : la liste mise en colonne par flatten des repères des "en-têtes" de lignes ... d'où en H les en-têtes elle-même issues de A,B par VLOOKUP

=arrayformula(vlookup(
arrayformula(ROUNDDOWN(flatten(sequence(8;6;0;1))/6)*6+1);
{flatten(sequence(8;6;1;1))\flatten(tab)};
2;0))

le résultat attendu est donc en H, E et B (dans cet ordre) sur lequel on applique query pour ne pas retenir les lignes où une case est vide, ce qui enlève automatiquement les en-têtes de la colonne H

on remplacera aussi les valeurs 8 et 6 par rows(tab) et columns(tab)

à noter que H aurait pu être obtenu comme E en transposant les données

=arrayformula(vlookup((roundup(flatten(sequence(6;8;1;1)/columns(tab))));{flatten(sequence(6;8;1;1))\flatten(transpose(tab))};2;0))

C'est pas simple, je le conçois et je ne suis pas sûr d'avoir été suffisamment didactique ! Mais refaire l'opération de A à Z peut-être un bon entraînement !! Enjoy.