Сводные таблицы в Excel

Сводные таблицы в ExcelСводные таблицы Excel.

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

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

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

К примеру:

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

Одним словом, если все это программировать формулами, то придется повозиться. Парой-тройкой кликов здесь не обойдешься. Вот тут и самое время обратиться к уникальному инструменту Excel, под названием «Сводные таблицы«.

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

Установите табличный курсор в любом поле нашего массива, и выберите в закладке меню «Вставка» пункт «Сводная таблица«. Обратите внимание — программа сама определила диапазон, который у нас будет обрабатываться (его границы отмечены бегущим пунктиром). Однако, диапазон будет определен неверно, если какая-то строка таблицы или какой-то столбец будут полностью пустыми. В этом случае будет выделен диапазон до пустой строки или столбца.

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

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

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

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

Например, нам необходимо определить, какие суммы затрачиваются на выплату окладной части заработной платы по управлениям. Управления будем размещать в строках. Поэтому захватываем мышкой поле «Управление» и переносим его к области «Названия строк«. Теперь, поскольку нам необходимо вычислить затраты по окладной части, захватываем поле «Оклад«, и переносим его к области «Значения«. Вот что у нас получилось:

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

Правильнее будет, если отделы мы разместим в строках и сгруппируем по управлениям. Захватываем поле «Отдел» и переносим на поле «Названия строк» ниже поля «Управление«. Согласитесь, в таком виде таблица становится намного понятнее.

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

Можно разместить колонки «Оклад» и «Премия» рядом. Если, при добавлении этих полей, они размещаются в одну колонку, посмотрите на область «Названия строк«. У вас там должен появиться пункт «∑ значения«. Переместите его к области «Названия столбцов»

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

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

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

Вставка формул в сводную таблицу

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

  • устанавливаем табличный курсор в любом поле сводной таблицы;
  • жмем вкладку меню «Параметры«;
  • выбираем пункт «формулы«-«вычисляемое поле«.

В открывшемся окне заносим имя нашего поля (я назвал «Общая ЗП»), а в пункте «Формула» заносим операции с интересующими нас полями: =Оклад + Премия. Жмем «ОК». В области значений появляется требуемый результат.

Изменение параметра полей значений и оформление

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

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

Настройки представления таблицы можно осуществить во вкладке меню «Конструктор«. Я, например, выбрал «Макет отчета» — «Показать в сжатой форме» и поставил галочку в пункте «Чередующиеся строки«. Вот такая таблица у меня получилась:

и вот такое у нее размещение полей по областям.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *