Microsoft 365 formule qui ne devrait pas me renvoyer une plage

[klin89]

Bonsoir le forum, 🙂

En C21, j'ai une formule que je recopie en bas jusqu'à C24 et à droite jusqu'à L21

=SI(C$20>0;INDEX(C$1:C$17;MIN(SI(NB.SI(C$9😀$11;C$12:C$17)=0;SI(NB.SI(C$20:C20;C$12:C$17)=0;LIGNE(C$12:C$17)))))&"";"")

Le problème réside en C24 où la cellule me renvoie la plage C1:C17 alors qu'elle ne devrait rien renvoyer.

Pour bien comprendre, C21 doit me renvoyer un nombre qui ne figure pas dans la plage C9😀12, idem pour C22, C23 et C24 sauf qu'en arrivant sur C24, j'ai complété ma série de 9 nombres, C24 ne devant rien renvoyer
Je cherche donc à compléter cette formule pour corriger le tir.
En E24, je n'ai pas le problème par contre si je recopie en E25 le problème est identique, la plage E1:E17 est alors recopiée.
Pour info, la ligne 20 représente les jours fériés.
merci aux formulistes pour leur aide.
A demain, je vais me coucher 🥱

klin89

 

[mapomme]

Bonsoir @klin89 🙂,

Ce n'est pas si étrange.
En E24, l'évaluation dans votre formule donne un l'INDEX :
=SI(C$20>0;INDEX(C$1:C$17;0)&"";"")

or INDEX(C$1:C$17;0) renvoie C$1:C$17 à cause de la présence du zéro comme référence de ligne.
En fait INDEX(plage ; 0 ; N) renvoie toute la Nième colonne de la plage.
Si plage n'a qu'une seule colonne, on aurait INDEX(plage;0;1). Excel 365 autorise à omettre le 1 soit INDEX(plage;0)

Dans votre formule Il va falloir traiter le cas où on aboutit à ce zéro dans l'index.
Je ne me suis pas penché sur ce que fait votre formule juste sur le pourquoi du résultat.

nota : je m'y suis penché malgré ce que j'ai dit => voir le message suivant.

 

[mapomme]

Re,

Vous pouvez aussi utiliser sous O365 la formule suivante en C21 :
=FILTRE(C12:C17;ESTNA(EQUIV(C12:C17;DANSCOL(C9:D11);0)))

Puis copier cette formule pour la coller en E21, G21, I21 et K21.

 

[klin89]

Waouh, super mapomme 🙂

J'ai placé la formule en C1 et propagé à droite

=SI(C$20>0;FILTRE(C14:C17;ESTNA(EQUIV(C14:C17;DANSCOL(C9😀13);0)));"")

Par contre, en changeant la dimension des plages traitées comme ci-dessous, cela me renvoie #CALC!, quand il n'y a pas de solution. Est-il possible de corriger la formule afin d'éviter l'erreur et ne rien renvoyer.

=SI(C$20>0;FILTRE(C17:C17;ESTNA(EQUIV(C17:C17;DANSCOL(C9😀16);0)));"")

Sinon est-ce possible d'avoir l'équivalent pour les versions d'Excel antérieures à O365
klin89

 

[mapomme]

Sinon est-ce possible d'avoir l'équivalent pour les versions d'Excel antérieures à O365

Bonjour @klin89 😉, @Cousinhub 😉,

Une piste pour des versions antérieures à O365.

On utilise une formule matricielle en C21 qu'on recopiera vers le bas (sur au moins autant de lignes que le nombre de lignes de C12 à C14) jusqu'à obtenir des lignes "vides" (voir vidéo *.gif jointe).

Formule matricielle en C21 :
=SIERREUR(INDEX(C:C;PETITE.VALEUR(SI(NB.SI(C$9:D$11;C$12:C$17)=0;LIGNE(C$12:C$17);"");LIGNE(C1)));"")

///

 

[mapomme]

Re,

Par contre, en changeant la dimension des plages traitées comme ci-dessous, cela me renvoie #CALC!, quand il n'y a pas de solution. Est-il possible de corriger la formule afin d'éviter l'erreur et ne rien renvoyer.

Et en insérant la formule dans SIERREUR(... ; "" ) ?
=SIERREUR(FILTRE(C12:C17;ESTNA(EQUIV(C12:C17;DANSCOL(C9:D11);0)));"")

 

[klin89]

Génial mapomme, comme d'habitude
Au plaisir de te croiser 😉

klin89