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

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

Кстати, в нашем учебном центре “РУНО” есть практический курс Microsoft Excel 2016/2019. Уровень 2. Расширенные возможности, на котором можно узнать всё про сводные таблицы, сводные диаграммы, инструменты проверки и подготовки данных и многое другое.

Содержание:

1. Зачем нужны сводные таблицы;
2. Подготовка данных;
3. Инструменты создания таблицы.

Сводные таблицы предназначены для решения ряда задач: представление большого объема данных в удобном для анализа виде; обобщение данных по категориям и подкатегориям; обобщения данных; фильтрации, группировки, сортировки различных фрагментов данных. Умение работать со сводной таблицей и развитые навыки в самостоятельном построении сводных таблиц — это ключевые черты, которые определяют облик Excel Pro — высококлассного специалиста, профессионала Excel.
В современном мире данных становится не просто много, а очень много. А человеку для принятия решений необходимо видеть информацию в лаконичном, сжатом виде — на одном листе. 

Как сжать огромный объем данных до одного листа формата А4, в том числе — с помощью сводных таблиц? Это не так сложно , как может показаться на первый взгляд. Сводная таблица, созданная в Microsoft Excel, поможет выделить основное и сфокусироваться на наиболее ценной информации.

Тема создания сводных таблиц в Excel подробно рассматривается на курсе образовательного центра “Руно” Microsoft Excel 2016/2019. Уровень 2. Расширенные возможности. Пройдите пробный урок на сайте. Основа для проектирования сводной таблицы — это определенный набор значений.

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

Возникает закономерный вопрос: где же применение сводной таблицы даст наибольший эффект?

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

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

• каждый столбец должен иметь заголовок шапки;
• все строки и столбцы нужно заполнить;
• для всех столбцов данных должны быть определенные форматы ячеек (для поля “Дата” нужен формат календарной даты, а для поля “Контрагент” — формат текста и т.п.);
• значения в ячейках должны быть “единоличными” (к примеру, “Договор № 23 от 03.09.2016 года” должен быть записан в 3 разных столбцах: “Документ”, “Номер” и “Дата”);
• если вы ведете расходно-доходную табличку, в которой, кроме суммирования, еще есть необходимость вычитания, то и в свою базу первоначальных данных вводите информацию со знаком “-”. Тогда в свёрнутом виде вы получите нужный результат;
• конструкция вашей сводной таблицы должна иметь оптимальный вид.

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

ВАЖНО!
В Microsoft Excel множество различных функций и команд, которые могут значительно облегчить работу даже уверенному пользователю программы.
Освоив курс Microsoft Excel 2016/2019. Уровень 2. Расширенные возможности, вы приобретете профессиональные навыки, которые сэкономят ваше время и помогут в считанные минуты и без ошибок сформировать даже самые объемные отчеты.

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

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

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

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

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

Сводная таблица (мастер сводных таблиц)

Начнем работу с выбора в меню «Вставка», блок «Таблицы», пиктограмма «Сводная таблица». Не забываем при этом указать курсором базу исходных данных или табличку, из которой мы будем делать сводную. В открывшемся окне мы выбираем несколько условий создания сводной таблицы — это диапазон нужных исходных данных и где следует разместить сводную табличку. А поскольку курсор уже стоял на таблице, Excel быстро и автоматически определил диапазон таблицы и подставил в графу диапазона. Нажимаем «ОК» и получаем:
Как видите, был создан новый «Лист3» и вызван «Конструктор сводных таблиц». В конструкторе Вы указываете какие столбики исходной таблицы, вам нужно перенести в сводную и какие именно вычисление нужно будет над ними произвести и всё это происходит обыкновенным перетаскиванием в необходимую область, заголовка нужной нам таблицы.

В конструкторе Вы указываете какие столбики исходной таблицы, вам нужно перенести в сводную и какие именно вычисление нужно будет над ними произвести и всё это происходит обыкновенным перетаскиванием в необходимую область, заголовка нужной нам таблицы. Вот мы получили и наш первый результат, но он нас не устраивает так как у нас не суммируется количество фруктов, которые были проданы, а значит, нам нужно с области «СТРОКИ» перетянуть заголовок столбца «Вес, кг» и у нас создаётся та конструкция сводной таблицы, которую мы хотим.

Профессиональный совет: Магия Excel в том, что буквально двумя-тремя кликами можно развернуть данные, проанализировать их и принять правильное бизнес-решение. Умея работать с формулами, вы автоматизируете свою работу. Вы тратите один раз время на конструирование формулы, дальше она работает, и вы экономите массу своего времени. Пройдя курсы Excel дистанционно, вы сможете в короткие сроки освоить работу с программой и успешно применять полученные навыки на практике. Курс ведёт сертифицированный тренер Microsoft. По завершению дистанционного видеокурса Microsoft Excel 2016/2019. Уровень 2. Расширенные возможности вы сможете: Применять продвинутые инструменты вычисления;  Эффективно работать с большими табличными массивами; Анализировать данные с помощью сводных таблиц;  Применять новые диаграммы Excel 2019;  Пройдите пробный урок на нашем сайте. По окончании вы получите удостоверение о повышении квалификации! Учебная программа    Получить доступ

СМОТРИТЕ ВИДЕОУРОКИ ПО ТЕМЕ:

СТАТЬИ ПО ТЕМЕ:

Сортировка в Excel. Автоматизируем свою работу

Как влиться в новый коллектив? Адаптация на новом месте.

Как стать бухгалтером с нуля

КАТАЛОГ КУРСОВ ПО EXCEL:

Microsoft Excel 2016/2019. Уровень 2+3. Специалист. Профессиональные приемы работы в программе

Microsoft Excel 2016/2019. Анализ и прогнозирование данных. Уровень 3. Эксперт

Функция ВПР и сводные таблицы (Комплекс из 4-х курсов)