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

Создание отчетов при помощи сводных таблиц

Видео

Лирическое вступление или мотивация

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

В ней каждая отдельная строка содержит полную информацию об одной отгрузке (сделке, партии):

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

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

  • Сколько и каких товаров продали в каждом месяце? Какова сезонность продаж?
  • Кто из менеджеров сколько заказов заключил и на какую сумму? Кому из менеджеров сколько премиальных полагается?
  • Кто входит в пятерку наших самых крупных заказчиков?

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

Если у вас Excel 2003 или старше

Ставим активную ячейку в таблицу с данными (в любое место списка) и жмем в меню Данные – Сводная таблица (Data – PivotTable and PivotChartReport) . Запускается трехшаговый Мастер сводных таблиц (Pivot Table Wizard) . Пройдем по его шагам с помощью кнопок Далее (Next) и Назад (Back) и в конце получим желаемое.

Шаг 1. Откуда данные и что надо на выходе?

На этом шаге необходимо выбрать откуда будут взяты данные для сводной таблицы. В нашем с Вами случае думать нечего – “в списке или базе данных Microsoft Excel”. Но. В принципе, данные можно загружать из внешнего источника (например, корпоративной базы данных на SQL или Oracle). Причем Excel “понимает” практически все существующие типы баз данных, поэтому с совместимостью больших проблем скорее всего не будет. Вариант В нескольких диапазонах консолидации (Multiple consolidation ranges) применяется, когда список, по которому строится сводная таблица, разбит на несколько подтаблиц, и их надо сначала объединить (консолидировать) в одно целое. Четвертый вариант “в другой сводной таблице. ” нужен только для того, чтобы строить несколько различных отчетов по одному списку и не загружать при этом список в оперативную память каждый раз.

Вид отчета – на Ваш вкус – только таблица или таблица сразу с диаграммой.

Шаг 2. Выделите исходные данные, если нужно

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

Шаг 3. Куда поместить сводную таблицу?

На третьем последнем шаге нужно только выбрать местоположение для будущей сводной таблицы. Лучше для этого выбирать отдельный лист – тогда нет риска что сводная таблица “перехлестнется” с исходным списком и мы получим кучу циклических ссылок. Жмем кнопку Готово (Finish) и переходим к самому интересному – этапу конструирования нашего отчета.

Работа с макетом

То, что Вы увидите далее, называется макетом (layout) сводной таблицы. Работать с ним несложно – надо перетаскивать мышью названия столбцов (полей) из окна Списка полей сводной таблицы (Pivot Table Field List) в области строк (Rows) , столбцов (Columns) , страниц (Pages) и данных (Data Items) макета. Единственный нюанс – делайте это поточнее, не промахнитесь! В процессе перетаскивания сводная таблица у Вас на глазах начнет менять вид, отображая те данные, которые Вам необходимы. Перебросив все пять нужных нам полей из списка, Вы должны получить практически готовый отчет.

Останется его только достойно отформатировать:

Если у вас Excel 2007 или новее

В последних версиях Microsoft Excel 2007-2010 процедура построения сводной таблицы заметно упростилась. Поставьте активную ячейку в таблицу с исходными данными и нажмите кнопку Сводная таблица (Pivot Table) на вкладке Вставка (Insert) . Вместо 3-х шагового Мастера из прошлых версий отобразится одно компактное окно с теми же настройками:

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

  • Названия строк (Row labels)
  • Названия столбцов (Column labels)
  • Значения (Values) – раньше это была область элементов данных – тут происходят вычисления.
  • Фильтр отчета (Report Filter) – раньше она называлась Страницы (Pages) , смысл тот же.

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

Единственный относительный недостаток сводных таблиц – отсутствие автоматического обновления (пересчета) при изменении данных в исходном списке. Для выполнения такого пересчета необходимо щелкнуть по сводной таблице правой кнопкой мыши и выбрать в контекстном меню команду Обновить (Refresh) .

Источник: www.planetaexcel.ru

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

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

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

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

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

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

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

Управление списком полей таблицы

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

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

  • Названия строк – отображает значения поля в виде названия строк. По сути данная область ничего не меняет, если назначить ей все имеющиеся поля, то получим исходную таблицу;
  • Названия столбцов – значения полей отображаются в виде названий столбцов. Действие напоминает обычное транспонирование;
  • Значения – со значениями столбцов, помещенными в эту область, производятся вычисления. При этом одно поле может быть назначено ей несколько раз;
  • Фильтр отчета – сюда помещаются поля, значения которых служат для фильтрации строк исходных данных, которые сводная таблица должна проигнорировать.
Читайте также:  Курсы эксель для начинающих

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

Месяц Дата Кол-во Курс Изменение
Январь 10.01.2013 1 30,4215 0,0488
Январь 11.01.2013 1 30,3650 -0,0565
Январь 12.01.2013 1 30,2537 -0,1113
. . . . .
Сентябрь 28.09.2013 1 32,3451 0,1715

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник: office-menu.ru

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

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

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

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

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

  • таблица данных не должна содержать объединенных ячеек
  • не должна содержать полностью пустых строк и пустых столбцов
  • в каждом столбце должны содержаться данные одного типа (либо текст, либо дата, либо числа)
  • каждый столбец должен иметь уникальный, краткий и информативный заголовок
  • столбцы должны идти одной строкой и не должны содержать пустых и объединенных ячеек
Читайте также:  Как вставить график в 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) . Это связано с тем, что сводная не содержит прямой ссылки на исходные данные, а хранит их в кэше, что позволяет сводной достаточно быстро обрабатывать данные.

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

Источник: www.excel-vba.ru

Excel для финансиста

Поиск на сайте

Глава 7. Сводные таблицы

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

Скачайте файл svodnie-tablici. На листе данные этого файла находятся двести записей о продажах товаров (на практике число анализируемых записей обычно на один-два порядка больше). Каждая запись представляет собой строчку в таблице и содержит информацию:

  • Дата совершения продажи;
  • Наименование товара;
  • Наименование покупателя товара;
  • Сумма сделки.

Относительно этих данных может возникнуть множество вопросов:

  • Какая общая сумма продаж?
  • Кто самый активный покупатель?
  • Какой самый популярный товар по общей сумме сделки?
  • Как распределены продажи в течение года, есть ли сезонность у товаров?
  • Растут или падают продажи в течение нескольких лет?

На все эти вопросы помогают ответить сводные таблицы.

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

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

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

Поля сводной таблицы

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

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

Всего несколько кликов мышкой, и первая сводная таблица в Excel готова! Программа уже посчитала суммы продаж в двух разрезах: по покупателям и товарам, и вывела общий итог. Таким образом программа берёт и структурирует данные. Можно немного доработать сводную таблицу. Выделите финансовые данные таблицы (диапазон B5:E9), задайте этим ячейкам финансовый формат, суммы стали нагляднее. Выделите ячейку Е5 (общий итог – покупатель Автоматика), нажмите меню Параметры, в разделе Сортировка – большую кнопку Сортировка, в открывшемся окне – Параметры сортировкиПо убыванию, ОК. Теперь и производители, и товары отсортированы по убыванию, ответы на первые три вопроса получены.

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

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

Скачать пример сводных таблиц Excel: svodnie-tablici

Источник: finexcel.ru

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

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

О том, что лучше один раз увидеть.

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

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

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

Читайте также:  Excel отобразить скрытый лист в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Когда я начинал читать тренинги по MS Excel то “стер” пальцы бесконечно создавая примеры для той или иной темы. Особенно это касалось темы “Сводные таблицы”. Решил я эту проблему просто – создал надстройку, которая мне эти примеры генерировала. Когда люди увидели это “чудо” на очередном тренинге они очень сильно “возбудились”. Оказалось, что это не только отличный инструмент для тренера, но и такой же отличный инструмент для “студента”.

Источник: e-xcel.ru

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

Очень часто при формировании отчётов (аналитики) основанных на больших объемах данных приходиться сводить в одну таблицу информацию с нескольких листов книги «Excel» .
В идеале для сбора и аналитики информации с разных листов «Эксель» желательно сделать сводную таблицу, которая будет брать данные из нескольких источников (листов книги).

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

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

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

Шаг первый.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

На существующий лист

  • Нажать кнопку «Готово».
  • Второй способ.

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

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

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

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

    Шаг первый.

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

    Шаг два.

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

    Power Query из таблицы

    Шаг три.

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

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

    Шаг четыре.

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

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

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

    Источник: ruexcel.ru