Создание отчетов при помощи сводных таблиц. Pivot tables что это

Как использовать сводную таблицу Excel для анализа состояния проекта — Трюки и приемы в Microsoft Excel

Сводная таблица позволяет управлять крупным массивом данных, которые введены в ячейках рабочего листа. Благодаря сводным таблицам вы можете быстро анализировать данные под разными углами зрения и легко изменять их вид.Это довольно компактная совокупность данных, но вообразите, как бы выглядел подобный список, если в нем представить пару десятков исполнителей проекта и несколько сотен рисков! Было бы весьма непросто даже прочитать такой список, не говоря уже о возможности проанализировать его и сделать на этом основании какие-то выводы.

Рис. 1. Реестр рисков проекта Grant St. Move

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

Рис. 2. Результат преобразования в таблицу выделенного диапазона ячеек

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

Теперь, когда данные представлены в табличном формате, вы можете создать сводную таблицу, не забудьте купить клавиатуру для компьютера. Для начала установите табличный курсор в любой ячейке этой таблицы, затем щелкните на значке PivotTable (Сводная таблица) вкладки Insert (Вставка) и в появившемся меню выберите команду PivotTable (Сводная таблица).

Рис. 3. Значок PivotTable расположен в крайней части вкладки Insert (Вставка)

На экране появится диалоговое окно Create Pivot Table (Создание сводной таблицы), показанное на рис. 4. В этом диалоговом окне следует указать программе, на основе каких данных будет построена сводная таблица — рабочего листа текущей рабочей книги или внешних данных (например, SQL Server). Обратите внимание, что в поле Table/Range (Таблица или диапазон) мы оставили заданное по умолчанию значение — Table1 (Таблица1).

Рис. 4. Диалоговое окно Create Pivot Table (Создание сводной таблицы)

Щелкните на кнопке ОК. Программа немедленно создаст макет сводной таблицы па новом рабочем листе (рис. 5), в правой части которого расположена панель Pivot Table Field List (Список полей сводной таблицы). Обратите внимание, что в верхней части этой панели перечислены названия всех полей созданной нами таблицы реестра рисков.

Рис. 5. Рабочий лист, на котором расположен макет сводной таблицы и панель Pivot Table Field List (Список полей сводной таблицы)

Названия полей, перечисленные на панели Pivot Table Field List, представляют собой названия заголовков столбцов, взятые из нашей таблицы. Области макета сводной таблицы предназначены для различного отображения данных. Их можно представлять как некую трехаспектную палитру. Допустим, нам требуется узнать количество рисков по каждой категории. Например, сколько внешних рисков у нашего проекта? Начните с перетаскивания поля Risk Category (Категория риска), как показано на рис. 6, в область Drop Row Fields Here (Перетащите сюда поля строк). (Местоположение этой области показано на рис. 5).

Рис. 6. Результат перетаскивания поля Risk Category (Категория риска) в область Drop Row Fields Here (Перетащите сюда поля строк)

Как видите, название поля Risk Category (Категория риска) появилось в области Row Labels (Названия строк), которая расположена в нижней части панели Pivot Table Field List (Список полей сводной таблицы). Теперь перетащите поле Risk Name (Название риска) в область макета сводной таблицы Drop Data Items Here (Перетащите сюда элементы данных), как показано на рис. 7.

Рис. 7. Результат перетаскивания поля Risk Name (Название риска) в область Drop Data /terns Here (Перетащите сюда элементы данных)

Обратите внимание, что в нижней части созданной нами сводной таблицы программа Excel автоматически добавила строку с заголовком Grand Total (Общий итог), в которой отображено общее количество названий рисков по отдельным категориям. Теперь нетрудно заметить, что, например, категория «Связанные с решением кадровых вопросов» (поле Organizational) содержит два риска, категория «Технические» (поле Technical) — четыре и т.д. В последней строке — Grand Total (Общий итог) — указано общее количество рисков (11) по всем категориям.

Как вы, должно быть, заметили, в области Values (Значения), расположенной в нижней части панели Pivot Table Field List (Список полей сводной таблицы), появился элемент Count of Risk Name (Количество по полю Risk Name), т.е. суммарное количество рисков (см. рис. 7). Существует множество способов отображения, представления и подсчета данных в сводных таблицах Excel. Если хотите увидеть результаты и подсчеты, которые сделает для вас Excel, поэкспериментируйте с перемещением полей из списка панели Pivot Table Field List (Список полей сводной таблицы) в разные области макета сводной таблицы.

excelexpert.ru

Как я использую сводные таблицы (pivot tables) – Офисное пространство

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

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

И я решила, что надо это сделать хорошо и правильно один раз, и потом весь год просто обновлять (3 минуты раз в 2 недели) и пользоваться плодами этой работы.

Для начала я создала из своих проектов что-то вроде базы, постаравшись предусмотреть все нужные мне параметры:

Чтобы было легче работать, нажимаем ctrl+T, и ячейки превращаются в таблицу.

Заполняется довольно легко, повторяющиеся поля просто копирую. После этого ставлю курсор на одну из ячеек таблицы, иду во вкладку Вставка и вставляю Сводную таблицу (она же Pivot table - пивот или пайвот).

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

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

Теперь мне нужно узнать, сколько и на какую аудиторию я трачу в каждом месяце. Перетаскиваю несколько полей.

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

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

Итак, я создаю Сводную таблицу-заготовку, и ставлю курсор на какую-нибудь из её ячеек. Появляется вкладка с настройками таблицы.

Вкладка Параметры, группа команд Вычисления, в выпадающем списке – Поля, элементы и наборы.

В очередном выпадающем списке выбираем Вычисляемое поле.

В появившемся окне делаем нужные вычисления

Теперь нажимаем Ок, переназываем столбец именяем формат столбца на процентный.

Нужен график? Кликните по вкладке Параметры Сводной таблицы и выберите график. Также, буквально одним кликом убираются заголовки и список полей.

Уже несколько месяцев, как я наслаждаюсь результатами этого подхода.

Мне нравится. )

blogs.technet.microsoft.com

Создание первой сводной таблицы

Исходные данные

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

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

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

Советую также преобразовывать исходный диапазон данных в таблицу (Главная - Форматировать как таблицу). Тогда при добавлении или удалении строк и столбцов не придется менять ссылку на этот диапазон в сводном отчете.

Самостоятельное создание сводной таблицы с помощью конструктора

Для тех, у кого нет Excel 2013, или же если нужной сводной таблицы нет в предложенных, можно создать ее с нуля с помощью конструктора. Для этого:

Выберите любую ячейку исходной таблицы.
Перейдите на вкладку ленты Вставка [Insert] и найдите группу Таблицы [Table].
Выберите команду Сводная таблица [PivotTable].
В диалоговом окне Создание сводной таблицы [Create PivotTable] убедитесь в правильности диапазона данных, на основе которого будет строиться отчет. Если диапазон некорректный, его нужно поменять в поле Таблица и диапазон [Table/Range].

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

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

Советую размещать каждый отчет сводной таблицы на отдельном листе. Это поможет избежать ошибок в случае если будут добавлять данные.

После формирования сводной таблицы в выбранном месте появиться область с ее названием. По умолчанию отчет называется СводнаяТаблица1 [PivotTable 1]. Для начала работы необходимо щелкнуть левой клавшей мыши по данной области. В результате в правой части листа откроется макет сводной таблицы.

Макет сводной таблицы состоит из списка полей сводной таблицы, в котором перечислены все заголовки исходной таблицы и четырех областей: ФИЛЬТРЫ, КОЛОННЫ, СТРОКИ и ЗНАЧЕНИЯ.

В зависимости от того в какую область мы перенесем то или иное поле будет зависеть вид будущей сводной таблицы.

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

Данную операцию можно сделать еще 2 способами:

отметить флажок напротив поля Группа;
щелкнуть по полу Группа правой кнопкой мыши и выбрать Добавить в названия строк [Add to Row Labels].

После добавления поля вы увидите список всех групп, которые есть в исходной таблице:

Теперь осталось добавить сумму по полю Рыночная стоимость. Для этого перенесем поле Рыночная стоимость в область значений. Желаемая таблица получена.

Скачать

micro-solution.ru

Общие сведения о сводных таблицах

Хитрости » 28 Июль 2013 Дмитрий 31592 просмотров

Скачать файл с исходными данными, используемый в видеоуроке:

БД.xlsx (30,9 KiB, 1 007 скачиваний)

Несмотря на то, что первая возможность создания сводных таблица появилась еще в Excel 5.0(аж в 1993 году), даже сейчас лишь немногие из пользователей Excel используют сводные таблицы для решения задач. Кто-то считает, что сводные таблицы это нечто слишком сложное, кто-то про них даже не слышал. В данном разделе (Сводные таблицы) я постараюсь убедить читателей в том, что это не так. На самом деле ничего сложного в сводных таблицах нет. А вот польза при анализе информации просто неоценима.

Для чего же нужны сводные? В Excel работу можно разделить на две категории: анализ(вычисление) и форматирование данных. Под вычислениями и анализом я понимаю получение неких показателей на основании имеющихся данных. А форматирование - не закраска ячеек цветом, а вид представления таблиц данных. Все это можно сделать и формулами. Предположим, есть исходные ежедневные данные по продажам всех филиалов за полгода. Из этих данных необходимо построить отчет в разрезе каждого филиала и для филиала в разрезе месяца. Плюс сводные отчеты по каждому филиалу за все полгода. А теперь представим как это будет выглядеть формулами:-сначала надо получить список месяцев;-затем список уникальных наименований филиалов;-далее создать листы с рыбой таблиц, в которые надо будет собрать данные из исходной таблицы при помощи СУММЕСЛИ, СУММПРОИЗВ и им подобным.При должном опыте можно уложиться минут в 15-20. С использованием сводных это можно сделать за две минуты.

Сводная таблица (Pivot Table) – инструмент Excel, используемый для создания уникального представления данных и последующего анализа. Сводная таблица может быть построена на основе правильно сформированной исходной таблицы данных:

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

Данные сводных таблиц можно группировать, сортировать, фильтровать и менять местами с целью получения различных аналитических отчетов. Что немаловажно - сводные таблицы никоим образом не меняет исходные данные, на основе которых построена сводная. Можно как угодно менять и данные местами в сводной и исключать из отчета - исходные данные останутся такими, какими и были.Но если изменить исходные данные, то изменение данных не будет автоматически отражено внутри сводных таблиц - для этого надо будет принудительно обновить отчет сводной таблицы:

Выделить любую ячейку сводной таблицы→Правая кнопка мыши→Обновить(Refresh) или вкладка Данные(Data) →Обновить все(Refresh all) →Обновить(Refresh).

СОЗДАНИЕ СВОДНОЙ ТАБЛИЦЫ

Выделить любую ячейку исходной таблицы
Вкладка Вставка(Insert)→группа Таблица(Table)→Сводная таблица(PivotTable)
В диалоговом окне Создание сводной таблицы(Create PivotTable) проверить правильность выделения диапазона данных (или установить новый источник данных), определить место размещения Сводной таблицы:
- На новый лист (New Worksheet)
- На существующий лист (Existing Worksheet)
нажать OK

СВОДНАЯ ТАБЛИЦА СОСТОИТ ИЗ ЧЕТЫРЕХ ОБЛАСТЕЙ:Область данных – основная область сводной таблицы, в которой производятся расчеты. Содержит основные итоговые данные по числовым полям. В область данных можно поместить одно и тоже поле, но с разными вычислениями (например одно Сумма по полю, другое Количество по полю).Основные вычислительные функции области данных:

 Сумма (Sum) Количество (Count) Среднее (Average) Максимум (Max) Минимум (Min) Произведение (Product)

Область строк – состоит из заголовков в левой части сводной таблицы и отображает уникальные значения вставленного поля.Область столбцов – состоит из заголовков столбцов сводной таблицы.Область страниц и фильтров – необязательная область. Расположена в ве

www.excel-vba.ru

Создание отчетов при помощи сводных таблиц

Видео

Лирическое вступление или мотивация

Представьте себя в роли руководителя отдела продаж. У Вашей компании есть два склада, с которых вы отгружаете заказчикам, допустим, овощи-фрукты. Для учета проданного в Excel заполняется вот такая таблица:

В ней каждая отдельная строка содержит полную информацию об одной отгрузке (сделке, партии):

кто из наших менеджеров заключил сделку
с каким из заказчиков
какого именно товара и на какую сумму продано
с какого из наших складов была отгрузка
когда (месяц и день месяца)

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

Сколько и каких товаров продали в каждом месяце? Какова сезонность продаж?
Кто из менеджеров сколько заказов заключил и на какую сумму? Кому из менеджеров сколько премиальных полагается?
Кто входит в пятерку наших самых крупных заказчиков?

... и т.д.

Ответы на все вышеперечисленные и многие аналогичные вопросы можно получить легче, чем Вы думаете. Нам потребуется один из самых ошеломляющих инструментов Microsof Excel - сводные таблицы.

Поехали...

Если у вас Excel 2003 или старше

Ставим активную ячейку в таблицу с данными (в любое место списка) и жмем в меню Данные - Сводная таблица (Data - PivotTable and PivotChartReport). Запускается трехшаговый Мастер сводных таблиц (Pivot Table Wizard). Пройдем по его шагам с помощью кнопок Далее (Next) и Назад (Back) и в конце получим желаемое.

Шаг 1. Откуда данные и что надо на выходе?

На этом шаге необходимо выбрать откуда будут взяты данные для сводной таблицы. В нашем с Вами случае думать нечего - "в списке или базе данных Microsoft Excel". Но. В принципе, данные можно загружать из внешнего источника (например, корпоративной базы данных на SQL или Oracle). Причем Excel "понимает" практически все существующие типы баз данных, поэтому с совместимостью больших проблем скорее всего не будет. Вариант В нескольких диапазонах консолидации (Multiple consolidation ranges) применяется, когда список, по которому строится сводная таблица, разбит на несколько подтаблиц, и их надо сначала объединить (консолидировать) в одно целое. Четвертый вариант "в другой сводной таблице..." нужен только для того, чтобы строить несколько различных отчетов по одному списку и не загружать при этом список в оперативную память каждый раз.

Вид отчета - на Ваш вкус - только таблица или таблица сразу с диаграммой.

Шаг 2. Выделите исходные данные, если нужно

На втором шаге необходимо выделить диапазон с данными, но, скорее всего, даже этой простой операции делать не придется - как правило Excel делает это сам.

Шаг 3. Куда поместить сводную таблицу?

На третьем последнем шаге нужно только выбрать местоположение для будущей сводной таблицы. Лучше для этого выбирать отдельный лист - тогда нет риска что сводная таблица "перехлестнется" с исходным списком и мы получим кучу циклических ссылок. Жмем кнопку Готово (Finish) и переходим к самому интересному - этапу конструирования нашего отчета.

Работа с макетом

То, что Вы увидите далее, называется макетом (layout) сводной таблицы. Работать с ним несложно - надо перетаскивать мышью названия столбцов (полей) из окна Списка полей сводной таблицы (Pivot Table Field List) в области строк (Rows), столбцов (Columns), страниц (Pages) и данных (Data Items) макета. Единственный нюанс - делайте это поточнее, не промахнитесь! В процессе перетаскивания сводная таблица у Вас на глазах начнет менять вид, отображая те данные, которые Вам необходимы. Перебросив все пять нужных нам полей из списка, Вы должны получить практически готовый отчет.

Останется его только достойно отформатировать:

Если у вас Excel 2007 или новее

В последних версиях Microsoft Excel 2007-2010 процедура построения сводной таблицы заметно упростилась. Поставьте активную ячейку в таблицу с исходными данными и нажмите кнопку Сводная таблица (Pivot Table) на вкладке Вставка (Insert). Вместо 3-х шагового Мастера из прошлых версий отобразится одно компактное окно с теми же настройками:

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

Названия строк (Row labels)
Названия столбцов (Column labels)
Значения (Values) - раньше это была область элементов данных - тут происходят вычисления.
Фильтр отчета (Report Filter) - раньше она называлась Страницы (Pages), смысл тот же.

Перетаскивать поля в эти области можно в любой последовательности, риск промахнуться (в отличие от прошлых версий) - минимален.

P.S.

Единственный относительный недостаток сводных таблиц - отсутствие автоматического обновления (пересчета) при изменении данных в исходном списке. Для выполнения такого пересчета необходимо щелкнуть по сводной таблице правой кнопкой мыши и выбрать в контекстном меню команду Обновить (Refresh).

Ссылки по теме

www.planetaexcel.ru

Агрегатные функции в Power Pivot

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .

Агрегаты — это способ группирования данных, свертывания данных и получения итоговых данных. В начале работы с необработанными данными из таблиц или других источников данных эти данные часто бывают неструктурированными, то есть представляют собой множество подробных данных, никак не упорядоченных и не сгруппированных. Такое отсутствие сводок или структуры может затруднить обнаружение закономерностей в данных. Таким образом, важную часть моделирования составляет определение агрегатов, которые упрощают и обобщают данные, выявляя закономерности, позволяющие решить поставленную бизнес-задачу.

Самые распространенные агрегатные функции, например тех, кто использует Среднее, СЧЕТЧИК, DISTINCTCOUNT, MAX, MINили Сумма могут создаваться в мер автоматически с помощью функции "Автосумма". Другие типы агрегатные функции, такие как AVERAGEX, COUNTX, COUNTROWSили SUMXвозвращают таблицу и требуют формулы, созданные с помощью Выражений анализа данных (DAX).

Основные сведения об агрегатах в Power Pivot

Выбор групп для агрегата

При агрегатной обработке данных они группируются по таким атрибутам, как продукт, цена, регион или дата, а затем определяется формула, работающая для всех данных в группе. Например, если создаются итоговые показатели за год, то это агрегат. Если создается соотношение этого года с предыдущим годом и данные представляются в виде процентов, то это другой тип агрегата.

Метод группировки данных определяется поставленным бизнес-вопросом. Например, агрегаты могут ответить на следующие вопросы.

Счетчики Сколько транзакций было выполнено за месяц?

Средние значения Какие показатели средних продаж в этом месяце у каждого менеджера по продажам?

Минимальные и максимальные значения Какие районы сбыта были в горячей пятерке по количеству проданного товара?

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

Если данные не содержат значений, которые можно использовать для группировки и категории продукта или имя географического региона, где находится хранилище, например, может потребоваться представить групп данных путем добавления категории. Когда вы создаете групп в Excel, необходимо вручную введите или выберите групп, в которых вы хотите использовать один из столбцов на листе. Тем не менее в реляционной системе иерархии, например категорий продуктов, часто хранятся в таблицу, отличную фактов или Таблица значений. Обычно таблице «категории» будет связана с данными фактов по какой-либо ключ. Например предположим, что вы найдете, что данные содержат коды продуктов, но не имена продуктов или их категории. Чтобы добавить категорию плоской лист Excel, бы скопировать в столбец, содержащий имена категорий. Power Pivot можно импортировать модель данных в таблице категории продукта, создайте связь между таблицей с числовых данных и список категорий продуктов и затем с помощью категорий, чтобы сгруппировать данные. Дополнительные сведения в статье Создание связи между таблицами.

Выбор функции для агрегата

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

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

Фильтруемый подсчет Сколько транзакций было в течение месяца, исключая период профилактического обслуживания в конце месяца?

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

Сгруппированные минимальные и максимальные значения Какие районы сбыта были ведущими для каждой категории продукта или для каждого стимулирования сбыта?

Добавление агрегатов к формулам и сводным таблицам

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

В вычисляемом столбце можно создавать агрегаты, учитывающие контекст текущей строки для извлечения связанных строк из другой таблицы с последующим суммированием, подсчетом или вычислением среднего значения этих значений в связанных строках.
В измеренияможно создать динамическое агрегаты, которые могут использовать фильтры, определенные в формуле и фильтры, устанавливаемые макет сводной таблицы, а также подборку срезы, заголовки столбцов и заголовков строк. Меры с помощью стандартных агрегатов могут создаваться в Power Pivot с помощью функции "Автосумма" или путем создания формулы. Также можно создавать Неявные меры с помощью стандартных агрегатов в сводной таблице в Excel.

Добавление группирований в сводную таблицу

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

При добавлении в сводную таблицу категорий, данные которых не связаны с данными фактов, могут возникнуть ошибки или непредвиденные результаты. Обычно Power Pivot пытается устранить проблему, автоматически обнаруживая и предлагая связи. Дополнительные сведения см. в статье Работа со связями в сводных таблицах.

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

Работа с группированиями в формуле

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

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

Дополнительные сведения о создании формул с подстановками см. в статье Подстановка в формулах PowerPivot.

Использование фильтров в агрегатах

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

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

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

Дополнительные сведения см. в статье Фильтрация данных в формулах.

Сравнение агрегатных функций Excel с агрегатными функциями DAX

В следующей таблице приводятся некоторые стандартные агрегатные функции, доступные в Excel, и указываются ссылки на реализацию этих функций в Power Pivot. DAX-версия этих функций во многом похожа на Excel-версию с незначительными различиями в синтаксисе и обработке некоторых типов данных.

Стандартные агрегатные функции

Функция	Использование
AVERAGE	Возвращает среднее арифметическое всех чисел из столбца.
AVERAGEA	Функция возвращает среднее (арифметическое) всех значений в столбце. Обрабатывает текстовые и нечисловые значения.
COUNT	Функция подсчитывает количество числовых значений в столбце.
COUNTA	Функция подсчитывает количество непустых значений в столбце.
MAX	Возвращает наибольшее числовое значение из столбца.
MAXX	Функция возвращает наибольшее значение из набора выражений, вычисленных в таблице.
MIN	Возвращает наименьшее числовое значение в столбце.
MINX	Функция возвращает наименьшее значение из набора выражений, вычисленных в таблице.
SUM	Функция добавляет все числа в столбец.

Агрегатные функции DAX

В DAX включены агрегатные функции, позволяющие указать таблицу, в которой следует выполнить статистическую обработку. Таким образом, эти функции вместо простого сложения значений в столбце или определения среднего позволяют создавать выражение, которое динамически определяет данные для статистической обработки.

В следующей таблице перечислены агрегатные функции, доступные в DAX.

Функция	Использование
AVERAGEX	Функция определяет среднее арифметическое для набора выражений, вычисленных в таблице.
COUNTAX	Функция подсчитывает набор выражений, вычисленных в таблице.
COUNTBLANK	Функция подсчитывает количество пустых значений в столбце.
COUNTX	Функция подсчитывает общее количество строк в таблице.
COUNTROWS	Функция подсчитывает количество строк, возвращенных вложенной табличной функцией, такой как функция фильтра.
SUMX	Функция возвращает сумму набора выражений, вычисленных в таблице.

Различия между агрегатными функциями DAX и Excel

Имена этих функций совпадают с аналогичными функциями Excel, однако DAX-функции используют подсистему аналитики в памяти Power Pivot. Их функциональность также была изменена для работы с таблицами и столбцами. Формулы DAX нельзя использовать в книге Excel (и наоборот). Они могут применяться только в окне Power Pivot и в сводных таблицах, основанных на данных Power Pivot. Кроме того, хотя функции и имеют одинаковые имена, их работа может немного различаться. Дополнительные сведения см. в статьях справочника по отдельным функциям.

Способ вычисления столбцов в статистическом выражении также отличается от способа обработки статистических выражений в Excel. Проиллюстрировать это поможет пример.

Предположим, требуется получить сумму значений в столбце Amount таблицы Sales, для чего создается следующая формула:

=SUM('Sales'[Amount])

В самом простом случае функция возвращает значения из одного неотфильтрованного столбца, и результат будет таким же, как в приложении Excel, в котором всегда просто суммируются значения в столбце Amount. Тем не менее в Power Pivot формула интерпретируется как "Получить значение в столбце Amount для каждой строки таблицы Sales и затем сложить эти отдельные значения". Power Pivot вычисляет каждую строку, для которой выполняется агрегирование, и вычисляет единичное скалярное значение для каждой строки, а затем агрегирует эти значения. Поэтому результат формулы может быть разным, если к таблице применялись фильтры или если значения вычислялись на основе других агрегатов, где могли использоваться фильтры. Дополнительные сведения см. в статье Контекст в формулах DAX.

Функции логики операций со временем DAX

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

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

Функция	Использование
CLOSINGBALANCEMONTH CLOSINGBALANCEQUARTER CLOSINGBALANCEYEAR	Функция вычисляет значение на конечную дату календаря данного периода.
OPENINGBALANCEMONTH OPENINGBALANCEQUARTER OPENINGBALANCEYEAR	Функция вычисляет значение на конечную дату календаря периода, предшествующего данному.
TOTALMTD TOTALYTD TOTALQTD	Функция вычисляет значение для интервала, начинающегося в первый день периода и заканчивающегося последней датой в указанном столбце дат.

Другие функции в разделе «Логика операций со временем» (Функции логики операций со временем) — это функции, которые могут использоваться для извлечения дат или пользовательских диапазонов дат для использования в агрегате. Например, с помощью функции DATESINPERIOD можно получить диапазон дат и использовать этот набор дат в качестве аргумента другой функции для вычисления пользовательского агрегата только по этим датам.

support.office.com

Вычисляемые столбцы в Power Pivot

Вычисляемый столбец дает возможность добавления новых данных в таблицу в Power Pivot модель данных. Вместо вставки или импорта значений в столбце, можно создать формулу Выражений анализа данных (DAX), определяющее значений столбцов.

Если, например, вам нужно добавить значения прибыли продажи для каждой строки в таблице factSales . Путем добавления нового вычисляемого столбца, а также с помощью формулы = [SalesAmount]-[TotalCost] – [ReturnAmount]новые значения вычисляются путем вычитания значений из каждой строки в столбцах TotalCost и ReturnAmount от значения в каждой строке SalesAmount столбец. Выберите столбец Profit можно использовать в отчете сводной таблицы, сводной диаграммы или Power View — как и любой другой столбец.

На приведенном рисунке выполняет вычисляемый столбец в Power Pivot.

Примечание: Хотя вычисляемые столбцы и меры, которые отличают из-за каждый зависит от формулы, они различаются. Меры наиболее часто используются в области значений сводной таблицы или сводной диаграммы. Если вы хотите поместить вычисленные результаты в другую область сводной таблицы с помощью вычисляемых столбцов — например, столбца или строки в сводной таблице или на оси в сводной таблице. Дополнительные сведения о меры читайте в статье мер в Power Pivot.

Основные сведения о вычисляемых столбцах

Формулы в вычисляемых столбцах очень похожи на формулы, созданных в Microsoft Excel. Тем не менее невозможно создать различных формул для различных строк в таблице. Вместо этого формулу DAX автоматически применяется ко всему столбцу.

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

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

Пример

Можно поддерживать вычисляемого столбца с данными, которые можно добавить в существующую таблицу. Например можно объединить значения, выполнение добавления, извлечения подстрок или сравнить значения в другие поля. Чтобы добавить вычисляемый столбец, должен иметь по крайней мере одну таблицу в уже Power Pivot.

Рассмотрим следующую формулу:

=EOMONTH([StartDate],0])

При использовании примера данных Contoso эта формула извлекает месяц из столбца StartDate в таблице Promotion. Затем для каждой строки в таблице Promotion вычисляется значение на конец месяца. Второй параметр задает число месяцев до или после месяца в дате StartDate. В этом случае 0 означает тот же самый месяц. Например, если в столбце StartDate содержится значение 01.06.2001, то в вычисляемом столбце появится значение 30.06.2001.

Дополнительные сведения о компании Contoso образцов данных читайте в статье Получение образцов данных для DAX и модели данных.

Присвоение имен вычисляемым столбцам

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

Существуют некоторые ограничения на изменения в вычисляемых столбцах:

Имя каждого столбца должно быть уникальным в пределах таблицы.
Используйте имена, которые уже используется для мер в той же книги. Хотя это возможно меру и иметь то же имя вычисляемого столбца имена не являются уникальными ошибок при расчете упрощая доступ. Чтобы избежать случайно вызова меры, всегда используйте ссылку полное имя столбца применительно к столбцу.
При переименовании вычисляемого столбца, необходимо также обновить все формулы, которые основаны на существующий столбец. Если вы находитесь в режиме ручного обновления, обновление результатов формул происходит автоматически. Тем не менее эту операцию может потребоваться некоторое время.
Существуют некоторые символы, которые нельзя использовать в именах столбцов или в именах объектов в Power Pivot. Дополнительные сведения читайте в статье «Требования к именованию» «в Спецификация синтаксиса DAX для PowerPivot.

Переименование или изменение существующего вычисляемого столбца:

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

Изменение типа данных

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

Производительность вычисляемых столбцов

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

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

Формула содержит зависимостей на объект ссылки в формуле, например другие столбцы или выражения, которые оценки значений. Например, вычисляемый столбец, основанного на другой столбец, или вычисление, которое содержит выражение, ссылка на столбец — не может быть вычислено, пока не будет проверяться другого столбца. По умолчанию включено автоматическое обновление. Итак Имейте в виду зависимости формул может повлиять на производительность.

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

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

Задачи

Дополнительные сведения о работе с в вычисляемых столбцах отображается Создание вычисляемого столбца.