Для тех, кто не знаком с функцией ВПР, она может показаться сложной. Но попрактиковавшись, вы увидите, насколько она полезна и проста (подробнее см. Билл Джелен. Всё о ВПР: от первого применения до экспертного уровня). ВПР выполняет поиск по ключу в исходной таблице, и возвращает значение из таблицы подстановки. Например, в качестве исходной можно рассмотреть таблицу продаж велосипедов и аксессуаров (левая таблица на рис. 1). В ней присутствует код товара. В качестве таблицы подстановки возьмем справочник товаров, в котором по коду можно узнать артикул, размер, цвет, … Нас же интересует цена. В версии Excel 365 наряду с ВПР доступна схожая новая функция ПРОСМОТРX – еще более мощная и простая в использовании. Также в версии Excel 365 есть две отличные альтернативы доброй старой функции ВПР – модель данных в Power Pivot и объединение таблиц в Power Query.
Рис. 1. Определение суммы чека с помощью таблицы подстановки и функции ВПР; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Скачать заметку в формате Word или pdf, примеры в формате zip (внутри три файла с примерами)
Если впервые слышите о Power Pivot и Power Query, рекомендую Роб Колли. Формулы DAX для Power Pivot и Кен Пульс и Мигель Эскобар. Язык М для Power Query.
Объединение таблиц в Power Query
Я с сыном играю в FPL и раз в неделю изучаю прогноз на сайте fplreview. На сайте есть возможность (платная) скачать данные в формате CSV (от англ. Comma-Separated Values — значения, разделённые запятыми). Файл в формате csv удобно импортировать в Excel с помощью Power Query. В Excel-файле пройдите по меню Данные –> Получить и преобразовать данные –> Из текстового/CSV-файла. Выберите файл fplreview.csv (приложен к заметке), нажмите Импорт. Отроется окно импорта:
Рис. 2. Окно предварительного просмотра Power Query
Если Power Query не сделал это автоматически, выберите кодировку UTF-8 и нажмите Преобразовать данные. Откроется окно Редактор Power Query:
Рис. 3. Окно редактора Power Query
Выделите все столбцы, содержащие десятичные числа с разделителем точка, кликните правой кнопкой мыши и выберите Тип изменения –> Используя локаль… В открывшемся окне выберите Тип данных – Десятичное число и Языковый стандарт (с которого следует преобразовать в наш родной) – Английский (США). Нажмите Ok.
Рис. 4. Замена десятичной точки на десятичную запятую
На вкладке редактора Power Query Главная кликните стрелку вниз, расположенную на кнопке Закрыть и загрузить. Выберите Закрыть и загрузить в… В окне Импорт данных выберите Только создать подключение. Остальные галочки снимите. Нажмите Ok. В Excel будет создано подключение к файлу fplreview.csv.
Небольшая проблема заключается в том, что позиции игроков в файле fplreview.csv (см. рис. 3) указаны одной буквой, а названия команд написаны полностью В то же время на сайте FPL используются трехбуквенные обозначения:
Рис. 5. Статистика с сайта FPL
В отдельном Excel-файле (Tables.xlsx также приложен) я создал две соответствующие таблицы подстановки:
Рис. 6. Таблицы подстановки в отдельном Excel-файле
Чтобы импортировать эти две таблицы в исходный файл (в тот, куда импортировали fplreview.csv) пройдите по меню Данные –> Получить и преобразовать данные –> Получить данные –> Из файла –> Из книги. В окне Импорт данных выберите файл Tables.xlsx, нажмите Импорт. В открывшемся окне Навигатора кликните Несколько элементов. Выберите таблицы Positions и Teams. Нажмите на стрелку вниз, расположенную на кнопке Загрузить. Выберите Загрузить в… В окне Импорт данных выберите Только создать подключение. Остальные галочки снимите. Нажмите Ok.
Рис. 7. Окно импорта из Excel-файла
В правой части окна Excel в области Запросы и подключения вы увидите три подключения: fplreview, Positions и Teams. Если вы не видите этой области, перейдите на вкладку Данные, кликните на кнопку Запросы и подключения. Кликните правой кнопкой мыши на первом запросе – fplreview, и выберите Объединить.
Рис. 8. Первый шаг в объединении запросов
Откроется окно Слияние:
Рис. 9. Настройка параметров слияния
В нижней части выберите вторую таблицу для слияния (пункт 1 на рис. 9), выделите общие столбцы в двух таблицах (2 и 3). Оставьте тип слияния по умолчанию (4). Нажмите Ok.
Откроется окно редактора Power Query, в котором будет отражаться новый запрос с именем Слияние1. К предыдущей таблице из запроса fplreview добавился столбец Teams. В каждой строке будет однотипная запись Table.
Рис. 10. Таблица Teams добавлена в каждую запись запроса fplreview
Обратите внимание на раздвоенную стрелку в заголовке столбца Teams. Сейчас мы развернем таблицы Teams во всех строках, и получим трехбуквенное обозначение команд. Кликните стрелку. Откроется новое окно. Оставьте переключатель в позиции Развернуть. Оставьте галочку только в позиции Team3. Снимите галочку Использовать исходное имя столбца как префикс…
Рис. 11. Настройка команды Развернуть, оставляющая от таблицы Teams столбец Team3
Переместите столбец Team3 ближе к столбцу Team.
Рис. 12. Теперь у нас есть два столбца с названием команд
Удалите столбец Team. Он нам больше не нужен. Переименуйте Team3 в Team. На вкладке редактора Power Query Главная кликните стрелку вниз, расположенную на кнопке Закрыть и загрузить. Выберите Закрыть и загрузить в… В окне Импорт данных выберите Только создать подключение. Остальные галочки снимите. Нажмите Ok. В Excel будет создано новое подключение Слияние1.
Кликните правой кнопкой мыши на запросе – Слияние1, выберите Объединить. Повторите действия, описанные с рис. 9 по рис. 12. На этом этапе вы объедините запросы Слияние1 и Positions. Единственное отличие будет в том, что запрос Слияние2 является финальным, и на последнем шаге в окне Импорт данных установите две опции: Только создать подключение и Добавить эти данные в модель данных. Вы всегда можете отредактировать метод загрузки запроса, кликнув на нем правой кнопкой мыши и выбрав опцию Загрузить в…
Теперь вы можете создать сводную таблицу на основе модели данных, созданной из запроса Слияние2. В окне Excel перейдите на вкладку Вставить. Кликните Сводная таблица. Откроется окно Создание сводной таблицы. Excel обнаружил модель данных, и предлагает создать сводную на ее основе. Кликните Ok.
Рис. 13. Создание сводной таблицы на основе модели данных
Вуаля, в исходные данные из файла fplreview.csv добавлены «правильные» обозначения позиций игроков и названий команд, взятые из таблиц подстановки.
Рис. 14. Сводная на основе запроса и таблиц подстановки
Вы скажите, что этот метод явно сложнее ВПР. Если вы хотите сделать подстановку один раз, соглашусь с вами. Преимущество Power Query заключается в том, что однажды выполнив этот сценарий, вы получите структуру, которую сможете обновлять при появлении новых данных. Вы можете обновить файл fplreview.csv и/или таблицы подстановки в файле Tables.xlsx, а затем в финальном файле просто нажать кнопку Обновить всё. Вуаля, никаких протягиваний формул, отслеживания размеров исходных таблиц, ничего…
Power Pivot
Можно повторить загрузку файла fplreview.csv и двух таблиц из файла Tables.xlsx в новый файл Excel. При этом импорт настроить следующим образом:
Рис. 15. Импорт в модель данных
Можно также изменить параметры импорта в уже имеющемся файле для запросов fplreview, Teams и Position.
Неважно, каким путем вы пойдете, но после загрузки трех запросов в модель данных перейдите на вкладку Power Pivot и кликните кнопку Управление. В открывшемся окне Power Pivot перейдите на вкладку Главная и в области Просмотр кликните кнопку Представление диаграммы. Перетащите поле Pos из таблицы Position на поле Pos в таблице fplreview. Затем перетащите Team.
Рис. 16. Установка связей между таблицами подстановки и основной таблицей
Только что вы создали две связи между основной таблицей fplreview и двумя таблицами подстановки Position и Teams. В редакторе Power Pivot пройдите по меню Файл –> Закрыть. Вы вернетесь в окно Excel. Постройте сводную таблицу, использовав поля трех исходных таблиц: fplreview, Position и Teams:
Рис. 17. Сводная таблица, использующая три связанные таблицы из модели данных
При изменении исходных файлов Power Pivot также поддерживает возможность обновления по кнопке Обновить всё.
Лично мне кажется более простым установление связей в Power Pivot. Но возможно слияние таблиц в Power Query вам также понравится.