У вас есть столбец с именами, городами или кодами товаров в Excel, и вам нужно объединить их в одну ячейку с определённым разделителем. Может быть, это запятая, вертикальная черта или точка с запятой. Excel предлагает несколько способов сделать это, и как только вы узнаете нужную формулу, всё займёт около десяти секунд.
Старый способ и правильный способ
Годами люди использовали функцию СЦЕПИТЬ (CONCATENATE) или оператор & для объединения ячеек. Это работает, но утомительно. Если у вас 20 ячеек, вам придётся писать очень длинную формулу и вручную добавлять разделитель между каждой ссылкой.
Лучший подход — ОБЪЕДИНИТЬ (TEXTJOIN), функция, которая появилась в Excel 2019 и Microsoft 365. Она решает всё одной компактной формулой.
Как работает TEXTJOIN
Синтаксис прост. Вот что делает каждый аргумент:
- разделитель: Символ или строка, которую вы хотите поставить между каждым значением (запятая, вертикальная черта, пробел и т. д.)
- пропускать_пустые: Установите TRUE, чтобы пропускать пустые ячейки, или FALSE, чтобы учитывать их.
- текст1, текст2, ...: Ячейки или диапазон, которые нужно объединить.
Базовая формула выглядит так:
= TEXTJOIN(", ", TRUE, A2:A10)
Эта формула объединяет все значения из диапазона A2:A10 через запятую с пробелом, пропуская пустые ячейки. Просто и аккуратно.
Примеры часто используемых разделителей
Пользовательский разделитель может быть практически любым. Вот несколько распространённых сценариев для начала.
| Сценарий | Разделитель | Пример формулы |
|---|---|---|
| Подготовка к экспорту в CSV | Запятая | =TEXTJOIN(",", TRUE, A2:A10) |
| Данные с разделителем-вертикальной чертой | Вертикальная черта (|) | =TEXTJOIN("|", TRUE, A2:A10) |
| Полное имя из частей | Пробел | =TEXTJOIN(" ", TRUE, A2, B2) |
| Импорт в базу данных | Точка с запятой | =TEXTJOIN(";", TRUE, A2:A10) |
| Читаемый список | " и " | =TEXTJOIN(" и ", TRUE, A2:A5) |
Что делать, если TEXTJOIN недоступна?
Если у вас старая версия Excel (2016 или более ранняя), функция TEXTJOIN недоступна. Есть несколько вариантов.
- Используйте оператор
&: =A2&","&A3&","&A4 (при большом количестве ячеек формула быстро становится громоздкой) - Используйте вспомогательный столбец, где каждая строка дополняет предыдущую, добавляя разделитель по ходу.
- Обновитесь до Microsoft 365, который включает TEXTJOIN и другие современные функции.
- Экспортируйте данные и выполните объединение вне Excel с помощью онлайн-конвертера разделителей.
Объединение ячеек из нескольких столбцов
TEXTJOIN работает не только с одним столбцом. Вы можете передать несколько диапазонов или отдельных ячеек в качестве аргументов. Например, чтобы объединить имя, фамилию и город через вертикальную черту:
=TEXTJOIN(" | ", TRUE, A2, B2, C2)
Вы также можете вложить другие функции внутрь TEXTJOIN. Именно тут она становится по-настоящему мощной. Обернув функцию ЕСЛИ (IF) внутрь, можно объединять только ячейки, соответствующие условию — и всё без вспомогательного столбца.
Работа с результатом за пределами Excel
После объединения данных вам часто нужно вставить их куда-то ещё — в базу данных, конфигурационный файл или другой инструмент. Выбранный разделитель на этом этапе имеет большое значение.
Если оказалось, что в данных используется неправильный разделитель для следующего шага, не нужно возвращаться и переписывать формулу. Вы можете вставить результат в конвертер запятых в вертикальные черты и заменить разделитель за секунды. Это намного быстрее, чем редактировать формулу в таблице и копировать всё заново.
Ключевые моменты
- TEXTJOIN — это современный и удобный способ объединения ячеек в Excel с произвольным разделителем.
- Синтаксис: =TEXTJOIN(разделитель, пропускать_пустые, диапазон).
- Установите пропускать_пустые в TRUE, чтобы избежать двойных разделителей из-за пустых ячеек.
- Старые версии Excel не поддерживают TEXTJOIN, поэтому используйте оператор & или внешний инструмент.
- Если нужно сменить разделитель после объединения, бесплатный онлайн-инструмент часто быстрее, чем переписывание формулы.
Краткая шпаргалка перед уходом
Вот однострочная подсказка для каждого сценария, с которым вы можете столкнуться. Сохраните в закладки или скопируйте в заметки.
- Объединить диапазон через запятые: =TEXTJOIN(",", TRUE, A2:A50)
- Объединить определённые ячейки через вертикальную черту: =TEXTJOIN("|", TRUE, A2, B2, C2)
- Объединить через пробел (имена): =TEXTJOIN(" ", TRUE, A2, B2)
- Автоматически пропускать пустые ячейки: всегда устанавливайте второй аргумент в TRUE
- Нужен другой разделитель после объединения: используйте конвертер разделителей, чтобы заменить его без возврата в Excel