Vlookup dans Excel - Quelle formule est la plus rapide?

Aujourd'hui, nous allons faire vlookup dans Excel avec plusieurs formules différentes, mesurer leur vitesse de calcul et évaluer la cohérence, afin que vous puissiez choisir votre gagnant.

Trouver des informations sur différentes tables est l'une des tâches les plus courantes d'Excel. Malheureusement, la fonction VLookup classique est notoire en matière de puissance et de flexibilité de traitement. Il n'est pas étonnant que au fil des ans, les utilisateurs d'Excel aient trouvé leurs propres solutions telles qu'une formule d'index. Heureusement, Microsoft a finalement réalisé que Vlookup a trop de faiblesses et a publié un successeur plus puissant - la fonction xlookup. Alors, lequel est le meilleur à utiliser?

Si vos tables n'ont que quelques douzaines de lignes, alors toute méthode sera probablement assez rapide et vous ne remarquerez pas la différence. Mais si le nombre de lignes est mesuré en milliers, le bon choix de la fonction est crucial - la différence de performance peut être plus de 10 fois! Alors, commençons la minuterie et voyons qui est le plus rapide :)

• Données de source
• Fonction Vlookup
• Formule de correspondance d'index
• Formule de correspondance de décalage
• Fonction xlookup
• Vlookup dans les tables Excel
• Vlookup avec des tableaux dynamiques
• Tirer les matchs avec la requête de puissance
• Bonus supplémentaire: l'assistant de tables de fusion
• Résumé et conclusions

Données de source

Dans tous les exemples, nous utilisons bien l'ensemble de données suivant:

• Table principale contenant 500 000 lignes
• Table de recherche contenant 500 lignes

Notre objectif est de faire correspondre les noms des articles et de tirer les prix de la table de recherche dans la table principale.

Pour chaque méthode, nous entrerons dans la formule en C2 et le copierons via C500001, mesurant le temps qu'il faut Excel pour calculer un demi-million de cellules.

Naturellement, le résultat dépendra de nombreux facteurs tels que les performances de votre CPU, la taille de la RAM, la version Excel, etc. Dans cette expérience, ce ne sont pas des nombres absolus qui comptent. Il est plus important de comprendre les performances de chaque formule en comparaison, leurs avantages et leurs inconvénients.

Nos exemples supposent que vous avez la connaissance de base des fonctions, et nous ne nous attarderons pas beaucoup sur leur syntaxe. Les liens vers des tutoriels approfondis sont inclus pour votre commodité.

Tous les tests ont été effectués sur mon ordinateur portable Dell (Intel Core i5-8250U; RAM 16 Go) avec des applications Microsoft 365 pour les affaires installées; Excel 32-bits, version 2011, build 13415, canal bêta. Les mêmes tests ont également été effectués par mon collègue sur Excel 64 bits, et certains résultats sont radicalement différents!

Formule vlookup

Lorsqu'il s'agit de rechercher et de récupérer des données correspondantes dans Excel, la première fonction qui vient à l'esprit est le bon vieux Vlookup. En fait, ce n'est pas si bon que cela pourrait l'être, mais nous en parlerons un peu plus tard :)

Vlookup (lookup_value, table_array, col_index_num, [range_lookup])

Pour tirer les prix de la table de recherche (E2: F501) à la table principale (A2: B500001), nous définissons les arguments suivants pour notre formule Vlookup:

• Lookup_value : b2 - la valeur à rechercher dans la table de recherche.
• Table_Array : $ E 3 $: $ f 501 $ - Veuillez remarquer que nous verrouillons les références avec le signe $ afin qu'ils ne changent pas lors de la copie de la formule.
• Col_index_num : 2 - Les données doivent être récupérées à partir de la 2 ⁿ colonne de la table de recherche.
• Range_lookup : false - Recherche de correspondance exacte.

La formule complète est:

=VLOOKUP(B2, $E$3:$F$501, 2, FALSE)

La formule ci-dessus va à C2, puis nous double-cliquez sur le signe plus dans le coin inférieur droit pour copier la formule sur toute la colonne. Au moment du double-clic, nous exécutons un chronomètre et voyons que ce calcul dure 6,6 secondes.

Lorsque vous faites VLookup dans de vraies feuilles de travail, de nombreuses personnes fournissent toutes les colonnes pour Table_Array pour répondre aux ajouts possibles à l'avenir:

=VLOOKUP(B2, E:F, 2, FALSE)

Cela affecte-t-il les performances? Oui, c'est le cas. Il faut 14,2 secondes pour calculer toute la colonne. C'était difficile à croire, alors j'ai double vérifié. Le même résultat - moins de la moitié de la vitesse des gammes.

En plus d'être rapide, votre formule doit également être robuste et durable, non? Malheureusement, Vlookup ne peut pas se vanter de fiabilité et de résilience.

Limitations Vlookup

Comme déjà mentionné, Excel Vlookup a un certain nombre de contraintes irritantes. Les plus essentiels sont:

• Ne peut pas regarder sa gauche. La fonction VLookup ne peut que consulter dans la colonne la plus à gauche du tableau de table et retourner les informations de la droite.
• Ne survit pas à l'insertion ou à la suppression de la colonne. Étant donné que la colonne de retour est spécifiée comme un numéro d'index, une formule VLookup cesse de fonctionner dès qu'une nouvelle colonne est ajoutée ou supprimée du tableau de table.
• Une valeur de recherche est limitée à 255 caractères.

Temps de calcul : plage - 6,6 secondes; Colonnes entières - 14,2 secondes.

Tutoriel approfondi : Excel Vlookup Exemples pour les débutants

Formule de correspondance d'index

Pour de nombreux utilisateurs, une forme avancée de recherche dans Excel est la formule magique d'index. Sous forme générique, cela ressemble à ceci:

Index ( return_column , match ( lookup_value , lookup_column , 0))

Pour notre ensemble de données, la formule prend ce formulaire:

=INDEX($F$2:$F$501, MATCH(B2, $E$2:$E$501, 0))

Comme pour Vlookup, n'oubliez pas d'utiliser des références absolues pour les plages de recherche et de retour pour vous assurer que la formule copie correctement aux cellules ci-dessous.

Avec les deux tables sur la même feuille, l'index correspondait beaucoup plus lent que VLookup (8,9 contre 6,6 secondes).

Mais si nous déplaçons la table de recherche vers une autre feuille de calcul , la formule commence à fonctionner beaucoup plus rapidement (~ 5 secondes), ce qui est mieux que VLookup.

Quoi qu'il en soit, la correspondance d'index constitue le temps avec un certain nombre d'avantages vitaux.

Index Match Avantages

• Peut regarder de droite à gauche. Oui, une formule de correspondance d'index ne se soucie pas de savoir où la colonne de recherche est située, car contrairement à VLookup, il définit explicitement une plage de recherche plutôt qu'un tableau de table.
• Immunisé contre l'insertion et la suppression de la colonne. Avec Index Match, vous pouvez ajouter et supprimer les colonnes en toute sécurité depuis que vous spécifiez une plage de retour, pas un numéro d'index.
• Aucune limite pour la taille d'une valeur de recherche. Bien que VLookup soit limité à 255 caractères, l'index Match n'a aucun problème avec le traitement des chaînes plus longues.
• Peut effectuer Vlookup avec plusieurs critères comme indiqué dans l'exemple lié ci-dessus.
• Peut effectuer une recherche bidimensionnelle et renvoyer une valeur à l'intersection d'une ligne et d'une colonne spécifiques.

Temps de calcul : gammes - 8,9 secondes; Colonnes entières - 17,7 secondes; à partir d'une autre feuille - 5,2 secondes.

Tutoriel approfondi : Formule d'indexer sur Excel

Formule de correspondance de décalage

Voici une autre formule à rechercher verticalement dans Excel, qui est exempte de nombreuses limites de Vlookup:

Offset ( Lookup_Table , Match ( Lookup_Value , Offset ( Lookup_Table , 0, N , Rows ( Lookup_Table ), 1), 0) -1, M , 1, 1)

Où:

• N - est le décalage de la colonne de recherche qui spécifie le nombre de colonnes pour passer du début de la table à la colonne de recherche.
• M - est le décalage de la colonne de retour qui détermine le nombre de colonnes à passer à la colonne de retour.

Dans notre cas, le décalage de la colonne de recherche (n) est 0 parce que nous recherchons dans la première colonne, donc aucun changement n'est nécessaire. Le décalage de la colonne de retour (M) est 1 car les correspondances sont dans la deuxième colonne, et nous devons déplacer 1 colonne vers la droite pour y accéder:

=OFFSET($E$2:$F$501, MATCH(B2, OFFSET($E$2:$F$501, 0, 0, ROWS($E$2:$F$501), 1), 0) -1, 1, 1, 1)

Par rapport aux solutions précédentes, la formule est trop encombrante, non? Cependant, il est beaucoup plus rapide que la correspondance VLookup ou Index. Le calcul de 500 000 lignes prend moins de 3 secondes! Sur des colonnes entières, le décalage est juste un peu plus lent - 3,5 secondes.

=OFFSET(E:F, MATCH(B2, OFFSET(E:F, 0, 0, ROWS(E:F), 1), 0) -1, 1, 1, 1)

Dans Excel 64 bits, cependant, le résultat n'est pas si impressionnant - environ 7,5 secondes. Pourquoi c'est? Une bonne question aux gars de Microsoft :)

Avantages de correspondance de décalage

Mis à part la vitesse, cette formule a quelques autres mérites:

• Peut effectuer un VLookup à droite à gauche et un hlookup supérieur.
• Peut effectuer une recherche bidirectionnelle basée sur la colonne et les valeurs de ligne.
• Ne se casse pas lorsque les colonnes sont insérées ou supprimées de la table de recherche.

Inconvénient de la correspondance de décalage

Syntaxe complexe.

Temps de calcul : gammes - 2,9 secondes; Colonnes entières - 3,5 secondes.

Tutoriels approfondis :

• Fonction de décalage avec des exemples de formule
• Comment utiliser la fonction de correspondance dans Excel

Formule xlookup

Les abonnés Microsoft 365 reçoivent une nouvelle fonction plus puissante pour rechercher des informations dans leurs feuilles de travail:

XLookup (lookup_value, lookup_array, return_array, [match_mode], [search_mode], [if_not_found])

Pour notre objectif, les paramètres des 3 derniers arguments fonctionnent très bien, nous ne spécifions donc que les 3 premiers paramètres, qui sont nécessaires. Les noms des arguments sont intuitifs, et je crois que vous pouvez comprendre la formule sans explications supplémentaires:

=XLOOKUP(B2, $E$2:$E$501, $F$2:$F$501)

Avantages xlookup

Par rapport au VLookup traditionnel, la fonction XLookup a de nombreuses améliorations telles que:

• Syntaxe simplifiée et plus significative
• Capacité à rechercher verticalement et horizontalement dans n'importe quelle direction: droite, gauche, bas ou haut.
• Pour les données triées, il dispose d'un mode de recherche binaire spécial beaucoup plus rapide que la recherche régulière.
• Recherchez dans l'ordre inverse pour obtenir la dernière occurrence.
• Capacité à renvoyer plusieurs valeurs.
• Gérer plus d'une condition comme expliqué dans Excel xlookup avec plusieurs critères.
• INBULT IF FONCTIONNALISATION ERREUR.

Xlookup Inconvénients

XLookup n'est disponible que dans Excel 365 et 2021. Dans Excel 2019, Excel 2016 et les versions antérieures, il n'est pas pris en charge.

Et maintenant, voyons à quel point cette nouvelle fonction est rapide. 11.2 secondes - Assez décevant :(

Et si nous utilisons des références de colonne au lieu de plages? =XLOOKUP(B2, E:E, F:F)

24,5 secondes. Pas de mots… presque deux fois plus lents que vlookup.

Temps de calcul : gammes - 11,2 secondes; Colonnes entières - 24,1 secondes.

Tutoriel complet : fonction Excel xlookup avec des exemples

Vlookup dans les tables Excel

Comme vous le savez probablement, les données dans les tables Excel peuvent être référencées de manière spéciale - en utilisant des noms de table et de colonnes au lieu d'adresses cellulaires. C'est ce qu'on appelle une référence structurée , et je me demande si elle a un impact sur la vitesse de calcul.

Pour vérifier cela, convertissons les gammes en tables et remettons nos formules.

Pour plus de commodité, nos tables sont nommées main_table (A1: C500001) et Lookup_Table (E1: F5001).

Pour créer une référence de table, commencez à taper la formule dans la première cellule (C2), sélectionnez les cellules et les plages auxquelles vous souhaitez vous référer, et Excel insérera automatiquement les références structurées.

Par exemple, voici à quoi ressemble la formule Vlookup:

=VLOOKUP([@Item], Lookup_table, 2, FALSE)

Une grande caractéristique des tables Excel est que dès que vous entrez une formule dans une seule cellule, il est immédiatement peuplé dans toutes les autres cellules de la même colonne. En outre, les tables sont dynamiques par nature et se développent automatiquement pour inclure toutes les nouvelles données que vous tapez à côté d'un tableau.

Dans notre tableau, la formule Vlookup calculée en 2,3 secondes, l'index correspond en 2,6 secondes, décalé et correspond en 2,7 secondes, et Xookup en 3,3 secondes. Comme vous le voyez, la vitesse de calcul augmente considérablement par rapport aux plages.

Les formules sont répertoriées ci-dessous pour votre référence:

=INDEX(Lookup_table[Price], MATCH([@Item], Lookup_table[Item], 0))

=OFFSET(Lookup_table, MATCH([@Item], OFFSET(Lookup_table, 0, 0, ROWS(Lookup_table), 1), 0) -1, 1, 1, 1)

=XLOOKUP([@Item], Lookup_table[Item], Lookup_table[Price])

Chose intéressante, les tables Excel sont très rapides même avec des références régulières . Autrement dit, si vous ne convertiez que la première plage (A1: C500001) en une table et utilisez une formule VLookup normale pour extraire les données de la plage de recherche , la colonne entière de la table principale sera calculée en environ 2,5 secondes!

Vitesse de calcul : de 2,3 à 3,3 secondes en fonction de la formule.

Tutoriels de bout en bout :

• Table Excel avec des exemples
• Références structurées dans les tables Excel

Vlookup avec des tableaux dynamiques

Le changement révolutionnaire du moteur de calcul Excel 365 survenu en janvier 2020 a ajouté un support pour les tableaux dits dynamiques . En bref, ce sont des tableaux résidants qui calculent automatiquement et renvoient les valeurs dans plusieurs cellules en fonction d'une formule entrée dans une seule cellule.

L'une des meilleures choses à propos des tableaux dynamiques est qu'ils peuvent être utilisés avec presque toutes les fonctions Excel traditionnelles. Pour notre formule Vlookup, cela ressemblera à ceci:

=VLOOKUP(B2:B500001, E2:F500001, 2, FALSE)

La différence avec la fonction VLookup classique est que vous fournissez l'intégralité du tableau de recherche pour le premier argument, pas une seule valeur de recherche. Parce que la formule est entrée dans une seule cellule, vous n'avez pas besoin de vous soucier de verrouiller les gammes avec des références absolues.

Quant aux performances, les tableaux dynamiques fonctionnent encore plus rapidement que les tables Excel! Un demi-million de cellules sont remplies des résultats presque immédiatement: 1,8 seconde - très impressionnant!

D'autres résultats sont répertoriés ci-dessous:

Correspondance d'index - 4,4 secondes

=INDEX(F2:F501, MATCH(B2:B500001, E2:E501, 0))

Xlookup - 7,3 secondes

=XLOOKUP(B2:B500001, E2:E501, F2:F501)

Hmm… xlookup qui est censé être dynamique par la conception fonctionne moins que les plus anciennes fonctions. Bizarre!

Vitesse de calcul : de 1,8 à 7,3 secondes en fonction de la formule.

Tutoriel approfondi : tableaux, fonctions et formules dynamiques Excel

Tirer les matchs avec la requête de puissance

Par souci d'exhaustivité, testons une autre solution possible pour notre tâche - Power Query. Bien sûr, il n'est pas tout à fait correct de comparer le calcul des formules avec la mise à jour de la requête, mais je suis juste curieux, ce qui est plus rapide :)

Les étapes détaillées de l'utilisation de la requête de puissance sont décrites dans un tutoriel séparé mentionné ci-dessous. Ici, nous allons simplement évaluer le résultat:

Le tableau fusionné est chargé de l'éditeur de requête de puissance en Excel en 8,5 secondes. Contrairement aux formules, les requêtes ne mettent pas à jour automatiquement. Après chaque modification des données source, vous devez mettre à jour le tableau résultant manuellement en cliquant sur le bouton Actualiser sur l'onglet Données ou Requête . Nos 500 000 lignes sont rafraîchies en environ 7 secondes. Pas mal, mais les formules Excel peuvent faire mieux. Étant donné que la configuration d'une requête est loin d'être un processus en un clic, c'est probablement la dernière méthode que j'utiliserais, seulement si rien d'autre ne fonctionne.

Performance : Chargement pour exceller 8,5 secondes; rafraîchissant 7,6 secondes

Tutoriels approfondis :

• Comment combiner des tables avec Excel Power Query
• Comment utiliser la requête de puissance dans Excel - Exemples pratiques
• Power Query in Excel Tutoriel pour les débutants

Bonus supplémentaire: l'assistant de tables de fusion

Les utilisateurs de notre suite Ultimate ont un autre outil dans leur boîte à outils Excel pour fusionner deux tables en fonction d'une colonne commune. Voyons comment cela se compare à celui d'Excel.

Pour exécuter l'assistant Tables Merge, cliquez sur le bouton Merger deux tables dans l'onglet Données AbleBits . Et puis, suivez simplement les étapes de l'assistant, et cela vous guidera tout au long du processus.

Eh bien, il a fallu à l'outil environ 3 secondes pour terminer. Pas si mal pour un demi-million de disques!

En regardant de plus près le message ci-dessus, vous remarquerez peut-être que tous les matchs n'ont pas été trouvés. Cela ne signifie cependant pas que l'outil est défectueux. Il vous permet simplement de savoir que certains éléments (valeurs de recherche) n'existent pas dans la table de recherche. La fonction VLookup renvoie une erreur # n / a dans ce cas, tandis que l'assistant de tables de fusion laisse une cellule vide.

Performance : 3,2 secondes

Plus d'informations : fusionnez deux tables dans Excel

Résumé et conclusions

Si vous lisez soigneusement tous les exemples, vous avez probablement déjà tiré vos propres conclusions. Si vous avez sauté les détails, vous pouvez trouver un résumé rapide dans ce tableau de comparaison:

Fonction	Vitesse de calcul en secondes
	Gammes	Colonnes entières	Tableau	Tableaux dynamiques
Vlookup	6.6	14.2	2.3	1.8
Correspondance d'index	8.9	17.7	2.6	4.4
Correspondance de décalage	2.9	3.5	2.7	-
Xlookup	11.2	24.1	3.3	7.3
Requête de puissance	8.5
Fusionner les tables	3.2

Ci-dessous, il y a quelques observations que j'ai faites en fonction des résultats des tests. Peut-être qu'ils seront utiles pour vous aussi.

• Malgré toutes ses limites et inconvénients, VLookup fonctionne assez bien, en particulier avec les tableaux dynamiques.
• La correspondance d'index n'est pas aussi rapide que prévu. Pour moi, cela semble très étrange car il traite des colonnes individuelles, pas un tableau de table comme Vlookup.
• Xlookup a de nombreuses capacités incroyables mais est plus lent que VLookup et Index correspond aux énormes ensembles de données. Espérons que Microsoft améliorera ses performances dans les futures versions.
• La correspondance de décalage est la plus rapide dans Excel 32 bits. Mais en raison de sa syntaxe complexe, il y a une grande chance de faire une erreur. De plus, cela ne fonctionne pas avec un tableau dynamique, au moins je n'ai pas pu le forcer.
• Il n'y a aucun sens de calculer des colonnes entières, sauf si c'est absolument nécessaire. Cela rend les formules plus de deux fois plus lentes.
• Excel Tables Rock! Pour tirer le meilleur parti de votre Excel, utilisez-les dans la mesure du possible.
• Les tableaux dynamiques sont l'avenir.

Veuillez garder à l'esprit que ces observations sont basées sur mes tests dans Dynamic Excel 365, je n'ai pas eu l'occasion de tester dans d'autres versions. Si vous l'avez fait, vos commentaires sont les bienvenus et seront grandement appréciés!

Classeur de pratique pour télécharger

Formule Vlookup la plus rapide dans Excel (fichier .xlsx, 74 Mo )

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!