Si vous utilisez beaucoup d'Excel, vous connaissez très certainement la fonction RECHERCHEV. Sinon, vous pouvez lire ce tutoriel pour apprendre cette fonction indispensable.
Mais si vous avez déjà travaillé sur de gros fichiers Excel avec des centaines de milliers de lignes, vous avez sans doute constaté à quel point la fonction RECHERCHEV peut être pénible. Bien que très utile pour lier les données de plusieurs tables, la fonction RECHERCHEV devient rapidement incapable de calculer dans un temps acceptable. Sur les gros fichiers Excel, le calcul de votre fichier peut prendre jusqu'à une heure avec à la clé un probable échec de l'application.
En attendant qu'Excel calcule
La bonne nouvelle est que vous pouvez faire fonctionner RECHERCHEV beaucoup plus rapidement avec une technique appelée la double-RECHERCHEV.
Comment ça marche ? Commençons par un petit rappel sur le fonctionnement de la fonction RECHERCHEV. Si vous le savez déjà, vous pouvez passer à la section suivante.
La fonction RECHERCHEV classique
La fonction RECHERCHEV s'écrit comme suit :
RECHERCHEV(valeur_cherchée; table_matrice;no_index_col; num; [valeur_proche), où :
Valeur_cherchée est l'ID utilisé pour lier les deux tables. Par exemple, votre ID est "1234" si vous voulez chercher une valeur pour l'ID "1234" dans une autre table.
Table_matrice est la table dans laquelle vous êtres en train de chercher une valeur. C'est la table avec les IDs en première colonne.
No_index_col est l'index de la colonne qui contient la valeur cible au sein de la table_matrice
Dans l'exemple ci-dessous, la fonction RECHERCHEV renverra la valeur de l'ID "1234" dans la table, qui est 17.
Recherche non triée versus recherche binaire
Les fins observateurs auront remarqué que je n'ai pas encore expliqué l'argument [valeur_proche]. FAUX correspond à la recherche de données non triées, tandis que VRAI correspond à une recherche binaire. Quelle est la différence ?
La recherche standard (FAUX) est très lente, car Excel vérifie pour chaque ligne du tableau si elle correspond à votre ID, jusqu'à ce qu'une correspondance soit trouvée. Statistiquement, il faut rechercher la moitié des lignes pour trouver une correspondance. Si vous avez 100k lignes dans votre première table et 200x dans la seconde, cela fait 100k x 200k x 0,5= 10 milliards d'opérations !
Les recherche binaires (VRAI) marchent uniquement avec des données triées et :
Parce que vos identifiants sont triés, Excel peut fonctionner plus intelligemment. Au premier calcul, il vérifie la valeur au milieu du tableau. S'il est plus petit que votre ID, alors Excel sait que votre ID ne peut être trouvé qu'au-dessus (car la colonne est triée), et s'il est au-dessus, l'ID ne peut être trouvé qu'en dessous. Cela signifie qu'en un seul calcul, Excel a déjà exclu la moitié de la colonne. Au deuxième calcul, il fera de même et exclura à nouveau la moitié du reste, etc.
La conséquence est la suivante : la recherche binaire est bien plus rapide que la recherche non triée. Bien, bien plus rapide.
La recherche binaire va vraiment vite
Le seul problème est : que se passe-t-il si votre ID n'existe pas dans la table que vous recherchez ? Votre fonction RECHERCHEV renverra la valeur de la correspondance la plus proche de votre ID, mais ce que vous voulez est généralement une correspondance exacte. La recherche binaire est donc plus rapide mais vous ne pouvez pas être sûr que le résultat est correct ! Bon sang!
L'astuce du double-RECHERCHEV
L'astuce du Double-RECHERCHEV est faite pour utiliser la vitesse de la recherche binaire tout en s'assurant que le résultat est correct. Voilà comment on l'écrit :
=SI(RECHERCHEV(ID;Table;1;VRAI)=ID;RECHERCHEV(ID;Table;2;VRAI);NA())
Le premier RECHERCHEV vérifie que l'ID que vous recherchez existe dans la table, et seulement s'il existe, le second RECHERCHEV renverra la valeur correspondante. Ce faisant, vous évitez d'obtenir des valeurs erronées lorsqu'il n'y a pas d'ID correspondant.
Alors combien de temps cette astuce vous fait gagner ?
Selon l'expérience menée par Dailydoseofexcel.com, la double-RECHERCHEV est 28 fois plus rapide que la RECHERCHEV classique pour une table de 10 000 lignes, et cela augmente jusqu'à 3 606 fois plus vite que la RECHERCHEV classique pour une table de 1 000 000.
Conclusion
Bien que la fonction RECHERCHEV habituelle soit plus simple à utiliser au quotidien, gardez à l'esprit que si votre feuille Excel commence à prendre du temps pour les calculs, vous pouvez l'accélérer en utilisant l'astuce Double-RECHERCHEV. N'oubliez juste pas de trier vos identifiants avant pour qu'Excel puisse calculer très rapidement.
Si votre version d'Excel est récente, vous devriez envisager d'utiliser la nouvelle fonction RECHERCHEX qui remplace RECHERCHEV avec plus de flexibilité et de meilleures performances.
Vous utilisez beaucoup Excel et PowerPoint ? Téléchargez le complément Power-user et vous obtiendrez des super pouvoirs avec des templates, des graphiques, des icônes, des diagrammes et de nombreux outils qui vous feront gagner des heures !