Sommeprod et les doublons

[KIM]

Bonjour le forum,
Lors de mon dernier fil 'suivi mensuel des depenses' j'ai appris des nouveautés dans l'utilisation de sommeprod (merci Charly) et assez souvent avec l'aide de monique et JM et bien sûr les autre s qu'ils m'excusent de ne pas les citer.
Hier je me suis confronté à une nouveau probleme que je n'ai pas reussi à contourner.
La somme calculée avec sommeprod sur les commandes et les factures est multipliée par le nombre de fois qu'apparait le meme numero de commande. Comment faire avec sommeprod pour ne prendre en compte qu'une seule fois (unicité) le numero de commande (voir les explications dans le fichier joint).
Merci de votre aide.
NB: Sommeprod est un outil assez puissant. Quel plaisir de retrouver un jour sur ce forum une compilation sur sommeprod faite par des specialistes pour une utilisation plus poussée.
Bien amicalement
KIM [file name=sommeprodsansdoublons.zip size=7282]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/sommeprodsansdoublons.zip[/file]

 

[Jocelyn]

Bonjour le Forum,
Bonjour Kim,

alors en en cellule D6 tu peux essayer la formule :

=SI(SOMMEPROD((1/NB.SI(NumCde;NumCde))*(Projet=$C6)*MntCde_J);SOMMEPROD((1/NB.SI(NumCde;NumCde))*(Projet=$C6)*MntCde_J);'')

en F6

=SI(SOMMEPROD((1/NB.SI(NumCde;NumCde))*(Projet=$C6)*MntFact_M);SOMMEPROD((1/NB.SI(NumCde;NumCde))*(Projet=$C6)*MntFact_M);'')

par contre il y a quelque chose que je ne comprend pas a partir du momment ou tes formule font reference a code projet et que tu prends les 2 premiers caractere tu te retrouve a prendre en compte 2 commande la numéro 7786 (A287)et 5573 (A290) ce qui fait que le résultat et bien 81654.98 pour la somme de la colonne M de la feuille CDE1 puisqu'il n'y a rien en face de la commande n° 5573 (A290)
mais de 84567.66 pour la somme de la colonne J puisque la commande 5573 a un montant a ce niveau la.

Voila

Bonne journée

Jocelyn

 

[KIM]

Bonjour Jocelyn e t le forum,
Merci pour ta reponse rapide.
effectivement avec mes formules sommeprod je trouve pour le projet A2
Commandé COMMANDE Facturé
166222,64 3922,26 163309,96
mais en respectant l'unicité des num de commandes pour la col J et M, il faut trouver:
Commandé COMMANDE Facturé
84567,66 3922,26 81654,98
Comme tu l'as bien noté.

Parcontre en executant tes macros dans mon fichier, j'ai le message d'erreur suivant '#NOM?' pour les 2 formules.
As-tu une idée? Sinon,
Peux-tu me renvoyer le fichier avec tes macros.

Merci d'avance
Amicalement
KIM

 

[jp14]

Bonjour Kim Jocelyn

Une autre solution
Insérer dans le tableau Cde1 une colonne avec la formule
=1/NB.SI($D:$D;$D:$D)*$J5
D colonne Code matière ( ou prendre la colonne N° de commande ou une nouvelle colonne qui est correspond à la concaténation des 2 )

J5 colonne montant commande

On utilisera cette colonne dans la formule Somme Prod

A+

Message édité par: jp14, à: 09/02/2006 11:20

 

[Jocelyn]

re bonjour,

Bon alors revoila le fichier j'ai mis les formule en D6 et F6 cela fonctionne a priori

Jocelyn [file name=sommeprodsansdoublons_20060209111642.zip size=7146]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/sommeprodsansdoublons_20060209111642.zip[/file]

Oup's bonjour JP14 désolé je ne t'avais pas vu

Message édité par: Jocelyn, à: 09/02/2006 11:18

 

[KIM]

Re Jocelyn, Bonjour JP14 et le forum,
Merci à vous, la formule complete de Jocelyn fonctionne sans passer par une col supplementaire. Parcontre en appliquant la formule sur mon fichier global je me retrouve avec des anomalies. En les analysant je constate qu'un numero de commande peut se retrouver dans 1 à plusieurs 'code unite' differents (col A).
Comment on peut rajouter dans cette meme formule l'unicité du code unite (col A)? C-a-d les numeros de commandes identiques mais de codes unites differents doivent etre traités separement.
Merci de votre aide. J'espere ne pas retrouver d'autres incidents.
Amicalement
KIM

 

[Jocelyn]

re bonjour,

bon petites explixcation de la partie de formule
1/NB.SI(NumCde;NumCde)

dans l'exemple traité pour la valeur gauche 2 soit A2 sommeprod trouve 2 fois 7786 auquel il affecte 0.5 pour chacune des 2 ligne et une fois 5573 a laquelle il afecte 1 ensuite le reste de somme prod va faire la somme (0.5*81654.98)+(0.5*81654.98)+2912.18 = 84567.66 pour la somme de D6 je qui veux dire que si une troisieme condition arrive et suprime l'une des 2 lignes de la commande 7786 le resulatat sera faux.

avec ces explication je ne sais pas si tu pourras t'en sortir mais comme je n'ais pas vraiment compris ton nouveau problème le mieux serait que tu remette ton fichier avec l'exemple de ce qui ne vas pas.

Jocelyn

 

[KIM]

Re Jocelyn, JP14 et le forum,

Dans la feuille CDE1 Cellule E21:Num Commande devient '7786' pour l'unite 'C57'. Les numeros de commandes peuvent se retrouver à l'identique d'une unité à une autre et attribués aussi à d'autres projets:
En appliquant la formule actuelle sommeprod,
je me retrouve avec un resultat ci-dessus incorrect (voir fichier joint).
Le resultat correct serait:
A2 84567,66 3922,26 81654,98
AE 1359,74 1348 1047,01

et non:
A2 57349,33333 3922,26 54436,65333
AE 959,74 1348 655,4966667

Comment integrer dans sommeprod l'unicite du code unite?
Merci de votre aide
Amicalement
KIM [file name=sommeprodavecunicite.zip size=8849]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/sommeprodavecunicite.zip[/file]

 

[jp14]

Bonjour

Ci joint un fichier avec des formules qui devrait répondre au problème.
J'ai rajouté des colonnes dans CDE1 en particulier une qui associe le numéro de commande avec l'unité.
Les autres permettent d'éclater le montant global.


A+ [file name=somprodunicite.zip size=10723]http://www.excel-downloads.com/components/com_simpleboard/uploaded/files/somprodunicite.zip[/file]

 

[Jocelyn]

re bonjour,

Comme tu peux le voir par les colonnes T et U surtout U de la feuille CDE1 suivant le nombre de fois ou il renctre un numéro de commande il affecte un pourcentage dans l'exemple de la commande 7786 ce numéro apparait 3 fois donc % 0.33333 ce qui fais que pour chacune des ces 3 lignes il multiplie le montant de la colonne J et M par ce % avant de l'ajouter.

ce qui pour A2 donne 81654.98/3 * 2 car 2 ligne avec A2 soit 54436.65 (par rapport a la colonne M)

et pour AE 391.51+le montant des autres commande.

donc dans l'état actuel cette fonction n'est bonne que si un numéro de commande n'est présent que dans un seul codeprojet

je vais cherché si je trouve quelque chose mai sans assurance

Jocelyn

 

[Jocelyn]

re bonjour

J'en suis au même niveau que JP14 (Bravo Jp14) je ne vois pas comment faire sans passer par une colonne complémentaire qui permet de donner un nouveau code a la commande.

Jocelyn

 

[KIM]

Re Jocelyn, JP14 et le forum,

JP14, Merci , ton astuce fonctionne. Pour rajouter, si necessaire, l'unicité de l'unite il faut creer une colonne =A5&D5&E5
Merci à vous Jocelyne et JP14, et le forum.
Je suis toujours preneur d'une solution intégrée dans sommeprod ou autre sans creer de nouvelle colonne.
Amicalement
KIM