Si vous travaillez avec une version d'Excel antérieure à 2019, vous serez intéressé de savoir comment accélérer la vitesse de la fonction RECHERCHEV. Depuis 2019, la fonction RECHERCHEV a été optimisée et il n'est plus nécessaire de faire cette manipulation.
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
- Positionné à 1 (ou VRAI) pour une recherche approchante
- Positionné à 0 (ou FAUX) pour une recherche exacte.
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 ?????????
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.
Encore mieux, la fonction RECHERCHEX est plus simple à coder et encore plus optimisée.
Technique pour accélérer la vitesse de RECHERCHEV
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 de jours 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")
Finalement, c'est tout simple et le gain de temps est tout simplement hallucinant ??
Vous trouverez des informations complémentaires sur la fonction RECHERCHEV sur le site de Microsoft.
Vidéo explicative
