Одна из самых распространенных задач — соединить текст или числа из разных ячеек, либо столбцов в таблице гугл или эксель в один логичный текст. Будь то объединение адреса и города из разных ячеек или составить ФИО из таблицы с данными. Давайте рассмотрим варианты, которые помогут нам успешно реализовать эту задачу.
Функция CONCATENATE (СЦЕПИТЬ)
В текстовом репозитории Google таблиц есть функция CONCATENATE (СЦЕПИТЬ), которая соединяет содержимое нескольких ячеек в одну структуру, позволяя комбинировать данные в свободной форме.
Синтаксис:
=CONCATENATE(text1; [text2]; …)
=СЦЕПИТЬ(текст1; [текст2]; …)
- Text1 (Текст1) — значение, которое нужно объединять с другим значением. Значение может быть: текстом, числом, ссылкой на ячейку, функцией.
- [Text2 (Текст2)] — значение, которое нужно объединить с Text1 (Текст1) переменной. Всего в одной функции можно использовать до 254 значения, суммарное число символов не может превышать 8192 символа.
Рассмотрим простой пример работы функции CONCATENATE (СЦЕПИТЬ) и соединим данные из строк и столбцов в одну структуру:
Вы обратили внимание, что данные свелись в одну структуру, но логики в ней нет. Не хватает пробелов между значениями ячеек. И это, пожалуй, самый большой минус данной функции. Чтобы, все же, появились пробелы — их нужно добавить в формулу, либо организовать дополнительную ячейку с пробелом, на которую мы будем ссылаться каждый раз, когда будем «склеивать» содержимое ячеек.
Оба эти варианта рабочие и имеют место быть, когда данных не так много. Чтобы добавить пробел в формулу, его записывают так: « » (пробел в кавычках); либо в ячейке проставим пробел, из которой он каждый раз будет изыматься формулой.
Не все знают, но чтобы «сцепить» содержимое ячеек можно воспользоваться символом — «&» (Амперсанд). Это та же сцепка, какая синтаксически записывается в формуле с пробелами в кавычках, только уже без самого названия:
=(text1&" "&[text2]&" "&[text3])
=(A2&$E$35&B2&$E$35&C2)
Рассмотрим реальный пример серьезного уместного применения функции CONCATENATE (СЦЕПИТЬ) через Амперсанды. В данном случае, применяется сцепка со специальными символами «| |» (требования Яндекс Директ), для правильного формирования строчного списка Заголовков фида для рекламы в Яндекс Директ. Так же, формула содержит функцию IF (ЕСЛИ), с которой мы познакомились в прошлой статье. Формула имеет следующий вид:
=IF(I3="";"";'Настройки №2'!$B$2&"||"&'Настройки №2'!$B$3&"||"&'Настройки №2'!$B$4&"||"&'Настройки №2'!$B$5&"||"&'Настройки №2'!$B$6&"||"&'Настройки №2'!$B$7&"||"&'Настройки №2'!$B$8&"||"&'Настройки №2'!$B$9)
Многомерная сложная формула сцепляет содержимое в ячейках из другого листа, отображая список заголовков через заданные разделители. Потом мы этот готовый шаблон Яндекс Директ Google (Excel) импортируем в Директ Коммандер.
Функция CONCAT (СЦЕП) в google таблицах
В свежих обновлениях добавилась новая функция CONCAT (СЦЕП). Она так же объединяет ячейки с данными в одну структуру, только в отличие от CONCATENATE (СЦЕПИТЬ) — состоит только из 2 обязательных аргументов, но может вмещать в себя в 4 раза больше символов. Чтобы в полной мере освоить данную функцию, ее применяют вместе с амперсандом.
=CONCAT(value_1; value_2)
=СЦЕП(значение_1; значение_2)
Value_1 (Значение_1) — значение, которое нужно объединять с другим значением. Значение может быть: текстом, числом, ссылкой на ячейку, функцией.
Value_2 (Значение_2) — значение, которое нужно объединить с Value_1 (Значение_1) переменной. Всего в одной функции можно использовать до 253 аргументов, суммарное число символов не может превышать 32767 символа.
Функция TEXTJOIN (ОБЪЕДИНИТЬ) в google таблицах
Для массового объединения значений в ячейках с 2016 года (Excel), с 2017 года (Google таблицы) появилась универсальная функция TEXTJOIN (ОБЪЕДИНИТЬ). Функция сводит в одну ячейку текст, число, ссылки на другие ячейки или информацию со всего массива / нескольких массивов данных. Суммарное число символов в одной ячейке не может превышать 32767 символов. Синтаксис:
=TEXTJOIN(separator; exclude_blanks; text1; [text2]; …)
=ОБЪЕДИНИТЬ(разделитель; исключить_пустые; текст1; [текст2]; …)
- Separator (Разделитель) — указанный символ, который будет разделять объединенные значения (точка, запятая, косая черта, и т.д.).
- Exclude_blanks (Исключить_пустые) — аргумент отвечает за то, нужно ли игнорировать пустые ячейки TRUE (ИСТИНА) или FALSE (ЛОЖЬ).
- Text1 (Текст1) — текст, ссылка на ячейку, число или диапазон, к которому будет применен процесс объединения — обязательный аргумент.
- [Text2 (Текст2)] — текст, ссылка на ячейку, число или диапазон, который будет объединяться с Text1 (Текст1).
Рассмотрим сложный вариант работы данной формулы при решении реальной задачи объединения текста через запятую из двух столбцов и отображении результата в одной ячейке.
У нас есть минус-фразы для рекламной кампании Google Ads и Яндекс Директ. Они записаны в 2 столбика: 1 — тематические минус-фразы; 2 — универсальные. Нам их нужно объединить в одну ячейку, чтобы каждая фраза шла через запятую. В этом нам поможет функция TEXTJOIN (ОБЪЕДИНИТЬ). Помимо основной задачи, у нас есть еще ссылка на условие через известную уже нам функцию IF (ЕСЛИ).
=IF(I3="";"";TEXTJOIN(", ";TRUE;'Ключевики'!$E$2:E;'Ключевики'!$F$2:F))
Функция объединяет в одну ячейку столбец E и столбец F, игнорируя заглавия колонок, проставляет через запятую минус-фразы и отображает в выбранной ячейке (в другой, итоговой вкладке) этот результат, опираясь на заданное условие IF (ЕСЛИ).
Поделитесь в комментариях, получилось ли у вас воспользоваться функциями объединения и с какими проблемами вы столкнулись, когда решали поставленные задачи?
Спасибо! Сэкономили несколько часов жизни)
Рад помочь!
Добрый день! как объединить данные большой таблицы с помощью формулы (чтобы в итоге получился список)?
Отлично, то что искал, нужно было составить запрос в Jira имея столбец с эскалированными номерами заявок!
Функция CONCATENATE может, так же, применяться для работы с многомерными массивами в том случае, если запрос срабатывает только при одном частном массиве:
Дефрагментируем массивы через сцепку, чтобы была уникальная база SKU (или номера заявок), а потом фрагментируем обратно через INDEX(SPLIT…
Недавно решал задачу, где нужно было извлекать один и тот же товар с одинаковыми SKU, но в разных маркетплейсах, при этом, нужно было помещать эти данные в один столбец.
Вот CONCATENATE и пригодился 😉