Somme d'une colonne selon période [Résolu/Fermé]

Signaler
Messages postés
70
Date d'inscription
mardi 26 octobre 2010
Statut
Membre
Dernière intervention
26 novembre 2019
-
Le Pingou
Messages postés
9517
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
30 décembre 2019
-
Bonjour,

J'ai un tableau avec une colonne de prix mensuel (ces prix changent régulièrement), une colonne où sont indiqués les dates de changement de prix et une autre colonne qui donne les montants journaliers selon les différentes dates et les prix mensuel.
Je voudrais avoir le montant total d'une période donnée (cellule J5).
Je mets le tableau en PJ
Merci

55 réponses

Messages postés
575
Date d'inscription
vendredi 11 juillet 2014
Statut
Membre
Dernière intervention
30 mars 2016
12
Merci Le pingou pour cette remarque ! J'ai pu corriger cette n-ième erreur :)

Le doc :

http://cjoint.com/?DGraq11lKZj

(j'arrête de coller la formule qui prend vraiment trop de place...)

J'avoue que ma formule est complexe s'il faut modifier les données sources mais encore une fois un peu de rechercher/remplacer permet de vite contourner le pb.
1
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 86871 internautes nous ont dit merci ce mois-ci

Messages postés
9517
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
30 décembre 2019
997
Bonjour,
Oui, désolé, en ajoutant les contrôles des dates j'ai omis un signe [.] dans une instruction.
J'en ai profité pour modifier la formule de Theo.R pour se référer aux choix de la feuille 2.
Petit exercice de style qui m'a permis de mieux comprendre sa démarche, merci Theo.R.
Le fichier : http://cjoint.com/?3GrqSKlTF44

1
Merci

Quelques mots de remerciements seront grandement appréciés. Ajouter un commentaire

CCM 86871 internautes nous ont dit merci ce mois-ci

Messages postés
9517
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
30 décembre 2019
997
Bonjour,
Pouvez-vous mettre votre exemple à disposition ? Si oui mettre sur http://cjoint.com/ et poster le lien !

Messages postés
24897
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
21 février 2020
5 043
Bonjour
Saur erreur de compréhension, (ce qui est fort probable:

excel à partir de 2007:
=SOMME.SI(Champ à sommer; Champ date;">="&cell début;champdate;"<="&cell fin)


avant 2007

=SOMMEPROD((Champ date>=cell début)*(champ date <=cell fin)*(champ à sommer))

crdlmnt


Errare humanum est, perseverare diabolicum
Le Pingou
Messages postés
9517
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
30 décembre 2019
997
Bonjour Vaucluse,
La formule SOMMEPROD(matrice1, [matrice2], [matrice3], ...) c'est parfait.
Par contre je pense que la fonction SOMME.SI ne convient pas,
mais c'est plutôt SOMME.SI.ENS (somme_plage; plage_critères1; critère1; [plage_critères2; critère2]; ...)
Salutations.
Le Pingou
Vaucluse
Messages postés
24897
Date d'inscription
lundi 23 juillet 2007
Statut
Contributeur
Dernière intervention
21 février 2020
5 043
effectivement mon ami, je me suis un peu laissé aller là...oubli de ma part

merci pour la correction et bon grand WE

crdlmnt
Messages postés
575
Date d'inscription
vendredi 11 juillet 2014
Statut
Membre
Dernière intervention
30 mars 2016
12
Tu parles d'une colonne avec les prix journaliers qui n'existe pas, tu as à la place une colonne avec Total HT qui est égal au montant mensuel * nbre de jours sur la période.

Pour la formule, il faut procéder en trois temps :

les jours de la borne inférieure après J2 (période qui commence AVANT J2 et finit APRES)

+les périodes comprises entre J2 et J3 strictement

+ les jours de la borne supérieure avant J3 (commence AVANT J3 et finit APRES).


Avec SOMME.SI.ENS, on obtient alors (j'ai sauté des lignes pour faire apparaître la correspondance avec ci-dessus) ATTENTION A BIEN VALIDER EN FORMULE MATRICIELLE (Ctrl+Shift+Enter une fois la formule collée dans la cellule) :

=(MIN(SI((C6:C100<J2)*(D6:D100>J2);D6:D100))-J2)*INDEX(B6:B100;EQUIV(MIN(SI((C6:C100<J2)*(D6:D100>J2);D6:D100));D6:D100))

+ SOMME.SI.ENS(F6:F100;C6:C100;">"&J2;C6:C100;"<"&J3;D6:D100;">"&J2;D6:D100;"<"&J3)

+(J3-MAX(SI((C6:C100<J3)*(D6:D100>J3);C6:C100)))*INDEX(B6:B100;EQUIV(MAX(SI((C6:C100<J3)*(D6:D100>J3);C6:C100));C6:C100))

Sans les sauts de ligne (pour copier/coller), à faire en formule matricielle (je répète mais ça change tout) :

=(MIN(SI((C6:C100<J2)*(D6:D100>J2);D6:D100))-J2)*INDEX(B6:B100;EQUIV(MIN(SI((C6:C100<J2)*(D6:D100>J2);D6:D100));D6:D100))+ SOMME.SI.ENS(F6:F100;C6:C100;">"&J2;C6:C100;"<"&J3;D6:D100;">"&J2;D6:D100;"<"&J3)+(J3-MAX(SI((C6:C100<J3)*(D6:D100>J3);C6:C100)))*INDEX(B6:B100;EQUIV(MAX(SI((C6:C100<J3)*(D6:D100>J3);C6:C100));C6:C100))

Chez moi ça marche donc si tu gardes les mêmes références que dans ton exemple aucun souci !

A plus :)
Messages postés
70
Date d'inscription
mardi 26 octobre 2010
Statut
Membre
Dernière intervention
26 novembre 2019

Bonjour à tous
Théo, quelle belle formule ! J'ai fait un copier coller et appliquer en formule matricielle, j'ai #N/A comme résultat.
J'ai enlevé l'espace en le + SOMME.SI.ENS, mais toujours le même résultat.
Pourtant je n'ai pas modifié le tableau.
Je n'arrive pas à trouver l'erreur.
Précision, il n'y aura jamais de date avant le 01/05/1999
Merci de votre aide
Messages postés
9517
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
30 décembre 2019
997
Bonjour,
Avez-vous essayé les 2 formules de Vaucluse
La 1 modifié en SOMME.SI.ENS :
=SOMME.SI.ENS(F6:F10;C6:C10;">="&J2;D6:D10;"<="&J3)
La 2 :
=SOMMEPROD(((C6:C10)>=J2)*(D6:D10<=J3)*(F6:F10))
Est-ce que cela répond à votre demande ?

Messages postés
70
Date d'inscription
mardi 26 octobre 2010
Statut
Membre
Dernière intervention
26 novembre 2019

Bonjour,
J'ai bien essayé avec les 2 formules, le résultat est le même et c'est pas bon.
Car par exemple du 01/05/199 au 15/11/2000, j'ai le même montant 39,54€ que du 01/05/199 au 31/10/2000 ou 01/05/1999 au 01/11/2000. La formule ne calcule pas le montant entre le 31/10/2000 et le 15/11/2000, soit (2,55*12/365)*16.
Merci encore.
eriiic
Messages postés
23271
Date d'inscription
mardi 11 septembre 2007
Statut
Contributeur
Dernière intervention
22 février 2020
5 927
Bonjour,

tu devrais faire ce qui a été demandé au 1er post par le pingou
eric
Le Pingou
Messages postés
9517
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
30 décembre 2019
997
Merci eriiic,
Je te souhaite un excellent week-end.
Amicales salutations.
Le Pingou
Messages postés
9517
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
30 décembre 2019
997
Bonjour,
Bien sûr que ce n'est pas correct, nous ne savions même pas comment devait se calculer le résultat....!
En voyant votre l'image nos pouvions penser que le résultat de l'exemple soit 39.54 Euros était correct.
Note et pourquoi ne pas mettre le résultat attendu...?
Si vous aviez mis votre fichier comme demander au poste vous auriez eu la bonne solution.


Salutations.
Le Pingou
Messages postés
70
Date d'inscription
mardi 26 octobre 2010
Statut
Membre
Dernière intervention
26 novembre 2019

Bonjour,
Voici le lien, j'ai testé la formule de Théo avant de joindre le tableau.

http://cjoint.com/?0Gmpf2NNJZg

Merci
Messages postés
9517
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
30 décembre 2019
997
Bonjour,
Merci pour le fichier.
Toutefois, pourquoi ne pas dire si la formule de Théo est conforme..... ? Ou bien c'est moi qui doit la tester... !

Messages postés
70
Date d'inscription
mardi 26 octobre 2010
Statut
Membre
Dernière intervention
26 novembre 2019

J'ai répondu hier à Theo que ça ne fonctionne pas

Lien 6 d'hier.
Merci
Le Pingou
Messages postés
9517
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
30 décembre 2019
997
Bonjour,
Pourtant il a préciser que : Chez moi ça marche donc si tu gardes les mêmes références que dans ton exemple aucun souci !
Dans ce cas, pour la période exemple la valeur selon Théo est de : 831.97 et pour moi, via VBA, de : 40.63 .... !
Me de préciser laquelle est correct... !
Salutations.
Le Pingou
Messages postés
9517
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
30 décembre 2019
997
Bonjour,
Pour votre exemple du 1.5.1999 au 15.11.2000 le montant est de : 40.63 ....Oui / Non ... !

Messages postés
70
Date d'inscription
mardi 26 octobre 2010
Statut
Membre
Dernière intervention
26 novembre 2019

Je trouve 40,80 : du 01/05/99 au 30/04/2000 (2,15/mois) = 25,87 (366 jours) + du 01/05/2000 au 31/10/2000 (2,26/mois) = 13,67 (184 jours) + du 01/11/2000 au 15/11/2000 (2,55/mois) = 1,26 (15 jours)
Ma formule pour compter le Nb de jour est J3-J2+1
Merci
Messages postés
9517
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
30 décembre 2019
997
Bonjour,
Désolé j'ai pas vu le dernier poste.
J'ai pris la peine de testez la formule de Théo et elle ne répond pas à votre résultat de 40.80¨.
je pense qu'il faut passer par une procédure (macro)......patience...!
Messages postés
70
Date d'inscription
mardi 26 octobre 2010
Statut
Membre
Dernière intervention
26 novembre 2019

Merci,
Cdt
Messages postés
9517
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
30 décembre 2019
997
Bonjour,
Voici ma proposition via procédure (voir note sur la feuille) : http://cjoint.com/?3GmsCzchVe9
Je vous laisse contrôler les résultats obtenues... !

Messages postés
575
Date d'inscription
vendredi 11 juillet 2014
Statut
Membre
Dernière intervention
30 mars 2016
12
Ma formule marchait sur mon test qui reprenait l'exacte copie de la photo initiale..

Elle calcule la totalité des prix entre la période en J2 et celle en J3. Pour la démarche j'avais déjà expliqué mon raisonnement.

Je me suis peut être trompé en collant ma formule dans mon MSG, mais n'ayant pas accès excel avant mardi je ne peux pas vérifier.

Autre possibilité : j'ai peut être juste mal compris la demande. Mais dans le cas contraire, un recours aux Macros n'est pas nécessaire !

Je vous tiens au courant mardi, mais disons que ma solution n'est pas valide en attendant.

Bonne continuation
Messages postés
70
Date d'inscription
mardi 26 octobre 2010
Statut
Membre
Dernière intervention
26 novembre 2019

Le Pingou,
Votre solution fonctionne, les valeurs sont justes, mais fonctionne uniquement jusqu'au 30/04/2003 et le montant de 117,47€. Si je tapes en J3 une date ultérieure, j'ai le même montant !
Cdt,
Messages postés
9517
Date d'inscription
mercredi 11 août 2004
Statut
Contributeur
Dernière intervention
30 décembre 2019
997
Bonjour,
Alors c'est parfait.
Une petite correction à faire dans le code et je vous transmets la version adaptée.
Salutations à Théo et c'est avec grand intérêt que je découvrirai sa version sans VBA.
1 2 3