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

Обновлено: 21.11.2024

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

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

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

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

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

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

Добавление двух наборов данных

Первый набор данных относится к магазину 1 и выглядит следующим образом.

Мы видим номер транзакции в 1-м столбце и сумму продажи в 5-м столбце.

Второй набор данных относится к Магазину 2 и выглядит следующим образом.

Обратите внимание, что номер транзакции во 2-м столбце и сумма продажи в 4-м столбце.

Это не будет проблемой, потому что выбранный нами инструмент переупорядочивает данные на основе имен столбцов перед их отправкой в ​​фидерную таблицу.

Познакомьтесь с вашим новым лучшим другом, Power Query

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

Хотя это и не требуется, Power Query работает более эффективно, если исходные данные представлены в правильном формате таблицы Excel.

Чтобы преобразовать две наши существующие «обычные» таблицы в правильные таблицы Excel, щелкните в любом месте первой таблицы (Хранилище данных 1) и нажмите CTRL-T на клавиатуре. . Появится диалоговое окно «Создать таблицу». Нажмите OK, чтобы выбрать значения по умолчанию.

На ленте "Конструктор таблиц" щелкните поле "Имя таблицы" (крайний левый край ленты) и переименуйте таблицу в "TableStore1" (без пробелов).< /p>

Повторите описанный выше процесс для второй таблицы (Data Store 2) и переименуйте обновленную таблицу в TableStore2 (без пробелов).

Использование Power Query для объединения двух наборов данных

Затем мы переносим две таблицы в Power Query.

Power Query будет использоваться для добавления (т. е. «складывания») двух таблиц в одну таблицу.

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

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

Шаг 1. Добавьте первую таблицу в Power Query

Нажмите в любом месте первой таблицы ("TableStore1") и выберите "Данные" (вкладка) -> "Получить и преобразовать данные" (группа) -> "Из листа" (прежнее название "Таблица/диапазон"). ).

Откроется новое окно с редактором Power Query.

Небольшая корректировка этих данных заключается в удалении информации о времени в столбце «Дата».

Это легко сделать, выбрав заголовок столбца «Дата», затем щелкнув «Тип данных: Дата/время» на ленте «Главная» и изменив тип данных на «Дата».

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

Шаг 2. Добавьте информацию об отслеживании для каждой транзакции

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

На ленте "Добавить столбец" нажмите "Пользовательский столбец" (крайний левый край ленты).

В диалоговом окне "Пользовательский столбец" присвойте новому столбцу имя "Магазин" и введите следующую формулу в большее поле формулы.

Результатом является новый столбец (крайний правый угол таблицы), в котором для каждой транзакции отображается «Магазин 1».

Как и в столбце "Дата", измените тип данных для недавно добавленного столбца "Магазин" на тип данных TEXT.

Шаг 3. Добавьте вторую таблицу в Power Query

Здесь мы можем сэкономить несколько шагов, скопировав запрос «TableStore1» и внеся несколько целенаправленных изменений.

Если щелкнуть шаг SOURCE на панели «Примененные шаги» (справа от окна редактора), вы увидите ссылку на таблицу «TableStore1».

Мы сделаем копию этого запроса и изменим указатель шага SOURCE с «TableStore1» на «TableStore2».

На панели "Запросы" (слева от окна редактора) щелкните правой кнопкой мыши запрос "TableStore1" и выберите "Дублировать".

Переименуйте повторяющийся запрос с «TableStore1 (2)» на «TableStore2».

Выберите шаг SOURCE на панели «Примененные шаги».

В строке формул измените запись с «TableStore1» на «TableStore2» и нажмите клавишу ВВОД, чтобы зафиксировать изменение.

Не забудьте обновить шаг «Добавленный пользовательский» в нижней части панели «Примененные шаги», чтобы использовать метку «Магазин 2» в столбце «Магазин».

Шаг 4. Закройте запросы

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

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

В диалоговом окне "Импорт данных" выберите параметр "Только создать соединение" и нажмите "ОК".

Теперь вы увидите два запроса в правой части экрана на панели «Запросы и подключения».

Шаг 5. Добавление/складывание таблиц в одну таблицу

Чтобы объединить две таблицы в одну, которая будет использоваться для управления сводной таблицей, нажмите Данные (вкладка) -> Получить и преобразовать данные (группа) -> Получить данные -> Объединить запросы -> Добавить.

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

Вернувшись в редактор Power Query, вы увидите только что созданный запрос с именем «Append1», в котором результаты двух предыдущих таблиц запроса объединены в единую единую таблицу.

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

Шаг 6. Переименуйте добавленный запрос и выведите его в таблицу Excel

Позвольте переименовать добавленный запрос результатов в «AllStores», затем нажмите нижнюю часть кнопки «Закрыть и загрузить» и выберите «Закрыть и загрузить…»

В диалоговом окне "Импорт данных" выберите "Таблица" и "Новый рабочий лист" в качестве параметров назначения и нажмите "ОК".

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

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

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

Примечание. Чтобы просмотреть расшифровку видео, перейдите на страницу видео с несколькими листами.

Введение в несколько диапазонов консолидации

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

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

Если возможно, переместите данные на один лист или сохраните их в базе данных, например в Microsoft Access, и у вас будет больше возможностей для создания сводной таблицы.

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

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

1. Чтобы открыть мастер сводных таблиц и диаграмм, выберите любую ячейку на листе, затем нажмите клавиши ALT+D, а затем нажмите клавишу P. Этот ярлык используется, потому что в более ранних версиях Excel мастер был указан в меню «Данные» как Команда отчета сводной таблицы и сводной диаграммы.
2. Нажмите "Несколько диапазонов консолидации", затем нажмите "Далее".

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

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

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

Удалить поля, не содержащие значимых данных

В этом примере поля "Цвет", "Дата", "Цена" и "Показ" содержат текст или числа, которые не имеют смысла в этом отчете, поэтому они будут удалены.

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

Изменить вычисление поля значения

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

ПРИМЕЧАНИЕ. Это повлияет на все значения — их нельзя изменить по отдельности.

1. Нажмите правой кнопкой мыши на одно из значений.
2. Наведите указатель на Суммировать значения и нажмите "Суммировать".

Удалить общий итог для строк

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

1. Нажмите правой кнопкой мыши на заголовок общего итога по строкам.
2. Нажмите «Удалить общую сумму».

Изменить ярлыки

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

1. Нажмите на любой ярлык в сводной таблице, введите новый ярлык и нажмите клавишу ВВОД.
2. Например, нажмите на ярлык Страница 1, введите Регион и нажмите клавишу ВВОД.

На приведенном ниже снимке экрана метки изменены. Заголовок «Ярлыки столбцов» заменен пробелом.

Изменить макет

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

1. Выберите любую ячейку в сводной таблице.
2. На ленте в разделе "Инструменты сводных таблиц" нажмите вкладку "Дизайн".
3. В группе «Макет» нажмите «Макет отчета», а затем нажмите «Структура формы».
4. В списке полей сводной таблицы перетащите поле «Страница1» из области «Фильтры» в область «Строка» над существующим полем «Строка».
5. Измените поле строки на элемент, теперь оно находится в отдельном столбце.

Ограничения множественной консолидации

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

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

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

Альтернативы множественной консолидации

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

Объединение таблиц с помощью Power Query

Если у вас есть версия Excel, поддерживающая надстройку Microsoft Power Query, вы можете использовать ее для объединения данных в двух или более таблицах. Таблицы могут находиться в одной книге или в разных файлах.

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

Перейдите на страницу объединения таблиц с Power Query, чтобы получить письменные инструкции и образец файла.

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

Создать запрос объединения

Если вы не можете объединить данные на одном листе, другим решением является создание именованных диапазонов в файле Excel и использование Microsoft Query (MS Query) для объединения данных.

Создание запроса объединения вручную

В Excel вы можете открыть инструмент Microsoft Query и написать оператор SQL, чтобы создать запрос на объединение (полное внешнее соединение) для объединения нескольких таблиц. Затем используйте результат в качестве исходных данных сводной таблицы.

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

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

Макрос запроса на объединение -- листы в одном файле

Вместо того, чтобы вручную настраивать запрос на объединение, вы можете использовать код из примера файла из Excel MVP Кирилла Лапина (KL) с поправками Гектора Мигеля Ороско Диаса.

1. Измените имена листов

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

2. Изменить местоположение сводной таблицы

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

3. Запустите макрос

Затем, после внесения этих небольших изменений, нажмите кнопку на листе, и сводная таблица будет создана автоматически.

Макрос запроса на объединение -- данные в нескольких файлах Excel

Если вам нужно объединить данные в несколько файлов, вот несколько вариантов использования макросов, предоставленных экспертом по Excel Кириллом Лапиным.

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

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

Как объединить несколько листов в сводную таблицу в Excel?

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

Объединить несколько листов в сводную таблицу

Пожалуйста, сделайте следующее, чтобы объединить данные нескольких рабочих листов в сводную таблицу.

<р>1. Нажмите «Настроить панель быстрого доступа» > «Дополнительные команды», как показано на снимке экрана ниже.

<р>2. В диалоговом окне «Параметры Excel» вам необходимо:

2.1 Выберите «Все команды» в раскрывающемся списке «Выбрать команды из»;

2.2 Выберите мастер сводных таблиц и диаграмм в списке команд;

2.3 Нажмите кнопку "Добавить";

2.4 Нажмите кнопку ОК. Смотрите скриншот:

<р>3. Затем на панели быстрого доступа появится кнопка Мастер сводных таблиц и диаграмм. Нажмите кнопку, чтобы открыть мастер сводных таблиц и диаграмм. В мастере выберите параметр «Несколько диапазонов консолидации» и параметр «Сводная таблица», а затем нажмите кнопку «Далее». Смотрите скриншот:

<р>4. Во втором мастере выберите параметр Я создам поля страницы и нажмите кнопку Далее.

<р>5. В третьем мастере нажмите кнопку, чтобы выбрать данные из первого рабочего листа, который вы объедините в сводную таблицу, и нажмите кнопку «Добавить». Затем повторите этот шаг, чтобы добавить данные других рабочих листов в поле «Все диапазоны». Выберите вариант 0 в разделе «Сколько полей страницы вам нужно» и нажмите кнопку «Далее».

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

<р>6. В последнем мастере выберите, куда вы хотите поместить сводную таблицу (здесь я выбираю параметр «Новый рабочий лист»), а затем нажмите кнопку «Готово».

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

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

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

Но есть одна особенность:

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

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

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

Проблема!

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

Вот как вы получаете дамп данных в Excel.

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

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

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

Но дело в том, что эти методы требуют кодирования, копирования или повторения.

Решение

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

Поверьте, вам понравится!

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

Загрузите этот файл данных, чтобы продолжить.

Шаги по созданию сводной таблицы из нескольких листов

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

• Прежде всего выберите все данные на каждом листе и назовите их.
  • Данные за 2005 год названы — Year2005
  • Данные за 2006 год названы — Year2006
  • Данные за 2007 год названы — Year2007
  • Данные за 2007 год названы — Year2007
  • После этого примените таблицы данных ко всем данным на листах Fours.
    • Выберите любую ячейку в диапазоне данных.
    • Используйте Ctrl + T, чтобы преобразовать данные на каждом листе в таблицу.
    • Убедитесь, что каждый раз установлен флажок "В моей таблице есть заголовки".
    • Повторите это для всех 4 лет (листы).
    • Начнем консолидировать эти данные на новом листе (сочетание клавиш для добавления нового листа: Shift + F11).
    • На вкладке "Данные" нажмите "Из других источников" -> выберите "Из Microsoft Query".
    • В поле выбора источника данных:
      • Нажмите "Файлы Excel" и нажмите "ОК".
      • Выберите путь к файлу Excel, а затем выберите файл, нажмите "ОК".
      • Именованные диапазоны появятся в поле "Мастер запросов — выбор столбцов".
      • В мастере запросов:
        • Перетащите каждый именованный диапазон (с помощью кнопки со стрелкой) в «Столбцы в области запроса».
        • Нажмите «Далее» — забудьте об ошибке.
        • И нажмите "ОК".
        • Теперь у нас есть все данные в нашем редакторе запросов, осталось только объединить данные из всех именованных диапазонов. Для этого нажмите маленькую кнопку SQL.
        • В поле SQL удалите весь текст, напишите новый запрос и нажмите "ОК".

        Выбрать * из 2005 года
        Объединить все
        Выбрать * из 2006 года
        Объединить все
        Выбрать * из 2007 года
        Объединить все
        Выбрать * из 2008 года< /p>

        • Теперь таблица, которая появляется на экране, содержит данные со всех 4 листов.
        • Все, что нам нужно сделать, это перейти на вкладку "Файл" и импортировать эту таблицу в Excel.
        • В конце импортируйте данные обратно в Excel в виде сводной таблицы.
          • В меню "Файл" -> нажмите "Вернуть данные в Microsoft Excel".
          • Вы видите, что всего (со всех 4 листов) у нас 592 записи.
          • Кроме того, если вы добавите дополнительные данные на любой из 4 листов, сводная таблица обновится, как только вы ее обновите.

          ПРИМЕЧАНИЕ. Работа со сводными таблицами — это один из ПРОМЕЖУТОЧНЫХ НАВЫКОВ EXCEL.

          Об авторе

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

          13 мыслей

          Оставить комментарий Отменить ответ

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

          Привет, Пунит!
          Спасибо, что поделились этой замечательной работой. Нет ссылки на файл для скачивания для практики.
          Вы можете помочь?

          Спасибо, что поделились, мне помогли настроить SQL-запрос, мое решение, приведенное ниже, выбирает только определенные столбцы (используя имена вкладок и столбцов)

          Еще один момент: кажется, что Excel сохраняет фиксированное имя файла в строке запроса, поэтому вы не можете легко переименовать файл, не обновив строку запроса в разделе «Данные» > «Подключения» > «Свойства» (существующего запроса)

          Пример SQL-запроса:
          ВЫБЕРИТЕ CoinsSheet.Date, CoinsSheet.Name, CoinsSheet.Amount, CoinsSheet.Project FROM CoinsSheet CoinsSheet
          Объединить все
          SELECT DonationsSheet.Date, DonationsSheet.Name, DonationsSheet .Amount, DonationsSheet.Project FROM DonationsSheet DonationsSheet
          Объединить все
          SELECT PledgesSheet.Date, PledgesSheet.Name, PledgesSheet.Amount, PledgesSheet.Project FROM PledgesSheet PledgesSheet

          Привет, спасибо за публикацию — в большинстве случаев это очень хорошо сработало для моего журнала расходов. Однако я столкнулся с проблемой, из-за которой некоторые из ключевых полей в моем отчете возвращаются как [пустые], хотя в исходной таблице есть запись. Знаете ли вы какую-либо причину, по которой это может быть так?

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

          Читайте также:

            
          • Как вернуть панель закладок в опере сверху
            
          • Как распределить стоимость доставки по стоимости продукта в Excel
            
          • Как сделать заметку в Word
            
          • Создание рингтонов для Android
            
          • Формат Orf, чем открыть