Somme avec plusieurs conditions Fermé

Question

Bonjour, 
Je ne suis pas expert d'excel. J'ai besoin d'aide pour réaliser un calcul qui me semble difficile.
Je suis en alternance dans une entreprise qui réalise du stockage de palettes de différents formats et je souhaite automatiser le fichier.

Le fichier se décompose en trois feuillets:
                        * Feuillet "Saisie" : pour etre bref, on renseigne n° semaine où rentre dans entrepot et n° de semaine où palette sort de l'entrepôt ( s'il y a), format palette.
ex:      
             entrée  sortie   format             
               44       45        80*120
               23                  100*120
               25       47        80*120   
Une ligne = 1palette; si cellule vide dans col sortie ce n'est pas sortie.

                          * Feuillet "Préfacturation" : il se compose de cinq tableaux (1 par semaine; on facture 4 ou 5 semaines par mois). il résume l'état des stocks à chaque semaine et pour chaque format. Il sert pour la facturation.

                          * Feuillet "recap": il reprend globalement les couts par semaine.

Mon problème:
Je souhaite que chaque tableau de "prefacturation" vienne chercher les infos dans la feuillet "saisie".

ex: solde initial = toutes les palettes entré avant la semaine qu'on facture à condition qu'elles ne soient pas sorties ou sorties la semaine que l'on facture. somme à faire pour chaque type de palette.
 

ex: entree = toutes les palette qui sont entrée dans la semaine de facturation.


J'ai essayé avec sommeprod ou nb.si.ens en vain.Peut etre je n'ai pas la bonne syntaxe.

Merci d'avance pour votre aide.
Cordialement.

gbinforme · Answer

bonjour

Ta feuille "Saisie" car tu as oublié la semaine
A	B	C	D
semaine	entrées	sorties	format
40	44	45	80*120
40	23		100*120
44	25	47	80*120
44	23	23	100*120

Ta feuille "Préfacturation"
A	B	C		D
semaine	format	solde initial	entree
44	80*120	-48		25
	100*120	0		23

La formule en colonne C
=SOMMEPROD((Saisie!$D$3:$D$32=B3)*(Saisie!$A$3:$A$32<$A$3)*(Saisie!$B$3:$B$32))-SOMMEPROD((Saisie!$D$3:$D$32=B3)*(Saisie!$A$3:$A$32<=$A$3)*(Saisie!$C$3:$C$32))

La formule en colonne D
=SOMMEPROD((Saisie!$D$3:$D$32=B3)*(Saisie!$A$3:$A$32=$A$3)*(Saisie!$B$3:$B$32))
ces formules peuvent être "tirées" en bas selon besoin.

PENSETROMALALATETE · Answer

Bonjour gbinforme, et merci pour ta réponse,

J'ai compris le raisonnement de ta formule mais l'assistant Excel me propose une autre:
Ta syntaxe= sommeprod((......)*(....)*(....))-.......
Assistant Excel=sommeprod(...;(...);(...);(...))...
Ca n'a pas fonctionné avec les deux syntaxes. 
Je pense qu'il te manque des précisions.

J'ai essaye d'être clair mais j'avoue que c assez complexe. Je vais préciser.
Dans la colonne "entrée" de "saisie" ainsi que la colonne "sortie", on indique la semaine ou on a rentre la palette ou sorti la palette. Pour la sortie elle peut être vide si la palette n'est pas sortie.

Dans mon exemple, 
1ère ligne: 1 palette 100*120 est rentrée semaine  40 et est sortie 45.
2ème ligne: 1 palette  80*120 est rentrée semaine 23 et est encore dans l'entrepôt.
..... etc.
Chaque ligne correspond à une palette. Il y a d'ailleurs une colonne "quantité" dans "saisie" renseignée 1 à chaque fois (je ne sais pas si elle est utile).

Dans la feuille "préfacturation", 

J'ai besoin du solde initial de chaque type de palette. Par exemple, si on facture la semaine 45 ,
Il nous faut la somme de toutes les 100*120 (ainsi de suite pour chaque type de palette) qui sont entrées avant la semaine 45 (donc colonne "entrée" de "saisie" < semaine 45) ET 
SOIT qui ne sont pas sorties (donc colonne "sortie" de "saisie" vide).
SOIT qui sont sorties semaine 45.
 
De plus j'ai prévu le passage à 2010 en notant les Semaines ainsi: 2009-45; quand le passage à 2010 se fera on notera les semaines ainsi 2010-34 par exemple. 
Pas de problème pour les calculs?

Merci encore de ton aide.

gbinforme · Answer

bonjour

Effectivement je n'avais pas compris la saisie palette / palette

Ta feuille "Saisie" revue

A	B	C	
entrées	sorties	format
44	45	80*120
23		100*120
25	47	80*120



Ta feuille "Préfacturation"

A		B		C		
semaine	format	solde initial	entree
44	80*120				
	100*120				


La formule en colonne C

=SOMMEPROD((Saisie!$C$3:$C$32=B3)*(Saisie!$A$3:$A$32<$A$3))-SOMMEPROD((Saisie!$C$3:$C$32=B3)*(Saisie!$B$3:$B$32=$A$3))



La formule en colonne D

=SOMMEPROD((Saisie!$C$3:$C$32=B3)*(Saisie!$A$3:$A$32=$A$3))

La syntaxe de l'assistant te permet de rajouter d'autres zones et celle-ci se limite à une seule zone.

Saisie!$C$3:$C$32=B3 répond faux (0) ou vrai (1)
Saisie!$A$3:$A$32=$A$3 répond faux (0) ou vrai (1)
Si tu as 2 vrai tu obtiens 1 (1*1) sinon 0 (1*0 ou 0*0) pour chaque élément de la plage

Le signe * correspond au test ET
Le signe + correspond au test OU

PENSETROMALALATETE · Answer

Bonjour et Bonne année,
Merci pour tes réponses pertinentes car j'ai pu bien avancé.
 
Cependant je rencontre quelques problèmes que je ne juge pas logiques.
Par exemple, pour la somme la plus simple (entrée ou sortie palette) je l'ai ecrite de cette manière:
 
=sommeprod((pal=A8)*(in=$d$4) je remplace in par out pour la fonction des sorties.
"in" est le nom de la colonne où il y a les infos sur l'entrée de la palette (feuille saisie)
"out" est le nom de la colonne où il y a les infos sur la sortie de la palette (feuille saisie)
A8 est la cellule où il y a le format que l'on recherche.
D4 est la cellule où on saisit la semaine de facturation.
 
Ces deux fonctions semblent fonctionner et les valeurs obtenues sont exactes. Le problème que je rencontre est que parfois la formule ne me trouve aucune palette ni en entrée ni en sortie, alors que dans la feuille saisie il en existent pour la semaine demandée.
 
Pour info j'ai noté les semaines ainsi pour prévoir le passage à 2010: 2009-23 ( semaine 23 de 2009).
 
Ensuite je rencontre un souci pour la formule me calculant le solde initial.
Peut être, serait il plus simple que tu visualise le fichier. Ce serait plus parlant. Comment faire, je l'ai déjà vu sur le site.
 
Cordialement.
Bonne journée.

gbinforme · Answer

bonjour

"in" est le nom de la colonne

Attention car c'est un nom réservé excel et la fonction ne s'applique pas à la colonne entière.

Tu peux mettre le classeur sur http://www.cijoint.fr/ et donner ici le lien généré.

Somme avec plusieurs conditions

5 réponses

Discussions similaires