Les fonctions de recherche (lookup) sont sans doute les fonctions les plus utilisées par les usagers d’Excel. Toutefois, dans certains contextes, elles peuvent entraîner des problèmes de performance dans un fichier Excel. Cet article vise donc à renforcer vos connaissances par rapport aux fonctions de recherche Excel, à vous mettre en garde vis-à-vis de certaines pratiques et à vous donner des trucs pour développer une approche plus efficace, l’idée étant de réduire au maximum les temps de calcul de vos fonctions.
Ce qu’il faut savoir concernant les fonctions de recherche
D’abord, il est important de spécifier que lorsque vous utilisez une équivalence (match) exacte dans une fonction de recherche (lookup), le temps de calcul de la fonction est proportionnel au nombre de cellules à valider avant de trouver l’équivalence exacte. Si la plage de données sous-jacente est très volumineuse, le temps de calcul peut devenir significatif. Si possible, trier les données permettra d’optimiser le temps de recherche.
Si vous utilisez plutôt une équivalence approximative sur des données triées (très important), le temps de calcul n’est pas affecté par la taille de la plage de données sous-jacente. Les calculs se font rapidement.
Rappel des options d’équivalences (match) dans les fonctions de recherche
Fonction Match (ou Equiv en français)
Voici un tableau provenant du menu d’aide d’Excel qui explique comment est structurée la fonction Match (ou Equiv en français), quels sont ses paramètres et surtout, comment fonctionne le paramètre « type », i.e. le type d’équivalence recherchée. Pour voir un exemple illustré, vous pouvez relire l’article: Excel: La fonction « Match » pour calculer le délai de récupération.
Fonctions Vlookup/Hlookup (ou Recherchev/Rechercheh en français)
Voici un tableau provenant du menu d’aide d’Excel qui explique comment est structurée la fonction Vlookup (ou Recherchev en français), quels sont ses paramètres et surtout, comment fonctionne le paramètre « valeur_proche », i.e. le type d’équivalence recherchée. Pour voir un exemple illustré, vous pouvez relire l’article: Excel: Quand mettre un vrai dans un recherchev?
Trucs pour augmenter la rapidité de calcul des fonctions de recherche
Les équivalences exactes peuvent introduire des délais de calcul considérables. Pour réduire ces délais au maximum, pensez à organiser vos données et à construire vos fonctions autrement ou à utiliser les équivalences approximatives lorsque possible. Voici quelques exemples concrets.
Exemples:
- Placez votre fonction de recherche dans le même onglet que la plage de données sous-jacente (les calculs se feront plus rapidement)
- Quand vous pouvez trier les données d’abord, faites-le et utilisez ensuite l’équivalence approximative au lieu de l’équivalence exacte
- Si vous ne pouvez pas utiliser l’équivalence approximative directement parce que vous n’êtes pas certain que la valeur recherchée se retrouve dans la plage de données sous-jacente, vous pouvez utiliser une combinaison de deux fonctions de recherche avec des équivalences approximatives, tel qu’illustré dans l’exemple ci-bas (les calculs se feront plus rapidement qu’avec une seule fonction possédant une équivalence exacte)
- À noter que lorsque la valeur recherchée est inférieure à la plus petite valeur de la plage de données, vous obtiendrez un #N/A, que vous pourriez aussi gérer avec un Ifna (Sina en français)
- Quand vous devez absolument utiliser une équivalence exacte, restreignez les plages de cellules auxquelles se réfère votre fonction de recherche, par le biais de plages de données dynamiques nommées
- Dans l’exemple suivant, j’ai d’abord inséré, dans les cellules L5 à O7, les paramètres des différentes fonctions Offset (Décaler) qui sont utilisées pour définir des plages de données dynamiques, représentées par des champs nommés. Il n’est pas nécessaire d’inscrire ces paramètres dans des cellules isolées. Je l’ai fait simplement pour des fins d’explication.
- Ensuite, j’ai défini des plages de champs nommés dynamiques, comme GestionnaireA, ci-bas, basées sur ces paramètres.
- Parallèlement à l’exercice que je cherche à compléter, j’ai également inséré 3 formules Sum(Offset) ou Somme(Decaler) pour vérifier que mes paramètres étaient les bons (donc en vérifiant que la somme des 3 gestionnaires correspondait à la somme des données du tableau).
- L’objectif ultime était donc de développer une formule Vlookup (Recherchev) avec un paramètre False (Faux), donc une équivalence exacte, mais à effectuer la recherche sur une plage de données restreinte, dans notre exemple GestionnaireA, plutôt que sur les données de l’ensemble du tableau. À noter que nous avons également utilisé une référence approximative dans notre fonction Match (Equiv).
- Pour rédiger adéquatement la formule ci-dessus, j’ai d’abord dû convertir les titres de colonnes du tableau en valeurs et rechercher dans ces valeurs (déjà triées en ordre croissant).
- Quand vous devez utiliser une équivalence exacte sur des données non triées, vous pouvez utiliser le Iferror (Sierreur) pour gérer les #N/A des valeurs recherchées qui n’existent pas dans la plage de données ou vous pouvez également à nouveau faire l’usage d’équivalences approximatives en exploitant conjointement d’autres fonctions comme dans l’exemple ci-bas.
- D’abord, on cherche la position de RepP dans la liste
- Comme la valeur existe, on obtient 9
- Si elle n’existait pas (comme dans le deuxième exemple RepPP), on obtiendrait #N/A
- Ensuite, on peut imbriquer notre fonction de recherche avec équivalence approximative dans un If(Isna) ou Si(Estna) ou encore dans un If(Countif) ou Si(Nb.Si)
Une alternative aux fonctions de recherche usuelles
Je recommande fortement l’usage de la fonction Index plutôt que des fonctions de recherche Vlookup (Recherchev) et Hlookup (Rechercheh). Pourquoi? De façon générale, les fonctions de recherche comme Vlookup et Hlookup sont plus rapides mais la fonction Index offre plus de flexibilité et en bout de ligne, offre des façons de réduire le temps de calcul, par exemple, en isolant les équivalences exactes. L’exemple suivant démontre qu’on peut remplacer les Recherchev (Vlookup) par des fonctions Index. De plus, en isolant les deux équivalences exactes dans les cellules F4 et F5, on peut améliorer la performance de calcul de notre fichier car ces deux paramètres sont utilisés également dans la fonction Index qui permet de calculer les commissions et les salaires.
Pour relire un article et consulter un exemple appliqué de la fonction Index simple, vous pouvez consulter l’article suivant:
Fonction Excel: Index/Equiv (Index/Match)
Pour relire un article et consulter un exemple appliqué de la fonction Index sur plusieurs plages de données, vous pouvez consulter l’article suivant:
Excel: Effectuer un Index/Match (Index/Equiv) sur plusieurs plages de données
La fonction Offset (Decaler) est également rapide et pourrait être utilisée. Toutefois, cette fonction est volatile, ce qui peut parfois augment la performance au niveau des calculs du fichier Excel. Néanmoins, la fonction Offset demeure dans certains cas, fort utile, notamment pour définir des plages de données dynamiques. Pour relire un article et consulter un exemple appliqué de la fonction Offset (Decaler), vous pouvez consulter l’article suivant:
Excel: Fonction Decaler (Offset
NOTRE OFFRE DE FORMATIONS
Le CFO masqué vous offre 13 formations, réparties dans 4 catégories: Tableaux de bord, Modélisation financière, Finance corporative et Analyse et modélisation de données. Ces formations sont offertes en classe, en entreprise et en ligne. Nos formateurs sont des experts dans leur domaine et sont accrédités par Emploi-Québec et vous remettent un certificat, à la fin de chaque formation, que vous pouvez notamment utiliser pour faire reconnaître des heures de formation continue auprès de votre ordre professionnel.
Consulter la liste des formations offertes
Pour info: 514-605-7112 ou info@lecfomasque.com