Si vous avez une feuille de calcul volumineuse, beaucoup de RECHERCHES VIDÉO peuvent commencer à ralentir les choses. Avez-vous une feuille de calcul lente en raison de VLOOKUP? Je parle d'une feuille de calcul qui nécessite 40 secondes ou 4 minutes pour calculer. Dans l'article d'aujourd'hui, une formule étonnante avec deux VLOOKUP utilisant la recherche de plage résoudra le problème.
RECHERCHEV est une fonction relativement coûteuse. Lorsque vous recherchez une correspondance exacte, Excel doit parcourir la table de recherche une ligne à la fois.
Le classeur que j'utilise aujourd'hui fait 7000 RECHERCHEV dans un tableau de 116 000 éléments. Sur une machine 64 bits très rapide avec 8 cœurs, le temps de recalcul est de 3,01 secondes.
Une façon d'améliorer VLOOKUP est de déplacer les articles les plus vendus en haut de la table de recherche. Obtenez un rapport sur les 100 articles les plus vendus et placez ces articles en haut de la liste. Le tri par popularité améliore le temps de recalcul à 0,369 seconde. C'est huit fois plus rapide que le premier résultat.
Mais il existe un moyen d'accélérer encore plus les choses. Pendant que vous construisez votre RECHERCHEV, lorsque vous arrivez au quatrième argument pour choisir False, il existe une autre option qui n'est presque jamais utilisée. Excel dit que "True" fait une "correspondance approximative". Ce n'est pas du tout correct. Si l'équipe Excel était honnête, elle expliquerait que True «fournit souvent une réponse correcte, mais d'autres fois, sans aucun avertissement, nous allons y glisser la mauvaise réponse. J'espère que cela ne vous dérange pas de reformuler vos chiffres à la Securities and Exchange Commission. »
Bien sûr, il y a un bon moment pour utiliser True. Consultez cet article. Mais il serait vraiment mauvais d'utiliser True lorsque vous essayez de faire une correspondance exacte.
Si vous essayez d'utiliser Vrai pour une correspondance exacte, vous obtiendrez souvent la bonne réponse. Mais lorsque l'élément que vous recherchez ne figure pas dans le tableau, Excel vous donnera la valeur d'une ligne différente. C'est la partie qui fait de «True» un non-starter pour tout le monde en comptabilité. La fermeture n'est jamais correcte dans la comptabilité.
Remarque
J'ai appris le truc suivant de Charles Williams. Il est le plus grand expert au monde de la vitesse des feuilles de calcul. Si vous avez un cahier de travail lent, engagez Charles Williams pour une demi-journée de consultation. Il peut trouver les goulots d'étranglement et rendre votre feuille de calcul plus rapide. Trouvez Charles sur http://www.decisionmodels.com.
Alors que moi-même et tous les comptables rejetons l'argument «Vrai» de RECHERCHEV en raison de l'imprévisibilité, Charles Williams plaide pour Vrai. Il souligne que le vrai est beaucoup plus rapide que le faux. Des centaines de fois plus vite. Il admet que parfois vous obtenez la mauvaise réponse. Mais il a un moyen de traiter les mauvaises réponses.
Charles veut en fait que vous fassiez deux RECHERCHEV. Tout d'abord, effectuez une RECHERCHEV et renvoyez la colonne 1 du tableau. Voyez si le résultat est ce que vous cherchiez en premier lieu. Si ce résultat correspond, alors vous savez qu'il est sûr de faire la vraie RECHERCHEV afin de renvoyer une autre colonne de la table:
=IF(VLOOKUP(A2,Table,1,True)=A2,"All is good","The Answer will be wrong")
À première vue, cela semble insensé. Pour utiliser la méthode de Charles, vous devez effectuer deux fois plus de RECHERCHEV. Mais, lorsque vous chronométrez le temps de calcul pour cette méthode, il est 35 fois plus rapide que le VLOOKUP normal.
Notez que si la plupart des tables de recherche n'ont pas besoin d'être triées, lorsque vous utilisez True comme quatrième argument, la table doit être triée. Pour une discussion de 7 minutes sur la façon dont la version True de RECHERCHEV passe à travers la table de recherche, consultez http://mrx.cl/TrueVLOOKUP.
Merci à Charles Williams pour m'avoir appris cette fonctionnalité et à Scott St. Amant pour l'avoir proposée pour un top 40.
Regarder la vidéo
- VLOOKUP lorsqu'il est utilisé avec False est une fonction lente
- Le tri des données AZ n'accélère pas la fonction
- Le tri par popularité pourrait accélérer la fonction
- Passer à RECHERCHEV avec True est plus rapide, mais il signalera la mauvaise réponse si l'élément n'est pas trouvé
- Pour atténuer le problème, effectuez une RECHERCHEV (A2, Table, 1, True) pour voir si le résultat est d'abord A2
- 14000 VLOOKUP (True) et 7000 IF s'exécutent plus rapidement que 7000 VLOOKUP (False)
Transcription générée automatiquement
- Apprenez Excel à partir d'un podcast
- épisode 2031 plus rapide vlookup I'm
- baladodiffusion de tous les conseils de ce livre
- cliquez sur le I dans le coin supérieur droit
- pour accéder à la liste de surveillance
- bon retour au mr. casting hutnik
- Je suis Bill Jelen, j'ai fait ça
- vidéo avant que ce soit l'une de mes préférées
- astuces si vous avez le look si vous
- avoir vlookup star prenant 30 40 50
- secondes quatre minutes tu sais quelque chose
- tu vas adorer cette vidéo si tu
- vlookup stick une seconde cliquez simplement sur Suivant
- et passez à la vidéo suivante II a un
- vlookup ici, il regarde dans une table
- sur 115000 éléments faisant 7000 vlookup donc
- nous allons utiliser du Charles Williams
- à partir du code Excel rapide pour voir combien de temps il
- prend pour faire ce vlookup bien quatre
- point zéro neuf secondes c'est le
- vlookup typique avec virgule false à la
- fin et tout cela est venu parce que longtemps
- il y a longtemps, j'ai été appâté par un gars
- Twitter qui a dit que ce serait mieux si
- vous trieriez votre table de recherche a
- envoi j'ai dit non ce n'est pas vrai du tout
- peu importe si nous allons un
- envoi ou descendant ou complètement
- au hasard, le vlookup doit juste aller chercher
- d'article à article en article et ainsi quand nous
- trier le tableau voir qu'il prend réellement
- plus de quatre virgule huit quatre secondes donc
- tu sais que ce n'est pas vrai que le tri
- la table le fera aller plus vite mais
- vraiment la chose qui pourrait le faire aller
- plus vite si vous pouviez trier par
- popularité si vous pouviez obtenir le meilleur
- vendre des articles en haut de la liste
- même tu connais ton top cinquante tu sais
- quels sont vos 50 articles les plus vendus
- mettez-les en tête de liste et
- regarder que pendant les secondes descend à 0,36
- secondes une amélioration décuplée dans le temps
- en utilisant le tri par popularité maintenant hé quelques-uns
- il y a des années, j'ai eu la chance d'être
- invité à Amsterdam pour présenter à un
- Excel sommet là-bas et ce n'est pas comme
- la plupart de mes séminaires où c'est juste moi
- juste il y avait deux pistes donc chambre a
- et la chambre B et moi étions dans la chambre
- parler de vlookups et plus dans la chambre
- une supposition qui était assis dans cette pièce
- Charles Williams allait-il bien et Charles
- voici
- son nom étant mentionné dans le
- mur pour qu'il vienne le regarder il
- regarde ma petite démo là où je vais
- de quatre secondes à 0,36 seconde, il
- vient vers moi après il dit que je parie
- tu es plutôt content de ça
- amélioration
- Je dis ouais c'est une tente pleine
- amélioration maintenant Charles Charles a le
- service d'Excel rapide notre modèle de décision
- les modèles de décision limités dans lesquels nous sommes
- une demi-journée il analysera votre classeur
- et il prétend en faire cent
- fois plus vite, il trouvera le
- goulots d'étranglement Annette et Charles Charles
- vient de il dit regarder la virgule faux
- que vous et vos amis comptables êtes
- le faire est la chose la plus lente dans Excel
- si tu ferais une virgule vrai c'est un
- mille fois plus vite et ensuite Charles
- dit que la clause suivante est si ce n'est pas le cas
- vraiment important il dit maintenant que parfois c'est
- faux oh attend Charles tu ne le fais pas
- comprendre qu'un comptable est parfois
- le mal est un non-démarreur que nous n'acceptons pas
- parfois c'est faux et et l'heure
- que c'est faux la virgule vrai quand
- tu fais une virgule vrai est nous allons regarder
- pour un P 3 2 2 1 1 et c'est introuvable
- ils vont vous donner l'article juste
- moins bien et ils ne le diront pas
- vous nous n'avons pas pu le trouver, ils sont juste
- ils vont juste te donner Adam
- juste moins que ce qui est inacceptable et
- Charles dit bien voici ce que nous pourrions
- imaginez si vous avez fait un vlookup de P 3 2
- 2 1 1 into just column G ask for the
- first column comma true and see if what
- you get back is what you were looking
- for if what you get back is what you're
- looking for then you know it's safe to
- go to the second vlookup if it's not
- what you were looking for then you have
- an if statement there that says not
- found alright so we do a vlookup of a2
- into the table comma 1 see if it's equal
- to 82 if it is then it's safe to go on
- and do the second vlookup otherwise they
- not found I said Charles do you realize
- I'm doing 7000 vlookups and now you're
- gonna be doing 7 14000 vlookups and 7000
- if statements I I said you really think
- this is gonna be faster sure I said well
- I'll bet you a pint alright so here we
- go remember the vlookup that all of us
- are doing with the comma falls 4.0 9
- seconds the sort by popularity which is
- kind of hard to do
- Oh point three seconds here we go here's
- Charles Williams are you ready look at
- that point zero four four one hundredths
- of a second from four seconds down to
- four one hundreds of a second imagine if
- you had a spreadsheet that was taking
- for 40 seconds to calculate and how much
- faster would be using this to vlookup
- method it's an amazing trick and yeah I
- guess I stole the trick and put it in
- the book although I put a great cartoon
- version of Charles Williams in there
- saying he's the fastest guy in all of
- Excel you can buy this tip and all of
- the other tips in this book click that I
- at the top right hand corner
- all right so recap vlookup when used
- with false it's a slow function sorting
- the data a disease does not speed up the
- function unless you sell a lot of things
- to begin with a and B sorting by
- popularity does it's about a tenfold
- augmenter, vous pourriez passer à vlookup
- avec vrai mais ça rapportera le faux
- répondre si les éléments ne sont pas trouvés afin que nous
- va en fait faire deux recherches vlookups un
- deux dans la colonne un à la table et
- voir si ce que nous récupérons est un deux si
- c'est sûr d'aller faire le vlookup dans
- la colonne commune pour avoir autrement un
- si la déclaration dit pas trouvé bien
- oh hé merci à Charles Williams pour
- m'apprendre ce truc incroyable et
- merci à toi d'être passé voir
- vous la prochaine fois pour un autre filet de
- MrExcel
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2031.xlsm
