Примеры сводных таблиц в эксель. Изменение структуры отчета

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

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

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

В появившемся окне необходимо выбрать источник данных, который представляет:

1. Диапазон (может находиться в другой книге);
2. Таблица данных (указывается ее имя);
3. Данные из внешнего источника, полученные по SQL-запросу из базы данных и т.п.

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

Управление списком полей таблицы

После нажатия кнопки «ОК» в окне создания сводной таблицы, создается пустая область для ее размещения, и отображается окно со списком всех полей.

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

• Названия строк – отображает значения поля в виде названия строк. По сути данная область ничего не меняет, если назначить ей все имеющиеся поля, то получим исходную таблицу;
• Названия столбцов – значения полей отображаются в виде названий столбцов. Действие напоминает обычное транспонирование;
• Значения – со значениями столбцов, помещенными в эту область, производятся вычисления. При этом одно поле может быть назначено ей несколько раз;
• Фильтр отчета – сюда помещаются поля, значения которых служат для фильтрации строк исходных данных, которые сводная таблица должна проигнорировать.

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

Месяц	Дата	Кол-во	Курс	Изменение
Январь	10.01.2013	1	30,4215	0,0488
Январь	11.01.2013	1	30,3650	-0,0565
Январь	12.01.2013	1	30,2537	-0,1113
...	...	...	...	...
Сентябрь	28.09.2013	1	32,3451	0,1715

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

В область «Названия строк» перетащим поле «Месяц». Области «Значения» назначим «Курс», после чего изменим параметры для поля так, чтобы по нему высчитывалось среднее арифметическое. Для этого кликаем по требуемому пункту в области, в раскрывшемся меню жмем на «Параметры полей значений…». В появившемся окне имеется вкладка «Операция». В ней необходимо выбрать из списка «Среднее». Готово.

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

Вычисляемые поля сводной таблицы

Если предоставленных операций и вычислений недостаточно, то эксель позволяет создать свое вычисляемое поле в сводной таблице. Для этого выделите ячейку из области таблицы, перейдите на вкладку «Параметры» («Анализ» для Excel 2013) появившейся ленты. Далее в разделе «Сервис» кликните по пиктограмме «Формулы», из раскрывающегося меню (в версии 2010 и выше путь отличается: Раздел «Вычисления» -> Раскрывающийся список «Поля, элементы и наборы») выберите пункт «Вычисляемое поле…». Должно появиться окно:

Задайте понятное имя, и запишите формулу, используя любые функции (имейте в виду, что вычисляемые поля не работают с текстом). В качестве примера умножим курс на 1000 и вычтем 13 процентов (=Курс*1000*0,87). Назовем поле «ЗП», добавим в область значений и в качестве операции применим максимум. Посмотрите новый вид отчета:

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

Параметры сводной таблицы в Excel

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

Из примера видно, что сводная таблица представляет древовидную структуру, если используется более 1 поля. Корнем являются значения столбца, который в списке области «Названия строк» идет первым. Все последующие поля вкладываются в него и в друг друга, согласно своей очередности в списке, изменить которую можно простым перетаскиванием мыши. Каждую отдельную ветвь подобного дерева можно сворачивать и раскрывать. Данное свойство так же применимо к области названий столбцов.
По умолчанию эксель задает сводным таблицам макет в сжатом виде. Его можно изменить через параметры (клик правой кнопкой мыши по области таблицы -> параметры сводной таблицы -> Вывод -> Классический макет) либо через конструктор:

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

Так как сводная таблица представляет древовидную структуру, то название строки отображается только один раз. В Microsoft Excel, начиная с версии 2010, можно дополнительно применить к макету повторение подписей элементов.

Теперь законченная сводная таблица выглядит так на листе Excel:

Помимо рассмотренных свойств через параметры таблицы можно установить:

1. Имя сводной таблицы;
2. Объединение и выравнивание подписей;
3. Вывод значений для пустых ячеек;
4. Автоматическое изменение ширины столбцов;

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

Создав общую таблицу, в каком либо из текстовых документов, можно осуществить её анализ, сделав в Excel сводные таблицы.

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

1. Данные вписываются в таблицу, где есть столбцы и списки с названиями.
2. Отсутствие незаполненных форм.
3. Отсутствие скрытых объектов.

Как сделать сводную таблицу в excel: пошаговая инструкция

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

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

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


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

Выбираем конкретного продавца. Зажимаем мышку и переносим поле «Продавец» в «Фильтр отчета». Новое поле отмечается галочкой, и вид таблицы немного изменяется.


Категорию «Товары» мы поставим в виде строк. В «Названия строк» мы переносим необходимое нам поле.


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

Столбец «Единицы», будучи в главной таблице, отображал количество товара проданного определенным продавцом по конкретной цене.


Для отображения продаж, например, по каждому месяцу, нужно поле «Дата» поставить на место «Названия столбцов». Выберите команду «Группировать», нажав на дату.


Указываем периоды даты и шаг. Подтверждаем выбор.

Видим такую таблицу.


Сделаем перенос поля «Сумма» к области «Значения».


Стало видно отображение чисел, а нам необходим именно числовой формат


Для исправления, выделим ячейки, вызвав окно мышкой, выберем «Числовой формат».

Числовой формат мы выбираем для следующего окна и отмечаем «Разделитель групп разрядов». Подтверждаем кнопкой «ОК».

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

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


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


Отдельно настраиваются и параметры поля. На примере мы видим, что определенный продавец Рома в конкретном месяце продал рубашек на конкретную сумму. Нажатием мышки мы в строке «Сумма по полю…» вызываем меню и выбираем «Параметры полей значений».


Далее для сведения данных в поле выбираем «Количество». Подтверждаем выбор.

Посмотрите на таблицу. По ней четко видно, что в один из месяцев продавец продал рубашки в количестве 2-х штук.


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

Выделение ячеек в сводной таблице приведет к появлению такой вкладки как «Работа со сводными таблицами», а в ней будут еще две вкладки «Параметры» и «Конструктор».


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

Надеюсь, вы усвоили весь материал, и теперь знаете, как сделать сводную таблицу в excel.

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

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

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

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

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

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

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

Теперь нужно определиться, что именно мы хотим получить и какие данные проанализировать. Например, мы хотим узнать, в какие города и какой товар поставлял конкретный менеджер по месяцам и по кварталам, а также необходимо знать объем. Для этого в область «Фильтры» мы перетаскиваем мышкой поле «Менеджер» , в область «Колонны» переместим поле «Дата» .

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

Осталась у нас область «Значения» , в которую мы перенесем поле «Масса» .

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

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

Как видно, произошла замена поля в списке и в область «Значения» его необходимо повторно добавить.

Вот теперь сводная таблица в Экселе готова и можно фильтровать все поставки по конкретному менеджеру.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Хотя, мы будем рассматривать процесс создания сводной таблицы на примере программы Microsoft Excel 2010, но данный алгоритм применим и для других современных версий этого приложения.

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

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

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

После этого, таблица превращается в динамическую, и авторастягивающуюся. Она также получает имя, которое при желании пользователь может изменить на любое удобное ему. Просмотреть или изменить имя таблицы можно во вкладке «Конструктор».

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

Открывается окно, в котором нам опять нужно выбрать диапазон, или название таблицы. Как видим, программа уже сама подтянула имя нашей таблицы, так что тут ничего больше делать не нужно. В нижней части диалогового окна можно выбрать место, где будет создавать сводная таблица: на новом листе (по умолчанию), или же на этом же. Конечно, в большинстве случаев, намного удобнее использовать сводную таблицу на отдельном листе. Но, это уже индивидуальное дело каждого пользователя, которое зависит от его предпочтений, и поставленных задач. Мы же просто жмем на кнопку «OK».

После этого, на новом листе открывается форма создания сводной таблицы.

Как видим, в правой части окна расположен список полей таблицы, а ниже четыре области:

1. Названия строк;
2. Названия столбцов;
3. Значения;
4. Фильтр отчёта.

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

Итак, в данном конкретном случае, мы переместили поля «Пол» и «Дата» в область «Фильтр отчёта», поле «Категория персонала» в область «Названия столбцов», поле «Имя» в область «Название строк», поле «Сумма заработной платы» в область «Значения». Нужно отметить, что все арифметические расчеты данных подтянутых из другой таблицы возможны только в последней области. Как видим, во время того, как мы проделывали данные манипуляции с переносом полей в области, соответственно изменялась и сама таблица в левой части окна.

Получилась вот такая сводная таблица. Над таблицей отображаются фильтры по полу и дате.

Настройка сводной таблицы

Но, как мы помним, в таблице должны остаться данные только за третий квартал. Пока же отображаются данные за весь период. Для того, что бы привести таблицу к нужному нам виду, кликаем на кнопку около фильтра «Дата». В появившемся окошке устанавливаем галочку напротив надписи «Выделить несколько элементов». Далее, снимаем галочки со всех дат, которые не вписываются в период третьего квартала. В нашем случае, это всего лишь одна дата. Жмем на кнопку «OK».

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

После этого, сводная таблица приобрела такой вид.

Чтобы продемонстрировать, что управлять данными в таблице можно как угодно, снова открываем форму списка полей. Для этого переходим во вкладку «Параметры», и жмем на кнопку «Список полей». Затем, перемещаем поле «Дата» из области «Фильтр отчета» в область «Название строк», а между полями «Категория персонала» и «Пол», производим обмен областями. Все операции выполняем с помощью простого перетягивания элементов.

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

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

Также, можно отобразить числовые значения таблицы в виде гистограммы. Для этого, выделяем ячейку с числовым значением в таблице, переходим во вкладку «Главная», жмем на кнопку «Условное форматирование», переходим в пункт «Гистограммы», и выбираем понравившийся вид гистограммы.

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

Теперь, наша сводная таблица приобрела презентабельный вид.

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

Создать сводную таблицу можно, применив Мастер сводных таблиц. Но, для этого сразу нужно вывести данный инструмент на Панель быстрого доступа.Переходим в пункт меню «Файл», и жмем на кнопку «Параметры».

В открывшемся окне параметров, переходим в раздел «Панель быстрого доступа». Выбираем команды из команд на ленте. В списке элементов ищем «Мастер сводных таблиц и диаграмм». Выделяем его, жмем на кнопку «Добавить», а потом на кнопку «OK» в правом нижнем углу окна.

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

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

• в списке или в базе данных Microsoft Excel;
• во внешнем источнике данных (другой файл);
• в нескольких диапазонах консолидации;
• в другой сводной таблице или в сводной диаграмме.

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

После этого, появляется окно с диапазоном таблицы с данными, который при желании можно изменить, но нам этого делать не нужно. Просто жмем на кнопку «Далее».

Затем, Мастер сводных таблиц предлагает выбрать место, где будет размещаться новая таблица на этом же листе или на новом. Делаем выбор, и жмем на кнопку «Готово».

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

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

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