Как создать сводную таблицу в Excel: руководство для начинающих

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

Что такое сводная таблица?

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

Пошаговое руководство по созданию сводных таблиц

На изображении ниже показаны примеры данных о продажах, собранные из базы данных доступа North Wind.

Вы можете скачать образец данных Excel здесь.

Загрузите указанный выше файл данных Excel.

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

• Откройте файл Excel, который вы скачали.
• Нажмите вкладку «ВСТАВКА» на ленте.

• Вы увидите следующее окно

• Нажмите кнопку выбора таблицы/диапазона, как показано на изображении выше.
• Вы получите следующее мини-окно

• Нажмите на адрес ячейки A1.
• Нажмите Ctrl + A на клавиатуре, чтобы выделить все ячейки данных.
• Ваше мини-окно теперь выглядит следующим образом:

• Нажмите кнопку «Закрыть», чтобы вернуться в окно параметров.
• Нажмите кнопку ОК

• Выберите следующие поля
  • Название компании
  • наименование товара
  • Цена за единицу
  • Количество
  • Промежуточный итог
• Ваш рабочий лист теперь должен выглядеть следующим образом

• Обратите внимание, что приведенные выше данные сгруппированы по названию компании-клиента, названию продукта, цене за единицу, сумме количеств и сумме промежуточных итогов.
• Обратите внимание на раскрывающуюся кнопку рядом с надписью «Метки строк». Эта кнопка позволяет нам сортировать/фильтровать наши данные. Предположим, нас интересует только Alfredс Футтеркисте
• Нажмите на раскрывающийся список «Метки строк», как показано ниже.

• Снимите галочку с пункта (Выбрать все)
• Выберите Alfredс Футтеркисте
• Нажмите кнопку ОК
• Вы получите следующие данные

Двумерные сводные таблицы

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

• Активируйте таблицу продаж
• Нажмите вкладку «ВСТАВКА».
• Нажмите кнопку «Сводная диаграмма и таблица».
• Выберите все данные. Теперь Excel должен запомнить предыдущий диапазон, поэтому вам просто нужно нажать кнопку «ОК».
• Новый лист будет создан с помощью инструментов сводной таблицы.
• Выберите поля, как показано на изображении ниже.

Ваша сводная таблица теперь будет выглядеть следующим образом

Визуализация данных сводной таблицы с помощью диаграмм

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

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

Резюме

Сводные таблицы и диаграммы позволяют нам обобщать и анализировать большие наборы данных. Мы можем использовать агрегатные функции, такие как СУММА, МИНИМАЛЬ, МАКСИМУМ, СРЗНАЧ и т. д. Диаграммы автоматически создаются и обновляются для нас в Excel по мере работы с данными.