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

Сводная таблица обеспечивает формирование сводной (агрегированной) информации в структурированном виде. Источниками данных для формирования сводной таблицы являются:

Список (база данных) Excel на листе текущей рабочей книги.

Внешний источник данных (текстовый файл, содержащий табличные данные, реляционная база данных).

Диапазоны ячеек для консолидации текущей рабочей книги.

Построение сводной таблицы осуществляется с помощью Мастера сводных таблиц – команда меню ДанныеСводная таблица:

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

На втором шаге задается диапазон ячеек для построения сводной таблицы.

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

На третьем шаге осуществляется формирование структуры сводной таблицы. Для этого нажимается кнопка Макет. Дополнительная настройка параметров сводной таблицы выполняется при нажатии кнопки Параметры.

Страница – для размещения полей фильтрации (отбора) записей, отображаемых в сводной таблице;

Строка – для размещения полей группирования, учитывается последовательность полей для создания вложенных групп, подгрупп и т.д.;

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

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

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

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

В табл. 20 приведены ограничения, которые действуют для сводных таблиц Microsoft Excel 2000.

Ограничивается объемом доступной оперативной памяти

Ограничивается объемом доступной оперативной памяти

256 (может ограничиваться объемом доступной оперативной памяти)

Число формул вычисляемых элементов в сводной таблице

Ограничивается объемом доступной оперативной памяти

Для работы со сводной таблицы используется панель инструментов Сводные таблицы – рис. 19.

Формат отчета – выбор формата отчета для сводной таблицы.

Сводная диаграмма – построение диаграммы по сводной таблице.

Мастер сводных таблиц – переход к 3 шагу построения сводной таблицы (изменение макета, настройка параметров, выбор места размещения сводной таблицы).

Скрыть детали, Отобразить детали полей группирования.

Обновить данные сводной таблицы при изменении источника.

Настройка клиент-сервера при использовании OLAP-куба для построения сводной таблицы.

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

Формулы – создание вычисляемых полей в области данных или вычисляемых объектов/элементов в области заголовков.

Параметры поля – настройка параметров поля сводной таблицы.

Другие кнопки соответствуют командам: Формат отчета, Мастер диаграмм, Мастер сводных таблиц, Скрыть/Отобразить детали (для выделенного заголовка), Обновить данные, Параметры поля, Отобразить поля.

Пример построения сводной таблицы для расчета месячной зарплаты рабочих при повременной форме оплаты труда, начисления премии и учета удержаний. Премия дифференцированы по разрядам: 2 разряд 25%, 3 разряд 32%, 4 разряд 50% к тарифу, 5 разряд – 50%. Удержания берутся со всех видов начислений (зарплата, премия) и составляют 13% от суммы начислений.

Вставить новый лист – команда меню ВставкаЛист, если необходимо.

Переименовать лист в Картотека – команда меню ФорматЛистПереименовать.

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

За 10 минут я научу вас пользоваться сводными таблицами Excel

Расположением полей можно управлять – делать их названиями строк или столбцов, перетаскивая в соответствующие окна, а так же и сортировать в удобном порядке. Можно фильтровать отдельные пункты, перетащив соответствующее поле в окно «Фильтр». В окно «Значение» помещается то поле, по которому производятся расчеты и подводятся итоги.

Что такое сводная таблица?

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

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

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

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

Сводная таблица, показанная справа является отличным способом подытоживания данных. Я показал каждого клиента «Client» в отдельной строке и подвел итоги «amount billed».

Обратите внимание, что в исходной таблице я выполнил несколько разных видов работы для клиента под «Schoen-Jacobson». Сводная таблица подводит итог этих счетов в той же строке, что очень удобно.

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

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

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

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

1. Каждая строка данных должна быть отдельной записью. В принципе, вы можете использовать каждую строку для ваших данных.
2. Убедитесь, что в каждом столбце есть заголовок и убедитесь, что нет пустых столбцов.

Изменение размера шрифта, начертания и стиля написания осуществляется вручную. Для этого пользователь пользуется инструментами блока «Шрифт» на вкладке «Главная» или вызывает диалоговое окно «Формат ячеек» через ПКМ.