7 видов ВПР - кто самый быстрый?
Вставка
- Опубліковано 16 лют 2020
- Тестируем на скорость 7 разных способов поиска и подстановки данных (ВПР, ПРОСМОТРХ, ИНДЕКС+ПОИСКПОЗ и т.д.)
Скачать пример www.planetaexcel.ru/technique...
Заходите в гости ↓↓↓
Twitter / planeta_excel
Facebook / planetaexcel
Instagram / planetaexcel
Мои книги planetaexcel.ru/books/
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru/plex/
Какое шикарное видео. Для меня не столько скорость была важна и интересна, а то, как можно одну и ту же задачу выполнить разными способами, спасибо большое за урок. Все формулы, которые хотела давно освоить в одном видео.
Высший пилотаж! Чувствуется, по интонации, что вы получаете удовольствие от опытов;) и от своей работы - заряжает).
Спасибо! :)
Благодарю вас за ваши знания и труды!
Благодаря вашей информации уходила вовремя с работы).😘
Как всегда интересно и полезно! Ждём следующих вдохновляющих видосов!
За обзор огромное спасибо! Результат - нежданчик, однако. Предполагал, что чем новее функция, тем она быстрее. Ан нет. Ещё раз спасибо. В ожидании следующих видео.
Спасибо! Очень полезно и интересно!
Отдельное спасибо за хорошо поставленную речь, дикцию и анимацию всех действий!
Николай, очень благодарен Вам за такое полезное видео! Супер! Данная информация для меня очень актуальна сейчас! Здорово, что так совпало! Даже Дмитрий Езепов оставил комментарий))) Молодец! Приятно видеть, как один мастер хвалит другого! Это здорово!)))
Спасибо, Николай! Как всегда, все по делу и понятно!
Огромное спасибо за ваши уроки! работаю в университете и благодаря Вам сильно упростил и автоматизировал свою работу! Здоровья Вам и успеха!
Как я люблю ваши разборы!
Николай, спасибо за видео! Впр с полным диапазоном стало приятным открытием, да ещё и быстрым!
Это невероятно! С помощью ваших знаний я прокачиваюсь! Узнаю все новые и новые плюшки данной программы.
Добрый день. Отличный ролик Николай. Чувствую себя асом Excel. Буду смотреть и другие видеоуроки. Спасибо вам огромное за ваш труд.
Здравствуйте! Пересмотрел достаточное количество видео по Excel. Ваши видеоуроки - лучшие: алгоритм передачи знаний, ОТСУТСТВИЕ музыкального сопровождения, дикция, корректные формулировки. Зарегистрировался на Вашем сайте, с удовольствием. Весьма полезно изучить в печатном формате учебный материал. Благодарю за труд!
Спасибо за видео. Снимайте больше видео!!!
Спасибо, Николай, за Ваш труд. Недавно приобрёл Вашу книгу по Power Query, которая является весьма сильным и внушительным дополнением к моей предыдущей книге ,,Сводные таблицы в MS Excel 2016" Билла Джелена и Майкла Александра.
Николай, спасибо за интересный обзор. Однако ВПР только в прошлом году сделали шустрее. Раньше это была одна из медленных функций Excel. Динамические массивы - огонь ))
Да, ВПР в прошлом году знатно прокачали :) ДМ - бомба, да :)
Тот самый случай когда нарвался на диалог двух специалистов, от которых двоих в восторге.
@@planetaexcel привет) можете посоветовать биржу для поиска простеньких заказов по эксель? Хочу попробовать свои силы))
ОДИН И ТОТ ЖЕ ПРЕПАРАТ В НЕСКОЛЬКО ПОЗИЦИЙ С КОЛ-ВОМ ЗАПИСАН,КАК ЕГО ОБЬЕДИНИТЬ В ОДНУ СТРОКУ ПОЖАЛУЙСТА ПОМОГИТЕ
Суммесли, либо сумм(если) формулой массива
До начала просмотра был уверен в безоговорочном преимуществе связки Индекс-Поискпоз... Но оказалось в новых версиях все немного иначе))) Николай, спасибо за обзор. Как всегда на высоте.
Как всегда на высоте! Спасибо!
Огромное спасибо! ООООчень полезное видео!
Лучший канал по Excel. Делаю скорее набрать 100 тыс. подписчиков
Класс! Спасибо!!! Шикарное видео!!!!
Большое спасибо за видеоурок!
Большое спасибо !
Вы очень сильно помогли)
Очень интересный урок! Спасибо)
Спасибо огромное, вы профессионал!
я мечтала о таком сравнении, спасибо ВАМ ОГРОМНОЕ
Я посмотрел, спасибо вам!
Только вот понят бы это все)) Попробую посмотреть еще раз
Спасибо вам огромное как раз решили мою проблему почти посмотрел все ваше видео удачи, счастья, успехов в вашем жизни вы полекчили мою работу пусть АЛЛАХ даст вам самое ценное для мусульман.
Спасибо, Тимур :)
Кстати. Николай, было бы интересно провести подобный анализ между формулами и сводными таблицами.
Огромное спасибо!
Интересно! Спасибо
F4 - это Гениально, спасибо Человечище!
Спасибо, очень полезно
Огромное спасибо.
ВПР удивил :) на узких задачах, пожалуй, идеальный выбор
Супер сравнение)
17:12 - ракета!!!
это просто нечто! нужно обновить ексель )
Николай как всегда ураган.спасибо огромное!
Пожалуйста :)
Вернулся к этому видео, когда на работе ОДНА формула vlookup вызвала увеличение размера файла в несколько десятков раз: из пары килобайт до пяти МЕГАБАЙТ! Удалил единственный vlookup, сохранил заново, размер вернулся в пару килобайт. Создалось впечатление, что vlookup подтянул к себе в файл, из которого он был вызван всю исходную таблицу в более 200к строк.
Так я и стал активным пользователем Power Query.😊
Как всегда лайк!!!!
Очень хочется посмотреть на скорость выполнения:
- при использовании функции выбор
- при буферизации таблицы 2
- при использовании ссылок на строки таблицы 2
Великолепнейший ролик! Николай, как думаешь, в будущем Power Query наделят динамическими массивами? и вообще, весь Excel, чтобы быстрей работал?
Класс!
спасибо, Николай, за опытный эксперимент. Делаем выводы)
"Суммпроизв" на мой взгляд самый крутой. Потому что он всё таки суммирующий, как часто можно столкнуться с повторениями строк в кассовых отчетах и тп. Я за old school. Если поискпозХ может также суммировать в купе с сумм() то я конечно буду рад пользоваться и этой формулой в будущем.
Многие задачи без PQ вообще не решить. Мой результат при переходе на PQ - 4 минуты вместо почти 50 минут на ВПР и ИНДЕКС (с умными таблицами, разумеется). Просто за счет отказа от целой кучи ставших ненужными промежуточных таблиц, т.к. у эксела ограничена длина формулы.
круто круто круто, вы сделали мой день))
Крайне познавательно
Крутой!
Супер
Очень информативно, доступно для понимания, просто супер. А вот недавно мне задали такой вопрос , на который я не смогла ответить, подскажите вы пожалуйста : Сколько будет ВПР-ГПР=?
С каждым видео чувствую себя всё ущербнее и ущербнее. Еще расти и расти... Столько нового, актуального! Вперед, грести!
Дмитрий, у меня это чувство не проходит с Excel уже лет 15 :)
Самый медленный способ - Power Query, так как, во-первых, для изучения и понимания этого метода требуется гораздо больше времени, а, во-вторых, требуется гораздо больше времени на создание запросов, чем на написание функций.
Николай, благодарю за интересный материал. Вопрос - в таблице Слияние 1, которую мы получили в результате эксперимента с Power Query, данные отображаются в другом порядке. По какому принципу они отфильтровались, и можно ли как-то вернуться к исходному порядку отображения данных? Спасибо!
Николай, большое спасибо за сравнение! И вопрос: будет ли влиять на скорость и как, если файл с данными ( с таблицами) сохранен не просто как "книга эксель", а как "двоичная книга эксель"? О том, что размер файла при таком сохранении значительно уменьшается, я знаю, но как это отражается на пересчетах?
Николай,
просто бомба!
Время засекал с помощью VBA?
Само-собой :) Timer рулит ;)
Можно ставить лайк и посмотреть позже😊👍👍👍
Самое главное это Проэфчетыреть!!! 🥰
Очень полезное видео, спасибо, в качестве пожелания просьба излагать материал быстрее
А кому-то я слишком быстрый :) Дмитрий, увеличьте скорость воспроизведения (значок шестеренки в правом нижнем углу), если я для вас медленный - делов-то! :)
Nikolay Pavlov точно, постоянно забываю эту функцию
Спасибо за видео вам. Лично я ВПР , делаю через формулу выбирая в разделе Формулы в командной части. А не пишу = и далее. Мне кажется что так быстрее, чем писать в ручную.
Тут вопрос привычки :)
Помню, году в 9-ом, в самом начале, ВПРом таблички делал, в магазине по продажам с личным планом, планом магазина и тд... один продавец одна строчка.. 24 чтоль продавана было :) ну чтоб с утреца выгрузить и каждый видел сколько он заработал, и что надо делать чтоб заработать больше :) Многого, конечно, не знал, да и многого тогда еще не было, одна и та же таблица на магазином сервере считалась 3-4 секунды, на домашнем, весьма не слабом на тот момент компе, около 5 минут, ноутбук... даже на мышку не реагировал 20 минут :)
Спасибо за видео!
В 2013 стараюсь не пользовать впр, так как при каждом действии с таблицей комп занят, подозреваю, пересчитывает формулы. Только PQ :)
На больших задачах PQ выигрывает, однозначно :)
Так можно же включить ручной пересчет и когда надо нажимать F9.
@@zibroyd можно только зачем? Есть PQ, ничего не пересчитывается, не тревожатся настройки и ф9, всё сказочно.
Кроме того, если бы нужно было сделать один впр, то это фигня. А если нужно выполнить кучу расчетов, то чего мучаться? Сделал по-максимуму в PQ, выгрузил в PP, добавил вычисления - и сидишь довольный.
Здрастуйте, сейчас было бы актуально сравнить эти функции с новой xlookup
Видео супер!!! А интересно, какой даст результат множественный ВПР (ПРОСМОТР), срабатывающий на одном значении, типа:=ПРОСМОТР(2;1/($G$2:$G$596=B2);$H$2:$H$596)? Да, и ПРОСМОТР при таком применении не требует сортировки диапазона поиска?
Добрый день. Подскажите, а возможно в екселе ставить напоминание по датам? Или календарь - это единственное место. У меня контроль по испытательных сроках. Таблицу веду в екссель, переносить все в календарь морока - долго. Есль другие варианты? Буду очень благодарна за ответ.
Такие эксперименты могут привести к печальным результатам. Например в прайсе могут быть случайно повторяющиеся значения, которые просуммируются. Выделение диапазона, а не столбцов целиком хоть и ускоряет расчеты, но при добавлении строк все расчеты ломаются и обычный пользователь этого может не заметить.
Спасибо. Теперь у меня есть настольное видео) а можно подобное сравнение для суммирования по условию? Формулы Динамических массивов есть соответствующие?
Есть новая мегафункция ФИЛЬТР, которую можно засунуть в СУММ ;)
Подскажите пожалуйста способ автоматически выводить даты только восресных дней в заданый период времени. Большое спасибо.
Добрый день! Просьба ответить на пару вопросов. Заранее спасибо)
1. Таблице Power Query можно дать имя, сделать из нее умною таблицу, и потом использовать в других формулах, в том числе в другом ВПР, ссылаясь на нее по имени таблицы и названию столбца?
2. В функции ВПР можно ссылаться на сводные таблицы, как это сделать? У меня не получилось, не видит имя сводной таблицы.
Николай добрый день, подскажите пожалуйста в следующем вопросе. Есть умная таблица и в одной из ячеек указано дата крайний срок выплаты. Можно ли сделать так чтобы когда подходил срок за неделю,за две до данной даты указаны в ячейке, весь этот ряд окрашивался в жёлтый цвет а когда срок настал или прошол уже, данная дата и весь ряд окрашивался допустим в красный цвет автоматически
существует и функция или комбинация функций ?? Спасибо, excel 2013
Время ВПР увеличилось в случае выделения полностью всего массива колонок из-за того, что они в одной вкладке (т.к. функция ищет по крайнюю последнюю позицию листа, а у Вас их 500 тыс., а не 0,6...т.е. линейкой длинны искомого массива можете использовать ползунок;) )
Спасибо за обзор!
Соглашусь - на разных листах было бы быстрее :)
Николай, здравствуйте.
Благодарен за информативное видео и убедительное доказательство плюсов использования умных таблиц, но как быть в ситуации использования функции ВПР для поиска в умной таблице, в случаях, когда поиск совпадения ведется не в первом столбце умной таблицы?
Тогда вместо имени умной таблицы во втором аргументе ВПР пишете что-нибудь типа:
Таблица1[Наименования:Цены]
где Наименования - столбец с которого нужно начать поиск, а столбец Цены - до которого нужно продлить выделение вправо.
Nikolay Pavlov благодарю за ответ. Думаю, что ответ будет полезен многим, кто начнет внедрять умные таблицы и сразу споткнётся. После просмотра видео пытаюсь переупаковать таблицы в умные для увеличения скорости работы с данными. Можно ли подтягивать данные из умных таблиц (впр и пр. инструментами) при работе в разных книгах? Когда я работаю в одной книге с умными таблицами, то при написании впр всплывает имя таблицы, при работе в разных книгах приходится прописывать диапазон, номер таблицы, т.е. как обычно.
Николай, долго думал можно ли упростить формулу ВПР. Например, избавиться от необходимости прописывать номер искомого столбца.
Как вариант, нашел способ ВПР, когда по умолчанию выбирается последний столбец в выделенном диапазоне.
Хотел бы поделиться.
Function ВПРР (Продукт As Range, Таблица)
i = Таблица.Columns.Count
ВПРР = Application.VLookup(Продукт,Таблица, i, 0)
End Function
То есть,прописывается только искомое значение (Продукт) и выделенный диапазон (Таблица) до столбца с нужным параметром (Цена, Количество,Клиент....). Точное совпадение тоже по умолчанию "вшито" в базовую формулу. Поскольку 1 практически никогда не используют.
Единственное, я не очень хорошо разбираюсь в разнице "форматов" Single, Integer, Range, String. Поэтому не знаю, насколько правильно писать Продукт As Range. Или можно просто Function ВПРР (Продукт, Таблица)
....
Можно и так. Но это уже макросы :)
Здравствуйте. можете помочь с таблицей?
Добрый день, смотрю видео ваши и есть у меня не решенный вопрос, я делаю обычную таблицу продаж через ВПР в этом же листе делаю наименование продуктов (около 60 позиций) , чтобы автоматом выскакивало имя товара и цена закупки, но вот как сделать (реально ли вообще), чтобы при продаже , снималось со склада проданная позиция (иногда забываю делать ручками и делаю тогда инвентаризацию) помогите кто знает???
привет, эту программу используют в Европе , в США ?
Николай, спасибо за ваш труд. Вопрос: если мне нужно при помощи Power Query сделать то, что вы сделали, но мне не нужна новая таблица на новом листе. У меня есть существующая таблица и мне нужно в нее подставить разные вычисляемые столбцы, то как это можно сделать?
Никак. PQ выгружает данные в отдельную таблицу, к сожалению.
Интересно,что на самых быстрых способах у меня Excel просто зависал и переставал отвечать. 8 ГБ ОЗУ и 200 ГБ свободного места на диске. С чем может быть связано?
Справедливости ради, стоило еще проверить случай, когда впр делается из таблицы с 10-15 столбцами с различной информацией и впрится не 2й, а 10-15 столбец. Скорее всего индекс + поискпоз будет быстрее работать в данном случае
Здравая мысль. Добавил к прайсу еще 20 столбцов с данными и протестировал вашу версию.
ВПР = 4.1 сек
ИНДЕКС+ПОИСКПОЗ = 7.4 сек
Количество столбцов в поисковой таблице, похоже, никак не влияет на процесс :)
@@planetaexcel Спасибо за тест, буду знать))
А что не так с суммпроизв?
Традиционно в этот обзор можно было бы добавить и пару вариантов с самописными функциями по средствам VBA
Они точно были бы самыми медленными :) VBA почти всегда медленнее, чем любой встроенный инструмент, по моему опыту.
Николай, добрый день! Всё-таки говоря о скорости работы, мне кажется, нельзя упрощенно рассказывать про последний параметр ВПР/ПОИСКПОЗ. Как вы знаете, этот параметр включает механизм бинарного поиска и раньше оказывал решающее влияние на скорость пересчета. Да, необходимо обеспечить сортировку и контроль ошибок, но для тех кто ждёт по 5 минут, пока пересчитается, это может оказаться вполне себе необременительным условием. К тому же при росте размера справочника значение параметра будет расти.
А вот то, что умная таблица настолько ускоряет поиск - не знал, это ценная информация.
Поиск текста в режиме приблизительного поиска может выйти боком, если есть хоть одно несуществующее значение - подставит не то. А проверка обязательного наличия всех значений заранее - съест весь выйгрыш в скорости от бинарного поиска, как мне кажется :)
В PQ есть проблема с дублями. Если в одном масиве 2 значения, а в другом 3, то он сливает все и находит 6 результатов. Это иногда портит картину, и нужно дополнительно чистить дубликаты. Этого нет в ВПР
Как сравнить две ячейки текстовых на наличие в них различий??? Помогите
Добрый день! Существует формула = D2 & "," & C2 & "," & B2 & "," & A2, где ячейки объединяются, образуя строку с текстом. Вы бы порекомендовали видеоурок, который показывает идею в виде запятой "," удаление, если в ячейке нет значения? Пример: ,Olaines pag., Olaines nov.
Я бы использовал функцию ЕСЛИ (IF), чтобы проверить что ячейка не пустая. Если пустая, но лишнюю запятую не клеим. Или можно использовать функцию ОБЪЕДИНИТЬ (TEXTJOIN) - она умеет соединять фрагменты через заданный разделитель (запятую) и сама пропускает пустые ячейки.
@@planetaexcel , cпасибо,excel гуру, я нашел видеоурок ua-cam.com/video/NejIt6JUDJE/v-deo.html
Разницы во времени при указании столбцов целиком скорей всего не будет, если столбцы G и H будут на другом листе.
Точно?
Ого! Я всегда выделяю целиком столбцы( Спасибо!
Кстати выделение всего диапазона сильно замедляем расчет только когда таблица с источником находится на том же листе, что и таблица, в которую мы тянем данные. Если разместить ее на другом листе, то разницы в скорости между полным и ограниченным диапазоном я не заметил.
Видимо, эксель весь столбец не проверяет в любом случае, а смотрит на количество "активных" строк на листе, на который ссылается формула, и проверяет данные именно в этом количестве ячеек.
А как зависит скорость ВПР от сортировки "прайс-листа" и основной таблицы? Можно ли повысить скорость, отсортировав обе таблицы по связываемому полю?
По моим ощущениям сортировка на скорость никак не влияет - поиск-то точный, а не приблизительный.
Николай, сколько лет вы уже видосы сюда пилите? Лет 15?
И еще, такой момент интересует, в мире есть кто-то, кто знает эксель лучше вас? Мне кажется нет
Есть огромное количество людей, знающих Excel лучше меня :)
Ищите контент на английском языке там в 100 раз больше материала и выходит намного раньше.
Не могу не согласиться :)))
...вопрос?
а как зависит скорость обработки данных если слабый ПК?
Здравствуйте. Подскажите пожалуйста как с помощью функции ВПР взять данные из нескольких таблиц, если они находятся на разных листах.
Просто переходите на другой лист и выбираете, в строке формулы отобразится (лист2 номер ячейки)
В какой версии excel добавили динамические массивы.? Можно их накатить патчем на 2013 ый excel? Или только office 365?
Только Office 365 с последними обновлениями, насколько я знаю.
я недавно делал что-то подобное, но замерял не время, а изменение в размерах файлов при разных формулах.
И каковы результаты? Было бы очень интересно посмотреть. Я всегда думал, что на размер файла это не влияет - только на скорость.
Добрый день! Впр субъективно быстрее скорее всего только когда столбцов мало, обычно когда имеется большое количество столбцов Индекс работает намного быстрее, и меньше занимает памяти
Добавил к прайсу еще 20 столбцов с данными и протестировал вашу версию.
ВПР = 4.1 сек
ИНДЕКС+ПОИСКПОЗ = 7.4 сек
Так что количество столбцов - не при чем :(
Здравствуйте. Не понял в варианте суммпроизв, почему можно было нажать просто ентер, а не фигурными закончить? Или это из-за того, что в полученном массиве только один единственный аргумент будет умножаться, а все остальные нули?
Функция СУММПРОИЗВ понимает формулы массива и без Ctr+Shift+Enter - это её известная в узких кругах особенность :)
Вижу как впр отрабатывает на пол миллиона текстовых значений за 1 секунду
*тяжело дышу*
Спасибо! Жаль, у меня не работает ПРОСМОТРХ(
Приветствую. Вот сталкиваюсь иногда, когда использовал формулу, и хочу автоматически спустить их на остальные ячейки с низу - и ничего не происходит. Что мешает? Пустых строк между значениями нету, формула простая минусую с одной ячейки другую. В ячейках только цифры, без букв и запятых. Спасибо.
Могут мешать пустые ячейки слева. Особенно, если у вас старая версия Excel - там это регулярно встречалось. Также в пустых на вид ячейках снизу могут быть пробелы.
Николай, помогите, пожалуйста.
Есть таблица с заголовками столбцов, в которых для читабельности используется кириллица и. что самое противное "мягкий перенос".
Насмотрелся Ваших видео, за которые Вам огромное спасибо, и решил переделать её в "умную". Но для работы формул никак не могу применить эти вычурные названия столбцов. Вывел эти названия за пределы умной таблицы, а внутри дал им уже приличные названия. Формулы переделал для работы с ними и теперь они стали летать. Профит, но ...
Антибонусом оказалось то, что кнопки фильтрации доступны только внутри "умной" таблицы, а за её пределами даже включить нереально. Ладно, не страшно, но эти кнопки не закрепляются в верхней части и уезжают вверх при пролистывании (оно и понятно почему).
Как можно решить эту проблему? Можно ли в заголовках делать подмену для отображения пользователям, а в формулах использовать удобное для формул название?