NB.SI.ENS avec conditions dans une plage de cellules

[Aurore49]

Bonjour,

Je me creuse la tête depuis hier pour trouver une solution dynamique à mon tableau.

je recherche en fait à trouver un nombre selon plusieurs critères dans ma base de données.
jusque là tout va bien, j'ai le merveilleux nb.si.ens de la version 2007 pour m'aider.

Sauf que j'aimerai qu'un des critères conditionnels se rapporte à une plage de cellules.
Actuellement, j'écris à la main la liste des critères dans chaque formule mais pour certains tableaux, je peux en avoir beaucoup et pour chaque produit différent
c'est donc source d'erreur.

ci-joint le fichier exemple

Objectif en cellule C8 : compter le nombre de possibilités pour le commercial A de placer le produit d'après le référencement par région. Le référencement par région est détaillé des lignes 13 à 15.

Actuellement, ma formule en C6 est : =SOMME(NB.SI.ENS(B2:B6;B8;A2:A6;{"région 1";"Région 2"}))
J'aimerai ne pas avoir à écrire manuellement les régions où chaque produit est référencé car j'ai 50 produits différents par tableau et jusqu'à 12 régions différentes ...

si quelqu'un a une idée.

NB : j'y connais pas grand chose en vba ... (quelques notions mais sans plus) mais je suis ouverte à toutes solutions !

 

[Guest]

Re : NB.SI.ENS avec conditions dans une plage de cellules

Bonjour,

Un TCD ferait-il l'affaire? Voir fichier joint

A+

 

[Aurore49]

Re : NB.SI.ENS avec conditions dans une plage de cellules

Bonjour,

Merci de votre réponse.

un TCD n'est pas suffisant.
1) parce que je ne peux pas sortir de ma trame initiale
2) parce que le produit C peut très bien n'être référencé que sur la région 3 par exemple
et dans votre proposition, le TCD filtre les régions 1 et 2 et si on ajoute une autre région, il comptabilisera 1 dans le produit A alors que la région 3 ne l'a pas référencé (pas de croix)

NB : j'ai modifié mon fichier joint pour être un peu plus complet dans la trame

Mon fichier d'origine continent environ 50 colonnes produits, 16 commerciaux, 12 régions et jusqu'à 500 magasins.
en fait, la ligne que je souhaite calculer automatiquement est le nombre de magasins où le produit est ouvert, sous condition que la région à laquelle appartient le magasin a référencé le produit ("x")

 

[Aurore49]

Re : NB.SI.ENS avec conditions dans une plage de cellules

Bon je fais le monologue mais j'ai pour l'instant trouvé une solution provisoire qui va se transformer en formule longue comme mon bras quand je vais avoir 15 régions à traiter :

=SI($D13="x";NB.SI.ENS($A$2:$A$6;$C$13;$C$2:$C$6;$A$8);0)+SI($D14="x";NB.SI.ENS($A$2:$A$6;$C$14;$C$2:$C$6;$A$8);0)+SI($D15="X";NB.SI.ENS($A$2:$A$6;$C$15;$C$2:$C$6;A8);0)

dans la cellule D8

mais c'est vraiment lourd à traiter sur un nombre de régions importante ...

une solution plus simple et moins longue ?

 

[JHA]

Re : NB.SI.ENS avec conditions dans une plage de cellules

Bonjour à tous,

Un essai avec des colonnes "E" & "G" sup

JHA

 

[Cavemanrock]

Re : NB.SI.ENS avec conditions dans une plage de cellules

Bonjour a tous,

une question a Aurore:
j'ai peut être pas saisi toutes les subtilités du fichier, mais d'après ce que tu mets comme données exemple, un simple nb.si sans tenir compte du critère "région qui contient x" devrait faire l'affaire puisque Produit A region 3 n'a pas de x dans les lignes 13 a 15 et n'a pas de valeur dans ta plage de données $D$1:$D$6...

Est ce qu'il peut y avoir un cas où tu ais une valeur en $D$1:$D$6 en face de région 3 par exemple, mais que le x ne soit pas renseigné dans les lignes 13 a 15?

A te relire

 

[Aurore49]

Re : NB.SI.ENS avec conditions dans une plage de cellules

Pour répondre à JHA

c'est intéressant et je ne maitrise pas encore parfaitement ces formules là (equiv/...).
par contre, cela m'impose de doubler mon nombre de colonnes.
Et dans mon fichier (dont je vous épargne l'envoi car il est lourd), mon plus grand tableau va de la colonne G à EG ...

ne peut-on pas trouver une formule qui remplacerait dans la formule la partie écrite entre les {} avec justement les formules type index/ equiv ou autre ?

Pour répondre à Cavemanrock,
oui c'est le cas. Le fichier joint est un exemple. je n'ai pas envoyé mon fichier d'origine qui est bien plus gros et complexe.
je gère jusqu'à 15 régions différentes, 15 commerciaux différents, jusqu'à 600 magasins et une 50aine de colonnes produits.
le tout sur 6 onglets d'enseignes différentes.

la cellule que je veux calculer doit donner le nombre de magasins pour le commercial A où le produit A est disponible à la vente.
En gros, dans combien de magasins le produit A peut-il etre vendu par le commercial A ?

NB

j'ai fait une demande de formation excel mais on m'a regardée les yeux équarquillés, car je manipule le logiciel de façon déjà bien plus approfondie que n'importe qui dans les bureaux (la plupart ne connaissent déjà même pas les TCD ou les formules qui sortent de somme ou nb. quand à mon support informatique, ils ne connaissent quasi rien sur les logiciels ...),

mais je suis persuadée que j'aurais des améliorations à trouver (peut être dans vba?) pour améliorer ce fichu document qui traite une masse d'info (mon fichier traité sur excel remplace une CRM que l'entreprise ne veut pas acheter ...)

 

[JBOBO]

Re : NB.SI.ENS avec conditions dans une plage de cellules

Bonjour,

Sur la base du fichier de JHA : En integrant la formule en colonne E dans la formule en D8, on obtient la formule suivante, à valider en matricielle par ctrl+maj+entree :

=SOMMEPROD((INDEX($D$13:$D$15;EQUIV($A$2:$A$6;$C$13:$C$15;0))="x")*(Produit_A<>"")*($C$2:$C$6=$A$8))

A tester , mais ça à l'air de fonctionner dans l'exemple.

 

[Aurore49]

Re : NB.SI.ENS avec conditions dans une plage de cellules

ça ne fonctionne que dans ce cas précis et pour la valeur recherchée dans D9 et non D8
si tu ajoutes un 1 dans la cellule D5, il le comptabilise alors qu'il ne devrait pas.

pour la cellule D8, j'ai retiré la partie "*(Produit_A<>"")" pour avoir le nombre de lignes où le produit est référencé (indépendamment s'il est détenu (1),
mais pareil, il me compte 3 au lieu de 2 attendu car il n'exclut pas la ligne "commercial A/Région 3" alors qu'il n'y a pas de croix dans "région 3" ...

c'est peut-être un cas désespéré et je vais peut-être devoir faire des formules de 4 lignes (heureusement que j'ai un grand écran)...
mais je me dis qu'impossible n'est pas excel ...

 

[Cavemanrock]

Re : NB.SI.ENS avec conditions dans une plage de cellules

Re,

mais je me dis qu'impossible n'est pas excel ...

effectivement, entre les macros et les formules, il y a de quoi faire pour solutionner ton problème... mais :

un TCD n'est pas suffisant.
1) parce que je ne peux pas sortir de ma trame initiale

... ça limite un peu! Ce serait peut être plus simple en ayant "carte blanche" pour restructurer les données etc...

 

[JHA]

Re : NB.SI.ENS avec conditions dans une plage de cellules

Re,

A tester,

JHA

 

[Aurore49]

Re : NB.SI.ENS avec conditions dans une plage de cellules

surement ...
mais malheureusement, pour 1 petite cellule que je n'arrive pas à automatiser complètement (perfectionnisme ? ), je ne vais pas refaire tout mon travail (qui m'a pris quand même quelques jours de développement avec mes petites mimines de "novice" et qui fonctionne plutôt bien malgré cette saisie semi-manuelle...

je pars de loin, à l'époque où ça a été créé, il n'y avait aucune formule et tout était fait à la main sur excel (copier/coller et companie) parce que mis en place par qq un dans l'idée mais pas la forme ! heureusement que j'ai toujours refusé de travailler dessus ainsi, j'aurais été la reine des erreurs et des mauvaises informations sinon !!

 

[filiph79]

Re : NB.SI.ENS avec conditions dans une plage de cellules

Bonjour,

et en ajoutant une colonne intermédiaire permettant d'afficher la croix par une recherche par exemple et en masquant cette colonne........

cf fichier

cordialement

 

[Aurore49]

Re : NB.SI.ENS avec conditions dans une plage de cellules

punaise, je l'avais fait et ça ne marchait pas sur mon gros fichier ...
j'avais du rajouter ou retirer autre chose car là, ça marche nickel !!!

peut-être que je ne l'ai assayé qu'avec somme et non sommeprod ...


merci merci merci
impossible n'est pas excel

= réponse à la soultion de JHA

 

[JHA]

Re : NB.SI.ENS avec conditions dans une plage de cellules

Re,

Pour étayer mes dires de la solution 2

JHA