Как объединить ячейки с другого листа или с нескольких разных листов в Excel?
В повседневной работе с Excel объединение или слияние содержимого ячеек часто повышает наглядность данных и помогает формировать сводную информацию из различных листов. Хотя функция CONCATENATE предназначена для объединения ячеек в пределах одного листа, на практике нередко возникает необходимость консолидировать значения ячеек сразу с нескольких — а то и множества — разных листов. Это особенно актуально при сборе данных из разных отделов, составлении ежемесячных отчётов или работе со стандартизированными шаблонами, распределёнными по разным вкладкам. Без эффективного подхода такой процесс превращается в рутину и чреват ошибками. В этой статье мы представляем несколько практических решений для быстрого объединения ячеек с разных листов в Excel — они помогут оптимизировать ваши рабочие процессы и обеспечить согласованность данных.
Объединение ячеек с разных листов с помощью формулы
Объединение ячеек с разных листов с помощью Kutools для Excel
Объединение ячеек с разных листов с помощью функции CONCATENATE
В Excel обычно можно использовать функцию CONCATENATE, последовательно указывая ячейки с разных листов для их объединения. Этот подход прост и удобен, если листов немного или требуется объединить лишь несколько конкретных ячеек. Однако по мере роста числа листов поддержка и редактирование такой формулы могут стать затруднительными.
Выберите пустую ячейку, в которую будет помещён результат объединения, и введите формулу =CONCATENATE(Tea!A1,« »,Coffee!A1,« »,'Soft Drink'!A1,« »,Milk!A1,« »,Water!A1) (где A1 — ячейка с данными для объединения, а Tea, Coffee, Soft Drink, Milk и Water — названия листов, содержащих нужные ячейки), затем нажмите клавишу Enter.
Теперь содержимое указанных ячеек с разных листов объединится в одной ячейке. См. скриншот:
Советы и меры предосторожности:
- При ссылке на имена листов, содержащие пробелы или специальные символы, обязательно заключайте их в одинарные кавычки — например:
'Soft Drink'!A1. - Вы можете изменить разделитель с пробела (« ») на запятую, точку с запятой или другие символы — в зависимости от ваших потребностей.
- Если нужно сослаться на другие ячейки (B1, C1 и т.д.), обновите каждую ссылку в формуле соответствующим образом.
Ограничение: Этот подход требует ручного редактирования формулы для каждого листа и каждой ячейки, поэтому он лучше всего подходит для небольших объединений или разовых задач. Для более сложных сценариев рассмотрите альтернативные методы ниже.
Формула Excel – использование TEXTJOIN для объединения значений ячеек с разных листов с разделителем
В Excel 2019 и более поздних версиях доступна функция TEXTJOIN, которая эффективно объединяет диапазон или массив значений, позволяя задать собственный разделитель между ними. Этот метод особенно удобен пользователям новых версий Excel, которым нужна простая формула, автоматически игнорирующая пустые ячейки и тем самым избавляющая от лишних разделителей.
1. Щёлкните по пустой ячейке, в которую нужно поместить объединённый результат (например, D1), и введите следующую формулу:
=TEXTJOIN(" ",TRUE,Tea!A1,Coffee!A1,'Soft Drink'!A1,Milk!A1,Water!A1)Эта формула объединяет значения ячейки A1 с пяти листов ()Tea, Coffee, Soft Drink, Milk, Water), разделяя их пробелом (« »). Второй аргумент TRUE указывает Excel автоматически игнорировать все пустые ячейки.
2. Нажмите клавишу Enter, чтобы применить формулу. В выбранной ячейке появится объединённый результат. Если нужно объединить больше или меньше листов, скорректируйте ссылки в формуле в соответствии с вашими требованиями.
- Измените разделитель внутри первых кавычек — например, используйте
", "для запятой с пробелом,"-"для дефисов и т.д. - Если вы хотите сослаться на разные адреса ячеек с каждого листа, обновите их в формуле соответствующим образом.
- Если у вас много листов или адреса ячеек меняются, воспользуйтесь приведённым ниже решением.
Преимущество: Очень компактная формула, легко настраиваемые разделители и надёжное игнорирование пустых ячеек — не требует дополнительных надстроек или активации макросов в совместимых версиях Excel.
Ограничения: Недоступна в версиях Excel до 2019 года. Адреса ячеек должны быть указаны явно, поэтому этот метод лучше всего подходит для умеренного (не чрезвычайно большого) количества листов.
Объединение ячеек с разных листов с помощью Kutools для Excel
Если нужно объединить ячейки с множества листов, приведённая выше формула может оказаться крайне утомительной. В таких случаях мы рекомендуем воспользоваться утилитой Kutools для Excel — Автоматическое инкрементирование ссылок на листе, которая значительно упрощает объединение данных в Excel. Этот инструмент автоматически создаёт ссылки на указанные ячейки сразу на нескольких листах, существенно сокращая время и усилия, а также минимизируя ошибки — особенно когда вы работаете с десятками листов или составляете ежемесячные сводные отчёты.
1. Создайте новый лист в Excel и на этом новом листе выберите ячейку с тем же адресом, что и у ячеек, которые вы хотите объединить с других листов (например, ячейку A1). Затем нажмите Kutools > More > Автоматическое инкрементирование ссылок на листе. См. скриншот:
2. В открывшемся диалоговом окне «Заполнить ссылки на листе» выполните следующие действия:
(1) Выберите Заполнить по строке, затем по столбцу из раскрывающегося списка Порядок заполнения.
(2) Укажите листы, ячейки которых вы хотите объединить, в разделе Список листов.
(3) Нажмите кнопку Fill Range и закройте это диалоговое окно после завершения операции. См. скриншот:
Kutools для Excel— раскройте весь потенциал Excel с помощью более чем 300 незаменимых инструментов, которые ускоряют и упрощают вашу работу, а также воспользуйтесь возможностями ИИ для более разумной обработки данных и повышения продуктивности.Получить сейчас
Теперь всё содержимое ячейки A1 с указанных листов извлечено и перечислено на новом листе.
3. Выберите пустую ячейку, куда должен быть помещён результат объединения (например, F1), и введите формулу =CONCATENATE(A1:E1&,« ») (A1:E1 — диапазон с содержимым ячеек, извлечённым с других листов). Выделите A1:E1&« » в строке формул и нажмите F9 — это преобразует ссылки в их фактические значения. Затем удалите фигурные скобки {} из формулы и, наконец, нажмите Enter, чтобы завершить операцию. См. скриншот:
Теперь все указанные ячейки с разных листов мгновенно объединены в одной ячейке.
- Это решение идеально подходит для ситуаций, когда вам часто нужно извлекать и объединять одну и ту же ячейку с нескольких листов или работать с большим количеством листов.
- Утилита Автоматическое инкрементирование ссылок на листе особенно полезна для поддержания и аудита книги ссылок: новые листы можно выбрать всего за несколько кликов, и ссылки автоматически обновятся.
Демонстрация: объединение ячеек с другого листа/разных листов в Excel
См. также:
Объединение с сохранением формата чисел/дат в Excel
Объединение диапазона с добавлением/включением кавычек в Excel
Объединение диапазона/ячеек с запятой, пробелом, переносом строки в Excel
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек