Bon­jour à tous,

Très inspiré par ces deux vidéos (Owen Price et Nico­las Brab­hant), je vous pro­pose ici une com­bi­nai­son de ces tech­niques qui expliquent com­ment utilis­er une fonc­tion Lamb­da (définie comme nom) dans une fonc­tion BYROW.

La for­mule finale est extrême­ment simple.

Suite à une dis­cus­sion très intéres­sante sur des cal­culs d’in­ter­valles de dates, Brad Yundt a pro­posé une solu­tion à base de MEDIANE.

Si on regarde, l’aide, on trou­ve la déf­i­ni­tion suivante : 

Ren­voie la valeur médi­ane des nom­bres. La médi­ane est la valeur qui se trou­ve au cen­tre d’un ensem­ble de nombres.

Ain­si, en l’u­til­isant avec 3 valeurs, on réduit une for­mule de type : 

Note : on peut sim­pli­fi­er un peu la for­mule en util­isant des ET et des OU, mais on voit bien que c’est un peu complexe.

D’où l’u­til­i­sa­tion de la fonc­tion MEDIANE : 

=MEDIANE(A1;A2;A3)

Une par­tic­u­lar­ité à not­er, si la médi­ane n’est pas disponible (nom­bre pair de valeurs), Excel con­sid­ère la moyenne entre les deux valeurs médi­ane. Ici, la médi­ane est 6 = (8+4)/2

Petite fonc­tion du jour : com­ment sépar­er en ligne et en colonnes.
Sur Excel 365 Insid­er, ca se fait en une seule for­mule (fractionner.texte), mais en atten­dant, voici ce qui devrait faire l’af­faire pour 365 (et 2021, je pense) :
Atten­tion, il faut exacte­ment le bon nom­bre de sépara­teur dans les don­nées ini­tiales (voir copie d’écran)

=LET(
plage;A1:A3;
DelimiteurL;".";
DelimiteurC;",";
resultat1;FILTRE.XML("<b><a>"&SUBSTITUE(JOINDRE.TEXTE(DelimiteurL;;plage);DelimiteurL;"</a><a>")&"</a></b>";"//a");
INDEX(TRANSPOSE(
FILTRE.XML("<b><a>"&SUBSTITUE(JOINDRE.TEXTE(DelimiteurC;;resultat1);DelimiteurC;"</a><a>")&"</a></b>";"//a")
);
SEQUENCE(LIGNES(resultat1);
NBCAR(INDEX(resultat1;1;1))-NBCAR(
SUBSTITUE(INDEX(resultat1;1;1);DelimiteurC;)
)+1)))

et avec une Lamb­da (pour plus d’in­for­ma­tions sur la créa­tion de Lamb­da, c’est par ici), par exem­ple L.FRACTIONNER.TEXTE :

=LAMBDA(plage;DelimiteurC;DelimiteurL; LET(resultat1;FILTRE.XML("<b><a>"&SUBSTITUE(JOINDRE.TEXTE(DelimiteurL;;plage);DelimiteurL;"</a><a>")&"</a></b>";"//a"); INDEX(TRANSPOSE( FILTRE.XML("<b><a>"&SUBSTITUE(JOINDRE.TEXTE(DelimiteurC;;resultat1);DelimiteurC;"</a><a>")&"</a></b>";"//a") ); SEQUENCE(LIGNES(resultat1); NBCAR(INDEX(resultat1;1;1))-NBCAR( SUBSTITUE(INDEX(resultat1;1;1);DelimiteurC;) )+1))))