Главная » Excel » Вы здесь

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

Сводная таблица в Microsoft Excel

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

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

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

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

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

Форматирование как таблица в Microsoft Excel

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

Указание расположения таблицы в Microsoft Excel

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

Имя таблицы в Microsoft Excel

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

Переход к созданию сводной таблицы в Microsoft Excel

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

Диалоговое окно в Microsoft Excel

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

Форма для создания сводной таблицы в Microsoft Excel

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

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

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

Поля и области сводной таблицы в Microsoft Excel

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

Перенос полей в области в Microsoft Excel

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

Сводная таблица в программе Microsoft Excel

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

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

Изменения диапазона периода в Microsoft Excel

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

Фильтр по полу в Microsoft Excel

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

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

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

Обмен областями в Microsoft Excel

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

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

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

Перемещение даты и имени в Microsoft Excel

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

Выбор гистограммы в Microsoft Excel

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

Применение гистограммы ко всем ячейкам в Microsoft Excel

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

Сводная таблица в Microsoft Excel готова

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

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

Переход в параметры  Microsoft Excel

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

Добавление мастера сводных таблиц в Microsoft Excel

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

Переход в панель быстрого доступа в Microsoft Excel

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

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

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

Выбор источника сводной таблицы в Microsoft Excel

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

Выбор диапазона данных в Microsoft Excel

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

Выбор места размещения сводной таблицы в Microsoft Excel

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

Форма для создания сводной таблицы в Microsoft Excel

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

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

Мы рады, что смогли помочь Вам в решении проблемы.
Попробуйте найти другие ответы на сайте



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

Опрос: помогла ли вам эта статья?

Да Нет

Еще статьи по этой теме:

Поделиться статьей в социальных сетях
2 комментария

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

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

Оставьте свое мнение