Dépivotez un tableau avec une seule formule (Office 365)

Avec l'introduction des fonctions matricielles dynamiques et de la fonction LET, Excel devient de plus en plus un langage de programmation matriciel (je ne sais pas si ça existe, mais ça sonne très bien 🙂 ).

LET permet de stocker l'équivalent de variables pour les réutiliser plusieurs fois dans la formule. La beauté de la fonction LET est qu'il suffit juste de changer les premiers paramètres pour adapter la fonction à n'importe quel tableau.

Tableau simple

L'objectif ici est dépivoter ce tableau qui contient une colonne Ville, et 4 colonnes de dates :

Il existe d'autres méthodes discutées ici, mais vous pouvez utiliser cette formule pour dépivoter le tableau :

=LET(T_Lignes;Tableau1[Ville];T_Colonnes;Tableau1[[#En-têtes];[2019]:[2022]];T_Data;Tableau1[[2019]:[2022]]; T_Nb_Lignes;LIGNES(T_Lignes);T_Nb_Colonnes;COLONNES(T_Colonnes); Index_Ligne;MOD(SEQUENCE(T_Nb_Lignes*T_Nb_Colonnes)-1;T_Nb_Lignes)+1; Index_Colonne;RECHERCHEV(SEQUENCE(T_Nb_Colonnes*T_Nb_Lignes;1);CHOISIR({1.2};SEQUENCE(T_Nb_Colonnes;1;1;T_Nb_Lignes);SEQUENCE(T_Nb_Colonnes;1;1;1));2;1); CHOISIR({1.2.3}; INDEX(T_Lignes;Index_Ligne); INDEX(T_Colonnes;1;Index_Colonne); INDEX(T_Data;Index_Ligne;Index_Colonne)))

L'explication de la formule est un peu longue, mais en gros, on calcule les coordonnées lignes et colonne de chaque titre ou valeur. Puis, avec la fonction INDEX, on reconstruit les différentes colonnes. La fonction CHOISIR permet de les fusionner.

Plus de colonnes à gauche

On peut aller plus loin avec deux colonnes, par exemple la ville et le pays. La formule ci-dessous doit probablement encore pouvoir être optimisée pour sélectionner la plage au complet, plutôt que deux colonnes distinctes (Ville et Pays).

=LET(T_Lignes;Tableau1[Ville];T_Lignes2;Tableau1[Pays];T_Colonnes;Tableau1[[#En-têtes];[2019]:[2022]];T_Data;Tableau1[[2019]:[2022]]; T_Nb_Lignes;LIGNES(T_Lignes);T_Nb_Colonnes;COLONNES(T_Colonnes); Index_Ligne;MOD(SEQUENCE(T_Nb_Lignes*T_Nb_Colonnes)-1;T_Nb_Lignes)+1; Index_Colonne;RECHERCHEV(SEQUENCE(T_Nb_Colonnes*T_Nb_Lignes;1);CHOISIR({1.2};SEQUENCE(T_Nb_Colonnes;1;1;T_Nb_Lignes);SEQUENCE(T_Nb_Colonnes;1;1;1));2;1); CHOISIR({1.2.3.4}; INDEX(T_Lignes;Index_Ligne); INDEX(T_Lignes2;Index_Ligne); INDEX(T_Colonnes;1;Index_Colonne); INDEX(T_Data;Index_Ligne;Index_Colonne)))

Plus de colonnes à droite

Et encore plus loin avec une deuxième série de montants.

Attention, il faut impérativement le même nombre de colonnes pour les années, et elles doivent être dans le même ordre (2019,2020,2021,2022)

=LET(T_Lignes;TableauN2[Ville];T_Lignes2;TableauN2[Pays]; T_Colonnes;TableauN2[[#En-têtes];[Profit 2019]:[Profit 2022]];T_Colonnes2;TableauN2[[#En-têtes];[Quantité 2019]:[Quantité 2022]]; T_Data;TableauN2[[Profit 2019]:[Profit 2022]];T_Data2;TableauN2[[Quantité 2019]:[Quantité 2022]]; T_Nb_Lignes;LIGNES(T_Lignes);T_Nb_Colonnes;COLONNES(T_Colonnes); Index_Ligne;MOD(SEQUENCE(T_Nb_Lignes*T_Nb_Colonnes)-1;T_Nb_Lignes)+1; Index_Colonne;RECHERCHEV(SEQUENCE(T_Nb_Colonnes*T_Nb_Lignes;1);CHOISIR({1.2};SEQUENCE(T_Nb_Colonnes;1;1;T_Nb_Lignes);SEQUENCE(T_Nb_Colonnes;1;1;1));2;1); CHOISIR({1.2.3.4.5}; INDEX(T_Lignes;Index_Ligne);INDEX(T_Lignes2;Index_Ligne); CNUM(DROITE( INDEX(T_Colonnes;1;Index_Colonne);4)); INDEX(T_Data;Index_Ligne;Index_Colonne); INDEX(T_Data2;Index_Ligne;Index_Colonne)))

N'hésitez-pas si vous voyez des améliorations ou des limites à la formule.

Quelques sources d'inspiration :

Répéter une valeur plusieurs fois avec Recherchev (en Anglais)

https://www.extendoffice.com/documents/excel/1897-excel-repeat-cell-value-x-times.html