SOMMEPROD et Exclusions

[choup67]

Bonjour à tous,

J'ai dans un fichier, besoin de compter des données en fonction de pays. Pour la zone Euro, j'ai cumulé des conditions vu qu'il n'y avait pas beaucoup de pays à prendre en compte. Ce qui me donne une formule de ce type :

=SOMMEPROD((INDIRECT("'["&$A$9&".xls]"&JOUR(H86)&"'!Magasin")="UP3")*((INDIRECT("'["&$A$9&".xls]"&JOUR(H86)&"'!Pays")=" FR")+(INDIRECT("'["&$A$9&".xls]"&JOUR(H86)&"'!Pays")=" DE")+(INDIRECT("'["&$A$9&".xls]"&JOUR(H86)&"'!Pays")=" NL")+(INDIRECT("'["&$A$9&".xls]"&JOUR(H86)&"'!Pays")=" LU")+(INDIRECT("'["&$A$9&".xls]"&JOUR(H86)&"'!Pays")=" BE")))

J'aimerai maintenant faire l'export mais au lieu de cumulé par des "+" pour chaque pays, vu qu'il y en a beaucoup, je voudrais simplement exclure de la recherche la France, l'allemagne, et le benelux.

Après quelques recherches, je suis tombée sur cette formule mais je n'arrive pas à la faire fonctionner, ça me renvoi 0.

=SOMMEPROD(--(NB.SI(Exclus;champ)=0))

Adaptée à mon cas, ça donne :

=SOMMEPROD((INDIRECT("'["&$A$9&".xls]"&JOUR(H86)&"'!Magasin")="UP3")*(--(NB.SI(N1:N5;INDIRECT("'["&$A$9&".xls]"&JOUR(H86)&"'!Pays")=0))))

N1 a N5 contiennent FR, DE, NL, LU, BE donc les pays que je souhaite justement ne pas comptabiliser.

Merci pour votre aide.

 

[Victor21]

Re : SOMMEPROD et Exclusions

Bonjour, choup67.

Sans fichier, pas facile...
Mais si -et seulement si- j'ai bien compris, une simple soustraction suffit, non ? (Tout moins ta première formule).

 

[choup67]

Re : SOMMEPROD et Exclusions

Effectivement ça fonctionne, mais ça me donne une formule à rallonge :

=SOMMEPROD((INDIRECT("'["&$A$9&".xls]"&JOUR(H86)&"'!Magasin")="UP3")*(INDIRECT("'["&$A$9&".xls]"&JOUR(H86)&"'!Suivi")<>" ")*(INDIRECT("'["&$A$9&".xls]"&JOUR(H86)&"'!Pays")<>0))-SOMMEPROD((INDIRECT("'["&$A$9&".xls]"&JOUR(H86)&"'!Magasin")="UP3")*(INDIRECT("'["&$A$9&".xls]"&JOUR(H86)&"'!Suivi")<>" ")*((INDIRECT("'["&$A$9&".xls]"&JOUR(H86)&"'!Pays")=" FR")+(INDIRECT("'["&$A$9&".xls]"&JOUR(H86)&"'!Pays")=" DE")+(INDIRECT("'["&$A$9&".xls]"&JOUR(H86)&"'!Pays")=" NL")+(INDIRECT("'["&$A$9&".xls]"&JOUR(H86)&"'!Pays")=" LU")+(INDIRECT("'["&$A$9&".xls]"&JOUR(H86)&"'!Pays")=" BE")))

Je pense qu'avec le bout de code que j'ai trouvé, la formule serait quand même plus courte mais je n'arrive pas à l'adapter à mon cas.

 

[CISCO]

Re : SOMMEPROD et Exclusions

Bonjour

Mets un petit fichier en pièce jointe, ainsi nous pourrons plus efficacement t'aider.

@ plus

 

[choup67]

Re : SOMMEPROD et Exclusions

Bonjour,

Voici un fichier simplifié de ce que je cherche à faire.

J'ai trouvé cette formule, mais je n'arrive pas à l'adapter à mon cas :

=SOMMEPROD(--(NB.SI(Exclus;champ)=0))

Merci pour votre aide.

 

[choup67]

Re : SOMMEPROD et Exclusions

Je viens de réussir à faire marcher la formule d'exclusion sur le petit fichier mais quand je veux l'adapter sur mon gros fichier, ça me renvoi 0

 

[Victor21]

Re : SOMMEPROD et Exclusions

Bonjour, choupi67.

Redéfinis tes noms : Sommeprod n'accepte pas les colonnes entières.

 

[choup67]

Re : SOMMEPROD et Exclusions

Victor21, es tu sûr de ce que tu dis? Car j'ai d'autres formules SOMMEPROD qui font appel à ces mêmes noms et tout fonctionne parfaitement...

 

[Tibo]

Re : SOMMEPROD et Exclusions

Bonjour,

Regarde le fichier joint où les différents noms ont été définis avec la fonction DECALER.

Je confirme le propos de Victor que je salue

@+

 

[choup67]

Re : SOMMEPROD et Exclusions

Merci pour votre aide. Est ce que tu pourrais m'expliquer un peu plus comment procéder pour la fonction "decaler" car je ne l'ai jamais utilisée.

En fait, j'ai une feuille "modèle", où les colonnes ont des noms. Lorsque je copie cette feuille et que je renomme par le jour, les noms des colonnes se mettent automatiquement en étendu sur la feuille en question. Est ce que ça fonctionne aussi avec décaler?

EDIT : J'ai fais le test pour l'histoire de gestion des noms et de la feuille modèle, ça fonctionne bien. Mais bon, j'ai bêtement recopier ta formule sans véritablement la comprendre, donc si tu veux bien m'éclairer un peu à ce sujet? A quoi sert-elle exactement? Quelle est son application habituellement?

Je vais maintenant réessayer de faire fonctionner mon sommeprod.

EDIT2 : Tout mes SOMMEPROD ne fonctionnent plus depuis que j'ai renommé les colonnes avec DECALER

 

[Tibo]

Re : SOMMEPROD et Exclusions

Bonjour,

Peux-tu nous rejoindre un fichier plus proche de ton vrai fichier.

Il sera alors plus facile d'adapter à ce fichier et de t'expliquer.

A te (re)lire

@+

 

[choup67]

Re : SOMMEPROD et Exclusions

La seule différence entre ce fichier et le mien, c'est que chez moi, l'onglet "21" se trouve dans un autre fichier excel. Est ce que c'est pour ça que ça ne fonctionne pas ?

J'ai utilisé le mode debugage pour essayer de voir d'où provient l'erreur et en fait si je remplace le mot "magasin" par F:F, il me renvoit bien un résultat. Donc c'est qu'il n'arrive pas à trouver la plage de cellule définit dans le gestionnaire de noms.

 

[Tibo]

Re : SOMMEPROD et Exclusions

re,

Il faut travailler avec les deux fichiers ouverts.

Essaye avec le dossier joint

@+

 

[choup67]

Re : SOMMEPROD et Exclusions

C'est à s'arracher les cheveux lol Pourquoi est ce que ça fonctionne avec toi? Je laisse bien mes deux fichiers ouverts pourtant... snif

Bon je procède par étape :
Jusque là, ça fonctionne :

=SOMMEPROD(('[Backlog Juin 2011.xls]21'!Magasin="UP3")*(('[Backlog Juin 2011.xls]21'!Pays=" FR")+('[Backlog Juin 2011.xls]21'!Pays=" DE")+('[Backlog Juin 2011.xls]21'!Pays=" BE")+('[Backlog Juin 2011.xls]21'!Pays=" NL")+('[Backlog Juin 2011.xls]21'!Pays=" LU")))

Ensuite, quand j'essaye de remplacer

'[Backlog Juin 2011.xls]21'!Magasin

par

(INDIRECT("'["&$A$9&".xls]"&JOUR(H86)&"'!Magasin")

ça ne fonctionne plus. (Je précise qu'avant, quand j'avais nommé mes plages par colonne entière, ça fonctionnait, donc c'est à n'y rien comprendre)

A9 = Backlog Juin 2011
H86 = 21/06/2011

Une erreur de syntaxe?

 

[Tibo]

Re : SOMMEPROD et Exclusions

re,

Peux-tu nous donner la façon dont tu as défini chacune de tes plages avec la fonction DECALER ?

Il faut que chaque plage nommée ait la même taille. Pour cela, je m'étais référé à NBVAL($A:$A) pour chaque plage dans les formules DECALER.

Indiques nous également quelle version d'Excel tu utilises (je me demande si la notion de colonne entière avec SOMMEPROD n'a pas sauté avec Excel 2007 : je ferais le test ce soir)

@+