Чем отличается сводная таблица от pivot таблицы в excel
Добавьте таблицы поиска в свои наборы данных с помощью этого изобретательного дополнения
У вас есть данные и их много. Если вы хотите проанализировать все эти данные, узнайте, как использовать надстройку Power Pivot с Excel для импорта наборов данных, определения связей, создания сводных таблиц и создания сводных диаграмм.
Инструкции в этой статье применяются к Excel 2019, 2016, 2013 и Excel для Office 365.
Как получить надстройку Excel Power Pivot
Power Pivot предоставляет вам мощное приложение для анализа и анализа бизнес-данных. Вам не нужно специализированное обучение для разработки моделей данных и выполнения расчетов. Вам просто нужно включить его, прежде чем вы сможете его использовать.
Откройте Excel .
Выберите Файл > Параметры .
Выберите Надстройки .
Откройте раскрывающееся меню Управление и выберите Надстройки COM .
Выберите Перейти .
Выберите Microsoft Power Pivot для Excel .
Выберите ОК . Вкладка Power Pivot добавлена в Excel.
Следуйте вместе с учебником
Если вы хотите быстро приступить к работе с Power Pivot, учитесь на примере. У Microsoft есть несколько примеров наборов данных, доступных для бесплатной загрузки, которые содержат необработанные данные, модель данных и примеры анализа данных. Это отличные инструменты обучения, которые позволяют понять, как профессионалы анализируют большие данные.
В этом руководстве используется образец рабочей книги Microsoft Student Data Model. В первой заметке на странице вы найдете ссылку для загрузки учебного пособия и заполненной модели данных.
Данные в этом образце книги Excel имеют следующее:
- Рабочая тетрадь содержит четыре рабочих листа.
- Каждый лист содержит связанные данные, то есть, по крайней мере, один заголовок столбца на листе соответствует заголовку столбца в другом листе.
- Данные в каждом рабочем листе форматируются в виде таблицы.
- Каждая ячейка в таблице содержит данные. В таблицах нет пустых ячеек, строк или столбцов.
Есть другие примеры наборов данных на веб-сайте Microsoft. Изучите эти учебные ресурсы:
- Загрузите данные из базы данных Microsoft Access, в которой описаны олимпийские медали.
- Загрузите три образца Business Intelligence, которые показывают, как использовать Power Pivot для импорта данных, создания отношений, создания сводных таблиц и проектирования сводных диаграмм.
Перед использованием любого набора данных очистите его. Используйте функцию CLEAN в Excel, чтобы избавиться от непечатных символов, запустить проверку орфографии, удалить дублирующиеся строки данных, преобразовать числа и даты в нужный формат и перегруппировать данные.
Как добавить данные в файл Excel и построить модель данных
Вы собрали данные, которые вам нужны. Теперь пришло время импортировать ваши наборы данных в Excel и автоматически создать модель данных. Модель данных похожа на реляционную базу данных и предоставляет табличные данные, используемые в сводных таблицах и сводных диаграммах.
Если вам нужны данные для школьного задания, рабочего проекта или для выполнения этого учебного руководства, вы найдете отличные общедоступные наборы данных на GitHub.
Чтобы импортировать данные Excel в модель данных Power Pivot:
Откройте пустой лист и сохраните файл с уникальным именем.
Выберите Данные , затем выберите Получить данные > Из файла > Из рабочей книги , чтобы открыть Импорт данных диалоговое окно.
В Excel 2013 выберите Power Query > Получить внешние данные и выберите источник данных.
Перейдите в папку, содержащую файл Excel, выберите файл, затем выберите Импорт , чтобы открыть навигатор.
Установите флажок Выбрать несколько элементов .
Выберите таблицы, которые вы хотите импортировать.
При импорте двух или более таблиц Excel автоматически создает модель данных.
Выберите Загрузить , чтобы импортировать таблицы данных в модель данных.
Чтобы убедиться, что импорт прошел успешно и модель данных была создана, перейдите в раздел Данные и в группе Инструменты данных выберите Перейти в окно Power Pivot .
Окно Power Pivot отображает ваши данные в формате рабочего листа и состоит из трех основных областей: таблица данных, область расчета и вкладки таблицы данных.
Вкладки в нижней части окна Power Pivot соответствуют каждой из импортированных таблиц.
Закройте окно Power Pivot.
Если вы хотите добавить новые данные в модель данных, в окне Excel перейдите на Power Pivot и выберите Добавить в модель данных . Данные отображаются в виде новой вкладки в окне Power Pivot.
Создание связей между таблицами с помощью Power Pivot Excel
Теперь, когда у вас есть модель данных, пришло время создать отношения между каждой из таблиц данных.
Выберите Power Pivot , затем выберите Управление , чтобы открыть окно Power Pivot.
Выберите Главная , затем выберите Вид диаграммы .
Импортированные таблицы отображаются в виде отдельных блоков в представлении схемы. Перетащите, чтобы переместить таблицы в другое место. Перетащите угол окна, чтобы изменить его размер.
Перетащите заголовок столбца из одной таблицы в другую или в таблицы, содержащие одинаковый заголовок столбца.
Продолжайте сопоставлять заголовки столбцов.
Выберите Главная , затем выберите Просмотр данных .
Как создавать сводные таблицы
Когда вы используете Power Pivot для создания модели данных, большая часть тяжелой работы, включающей сводные таблицы и сводные диаграммы, была выполнена для вас. Отношения, которые вы создали между таблицами в вашем наборе данных, используются для добавления полей, которые вы будете использовать для создания сводных таблиц и сводных диаграмм.
В окне Power Pivot выберите Главная , затем выберите Сводная таблица .
В диалоговом окне Создать сводную таблицу выберите Новый лист , затем выберите ОК .
На панели Поля сводной таблицы выберите поля, которые нужно добавить в сводную таблицу. В этом примере создается сводная таблица, которая содержит имя учащегося и его среднюю оценку.
Чтобы отсортировать данные сводной таблицы, перетащите поле в область «Фильтры». В этом примере поле «Имя класса» добавляется в область «Фильтры», поэтому список можно отфильтровать, чтобы показать среднюю оценку ученика для класса.
Чтобы изменить метод расчета, используемый полем в области «Значения», выберите раскрывающийся список рядом с именем поля и выберите Настройки поля значения . В этом примере сумма оценки была изменена на среднюю оценку.
Проанализируйте ваши данные. Поэкспериментируйте с фильтрами и отсортируйте данные, используя стрелки раскрывающегося списка заголовков столбцов.
Преобразовать сводную таблицу в сводную диаграмму
Если вы хотите визуализировать данные сводной таблицы, превратите сводную таблицу в сводную диаграмму.
- Выберите сводную таблицу, затем перейдите в Анализ инструментов сводной таблицы .
- Выберите Сводная диаграмма , чтобы открыть диалоговое окно Вставить диаграмму .
- Выберите диаграмму, затем нажмите ОК .
Создать сводные диаграммы
Если вы предпочитаете анализировать данные в визуальном формате, создайте сводную диаграмму.
В окне Power Pivot выберите Главная , затем выберите стрелку раскрывающегося списка Сводная таблица . Появится список параметров.
Выберите Сводная диаграмма .
Выберите Новый лист и выберите ОК . Заполнитель PivotChart появится на новом листе.
Перейдите на страницу Анализ инструментов сводной диаграммы и выберите Список полей , чтобы отобразить панель полей сводной диаграммы.
Перетащите поля для добавления в сводную диаграмму. В этом примере создается сводная диаграмма, показывающая среднюю оценку для классов, отфильтрованных по семестрам.
Проанализируйте ваши данные. Поэкспериментируйте с фильтрами и отсортируйте данные с помощью стрелок раскрывающегося списка заголовков столбцов.
Термины "Power Query", "Power Pivot", "Power BI" и прочие "пауэры" все чаще всплывают в статьях и материалах о Microsoft Excel. По моему опыту, далеко не все ясно представляют себе что скрывается за этими понятиями, как они между собой взаимосвязаны и как могут помочь простому пользователю Excel.
Давайте проясним ситуацию.
Power Query
Еще в 2013 году специально созданная группа разработчиков внутри Microsoft выпустила для Excel бесплатную надстройку Power Query (другие названия - Data Explorer, Get&Transform), которая умеет массу полезных для повседневной работы вещей:
- Загружать данные в Excel из почти 40 различных источников, среди которых базы данных (SQL, Oracle, Access, Teradata. ), корпоративные ERP-системы (SAP, Microsoft Dynamics, 1C. ), интернет-сервисы (Facebook, Google Analytics, почти любые сайты).
- Собирать данные из файлов всех основных типов данных (XLSX, TXT, CSV, JSON, HTML, XML. ), как поодиночке, так и сразу оптом - из всех файлов указанной папки. Из книг Excel можно автоматически загружать данные сразу со всех листов.
- Зачищать полученные данные от "мусора": лишних столбцов или строк, повторов, служебной информации в "шапке", лишних пробелов или непечатаемых символов и т.п.
- Приводить данные в порядок: исправлять регистр, числа-как-текст, заполнять пробелы, добавлять правильную "шапку" таблицы, разбирать "слипшийся" текст на столбцы и склеивать обратно, делить дату на составляющие и т.д.
- Всячески трансформировать таблицы, приводя их в желаемый вид (фильтровать, сортировать, менять порядок столбцов, транспонировать, добавлять итоги, разворачивать кросс-таблицы в плоские и сворачивать обратно).
- Подставлять данные из одной таблицы в другую по совпадению одного или нескольких параметров, т.е. прекрасно заменяет функцию ВПР (VLOOKUP) и ее аналоги.
Power Query встречается в двух вариантах: как отдельная надстройка для Excel 2010-2013, которую можно скачать с официального сайта Microsoft и как часть Excel 2016. В первом случае после установки в Excel появляется отдельная вкладка:
В Excel 2016 весь функционал Power Query уже встроен по умолчанию и находится на вкладке Данные (Data) в виде группы Получить и преобразовать (Get & Transform) :
Возможности этих вариантов совершенно идентичны.
Принципиальной особоенностью Power Query является то, что все действия по импорту и трансформации данных запоминаются в виде запроса - последовательности шагов на внутреннем языке программирования Power Query, который лаконично называется "М". Шаги можно всегда отредактировать и воспроизвести повторно любое количество раз (обновить запрос).
Основное окно Power Query обычно выглядит примерно так:
По моему мнению, это самая полезная для широкого круга пользователей надстройка из всех перечисленных в этой статье. Очень много задач, для которых раньше приходилось либо жутко извращаться с формулами, либо писать макросы - теперь легко и красиво делаются в Power Query. Да еще и с последующим автоматическим обновлением результатов. А учитывая бесплатность, по соотношению "цена-качество" Power Query просто вне конкуренции и абсолютный must have для любого средне-продвинутого пользователя Excel в наши дни.
Power Pivot
Power Pivot - это тоже надстройка для Microsoft Excel, но предназначенная немного для других задач. Если Power Query сосредоточена на импорте и обработке, то Power Pivot нужен, в основном, для сложного анализа больших объемов данных. В первом приближении, можно думать о Power Pivot как о прокачанных сводных таблицах.
Общие принципы работы в Power Pivot следующие:
- Сначала мы загружаем данные в Power Pivot - поддерживается 15 различных источников: распространенные БД (SQL, Oracle, Access. ), файлы Excel, текстовые файлы, веб-каналы данных. Кроме того, можно использовать Power Query как источник данных, что делает анализ почти всеядным.
- Затем между загруженными таблицами настраиваются связи или, как еще говорят, создается Модель Данных. Это позволит в будущем строить отчеты по любым полям из имеющихся таблиц так, будто это одна таблица. И никаких ВПР опять же.
- При необходимости, в Модель Данных добавляют дополнительные вычисления с помощью вычисляемых столбцов (аналог столбца с формулами в "умной таблице") и мер (аналог вычисляемого поля в сводной). Всё это пишется на специальном внутреннем языке Power Pivot, который называется DAX (Data Analysis eXpressions).
- На листе Excel по Модели Данных строятся интересующие нас отчеты в виде сводных таблиц и диаграмм.
Главное окно Power Pivot выглядит примерно так:
А так выглядит Модель Данных, т.е. все загруженные таблицы с созданными связями:
У Power Pivot есть ряд особенностей, делающих её уникальным инструментом для некоторых задач:
- В Power Pivot нет предела по количеству строк (как в Excel). Можно грузить таблицы любого размера и спокойно работать с ними.
- Power Pivot очень хорошо умеет сжимать данные при загрузке их в Модель. 50 Мб исходный текстовый файл может легко превратиться в 3-5 Мб после загрузки.
- Поскольку "под капотом" у Power Pivot, по сути, полноценный движок базы данных, то с большими объемами информации он справляется очень быстро. Нужно проанализировать 10-15 млн. записей и построить сводную? И все это на стареньком ноутбуке? Без проблем!
К сожалению, пока что Power Pivot входит не во все версии Excel. Если у вас Excel 2010, то скачать её можно бесплатно с сайта Microsoft. А вот если у вас Excel 2013-2016, то всё зависит от вашей лицензии, т.к. в некоторых вариантах она включена (Office Pro Plus, например), а в некоторых нет (Office 365 Home, Office 365 Personal и т.д.) Подробнее об этом можно почитать тут.
Power Maps
Эта надстройка впервые появилась в 2013 году и первоначально называлась GeoFlow. Она предназначена для визуализации гео-данных, т.е. числовой информации на географических картах. Исходные данные для отображения берутся все из той же Модели Данных Power Pivot (см. предыдущий пункт).
Демо-версию Power Map (почти не отличающуюся от полной по возможностям, кстати) можно совершенно бесплатно загрузить опять же с сайта Microsoft. Полная же версия включена в некоторые пакеты Microsoft Office 2013-2016 вместе с Power Pivot - в виде кнопки 3D-карта на вкладке Вставка (Insert - 3D-map) :
Ключевые особенности Power Map:
- Карты могут быть как плоскими, так и объемными (земной шар).
- Можно использовать несколько разных типов визуализации (гистограммы, пузырьковые диаграммы, тепловые карты, заливку областями).
- Можно добавлять измерение времени, т.е. анимировать процесс и смотреть на него в развитии.
- Карты подгружаются из сервиса Bing Maps, т.е. для просмотра нужен весьма шустрый доступ в интернет. Иногда возникают сложности с правильным распознаванием адресов, т.к. названия в данных не всегда совпадают с Bing Maps.
- В полной (не демо) версии Power Map можно использовать собственные загружаемые карты, например визуализировать посетителей торгового центра или цены на квартиры в жилом доме прямо на строительном плане.
- На основе созданных гео-визуализаций можно прямо в Power Map создавать видеоролики (пример), чтобы поделиться ими потом с теми, у кого надстройка не установлена или включить в презентацию Power Point.
Power View
Эта надстройка появилась впервые в составе Excel 2013 и предназначена для "оживления" ваших данных - построения интерактивных графиков, диаграмм, карт и таблиц. Иногда для этого используют термины дашборд (dashboard) или панель показателей (scorecard) . Суть в том, что вы можете вставить в ваш файл Excel специальный лист без ячеек - слайд Power View, куда добавить текст, картинки и массу различного типа визуализаций по вашим данным из Модели Данных Power Pivot.
Выглядеть это будет примерно так:
Нюансы тут такие:
- Исходные данные берутся всё оттуда же - из Модели Данных Power Pivot.
- Для работы с Power View необходимо установить на вашем компьютере Silverlight - майкрософтовский аналог Flash (бесплатный).
На сайте Microsoft, кстати, есть весьма приличный обучающий курс по Power View на русском языке.
Power BI
В отличие от предыдущих, Power BI - это не надстройка для Excel, а отдельный продукт, представляющий собой целый комплекс средств для бизнес- анализа и визуализации. Он состоит из трех ключевых элементов:
В Power BI Desktop можно:
- Загружать данные из более чем 70 различных источников (как в Power Query + дополнительные коннекторы).
- Связывать таблицы в модель (как в Power Pivot)
- Добавлять к данным дополнительные вычисления с помощью мер и вычисляемых столбцов на DAX (как в Power Pivot)
- Создавать на основе данных красивейшие интерактивные отчеты с разного типа визуализациями (очень похоже на Power View, но еще лучше и мощнее).
- Публиковать созданные отчеты на сайте Power BI Service (см. следующий пункт) и делиться ими с коллегами. Причем есть возможность давать разные права (чтение, редактирование) разным людям.
2. Онлайн-сервис Power BI - упрощенно говоря, это сайт, где у вас и у каждого пользователя в вашей компании будет своя "песочница" (workspace) куда можно загружать созданные в Power BI Desktop отчеты. Помимо просмотра, позволяет их даже редактировать, воспроизводя онлайн почти весь функционал Power BI Desktop. Также сюда можно заимствовать отдельные визуализации из чужих отчетов, собирая из них свои авторские дашборды.
Выглядит это примерно так:
На iPhone, например, созданный выше отчет выглядит так:
Причем всё это с сохранением интерактивностии и анимации + заточенность под тач и рисование по экрану пером. Очень удобно. Таким образом, бизнес-аналитика становится доступной всем ключевым лицам компании в любой момент и в любом месте - нужен только доступ в интернет.
Тарифные планы Power BI. Power BI Desktop и Mobile бесплатны изначально, большинство функций Power BI Service - тоже. Так что для персонального использования или применения в пределах небольшой компании за всё вышеперечисленное не нужно платить ни копейки и можно смело оставаться на плане Free. Если вы хотите делиться отчетами с коллегами и администрировать их права доступа, то придется перейти на Pro (10$ в месяц за пользователя). Есть еще Premium - для больших компаний (>500 пользователей), которым требуются для данных отдельные хранилища и серверные мощности.
Одно из преимуществ PowerPivot заключается в том, что несколько сводных таблиц могут использовать одни и те же данные и срезы. Просто откройте раскрывающийся список Сводная таблица (PivolTable), находящийся на вкладке Главная (Ноmе) ленты PowerPivot (рис. 10.19), и выберите в нем сводную таблицу, сводную диаграмму, сводную таблицу и диаграмму, две сводные диаграммы и т. д.
Рис. 10.19. Помимо единственной сводной таблицы либо диаграммы доступно множество других параметров
Для создания сводной таблицы в PowcrPivol выполните следующие действия.
-
Выделите сводную таблицу. На экране появится окно Excel со вкладкой PowerPivot.
Рис. 10.21. Структура списка полей PowerPivot отличается от структуры списка полей обычной сводной таблицы
Рис 10.22. Эта сводная таблица суммирует 1.8 млн строк, а также использует данные из двух таблиц
А теперь можно воспользоваться инструментами из группы контекстных вкладок Работа со сводными таблицами (PivotTable Tools) для форматирования созданной сводной таблицы. Например, можно применить денежный формат и переименовать поле Sum of Revenue (Сумма по полю Доход), выбрать формат с чередующимися строками и т.д. Для демонстрации других свойств сводной таблицы PowerPivot добавим в нее срезы.
Power Pivot - это мощнейший инструмент excel, предназначенный в первую очередь для настройки связей между различными источниками информации и одновременной работы с этими данными без потери быстродействия программы, даже если они имеют огромный размер.
Первоначально Power Pivot являлся отдельной надстройкой на excel, однако начиная с версии 2013 ее включили в состав программы. Теперь ее не надо скачивать и отдельно устанавливать. Power Pivot также вошел в состав Power BI, который в свою очередь включает в себя Power Query и мощнейший инструмент для визуализации данных.
Соответственно, вопрос о том, а стоит ли мне знакомиться с этим инструментом, если я и так хорошо знаю excel, не должен стоять. Однозначно, да ! Power Pivot обязательно должен быть в вашем арсенале .
Для того, чтобы найти Power Pivot у себя в excel необходимо перейти на ленте во вкладку Power Pivot.
Если у вас нет этой вкладки - ее необходимо включить . Для этого на ленте выберите команду Файл -> Параметры -> Настроить ленту. В списке слева в поле "Выбрать команды" выберите "Основные вкладки". В списке "Основные вкладки" найдите строчку Power Pivot, выделите ее, нажмите Добавить и ОК:
Первое, что вы делаете, начиная работать в Power Pivot - загружаете в него данные . Если этого не сделать, то надстройке работать будет не с чем.
Предположим, у нас есть 3 таблицы, которые нам необходимо загрузить в Power Pivot. В первой таблице содержится дата обращения клиента за кредитом. Во второй - Сумма кредита и его тип. В третьей - расшифровка типов кредита.
Если не использовать Power Pivot, вам бы пришлось несколько раз прописывать формулу с ВПР, чтобы соединить все данные в одной таблице.
Чтобы загрузить таблицу в Power Pivot, выделите любую из ячеек диапазона и на ленте во вкладке Power Pivot нажмите Добавить в модель. В открывшемся окне "Создание таблицы" проверьте, что границы таблицы определены верно и оставьте галку "Таблица имеет верхние колонтитулы.
После нажатия на ОК, откроется окно Power Pivot, в котором появится ваша таблица:
Визуально структура Power Pivot выглядит практически также, как и excel, только содержит ленту со своими, отличными от excel, командами.
Важно : Power Pivot не позволяет редактировать исходные данные, но позволяет проводить расчеты с их использованием. При редактировании исходных данных в excel, они автоматически будут обновляться в Power Pivot.
Таким же образом я загрузила остальные две таблицы. Обратите внимание, что каждая из таблиц расположилась на своей вкладке (Таблица1, Таблица2 и Таблица3 внизу слева). Можно переименовать эти вкладки, нажав правой кнопкой на названии вкладки и перейдя на п. Переименовать.
Для того, чтобы настроить отношения между этими таблицами (связать их по ключевым столбцам) перейдем на ленте в блок Просмотр -> Представление диаграммы. Откроется визуализация, в которой в каждом квадрате находится отдельная таблица с перечислением ее полей:
Таблицы "Дата обращения" и "Сумма кредита" могут быть соединены по единому полю "Клиент". Таблицы "Сумма кредита" и Тип кредита" могут быть соединены по полю "Тип кредита".
Для соединения таблиц нажмите на поле Клиент в таблице "Дата обращения" и перетащите его мышью на поле Клиент в таблице "Сумма кредита". Тоже самое сделаем и с полем Тип кредита.
На диаграмме появились связи в виде линии с единицей и *. Такие связи называют "1 ко многим", когда в таблице с 1 все значения являются уникальными, а в таблице с обозначением * значения могут дублироваться.
Теперь наши таблицы связаны и с ними можно работать как с единой моделью данных. Больше - в следующих статьях про Power Pivot.
✔ Ищите ответы на свои вопросы в статье Фишки excel - подборка статей . Там я перечислила все свои статьи по разбору работы функций excel.
✔ Вот здесь есть подборка видео о том, как строить необычные диаграммы - Фишки Excel - подборка видеороликов .
Power Query и Power Pivot — надстройки Excel для легкой работы с тяжелыми файлами. С помощью Power Query можно подключать и обрабатывать источники информации произвольного вида, а в Power Pivot – выполнять сложные расчеты и создавать модель данных.
Надстройка Power Query
Появление Power Query – одно из важных событий в истории развития Excel. То, что раньше пользователи делали в Excel с помощью рутинных операций по преобразованию данных, писали огромные формулы или макросы на VBA, теперь можно выполнять буквально в несколько щелчков мышкой.
Power Query – это надстройка для создания запросов по импорту, очистке и преобразованию данных.
Power Query сам автоматически генерирует и записывает код запросов на языке M .Но это вовсе не значит, что пользователям нужно писать код – интерфейс Power Query очень понятный и позволяет выполнять операции только с помощью мышки. Большинство операций по преобразованию данных в нем можно выполнить, не написав ни одной строчки кода. Все запросы Power Query записываются и сохраняются, поэтому при последующих подключениях повторять операции еще раз не нужно – они выполнятся автоматически после нажатия на кнопку в меню Данные → Обновить.
Как работает
В привычном нам виде Power Query впервые появился в Excel 2013. В Excel 2010 и 2013 надстройка отображается в виде отдельной вкладки «Power Query».
В версии Excel 2019 на вкладке Данные разделы с Power Query — «Получить и преобразовать данные» и «Запросы и подключения».
Читайте также:
- Как сделать мероприятие в дискорд
- Панель навигации 1с это
- Excel определите срок кредита при условии что размер ежемесячных выплат не должен превышать 8000 руб
- Для оформления коробки со своей антивирусной программой лаборатория касперского выбрала изображение
- Uplay библиотека игр как посмотреть в браузере