Concatenate 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 объединит выделенные ячейки в одну, слив туда же и текст через пробелы.

Источник: www.planetaexcel.ru

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Как открыть xml файл в excel

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

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

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

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

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

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

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

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

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



Источник: statanaliz.info

Работа с функцией СЦЕПИТЬ в Microsoft Excel

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

Применение оператора СЦЕПИТЬ

Функция СЦЕПИТЬ относится к группе текстовых операторов Excel. Её основная задача — объединение в одной ячейке содержимого нескольких ячеек, а также отдельных символов. Начиная с версии Excel 2016, вместо данного оператора используется функция СЦЕП. Но в целях сохранения обратной совместимости оператор СЦЕПИТЬ тоже оставлен, и его можно использовать наравне со СЦЕП.

Синтаксис этого оператора выглядит следующим образом:

В качестве аргументов могут выступать, как текст, так и ссылки на ячейки, которые его содержат. Количество аргументов может варьироваться от 1 до 255 включительно.

Способ 1: объединение данных в ячейках

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

  1. Выделяем ячейку, в которой планируем разместить объединенные данные. Кликаем на кнопку «Вставить функцию». Она имеет вид пиктограммы и размещена слева от строки формул.

Открывается Мастер функций. В категории «Текстовые» или «Полный алфавитный перечень» ищем оператор «СЦЕПИТЬ». Выделяем это наименование и жмем на кнопку «OK».

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

Устанавливаем курсор в первое поле окна. Затем выделяем ссылку на листе, в которой содержатся данные, нужные для объединения. После того, как координаты отобразились в окошке, аналогичным образом поступаем со вторым полем. Соответственно выделяем другую ячейку. Подобную операцию проделываем, пока координаты всех ячеек, которые нужно объединить, не будут внесены в окно аргументов функции. После этого жмем на кнопку «OK».

  • Как видим, содержимое выбранных областей отразилось в одной предварительно указанной ячейке. Но у данного способа имеется существенный недостаток. При его использовании происходит так называемая «склейка без шва». То есть, между словами нет пробела и они склеены в единый массив. При этом вручную добавить пробел не получится, а только через редактирование формулы.

    • Способ 2: применение функции с пробелом

    Существуют возможности исправить данный недочет, вставив пробелы между аргументами оператора.

    1. Выполняем задачу по тому же алгоритму, который описан выше.
    2. Двойным щелчком левой кнопки мыши по ячейке с формулой активируем её для редактирования.

    Между каждым аргументом записываем выражение в виде пробела, ограниченного с двух сторон кавычками. После внесения каждого такого значения ставим точку с запятой. Общий вид добавленных выражений должен быть следующий:

  • Для того, чтобы вывести результат на экран, жмем на кнопку Enter.

    • Как видим, на месте вставки пробелов с кавычками в ячейке появились разделения между словами.

    Способ 3: добавление пробела через окно аргументов

    Конечно, если преобразуемых значений не много, то вышеприведенный вариант разрыва склейки прекрасно подойдет. Но его будет затруднительно быстро воплотить, если существуют множество ячеек, которые нужно объединить. Тем более, если эти ячейки не находятся в едином массиве. Значительно упростить расстановку пробела можно, воспользовавшись вариантом его вставки через окно аргументов.

      Выделяем двойным кликом левой кнопки мыши любую пустую ячейку на листе. С помощью клавиатуры устанавливаем внутри неё пробел. Желательно, чтобы она находилась подальше от основного массива. Очень важно, чтобы эта ячейка никогда после этого не заполнялась никакими данными.

    Выполняем те же действия, что и при первом способе применения функции СЦЕПИТЬ, вплоть до открытия окна аргументов оператора. Добавляем значение первой ячейки с данными в поле окна, как это уже было описано ранее. Затем устанавливаем курсор во второе поле, и выделяем ту пустую ячейку с пробелом, о которой шла речь ранее. Появляется ссылка в поле окна аргументов. Для ускорения процесса можно её скопировать, выделив и нажав сочетание клавиш Ctrl+C.

  • Затем добавляем ссылку на следующий элемент, который нужно добавить. В очередном поле опять добавляем ссылку на пустую ячейку. Так как мы скопировали её адрес, то можно установить курсор в поле и нажать сочетание клавиш Ctrl+V. Координаты будут вставлены. Таким способом чередуем поля с адресами элементов и пустой ячейки. После того, как все данные внесены, жмем на кнопку «OK».

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

    Внимание! Как видим, вышеуказанный способ значительно ускоряет процедуру корректного объединения данных в ячейках. Но следует учесть, что данный вариант таит в себе и «подводные камни». Очень важно, чтобы в элементе, который содержит пробел, со временем не появились какие-то данные или он не был сдвинут.

    Способ 4: объединение колонок

    С помощью функции СЦЕПИТЬ можно быстро объединять данные нескольких колонок в одну.

      С ячейками первой строки объединяемых колонок проделываем на выбор те действия, которые указаны во втором и третьем способе применения аргумента. Правда, если вы решили воспользоваться способом с пустой ячейкой, то ссылку на неё нужно будет сделать абсолютной. Для этого, перед каждым знаком координат по горизонтали и вертикали этой ячейки ставим знак доллара ($). Естественно, что лучше всего это сделать в самом начале, чтобы в другие поля, где содержится этот адрес, пользователь мог копировать его, как содержащий постоянные абсолютные ссылки. В остальных полях оставляем относительные ссылки. Как всегда, после выполнения процедуры, жмем на кнопку «OK».

    Устанавливаем курсор в нижний правый угол элемента с формулой. Появляется значок, имеющий вид крестика, который называется маркером заполнения. Зажимаем левую кнопку мыши и тянем его вниз параллельно расположению объединяемых элементов.

  • После выполнения этой процедуры данные в указанных столбцах будут объединены в одном столбце.

    • Способ 5: добавление дополнительных символов

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

    Читайте также:  Выпадающий список с условием в excel

      Выполняем действия по добавлению значений в окно аргументов функции любым из способов, который был приведен выше. В одно из полей (при необходимости их может быть и несколько) добавляем любой текстовый материал, который пользователь считает нужным добавить. Данный текст обязательно должен быть заключен в кавычки. Жмем на кнопку «OK».

  • Как видим, после этого действия к объединенным данным был добавлен и текстовый материал.

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

    Отблагодарите автора, поделитесь статьей в социальных сетях.

    Источник: lumpics.ru

    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

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

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

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

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

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

    Источник: biprosto.ru

    CONCATENATE CONCATENATE

    Объединяет две текстовые строки в одну. Joins two text strings into one text string.

    Синтаксис Syntax

    Параметры Parameters

    Термин Term Определение Definition
    text1, text2 text1, text2 Текстовые строки, сцепляемые в одну текстовую строку. The text strings to be joined into a single text string. Строки могут содержать текст или числа. Strings can include text or numbers.

    Также можно использовать ссылки на столбцы. You can also use column references.

    Возвращаемое значение Return value

    Объединенная строка. The concatenated string.

    Примечания Remarks

    Функция CONCATENATE сцепляет две текстовые строки в одну. The CONCATENATE function joins two text strings into one text string. Соединяемые элементы могут быть текстовыми, численными или логическими значениями, представленными в виде текста, или сочетанием этих элементов. The joined items can be text, numbers or Boolean values represented as text, or a combination of those items. Можно также использовать ссылку на столбец, если столбец содержит соответствующие значения. You can also use a column reference if the column contains appropriate values.

    Функция CONCATENATE в DAX принимает только два аргумента, тогда как функция CONCATENATE в Excel принимает до 255 аргументов. The CONCATENATE function in DAX accepts only two arguments, whereas the Excel CONCATENATE function accepts up to 255 arguments. Если необходимо выполнить сцепку над несколькими выражениями, можно создать ряд вычислений или лучше использовать оператор сцепки ( & ), чтобы объединить их в более простое выражение. If you need to concatenate multiple columns, you can create a series of calculations or, better, use the concatenation operator (&) to join all of them in a simpler expression.

    Если вы хотите использовать текстовые строки напрямую, а не указывать ссылку на столбец, каждую строку необходимо заключить в двойные кавычки. If you want to use text strings directly, rather than using a column reference, you must enclose each string in double quotation marks.

    Эта функция DAX может возвращать различные результаты при использовании в модели, которая развертывается, а затем запрашивается в режиме DirectQuery. This DAX function may return different results when used in a model that is deployed and then queried in DirectQuery mode. Дополнительные сведения о семантических различиях в режиме DirectQuery см. в разделе https://go.microsoft.com/fwlink/?LinkId=219171. For more information about semantic differences in DirectQuery mode, see https://go.microsoft.com/fwlink/?LinkId=219171.

    Пример: Сцепка литералов Example: Concatenation of Literals

    Описание Description

    В примере формулы новое строковое значение создается путем сцепки двух строковых значений, указанных в качестве аргументов. The sample formula creates a new string value by combining two string values that you provide as arguments.

    Код Code

    Пример: Сцепка строк в столбцах Example: Concatenation of Strings in Columns

    Описание Description

    Пример формулы возвращает полное имя клиента, указанное в телефонной книге. The sample formula returns the customer’s full name as listed in a phone book. Обратите внимание, что в качестве второго аргумента используется вложенная функция. Note how a nested function is used as the second argument. Это один из способов сцепки нескольких строк, если есть более двух значений, которые необходимо использовать в качестве аргументов. This is one way to concatenate multiple strings, when you have more than two values that you want to use as arguments.

    Код Code

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

    Пример: Условная сцепка строк в столбцах Example: Conditional Concatenation of Strings in Columns

    Описание Description

    Пример формулы создает в таблице Customer новый вычисляемый столбец с полным именем клиента в виде сочетания имени, среднего инициала и фамилии. The sample formula creates a new calculated column in the Customer table with the full customer name as a combination of first name, middle initial, and last name. Если среднее имя отсутствует, фамилия стоит сразу после имени. If there is no middle name, the last name comes directly after the first name. Если среднее имя есть, то используется только его инициал. If there is a middle name, only the first letter of the middle name is used and the initial letter is followed by a period.

    Код Code

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

    Комментарии Comments

    В этой формуле используются вложенные функции CONCATENATE и IF вместе с оператором-амперсандом (&) для условного сцепления трех строковых значений и добавления пробелов в качестве разделителей. This formula uses nested CONCATENATE and IF functions, together with the ampersand (&) operator, to conditionally concatenate three string values and add spaces as separators.

    Пример: Сцепка столбцов с различными типами данных Example: Concatenation of Columns with Different Data Types

    В следующем примере показано, как сцеплять значения в столбцах, имеющих различные типы данных. The following example demonstrates how to concatenate values in columns that have different data types. Если сцепляемое значение является числовым, оно будет неявно преобразовано в текст. If the value that you are concatenating is numeric, the value will be implicitly converted to text. Если оба значения являются числовыми, то оба значения будут приведены к тексту и сцеплены, как если бы они были строками. If both values are numeric, both values will be cast to text and concatenated as if they were strings.

    Описание продукта Product description Сокращенное название продукта (первый столбец составного ключа) Product abbreviation (column 1 of composite key) Артикул продукта (второй столбец составного ключа) Product number (column 2 of composite key) Новый созданный ключевой столбец New generated key column
    Горные велосипеды Mountain bike MTN MTN 40 40 MTN40 MTN40
    Горные велосипеды Mountain bike MTN MTN 42 42 MTN42 MTN42

    Код Code

    Комментарии Comments

    Функция CONCATENATE в DAX принимает только два аргумента, тогда как функция CONCATENATE в Excel принимает до 255 аргументов. The CONCATENATE function in DAX accepts only two arguments, whereas the Excel CONCATENATE function accepts up to 255 arguments. Если необходимо добавить дополнительные аргументы, можно использовать оператор-амперсанд (&). If you need to add more arguments, you can use the ampersand (&) operator. Например, следующая формула выдает результаты, MTN-40 и MTN-42. For example, the following formula produces the results, MTN-40 and MTN-42.

    Источник: docs.microsoft.com

    Финансы в Excel

    Объединение строк

    Подробности Создано 15 Апрель 2012

    Содержание
    Решение
    Как это работает
    Выводы
    Вложения:

    concatenate.xls [Объединение строк] 27 kB

    У продвинутых пользователей Excel очень популярен вопрос о возможности объединения диапазона ячеек, содержащих текст, в одну строку при помощи стандартной функции. К сожалению, функция CONCATENATE этого делать не умеет, она работает только с одиночными ячейками или константами. Т.е. абсолютна идентична по функциональности оператору “&”. Заметим, что при помощи пользовательской функции на VBA задача решается достаточно просто. Также несложно добавить служебный диапазон с формулами нарастающего итога. Но на вопрос объединения строк при помощи одной формулы обычно смело дается отрицательный ответ. Попробуем опровергнуть это утверждение.

    Решение

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

    Открывайте файл только в новом окне приложения Excel – это позволит автоматически установить заданные параметры итераций.

    Как это работает

    Формула в ячейке A1, заставляет обновлять значение самой этой ячейки в итерационном режиме. Результат проверяется по суммарной длине строки исходного диапазона. До достижения конца диапазона в начало результирующей ячейки добавляется трехзначное число, хранящее относительную ссылку на обрабатываемую на текущем шаге ячейку. Добавляется также служебный разделитель – символ “$”. Анализ наличия этого разделителя также позволяет понять, закончен ли итерационный расчет на конкретном шаге или нет.

    Символы “<" и ">” указывают на формулу с обработкой массива – то есть ввод через Ctrl+Shift+Enter. Если нужно обрабатывать другой исходный диапазон, то надо заменить ссылку “A3:A8” в двух местах формулы. Обрабатываются только связанные вертикальные диапазоны ячеек. A1 в формуле означает ссылку на саму себя (это стандартный способ работы в итерационных расчетах).

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

    В результате работы такой формулы на каждом шаге расчетов последовательно меняется значение с “0” на “1”, затем наоборот. Эта ячейка используется в качестве “спускового механизмома” для автоматического повторения максимально возможного количества итераций (по умолчанию в параметрах установлено значение 100). Можно сказать, что A2 – это совсем не нужная для расчетов ячейка, так как никакой связи с результатом в ней нет (у нее вообще ссылка только на саму себя). Она просто обновляет собственное значение на каждом шаге итерации, заставляя автоматически возобновлять расчет, тем самым пересчитывая нужную нам текстовую ячейку A1. В A2, в принципе, может стоять любая формула, обновляющая сама себя, например: =А2+1.

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

    Выводы

    Итак, что мы имеем. VBA не использовался. Служебных диапазонов нет (кроме, единственной ячейки). Диапазоны (векторы) до 100 ячеек объединяются успешно. Если нужно больше, меняйте параметры итерации. Можно попробовать сделать универсальную формулу для горизонтальных векторов и матриц исходных ячеек, но такой задачи не стояло – и так формула не очень простая.

    Пересчет происходит при изменении длины текста любой ячейки или изменении размера диапазона. В частном случае, когда заменен символ без изменения длины строки, результат не обновится. Это “лечится”, например, добавлением пробела в конец последней ячейки.

    Для чистоты эксперимента, конечно, жаль, что не удалось избавиться от служебной ячейки (A2). Т.е. все-таки одной формулой не обошлись. Хоть ячейка и никак не связана с формированием текстового результата, но выполнить условие задачи на 100% не получилось. В оправдание можно сказать, что алгоритм, заложенный в расчетную формулу, в принципе, верный и теоретически достаточный для достижения правильного результата. Но, как оказалось на практике, Excel несовсем корректно работает с итерациями при обработке текстовых значений. По всей видимости, такая функциональность просто не была предусмотрена разработчиками.

    Если интересно понять логику работы итерационных вычислений, то можно, во-первых, удалить формулу в служебной ячейке, во-вторых, переключиться на ручной режим вычислений и запускать каждый шаг нажатием кнопки F9.

    Эта техника вряд ли пригодится в экономических задачах. Но для выигрыша спора с каким-нибудь “экспертом Excel” вполне подойдет)

    Источник: www.excelfin.ru