Discussion :

[marot_r]

Bonjour à tous et toutes et merci de votre temps et de votre aide.

J'ai la formule suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
=NB.SI.ENS('Suivi Dossiers'!$E:$E; ">1";   'Suivi Dossiers'!$F:$F; CurrentYear;   'Suivi Dossiers'!$H:$H; 'Listes Réf.'!$D$5)
  + NB.SI.ENS('Suivi Dossiers'!$E:$E; ">1";   'Suivi Dossiers'!$F:$F; CurrentYear;   'Suivi Dossiers'!$H:$H; 'Listes Réf.'!$D$6)
  + NB.SI.ENS('Suivi Dossiers'!$E:$E; ">1";   'Suivi Dossiers'!$F:$F; CurrentYear;   'Suivi Dossiers'!$H:$H; 'Listes Réf.'!$D$7)
  + NB.SI.ENS('Suivi Dossiers'!$E:$E; ">1";   'Suivi Dossiers'!$F:$F; CurrentYear;   'Suivi Dossiers'!$H:$H; 'Listes Réf.'!$D$16)
  + NB.SI.ENS('Suivi Dossiers'!$E:$E; ">1";   'Suivi Dossiers'!$F:$F; CurrentYear;   'Suivi Dossiers'!$H:$H; 'Listes Réf.'!$D$17)
  + NB.SI.ENS('Suivi Dossiers'!$E:$E; ">1";   'Suivi Dossiers'!$F:$F; CurrentYear;   'Suivi Dossiers'!$H:$H; 'Listes Réf.'!$D$18)
  + NB.SI.ENS('Suivi Dossiers'!$E:$E; ">1";   'Suivi Dossiers'!$F:$F; CurrentYear;   'Suivi Dossiers'!$H:$H; 'Listes Réf.'!$D$30)

Comme on peut le voir à part le dernier terme, 'Listes Réf.'!$D$5 tous les autres paramètres sont identiques.
Je ne peux pas modifier ma source 'Suivi Dossiers' pou rajouter un identifant "de groupe" qui permettrai de ne tester qu'un seule valeur.

Les valeurs contenues dans la colonne D de la feuille List Réf est une listes de textes variés (pas triés, pas compris entre des bornes).
Je peux peut-être modifier Liste Réf. pour ajouter une notion de groupe mais j'aimerai mieux pas.

Donc, est-il possible d'avoir un truc du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=NB.SI.ENS('Suivi Dossiers'!$E:$E; ">1";   'Suivi Dossiers'!$F:$F; CurrentYear;   'Suivi Dossiers'!$H:$H; DANS('Listes Réf.'!$D$5; 'Listes Réf.'!$D$6; ...; 'Listes Réf.'!$D$30))

A+

[halaster08]

Bonjour,

Je travaille actuellement sur un cas similaire et je n'ai malheureusement pas de réponse à ta question.
J'ai cependant contourné/déplacé le problème en ajoutant une colonne à ma source de données ('suivi dossiers' pour toi) avec une formule qui renvoie vrai dans les cas qui m'intéresse
Puis je fais mon nb.si.ens en utilisant cette colonne:

NB.SI.ENS('Suivi Dossiers'!$E:$E; ">1"; 'Suivi Dossiers'!$F:$F; CurrentYear; 'Suivi Dossiers'!"colonne_sup";Vrai)

Ce qui me permet si la liste change de n'avoir qu'une formule à modifier au lieu d'avoir à ajouter/supprimer des nb.si.ens
C'est pas l'idéal mais je n'ai pas trouver mieux.
D'ailleurs si toi tu réussi à faire mieux, ça m'intéresse

[papouclo]

Bonjour,
On peut essayer de remplacer la fonction NB.SI.ENS par un SOMMEPROD avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
=  SOMMEPROD(1 * ('Suivi Dossiers'!$E:$E>1)*('Suivi Dossiers'!$F:$F=CurrentYear) 
               * (ESTNUM(EQUIV('Suivi Dossiers'!$H:$H; 'Listes Réf.'!$D$5:$D$7;0)) 
                   +  ESTNUM(EQUIV('Suivi Dossiers'!$H:$H; 'Listes Réf.'!$D$16:$D$18;0)) 
                   +  ESTNUM(EQUIV('Suivi Dossiers'!$H:$H; 'Listes Réf.'!$D$30;0))))

On passe d'une formule d'environ 900 caractères à une formule qui s'écrit avec moins de 300 caractères.
Mais le problème reste la rapidité avec une formule (qui reste matricielle avec le SOMMEPROD) travaillant sur 3 colonnes entières de la feuille 'Suivi Dossiers'.
Cordialement
Claude

[marot_r]

Bonjour.

Merci papouclo de cette idée.

Un autre problème avec SOMMEPROD est que cela fait une formule difficile à comprendre pour moi ou mes collègues.

J'ai un peu réorganisé l'affichage pour essayer d'y voir plus claire et cela reste encore mystérieux.

Et à quoi sert le 1 * au début ? À priori multiplier par 1 ne change rien au résultat, non ?

A+

[papouclo]

Bonjour,
On peut ne pas écrire le produit par 1. C'est une vieille habitude qui n'est plus nécessaire.
Les facteurs du produit :
('Suivi Dossiers'!$E:$E>1)
('Suivi Dossiers'!$F:$F=CurrentYear)
(ESTNUM(EQUIV('Suivi Dossiers'!$H:$H; 'Listes Réf.'!$D$5:$D$7;0)) + ESTNUM(EQUIV('Suivi Dossiers'!$H:$H; 'Listes Réf.'!$D$16:$D$18;0)) + ESTNUM(EQUIV('Suivi Dossiers'!$H:$H; 'Listes
Réf.'!$D$30;0))))
renvoient des valeurs logiques VRAI ,FAUX, remplacées par Excel par des valeurs numériques 1 , 0 dans des calculs.
Mais dans d'anciennes versions, le calcul faisant intervenir ces valeurs logiques devait contenir au moins un facteur numérique d'où le produit par 1.
Cordialement
Claude

[Patrice740]

Bonjour,

Et à quoi sert le 1 * au début ? À priori multiplier par 1 ne change rien au résultat, non ?

Le * 1 sert à convertir un résultat booléen (vrai ou faux) en résultat numérique (1 ou 0).
Il est aussi possible d'utiliser + 0 ou encore --
Par exemple :
=SOMMEPROD($A$1:$A$20=4)
Ne donne pas le même résultat que les 3 formules ci-dessous
=SOMMEPROD(($A$1:$A$20=4)*1)
=SOMMEPROD(($A$1:$A$20=4)+0)
=SOMMEPROD(--($A$1:$A$20=4))
Contrairement à ce qu'affirme papouclo il le faut toujours car dans la première formule le résultat est toujours 0 alors que les autres comptent le nombre de 4 dans la plage.

PS :
Dans les 3 cas il s'agit d'un subterfuge pour provoquer la conversion des booléens en nombres.
L'écriture la plus logique est d'utiliser la fonction de conversion en nombre N().
=SOMMEPROD(N($A$1:$A$20=4))
(et on économise un caractère)

[marot_r]

Bonjour, désolé du retard mais on a eu quelques urgences à régler.

Merci de ces réponses je prends note du SOMMEPROD ainsi que les astuces de conversion et je vais étudier cela plus profondément dés que possible.

A+