Функция ВПР в Excel
ВПР (англ. вариант – VLOOKUP) – очень удобная и полезная функция особенно для работы с большим объемом данных, поскольку позволяет автоматически сопоставить диапазоны с десятками тысяч наименований. Является разновидностью поиска, но только вертикального (сверху вниз) – извлекает информацию из таблицы или определенного диапазона по строкам. Работает во всех версиях Excel и даже Google Sheets.
Синтаксис
Элемент – может быть числовым (адрес ячейки) или текстовым («текст»).
Адрес таблицы – диапазон ячеек, где примерно находится значение.
Номер столбца – принимает целое число из диапазона от 1 до n, из него будет извлечен результат.
Интервальный просмотр – приблизительное (ближайшее) соответствие критерию обозначается как 1 (истина), а точное соответствие – 0 (ложь). Данный логический аргумент указывать необязательно, если таблица отсортирована от минимального к максимальному значению. Если таблица не отсортирована и аргумент опущен, это равносильно истине.
В русифицированной версии Excel аргументы вводятся через знак «;», в англоязычной – через запятую.
Как работает функция?
Программа после поиска искомого значения переходит во второй столбец, чтобы извлечь возвращаемый элемент.
С одним условием
Рассмотрим функцию на простом примере поиска сотрудника по присвоенному ему коду. Таблицу нужно отсортировать в порядке возрастания.
- Кликнуть по ячейке справа от таблицы или под ней и вписать искомый номер.
- В ячейке рядом ввести функцию ВПР через знак «=», либо же использовать опцию «Вставить функцию» в разделе «Формулы». Проще использовать именно опцию – там есть подсказки, какие именно данные нужны.
- Задать параметры поиска, заполнив соответствующие поля. Интервальный просмотр в данном случае пропустим.
- Нажать «Enter» – и появится результат поиска.
Когда таблица не отсортирована и данные введены в хаотичном порядке, результат будет неправильный – программа найдет ближайшее соответствие («ИСТИНА»). Но можно не сортировать таблицу, а указать интервальный просмотр «ЛОЖЬ».
Чтобы найти другой элемент, следует просто изменить первый аргумент, и результат автоматически преобразится.
С несколькими условиями
Часто на практике требуется сравнить данные нескольких диапазонов и выбрать значение с учетом 2-х и более критериев. Здесь задействована также функция ЕСЛИ, которая отвечает как раз за условия.
Из таблицы нужно найти показатель выручки по конкретному менеджеру в определенный день:
- В первую выделенную ячейку вписать дату, во вторую – фамилию менеджера. В третьей ячейке будет происходить поиск.
- Кликнуть по пустой ячейке и ввести формулу =ВПР(G1;ЕСЛИ(C2:C12=G2;A2:D12;»»);4;0).
- Для подтверждения действия зажать комбинацию клавиш Ctrl+Shift+Enter. Нажатие на «Enter» не сработает в этом случае, потому что формула должна быть выполнена в массиве (об этом свидетельствуют скобки «<>», в которые взята вся формула).
Поиск по нескольким столбцам
Объем данных расширен, и нужно найти конкретное значение среди нескольких столбцов, просуммировав данные с помощью функции СУММ.
- Кликнуть по ячейке и ввести формулу =СУММ(ВПР(G1;A1:D12;<2;3;4>;ЛОЖЬ)). Для третьего аргумента перечисление столбцов происходит в скобках «<>».
- Одновременно зажать клавиши Ctrl+Shift+Enter. В результате формула будет взята в фигурные скобки «<>».
Программа сравнивает данные в таблице и, как только определяет точное совпадение, суммирует их.
Таким же способом можно найти среднее значение с помощью СРЗНАЧ: =СРЗНАЧ(ВПР(G1;A1:D12;<2;3;4>;ЛОЖЬ)).
Сравнение двух таблиц
ВПР помогает сопоставить значения в таблицах.
Необходимо сравнить зарплату сотрудников за 2 месяца, для этого:
- В таблице с зарплатой за март добавить еще один столбец.
- Клацнуть по первой ячейке в столбце и написать функцию ВПР со следующими аргументами: =ВПР($A$2:$A$12;ссылка_на_новый_лист!$A$2:$B$12;2;ЛОЖЬ). То есть нужно выделить диапазон с фамилиями менеджеров и сделать ссылки (строки и столбца) неизменными с помощью знака «$», посмотреть его в таблице с новой зарплатой, взять данные из второго столбца новой зарплаты и подставить их в ячейку С2. В результате отобразится первый результат.
- При помощи маркера заполнения протянуть полученное значение вниз.
По желанию теперь можно найти численную и процентную разницу.
Поиск в выпадающем списке
Необходимо настроить функцию так, чтобы при выборе элемента из выпадающего списка отображалось его числовое значение.
Для создания раскрывающегося списка:
- Поставить курсор в ячейку, где он будет располагаться.
- Перейти в раздел книги «Данные» – «Проверка данных».
- В «Типе данных» выбрать «Список», задать диапазон (в нашем случае – фамилии менеджеров).
- Нажать «Ок». Отобразится список.
- В следующую ячейку вписать функцию ВПР. Первый аргумент – ссылка на раскрывающийся список, второй – диапазон таблицы, третий – номер столбца, четвертый – «ЛОЖЬ». В итоге получится следующее: =ВПР(E1;A1:B12;2;ЛОЖЬ). Нажать «Enter».
Меняется фамилия в списке – меняется и зарплата.
Перенос данных
Есть таблица с менеджерами и объемом их продаж. Во второй таблице значится сумма премии за продажу для каждого менеджера. Необходимо перенести данные в левую таблицу, чтобы подсчитать общую выручку (произведение объема продаж и премии за 1 продажу: =ПРОИЗВЕД(C2*D2)).
- Выделить первую ячейку с премией в левой таблице. Написать функцию с аргументами, сделать неизменными значения из второй таблицы, указать в третьем аргументе столбец 2, вместо «ЛОЖЬ» можно вписать 0: =ВПР(B2;$G$2:$H$12;2;0)
- Протянуть формулу вниз, чтобы заполнить все ячейки.
Ошибки
Когда пользователь ошибается при вводе данных или выборе диапазона, вместо результата отображаются различные ошибки: #Н/Д, #ЗНАЧ, #ССЫЛКА.
Ошибка #Н/Д появляется, если:
- Указанный диапазон не содержит искомый элемент.
- Искомый элемент меньше, чем минимальный в массиве.
- Задан точный поиск (аргумент «ЛОЖЬ» или 0), а искомого нет в диапазоне.
- Задан приблизительный поиск (аргумент «ИСТИНА» или 1), но данные не отсортированы по возрастанию.
- Разный формат (числовой и текстовый) у ячейки, откуда берется искомое, и ячейки с данными первого столбца.
- В коде есть пробелы или невидимые непечатаемые знаки.
- Используются значения времени или большие десятичные числа.
Во избежание ошибки #Н/Д, когда ВПР не находит значение, рекомендуется использовать следующую формулу: =ЕСЛИОШИБКА(ВПР(C2;A1:B12;2;ЛОЖЬ);0) – вместо 0 можно написать «не найдено».
Ошибка #ЗНАЧ появляется, если:
- В качестве номера столбца указано число 0.
- Длина первого аргумента превышает 255 знаков.
Ошибка #ССЫЛКА появляется, если третий аргумент больше количества столбцов в таблице.
Вместо заключения
В Excel есть улучшенная версия ВПР – функция ПРОСМОТРХ. Считается более простой и удобной в использовании, работает в любом направлении и возвращает точные совпадения по умолчанию.
При необходимости задействовать горизонтальный поиск элемента стоит воспользоваться функцией ГПР.
В программах OpenOffice и LibreOffice используется VLOOKUP, ее синтаксис =VLOOKUP(lookupvalue; datatable; columnindex; mode), а работает функция точно так же, как описано выше.
Перенос данных таблицы через функцию ВПР
ВПР в Excel – очень полезная функция, позволяющая подтягивать данные из одной таблицы в другую по заданным критериям. Это достаточно «умная» команда, потому что принцип ее работы складывается из нескольких действий: сканирование выбранного массива, выбор нужной ячейки и перенос данных из нее.
Весь процесс просмотра и выборки данных происходит за доли секунды, поэтому результат мы получаем моментально.
Синтаксис ВПР
ВПР расшифровывается как вертикальный просмотр. То есть команда переносит данные из одного столбца в другой. Для работы со строками существует горизонтальный просмотр – ГПР.
Аргументы функции следующие:
- искомое значение – то, что предполагается искать в таблице;
- таблица – массив данных, из которых подтягиваются данные. Примечательно, таблица с данными должна находиться правее исходной таблицы, иначе функций ВПР не будет работать;
- номер столбца таблицы, из которой будут подтягиваться значения;
- интервальный просмотр – логический аргумент, определяющий точность или приблизительность значения (0 или 1).
Как перемещать данные с помощью ВПР?
Рассмотрим пример на практике. Имеем таблицу, в которой прописываются партии заказанных товаров (она подсвечена зеленым). Справа прайс, где значатся цены каждого товара (подсвечен голубым). Нам нужно перенести данные по ценам из правой таблицы в левую, чтобы подсчитать стоимость каждой партии. Вручную это делать долго, поэтому воспользуемся функцией Вертикального Просмотра.
В ячейку D3 нужно подтянуть цену гречки из правой таблицы. Пишем =ВПР и заполняем аргументы.
Искомым значением будет гречка из ячейки B3. Важно проставить именно номер ячейки, а не слово «гречка», чтобы потом можно было протянуть формулу вниз и автоматически получить остальные значения.
Таблица – выделяем прайс без шапки. Т.е. только сами наименования товаров и их цены. Этот массив мы зафиксируем клавишей F4, чтобы он не изменялся при протягивании формулы.
Номер столбца – в нашем случае это цифра 2, потому что необходимые нам данные (цена) стоят во втором столбце выделенной таблицы (прайса).
Интервальный просмотр – ставим 0, т.к. нам нужны точные значения, а не приблизительные.
Видим, что из правой таблицы в левую подтянулась цена гречки.
Протягиваем формулу вниз и визуально проверяем некоторые товары, чтобы понять, что все сделали правильно.
Вот так, с помощью элементарных действий можно подставлять значения из одной таблицы в другую. Важно помнить, что функция Вертикального Просмотра работает только если таблица, из которой подтягиваются данные, находится справа. В противном случае, нужно переместить ее или воспользоваться командой ИНДЕКС и ПОИСКПОЗ. Овладев этими двумя функциями вы сможете намного больше реализовать сложных решений чем те возможности, которые предоставляет функция ВПР или ГПР.
ВПР (VLOOKUP) по нескольким условиям
Друзья, нас очень часто спрашивают — а как все-таки сделать ВПР по нескольким условиям. Давайте разбираться.
Объединяем условия и диапазоны для поиска
Как работает функция ВПР? Вы задаете условие поиска и функция ищет его в первом столбце выбранного диапазона данных, в случае успеха выводится тот столбец из таблицы, который вы указали.
Наша таблица — ниже, из нее нужно отобрать ячейку по двум условиям, “а” и “б”.
Для этого в функции ВПР нужно объединить оба условия поиска и диапазоны поиска. Объединим и выведем для наглядности.
Добавляем все в ВПР. Формула массива (arrayformula) нужна, чтобы “на лету” объединить столбцы.
Функция уже работает но в ней всего один столбец данных, добавляем к диапазону тот столбец, который будем выводить, C2:C3.
Конструкцией ниже мы создали массив из двух столбцов (объединенного A2:A3 и B2:B3 и C2:C3), обратная косая черта (“\”) — отделяет столбцы, фигурные скобки <> — создают массив.
Итак, формула:
Поиск по двум условиям, FILTER
ВПР по нескольким условиям возможен, но непрост, особенно для новичков, плюс из-за формулы массива это не самая быстрая формула.
Тоже самое можно сделать с помощью функции FILTER:
Если значений большего одного
FILTER, в отличии от ВПР выводит все значения, которые отвечают нашему условию, чтобы оставить одно (например верхнее), добавляем INDEX:
=INDEX(FILTER(C2:C4;A2:A4=E2;B2:B4=F2);1)
Мы много писали и про ВПР и про FILTER, все статьи — в оглавлении нашего канала.
Самый быстрый ВПР
Если в ваших таблицах всего лишь несколько десятков строк, то, скорее всего, эта статья не будет для вас актуальной. На таких небольших объемах данных любой способ будет работать достаточно шустро, чтобы вы этого не замечали. Если же число строк в ваших списках измеряется тысячами, да и самих таблиц не одна-две, то время мучительного ожидания на пересчете формул в Excel может доходить до нескольких минут.
В этом случае, правильный выбор функции, применяемой для связывания таблиц, играет решающую роль — разница в производительности между ними, как мы увидим далее, может составлять более 20 раз!
Когда я писал свою первую книжку пять лет назад, то уже делал сравнительный скоростной тест различных способов поиска и подстановки данных функциями ВПР, ИНДЕКС+ПОИСКПОЗ, СУММЕСЛИ и др. С тех пор сменилось три версии Office, появились надстройки Power Query и Power Pivot, кардинально изменившие весь процесс работы с данными. А в прошлом году ещё и обновился вычислительный движок Excel, получив поддержку динамических массивов и новые функции ПРОСМОТРХ, ФИЛЬТР и т.п.
Так что пришла пора снова взяться за секундомер и выяснить — кто же самый быстрый. Ну и, заодно, проверить — какие способы поиска и подстановки данных в Excel вы знаете 🙂
Подопытный кролик
Тест будем проводить на следующем примере:
Это книга Excel с одним листом, где расположены две таблицы: отгрузки (500 000 строк) и прайс-лист (600 строк). Наша задача — подставить цены из прайс-листа в таблицу отгрузок. Для каждого способа будем вводить формулу в ячейку С2 и копировать вниз на весь столбец, замеряя время, которое потребуется Excel, чтобы просчитать весь столбец из полумиллиона ячеек. Полученные значения, безусловно, зависят от множества факторов (поколение процессора, объем оперативной памяти, текущая загрузка системы, версия Office и т.д.), но нам важны не конкретные цифры, а, скорее, их сравнение друг с другом. Важно понимать прожорливость каждого способа и их ограничения.
Способ 1. ВПР
Сначала — классика 🙂 Легендарная функция вертикального просмотра — ВПР (VLOOKUP) , которая приходит в голову первой в подобных ситуациях:
- B2 — искомое значение, т.е. название товара, который мы хотим найти в прайс-листе
- $G$2:$H$600 — закреплённая знаками доллара (чтобы не сползала при копировании формулы вниз) абсолютная ссылка на прайс
- 2 — номер столбца в прайс-листе, откуда мы хотим взять цену
- 0 или ЛОЖЬ — переключение в режим поиска точного соответствия, когда любое некорректное название товара (например, ФОНЕРА) в столбце B в таблице отгрузок приведёт к появлению ошибки #Н/Д как результата работы функции.
Время вычисления = 4,3 сек.
Способ 2. ВПР с выделением столбцов целиком
Многие пользователи, применяя ВПР, во втором аргументе этой функции, где нужно задать поисковую таблицу (прайс), выделяют не ограниченный диапазон ( $G$2:$H$600 ), а сразу столбцы G:H целиком. Это проще, быстрее, позволяет не думать про F4 и то, что завтра прайс-лист может быть на несколько строк больше. Формула в этом случае выглядит тоже компактнее:
В старых версиях Excel такое выделение не сильно влияло на скорость вычислений, но сейчас (неожиданно для меня, признаюсь) результат получился в разы хуже предыдущего.
Время вычисления = 14,5 сек.
Способ 3. ИНДЕКС и ПОИСКПОЗ
Следующей после ВПР ступенью эволюции для многих пользователей Microsoft Excel обычно является переход на использование связки функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) . Выглядит эта формула так:
Функция ИНДЕКС извлекает из заданного в первом аргументе диапазона (столбца $H$2:$H$600 с ценами в прайс-листе) содержимое ячейки с заданным номером. А номер этот, в свою очередь, определяется функцией ПОИСКПОЗ, у которой три аргумента:
- Что нужно найти — название товара из B2
- Где мы это ищем — столбец с названиями товаров в прайсе ( $G$2:$G$600 )
- Режим поиска: 0 — точный, 1 или -1 — приблизительный с округлением в меньшую или большую сторону, соответственно.
Формула выходит чуть сложнее, но, при этом имеет несколько ощутимых преимуществ перед классической ВПР, а именно:
- Не нужно отсчитывать номер столбца (как в третьем аргументе ВПР).
- Можно извлекать данные, которые находятся левее столбца, где просходит поиск.
По скорости, однако же, этот способ проигрывает ВПР почти в два раза:
Время вычисления = 7,8 сек.
Если же, вдобавок, полениться и выделять не ограниченные диапазоны, а столбцы целиком:
. то результат получается совсем печальный:
Время вычисления = 28,5 сек.
28 секунд, Карл! В 6 раз медленнее ВПР!
Способ 4. СУММЕСЛИ
Если нужно найти не текстовые, а именно числовые данные (как в нашем случае — цену), то вместо ВПР вполне можно использовать функцию СУММЕСЛИ (SUMIF) . Изначально она задумывалась как инструмент для выборочного суммирования данных по условию (найди и сложи мне все продажи кабелей, например), но можно заставить её искать нужный нам товар и в прайс-листе. Если грузы в нём не повторяются, то суммировать будет не с чем и эта функция просто выведет искомое значение:
- Первый аргумент СУММЕСЛИ — это диапазон проверяемых ячеек, т.е. названия товаров в прайсе ( $G$2:$G$600 ).
- Второй аргумент ( B2 ) — что мы ищем.
- Третий аргумент — диапазон ячеек с ценами $H$2:$H$600 , числа из которых мы хотим просуммировать, если в соседних ячейках проверяемого диапазона есть искомое значение.
Очевидным минусом такого подхода является то, что он работает только с числами. Также этот способ не удобен, если прайс-лист находится в отдельном файле — придется всё время держать его открытым, т.к. функция СУММЕСЛИ не умеет брать данные из закрытых книг, в отличие от ВПР, для которой это не проблема.
В плюсы же можно записать удобство при поиске сразу по нескольким столбцам — для этого идеально подходит более продвинутая версия этой функции — СУММЕСЛИМН (SUMIFS) . Скорость вычислений же, при этом, весьма посредственная:
Время вычисления = 12,8 сек.
При выделении столбцов целиком, т.е. использовании формулы вида =СУММЕСЛИ( G:G ; B2 ; H:H ) всё ещё хуже:
Время вычисления = 41,7 сек.
Это самый плохой результат в нашем тесте.
Способ 5. СУММПРОИЗВ
Этот подход сейчас встречается не часто, но всё ещё достаточно регулярно. Обычно так любят извращаться пользователи старой школы, ещё хорошо помнящие те времена, когда в Excel было всего 255 столбцов и 56 цветов 🙂
Суть этого метода заключается в использовании функции СУММПРОИЗВ (SUMPRODUCT) , изначально предназначенной для поэлементного перемножения нескольких диапазонов с последующим суммированием полученных произведений. В нашем случае, вместо одного из массивов будет выступать условие, а вторым будут цены:
Выражение ($G$2:$G$600=B2) , по сути, проверяет каждое название груза в прайс-листе на предмет соответствия искомому значению (ФАНЕРА ПР). Результатом каждого сравнения будет логическое значение ИСТИНА (TRUE ) или ЛОЖЬ (FALSE) , что в Excel интерпретируется как 1 и 0, соответственно. Последующее умножение этих нулей и единиц на цены оставит в живых цену только того товара, который нам, в данном случае, и нужен.
Эта формула является, по сути, формулой массива, но не требует нажатия обычного для них сочетания клавиш Ctrl + Shift + Enter , т.к. функция СУММПРОИЗВ поддерживает массивы уже сама по себе. Возможно, по этой же причине (формулы массива всегда медленнее, чем обычные) такой скорость пересчёта такой формулы — не очень:
Время вычисления = 11,8 сек.
- Совместимость с любыми, самыми древними версиями Excel.
- Возможность задавать сложные условия (и несколько)
- Способность этой формулы работать с данными из закрытых файлов, если добавить перед ней двойное бинарное отрицание (два подряд знака «минус»). СУММЕСЛИМН таким похвастаться не может.
Способ 6. ПРОСМОТР
Ещё один относительно экзотический способ поиска и подстановки данных, наравне с ВПР — это использование функции ПРОСМОТР (LOOKUP) . Только не перепутайте её с новой, буквально, на днях появившейся функцией ПРОСМОТРХ (XLOOKUP) — про неё мы поговорим дальше особо. Функция ПРОСМОТР существовала в Excel начиная с самых ранних версий и тоже вполне может решить нашу задачу:
- B2 — название груза, которое мы ищем
- $G$2:$G$600 — одномерный диапазон-вектор (столбец или строка), где мы ищем совпадение
- $H$2:$H$600 — такого же размера диапазон, откуда нужно вернуть найденный результат (цену)
На первый взгляд всё выглядит очень удобно и логично, но всю картину портят два неочевидных момента:
- Эта функция требует обязательной сортировки прайс-листа по возрастанию (алфавиту) и без этого не работает.
- Если в таблице отгрузок искомое значение будет написано с опечаткой (например, АГ Е ДОЛ вместо АГИДОЛ), то функция ПРОСМОТР выдаст не ошибку #Н/Д, а цену для ближайшего предыдущего товара:
При работе с неидеальными данными в реальном мире это гарантированно создаст проблемы, как вы понимаете.
Скорость же вычислений у функции ПРОСМОТР (LOOKUP) весьма приличная:
Время вычисления = 7,6 сек.
Способ 7. Новая функция ПРОСМОТРХ
Эта функция пришла с одним из недавних обновлений пока только пользователям Office 365 и пока отсутствует во всех остальных версиях (Excel 2010, 2013, 2016, 2019). По сравнению с классической ВПР у этой функции есть масса преимуществ (упрощенный синтаксис, возможность искать не только сверху-вниз, возможность сразу задать значение вместо #Н/Д и т.д.) Формула для решения нашей задачи будет выглядеть в этом случае так:
Если не брать в расчёт необязательные 4,5,6 аргументы, то синтаксис этой функции полностью совпадает с её предшественником — функцией ПРОСМОТР (LOOKUP) . Скорость вычислений при тестировании на наши 500000 строк тоже оказалась аналогичной:
Время вычисления = 7,6 сек.
Почти в два раза медленнее, чем у ВПР, вместо которой Microsoft предлагает теперь использовать ПРОСМОТРХ. Жаль.
И, опять же, если полениться и выделить диапазоны в прайс-листе целыми столбцами:
. то скорость падает до совершенно неприличных уже значений:
Время вычисления = 28,3 сек.
А если на динамических массивах?
Прошлогоднее (осень 2019) обновление вычислительного движка Microsoft Excel добавило ему поддержку динамических массивов (Dynamic Arrays), о которых я уже писал. Это принципиально новый подход к работе с данными, который можно использовать почти с любыми классическими функциями Excel. На примере ВПР это будет выглядеть так:
Разница с классическим вариантом в том, что первым аргументом ВПР здесь выступает не одно искомое значение (а формулу потом нужно копировать вниз на остальные строки), а сразу весь массив из полумиллиона грузов B2:B500000, цены для которых мы хотим найти. Формула при этом сама распространяется вниз, занимая требуемое количество ячеек.
Скорость пересчета в таком варианте меня, откровенно говоря, ошеломила — пауза между нажатием на Enter после ввода формулы и получением результатов почти отсутствовала.
Время вычисления = 1 сек.
Что интересно, и новая ПРОСМОТРХ, и старая ПРОСМОТР, и связка ИНДЕКС+ПОИСКПОЗ в таком режиме тоже были очень быстрыми — время вычислений не больше 1 секунды! Фантастика.
А вот олдскульные подходы на основе СУММПРОИЗВ и СУММЕСЛИ(МН) с динамическими массивами работать отказались 🙁
Что с умными таблицами?
Обрадовавшись фантастическим результатам, полученным на динамических массивах, я решил вдогон попробовать протестировать разницу в скорости при работе с обычными и «умными» таблицами. Я имею ввиду те самые «красивые таблицы», в которые вы можете преобразовать ваш диапазон с помощью команды Форматировать как таблицу на вкладке Главная (Home — Format as Table) или с помощью сочетания клавиш Ctrl + T .
Если предварительно превратить наши отгрузки и прайс в «умные» (по умолчанию они получат имена Таблица1 и Таблица2, соответственно), то формула с той же ВПР будет выглядеть как:
- [@Груз] — ссылка на ячейку B2, означающая, в данном случае, что нужно взять значение из той же строки из столбца Груз текущей умной таблицы.
- Таблица2 — ссылка на прайс-лист
Жирным плюсом такого подхода будет возможность легко добавлять данные в наши таблицы в будущем. При дописывании новых строк в отгрузки или к прайс-листу, наши «умные» таблицы будут растягиваться автоматически.
Скорость же, как выяснилось, тоже вырастает очень значительно и примерно равна скорости работы на динамических массивах:
Время вычисления = 1 сек.
У меня есть подозрение, что дело тут не в самих «умных» таблицах, а всё в том же обновлении вычислительного движка, т.к. на старых версиях Excel такого прироста в скорости на умных таблицах я не помню.
Бонус. Запрос Power Query
Замерять, так замерять! Давайте, для полноты картины, сравним наши перечисленные способы еще и с запросом Power Query, который тоже может решить нашу задачу. Кто-то скажет, что некорректно сравнивать пересчёт формул с механизмом обновления запроса, но мне, откровенно говоря, просто самому было интересно — кто быстрее?
- Превращаем обе наши таблицы в «умные» с помощью команды Форматировать как таблицу на вкладке Главная (Home — Format as Table) или с помощью сочетания клавиш Ctrl + T .
- По очереди загружаем таблицы в Power Query с помощью команды Данные — Из таблицы / диапазона (Data — From Table/Range) .
- После загрузки в Power Query возвращаемся обратно в Excel, оставляя загруженные данные как подключение. Для этого в окне Power Query выбираем Главная — Закрыть и загрузить — Закрыть и загрузить в. — Только создать подключение (Home — Close&Load — Close&Load to. — Only create connection) .
- После того, как обе исходные таблицы будут загружены как подключения, создадим ещё один, третий запрос, который будет объединять их между собой, подставляя цены из прайса в отгрузки. Для этого на вкладке Данные выберем Получить данные / Создать запрос — Объединить запросы — Объединить (Get Data / New Query — Merge queries — Merge) :
В отличие от формул, запросы Power Query не обновляются автоматически «на лету», а требуют щелчка правой кнопкой мыши по таблице (или запросу в правой панели) и выбору команды Обновить (Refresh) . Также можно воспользоваться командой Обновить все (Refresh All) на вкладке Данные (Data) .
Время обновления = 8,2 сек.
Итоговая таблица и выводы
Если вы честно дочитали до этого места, то какие-то выводы, наверное, уже сделали самостоятельно. Если же пропустили все детали и сразу перешли к итогам, то вот вам общая результирующая таблица по скорости всех методов:
Само-собой, у каждого из нас свои предпочтения, задачи и тараканы, но для себя я сформулировал выводы после этого тестирования так:
