Как в экселе сцепить много ячеек

Функция =СЦЕПИТЬДИАПАЗОН(ДИАПАЗОН, [РАЗДЕЛИТЕЛЬ]) имеет два аргумента:

— ДИАПАЗОН — спектр ячеек, которые нужно сцепить.
— [РАЗДЕЛИТЕЛЬ] — символ-разделитель, который будет вставляться меж значениями ячеек. Данный аргумент является не неотклонимым.

Подобная функция возникла лишь в Excel 2016. Если у Вас Excel ранешней версии, то надстройка на сто процентов исправляет это упущение разрабов.

Дальше привожу огромное количество примеров использования данной функции.

Пример 1

Просто сцепить все ячейки спектра, вставив меж ними пробел.

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

Пример 2

Перечислить все фамилии через запятую.

Направьте внимание, что в качестве разделителя указана запятая с пробелом («, «). Если не указать пробел, то все ячейки «соединятся».

Пример 3

Можно сцепить наиболее 1-го столбца. При всем этом сцепление ячеек происходит построчно.

Пример 4

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

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

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

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

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

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

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

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

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

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

Итог объединения значений в ячейках:

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

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

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

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

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

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

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

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

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

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

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

Интересно почитать: Excel посчитать количество ячеек со значением

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

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

Ниже приведены примеры.

1-ый элемент для объединения. Это быть может текстовое значение, число либо ссылка на ячейку.

Доп текстовые элементы для объединения. Можно указать до 255 частей и до 8 192 знаков.

Примеры

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

Делает предложение, объединяя данные в столбце А с остальным текстом. Итог: «Популяция рек для вида речная форель составляет 32 на километр».

Соединяет воединыжды строчку в ячейке В2, пробел и значение в ячейке С2. Итог: «Виталий Токарев».

Соединяет воединыжды текст в ячейке C2, строчку, состоящую из запятой и пробела, и значение в ячейке B2. Итог: «Виталий Токарев».

Соединяет воединыжды строчку в ячейке B3, строчку, состоящую из пробела, амперсанда и еще 1-го пробела, и значение в ячейке C3. Итог: Fourth & Pine.

Соединяет воединыжды те же элементы, что и в прошлом примере, но при помощи оператора & (амперсанд) заместо функции СЦЕПИТЬ. Итог: Fourth & Pine.

Всераспространенные проблемы

Кавычки показываются в строке результата.

Используйте точку с запятой для разделения текстовых частей. К примеру: Excel покажет итог функции =СЦЕПИТЬ («Привет, «„мир!“) как „Привет,“мир!» с излишней кавычкой, потому что пропущена точка с запятой меж текстовыми аргументами.

Числа не надо заключать в кавычки.

Слова размещены вплотную друг к другу.

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

Добавьте двойные кавычки с пробелом меж ними (» «). К примеру: =СЦЕПИТЬ(«Привет,»; » «; «мир!»).

Добавьте пробел опосля текстового аргумента. К примеру: =СЦЕПИТЬ(«Привет, «; «мир!»). К строке «Привет,» добавлен пробел.

Ошибка #ИМЯ? отображается заместо ожидаемого результата.

Ошибка #ИМЯ? обычно значит, что для текстового аргумента не хватает кавычек.

Советы

Заместо функции СЦЕПИТЬ используйте символ & (амперсанд).

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

К примеру, функция = a1 & B1 возвращает то же значение, что = СЦЕПИТЬ (A1; B1). В почти всех вариантах пользоваться оператором амперсанда проще и резвее, чем создавать строчки при помощи функции СЦЕПИТЬ.

Для объединения и форматирования строк используйте функцию ТЕКСТ.

Функция ТЕКСТ конвертирует числовое значение в текст и соединяет воединыжды числа с текстом либо знаками.

Представим, что ячейка A1 содержит число 23,5. Чтоб отформатировать число как сумму в рублях, можно применять последующую формулу:

Доп сведения

Для объединения и форматирования строк используйте функцию ТЕКСТ.

Ознакомьтесь с доп сведениями о использовании операторов вычислений.

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

Как в Экселе протянуть формулу по строке либо столбцу: 5 методов

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

1 Обычное протягивание формулы

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

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

Аргументы в формуле будут изменяться соответственно новенькому расположению. И если в самой первой ячейке это были F7 и G7, в крайней позиции столбца это будет уже F12 и G12. Соответственно, если начинать распространять формулы по строчкам, изменяться будут не числа, а буковкы в обозначениях ячеек.

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

2 Резвое автозаполнение

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

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

Интересно почитать: Как в excel суммировать ячейки по условию

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

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

3 Протягивание без конфигурации ячеек в формуле

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

Для распределения без конфигурации адреса ячейки производятся те же деяния, что и при обыкновенном протягивании либо автоматическом копировании. Но при вводе формулы следует зафиксировать адреса, которые не будут изменяться. Для этого употребляются знаки бакса — $. Если в любом новеньком пт столбца при расчетах употребляется одна и та же ячейка, значки нужно будет поставить и перед номером строчки, и перед литерой, которая показывает на колонку. Как в примере: $G$6.

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

4 Обычное копирование

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

Записать в одну из последних ячеек строчки либо столбца подходящую формулу и надавить Enter.
Скопировать значение функции — при помощи контекстного меню, иконки на панели либо композиции кнопок Ctrl + C.
Установить курсор в обратную часть столбца либо строчки.
Надавить на клавиши Ctrl + Shift + «Стрелка ввысь». Результатом становится выделение подходящего спектра, даже если на этом участке столбца будет несколько сотен либо тыщ пт.
Вставить формулу. Самый обычный метод создать это — надавить комбинацию Ctrl + V.

Результатом будет такое же распределение функции по столбцу, как и при использовании метода №2. Но в отличие от него тут можно выделить лишь часть спектра. Либо, напротив, продлить такое протягивание далее даже при наличии пустых строк. Правда, во 2-м случае избыточное значение придется удалить вручную.

Эта маленькая хитрость подступает и для распределения вдоль строчки. В этом случае заместо композиции Ctrl + Shift + «Стрелка ввысь» придется надавить Ctrl + Shift + «Стрелка на лево» (либо на право, если копируемая формула находится в последнем левом столбце).

5 Протягивание формул в таблице Excel

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

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

Формула автоматом распределяется по колонке. Достоинства метода — скорость, сопоставимая с применением макроса. Недочет — работает он лишь при использовании табличной формы размещения данных в Excel и дозволяет протянуть формулу сходу до конца таблицы, а не до подходящей строчки.

Как привязать число к тексту в excel

Выделите столбец либо спектр, в котором вы желаете поместить значения, а потом нажмите клавиши CTRL + 1 , чтоб перейти в диалоговом окне Формат > ячейки , а потом на вкладке Число изберите текст . Сейчас Excel сохранит изначальное значение 0.

Как показать значение ячейки в Excel?

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

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

Как сцепить текст в Excel с пробелом?

Добавьте пробелы в формулу с функцией СЦЕПИТЬ. Это можно создать 2-мя методами. Добавьте двойные кавычки с пробелом меж ними (» «). К примеру: =СЦЕПИТЬ(«Привет,»; » «; «мир!»).

Как присвоить значение в Excel?

Чтоб присвоить значению имя делаем последующее: Изберите инструмент: «Формулы»-«Определенные имена»-«Присвоить имя». Введите имя «Наценка1», а в поле «Спектр» просто значение 10% потому что обозначено на рисунке. По мере необходимости изберите область распространения имени (на определенный лист либо на целую книжку).

Как текстовой ячейке придать числовое значение?

Выделите ячейки с числами, которые сохранены как текст. На вкладке Основная щелкните Вставить и изберите пункт Особая вставка. Щелкните помножить и нажмите клавишу ОК. Excel помножит каждую ячейку на 1, при всем этом преобразовав текст в числа.

Как вставить текст в формулу Excel?

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

Интересно почитать: В ms excel 2007 проверить правописание текстовых ячеек

Как в Excel создать ячейку невидимой?

Заходим в меню Файл → элемент Характеристики → пункт Допомлнительно → раздел показать характеристики для последующего листа. Тут избираем параметр Демонстрировать нули в ячейках, которые содержат нулевые значения, и убираем галочку. Сейчас нулевые значения не будут отображаться на листе для которого установлен этот параметр.

Как создать видимыми ячейки в Excel?

Отображение укрытых столбцов

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

Почему в ячейке отображается формула а не итог?

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

Как сцепить спектр ячеек Excel?

Для этого запишем формулу =СЦЕПИТЬ(A6:A9), указав в качестве единственного аргумента весь спектр сходу, а не отдельные ячейки. В итоге получим только значение первой ячейки. Сейчас в Строке формул выделим формулу =СЦЕПИТЬ(A6:A9) и нажмем кнопку F9.

Как создать фиксированную строчку в Excel?

Закрепление столбцов и строк

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

Как записывается логическая функция Если в Excel?

Чтоб решить поставленную задачку, воспользуемся логической функцией ЕСЛИ. Формула будет смотреться так: =ЕСЛИ(C2>=8;B2/2;B2). Логическое выражение «С2>=8» выстроено при помощи операторов дела «>» и «=». Итог его вычисления – логическая величина «ИСТИНА» либо «ЛОЖЬ».

Как в Excel привязать одну ячейку к иной?

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

Как присвоить имя ячейки в Excel?

Чтоб присвоить имя ячейке либо спектру сиим методом, проделайте последующие деяния:

Выделите требуемую область (на данном шаге можно выделить всякую область, в предстоящем вы можете ее перезадать). …
Перейдите на вкладку Формулы и изберите команду Присвоить имя.
Раскроется диалоговое окно Создание имени.

Как работает функция впр в excel?

Функция ВПР() , британский вариант VLOOKUP(), отыскивает значение в первом (в самом левом) столбце таблицы и возвращает значение из той же строчки, но другого столбца таблицы. Функция ВПР() является одной из более применяемых в EXCEL, потому разглядим ее тщательно.

Как показать текст и число в одной ячейке

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

Конкатенация;
Функция СЦЕПИТЬ;
Функция ТЕКСТ;
Пользовательский формат.

Разберем эти методы и разглядим плюсы и минусы всякого из их.

Внедрение конкатенации

Один из самых обычных методов воплотить сочетание текста и значения — применять конкатенацию (знак &).

Допустим ячейка A1 содержит итоговое значение 123,45, тогда в хоть какой иной ячейке можно записать формулу
="Итого: "&A1

В итоге результатом будет последующее содержание ячейки Итого: 123,45.

Это обычное решение, но имеет много минусов.

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

Применение функции СЦЕПИТЬ

Аналогичное обычное решение, но с теми же недочетами — внедрение функции СЦЕПИТЬ. Применяется она так: =СЦЕПИТЬ("Итого: ";A1). Результаты ее использования подобные:

Применение функции ТЕКСТ

Функция ТЕКСТ дозволяет не только лишь соединить текст и значение, но к тому же отформатировать значение в подходящем формате. Если мы применим последующую формулу =ТЕКСТ(A1;"""Итого: ""##0"), то мы получим таковой итог Итого: 123.

В качестве второго аргумента функция ТЕКСТ воспринимает строчку с числовым форматом. Наиболее тщательно о числовых форматах вы сможете прочесть в статье Применение пользовательских форматов.

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

Внедрение пользовательского формата

Не таковой обычный метод как прошлые, но более многофункциональный. Заключается в применении к итоговой ячейки пользовательского числового формата. Чтоб добавить текст "Итого" к ячейке A1 нужны последующие деяния:

Изберите ячейку A1.
Откройте диалоговое окно Формат ячейки.
В поле Тип укажите подходящий формат. В нашем случае "Итого: "# ##0.

В итоге ячейка A1 будет содержать Итого: 123.

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