Trouver la n-ième plus grande valeur avec un critère

Nous avons déjà vu qu’il était possible de faire une RECHERCHEV sur la 2ème valeur, nous avons même vu 2 méthodes : la première en décomposant le calcul avec des colonnes intermédiaires, la seconde directe en une seule formule.
Dans les deux cas, le tableau de référence n’était pas trié et nous prenions donc les valeurs dans l’ordre où elles étaient renseignées. Voyons comment à partir de cette même liste récupérer la 1ère, 2ème ou n-ième plus grande valeur avec une condition.


 

Présentation de notre exemple

Nous conservons le même contexte : une liste de commandes avec le commercial, les quantités et le montant et une zone de recherche où nous entrons un nom et un numéro pour la commande cherchée.

Liste commandes non triee

Cette fois, nous ne nous contentons pas de prendre une occurrence dans la liste. Par exemple, ci-dessus la 3ème commande trouvée de Benjamin Besson.
Nous allons chercher la 3ème plus grosse commande de Benjamin Besson en termes de chiffre d’affaires.

 

Récupérer le montant de la commande

Découverte de la formule

Nous trions les commandes sur leur montant, commençons donc par la formule pour récupérer le chiffre d’affaires.

Formule recherche n-ieme valeur condition
 

Explications de la formule

Détaillons le calcul pas-à-pas pour bien comprendre cette formule. Comme d’habitude, nous attaquons avec la partie la plus imbriquée.

SI($A$2:$A$27=$E2;C$2:C$27;0) : la fonction permet de vérifier le nom du commercial sur toute la plage (lignes 2 à 27), si le nom est celui cherché, on prend en compte le montant, sinon on prend une valeur à 0.

GRANDE.VALEUR( SI(...) ;$F2) : ensuite on cherche la n-ième plus grande valeur, celle précisée en F2, dans cette plage que nous venons de créer et composée des montants et de zéros.

Comme on utilise une plage de cellules dans la fonction SI, il s’agit d’une formule matricielle que nous validons par Ctrl+Maj+Entrée.

Resultat recherche n-ieme valeur condition

En triant et filtrant la plage, on vérifie que le résultat est bien correct.

Verification recherche n-ieme valeur condition

Récupérer les quantités

Pour les quantités, ça dépend de ce que nous voulons obtenir.

Si nous recopions la formule ci-dessus dans la case des quantités, la valeur 17 est renvoyée.

Recopie formule n-ieme valeur autre champ

Il s’agit de la 3ème plus grande valeur en termes de quantités commandées. Mais ça ne correspond pas aux quantités de la commande à 1 314€.

Tri champ supplementaire ou valeur correspondante

Si nous voulons que les deux valeurs soient cohérentes, les quantités liées au montant affiché, il suffit de faire une RECHERCHEV sur le montant.
Dans ce cas, nous sommes confrontés à un autre problème. Les quantités sont situées à gauche des montants, toute RECHERCHEV est donc impossible. Il nous faut passer par les fonctions INDEX et EQUIV pour éviter de reclasser les colonnes.

Recuperation valeur correspondante fonctions index equiv
 

Quelques liens utiles

Pour terminer, voici quelques articles qui peuvent vous être utiles pour bien appréhender la formule ainsi que l’alternative à RECHERCHEV :

Dites-nous en commentaires si cette formule vous est utile et laissez vos questions si besoin.

 

Abonnez-vous à la newsletter

Illustration couverture ebook 100 trucs et astuces pour Excel-ler au travail

Et recevez gratuitement l'ebook 100 trucs et astuces pour Excel-ler au travail (5€ sur Amazon)

Votre adresse mail ne sera pas communiquée et vous pourrez vous désabonner à tout moment.

Taggé , , .Mettre en favori le Permaliens.

Laisser un commentaire

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.