RECHERCHEV 2ème valeur sans ajout de colonne

Dans notre précédent article, nous avons vu comment récupérer la 2ème valeur avec une RECHERCHEV. Plus généralement, cette méthode fonctionnait pour toute valeur quel que soit le nombre d’occurrences dans la liste de références.
Si vous cherchez une méthode plus directe, sans étape intermédiaire, cet article est pour vous.


 

Présentation du contexte

Nous reprenons le même exemple de départ que la dernière fois. Une liste de commandes prises par des commerciaux avec les quantités et le CA associés.

Tableau RECHERCHEV 2eme valeur

On prévoit aussi un champ dans lequel faire figurer le nom du commercial et le numéro de l’occurrence cherchée.

Criteres RECHERCHEV 2eme valeur
 

Présentation de la formule

Comme souvent, la fonction RECHERCHEV elle-même est trop limitée pour un tel problème. Comme nous l’avions déjà vu, nous nous tournons vers la fonction INDEX comme alternative.

Le tableau des commandes s’étendant de la ligne 2 à la ligne 27, nous utilisons la formule suivante pour récupérer les quantités. Elle peut être recopiée telle qu’elle pour obtenir le CA également.
Ce n’est pas visible dans le mode édition ci-dessous mais il s’agit d’une formule matricielle, donc validée avec Ctrl+Maj+Entrée.

Formule matricielle RECHERCHEV 2eme valeur
 

Explications de la formule

Décortiquons la formule pour bien la comprendre en commençant par la partie la plus imbriquée.

SI($A$2:$A$27=$E2;LIGNE($A$2:$A$27);NA()) : on s’intéresse à la plage des noms de commerciaux et on vérifie pour quelles lignes le nom correspond bien à celui placé en E2. Si le nom est le même, on renvoie le numéro de la ligne sinon on force une erreur #N/A avec la fonction NA().

AGREGAT(15;6; SI(...) ;$F2) : on utilise la fonction AGREGAT, pour faire appel à une autre fonction statistique et gérer des valeurs particulières comme les erreurs. Ici on appelle la fonction 15, c’est-à-dire PETITE.VALEUR avec l’option 6 pour ignorer les erreurs dans le calcul.
On donne ensuite la plage calculée ci-dessus et la cellule F2 pour déterminer le rang. Les erreurs #N/A étant ignorées, le calcul se fait uniquement avec les lignes où le nom du commercial est le bon. Et on récupère ainsi le numéro de la ligne à afficher.

INDEX(B$2:B$27; AGREGAT(...) -LIGNE($A$1)) : on termine avec la fonction INDEX pour renvoyer les quantités. La fonction AGREGAT à l’étape précédente nous renvoie le numéro de ligne au sein de la feuille de calcul pour le transposer au sein du tableau, on soustrait le numéro de ligne de l’en-tête de ce tableau.

Comme déjà évoqué dans la première partie, c’est une formule matricielle, on la valide donc avec Ctrl+Maj+Entrée.

Pour la rendre plus lisible, on peut aussi intégrer la liste des commandes dans un tableau. Ci-dessous, la formule corrigée avec un tableau nommé ListeCdes.

Formule tableau Excel RECHERCHEV 2eme valeur

Comme avec la première méthode, on peut vérifier que les formules fonctionnent quels que soient le nom et le numéro.

Resultats RECHERCHEV nieme valeur
 

Cette méthode est plus rapide que celle de la dernière fois. Pas besoin de décomposer le calcul, on trouve directement. Mais la formule est plus complexe.
Utilisez celle que vous voulez, ce qui compte c’est que vous compreniez comment ça marche et que vous soyez capable de la refaire en toutes circonstances.
Si vous avez des questions sur cette nouvelle formule, laissez-les en commentaires !

 

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.