Concatenate excel русский

Функция СЦЕПИТЬ (CONCATENATE)

В этой заметке рассмотрим, как склеить содержимое из нескольких ячеек, чтобы все оказалось в одной ячейке. Это распространенная задача и она довольно легко решается с помощью функции Excel СЦЕПИТЬ. Эта функция умеет не только соединять ячейки, но и добавлять любые другие символы. В строке формул выглядит примерно так.

Здесь между ячейками вставлены пробелы (» «) и в конце дорисовывается восклицательный знак.

Однако эти самые пробелы являются первым неприятным моментом при использовании функции СЦЕПИТЬ, т.к. функция склеивает ячейки без «шва» и пробелы приходится добавлять вручную. Если нужно соединить только несколько слов, то беда не большая, но когда нужно склеить хотя бы с десяток ячеек, то ручная добавка пробелов слегка утомляет. Чтобы немного облегчить процесс, можно посоветовать вставить пробел в отдельную ячейку и затем просто делать на нее ссылку.

Так дело идет немного быстрее. Хотя и не очень эффективно – потребуется занять отдельную ячейку. А если нечаянно эту ячейку удалить, то получится вообще нехорошо. В целом при небольшом количестве данных, работают оба варианта.

Кстати, не все знают, что в Excel для склеивания ячеек также предусмотрен специальный оператор – & (амперсанд). Его нужно просто поставить между связываемыми ячейками или символами.

Вставлять амперсанд не удобно – нужно вначале переключиться на английскую раскладку, а затем нажать Shift + 7. Поэтому рекомендуется воспользоваться специальной комбинацией – удерживая клавишу Alt нажать 3 и 8 на цифровой клавиатуре. Здорово помогает сохранить душевное равновесие.

Формула СЦЕПИТЬ и амперсанд исправно работают до тех пор, пока не приходится соединять большое количество ячеек. Замучаешься вставлять «;» либо «&». Более того, между ячейками, как привило, добавляется пробел. К сожалению, указать в качестве аргумента целый диапазон нельзя (уже можно). Выход из этой ситуации следующий.

Функция СЦЕПИТЬ для большого количества ячеек

Один из вариантов заключатся в том, чтобы в качестве ссылки на ячейки указать массив данных. Следует сразу отметить, что данные могут располагаться по горизонтали или вертикали. Если данные расположены в одной строке, то делаем следующее. Для примера, отдельные по ячейкам слова находится в 5-й строке. Теперь в пустой ячейке указываем весь диапазон для соединения и через амперсанд (&) добавляем пробел (» «).

Затем нажимаем F9, для того, чтобы формула выдала результат вычисления, в нашем случае это будет массив.

Как видно к каждому слову добавился пробел, а между словами стоит точка с запятой – как раз то, что нужно для вставки в формулу СЦЕПИТЬ. Теперь убираем лишние скобки и вставляем этот массив в формулу. Нажимаем Enter.

Данные могут быть расположены не по горизонтали, (как в примере выше), а по вертикали. В этом случае в получаемом массиве значения будут разделены двоеточием. Придется сделать один лишний шаг, чтобы заменить их на точку с запятой (через Найти и заменить, горячая клавиша Ctrl + H, либо еще быстрее — использовать функцию ТРАНСП, чтобы из горизонтального массива сделать вертикальный).

Этот вариант работает неплохо, но подходит для одноразового использования, т.к. исходная информация не связана с исходными ячейками. Для изменения придется лезть внутрь формулы или повторять действия для новых данных. Кроме того, существует ограничение по длине одной формулы, и соединить таким образом несколько тысяч ячеек не всегда получается.

Поэтому переходим к следующему способу. Он, признаться, выглядит не очень эстетично. Зато дешево, надежно, и практично! (с) Во всяком случае прием помогает решить проблему. А это самое главное. Нам потребуется один дополнительный столбец (или строка, смотря как расположены исходные данные для соединения).

Рассмотрим только вертикальное расположение данных (горизонтальное будет работать аналогично). Суть в том, чтобы рядом с данными создать столбец с формулой СЦЕПИТЬ, которая последовательно будет присоединять по одному слову по мере продвижения вниз. В нашем случае первая формула будет содержать только слово «Александр » (и пробел), следующая ниже добавляет «Сергеевич » (и пробел) и т.д.

Искомой ячейкой будет самая последняя, объединяющая уже все слова. Результат можно оставить в виде формулы, а можно сохранить как значение, удалив все расчеты.

Соединение текста и даты

И еще один трюк с формулой СЦЕПИТЬ. Довольно часто приходится соединять текст с датой. Дата, как известно, это простое порядковое число (отсчет начинается с 1 января 1900 года), которому придается нужный формат даты. Если же соединить напрямую текст и дату, то на выходе получится указанный текст и простое число. Возьмем, например, слово «Сегодня » (не забываем добавить пробел в конце) и функцию СЕГОДНЯ (всегда возвращающую текущую дату).

Как видно, ничего не получилось.

Для правильной склейки нужно «обернуть» дату в формулу ТЕКСТ, которая придает указанный формат числу.

Формат ДД.ММ.ГГ указывает, в каком виде должна отражаться дата. Если, например, указать формат ДД.ММ.ГГГГ, то дата будет выглядеть так: 03.09.2015 (год состоит из 4-х цифр).

Кстати, добавлять текст к числу или дате можно не только с помощью функции СЦЕПИТЬ. Можно использовать пользовательский числовой формат. У меня когда-то в рабочем файле красовалась автоматически обновляемая надпись.

Поэтому я всегда был прекрасно осведомлен о текущей дате и дне недели. При этом содержимое ячейки состояло только из одной функции СЕГОДНЯ.

Реализовать подобную идею довольно просто. Но это уже чистое форматирование. Расскажу при случае подробней.

P.S. Все проблемы выше решены в новых функциях Excel СЦЕП и ОБЪЕДИНИТЬ .

3 способа склеить текст из нескольких ячеек

Надпись на заборе: «Катя + Миша + Семён + Юра + Дмитрий Васильевич +
товарищ Никитин + рыжий сантехник + Витенька + телемастер Жора +
сволочь Редулов + не вспомнить имени, длинноволосый такой +
ещё 19 мужиков + муж = любовь!»

Способ 1. Функции СЦЕПИТЬ, СЦЕП и ОБЪЕДИНИТЬ

В категории Текстовые есть функция СЦЕПИТЬ (CONCATENATE) , которая соединяет содержимое нескольких ячеек (до 255) в одно целое, позволяя комбинировать их с произвольным текстом. Например, вот так:

Нюанс: не забудьте о пробелах между словами — их надо прописывать как отдельные аргументы и заключать в скобки, ибо текст.

Очевидно, что если нужно собрать много фрагментов, то использовать эту функцию уже не очень удобно, т.к. придется прописывать ссылки на каждую ячейку-фрагмент по отдельности. Поэтому, начиная с 2016 версии Excel, на замену функции СЦЕПИТЬ пришла ее более совершенная версия с похожим названием и тем же синтаксисом — функция СЦЕП (CONCAT) . Ее принципиальное отличие в том, что теперь в качестве аргументов можно задавать не одиночные ячейки, а целые диапазоны — текст из всех ячеек всех диапазонов будет объединен в одно целое:

Для массового объединения также удобно использовать новую функцию ОБЪЕДИНИТЬ (TEXTJOIN) , появившуюся начиная с Excel 2016. У нее следующий синтаксис:

=ОБЪЕДИНИТЬ( Разделитель ; Пропускать_ли_пустые_ячейки ; Диапазон1 ; Диапазон2 . )

• Разделитель — символ, который будет вставлен между фрагментами
• Второй аргумент отвечает за то, нужно ли игнорировать пустые ячейки (ИСТИНА или ЛОЖЬ)
• Диапазон 1, 2, 3 . — диапазоны ячеек, содержимое которых хотим склеить

Например:

Способ 2. Символ для склеивания текста (&)

Это универсальный и компактный способ сцепки, работающий абсолютно во всех версиях Excel.

Для суммирования содержимого нескольких ячеек используют знак плюс «+«, а для склеивания содержимого ячеек используют знак «&» (расположен на большинстве клавиатур на цифре «7»). При его использовании необходимо помнить, что:

• Этот символ надо ставить в каждой точке соединения, т.е. на всех «стыках» текстовых строк также, как вы ставите несколько плюсов при сложении нескольких чисел (2+8+6+4+8)
• Если нужно приклеить произвольный текст (даже если это всего лишь точка или пробел, не говоря уж о целом слове), то этот текст надо заключать в кавычки. В предыдущем примере с функцией СЦЕПИТЬ о кавычках заботится сам Excel — в этом же случае их надо ставить вручную.

Вот, например, как можно собрать ФИО в одну ячейку из трех с добавлением пробелов:

Если сочетать это с функцией извлечения из текста первых букв — ЛЕВСИМВ (LEFT) , то можно получить фамилию с инициалами одной формулой:

Способ 3. Макрос для объединения ячеек без потери текста.

Имеем текст в нескольких ячейках и желание — объединить эти ячейки в одну, слив туда же их текст. Проблема в одном — кнопка Объединить и поместить в центре (Merge and Center) в Excel объединять-то ячейки умеет, а вот с текстом сложность — в живых остается только текст из верхней левой ячейки.

Чтобы объединение ячеек происходило с объединением текста (как в таблицах Word) придется использовать макрос. Для этого откройте редактор Visual Basic на вкладке Разработчик — Visual Basic (Developer — Visual Basic) или сочетанием клавиш Alt + F11 , вставим в нашу книгу новый программный модуль (меню Insert — Module) и скопируем туда текст такого простого макроса:

Теперь, если выделить несколько ячеек и запустить этот макрос с помощью сочетания клавиш Alt + F8 или кнопкой Макросы на вкладке Разработчик (Developer — Macros) , то Excel объединит выделенные ячейки в одну, слив туда же и текст через пробелы.

Функция СЦЕПИТЬ – скотч для Excel

Им пользовался Макгайвер. Экипаж Аполлон-13 также использовал его. Всегда в трудной ситуации, когда нужно сцепить две вещи, люди берут в руки скотч. Возможно, Вы сильно удивитесь, но в Excel есть встроенная функция, которая делает то же самое. Это функция CONCATENATE (СЦЕПИТЬ).

Функция CONCATENATE (СЦЕПИТЬ) позволяет связать два или более куска текста в одной ячейке. Несмотря на длинное имя, она очень проста в использовании и одинаково работает во всех версиях Excel, а также в других электронных таблицах, например, в Google Sheets.

Примечание: Если Вы никогда прежде не использовали функции Excel, то можете обратиться к разделу Формулы и функции нашего самоучителя по Excel для начинающих, где представлена серия уроков на эту тему.

Связываем имена

Предположим, у нас есть таблица с контактной информацией, где имена и фамилии находятся в разных столбцах. Мы хотим связать их и получить полное имя для каждого человека. На рисунке ниже Вы видите имена в столбце B, а фамилии в столбце A. Наша формула будет в ячейке E2.

Прежде, чем мы начнем вводить формулу, поймите важный момент: функция СЦЕПИТЬ будет связывать только то, что Вы укажете и ничего более. Если необходимо, чтобы в ячейке появились знаки пунктуации, пробелы или что-то еще, добавьте их в аргументы функции.

В данном примере мы хотим, чтобы между именами стоял пробел (чтобы не получалось что-то вроде – JosephineCarter), поэтому в аргументы нам нужно будет добавить пробел. Таким образом, у нас окажется три аргумента:

• B2 (First Name) – имя
• ” “ – символ пробела в кавычках
• A2 (Last Name) – фамилия

Теперь, когда аргументы определены, мы можем записать в ячейку E2 вот такую формулу:

=CONCATENATE(B2,» «,A2)
=СЦЕПИТЬ(B2;» «;A2)

Как и с любой другой функцией Excel, важен синтаксис. Не забудьте начать со знака равенства (=) и поставить между аргументами разделители (запятая или точка с запятой).

Замечание: ставить между аргументами запятую или точку с запятой – зависит от того, в какой стране Вы живёте и какой версией Excel пользуетесь.

Вот и всё! Когда нажмете Enter, появится полное имя: Josephine Carter.

Теперь, потянув мышью за маркер автозаполнения, скопируйте формулу во все ячейки до E11. В итоге полное имя появится для каждого человека.

Если хотите усложнить задачу, то попробуйте при помощи функции СЦЕПИТЬ связать город и штат в столбце F, чтобы получилось как на рисунке ниже:

Связываем числа и текст

При помощи функции СЦЕПИТЬ Вы можете связывать даже числа и текст. Давайте представим, что мы используем Excel для хранения записей об инвентаризации магазина. Сейчас у нас есть 25 яблок (apples), но число “25” и слово “apples” хранятся в разных ячейках. Попробуем связать их в одной ячейке, чтобы получилось что-то вроде этого:

Нам понадобится связать три элемента:

• F17 (Number in stock) – количество
• ” “ – символ пробела в кавычках
• F16 (Product) – наименование

Введите вот такую формулу в ячейке E19:

=CONCATENATE(F17,» «,F16)
=СЦЕПИТЬ(F17;» «;F16)

Усложним задачу! Предположим, что мы хотим получить: We have 25 apples (У нас есть 25 яблок). Для этого нужно добавить еще один аргумент – фразу “We have”:

=CONCATENATE(«We have «,F17,» «,F16)
=СЦЕПИТЬ(«We have «;F17;» «;F16)

При желании можно добавлять еще больше аргументов, чтобы создать более сложное выражение. Главное помните, что синтаксис формулы должен быть предельно точен, иначе она может не заработать. В большой формуле легко допустить ошибку!

Concatenate excel русский

Соединяет две текстовые строки в одну текстовую строку.

Текстовые строки, которые необходимо объединить в одну текстовую строку. Строки могут включать текст или числа.

Также можно использовать ссылки на столбец.

Функция CONCATENATE соединяет две текстовые строки в одну. Соединяемые элементы могут быть текстом, числами, логическими значениями, представленными в виде текста, или сочетанием таких элементов. Также можно использовать ссылку на столбец, если столбец содержит подходящие значения.

Функция CONCATENATE в DAX принимает только два аргумента, в то время как функция CONCATENATE в Excel — до 255 аргументов. Чтобы объединить несколько столбцов, можно создать последовательность вычислений, но предпочтительнее использовать оператор объединения ( & ) для их соединения с помощью более простого выражения.

Чтобы непосредственно использовать текстовые строки, а не ссылки на столбец, необходимо заключить каждую строку в двойные кавычки.

Эта функция DAX может возвращать другие результаты при использовании в развернутой модели в режиме DirectQuery. Дополнительные сведения о семантических отличиях в режиме DirectQuery см. по адресу http://go.microsoft.com/fwlink/?LinkId=219171.

Этот образец формулы создает новое строковое значение путем объединения двух строковых значений, переданных в качестве аргументов.

Этот образец формулы возвращает полное имя клиента из телефонной книги. Обратите внимание, как вложенная функция используется в качестве второго аргумента. Это один из способов объединить несколько строк, если нужно использовать в качестве аргументов больше двух значений.

=CONCATENATE(Customer[LastName], CONCATENATE(«, «, Customer[FirstName]))

Этот образец формулы создает в таблице Customer новый вычисляемый столбец, содержащий полное имя клиента, состоящее из имени, отчества и фамилии. Если отчества нет, фамилия следует сразу после имени. Если отчество есть, используется только первая буква отчества, за которой следует точка.

=CONCATENATE( [FirstName]&» «, CONCATENATE( IF( LEN([MiddleName])>1, LEFT([MiddleName],1)&» «, «»), [LastName]))

Комментарии

В этой формуле используются вложенные функции CONCATENATE и IF, наряду с оператором-амперсандом (&), для условного объединения трех строковых значений и добавления пробелов в качестве разделителей.

В следующем примере показано, как объединить значения в столбцах, имеющих различные типы данных. Если объединяется числовое значение, оно будет неявно преобразовано в текст. Если оба значения числовые, они будут преобразованы в текст и объединение будет выполняться так же, как для строковых значений.

Сокращенное наименование товара (столбец 1 составного ключа)

BI — это просто

Простой авторский взгляд на сквозную BI аналитику (разбираем на практике Power BI, Excel, Power Pivot, DAX. и многое другое)

Как провести конкатенацию строк (объединить текст) в Power BI или Power Pivot? DAX функции CONCATENATE и CONCATENATEX

Содержание статьи: (кликните, чтобы перейти к соответствующей части статьи):

Приветствую Вас, дорогие друзья, с Вами Будуев Антон. В данной статье мы поговорим о том, как в Power BI или PowerPivot провести конкатенацию строк (объединить текст). А конкретно, разберем DAX функции CONCATENATE и CONCATENATEX, позволяющие конкатенировать (объединять) текст.

Для Вашего удобства, рекомендую скачать «Справочник DAX функций для Power BI и Power Pivot» в PDF формате.

Если же в Ваших формулах имеются какие-то ошибки, проблемы, а результаты работы формул постоянно не те, что Вы ожидаете и Вам необходима помощь, то записывайтесь в бесплатный экспресс-курс «Быстрый старт в языке функций и формул DAX для Power BI и Power Pivot».

А также, подписывайтесь на наши социальные сети. Потому что именно в них, Вам будут доступны оперативно и каждый день наши актуальные фишки, секреты, наработки, примеры, кейсы, полезные советы, видео и статьи по темам сквозной BI аналитики (Power BI, DAX, Power Pivot, Excel…): Вконтакте, Инстаграм, Фейсбук, YouTube.

DAX функция CONCATENATE в Power BI и Power Pivot

CONCATENATE () — производит объединение двух текстовых строк в одну единую.

Где, «Текст» — это строка, содержащая текст или число. Также, в качестве параметров могут быть ссылки на столбцы с текстовым типом данных.

В качестве примера формулы рассмотрим DAX функцию CONCATENATE вложенную саму в себя:

Результатом выполнения этой формулы будет следующая единая текстовая строка:

Аналогом функции CONCATENATE является оператор объединения текста в языке DAX «амперсанд и» — &:

DAX функция CONCATENATEX в Power BI и Power Pivot

CONCATENATEX () — объединяет результат выражения, вычисленного для каждой строки таблицы.

• ‘Таблица’ — таблица, содержащая строки, для которых будет вычислено выражение
• Выражение — выражение, вычисляемое для каждой строки таблицы
• «Разделитель» — разделитель объединяемых частей текста (необязательный элемент)

Рассмотрим пример формулы на основе DAX функции CONCATENATEX.

В Power BI Desktop имеется исходная таблица «Города», содержащая столбец, в каждой строчке которого, прописан только один город:

Задача — создать единую текстовую строку, где будут объединены города из всех строк исходной таблицы, с разделителем — «, » (запятая и пробел).

Для решения этой задачи хорошо подойдет функция CONCATENATEX, в первый параметр которой, мы пропишем исходную таблицу «Города». Во втором параметре, в качестве выражения, просто укажем исходный столбец [Город], тогда, когда это выражение будет выполнятся для каждой строки исходной таблицы, результатом будет возвращение значения из ячейки этого столбца.

В третьем параметре функции CONCATENATEX мы пропишем разделитель «, » (запятая и пробел).

Итого, у нас получится следующая формула:

И результатом выполнения этой формулы в Power BI будет единая строка с перечислением всех городов через запятую:

На этом, с разбором функций конкатенации CONCATENATE и CONCATENATEX, позволяющих в Power BI и Power Pivot объединить разные текстовые строки в одну, все.

Пожалуйста, оцените статью:

1. 5
2. 4
3. 3
4. 2
5. 1

(4 голоса, в среднем: 5 из 5 баллов)

Успехов Вам, друзья!
С уважением, Будуев Антон.
Проект «BI — это просто»

Если у Вас появились какие-то вопросы по материалу данной статьи, задавайте их в комментариях ниже. Я Вам обязательно отвечу. Да и вообще, просто оставляйте там Вашу обратную связь, я буду очень рад.

Также, делитесь данной статьей со своими знакомыми в социальных сетях, возможно, этот материал кому-то будет очень полезен.

Понравился материал статьи?
Добавьте эту статью в закладки Вашего браузера, чтобы вернуться к ней еще раз. Для этого, прямо сейчас нажмите на клавиатуре комбинацию клавиш Ctrl+D