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

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

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

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

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

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

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

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

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

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

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

Создание таблицы в формате EXCEL 2007 добавляет новые возможности:

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

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

В таблице имеются столбцы:

• Товар – наименование партии товара, например, « Апельсины »;
• Группа – группа товара, например, « Апельсины » входят в группу « Фрукты »;
• Поставщик – компания-поставщик Товаров, Поставщик может поставлять несколько Групп Товаров;
• Дата поставки – Дата поставки Товара Поставщиком;
• Регион продажи – Регион, в котором была реализована партия Товара;
• Продажи – Стоимость, по которой удалось реализовать партию Товара;
• Сбыт – срок фактической реализации Товара в Регионе (в днях);
• Прибыль – отметка о том, была ли получена прибыль от реализованной партии Товара.

Через Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен ) откорректируем Имя таблицы на « Исходная_таблица ».

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

СВОДНЫЕ ТАБЛИЦЫ Microsoft Excel. ЧТО ЭТО?

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

Рассмотрим базовые приемы создания сводных таблиц на примере.

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

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

Например, сумма продаж по каждому менеджеру; сумма продаж за каждый период; количество проданного товара по периодам или менеджерам и т.д.

Таким образом, за несколько минут можно обработать большое количество строк и получить отчет в нужном виде.

СОЗДАНИЕ СВОДНОЙ ТАБЛИЦЫ

В меню ВСТАВКА в группе ТАБЛИЦЫ нажмите по кнопке Сводная таблица и выберите пункт Сводная таблица .

В открывшемся окне нужно указать источник данных сводной таблицы (это наша исходная таблица) и местоположения сводной таблице (укажем На новый лист). И нажимаем ОК.

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

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

Теперь создадим первую сводную таблицу. Для этого перетащите поле Период в квадрат СТРОКИ . Поле Сумма перетащите в квадрат ЗНАЧЕНИЯ . В итоге в сводной таблице получили объем продаж по периодам.

В том случае, если Excel автоматически суммировал количество продаж, а не их стоимость, необходимо сделать еще одну настройку.

Щелкаем левой кнопкой мыши по названию поля в квадрате Значения и выбираем Параметры полей значений .

В открывшемся окне выбираем операцию для данного поля. В нашем случае – это Сумма и нажимаем ОК.

Источник

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

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

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

В качестве примера рассмотрим алгоритм действий для составления сводных таблиц в Microsoft Excel 2019. Эта же логика может применяться и в более ранних версиях программы.

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

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

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

Шаг 1. Создаем “умную таблицу”

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

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

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

Шаг 2. Формируем сводную таблицу

1. Переключаемся во вкладку “Вставка”, в левом углу нажимаем на кнопку “Таблицы” и в появившемся перечне вариантов выбираем “Сводная таблица”.
2. Откроется окно создания сводной таблицы, в котором уже указан диапазон исходных значений (в нашем случае – автоматически выбрана “Таблица2”, которую мы создали в шаге 1). В случае необходимости диапазон можно скорректировать или же указать Имя таблицы, из которой нужно собрать сводную.
3. В этом же окне выбираем, где следует разместить новую таблицу. Варианта два – на новом листе или существующем. При прочих равных условиях приоритетнее первый вариант, но конечный выбор остается за пользователем. После того, как мы определились с выбором и поставили нужную отметку (в нашем примере – на новом листе), нажимаем ОК.

• Фильтр
• Столбцы
• Строки
• Значения

• “Пол” и “Вид спорта” – в “Фильтр”
• “Столбцы” в нашем случае оставляем пустыми
• “Наименование” – в “Строки”
• “Сумма, руб.” – в “Значения”.

Шаг 3. Применяем фильтры и другие настройки

Теперь вернемся к нашей задаче. Нам нужно отфильтровать данные по полу (женский) и оставить только строки, которые относятся к виду спорта “теннис”.

1. Кликаем по текущему значения фильтра “Пол”, выбираем из списка “женский” и нажимаем кнопку OK.
2. Аналогичным образом настраиваем фильтр по “Виду спорта”, оставив только “теннис”.
3. Таким образом, мы получили финальную таблицу с нужно нам информацией.

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

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

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

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

Благодаря нашим стараниям таблица изменила свой вид. Теперь есть возможность увидеть разбивку суммы по ценам за единицу товара. При желании и необходимости снова можно задействовать фильтры по полу или виду спорта.

Также можно попробовать добавить в таблицу еще больше информации. Для этого снова открываем список полей и перетаскиваем поле “Продано, шт.” в область “Строки”.

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

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

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

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

Использование Мастера сводных таблиц

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

1. Открываем меню Файл, кликаем по строке “Параметры”, далее – “Панель быстрого доступа”. Выбрав “Команды не на ленте” в предлагаемом перечне нам нужен пункт “Мастер сводных таблиц и диаграмм”. Отмечаем его курсором, нажимаем “Добавить >>” и завершаем настройки кликом по кнопке OK.
2. В самом верхнем левом углу окна программы появится значок, нажав на который, запускаем Мастер сводных таблиц.
3. В открывшемся окне необходимо выбрать источник данных, и на выбор может предлагаться до четырех опций. В нашем случае останавливаемся на первом варианте, т.е. создаем таблицу из списка или базы данных Excel. В нижней части окна выбираем пункт “сводная таблица” и нажимаем “Далее”.
4. Появится следующее окно, где нужно указать координаты исходной таблицы, из которой будет сформирована сводная таблица. Если мы согласны с диапазоном, присвоенным программой автоматически, кликаем по кнопке “Далее”, либо сначала выделяем нужную область и затем уже двигаемся дальше.
5. Аналогично ранее рассмотренному примеру выбираем место для размещения сводной таблицы и кликаем “Готово”. На выбор предлагаются две опции.
   • на новом листе
   • на существующем листе (нужно выбрать конкретный лист).
6. Будет создана уже знакомая нам форма для конструирования сводной таблицы. Далее приступаем к ее настройке согласно нашим пожеланиям и задачам.

Важные моменты, которые нужно учитывать при формировании сводных таблиц

1. Если в исходной таблице данные не совсем корректны, они в таком же виде перенесутся и в сводную. То есть, если, например, в исходной таблицы в одной строке написано “Кроссовки беговые, размер 35″, а в другой “Кросовки беговые, размер 35″, то в сводной таблице оба этих варианта будут отображены как две отдельные строки, и никакой консолидации данных по ним не будет.
2. Если первоначальная таблица не была преобразована в “умную” на первоначальном этапе, и сводная таблица создана из простой, необходимо проверить, чтобы все заголовки в шапке таблицы были заполнены. В противном случае, программа может выдать ошибку.
3. Если сводная таблица построена на базе простой (не “умной”), то после любых изменений исходной таблицы нужно обновлять данные сводной с помощью кнопки “Обновить все” во вкладке “Данные”. Однако, при этом нужно учитывать, что обновление не удастся сделать, если были добавлены строки или столбцы за пределами ранее указанных границ.

Заключение

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

Источник



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

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

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

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

Как за 10 минут подготовить отчет по выручке: сводные таблицы против формул

Сводные таблицы – это инструмент для аналитики и представления данных в удобном и легко понятном виде. Их используют в следующих случаях:

1. Большой объем исходных данных. Небольшое количество данных можно просчитать и проанализировать вручную. Однако если в распоряжении имеется сотни и тысячи записей, ошибки неизбежны и их будет много. В разы увеличится и время, необходимое для обработки.
2. Необходимо выявить тенденции и динамику в данных. В сводных таблицах можно легко преобразовать вывод информации, не нужно сортировать ее вручную.
3. Чтобы создать диаграмму или построить график на основе отформатированных данных.
4. Нужно делать промежуточный «срез» итогов, которые часто меняются, для последующего их сравнения. В этом случае достаточно обновить данные, Excel пересчитает все автоматически.
5. Для анализа и поиска экстремальных значений – минимумы и максимумы данных.
6. Для сведения разрозненных данных из разных источников. При необходимости в исходные данные можно добавить необходимую строку или столбец и обновить итоговое отображение отчета.

Excel поможет превратить бесчеловечные отчеты в удобные сводные таблицы

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

Скачать модель в Excel

Сводные таблицы эксель: как они выглядят

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

• значения;
• строки;
• столбцы;
• фильтр отчета.

Сводная таблица файла .xlsx имеет следующие ограничения:

• максимальное количество полей строк – 1 048 576 или ограничено размером оперативной памяти;
• максимальное количество полей столбцов – 16 384;
• максимальное количество полей страниц – 16 384;
• максимальное количество полей данных – 16 384.

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

Пример сводной таблицы в Excel:

В правой части таблицы – фамилии, вверху – даты, в центре – итоги работы. Очевидно, что такая подача информации удобнее и понятнее. См. также 8 функций Ехсеl, которые помогут сократить работу вдвое.

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

Это сегмент строк. Здесь может быть одно или несколько значений. В нашем случае – это фамилии менеджеров. Напротив фамилий в соответствующих строках – сводный итог работы.

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

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

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

Сводная таблица в Экселе: пошаговая инструкция

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

Появится новое окно:

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

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

Если выбрать новый лист, то в этом случае он будет создан, на нем появится конструктор сводной таблицы. Рекомендуем так и делать. После того, как указаны все сведения – исходные данные и место для отчета, надо нажать кнопку «ОК» и появится подсказка с предложением выбрать поля для построения нового отчета.

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

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

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

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

Таким образом, создадим сводную таблицу и она примет следующий вид.

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

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

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

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

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

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

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

Чтобы добавить их в отчет нужно кликнуть на сводной таблице, перейти во вкладку «Параметры» и выбрать «Изменить источник данных» – на картинке эта опция выделена цветом.
В появившемся окне указать источник данных вместе с обновленными строками и нажать «ОК».

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

Источник