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)