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

Зачем нужны сводные таблицы и когда их используют

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

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

Анализ данных: сводные таблицы позволяют быстро и эффективно анализировать большие объемы данных.

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

Объединение данных из различных источников: таблицы идеально подходят для объединения данных из разных таблиц или даже разных файлов Excel. 

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

Визуализация данных: сводные таблицы предоставляют множество опций для визуализации данных.

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

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

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

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

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

Обучение и исследования: они широко используются в обучении и исследованиях. 

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

Создаем и настраиваем сводную таблицу

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

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

Вот так это выглядит в нашем примере:

Для того чтобы создать сводную таблицу в Microsoft Excel, нужно выполнить следующие шаги.

— Откройте файл Excel, в котором вы хотите создать сводную таблицу.

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

— На вкладке «Вставка» нажмите на кнопку «Сводная таблица».

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

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

В случае с нашим примером, будет так:

Давайте сведем в таблицу данные по общему количеству проданного товара. В «Строке» указываем «Товар», а в столбце — «Количество». Чтобы настроить расчет таблицы в пункте Значения выбираем Количество и суммировать по SUM. Получается такой результат.

Готово — таблица создана. Далее настроим ее внешний вид, форматирование, сортировку и фильтрацию данных.

Настраиваем фильтры

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

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

В редакторе прокручиваем курсор вниз, находим «Фильтры», нажимаем «Добавить» и выбираем «Товар».

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

Чтобы настроить фильтр нажмите «Показаны все объекты», откройте «Фильтровать по условию». Сейчас у нас отображены все объекты.

Мы хотим посмотреть, сколько продано устройств, поэтому убираем галочку с модуля. Нажимаем «ОК».

Вот, что получилось — всего продали 323 устройства.

Дополнительные вычисления

Сводная таблица позволяет проводить и дополнительные вычисления. Например, вы хотите узнать, сколько модулей и устройств продалось в процентном соотношении. Для этого вернемся в «Редактор таблицы» и выберем «Значение» —> «Показывать как» —> «% от итоговой суммы».

Готово! Мы узнали, что продали 51,21% модулей и 48,79% устройств от общей суммы.

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

Если в исходную таблицу вы добавили новые вводные, например, менеджеры за новую рабочую неделю продали еще товар. Обновить таблицу не составит труда.

Если добавились новые строки, то нужно изменить диапазон сводной таблицы. В нашем примере добавили 3 строки:

Поэтому в сводной таблице меняем диапазон с A1:I6 на A1:I9.

Так выглядит ДО:

Так ПОСЛЕ:

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

Такие таблицы и расчеты используют, к примеру, специалисты по маркетплейсам. Все об этой профессии вы узнаете на бесплатном курсе

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

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

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

Чтобы помочь вам найти работу, поддержать и ответить на все вопросы, работает Центр карьеры. 

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

Оставить заявку на обучение