Сцепить и левсимв в excel
Используйте функцию СЦЕПИТЬ, относящуюся к текстовым функциям, для объединения двух или нескольких текстовых строк в одну.
Важно: В Excel 2016, Excel Mobile и Excel в Интернете эта функция была заменена функцией СКАЙТ. Функция СЦЕПИТЬ пока еще доступна для обратной совместимости, однако рекомендуем использовать вместо нее функцию СЦЕП. В последующих версиях Excel функция СЦЕПИТЬ может быть недоступна.
Синтаксис: СЦЕПИТЬ(текст1;[текст2];…)
Ниже приведены примеры.
=СЦЕПИТЬ("Популяция рек для ";A2;" ";A3;" составляет ";A4;" на километр.")
Имя аргумента
текст1 (обязательный)
Первый элемент для объединения. Это может быть текстовое значение, число или ссылка на ячейку.
текст2. (необязательные)
Дополнительные текстовые элементы для объединения. Можно указать до 255 элементов и до 8 192 символов.
Примеры
Чтобы использовать эти примеры в Excel, скопируйте данные из приведенной ниже таблицы и вставьте их на новый лист в ячейку A1.
=СЦЕПИТЬ("Популяция рек для ";A2;" ";A3;" составляет ";A4;" на километр.")
Создает предложение, объединяя данные в столбце А с остальным текстом. Результат: "Популяция рек для вида речная форель составляет 32 на километр".
Объединяет строку в ячейке В2, пробел и значение в ячейке С2. Результат: "Виталий Токарев".
Объединяет текст в ячейке C2, строку, состоящую из запятой и пробела, и значение в ячейке B2. Результат: "Виталий Токарев".
Объединяет строку в ячейке B3, строку, состоящую из пробела, амперсанда и еще одного пробела, и значение в ячейке C3. Результат: Fourth & Pine.
Объединяет те же элементы, что и в предыдущем примере, но с помощью оператора & (амперсанд) вместо функции СЦЕПИТЬ. Результат: Fourth & Pine.
Распространенные неполадки
Кавычки отображаются в строке результата.
Используйте точку с запятой для разделения текстовых элементов. Например: Excel отобразит результат функции =СЦЕПИТЬ ("Привет, ""мир!") как "Привет,"мир!" с лишней кавычкой, так как пропущена точка с запятой между текстовыми аргументами.
Числа не нужно заключать в кавычки.
Слова расположены впритык друг к другу.
Если не поместить пробелы между текстовыми элементами, в строке результата они будут отображаться слитно. Добавьте пробелы в формулу с функцией СЦЕПИТЬ. Это можно сделать двумя способами.
Добавьте двойные кавычки с пробелом между ними (" "). Например: =СЦЕПИТЬ("Привет,"; " "; "мир!").
Добавьте пробел после текстового аргумента. Например: =СЦЕПИТЬ("Привет, "; "мир!"). К строке "Привет," добавлен пробел.
Рекомендации
Необходимые действия
Вместо функции СЦЕПИТЬ используйте знак & (амперсанд).
С помощью оператора & можно объединять текстовые элементы, не прибегая к функции.
Например, =A1 & B1 возвращает то же значение, что и =С CONCATENATE(A1;B1). Во многих случаях воспользоваться оператором амперсанда проще и быстрее, чем создавать строки с помощью функции СЦЕПИТЬ.
Для объединения и форматирования строк используйте функцию ТЕКСТ.
Функция ТЕКСТ преобразует числовое значение в текст и объединяет числа с текстом или символами.
Предположим, что ячейка A1 содержит число 23,5. Чтобы отформатировать число как сумму в рублях, можно использовать следующую формулу:
Дополнительные сведения
Для объединения и форматирования строк используйте функцию ТЕКСТ.
Ознакомьтесь с дополнительными сведениями об использовании операторов вычислений.
Чтобы объединить значения (текстовые, цифровые) из разных ячеек в одну, используется функция СЦЕПИТЬ. Ее аналог – & (амперсанд). Знак справляется с простейшими задачами. Но для объединения множества строк не подходит.
Синтаксис функции СЦЕПИТЬ
Функция входит в группу «текстовые». Синтаксис: (текст 1; текст 2;…). Можно включить до 255 текстовых строк. Первый аргумент – «текст 1» – является обязательным. Любые аргументы можно указать в виде текста, цифр или ссылок на ячейки.
Общие правила использования функции СЦЕПИТЬ:
Как пользоваться функцией СЦЕПИТЬ в Excel
Начнем с простейших примеров.
Данные для объединения:
Ставим курсор в ячейку Е2 и вызываем мастер функций (кнопка fx). В категории «Текстовые» находим функцию СЦЕПИТЬ. Аргументы – ссылки на ячейки А2, В2, С2 и пробелы:
Результат объединения значений в ячейках:
Такой же результат получим с помощью амперсанда:
Один из аргументов – текст. Формула: =СЦЕПИТЬ("слесарь"; " ";A2;" ";B2;" ";C2). Результат:
И с более сложными задачами справится функция СЦЕПИТЬ в Excel. Примеры:
- Соединим текст с датой. Данные находятся в отдельных ячейках. Для столбца «Дата» установлен формат «Дата». Если использовать только функцию СЦЕПИТЬ, дата отобразится в числовом формате. Поэтому добавим функцию ТЕКСТ для форматирования чисел. Формула: Второй аргумент функции ТЕКСТ – формат представления даты. Результат:
- Соединим сразу много ячеек в строке. Если писать формулу с функцией СЦЕПИТЬ, получится долго и малоэффективно. Используем маленькую хитрость. Вот наша строка со значениями в отдельных ячейках. В ячейку Н1 ставим знак «равно». Выделяем диапазон со значениями А1:F1. Вводим амперсанд & и пробел “ “. Жмем F9. В строке формул появится формула массива. Ставим курсор в строку формул после знака «равно». Пишем название функции. Фигурные скобки заменяем на круглые и жмем ВВОД. Получаем нужный результат.
- Соединим значения через косую черточку («-», «&» и т.п.). Формула будет выглядеть так: = СЦЕПИТЬ (ссылка на ячейку 1; «/»; ссылка на ячейку 2).
- Соединим несколько значений в столбце. Функция подходит для значений в строках. Описанный выше способ с формулой массива тоже не сработает.
Преобразуем вертикальный диапазон в горизонтальный с помощью функции ТРАНСП. А затем воспользуемся формулой массива для быстрого соединения значений. В ячейку В1 ставим знак «равно». Пишем ТРАНСП. Указываем диапазон А1:А6. Вводим & и пробел (« »). Жмем F9.
Теперь преобразуем формулу массива, добавив функцию и заменив скобки.
Обратная функция СЦЕПИТЬ в Excel
Стандартной функции, которая разделяла бы значения одной ячейки, нет. Для выполнения ряда подобных задач можно использовать другие функции:
- ЛЕВСИМВ (отображает заданную часть символов с начала строки);
- ПРАВСИМВ (отображает заданную часть символов с конца строки);
- ПСТР (отображает указанную часть символов, начиная с заданной позиции) и другие.
- Извлечем первое слово из строки, применив функцию ЛЕВСИМВ. Аргументы – «текст» (ссылка на строку с искомым значением), «количество знаков» (число символов, которые нужно извлечь).
- Извлечем последнее слово из строки с помощью функции ПРАВСИМВ. Синтаксис похожий (как в предыдущем примере). Только количество знаков считается с конца.
- Извлечем из записи в строке фамилию (второе слово) с помощью функции ПСТР. Аргументы – «текст» (ссылка на строку), «начальная позиция» (номер символа, с которого программа начнет считать число знаков); «количество знаков». В примере: Е2 – «текст», с 9 знака функция вернет 8 символов.
Для выполнения более сложных задач используются более сложные формулы, составляются пользовательские функции.
Одна из самых важных задач при редактировании текстовых строк в Excel – это отделение части строки для получения фрагмента текста.
Примеры основных текстовых функций в Excel с описанием
С помощью текстовых функций: ЛЕВСИМВ, ПРАВСИМВ и ПСТР в процессе офисной работы можно быстро и легко решить такие задачи как:
- преобразование территориального кода в почтовый код;
- удаление кода страны или кода оператора сотовой связи в номерах телефонов
- выборка фрагмента кода сотрудника или должности из номера картотеки в базе данных кадрового отдела;
- и похожие другие задачи…
Умение быстро решать подобного рода базовые задачи в Excel пригодиться каждому офисному сотруднику.
На рисунке примеров показано, как легко с помощью текстовых функций ЛЕВСИМВ, ПРАВСИМВ и ПСТР реализуются решения выше описанных задач:
Функция ЛЕВСИМВ предоставляет возможность пользователю Excel отделить от значения ячейки фрагмент текста или числа с определенным количеством символов от начала исходной строки данных. Данная функция требует указать 2 аргумента:
- Текст – исходные данные. Поддерживает и другие типы значений кроме текстовых: число, логическое значение. Не поддерживает значение ошибок, а дату воспринимает как числовое значение.
- Количество_знаков – количество символов, взятых от начала, которые следует оставить в фрагменте текста при отделения его от строки. Другими словами, длина вырезанного фрагмента текста с левой стороны исходной строки.
В примере функция =ЛЕВСИМВ(A2 ;5) отделяет 5 первых чисел из текста в ячейке A2.
Текстовая функция ПРАВСИМВ предоставляет возможность отделения определенного количества символов с конца исходной текстовой строки. Фактически данная функция работает обратно пропорционально для ЛЕВСИМВ. И требует заполнить такие же аргументы. Главное отличие — это направление действия функции: с права на лево, то есть начиная отсчет символов с конца.
В данном примере с помощью текстовой функции ПРАВСИМВ отделяется правая часть номеров телефонов сотрудников фирмы. Это 9 последних знаков в конце: =ПРАВСИМВ(A6;9).
Следующая текстовая функция ПСТР более продвинутая. Она позволяет получить фрагмент текста из середины исходной строки. Функция ПСТР требует заполнить 3 аргумента:
- Текст – исходные данные (текстовое либо числовое значение).
- Начальная_позиция – порядковый номер символа от начала строки с которого следует начать отделение фрагмента текста.
- Количество_знаков – количество символов, взятых из середины текста в исходных данных.
В описанном примере функция =ПСТР(A10;4;1) – отделяет только одно числовое значение начинающиеся от 4-го символа с начала исходной строки.
Функция ЛЕВСИМВ в Excel используется для получения части текста и возвращает заданное количество символов текстовой строки, при этом отсчет символов начинается слева.
Как вырезать часть текста из строки функцией ЛЕВСИМВ в Excel?
Пример 1. В таблице Excel содержатся имена переменных вместе с текстом, определяющим их тип данных. Необходимо выделить только названия переменных и записать их в отдельный столбец.
Исходная таблица данных:
Для выделения первого символа, которым является искомое название переменной, используем формулу:
Второй равен 1 по умолчанию, поэтому функция возвращает первый символ. Аналогично выделим названия переменных из других строк. В результате получим:
Как видно на рисунке с помощью функции ЛЕВСИМВ получилось вытащить левую часть текста из каждой ячейки по одному (первому) символу.
Как вытащить часть текста из ячейки в Excel используя функцию ЛЕВСИМВ?
Пример 2. В таблицу Excel подтягиваются данные из другого программного продукта. Одно из полей данных получило ошибку. Для обработки ошибки необходимо выделить ее код и описание из строки.
Выделим код ошибки с помощью следующей формулы:
- ЛЕВСИМВ(B5;НАЙТИ("3";B5)+1) – функция, выделяющая часть текстовой строки слева, количество символов в которой соответствуют порядковому номеру символа «3» (функция НАЙТИ выполняет поиск порядкового номера этого символа и возвращает соответствующее значение). Единица (+1) добавлена для выделения всего кода ошибки.
- 2 – количество символов, которые вернет функция ПРАВСИМВ, работающая аналогично рассматриваемой ЛЕВСИМВ (отличие – возвращает символы справа, а не слева строки).
В результате получим:
Выделим часть текста ошибки:
В данном случае удобнее использовать ПРАВСИМВ, которая является, по сути, зеркальным отражением ЛЕВСИМВ. Для определения второго аргумента используется запись ДЛСТР(B5)-НАЙТИ(":";B5)-1, определяющая позицию символа «u» в слове «unknown» (единица вычтена для удаления пробела).
В результате получим:
Округление чисел текстовыми функциями в текстовой строке
Пример 3. Создать функцию для округления дробных чисел до ближайших целых значений с использованием формул для работы с текстом.
Форма для округления числовых значений имеет следующий вид:
В ячейке B3 введена следующая сложная формула:
=5,ЛЕВСИМВ(A2+1,НАЙТИ(".",A2)-1),ЛЕВСИМВ(A2,НАЙТИ(".",A2)-1)),"Число не введено")' >
- Первая функция ЕСЛИ проверяет с помощью формулы ЕПУСТО, заполнена ли ячейка, которая должна содержать значение, которое требуется округлить.
- Если число введено, запись ЧЗНАЧ(ПРАВСИМВ(ЛЕВСИМВ(B2;НАЙТИ(",";B2)+1)) возвращает числовое значение первого символа после запятой и сравнивает полученное значение с числом 5.
- Если полученное значение больше 5, будет возвращен результат выполнения функции ЛЕВСИМВ(B2+1;НАЙТИ(",";B2)-1), то есть целой части числа +1 по правилам округления.
- Если <5, то будет возвращен результат работы функции ЛЕВСИМВ(B2;НАЙТИ(",";B2)-1)), то есть просто целая часть исходного числа.
Примечание: данный алгоритм нельзя назвать гибким и он имеет некоторые недостатки (например, не учтен вариант ввода сразу целого числа). Он приведен в качестве примера использования функции ЛЕВСИМВ. Для расчетов лучше использовать встроенную функцию ОКРГУГЛ и подобные ей.
Функция ЛЕВСИМВ в Excel и особенности ее использования
Функция ЛЕВСИМВ используется наряду с прочими функциями для работы с текстовыми строками в Excel (ПРАВСИМВ, СИМВОЛ, СЦЕПИТЬ, ДЛСТР и прочими) и имеет следующую синтаксическую запись:
- текст – ссылка на ячейку, содержащую текст, или текстовая строка, в которой требуется выделить требуемое количество символов слева. Аргумент обязателен для заполнения.
- [число_знаков] – необязательный для заполнения аргумент; числовое значение, характеризующее количество символов, которые требуется выделить из текстовой строки.
Примечание 2: в Excel также существует схожая по принципу работы функция ЛЕВБ, возвращающая определенное количество символов текстовой строки слева на основе определенного количества байтов. Если по умолчанию используется язык с поддержкой двухбайтовой кодировки, для получения первого символа строки требуется в качестве аргумента указать число 2, а для получения последующих второго, третьего и прочих символов необходимо указывать соответствующие числа, кратные двум. Для языков с однобайтовой кодировкой функции ЛЕВСИМВ и ЛЕВБ возвращают идентичные результаты.
Читайте также: