Это продолжение перевода книги Зак Барресс и Кевин Джонс. Таблицы Excel: Полное руководство для создания, использования и автоматизации списков и таблиц (Excel Tables: A Complete Guide for Creating, Using and Automating Lists and Tables by Zack Barresse and Kevin Jones. Published by: Holy Macro! Books. First printing: July 2014. – 161 p.). Таблицы являются отличными источниками данных. Они строятся по определенным правилам: заголовки в один ряд, уникальные имена заголовков, однородная структура данных. Поэтому таблицы идеально подходят для использования в качестве источников данных для сводных таблиц и иных методов отчетности. В этой главе мы объясним почему.
Предыдущая глава Содержание Следующая глава
Рис. 5.1. Создание сводной таблицы
Скачать заметку в формате Word или pdf, примеры в формате архива (файлы Excel с поддержкой макросов провайдер не позволяет загружать по соображениям безопасности)
Создание сводных таблиц
Поскольку Таблицы могут расширяться и сжиматься вместе с данными, они являются отличными источниками данных для сводных таблиц. Например, когда вы вводите пустую строку непосредственно под таблицей, таблица расширяется, чтобы включить новую строку. По мере расширения и сжатия таблица Excel сохраняет имя таблицы и связанные с ней ссылки на диапазон. Эта функция позволяет удобно ссылаться на таблицу из внешних расположений, таких как сводная таблица.
Чтобы создать сводную таблицу на основе Таблицы, выберите любую часть Таблицы, а затем пройдите по меню Работа с таблицами –> Конструктор –> Сводная таблица. Можно также пройти по меню Вставка –> Сводная таблица. В обоих случаях откроется окно Создание сводной таблицы (рис. 5.1). По умолчанию в качестве источника появится имя таблицы, а назначение направляется на новый лист.
Обновление сводной таблицы
Excel не обновляет сводные таблицы автоматически; вы должны обновить их вручную. Чтобы обновить сводную таблицу щелкните правой кнопкой мыши на любой ячейке в сводной таблице и выберите команду Обновить.
Вы можете обновлять сводные таблицы автоматически, используя код VBA. Чтобы реализовать это решение, щелкните правой кнопкой мыши ярлык рабочего листа и выберите Просмотреть код:
Рис. 5.2. Запуск редактора VBA
Скопируйте и вставьте этот код в появившееся окно редактора VBA:
1 2 3 4 5 6 |
Private Sub Worksheet_Activate() Dim PT As PivotTable For Each PT In Me.PivotTables PT.PivotCache.Refresh Next PT End Sub |
Рис. 5.3. Окно редактора VBA с кодом, обновляющим сводные таблицы
Нажмите Alt+F11, чтобы вернуться в окно Excel. Код запускается при активации рабочего листа, к которому привязан код. Он обновляет все сводные таблицы на листе. Как только код VBA добавляется в рабочую книгу, она должна быть сохранена в формате xlsm.
Power Pivot
Понимание того, как таблицы работают с Power Pivot, требует базового понимания того, что такое Power Pivot.[1] Power Pivot использует данные из различных подключений к источникам данных, преодолевая ограничение числа строк Excel (около 1 млн.). Данные импортируются и сохраняются в модели данных Power Pivot и затем могут выводиться на листы Excel в виде сводных таблиц, сводных диаграмм и Таблиц.
Одной из самых мощных характеристик Power Pivot является его способность одновременно подключаться к нескольким разрозненным источникам данных, включая Таблицы Excel. Данные на лист Excel могут запрашиваться из модели данных с помощью формул DAX.
Нельзя использовать стандартный диапазон листа Excel в качестве источника для модели данных. Источник должен быть именно Таблицей Excel.
Термины Power Pivot и модель данных часто взаимозаменяемы. Можно сказать, что Power Pivot – это вся надстройка и пользовательский интерфейс, а модель данных – это совокупность данных, отношений и мер, созданных и поддерживаемых Power Pivot.
Не все версии Excel поддерживают Power Pivot (подробнее см. Где есть Power Pivot?).
После создания книги с использованием Power Pivot ее можно опубликовать с помощью 2010 SharePoint Server или Power BI.
Добавление таблицы в модель данных
Чтобы добавить таблицу в модель данных, кликните на любой ячейке Таблицы и пройдите по меню Power Pivot –> Таблицы –> Добавить в модель данных.
Рис. 5.4. Добавление Таблицы в модель данных
Как только таблица становится частью модели данных, вы можете использовать ее для создания связей и мер таким же образом, как и при использовании любого другого источника данных.
Power Query
Power Query предоставляет возможность проверять, запрашивать, преобразовывать, организовывать и предоставлять данные из многих источников. Он использует язык M, который работает быстро и возвращает только окончательные результаты запроса.
Power Query доступен, начиная с Excel 2010. В Excel 2016 и более поздних версиях этот инструмент полностью интегрирован в Excel, и доступен на вкладке Данные в областях Получить и преобразовать данные и Запросы и подключения.
Рис. 5.5. Интерфейс Power Query в MS Office 365 ProPlus; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Power Query может извлекать данные из различных источников данных, включая Таблицы. Power Query исключительно хорошо преобразует данные из форм, которыми трудно управлять и анализировать, в формы, которые лучше структурированы. Следующий пример берет плохо сформированный отчет и преобразует его в форму, более удобную для анализа.
Исходные данные представлены в виде, напоминающем сводные таблицы:
Рис. 5.6. Исходные данные сведены по возрастным категориям
Для последующего анализа желательно чтобы данные имели приблизительно следующий вид:
Рис. 5.7. Вид, к которому желательно привести исходные данные
Power Query легко справляется с этой задачей (правда, для начала надо потратить время на изучение самого Power Query; если вас интересует эта тема, рекомендую Кен Пульс и Мигель Эскобар. Язык М для Power Query. – Прим. Багузина).
Чтобы начать преобразование, кликните на любой ячейке диапазона B2:H15 на рис. 5.6 (если вы хотите проделать эти манипуляции самостоятельно, откройте Excel-файл к этой заметке). Пройдите по меню Данные –> Получить и преобразовать данные –> Из таблицы/диапазона. Excel предложит правильно выбранный диапазон:
Рис. 5.8. Excel автоматически предлагает правильный диапазон для преобразования
Кликните OK. Power Query преобразует диапазон на листе Excel в Таблицу, создаст запрос и загрузит данные в редактор Power Query.
Рис. 5.9. Исходные данные загружены в редактор Power Query
Для преобразования данных кликните правой кнопкой мыши на столбец Year и выберите опцию Отменить свертывание других столбцов:
Рис. 5.10. Команда Отменить свертывание других столбцов
Теперь данные отображаются в трех столбцах:
Рис. 5.11. Преобразованные данные в окне редактора Power Query
Кликните правой кнопкой мыши на столбце Атрибут, и выберите опцию Переименовать. Наберите новое имя – Возрастной диапазон. Переименуйте Year в Год.
Загрузите данные на лист Excel. Для этого в окне редактора Power Query пройдите по меню Главная –> Закрыть и загрузить –> Закрыть и загрузить в… Оставьте условия загрузки, предложенные по умолчанию, нажав OK:
Рис. 5.12. Параметры загрузки данных из Power Query в Excel
Данные загрузятся в Таблицу на новый лист Excel:
Рис. 5.13. Преобразованные данные в Таблице на листе Excel
При этом окно редактора Power Query автоматически закроется.
Обратите внимание, что любые изменения, внесенные в диапазон (рис. 5.6), будут отражены в Таблице (рис. 5.13) при обновлении запроса.
Подытожим. Запрос Power Query содержит три различных действия по преобразованию данных:
- Определение диапазона в качестве источника данных для запроса (и преобразование диапазона в Таблицу перед загрузкой его в редактор Power Query).
- Преобразование свернутых данных в простую вертикальную таблицу.
- Переименование столбцов.
Эти действия были записаны на языке M. Чтобы увидеть код, в Excel активируйте окно Запросы и подключения. Для этого пройдите по меню Данные –> Запросы и подключения. В окне Запросы и подключения кликните правой кнопкой мыши на единственном запросе Таблица2, и выберите опцию Изменить. Откроется окно редактора Power Query. Пройдите по меню Главная –> Расширенный редактор:
Рис. 5.14. Код запроса на языке М
[1] Работа с моделью данных описана в книге всего лишь в нескольких абзацах. Если вас интересует эта тема, рекомендую Роб Колли. Формулы DAX для Power Pivot. – Прим. Багузина.