Что такое пивот в экселе
Power Pivot: мощные средства анализа и моделирования данных в Excel
Power Pivot — это Excel надстройка, которая используется для выполнения мощного анализа данных и создания сложных моделей данных. С помощью Power Pivot можно работать с большими объемами данных из различных источников одновременно, быстро анализировать их и распространять результаты.
Примечание. Прежде чем приступить к подробной информации, вы можете вернуться к видеоролику или воспользоваться учебным руководством по & преобразованию и Power Pivot.
Импорт миллионов строк данных из нескольких источников С помощью Power Pivot для Excel можно импортировать миллионы строк данных из нескольких источников в одну книгу Excel, создавать связи между разнородными данными, создавать вычисляемые столбцы и меры с помощью формул, создавать систетные и свиты, а затем дополнительно анализировать данные, чтобы принимать вовремя деловые решения, не требуя помощи ИТ-поддержки.
Быстрое вычисление и анализ Обработка миллионов строк примерно одновременно с тысячами и максимальное количество многоядерных процессоров и гигабайт памяти для быстрой обработки вычислений. Преодолеет существующие ограничения для масштабного анализа данных на компьютере с помощью эффективных алгоритмов сжатия для загрузки даже самых крупных наборов данных в память.
Практически неограниченная поддержка источников данных Служит основой для импорта и объединения исходных данных из любого места для масштабного анализа на компьютере, включая реляционные базы данных, многомерные источники, облачные службы, веб-каналы данных, Excel файлы, текстовые файлы и данные из Интернета.
Панель управленияPower Pivot безопасности и управления позволяет ИТ-администраторам отслеживать общие приложения и управлять ими для обеспечения безопасности, высокой доступности и производительности.
Выражения анализа данных (DAX) DAX — это язык формул, расширяющий возможности работы с данными в Excel для более сложной группировки, вычислений и анализа. Синтаксис формул DAX очень похож на синтаксис Excel формул.
Задачи в Power Pivot или в Excel
Основное различие между Power Pivot и Excel заключается в том, что в окне Power Pivot можно создавать более сложные модели данных. Вот некоторые задачи для сравнения.
Импорт данных из различных источников, таких как большие корпоративные базы данных, публичные ленты новостей, электронные таблицы и текстовые файлы на локальном компьютере.
Импорт всех данных из источника данных.
Фильтрация данных и переименование столбцов и таблиц при импорте.
Прочитайте о том, как получать данные с помощью надстройки Power Pivot
Таблицы могут находиться на любом листе книги. Листы могут содержать несколько таблиц.
Таблицы организованы в виде отдельных страниц с вкладками в окне Power Pivot.
Редактирование данных в таблице
Можно изменять значения в отдельных ячейках таблицы.
Нельзя изменять отдельные ячейки.
Создание связей между таблицами
В диалоговом окне «Связи».
В представлении диаграммы или диалоговом окне «Создание связей».
Расширенные формулы на языке выражений анализа данных (DAX).
Иерархии можно создавать и использовать везде в книге, в том числе в Power View.
Создание ключевых показателей эффективности
Создавайте KPIS для использования в свиттах и отчетах Power View.
Создаваемые перспективы позволяют ограничить число столбцов и таблиц, которые видны пользователям книги.
Создание сводных таблиц и сводных диаграмм
Нажмите кнопку PivotTable в окне Power Pivot.
Расширение модели для Power View
Создается базовая модель данных.
Доступны расширения, например определение полей по умолчанию, изображений и уникальных значений.
Использование Visual Basic для Applications (VBA)
VBA не поддерживается в окне Power Pivot окна.
DaX используется в вычисляемом столбце и вычисляемом поле.
Способ хранения данных
Данные, которые вы работаете в Excel и в окне Power Pivot, хранятся в аналитической базе данных внутри книги Excel, а мощный локальный механизм загружает, запрашивает и обновляет данные в этой базе данных. Так как данные Excel, они сразу же доступны для сводных и сводных книг, Power View и других функций в Excel, которые используются для агрегированных данных и взаимодействия с ними. Все презентации данных и взаимодействие предоставляются Excel; данные и Excel презентации содержатся в одном файле книги. Power Pivot поддерживает файлы размером до 2 ГБ и позволяет работать с данными размером до 4 ГБ в памяти.
Сохранение в SharePoint
Книги, изменяемые с помощью Power Pivot, можно сделать общими, как и любые другие файлы. Однако публикация книги в среде SharePoint с включенными службами Excel имеет некоторые преимущества. На сервере SharePoint службы Excel обрабатывают данные и готовят их к просмотру в окне браузера, где другие пользователи смогут продолжить анализ данных.
В SharePoint вы можете добавить Power Pivot для SharePoint, чтобы получить дополнительную поддержку для совместной работы и управления документами, включая галерею Power Pivot, панель мониторинга управления Power Pivot в Центре администрирования, запланированное обновление данных и возможность использования опубликованной книги в качестве внешнего источника данных в SharePoint.
Получение справочной информации
Все о Power Pivot можно узнать в справке По Power Pivot.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Узнайте, как использовать Power Query и Power Pivot в Excel
Быстрое получение аналитики
С помощью Power Query & (в Excel) и Power Pivot в Excel вы будете тратить меньше времени на обработку данных и больше времени на их влияние. Для ознакомления посмотрите это 2-минутное видео.
Попробуйте сами
Независимо от того, небольшие или крупные наборы данных с миллионами строк, вы можете быстрее подключаться, очищать, анализировать данные и делиться ими с помощью Power Query (или получить & Transform в Excel) и Power Pivot.
Вопросы и ответы
С Приложения Microsoft 365 для предприятий приложение Excel на Windows для ПК предлагает все функции Power Query и Power Pivot, которые улучшают аналитику и моделирование, могут использовать расширенные возможности подключения к данным и эффективно обмениваться данными в организации. Дополнительные сведения о доступности Power Query и Power Pivot см. ниже и узнайте у ИТ-группы, поддерживает ли ваш текущий план Office эти функции.
Основные функции бизнес-аналитики
Основные функции аналитики доступны для Microsoft 365 подписки или Office 2016. К таким функциям относятся срезы, срезы, диаграммы и возможности модели данных. С помощью Power Query можно импортировать, формировать и объединять данные из файлов, баз данных и веб-сайтов. Поддержка базовой модели данных позволяет загружать данные в модель данных, автоматически обнаруживать связи, добавлять группировку по времени и создавать показатели DAX.
Дополнительные сведения о том, как получить наилучшие результаты, см. в этой Office.
Расширенные функции бизнес-аналитики
Расширенные возможности аналитики доступны в Приложения Microsoft 365 для предприятий, Office 2019 Professional, Office 2019 Professional Plus и Excel 2019. Помимо основных функций аналитики, с помощью Power Query можно импортировать, формировать и объединять данные из корпоративных, больших данных и облачных источников данных, а также делиться запросами. Поддержка расширенных моделей данных в Power Pivot включает специальный редактор моделирования данных, представления данных, вычисляемые столбцы DAX, ключевые показатели эффективности и иерархии.
Дополнительные сведения см. в источниках данных Power Query в Excel версияхи Где находится Power Pivot?
Excel 2016 для Mac и Excel 2019 для Mac имеют множество основных функций анализа, перечисленных выше: срезы, диаграммы и основные возможности импорта. У них еще нет Power Query или Power Pivot, но мы постоянно улучшаем Excel на Mac. Excel для Microsoft 365 для Mac предлагает ограниченную поддержку Power Query.
Дополнительные сведения см. в этой Excel для Mac.
Power Query обеспечивает быстрый и простой сбор и формирование данных. Power Query позволяет подключать, объединять и уточнять источники данных в нужных для анализа запросах. Power Query можно использовать в четыре этапа:
Подключение Импорт данных и подключение к данным в облаке, в службе или локально.
Преобразовать Формировать данные в нужном виде, а исходный источник остается без изменений.
Объединить Дополнительные формы данных путем их интеграции из нескольких источников для получения уникального представления данных.
Нагрузки Выполните запрос и сохраните его на компьютере или в модели данных.
Дополнительные сведения см. в справке по Power Query в Excel и Power Query для Excel.
Возможность Power Pivot предназначена для эффективного анализа и создания сложных моделей данных. С помощью Power Pivot можно работать с большими объемами данных из различных источников одновременно, быстро анализировать их и распространять результаты. Вы можете использовать модель данных и коллекцию таблиц, чтобы создавать связи, сохраняемые в книге. Модель данных тесно интегрирована с другими функциями Excel, например таблицами и сводными таблицами, чтобы обеспечивать удобный интерфейс анализа.
Дополнительные сведения см. в этой Microsoft Excel.
Power BI — это набор инструментов бизнес-аналитики для предоставления подробных сведений обо всей вашей организации. С помощью Power BI Desktop вы можете подключаться к сотням источников данных, упрощать подготовку данных и выполнять специальный анализ. Вы также можете создавать отчеты и публиковать их в организации для использования в Интернете и на мобильных устройствах. Все пользователи могут создавать персонализированные информационные панели с использованием всестороннего представления организации. Имеются встроенные средства управления и обеспечения безопасности.
Excel 2016 и Power BI Desktop предоставляют уникальный для отрасли набор инструментов. Вместе они позволяют бизнес-аналитикам легко собирать, формировать, анализировать и наглядно изучать данные. Power BI — это набор инструментов бизнес-аналитики для предоставления подробных сведений обо всей вашей организации. Он помогает всем сотрудникам организации объединять данные из различных источников: книг Excel, локальных корпоративных данных, служб Azure и других популярных облачных решений, таких как Salesforce.com или Marketo, чтобы просматривать и совместно использовать информационные панели и интерактивные отчеты.
Дополнительные сведения см. в Excel для Power BI.
Подписчики Microsoft 365 и Excel 2019 добавили улучшения в функции и функции, к которые вы уже привыкли.
Что такое Power Query / Pivot / Map / View / BI и зачем они пользователю Excel
Термины «Power Query», «Power Pivot», «Power BI» и прочие «пауэры» все чаще всплывают в статьях и материалах о Microsoft Excel. По моему опыту, далеко не все ясно представляют себе что скрывается за этими понятиями, как они между собой взаимосвязаны и как могут помочь простому пользователю Excel.
Давайте проясним ситуацию.
Power Query
Power Query встречается в двух вариантах: как отдельная надстройка для Excel 2010-2013, которую можно скачать с официального сайта Microsoft и как часть Excel 2016. В первом случае после установки в Excel появляется отдельная вкладка:
В Excel 2016 весь функционал Power Query уже встроен по умолчанию и находится на вкладке Данные (Data) в виде группы Получить и преобразовать (Get & Transform) :
Возможности этих вариантов совершенно идентичны.
Основное окно Power Query обычно выглядит примерно так:
Power Pivot
Общие принципы работы в Power Pivot следующие:
Главное окно Power Pivot выглядит примерно так:
А так выглядит Модель Данных, т.е. все загруженные таблицы с созданными связями:
У Power Pivot есть ряд особенностей, делающих её уникальным инструментом для некоторых задач:
К сожалению, пока что 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:
Power View
Выглядеть это будет примерно так:
На сайте Microsoft, кстати, есть весьма приличный обучающий курс по Power View на русском языке.
Power BI
В Power BI Desktop можно:
Выглядит это примерно так:
На iPhone, например, созданный выше отчет выглядит так:
Как сделать сводную таблицу в Excel – пошаговая инструкция для чайников
В этом руководстве вы узнаете, что такое сводная таблица, и найдете подробную инструкцию, как по шагам создавать и использовать её в Excel.
Если вы работаете с большими наборами данных в Excel, то сводная таблица очень удобна для быстрого создания интерактивного представления из множества записей. Помимо прочего, она может автоматически сортировать и фильтровать информацию, подсчитывать итоги, вычислять среднее значение, а также создавать перекрестные таблицы. Это позволяет взглянуть на ваши цифры совершенно с новой стороны.
Важно также и то, что при этом ваши исходные данные не затрагиваются – что бы вы не делали с вашей сводной таблицей. Вы просто выбираете такой способ отображения, который позволит вам увидеть новые закономерности и связи. Ваши показатели будут разделены на группы, а огромный объем информации будет представлен в понятной и доступной для анализа форме.
Что такое сводная таблица?
Это инструмент для изучения и обобщения больших объемов данных, анализа связанных итогов и представления отчетов. Они помогут вам:
Например, у вас множество записей в электронной таблице с цифрами продаж шоколада:
И каждый день сюда добавляются все новые сведения. Одним из возможных способов суммирования этого длинного списка чисел по одному или нескольким условиям является использование формул, как было продемонстрировано в руководствах по функциям СУММЕСЛИ и СУММЕСЛИМН.
Однако, когда вы хотите сравнить несколько показателей по каждому продавцу либо по отдельным товарам, использование сводных таблиц является гораздо более эффективным способом. Ведь при использовании функций вам придется писать много формул с достаточно сложными условиями. А здесь всего за несколько щелчков мыши вы можете получить гибкую и легко настраиваемую форму, которая суммирует ваши цифры как вам необходимо.
Вот посмотрите сами.
Этот скриншот демонстрирует лишь несколько из множества возможных вариантов анализа продаж. И далее мы рассмотрим примеры построения сводных таблиц в Excel 2016, 2013, 2010 и 2007.
Как создать сводную таблицу.
Многие думают, что создание отчетов при помощи сводных таблиц для «чайников» является сложным и трудоемким процессом. Но это не так! Microsoft много лет совершенствовала эту технологию, и в современных версиях Эксель они очень удобны и невероятно быстры.
Фактически, вы можете сделать это всего за пару минут. Для вас – небольшой самоучитель в виде пошаговой инструкции:
1. Организуйте свои исходные данные
Перед созданием сводного отчета организуйте свои данные в строки и столбцы, а затем преобразуйте диапазон данных в таблицу. Для этого выделите все используемые ячейки, перейдите на вкладку меню «Главная» и нажмите «Форматировать как таблицу».
Полезные советы:
2. Создаем и размещаем макет
Нажатие ОК создает пустой макет без цифр в целевом местоположении, который будет выглядеть примерно так:
Полезные советы:
Область, в которой вы работаете с полями макета, называется списком полей. Он расположен в правой части рабочего листа и разделен на заголовок и основной раздел:
Изменения, которые вы вносите в этих разделах, немедленно применяются в вашей таблице.
3. Как добавить поле
Чтобы иметь возможность добавить поле в нужную область, установите флажок рядом с его именем.
По умолчанию Microsoft Excel добавляет поля в раздел «Макет» следующим образом:
4. Как удалить поле из сводной таблицы?
Чтобы удалить любое поле, вы можете выполнить следующее:
И еще один простой и наглядный способ удаления поля. Перейдите в макет таблицы, зацепите мышкой ненужный вам элемент и перетащите его за пределы макета. Как только вы вытащите его за рамки, рядом со значком появится хатактерный крестик. Отпускайте кнопку мыши и наблюдайте, как внешний вид вашей таблицы сразу же изменится.
5. Как упорядочить поля?
Вы можете изменить расположение показателей тремя способами:
Все внесенные вами изменения применяются немедленно.
Ну а ежели спохватились, что сделали что-то не так, не забывайте, что есть «волшебная» комбинация клавиш CTRL+Z, которая отменяет сделанные вами изменения (если вы не сохранили их, нажав соответствующую клавишу).
6. Выберите функцию для значений (необязательно)
По умолчанию Microsoft Excel использует функцию «Сумма» для числовых показателей, которые вы помещаете в область «Значения». Когда вы помещаете нечисловые (текст, дата или логическое значение) или пустые значения в эту область, к ним применяется функция «Количество».
Думаю, названия операций говорят сами за себя, и дополнительные пояснения здесь не нужны. В крайнем случае, попробуйте различные варианты сами.
Здесь же вы можете изменить имя его на более приятное и понятное для вас. Ведь оно отображается в таблице, и поэтому должно выглядеть соответственно.
7. Используем различные вычисления в полях значения (необязательно)
Подсказка. Функция «Дополнительные вычисления» может оказаться особенно полезной, когда вы добавляете одно и то же поле более одного раза и показываете, как в нашем примере, общий объем продаж и объем продаж в процентах от общего количества одновременно. Согласитесь, обычными формулами делать такую таблицу придется долго. А тут – пара минут работы!
Итак, процесс создания завершен. Теперь пришло время немного поэкспериментировать, чтобы выбрать макет, наиболее подходящий для вашего набора данных.
Работа со списком показателей сводной таблицы
Чтобы изменить способ отображения вашей рабочей области, нажмите кнопку «Инструменты» и выберите предпочитаемый макет.
Вы также можете изменить размер панели по горизонтали, перетаскивая разделитель, который отделяет панель от листа.
Закрытие и открытие панели редактирования.
Закрыть список полей в сводной таблице так же просто, как нажать кнопку «Закрыть» (X) в верхнем правом углу панели. А вот как заставить его появиться снова – уже не так очевидно 🙂
Также можно нажать кнопку «Список полей» на ленте, которая находится на вкладке меню «Анализ».
Воспользуйтесь рекомендациями программы.
Как вы видите на скриншоте выше, Эксель смог предложить несколько базовых макетов для моих исходных данных, которые значительно уступают сводным таблицам, которые мы создали вручную несколько минут назад. Конечно, это только мое мнение 🙂
Давайте улучшим результат.
Теперь, когда вы знакомы с основами, вы можете перейти к вкладкам «Анализ» и «Конструктор» инструментов в Excel 2016 и 2013 ( вкладки « Параметры» и « Конструктор» в 2010 и 2007). Они появляются, как только вы щелкаете в любом месте таблицы.
Вы также можете получить доступ к параметрам и функциям, доступным для определенного элемента, щелкнув его правой кнопкой мыши (об этом мы уже говорили при создании).
После того, как вы построили таблицу на основе исходных данных, вы, возможно, захотите уточнить ее, чтобы провести более серьёзный анализ.
Чтобы улучшить дизайн, перейдите на вкладку «Конструктор», где вы найдете множество предопределенных стилей. Чтобы получить свой собственный стиль, нажмите кнопку «Создать стиль….» внизу галереи «Стили сводной таблицы».
На снимке экрана ниже показан новый дизайн и макет.
Я изменил цветовой макет, а также постарался, чтобы таблица была более компактной. Для этого поменяем параметры представления товара. Какие параметры я использовал – вы видите на скриншоте.
Думаю, стало даже лучше. 😊
Как избавиться от заголовков «Метки строк» и «Метки столбцов».
При создании сводной таблицы, Excel применяет Сжатую форму по умолчанию. Этот макет отображает «Метки строк» и «Метки столбцов» в качестве заголовков. Согласитесь, это не очень информативно, особенно для новичков.
И вот что мы получим в результате.
Показаны реальные имена, как вы видите на рисунке справа, что имеет гораздо больше смысла.
Как обновить сводную таблицу.
Хотя отчет связан с исходными данными, вы можете быть удивлены, узнав, что Excel не обновляет его автоматически. Это можно считать небольшим недостатком. Вы можете обновить его, выполнив операцию обновления вручную или же это произойдет автоматически при открытии файла.
Как обновить вручную.
Кроме того, вы можете по щелчку правой кнопки мыши выбрать пункт Обновить из появившегося контекстного меню.
Примечание. Если внешний вид вашей сводной таблицы сильно изменяется после обновления, проверьте параметры «Автоматически изменять ширину столбцов при обновлении» и « Сохранить форматирование ячейки при обновлении». Чтобы сделать это, откройте «Параметры сводной таблицы», как это показано на рисунке, и вы найдете там эти флажки.
Автоматическое обновление сводной таблицы при открытии файла.
Как переместить на новое место?
Как удалить сводную таблицу?
Если вам больше не нужен определенный сводный отчет, вы можете удалить его несколькими способами.
Примечание. Если у вас есть какая-либо диаграмма, построенная на основе свода, то описанная выше процедура удаления превратит ее в стандартную диаграмму, которую больше нельзя будет изменять или обновлять.
Надеемся, что этот самоучитель станет для вас хорошей отправной точкой. Далее нас ждут еще несколько рекомендаций, как работать со сводными таблицами. И спасибо за чтение!