Перейти к содержимому

Глава 5. Сводные на основе Таблиц

Это продолжение перевода книги Зак Барресс и Кевин Джонс. Таблицы 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.). Таблицы являются отличными источниками данных. Они строятся по определенным правилам: заголовки в один ряд, уникальные имена заголовков, однородная структура данных. Поэтому таблицы идеально подходят для использования в качестве источников данных для сводных таблиц и иных методов отчетности. В этой главе мы объясним почему.

Предыдущая глава        Содержание    Следующая глава

Ris. 5.1. Sozdanie svodnoj tablitsy

Рис. 5.1. Создание сводной таблицы

Скачать заметку в формате Word или pdf, примеры в формате архива (файлы Excel с поддержкой макросов провайдер не позволяет загружать по соображениям безопасности)

Создание сводных таблиц

Поскольку Таблицы могут расширяться и сжиматься вместе с данными, они являются отличными источниками данных для сводных таблиц. Например, когда вы вводите пустую строку непосредственно под таблицей, таблица расширяется, чтобы включить новую строку. По мере расширения и сжатия таблица Excel сохраняет имя таблицы и связанные с ней ссылки на диапазон. Эта функция позволяет удобно ссылаться на таблицу из внешних расположений, таких как сводная таблица.

Чтобы создать сводную таблицу на основе Таблицы, выберите любую часть Таблицы, а затем пройдите по меню Работа с таблицами –> Конструктор –> Сводная таблица. Можно также пройти по меню Вставка –> Сводная таблица. В обоих случаях откроется окно Создание сводной таблицы (рис. 5.1). По умолчанию в качестве источника появится имя таблицы, а назначение направляется на новый лист.

Обновление сводной таблицы

Excel не обновляет сводные таблицы автоматически; вы должны обновить их вручную. Чтобы обновить сводную таблицу щелкните правой кнопкой мыши на любой ячейке в сводной таблице и выберите команду Обновить.

Вы можете обновлять сводные таблицы автоматически, используя код VBA. Чтобы реализовать это решение, щелкните правой кнопкой мыши ярлык рабочего листа и выберите Просмотреть код:

Ris. 5.2. Zapusk redaktora VBA

Рис. 5.2. Запуск редактора VBA

Скопируйте и вставьте этот код в появившееся окно редактора VBA:

Ris. 5.3. Okno redaktora VBA s kodom obnovlyayushhim svodnye tablitsy

Рис. 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 –> Таблицы –> Добавить в модель данных.

Ris. 5.4. Dobavlenie Tablitsy v model dannyh

Рис. 5.4. Добавление Таблицы в модель данных

Как только таблица становится частью модели данных, вы можете использовать ее для создания связей и мер таким же образом, как и при использовании любого другого источника данных.

Power Query

Power Query предоставляет возможность проверять, запрашивать, преобразовывать, организовывать и предоставлять данные из многих источников. Он использует язык M, который работает быстро и возвращает только окончательные результаты запроса.

Power Query доступен, начиная с Excel 2010. В Excel 2016 и более поздних версиях этот инструмент полностью интегрирован в Excel, и доступен на вкладке Данные в областях Получить и преобразовать данные и Запросы и подключения.

Ris. 5.5. Interfejs Power Query v MS Office 365 ProPlus

Рис. 5.5. Интерфейс Power Query в MS Office 365 ProPlus; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Power Query может извлекать данные из различных источников данных, включая Таблицы. Power Query исключительно хорошо преобразует данные из форм, которыми трудно управлять и анализировать, в формы, которые лучше структурированы. Следующий пример берет плохо сформированный отчет и преобразует его в форму, более удобную для анализа.

Исходные данные представлены в виде, напоминающем сводные таблицы:

Ris. 5.6. Ishodnye dannye svedeny po vozrastnym kategoriyam

Рис. 5.6. Исходные данные сведены по возрастным категориям

Для последующего анализа желательно чтобы данные имели приблизительно следующий вид:

Ris. 5.7. Vid k kotoromu zhelatelno privesti ishodnye dannye

Рис. 5.7. Вид, к которому желательно привести исходные данные

Power Query легко справляется с этой задачей (правда, для начала надо потратить время на изучение самого Power Query; если вас интересует эта тема, рекомендую Кен Пульс и Мигель Эскобар. Язык М для Power Query. – Прим. Багузина).

Чтобы начать преобразование, кликните на любой ячейке диапазона B2:H15 на рис. 5.6 (если вы хотите проделать эти манипуляции самостоятельно, откройте Excel-файл к этой заметке). Пройдите по меню Данные –> Получить и преобразовать данные –> Из таблицы/диапазона. Excel предложит правильно выбранный диапазон:

Ris. 5.8. Excel avtomaticheski predlagaet pravilnyj diapazon dlya preobrazovaniya

Рис. 5.8. Excel автоматически предлагает правильный диапазон для преобразования

Кликните OK. Power Query преобразует диапазон на листе Excel в Таблицу, создаст запрос и загрузит данные в редактор Power Query.

Ris. 5.9. Ishodnye dannye zagruzheny v redaktor Power Query

Рис. 5.9. Исходные данные загружены в редактор Power Query

Для преобразования данных кликните правой кнопкой мыши на столбец Year и выберите опцию Отменить свертывание других столбцов:

Ris. 5.10. Komanda Otmenit svertyvanie drugih stolbtsov

Рис. 5.10. Команда Отменить свертывание других столбцов

Теперь данные отображаются в трех столбцах:

Ris. 5.11. Preobrazovannye dannye v okne redaktora Power Query

Рис. 5.11. Преобразованные данные в окне редактора Power Query

Кликните правой кнопкой мыши на столбце Атрибут, и выберите опцию Переименовать. Наберите новое имя – Возрастной диапазон. Переименуйте Year в Год.

Загрузите данные на лист Excel. Для этого в окне редактора Power Query пройдите по меню Главная –> Закрыть и загрузить –> Закрыть и загрузить в… Оставьте условия загрузки, предложенные по умолчанию, нажав OK:

Ris. 5.12. Parametry zagruzki dannyh iz Power Query v Excel

Рис. 5.12. Параметры загрузки данных из Power Query в Excel

Данные загрузятся в Таблицу на новый лист Excel:

Ris. 5.13. Preobrazovannye dannye v Tablitse na liste Excel

Рис. 5.13. Преобразованные данные в Таблице на листе Excel

При этом окно редактора Power Query автоматически закроется.

Обратите внимание, что любые изменения, внесенные в диапазон (рис. 5.6), будут отражены в Таблице (рис. 5.13) при обновлении запроса.

Подытожим. Запрос Power Query содержит три различных действия по преобразованию данных:

  1. Определение диапазона в качестве источника данных для запроса (и преобразование диапазона в Таблицу перед загрузкой его в редактор Power Query).
  2. Преобразование свернутых данных в простую вертикальную таблицу.
  3. Переименование столбцов.

Эти действия были записаны на языке M. Чтобы увидеть код, в Excel активируйте окно Запросы и подключения. Для этого пройдите по меню Данные –> Запросы и подключения. В окне Запросы и подключения кликните правой кнопкой мыши на единственном запросе Таблица2, и выберите опцию Изменить. Откроется окно редактора Power Query. Пройдите по меню Главная –> Расширенный редактор:

Ris. 5.14. Kod zaprosa na yazyke M

Рис. 5.14. Код запроса на языке М

[1] Работа с моделью данных описана в книге всего лишь в нескольких абзацах. Если вас интересует эта тема, рекомендую Роб Колли. Формулы DAX для Power Pivot. – Прим. Багузина.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *