Recherche

Accélérer la fonction RECHERCHEV

Temps de lecture 3 minutes

Rappel de l'utilisation de RECHERCHEV

La fonction RECHERCHEV peut être utilisée de 2 manières différentes selon la valeur du dernier paramètre

La fonction RECHERCHEV retourne le contenu de la colonne spécifiée

Comment la fonction RECHERCHEV trouve les valeurs ?

Afin de comprendre la méthode pour améliorer la vitesse de calcul de la fonction RECHERCHEV, il faut étudier la façon dont la fonction a été codée par les ingénieurs de Microsoft

Charles Williams , Microsoft MVP, a découvert que la programmation de la fonction RECHERCHEV n'était pas la même selon que vous choisissez la recherche approchante ou la recherche exacte.

  • La méthode de codage utilisée pour la recherche approchante est la méthode binaire
  • La méthode de codage utilisée pour la recherche exacte est la méthode linéaire

Vous comprenez mieux dès lors, pourquoi une recherche exacte est si lente comparée à une recherche codée en binaire. 😉

Comme la fonction RECHERCHEV est exclusivement utilisée pour rechercher une valeur exacte, c'est donc le traitement linéaire qui s'applique. Et quand vous travaillez sur des classeurs de plusieurs dizaines de milliers de lignes, ou même plusieurs centaines de milliers de lignes, le temps de traitement d'exécution peut dépasser plusieurs minutes 😱😡💥🕐🕝🕤😴😴😴

Evolution Microsoft 365 ❗❗❗

Si vous utilisez la version d'Excel avec Microsoft 365, l'algorithme de la fonction a été modifié pour rendre la recherche plus performante. Cette évolution a été mise en ligne en Mai 2019.

Technique pour optimiser la vitesse de recherche

Toute l'astuce va consister à écrire une fonction RECHERCHEV exacte en utilisant la fonction RECHERCHEV approchante 🤨🤔

Exemple sur plusieurs milliers de lignes

Prenons le cas d'un classeur qui contient dans une feuille la liste des 50 000 salariés d'une entreprise

Et dans une autre feuille, vous avez la liste jour de congés pris par vos salariés ce qui représente près de 200,000 lignes.

Le besoin c'est de créer une RECHERCHEV entre les 2 feuilles pour afficher le nom des salariés basée sur le code employé.

Faire un test sur le résultat de la recherche

Nous allons commencer par faire un test pour savoir si le résultat de la recherche retourne exactement le résultat recherché. Oui, c'est un peu con comme test, mais c'est ce qu'il faut faire.

=RECHERCHEV(A2;$A$2:$A$50000;1;1)=A2

Les données de votre colonne de recherche doivent OBLIGATOIREMENT être triées en ordre croissant.

Intégrer ce test dans une fonction SI

Maintenant que nous avons bâti un test qui vérifie si la recherche approchante retourne exactement la valeur que vous voulons, nous allons mettre ce test dans une fonction SI.

Si le résultat du test est vrai, alors nous allons retourner une recherche approchante mais sur la colonne que nous voulons retourner cette fois-ci (4ème colonne ici)

Nous terminons la fonction SI avec une valeur quand la recherche est infructueuse. La formule s'écrit alors :

=SI(RECHERCHEV(A2;Employé!$A$2:$H$50000;1;1)=A2,RECHERCHEV(A2; Employé!$A$2:$H$50000;4;1), "Inconnu")

Formule qui accélère la vitesse de recherche de la fonction RECHERCHEV

Finalement, c'est tout simple et le gain de temps est tout simplement hallucinant 😎👍

Vidéo explicative

Related posts

Fonction INDEX

Frédéric LE GUEN

Comparer 2 colonnes

Frédéric LE GUEN

Fonction RECHERCHEV

Frédéric LE GUEN

12 commentaires

Ades 27/07/2019 at 22:38

Merci pour votre site.
Dans la formule que vous indiquez en blanc sur fond gris foncé
=SI(RECHERCHEV(A2;$A$2:$H$50000;1;1)=A2,RECHERCHEV(A2;$A$2:$H$50000;4;1), "Inconnu")
pour l'égalité de cellules A2, il manque la référence à l'autre feuille de calcul.

Répondre
Marie 18/01/2019 at 17:15

Bonjour Frédéric,
Est-ce que remplacer VLOOKUP par un MATCH imbriqué dans un INDEX accélère la recherche ?
On m'a conseillé cette ruse, mais en lisant votre article, j'ai des doutes.
Cordialement,
Marie

Répondre
Frédéric LE GUEN 18/01/2019 at 19:44

Bonjour,
Mille mercis d'avoir posée la question car c'est un mythe pour beaucoup d'utilisateurs.
Non, le seul moyen d'accélérer une rechercheV c'est l'astuce que je décris dans cet article https://www.excel-exercice.com/accelerer-la-fonction-recherchev/
Mais l'astuce INDEX / EQUIV (enfin MATCH) c'est pour faire une RECHERCHEV vers la gauche https://www.excel-exercice.com/comment-faire-une-recherchev-vers-la-gauche/
Merci bcp pour cette question très pertinente

Répondre
Zako 12/02/2018 at 16:07

Super merci pour l'astuce !

Répondre
Knittel 10/11/2017 at 21:43

Vraiment Efficace, testé sur un onglet avec 60.000 références à rechercher dans un onglet avec 500.000 références... 10 mn avec recherchev classique, et 15 sec avec cette méthode.

Répondre
hh 18/04/2017 at 16:24

Bonjour

Je pensais que la recherche approximative retournait toujours la valeur la plus proche.

Or je m'aperçois que dans certains cas, elle retourne #N/A comme dans une recherche exacte.
Il semblerait que ce soit dans le cas où ma valeur cherchée est inférieure (en tri) à ma première
valeur du tableau

Avez-vous déja recontré ce problème?

PS: je peux résoudre par:
si(esterreur(recherchev(a;tb;1;vrai);"erreur";si(recherchev(a;tb;1;vrai)=a;recherchev(a;tb;xx;vrai);"erreur"))
mais cela devient lourd comme formule

Par avance merci

Répondre
Frédéric LE GUEN 24/04/2017 at 08:48

Attention, il y a 2 choses à ne pas confondre. Là vous êtes sur l'article sur le fait d'accélérer la vitesse de recherche dans le cas d'une recherche exacte (et la bidouille c'est d'utiliser la recherche approchante c'est vrai). Maintenant, si votre besoin c'est de faire une recherche approchante "classique", la valeur min doit obligatoirement être dans votre tableau de référence alors que la valeur max peut être omise.

Répondre
Valérie 21/09/2015 at 12:29

Bonjour et merci pour cette astuce qui accélère spectaculairement les formules !
Je me permets une remarque : il est indispensable que les cellules aient le même format, plus précisément la même longueur.
Une question reste en suspens : l'enregistrement d'un tel fichier est très long. Je ne me l'explique pas.
Cordialement,

Valérie

Répondre
Polo 03/07/2015 at 16:26

Hallucinant que Microsoft ne peut pas integrer ce test directement dans sa fonction RECHERCHEV
Quelle bouse !

Répondre
DARMON Henri 17/06/2015 at 08:53

J'ai l'impression de revenir 20 ans en arrière lorsque le paramètre 0 ou faux n'existait pas !!!

Répondre
Das Michel 19/10/2014 at 07:01

Effectivement, c'est simple et efficace comme technique. Merci pour l'information. Je cherche comment accélérer l'ouverture de mon Excel (passer de 15 secondes à 1 ou 2 secondes) (fichier avec plusieurs centaines de champs et liaisons pages BD client - BD pièce avec gestion de stock (2000 références) - Facturation.

Répondre

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

Ce site utilise des cookies pour améliorer votre expérience et vos recherches. Nous pensons que vous êtes dʼaccord sur ce principe mais vous pouvez refuser cette option. Accepter Continuer

Privacy & Cookies Policy