Работа со сводными таблицами в microsoft excel

Сводные таблицы MS Excel

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

Приходилось ли вам когда-нибудь попадать в такую ситуацию?:

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

После этих слов, вы готовы его убить, а сами готовы повеситься, так как понимаете, что вам сейчас нужно потратить еще полдня, чтобы все это реализовать…

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

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

Сводная таблица MS Excel — это мощный инструмент анализа данных.

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

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

Как проводить вычисления в сводной таблице?

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

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

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

Как быстро построить сводную таблицу из отчета 1C или SAP?

Суть проблемы, я думаю, ясна всем, кто хоть раз строил Сводные таблицы на основе отчета, полученного из учетной системы. В одном столбце расположены разнотипные данные и Клиент, и Категория товара, и Наименование товара. Значения же, например, объем продаж разбит по нескольким столбцам, по месяцам: Январь в своем столбце, Февраль в своем и так далее.

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

Как отфильтровать столбец сводной таблицы?

На тренингах я часто задаю вопрос участникам: Кто знает, как отфильтровать отдельный (не итоговый) столбец сводной таблицы по значениям? И что я слышу в ответ: Это невозможно! В сводных таблицах нет такой возможности! Вопрос настолько древний, что люди уже перепробовали всякие варианты и не найдя решения сделали вывод — невозможно. Хотя решение существует.

Как построить сводную таблицу по нескольким массивам (листам)?

Типичная задача при обработке информации полученной из разных источников. Типовое решение — взять и свести все таблицы в одну. Но что делать, когда таблиц много (например, 20), или свести их в одну нет возможности, на листе просто не хватает строк (все таблицы в сумме дают больше 1 100 000 строк)?

Однако решение существует! И оно не очень сложное.

Генератор примеров (массивы, таблицы, отчеты).

Когда я начинал читать тренинги по MS Excel то «стер» пальцы бесконечно создавая примеры для той или иной темы. Особенно это касалось темы «Сводные таблицы».

Решил я эту проблему просто — создал надстройку, которая мне эти примеры генерировала. Когда люди увидели это «чудо» на очередном тренинге они очень сильно «возбудились».

Оказалось, что это не только отличный инструмент для тренера, но и такой же отличный инструмент для «студента».

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

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

Имея исходную таблицу в формате EXCEL 2007 , для создания Сводной таблицы достаточно выделить любую ячейку исходной таблицы и в меню Работа с таблицами/ Конструктор/ Сервис выбрать пункт Сводная таблица .

В появившемся окне нажмем ОК, согласившись с тем, что Сводная таблица будет размещена на отдельном листе.

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

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

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

Т.к. ячейки столбца Товар имеют текстовый формат, то они автоматически попадут в область Названия строк Списка полей. Разумеется, поле Товар можно при необходимости переместить в другую область Списка полей. Заметьте, что названия Товаров будут автоматически отсортированы от А до Я (об изменении порядка сортировки читайте ниже ).

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

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

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

О чем идет речь

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

Общее количество проданного товара увидим, если с помощью базы данных построить сводную таблицу в 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 исходной таблицы

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

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

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

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

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

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

Данные в ключевых столбцах не совпадают

Вот пример: Вы владелец небольшого магазина, получаете товар от одного или нескольких поставщиков. У каждого из них принята собственная номенклатура, отличающаяся от Вашей. В результате возникают ситуации, когда Ваша запись “Case-Ip4S-01” соответствует записи “SPK-A1403” в файле Excel, полученном от поставщика. Такие расхождения возникают случайным образом и нет никакого общего правила, чтобы автоматически преобразовать “SPK-A1403” в “Case-Ip4S-01”.

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

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

Создаём вспомогательную таблицу для поиска.

Создаём новый лист Excel и называем его SKU converter. Копируем весь столбец Our.SKU из листа Store на новый лист, удаляем дубликаты и оставляем в нём только уникальные значения.

Рядом добавляем столбец Supp.SKU и вручную ищем соответствия между значениями столбцов Our.SKU и Supp.SKU (в этом нам помогут описания из столбца Description). Это скучная работёнка, пусть Вас радует мысль о том, что её придётся выполнить только один раз :-).

В результате мы имеем вот такую таблицу:

Обновляем главную таблицу при помощи данных из таблицы для поиска.

В главную таблицу (лист Store) вставляем новый столбец Supp.SKU.

Далее при помощи функции ВПР (VLOOKUP) сравниваем листы Store и SKU converter, используя для поиска соответствий столбец Our.SKU, а для обновлённых данных – столбец Supp.SKU.

Столбец Supp.SKU заполняется оригинальными кодами производителя.

Замечание: Если в столбце Supp.SKU появились пустые ячейки, то необходимо взять все коды SKU, соответствующие этим пустым ячейкам, добавить их в таблицу SKU converter и найти соответствующий код из таблицы поставщика.

Как использовать

Сводные таблицы в Excel для чайников представляются чем-то очень сложным и непонятным. На самом же деле не все так страшно. Перед тем как сделать сводную таблицу в Excel, необходимо «раздобыть» для нее исходные данные. Получают их как автоматически, выгрузив необходимую информацию из 1С или другой программы, например, системы ЭДО, так и в ручном режиме, создав документ со всеми необходимыми данными. Идеальный вариант, если сам учет деятельности ведется в Эксель, тогда никаких дополнительных действий совершать не придется. Главное — проверить, что исходный массив соответствует следующим требованиям:

• в нем нет объединенных ячеек;
• нет пустых строк и столбцов;
• все столбцы имеют заголовки.

Если какое-то из этих условий не соблюдено, данные необходимо отформатировать, иначе создать базу не получится. Чтобы наглядно показать, как в Экселе сделать сводную таблицу, используем для примера документ следующего вида:

Создаем базу Excel с помощью функции «Вставка» — «Таблица» — «Сводная таблица».

Получим следующий результат:

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

А теперь добавим типы продаж.

Как сделать вычисления

В отчет можно добавить вычисляемые поля. Для этого необходимо поставить курсор в любую ячейку Еxcel, выбрать вкладку «Анализ» — «Вычисления» — «Поля, элементы и наборы» — «Вычисляемое поле». В появившемся окне зададим имя поля и формулу для вычислений. В нашем случае зарплата составляет 5% от выручки, и формула выглядит следующим образом:

Добавлять и убирать столбцы можно, перетаскивая поля в соответствующую область.

Если данные в исходном массиве изменились, базу необходимо обновить. Добавим менеджера Самуйлову в исходные данные, поставим курсор в любую ячейку базы и обновим результат сведений с помощью вкладки «Анализ» — «Обновить данные».

Чтобы настроить автоматическое обновление данных при открытии файла, необходимо установить галочку в соответствующем месте (вкладка «Анализ» — «Параметры» — «Данные»).

Удаляем базу, выделив ее и нажав клавишу Delete.

Создание сводной таблицы вручную

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

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

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

Если источник данных сводной таблицы представляет собой внешнюю базу данных, созданную в другой программе, такой как Access, установите переключатель Использовать внешний источник данных. Потом щелкните на кнопке Выбрать подключение, а затем в открывшемся диалоговом окне выберите требуемое подключение. Кроме того, Excel поддерживает анализ данных для нескольких связанных таблиц листа (так называемая “модель данных”). Если данные новой сводной таблицы будут анализироваться наряду с данными существующей сводной таблицы, то установите флажок Добавить эти данные в модель данных.

После того как будет определен источник данных и указано место расположения сводной таблицы, щелкните на кнопке ОК, и программа добавит пустую сетку для новой таблицы, а также откроет в правой части области рабочего листа панель Список полей сводной таблицы. Эта панель разделена на две части. Вверху находится список полей источника данных, которые можно добавить в сводную таблицу, а внизу — область, разделенная на четыре зоны: ФИЛЬТРЫ, СТРОКИ, СТОЛБЦЫ и ЗНАЧЕНИЯ.

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

• ФИЛЬТРЫ. Здесь содержатся поля, позволяющие фильтровать данные таблицы. Так, например, если перетащить сюда поле года, то можно будет отображать в таблице сводные данные для каждого конкретного года, представленного в списке данных.
• СТОЛБЦЫ. Здесь содержатся поля, определяющие данные, которые отображаются в столбцах сводной таблицы.
• СТРОКИ. Здесь находятся поля, определяющие данные, которые отображаются в строках сводной таблицы.
• ЗНАЧЕНИЯ. Здесь содержатся поля, определяющие, какие данные будут отображаться в ячейках таблицы, т.е. значения, консолидируемые в последнем столбце (по умолчанию суммируемые).

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

В этой сводной таблице в качестве фильтра из списка данных было выбрано поле Дата. Поле Категория я назначил меткам строк. В качестве значений были выбраны поля Расход и Доход.

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

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

• Список полей. Служит для сокрытия и отображения списка полей на панели задач в правой части области рабочего листа.
• +/- Кнопки. Используется для сокрытия и отображения кнопок сворачивания (-) и разворачивания (+) конкретных строк и столбцов, позволяющих временно удалять и отображать в сводной таблице конкретные значения.
• Заголовки полей. Служит для сокрытия и отображения полей, назначаемых меткам строк и столбцов сводной таблицы.

Области сводной таблицы в Excel

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

Ниже вы узнаете подробней об областях:

• Кэш
• Область “Значения”
• Область “Строки”
• Область “Столбцы”
• Область “Фильтры”

Что такое кэш сводной таблицы

При создании сводной таблицы, Excel создает кэш данных, на основе которых будет построена таблица.

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

Область “Значения”

Область “Значения” включает в себя числовые элементы таблицы. Представим, что мы хотим отразить объем продаж регионов по месяцам (из примера в начале статьи). Область закрашенная желтым цветом, на изображении ниже, отражает значения размещенные в области “Значения”.

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

Область “Строки”

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

Область”Столбцы”

Заголовки вверху значений таблицы называются “Столбцы”.

На примере ниже красным выделены поля “Столбцы”, в нашем случае это значения месяцев.

Область “Фильтры”

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

Список полей и области сводной таблицы

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

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

Также необходимо, чтобы в таблице отсутствовали пустые строки со столбцами и объединенные ячейки, в этом случае Excel не понимает структуру исходных данных и может свести данные некорректно.

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

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

С элементами разобрались, теперь перейдем непосредственно к построению.

Создание рабочих сводных таблиц Excel.

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

Внимание!!!

Элементами окна «Список полей сводной таблицы» являются заголовки полей базы данных!

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

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

3. Если перетащить один элемент (или два, реже – три и более) «Списка …» в верхнюю зону шаблона, где он станет «Полем столбцов», то мы получим из записей этого поля таблицы базы данных заголовки столбцов сводной таблицы.

4. Если перетащить один элемент (или два, реже – три и более) «Списка …» в центральную область шаблона, где он станет «Элементом данных», то мы получим из записей этого поля базы данных значения сводной таблицы.

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

Не очень понятно? Перейдем к практическим примерам — все станет ясно!

Сколько всего тонн металлоконструкций изготовлено по каждому заказу?

Для ответа на этот вопрос выполним всего два действия! Схема этих действий показана на предыдущем рисунке синими стрелками.

1. Перетащим мышью элемент «Заказ» из окна «Список полей сводной таблицы» в зону «Поля строк» пустого шаблона.

2. Перетащим мышью элемент «Общая масса, т» из окна «Список полей сводной таблицы» в зону «Элементы данных» шаблона. Результат – на снимке экрана слева. Думаю, пояснения не требуются.

Заголовок «Элементов данных» «Сумма по полю Общая масса, т» расположился выше заголовка «Поля строк» и левее места, где может расположиться заголовок «Поля столбцов»

Обратите на это внимание!

Сколько тонн металлоконструкций изготовлено по каждому заказу по датам?

Продолжаем работу.

3. Для ответа на вопрос задачи №10 достаточно добавить в нашу сводную таблицу элемент «Дата» из окна «Список полей сводной таблицы» в зону «Поля столбцов» шаблона.

4. Записи можно сгруппировать, например, по месяцам. Для этого на панели «Сводные таблицы» нажимаем вкладку «Сводная таблица» и выбираем «Группа и структура» — «Группировать…». В окне «Группирование» делаем настройки в соответствии со скриншотом слева.

Так как все записи нашей базы данных сделаны в апреле, то после группировки мы видим всего два столбца – «апр» и «Общий итог». Когда в исходной базе данных появятся записи, датированные маем и июнем, в сводной таблице добавятся соответствующие два столбца.

Когда и сколько тонн и штук марок изготовлено всего по заказу №3?

5. Для начала перетащим поле «Заказ» из окна «Список полей сводной таблицы» или прямо из самой сводной таблицы в самый верх листа в зону «Поля страниц» шаблона.

6. Далее поместим поле «Изделие» в область «Поля строк».

7. Поле «Количество, шт» добавим в область «Элементы данных».

Необходимая для ответа на вопрос задачи конфигурация сводной таблицы Excel сформирована.

8. Нажмем на кнопку со стрелкой вниз в ячейке B1 и в выпавшем списке вместо записи «(Все)» выберем «3», соответствующую интересующему нас заказу №3 и закроем список, нажав кнопку «OK».

Ответ на вопрос задачи на снимке экрана ниже этого текста.

Зачем нужны сводные таблицы и когда их используют

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

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

Разберём на примере. Представьте небольшой автосалон, в котором работают три менеджера по продажам. В течение квартала данные об их продажах собирались в обычную таблицу: модель автомобиля, его характеристики, цена, дата продажи и ФИО продавца.

Таблица, в которой хранятся данные о продажах автосалонаСкриншот: Skillbox Media

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

Создание сводной таблицы Excel — алгоритм для новичков

Excel как программа работы с таблицами выпускалась и функционирует в нескольких версиях. Пользуется популярностью версия 2007 г. и 2016 г., хотя при переходе от предыдущих версий для некоторых пользователей функционал видится несколько непривычным. Но вне зависимости от того, в какой версии программы работает пользователь, создать сводную таблицу не трудно даже новичкам. Рассмотрим процесс создания сводной таблицы.

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

В качестве примера рассмотрим алгоритм создания сводной таблицы из продаж кафе за неделю. Исходные данные — таблица с данными продаж за семь дней. При открытии программы выбиваем функцию «Вставка», где из выпадающего меню указываем «Сводная таблица». При её создании необходимо указать ту таблицу, где содержатся нужные данные или диапазон ячеек.

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

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

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

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

Такую таблицу можно выполнить двумя разными способами.

1. Создание сводной таблицы с разных листов при помощи стандартных возможностей и инструментов.
2. Создание таблицы берущей данные с нескольких листов при помощи запроса сформированного в надстройке Power Query .

Рассмотрим первый способ.

Шаг первый.

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

Для этого следует кликнуть правой кнопкой мыши по ленте (панели инструментов) и выбрать из выпадающего меню пункт «Настройка ленты»

Настройка ленты

либо войти во вкладку

«Файл» => «Параметры» => «Настройка ленты».

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

Из списка выбираем «Мастер сводных таблиц и диаграмм»

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

В правом окне при помощи кнопки «Создать группу» создаем новую группу инструментов. Для группы можно выбрать удобное для Вас наименование. Например, «Своя группа». Можно выбрать на какой вкладке будет создана группа. В своем примере я выбрал вкладку «Главная».

Когда группа создана, выделите ее курсором, выделите курсором «Мастер сводных таблиц и диаграмм» в левом окне и нажмите кнопку «Добавить >>».

После нажмите «Ок».

Теперь на главной вкладке панели инструментов находится инструмент «Мастер сводных таблиц и диаграмм».

Мастер сводных на панели

Шаг второй. Построение сводной таблицы из нескольких источников данных.

• Кликнуть по кнопке мастера построения сводных таблиц.

• На первом окне поставить флажок, напротив «в нескольких диапазонах консолидации» и флажок напротив «сводная таблица»

• Во втором окне выбрать «Создать одно поле страницы»

• В третьем окне добавить все диапазоны, которые Вы хотите консолидировать(соединить в сводной таблице).

• В четвертом окне выбрать лист, на котором будет размещена сводная таблица.

• Нажать кнопку «Готово».

Данный способ заключается в использовании запроса надстройки Power Query.

О данной надстройке рассказывалось в статье: «Power Query» в «Excel» — что это?

Создание запроса Power Query для сведения нескольких страниц книги в одну таблицу.

Шаг первый.

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

Шаг два.

Для этого во вкладке Power Query нужно нажать кнопку «Из таблицы» и указать в появившемся окне диапазон – источник данных. После чего нажать «Ок».

Power Query из таблицы

Шаг три.

Когда создан второй запрос, нужно во вкладке Power Query кликнуть по кнопке «Слияние запросов» и настроит в появившемся окне вид получившейся общей таблицы.

Слияние запросов

Шаг четыре.

Когда вид настроен, нужно нажать кнопку «Закрыть и загрузить.»

Закрыть и загрузить

Надстройка Power Query соберет данные с двух листов и соединит их в одной таблице.

Где применять

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

Расчет KPI

Современные CRM-системы позволяют выгрузить все необходимые отчеты в готовом виде. Но что делать тем, кто специализированный софт не использует? Остается возможность как в Экселе сделать сводную таблицу, так и посчитать необходимые показатели в ручном режиме. Второй способ кажется проще, но он не всегда удобен. Если исходные данные представлены в виде списка подобного вида, использовать объединенные реестры вполне уместно, так как это значительно облегчает последующую работу.

Мы уже посчитали объем продаж для каждого менеджера с помощью сводной базы.

Теперь эти данные используем для дальнейшего расчета. Сравним плановый показатель с фактическим и вычислим отклонение.

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

Отчет по персоналу

Практически все данные о персонале получаем из 1С. Но если такой софт в организации не используется или необходим отчет в другой форме, не остается ничего, кроме как делать сводные таблицы в Еxcel. Даже если массив данных составляется в ручном режиме, базы помогут представить их в более «красивом» виде. Имея сведения об образовании, стаже, окладе сотрудников в виде подобного списка, есть возможность, допустим, выяснить, сколько сотрудников каждого из отделов имеют образование определенного уровня.

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

На основе таких отчетов удобно строить диаграммы для графического отображения информации.