Бывают моменты, когда вам потребуется анализировать большие объемы данных и создавать легко читаемые и понятные отчеты. Сводные таблицы позволяют нам анализировать такие данные и создавать отчеты, соответствующие нашим требованиям к бизнес-отчетности.
В этом руководстве мы рассмотрим следующие темы;
- Что такое сводная таблица?
- Пошаговое руководство по созданию сводных таблиц
- 2-мерные сводные таблицы
- Визуализация данных сводной таблицы с помощью диаграмм
Что такое сводная таблица?
Сводная таблица - это сводка большого набора данных, который обычно включает в себя общие цифры, среднее, минимальное, максимальное и т. Д. Допустим, у вас есть данные о продажах для разных регионов, с помощью сводной таблицы вы можете суммировать данные по регионам и найти средние продажи по регионам, максимальные и минимальные продажи по регионам и т. д. Сводные таблицы позволяют нам анализировать, обобщать и отображать только релевантные данные в наших отчетах.
Пошаговое руководство по созданию сводных таблиц
На изображении ниже показан образец данных о продажах, взятых из базы данных North wind access.
Вы можете скачать образцы данных Excel здесь.
Загрузите указанный выше файл данных Excel
Как вы можете видеть на изображении выше, наша таблица содержит много данных. Допустим, мы хотим создать сводку клиентов, сгруппировать все их заказы по продуктам и показать количество, цену за единицу и промежуточные итоги для всех транзакций.
- Откройте файл Excel, который вы скачали.
- Щелкните вкладку ВСТАВИТЬ на ленте.
- Вы получите следующее окно
- Нажмите кнопку выбора таблицы / диапазона, как показано на изображении выше.
- Вы получите следующее мини-окно
- Щелкните в ячейке с адресом A1
- Нажмите Ctrl + A на клавиатуре, чтобы выбрать все ячейки данных.
- Ваше мини-окно, показанное теперь, выглядит следующим образом
- Нажмите кнопку «Закрыть», чтобы вернуться в окно параметров.
- Нажмите кнопку ОК
- Выберите следующие поля
- Название компании
- наименование товара
- Цена за единицу
- Количество
- Промежуточный итог
- Теперь ваш рабочий лист должен выглядеть следующим образом
- Обратите внимание, что приведенные выше данные сгруппированы по названию компании-клиента, названию продукта, цене за единицу, сумме количеств и сумме промежуточных итогов.
- Обратите внимание на раскрывающуюся кнопку рядом с надписью Rows Labels. Эта кнопка позволяет нам сортировать / фильтровать наши данные. Предположим, нас интересует только Альфредс Футтеркисте.
- Щелкните раскрывающийся список "Ярлыки строк", как показано ниже.
- Снимите галочку с (Выбрать все)
- Выберите Альфредса Футтеркисте
- Нажмите кнопку ОК
- Вы получите следующие данные
2-мерные сводные таблицы
Двумерная сводная таблица - это таблица, в которой есть поля как в строках, так и в столбцах. Допустим, мы хотим перечислить имена сотрудников в виде строк и использовать столбцы для представления имен клиентов и заполнить ячейки общими продажами.
- Активируйте технический паспорт продаж
- Перейдите на вкладку ВСТАВИТЬ.
- Нажмите кнопку "Сводная диаграмма и таблица".
- Выберите все данные. Теперь Excel должен запомнить предыдущий диапазон, поэтому вам просто нужно нажать кнопку ОК.
- Новый лист будет создан с помощью инструментов сводной таблицы.
- Выберите поля, как показано на изображении ниже.
Ваша сводная таблица теперь будет выглядеть следующим образом
Визуализация данных сводной таблицы с помощью диаграмм
В Excel 2013 вам не нужно создавать диаграммы вручную. Excel будет создавать диаграммы для вас по мере создания сводных таблиц, изменения агрегатных функций, применения фильтров и т. Д.
Приведенная ниже диаграмма была автоматически создана для нас на основе простого упражнения со сводной диаграммой, которое отфильтровало данные только для Альфреда Футтеркисте.
Резюме
Сводные таблицы и диаграммы позволяют нам обобщать и анализировать большие наборы данных. Мы можем использовать агрегатные функции, такие как SUM, MINIMUM, MAXIMUM, AVERAGE и т. Д., Диаграммы автоматически создаются и обновляются для нас в Excel по мере того, как мы работаем с данными.