la somme selon deux critères: les encaissements et les dates

[moebius]

bonjour,

un problème tout simple mais toujours avec un résultat qui affiche 0:
dans la feuille 1(saisie) j'ai une colonne A avec les dates
la colonne E avec les achats ou les ventes
j'aimerais maintenant avoir dans la feuille 2 un tableau avec la somme mensuelle des ventes et des achats séparement... quelle est donc cette (foutue) formule?
voici ce que j'avais indiqué dans un premier temps pour les ventes: =SOMME.SI.ENS(Saisie!E2:E200;Saisie!E2:E200;"=>0";Saisie!A2:A200;"=8")
soit le problème vient de la date, comment le notifier correctement dans la formule?
soit je n'ai pas utilisé la bonne fonction...
ci joint le fichier en questionRegarde la pièce jointe tresorerie brouillon.xlsm
d'avance merci...

 

[david84]

Re : la somme selon deux critères: les encaissements et les dates

Bonsoir,
cf fichier ci-joint.
A+

 

[moebius]

Re : la somme selon deux critères: les encaissements et les dates

j'hallucine! plus de deux jours pour chercher la bonne formule et voila qu'on me la résoud en moins de deux minutes! merci à david
toutefois : dans la formule que tu as inscrit je ne vois aucun lien avec l'autre feuille, sans doute dois je considerer que l'inscription de la date prend en compte toute les feuilles?
ensuite le signe "*" n'est apparament plus une multiplication dans la formule, il devient quoi? une sorte de passage au deuxieme critière?
pourquoi ma formule n'a-t-elle pas été correct?
enfin je te bombarde de questions, aucune obligation de réponse, j'aime comprendre tout simplement.
Encore merci...

 

[david84]

Re : la somme selon deux critères: les encaissements et les dates

Re,

=SOMMEPROD((MOIS(date)=MOIS(B$1))*(Montant>=0);Montant)

Ta formule ne fonctionne pas notamment parce que tu ne peux utiliser somme.si car tu as 2 critères de recherche (le montant >=0 et le mois), d'où le "*" qui dans le cadre d'une formule matricielle (sommeprod en est une même si elle se valide classiquement) veut dire "et".
Les plages sont nommées (aller dans l'onglet "formule" puis dans "gestionnaire de nom" pour le constater) ce qui te permet d'y faire référence quelle que soit la feuille où tu te trouves). Pour nommer une plage, aller dans "définir un nom" et suivre les directives.
Pour rendre ta plage de dates dynamique et lui permettre de prendre en compte l'ajout de nouvelles dates, remplacer le formule actuelle par

=DECALER(Saisie!$A$2;;;NBVAL(Saisie!$A:$A)-1)

et adapter de la même manière pour "Montant"
A+

 

[moebius]

Re : la somme selon deux critères: les encaissements et les dates

ok, si j'ai bien compris, le gestionnaire de noms dans formule permet de simplifier l'écriture de la formule... je vais tester encore un peu mais je sens que je fais la un grand bond en avant, et ce grâce à vous. Bien le merci.

 

[moebius]

Re : la somme selon deux critères: les encaissements et les dates

au fait, en guise de votre aide, si vous avez des questions techniques pour tout aménagement d'intérieur n'hésitez pas. je suis artisan spécialisé dans le bois et la pose de cuisine. et vous?

 

[moebius]

Re : la somme selon deux critères: les encaissements et les dates

J'ai copié vos formules =SOMMEPROD((MOIS(date)=MOIS(B$1))*(Montant>=0);Montant) en définissant la date (comme pour le montant) de cette façon: =Saisie!$A$2:$A$35. Impeccable, ca fonctionne.
Par contre, lorsque que je les définis en dynamique, ca ne fonctionne pas avec la formule :=DECALER(Saisie!$A$2;;;NBVAL(Saisie!$A:$A)-1) que se passe t il? pourquoi trois fois ";" pourquoi le "-1"?

 

[ERIC S]

Re : la somme selon deux critères: les encaissements et les dates

Bonjour moebius

avec decaler cela fonctionne très bien à condition de s'assurer qu'il y a le même nombre d'enregistrements pris en compte par date et montant.
Il faut donc définir date comme :=DECALER(Saisie!$A$2;;;NBVAL(Saisie!$A:$A)-1)
et montant comme :=DECALER(Saisie!$E$2;;;NBVAL(Saisie!$A:$A)-1)

le nbval sera le même pour les deux noms

reste le problème qu'il faut comparer mois ET année car nous sommesbientôt en 2011

Personnellement, j'aurais plutôt créé une colonne crédit et une colonne débit sur la feuille de saisie. Si tu veux tu saisis toujours en colonne C mais tu ventiles les positifs en crédit et les négatifs en débit (en changeant de signe), pour se rapprocher d'une compta classique.
J'aurais aussi ajouté une colonne avec =DATE(ANNEE(A2);MOIS(A2);1) ramenant les dates au 1er du mois et un affichage mois année

ensuite, au lieu de somme prod on utilise somme.si

le tableau se complique légèrement mais est facile à comprendre

voir exemple

 

[moebius]

Re : la somme selon deux critères: les encaissements et les dates

Bonjour eric,

Je te remercie vivement pour ta réponse.
en effet la formule decaler fonctionne maintenant parfaitement avec tes recommandations.
Avec un peu de recherche je comprends bcp mieux cette fonction et le pourquoi des trois ";" (on a sauté des données) et le -1 (le nombre de ligne dans la colonne moins le titre= le nombre de jour exact)
Je te remercie également pour ta proposition de tableau. les formules sont nettement simplifiées. le seul truc qui m'ennuie c'est d'avoir deux fois les dates et deux fois les montants. mais je suppose que je peux les masquer par la suite dans la mise en page par exemple...je vais etudier cela.
je n'ai pas compris où est le problème du passage à l'année 2011? j'ai fait une simulation toutes les formules, aucun problème...
En tout cas c'est bon de se faire aider...

 

[ERIC S]

Re : la somme selon deux critères: les encaissements et les dates

Re

un phénomène qui ne tient pas du miracle est que 2011 aura, comme en 2010 les mois de Janvier, Février...
et qu'il faut faire attention de ne pas récupérer tous les mois de janvier, mais bien janvier 2010 puis, un an après, janvier 2011.

cela évite de gérer un fichier par an, excel 2003 n'a que 65536 lignes mais on peut en général tenir plus d'un an.

 

[moebius]

Re : la somme selon deux critères: les encaissements et les dates

je comptais faire un fichier par an, donc pas de deux "janvier"...
sinon dans la frappe de la cellule en dessous de decembre 2010, je tape 03/01 il indique 3 janvier 2010 au lieu de 2011, que ce soit dans la feuille de david ou d'eric, comment résoudre cela?

 

[ERIC S]

Re : la somme selon deux critères: les encaissements et les dates

re

à ma connaissance, il faut taper l'année si diférente de l'année en cours :

XL ne peut pas deviner

 

[Sympa974]

Re : la somme selon deux critères: les encaissements et les dates

Bonjour,
Je dois réaliser une formule équivalente et du coup je me suis inspiré ici.
Malheureusement, ma formule ne fonctionne pas alors que pour vous oui.
Je m'en remets à votre aide car je ne comprends pas.

Je vous joins mon fichier XL.

Principe : additionner les dépenses (montant) pour un fournisseur donné et une semaine donnée. Résultat dans le tableau de suivi.

Merci d'avance.

 

[Monique]

Re : la somme selon deux critères: les encaissements et les dates

Bonjour,

Dans ton fichier, les plages nommées n'ont pas la même taille
C'est tout

 

[Sympa974]

Re : la somme selon deux critères: les encaissements et les dates

Merci beaucoup Monique;
Ca marche désormais.
Merci encore.