«

»

Accélérer la fonction RECHERCHEV

La fonction RECHERCHEV est une fonction très utile dans Excel : elle permet de retrouver des informations liées à un identifiant, un peu comme c’est le cas dans une base de données. Seulement, quand le nombre de lignes augmente sensiblement, la fonction RECHERCHEV retourne le résultat attendu après de très longues secondes, voire minutes.

Dans cet article, je vais vous montrer une technique qui vous permettra de réduire le temps de calcul à une fraction de seconde.

Présentation du problème


La fonction RECHERCHEV peut être utilisée de 2 manières différentes selon le fait que le dernier paramètre soit positionné à 1 (ou VRAI) pour une recherche approchante, ou à 0 (ou FAUX) pour une recherche exacte.

Quand la recherche s’effectue sur un tableau qui contient une centaine de lignes, vous ne constatez pas de délai important pour le traitement du résultat mais 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 peut dépasser plusieurs minutes. Non seulement cela bloque votre ordinateur mais en plus cela vous fait perdre un temps précieux dans votre analyse.

Une technique proposée par Charles Williams vous permet de lancer une recherche exacte en quelques secondes sur de grandes plages de données.

Approche

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. Charles Williams 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.

Fort de ce constat, toute l’astuce va consister à coder une fonction RECHERCHEV exacte en utilisant la fonction RECHERCHEV approchante.

Méthode


En positionnant le 4ème paramètre à VRAI, la fonction RECHERCHEV va retourner tous les résultats approchants. Par contre, si nous faisons un simple test entre le résultat de la fonction RECHERCHEV et la valeur elle-même recherchée, alors les possibilités sont déjà beaucoup plus intéressantes.

Prenons le cas de figure suivant où nous avons en colonne A l’identifiant et en colonne B, C et D les valeurs relatives à l’identifiant. Notre test va donc consister à vérifier que le résultat de notre recherche est égal à celui contenu en colonne A

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

Maintenant que nous avons un bâti un test qui nous permet de déterminer si la recherche approchante retourne malgré tout une valeur exacte, nous allons mettre ce test dans une fonction SI.

Si le résultat du test est vrai, alors nous allons refaire une recherche approchante mais sur la colonne que nous voulons retourner cette fois-ci (changement de la valeur du 3ème paramètre) et enfin mettre en paramètre FAUX de la fonction SI la valeur à retourner en cas de recherche infructueuse. La formule s’écrit alors :

=SI(RECHERCHEV(A2;$A$2:$A$50000;1;1)=A2,RECHERCHEV(A2;$A$2:$A$50000;4;1), « Inconnu »)

Finalement, c’est tout simple et le gain de temps est tout simplement hallucinant 🙂

Les données de votre colonne de recherche (la première dans notre exemple) doivent être triées en ordre croissant.




Pour aller plus loin

Charles Williams ne se contente pas de nous révéler certains secrets de codage des fonctions d’Excel, il a lui-même codé des fonctions, similaires à celles présentes dans Excel, mais avec un résultat bien plus rapide que les fonctions existantes.

En effet, toujours sur de grandes plages de données, les fonctions SOMME.SI.ENS, NB.SI.ENS, …. sont aussi extrêmement lentes. Pour remédier à ces problèmes, il a conçu un module complémentaire (add-in) qui viendra ajouter de nouvelles fonctions dans votre logiciel Excel afin de vous faire gagner un temps précieux.

Vous pouvez le télécharger à l’adresse suivante : son utilisation est gratuite pendant 15 jours mais vous verrez bien vite qu’à l’usage, vous ne pourrez plus vous en passer tellement la rapidité de ces formules vous fera gagner un temps précieux au quotidien.


Lien Permanent pour cet article : https://www.excel-exercice.com/accelerer-la-fonction-recherchev/

(6 commentaires)

Passer au formulaire de commentaire

  1. hh

    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

    1. Frédéric LE GUEN

      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.

  2. Valérie

    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

  3. Polo

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

  4. DARMON Henri

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

  5. Das Michel

    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.

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Vous pouvez utiliser ces balises et attributs HTML : <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Advertisment ad adsense adlogger