Объединение данных с нескольких листов
Если данные, которые требуется проанализировать, представлены на нескольких листах или в нескольких книгах, их можно объединить на одном листе с помощью команды «Консолидация». Например, если есть отдельный лист расходов для каждого регионального представительства, с помощью консолидации можно создать на базе этих данных корпоративный лист расходов. Такой лист может содержать итоговые и средние данные по продажам, текущим уровням запасов и наиболее популярным продуктам в рамках всей организации.
Тип консолидации следует выбирать с учетом того, как выглядят объединяемые листы. Если данные на листах расположены единообразно (названия строк и столбцов могут при этом различаться), воспользуйтесь консолидацией по расположению. Если же на листах для соответствующих категорий используются одинаковые названия строк и столбцов (данные при этом могут быть расположены по-разному), используйте консолидацию по категории.
Консолидация по расположению
Для консолидации по расположению диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов.
- Откройте каждый исходный лист и убедитесь, что данные на каждом листе расположены в одинаковом положении.
- На конечном листе щелкните верхнюю левую ячейку области, в которой требуется разместить консолидированные данные.
Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.
Консолидация по категории
Для консолидации по категории диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов. Кроме того, категории должны быть последовательно помечены. Например, если один из столбцов называется Сред. , а другой — Среднее, консолидация не просуммирует эти столбцы.
- Откройте каждый из исходных листов.
- На конечном листе щелкните верхнюю левую ячейку области, в которой требуется разместить консолидированные данные.
Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.
Примечание: Любые названия, не совпадающие с названиями в других исходных областях, могут привести к появлению в консолидированных данных отдельных строк или столбцов.
Важно: Microsoft Office для Mac 2011 больше не поддерживается. Перейдите на Microsoft 365, чтобы работать удаленно с любого устройства и продолжать получать поддержку.
Консолидация по расположению
Для консолидации по расположению диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов.
- Откройте каждый из исходных листов и убедитесь в том, что данные на них расположены одинаково.
- На конечном листе щелкните верхнюю левую ячейку области, в которой требуется разместить консолидированные данные.
Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.
Консолидация по категории
Для консолидации по категории диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов. Кроме того, категории должны быть названы одинаково. Например, если один из столбцов называется Сред. , а другой — Среднее, консолидация не просуммирует эти столбцы.
- Откройте каждый из исходных листов.
- На конечном листе щелкните верхнюю левую ячейку области, в которой требуется разместить консолидированные данные.
Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.
Примечание: Любые названия, не совпадающие с названиями в других исходных областях, могут привести к появлению в консолидированных данных отдельных строк или столбцов.
Использование нескольких таблиц для создания сводной таблицы
Сводные таблицы удобно использовать для анализа данных и создания отчетов с ними. А если это реляционные данные (т. е. такие, которые хранятся в отдельных таблицах, но при этом их можно объединить благодаря общим значениям), вы можете создать сводную таблицу.
 |
 |
Чем отличается эта сводная таблица? Обратите внимание, что в списке полей справа отображается не одна таблица, а целый набор таблиц. Каждая из этих таблиц содержит поля, которые можно объединить в одну сводную таблицу для получения различных срезов данных. Не требуются ручное форматирование и подготовка данных. Сразу после импорта данных можно создать сводную таблицу на основе связанных таблиц.
Создание сводной таблицы с использованием нескольких таблиц
Ниже приведены три основных шага для добавления нескольких таблиц в список полей сводной таблицы.
Шаг 1. Импорт связанных таблиц из базы данных
Импортируйте их из реляционной базы данных, например Microsoft SQL Server, Oracle или Access. Вы можете импортировать несколько таблиц одновременно:
Может потребоваться установить дополнительное клиентское программное обеспечение. Обратитесь к администратору базы данных, чтобы уточнить, есть ли такая необходимость.
Шаг 2. Добавление полей в сводную таблицу
Обратите внимание: список полей содержит несколько таблиц.
Это все таблицы, выбранные вами во время импорта. Каждую таблицу можно развернуть и свернуть для просмотра ее полей. Так как таблицы связаны, вы можете создать сводную таблицу, перетянув поля из любой таблицы в область ЗНАЧЕНИЯ, СТРОКИ или СТОЛБЦЫ. Вы можете:
- Перетащите числовые поля в область ЗНАЧЕНИЯ. Например, если используется образец базы данных Adventure Works, вы можете перетащить поле «ОбъемПродаж» из таблицы «ФактПродажиЧерезИнтернет».
- Перетащите поля даты или территории в область СТРОКИ или СТОЛБЦЫ, чтобы проанализировать объем продаж по дате или территории сбыта.
Шаг 3. Создание связей при необходимости
Иногда нужно создать связь между двумя таблицами, прежде чем использовать их в сводной таблице. Если появится сообщение о необходимости такой связи между таблицами, щелкните Создать, чтобы начать работу с ними.
Использование модели данных для создания новой сводной таблицы
Примечание Модели данных не поддерживаются в Excel для Mac.
Реляционные базы данных — это не единственный источник данных, который поддерживает работу с несколькими таблицами в списке полей сводной таблицы. Вы можете использовать таблицы в своей книге или импортировать каналы данных, а затем интегрировать их с другими таблицами данных в книге. Дополнительные сведения см. в статье Импорт нескольких таблиц из других источников данных.
Чтобы все эти несвязанные данные работали вместе, нужно каждую таблицу добавить в модель данных, а затем создать связи между ними с помощью соответствующих значений полей. Дополнительные сведения см. в статьях Добавление данных с листа в модель данных с помощью связанной таблицы,Создание связи между двумя таблицами иСоздание связей в представлении схемы.
После создания модели данных эти данные можно использовать в анализе. Ниже описано, как создать новую сводную таблицу или сводную диаграмму с помощью модели данных в книге.
- Щелкните любую ячейку на листе.
- Выберите Вставка и щелкните стрелку вниз элемента Сводная таблица.
- Выберите Из внешнего источника данных.
- Нажмите Выбрать подключение.
- На вкладке Таблицы в разделе Модель данных этой книги выберите Таблицы в модели данных книги.
См. также
- Создание модели данных в Excel
- Получение данных с помощью надстройки Power Pivot
- Упорядочение полей сводной таблицы с помощью списка полей
- Создание сводной таблицы для анализа данных на листе
- Создание сводной таблицы для анализа внешних данных
- Создание сводной таблицы, подключенной к наборам данных Power BI
- Изменение диапазона исходных данных для сводной таблицы
- Обновление данных в сводной таблице
- Удаление сводной таблицы
Как свести таблицы в excel из разных файлов
Объединение таблиц из нескольких файлов
Для того, чтобы объединить и просуммировать данные из таблиц, расположенных в отдельных файлах, можно воспользоваться консолидацией данных. Для этого:
-
В каждом файле задаем имя диапазонов для наших таблиц. Для этого выделяем всю таблицу и в строке адресов пишем уникальное имя диапазона (Продажи2012) без пробелов (или с нижним подчеркиванием). Выполняем это для всех наших таблиц.
В отдельном файле, в котором будем выполнять объединение, выбрав ячейку, переходим на вкладку «Данные» и нажимаем на кнопку «Консолидация»
В строке «ССЫЛКА» сдвигаемся вправо мышью на конец ссылки, выделяя последние элементы (применять стрелки нельзя) и нажимаем на кнопку выбора диапазона.
В окне после «!» дописываем имя диапазона, из которого будут браться данные так, как присвоили в исходном файле (Продажи2012)
Повторяем для всех остальных файлов те же самые действия и нажимаем «ОК». Поставив все галки ниже в окне, в том числе «Создавать связи с исходными данными» — консолидированная таблица будет зависеть от параметров в исходных данных.
Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
Консолидация таблиц из нескольких файлов Excel
В больших компаниях, с несколькими отделами или подразделениями, часто встречается потребность в создании общего реестра на основе данных от каждого отдела. Не всегда этот реестр можно вести совместно, поэтому приходится его собирать по кусочкам. Можно автоматизировать этот процесс с помощью подобного решения.
Описание задачи
- Необходимо объединить таблицы, содержащиеся в большом количестве Excel файлов. При этом все они хранятся в разных подкаталогах рабочей папки.
- После создания консолидированного файла со всеми таблицами, необходимо обработать данные и разбить единую таблицу на несколько, в зависимости от заданного критерия или (как в нашем случае) на основе столбцов, в которых проставляются знаки «+».
Описание программы
Программа представляет собой файл Excel, содержащее меню. Тут пользователь задает папку в которой расположены подпапки с файлами. Для удобства, выбор папки также реализован по клику на иконку.
После выбора папки останется только нажать на кнопку «Консолидация данных». Программа пройдет по всем каталогам и подкаталогам в указанной папке, найдет все файлы Excel и создаст сводную таблицу содержащую все данные из найденных файлов.
Помимо самой консолидации в программе реализована функция сортировки и группировки данных. Здесь стоит отметить, что конкретная обработка данных зависит от потребностей компании. Это может быть как проверка условий из других источников, так и всевозможные дополнительные вычисления.
В данном случае после консолидации данных была выполнена разбивка сводной таблицы на несколько подтаблиц в зависимости от наличия отметки «+». Все подтаблицы сохранялись в итоговый файл на отдельных листах Excel.