Как делать сводную таблицу в excel. Почему именно новые версии

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

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

Форма сводной таблицы содержит три области для перетаскивания в них полей: для столбцов, для строк и для данных.

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

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

Теперь перетащите поле Сумма в область данных .

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

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

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

Отчет получился слишком подробным. Сгруппируем его по месяцам.

Щелкните правой кнопкой по полю отчета Дата , в появившемся меню выберите Группа и структура , затем Группировать...
В следующем диалоге Excel просит нас указать как именно группировать и сразу предлагает по месяцам (выделено). Ничего не меняем и нажимаем ОК .

Теперь дата продажи в отчете сгруппирована по месяцам.

Изменение сводного отчета Excel

Чтобы изменить отчет, например, заменить поле строк, просто перетяните это поле из отчета за его пределы. А затем перенесите туда другое поле, например, поле Продукт .

Получили отчет по продажам продуктов по месяцам.

А вот что получится, если мы заменим поле Дата на Фамилия .

Возможности сводных таблиц кажутся безграничными. А как вам это?

Резюме

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

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

Чтобы укомплектовать данные в таблице наиболее удобным способом и всегда иметь возможность быстрого доступа к ним, попробуйте сделать сводную таблицу в программе Microsoft Office Excel. Составление такого типа таблицы займёт у вас пару минут, а её использование сохранит драгоценное время в дальнейшем.

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

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

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

Перед вами появится такое окно по созданию таблицы. Выберите в первой графе “Выбрать таблицу или диапазон”, чтобы вручную выделить область для создания таблицы. Во второй графе отметьте на каком именно листе требуется составить сводную таблицу: текущем или следующем.
Это особенно удобно, когда вы не уверены на сто процентов, что не будете менять ваши значения в ячейках. Вы просто создадите таблицу на следующем листе, а при исправлениях, сможете удалить её без потери информации.

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

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

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

Теперь вы без труда можете создавать сводные таблицы в программе от Microsoft – Excel. В ней есть опции помещения данных из совершенно другой таблицы, даже на другом листе. А выделяя разные столбцы, вы имеете возможность комбинировать множество таблиц в одной.

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

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

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

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

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

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

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

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

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

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

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

При построении сводных таблиц нужно обратить внимание на следующее. Количество строк и столбцов сводной таблицы равно количеству разных значений соответствующего поля исходной таблицы. В рассматриваемом примере в колонке (в поле) Дата исходной таблицы есть названия только трех месяцев: Декабрь, Январь и Февраль. Поэтому в сводной таблице три основные колонки. Здесь следует вспомнить, что содержимое ячейки может отображаться на экране по-разному. Разные даты, например 30.02.2010 и 8.02.2010, в случае, если задан формат отображения даты "только месяц", на экране отображаются как Февраль. При формировании сводной таблицы Excel выполняет анализ содержимого ячеек колонок без учета формата отображения. Поэтому, для того чтобы приведенный пример работал правильно, даты расходов в исходной таблице должны быть введены как текст.

Сводная таблица в Excel - это мощнейший инструмент для анализа данных, который поможет вам быстро:

Подготовить данные для отчетов;
Рассчитать различные показатели;
Сгруппировать данные;
Отфильтровать и проанализировать интересующие показатели.

А также сэкономить вам кучу времени.

Из данной статьи вы узнаете:

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

Для начала научимся делать сводные таблицы.

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

Товар
Продажи в руб.

И в каждой строке 3-м параметра связаны между собой, т.е. например, 01.02.2010 года Товар 1 продали на 422 656 руб.

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

Появится диалоговое окно, в котором:

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

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

Нажимаем "ОК", сводная таблица готова и выведена в новый лист. Назовем лист "Сводная".

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

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

Группировка и фильтрация временных рядов в сводной таблице

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

Для этого переходим в лист "Данные", и после даты вставляем 3 пустых столбца. Выделяем столбец "Товар" и нажимаем "Вставить".

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

Вставленные столбцы называем "Год", "Месяц", "Год-Месяц".

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

В столбец "Год" добавляем формулу =ГОД(со ссылкой на дату);
В столбец "Месяц" добавляем формулу =МЕСЯЦ(со ссылкой на дату);
В столбец "Год - Месяц" добавляем формулу =СЦЕПИТЬ(ссылка на год;" ";ссылка на месяц).

Получаем 3 столбца с годом, месяцем и годом и месяцем:

Теперь переходим в лист "Сводная", устанавливаем курсор на сводную таблицу, вызываем правой кнопкой мыши меню и нажимаем кнопку "Обновить" . После обновления в списке полей у нас появляются новые поля сводной таблицы "Год", "Месяц", "Год - месяц", которые мы добавили в простую таблицу с данными:

Теперь давайте проанализируем продажи по годам.

Для этого поле "Год" мы перетаскиваем в "название столбцов" сводной таблицы. Получаем таблицу с продажами по товарам по годам:

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

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

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

продажи по каждому товару в сумме за целый год (строка с названием товара);
более подробно продажи по каждому товару в каждом месяце в динамике за 4 года.

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

Нажимаем на появившейся над сводной фильтр и ставим галочку "Выделить несколько элементов" . Затем в списке с годами и номерами месяцев снимаем галочку с 2012 10 и нажимаем ОК.

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

Расчет проноза с помощью сводной таблицы и Forecast4AC PRO

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

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

и нажимаем кнопку "График Модель прогноза" в меню Forecast4AC PRO

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

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

Точных вам прогнозов!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа :

Novo Forecast Lite - автоматический расчет прогноза в Excel .
4analytics - ABC-XYZ-анализ и анализ выбросов в Excel.
Qlik Sense Desktop и QlikView Personal Edition - BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

Novo Forecast PRO - прогнозирование в Excel для больших массивов данных.

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

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

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

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

Область значений

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

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

Область строк

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

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

Область столбцов

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

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

Область фильтров

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

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

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

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

Вы можете скачать файл с примером

Щелкните на любой ячейке, находящейся внутри таблицы с исходными данными (те, которые вы будете использовать для создания сводной таблицы)
Перейдите к вкладке Вставка –> Таблица -> Сводная таблица, как показано на рисунке.

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

Щелкаем OK.

На данном этапе вы создали пустой отчет сводной таблицы на ново листе.

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

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

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

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

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

В нашем примере мы хотим увидеть основные показатели регионов, сгруппированных по округам. Для этого необходимо добавить поле Федеральный округ и Регион в область Строками . А поля Площадь территории, Численность населения и Денежные доходы в область Значения.

В списке с полями для добавления в отчет, ставим галочку напротив поля Федеральный округ. Теперь в области Строки и в сводной таблице появились данные поля.

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

Обратите внимание, что если мы ставим галки напротив полей с текстовыми значениями, excel по умолчанию помещает эти значения в область строк, с числовыми значениями – в область значений.

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

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

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

Использование фильтров в сводной таблице

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

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

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

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

Щелкаем левой кнопкой мыши в любом месте сводной таблицы. Идем во вкладку Работа со сводными таблицами -> Анализ –> Источник данных .

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

Итог

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