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 :
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).
T_Lignes; T_Colonnes; T_Nb_Lignes; T_Nb_Colonnes; T_Data correspondent aux noms des variables qui sont définis au début de la fonction LET pour être ensuite réutilisées dans le reste de la fonction.
Bonjour Gaëtan,
J'arrive ici en cherchant (une explication sur) les limites du matriciel avec let. Ta formule avec recherche, sequence et choisir est impressionnante :o)
Tu connais probablement, transpose() peut être super intéressant.
Mon souci vient quand j'empile dans let() des parametres (plage) qui sont ensuite utilisés pour définir autre parametre (plage) avec decaler() et/ou Filtre() . As tu connaissance de limites ou des conseils ?
Merci et bravo pour ta pédagogie et ta science
Christian
Bonjour Christian,
Merci pour les commentaires. Oui, transpose existe aussi (depuis très longtemps), mais elle fait quelque chose de différent par rapport à dépivoter. Au début, il y a toujours un peu de confusion entre les deux. Voir powerquery qui a d'ailleurs les deux fonctions avec deux boutons différents.
Pour la question, il faudrait voir la formule. Il y a des transformations de plages en tableaux, ce qui peut créer des problèmes. J'aime beaucoup MAKEARRAY pour gérer cela de manière intelligible.
Pour moi, la limite la plus importante actuellement est ce qu'on appelle des array of array. C'est l'idée de ne pas pouvoir faire un tableau de tableaux. C'est utile par exemple pour la fonction =FRACTIONNER.TEXTE(B2:B6;"/")
qui ne "fonctionne" pas comme attendu.
=FRACTIONNER.TEXTE(B2;"/")
fonctionne bien, amis on aurait préféré que la formule puisse se propager automatiquement aussi vers le bas.
Une autre limite est liée aux lambda récursives qui ne vont pas au delà d'un certain nombre d'itérations.
Finalement, la dernière limite est la testabilité et la complexité des fonctions créées. Il y a moyen de tester des fonctions let en reprenant les différentes valeurs intermédiaires en dernière position, ce qui permet de tester graduellement ce qui va ou ne va pas.
En espérant que ca aide.
Excellente question, merci beaucoup.
Bonjour Gaëtan,
J'essaie d'enchaîner des fonctions matricielles dynamiques, mais je tombe souvent en echec...Peut être auras tu une piste. J'essaie de concentrer dans un let( ) en commençant par définir une zone datas globale, et ensuite piocher telle ou telle colonne... Les grouper.par et pivoter.par ouvrent beaucoup de possibilités...
J'ai réussi à simplifier un de mes pb : =NB.SI(CHOISIRCOLS(a2:a10;1);"x")
Excel 365 REFUSE de l'écrire ! Ce n'est pas un résultat bizarre, ou un #VALEUR, c'est carrément qu'Excel ne reconnait pas la syntaxe ... ça marche avec index().
Choisircols retourne un tableau de VARIANT, et nb.si semble n'accepter que RANGE en 1er argument (plage)
J'utilise Office 365 canal Entreprise semestriel, version 2502 et l'entreprise bloque pas mal de choses, dont le support.
Grand merci pour ta réponse, et le lien.
Pour moi, il n'est pas intuitif que choisircols() ne retourne pas une colonne donc un range ;o)
Paradoxalement celà doit marcher avec decaler() (mais pas avec unique(), exclure() ni prendre()
Où peut on trouver des définitions suffisamment précises des fonctions pour les imbriquer, notamment avec LET()
Je n'ai par exemple pas su enchaîner un pivoter.par d'un grouper.par. Au début je croyais que ça venait de fonctions volatiles :o(
Bis,
J'ai vu des infos intéressantes sur https://exceljet.net/articles/excels-racon-functions
Néanmoins, après que Microsoft ait quasi généralisé le matriciel dynamique, je m'attendrais à voir évoluer/disparaitre ces restrictions.
Je suis peut être trop gourmand mais le matriciel, les nouvelles fonctions (grouper.par et pivoter.par notamment) conjuguées avec LET(), m'ouvrent l'appétit :o)
Y compris remplacer certains TCD, et alimenter directement des graphiques (cascade, pas autorisé en GCD par exemple)...
Bonjour Gaetan,
Pouvez-vous m'indiquer à quoi correspondent T_Lignes; T_Colonnes; T_Nb_Lignes
T_Nb_Colonnes; T_Data dans les formules ?
Merci
T_Lignes; T_Colonnes; T_Nb_Lignes; T_Nb_Colonnes; T_Data correspondent aux noms des variables qui sont définis au début de la fonction LET pour être ensuite réutilisées dans le reste de la fonction.
Bonjour,
Les commentaires ne s'affichent plus chez moi (testé sur IE et Chrome) dans vos posts récents.
Le titre indique bien qu'il y a des coms.
++
Bonjour Gaëtan,
J'arrive ici en cherchant (une explication sur) les limites du matriciel avec let. Ta formule avec recherche, sequence et choisir est impressionnante :o)
Tu connais probablement, transpose() peut être super intéressant.
Mon souci vient quand j'empile dans let() des parametres (plage) qui sont ensuite utilisés pour définir autre parametre (plage) avec decaler() et/ou Filtre() . As tu connaissance de limites ou des conseils ?
Merci et bravo pour ta pédagogie et ta science
Christian
Bonjour Christian,
Merci pour les commentaires. Oui, transpose existe aussi (depuis très longtemps), mais elle fait quelque chose de différent par rapport à dépivoter. Au début, il y a toujours un peu de confusion entre les deux. Voir powerquery qui a d'ailleurs les deux fonctions avec deux boutons différents.
Pour la question, il faudrait voir la formule. Il y a des transformations de plages en tableaux, ce qui peut créer des problèmes. J'aime beaucoup MAKEARRAY pour gérer cela de manière intelligible.
Pour moi, la limite la plus importante actuellement est ce qu'on appelle des array of array. C'est l'idée de ne pas pouvoir faire un tableau de tableaux. C'est utile par exemple pour la fonction =FRACTIONNER.TEXTE(B2:B6;"/")
qui ne "fonctionne" pas comme attendu.
=FRACTIONNER.TEXTE(B2;"/")
fonctionne bien, amis on aurait préféré que la formule puisse se propager automatiquement aussi vers le bas.
Une autre limite est liée aux lambda récursives qui ne vont pas au delà d'un certain nombre d'itérations.
Finalement, la dernière limite est la testabilité et la complexité des fonctions créées. Il y a moyen de tester des fonctions let en reprenant les différentes valeurs intermédiaires en dernière position, ce qui permet de tester graduellement ce qui va ou ne va pas.
En espérant que ca aide.
Excellente question, merci beaucoup.
Bonjour Gaëtan,
J'essaie d'enchaîner des fonctions matricielles dynamiques, mais je tombe souvent en echec...Peut être auras tu une piste. J'essaie de concentrer dans un let( ) en commençant par définir une zone datas globale, et ensuite piocher telle ou telle colonne... Les grouper.par et pivoter.par ouvrent beaucoup de possibilités...
J'ai réussi à simplifier un de mes pb : =NB.SI(CHOISIRCOLS(a2:a10;1);"x")
Excel 365 REFUSE de l'écrire ! Ce n'est pas un résultat bizarre, ou un #VALEUR, c'est carrément qu'Excel ne reconnait pas la syntaxe ... ça marche avec index().
Choisircols retourne un tableau de VARIANT, et nb.si semble n'accepter que RANGE en 1er argument (plage)
J'utilise Office 365 canal Entreprise semestriel, version 2502 et l'entreprise bloque pas mal de choses, dont le support.
Bonjour Christian,
NB.SI attend une plage, et non pas un tableau de valeurs.
D'ailleurs, ceci ne fonctionne pas non plus.
=nb.si({1;1;"x";1;"x"};"x")
Plus de détails ici :
https://www.reddit.com/r/excel/comments/xjdec4/choosecols_countifs_cannot_seem_to_make_them_work/
Excellente question, en tous les cas, je ne savais/souvenais pas (j'ai un vague souvenir de l'avoir vu passer).
Grand merci pour ta réponse, et le lien.
Pour moi, il n'est pas intuitif que choisircols() ne retourne pas une colonne donc un range ;o)
Paradoxalement celà doit marcher avec decaler() (mais pas avec unique(), exclure() ni prendre()
Où peut on trouver des définitions suffisamment précises des fonctions pour les imbriquer, notamment avec LET()
Je n'ai par exemple pas su enchaîner un pivoter.par d'un grouper.par. Au début je croyais que ça venait de fonctions volatiles :o(
Bis,
J'ai vu des infos intéressantes sur https://exceljet.net/articles/excels-racon-functions
Néanmoins, après que Microsoft ait quasi généralisé le matriciel dynamique, je m'attendrais à voir évoluer/disparaitre ces restrictions.
Je suis peut être trop gourmand mais le matriciel, les nouvelles fonctions (grouper.par et pivoter.par notamment) conjuguées avec LET(), m'ouvrent l'appétit :o)
Y compris remplacer certains TCD, et alimenter directement des graphiques (cascade, pas autorisé en GCD par exemple)...