Excel - Somme matricielle par année
Résolu
gobman007
-
Mike-31 Messages postés 19571 Date d'inscription Statut Contributeur Dernière intervention -
Mike-31 Messages postés 19571 Date d'inscription Statut Contributeur Dernière intervention -
Bonjour,
Voici mon soucis :
J'ai sous excel :
- en colonne A des dates (format jj/mm/aaaa) avec des années allant de 1950 à 2011,
- en colonne B des nombres.
Je cherche la formule me permettant d'obtenir l'année pour laquelle la somme des nombres de la colonne B est maximale.
Merci de votre aide
Voici mon soucis :
J'ai sous excel :
- en colonne A des dates (format jj/mm/aaaa) avec des années allant de 1950 à 2011,
- en colonne B des nombres.
Je cherche la formule me permettant d'obtenir l'année pour laquelle la somme des nombres de la colonne B est maximale.
Merci de votre aide
A voir également:
- Excel - Somme matricielle par année
- Formule somme excel colonne - Guide
- Liste déroulante excel - Guide
- Excel trier par ordre croissant chiffre - Guide
- Somme si couleur excel - Guide
- Word et excel gratuit - Guide
7 réponses
Salut,
Colles cette formule dans une cellule et formate la cellule en format date
pour analyser la plage A2 à B100, à adapter
=INDEX(A2:B100;EQUIV(MAX(B2:B100);B2:B100;0);1)
Colles cette formule dans une cellule et formate la cellule en format date
pour analyser la plage A2 à B100, à adapter
=INDEX(A2:B100;EQUIV(MAX(B2:B100);B2:B100;0);1)
Bonjour
Mike, sauf erreur, je crois que ta proposition détecte un montant maxi en B, mais pas le total des montants de B correspondant à une année
pour ma part je vois une solution en deux temps:, avec cet exemple à adapter
DE A1 à A1000 les dates
de B1 à B1000 les montants
ne C1et au dessous la liste des années présentes en A
en D1 la formule:
=SOMMEPROD((ANNEE(A1:A1000)=C1)*(B1:B1000))
à tirer sur la hauteur de la liste des années
qui fera le total par année de la colonne B
et ensuite effectivement:
=INDEX(D:D;EQUIV(MAX(D:D);D:D;0);1)
donnera l'année du maxi
il y a peut être moyen de faire ça en une seule fois , mais pour l'instant je ne vois pas.
crdlmnt
ps on obtient le même résultat avec la formule en C1:
=SOMMEPROD((ANNEE($A$1:$A$100)=ANNEE($A1))*($B$1:$B$100))
sur toute la hauteur de la liste en A et en plaçant en D1:
=ANNEE(INDEX(A:A;EQUIV(MAX(C:C);C:C;0);1))
Demandons nous si nous ne sommes pas seuls à comprendre ce que l'on explique?
Mike, sauf erreur, je crois que ta proposition détecte un montant maxi en B, mais pas le total des montants de B correspondant à une année
pour ma part je vois une solution en deux temps:, avec cet exemple à adapter
DE A1 à A1000 les dates
de B1 à B1000 les montants
ne C1et au dessous la liste des années présentes en A
en D1 la formule:
=SOMMEPROD((ANNEE(A1:A1000)=C1)*(B1:B1000))
à tirer sur la hauteur de la liste des années
qui fera le total par année de la colonne B
et ensuite effectivement:
=INDEX(D:D;EQUIV(MAX(D:D);D:D;0);1)
donnera l'année du maxi
il y a peut être moyen de faire ça en une seule fois , mais pour l'instant je ne vois pas.
crdlmnt
ps on obtient le même résultat avec la formule en C1:
=SOMMEPROD((ANNEE($A$1:$A$100)=ANNEE($A1))*($B$1:$B$100))
sur toute la hauteur de la liste en A et en plaçant en D1:
=ANNEE(INDEX(A:A;EQUIV(MAX(C:C);C:C;0);1))
Demandons nous si nous ne sommes pas seuls à comprendre ce que l'on explique?
Salut mon ami,
Si j'ai bien analysé la demande
"Je cherche la formule me permettant d'obtenir l'année pour laquelle la somme des nombres de la colonne B est maximale"
effectivement je recherche la valeur Maxi en B et affiche la date qui s'y rapporte en A
=INDEX(A2:B1000;EQUIV(MAX(B2:B1000);B2:B1000;0);1)
J'ai hésité de donner que l'année
=ANNEE(INDEX(A2:B1000;EQUIV(MAX(B2:B1000);B2:B1000;0);1))
mais j'ai peut être mal compris, à suivre
Si j'ai bien analysé la demande
"Je cherche la formule me permettant d'obtenir l'année pour laquelle la somme des nombres de la colonne B est maximale"
effectivement je recherche la valeur Maxi en B et affiche la date qui s'y rapporte en A
=INDEX(A2:B1000;EQUIV(MAX(B2:B1000);B2:B1000;0);1)
J'ai hésité de donner que l'année
=ANNEE(INDEX(A2:B1000;EQUIV(MAX(B2:B1000);B2:B1000;0);1))
mais j'ai peut être mal compris, à suivre
pas de problème mon ami Mike, mais je crois qu'on attendra le demandeur.
Mon point de vue:
Il parle de dates dans sa liste.J'en ai déduit qu'il pouvait avoir plusieurs montants pour la même année et donc que le montant maximum par an (somme des montants de la même année) n'est pas forcément le maximum des montants en colonne B;
A suivre ??
bien amicalement
Mon point de vue:
Il parle de dates dans sa liste.J'en ai déduit qu'il pouvait avoir plusieurs montants pour la même année et donc que le montant maximum par an (somme des montants de la même année) n'est pas forcément le maximum des montants en colonne B;
A suivre ??
bien amicalement
Merci Vaucluse.
J'avais pensé à une solution du genre ... mais cela m'oblige à passer par une colonne intermédiaire. Ce qui n'est pas très joli ...
J'avais pensé à une solution du genre ... mais cela m'oblige à passer par une colonne intermédiaire. Ce qui n'est pas très joli ...
Re,
Plus simple peut être une mise en forme conditionnelle qui colorisera la date correspondante à la valeur maxi
Avec Xp tu dois être avec Excel 2003
Toujours sur ton exemple en A les dates et en B les valeurs mets en surbrillance la plage ex.A2 à B1000
Format/Mise en forme conditionnelle
choisis la formule est:
et colles cette formule
=B2=MAX($B$2:$B$1000)
Format choisis une couleur
Plus simple peut être une mise en forme conditionnelle qui colorisera la date correspondante à la valeur maxi
Avec Xp tu dois être avec Excel 2003
Toujours sur ton exemple en A les dates et en B les valeurs mets en surbrillance la plage ex.A2 à B1000
Format/Mise en forme conditionnelle
choisis la formule est:
et colles cette formule
=B2=MAX($B$2:$B$1000)
Format choisis une couleur
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
Salut le forum
J'ai pas trouvé mieux moi non plus
En Colonne C1
=ANNEE(A1) à recopier vers le bas sur toute la plage
La formule pour avoir l'année de la somme maximale
Pour la valeur
Mytå
Merci de donner suite à votre question, nous ne sommes pas des robots...
Versions installées [MsProject 2003(FR), Excel 2003-2007(FR)]
J'ai pas trouvé mieux moi non plus
En Colonne C1
=ANNEE(A1) à recopier vers le bas sur toute la plage
La formule pour avoir l'année de la somme maximale
=INDEX(C1:C100;EQUIV(MAX(SOMME.SI(C1:C100;C1:C100;B1:B100));SOMME.SI(C1:C100;C1:C100;B1:B100);0))
Pour la valeur
=MAX(SOMME.SI(C1:C100;C1:C100;B1:B100))Formules matricielles à valider par Ctrl+Shift+Enter
Mytå
Merci de donner suite à votre question, nous ne sommes pas des robots...
Versions installées [MsProject 2003(FR), Excel 2003-2007(FR)]
Merci à tous :)
J'ai ma réponse ... il n'est donc hélas pas possible de faire sans colonne intermédiaire.
gobman007
J'ai ma réponse ... il n'est donc hélas pas possible de faire sans colonne intermédiaire.
gobman007
Re,
Je ne comprends pas tes dires,
"il n'est donc hélas pas possible de faire sans colonne intermédiaire"
Tu n'as pas besoin de colonne intermédiaire, tu as en colonne A tes dates et en colonne B les valeurs, je t'ai donné une réponse avec une mise en forme conditionnelle qui colorise la date colonne A correspondant à la valeur maximale en B
Ensuite plusieurs propositions t'on été faites Vaucluse, Myta, moi même ou il suffit de coller une des formules proposée dans une simple cellule, il n'a jamais été question d'ajouter une colonne intermédiaire
https://www.cjoint.com/?0bslvulqaiJ
Je ne comprends pas tes dires,
"il n'est donc hélas pas possible de faire sans colonne intermédiaire"
Tu n'as pas besoin de colonne intermédiaire, tu as en colonne A tes dates et en colonne B les valeurs, je t'ai donné une réponse avec une mise en forme conditionnelle qui colorise la date colonne A correspondant à la valeur maximale en B
Ensuite plusieurs propositions t'on été faites Vaucluse, Myta, moi même ou il suffit de coller une des formules proposée dans une simple cellule, il n'a jamais été question d'ajouter une colonne intermédiaire
https://www.cjoint.com/?0bslvulqaiJ
Bonjour mon ami Mike
je pense que quelque chose à du t'échapper l:-) :-)
la proposition de Mita et la mienne passe bien par au moins une colonne intermédiaire. Quant à ta proposition, je me répète mais elle donne la valeur maxi sur une ligne, pas l'année pour laquelle la somme des montants de toutes les dates de l'année est maximum
Si tu prends ton exemple, il faut définir quel est le maximum du total des valeurs B pour l'année 2009 ou de celui des valeurs B de l'année 2010 et non pas uniquement le maximum des valeurs B
le demandeur avait confirmé ici ce que je supposais;
https://forums.commentcamarche.net/forum/affich-20556842-excel-somme-matricielle-par-annee#6
Mais rien n'empêche de donner une solution sans colonne si tu en as une.
Bien amicalement
je pense que quelque chose à du t'échapper l:-) :-)
la proposition de Mita et la mienne passe bien par au moins une colonne intermédiaire. Quant à ta proposition, je me répète mais elle donne la valeur maxi sur une ligne, pas l'année pour laquelle la somme des montants de toutes les dates de l'année est maximum
Si tu prends ton exemple, il faut définir quel est le maximum du total des valeurs B pour l'année 2009 ou de celui des valeurs B de l'année 2010 et non pas uniquement le maximum des valeurs B
le demandeur avait confirmé ici ce que je supposais;
https://forums.commentcamarche.net/forum/affich-20556842-excel-somme-matricielle-par-annee#6
Mais rien n'empêche de donner une solution sans colonne si tu en as une.
Bien amicalement
