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

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

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

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

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

Пример 1

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

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

Пример 2

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

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

Пример 3

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

Пример 4

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • ЛЕВСИМВ (показывает заданную часть знаков с начала строчки);
  • ПРАВСИМВ (показывает заданную часть знаков с конца строчки);
  • ПСТР (показывает обозначенную часть знаков, начиная с данной позиции) и остальные.
  1. Извлечем 1-ое слово из строчки, применив функцию ЛЕВСИМВ. Аргументы — «текст» (ссылка на строчку с разыскиваемым значением), «количество символов» (число знаков, которые необходимо извлечь).
  2. Извлечем крайнее слово из строчки при помощи функции ПРАВСИМВ. Синтаксис схожий (как в прошлом примере). Лишь количество символов считается с конца.
  3. Извлечем из записи в строке фамилию (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?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Плюсы и минусы способов

В таблице дальше сведены плюсы и минусы. Зависимо от ситуации можно воспользоваться тем либо другим методом обращая на индивидуальности всякого.

Ссылка на основную публикацию
Adblock
detector