В этом разделе самоучителя дана пошаговая инструкция, как создать продвинутую сводную таблицу в современных версиях Excel (2007 и более новых). Для тех, кто работает в более ранних версиях Excel, рекомендуем статью: Как создать продвинутую сводную таблицу в Excel 2003?
В качестве исходных данных для построения сводной таблицы, мы используем таблицу данных о продажах компании в первом квартале 2016 года.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Invoice Ref | Amount | Sales Rep. | Region |
| 2 | 01/01/2016 | 2016-0001 | $819 | Barnes | North |
| 3 | 01/01/2016 | 2016-0002 | $456 | Brown | South |
| 4 | 01/01/2016 | 2016-0003 | $538 | Jones | South |
| 5 | 01/01/2016 | 2016-0004 | $1,009 | Barnes | North |
| 6 | 01/02/2016 | 2016-0005 | $486 | Jones | South |
| 7 | 01/02/2016 | 2016-0006 | $948 | Smith | North |
| 8 | 01/02/2016 | 2016-0007 | $740 | Barnes | North |
| 9 | 01/03/2016 | 2016-0008 | $543 | Smith | North |
| 10 | 01/03/2016 | 2016-0009 | $820 | Brown | South |
| 11 | ... | ... | ... | ... | ... |
В следующем примере мы создадим сводную таблицу, которая показывает итоги продаж помесячно за год с разбивкой по регионам и по продавцам. Процесс создания этой сводной таблицы описан ниже.
Как и требовалось, наша сводная таблица (смотрите картинку ниже) теперь показывает итоги продаж по месяцам с разбивкой по регионам и по продавцам.
Чтобы улучшить вид сводной таблицы, следует настроить форматирование. Например, если для значений в столбцах B – G настроить денежный формат, то прочесть сводную таблицу станет гораздо легче.
Фильтры в сводной таблице
Фильтры в сводной таблице позволяют отобразить информацию для одного значения или избирательно для нескольких значений из имеющихся полей данных. Например, в показанной выше сводной таблице мы сможем просматривать данные только для региона продаж North или только для региона South .
Чтобы отобразить данные только для региона продаж North , в панели Поля сводной таблицы (Pivot Table Field List) перетащите поле Region в область Фильтры (Report Filters).
Поле Region появится вверху сводной таблицы. Откройте выпадающий список в этом поле и выберите в нём регион North . Сводная таблица (как показано на картинке ниже) покажет значения только для региона North .
Вы можете быстро переключиться на просмотр данных только для региона South – для этого нужно в выпадающем списке в поле Region выбрать South .
Сводные таблицы - один из самых эффективных инструментов в MS Excel. С их помощью можно в считанные секунды преобразовать миллион строк данных в краткий отчет. Помимо быстрого подведения итогов, сводные таблицы позволяют буквально «на лету» изменять способ анализа путем перетаскивания полей из одной области отчета в другую.
Сводные таблицы - это также один из самых недооцененных инструментов эксель. Большинство пользователей не подозревает, какие возможности находятся в их руках. Представим, что сводные таблицы еще не придумали. Вы работаете в компании, которая продает свою продукцию различным клиентам. Для простоты в ассортименте только 4 позиции. Продукцию регулярно покупает пара десятков клиентов, которые находятся в разных регионах. Каждая сделка заносится в базу данных и представляет отдельную строку.
Ваш директор дает указание сделать краткий отчет о продажах всех товаров по регионам (областям). Решить задачу можно следующим образом.
Вначале создадим макет таблицы, то есть шапку, состоящую из уникальных значений товаров и регионов. Сделаем копию столбца с товарами и удалим дубликаты. Затем с помощью специальной вставки транспонируем столбец в строку. Аналогично поступаем с областями, только без транспонирования. Получим шапку отчета.
Данную табличку нужно заполнить, т.е. просуммировать выручку по соответствующим товарам и регионам. Это нетрудно сделать с помощью функции СУММЕСЛИМН. Также добавим итоги. Получится сводный отчет о продажах в разрезе область-продукция.
Вы справились с заданием и показываете отчет директору. Посмотрев на таблицу, он генерирует сразу несколько замечательных идей.
- Можно ли отчет сделать не по выручке, а по прибыли?
- Можно ли товары показать по строкам, а регионы по столбцам?
- Можно ли такие таблицы делать для каждого менеджера в отдельности?
Даже если вы опытный пользователь Excel, на создание новых отчетов потребуется немало времени. Это уже не говоря о возможных ошибках. Однако если вы владеете сводными таблицами, то ответите: да, мне нужно 5 минут, возможно меньше.
Вот как это делается. Открываем исходные данные. Сводную таблицу можно строить по обычному диапазону, но правильнее будет преобразовать его в таблицу Excel . Это сразу решит вопрос с автоматическим захватом новых данных. Выделяем любую ячейку и переходим во вкладку Вставить . Слева на ленте находятся две кнопки: и Рекомендуемые сводные таблицы.
Если Вы не знаете, каким образом организовать имеющиеся данные, то можно воспользоваться командой Рекомендуемые сводные таблицы . Эксель на основании ваших данных покажет миниатюры возможных макетов.
Кликаете на подходящий вариант и сводная таблица готова. Остается ее только довести до ума, т.к. вряд ли стандартная заготовка полностью совпадет с вашими желаниями. Если же нужно построить сводную таблицу с нуля, или у вас старая версия программы, то нажимаете кнопку . Появится окно, где нужно указать исходный диапазон (если активировать любую ячейку Таблицы Excel, то он определится сам) и место расположения будущей сводной таблицы (по умолчанию будет выбран новый лист).
Очень часто ничего менять здесь не нужно. После нажатия Ок будет создан новый лист Excel с пустым макетом сводной таблицы.
Перед тем, как перейти к настройкам, познакомимся с интерфейсом и основными понятиями. Макет таблицы настраивается в панели Поля сводной таблицы , которая находится в правой части листа.
В верхней части панели находится перечень всех доступных полей, то есть столбцов в исходных данных. Если в макет нужно добавить новое поле, то можно поставить галку напротив - эксель сам определит, где должно быть размещено это поле. Однако угадывает далеко не всегда, поэтому лучше перетащить мышью в нужное место макета. Удаляют поля также: снимают флажок или перетаскивают назад.
Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.
Область значений
- это центральная часть сводной таблицы со значениями, которые получаются путем агрегирования выбранным способом исходных данных.
В большинстве случае агрегация происходит путем Суммирования
. Если все данные в выбранном поле имеют числовой формат, то Excel назначит суммирование по умолчанию. Если в исходных данных есть хотя бы одна текстовая или пустая ячейка, то вместо суммы будет подсчитываться Количество
ячеек. В нашем примере каждая ячейка - это сумма всех соответствующих товаров в соответствующем регионе.
В ячейках сводной таблицы можно использовать и другие способы вычисления. Их около 20 видов (среднее, минимальное значение, доля и т.д.). Изменить способ расчета можно несколькими способами. Самый простой, это нажать правой кнопкой мыши по любой ячейке нужного поля в самой сводной таблице и выбрать другой способ агрегирования.
Область строк - названия строк, которые расположены в крайнем левом столбце. Это все уникальные значения выбранного поля (столбца). В области строк может быть несколько полей, тогда таблица получается многоуровневой. Здесь обычно размещают качественные переменные типа названий продуктов, месяцев, регионов и т.д.
Область столбцов - аналогично строкам показывает уникальные значения выбранного поля, только по столбцам. Названия столбцов - это также обычно качественный признак. Например, годы и месяцы, группы товаров.
Область фильтра - используется, как ясно из названия, для фильтрации. Например, в самом отчете показаны продукты по регионам. Нужно ограничить сводную таблицу какой-то отраслью, определенным периодом или менеджером. Тогда в область фильтров помещают поле фильтрации и там уже в раскрывающемся списке выбирают нужное значение.
С помощью добавления и удаления полей в указанные области вы за считанные секунды сможете настроить любой срез ваших данных, какой пожелаете.
Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы - «Товар».
В результате мы получаем настоящую сводную таблицу.
На ее построение потребовалось буквально 5-10 секунд. Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания директора легко воплощаются в реальность.
Заменим выручку на прибыль.
Товары и области меняются местами также перетягиванием мыши.
Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле "Менеджер" в область фильтров.
На все про все ушло несколько секунд. Вот, как на самом деле легко работать со сводными таблицами. Конечно, не все задачи столь тривиальные. Бывают и такие, что необходимо использовать более замысловатый способ агрегации, добавлять вычисляемые поля, условное форматирование и т.д. Но это уже более продвинутое использование сводных таблиц.
Исходные данные
Для успешной работы со сводными таблицами исходные данные должны отвечать ряду требований. Обязательным условием является наличие названий над каждым полем (столбцом), по которым эти поля будут идентифицироваться. Теперь полезные советы.
1. Лучший формат для данных - это Таблица Excel. Она хороша тем, что у каждого поля есть наименование и при добавлении новых строк они автоматически включаются в сводную таблицу.
2. Избегайте повторения групп в виде столбцов. Например, все даты должны находиться в одном поле, а не разбиты, например, по месяцам в отдельных столбцах.
3. Уберите пропуски и пустые ячейки иначе данная строка может выпасть из анализа.
4. Применяйте правильное форматирование к полям. Числа должны быть в числовом формате, даты должны быть датой. Иначе возникнут проблемы при группировке и математической обработке. Но здесь эксель вам поможет, т.к. сам неплохо определяет формат данных.
В целом требований немного, но их следует знать.
Если внести изменения в источник (например, добавить новые строки), сводная таблица не изменится, пока вы ее не обновите через правую кнопку мыши
или
через команду во вкладке Данные - Обновить все
.
Так сделано специально из-за того, что сводная таблица занимает много места в оперативной памяти. Поэтому, чтобы расходовать ресурсы компьютера более экономно, работа идет не напрямую с источником, а с кешем, где находится моментальный снимок исходных данных.
Используя сводные таблицы даже на таком базовом уровне, вы сможете в разы увеличить скорость и качество обработки больших массивов данных. Если что-то осталось непонятным, напишите в комментариях.
Ниже находится видеоурок о том, как в Excel создать простую сводную таблицу.
P.S. Углубленные знания о сводных таблицах можно получить в
Чтобы лучше понимать как создавать сводные таблицы в программе, нам нужно научиться делать их в Excel.
Сводные таблицы в Excel
Форма сводной таблицы содержит три области для перетаскивания в них полей: для столбцов, для строк и для данных.
Из диалога Список полей сводной таблицы перетащите поле Фамилия в область Перетащите сюда поля строк . Форма сводной таблицы немного изменилась.
Excel выбрал все уникальные значения столбца Фамилия и расположил их в строках нашего будущего отчета.
Теперь перетащите поле Сумма в область данных .
Excel посчитал сумму продаж для каждого продавца. Главное быстро и без ошибок. Отчет можно считать готовым.
Но для большего понимания возможностей сводной таблицы укажем еще область столбцов. Эта область кажется теперь скрытой, но на самом деле ее видно. Перенесите поле Дата на правый верхний угол отчета , вот сюда:
Как только вы отпустите кнопку мыши отчет сразу изменится, показав динамику продаж по дням для каждого продавца.
Отчет получился слишком подробным. Сгруппируем его по месяцам.
- Щелкните правой кнопкой по полю отчета Дата , в появившемся меню выберите Группа и структура , затем Группировать...
- В следующем диалоге Excel просит нас указать как именно группировать и сразу предлагает по месяцам (выделено). Ничего не меняем и нажимаем ОК .
Теперь дата продажи в отчете сгруппирована по месяцам.
Изменение сводного отчета Excel
Чтобы изменить отчет, например, заменить поле строк, просто перетяните это поле из отчета за его пределы. А затем перенесите туда другое поле, например, поле Продукт .
Получили отчет по продажам продуктов по месяцам.
А вот что получится, если мы заменим поле Дата на Фамилия .
Возможности сводных таблиц кажутся безграничными. А как вам это?
Резюме
В качестве исходных данных для построения отчета мы взяли коротенькую таблицу продаж. Но она могла состоять из тысяч строк и десятков столбцов. Способ построения отчета останется таким же: указали столбцы, строки и данные.
Вы получили базовые знания о сводных таблицах Excel. Этого вам достаточно, чтобы понять принцып их настройки и пользу от них. Теперь посмотрим
Сводные таблицы Excel – это особый тип построения, который подразумевает наличие функции моментального формирования отчета по документу. С их помощью можно легко обобщить некоторые однотипные данные.
В программе Excel 2007 (MS Excel 2010|2013) сводная таблица используется, в первую очередь, для составления математического или экономического анализа данных.
Как сделать сводную таблицу в Excel
Анализ данных документа способствует более быстрому и правильному решению поставленных задач. Табличный процессор Эксель может справиться даже с самыми объемными и сложными документами. Составить отчет и обобщить данные не составит труда для программы.
Чтобы создать саму простую таблицу-сводку, следуйте нижеприведенным указаниям:
- С помощью вкладки «вставка», которая находится в главном меню панели инструментов программы, выберите клавишу создания рекомендуемых сводных таблиц;
- В открывшемся диалоговом окошке программы нажмите на понравившийся вам каркас таблички. Таким образом макет станет доступным для предварительного пользовательского просмотра.
Выберите наиболее подходящий вариант, который в полной мере сможет отобразить всю информацию;
Совет! Дополнительные макеты сводных таблиц можно скачать с официального сайта компании «Майкрософт ».
- Нажмите клавишу ОК, и программа сразу добавит выбранную таблицу (или пустой макет) на открытый лист документа. Также программа автоматически определит порядок расположения строк, согласно представляемой информации;
- Чтобы выделить элементы таблицы и упорядочить их вручную, отсортируйте содержимое. Также данные можно фильтровать. По сути, сводная табличка – это прототип небольшой базы данных.
Фильтрация крайне необходима, когда появляется необходимость быстрого просмотра только определенных колонок и строчек. Ниже приведен пример сводной таблицы по продажам после фильтрования содержимого.
Таким образом можно быстро просмотреть объемы продаж в отдельных регионах (в нашем случае, запад и Юг);
Желательно пользоваться уже созданными макетами, однако, пользователь может выбрать пустую таблицу и наполнить ее самостоятельно. В пустой шаблон необходимо добавить поля, формулы для расчета, фильтры.
Также можно создавать связанные таблицы-сводки на нескольких листах документа одновременны. Таким образом можно анализировать данные всего документа или нескольких документов/листов сразу.
Проводить анализ внешних данных тоже можно с помощью сводных таблиц.
Сводные расчеты в Microsoft Excel - Формулы
При составлении отчетов в созданной сводной таблице, можно использовать большое количество статистических функций и формул для того, чтобы проанализировать полученные значения и базовые входящие данные.
В таблицу можно добавить, к примеру, элемент для вычисления комиссии для платежа или любой другой вид расчетного элемента. Все формулы в столбики и строки добавляются с помощью поля «Вставка».
С помощью данной вкладки можно создать график, рассчитать элементы по формуле и построить гистограмму зависимости.
Сводная таблица Excel - незаменимый инструмент в работе с большим объемом данных. С помощью сводной таблицы вы можете вычленять и работать только с определенными данными из всего массива. Например, уровень продаж определенного товара из всего ассортимента, сколько товаров продал конкретный менеджер или в каком магазине больше всего купили тот или иной продукт и т.п.
Создание сводных таблиц в Excel
Шаг 1. Выделите общую таблицу с помощью мышки, чтобы она «стала синей».
Шаг 2. Не снимая выделения, выберите меню «Вставка», в нем нажмите кнопку «Сводная таблица».
Шаг 3. Перед вами откроется новое окно «Создание сводной таблицы», в котором нужно нажать кнопку «Ок».
Шаг 4. На вашем листе появится редактор сводной таблицы, с помощью которого вы можете сформировать собственную сводную таблицу только с теми данными, которые вас интересуют. Просто перетащите необходимые вам поля в одну из четырех областей.
Несмотря на то, что многим из нас Excel кажется очень сложной и непонятной программой, она имеет огромное количество полезных функций и возможностей для каждого пользователя. Сводная таблица в Excel существенно облегчит вашу работу с данными, быстро сортируя и выбирая только те значения, которые вам нужны. Благодаря нашим советам вы теперь знаете, как сделать сводную таблицу в Excel всего в несколько кликов мышкой. Удачи!