Функция сцепить в excel для чайников

Функция сцепить в excel для чайников

Чтобы объединить значения (текстовые, цифровые) из разных ячеек в одну, используется функция СЦЕПИТЬ. Ее аналог – & (амперсанд). Знак справляется с простейшими задачами. Но для объединения множества строк не подходит.

Синтаксис функции СЦЕПИТЬ

Функция входит в группу «текстовые». Синтаксис: (текст 1; текст 2;…). Можно включить до 255 текстовых строк. Первый аргумент – «текст 1» – является обязательным. Любые аргументы можно указать в виде текста, цифр или ссылок на ячейки.

Общие правила использования функции СЦЕПИТЬ:

  1. Для разделения аргументов применяется точка с запятой. Пропуск или использование других знаков приведет к отображению в ячейке с результатом кавычек.
  2. Текстовые аргументы обязательно заключать в кавычки. В противном случае появляется ошибка – #ИМЯ? Числа и ссылки на ячейки – не нужно.
  3. Чтобы соединяемые элементы в строке результата отобразились с пробелом, пробелы нужно добавлять в формулу с функцией. С помощью двойных кавычек с пробелом внутри (“ “) или с помощью пробела после текстового аргумента (“Иванов ”;…).



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

Начнем с простейших примеров.

Данные для объединения:

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

Результат объединения значений в ячейках:

Такой же результат получим с помощью амперсанда:

Один из аргументов – текст. Формула: =СЦЕПИТЬ("слесарь"; " ";A2;" ";B2;" ";C2). Результат:

И с более сложными задачами справится функция СЦЕПИТЬ в Excel. Примеры:

  1. Соединим текст с датой. Данные находятся в отдельных ячейках. Для столбца «Дата» установлен формат «Дата». Если использовать только функцию СЦЕПИТЬ, дата отобразится в числовом формате. Поэтому добавим функцию ТЕКСТ для форматирования чисел. Формула: Второй аргумент функции ТЕКСТ – формат представления даты. Результат:
  2. Соединим сразу много ячеек в строке. Если писать формулу с функцией СЦЕПИТЬ, получится долго и малоэффективно. Используем маленькую хитрость. Вот наша строка со значениями в отдельных ячейках. В ячейку Н1 ставим знак «равно». Выделяем диапазон со значениями А1:F1. Вводим амперсанд & и пробел “ “. Жмем F9. В строке формул появится формула массива. Ставим курсор в строку формул после знака «равно». Пишем название функции. Фигурные скобки заменяем на круглые и жмем ВВОД. Получаем нужный результат.
  3. Соединим значения через косую черточку («-», «&» и т.п.). Формула будет выглядеть так: = СЦЕПИТЬ (ссылка на ячейку 1; «/»; ссылка на ячейку 2).
  4. Соединим несколько значений в столбце. Функция подходит для значений в строках. Описанный выше способ с формулой массива тоже не сработает.

Преобразуем вертикальный диапазон в горизонтальный с помощью функции ТРАНСП. А затем воспользуемся формулой массива для быстрого соединения значений. В ячейку В1 ставим знак «равно». Пишем ТРАНСП. Указываем диапазон А1:А6. Вводим & и пробел (« »). Жмем F9.

Теперь преобразуем формулу массива, добавив функцию и заменив скобки.

Обратная функция СЦЕПИТЬ в Excel

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

  • ЛЕВСИМВ (отображает заданную часть символов с начала строки);
  • ПРАВСИМВ (отображает заданную часть символов с конца строки);
  • ПСТР (отображает указанную часть символов, начиная с заданной позиции) и другие.
Читайте также:  Диапазон рабочих температур кондиционера

  1. Извлечем первое слово из строки, применив функцию ЛЕВСИМВ. Аргументы – «текст» (ссылка на строку с искомым значением), «количество знаков» (число символов, которые нужно извлечь).
  2. Извлечем последнее слово из строки с помощью функции ПРАВСИМВ. Синтаксис похожий (как в предыдущем примере). Только количество знаков считается с конца.
  3. Извлечем из записи в строке фамилию (второе слово) с помощью функции ПСТР. Аргументы – «текст» (ссылка на строку), «начальная позиция» (номер символа, с которого программа начнет считать число знаков); «количество знаков». В примере: Е2 – «текст», с 9 знака функция вернет 8 символов.

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Не включается материнская плата в компьютере

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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


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

Читайте также:  Почему медицинскую карту не выдают на руки

Сразу хотелось бы отметить, что все примеры будем рассматривать в Microsoft office 2010.

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

Примечание! Сегодняшний материал посвящен встроенным функциям, которые присутствуют в Excel по умолчанию, рассматривать макросы или программки на VBA сегодня мы не будем, однажды на этом сайте мы уже затрагивали тему VBA Excel в статье — Запрет доступа к листу Excel с помощью пароля, если интересно можете посмотреть.

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

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

Функция Excel – ВПР

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

Вы действуете также как и в предыдущем примере, или пишите или выбираете через графический интерфейс, например:

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

Функции Excel – Правсимв и Левсимв

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

Функция Excel – Если

Это обычная функция на проверку выражения или значения. Иногда бывает полезна. Например, нам необходимо в столбец C записывать значение «Больше» или «Меньше» на основании сравнения полей A и B т.е. например, если A больше B то записываем «Больше» если меньше то соответственно записываем «Меньше»:

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

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

Ссылка на основную публикацию
Функции в вольфрам математика
Функции пользователя Хотя в систему входят многие сотни встроенных функций (начиная от элементарных и кончая специальными математическими функциями и системными...
Учимся рисовать в paint
Серия видео уроков «Создание компьютерного рисунка в программе Paint» МОУ «Межборская средняя общеобразовательная школа» (Уроки предназначены для детей 9-12 лет,...
Учиться без троек сканворд
Музыкант, играющий на барабанах, тарелках Передовой работник производства (ударник) Часть затвора стрелкового оружия (ударник) "Барабанщик" коммунистического труда (устар.) (ударник) "Барабанщик"...
Функция abs в паскале
Возвращает абсолютную величину параметра. Объявление Function Abs(X) : (тип параметра); Режим Windows, Real, Protected Замечания Параметр X — выражение вещественного...
Adblock detector