Ако имате голям работен лист, много VLOOKUP могат да започнат да забавят нещата. Имате ли бавен работен лист поради VLOOKUP? Говоря работен лист, който изисква 40 секунди или 4 минути за изчисляване. В днешната статия невероятна формула с две VLOOKUP, използващи търсене на диапазон, ще реши проблема.
VLOOKUP е относително скъпа функция. Когато търсите точно съвпадение, Excel трябва да преглежда таблицата за търсене един по един ред.
Работната книга, която използвам днес, прави 7000 VLOOKUP в таблица от 116 000 елемента. На наистина бърза 64-битова машина с 8 ядра времето за повторно повторно измерване е 3,01 секунди.
Един от начините за подобряване на VLOOKUP е да преместите най-продаваните артикули в горната част на таблицата за търсене. Вземете отчет за топ 100 на най-продаваните артикули и ги преместете в горната част на списъка. Сортирането по популярност подобрява времето за повторно повтаряне до 0,369 секунди. Това е осем пъти по-бързо от първия резултат.
Но има начин да ускорите нещата още повече. Докато изграждате своя VLOOKUP, когато стигнете до четвъртия аргумент, за да изберете False, има друга опция, която едва ли някога се използва. Excel казва, че „True“ прави „приблизително съвпадение“. Това изобщо не е правилно. Ако екипът на Excel беше честен, те щяха да обяснят, че True „доставя верен отговор през повечето време, но друг път, без никакво предупреждение, ще поставим грешния отговор там. Надявам се, че нямате нищо против да препрограмирате номерата си в Комисията за ценни книжа и борси. "
Разбира се, има подходящо време за използване на True. Вижте тази статия. Но би било наистина лошо да използвате True, когато се опитвате да направите точно съвпадение.
Ако се опитате да използвате True за точно съвпадение, много пъти ще получавате верния отговор. Но когато елементът, който търсите, не е в таблицата, Excel ще ви даде стойността от различен ред. Това е частта, която прави “True” неподходящ за всички в счетоводството. Затварянето никога не е правилно в счетоводството.
Забележка
Следния трик научих от Чарлз Уилямс. Той е най-големият експерт в света за скоростта на работния лист. Ако имате бавна работна книга, наемете Чарлз Уилямс за половин ден консултации. Той може да намери пречките и да направи работния ви лист по-бърз. Намерете Чарлз на адрес http://www.decisionmodels.com.
Докато аз и всички счетоводители отхвърляме аргумента „True“ на VLOOKUP поради непредсказуемостта, Чарлз Уилямс се аргументира за True. Той посочва, че истината е много по-бърза от фалшивата. Стотици пъти по-бързо. Той признава, че понякога получавате грешен отговор. Но той има начин да се справи с грешните отговори.
Чарлз всъщност иска да направите две VLOOKUPs. Първо направете VLOOKUP и върнете колона 1 от таблицата. Вижте дали резултатът е това, което първо търсите. Ако този резултат съвпада, тогава знаете, че е безопасно да направите истинското VLOOKUP, за да върнете друга колона от таблицата:
=IF(VLOOKUP(A2,Table,1,True)=A2,"All is good","The Answer will be wrong")
На пръв поглед това изглежда безумно. За да използвате метода на Чарлз, трябва да направите двойно повече VLOOKUPs. Но когато определяте времето за изчисление за този метод, то е 35 пъти по-бързо от нормалното VLOOKUP.
Имайте предвид, че докато повечето справочни таблици не трябва да бъдат сортирани, когато използвате True като четвърти аргумент, таблицата трябва да бъде сортирана. За 7-минутна дискусия за това как истинската версия на VLOOKUP прескача през справочната таблица, вижте http://mrx.cl/TrueVLOOKUP.
Благодаря на Чарлз Уилямс, че ме научи на тази функция и на Скот Сент Амант, че я номинира за топ 40 съвета.
Гледам видео
- VLOOKUP, когато се използва с False, е бавна функция
- Сортирането на данните AZ не ускорява функцията
- Сортирането по популярност може да ускори функцията
- Преминаването към VLOOKUP с True е по-бързо, но ще докладва грешен отговор, ако елементът не бъде намерен
- За да смекчите проблема, направете VLOOKUP (A2, Table, 1, True), за да видите дали резултатът е първо A2
- 14000 VLOOKUP (True) и 7000 IF, ако се изпълняват по-бързо от 7000 VLOOKUP (False)
Автоматично генериран препис
- Научете Excel от Podcast
- епизод 2031 по-бързо vlookup съм
- подкастиране на всички съвети в тази книга
- щракнете върху I в горния десен ъгъл
- за да стигнете до списъка за наблюдение
- хей добре дошли обратно при г-н. хътник гласове
- Аз съм Бил Джелен. Направих това
- видео, преди да е едно от любимите ми
- трикове, ако имате външен вид, ако имате
- имат vlookup звезда като 30 40 50
- секунди четири минути знаете нещо
- ще ви хареса това видео, ако вашият
- vlookup стик една секунда просто щракнете върху Напред
- и преминете към следващото видео II имам a
- vlookup тук търси в таблица
- от 115 000 елемента, които правят 7000 vlookup така
- ще използваме малко Чарлз Уилямс
- от бърз код на Excel, за да видите колко дълго
- отнема да направи това vlookup добре четири
- точка нула девет секунди това е
- типично vlookup със запетая false в
- край и всичко това се появи, защото дълго
- отдавна бях стръвен от някакъв тип
- Twitter, който каза, че би било по-добре, ако
- можете да сортирате вашата справочна таблица a
- изпращайки казах, че това изобщо не е вярно
- няма значение дали ще отидем a
- изпращане или спускане или изцяло
- произволно vlookup просто трябва да отиде да търси
- от елемент на елемент на елемент и така когато ние
- сортирайте таблицата, за да видите всъщност
- по-дълго четири точки осем четири секунди така
- знаете, че не е вярно, че сортирането на
- таблицата ще го накара да върви по-бързо, но
- наистина това, което би могло да го накара да си отиде
- по-бързо, ако по някакъв начин можете да сортирате
- популярност, ако можете да получите най-доброто
- продажба на артикули в горната част на списъка
- дори вие знаете своите петдесет, които знаете
- кои са вашите топ 50 най-продавани артикула
- изведете тези в началото на списъка и
- гледайте, че за секунди спада до 0,36
- секунди десетократно подобрение във времето
- използване на сортиране по популярност сега ей няколко
- преди години имах късмета да бъда
- поканен в Амстердам за представяне на
- Excel на върха там и не е като
- повечето от моите семинари, където съм само аз
- точно имаше две писти, така че стая a
- и стая Б и бях свършил в стая бе
- говорим за vlookups и повече в стаята
- познайте кой е седял в тази стая
- беше Чарлз Уилямс добре и Чарлз
- ето
- името му се споменава чрез
- стена, така че той идва да го гледа той
- гледа моята малка демонстрация там, където отивам
- от четири секунди до 0,36 секунди той
- излиза при мен след това той казва, че съм заложил
- доста си доволен от това
- подобрение
- Казвам, да, пълна е палатка
- подобрение сега Чарлз Чарлз има
- услуга за бърз Excel нашият модел за вземане на решения
- моделите за вземане на решения са ограничени
- половин ден той ще анализира вашата работна книга
- и той твърди, че го прави сто
- пъти по-бързо, нали ще намери
- тесни места Анет и Чарлз Чарлз
- comes from he says look at comma false
- that you and your accountant friends are
- doing it is the slowest thing in Excel
- if you would do a comma true it's a
- thousand times faster and then Charles
- says this next Clause is if it doesn't
- really matter he says now sometimes it's
- wrong oh wait Charles you don't
- understand an accountant sometimes is
- wrong is a non-starter we do not accept
- sometimes it's wrong and and the time
- that it's wrong the comma true when
- you're doing a comma true is we go look
- for a P 3 2 2 1 1 and it's not found
- they're gonna give you the item just
- less alright and they're not gonna tell
- you we couldn't find it they're just
- gonna they're just gonna give you Adam
- just less that that's unacceptable and
- Charles says well here's what we could
- do imagine if you did a vlookup of 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
- увеличаване можете да преминете към vlookup
- с true, но ще докладва за грешката
- отговори, ако елементите не са намерени, така че ние сме
- всъщност ще направя две vlookups търсения
- две в колоната едно на масата и
- вижте дали това, което получаваме обратно, е две, ако
- безопасно е да отидете да направите vlookup в
- общата колона, за да има иначе
- ако твърдение пише, че не е намерено добре
- о, ей, благодаря на Чарлз Уилямс за
- научи ме на този удивителен трик и
- благодарим ви, че се отбихте ще видите
- следващия път за друг нетен актьор от
- MrExcel
Свали файл
Изтеглете примерния файл тук: Podcast2031.xlsm
