Как создать сводную таблицу.
Многие думают, что создание отчетов с использованием сводных таблиц для чайников — сложный и трудоемкий процесс. Но это не так! Microsoft совершенствует эту технологию в течение многих лет, и в современных версиях Excel они очень доступны и невероятно быстры.
Фактически, вы можете сделать это за пару минут. Для вас: небольшой туториал в виде подробной инструкции:
Организуйте свои исходные данные
Перед созданием сводного отчета сгруппируйте данные в строки и столбцы, а затем преобразуйте диапазон данных в таблицу. Для этого выделите все используемые ячейки, перейдите на вкладку меню «Главная» и нажмите «Форматировать как таблицу».
Использование интеллектуальной таблицы в качестве входных данных дает вам большое преимущество: диапазон данных становится динамическим. Это означает, что он будет автоматически расширяться или сжиматься при добавлении или удалении записей. Таким образом, вам не нужно беспокоиться о том, что самая свежая информация не будет включена в коллекцию.
Полезные советы:
- Добавьте уникальные и содержательные заголовки в свои столбцы, которые позже станут именами полей.
- Убедитесь, что исходная таблица не содержит пустых и промежуточных строк или столбцов.
- Чтобы упростить работу, вы можете дать исходной таблице уникальное имя, введя его в поле «Имя» в правом верхнем углу.
Создаем и размещаем макет
Выберите любую ячейку в исходных данных, а затем перейдите на вкладку «Вставка»> «Сводная таблица .
Откроется окно «Создать….» Убедитесь, что в поле «Диапазон» указан правильный источник данных. Затем выберите место для хранилища:
- При выборе нового листа он будет помещен на новый лист, начиная с ячейки A1.
- Выбор существующего листа поместит его в указанное положение на существующем листе. В поле «Диапазон» выберите первую ячейку (т. Е. Верхнюю левую), в которой вы хотите разместить таблицу.
Нажатие OK создает пустой макет без номеров в целевом местоположении, который будет выглядеть следующим образом:
Полезные советы:
- В большинстве случаев имеет смысл разместить его на отдельном листе. Это особенно рекомендуется новичкам.
- Если вы получаете информацию из другой таблицы или книги, укажите их имена, используя следующий синтаксис: [workbook_name] sheet_name! Например, [Book1.xlsx] Sheet1! $ A $ 1: $ E $ 50. Конечно, вы не можете написать все вручную, а просто выберите диапазон ячеек в другой книге с помощью мыши.
- Может быть полезно создать таблицу и диаграмму одновременно. Для этого в Excel 2016 и 2013 перейдите на вкладку «Вставка», щелкните стрелку под кнопкой «Сводная диаграмма», затем щелкните «Диаграмма и таблица». В версиях 2010 и 2007 щелкните стрелку под сводной таблицей, а затем выберите «Сводная диаграмма.
Макетная организация.
Область, в которой вы работаете с полями макета, называется списком полей. Он расположен в правой части рабочего листа и разделен на заголовок и основной раздел:
- Раздел «Поле» содержит названия метрик, которые вы можете добавить. Они соответствуют именам столбцов в исходных данных.
- Раздел «Макет» содержит области «Фильтры», «Столбцы», «Строки» и «Значения». Здесь вы можете расположить поля в желаемом порядке.
Изменения, внесенные в эти разделы, немедленно применяются к рабочему листу.
Как добавить поле
Чтобы иметь возможность добавить поле в нужную область, установите флажок рядом с его названием.
По умолчанию Microsoft Excel добавляет поля в раздел «Макет» следующим образом:
- Нечисловые числа добавляются в область Строки>;
- Цифры добавляются в диапазон>;
- Дата и время добавляются в область «Столбцы.
Как удалить поле из сводной таблицы?
Чтобы удалить любое поле, вы можете сделать следующее:
- Снимите флажок рядом с ним, который вы установили ранее.
- Щелкните поле правой кнопкой мыши и выберите Удалить……».
Это более простой и интуитивно понятный способ удалить поле. Перейдите в макет таблицы, щелкните по ненужному элементу и перетащите его за пределы макета. Как только вы вынете его из кадра, рядом со значком появится гатактический крест. Отпустите кнопку мыши и наблюдайте за мгновенным изменением внешнего вида вашего стола.
Как упорядочить поля?
Вы можете изменить порядок показателей тремя способами:
Перетащите рамку между 4 областями раздела с помощью мыши. Либо щелкните и удерживайте его имя в разделе «Поле», а затем перетащите его в нужную область в разделе «Макет». Это удалит его из текущей области и поместит в новое место.
Щелкните правой кнопкой мыши имя в разделе «Поле» и выберите область, в которую вы хотите его добавить:
Щелкните поле под макетом, чтобы выбрать его. Это сразу покажет доступные варианты:
Любые внесенные изменения применяются немедленно.
Что ж, если вы поняли, что сделали что-то не так, не забывайте, что есть «волшебная» комбинация клавиш CTRL + Z, которая отменяет сделанные изменения (если вы не сохранили их, нажав соответствующую клавишу).
Выберите функцию для значений (необязательно)
По умолчанию Microsoft Excel использует функцию Sum для числовых мер, введенных в области значений. Когда вы вводите в эту область нечисловые (текст, дата или логическое значение) или значения NULL, применяется функция Count».
Но, конечно, вы можете выбрать другой метод расчета. Щелкните правой кнопкой мыши поле значения, которое вы хотите изменить, выберите Параметры поля значения, а затем нужную функцию.
Я думаю, что названия операций говорят сами за себя, и здесь не нужно никаких дополнительных пояснений. В крайнем случае попробуйте сами разные варианты.
Здесь вы можете изменить его название на более приятное и понятное для вас. В конце концов, он появляется в таблице, и поэтому он должен отображаться соответственно.
В Excel 2010 и более ранних версиях параметр «Суммировать значения по» также доступен на ленте на вкладке «Параметры» в группе «Вычисления».
Используем различные вычисления в полях значения (необязательно)
Еще одна полезная функция позволяет вам представлять значения по-разному, например, отображать итоги в виде процентов или ранжировать значения от самого низкого до самого высокого и наоборот.
Это называется дополнительными вычислениями. Доступ к нему можно получить, открыв вкладку «Параметры…», как описано выше.
Запрос. Функция расширенных вычислений может быть особенно полезна, когда вы добавляете одно и то же поле более одного раза и одновременно показываете, как в нашем примере, общие продажи и продажи в процентах от общей суммы. Согласитесь, на создание такой таблицы по обычным формулам уйдет много времени. И вот — пара минут работы!
На этом процесс создания завершен. Пришло время немного поэкспериментировать, чтобы выбрать макет, который лучше всего подходит для вашего набора данных.
Советы
- Прежде чем приступить к редактированию сводной таблицы, убедитесь, что вы сделали резервную копию исходного файла Excel.
Предупреждения
- Не забывайте сохранять результаты проделанной работы.
Работа со сводными таблицами в Excel
Редактировать существующую сводную таблицу также легко. Посмотрим, как желания режиссера легко претворяются в жизнь.
Мы заменяем выручку прибылью.
Обмен товарами и областями также осуществляется путем перетаскивания мышью.
Есть несколько инструментов для фильтрации сводных таблиц. В этом случае мы просто вставим поле «Менеджер» в область фильтра.
Все это длилось несколько секунд. Вот как работать со сводными таблицами. Конечно, не все задачи такие рутинные. Есть также те, кому нужно использовать более сложный метод агрегирования, добавлять вычисляемые поля, условное форматирование и т.д., но мы поговорим об этом в другой раз.
Источник данных сводной таблицы Excel
Для правильной работы со сводными таблицами исходные данные должны соответствовать ряду требований. Обязательным условием является наличие названий над каждым полем (столбцом), по которым эти поля будут идентифицироваться. Теперь несколько полезных советов.
1. Лучшим форматом данных является таблица Excel. Это хорошо, так как каждое поле имеет имя и при добавлении новых строк они автоматически включаются в сводную таблицу.
2. Избегайте повторения групп в столбцах. Например, все даты должны быть в одном поле, а не разбиты по месяцам на отдельные столбцы.
3. Удалите пробелы и пустые ячейки, иначе эта строка может быть не проанализирована.
4. Примените правильное форматирование к полям. Числа должны быть в числовом формате, даты должны быть указаны. В противном случае возникнут проблемы с группировкой и математической обработкой. Но тут вам поможет Excel, т.к достаточно хорошо определяет сам формат данных.
В общем, требований немного, но вы должны их знать.
Вычисляемое поле. Алгоритм расчета
Для каждого месяца у нас есть только одно значение для фактических продаж (столбец «Продажи») и плана. Вычисляемое поле PercentageFulfillment возвращает значение, равное их соотношению. Например, на январь 2012 года это 50,19% (было продано 36 992,22 при плане 73 697,76). 36992,22 / 73697,76 = 0,5019 (см. Строку 10 на листе исходной таблицы).
Теперь проверим итоги по месяцам. Итого за январь — 93,00%. Как возникает эта ценность?
Сначала программа рассчитала СУММ продаж за январь за все годы, затем рассчитала СУММ всех плановых значений. Деление одного на другое дает 93,00%. Вы можете убедиться в этом, выполнив вычисления самостоятельно (см. Строку 10 на листе сводной таблицы, столбцы H: J).
Это одно из ограничений вычисляемого поля: итоги рассчитываются только на основе суммы.
Аналогично производится расчет итогов по столбцам: находится сумма продаж и план по годам, затем рассчитывается их соотношение.
Если бы в исходной таблице было несколько продаж и целевых сумм для каждого месяца, расчет был бы аналогичен итоговому.
Чтобы обойти это ограничение и рассчитать, например, средний% реализации плана за все месяцы января, вам нужно будет покинуть поле Рассчитано. Создайте новый столбец в исходной таблице — Отношение продаж к плану на каждый месяц (см. Рабочий лист исходной таблицы 2). Затем создайте на его основе еще одну сводную таблицу. В окне «Параметры поля значения» установите «Среднее.
В итоговом столбце теперь будет отображаться средний процент выполненного плана.
Изменяем и удаляем Вычисляемое поле
Вызовите тот же диалог, который мы использовали для создания вычисляемого поля. В раскрывающемся списке выберите необходимое поле. Отображается его формула, которую вы можете редактировать, а также имя этого вычисляемого поля.
Вы также можете удалить это поле там.
Подготовка исходной таблицы
Начнем с требований к исходному столу.
- у каждого столбца должен быть заголовок;
- значения только в одном формате должны быть введены в каждый столбец (например, столбец «Дата доставки» должен содержать все значения только в формате даты >;>
- в таблице не должно быть полностью пустых строк и столбцов;
- в ячейки необходимо вводить «атомарные» значения, т.е только те, которые нельзя разделить на разные столбцы. Например, вы не можете ввести адрес в ячейку в формате: «Город, название улицы, № дома». Вам необходимо создать 3 столбца с одинаковым названием, иначе сводная таблица будет работать неэффективно (если вам нужна информация, например, в разрезе города);
- избегайте таблиц с «неправильной» структурой (см рисунок ниже).
Вместо создания повторяющихся столбцов (область 1, область 2,…), где будет много пустых ячеек, переосмыслите структуру таблицы, как показано на рисунке выше (все значения объема продаж должны быть в одном столбце и не распределяться по нескольким столбцам. Для этого вам может потребоваться вести более подробные записи (см рисунок выше), а не указывать общие продажи по каждому региону).
Более подробные советы по созданию таблиц см. В одноименной статье «Советы по созданию таблиц .
Процесс создания сводной таблицы будет несколько проще, если исходная таблица преобразована в формат EXCEL 2007 (Insert / Tables / Table). Для этого сначала приведите исходную таблицу в соответствии с вышеуказанными требованиями, затем выберите любую ячейку в таблице и вызовите окно меню Вставка / Таблицы / Таблица. Все поля в окне заполнятся автоматически, нажмите ОК.
Создание таблицы в формате EXCEL 2007 добавляет новые возможности:
- когда в таблицу добавляются новые значения, в таблицу автоматически добавляются новые строки;
- при создании таблицы применяется форматирование, применяется фильтр к заголовкам, появляется возможность автоматически создавать итоговую строку, сортировать данные и т д.;
- таблице автоматически присваивается имя .
В качестве отправной точки будем использовать таблицу в формате EXCEL 2007, содержащую информацию о продажах партий товара. Строки таблицы содержат данные о доставке партии товара и ее продажах.
В таблице есть столбцы:
- Продукт: название партии продукта, например «Апельсины >»;
- Группа: группа товаров, например «Апельсины» входит в группу «Фрукты >»;
- Поставщик — компания, поставляющая Товар, Поставщик может поставлять несколько Групп Товаров;
- Дата доставки — дата доставки Товара Поставщиком;
- Регион реализации — регион, в котором была реализована отгрузка Товара;
- Продажа — Стоимость, по которой товар может быть продан;
- Продажа — срок фактической продажи Товара в Регионе (в днях);
- Прибыль — знак, указывающий, была ли получена прибыль от проданной партии товара.
Используя Диспетчер имен (Формула / Определенные имена / Диспетчер имен), мы исправим имя таблицы на «Source_table ».
Создание Сводной таблицы
Мы создадим сводную таблицу для решения следующей задачи: «Рассчитать общий объем продаж для каждого продукта».
Имея исходную таблицу в формате EXCEL 2007, для создания сводной таблицы просто выберите любую ячейку исходной таблицы и выберите пункт Сводная таблица в меню Работа с таблицами / Дизайн / Инструменты .
В появившемся окне нажмите ОК, принимая, что сводная таблица размещена на отдельном листе.
Пустая сводная таблица и список полей, расположенных справа от листа, будут отображаться на отдельном листе (отображаются только тогда, когда активная ячейка находится в диапазоне ячеек сводной таблицы).
Структуру сводной таблицы можно резюмировать следующим образом:
Сначала заполните раздел «Имена линий». Поскольку необходимо определить объем продаж для каждого продукта, названия продуктов должны быть введены в строки сводной таблицы. Для этого поставьте галочку в Списке полей рядом с полем Продукт (поле и столбец являются синонимами).
Почему? Если ячейки в столбце «Продукт» имеют текстовый формат, они автоматически перейдут в область «Имена строк» в списке полей. Очевидно, что поле «Статья» при необходимости можно переместить в другую область списка полей. Обратите внимание, что названия продуктов будут автоматически отсортированы от А до Я (см. Изменение порядка ниже).
Теперь поставим галочку в списке полей рядом с полем «Продажи.
Почему? Если ячейки в столбце «Продажи» имеют числовой формат, они автоматически перейдут в раздел «Значения списка полей.
С помощью нескольких щелчков мышью (точнее шести) мы создали отчет о продажах для каждого продукта. Такого же результата можно было добиться с помощью формул (см. Статью Выбор уникальных значений с суммированием по соседнему столбцу). Например, если вам нужно определить объемы продаж для каждого поставщика, снимите флажок в списке полей рядом с полем «Товар» и установите флажок рядом с полем «Поставщик.
Детализация данных Сводной таблицы
Если у вас есть вопросы о том, какие данные исходной таблицы использовались для расчета определенных значений сводной таблицы, просто дважды щелкните определенное значение в сводной таблице, чтобы создать отдельный лист с выбранными строками из исходной таблицы. Например, давайте посмотрим, какие записи использовались для суммирования продаж продукта «Апельсины». Для этого дважды щелкните значение 646720. Будет создан отдельный лист, содержащий только строки исходной таблицы, относящиеся к продукту «Апельсины».
Обновление Сводной таблицы
Если после создания сводной таблицы в исходную таблицу были добавлены новые записи (строки), эти данные не будут автоматически учтены в сводной таблице. Чтобы обновить сводную таблицу, выделите одну из ее ячеек и выберите пункт меню: меню Работа со сводными таблицами / Параметры / Данные / Обновить. Такой же результат можно получить через контекстное меню: выберите любую ячейку сводной таблицы, откройте контекстное меню правой кнопкой мыши и выберите пункт «Обновить .
Удаление Сводной таблицы
Есть несколько способов удалить сводную таблицу. Первый — просто удалить лист со сводной таблицей (если нет других полезных данных, например исходной таблицы). Второй способ — удалить только саму сводную таблицу: выберите любую ячейку сводной таблицы, нажмите CTRL + A (будет выделена вся сводная таблица), нажмите клавишу «Удалить .
Изменение функции итогов
При создании сводной таблицы сгруппированные значения по умолчанию суммируются. Фактически, при решении задачи нахождения объемов продаж для каждого Продукта нас не интересовала функция итогов: суммировались все Продажи, относящиеся к Продукту. Например, если вам нужно подсчитать количество проданных лотов для каждого Продукта, вам нужно изменить функцию итогов. Для этого в сводной таблице выберите любое значение поля «Продажи», откройте контекстное меню правой кнопкой мыши и выберите пункт «Итого по / количеству .
Изменение порядка сортировки
Теперь давайте немного отредактируем наш сводный отчет. Прежде всего, давайте изменим порядок названий Продуктов: отсортируем их в обратном порядке от Z до A. Для этого воспользуйтесь раскрывающимся списком вверху столбца, содержащего названия Продуктов, войдите в меню и выберите Сортировать от Я до А .
Теперь предположим, что продукт бублика — самый важный продукт, поэтому его нужно отображать в первой строке. Для этого выделите ячейку со значением «Бублики» и поместите курсор на край ячейки (курсор должен иметь форму креста со стрелками).
Затем, нажав левую кнопку мыши, перетащите ячейку в самую верхнюю позицию в списке, чуть ниже заголовка столбца.
После отпускания кнопки мыши значение Баранки переместится на самую верхнюю позицию в списке.
Изменение формата числовых значений
Теперь добавим разделитель групп цифр для числовых значений (поле Продажи). Для этого выберите любое значение в поле «Продажи», откройте контекстное меню правой кнопкой мыши и выберите пункт меню «Числовой формат …
В появившемся окне выберите формат числа и поставьте галочку в поле Разделитель группы цифр .
Добавление новых полей
Предположим, вы хотите подготовить отчет о продажах продуктов, но с разбивкой по регионам продаж. Для этого добавьте поле «Область продаж», установив соответствующий флажок в списке полей. Поле «Регион продаж» добавляется в область «Имена строк» в списке полей (в поле «Товар»). Изменив порядок полей товаров и региона продаж в области названий строк в списке полей, мы получим следующий результат.
Выбрав любое название продукта и щелкнув пункт меню «Работа со сводными таблицами» / «Параметры» / «Активное поле» / «Свернуть все поле», вы можете свернуть сводную таблицу, чтобы отобразить только продажи по регионам.
