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

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

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

Из примера видно, что сводная таблица представляет древовидную структуру, если используется более 1 поля. Корнем являются значения столбца, который в списке области «Названия строк» идет первым. Все последующие поля вкладываются в него и в друг друга, согласно своей очередности в списке, изменить которую можно простым перетаскиванием мыши. Каждую отдельную ветвь подобного дерева можно сворачивать и раскрывать. Данное свойство так же применимо к области названий столбцов.По умолчанию эксель задает сводным таблицам макет в сжатом виде. Его можно изменить через параметры (клик правой кнопкой мыши по области таблицы -> параметры сводной таблицы -> Вывод -> Классический макет) либо через конструктор:

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

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

Теперь законченная сводная таблица выглядит так на листе Excel:

Помимо рассмотренных свойств через параметры таблицы можно установить:

1. Имя сводной таблицы;
2. Объединение и выравнивание подписей;
3. Вывод значений для пустых ячеек;
4. Автоматическое изменение ширины столбцов;
5. Отображение общих итогов по строкам и столбцам;
6. Сортировку;
7. Печать;
8. Обновление и др.

Теперь Вы умеете пользоваться сводными таблицами Excel. Полученные здесь знания позволят Вам далее самостоятельно экспериментировать с ними и повышать свой навык.

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

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

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

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

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

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

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

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

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

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

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

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

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

Автоматический режим

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

1. Перейдите на вкладку «Вставка». Нажмите на кнопку «Таблицы» и выберите последний пункт.

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

1. Для этого достаточно просто выделить любую область – координаты подставятся автоматом.

1. Как только вы отпустите курсор, окно примет исходный вид. Нажмите на кнопку «OK».

1. В результате этого будет создана красивая таблица с чередующимися линиями.

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

Дальше можете делать, что душе угодно.

Именованный диапазон Excel

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

Либо выполнить команду Формулы → Определенные имена → Присвоить имя. В Excel будет создан новый объект, к которому можно обращаться, например, в формулах. Диапазон виден в Диспетчере имен.

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

Теперь можно стать на любую ячейку внутри именованного диапазона (или выбрать его из выпадающего списка в поле Имя) и вызвать ту же команду: Данные → Получить и преобразовать данные → Из таблицы/диапазона. Произойдет загрузка данных в Power Query.

Такой способ позволяет «не портить» исходные данные. Но у него есть и очевидный недостаток: новые строки, которые выйдут за пределы именованного диапазона, не попадут в запрос.

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

Если предоставленных операций и вычислений недостаточно, то эксель позволяет создать свое вычисляемое поле в сводной таблице. Для этого выделите ячейку из области таблицы, перейдите на вкладку «Параметры» («Анализ» для Excel 2013) появившейся ленты. Далее в разделе «Сервис» кликните по пиктограмме «Формулы», из раскрывающегося меню (в версии 2010 и выше путь отличается: Раздел «Вычисления» -> Раскрывающийся список «Поля, элементы и наборы») выберите пункт «Вычисляемое поле…». Должно появиться окно:

Задайте понятное имя, и запишите формулу, используя любые функции (имейте в виду, что вычисляемые поля не работают с текстом). В качестве примера умножим курс на 1000 и вычтем 13 процентов (=Курс*1000*0,87). Назовем поле «ЗП», добавим в область значений и в качестве операции применим максимум. Посмотрите новый вид отчета:

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

Группировка с шагом

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

1. Создаем сводную таблицу, где в строках будут дни, а в значениях – продажи. Если Вы не знаете, как создать сводную таблицу – сначала прочтите предыдущий пост;
2. Кликните правой кнопкой мыши по любой из дат в сводной таблице и в контекстном меню выберите «Группировать». Откроется окно настройки группировки;

1. В полях «Начиная с» и «по» автоматически установятся минимальная и максимальная даты в списке. Можете, при необходимости, указать здесь более узкий период для группировки
2. В списке «С шагом» выберите эталонный интервал времени. У нас это «Месяцы». Можно выбрать сразу несколько пунктов в этом списке. Давайте попробуем построить по кварталам и месяцам, отмечаем их;
3. Жмем «Ок» и сразу же получаем результат. Взгляните, что получилось:

Таким же образом можно группировать обычные числовые данные. Например, мы хотим сгруппировать ежедневные продажи с шагом 1000 и узнать, в каком из интервалов было больше всего чеков. Делаем так:

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

1. Кликнем правой кнопкой в любо строке первого столбца (суммы продаж) и выберем «Группировать». В открывшемся окне задаем минимальное и максимальное числа для группировки, а так же, шаг. У нас это 1000. Вместо огромной таблицы, получаем компактную, из десяти строк. В каждой строке – интервал сумм и количество чеков в этом интервале.

1. Кликнем правой кнопкой по любой ячейке в столбце «Количество чеков» и выберем Сортировка – по убыванию;
2. Можно для наглядности выразить результат в процентах. Кликнем по той же ячейке и выберем Дополнительные вычисления — % от суммы по столбцу. Вот и все, задача решена, нам прекрасно видно в каких интервалах сумм было больше всего чеков.

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

Так же, можно группировать записи вручную. Для этого выделите нужные данные и нажмите Работа со сводными таблицами – Анализ – Группировать – Группировка по выделенному.

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

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

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

А выглядит это вот так:

Вы видите несколько окон на рабочем листе с перечисленными в них данными, а так же, сводную таблицу, содержащую полный набор данных. Но что если кому-то нужно посмотреть продажи метизов у Романа 1 апреля 2016 года? Кликаем в окнах на кнопки:

1. В окне «Дата» ищем и выбираем 01.04.2016;
2. В окне «Менеджер» выбираем «Роман»;
3. В окне «Группа товара» кликаем «Метизы»

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

По-моему, отлично! Так вот, чтобы включить срезы в сводных таблицах – выделите любую ячейку этой таблицы и выполните на ленте Работа со сводными таблицами – Анализ – Фильтр – Вставить срез. На экране появится окошко, где нужно галочками отметить те поля сводной таблицы, по которым можно будет делать срезы. Каждому полю будет соответствовать свое окошко со списком. Выбирайте, жмите Ок и все, заработало!

Аналогично работает временная шкала. Этот инструмент очень похож на срезы, но управляет полями, в которых содержатся даты. Чтобы добавить временную шкалу – нажмите Работа со сводными таблицами – Анализ – Фильтр – Вставить временную шкалу. После простых настроек появится окно фильтрации дат, которое позволяет эффективно и быстро ограничивать периоды дат, выводимые в отчет.

Применение умных таблиц

Давайте вернемся к исходной таблице с данными, встанем в любую ячейки таблицы и в панели вкладок перейдем на Главная -> Стили -> Форматировать как таблицу:

Среди стилей оформления выбираем любой понравившийся (внешний вид на функциональности не влияет), далее выбираем диапазон для таблицы (по умолчанию Excel автоматически его определяет) и нажимаем OK:

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

• Умная таблица получает имя (в примере выше Таблица1, его можно легко поменять), которое можно использовать для определения диапазона в качестве источника для сводной таблицы;
• Умная таблица автоматически изменяет размер при добавлении или удалении новых строк или столбцов.

Таким образом мы можем использовать имя таблицы в качестве источника данных. Вернемся к нашим сводным таблицам и в Источнике данных поменяем диапазон на умную таблицу:

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

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

Общие сведения о сводных таблицах

БД.xlsx (30,9 KiB, 1 434 скачиваний)

Несмотря на то, что первая возможность создания сводных таблица появилась еще в Excel 5.0(аж в 1993 году), даже сейчас лишь немногие из пользователей Excel используют сводные таблицы для решения задач. Кто-то считает, что сводные таблицы это нечто слишком сложное, кто-то про них даже не слышал. В данном разделе (Сводные таблицы) я постараюсь убедить читателей в том, что это не так. На самом деле ничего сложного в сводных таблицах нет. А вот польза при анализе информации просто неоценима.

Для чего же нужны сводные? В Excel работу можно разделить на две категории: анализ(вычисление) и форматирование данных. Под вычислениями и анализом я понимаю получение неких показателей на основании имеющихся данных. А форматирование – не закраска ячеек цветом, а вид представления таблиц данных. Все это можно сделать и формулами. Предположим, есть исходные ежедневные данные по продажам всех филиалов за полгода. Из этих данных необходимо построить отчет в разрезе каждого филиала и для филиала в разрезе месяца. Плюс сводные отчеты по каждому филиалу за все полгода. А теперь представим как это будет выглядеть формулами: -сначала надо получить список месяцев; -затем список уникальных наименований филиалов; -далее создать листы с рыбой таблиц, в которые надо будет собрать данные из исходной таблицы при помощи СУММЕСЛИ, СУММПРОИЗВ и им подобным. При должном опыте можно уложиться минут в 15-20. С использованием сводных это можно сделать за две минуты.

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

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

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

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

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

Выделить любую ячейку сводной таблицы→Правая кнопка мыши→Обновить (Refresh) или вкладка Данные (Data) →Обновить все (Refresh all) →Обновить (Refresh) .

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

1. Выделить любую ячейку исходной таблицы
2. Вкладка Вставка (Insert) →группа Таблица (Table) →Сводная таблица (PivotTable)
3. В диалоговом окне Создание сводной таблицы (Create PivotTable) проверить правильность выделения диапазона данных (или установить новый источник данных), определить место размещения Сводной таблицы:
   • На новый лист (New Worksheet)
   • На существующий лист (Existing Worksheet)
4. нажать OK

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

 Сумма (Sum)  Количество (Count)  Среднее (Average)  Максимум (Max)  Минимум (Min)  Произведение (Product)

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

При использовании сводной таблицы следует учитывать, что она не обновляет свои значения автоматически при изменении значений в исходных данных. Для выполнения обновления необходимо выделить любую ячейку сводной таблицы→Правая кнопка мыши→Обновить (Refresh) или вкладка Данные (Data) →Обновить (Refresh) . Это связано с тем, что сводная не содержит прямой ссылки на исходные данные, а хранит их в кэше, что позволяет сводной достаточно быстро обрабатывать данные.

Статья помогла? Поделись ссылкой с друзьями!

Проверка правильности выставленных коммунальных счетов

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

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

Для примера мы сделали сводную табличку тарифов для Москвы:

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

Первый столбец = первому столбцу из сводной таблицы. Второй – формула для расчета вида:

= тариф * количество человек / показания счетчика / площадь

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

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

Плюсы сводных таблиц

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

1. Таблица составляется по практически любому объему данных.
2. Редактировать вид отчета можно через меню форматов – встроенная библиотека содержит множество цветовых тем таблиц.
3. Возможно объединение данных в более широкие группы, например несколько дат объединяются в кварталы.
4. По результатам отчета можно провести расчеты с помощью инструментов Excel, это не повлияет на источники данных.
5. Информация в сводной таблице может стать основой для составления графика или другого визуального отчета.

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

На заре развития программ для создания электронных таблиц балом правил 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 будет трудно правильно сгруппировать и обработать данные

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