Rechercher occurrences dans une plage nommée avec critères d'une autre plage nommée

[Nicouse]

Bonsoir,

Je tourne en rond et ne parviens pas à résoudre mon problème. J'ai cherché en vain sur le forum et ailleurs également.
Voici le type de formule qui me pose souci:

=SOMMEPROD((SOUS_CENTRE_COMMANDE=SOUS_CENTRES_ITB)*(COMPTE_IMPUTATION<>""))

Cette formule me renvoie #N/A.

A savoir que la plage nommée SOUS_CENTRE_COMMANDE correspond à la plage dynamique de la colonne B dans la feuille 1 (source des données).
La plage nommée SOUS_CENTRES_ITB correspond à la plage dynamique de la colonne A dans la feuille 2 (feuille de paramètres).

Cette dernière plage contient plusieurs valeurs que je souhaite compter au sein de la plage dynamique SOUS_CENTRE_COMMANDE afin de déterminer le nombre de commandes imputées à ces groupes de travail du même département (ITB).

Je rencontre le même problème avec la plage nommée SOUS_CENTRE_EXPERTS qui elle aussi contient plusieurs valeurs, alors que la formule prenant en compte la plage nommée SOUS_CENTRE_ISSME qui ne contient qu'une seule valeur fonctionne (cellule B2 de la feuille 4).

En intégrant la fonction ESTNA à (SOUS_CENTRE_COMMANDE=SOUS_CENTRES_ITB), le résultat final n'est pas celui attendu (124 au lieu de 46, et pour cause l'évaluation de la formule accrédite ce résultat bien que je ne comprenne pas pourquoi ça ne fonctionne pas).

Comment dois-je formuler ceci ? Est-ce seulement possible ?

J'espère avoir été assez clair.

Merci d'avance pour votre expertise qui m'a maintes fois dépanné.

Nicouse

 

[R@chid]

Re : Rechercher occurrences dans une plage nommée avec critères d'une autre plage nom

Salut et bienvenu,
j'ai nommer des plages.
Voir PJ.

 

[Nicouse]

Re : Rechercher occurrences dans une plage nommée avec critères d'une autre plage nom

Merci pour ta réponse.
Cela répond au besoin (ie on a le résultat attendu) mais je souhaite conserver mes plages nommées dynamiques.
Or la solution que tu proposes passe par des plages fixes.

 

[Misange]

Re : Rechercher occurrences dans une plage nommée avec critères d'une autre plage nom

Bonjour

Il te suffit de remplacer ITB par souscentre _ITB dans la formule donnée par Rachid...
Au fait, pas besoin de valider les formules avec sommeprod par ctrl+maj+entrée. Ce sont bien des matricielles mais elles ont cet avantage d'une validation "normale".
Tu n'a pas besoin de tester la condition COMPTE_IMPUTATION<>"" vu la façon dont tu saisis tes données et dont tu définis tes noms, il ne peut pas y avoir de cellule vide dans ta colonne.

 

[Nicouse]

Re : Rechercher occurrences dans une plage nommée avec critères d'une autre plage nom

Misange > effectivement la condition COMPTE_IMPUTATION semble inutile ici puisque toutes les cellules sont renseignées, mais ce n'est pas toujours le cas.

Quand je fais =SOMMEPROD(NB.SI(SOUS_CENTRE_COMMANDE;SOUS_CENTRES_ITB)*(COMPTE_IMPUTATION<>"")) j'obtiens #N/A

Quand je fais =SOMMEPROD(NB.SI(SOUS_CENTRE_COMMANDE;SOUS_CENTRES_ITB)) j'obtiens bien 46.

J'avoue que je ne comprends pas pourquoi j'obtiens #N/A pour la première formule.

 

[R@chid]

Re : Rechercher occurrences dans une plage nommée avec critères d'une autre plage nom

Salut,
pour moi j'ai pas pris le temps pour voir tes plages nommes alors je m'en cree tu peux remplcer mes plages nommes par les tiennes ca changes rien dans la formule et dans le resultas
@+
Rachid

 

[Nicouse]

Re : Rechercher occurrences dans une plage nommée avec critères d'une autre plage nom

Merci de cette précision Rachid.

Lorsque je mets un critère sur COMPTE_IMPUTATION ça ne trouve plus rien

=SOMMEPROD(ESTNUM(TROUVE("F45";COMPTE_IMPUTATION))*NB.SI(SOUS_CENTRE_COMMANDE;SOUS_CENTRES_ITB))

Je devrais trouver 8 occurrences et je trouve #N/A.
J'imagine que mon critère sur COMPTE_IMPUTATION est mal exprimé.

 

[R@chid]

Re : Rechercher occurrences dans une plage nommée avec critères d'une autre plage nom

Re,
quel critere? si tu peux preciser sur la piece jointe?
@ te relire
Rachid

 

[Misange]

Re : Rechercher occurrences dans une plage nommée avec critères d'une autre plage nom

Re bonjour

Sommeprod = somme des produits. Cela ne peut se faire QUE sur des plages qui ont la même taille.
Tu peux dans une formule somme prod entrer un calcul additionnel (comme le nb.si ici) mais il faut que la plage renvoyée soit de la même taille que l'autre ou les autres arguments du sommeprod.

Dans la formule de Rachid, modifiée pour utiliser tes plages dynamiques
=SOMMEPROD(NB.SI(SOUS_CENTRE_COMMANDE;SOUS_CENTRES_ITB))
la formule commence par compter le nombre de valeurs de commandes associées à chacun des ITB. Puis elle fait la somme de chacune des valeurs de la matrice renvoyée. Pouur voir ce qui se passe, clique dans la formule puis dans l'onglet formule clique sur évaluation de formule et regarde pas à pas comment excel fait le calcul.
0 un moment donné tu verras =sommeprod({2;11;4;22;7})
Tu as donc ici une matrice de 5 arguments. Tu ne peux pas demander à excel de multiplier chacune des valeurs de cette matrice parautre chose qu'une matrice contenant 5 arguments...

Pour un tuto détaillé sur sommeprod avec des tas d'exemples je t'incite à lilre ces quelques pages
Ce lien n'existe plus

Par ailleurs je ne comprends toujours pas ton argument sur le fait que tu puisses avec des N° de comptes vides.
Dans ce cas fais très attention car toutes tes formules seront vite fausses. Toujours pour cette raison que sommeprod ne peut travailler qu'avec des plages uniques ou de même taille.
Dans tes formules decaler, je te conseille de définir tes plages de la façon suivante pour éviter les erreurs :
au lieu de partir de A2, pars de A1 et décale d'un vers le bas, ça permet d'daviter d'avoir des grosses errerurs si tu es amené à effacer (colontairement ou par erreur) le contenude cette plage
=decaler($A$1;1;0;nbval($A:$A)-1)
quand tu as une colonne à côté, que c'est la colonne A qui aura toujours le plus grand nombre d'éléments, et que tu sais que tu as des sommeprod à faire entre ces deux colonnes, au lieu de faire
=decaler($B$1;1;0;nbval($B:$B)-1)
écris
=decaler($B$1;1;0;nbval($A:$A)-1)
de cette façon, même si tu as des éléments absents dans la deuxième colonne tes plages auront la même taille. Mais attention dans ce cas au résultat des sommeprod. Ce n'est pas parce que ça ne renvoie pas une valeur d'erreur que le résultat est valide !

 

[Nicouse]

Re : Rechercher occurrences dans une plage nommée avec critères d'une autre plage nom

Re,
quel critere? si tu peux preciser sur la piece jointe?
@ te relire
Rachid

Maintenant que je connais le nombre de cdes affectées à ITB je souhaiterai identifier dans cette qté le nb concernée par le trigramme F45 par exemple (trigramme = trois premières lettres du compte d'imputation).
Mais je suis encore bloqué .

 

[Nicouse]

Re : Rechercher occurrences dans une plage nommée avec critères d'une autre plage nom

Re bonjour

Sommeprod = somme des produits. Cela ne peut se faire QUE sur des plages qui ont la même taille. Je connaissais ce "détail".
Tu peux dans une formule somme prod entrer un calcul additionnel (comme le nb.si ici) mais il faut que la plage renvoyée soit de la même taille que l'autre ou les autres arguments du sommeprod. MAis je n'avais pas réfléchi à l'implication de celui-ci dans le cas d'une imbrication de formule addionnelle. Merci je saurai m'en souvenir.

Dans la formule de Rachid, modifiée pour utiliser tes plages dynamiques
=SOMMEPROD(NB.SI(SOUS_CENTRE_COMMANDE;SOUS_CENTRES_ITB))
la formule commence par compter le nombre de valeurs de commandes associées à chacun des ITB. Puis elle fait la somme de chacune des valeurs de la matrice renvoyée. Pouur voir ce qui se passe, clique dans la formule puis dans l'onglet formule clique sur évaluation de formule et regarde pas à pas comment excel fait le calcul.
0 un moment donné tu verras =sommeprod({2;11;4;22;7})
Tu as donc ici une matrice de 5 arguments. Tu ne peux pas demander à excel de multiplier chacune des valeurs de cette matrice parautre chose qu'une matrice contenant 5 arguments...
Cela prend effectivement tout son sens vu mon erreur de départ.

Je crois bien que j'ai déjà lu le tutoriel en question.
Merci en tout cas.

 

[R@chid]

Re : Rechercher occurrences dans une plage nommée avec critères d'une autre plage nom

Salut,
c'est peut etre ca :

=SOMME(N(FREQUENCE(SI(GAUCHE(COMPTE_IMPUTATION;3)="F45";EQUIV(SOUS_CENTRE_COMMANDE;SOUS_CENTRE_COMMANDE;0));LIGNE(SOUS_CENTRES_ITB))>0))

A valider par Ctrl+Maj+Entree
@+
Rachid

Edit : je ne sais pas si cette formule a bien fonctionne pour toi?
elle renvoie 3 pour ITB avec critere "F45"

 

[Nicouse]

Re : Rechercher occurrences dans une plage nommée avec critères d'une autre plage nom

Oh my god

Bon merci beaucoup Rachid, je regarde ça.

 

[R@chid]

Re : Rechercher occurrences dans une plage nommée avec critères d'une autre plage nom

Salut,
jusqu'a le moment pas de reponse????

 

[Nicouse]

Re : Rechercher occurrences dans une plage nommée avec critères d'une autre plage nom

ça n'a pas fonctionné mais je me suis rabattu sur autre chose.
J'ai rajouté un critère Oui/Non dans une colonne à part pour permettre mes calculs.
J'ai conservé ton NB.SI dans le SOMMEPROD comme tu l'avais proposé dès le départ et ça ça fonctionne très bien.

MErci.