Formule matricielle "propre" sans #N/A

[JNP]

Bonjour à tous ,
Ayant été très intéressé par les possibilités matricielles d'Excel (Tibo nous fait de tellement belles formules ), j'ai utilisé et/ou développé des fonctions VBA matricielles.
Mais je n'arrive pas à faire renvoyer aucune valeur lors du dépassement de la dernière valeur du tableau ...
Évidemment, je peux tricher en "gonflant" le tableau avec des valeurs nulles jusqu'à la 20ème ou 30ème valeur, mais c'est pas très pro ...
Je peux aussi faire une colonne intermédiaire et traiter avec ESTNA la première colonne .
Mais je n'arrive pas à intégrer la vérification de la valeur dans la formule de la matricielle .
En pièce jointe, un petit exemple avec la fonction jours fériés de Frédéric Sigorneau, et une matricielle développée pour un autre fil sur le forum.
le résultat souhaité est en jaune, mais sans calcul intermédiaire.
Merci à tous de vos lumières

 

[Guest]

Re : Formule matricielle "propre" sans #N/A

Bonsoir JNP,

[QUOTE
Évidemment, je peux tricher en "gonflant" le tableau avec des valeurs nulles jusqu'à la 20ème ou 30ème valeur, mais c'est pas très pro ...
[/QUOTE]

Pourtant, il n' y a, à ma connaissance aucun moyen de changer ça. C'est excel, à un niveau supérieur que VBA qui gère cela.

Soit tu laisses excel gérer les dimensions manquantes de tableau de résultats et tu aura des #NA soit tu gère au niveau VBA en "gonflant" ton tableau de retour.
A+

 

[Habitude]

Re : Formule matricielle "propre" sans #N/A

J'ai modifié ton code dans la fonction jourFérié avec commentaires

 

[Guest]

Re : Formule matricielle "propre" sans #N/A

Bosoir Habitude,

Tu "gonfles" le tableau de retour!

A+

 

[Habitude]

Re : Formule matricielle "propre" sans #N/A

Bosoir Habitude,

Tu "gonfles" le tableau de retour!

A+

Il est de la même dimension que le tableau matriciel excel.
À mon avis c'est la dimension qu'il doit avoir

 

[Guest]

Re : Formule matricielle "propre" sans #N/A

Re bonsoir Habitude,

Il est de la même dimension que le tableau matriciel excel.
À mon avis c'est la dimension qu'il doit avoir

Nous sommes d'accord, il ne peut en être autrement.

Il me semblait pourtant que c'était ce que voulait éviter JNP, en disant:

Évidemment, je peux tricher en "gonflant" le tableau avec des valeurs nulles

A+

 

[JNP]

Re : Formule matricielle "propre" sans #N/A

Bonjour le fil ,
Merci à tous les 2.
Effectivement, j'aurais souhaité ne pas gonfler le tableau, et que ce soit dans la formule que je détecte si le tableau était dépassé .
Mais quand je parlais de gonfler le tableau, je pensais à une gonflette au hasard (type 20 ou 30 valeurs), avec le risque d'être trop court .
Je ne connaissais pas cette fonction

ReDim Tableau(Application.Caller.Columns.Count)

qui m'a permit de corriger l'autre fonction en colonne pour vérifier que j'avais bien compris.
C'est ce que j'appellerais du "gonflage intelligent", vu qu'il limite à la taille nécessaire celle du tableau. C'est juste dommage que si la formule matricielle est étalée sur 100 lignes, elle renverra un tableau de 100 lignes même si il n'y a que 3 valeurs dedans .
En tout cas, c'est une belle avancée, et si par hasard, quelqu'un connait la solution via formule, type

=SI(ESTNA({JoursFériés(2010)});"";{JoursFériés(2010)}

mais je ne sais pas comment positionner les {} à la demande .
Bonne journée

 

[hoerwind]

Re : Formule matricielle "propre" sans #N/A

Bonjour JNP, salut les autres,

A la lecture de ton fichier je me pose une question : pourquoi faire appel à des macros créant des fonctions personnalisées et des formules matricielles, ce qui augmente inutilement le poids du fichier, alors que le même résultat peut aisément être obtenu par de simples formules, même si certaines d'entre-elles renvoient "" ?

Ce qui pourrait éventuellement être fait, c'est copier ces formules par macro dans le nombre de cellules nécessaires, mais je n'en vois vraiment pas l'avantage (poids).

 

[JNP]

Re : Formule matricielle "propre" sans #N/A

Bonjour Hoerwind ,

A la lecture de ton fichier je me pose une question : pourquoi faire appel à des macros créant des fonctions personnalisées et des formules matricielles, ce qui augmente inutilement le poids du fichier, alors que le même résultat peut aisément être obtenu par de simples formules, même si certaines d'entre-elles renvoient "" ?

Oui, effectivement, les résultats peuvent être obtenus par formule, et de plus, le calcul peut être plus rapide .
Il y a quand même quelques avantages aux fonctions VBA :
Lisibilité des formules.
Simplification des formules.
Utilisation simple depuis une XLA.
Etc.
Il faut voir aussi côté utilisateur lambda. Entre lui glisser une petite fonction VBA et un gros paquet de formule, il a l'impression de comprendre ...
Du fait, j'avoue que même si j'essaie de plus en plus de passer par des formules, je ne suis pas prêt de lâcher le côté VBA .
L'essentiel du problème que je me pose dans ce fil, c'est comment indiquer en formule que telle partie est matricielle et pas l'autre ...
Bonne journée

 

[Tibo]

Re : Formule matricielle "propre" sans #N/A

Salut Jean-Noël,

Peut-être une piste pour ta question ... ?

Une formule matricielle peut être nommée.

L'avantage, c'est que dans ce cas, il n'est pas besoin de la valider par la séquence CTRL + MAJ + ENTREE lors de l'utilisation du nom correspondant.

Bon app

@+

 

[hoerwind]

Re : Formule matricielle "propre" sans #N/A

Re,

Ce que je voulais dire c'est qu'il ne faut pas nécessairement faire appel à une formule matricielle pour obtenir le même résultat, une formule nommée répondant également à ton objection :
Entre lui glisser une petite fonction VBA et un gros paquet de formule, il a l'impression de comprendre
Peut-être même encore mieux, vois la pièce jointe sous D2:I2

 

[JNP]

Re : Formule matricielle "propre" sans #N/A

Bonjour Tibo ,

Une formule matricielle peut être nommée.
L'avantage, c'est que dans ce cas, il n'est pas besoin de la valider par la séquence CTRL + MAJ + ENTREE lors de l'utilisation du nom correspondant.

Peux-tu être plus explicatif sur la création d'un nom en matriciel.
J'ai essayé de mettre les {} à la main, ça me renvoie une chaine de texte.
J'ai essayé de valider le nom avec Ctrl+Maj+Enter, elle ne me renvoie que la première valeur du tableau dans toutes les cellules.
Il y a certainement un "truc" ...
Bon après-midi

 

[hoerwind]

Re : Formule matricielle "propre" sans #N/A

Re,

La truc c'est justement de ne rien faire, en dehors de coller simplement la formule, sans les {}, dans la petite fenêtre ad hoc en la nommant sous le menu Insertion - Noms- Définir.

Il ne faut pas non plus valider en matricielle le nom attribué dans la cellule.
Pour coller rapidement le nom dans une cellule, sans risque de fautes d'orthographe, la touche clavier F3, faire son choix et valider (par la touche clavier Entrée).

Ne demande pas pourquoi, c'est ainsi qu'en ont décidé les programmeurs chez MS.

 

[JNP]

Re : Formule matricielle "propre" sans #N/A

Re ,

La truc c'est justement de ne rien faire, en dehors de coller simplement la formule, sans les {}, dans la petite fenêtre ad hoc en la nommant sous le menu Insertion - Noms- Définir.
Il ne faut pas non plus valider en matricielle le nom attribué dans la cellule.
Pour coller rapidement le nom dans une cellule, sans risque de fautes d'orthographe, la touche clavier F3, faire son choix et valider (par la touche clavier Entrée).

Mais dans ce cas là, après glisser vers le bas (ou sur le côté), chaque cellule ne me renvoie que la première valeur du tableau ...
J'ai bien l'impression que je vais rester sur la solution d'Habitude, qui semble être le mieux qu'on puisse faire .
Merci à tous

 

[hoerwind]

Re : Formule matricielle "propre" sans #N/A

Re,

Pour ma propre information, pourrais-tu me dire ce que tu reproches à la procédure proposée sous le message #11 ?