Как создать сводную таблицу в Эксель

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

Содержание

Подготовка данных
Создание сводной таблицы в Эксель
Работа со сводными таблицами
Резюме

как сделать сводную таблицу в excel - фото

Подготовка данных

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

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

Сводная таблица в Excel должна соответствовать следующим параметрам:

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

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

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

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

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

После этого можно приступать к работе:

На панели управления документом открываем вложением «Вставка» и выбираем табличный формат:

если вы полный «чайник» в Excel, то выбирайте «Рекомендуемые сводные таблицы»;
если же вы опытный пользователь, выберите «Сводную таблицу» с полностью ручной настройкой данных.

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

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

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

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

Работа со сводными таблицами

Как было сказано выше, сводные таблицы в Экселе имеют два формата. Рассмотрим, как работать с каждым из них.

Мастер сводных таблиц

«Мастер сводных таблиц» — многофункциональное табличное поле, позволяющее разными способами анализировать данные, при условии если сама таблица верно создана и заполнена (см. пункт «Подготовка данных»).

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

В окне настроек выбираем:

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

как создать сводную таблицу в excel - фото

В итоге мы получили:

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

Внизу панели настроек есть четыре области «Значения», «Строки», «Столбцы» и «Фильтры», каждая из которых выполняет определенную функцию:

«Значения» — считает выбранные данные из исходной таблицы и переносит результат в сводную. Изначально (по умолчанию) данные суммируются, однако вы всегда можете выбрать другие действия: расчет среднего показателя, перемножение, расчет минимума или максимума;
«Строки» и «Столбцы» — параметры визуального расположение нужных полей в сводной таблице в Excel. При выборе «Строк» поля разместятся построчно, при выборе «Столбцов» — столбцами, соответственно;
«Фильтры» — в этой вкладке можно выбрать данные для отображения в сводной таблице, и данные для их сокрытия.

как создать сводную таблицу в excel пошаговая инструкция - фото

Настройка сводной таблицы происходит следующими способами:

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

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

Все расчеты и результаты будут отображаться в области слева и на созданном «новом листе».

как сделать сводную таблицу в excel из нескольких листов - фото

Обновление данных в таблице Microsoft Excel

Если вам необходимо изменить выбранные данные для анализа сводной таблицы, то следуйте этому алгоритму:

переместитесь на «новый лист» созданный сводной таблицей;
найдите вкладку «Анализ сводной таблицы»;
выберите «Изменить источник данных»;
программа переведет вас на лист с исходной таблицей;
в окне «Переместить сводную таблицу» выберите новый диапазон или расположение и нажмите «Ок», чтобы данные обновились.

Если вам необходимо обновить данные в исходной таблице для анализа, то их меняем непосредственно в исходной таблице. Затем, для обновления данных для анализа, уже в сводной таблице возвращаемся во вкладку «Анализ сводной таблицы» и жмем кнопку «Обновить». Новые внесенные данные автоматически учтутся в анализе «Сводной таблицы» на «Новом листе».

как создать сводную таблицу в эксель - фото

Резюме

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

#Инструкции #Excel