Excel - Sous-total selon un periode de temps
CharlesAU
-
gbinforme Messages postés 15481 Date d'inscription Statut Contributeur Dernière intervention -
gbinforme Messages postés 15481 Date d'inscription Statut Contributeur Dernière intervention -
Salut,
Je n'arrive pas a resoudre un casse-tete avec Excel.
Je cherche a faire la somme de plusieurs valeurs (revenus) selon une certaine periode a savoir 12 mois avant chaque valeur.
Par exemple :
Closed date Revenue Colonne3
5/3/1999 $6,000.00 ?
5/4/1999 $6,000.00 ?
5/5/1999 $6,000.00 ?
5/6/1999 $6,000.00 ?
5/7/1999 $6,000.00 ?
5/8/1999 $6,000.00 ?
5/9/1999 $6,000.00 ?
5/10/2000 $6,000.00 ?
5/11/2000 $6,000.00 ?
5/12/2000 $6,000.00 ?
5/13/2000 $6,000.00 ?
5/14/2002 $6,000.00 ?
5/15/2002 $6,000.00 ?
5/16/2003 $6,000.00 ?
5/17/2003 $6,000.00 ?
5/18/2003 $6,000.00 ?
J'aimerai donc reussir a faire la somme, par exemple sur la ligne de la date 5/14/2002, des revenus sur les 12 derniers mois. Un genre de subtotal sur les 12 derniers mois par rapport a chaque ligne.
J'imagine qu'il y a du SOMME.SI ou SOMME + DATVAL la dedans mais apres de nombreux essais, je n'y suis toujours pas parvenu.
Merci d'avance pour votre aide.
Charles.
Je n'arrive pas a resoudre un casse-tete avec Excel.
Je cherche a faire la somme de plusieurs valeurs (revenus) selon une certaine periode a savoir 12 mois avant chaque valeur.
Par exemple :
Closed date Revenue Colonne3
5/3/1999 $6,000.00 ?
5/4/1999 $6,000.00 ?
5/5/1999 $6,000.00 ?
5/6/1999 $6,000.00 ?
5/7/1999 $6,000.00 ?
5/8/1999 $6,000.00 ?
5/9/1999 $6,000.00 ?
5/10/2000 $6,000.00 ?
5/11/2000 $6,000.00 ?
5/12/2000 $6,000.00 ?
5/13/2000 $6,000.00 ?
5/14/2002 $6,000.00 ?
5/15/2002 $6,000.00 ?
5/16/2003 $6,000.00 ?
5/17/2003 $6,000.00 ?
5/18/2003 $6,000.00 ?
J'aimerai donc reussir a faire la somme, par exemple sur la ligne de la date 5/14/2002, des revenus sur les 12 derniers mois. Un genre de subtotal sur les 12 derniers mois par rapport a chaque ligne.
J'imagine qu'il y a du SOMME.SI ou SOMME + DATVAL la dedans mais apres de nombreux essais, je n'y suis toujours pas parvenu.
Merci d'avance pour votre aide.
Charles.
A voir également:
- Excel - Sous-total selon un periode de temps
- Liste déroulante excel - Guide
- Formule total excel - Guide
- Word et excel gratuit - Guide
- Trier un tableau excel - Guide
- Si ou excel - Guide
14 réponses
bonjour
Dans ta colonne 3 tu mets en C1 la formule matricielle suivante à valider par CTRL + MAJ + ENTER
et tu tires ta formule vers le bas avec la petite croix en bas à droite de C1.
Dans ta colonne 3 tu mets en C1 la formule matricielle suivante à valider par CTRL + MAJ + ENTER
=SOMME(($A$1:$A$65535<=A1)*($A$1:$A$65535>A1-365)*($B$1:$B$65535))
et tu tires ta formule vers le bas avec la petite croix en bas à droite de C1.
Ca ne fonctionne toujours pas. J'ai simplement copiée-collée ta formule dans C2 (C1 étant l'en-tete de ma colonne) et j'ai donc changé les A1 et B1 en A2 et B2 afin que ça corresponde mais ça ne marche pas. Puis j'ai validé CTRL+MAJ+ENTREE.
Voici le résultat:
Closed date Revenue TTM
03/05/1999 $6 000,00 6 000,00
04/05/1999 $6 000,00 6 000,00
05/05/1999 $6 000,00 6 000,00
06/05/1999 $6 000,00 6 000,00
07/05/1999 $6 000,00 6 000,00
08/05/1999 $6 000,00 6 000,00
09/05/1999 $6 000,00 6 000,00
10/05/1999 $6 000,00 6 000,00
11/05/1999 $6 000,00 6 000,00
12/05/1999 $6 000,00 6 000,00
13/05/1999 $6 000,00 6 000,00
14/05/1999 $6 000,00 6 000,00
15/05/1999 $6 000,00 6 000,00
16/05/1999 $6 000,00 6 000,00
17/05/1999 $6 000,00 6 000,00
18/05/1999 $6 000,00 6 000,00
Etc...........................................
Donc ça ne fait pass encore de sous-total. Aurais-tu une autre idée?
Ceci étant, merci bcp pour ton entraide et ta réponse si rapide.
Charles.
Voici le résultat:
Closed date Revenue TTM
03/05/1999 $6 000,00 6 000,00
04/05/1999 $6 000,00 6 000,00
05/05/1999 $6 000,00 6 000,00
06/05/1999 $6 000,00 6 000,00
07/05/1999 $6 000,00 6 000,00
08/05/1999 $6 000,00 6 000,00
09/05/1999 $6 000,00 6 000,00
10/05/1999 $6 000,00 6 000,00
11/05/1999 $6 000,00 6 000,00
12/05/1999 $6 000,00 6 000,00
13/05/1999 $6 000,00 6 000,00
14/05/1999 $6 000,00 6 000,00
15/05/1999 $6 000,00 6 000,00
16/05/1999 $6 000,00 6 000,00
17/05/1999 $6 000,00 6 000,00
18/05/1999 $6 000,00 6 000,00
Etc...........................................
Donc ça ne fait pass encore de sous-total. Aurais-tu une autre idée?
Ceci étant, merci bcp pour ton entraide et ta réponse si rapide.
Charles.
bonjour
Tu peux copier et mettre ici la formule que tu as en C2 donc car ce n'est pas possible que cela te donnes toujours le même résultat.
Tu peux copier et mettre ici la formule que tu as en C2 donc car ce n'est pas possible que cela te donnes toujours le même résultat.
Bonjour,
Voici la formule que j'ai en C2 :
=SUM(($A$2:$A$65535<=A2)*($A$2:$A$65535>A2-365)*($B$2:$B$65535))
Finalement je viens d'y retravailler et quelque chose vient de changer. En revanche ca ne m'a pas l'air juste niveau resultat. Car au bout de quelques annees et la somme reste la meme jusqu'a la fin. Est-ce car c'est limite a 65535 ici dans la formule?
Merci encore pour ton aide.
Charles.
Voici la formule que j'ai en C2 :
=SUM(($A$2:$A$65535<=A2)*($A$2:$A$65535>A2-365)*($B$2:$B$65535))
Finalement je viens d'y retravailler et quelque chose vient de changer. En revanche ca ne m'a pas l'air juste niveau resultat. Car au bout de quelques annees et la somme reste la meme jusqu'a la fin. Est-ce car c'est limite a 65535 ici dans la formule?
Merci encore pour ton aide.
Charles.
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
Re,
Je viens d'essayer d'integrer la formule dans un autre document et ca ne marche pas, detectant une erreur du type #VALUE
Ma formule est la suivante : =SUM(($F$4:$F$65535<=F4)*($F$4:$F$65535>F4-365)*($N$4:$N$65535))
Les colonnes sont telles:
Close Date (Collone F)
3/5/1999
3/5/1999
3/5/1999
3/10/1999
3/10/1999
3/19/1999
3/19/1999
3/19/1999
3/19/1999
3/21/1999
Revenue US$ (Colonne O)
$3,630.00
$545.00
$750.00
$769.00
$855.00
$5,500.00
$4,800.00
$720.00
$825.00
$995.00
TTM (COLONNE SUR LAQUELLE APPLIQUER LA FORMULE) (Colonne P)
$3,630.00
$545.00
$750.00
$769.00
$855.00
$5,500.00
$4,800.00
$720.00
$825.00
$995.00
Comme tu peux le constater, ca reproduit simplement la colonne O en colonne P.
Et des que je valide la formule par CTRL+SHIFT+ENTER, le resulstat est le suivant:
TTM
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
Merci d'avance pour ton aide.
Charles.
Je viens d'essayer d'integrer la formule dans un autre document et ca ne marche pas, detectant une erreur du type #VALUE
Ma formule est la suivante : =SUM(($F$4:$F$65535<=F4)*($F$4:$F$65535>F4-365)*($N$4:$N$65535))
Les colonnes sont telles:
Close Date (Collone F)
3/5/1999
3/5/1999
3/5/1999
3/10/1999
3/10/1999
3/19/1999
3/19/1999
3/19/1999
3/19/1999
3/21/1999
Revenue US$ (Colonne O)
$3,630.00
$545.00
$750.00
$769.00
$855.00
$5,500.00
$4,800.00
$720.00
$825.00
$995.00
TTM (COLONNE SUR LAQUELLE APPLIQUER LA FORMULE) (Colonne P)
$3,630.00
$545.00
$750.00
$769.00
$855.00
$5,500.00
$4,800.00
$720.00
$825.00
$995.00
Comme tu peux le constater, ca reproduit simplement la colonne O en colonne P.
Et des que je valide la formule par CTRL+SHIFT+ENTER, le resulstat est le suivant:
TTM
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
Merci d'avance pour ton aide.
Charles.
Re,
Je viens d'essayer d'integrer la formule dans un autre document et ca ne marche pas, detectant une erreur du type #VALUE
Ma formule est la suivante : =SUM(($F$4:$F$65535<=F4)*($F$4:$F$65535>F4-365)*($N$4:$N$65535))
Les colonnes sont telles:
Close Date (Collone F)
3/5/1999
3/5/1999
3/5/1999
3/10/1999
3/10/1999
3/19/1999
3/19/1999
3/19/1999
3/19/1999
3/21/1999
Revenue US$ (Colonne O)
$3,630.00
$545.00
$750.00
$769.00
$855.00
$5,500.00
$4,800.00
$720.00
$825.00
$995.00
TTM (COLONNE SUR LAQUELLE APPLIQUER LA FORMULE) (Colonne P)
$3,630.00
$545.00
$750.00
$769.00
$855.00
$5,500.00
$4,800.00
$720.00
$825.00
$995.00
Comme tu peux le constater, ca reproduit simplement la colonne O en colonne P.
Et des que je valide la formule par CTRL+SHIFT+ENTER, le resulstat est le suivant:
TTM
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
Merci d'avance pour ton aide.
Charles.
Je viens d'essayer d'integrer la formule dans un autre document et ca ne marche pas, detectant une erreur du type #VALUE
Ma formule est la suivante : =SUM(($F$4:$F$65535<=F4)*($F$4:$F$65535>F4-365)*($N$4:$N$65535))
Les colonnes sont telles:
Close Date (Collone F)
3/5/1999
3/5/1999
3/5/1999
3/10/1999
3/10/1999
3/19/1999
3/19/1999
3/19/1999
3/19/1999
3/21/1999
Revenue US$ (Colonne O)
$3,630.00
$545.00
$750.00
$769.00
$855.00
$5,500.00
$4,800.00
$720.00
$825.00
$995.00
TTM (COLONNE SUR LAQUELLE APPLIQUER LA FORMULE) (Colonne P)
$3,630.00
$545.00
$750.00
$769.00
$855.00
$5,500.00
$4,800.00
$720.00
$825.00
$995.00
Comme tu peux le constater, ca reproduit simplement la colonne O en colonne P.
Et des que je valide la formule par CTRL+SHIFT+ENTER, le resulstat est le suivant:
TTM
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
#VALUE!
Merci d'avance pour ton aide.
Charles.
bonjour
Est-ce car c'est limite a 65535 ici dans la formule?
C'est le nombre maximum de lignes - 1, sur un classeur Excel sauf si tu as 2007 et auquel cas tu peux doubler.
Dans cette formule, la colonne date est la colonne A et la colonne B est la colonne à additionner, mais tu peux la mettre sur n'importe quelles colonnes.
Si tu obtiens #VALUE! c'est sans doute que tu ne saisis pas correctement tes touches : il faut avoir un doigt sur ctrl enfoncé, un autre sur shift ou maj enfoncé et seulement alors appuyer sur entrée.
Est-ce car c'est limite a 65535 ici dans la formule?
C'est le nombre maximum de lignes - 1, sur un classeur Excel sauf si tu as 2007 et auquel cas tu peux doubler.
SOMME(($A$1:$A$65535<=A1)*($A$1:$A$65535>A1-365)*($B$1:$B$65535))
Dans cette formule, la colonne date est la colonne A et la colonne B est la colonne à additionner, mais tu peux la mettre sur n'importe quelles colonnes.
Si tu obtiens #VALUE! c'est sans doute que tu ne saisis pas correctement tes touches : il faut avoir un doigt sur ctrl enfoncé, un autre sur shift ou maj enfoncé et seulement alors appuyer sur entrée.
Effectivement j'ai Excel 2007. Mais j'ai seulement 1700 lignes donc le probleme ne vient pas de la.
J'ai reessayer en faisant attention a bien saisir les touches et aucune difference, #VALUE! apparait toujours.
Y'aurait-il un probleme dans le format d'une de mes colonnes? Apparemment non.
Ai-je la possibilite de t'envoyer un echantillon de ce document excel?
PS: je pense que ca n'a aucune importance mais ma version d'Excel est une version anglaise donc SOMME devient SUM mais sans importance je suppose.
J'ai reessayer en faisant attention a bien saisir les touches et aucune difference, #VALUE! apparait toujours.
Y'aurait-il un probleme dans le format d'une de mes colonnes? Apparemment non.
Ai-je la possibilite de t'envoyer un echantillon de ce document excel?
PS: je pense que ca n'a aucune importance mais ma version d'Excel est une version anglaise donc SOMME devient SUM mais sans importance je suppose.
bonjour
Ai-je la possibilite de t'envoyer un echantillon de ce document excel?
Oui bien sûr mais pour que je puisses le lire il faut le sauvegarder en .xls (excel 97-2003 sur ta version je crois)
Tu mets l'échantillon sur https://www.cjoint.com/ et tu donnes le code obtenu sur ce site.
Ai-je la possibilite de t'envoyer un echantillon de ce document excel?
Oui bien sûr mais pour que je puisses le lire il faut le sauvegarder en .xls (excel 97-2003 sur ta version je crois)
Tu mets l'échantillon sur https://www.cjoint.com/ et tu donnes le code obtenu sur ce site.
bonjour
Regardes le magnifique montant que tu as en ligne 382 : le cumul de S$ n'est pas supporté.
une fois corrigé cela fonctionne normalement.
Regardes le magnifique montant que tu as en ligne 382 : le cumul de S$ n'est pas supporté.
une fois corrigé cela fonctionne normalement.
Rebonjour,
Je reviens vers toi car je viens de detecter un autre probleme dans ma fauille de calcul excel.
Par rapport a ce TTM et la formule matricielle, si j'applique un filtre sur une autre colonne (une colonne Probabilite que j'ai dans cette meme feuille de calcul), la colonne TTM ne s'y adapte pas, rien ne change. Donc les sous-totaux deviennent tout d'un coup faux alors que sans filtre c'est parfait.
Saurais-tu pourquoi????
Merci d'avance et dsl de t'embeter une fois de plus!
Charles.
Je reviens vers toi car je viens de detecter un autre probleme dans ma fauille de calcul excel.
Par rapport a ce TTM et la formule matricielle, si j'applique un filtre sur une autre colonne (une colonne Probabilite que j'ai dans cette meme feuille de calcul), la colonne TTM ne s'y adapte pas, rien ne change. Donc les sous-totaux deviennent tout d'un coup faux alors que sans filtre c'est parfait.
Saurais-tu pourquoi????
Merci d'avance et dsl de t'embeter une fois de plus!
Charles.
bonjour
la colonne TTM ne s'y adapte pas, rien ne change
La formule TTM prend comme matrice l'ensemble de la colonne et en fait le filtre n'agit que comme "masquage" de certaines lignes ce qui ne change en rien la totalisation de la matrice.
Donc les sous-totaux deviennent tout d'un coup faux alors que sans filtre c'est parfait.
Ce n'est pas tout à fait exact puisque comme je viens de le dire ce ne sont pas à proprement parler des sous-totaux.
Si tu veux des sous-totaux qui évoluent avec les filtres, il faut utiliser la fonction qui se trouve dans le menu données et sous-totaux.
Il n'y a pas de colonne supplémentaire mais une ligne supplémentaire est créée au changement de critère.
Dans ce cas il faut absolument que tes données soient triées selon les bons critères alors que la formule actuelle fonctionne quel que soit le tri.
la colonne TTM ne s'y adapte pas, rien ne change
La formule TTM prend comme matrice l'ensemble de la colonne et en fait le filtre n'agit que comme "masquage" de certaines lignes ce qui ne change en rien la totalisation de la matrice.
Donc les sous-totaux deviennent tout d'un coup faux alors que sans filtre c'est parfait.
Ce n'est pas tout à fait exact puisque comme je viens de le dire ce ne sont pas à proprement parler des sous-totaux.
Si tu veux des sous-totaux qui évoluent avec les filtres, il faut utiliser la fonction qui se trouve dans le menu données et sous-totaux.
Il n'y a pas de colonne supplémentaire mais une ligne supplémentaire est créée au changement de critère.
Dans ce cas il faut absolument que tes données soient triées selon les bons critères alors que la formule actuelle fonctionne quel que soit le tri.
