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

Наверняка в вакансиях вы видели похожее требование: «Знание Excel на уровне продвинутого пользователя (ВПР, сводные таблицы, формулы)». Звучит сложно, но на самом деле освоить эти функции довольно легко. Начнем со сводных таблиц: разберемся, что это такое, зачем они нужны и как их делать. 

Что такое сводная таблица Excel и почему это удобно

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

Сводные таблицы создал разработчик Пито Салас в 1986 году. Разрабатывая программу для работы с электронными таблицами Lotus Improv, он начал замечать в данных закономерности, которые возникали при объединении разных таблиц. Он понял, что это может быть полезным инструментом для анализа данных, и создал сводные таблицы. Microsoft добавила функциональность сводных таблиц в Excel только в 1994 году. С тех пор на сводных таблицах держится многое в самых разных отраслях: финансах, маркетинге, продажах, закупках.

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

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

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

Начинаем работу

Попробуем сделать простую сводную таблицу. В нашем примере будет небольшая IT-компания, которая занимается разработкой на заказ. Проектов много, поэтому без сводных таблиц не обойтись. В нашей таблице будут колонки «Название проекта», «Тип проекта», «Команда», которая занимается этим проектом, «Бюджет», который выделил заказчик, и «Срок (в месяцах)» — время, которое уйдет на выполнение проекта. 

Картинка с сайта: blog.skillfactory.ru

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

Картинка с сайта: blog.skillfactory.ru

Откроется окно создания сводной таблицы. По умолчанию оно выглядит так:

Картинка с сайта: blog.skillfactory.ru

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

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

Картинка с сайта: blog.skillfactory.ru

Настраиваем сводную таблицу

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

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

Если выбрать сначала «Название проекта», а потом «Команда», таблица выглядит совсем по-другому. Порядок выбора столбцов важен.

Картинка с сайта: blog.skillfactory.ru

Посмотрим на фильтры. Наша исходная таблица небольшая, команд немного, поэтому вывести всё и сразу довольно удобно. Если бы команд или проектов было больше, то такое отображение не подошло бы. Здесь выручат фильтры. Например, можно добавить «Команду» в поле «Фильтры». 

Картинка с сайта: blog.skillfactory.ru

Тогда все наши команды станут пунктами выпадающего меню, а проекты будут располагаться ниже и только для выбранной команды. Красиво, удобно, наглядно.

А что, если руководитель захочет посмотреть, сколько заработает каждая команда на проектах? Выбираем «Команда», выбираем «Бюджет». Обратите внимание, что «Команда», как обычно, оказалась в поле «Строки», а вот «Бюджет» попал в «Значения» автоматически, потому что в этом столбце были записаны числа.

Картинка с сайта: blog.skillfactory.ru

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

Картинка с сайта: blog.skillfactory.ru

При работе с числовыми значениями часто применяются фильтры. Например, так наша IT-компания может узнать, какое из направлений ей приносит больше денег. Для этого нужно переместить «Тип проекта» в поле «Фильтры», а «Бюджет» сам попадет в «Значения». Теперь можно выбирать направление и смотреть, какое из них самое выгодное для компании. 

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

Картинка с сайта: blog.skillfactory.ru

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

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

Что делать, если исходные данные изменились? Например, на какой-то проект назначили Team 2, а не Team 1? Перейдите в раздел «Анализ сводной таблицы» и нажмите «Обновить». Никаких подтверждающих окон не появится, но актуальные данные подтянутся в вашу сводную таблицу.

Картинка с сайта: blog.skillfactory.ru

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

Картинка с сайта: blog.skillfactory.ru

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

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

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

Картинка с сайта: blog.skillfactory.ru

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

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

Делаем это для обеих таблиц. Теперь они выглядят так:

Картинка с сайта: blog.skillfactory.ru

Картинка с сайта: blog.skillfactory.ru

После этого создаем сводную таблицу для наших исходных данных: таблицы с проектами. Обязательно ставим галочку в окошке «Добавить эти данные в модель данных». Мы видим уже привычный конструктор сводных таблиц. Но как же связать данные из наших двух таблиц? Обратимся к конструктору справа. Перейдем в раздел «Все». 

Увидим обе наши таблицы! «Проекты» и «Тимлиды» — это названия, которые мы придумали. 

Картинка с сайта: blog.skillfactory.ru

Кликаем на каждый, раскрываем списки и видим названия столбцов. Чтобы посмотреть нагрузку на тимлидов, в списке «Проекты» мы выберем столбец «Название проекта», в списке «Тимлиды» — столбец «Тимлид». Разместим их в полях «Столбцы», чтобы придать таблице нужный вид, и в поле «Значение», чтобы автоматически посчитать количество проектов. У нас появляется желтое поле вверху. 

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

Открылось окошко создания отношений. В «Таблица» выберем основную таблицу (с проектами), в «Связанная таблица» — второй лист (с тимлидами). В «Столбец» и «Связанный столбец» должны быть одни и те же столбцы. Чтобы связать две таблицы, в них должен быть общий столбец, по которому и будет создано отношение. У нас это «Команда».

Картинка с сайта: blog.skillfactory.ru

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

Картинка с сайта: blog.skillfactory.ru

Полезные функции сводной таблицы

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

Вычисляемые поля

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

В разделе «Анализ сводной таблицы» нужно найти выпадающее меню «Поля, элементы и наборы». Кликаем и выбираем «Вычисляемое поле».

Картинка с сайта: blog.skillfactory.ru

Появляется окно, в котором можно написать имя нового столбца, у нас это «Расходы команды», и выбрать столбцы, которые понадобятся для расчета нового. Мы выбираем «Бюджет» (он сам подставится в поле «Формула») и дописываем умножение на 0,03 (3% от бюджета).

Картинка с сайта: blog.skillfactory.ru

После нажатия «Ок» в нашей сводной таблице появился новый столбец. Все посчитано!

Картинка с сайта: blog.skillfactory.ru

Рекомендуемые таблицы

Рядом с иконкой создания сводной таблицы есть и другая опция — «Рекомендуемые сводные таблицы». Эта функция предлагает шаблоны сводных таблиц для данных. Будет полезна тем, кто начинает работать со сводными таблицами. Можно не только сделать то, что нужно, но и посмотреть, в каких полях конструктора расположены разные столбцы, и глубже понять принцип работы сводных таблиц.

Картинка с сайта: blog.skillfactory.ru

Подводя итог

В этой статье мы рассмотрели, как создать сводную таблицу в Excel. Если что-то не получилось сразу, не расстраивайтесь, попробуйте еще раз. И через некоторое время вы сможете сказать: «Я люблю Excel!».

Картинка с сайта: blog.skillfactory.ru

Напоследок несколько советов, которые помогут вам использовать сводные таблицы более эффективно:

• Продумайте, какие данные вы хотите проанализировать. Что вы хотите узнать? Какая информация вам нужна для этого?
• Выберите правильные поля для вашей сводной таблицы. Они определяют, как будут группироваться ваши данные и какие вычисления будут выполняться.
• Используйте фильтры и сортировку, чтобы настроить свою сводную таблицу. Фильтры и сортировка могут помочь вам сосредоточиться на конкретных данных или увидеть данные в новом свете.
• Добавьте диаграммы и графики к своей сводке. Они помогут визуализировать данные и сделать их более понятными.

А если захотите пойти дальше в освоении Excel — можете познакомиться с курсом «Excel + Google-таблицы».

Иллюстрация: Meery Mary для Skillbox Media

Картинка с сайта: skillbox.ru

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

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

Разберёмся, для чего нужны сводные таблицы. На конкретном примере покажем, как их создать, настроить и использовать. В конце расскажем, можно ли делать сводные таблицы в «Google Таблицах».

Подписывайтесь на наш телеграм-канал «Котлер любит котиков | Школа маркетинга и управления Skillbox». Здесь мы подкидываем идеи для нестандартной рекламы, дарим книги, обсуждаем новости и важные темы для диджитал-специалистов и руководителей.

А ещё постим мемчики и вакансии, которые могут вас заинтересовать. Например, недавно выкладывали реальную вакансию директора по расслабону с зарплатой 200 000 рублей в месяц ?

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

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

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

Картинка с сайта: skillbox.ru

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

Разберёмся пошагово, как это сделать с помощью сводной таблицы.

---

Создаём сводную таблицу

Чтобы сводная таблица сработала корректно, важно соблюсти несколько требований к исходной:

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

Теперь переходим во вкладку «Вставка» и нажимаем на кнопку «Сводная таблица».

Картинка с сайта: skillbox.ru

Появляется диалоговое окно. В нём нужно заполнить два значения:

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

В нашем случае выделяем весь диапазон таблицы продаж вместе с шапкой. И выбираем «Новый лист» для размещения сводной таблицы — так будет проще перемещаться между исходными данными и сводным отчётом. Жмём «Ок».

Картинка с сайта: skillbox.ru

Excel создал новый лист. Для удобства можно сразу переименовать его.

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

Картинка с сайта: skillbox.ru

---

Настраиваем сводную таблицу и получаем результат

В верхней части панели настроек находится блок с перечнем возможных полей сводной таблицы. Поля взяты из заголовков столбцов исходной таблицы: в нашем случае это «Марка, модель», «Цвет», «Год выпуска», «Объём», «Цена», «Дата продажи», «Продавец».

Нижняя часть панели настроек состоит из четырёх областей — «Значения», «Строки», «Столбцы» и «Фильтры». У каждой области своя функция:

• «Значения» — проводит вычисления на основе выбранных данных из исходной таблицы и относит результаты в сводную таблицу. По умолчанию Excel суммирует выбранные данные, но можно выбрать другие действия. Например, рассчитать среднее, показать минимум или максимум, перемножить.

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

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

Настроить сводную таблицу можно двумя способами:

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

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

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

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

После этого в левой части листа появится первый блок сводной таблицы: фамилии менеджеров по продажам.

Картинка с сайта: skillbox.ru

Теперь добавим модели автомобилей, которые эти менеджеры продали. По такому же принципу перетянем поле «Марка, модель» в область «Строки».

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

Картинка с сайта: skillbox.ru

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

Чтобы сводная таблица самостоятельно суммировала эти значения, перетащим поля «Марка, модель» и «Цена» в область «Значения».

Картинка с сайта: skillbox.ru

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

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

---

Настраиваем фильтры сводной таблицы

Чтобы можно было фильтровать информацию сводной таблицы, нужно перенести требуемые поля в область «Фильтры».

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

Картинка с сайта: skillbox.ru

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

В блоке фильтров нажмём на стрелку справа от поля «Год выпуска»:

Картинка с сайта: skillbox.ru

В появившемся окне уберём галочку напротив параметра «Выделить все» и поставим её напротив параметра «2017». Закроем окно.

Картинка с сайта: skillbox.ru

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

Картинка с сайта: skillbox.ru

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

---

Проводим дополнительные вычисления

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

Кликнем правой кнопкой на любое значение цены в таблице. Выберем параметр «Дополнительные вычисления», затем «% от общей суммы».

Картинка с сайта: skillbox.ru

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

Картинка с сайта: skillbox.ru

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

Картинка с сайта: skillbox.ru

Чтобы снова раскрыть данные об автомобилях — нажимаем +.

Чтобы значения снова выражались в рублях — через правый клик мыши возвращаемся в «Дополнительные вычисления» и выбираем «Без вычислений».

---

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

Предположим, в исходную таблицу внесли ещё две продажи последнего дня квартала.

Картинка с сайта: skillbox.ru

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

Переходим на лист сводной таблицы. Во вкладке «Анализ сводной таблицы» нажимаем кнопку «Изменить источник данных».

Картинка с сайта: skillbox.ru

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

Картинка с сайта: skillbox.ru

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

Картинка с сайта: skillbox.ru

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

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

Картинка с сайта: skillbox.ru

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

Теперь у менеджера Соколова П. изменились данные в столбце «Цена, руб.».

Картинка с сайта: skillbox.ru

Как использовать сводные таблицы в «Google Таблицах»? Нужно перейти во вкладку «Вставка» и выбрать параметр «Создать сводную таблицу». Дальнейший ход действий такой же, как и в Excel: выбрать диапазон таблицы и лист, на котором её нужно построить; затем перейти на этот лист и в окне «Редактор сводной таблицы» указать все требуемые настройки. Результат примет такой вид:

Картинка с сайта: skillbox.ru

Картинка с сайта: skillbox.ru

Курс-тренажёр: «Excel + «Google Таблицы» с нуля до PRO»
Узнать о курсе

Сборка данных со всех листов книги в одну таблицу

Постановка задачи

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

Картинка с сайта: www.planetaexcel.ru

Давайте будем исходить из следующих соображений:

• Структура и столбцов на всех листах одинаковая.
• Количество строк на всех листах разное.
• Листы могут в будущем добавляться или удаляться.

Наша задача — собрать все данные со всех листов в одну таблицу, чтобы потом с ней работать (фильтровать, сортировать, построить сводную и т.д.) Сделать это можно разными способами, но самыми удобными будут, пожалуй, Power Query и макросы.

Способ 1. Сборка данных с листов с помощью Power Query

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

Шаг 1. Подключаемся к файлу

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

Если у вас Excel 2010-2013 и вы установили Power Query как отдельную надстройку, то откройте вкладку Power Query, если у вас Excel 2016 или новее, то вкладку Данные (Data). Нажмите кнопку Получить данные / Создать запрос — Из файла — Книга Excel (Get Data / New Query — From file — From Excel) и укажите наш файл с исходными листами:

Картинка с сайта: www.planetaexcel.ru

В появившемся окне Навигатора (Navigator) выберите слева любой лист и нажмите в правом нижнем углу кнопку Преобразовать данные (Transform Data) или Изменить (Edit):

Картинка с сайта: www.planetaexcel.ru

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

Картинка с сайта: www.planetaexcel.ru

То, что останется после удаления шагов — это список всех объектов, которые Power Query «видит» во внешних файлах, а это:

• листы (Sheet)
• «умные таблицы» (Table)
• именованные диапазоны (Defined Name)
• области печати (Print Area), которые, по сути, являются одним из видов именованного диапазона

Шаг 2. Отбираем нужные листы

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

Во-первых, легко можно отфильтровать нужные объекты по типу по столбцу Kind. Например, если вам нужны только листы:

Картинка с сайта: www.planetaexcel.ru

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

В-третьих, если вы точно знаете размер таблиц, которые вам нужны, то можно легко добавить к нашему списку вычисляемый столбец с формулой, выводящей количество столбцов или строк и использовать потом эти числа для отбора. Для этого выберем на вкладке Добавление столбца — Настраиваемый столбец (Add Column — Custom Column) и введём в открывшееся окно следующую формулу (с учётом регистра):

Картинка с сайта: www.planetaexcel.ru

Для подсчёта количества строк можно использовать аналогичную функцию Table.RowCount. Получившийся столбец затем можно использовать для фильтрации «мусорных» таблиц.

В-четвёртых, можно извлечь с каждого листа содержимое любой ячейки (например, А1) и использовать его для отбора. Например, если там нет слова «Товар«, то это не наш лист. Для извлечения нужно будет также добавить вычисляемый столбец с такой конструкцией:

=[Data][Column1]{0}

Здесь:

• [Data] — имя столбца, где в каждой ячейке лежат таблицы с содержимым каждого листа (убийственная формулировка для рядового пользователя Excel, да, я знаю)
• [Column1] — имя столбца на листе, из которого мы хотим извлечь данные
• {0} — номер строки (считая с нуля), откуда мы хотим взять данные

Картинка с сайта: www.planetaexcel.ru

После фильтрации «мусора» все добавленные вспомогательные столбцы можно, конечно же, спокойно удалить, оставив только колонки Name и Data.

Шаг 3. Разворачиваем таблицы

Теперь развернём содержимое таблиц в одно целое, используя кнопку с двойными стрелками в заголовке столбца Data, отключив флажок Использовать исходное имя столбца как префикс (Use original column name as prefix):

Картинка с сайта: www.planetaexcel.ru

После нажатия на ОК Power Query соберёт для нас все данные в одну мегатаблицу со всех отобранных листов нашего файла:

Картинка с сайта: www.planetaexcel.ru

Останется лишь «навести блеск», а именно:

1. Поднять первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков (Use first row as headers) на вкладке Главная (Home).
2. Переименовать первый столбец в Город двойным щелчком на заголовку.
3. Удалить повторяющиеся шапки таблиц, попавшие в одну кучу вместе с данными, используя фильтр по столбцу Товар.

Всё. Осталось только дать нашему запросу подходящее имя (например, Сборка) в панели справа и выгрузить затем собранные данные обратно в Excel кнопкой Закрыть и загрузить на вкладке Главная (Home — Close & Load):

Картинка с сайта: www.planetaexcel.ru

В будущем, при любых изменениях в исходном файле достаточно будет лишь обновить наш запрос, щелкнув по собранной таблице правой кнопкой мыши и выбрав команду Обновить (Refresh) или такой же кнопкой на вкладке Данные (Data) или сочетанием клавиш Ctrl+Alt+F5.

Плюсы такого подхода:

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

Минусы этого способа:

• Собираются только значения, т.е. формулы с исходных листов не сохраняются.
• Названия столбцов должны на всех листах совпадать с точностью до регистра.
• Нельзя выбрать какой именно диапазон берётся с каждого листа — это определяется автоматически (берётся всё, что есть).
• Для обновления нужен Excel 2016 или новее или установленная надстройка Power Query.

Способ 2. Сборка данных с листов макросом на VBA

Похожего результата можно добиться и с помощью более «классического» подохода — макросом на VBA. Для этого на вкладке Разработчик (Developer) нажмите кнпоку Visual Basic или воспользуйтесь сочетанием клавиш Alt+F11. В открывшемся окне добавьте новый модуль через меню Insert — Module и скопируйте туда текст вот такого макроса:

Sub CollectDataFromAllSheets()
    Dim ws As Worksheet
    
    Set wbCurrent = ActiveWorkbook
    Workbooks.Add
    Set wbReport = ActiveWorkbook
    
    'копируем на итоговый лист шапку таблицы из первого листа
    wbCurrent.Worksheets(1).Range("A1:D1").Copy Destination:=wbReport.Worksheets(1).Range("A1")
    
    'проходим в цикле по всем листам исходного файла
    For Each ws In wbCurrent.Worksheets
    
        'определяем номер последней строки на текущем листе и на листе сборки
        n = wbReport.Worksheets(1).Range("A1").CurrentRegion.Rows.Count
        
        'задаем исходный диапазон, который надо скопировать с каждого листа - любой вариант на выбор:
        Set rngData = ws.Range("A1:D5")            'фиксированный диапазон или
        Set rngData = ws.UsedRange                 'всё, что есть на листе или
        Set rngData = ws.Range("F5").CurrentRegion    'область, начиная от ячейки F5 или
        Set rngData = ws.Range("A2", ws.Range("A2").SpecialCells(xlCellTypeLastCell))    'от А2 и до конца листа
        
        'копируем исходный диапазон и вставляем в итоговую книгу со следующей строки
        rngData.Copy Destination:=wbReport.Worksheets(1).Cells(n + 1, 1)
        
    Next ws
End Sub

Запустить созданный макрос можно на вкладке Разработчик кнопкой Макросы (Developer — Macros) или с помощью сочетания клавиш Alt+F8. Макрос автоматически создаст новую книгу и скопирует туда нужные вам данные.

Плюсы такого подхода:

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

Минусы этого способа:

• Последовательность столбцов на всех листах должна быть одинаковой, т.к. происходит, по сути, тупое копирование таблиц друг-под-друга.
• Защита от макросов должна быть отключена.
• Быстрого обновления, как это было с Power Query, здесь, к сожалению, не будет. При изменении исходных данных придётся запустить макрос повторно.

Способ 3. Готовый макрос из надстройки PLEX

Если лень возиться с макросами или Power Query, то можно пойти по пути наименьшего сопротивления — использовать готовый макрос (кнопка Собрать) из моей надстройки PLEX для Excel. Это, может, и не спортивно, но зато эффективно:

Картинка с сайта: www.planetaexcel.ru

В общем, выбирайте любой удобный вам вариант и действуйте. Выбор — это всегда хорошо.

Ссылки по теме

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