Dépivoter un tableau avec la nouvelle fonction LET (Excel 365)

Voici un exem­ple assez com­plet qui décor­tique la for­mule que j’avais posté ici. Vous y trou­verez le fichi­er et la formule.

Bon vision­nage.

En cadeau de bien­v­enue : les 7 com­bi­naisons de touch­es indis­pens­ables sur Excel

Un cercle dans un graphique

Directe­ment inspiré de cet arti­cle, voici un petit fichi­er qui per­met de représen­ter un cer­cle dans un graphique.

Çà pour­rait être utile, sait-on jamais 🙂

La magie du fichi­er se trou­ve dans la créa­tion de noms (For­mules — Ges­tion­naires de noms)

En cadeau de bien­v­enue : les 7 com­bi­naisons de touch­es indis­pens­ables sur Excel

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

Avec l’in­tro­duc­tion des fonc­tions matricielles dynamiques et de la fonc­tion LET, Excel devient de plus en plus un lan­gage de pro­gram­ma­tion matriciel (je ne sais pas si ça existe, mais ça sonne très bien 🙂 ). 

LET per­met de stock­er l’équiv­a­lent de vari­ables pour les réu­tilis­er plusieurs fois dans la for­mule. La beauté de la fonc­tion LET est qu’il suf­fit juste de chang­er les pre­miers paramètres pour adapter la fonc­tion à n’im­porte quel tableau.

Tableau simple

L’ob­jec­tif ici est dépiv­ot­er ce tableau qui con­tient une colonne Ville, et 4 colonnes de dates : 

Il existe d’autres méth­odes dis­cutées ici, mais vous pou­vez utilis­er cette for­mule pour dépiv­ot­er 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’ex­pli­ca­tion de la for­mule est un peu longue, mais en gros, on cal­cule les coor­don­nées lignes et colonne de chaque titre ou valeur. Puis, avec la fonc­tion INDEX, on recon­stru­it les dif­férentes colonnes. La fonc­tion CHOISIR per­met de les fusionner.

Plus de colonnes à gauche

On peut aller plus loin avec deux colonnes, par exem­ple la ville et le pays. La for­mule ci-dessous doit prob­a­ble­ment encore pou­voir être opti­misée pour sélec­tion­ner la plage au com­plet, plutôt que deux colonnes dis­tinctes (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 deux­ième série de montants. 

Atten­tion, il faut impéra­tive­ment le même nom­bre 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élio­ra­tions ou des lim­ites à 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

En cadeau de bien­v­enue : les 7 com­bi­naisons de touch­es indis­pens­ables sur Excel