Формула сцепить в excel примеры

Формула сцепить в excel примеры

Здравствуй дорогой, читатель!

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

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

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

На данный момент существует 3 способа сцепить ячейки в Excel:

Но рассмотрим мы только первые 2 варианта, ну а о третем поговорим уж в другой раз. Итак:

1. Функция СЦЕПИТЬ в Excel

С помощью функции СЦЕПИТЬ, как я выше уже писал, можно объединить до 255 ячеек и до 8 192 символов. Что позволяет производить громадное количество комбинаций для наших нужд. Есть также небольшой нюанс в использовании, не стоить забывать о пробелах, программа, увы, сама их не ставит и что бы не получить слитый текст в одно слово, между значениями нужно указать пробел как текстовое значение (пример: “ “). В таком случае функция СЦЕПИТЬ в Excel сработает на 100% и вы получите нужный вам результат.

2. Сцепить диапазон ячеек в Excel при помощи оператора & (амперсанд) вместо функции СЦЕПИТЬ

Амперсанд — это своеобразный знак “+” для текстовых значений, которые нам нужно соединить. Найти амперсанд можно на клавиатуре, возле циферки “7”, ну по крайней мере, на большинстве клавиатур там он и находится. А если его нет, значит, внимательно посмотрите куда его перенесли. А так данный вариант похож, как и функция СЦЕПИТЬ, за исключением специфики орфографии и об этом не стоит забывать, так, к примеру, функция СЦЕПИТЬ сама ставит кавычки, а вот при использовании амперсанда вы прописываете их вручную. Но вот в возможности склеить значения в ячейках Excel по скорости, использование 2 варианта самое оптимальное.

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

Пример №1:

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

Пример №2:

Нам надо сцепить разрозненную информацию о договорах его номер и дату заключения. К примеру, у нас есть “Договор на транспортные перевозки” “№23” “02.09.2015” и нам надо получить все данные одним предложением “Договор на транспортные перевозки №23 от 02.09.2015 года”. Но при использовании возможности сцепить диапазон ячеек с помощью функции СЦЕПИТЬ, мы не сможем получить нужный нам результат так будет произведена склейка текстовых значений, а нас есть дата. Соответственно, данные будут исковерканы. Для получения результата необходимо использовать дополнительно функцию ТЕКСТ. Она позволит, назначить для даты соответствующий формат «ДД.ММ.ГГГГ», соответственно формату мы получим данные двузначные для дней “ДД” и месяца “ММ” и четырёхзначное для года “ГГГГ”. Таким образом, мы сможем получить правильный конечный вариант.

Читайте также:  Как перекодировать в divx

Файл с примерами можно забрать сдесь.

А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Не забудьте поблагодарить автора!

Экономия — способ тратить деньги без удовольствия.
Арман Салакру

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

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

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

Формула функций выглядит так:

=СЦЕП(текст1;текст2;. ) или =СЦЕПИТЬ(текст1;текст2;. )

Максимальное количество аргументов – 255.

Вставка и настройка функции

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

  1. Для начала определяемся с ячейкой, в которой планируем объединить данные из других. Переходим в нее (выделяем) и щелкаем по значку “Вставить функцию” (fx).
  2. В открывшемся окне вставки функции выбираем категорию “Текстовые” (или “Полный алфавитный перечень”), отмечаем строку “СЦЕП” (или “СЦЕПИТЬ”) и кликаем OK.
  3. На экране появится окно, в котором нужно заполнить аргументы функции, в качестве которых могут быть указаны как конкретные значения, так и ссылки на ячейки. Причем последние можно указать как вручную, так и просто кликнув по нужным ячейкам в самой таблице (при это курсор должен быть установлен в поле для ввода значения напротив соответствующего аргумента). В нашем случае делаем следующее:
    • находясь в поле “Текст1” щелкаем по ячейке (A2), значение которой будет стоять на первом месте в объединенной ячейке;
    • кликаем по полю “Текст2”, где ставим запятую и пробел (“, “), которые будут служит разделителем между содержимыми ячеек, указанных в аргументах “Текст1” и “Текст3” (появится сразу же после того, как мы приступим к заполнению аргумента “Текст2”). Можно на свое усмотрение указывать любые символы: пробел, знаки препинания, текстовые или числовые значения и т.д.
    • переходим в поле “Текст3” и кликаем по следующей ячейке, содержимое которой нужно добавить в общую ячейку (в нашем случае – это B2).
    • аналогичным образом заполняем все оставшиеся аргументы, после чего жмем кнопку OK. При этом увидеть предварительный результат можно в нижней левой части окна аргументов.
    • Все готово, нам удалось объединить содержимое всех выбранных ячеек в одну общую.
    • Выполнять действия выше для остальных ячеек столбца не нужно. Просто наводим указатель мыши на правый нижний угол ячейки с результатом, и, после того как он сменит вид на небольшой черный плюсик, зажав левую кнопку мыши тянем его вниз до нижней строки столбца (или до строки, для которой требуется выполнить аналогичные действия).
    • Таким образом, получаем заполненный столбец с новыми наименованиями, включающими данные по размеру и полу.
    Читайте также:  Табор знакомства зайти на свою страницу

    Аргументы функции без разделителей

    Если разделители между содержимыми ячеек не нужны, в этом случае в значении каждого аргумента сразу указываем адреса требуемых элементов.

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

    Указание разделителя в отдельной ячейке

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

    Например, мы добавляем запятую и пробел (“, “) в ячейку B16.

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

    Но здесь есть один нюанс. Чтобы при копировании формулы функции на другие ячейки не произошло нежелательного сдвига адреса ячейки с разделителем, ссылку на нее нужно сделать абсолютной. Для этого выделив адрес в поле соответствующего аргумента нажимаем кнопку F4. Напротив обозначений столбца и строки появятся символы “$”. После этого можно нажимать кнопку OK.

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

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

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

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

    Редактирование функции

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

    Способ 1: изменения в окне аргументов

    Перейти к редактированию функции можно, просто нажав на кнопку “Вставить функцию” (fx), которая находится слева от строки формул. При этом предварительно нужно выбрать ячейку, содержащую функцию.

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

    Мы получим результат с учетом скорректированных данных.

    Способ 2: ручная корректировка формулы

    Альтернативный метод изменения аргументов функции – ручная корректировка формулы. Перейти в режим редактирования можно двумя по-разному:

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

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

    • адреса ячеек пишутся без кавычек;
    • перечисление аргументов – через знак “;”
    • если в качестве аргумента используется символ или текст, наличие кавычек с двух сторон указанного значения обязательно.

    Заключение

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

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

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

    Функция входит в группу «текстовые». Синтаксис: (текст 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 символов.

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

    Ссылка на основную публикацию
    Файлы mdi чем открыть
    Если вы не смогли открыть файл двойным нажатием на него, то вам следует скачать и установить одну из программ представленных...
    Украли сумку с документами что делать
    В связи с угрозой распространения на территории Российской Федерации коронавирусной инфекции приостановлен личный прием граждан в судах. Смотреть как изолируются...
    Ультра исо вам необходимо иметь права администратора
    Очень многие пользователи, когда им нужно сделать загрузочную флешку Windows или с дистрибутивом другой операционной системы, прибегают к использованию программы...
    Файлы mdx чем открыть
    MDX - это формат образов дисков, который был создан разработчиками программы DAEMON Tools. Это формат был создан в результате усовершенствования...
    Adblock detector