All posts tagged RECHERCHEV

Améliorer les performances de la fonction RECHERCHEV Excel

Ou comment améliorer les performances de la fonction RECHERCHEV… De façon spectaculaire !

Sur quelques articles de ce site, nous avons évoqué les performances poussives de la fonction RECHERCHEV, lorsqu’elle est utilisée en mode recherche exacte (dernier paramètre à VRAI). Dommage pour la fonction la plus utile du

Pourtant, la solution pour accélérer radicalement cette fonction est vraiment simple. Tellement simple qu’on se demande bien comment diable cette correction n’est pas implémentée directement dans Excel

Il suffit juste de travailler avec cette même fonction RECHERCHEV en recherche approximative, et tester si la valeur retournée est égale à la valeur cherchée. Si nous effectuons la recherche suivante :

=RECHERCHEV(B2;clients!$A$1:$C$10001;3;FAUX)

Nous cherchons la 3ème colonne de la correspondance exacte de B2. Nous pouvons écrire très simplement :

=SI(RECHERCHEV(B2;clients!$A$1:$C$10001;1;VRAI)<>B2;NA();RECHERCHEV(B2;clients!$A$1:$C$10001;3;VRAI))

Attention, les données cibles doivent être triées !

Ce qui revient à faire une recherche approximative (qui est 50 fois plus rapide…), tester la valeur retournée, et si elle est différence, forcer le résultat à #N/A. Si elle est égale, nous renvoyons la 3ème colonne.

Simplissime, et pour les tableaux « monstrueux » à plusieurs centaines de milliers de lignes, le résultat sera sans appel. Dans un cas avec 500.000 lignes, on est passé de 14s à 1s.

La fonction RECHERCHE est lente ? Plus maintenant !

Top

Comparaison de RECHERCHEV et EQUIV + INDEX

En associant les fonctions EQUIV et INDEX, on aboutit aux mêmes résultats que la fonction RECHERCHEV.

AVANTAGES ET INCONVENIENTS DE RECHERCHEV :

Avantage : plus rapide à écrire.

Inconvénient : la valeur cherchée doit obligatoirement être en première colonne, la valeur ramenée est forcément à droite. Cela implique parfois de remanier le tableau de données.

AVANTAGES ET INCONVENIENTS D’INDEX + EQUIV :

Avantage : permet de ramener des valeurs à gauche de la valeur cherchée. Peut donc s’utiliser sans modifier la forme d’un tableau.

Inconvénient : lourd à écrire (fonctions imbriquées), ne sait pas effectuer une recherche sur une colonne entière, il faut délimiter la plage.

IMPLEMENTATION DE INDEX + EQUIV :

Dans le tableau suivant, nous allons ramener le nom du client :

La première formule :

=EQUIV(B3;clients!$A$2:$A$10001;0)

permet de rechercher le numéro de ligne contenant B3, dans la plage A2:A10000 de la feuille « clients ». Le dernier argument 0, c’est pour indiquer que l’on souhaite faire une recherche exacte et non approchée. Cette fameuse recherche exacte qui massacre les performances de RECHERCHEV… On verra plus tard si EQUIV fait mieux !

La deuxième formule :

=INDEX(clients!$A$2:$C$10001;EQUIV(B3;clients!$A$2:$A$10001;0);3)

Ramène la valeur de la cellule située dans la feuille « clients » plage A2:C10000, à la ligne renvoyée par la fonction EQUIV, et à la colonne 3.

Nous obtenons bien sur les mêmes résultats qu’avec la fonction RECHERCHEV.

BENCHMARK DE INDEX + EQUIV ET RECHERCHEV :

Nous implémentons 500.000 fois les formules avec les fonctions RECHERCHEV et INDEX+EQUIV, et nous mesurons avec quelques lignes VBA le temps de rafraichissement de chaque solution.

Les résultats sont à la hauteur de nos attentes…

RECHERCHEV : 54s

INDEX + EQUIV : 55s

Bref, ce n’est pas bon. La recherche exacte dans Excel est décidément très lente…Que ce soit avec EQUIV ou RECHERCHEV, Microsoft n’a pas su implémenter simplement un algorithme efficace… Pour rappel les mêmes tests avec des recherches approchées rendent un résultat en 1s ou 2s…

Top

Fonction RECHERCHEV (3/3) : recherche sur plusieurs colonnes

3ème volet de notre série sur la RECHERCHEV, la recherche sur plusieurs colonnes.

Sur le 1er onglet client à vérifier, nous avons le nom en 1ère colonne, et le prénom en 2ème colonne. Sur notre 2ème onglet clients, idem, nom et prénom et age en plus. Nous voulons savoir si le couple nom + prénom du permer onglet existe et ramener l’âge de la personne.

Cela reste toujours simple : sur l’onglet clients à vérifier, on clic-droit sur l’en-tête de la première colonne A, et nous choisissons « insertion » afin d’insérer une nouvelle colonne avant les autres. En A2, nous faisons appel à l’assistant formule (le petit fx) dans la catégorie texte pour choisir la formule CONCATENER et ainsi concaténer les cellules B2 et C2 (résultat nom9prenom9).

Nous procédons à la même manip sur l’onglet clients, et comme nous connaissons bien les formules Excel, nous saisirons directement dans la cellule

=B2&C2

Cette formule est la concaténation rapide !

Désormais, nous pouvons implémenter la RECHERCHEV() sur les colonnes D et E.

En colonne D :

=SI(ESTNA(RECHERCHEV(A2;clients!A:D;1;FAUX)); »client absent »; »client existe »)

En colonne E :

=SI(ESTNA(RECHERCHEV(A2;clients!A:D;4;FAUX)); »erreur client »;RECHERCHEV(A2;clients!A:D;4;FAUX))

Remarque : pour un exemple plus visuel, une colonne a été insérée dans l’onglet clients à vérifier, mais ce n’était pas obligatoire. On aurait pu directement écrire la formule :

=RECHERCHEV(A2&B2;clients!A:D;1;FAUX) (formules imbriquées)

En revanche, l’insertion d’une colonne AVANT les autres données est obligatoire dans l’onglet clients, car il faut que la donnée cherchée par RECHERCHEV soit contenue dans une cellule unique.

Et voilà, vous savez désormais à peu près tout sur la fonction RECHERCHEV ! Du moins, vous en savez suffisamment pour savoir répondre à la plupart de vos besoins de « croisement » de données sous Excel !

Top

Fonction RECHERCHEV (2/3) : ramener des informations issues d’une autre feuille

Continuons avec la fonction RECHERCHEV(). Nous avons vu dans la première partie du tutoriel que cette fonction était utile pour effectuer un test de présence. Nous allons maintenant l’utiliser pour chercher une information (de façon similaire au test de présence) dans un autre onglet (ou dans un autre classeur, ce n’est pas nécessaire de copier-coller les infos dans lesquelles on va chercher), et obtenir un détail de cette information.

EXEMPLE : nous avons une liste de clients à vérifier sans leur prénom. Leur prénom se trouve dans une autre liste plus complète clients.

Rien de plus simple ! Nous allons remettre en place notre fonction de jointure Excel, RECHERCHEV()

La liste des clients à vérifier, avec le prénom manquant :

La liste des clients avec leur prénom :

Dans clients à vérifier, dans la cellulle B2 on écrit (avec l’assistant formule ou directement dans la cellule) :

=RECHERCHEV(A2;clients!A:B;2;FAUX)

En d’autres termes, on recherche la valeur de A2 (nom18) dans l’onglet clients, sur la colonne A et B (on note clients!A:B, car on a besoin de la colonne B pour ramener le résultat), on ramène la valeur de la 2ème colonne, et on laisse FAUX car on veut bien la correspondance exacte du nom.

EXPLICATION DE L’ARGUMENT No_index_col :
Si dans l’onglet clients le prénom se trouvait sur la 3ème colonne (après le nom marital par exemple) on aurait écrit :
=RECHERCHEV(A2;clients!A:C;3;FAUX)

Et voici le résultat de clients à vérifier avec la formule RECHERCHEV() implémentée dans une fonction imbriquée (voir partie 1 de RECHERCHEV) :

Avec la formule :

=SI(ESTNA(RECHERCHEV(A3;clients!A:B;2;FAUX)); »client non trouvé »;RECHERCHEV(A3;clients!A:B;2;FAUX))

Attention ! Cas des doublons !

Il est possible d’avoir plusieurs nom18 dans la liste nom prénom (personne en réalité ne s’appelle nom18, mais des Durand ou des Martin, il peut y en avoir plusieurs !).

Comment fait-on dans ce cas ? Dans notre cas de figure, pas beaucoup de solutions… Il faut essayer d’avoir une « clé » de recherche discriminante (exemple, on ne recherche pas une personne par son nom, mais son numéro de sécurité sociale, dont on est sûr qu’il est unique).

Si on fait cela avec seulement le nom, Excel ramène le premier résultat trouvé. Donc le premier prénom…

RECHERCHEV ne gère que des correspondances ligne à ligne (relation 1,1 ou 0,1 en langage base de données). Pour faire des recherches multiples (de type 1,n), on utilise soit la fonction NB.SI(), soit un programme VBA, soit du SQL dans Excel.

Top

Fonction RECHERCHEV (1/3) : test de présence d’une valeur dans une liste

LA fonction essentielle sous Excel, c’est bien la fonction RECHERCHEV (en fait, pour ceux qui maîtrisent les bases de données, c’est la jointure).

Celui qui connaît bien cette fonction saura se sortir de nombreuses difficultés classiques et pourra dans certains cas grandement améliorer son efficacité (ou rapidité…).

Un cas vraiment tout bête auquel nous avons tous été confronté au moins une fois : le test de présence d’une valeur dans une liste…

EXEMPLE : nous avons une liste de 3000 noms (une liste de clients) sur un fichier Excel, et une autre liste de clients à vérifier, mais nous ne sommes pas sur que ces clients à vérifier sont bien présents dans ma liste clients. Nous voulons le vérifier. Avec 100 clients dans la liste clients, nous aurions pu le faire « manuellement », mais avec du volume ce devient impossible, et de plus, cela manque sérieusement de rigueur.

Pour la liste de clients (dans l’onglet clients), on imagine que les clients ont pour nom : nom1, nom2, etc jusqu’à nom3000.

Et voici la liste des clients à vérifier, dans l’onglet du même nom :

Pour insérer une formule, sélectionnons tout d’abord la cellule dans laquelle nous souhaitons insérer la formule, puis cliquons sur le « fx » ci-dessus entouré en rouge, pour faire apparaître l’assistant formule :

Choisissons la catégorie « Recherche et matrices », puis RECHERCHEV et validons par OK :

Comment fonctionne cette fonction :

  1. Sélectionner Valeur_cherchée (clic sur la zone de texte associée), et cliquer sur la valeur que l’on va chercher sur l’onglet client à vérifier.
  2. Sélectionner Table_matrice (clic sur la zone de texte associée), et cliquer sur l’onglet clients pour se positionner sur la liste des clients, puis cliquer sur l’entête de la colonne A pour sélectionner TOUTE la colonne (si on ajoute des clients par la suite, la formule fonctionnera toujours…).
  3. Mettre 1 dans No_index_colonne, et FAUX dans Valeur_proche (on travaillera le plus souvent avec cette valeur pour ramener la valeur exacte, mais attention aux performances ! Nous y reviendrons).

Le résultat de la formule est « nom18″, car Excel a simplement ramené la 1ère colonne de l’onglet clients, pour la ligne « nom18″. On peut par la suite faire un copier-coller jusqu’à la cellule B9. Mais il y a mieux et plus « ergonomique » : placez le curseur de la souris sur le coin bas-droite (entouré en rouge) de la cellule B2, laissez le bouton gauche de la souris enfoncé, et descendez la souris jusqu’à la cellule B9, puis relâcher le bouton. Magique ! Encore mieux, double-cliquez directement sur le coin bas-droite de la cellule !

Excel a automatiquement incrémenté les formules. En B3 il a écrit :

=RECHERCHEV(A3;clients!A:A;1;FAUX)

Quand il ne trouve pas le client à supprimer dans l’onglet clients, Excel renvoie la valeur #N/A.

Pour finir, on peut faire plus abouti en utilisant des formules imbriquées (une des bases de l’utilisation avancée d’Excel !) :

=SI(ESTNA(RECHERCHEV(A4;clients!A:A;1;FAUX)); »absent »; »présent »)

La formule ESTNA() teste si la formule RECHERCHEV() a trouvé une correspondance ou non, et renvoie VRAI si RECHERCHEV a trouvé, FAUX si la formule n’a pas trouvé de correspondance. Et la formule SI permet de tester : Si on ne trouve pas, alors nous renvoyons « absent » dans la cellule, sinon, nous écrivons « présent ». Les fonctions imbriquées ne sont pas toujours très lisibles (surtout quand on commence à imbriquer sur 3 niveaux ou plus…) mais on n’y coupe pas pour travailler vite sous Excel.

L’aide en ligne (F1) est très bien faite, c’est du Microsoft… Les formules sont bien documentées. Mais ce petit tutoriel sur la RECHERCHEV() est INDISPENSABLE, c’est le début d’une utilisation avancée de l’outil !

Top
Page 1 of 1

Contactez-nous contact@expert-data.fr

EXPERT data, solutions pour TPE/PME et grands comptes