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.

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.

 

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.

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

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.

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€.

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.

 

Quelques liens utiles

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

• C’est quoi une formule matricielle ?
• Classer des valeurs à l’aide de fonctions
• Oubliez RECHERCHEV, place à INDEX et EQUIV

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