Если у вас большой рабочий лист, множество ВПР могут замедлить работу. У вас медленный рабочий лист из-за ВПР? Я говорю о листе, на вычисление которого требуется 40 секунд или 4 минуты. В сегодняшней статье проблему решит удивительная формула с двумя VLOOKUP, использующими поиск по диапазону.
ВПР - относительно дорогая функция. Когда вы ищете точное совпадение, Excel должен просматривать таблицу поиска по одной строке за раз.
Рабочая тетрадь, которую я использую сегодня, выполняет 7000 ВПР в таблице из 116 000 элементов. На действительно быстрой 64-битной машине с 8 ядрами время пересчета составляет 3,01 секунды.
Один из способов улучшить функцию ВПР - переместить самые продаваемые элементы в верхнюю часть таблицы поиска. Получите отчет о 100 самых продаваемых товарах и переместите их в верхнюю часть списка. Сортировка по популярности сокращает время пересчета до 0,369 секунды. Это в восемь раз быстрее первого результата.
Но есть способ еще больше ускорить процесс. Когда вы строите свою ВПР, когда вы дойдете до четвертого аргумента, чтобы выбрать «Ложь», есть еще один вариант, который почти никогда не используется. Excel говорит, что "True" является "приблизительным совпадением". Это совсем не так. Если бы команда Excel была честна, они бы объяснили, что True «часто дает правильный ответ, но в других случаях, без какого-либо предупреждения, мы собираемся вставить туда неправильный ответ. Надеюсь, вы не против сообщить свои цифры Комиссии по ценным бумагам и биржам ».
Конечно, сейчас самое время использовать True. См. Эту статью. Но было бы очень плохо использовать True, когда вы пытаетесь добиться точного совпадения.
Если вы попытаетесь использовать True для точного совпадения, в большинстве случаев вы получите правильный ответ. Но когда искомого элемента нет в таблице, Excel предоставит вам значение из другой строки. Это та часть, которая делает «True» бесполезным для всех, кто занимается бухгалтерским учетом. Close в бухгалтерии никогда не бывает правильным.
Заметка
Я научился следующему трюку от Чарльза Вильямса. Он является ведущим в мире экспертом по скорости работы с листами. Если у вас медленная рабочая тетрадь, наймите Чарльза Уильямса на консультацию на полдня. Он может найти узкие места и ускорить ваш рабочий лист. Найдите Чарльза на http://www.decisionmodels.com.
В то время как я и все бухгалтеры отвергаем аргумент «Истина» о ВПР из-за ее непредсказуемости, Чарльз Уильямс отстаивает «Истину». Он указывает, что Истина намного быстрее, чем Ложь. В сотни раз быстрее. Он признает, что иногда вы получаете неправильный ответ. Но у него есть способ справиться с неправильными ответами.
Чарльз на самом деле хочет, чтобы вы провели две ВПР. Сначала выполните ВПР и верните столбец 1 из таблицы. Посмотрите, является ли результат именно тем, что вы искали в первую очередь. Если этот результат совпадает, значит, можно безопасно выполнить реальную ВПР, чтобы вернуть какой-либо другой столбец из таблицы:
=IF(VLOOKUP(A2,Table,1,True)=A2,"All is good","The Answer will be wrong")
На первый взгляд это кажется безумием. Чтобы использовать метод Чарльза, вам нужно сделать вдвое больше ВПР. Но если вы рассчитываете время расчета для этого метода, оно в 35 раз быстрее, чем при обычном режиме ВПР.
Обратите внимание, что, хотя большинство таблиц поиска не нужно сортировать, при использовании True в качестве четвертого аргумента таблица действительно должна быть отсортирована. Для 7-минутного обсуждения того, как истинная версия VLOOKUP перескакивает через таблицу поиска, см. Http://mrx.cl/TrueVLOOKUP.
Спасибо Чарльзу Уильямсу за то, что научил меня этой функции, и Скотту Сент-Аманту за то, что он был номинирован на 40 лучших советов.
Смотреть видео
- ВПР при использовании с False - медленная функция
- Сортировка данных по AZ не ускоряет работу
- Сортировка по популярности может ускорить работу
- Переключение на ВПР с True выполняется быстрее, но он сообщит о неправильном ответе, если элемент не найден.
- Чтобы смягчить проблему, выполните ВПР (A2, Таблица, 1, Истина), чтобы сначала увидеть, будет ли результат A2
- 14000 ВПР (True) и 7000 IF работают быстрее, чем 7000 VLOOKUP (False)
Автоматически созданная стенограмма
- Изучите Excel из подкаста
- серия 2031 быстрее vlookup я
- подкастинг всех советов из этой книги
- нажмите I в правом верхнем углу
- чтобы попасть в список просмотра
- привет добро пожаловать обратно в MR. хутник литой
- Я Билл Джелен, я сделал это
- видео, прежде чем оно станет одним из моих любимых
- уловки, если у вас есть взгляд, если вы
- получить звезду vlookup 30 40 50
- секунды четыре минуты ты что-нибудь знаешь
- тебе понравится это видео, если ты
- vlookup stick на одну секунду просто нажмите Next
- и переходите к следующему видео. Я
- vlookup здесь он смотрит в таблицу
- из 115000 элементов выполняется 7000 просмотров, поэтому
- мы собираемся использовать Чарльза Вильямса
- из быстрого кода Excel, чтобы узнать, как долго он
- нужно сделать это vlookup хорошо четыре
- точка ноль девять секунд это
- типичный vlookup с запятой false на
- конец, и все это произошло, потому что долго
- Давным-давно меня заманил какой-то парень на
- Twitter, который сказал, что будет лучше, если
- вы бы отсортировали свою таблицу поиска
- отправка я сказал нет, это совсем не правда
- не имеет значения, пойдем ли мы
- отправка или по убыванию или полностью
- случайный vlookup просто должен пойти посмотреть
- от элемента к элементу и поэтому, когда мы
- отсортируйте таблицу, посмотрите, что на самом деле нужно
- дольше четыре целых восемь десятых секунды так
- вы знаете, это неправда, что сортировка
- стол сделает его быстрее, но
- действительно то, что могло заставить его пойти
- быстрее, если бы вы могли отсортировать
- популярность, если бы вы могли получить лучшее
- продажа предметов в верхней части списка
- даже вы знаете свои лучшие пятьдесят вы знаете
- какие у вас 50 самых продаваемых товаров
- выведите их в начало списка и
- посмотрите, что в течение секунд опускается до 0,36
- секунд - десятикратное улучшение времени
- используя сортировку по популярности сейчас эй несколько
- лет назад мне посчастливилось быть
- приглашен в Амстердам для выступления на
- Саммит Excel там и не похоже
- большинство моих семинаров, где только я
- здесь было две дорожки, так что комната
- и комната B, и я был в комнате быть
- говорить о 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
- с правдой, но он сообщит о неправильном
- ответьте, если товар не найден, поэтому мы
- на самом деле собираюсь сделать два просмотра vlookup
- два в первом столбце за столом и
- посмотрим, если это то, что мы получим, это два, если
- это безопасно сделать vlookup в
- общий столбец, чтобы в противном случае
- если в заявлении говорится, что не найдено, все в порядке
- эй, спасибо Чарльзу Уильямсу за
- научил меня этому удивительному трюку и
- спасибо, что заглянули, увидим
- вы в следующий раз для другой сети
- MrExcel
Скачать файл
Загрузите образец файла здесь: Podcast2031.xlsm
