Monsieur Excel
Pour tout savoir faire sur Excel !

18 octobre 2016

La somme des deux derniers

Une des dernières colles posées à la sagacité des MVP Excel était la suivante. Vous avez en colonne A des noms et en colonne B des montants. Vous désirez récupérer en colonne G, pour chaque personne, la somme des deux derniers montants qui lui sont associés.

La solution proposée ci-dessous est le résultat conjugué des contributions de Craig Hatmaker, Peter Bartholomew et Crispo Mwangi, avec un petit coup de main de votre serviteur.


Nous avons mis en colonne C la position de la ligne pour chacun des trois héros, avec la formule suivante en C2, recopiée ensuite vers le bas : =nb.si($A$2:A2;A2).

La formule la plus compliquée est celle de D2, recopiée elle aussi vers le bas. Cette formule doit indiquer le montant placé en colonne B si ce montant est un des deux derniers montants listés pour la personne dont le nom est en colonne A. La voici :

=si(ou(grande.valeur(($C$2:$C$14)*($A$2:$A$14=A2);{1;2})=C2);B2;"")

Cette formule est matricielle, donc validée avec [Ctrl]-[Maj]-[Entrée].

Son principe est le suivant : on récupère le montant en B2 si, pour les lignes où l’on a la même personne, la position est l’une des deux les plus grandes ; sinon, on ne met rien…

Quand on est en D7, la formule ($C$2:$C$14)*($A$2:$A$14=A7) s’évalue en {0;1;0;2;0;3;0;4;0;0;0;0;0}, c’est-à-dire la liste des positions pour Paul.

Pour terminer, la formule de G2, recopiée elle aussi vers le bas, est toute simple :

=somme.si(A:A;F2;D:D)

7 Commentaire(s):

  • Bonjour

    Si on est limité aux 2 derniers : (sans colonnes intermédiaires)


    = INDEX($B$2:$B$14;GRANDE.VALEUR((LIGNE($A$2:$A$14))*($A$2:$A$14=F2);1 )-1 )+ INDEX($B$2:$B$14;GRANDE.VALEUR((LIGNE($A$2:$A$14))*($A$2:$A$14=F2);2 )-1 )

    By Blogger Arnaud Cottin, sur 5:44 PM  

  • Bonjour,

    Si on peut rajouter des colonnes intermédiaires, on peut faire simplement :
    - pour Jean : dans la cellule C2, on met la formule : =SI($F$2=A2;CELLULE("ligne";A2);0) et on la recopie vers le bas.

    Pour additionner les 2 derniers résultats de Jean : on inscrit en G2 : =INDEX(A2:B14;EQUIV(GRANDE.VALEUR(C2:C14;1);C2:C14;0);2)+INDEX(A2:B14;EQUIV(GRANDE.VALEUR(C2:C14;2);C2:C14;0);2)

    Pour Marie : on reprend la formule C2 en D2 en la modifiant =SI($F$3=A2;CELLULE("ligne";A2);0) et on la recopie vers le bas.
    Pour les 2 derniers résultats : en G3 : =INDEX(A2:B14;EQUIV(GRANDE.VALEUR(D2:DC14;1);D2:D14;0);2)+INDEX(A2:B14;EQUIV(GRANDE.VALEUR(D2:D14;2);D2:D14;0);2)

    Même logique pour Paul.

    By Blogger Pierre AUTHIER, sur 1:07 PM  

  • Bonjour Arnaud,

    Si on veut garder la même logique, il faut ajouter des conditions. Toujours sans colonne supplémentaire.
    =SI(NB.SI($A$2:$A$14;$F2)=0;0;SI(NB.SI($A$2:$A$14;$F2)>1;INDEX($B$2:$B$14;GRANDE.VALEUR(LIGNE($A$2:$A$14)*($A$2:$A$14=F2);1 )-1 )+ INDEX($B$2:$B$14;GRANDE.VALEUR(LIGNE($A$2:$A$14)*($A$2:$A$14=F2);2 )-1 );INDEX($B$2:$B$14;GRANDE.VALEUR(LIGNE($A$2:$A$14)*($A$2:$A$14=F2);1 )-1 )))
    Formule matricielle

    By Anonymous Anonyme, sur 4:06 PM  

  • Pour gérer les cas d'erreurs si le nom n'est pas présent ou présent qu'une fois :
    = SIERREUR(INDEX($B$2:$B$14;GRANDE.VALEUR((LIGNE($A$2:$A$14))*($A$2:$A$14=F2);1 )-1 );0)+ SIERREUR(INDEX($B$2:$B$14;GRANDE.VALEUR((LIGNE($A$2:$A$14))*($A$2:$A$14=F2);2 )-1 );0)

    By Blogger Arnaud Cottin, sur 12:55 AM  

  • @ Arnaud Cottin (premier message)

    Excellente solution Arnaud !

    Du coup, si l'on veut la formule la plus courte possible, la suivante (matricielle elle aussi) marchera si les noms de la liste n'apparaissent pas plus loin dans la colonne A :
    =INDEX(B:B,LARGE(ROW(A:A)*(A:A=F2),1))+ INDEX(B:B,LARGE(ROW(A:A)*(A:A=F2),2))

    By Blogger Hervé Thiriez, sur 2:41 PM  

  • Pardon, la voici en français (c'est bien plus court en anglais !):
    =INDEX(B:B;GRANDE.VALEUR(LIGNE(A:A)*(A:A=F2);1))+ INDEX(B:B;GRANDE.VALEUR(LIGNE(A:A)*(A:A=F2);2))

    By Blogger Hervé Thiriez, sur 2:43 PM  

  • stephan brizoua

    Bonjour,

    Je pense que la formule suivante (excel anglais) à mettre en G1 pour Jean et en ensuite à recopier pour les deux autres permet d'effectuer le calcul sans colonnes intermédiaires.

    =LOOKUP(2;1/(($A$2:$A$14=F2));$B$2:$B$14)+INDEX($B$2:$B$14;AGGREGATE(14;6;(ROW($A$2:$A$14)-ROW($A$2)+1)*($A$2:$A$14=$F2);2);1)

    Je donnerai les explications plus tard

    By Blogger Stéphane Brizoua, sur 11:21 AM  

Enregistrer un commentaire

<< Accueil