Якщо у вас великий робочий аркуш, багато VLOOKUP можуть почати гальмувати ситуацію. У вас повільний аркуш завдяки VLOOKUP? Я говорю про робочий аркуш, для обчислення якого потрібно 40 секунд або 4 хвилини. У сьогоднішній статті дивовижна формула з двома VLOOKUP, що використовують пошук діапазону, вирішить проблему.
VLOOKUP - відносно дорога функція. Коли ви шукаєте точну відповідність, Excel повинен переглядати таблицю пошуку по одному рядку за раз.
Книга, якою я користуюся сьогодні, містить 7000 ПЕРЕГЛЯДІВ у таблиці з 116000 предметів. На справді швидкій 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 через непередбачуваність, Чарльз Вільямс доводить правду. Він зазначає, що Істинне набагато швидше, ніж Неправдиве. Швидше в сотні разів. Він визнає, що іноді ви отримуєте неправильну відповідь. Але у нього є спосіб боротися з неправильними відповідями.
Чарльз насправді хоче, щоб ви зробили два VLOOKUP. Спочатку виконайте ПЕРЕГЛЯД і поверніть стовпець 1 із таблиці. Подивіться, чи результат - це те, що ви шукали спочатку. Якщо цей результат збігається, ви знаєте, що безпечно зробити справжній VLOOKUP, щоб повернути якийсь інший стовпець із таблиці:
=IF(VLOOKUP(A2,Table,1,True)=A2,"All is good","The Answer will be wrong")
На перший погляд, це здається божевільним. Щоб скористатися методом Чарльза, вам потрібно зробити вдвічі більше VLOOKUP. Але, коли ви підраховуєте час обчислення для цього методу, це в 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 з подкасту
- епізод 2031 швидший vlookup я
- подкастинг усіх порад цієї книги
- натисніть I у верхньому правому куті
- щоб перейти до списку спостереження
- привіт, ласкаво просимо до пана. гутник литий
- Я Білл Джелен Я зробив це це
- відео, перш ніж воно стане одним з моїх улюблених
- трюки, якщо ти маєш зовнішній вигляд, якщо ти
- мати vlookup зірку, яка бере 30 40 50
- секунди чотири хвилини ви щось знаєте
- вам сподобається це відео, якщо ваш
- vlookup stick одна секунда просто натисніть Далі
- і перейти до наступного відео II є
- vlookup тут він переглядає таблицю
- з 115000 предметів, які роблять 7000 перегляду таким чином
- ми використаємо Чарльза Вільямса
- з швидкого коду Excel, щоб побачити, як довго він триває
- потрібно зробити це vlookup добре чотири
- точка нуль дев'ять секунд, це
- типовий пошук з комою помилковий на
- кінець, і це все придумано, бо довго
- давно мене нажив якийсь хлопець
- Twitter, який сказав, що було б краще, якби
- ви б відсортували вашу таблицю пошуку a
- надіславши, я сказав: ні, це зовсім не так
- не має значення, чи підемо ми
- відправлення або спадання або повністю
- випадковий пошук просто повинен шукати
- від елемента до предмета до предмета і так коли ми
- відсортувати таблицю, побачити, що це насправді потрібно
- довше чотири очки вісім чотири секунди так
- Ви знаєте, що неправда, що сортування
- таблиця змусить його йти швидше, але
- насправді те, що могло змусити його піти
- швидше, якби якось можна було відсортувати
- популярність, якби ви могли отримати найкраще
- продаж товарів у верхній частині списку
- навіть ти знаєш свою п'ятдесят, яку знаєш
- які ваші 50 найкращих товарів
- довести їх до початку списку та
- дивіться, що за секунди опускається до 0,36
- секунд десятикратне покращення в часі
- використання сортування за популярністю зараз привіт кілька
- років тому мені пощастило бути
- запрошений в Амстердам для презентації на
- Саміт Excel там, і це не подобається
- більшість моїх семінарів, де це лише я
- тут було дві колії, тому кімната a
- і кімната B, і я закінчився в кімнаті be
- говорити про влуки і більше в кімнаті
- здогадка, хто сидів у цій кімнаті
- був Чарльз Вільямс добре і Чарльз
- ось
- його ім'я згадується через
- стіни, щоб він підійшов, щоб подивитися
- дивиться мою маленьку демонстрацію там, де я ходжу
- від чотирьох секунд до 0,36 секунд він
- згодом підходить до мене, він каже, що я ставлю
- ви цілком задоволені цим
- вдосконалення
- Я кажу так, що намет повний
- Поліпшення тепер у Чарльза Чарльза
- послуга швидкого Excel - наша модель рішення
- моделі прийняття рішень обмежені
- півдня він проаналізує вашу робочу книжку
- і він стверджує, що це складає сотню
- в рази швидше, він знайде
- вузькі місця Аннет і Чарльз Чарльз
- походить від він каже дивитись на кому помилково
- що ви та ваші друзі-бухгалтери
- робити це найповільніше в Excel
- якщо ви робите кому істиною, це a
- в тисячі разів швидше, а потім Чарльз
- каже, що наступний пункт, якщо це не так
- справді має значення, що він каже зараз, іноді це так
- не так, чекай, Чарльзе, ти цього не робиш
- розуміти бухгалтера іноді так
- неправильно - це не стартер, який ми не приймаємо
- іноді це неправильно і і час
- що це неправильна кома правда коли
- ти робиш кому вірно, це ми йдемо дивитись
- для P 3 2 2 1 1, і його не знайдено
- вони дадуть вам товар просто
- менш добре, і вони не збираються розповідати
- ти не зміг знайти, вони просто
- вони просто дадуть тобі Адама
- ще менше, що це неприпустимо і
- Чарльз добре каже, ось що ми могли б
- уявіть, якщо ви робили перегляд 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, але він повідомить про помилку
- відповісти, якщо предметів не знайдено, тож ми
- насправді збираюся зробити два пошуки
- два в колонці один за столом і
- подивіться, якщо це те, що ми отримаємо назад, це два if
- це безпечно перейти до vlookup
- загальний стовпець, щоб інакше мати
- якщо в заяві сказано, що не знайдено все гаразд
- ой привіт дякую Чарльзу Вільямсу за
- навчивши мене цього дивовижного трюку і
- спасибі тобі за заїзд побачиш
- Ви наступного разу на ще один чистий акторський склад від
- MrExcel
Завантажити файл
Завантажте зразок файлу тут: Podcast2031.xlsm
