Постановка задачи
Итак, сводные таблицы могут быть применены к абсолютно любым данным, но чаще всего Excel применяют для анализа различных финансовых показателей компаний или проектов, поэтому давайте рассмотрим следующий пример (скачать файл).
Допустим вы трудитесь в компании, которая является поставщиком овощей и фруктов в сетевые супермаркеты, которые находятся в нескольких крупных городах страны.
По каждой поставке в базе есть информация, которую можно выгрузить в приблизительно вот такую таблицу:
Здесь в каждой строке мы видим дату заказа, название сети супермаркетов, город, в который осуществлялась поставка, категорию товара, его наименование, цену на дату поставки, количество заказов и итоговую сумму сделки.
Информации может быть намного больше. Для упрощения задачи я взял лишь минимальный набор данных. Тем не менее данных очень много и таблица состоит из нескольких тысяч строк.
Любую компанию в первую очередь интересует прибыль и поэтому может потребоваться найти ответы на ряд вопросов, например:
- Определить, в каком из городов за прошедшее время выручка была максимальной.
- Какая торговая сеть позволила получить компании наибольшую выручку.
- Определить категорию товаров и конкретный товар, принесшие наибольшую выручку.
В таблице представлены данные за два года, поэтому приблизительно такие же вопросы могут возникнуть у руководства компании по отношению к какому-то конкретному временному интервалу, например, какой товар был наиболее востребованным прошлым летом, или в прошлом году, какова была динамика продаж товаров разных категорий в течении года (по месяцам, кварталам) или какой из заказчиков в прошлом месяце был для компании наиболее значимым. Также часто возникает необходимость определить лидеров продаж, например составить ТОП5 товаров, на которые был наибольший спрос в определенное время.
Синтаксис
ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(поле_данных; сводная_таблица; ; …)
Аргументы функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ описаны ниже.
Имя поля сводной таблицы, содержащего данные, которые требуется извлечь. Это должно быть в кавычках.
Ссылка на ячейку, диапазон ячеек или именованный диапазон ячеек в сводной таблице. Эти сведения используются для определения сводной таблицы, содержащей данные, которые необходимо извлечь.
field1, Item1, поле2, элем2.
От 1 до 126 пар имен полей и элементов, описывающих данные, которые необходимо извлечь. Они могут следовать друг за другом в произвольном порядке. Имена полей и имена для элементов, отличных от даты и чисел, должны быть заключены в кавычки.
Для сводных таблиц OLAPэлементы могут содержать исходное имя измерения, а также исходное имя элемента. Пара “поле-элемент” для сводной таблицы OLAP может выглядеть следующим образом:
Можно быстро ввести простую формулу ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ, введя = (знак равенства) в ячейке, в которой должно быть возвращено значение, и затем щелкнув ячейку в сводной таблице, содержащей необходимые данные.
Вы можете отключить эту функцию, выбрав любую ячейку в существующей сводной таблице, а затем перейдите на вкладку анализ сводной таблицы > Параметры > сводной таблицы > снимите флажок Создать GetPivotData .
Вычисляемые поля, элементы и пользовательские вычисления можно включить в расчеты в GETPIVOTDATA.
Если аргумент pivot_table является диапазоном, содержащим две или несколько сводных таблиц, данные будут извлекаться из какой-либо сводной таблицы, созданной последними.
Если аргументы поля и элемента описывают одну ячейку, возвращается значение этой ячейки независимо от того, является ли она строкой, числом, ошибкой или пустой ячейкой.
Если аргумент “элемент” содержит дату, необходимо представить это значение как порядковый номер или воспользоваться функцией ДАТА, чтобы это значение не изменилось при открытии листа в системе с другими языковыми настройками. Например, элемент, ссылающийся на дату 5 марта 1999 г., можно ввести двумя способами: 36 224 или ДАТА(1999;3;5). Время можно задать в виде десятичных значений или с помощью функции ВРЕМЯ.
Если аргумент pivot_table не является диапазоном, в котором найдена Сводная таблица, функция GETPIVOTDATA возвращает #REF!.
Если аргументы не описывают видимое поле или содержат фильтр отчета, в котором не отображаются отфильтрованные данные, функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ возвращает значение ошибки #ССЫЛКА!. значение ошибки.
Использование Мастера сводных таблиц
Сводная таблица может быть создана с помощью инструмента под названием “Мастер сводных таблиц”. Но предварительно нужно вынести значок Мастера на Панель быстрого доступа. Для этого выполняем следующую цепочку действий:
- Открываем меню Файл, кликаем по строке “Параметры”, далее – “Панель быстрого доступа”. Выбрав “Команды не на ленте” в предлагаемом перечне нам нужен пункт “Мастер сводных таблиц и диаграмм”. Отмечаем его курсором, нажимаем “Добавить >>” и завершаем настройки кликом по кнопке OK.
- В самом верхнем левом углу окна программы появится значок, нажав на который, запускаем Мастер сводных таблиц.
- В открывшемся окне необходимо выбрать источник данных, и на выбор может предлагаться до четырех опций. В нашем случае останавливаемся на первом варианте, т.е. создаем таблицу из списка или базы данных Excel. В нижней части окна выбираем пункт “сводная таблица” и нажимаем “Далее”.
- Появится следующее окно, где нужно указать координаты исходной таблицы, из которой будет сформирована сводная таблица. Если мы согласны с диапазоном, присвоенным программой автоматически, кликаем по кнопке “Далее”, либо сначала выделяем нужную область и затем уже двигаемся дальше.
- Аналогично ранее рассмотренному примеру выбираем место для размещения сводной таблицы и кликаем “Готово”. На выбор предлагаются две опции.
- на новом листе
- на существующем листе (нужно выбрать конкретный лист).
- Будет создана уже знакомая нам форма для конструирования сводной таблицы. Далее приступаем к ее настройке согласно нашим пожеланиям и задачам.
Создание сводной таблицы в Excel
Открываем исходные данные. Сводную таблицу можно строить по обычному диапазону, но правильнее будет преобразовать его в таблицу Excel. Это сразу решит вопрос с автоматическим захватом новых данных. Выделяем любую ячейку и переходим во вкладку Вставить. Слева на ленте находятся две кнопки: Сводная таблица и Рекомендуемые сводные таблицы.
Если Вы не знаете, каким образом организовать имеющиеся данные, то можно воспользоваться командой Рекомендуемые сводные таблицы. Эксель на основании ваших данных покажет миниатюры возможных макетов.
Кликаете на подходящий вариант и сводная таблица готова. Остается ее только довести до ума, так как вряд ли стандартная заготовка полностью совпадет с вашими желаниями. Если же нужно построить сводную таблицу с нуля, или у вас старая версия программы, то нажимаете кнопку Сводная таблица. Появится окно, где нужно указать исходный диапазон (если активировать любую ячейку Таблицы Excel, то он определится сам) и место расположения будущей сводной таблицы (по умолчанию будет выбран новый лист).
Обычно ничего менять здесь не нужно. После нажатия Ок будет создан новый лист Excel с пустым макетом сводной таблицы.
Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.
В верхней части панели находится перечень всех доступных полей, то есть столбцов в исходных данных. Если в макет нужно добавить новое поле, то можно поставить галку напротив – эксель сам определит, где должно быть размещено это поле. Однако угадывает далеко не всегда, поэтому лучше перетащить мышью в нужное место макета. Удаляют поля также: снимают флажок или перетаскивают назад.
Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.
Область значений – это центральная часть сводной таблицы со значениями, которые получаются путем агрегирования выбранным способом исходных данных.
В большинстве случае агрегация происходит путем Суммирования. Если все данные в выбранном поле имеют числовой формат, то Excel назначит суммирование по умолчанию. Если в исходных данных есть хотя бы одна текстовая или пустая ячейка, то вместо суммы будет подсчитываться Количество ячеек. В нашем примере каждая ячейка – это сумма всех соответствующих товаров в соответствующем регионе.
В ячейках сводной таблицы можно использовать и другие способы вычисления. Их около 20 видов (среднее, минимальное значение, доля и т.д.). Изменить способ расчета можно несколькими способами. Самый простой, это нажать правой кнопкой мыши по любой ячейке нужного поля в самой сводной таблице и выбрать другой способ агрегирования.
Область строк – названия строк, которые расположены в крайнем левом столбце. Это все уникальные значения выбранного поля (столбца). В области строк может быть несколько полей, тогда таблица получается многоуровневой. Здесь обычно размещают качественные переменные типа названий продуктов, месяцев, регионов и т.д.
Область столбцов – аналогично строкам показывает уникальные значения выбранного поля, только по столбцам. Названия столбцов – это также обычно качественный признак. Например, годы и месяцы, группы товаров.
Область фильтра – используется, как ясно из названия, для фильтрации. Например, в самом отчете показаны продукты по регионам. Нужно ограничить сводную таблицу какой-то отраслью, определенным периодом или менеджером. Тогда в область фильтров помещают поле фильтрации и там уже в раскрывающемся списке выбирают нужное значение.
С помощью добавления и удаления полей в указанные области вы за считанные секунды сможете настроить любой срез ваших данных, какой пожелаете.
Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».
В результате мы получаем настоящую сводную таблицу.
На ее построение потребовалось буквально 5-10 секунд.
Где применять
Надеемся, что вы разобрались как создать сводную таблицу в Эксель и как с ней работать. Теперь немного о том, для чего нужны сводные таблицы в Excel. В первую очередь их очень удобно использовать при расчете показателей ключевой эффективности, так называемых KPI. Во-вторых, они незаменимы, если необходимо составить какие-либо отчеты о персонале, например, в разрезе пола, возраста, образования и пр.
Расчет KPI
Современные CRM-системы позволяют выгрузить все необходимые отчеты в готовом виде. Но что делать тем, кто специализированный софт не использует? Остается возможность как в Экселе сделать сводную таблицу, так и посчитать необходимые показатели в ручном режиме. Второй способ кажется проще, но он не всегда удобен. Если исходные данные представлены в виде списка подобного вида, использовать объединенные реестры вполне уместно, так как это значительно облегчает последующую работу.
Мы уже посчитали объем продаж для каждого менеджера с помощью сводной базы.
Теперь эти данные используем для дальнейшего расчета. Сравним плановый показатель с фактическим и вычислим отклонение.
Тем менеджерам, которые превысили плановое значение (отклонение положительно), положена премия, рассчитываемая в процентах от выручки, превышающей плановое значение. Рассчитаем премию.
Отчет по персоналу
Практически все данные о персонале получаем из 1С. Но если такой софт в организации не используется или необходим отчет в другой форме, не остается ничего, кроме как делать сводные таблицы в Еxcel. Даже если массив данных составляется в ручном режиме, базы помогут представить их в более «красивом» виде. Имея сведения об образовании, стаже, окладе сотрудников в виде подобного списка, есть возможность, допустим, выяснить, сколько сотрудников каждого из отделов имеют образование определенного уровня.
С помощью подобной базы данных решают и задачи посложнее. Отобразим минимальный оклад сотрудников различных отделов по каждому уровню образования.
На основе таких отчетов удобно строить диаграммы для графического отображения информации.
СУММЕСЛИМН
Функция СУММЕСЛИМН позволяет суммировать значения по определенным условиям. Условий может быть несколько. В Excel также есть функция СУММЕСЛИ, которая позволяет суммировать по одному критерию. Призываю вас использовать более универсальную формулу.
— Диапазон суммирования — область листа Эксель, из которой мы суммируем данные- Диапазон условия 1 — Диапазон ячеек, которые мы проверяем на соответствие условию- Условие 1 — Условие, которое проверяется на соответствие в Диапазоне 1.Обратите внимание, что диапазонов условий и соответственно условий может быть столько, сколько вам нужно. Для примера выше мы хотим получит выручку, которую принес нам Петров в городе Москва
Формула имеет вид СУММЕСЛИМН(C2:C13;A2:A13;E2;B2:B13;F2), где C2:C13 — диапазон со значениями выручки, которые необходимо просуммировать; А2:А13 — диапазон с фамилиями, которые мы будем проверять; Е2 — ссылка на конкретную фамилию; B2:B13 — ссылка на диапазон с городами; F2 — ссылка на конкретный город
Для примера выше мы хотим получит выручку, которую принес нам Петров в городе Москва. Формула имеет вид СУММЕСЛИМН(C2:C13;A2:A13;E2;B2:B13;F2), где C2:C13 — диапазон со значениями выручки, которые необходимо просуммировать; А2:А13 — диапазон с фамилиями, которые мы будем проверять; Е2 — ссылка на конкретную фамилию; B2:B13 — ссылка на диапазон с городами; F2 — ссылка на конкретный город.
Более подробно о функциях СУММЕСЛИМН и СУММЕСЛИ рассказано в статье «СУММЕСЛИ и СУММЕСЛИМН в Excel».
Сортировка и фильтрация
Скорее всего вы уже обратили внимание на то, что в сводной таблице есть две ячейки с кнопками. По щелчку мыши на них появляется меню с возможностью фильтрации и сортировки данных
Эти инструменты относятся к заголовкам строк и, соответственно, столбцов
По щелчку мыши на них появляется меню с возможностью фильтрации и сортировки данных. Эти инструменты относятся к заголовкам строк и, соответственно, столбцов.
Если нужно сформировать отчет только по какой-то одной товарной категории (например, «Зелень»), то с помощью фильтра отключаем все ненужные и получаем результат:
То же самое касается и заказчиков. То есть мы можем сократить отчет только до нужной категории товаров, заказанных определенной торговой сетью.
Если кроме категории нужно отфильтровать данные еще и по конкретным товарам, то в меню в выпадающем списке указываем соответствующее поле, а затем делаем фильтрацию по нему.
При применении сортировки или фильтрации значок на кнопке изменяется. По нему можно однозначно определить, что данные в столбце или строке отфильтрованы или отсортированы.
Чтобы удалить фильтры достаточно выбрать соответствующий пункт в меню, однако в случае с вложенными полями удаление фильтра касается только выбранного в выпадающем списке. То есть если фильтрация была произведена по нескольким полям, то для ее удаления нужно будет сначала переключиться на соответствующее поле.
Кроме стандартных возможностей фильтрации мы можем настроить фильтр по произвольному полю. Для этого есть отдельная область, которая так и называется Фильтры.
Сейчас мы построили отчет, дающий полное представление об объемах заказов со стороны торговых сетей, но вот как дела обстоят по отдельным городам?
Перетаскиваем соответствующее поле в область Фильтр и над сводной таблицей появляется соответствующий выпадающий список.
Мы можем выбрать отдельный город, чтобы получить информацию только по нему.
Что же касается сортировки, то в выпадающем меню есть стандартные инструменты, позволяющие отсортировать заголовки строк или столбцов в алфавитном порядке.
Однако намного удобнее пользоваться контекстным меню. Например одной из первых задач у нас было определить, в каком из городов за прошедшее время выручка была максимальной. Мы получили результат в виде данных по всем городам, но чтобы быстро определить нужное значение необходимо отсортировать значения по возрастанию или убыванию. Вызываем контекстное меню на любой ячейке столбца и выбираем нужный вариант.
Аналогично можно отсортировать данные по любому полю или итогам. Просто вызываем контекстное меню на соответствующей ячейке и выбираем нужное направление сортировки.
Ну и затронув тему фильтрации нельзя обойти стороной так называемые срезы.
Применение умных таблиц
Давайте вернемся к исходной таблице с данными, встанем в любую ячейки таблицы и в панели вкладок перейдем на Главная -> Стили -> Форматировать как таблицу:
Среди стилей оформления выбираем любой понравившийся (внешний вид на функциональности не влияет), далее выбираем диапазон для таблицы (по умолчанию Excel автоматически его определяет) и нажимаем OK:
Полученная умная таблица обладает большим спектром удобных свойств, но в данном случае нам интересны 2:
- Умная таблица получает имя (в примере выше Таблица1, его можно легко поменять), которое можно использовать для определения диапазона в качестве источника для сводной таблицы;
- Умная таблица автоматически изменяет размер при добавлении или удалении новых строк или столбцов.
Таким образом мы можем использовать имя таблицы в качестве источника данных. Вернемся к нашим сводным таблицам и в Источнике данных поменяем диапазон на умную таблицу:
Теперь при добавлении данных в исходную таблицу не нужно будет каждый раз проверять диапазон на целостность данных и смотреть сколько строк или столбцов было в таблице изначально.
Давайте подведем небольшое резюме, что в итоге нужно делать, чтобы построить сводную таблицу.
В чем полезность сводных таблиц
Многие специалисты используют Microsoft Excel для вышеперечисленных целей, потому что программа сильно экономит время профессионала, забирая всю трудоемкую работу с данными на себя.
Еще отличительной чертой, из-за которой Excel стал популярным — создание достаточно нетривиальных средств анализа данных (Сводные таблицы входят в их число) не требует особых знаний по программированию.
Но перед тем, как начать пользоваться всеми «профитами» от Сводных таблиц Excel, нужно приложить немало усилий, чтобы хорошо освоить довольно сложную технику создания таблиц и работы с ними. Но в итоге, эффект от приобретения нового навыка, вот увидите, окупит потраченное время, энергию и силы.
В этой статье мы решили рассказать об основных технологиях создания Сводных таблиц в Excel и отметить некоторые особенности методов, которые могут Вам пригодиться в будущей работе.
Сначала предлагаем разобраться в том, что же такое Сводная таблица и «с чем ее едят».
Сводные таблицы — это перечень, содержащий выборочные данные из каких-либо источников (Базы Данных, таблицы Excel и другие), который нужен специалисту для дальнейшего анализа данных, изменения структуры таблицы и так далее.
Сводные таблицы позволяют менять визуальное представление данных, представляя их с новой точки зрения, не меняя сам источник.
Сводные расчеты в Microsoft Excel — Формулы
При составлении отчетов в созданной сводной таблице, можно использовать большое количество статистических функций и формул для того, чтобы проанализировать полученные значения и базовые входящие данные.
В таблицу можно добавить, к примеру, элемент для вычисления комиссии для платежа или любой другой вид расчетного элемента.
Все формулы в столбики и строки добавляются с помощью поля «Вставка».
С помощью данной вкладки можно создать график, рассчитать элементы по формуле и построить гистограмму зависимости.
Вкладка сводных расчетов
Еще интересный материал на тему:
- Практичные советы — Как объединить ячейки в Excel
- Округление в Excel — Пошаговая инструкция
Что такое сводная таблица?
Это инструмент для изучения и обобщения больших объемов данных, анализа связанных итогов и представления отчетов. Они помогут вам:
- представить большие объемы данных в удобной для пользователя форме.
- группировать информацию по категориям и подкатегориям.
- фильтровать, сортировать и условно форматировать различные сведения, чтобы вы могли сосредоточиться на самом актуальном.
- поменять строки и столбцы местами.
- рассчитать различные виды итогов.
- разворачивать и сворачивать уровни данных, чтобы узнать подробности.
- представить в Интернете сжатые и привлекательные таблицы или печатные отчеты.
Например, у вас множество записей в электронной таблице с цифрами продаж шоколада:
И каждый день сюда добавляются все новые сведения. Одним из возможных способов суммирования этого длинного списка чисел по одному или нескольким условиям является использование формул, как было продемонстрировано в руководствах по функциям СУММЕСЛИ и СУММЕСЛИМН.
Однако, когда вы хотите сравнить несколько показателей по каждому продавцу либо по отдельным товарам, использование сводных таблиц является гораздо более эффективным способом. Ведь при использовании функций вам придется писать много формул с достаточно сложными условиями. А здесь всего за несколько щелчков мыши вы можете получить гибкую и легко настраиваемую форму, которая суммирует ваши цифры как вам необходимо.
Вот посмотрите сами.
Этот скриншот демонстрирует лишь несколько из множества возможных вариантов анализа продаж.
Подготавливаем данные для построения сводной таблицы
Давайте разберемся, из какой исходной таблицы можно построить сводную таблицу в excel (спойлер: не каждая таблица годится).
Основные требования к таблице, из которой будет создаваться сводная таблица:
- Все столбцы в исходной таблице должны иметь уникальные (т.е. не повторяющиеся) заголовки.
- Все строки в пределах одного столбца должны иметь однородные данные. Например, если в столбце указан месяц в формате “янв”, то не должно быть данных в формате “Январь” — эксель сочтет их как два разных месяца. Также использование русских и похожих латинских букв (например С и C на глаз не отличишь, но одна из них в латинской раскладке, и эксель также посчитает их разными).
- Числовые данные, которым вы будете оперировать, должны быть именно числовыми. Если есть зеленый треугольник в верхнем левом углу ячейки — такое число является текстом, и должно быть преобразовано в число, иначе с ним нельзя делать вычисления.
- Исходная таблица не должна содержать промежуточных итогов.
- Нежелательно иметь пустые строки
- Под исходной таблицей нежелательно иметь какие-то другие данные, оставьте это пространство пустым, это облегчит дальнейшее обновление сводной таблицы.
Лучшим вариантом организации исходных данных является преобразование их в “умную” таблицу. Но это не является обязательным требованием, к тому же “умные” таблицы имеют некоторые ограничения и могут быть использованы не всегда. Но в целом их использование очень упрощает работу со сводными таблицами.
Перестановка полей сводной таблицы
Основное преимущество сводной таблицы заключается в возможности быстрой настройки за счет простой “перетасовки” полей строк и столбцов. Предположим, например, что в настоящий момент в строках у нас находятся категории расходов и доходов, а в столбцах — значения расходов и доходов, а мы решили, что все должно быть наоборот.
Нет проблем: на панели списка полей сводной таблицы переместите поле Значения из области СТОЛБЦЫ в область СТРОКИ, а поле Категории — в прямо противоположном направлении.
Вот и все! Программа сама переупорядочит итоговые значения в ячейках сводной таблицы. Теперь общие итоги по отделам будут находиться в строках, а по городам — в столбцах.