Как создать сводную таблицу в Excel: руководство для начинающих
Шарлотта Миллер
⚡ Умное резюме
Сводные таблицы в Excel позволяют обобщить большой набор данных в компактный отчет, содержащий итоги, средние значения и другие агрегированные показатели. На этой странице объясняется, что такое сводная таблица, пошагово объясняется, как ее создать на основе данных о продажах, как построить двухмерную сводную таблицу и как создать сводную диаграмму.
Бывают случаи, когда вам придется анализировать большие объемы данных и создавать простые для чтения и понимания отчеты. Сводные таблицы позволяют нам анализировать такие данные и создавать отчеты, соответствующие нашим требованиям к бизнес-отчетности.
Что такое сводная таблица?
Сводная таблица — это сводка большого набора данных, которая обычно включает общие показатели, среднее, минимальное, максимальное и т. д. Допустим, у вас есть данные о продажах для разных регионов, с помощью сводной таблицы вы можете суммировать данные по регионам и найти средние продажи по региону, максимальные и минимальные продажи по региону и т. д. Сводные таблицы позволяют нам анализировать, обобщать и показывать в наших отчетах только актуальные данные.
Четыре области сводной таблицы
Каждая сводная таблица создается путем перетаскивания полей исходных данных в одну из четырех областей списка полей. Понимание этих областей упрощает последующее представление о дальнейших шагах.
| Район | Что она делает | Пример |
|---|---|---|
| Ряды | Категории перечислены слева, по одной в каждой строке. | Название компании |
| Колонки | Распределяет вторую категорию по верхней части списка. | Имя покупателя |
| Наши ценности | Числа, которые суммируются с использованием функций SUM, AVERAGE, MIN или MAX. | Сумма промежуточных итогов |
| Фильтры | Ограничивает отображение всей таблицы выбранным значением. | Показать только один товар |
💡 Совет: Поле, размещенное как в строках, так и в столбцах, создает двумерную таблицу, показанную далее. Измените агрегированное значение в области «Значения», щелкнув по полю и выбрав «Настройки поля значений», затем выберите «Среднее», «Количество», «Максимум» или «Минимум».
Пошаговое руководство по созданию сводных таблиц
На изображении ниже показаны примеры данных о продажах, собранные из базы данных доступа North Wind.
Вы можете скачать образец данных Excel здесь.
Загрузите указанный выше файл данных Excel.
Как вы можете видеть на изображении выше, наша электронная таблица содержит много данных. Допустим, мы хотим создать сводную информацию о клиентах, сгруппировать все их заказы по продуктам и отобразить количества, цену за единицу и промежуточные итоги для всех транзакций.
- Откройте файл Excel, который вы скачали.
- Нажмите вкладку «ВСТАВКА» на ленте.
- Вы увидите следующее окно
- Нажмите кнопку выбора таблицы/диапазона, как показано на изображении выше.
- Вы получите следующее мини-окно
- Нажмите на адрес ячейки A1.
- Нажмите Ctrl + A на клавиатуре, чтобы выделить все ячейки данных.
- Ваше мини-окно теперь выглядит следующим образом:
- Нажмите кнопку «Закрыть», чтобы вернуться в окно параметров.
- Нажмите кнопку ОК
-
Выберите следующие поля
- Название компании
- Наименование
- Цена за единицу
- Количество
- Промежуточный итог
- Ваш рабочий лист теперь должен выглядеть следующим образом
- Обратите внимание, что приведенные выше данные сгруппированы по названию компании-клиента, названию продукта, цене за единицу, сумме количеств и сумме промежуточных итогов.
- Обратите внимание на раскрывающуюся кнопку рядом с надписью «Метки строк». Эта кнопка позволяет нам сортировать/фильтровать наши данные. Предположим, нас интересует только Alfredс Футтеркисте
- Нажмите на раскрывающийся список «Метки строк», как показано ниже.
- Снимите галочку с пункта (Выбрать все)
- Выбрать Alfredс Футтеркисте
- Нажмите кнопку ОК
- Вы получите следующие данные
Двумерные сводные таблицы
Двумерная сводная таблица — это таблица, в которой поля есть как в строках, так и в столбцах. Допустим, мы хотим перечислить имена сотрудников в виде строк и использовать столбцы для представления имен клиентов и заполнить ячейки общим объемом продаж.
- Активируйте таблицу продаж
- Нажмите вкладку «ВСТАВКА».
- Нажмите кнопку «Сводная диаграмма и таблица».
- Выберите все данные. Теперь Excel должен запомнить предыдущий диапазон, поэтому вам просто нужно нажать кнопку «ОК».
- Новый лист будет создан с помощью инструментов сводной таблицы.
- Выберите поля, как показано на изображении ниже.
Ваша сводная таблица теперь будет выглядеть следующим образом
Визуализация данных сводной таблицы с помощью диаграмм
В Excel 2013 вам не нужно создавать диаграммы вручную. Excel создаст графики для вас, когда вы создаете сводные таблицы, меняете агрегатные функции, применяете фильтры и т. д.
Приведенная ниже диаграмма была автоматически создана для нас на основе простого упражнения по сводной диаграмме, в котором данные фильтровались по Alfredтолько Футтеркисте.
