Сейчас на простом примере мы научимся создавать сводные таблицы и познакомимся с некоторыми их возможностями.

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

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

Советую также преобразовывать исходный диапазон данных в таблицу (Главная - Форматировать как таблицу ). Тогда при добавлении или удалении строк и столбцов не придется менять ссылку на этот диапазон в сводном отчете.

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

Самостоятельное создание сводной таблицы с помощью конструктора

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

Важно! При выделении диапазона убедитесь, что выбрана таблица с заголовками, а так же что выбранный диапазон не включает строку итогов.

Так же укажите, где необходимо разместить отчет сводной таблицы: на новом листе или на уже существующий.

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

После формирования сводной таблицы в выбранном месте появиться область с ее названием. По умолчанию отчет называется СводнаяТаблица1 . Для начала работы необходимо щелкнуть левой клавшей мыши по данной области. В результате в правой части листа откроется макет сводной таблицы.

Макет сводной таблицы состоит из списка полей сводной таблицы, в котором перечислены все заголовки исходной таблицы и четырех областей: ФИЛЬТРЫ , КОЛОННЫ , СТРОКИ и ЗНАЧЕНИЯ .

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

В качестве примера давайте создадим сводный отчет, в котором посчитаем рыночную стоимость для каждой группы из исходной таблицы. Т.е. мы хотим, чтобы напротив группы стали строками сводного отчета. Значит нужно перетащить поле Группа в область строк.

Данную операцию можно сделать еще 2 способами:

• отметить флажок напротив поля Группа ;
• щелкнуть по полу Группа правой кнопкой мыши и выбрать Добавить в названия строк .

После добавления поля вы увидите список всех групп, которые есть в исходной таблице:

Теперь осталось добавить сумму по полю Рыночная стоимость. Для этого перенесем поле Рыночная стоимость в область значений. Желаемая таблица получена.

Чтобы укомплектовать данные в таблице наиболее удобным способом и всегда иметь возможность быстрого доступа к ним, попробуйте сделать сводную таблицу в программе Microsoft Office Excel. Составление такого типа таблицы займёт у вас пару минут, а её использование сохранит драгоценное время в дальнейшем.

Зайдите в программу Эксель. В шапке программы среди множества разделов найдите “Вставка” и кликните по ней. Вам понадобится инструмент “Сводная таблица”, если данные вами уже введены и отформатированы. Если же нет, приступите к вводу.

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

Теперь нажмите на окно “Сводная таблица”, в выпадающем списке выберите пункт с таким же названием. Обратите внимание: не нажмите по случайности на второй пункт с похожим названием, он отвечает за диаграммы.

Перед вами появится такое окно по созданию таблицы. Выберите в первой графе “Выбрать таблицу или диапазон”, чтобы вручную выделить область для создания таблицы. Во второй графе отметьте на каком именно листе требуется составить сводную таблицу: текущем или следующем.
Это особенно удобно, когда вы не уверены на сто процентов, что не будете менять ваши значения в ячейках. Вы просто создадите таблицу на следующем листе, а при исправлениях, сможете удалить её без потери информации.

Потяните курсором в области таблицы, откуда вы желаете захватить данные. Вы увидите, что в поле “Таблица или диапазон” появляются новые координаты. Нажмите “Ок”, как закончите выделение.

Ваша сводная таблица создана. Справа вы увидите отдельно каждый столбец вашей таблицы. Поставьте галочки, чтобы отобразить их все либо отдельно каждый.

Вы можете свободно перемещаться по своей сводной таблице, однако, исправления прямо в ячейках недопустимы. Учтите, что параметры форматирования сохранятся и полностью перенесутся в данную таблицу.
Значение суммы будет отображено отдельно под каждым столбцом автоматически, справа снизу вы увидите некоторые параметры для редактирования таблицы.

Теперь вы без труда можете создавать сводные таблицы в программе от Microsoft – Excel. В ней есть опции помещения данных из совершенно другой таблицы, даже на другом листе. А выделяя разные столбцы, вы имеете возможность комбинировать множество таблиц в одной.

Такой тип таблиц лучше использовать с очень объёмными и большими таблицами, так как вам не придётся вручную задавать значение суммы для каждого столбца.

Сводные таблицы необходимы для суммирования, анализа и представления данных, находящихся в «больших» исходных таблицах, в различных разрезах . Рассмотрим процесс создания несложных Сводных таблиц.

Сводные таблицы (Вставка/ Таблицы/ Сводная таблица ) могут пригодиться, если одновременно выполняются следующие условия:

• имеется исходная таблица с множеством строк (записей), речь идет о нескольких десятках и сотнях строк;
• необходимо провести анализ данных, который требует выборки (фильтрации) данных, их группировки (суммирования, подсчета) и представления данных в различных разрезах (подготовки отчетов);
• этот анализ затруднительно провести на основе исходной таблицы с использованием других стредств: (CTRL+SHIFT+L ), ;
• исходная таблица удовлетворяет определенным требованиям (см. ниже).

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

Подготовка исходной таблицы

Начнем с требований к исходной таблице.

• каждый столбец должен иметь заголовок;
• в каждый столбец должны вводиться значения только в одном формате (например, столбец «Дата поставки» должен содержать все значения только в формате Дата ; столбец «Поставщик» - названия компаний только в текстовом формате или можно вводить Код поставщика в числовом формате);
• в таблице должны отсутствовать полностью незаполненные строки и столбцы;
• в ячейки должны вводиться «атомарные» значения, т.е. только те, которые нельзя разнести в разные столбцы. Например, нельзя в одну ячейку вводить адрес в формате: «Город, Название улицы, дом №». Нужно создать 3 одноименных столбца, иначе Сводная таблица будет работать неэффективно (в случае, если Вам нужна информация, например, в разрезе города);
• избегайте таблиц с «неправильной» структурой (см. рисунок ниже).

Вместо того, чтобы плодить повторяющиеся столбцы (регион 1, регион 2, … ), в которых будут в изобилии незаполненные ячейки, переосмыслите структуру таблицы, как показано на рисунке выше (Все значения объемов продаж должны быть в одном столбце, а не размазаны по нескольким столбцам. Для того, чтобы это реализовать, возможно, потребуется вести более подробные записи (см. рисунок выше), а не указывать для каждого региона суммарные продажи).

Более детальные советы по построению таблиц изложены в одноименной статье .

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

Теперь поставим галочку в Списке полей у поля Продажи.

Т.к. ячейки столбца Продажи имеют числовой формат, то они автоматически попадут в раздел Списка полей Значения.

Несколькими кликами мыши (точнее шестью) мы создали отчет о Продажах по каждому Товару. Того же результата можно было достичь с использованием формул (см. статью ).
Если требуется, например, определить объемы продаж по каждому Поставщику, то для этого снимем галочку в Списке полей у поля Товар и поставим галочку у поля Поставщик.

Детализация данных Сводной таблицы

Если возникли вопросы о том, какие же данные из исходной таблицы были использованы для подсчета тех или иных значений Сводной таблицы , то достаточно двойного клика мышкой на конкретном значении в Сводной таблице , чтобы был создан отдельный лист с отобранными из исходной таблицей строками. Например, посмотрим какие записи были использованы для суммирования продаж Товара «Апельсины». Для этого дважды кликнем на значении 646720. Будет создан отдельный лист только со строками исходной таблицы относящихся к Товару «Апельсины».

Обновление Сводной таблицы

Если после создания Сводной таблицы в исходную таблицу добавлялись новые записи (строки), то эти данные не будут автоматически учтены в Сводной таблице . Чтобы обновить Сводную таблицу выделите любую ее ячейку и выберите пункт меню: меню Работа со сводными таблицами/ Параметры/ Данные/ Обновить . Того же результата можно добиться через контекстное меню: выделите любую ячейку Сводной таблицы Обновить .

Удаление Сводной таблицы

Удалить Сводную таблицу можно несколькими способами. Первый – просто удалить лист со Сводной таблицей (если на нем нет других полезных данных, например исходной таблицы). Второй способ - удалить только саму Сводную таблицу : выделите любую ячейку Сводной таблицы , нажмите CTRL + A (будет выделена вся Сводная таблица ), нажмите клавишу Delete .

Изменение функции итогов

При создании Сводной таблицы сгруппированные значения по умолчанию суммируются. Действительно, при решении задачи нахождения объемов продаж по каждому Товару, мы не заботились о функции итогов – все Продажи, относящиеся к одному Товару были просуммированы.
Если требуется, например, подсчитать количество проданных партий каждого Товара, то нужно изменить функцию итогов. Для этого в Сводной таблице Итоги по/ Количество .

Изменение порядка сортировки

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

Теперь предположим, что Товар Баранки – наиболее важный товар, поэтому его нужно выводить в первой строке. Для этого выделите ячейку со значением Баранки и установите курсор на границу ячейки (курсор должен принять вид креста со стрелками).

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

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

Изменение формата числовых значений

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

В появившемся окне выберите числовой формат и поставьте галочку флажка Разделитель групп разрядов .

Добавление новых полей

Предположим, что необходимо подготовить отчет о продажах Товаров, но с разбивкой по Регионам продажи. Для этого добавим поле Регион продажи, поставив соответствующую галочку в Списке полей. Поле Регион продажи будет добавлено в область Названия строк Списка полей (к полю Товар). Поменяв в области Названия строк Списка полей порядок следования полей Товар и Регион продажи, получим следующий результат.

Выделив любое название Товара и нажав пункт меню Работа со сводными таблицами/ Параметры/ Активное поле/ Свернуть все поле , можно свернуть Сводную таблицу , чтобы отобразить только продажи по Регионам.

Добавление столбцов

Добавление поля Регион продажи в область строк привело к тому, что Сводная таблица развернулась на 144 строки. Это не всегда удобно. Т.к. продажи осуществлялись только в 6 регионах, то поле Регион продажи имеет смысл разместить в области столбцов.

Сводная таблица примет следующий вид.

Меняем столбцы местами

Чтобы изменить порядок следования столбцов нужно взявшись за заголовок столбца в Сводной таблице перетащить его в нужное место.

Удаление полей

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

Другой способ – снять галочку напротив удаляемого поля в верхней части Списка полей. Но, в этом случае поле будет удалено сразу из всех областей Списка полей (если оно использовалось в нескольких областях).

Добавление фильтра

Предположим, что необходимо подготовить отчет о продажах Групп Товаров, причем его нужно сделать в 2-х вариантах: один для партий Товаров принесших прибыль, другой – для убыточных. Для этого:

• Сводной таблицы, нажмите пункт меню ;
• Ставим галочки в Списке полей у полей Группа, Продажи и Прибыль;
• Переносим поле Прибыль из области Названия строк Списка полей в область Фильтр отчета;

Вид получившейся Сводной таблицы должен быть таким:

Теперь воспользовавшись Выпадающим (раскрывающимся) списком в ячейке B1 (поле Прибыль) можно, например, построить отчет о продажах Групп Товаров, принесших прибыль.

После нажатия кнопки ОК будут выведены значения Продаж только прибыльных Партий.

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

Очистить фильтр можно через меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить фильтры .

Также данных доступен через выпадающий список в заголовках строк и столбцов Сводной таблицы.

Несколько итогов для одного поля

• Очистим ранее созданный отчет: выделите любое значение Сводной таблицы , нажмите пункт меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить все ;
• Поставьте галочки напротив полей Товар и Продажи в верхней части Списка полей. Поле Продажи будет автоматически помещено в область Значения;
• Перетащите мышкой еще одну копию поля Продажи в ту же область Значения. В Сводной таблице появится 2 столбца подсчитывающими суммы продаж;

• в Сводной таблице выделите любое значение поля Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/ Количество . Задача решена.

Отключаем строки итогов

Строку итогов можно отключить через меню: Работа со сводными таблицами/ Конструктор/ Макет/ Общие итоги . Не забудьте предварительно выделить любую ячейку Сводной таблицы .

Группируем числа и Даты

Предположим, что требуется подготовить отчет о сроках сбыта. В результате нужно получить следующую информацию: сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д. Для этого:

• Очистим ранее созданный отчет: выделите любое значение Сводной таблицы , нажмите пункт меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить все ;
• Поставьте галочку напротив поля Сбыт (срок фактической реализации Товара) в верхней части Списка полей. Поле Сбыт будет автоматически помещено в область Значения;
• выделите единственное значение поля Сбыт в Сводной таблице , вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/ Количество .
• Перетащите мышкой еще одну копию поля Сбыт в область Названия строк;

Теперь Сводная таблица показывает сколько партий Товара сбывалось за 5, 6, 7, … дней. Всего 66 строк. Сгруппируем значения с шагом 10. Для этого:

• Выделите одно значение Сводной таблицы в столбце Названия строк;
• В меню выберите пункт Группировка по полю ;
• Появившееся окно заполните, как показано на рисунке ниже;

• Нажмите ОК.

Теперь Сводная таблица показывает сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д.

Чтобы разгруппировать значения выберите пункт Разгруппировать в меню Работа со сводными таблицами/ Параметры/ Группировать .

Аналогичную группировку можно провести по полю Дата поставки. В этом случае окно Группировка по полю будет выглядеть так:

Теперь Сводная таблица показывает, сколько партий Товара поставлялось каждый месяц.

Условное форматирование ячеек Сводной таблицы

К ячейкам Сводной таблицы можно применить правила как и к ячейкам обычного диапазона.
Выделим, например, ячейки с 10 наибольшими объемами продаж. Для этого:

• Выделите все ячейки содержащие значения продаж;
• Выберите пункт меню Главная/ Стили/ Условное форматирование/ Правила отбора первых и последних значений/ 10 первых элементов ;
• Нажмите ОК.

Сводные таблицы – это один из мощнейших инструментов Excel. Они позволяют анализировать и подводить различные итоги для больших объёмов данных с помощью всего лишь нескольких щелчков мышью. В этой статье мы познакомимся со сводными таблицами, разберёмся, что они собой представляют, научимся создавать их и настраивать.

При написании данной статьи был использован Excel 2010. Концепция сводных таблиц почти не изменялась долгие годы, но способ их создания немного различен в каждой новой версии Excel. Если у Вас версия Excel не 2010 года, то будьте готовы, что снимки экранов в данной статье будут отличаться от того, что Вы увидите на своём экране.

Немного истории

На заре развития программ для создания электронных таблиц балом правил Lotus 1-2-3. Его превосходство было настолько полным, что усилия Microsoft, направленные на разработку собственного программного обеспечения (Excel), как альтернативы Lotus, казались пустой тратой времени. А теперь перенесёмся в 2010 год! Excel доминирует среди электронных таблиц более, чем Lotus кода-либо за всю свою историю, а число людей, которые до сих пор используют Lotus, стремится к нулю. Как это могло произойти? Что послужило причиной для такого драматического разворота событий?

Аналитики выделяют два основных фактора:

• Во-первых, компания Lotus решила, что эта новомодная GUI-платформа с названием Windows – это всего лишь мимолётное увлечение, которое долго не протянет. Они отказались создавать версию Lotus 1-2-3 для Windows (впрочем, только несколько лет), предсказывая, что DOS-версия их программного обеспечения – это всё, что когда-либо будет нужно потребителям. Microsoft, естественно, разработала Excel специально под Windows.
• Во-вторых, Microsoft разработала в Excel такой инструмент, как сводные таблицы, которого не было в Lotus 1-2-3. Сводные таблицы, эксклюзивная для Excel вещь, оказалась так ошеломительно полезна, что люди были склонны осваивать новый программный пакет Excel, а не продолжать работать в Lotus 1-2-3, в котором их не было.

Сводные таблицы вместе с недооценкой успеха Windows в целом, сыграли похоронный марш для Lotus 1-2-3 и положили начало успеху Microsoft Excel.

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

Итак, как же лучше охарактеризовать, что собой представляют сводные таблицы?

Говоря простым языком, сводные таблицы – это итоги каких-то данных, созданные для облегчения анализа этих данных. В отличие от созданных вручную итогов, сводные таблицы Excel интерактивны. После создания, Вы можете легко изменять их, если они не дали той картины, которую Вы надеялись получить. Всего лишь парой щелчков мышью итоги могут быть перевёрнуты таким образом, что заголовки столбцов становятся заголовками строк и наоборот. Со сводными таблицами можно проделать множество различных действий. Вместо того, чтобы пытаться описать на словах все возможности сводных таблиц, проще продемонстрировать это на практике…

Данные, которые Вы анализируете с помощью сводных таблиц, не могут быть какими попало. Это должны быть необработанные исходные данные, вроде какого-то списка. Например, это может быть список совершённых продаж в компании за последние шесть месяцев.

Посмотрите на данные, показанные на рисунке ниже:

Обратите внимание, что это не сырые исходные данные, поскольку для них уже подведены итоги. В ячейке B3 мы видим $30000, что, вероятно, является суммарным результатом, который сделал James Cook в январе. Где же тогда исходные данные? Откуда взялась цифра $30000? Где исходный список продаж, из которого этот итог за месяц был получен? Ясно, что кто-то совершил огромный труд по упорядочиванию и сортировке всех данных о продажах за последние шесть месяцев и превратил их в таблицу итогов, которую мы видим. Сколько, по-вашему, это заняло времени? Час? Десять часов?

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

Если вернуться к исходному списку продаж, то он выглядел бы примерно так:

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

Как создать сводную таблицу?

Для начала убедитесь, что у Вас есть какие-то исходные данные на листе Excel. Перечень финансовых операций – самое типичное, что встречается. На самом деле, это может быть перечень чего угодно: контактные данные сотрудников, коллекция компакт-дисков или данные о расходе топлива Вашей компании.

Итак, запускаем Excel… и загружаем такой список…

После того, как мы открыли этот список в Excel, можем приступить к созданию сводной таблицы.

Выделите любую ячейку из этого списка:

Затем на вкладке Insert (Вставка) выберите команду PivotTable (Сводная таблица):

Появится диалоговое окно Create PivotTable (Создание сводной таблицы) с двумя вопросами для Вас:

• Какие данные использовать для создания новой сводной таблицы?
• Куда поместить сводную таблицу?

Так как на предыдущем шаге мы уже выбрали одну из ячеек списка, то для создания сводной таблицы будет выделен весь список автоматически. Заметьте, что мы можем выбрать другой диапазон, другую таблицу и даже какой-нибудь внешний источник данных, например, таблицу базы данных Access или MS-SQL. К тому же нам необходимо выбрать, где разместить новую сводную таблицу: на новом листе или на одном из существующих. В данном примере мы выберем вариант – New Worksheet (На новый лист):

Excel создаст новый лист и разместит на нем пустую сводную таблицу:

Как только мы кликнем по любой ячейке в сводной таблице, появится ещё одно диалоговое окно: PivotTable Field List (Поля сводной таблицы).

Список полей в верхней части диалогового окна – это перечень всех заголовков из исходного списка. Четыре пустые области в нижней части экрана позволяют указать сводной таблице, как требуется обобщить данные. Пока эти области пусты, в таблице тоже ничего нет. Всё, что от нас требуется, это перетащить заголовки из верхней области в пустые области внизу. При этом автоматически формируется сводная таблица, в соответствии с нашими инструкциями. Если мы допустили ошибку, то можно удалить заголовки из нижней области либо перетащить другие им на замену.

Область Values (Значения), вероятно, самая важная из четырёх. То, какой заголовок помещён в эту область, определяет, по каким данным будут подводиться итоги (сумма, среднее, максимум, минимум и т.д.) Это, почти всегда, численные значения. Отличный кандидат на место в этой области – данные под заголовком Amount (Стоимость) нашей исходной таблицы. Перетащим этот заголовок в область Values (Значения):

Обратите внимание, что заголовок Amount теперь отмечен галочкой, а в области Values (Значения) появилась запись Sum of Amount (Сумма по полю Amount), указывающая на то, что столбец Amount просуммирован.

Если мы посмотрим на саму сводную таблицу, то увидим сумму всех значений из столбца Amount исходной таблицы.

Итак, наша первая сводная таблица создана! Удобно, но не особо впечатляет. Вероятно, мы хотим получить больше информации о наших данных, чем есть сейчас.

Обратимся к исходным данным и попробуем определить один или несколько столбцов, которые можно использовать, чтобы раздробить эту сумму. Например, мы можем сформировать нашу сводную таблицу таким образом, чтобы итоговая сумма продаж подсчитывалась для каждого продавца по отдельности. Т.е. в нашу сводную таблицу добавятся строки с именем каждого продавца компании и его итоговой суммой продаж. Чтобы достичь такого результата, достаточно перетащить заголовок Salesperson Row Labels (Строки):

Становится интересней! Наша сводная таблица начинает обретать форму…

Видите преимущества? За пару кликов мы создали таблицу, которую вручную пришлось бы создавать очень долго.

Что ещё мы можем сделать? Ну, в определённом смысле, наша сводная таблица уже готова. Мы создали полезную сводку по исходным данным. Уже получена важная информация! В оставшейся части статьи мы разберём некоторые способы создания более сложных сводных таблиц, а также узнаем, как их настраивать.

Настройка сводной таблицы

Во-первых, мы можем создать двумерную сводную таблицу. Сделаем это, используя заголовок столбца Payment Method (Способ оплаты). Просто перетащите заголовок Payment Method в область Column Labels (Колонны):

Получим результат:

Выглядит очень круто!

Теперь сделаем трёхмерную таблицу. Как может выглядеть такая таблица? Давайте посмотрим…

Перетащите заголовок Package (Комплекс) в область Report Filter (Фильтры):

Заметьте, где он оказался…

Это даёт нам возможность отфильтровать отчёт по признаку “Какой комплекс отдыха был оплачен”. Например, мы можем видеть разбивку по продавцам и по способам оплаты для всех комплексов или за пару щелчков мышью изменить вид сводной таблицы и показать такую же разбивку только для заказавших комплекс Sunseekers .

Итак, если Вы правильно это понимаете, то нашу сводную таблицу можно назвать трёхмерной. Продолжим настраивать…

Если вдруг выясняется, что в сводной таблице должны выводится только оплата чеком и кредитной картой (то есть безналичный расчёт), то мы можем отключить вывод заголовка Cash (Наличными). Для этого рядом с Column Labels нажмите стрелку вниз и в выпадающем меню снимите галочку с пункта Cash :

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

Форматирование сводных таблиц в Excel

Очевидно, что сводные таблицы – это очень мощный инструмент, но до сих пор результаты выглядят как-то незамысловато и скучно. Например, цифры, которые мы суммируем, не похожи на суммы в долларах – это просто какие-то цифры. Давайте это исправим.

Велик соблазн сделать привычные в такой ситуации действия и просто выделить всю таблицу (или весь лист) и использовать стандартные кнопки форматирования чисел на панели инструментов, чтобы задать нужный формат. Проблема такого подхода состоит в том, что если Вы когда-либо в будущем измените структуру сводной таблицы (а это случится с вероятностью 99%), то форматирование будет потеряно. Нам же нужен способ сделать его (почти) постоянным.

Во-первых, найдём запись Sum of Amount в области Values (Значения) и кликнем по ней. В появившемся меню выберем пункт Value Field Settings (Параметры полей значений):

Появится диалоговое окно Value Field Settings (Параметры поля значений).

Нажмите кнопку Number Format (Числовой формат), откроется диалоговое окно Format Cells (Формат ячеек):

Из списка Category (Числовые форматы) выберите Accounting (Финансовый) и число десятичных знаков установите равным нулю. Теперь несколько раз нажмите ОК , чтобы вернуться назад к нашей сводной таблице.

Как видите, числа оказались отформатированы как суммы в долларах.

Раз уж мы занялись форматированием, давайте настроим формат для всей сводной таблицы. Есть несколько способов сделать это. Используем тот, что попроще…

Откройте вкладку PivotTable Tools: Design (Работа со сводными таблицами: Конструктор):

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

Прочие настройки сводных таблиц в Excel

Иногда приходится фильтровать данные по датам. Например, в нашем списке торговых операций присутствует много-много дат. Excel предоставляет инструмент для группировки данных по дням, месяцам, годам и т.д. Давайте посмотрим, как это делается.

Для начала уберите запись Payment Method из области Column Labels (Колонны). Для этого перетащите его обратно к списку заголовков, а на его место переместите заголовок Date Booked (Дата бронирования):

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

Чтобы исправить это, кликните правой кнопкой мыши по любой дате и выберите из контекстного меню пункт Group (Группировать):

Появится диалоговое окно группировки. Мы выбираем Months (Месяцы) и жмём ОК :

Вуаля! От такой таблицы намного больше пользы:

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

Есть еще один очень важный момент, который необходимо знать! Вы можете создать не один, а несколько уровней заголовков строк (или столбцов):

… а выглядеть это будет так…

То же самое можно проделать с заголовками столбцов (или даже с фильтрами).

Вернёмся к исходному виду таблицы и посмотрим, как вывести средние значения вместо сумм.

Для начала кликните на Sum of Amount и из появившегося меню выберите Value Field Settings (Параметры полей значений):

В списке Summarize value field by (Операция) в диалоговом окне Value Field Settings (Параметры поля значений) выберите Average (Среднее):

Заодно, пока мы здесь, давайте изменим Custom Name (Пользовательское имя) с Average of Amount (Количество по полю Amount) на что-нибудь покороче. Введите в этом поле что-нибудь вроде Avg :

Нажмите ОК и посмотрите, что получилось. Обратите внимание, все значения изменились с итоговых сумм на средние значения, а заголовок таблицы (в левой верхней ячейке) поменялся на Avg :

Если захотеть, то можно получить сразу сумму, среднее и количество (продаж), размещённые в одной сводной таблице.

Вот пошаговая инструкция, как сделать это, начиная с пустой сводной таблицы:

1. Перетащите заголовок Salesperson (Торговый представитель) в область Column Labels (Колонны).
2. Трижды перетащите заголовок Amount (Стоимость) в область Values (Значения).
3. Для первого поля Amount измените название на Total (Сумма), а формат чисел в этом поле на Accounting (Финансовый). Количество десятичных знаков равно нулю.
4. Второе поле Amount назовите Averag e, операцию для него установите Average (Среднее) и формат чисел в этом поле тоже измените на Accounting (Финансовый) с числом десятичных знаков равным нулю.
5. Для третьего поля Amount установите название Count и операцию для него – Count (Количество)
6. В области Column Labels (Колонны) автоматически создано поле Σ Values (Σ Значения) – перетащите его в область Row Labels (Строки)

Вот что мы получим в итоге:

Общая сумма, среднее значение и количество продаж – всё в одной сводной таблице!

Заключение

Сводные таблицы Microsoft Excel содержат очень-очень много функций и настроек. В такой небольшой статье их все не охватить даже близко. Чтобы полностью описать все возможности сводных таблиц, потребовалась бы небольшая книга или большой веб-сайт. Смелые и любознательные читатели могут продолжить исследование сводных таблиц. Для этого достаточно щелкать правой кнопкой мыши практически на любом элементе сводной таблицы и смотреть, какие открываются функции и настройки. На Ленте Вы найдёте две вкладки: PivotTable Tools: Options (Анализ) и Design (Конструктор). Не бойтесь допустить ошибку, всегда можно удалить сводную таблицу и начать все заново. У Вас есть возможность, которой никогда не было у давних пользователей DOS и Lotus 1-2-3.

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

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

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

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

Нам необходимо сделать активной любую ячейку под шапкой таблицы, перейти на вкладку «Вставка» и выбрать пункт меню «Сводная таблица» .

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

На новом листе мы видим область для сводной таблицы и список полей, которые необходимо разместить в областях сводной таблицы.

Теперь нужно определиться, что именно мы хотим получить и какие данные проанализировать. Например, мы хотим узнать, в какие города и какой товар поставлял конкретный менеджер по месяцам и по кварталам, а также необходимо знать объем. Для этого в область «Фильтры» мы перетаскиваем мышкой поле «Менеджер» , в область «Колонны» переместим поле «Дата» .

Теперь займемся строками. В строках нам необходимо разместить два поля и здесь важную роль играет порядок их добавления. Если мы первым расположим поле «Товар» , а под ним «Город» , товары станут раскрывающимися списками, в которых будут указаны все города, куда поставлялся этот товар. Поменяв пола местами в области «Строки» мы получим другой результат.

Осталась у нас область «Значения» , в которую мы перенесем поле «Масса» .

Как видим, считается у нас количество отправок, а не масса, которая нас интересует. Значит, у нас что-то не так с исходными данными. В исходной таблице нам необходимо немного подправить значения и задать ячейкам числовой формат.

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

Как видно, произошла замена поля в списке и в область «Значения» его необходимо повторно добавить.

Вот теперь сводная таблица в Экселе готова и можно фильтровать все поставки по конкретному менеджеру.