В этой статье, я расскажу о том, как проВПРить два массива, когда данных очень много.
Я видел собственными глазами, когда в очень крупной организации ВПР-илось два массива данных по 400 000 строк.
То есть в первой таблице, в которую нужно подставить данные из второй таблицы 400 тыс. строк, и во второй таблице столько же уникальных связок.
Представим, что у нас в двух таблицах данные за 2017 и за 2016 год.
1000 магазинов, 12 месяцев и примерно 20 продуктовых групп. Как понятно, это 1000*12*20 строк = 240 тыс. строк.
В обеих таблицах совокупность магазинов не повторяется, часть магазинов закрылась в течение этих двух лет, часть открылась. Поэтому вариант решения сортировкой не подходит.
Давайте посмотрим, сколько времени займёт ВПР тремя способами:
- Стандартный ВПР в Excel;
- Слияние запросов в Power Query (Merge Queries);
- Related в Power Pivot.
- Стандартный ВПР
В нашей таблице следующие столбцы: ID Магазина (от 1 до 1000), ID Месяца (от 1 до 12), ID Группы (от 1 до 20) и сумма продаж в одной таблице за 2016 год, в другой за 2017.
Столбец ключ – это уникальная связка Магазина, Месяца и группы.
Давайте с помощью стандартного ВПР добавим в таблицу продаж за 2017 год, продажи за 2016 год.
На эту операцию в заданных условиях у меня ушло 14 минут. И это еще неплохо.
Стоить отметить, что время выполнения ВПР зависит от нескольких условий:
- Количество символов искомого текста – чем длиннее поле, тем дольше происходит ВПР. В связи с этим, например, лучше производить поиск по порядковому номеру месяца, или магазина, чем по названию.
- Количество полей для поиска. Причем это касается не только таблицы, в которую подставляются значения, но и из которой. С этой точки зрения искать совпадения в двух таблицах по 200 000 строк (400 тыс.), будет дольше, чем к одной таблице в 300 тыс. строк проВПРить признак из 10 строк другой таблицы – можете проверить.
2. Слияние запросов в Power Query
Отформатируем таблицы продаж 2016 и 2017 года как умные, и загрузим как таблицы в редактор Power Query (на вкладке Данные).
Назовём запросы продажи_2016 и продажи_2017
Для того, чтобы произвести слияние запросов, на вкладке «Главная» выберем «Комбинировать» -> слияние запросов.
В окне слияния запросов выбираем столбцы, по которым необходимо производить слияние.
Причём в Power Query можно осуществлять слияние запросов не только по одному столбцу ключ, но и по нескольким столбцам выделяя их через CTRL.
Что также важно, в Power Query можно выбрать тип соединения в слияниях запроса, что также очень удобно.
В рамках примера, давайте выберем полное внешнее соединение.
Еще одним отличием «Слияния запросов» от классического ВПР является то, что мы можем добавить сразу несколько столбцов из одной таблицы в другую.
Для этого после произведения слияния необходимо нажать на значок развернуть и выбрать необходимые столбцы.
После добавления необходимых столбцов таблицы, нажимаем на вкладке главное «Закрыть и загрузить в…» и в окне импорта данных выбираем «Таблица».
Данный способ занимает по времени не больше 3х минут по загрузке, что почти в 5 раз быстрее первого варианта.
3. Related в Power Pivot
Третьим способом является создание модели данных и загрузка в Power Pivot.
Чтобы загрузить таблицы в Power Pivot мы можем также воспользоваться редактором Power Query и после обработки таблиц при выборе способа импорта данных выбрать «Только создать подключение» и «Добавить эти данные в модель данных».
Либо загрузить таблицы в Power Pivot можно напрямую из файла выбрав таблицу, и на вкладке Power Pivot выбрать «Добавить в модель данных».
После загрузки таблиц в Power Pivot, открываем его, выбрав «Управление» на рисунке выше.
На вкладке «Главная» в Power Pivot выберем режим просмотра «Представление диаграммы» и создадим связь между столбцами ключ в таблице «Продажи_2017» и «Продажи_2016».
У нас создалась связь один ко многим, один находится на стороне с уникальными записями в строках столбца ключа. В нашем случае записи уникальные в обоих столбцах связываемых таблиц, поэтому Power Pivot по умолчанию будет присваивать «многие» для таблицы от которой мы тянем связь.
В режиме просмотра «Представление данных» в таблицу «Продажи_2017» мы можем добавить «Продажи_2016» записав в новом столбце функцию
RELATED(‘Продажи_2016’[Продажи 2016]))
, где ‘Продажи_2016’ это название связанной таблицы, [Продажи 2016] – столбец, данные которого подставляем.
Для того, чтобы выгрузить данную таблицу на лист, закроем Power Pivot и в Excel загрузим таблицу через «Вставка» «Сводная», либо через «Данные» «Существующие подключение» как показано на gif ниже.
Данный способ занял у меня также около трёх минут, но на больших массивах данных он более производителен, чем Слияние запросов в Power Query.