Doconomist — учет в электронных таблицах. Сцепить функция эксель

Функция «Сцепить» в Excel | RUUD

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

Способ 1: простое объединение

Логично будет начать статью с информации о том, как в Excel сцепить ячейки обычным способом, рассказать о самой функции «Сцепить» и привести пример ее использования. Предварительно рассмотрим синтаксис: =СЦЕПИТЬ (текст1;текст2;…).

Из него становится понятным, что функция довольна простая. Из аргументов один лишь текст, который нужно объединить в единой ячейке. Ну а теперь давайте рассмотрим все на примере. Допустим, у вас есть таблица с именами людей. При этом имя, отчество и фамилия вписаны в отдельные колонки. Наша задача — объединить их в одной:

Выделите пустую ячейку. В нашем случае в колонке «ФИО».

Нажмите по кнопке «Вставить функцию». Находится она слева от строки формул.

В появившемся окне выберите категорию «Текстовые».

В самом списке выделите функцию «Сцепить».

Нажмите ОК.

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

В поле «Текст1» укажите ячейку с фамилией человека. Сделать это можно как вручную, прописав «B4», так и автоматически, нажав по кнопке справа от поля и указав ячейку мышкой.

В «Текст2» укажите ячейку с именем.

В «Текст3» – с отчеством.

Нажмите ОК.

Это первый пример использования функции «Сцепить» в Excel.Теперь вы можете наблюдать, что в колонке «ФИО» появился результат – фамилия, имя и отчество записаны последовательно.

Способ 2: формула «Сцепить» с пробелом в Excel

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

Кликните дважды левой кнопкой мыши по ячейке с формулой.

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

Нажмите Enter для применения изменений.

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

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

Может случиться и так, что предыдущий способ расстановки пробелов в функции «Сцепить» в Excel не подойдет вам. Тогда воспользуйтесь другим. Он подразумевает включение пробелов непосредственно в аргументы в «Мастере функций». Ну а теперь давайте перейдем к подробной инструкции:

Далеко от таблицы выделите ячейку, которую никогда не будете использовать.

Введите в нее знак пробела.

Создайте функцию «Сцепить» так, как было это описано в первом способе, только остановитесь на этапе ввода аргументов.

Первым аргументом введите фамилию.

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

Далее выберите имя, снова пробел и в последней – отчество.

Нажмите ОК.

Как можно заметить, второй способ постановки пробелов между аргументами формулы «Сцепить» в Excel также отлично справляется со своими задачами. Некоторым пользователям он придется по душе, так как все действия выполняются непосредственно в графическом интерфейсе.

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

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

Откройте окно функций первой ячейки в колонке «ФИО».

Перейдите на этап заполнения аргументов.

Во всех ссылках на ячейку поставьте знак «$» перед буквой и перед числом.

Нажмите ОК.

После этого можно приступать к копированию функции «Сцепить» на все оставшиеся в колонке ячейки. Для этого:

Выделите первую ячейку с введенной функцией.

Наведите курсор на ее нижний правый угол.

После появления крестика зажмите левую кнопку мыши.

Удерживая ее, протяните курсор до конца колонки, выделив все сопутствующие ячейки.

Отпустите кнопку мыши.

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

Источник

ruud.ru

Функция «Сцепить» в Excel

Способ 1: простое объединение

Выделите пустую ячейку. В нашем случае в колонке «ФИО».
Нажмите по кнопке «Вставить функцию». Находится она слева от строки формул.
В появившемся окне выберите категорию «Текстовые».
В самом списке выделите функцию «Сцепить».
Нажмите ОК.

В поле «Текст1» укажите ячейку с фамилией человека. Сделать это можно как вручную, прописав «B4», так и автоматически, нажав по кнопке справа от поля и указав ячейку мышкой.
В «Текст2» укажите ячейку с именем.
В «Текст3» – с отчеством.

Нажмите ОК.

Способ 2: формула «Сцепить» с пробелом в Excel

Кликните дважды левой кнопкой мыши по ячейке с формулой.
Впишите между аргументами пробелы. Однако делается это нестандартным способом. Пробел должен выступать также в роли аргумента. Это означает, что его необходимо взять в кавычки, а после них поставить точку с запятой.
Нажмите Enter для применения изменений.

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

Далеко от таблицы выделите ячейку, которую никогда не будете использовать.
Введите в нее знак пробела.
Создайте функцию «Сцепить» так, как было это описано в первом способе, только остановитесь на этапе ввода аргументов.
Первым аргументом введите фамилию.
Вторым выберите ячейку, которую недавно создали, то есть пробел.
Далее выберите имя, снова пробел и в последней – отчество.
Нажмите ОК.

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

Откройте окно функций первой ячейки в колонке «ФИО».
Перейдите на этап заполнения аргументов.
Во всех ссылках на ячейку поставьте знак «$» перед буквой и перед числом.
Нажмите ОК.

После этого можно приступать к копированию функции «Сцепить» на все оставшиеся в колонке ячейки. Для этого:

Выделите первую ячейку с введенной функцией.
Наведите курсор на ее нижний правый угол.
После появления крестика зажмите левую кнопку мыши.
Удерживая ее, протяните курсор до конца колонки, выделив все сопутствующие ячейки.
Отпустите кнопку мыши.

fb.ru

Как использовать функцию СЦЕПИТЬ в эксель

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

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

Для этого опять помучаем вашу тётю, введем в ячейку B3 текст "Здравствуйте", в C3 "я", в D3 "ваша" и в E3 "тетя!", установим курсор в ячейку B7 и нажмем на иконку Fx в строке формул.

Откроется мастер формул, в категории Текстовые выберем функцию СЦЕПИТЬ, в поле Текст1 напишем B3, в поле Текст2 поставим пробел, Текст3 C3, Текст4 пробел, Текст5 D3, Текст6 снова пробел, Текст7 E3 и нажмем ОК.

В ячейке B7 мы можем увидеть, что фраза соединилась.

Кстати, иногда заменой функции СЦЕПИТЬ, можно использовать амперсанд "&", формула будет выглядеть так: =B3 & " " & C3 & " " & D3 & " " & E3.

Введем ее в ячейку B9, и увидим, что результат одинаковый, как и в ячейке B7.

В этом видео подробно показано применение функции СЦЕПИТЬ в excel:

Рекомендуем смотреть видео в полноэкранном режиме, в настойках качества выбирайте 1080 HD, не забывайте подписываться на канал в YouTube, там Вы найдете много интересного видео, которое выходит достаточно часто. Приятного просмотра!

С уважением, авторы сайта Компьютерапия

Понравилась статья? Поделитесь ею с друзьями и напишите отзыв в комментариях!

maxfad.ru

Функция СЦЕПИТЬ в Excel: синтаксис, использование, примеры

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

Функция "Сцепить"

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

Функция "Сцепить" в Excel является одной из тех, что предназначены для работы со строковыми значениями ячеек. Она нужна пользователю, если необходимо объединить несколько текстовых полей в одно. Очень часто она используется для соединения фамилии, имени и отчества, которые хранятся в разных столбцах, в одно целое поле ФИО. Но применений этой опции много и кроме этого стандартного случая.

Если вы используете не русскоязычную версию программы, то на английском функция "Сцепить" в Excel называется "Concatenate". В остальном все, описанное ниже, соответствует и англоязычной версии – синтаксис, применение и примеры.

Применение

Операция имеет следующий синтаксис: СЦЕПИТЬ([текст2];[текст2];…). Количество строковых атрибутов может быть каким угодно большим, главное, чтобы полученный текст соответствовал требованию к длине результирующего поля. В качестве этих атрибутов могут быть как заранее определенные значения, так и данные на листе Excel. При этом типы данных элементы для объединения не обязательно должны быть текстовыми.

Для внесения атрибутов воспользуйтесь формой вставки функции. Для этого в нужной ячейке впишите знак равенства и нажмите кнопку со значком fx. В открывшемся окне выбираете нужный элемент и нажимаете «Ок».

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

Параметры функции можно вносить и прямо в окно содержимого ячейки. В приведенном нами примере строка операции будет выглядеть так:

=СЦЕПИТЬ(D2;".";E2)

В результате получим в выбранной ячейке текст 21.09, состоящий из данных ячейки D2 – числа 21, точки и данных ячейки Е2 – строки «09». Информация в поле Е2 представляет собой число, но отформатирована как текст, так как важно, чтобы на первом месте стояла цифра 0. Если такое число привести к числовому формату, этот ноль исчезнет, и останется только цифра 9.

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

Примеры

Приведем еще несколько примеров функции "Сцепить" в Excel. Прежде всего, закончим начатое выше и превратим информацию в ячейке G2 в полноформатную дату рождения клиента. Для этого нам потребуется соединить в одной строке день рождения, месяц и год, содержащиеся, соответственно, в ячейках D2, E2 и F2. Между этими значениями необходимо поставить точки.

В окне аргументов добавится еще 2 поля, в результате оно будет выглядеть таким образом:

Строка функции будет иметь вид:

=СЦЕПИТЬ(D2;".";E2;".";F2).

Как видите, специально введенные значения – в нашем примере это точки – выделяются кавычками в строке.

В результате выполнения выражения, в ячейке G2 получаем дату 21.09.1990. Остальные поля столбца заполняем аналогичным выражением с помощью опции копирования информации, то есть, потянув за крестик в нижнем правом углу G2 до конца таблицы.

По аналогии, в этой же таблице соберем с помощью функции "Сцепить" в Excel данные об именах клиентов и получим их полные ФИО в одном столбце из разделенных частей. Используем выражение =СЦЕПИТЬ(A2;" ";B2;" ";C2). Обратите внимание, что в качестве разделителей отдельных частей имени стоят пробелы. О них нужно всегда помнить при таких операциях.

Результирующая таблица показана на снимке:

Обратная функция

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

Первый – использование опции «Данные по столбцам» панели «Работа с данными» вкладки «Данные». При вызове этой возможности появляется мастер разделения, в котором вы задаете параметры:

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

Второй способ – использование команды VBA Split. Ее синтаксис:

Split (Выражение,[Разделитель],[Количество строк],[Сравнение]).

Атрибут «Выражение» является обязательным – это строка для разделения. «Разделитель» показывает знак, по которому исходный текст делится на части, по умолчанию это пробел. «Количество строк» определяет максимальное число результирующих значений. «Сравнение» - метод, определяющий чувствительность к реестру символов.

Другие возможности объединения строк

Аналогом функции СЦЕПИТЬ в Excel является знак амперсанда - &. В нашем примере он применяется таким образом:

=A2&" "&B2&" "&C2.

В результате получим те же значения, что и раньше:

В Excel версии 2016 появилась новая функция СЦЕП(). Ее отличие в том, что у нее всего один атрибут – диапазон ячеек. Можно соединить значения полей, выделив диапазон, а не вводя отдельно каждую ячейку.

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

fb.ru

СцепитьЕсли | Excel для всех

MulTEx » 17 Февраль 2014 Дмитрий 12109 просмотров

Данная функция является частью надстройки MulTEx

Объединение текстовых данных в одной ячейке на основе критерия

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

По принципу работы функция похожа на стандартную СУММЕСЛИ. Указываете диапазон значений (фамилии), критерий (конкретную фамилию) и диапазон значений для сцепления (оценки). Символ для разделения слов может быть как любым символом так и группой символов. Одновременно можно применить сравнение более чем по 50 критериям.

Вызов команды через стандартный диалог:

Мастер функций-Категория "MulTEx"- СцепитьЕсли

Вызов с панели MulTEx:

Сумма/Поиск/Функции - Текстовые - СцепитьЕсли

Синтаксис:=СцепитьЕсли(Лист1!$B$9:$B$38;""&D$1;Лист1!$A$9:$A$38;"-";1;Лист1!$B$9:$B$38;"*продажа*")

Диапазон(Лист1!$B$9:$B$38) - диапазон с критериями(указывается один столбец).

Критерий(""&D$1) - ссылка на ячейку или непосредственно значение, с которым сравниваем значения в Диапазоне. Может содержать символы подстановки - * и ?. Просматривается Диапазон. Помимо просто текстовых критериев, можно указывать символы сравнения ("", 0, ""&A1, >1 и т.п.). Удобно когда необходимо получить список из значений, если критерием является число больше 100, например. Можно указывать значение, объединяя со значением из ячейки: ""&D$1. Если предположить, что в ячейке D1 записано значение 23, то функция объединит в одну строку все ячейки диапазона $A$9:$A$38 напротив которых в ячейках диапазона $B$9:$B$38 значение будет не равно 23.

Диапазон_сцепления(Лист1!$A$9:$A$38) - из этого диапазона берется значение для сцепления, если значение в аргументе Диапазон совпадает с аргументом Критерий. Указывается столбец с данными, равный по количеству строк аргументу Диапазон. Если Диапазон_Сцепления по количеству ячеек будет различаться от аргумента Диапазон, то результат может отличаться от ожидаемого.

Разделитель(-) - Указывается один любой символ или группа символов(или даже слово), который будет вставлен между каждым найденным и отобранным значением.

БезПовторов(1) - Если указать 1 или ИСТИНА, то в результате получится строка, в которой нет одинаковых значений. Если указать 0 или ЛОЖЬ, то будут выведены все значения, независимо от того, повторяются они или нет.

ДиапазонКритерий(Лист1!$B$9:$B$38;"*продажа*") - дополнительные диапазоны и критерии для отбора. Важно: сначала указывается диапазон, затем критерий, который в этом диапазоне просматривать. Условия использования те же, что и для критериев Диапазон и Критерий. Допускается до 125 связок Диапазон-Критерий(т.е. всего 250 аргументов - до 125 Дипазонов и до 125 относящихся к ним Критериев). Все условия объединены между собой по принципу совпадения И. Если хоть одно условие связки ДиапазонКритерий не выполняется, условия расположенные далее по списку не проверяются, общее условие по строке считается как несоответствующее и данные этой строки не будут сцеплены.

www.excel-vba.ru

Функция СЦЕПИТЬ – скотч для 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)

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

Оцените качество статьи. Нам важно ваше мнение:

office-guru.ru

Сцепить текст массово

Как объединить текст при помощи пользовательской функции в MS Excel. А так же сделать умную сцепку с условиями. Функции join и filter.

Задача

Пользователи Экселя издавна страдали от недостатка пользовательских функций с условиями. С новыми версиями Экселя начали появляться новые полезные функции: СУММЕСЛИМН, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИМН. Но так и не появилась функция, которая соберет текст из нескольких ячеек в одну, причем сделает это элегантно.

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

Таким образом, приходится как то справляться с задачами, идя в обход. Например, нам нужно получить из этого это ↓:

Итак, вот обрисовалась такая задача: собрать из множества клеток Эксель данные в одну, используя любой заданный разделитель. В примере выше используется разделитель "-". При этом функция проста в использовании. Вот она:

=join(A2:A15;"-")

Если зайти в саму клетку, то увидишь такое:

Решение поставленной задачи

Решение данной задачи -- использовать свою пользовательскую функцию. Открой редактор Visual Basic в Экселе, нажав Alt+F11, вставь новый модуль и в новый модуль вставь следующий код:

Function join(rng As Range, Optional Delim As String = " ") As String Dim c As Range Set rng = Intersect(rng, ActiveSheet.UsedRange) For Each c In rng join = join & c.Value & Delim Next c If Len(join) > 0 Then join = Left(join, Len(join) - Len(Delim)) End If End Function Теперь еще раз по порядку, как это работает:

Ты вносишь код в книгу и код с функцией работает внутри этой книги. Чтобы код работал во всех книгах Эксель, необходимо вставить код в надстройку Экселя, сохранить книгу с кодом как надстройку-книгу с расширением XLAM и добавить себе в надстройки.
Сам код очень прост, но он делает большое дело. После вставки кода, ты можешь запустить функцию, введя ее, как любую другую функцию в Экселе. Ты можешь поменять название функции, для этого в ее тексте тебе необходимо заменить 6 раз слово join на свое слово, например ОБЪЕДИНИТЬ.
Функция принимает 2 аргумента. Первый аргумент -- диапазон. Он может быть довольно большим. Если делать то же через стандартную фукнцию Экселя СЦЕПИТЬ, то при наличии ста исходных клеток можно рехнуться, вводя каждую клетку вручную. Тут же берется диапазон ячеек целиком. второй аргумент -- это разделитель. Его мы заключаем в кавычки. Второй диапазон можно упустить. По умолчанию разделителем будет пробел. Ты можешь поменять значение по умолчанию, заменив в первой строке Optional Delim As String = " ") на Optional Delim As String = "Ы"), где Ы -- будет твоим новым разделителем по умолчанию.

Решение сверхзадачи -- сцепить с условиями

Иногда появляются еще более сложные задачи, когда необходимо сделать какую либо операцию, но при строго заданных условиях. Вот тогда нам на помощь придет еще одна пользовательская функция -- join. Она слишком большая, чтобы ее здесь приводить, поэтому я скину файлик с примером -- качай его себе и пользуйся. Вот скриншот из этого файла:

Функция Фильтра интересна сама по себе. О ней подробнее расскажу в следующий раз.