Статьи и новости
Как сделать сводную таблицу в Excel. Пошаговая инструкция

Как сделать сводную таблицу в Excel. Пошаговая инструкция

23 марта 2023
Сводные таблицы 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-х курсов) 

Если вы не знаете, что входит в Microsoft Office, то видео уроки по Майкрософт Офис: Эксель и Ворд бесплатно именно для вас. С помощью таких курсов вы с легкостью сможете понять:

  • что за программа Microsoft Office
  • что такое MS Office и как им пользоваться
  • для чего нужен Майкрософт офис

Затрагивая отдельные компоненты MS Office, бесплатные видео курсы по работе в Microsoft Excel помогут вам разобраться в:

  • работе с формулами Excel
  • назначении программы MS Excel
  • для чего используются функции Microsoft Excel
  • с какими типами данных работает Excel

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

Видео уроки Power Point помогут научиться наглядно доносить информацию до коллег и руководства, через лаконичные презентации и диаграммы.

Бесплатные видео курсы по работе в Microsoft Word станут основой для правильного составления:

  • приказов
  • внутренних документов организаций
  • служебных писем

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

Видео уроки по Майкрософт Офис: Эксель и Ворд бесплатно могут стать отправной точкой в повышении квалификации новичков. Данный раздел учебного центра РУНО дает возможность почувствовать себя более уверенно перед поступлением на дипломную программу или большой курс, где потребуются данные навыки.

Любому гостю сайта или слушателю программ РУНО предоставлен открытый доступ к бесплатным видео урокам, где можно ознакомиться с преподавателями, примерными программами и форматом уроков.

Если вы до сих пор сомневаетесь, нужен ли вам полный курс по Microsoft Office, открывайте бесплатные уроки. Определите, в какой именно области вам нужно повышение квалификации и далее переходите в раздел нужных вам курсов.

Если вы не знаете, что входит в Microsoft Office, то видео уроки по Майкрософт Офис: Эксель и Ворд бесплатно именно для вас. С помощью таких курсов вы с легкостью сможете понять:

  • что за программа Microsoft Office
  • что такое MS Office и как им пользоваться
  • для чего нужен Майкрософт офис

Затрагивая отдельные компоненты MS Office, бесплатные видео курсы по работе в Microsoft Excel помогут вам разобраться в:

  • работе с формулами Excel
  • назначении программы MS Excel
  • для чего используются функции Microsoft Excel
  • с какими типами данных работает Excel

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

Видео уроки Power Point помогут научиться наглядно доносить информацию до коллег и руководства, через лаконичные презентации и диаграммы.

Бесплатные видео курсы по работе в Microsoft Word станут основой для правильного составления:

  • приказов
  • внутренних документов организаций
  • служебных писем

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

Видео уроки по Майкрософт Офис: Эксель и Ворд бесплатно могут стать отправной точкой в повышении квалификации новичков. Данный раздел учебного центра РУНО дает возможность почувствовать себя более уверенно перед поступлением на дипломную программу или большой курс, где потребуются данные навыки.

Любому гостю сайта или слушателю программ РУНО предоставлен открытый доступ к бесплатным видео урокам, где можно ознакомиться с преподавателями, примерными программами и форматом уроков.

Если вы до сих пор сомневаетесь, нужен ли вам полный курс по Microsoft Office, открывайте бесплатные уроки. Определите, в какой именно области вам нужно повышение квалификации и далее переходите в раздел нужных вам курсов.