sommeprod, nb.si et nom de liste en variable

[Antoine B]

Bonjour,

Je rencontre la difficulté suivante sous excel : j'ai plusieurs listes (qui contiennent des formules) et suivant la valeur d'une cellule, je souhaite qu'une cellule soit calculée en utilisant une liste plutot qu'une autre.

voici l'exemple :
la formule ci dessous fonctionne et me renvoie la valeur souhaitée :
=SOMMEPROD((NB.SI(listeA;'pointages BO sept'!$A$2:$A$50000))*('pointages BO sept'!$D$2:$D$50000=$A294)*('pointages BO sept'!$G$2:$G$50000<=RECHERCHEV('1 - pointages'!$A$4;calcul!$A$3:$E$14;5;FAUX))*('pointages BO sept'!$F$2:$F$50000))

en revanche, lorsque - dans la même formule - je remplace "listeA" (qui est une liste) par la cellule C$285 dont le contenu est "listeA", excel me renvoie la valeur 0 :
=SOMMEPROD((NB.SI(C$285;'pointages BO sept'!$A$2:$A$50000))*('pointages BO sept'!$D$2:$D$50000=$A294)*('pointages BO sept'!$G$2:$G$50000<=RECHERCHEV('1 - pointages'!$A$4;calcul!$A$3:$E$14;5;FAUX))*('pointages BO sept'!$F$2:$F$50000))

sachant que :
liste A = DECALER(calcul!$H$2;;;NB.SI(calcul!$H$2:$H$14;"><"&""))

Si l'un de vous a une solution (idéalement en excel et pas en vba que je ne connais pas du tout) ?

encore merci d'avance,
Antoine

 

[R@chid]

Re : sommeprod, nb.si et nom de liste en variable

Bonjour et Bienvenue sur XLD,
Remplace ListeA par INDIRECT(C$285)

Sinon, un fichier exemple sera plus parlant.

@ + +

Edit : Salut l'ami

 

[Modeste]

Re : sommeprod, nb.si et nom de liste en variable

Bonjour Antoine et bienvenue parmi nous

Qu'appelles-tu des listes?
Peut-être que la fonction INDIRECT te procurera beaucoup de bonheur ... mais si tu as besoin d'indications plus précises, un petit extrait de ton fichier (sans données confidentielles) serait le bienvenu, lui aussi!

[Edit:] Salut R@chid

 

[Antoine B]

Re : sommeprod, nb.si et nom de liste en variable

Bonjour R@chid et Modeste, et merci pour vos retours,

j'avais essayé la fonction indirect() en remplaçant listeA par indirect (C$285) et ça me renvoyait #ref! mais comme je ne maitrise pas très bien cette fonction, je suis peut être passé à coté du truc.
j'ai commencé à dénaturer le fichier pour vous l'envoyer mais je me retrouve à reconstruire les formules uniquement pour la version dénaturée :0(
j'espère que les infos ci dessous peuvent préciser mon besoin, n'hésitez pas à me dire si ce n'est pas clair :
mes listes sont des ensembles de noms de villes présents en colonne 1 d'un tableau de valeur sur lesquels j'effectue l'opération de calcul. Exemple : ma liste A contient les noms de villes (Agen, Montpellier, Tours) et dans mon tableau, en colonne 4, j'ai le nombre d'heures travaillées dans chacune des villes, et je souhaite sommer ces heures pour les villes de la liste A (avec d'autres paramètres du type le mois où ces heures ont été réalisées, ...).
Je ne peux pas rentrer "listeA" en dur dans ma formule car je souhaite que l'utilisateur du fichier puisse choisir - dans une liste déroulante - la liste de villes sur lesquelles il souhaite obtenir le nombre d'heures travaillées, d'où l'idée de mettre le nom de la liste dans une cellule (C$285) pour pouvoir ensuite tirer ma formule sur la droite et incrémenter automatiquement.
j'ai 5 listes, dont le nom est toujours le même (listeA à E par exemple) mais dont la liste de villes varient pour chacune selon les choix de l'utilisateur (ex : listeA pourra contenir paris tours et lyon, mais aussi marseilles cassis selon le choix utilisateur).

encore merci pour votre coup de main
Antoine

 

[Modeste]

Re : sommeprod, nb.si et nom de liste en variable

Re,

Parmi les nombreuses choses qu'il m'arrive d'oublier, figure le fait qu'on ne peut pas utiliser la fonction INDIRECT pour faire référence à une zone nommée à l'aide de la fonction DECALER. Les deux sont incompatibles, dans ce cas-là (et seulement celui-là)!

Il va donc falloir trouver un système pour contourner cet écueil

 

[Antoine B]

Re : sommeprod, nb.si et nom de liste en variable

merci pour l'info Modeste, je vais donc abandonner la piste indirect,
j'avais une autre idée, celle de rentrer la formule de la liste (ex pour la liste 1 = DECALER(calcul!$H$2;;;NB.SI(calcul!$H$2:$H$14;"><" &"")) dans la formule globale. et auquel cas il ne me reste plus qu'à rendre variable la colonne (H) dans la formule décaler, car d'une liste à l'autre, la seule chose qui change dans la formule est la lettre de la colonne (H puis I, J, K et L) :
liste 1 = DECALER(calcul!$H$2;;;NB.SI(calcul!$H$2:$H$14;"><" &""))
liste 2 = DECALER(calcul!$i$2;;;NB.SI(calcul!$i$2:$i$14;"><" &""))
liste 3 = DECALER(calcul!$j$2;;;NB.SI(calcul!$j$2:$j$14;"><" &""))
liste 4 = DECALER(calcul!$k$2;;;NB.SI(calcul!$k$2:$k$14;"><" &""))
liste 5 = DECALER(calcul!$l$2;;;NB.SI(calcul!$l$2:$l$14;"><" &""))

j'ai essayé avec la fonction colonne, mais elle renvoie un numéro et ça ne marche pas, si tu as une idée, je suis plus que preneur !
Antoine

 

[JCGL]

Re : sommeprod, nb.si et nom de liste en variable

Bonjour à tous,
Salut les amis,

Et si tu joignais ton fichier anonymisé comme déjà demandé...

A++
A+ à tous

 

[Antoine B]

Re : sommeprod, nb.si et nom de liste en variable

bonjour JCGL,
j'ai anonymisé le fichier, il se trouve en pièce jointe,
merci à vous,
Antoine

 

[JCGL]

Re : sommeprod, nb.si et nom de liste en variable

Bonjour à tous,

Un essai avec ce que j'ai compris

A+ à tous

 

[Antoine B]

Re : sommeprod, nb.si et nom de liste en variable

merci beaucoup JCGL,
ça marche effectivement sur ton fichier, ça devrait aussi marcher sur le mien mais je ne sais pas comment tu as créé le tableau que tu as intitulé tablo1 : j'ai essayé de le créer (dans mon doc) dans formule -> gestionnaire de nom mais ça n'a pas l'air d'être le bon truc ...

 

[Antoine B]

Re : sommeprod, nb.si et nom de liste en variable

étant novice sous excel et au cas où ma dernière question relève de l'évidence, voici qq précisions sur ce que je n'arrive pas à créer et qui se trouve dans ton fichier qui fonctionne : c'est le tableau (ou la matrice) avec les intervalles []
encore merci !
Antoine

 

[JCGL]

Re : sommeprod, nb.si et nom de liste en variable

Bonjour à tous,

Voir sur le portail de notre amie Misange : Ce lien n'existe plus

Bises à Flo
A+ à tous

 

[Antoine B]

Re : sommeprod, nb.si et nom de liste en variable

merci JCGL pour le lien vers les tableaux, effectivement ça me permet de faire des formules plus simple !
pour la formule de calcul des heures, ça ne marche malheureusement pas car je dois conserver mes fonctions décaler dans les formules de mes listes 1 à 5, et apparemment ça ne fonctionne pas avec indirect

 

[JCGL]

Re : sommeprod, nb.si et nom de liste en variable

Bonjour à tous,

je dois conserver mes fonctions décaler dans les formules de mes listes 1 à 5, et apparemment ça ne fonctionne pas avec indirect

.

Je confirme que la fonction les listes "Décaler" ne font pas bon ménage avec INDIRECT().
Pourquoi dois-tu conserver les DECALER() ?

A+ à tous

 

[Antoine B]

Re : sommeprod, nb.si et nom de liste en variable

merci pour ton retour !
mon nombre (et le nom) des villes varie suivant les choix de l'utilisateur (pour chacune des listes), je peux en avoir entre 1 et 14, d'où mon utilisation de la fonction décaler.
l'autre idée que j'avais était de paramétrer la colonne dans ma formule décaler (dans l'exemple ci dessous la colonne H) car mes 5 listes ont la même formule hormi la colonne qui varie pour chacune d'elle; mais là encore je bloque
=DECALER(calcul!$H$2;;;NB.SI(calcul!$H$2:$H$14;"><"&""))