Somme d'une colonne selon période
Résolu
pajude
Messages postés
84
Statut
Membre
-
Le Pingou Messages postés 12646 Date d'inscription Statut Contributeur Dernière intervention -
Le Pingou Messages postés 12646 Date d'inscription Statut Contributeur Dernière intervention -
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
A voir également:
- Somme d'une colonne selon période
- Déplacer une colonne excel - Guide
- Formule somme excel colonne - Guide
- Trier une colonne excel - Guide
- Colonne word - Guide
- Somme si couleur - Guide
55 réponses
Merci encore, c'est super de votre part.
Pourriez vous dans la macro mettre quelques explications, car je modifierai sûrement le tableau et je suis loin de maitriser VBA.
Si Theo arrive à un résultat avec des fonctions ce serait également parfait.
Bonne soirée
Pajude
Pourriez vous dans la macro mettre quelques explications, car je modifierai sûrement le tableau et je suis loin de maitriser VBA.
Si Theo arrive à un résultat avec des fonctions ce serait également parfait.
Bonne soirée
Pajude
Bonjour,
Je suis tombé sur une anomalie sur les dates, par exemple la ligne 11 vous avez date de fin 30.04.2003 et sur la ligne 12 date chgt prix 05.05.2003 ce qui représente un trou de 6 jours sans tarif... est-ce bien correct... ?
Il y en a d'autre ........ et cela influence le code qui donne un résultat erroné... !
Merci de votre réponse...
Je suis tombé sur une anomalie sur les dates, par exemple la ligne 11 vous avez date de fin 30.04.2003 et sur la ligne 12 date chgt prix 05.05.2003 ce qui représente un trou de 6 jours sans tarif... est-ce bien correct... ?
Il y en a d'autre ........ et cela influence le code qui donne un résultat erroné... !
Merci de votre réponse...
Non ce n'est pas correct, car Il doit y avoir une anomalie car la colonne D affiche le dernier jour précédent de la colonne C. Donc si en ligne 12 il y a 05/05/2003 en ligne 11 colonne D il doit y avoir 04/05/2003. Erreur probable des formules en colonne D.
Cdt,
Cdt,
Bonjour,
En attendant votre réponse.
J'ai modifié la procédure et aussi les dates de fin selon mon idée en espérant que c'est conforme pour vous.
Le fichier : https://www.cjoint.com/?3GmxklaxDuc
En attendant votre réponse.
J'ai modifié la procédure et aussi les dates de fin selon mon idée en espérant que c'est conforme pour vous.
Le fichier : https://www.cjoint.com/?3GmxklaxDuc
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
Bonsoir,
Excellent, le résultat est correct, je ne pensais pas que date - 1 fonctionnait, surtout quand on fait par ex 01/03/2014 - 1 = 28/02/2014.
Pouvez vous m'expliquer les lignes de la macro (quand vous aurez un moment), je ne maîtrise pas vraiment VBA.
Attendons maintenant la solution de Theo avec des fonctions.
Merci beaucoup
Cdt,
Excellent, le résultat est correct, je ne pensais pas que date - 1 fonctionnait, surtout quand on fait par ex 01/03/2014 - 1 = 28/02/2014.
Pouvez vous m'expliquer les lignes de la macro (quand vous aurez un moment), je ne maîtrise pas vraiment VBA.
Attendons maintenant la solution de Theo avec des fonctions.
Merci beaucoup
Cdt,
Bonjour,
La procédure est commenté voir sous MSO VB ... touche : [Ctrl + F11).
En plus j'ai mis une petite note concernant votre formule pour les dates [=SI(ESTVIDE($N7);"";DATE(AN.....] sur la feuille [Copie arch].
Le fichier : https://www.cjoint.com/?3GnsqIrcJU8
La procédure est commenté voir sous MSO VB ... touche : [Ctrl + F11).
En plus j'ai mis une petite note concernant votre formule pour les dates [=SI(ESTVIDE($N7);"";DATE(AN.....] sur la feuille [Copie arch].
Le fichier : https://www.cjoint.com/?3GnsqIrcJU8
Bonjour,
Merci pour les infos, en revanche je vois que vous avez remplacé dat par val dans la macro.
Est ce que c'est pour cela que le montant est arrondi à zéro. Ex du 01/05/99 au 30/04/00, le montant est de 26,00 au lieu de 25,87 qui me faudrait.
Merci
pajude
Merci pour les infos, en revanche je vois que vous avez remplacé dat par val dans la macro.
Est ce que c'est pour cela que le montant est arrondi à zéro. Ex du 01/05/99 au 30/04/00, le montant est de 26,00 au lieu de 25,87 qui me faudrait.
Merci
pajude
Bonjour,
Concerne : avez remplacé dat par val dans la macro
Eh oui, par principe : la variable fait référence à une valeur donc [val] et cela n'a pas d'influence sur la procédure.
Suite : Ex du 01/05/99 au 30/04/00, le montant est de 26,00 au lieu de 25,87 qui me faudrait.
J'ai déclaré les variables utilisées dans la procédure et une petite erreur pour [resu]
Merci de remplacer dans la procédure :
Et enfin : si je déplace les données à saisir (I2:J5) sur une autre feuille
Oui car elle se réfère à la feuille active par défaut.
Solution vous nommez la feuille qui contient les données ou nommer la plage des données ou encore mieux mettre le nom de la feuille dans une cellule sur l'autre feuille .
Qu'elle est votre choix ... ?
Concerne : avez remplacé dat par val dans la macro
Eh oui, par principe : la variable fait référence à une valeur donc [val] et cela n'a pas d'influence sur la procédure.
Suite : Ex du 01/05/99 au 30/04/00, le montant est de 26,00 au lieu de 25,87 qui me faudrait.
J'ai déclaré les variables utilisées dans la procédure et une petite erreur pour [resu]
Merci de remplacer dans la procédure :
Dim resu As LongPar :
Dim resu As Double
Et enfin : si je déplace les données à saisir (I2:J5) sur une autre feuille
Oui car elle se réfère à la feuille active par défaut.
Solution vous nommez la feuille qui contient les données ou nommer la plage des données ou encore mieux mettre le nom de la feuille dans une cellule sur l'autre feuille .
Qu'elle est votre choix ... ?
Merci encore de votre disponibilité (c'est énorme ce que vous faîtes !).
j'ai remplacé comme indiqué et ça fonctionne.
Pour le déplacement, je préfère nommer la feuille (d'ailleurs que je protègerai) et avoir le résultat sur une autre feuille.
Les données seront sur la feuille nommée "Tarif" et le calcul sur la feuille nommée "Facture"
J'aimerai aussi(si possible) avoir la solution que vous avez souligné.
Merci
j'ai remplacé comme indiqué et ça fonctionne.
Pour le déplacement, je préfère nommer la feuille (d'ailleurs que je protègerai) et avoir le résultat sur une autre feuille.
Les données seront sur la feuille nommée "Tarif" et le calcul sur la feuille nommée "Facture"
J'aimerai aussi(si possible) avoir la solution que vous avez souligné.
Merci
Bonjour,
Ma proposition (voir Feuil1 et Feuil2) avec le nom de feuille dans une cellule : https://www.cjoint.com/?3GoqGV6W2Hj
Ma proposition (voir Feuil1 et Feuil2) avec le nom de feuille dans une cellule : https://www.cjoint.com/?3GoqGV6W2Hj
Bonjour,
Chez moi c'est en ordre.
Je vous renvoie une nouvelle fois : https://www.cjoint.com/?3GorKK0vNfE
Note : il est préférable d'utiliser la solution en mentionnant le nom de la feuille des données et ainsi on évite une correction dans la procédure si on change le nom ... !
Chez moi c'est en ordre.
Je vous renvoie une nouvelle fois : https://www.cjoint.com/?3GorKK0vNfE
Note : il est préférable d'utiliser la solution en mentionnant le nom de la feuille des données et ainsi on évite une correction dans la procédure si on change le nom ... !
Super, ça fonctionne.
Je vous remercie beaucoup.
Je vous aurai bien proposé de prendre un verre (sur Toulouse).
Merci encore pour vos services.
@ bientôt
Pajude
Je vous remercie beaucoup.
Je vous aurai bien proposé de prendre un verre (sur Toulouse).
Merci encore pour vos services.
@ bientôt
Pajude
Bonjour tout le monde,
J'ai donc pu revoir ma première proposition, en effet elle était incomplète et j'ai du reprendre la formule pour prendre en compte tous les cas spécifiques.
Au final, ELLE MARCHE !! En tout cas avec tous les cas spécifiques que j'ai pu repérer. Je vous laisse regarder par vous même :
https://www.cjoint.com/?DGpkImrltdG
Le problème avait donc bien une solution sans recours aux macros ;)
A plus tard,
P.S: Pour ceux qui n'auraient pas le temps de regarder le document, la formule à entrer en FORMULE MATRICIELLE est la suivante :
=SI(J2<C6;"Date en J2 hors données";SI(J3>MAX(C6:D100);"Date en J3 hors données";SI(MAX(SI((C6:C100<=J3)*(D6:D100>=J3);C6:C100))=MIN(SI((C6:C100<=J2)*(D6:D100>=J2);C6:C100));J4*(12/365)*INDEX(B6:B100;EQUIV(MIN(SI((C6:C100<=J2)*(D6:D100>=J2);C6:C100));C6:C100));SI(J3=MAX(C6:C100);(1+MIN(SI((C6:C100<=J2)*(D6:D100>=J2);D6:D100))-J2)*(12/365)*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)+(12/365)*INDEX(B6:B100;EQUIV(MAX(SI((C6:C100<=J3)*(D6:D100>=J3);C6:C100));C6:C100));(1+MIN(SI((C6:C100<=J2)*(D6:D100>=J2);D6:D100))-J2)*(12/365)*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)+(1+J3-MAX(SI((C6:C100<=J3)*(D6:D100>=J3);C6:C100)))*(12/365)*INDEX(B6:B100;EQUIV(MAX(SI((C6:C100<=J3)*(D6:D100>=J3);C6:C100));C6:C100))))))
J'ai donc pu revoir ma première proposition, en effet elle était incomplète et j'ai du reprendre la formule pour prendre en compte tous les cas spécifiques.
Au final, ELLE MARCHE !! En tout cas avec tous les cas spécifiques que j'ai pu repérer. Je vous laisse regarder par vous même :
https://www.cjoint.com/?DGpkImrltdG
Le problème avait donc bien une solution sans recours aux macros ;)
A plus tard,
P.S: Pour ceux qui n'auraient pas le temps de regarder le document, la formule à entrer en FORMULE MATRICIELLE est la suivante :
=SI(J2<C6;"Date en J2 hors données";SI(J3>MAX(C6:D100);"Date en J3 hors données";SI(MAX(SI((C6:C100<=J3)*(D6:D100>=J3);C6:C100))=MIN(SI((C6:C100<=J2)*(D6:D100>=J2);C6:C100));J4*(12/365)*INDEX(B6:B100;EQUIV(MIN(SI((C6:C100<=J2)*(D6:D100>=J2);C6:C100));C6:C100));SI(J3=MAX(C6:C100);(1+MIN(SI((C6:C100<=J2)*(D6:D100>=J2);D6:D100))-J2)*(12/365)*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)+(12/365)*INDEX(B6:B100;EQUIV(MAX(SI((C6:C100<=J3)*(D6:D100>=J3);C6:C100));C6:C100));(1+MIN(SI((C6:C100<=J2)*(D6:D100>=J2);D6:D100))-J2)*(12/365)*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)+(1+J3-MAX(SI((C6:C100<=J3)*(D6:D100>=J3);C6:C100)))*(12/365)*INDEX(B6:B100;EQUIV(MAX(SI((C6:C100<=J3)*(D6:D100>=J3);C6:C100));C6:C100))))))
Bonjour,
Excellente la formule, le résultat est OK.
Seul Hic sur la première période, du 01/05/1999 au 30/04/2000, le montant est 51,74 au lieu de 25,87.
Cdt
Excellente la formule, le résultat est OK.
Seul Hic sur la première période, du 01/05/1999 au 30/04/2000, le montant est 51,74 au lieu de 25,87.
Cdt
Je vais regarder cela cet après-midi, l'erreur à lieu quand on sélectionne une période égale à une ligne exactement ?
--
--
Il y avait une erreur sur la fonction MIN(SI(...)) que je ne saurais expliquer, mais j'ai pu simplifier la fonction pour qu'elle marche.
Voici le nouveau doc avec la fonction corrigée :
https://www.cjoint.com/?DGpobaedTrm
P.S: ATTENTION au résultat par macro, il ne donne pas du tout le bon chiffre... dans l'exemple que j'ai laissé il donne 218,87 au lieu de 4,99...
En espérant que la formule soit maintenant parfaite ;)
Formule brut :
=SI(J2<C6;"Date en J2 hors données";SI(J3>MAX(C6:D100);"Date en J3 hors données";SI(MAX(SI((C6:C100<J3)*(D6:D100=J3);C6:C100))=MIN(SI((C6:C100=J2)*(D6:D100>J2);C6:C100));J4*(12/365)*INDEX(B6:B100;EQUIV(MIN(SI((C6:C100<=J2)*(D6:D100>=J2);D6:D100));C6:C100));SI(J3=MAX(C6:C100);(1+MIN(SI((C6:C100<=J2)*(D6:D100>=J2);D6:D100))-J2)*(12/365)*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)+(12/365)*INDEX(B6:B100;EQUIV(MAX(SI((C6:C100<=J3)*(D6:D100>=J3);C6:C100));C6:C100));(1+MIN(SI((C6:C100<=J2)*(D6:D100>=J2);D6:D100))-J2)*(12/365)*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)+(1+J3-MAX(SI((C6:C100<=J3)*(D6:D100>=J3);C6:C100)))*(12/365)*INDEX(B6:B100;EQUIV(MAX(SI((C6:C100<=J3)*(D6:D100>=J3);C6:C100));C6:C100))))))
Voici le nouveau doc avec la fonction corrigée :
https://www.cjoint.com/?DGpobaedTrm
P.S: ATTENTION au résultat par macro, il ne donne pas du tout le bon chiffre... dans l'exemple que j'ai laissé il donne 218,87 au lieu de 4,99...
En espérant que la formule soit maintenant parfaite ;)
Formule brut :
=SI(J2<C6;"Date en J2 hors données";SI(J3>MAX(C6:D100);"Date en J3 hors données";SI(MAX(SI((C6:C100<J3)*(D6:D100=J3);C6:C100))=MIN(SI((C6:C100=J2)*(D6:D100>J2);C6:C100));J4*(12/365)*INDEX(B6:B100;EQUIV(MIN(SI((C6:C100<=J2)*(D6:D100>=J2);D6:D100));C6:C100));SI(J3=MAX(C6:C100);(1+MIN(SI((C6:C100<=J2)*(D6:D100>=J2);D6:D100))-J2)*(12/365)*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)+(12/365)*INDEX(B6:B100;EQUIV(MAX(SI((C6:C100<=J3)*(D6:D100>=J3);C6:C100));C6:C100));(1+MIN(SI((C6:C100<=J2)*(D6:D100>=J2);D6:D100))-J2)*(12/365)*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)+(1+J3-MAX(SI((C6:C100<=J3)*(D6:D100>=J3);C6:C100)))*(12/365)*INDEX(B6:B100;EQUIV(MAX(SI((C6:C100<=J3)*(D6:D100>=J3);C6:C100));C6:C100))))))
Bonjour Theo.R,
Merci pour la formule, superbe travail, bravo.
En bien oui il y a une erreur dans la procédure, que pajude ma gentiment signalé, je vais la corriger.
Je vous signale au passage que le cas 14.11.2004 au 14.11.2004 soit 1 jour avec votre formule j'ai 50.90 ce qui est visiblement faux.... Elle est répétitive car j'ai omis une condition... !
Ps : en fait selon le voeu de pajude d'avoir le choix et résultat sur une autre feuille, votre formule ne convient pas... !
Salutations.
Le Pingou
Merci pour la formule, superbe travail, bravo.
En bien oui il y a une erreur dans la procédure, que pajude ma gentiment signalé, je vais la corriger.
Je vous signale au passage que le cas 14.11.2004 au 14.11.2004 soit 1 jour avec votre formule j'ai 50.90 ce qui est visiblement faux.... Elle est répétitive car j'ai omis une condition... !
Ps : en fait selon le voeu de pajude d'avoir le choix et résultat sur une autre feuille, votre formule ne convient pas... !
Salutations.
Le Pingou
Il y a encore une petite erreur, quand la date de début n'est pas une date égale à la colonne C.
Ex : du 28/04/2000 au 30/04/2000 = 26,08
30/05/2014 au 31/05/2014 = 5,32
Pour la macro, effectivement il y a aussi une petite erreur que j'ai signalé à Le Pingou
Je suis désolé de vous embêter.
Merci
Ex : du 28/04/2000 au 30/04/2000 = 26,08
30/05/2014 au 31/05/2014 = 5,32
Pour la macro, effectivement il y a aussi une petite erreur que j'ai signalé à Le Pingou
Je suis désolé de vous embêter.
Merci
Décidément !
J'ai (encore) corrigé ma formule pour les erreurs qui m'ont été relevées. N'hésitez pas à me dire s'il y a encore des cas que je n'aurais pas détectés.
Le doc :
https://www.cjoint.com/?DGpoXyYAB2Z
La formule (à valider en matricielle) :
=SI(J2<C6;"Date en J2 hors données";SI(J3>MAX(C6:D100);"Date en J3 hors données";SI(OU(MAX(SI((C6:C100<J3)*(D6:D100=J3);C6:C100))=MIN(SI((C6:C100=J2)*(D6:D100>J2);C6:C100));(MIN(SI((C6:C100>J2)*(D6:D100>J2);C6:C100))>J2)*(MAX(SI((C6:C100<J3)*(D6:D100=J3);D6:D100))=J3));J4*(12/365)*INDEX(B6:B100;EQUIV(MIN(SI((C6:C100<=J2)*(D6:D100>=J2);D6:D100));C6:C100));SI(J3=MAX(C6:C100);(1+MIN(SI((C6:C100<=J2)*(D6:D100>=J2);D6:D100))-J2)*(12/365)*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)+(12/365)*INDEX(B6:B100;EQUIV(MAX(SI((C6:C100<=J3)*(D6:D100>=J3);C6:C100));C6:C100));(1+MIN(SI((C6:C100<=J2)*(D6:D100>=J2);D6:D100))-J2)*(12/365)*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)+(1+J3-MAX(SI((C6:C100<=J3)*(D6:D100>=J3);C6:C100)))*(12/365)*INDEX(B6:B100;EQUIV(MAX(SI((C6:C100<=J3)*(D6:D100>=J3);C6:C100));C6:C100))))))
A très vite,
J'ai (encore) corrigé ma formule pour les erreurs qui m'ont été relevées. N'hésitez pas à me dire s'il y a encore des cas que je n'aurais pas détectés.
Le doc :
https://www.cjoint.com/?DGpoXyYAB2Z
La formule (à valider en matricielle) :
=SI(J2<C6;"Date en J2 hors données";SI(J3>MAX(C6:D100);"Date en J3 hors données";SI(OU(MAX(SI((C6:C100<J3)*(D6:D100=J3);C6:C100))=MIN(SI((C6:C100=J2)*(D6:D100>J2);C6:C100));(MIN(SI((C6:C100>J2)*(D6:D100>J2);C6:C100))>J2)*(MAX(SI((C6:C100<J3)*(D6:D100=J3);D6:D100))=J3));J4*(12/365)*INDEX(B6:B100;EQUIV(MIN(SI((C6:C100<=J2)*(D6:D100>=J2);D6:D100));C6:C100));SI(J3=MAX(C6:C100);(1+MIN(SI((C6:C100<=J2)*(D6:D100>=J2);D6:D100))-J2)*(12/365)*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)+(12/365)*INDEX(B6:B100;EQUIV(MAX(SI((C6:C100<=J3)*(D6:D100>=J3);C6:C100));C6:C100));(1+MIN(SI((C6:C100<=J2)*(D6:D100>=J2);D6:D100))-J2)*(12/365)*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)+(1+J3-MAX(SI((C6:C100<=J3)*(D6:D100>=J3);C6:C100)))*(12/365)*INDEX(B6:B100;EQUIV(MAX(SI((C6:C100<=J3)*(D6:D100>=J3);C6:C100));C6:C100))))))
A très vite,
