Eviter les erreurs de propagation (sur certaines formules)

Bonjour à tous,

Je vous partage une petite astuce que je ne connaissais pas, très pratique, trouvée ici : Why some Excel functions won't spill | Exceljet (Anglais). Voici ci-après le résumé de l'article.

Pour les personnes travaillant avec les nouvelles formules à propagation dynamique, vous avez peut-être pu remarquer que certaines fonctions ne supportent pas la propagation. Il y a en général 2 causes :

• Le cas ou la fonction à propager renvoie une plage/array, auquel cas on se retrouve dans le cas de l'erreur #CALC : tableau imbriqué (nested array). Dans ce cas on ne peut pas faire grand-chose si ce n'est recréer le tableau final avec MAKEARRAY.
• Mais parfois, même si la fonction à propager ne renvoie qu'une valeur unique, on a une erreur #VALEUR pas très claire… Dans ce cas, il existe une solution un peu saugrenue : ajouter un "+" devant la plage à évaluer.

Supposons que l'on dispose d'une liste de dates en A1:A10, par exemple via

=SEQUENCE(10;;AUJOURDHUI();30)

Si l'on écrit =FIN.MOIS(A1#;0) (ou =FIN.MOIS(A1:A10;0)), on obtient #VALEUR!. Excel n'arrive pas à propager FIN.MOIS sur chacune des dates. C'est un problème récurrent avec beaucoup de "vieilles" fonctions.

Il fallait alors obligatoirement passer par MAP ce qui alourdit vite les expressions

=MAP(A1#;LAMBDA(d;FIN.MOIS(d;0)))

La solution proposée dans l'article est assez simple, pour résoudre le problème il suffit d'écrire :

=FIN.MOIS(+A1#; 0) (ou =FIN.MOIS(+A1:A10; 0))

En fait le "+" force Excel à évaluer la plage (en tant que liste) avant d'appeler la fonction, et alors dans ce cas la propagation fonctionne (quasiment tout le temps, il y a quelques exceptions).

Notez que cela fonctionne également avec les anciennes fonctions matricielles (CSE: CTRL+SHIFT+ENTER) et dans un LET =LET(_mesDates;A1#;FIN.MOIS(+_mesDates;0)).

Voilà, en espérant que ce petit truc pourra vous aider si jamais il vous arrive de vous casser la tête devant un #VALEUR inexplicable.

Bonjour,
Un petit complément YouTube !
Cdlt.

lien : ExcelOffTheGrid