Как объединить таблицы в excel
Сопоставляйте и объединяйте данные из двух таблиц быстро и точно, даже если они находятся в разных файлах Excel.
Обновить данные в основной таблице
Обновите выбранные столбцы в основной таблице информацией из таблицы поиска. при этом старые данные будут перезаписаны.
Добавить несовпадающие строки в основную таблицу
Вставьте несовпадающие строки в конец основной таблицы после всех остальных данных. Это позволит дополнить основную таблицу новыми данными.
Игнорировать пустые ячейки в таблице поиска
Обновляйте значения в основной таблице, только если соответствующие ячейки в таблице поиска содержат данные. Это позволит избежать потери данных в основной таблице, если в таблице поиска соответствующие ячейки пусты.
Выполнить сопоставление с учетом регистра
Если регистр текста имеет значение, выполните поиск с учетом регистра, чтобы различать заглавные и прописные буквы.
Сопоставить таблицы по одному или нескольким столбцам
Автоматическое определение всех идентичных столбцов о обоих таблицах и использование любого количества таких столбцов в качестве уникального идентификатора.
Добавить новые столбцы из таблицы поиска
При необходимости перенесите несколько столбцов из таблицы поиска в основную таблицу. Так вы можете, к примеру, добавить данные за новый временной период.
Получить дополнительные совпадения
Функции поиска ищут только первое совпадение. Здесь вы можете найти все совпадения ключевого поля в таблице поиска и добавить их в конец таблицы или под строкой с таким же значением ключа.
Обновить только пустые ячейки в основной таблице
Эта опция пригодится, если вы не хотите перезаписывать какие-либо существующие значения в основной таблице.
Выделить обновленные ячейки и добавленные строки
Добавьте столбец состояния, чтобы отметить в нем изменения, или же выделите цветом в основной таблице обновлённые ячейки и добавленные строки.
Объедините таблицы Excel
именно так, как вам нужно
Обновите существующий столбец и добавьте несовпадающие строки в конец основной таблицы
Часто задаваемые вопросы
Что такое инструмент объединения таблиц и зачем он мне нужен?
Эта опция предназначена для быстрого объединения таблиц Excel путем сопоставления столбцов. Интересующие нас таблицы могут находиться в одной книге или в двух отдельных файлах Excel и иметь разное количество строк и столбцов.
С помощью мастера объединения таблиц вы можете:
- Использовать один или несколько ключевых столбцов в качестве критерия поиска.
- Добавить новые столбцы в основную таблицу или обновить существующие.
- Добавить несовпадающие строки в конец таблицы после всех остальных данных.
- Извлечь все совпадения из таблицы поиска.
- Выделить цветом обновленные строки или отметить сделанные изменения в столбце статуса.
- Обновить только пустые ячейки в основной таблице, если вы не хотите перезаписывать существующие значения.
- Обновлять данные, только если ячейки из таблицы поиска содержат значения.
Есть ли бесплатный способ сопоставить и объединить два листа Excel?
Вы можете использовать формулы ВПР или ИНДЕКС+ПОИСКПОЗ для объединения двух листов по ключевому столбцу. Если ваши знания формул Excel выше среднего, вы можете комбинировать несколько различных функций в формуле массива, чтобы выбрать сразу несколько совпадений. Возможно, вы даже могли бы найти способ искать по нескольким критериям, объединив несколько значений поиска во вспомогательном столбце. В любом случае, задача достаточно сложная, требующая знаний и времени.
Или вы можете использовать наш мастер объединения таблиц, чтобы объединить их по любому количеству ключевых столбцов за шесть простых шагов.
Как объединить два файла Excel с помощью мастера объединения таблиц?
- Выберите свою основную таблицу.
- Выберите таблицу поиска.
- Выберите ключевые столбцы для сопоставления.
- Укажите столбцы для обновления в основной таблице.
- При желании выберите столбцы, которые нужно добавить в основную таблицу.
- При необходимости выберите дополнительные параметры слияния и нажмите «Готово».
Подробный пример объединения таблиц смотрите здесь.
Как я могу выполнить поиск с учетом регистра?
Просто отметьте опцию сопоставления с учетом регистра на шаге 3. Мастер будет рассматривать строчные или прописные буквы в ключевых столбцах как разные значения.
Можно ли просто добавить недостающие столбцы из таблицы поиска без каких-либо обновлений?
Конечно. На шаге 4 не выбирайте столбцы для обновления, просто нажмите «Далее». На шаге 5 выберите столбцы, которые нужно добавить в основную таблицу.
Что, если в моей таблице поиска есть строки, которые не соответствуют ни одному ключевому столбцу в основной таблице?
Если вы хотите скопировать такие строки в основную таблицу, установите флажок «Добавить несовпадающие строки в конец основной таблицы» на шаге 6. Естественно, что если вы не хотите переносить несовпадающие строки, не используйте этот вариант :)
В моей таблице поиска есть несколько строк с одинаковыми значениями в ключевых столбцах. Эти строки содержат разную информацию. Какие из них попадут в основную таблицу?
По умолчанию мастер объединения таблиц берет первую совпадающую строку, которую он находит. Если вы хотите перенести все совпадения в свою основную таблицу, на шаге 6 выберите поле Вставить дополнительные совпадающие строки, а затем выберите либо добавление их всех в конец таблицы, либо вставьте их сразу после строки с тем же значением ключа.
Что делать, если некоторые ячейки в моих таблицах пусты?
Не беспокойтесь, мы учли этот вариант. На шаге 6 выберите один из следующих вариантов или оба сразу:
- Чтобы игнорировать пустые ячейки в таблице поиска, установите флажок Обновлять, только если ячейки из таблицы поиска содержат данные.
- Чтобы не перезаписывать существующие значения в основной таблице, выберите Обновлять только пустые и новые ячейки в основной таблице.
В моих таблицах много данных. Как я могу увидеть, что именно было обновлено?
Есть два способа определить изменения: текст и цвет. Если вы предпочитаете текстовые метки, добавьте столбец «Состояние» в основную таблицу, а затем вы сможете фильтровать обновленные строки, не обновленные строки или новые строки.
Если вам больше нравится выделение цветом, закрасьте обновленные ячейки и / или добавленные строки любым цветом по вашему выбору.
Если данные, которые требуется проанализировать, представлены на нескольких листах или в нескольких книгах, их можно объединить на одном листе с помощью команды "Консолидация". Например, если есть отдельный лист расходов для каждого регионального представительства, с помощью консолидации можно создать на базе этих данных корпоративный лист расходов. Такой лист может содержать итоговые и средние данные по продажам, текущим уровням запасов и наиболее популярным продуктам в рамках всей организации.
Тип консолидации следует выбирать с учетом того, как выглядят объединяемые листы. Если данные на листах расположены единообразно (названия строк и столбцов могут при этом различаться), воспользуйтесь консолидацией по расположению. Если же на листах для соответствующих категорий используются одинаковые названия строк и столбцов (данные при этом могут быть расположены по-разному), используйте консолидацию по категории.
Консолидация по расположению
Для консолидации по расположению диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов.
Откройте каждый из исходных листов и убедитесь в том, что данные на них расположены одинаково.
На конечном листе щелкните верхнюю левую ячейку области, в которой требуется разместить консолидированные данные.
Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.
На вкладке Данные в группе Работа с данными нажмите кнопку Консолидация.
Выберите в раскрывающемся списке функцию, которую требуется использовать для консолидации данных.
Выделите на каждом листе нужные данные.
Путь к файлу вводится в поле Все ссылки.
После добавления данных из всех исходных листов и книг нажмите кнопку ОК.
Консолидация по категории
Для консолидации по категории диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов. Кроме того, категории должны быть названы одинаково. Например, если один из столбцов называется Сред. , а другой — Среднее, консолидация не просуммирует эти столбцы.
Откройте каждый из исходных листов.
На конечном листе щелкните верхнюю левую ячейку области, в которой требуется разместить консолидированные данные.
Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.
На вкладке Данные в группе Работа с данными нажмите кнопку Консолидация.
Выберите в раскрывающемся списке функцию, которую требуется использовать для консолидации данных.
Установите флажки в группе Использовать в качестве имен, указывающие, где в исходных диапазонах находятся названия: подписи верхней строки, значения левого столбца либо оба флажка одновременно.
Выделите на каждом листе нужные данные. Не забудьте включить в них ранее выбранные данные из верхней строки или левого столбца.
Путь к файлу вводится в поле Все ссылки.
После добавления данных из всех исходных листов и книг нажмите кнопку ОК.
Примечание: Любые названия, не совпадающие с названиями в других исходных областях, могут привести к появлению в консолидированных данных отдельных строк или столбцов.
Консолидация по расположению
Для консолидации по расположению диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов.
Откройте каждый из исходных листов и убедитесь в том, что данные на них расположены одинаково.
На конечном листе щелкните верхнюю левую ячейку области, в которой требуется разместить консолидированные данные.
Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.
На вкладке Данные в разделе Сервис нажмите кнопку Консолидация.
Выберите в раскрывающемся списке функцию, которую требуется использовать для консолидации данных.
Выделите на каждом листе нужные данные и нажмите кнопку Добавить.
Путь к файлу вводится в поле Все ссылки.
После добавления данных из всех исходных листов и книг нажмите кнопку ОК.
Консолидация по категории
Для консолидации по категории диапазон данных на каждом из исходных листов должен иметь формат списка без пустых строк и столбцов. Кроме того, категории должны быть названы одинаково. Например, если один из столбцов называется Сред. , а другой — Среднее, консолидация не просуммирует эти столбцы.
Откройте каждый из исходных листов.
На конечном листе щелкните верхнюю левую ячейку области, в которой требуется разместить консолидированные данные.
Примечание: Убедитесь, что справа и снизу достаточно свободных ячеек для консолидированных данных.
На вкладке Данные в разделе Сервис нажмите кнопку Консолидация.
Выберите в раскрывающемся списке функцию, которую требуется использовать для консолидации данных.
Установите флажки в группе Использовать в качестве имен, указывающие, где в исходных диапазонах находятся названия: подписи верхней строки, значения левого столбца либо оба флажка одновременно.
Выделите на каждом листе нужные данные. Убедитесь, что вы выбрали верхнюю строку или левый столбец, а затем нажмите кнопку Добавить.
Путь к файлу вводится в поле Все ссылки.
После добавления данных из всех исходных листов и книг нажмите кнопку ОК.
Примечание: Любые названия, не совпадающие с названиями в других исходных областях, могут привести к появлению в консолидированных данных отдельных строк или столбцов.
Типичная задача - имеем несколько однотипных таблиц на разных листах рабочей книги. Хотим, чтобы при внесении данных в любую из этих таблиц - данные добавлялись в одну общую таблицу, расположенную на отдельном листе.
Инструкция
Устанавливаем себе надстройку ЁXCEL . Читаем справку.
Приступаем к решению. Переходим во вкладку ЁXCEL главного меню, нажимаем кнопку "Таблицы", в выпавшем списке выбираем команду "Объединить таблицы":
В открывшемся диалоговом окне выделяем листы с таблицами, которые необходимо объединить и нажимаем "ОК":
Переходим на итоговый лист (тот где хотим вывести общую таблицу), устанавливаем курсор в ячейку "A1". Переходим в главном меню во вкладку "Данные" в разделе "Получение внешних данных" нажимаем кнопку "Существующие подключения":
В открывшемся диалоговом окне выбираем "Подключения в этой книге" - "Запрос из Excel Files" и нажимаем "Открыть":
В открывшемся диалоговом окне устанавливаем переключатели в положения "Таблица" и "Имеющийся лист", нажимаем "ОК":
В активном листе будет создана таблица, которая будет объединять таблицы, расположенные на указанных нами листах:
Теперь все таблицы связаны. Добавьте новые строчки в любую из объединенных таблиц. Перейдите во вкладку "Данные" и нажмите кнопку "Обновить все":
В итоговой таблице появятся строчки, добавленные в выбранный вами лист.
Чтобы обновление итоговой таблицы происходило автоматически вставьте в модуль каждого листа (кроме Итогового) следующий код (Как вставлять макросы?):
Видео-пример
Важно:
- Количество столбцов во всех таблицах должно быть одинаково;
- Кроме таблиц на листах не должно быть никакой информации;
- Если вы переместили файл в другую папку или отправили файл коллеге по электронной почте - необходимо заново связать таблицы (в запросе прописывается абсолютный путь к файлу).
Чтобы запрос работал не зависимо от того в какой папке лежит файл вставьте в модуль "ЭтаКнига" следующий код:
Данный макрос при открытии книги будет определять текущий путь к файлу и менять путь к файлу в запросе.
Файл | Описание | Размер файла: | Скачивания |
---|---|---|---|
Пример | 21 Кб | 2576 |
Чтобы оценить всю прелесть - выньте файл из архива и при загрузке файла включите макросы.
Возможные ошибки при использовании этого метода:
Добавить комментарий
Комментарии
Сергей, добрый день!
Подскажите, пожалуйста, как можно свести около 20 таблиц в одну, если при этом в шапках у всех этих таблиц не всегда совпадает количество столбцов? Спасибо.
Здравствуйте!
У меня 54 листа с данными, при объединении выходит ошибка "Слишком сложный запрос". По частям на одном листе не получается "Внутренняя ошибка программировани я объектов".
Добрый день! Да 54 листа это много. Я сейчас пишу команду которая будет делать это статично без ограничений. Пока предлагаю такое решение. Объедините 10 листов, выведите итог, и так несколько раз. потом объедините итоги.
Типичная проблема - имеем два списка. Первый список - содержит объем продаж по менеджерам с фамилиями и суммами, другой содержит фамилии менеджеров и отделы к которым они относятся. Как в первый список быстро добавить название отдела, к которому относится менеджер, чтобы посмотреть продажи по отделу?
Файл | Описание | Размер файла: | Скачивания |
---|---|---|---|
Пример | 6 Кб | 2377 |
Графически эта задача выглядит, примерно, так:
Шаг 1
Приступаем к решению. Встаем в ячейку "D2" и вводим "=ВПР(":
Шаг 2
Начинаем вводить аргументы функции: "Искомое_значение" (Что будем искать?) вводим ячейку, в которой содержится значение (фамилия), которое нам необходимо найти во второй таблице - " B2 ":
Шаг 3
Ставим ";". Следующий аргумент: "Таблица" (Где будем искать?) вводим диапазон второй таблицы, в которой мы будем искать значения (фамилии) из первой - " G6:H10 ":
Шаг 4
Ставим ";". Следующий аргумент: "Номер_столбца" (Из какого столбца подставлять данные?) вводим номер столбца, который содержит интересующие нас данные (отдел) так как этот столбец по счету слева - второй, вводим цифру " 2 ":
Шаг 5
Ставим ";". Следующий аргумент: "Интервальный_просмотр" вводим слово " ЛОЖЬ ":
Шаг 6
Закрываем скобку и нажимаем "Enter". Получаем результат - в ячейке "D2" мы получили название отдела "ОПТ":
Шаг 7
Не торопитесь хлопать в ладоши и копировать формулу на нижние ячейки, не все так просто. Предварительно нужно заблокировать в формуле диапазон второй таблицы, чтобы он "не съехал" при копировании. Для этого встаем курсором мышки в поле формул и выделяем диапазон таблицы:
Шаг 8
Нажимаем на клавиатуре кнопку "F4", перед буквами и цифрами диапазона должны будут появиться знаки "$", если это произошло нажимаем клавишу "Enter":
Шаг 9
Вот теперь можно копировать формулу на нижние ячейки и получать полноценный результат:
Проблемы с отображением видео:
Объединить таблицы
Данная команда виртуально объединяет однотипные таблицы, расположенные на разных листа (количество столбцов в таблицах должно быть одинаково, на листах, содержащих таблицы не должно быть больше никаких данных).
Чтобы воспользоваться данной командой сохраните ваш файл, перейдите во вкладку "ЁXCEL" Главного меню, нажмите кнопку "Таблицы" и выберите команду "Объединить таблицы":
В открывшемся диалоговом окне выберите листы на которых расположены необходимые таблицы или выберите другой файл и укажите с каких листов необходимо взять таблицы для объединения и нажмите "ОК":
Для отмены операции нажмите кнопку отмены:
Чтобы получить доступ к созданной таблице перейдите во вкладку "Данные" Главного меню, в разделе "Получение внешних данных" нажмите кнопку "Существующие подключения":
В открывшемся диалоговом окне во вкладке "Подключения" в разделе "Подключения в этой книге" выберите созданную таблицу "Запрос из Excel Files" и нажмите кнопку "Открыть":
В открывшемся диалоговом окне выберите способ представления данных и нажмите "ОК":
Созданная таблица, в указанном вами виде (таблица, сводная таблица или сводная диаграмма) будет вставлена в указанное вами место (в активный лист или в новый лист).
Читайте также: