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

Сводные таблицы Excel — тема очень интересная и обширная.

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

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

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

Постановка задачи

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

Функция ВПР | Сводные таблицы

Допустим вы трудитесь в компании, которая является поставщиком овощей и фруктов в сетевые супермаркеты, которые находятся в нескольких крупных городах страны.

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

Таблица с данными о поставках

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

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

Любую компанию в первую очередь интересует прибыль и поэтому может потребоваться найти ответы на ряд вопросов, например:

1. Определить, в каком из городов за прошедшее время выручка была максимальной.
2. Какая торговая сеть позволила получить компании наибольшую выручку.
3. Определить категорию товаров и конкретный товар, принесшие наибольшую выручку.

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

Решение задачи формулами

На первый взгляд все эти задачи легко решаются несложными формулами и стандартными функциями.

Так, например, для определения города, принесшего максимальную выручку, нужно лишь сложить сумму всех поставок по каждому из городов. Сделать это можно с помощью функции СУММЕСЛИ .

Сводная таблица без ошибок. Как правильно отбирать данные для сводных таблиц?

То есть нам нужно будет создать отдельную таблицу, в которую с помощью функции СУММЕСЛИ свести данные по каждому из городов.

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

Источник: dzen.ru

Формирование итогов в списках по заданным условиям

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

Решение подобных задач осуществляется с помощью следующих функций EXCEL:

где диапазон — фрагмент списка, который оценивается на критерий;

условие — задастся в виде числового выражения с использованием логических операторов =, >, >= и т.п. или в виде текстовой константы;

диапазон_суммирования — задает интервал ячеек списка для формирования итогов.

В качестве примера рассчитаем количество компьютеров в прайс-листе, цена которых не превышает $700 (таблица 3). Для этого в ячейку А1 (рисунок 6) введем следующую формулу: =СЧЕТЕСЛИ(G6:G15,»<700″) или =СЧЕТЕСЛИ(Цена,»<700″), при выполнении которой получим число 6, что соответствует значениям прайс-листа (таблица 3).

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

БДСУММ, БДДИСП(), БСЧЕТ(), БСЧЕТА(), БИЗВЛЕЧЬ(), ДМАКС(), ДСРЗНАЧ(), ДСТАНДОТКЛ() и другие.

Практически каждая из функций баз данных, является аналогом обычной статистической функции, например, функция БДСУММ() — это аналог функции СУММ(), а функция ДСТАНДОТКЛ() — аналог СТАНДОТКЛОН(). Различие между функциями баз данных и их аналогами состоит в том, что первые оперируют только теми данными списка, которые удовлетворяют заданным условиям. Формат функций баз данных следующий:

Функция БД(Фрагмент БД, Поле, Критерий),

где Фрагмент БД — это адрес области списка или весь список, для которого выполняется расчет;

Поле — имя столбца или адрес столбца, по которому ведется расчет;

Критерий — адрес области критериев, где хранятся условия фильтрации списка (часть списка, или предварительно построенная область подобно Диапазону условий в команде Расширенный фильтр).

Например, чтобы рассчитать среднюю цену компьютеров с размером Cache-памяти в 512 Kb по значениям прайс-листа, приведенного в таблице 3, можно воспользоваться функцией ДСРЗНАЧ(), для чего предварительно построить область критериев с заголовком Cache и значением 512. Если область критериев размещается по адресу В1:В2 и ей присвоено имя блока Критерий, а фрагмент исходного списка занимает ячейки блока В5:G15 (таблица 3), которому присвоено имя Фрагмент, то аргументы функции ДСРЗНАЧ() должны быть заданы следующим образом: ДСРЗНАЧ(Фрагмент;G5;Критерий). [4] В результате расчета в месте расположения функции получим значение 872,8.

Для расчета максимальной (минимальной) цены компьютеров типа Pentium 200 ММХ по прайс-листу в таблице 3 можно использовать функцию ДМАКС(), предварительно разместив в ячейке C1 заголовок области критериев Тип, а в ячейке С2 — значение Pentium 200 ММХ. Если блоку ячеек С1:С2 присвоить имя Критерий2, а блоку ячеек исходного списка А5:G15 — имя БД, то можно обратиться к функции: ДМАКС(БД;G5;Критерий2). При этом получится значение 730.

Отметим, что в области критериев функций баз данных можно использовать несколько условий, связанных операциями И либо ИЛИ. Например, необходимо определить цену компьютера (таблица 11) с характеристиками, заданными в ячейках блока C1:E2, которому присвоено имя Критерий З (таблица 17):

Таблица 17 – Критерий отбора

Для решения этой задачи следует воспользоваться функцией БИЗВЛЕЧЬ(), которая возвращает значение ячейки в столбце, удовлетворяющее нескольким условиям, заданным в области критериев. Функция БИЗВЛЕЧЬ(БД;G5; Критерий3) для нашего примера дает следующий результат — 674.

Решение задач бизнес-анализа средствами аппарата сводных
таблиц MS Excel

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

Рассмотрим построение сводной таблицы на примере формирования Ведомости зарплаты за полугодие на основе Ведомостей зарплаты сотрудников за 1-й и 2-й кварталы. Прежде всего необходимо разместить исходные ведомости на одном рабочем листе EXCEL, как этo представлено в таблице 18. [5]

Таблица 18 – Подготовка списка для создания сводной ведомости за полугодие

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

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

Рисунок 35 – Образец подготовки списка для создания сводной ведомости
за полугодие в MS Excel

Макет сводной таблицы содержит 4 области (рисунок 7):

• Страница — обеспечивает фильтрацию данных сводной таблицы по значениям выбранных полей, может содержать О-N полей списка;

• Строка, Столбец — обеспечивают группирование строк и столбцов списка для вычисления итогов (О-N полей списка);

• Данные — обязательная область макета; содержит произвольное число полей, не включенных в другие области. Одно и то же поле списка может быть многократно размещено в области данных, если для него нужны разные виды итогов (сумма, среднее значение и т.д.).

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

На последнем шаге определяется место размещения результатов построения сводной таблицы. Конечный результат формирования Ведомости зарплаты за полугодие приведен в таблице 19.

Таблица 19 – Сводная ведомость зарплаты за полугодие

При подведении итогов можно объединять данные в группы в зависимости от типа данных разными способами:

• группировать выделенные элементы по выбранным категориям;

• автоматически группировать числовые элементы;

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

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

Для группировки данных следует выделить элементы, которые группируются, и воспользоваться кнопкой Группировка на панели инструментов Сводные таблицы. Например, для объединения сотрудников по отделам и получения дополнительных итогов для предыдущего примера (таблица 18) следует в построенной сводной таблице выделить требуемые фамилии, а затем выполнить группировку описанным способом.

С помощью мастера сводных таблиц можно также консолидировать данные из таблиц, расположенных на разных рабочих листах EXCEL. Непременным условием такой консолидации является единая структура таблиц. При этом каждая таблица должна содержать данные одного временного (или другого типа) диапазона. Например, ведомости зарплаты за каждый квартал 1998 года сформированы на разных рабочих листах EXCEL с именами 1кв-98, 2 кв-98, 3 кв-98, 4 кв-98. Для построения сводной ведомости за весь 1998 год не обязательно переносить их на один рабочий лист EXCEL, а можно воспользоваться средством консолидации мастера сводных таблиц, для чего выполнить следующие действия:

— в команде Сводная таблица установить флажок в нескольких диапазонах консолидации;

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

— сформировать макет итогового документа,[6] выбирая для расчетов требуемые функции.

Таблица 20 — Образец сводной ведомости зарплаты за 1998 год в MS Excel

Результат формирования Ведомости зарплаты за 1998 год методом консолидации диапазонов, расположенных на листах 1кв-98, 2 кв-98, 3 кв-98, 4 кв-98, приведен на рисунке 38.

По списку (Все) можно получить сведения по каждому кварталу.

Варианты заданий для самостоятельной работы

Понравилась статья? Добавь ее в закладку (CTRL+D) и не забудь поделиться с друзьями:

Источник: studopedia.ru

Анализ данных с помощью сводных таблиц и других средств бизнес-аналитики

Если вы хотите узнать о больших объемах данных ( будь то на вашем сайте или в другом месте), Excel дает вам мощные инструменты.

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

Советы, которые следует прочитать до начала работы

• Позвольте приложению Excel выбрать для вас сводную таблицу Чтобы быстро отбирать данные, которые вы хотите проанализировать в Excel, сначала нужно выбрать с помощью макета Excel для ваших данных.
• Анализ данных в нескольких таблицах Вы можете анализировать данные из двух таблиц в отчете Excel, даже если не используете Power Pivot. Функция модели данных встроена в Excel. Просто добавьте данные в несколько таблиц в Excel а затем создайте связи между ними на листе таблицы или Power View. Готово! Теперь у вас есть модель данных, которая добавляет больше энергии для анализа данных.
• Наносите данные непосредственно на интерактивную сводную диаграмму В Excel можно создать автономный (автономный) сводная диаграмма, который позволяет взаимодействовать с данными и фильтровать их прямо на диаграмме.
• Использовать все Power Pivot и Power View Если у вас установлен Office профессиональный плюс, попробуйте воспользоваться преимуществами этих мощных надстройок:

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

Создание и создание сводная диаграмма

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

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

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

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

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

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

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

Изменение формата вашей скайп-формы

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

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

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

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

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

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

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

В Excel новая функция «Быстрое изучение» позволяет детализтировать данные в кубе OLAP или иерархии на основе модели данных для анализа данных на разных уровнях. Эта функция позволяет переходить к нужным сведениям и действует как фильтр при их детализации. Соответствующая кнопка отображается при выборе элемента в поле.

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

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

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

Для сведения данных в сводных таблицах предназначены функции расчета суммы, количества и среднего значения. Функции сведения недоступны в сводных таблицах на базе источников данных OLAP.

Изменение и обновление данных сводной таблицы

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

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

Использование богатых возможностей Power Pivot

Если вы уже установили Office профессиональный плюс, запустите надстройку Power Pivot, которая поставляется вместе с Excel для проведения мощного анализа данных. После этого вы сможете создавать сложные модели данных в окне Power Pivot.

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

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

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

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

С помощью Power Pivot можно создавать ключевые показатели эффективности и добавлять их в сводные таблицы.

В этом учебнике показано, как вносить изменения в модель данных для улучшения отчетов Power View.

Анализ данных с помощью Power View

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

В конце учебника: импорт данных в Excel и Создание модели данных вы найдете полезные инструкции по оптимизации Power Pivot данных для Power View.

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

• Создание сводной таблицы для анализа данных на листе Принимайте более обоснованные бизнес-решения на основе данных в отчетах сводных таблиц, на которые можно взглянуть под разным углом. Excel поможет вам приступить к работе, порекомендовав модель, оптимальную для имеющихся данных.
• Создание сводной диаграммы Чтобы провести наглядную презентацию, создайте сводную диаграмму с интерактивными элементами фильтрации, позволяющими анализировать отдельные подмножества исходных данных. Приложение Excel даже может порекомендовать вам подходящую сводную диаграмму. Если вам необходима просто интерактивная диаграмма, создавать для этого сводную таблицу не требуется.
• Сортировка данных в сводной таблице Сортировка помогает упорядочивать большие объемы данных в сводных таблицах, чтобы упростить поиск объектов анализа. Данные можно отсортировать в алфавитном порядке, по убыванию или возрастанию.
• Фильтрация данных в сводной таблице Чтобы провести более подробный анализ определенного подмножества исходных данных сводной таблицы, их можно отфильтровать. Сделать это можно несколькими способами. Например, можно добавить один или несколько срезов, которые позволяют быстро и эффективно фильтровать информацию.
• Создание временной шкалы сводной таблицы для фильтрации дат Вместо создания фильтров для отображения данных в сводной таблице теперь можно воспользоваться временной шкалой. Ее можно добавить в сводную таблицу, а затем с ее помощью осуществлять фильтрацию по времени и переходить к различным периодам.
• Показ и скрытие подытогов в pivotTable Промежуточные итоги в сводных таблицах вычисляются автоматически и отображаются по умолчанию. Если итогов не видно, их можно добавить.
• Использование внешнего источника данных для использования в стеблиной После создания сводной таблицы может потребоваться изменить исходные данные для анализа (например, добавить или исключить те или иные сведения).

Источник: support.microsoft.com