Многие думают, что создание отчетов с использованием сводных таблиц для чайников — сложный и трудоемкий процесс. Но это не так! Microsoft совершенствует эту технологию в течение многих лет, и в современных версиях Excel они очень доступны и невероятно быстры.
Фактически, вы можете сделать это за пару минут. Для вас: небольшой туториал в виде подробной инструкции:
Перед созданием сводного отчета сгруппируйте данные в строки и столбцы, а затем преобразуйте диапазон данных в таблицу. Для этого выделите все используемые ячейки, перейдите на вкладку меню «Главная» и нажмите «Форматировать как таблицу».
Использование интеллектуальной таблицы в качестве входных данных дает вам большое преимущество: диапазон данных становится динамическим. Это означает, что он будет автоматически расширяться или сжиматься при добавлении или удалении записей. Таким образом, вам не нужно беспокоиться о том, что самая свежая информация не будет включена в коллекцию.
Полезные советы:
Выберите любую ячейку в исходных данных, а затем перейдите на вкладку «Вставка»> «Сводная таблица .
Откроется окно «Создать….» Убедитесь, что в поле «Диапазон» указан правильный источник данных. Затем выберите место для хранилища:
Нажатие OK создает пустой макет без номеров в целевом местоположении, который будет выглядеть следующим образом:
Область, в которой вы работаете с полями макета, называется списком полей. Он расположен в правой части рабочего листа и разделен на заголовок и основной раздел:
Изменения, внесенные в эти разделы, немедленно применяются к рабочему листу.
Чтобы иметь возможность добавить поле в нужную область, установите флажок рядом с его названием.
По умолчанию Microsoft Excel добавляет поля в раздел «Макет» следующим образом:
Чтобы удалить любое поле, вы можете сделать следующее:
Это более простой и интуитивно понятный способ удалить поле. Перейдите в макет таблицы, щелкните по ненужному элементу и перетащите его за пределы макета. Как только вы вынете его из кадра, рядом со значком появится гатактический крест. Отпустите кнопку мыши и наблюдайте за мгновенным изменением внешнего вида вашего стола.
Вы можете изменить порядок показателей тремя способами:
Перетащите рамку между 4 областями раздела с помощью мыши. Либо щелкните и удерживайте его имя в разделе «Поле», а затем перетащите его в нужную область в разделе «Макет». Это удалит его из текущей области и поместит в новое место.
Щелкните правой кнопкой мыши имя в разделе «Поле» и выберите область, в которую вы хотите его добавить:
Щелкните поле под макетом, чтобы выбрать его. Это сразу покажет доступные варианты:
Любые внесенные изменения применяются немедленно.
Что ж, если вы поняли, что сделали что-то не так, не забывайте, что есть «волшебная» комбинация клавиш CTRL + Z, которая отменяет сделанные изменения (если вы не сохранили их, нажав соответствующую клавишу).
По умолчанию Microsoft Excel использует функцию Sum для числовых мер, введенных в области значений. Когда вы вводите в эту область нечисловые (текст, дата или логическое значение) или значения NULL, применяется функция Count».
Но, конечно, вы можете выбрать другой метод расчета. Щелкните правой кнопкой мыши поле значения, которое вы хотите изменить, выберите Параметры поля значения, а затем нужную функцию.
Я думаю, что названия операций говорят сами за себя, и здесь не нужно никаких дополнительных пояснений. В крайнем случае попробуйте сами разные варианты.
Здесь вы можете изменить его название на более приятное и понятное для вас. В конце концов, он появляется в таблице, и поэтому он должен отображаться соответственно.
В Excel 2010 и более ранних версиях параметр «Суммировать значения по» также доступен на ленте на вкладке «Параметры» в группе «Вычисления».
Еще одна полезная функция позволяет вам представлять значения по-разному, например, отображать итоги в виде процентов или ранжировать значения от самого низкого до самого высокого и наоборот.
Это называется дополнительными вычислениями. Доступ к нему можно получить, открыв вкладку «Параметры…», как описано выше.
Запрос. Функция расширенных вычислений может быть особенно полезна, когда вы добавляете одно и то же поле более одного раза и одновременно показываете, как в нашем примере, общие продажи и продажи в процентах от общей суммы. Согласитесь, на создание такой таблицы по обычным формулам уйдет много времени. И вот — пара минут работы!
На этом процесс создания завершен. Пришло время немного поэкспериментировать, чтобы выбрать макет, который лучше всего подходит для вашего набора данных.
Редактировать существующую сводную таблицу также легко. Посмотрим, как желания режиссера легко претворяются в жизнь.
Мы заменяем выручку прибылью.
Обмен товарами и областями также осуществляется путем перетаскивания мышью.
Есть несколько инструментов для фильтрации сводных таблиц. В этом случае мы просто вставим поле «Менеджер» в область фильтра.
Все это длилось несколько секунд. Вот как работать со сводными таблицами. Конечно, не все задачи такие рутинные. Есть также те, кому нужно использовать более сложный метод агрегирования, добавлять вычисляемые поля, условное форматирование и т.д., но мы поговорим об этом в другой раз.
Для правильной работы со сводными таблицами исходные данные должны соответствовать ряду требований. Обязательным условием является наличие названий над каждым полем (столбцом), по которым эти поля будут идентифицироваться. Теперь несколько полезных советов.
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). Затем создайте на его основе еще одну сводную таблицу. В окне «Параметры поля значения» установите «Среднее.
В итоговом столбце теперь будет отображаться средний процент выполненного плана.
Вызовите тот же диалог, который мы использовали для создания вычисляемого поля. В раскрывающемся списке выберите необходимое поле. Отображается его формула, которую вы можете редактировать, а также имя этого вычисляемого поля.
Вы также можете удалить это поле там.
Начнем с требований к исходному столу.
Вместо создания повторяющихся столбцов (область 1, область 2,…), где будет много пустых ячеек, переосмыслите структуру таблицы, как показано на рисунке выше (все значения объема продаж должны быть в одном столбце и не распределяться по нескольким столбцам. Для этого вам может потребоваться вести более подробные записи (см рисунок выше), а не указывать общие продажи по каждому региону).
Более подробные советы по созданию таблиц см. В одноименной статье «Советы по созданию таблиц .
Процесс создания сводной таблицы будет несколько проще, если исходная таблица преобразована в формат EXCEL 2007 (Insert / Tables / Table). Для этого сначала приведите исходную таблицу в соответствии с вышеуказанными требованиями, затем выберите любую ячейку в таблице и вызовите окно меню Вставка / Таблицы / Таблица. Все поля в окне заполнятся автоматически, нажмите ОК.
Создание таблицы в формате EXCEL 2007 добавляет новые возможности:
В качестве отправной точки будем использовать таблицу в формате EXCEL 2007, содержащую информацию о продажах партий товара. Строки таблицы содержат данные о доставке партии товара и ее продажах.
В таблице есть столбцы:
Используя Диспетчер имен (Формула / Определенные имена / Диспетчер имен), мы исправим имя таблицы на «Source_table ».
Мы создадим сводную таблицу для решения следующей задачи: «Рассчитать общий объем продаж для каждого продукта».
Имея исходную таблицу в формате EXCEL 2007, для создания сводной таблицы просто выберите любую ячейку исходной таблицы и выберите пункт Сводная таблица в меню Работа с таблицами / Дизайн / Инструменты .
В появившемся окне нажмите ОК, принимая, что сводная таблица размещена на отдельном листе.
Пустая сводная таблица и список полей, расположенных справа от листа, будут отображаться на отдельном листе (отображаются только тогда, когда активная ячейка находится в диапазоне ячеек сводной таблицы).
Структуру сводной таблицы можно резюмировать следующим образом:
Сначала заполните раздел «Имена линий». Поскольку необходимо определить объем продаж для каждого продукта, названия продуктов должны быть введены в строки сводной таблицы. Для этого поставьте галочку в Списке полей рядом с полем Продукт (поле и столбец являются синонимами).
Почему? Если ячейки в столбце «Продукт» имеют текстовый формат, они автоматически перейдут в область «Имена строк» в списке полей. Очевидно, что поле «Статья» при необходимости можно переместить в другую область списка полей. Обратите внимание, что названия продуктов будут автоматически отсортированы от А до Я (см. Изменение порядка ниже).
Теперь поставим галочку в списке полей рядом с полем «Продажи.
Почему? Если ячейки в столбце «Продажи» имеют числовой формат, они автоматически перейдут в раздел «Значения списка полей.
С помощью нескольких щелчков мышью (точнее шести) мы создали отчет о продажах для каждого продукта. Такого же результата можно было добиться с помощью формул (см. Статью Выбор уникальных значений с суммированием по соседнему столбцу). Например, если вам нужно определить объемы продаж для каждого поставщика, снимите флажок в списке полей рядом с полем «Товар» и установите флажок рядом с полем «Поставщик.
Если у вас есть вопросы о том, какие данные исходной таблицы использовались для расчета определенных значений сводной таблицы, просто дважды щелкните определенное значение в сводной таблице, чтобы создать отдельный лист с выбранными строками из исходной таблицы. Например, давайте посмотрим, какие записи использовались для суммирования продаж продукта «Апельсины». Для этого дважды щелкните значение 646720. Будет создан отдельный лист, содержащий только строки исходной таблицы, относящиеся к продукту «Апельсины».
Если после создания сводной таблицы в исходную таблицу были добавлены новые записи (строки), эти данные не будут автоматически учтены в сводной таблице. Чтобы обновить сводную таблицу, выделите одну из ее ячеек и выберите пункт меню: меню Работа со сводными таблицами / Параметры / Данные / Обновить. Такой же результат можно получить через контекстное меню: выберите любую ячейку сводной таблицы, откройте контекстное меню правой кнопкой мыши и выберите пункт «Обновить .
Есть несколько способов удалить сводную таблицу. Первый — просто удалить лист со сводной таблицей (если нет других полезных данных, например исходной таблицы). Второй способ — удалить только саму сводную таблицу: выберите любую ячейку сводной таблицы, нажмите CTRL + A (будет выделена вся сводная таблица), нажмите клавишу «Удалить .
При создании сводной таблицы сгруппированные значения по умолчанию суммируются. Фактически, при решении задачи нахождения объемов продаж для каждого Продукта нас не интересовала функция итогов: суммировались все Продажи, относящиеся к Продукту. Например, если вам нужно подсчитать количество проданных лотов для каждого Продукта, вам нужно изменить функцию итогов. Для этого в сводной таблице выберите любое значение поля «Продажи», откройте контекстное меню правой кнопкой мыши и выберите пункт «Итого по / количеству .
Изменение порядка сортировки
Теперь давайте немного отредактируем наш сводный отчет. Прежде всего, давайте изменим порядок названий Продуктов: отсортируем их в обратном порядке от Z до A. Для этого воспользуйтесь раскрывающимся списком вверху столбца, содержащего названия Продуктов, войдите в меню и выберите Сортировать от Я до А .
Теперь предположим, что продукт бублика — самый важный продукт, поэтому его нужно отображать в первой строке. Для этого выделите ячейку со значением «Бублики» и поместите курсор на край ячейки (курсор должен иметь форму креста со стрелками).
Затем, нажав левую кнопку мыши, перетащите ячейку в самую верхнюю позицию в списке, чуть ниже заголовка столбца.
После отпускания кнопки мыши значение Баранки переместится на самую верхнюю позицию в списке.
Теперь добавим разделитель групп цифр для числовых значений (поле Продажи). Для этого выберите любое значение в поле «Продажи», откройте контекстное меню правой кнопкой мыши и выберите пункт меню «Числовой формат …
В появившемся окне выберите формат числа и поставьте галочку в поле Разделитель группы цифр .
Предположим, вы хотите подготовить отчет о продажах продуктов, но с разбивкой по регионам продаж. Для этого добавьте поле «Область продаж», установив соответствующий флажок в списке полей. Поле «Регион продаж» добавляется в область «Имена строк» в списке полей (в поле «Товар»). Изменив порядок полей товаров и региона продаж в области названий строк в списке полей, мы получим следующий результат.
Выбрав любое название продукта и щелкнув пункт меню «Работа со сводными таблицами» / «Параметры» / «Активное поле» / «Свернуть все поле», вы можете свернуть сводную таблицу, чтобы отобразить только продажи по регионам.
