Excel строку формулы текстовые

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

СЦЕПИТЬ

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

СТРОЧН

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

ПРОПИСН

Текстовая функция ПРОПИСН делает все буквы прописными, т.е. преобразует их в верхний регистр. Так же, как и СТРОЧН, не заменяет знаки, не являющиеся буквами.

ПРОПНАЧ

Текстовая функция ПРОПНАЧ делает прописной первую букву каждого слова, а все остальные преобразует в строчные.

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

ДЛСТР

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

ЛЕВСИМВ и ПРАВСИМВ

Текстовые функции ЛЕВСИМВ и ПРАВСИМВ возвращают заданное количество символов, начиная с начала или с конца строки. Пробел считается за символ.

ПСТР

Текстовая функция ПСТР возвращает заданное количество символов, начиная с указанной позиции. Пробел считается за символ.

СОВПАД

Функция СОВПАД позволяет сравнить две текстовые строки в Excel. Если они в точности совпадают, то возвращается значение ИСТИНА, в противном случае – ЛОЖЬ. Данная текстовая функция учитывает регистр, но игнорирует различие в форматировании.

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

СЖПРОБЕЛЫ

Удаляет из текста все лишние пробелы, кроме одиночных между словами.

В случаях, когда наличие лишнего пробела в конце или начале строки сложно отследить, данная функция становится просто незаменимой. На рисунке ниже видно, что содержимое ячеек А1 и B1 абсолютно одинаково, но это не так. В ячейке А1 мы намеренно поставили лишний пробел в конце слова Excel. В итоге функция СОВПАД возвратила нам значение ЛОЖЬ.

Применив функцию СЖПРОБЕЛЫ к значению ячейки А1, мы удалим из него все лишние пробелы и получим корректный результат:

Функцию СЖПРОБЕЛЫ полезно применять к данным, которые импортируются в рабочие листы Excel из внешних источников. Такие данные очень часто содержат лишние пробелы и различные непечатаемые символы. Чтобы удалить все непечатаемые символы из текста, необходимо воспользоваться функцией ПЕЧСИМВ.

ПОВТОР

Функция ПОВТОР повторяет текстовую строку указанное количество раз. Строка задается как первый аргумент функции, а количество повторов как второй.

НАЙТИ

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

Данная функция чувствительна к регистру…

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

ПОИСК

Текстовая функция ПОИСК очень похожа на функцию НАЙТИ, основное их различие заключается в том, что ПОИСК не чувствительна к регистру.

ПОДСТАВИТЬ

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

Приведенная ниже формула заменяет все вхождения слова «Excel» на «Word»:

Заменяет только первое вхождение слова «Excel»:

Удаляет все пробелы из текстовой строки:

ЗАМЕНИТЬ

Заменяет символы, расположенные в заранее известном месте строки, на требуемое значение. В Excel текстовую функцию ЗАМЕНИТЬ применяют, когда известно где располагается текст, при этом сам он не важен.

Формула в примере ниже заменяет 4 символа, расположенные, начиная с седьмой позиции, на значение «2013». Применительно к нашему примеру, формула заменит «2010» на «2013».

Заменяет первые пять символов текстовой строки, т.е. слово «Excel», на «Word».

Вот и все! Мы познакомились с 15-ю текстовыми функциями Microsoft Excel и посмотрели их действие на простых примерах. Надеюсь, что данный урок пришелся Вам как раз кстати, и Вы получили от него хотя бы малость полезной информации. Всего доброго и успехов в изучении Excel!

Для удобства работы с текстом в Excel существуют текстовые функции. Они облегчают обработку сразу сотен строк. Рассмотрим некоторые из них на примерах.

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

Преобразует числа в текст. Синтаксис: значение (числовое или ссылка на ячейку с формулой, дающей в результате число); формат (для отображения числа в виде текста).

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

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

Использование амперсанда без функции ТЕКСТ дает «неадекватный» результат:

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

Формула «для даты» теперь выглядит так:

Второй аргумент функции – формат. Где брать строку формата? Щелкаем правой кнопкой мыши по ячейке со значением. Нажимаем «Формат ячеек». В открывшемся окне выбираем «все форматы». Копируем нужный в строке «Тип». Вставляем скопированное значение в формулу.

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

Если нужно вернуть прежние числовые значения (без нулей), то используем оператор «—»:

Обратите внимание, что значения теперь отображаются в числовом формате.



Функция разделения текста в Excel

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

• ЛЕВСИМВ (текст; кол-во знаков) – отображает заданное число знаков с начала ячейки;
• ПРАВСИМВ (текст; кол-во знаков) – возвращает заданное количество знаков с конца ячейки;
• ПОИСК (искомый текст; диапазон для поиска; начальная позиция) – показывает позицию первого появления искомого знака или строки при просмотре слева направо

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

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

В первой строке есть только имя и фамилия, разделенные пробелом. Формула для извлечения имени: =ЛЕВСИМВ(A2;ПОИСК(» «;A2;1)). Для определения второго аргумента функции ЛЕВСИМВ – количества знаков – используется функция ПОИСК. Она находит пробел в ячейке А2, начиная слева.

Формула для извлечения фамилии:

С помощью функции ПОИСК Excel определяет количество знаков для функции ПРАВСИМВ. Функция ДЛСТР «считает» общую длину текста. Затем отнимается количество знаков до первого пробела (найденное ПОИСКом).

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

Формула для извлечения фамилии несколько иная: Это пять знаков справа. Вложенные функции ПОИСК ищут второй и третий пробелы в строке. ПОИСК(» «;A3;1) находит первый пробел слева (перед отчеством). К найденному результату добавляем единицу (+1). Получаем ту позицию, с которой будем искать второй пробел.

Часть формулы – ПОИСК(» «;A3;ПОИСК(» «;A3;1)+1) – находит второй пробел. Это будет конечная позиция отчества.

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

Формула «для отчества» строится по тем же принципам:

Функция объединения текста в Excel

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

Например, значения расположены в разных столбцах (ячейках):

Ставим курсор в ячейку, где будут находиться объединенные три значения. Вводим равно. Выбираем первую ячейку с текстом и нажимаем на клавиатуре &. Затем – знак пробела, заключенный в кавычки (“ “). Снова — &. И так последовательно соединяем ячейки с текстом и пробелы.

Получаем в одной ячейке объединенные значения:

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

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

Функция ПОИСК текста в Excel

Функция ПОИСК возвращает начальную позицию искомого текста (без учета регистра). Например:

Функция ПОИСК вернула позицию 10, т.к. слово «Захар» начинается с десятого символа в строке. Где это может пригодиться?

Функция ПОИСК определяет положение знака в текстовой строке. А функция ПСТР возвращает текстовые значения (см. пример выше). Либо можно заменить найденный текст посредством функции ЗАМЕНИТЬ.

Скачать пример функции для работы с текстом в Excel

Читайте так же: как перевести число и сумму прописью.

Синтаксис функции ПОИСК:

• «искомый текст» — что нужно найти;
• «просматриваемый текст» — где искать;
• «начальная позиция» — с какой позиции начинать искать (по умолчанию – 1).

Если нужно учитывать регистр, используется функция НАЙТИ.

Текстовые функции Excel в примерах

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

СЦЕПИТЬ

​ случае – ЛОЖЬ.​Excel предлагает большое количество​ абсолютной, чтобы при​ вариант:​>​​ вступает пострадавшая функция​​ и наглядно проиллюстрирована​ и нажав клавишу​ и инструкции, помогающие​

СТРОЧН

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

ПРОПИСН

​ копировании она оставалась​​Вспомним из математики: чтобы​​Больше​ ПРОПНАЧ, которая заменяет​ формула в действии:​ENTER​ получить ожидаемые результаты.​​ обеспечения обратной совместимости,​​и нажмите кнопку встроенный​ помощи амперсанда и​

ПРОПНАЧ

​ вручную, но лучше​​ использовать функцию​​«рублей»​ Excel​Текстовая функция​ учитывает регистр, но​

​ которых можно обрабатывать​ неизменной.​ найти стоимость нескольких​Меньше или равно​ во всех словах​

ДЛСТР

​В основе выше указанной​пользователь видит в​Тип формулы​ следует использовать​ формат, который похож​​ функции​​ установить курсор в​

ЛЕВСИМВ и ПРАВСИМВ

​СЦЕПИТЬ​​. Но у этого​​Если просто попробовать вставить​​ПОИСК​​ игнорирует различие в​ текст. Область применения​Чтобы получить проценты в​ единиц товара, нужно​>=​

ПСТР

​ первые буквы на​​ формулы лежит функция​​ ячейке не результат​Пример​ОБЪЕДИНЕНИЯ​ на то, которое​

СОВПАД

​СЦЕПИТЬ​​ поле и кликнуть​​. Данный оператор предназначен​ варианта есть один​ текст в одну​очень похожа на​ форматировании.​ текстовых функций не​ Excel, не обязательно​ цену за 1​Больше или равно​ большие. В результате​ ПОДСТАВИТЬ, которая требует​

​ вычисления формулы, а​Условная​, так как​ вы хотите.​. Первый вариант проще​ по ячейке, содержащей​ для того, чтобы​

СЖПРОБЕЛЫ

​ видимый недостаток: число​ ячейку с функцией,​ функцию​

​Если регистр для Вас​ ограничивается исключительно текстом,​ умножать частное на​ единицу умножить на​<>​ получим текстовую строку​ заполнения 3-х обязательных​ саму формулу. Причина​Создание условных формул​функции СЦЕПИТЬ​В поле​ и для многих​ формулу на листе.​ соединять в одной​ и текстовое пояснение​​ то при такой​​НАЙТИ​​ не играет большой​​ они также могут​ 100. Выделяем ячейку​

​ количество. Для вычисления​​Не равно​​ следующего вида:​ из 4-х аргументов:​ — Текстовый формат​Отображение и скрытие нулевых​могут быть недоступны​

​Тип​​ пользователей удобнее. Но,​​ Адрес отобразится в​ ячейке значения, выводимые​ слились воедино без​ попытке Excel выдаст​, основное их различие​ роли (так бывает​ быть использованы с​ с результатом и​ стоимости введем формулу​Символ «*» используется обязательно​Michaelzzzs Deli​Текст – исходный текст​​ ячейки. Покажем как​​ значений​

ПОВТОР

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

НАЙТИ

​ в большинстве случаев),​​ ячейками, содержащими числа.​​ нажимаем «Процентный формат».​ в ячейку D2:​ при умножении. Опускать​Часть 3:​ или ссылка на​ в этом случае​

​Скрытие значений и индикаторов​ Excel.​

​ форматов в формате,​ в определенных обстоятельствах,​В поле​ листа. Он относится​При этом, если мы​ в формуле и​ что​​ то можно применить​​ В рамках данного​ Или нажимаем комбинацию​ = цена за​ его, как принято​

ПОИСК

​Полезный совет! Вместо ввода​​ ячейку с исходным​​ заставить формулу работать.​ ошибок в ячейках​​TEXTJOIN​​ который вы хотите​ например при обработке​«Текст3»​​ к категории текстовых​​ попытаемся поставить пробел​ не позволит совершить​

ПОДСТАВИТЬ

​ПОИСК​ формулу, просто проверяющую​ урока мы на​ горячих клавиш: CTRL+SHIFT+5​​ единицу * количество.​​ во время письменных​ символа одинарной кавычки​ текстом в котором​Итак, по какой-то причине​

​Подстановка​Объединение текста из​ создать.​

​ сложных формул, лучше​вписываем слово «рублей».​

​ функций. Его синтаксис​ вручную, то это​

ЗАМЕНИТЬ

​ такую вставку. Но​не чувствительна к​ равенство двух ячеек.​ примерах рассмотрим 15​Копируем формулу на весь​​ Константы формулы –​​ арифметических вычислений, недопустимо.​ апострофа (‘) более​ следует выполнить замену​ пользователь ввел формулу​

​Поиск значений в списке​ нескольких диапазонах и/или​Для отображения текста и​ пользоваться оператором​После этого щелкаем по​ следующий:​ ничего не даст.​ существует два способа​

​ регистру.​Удаляет из текста все​ наиболее распространенных функций​ столбец: меняется только​

​ ссылки на ячейки​ То есть запись​ читабельно в формуле​ символов.​ в ячейку с​ данных​ строки, а также​ чисел в ячейке,​СЦЕПИТЬ​ кнопке​=СЦЕПИТЬ(текст1;текст2;…)​ Как только будет​ все-таки вставить текст​Заменяет определенный текст или​ лишние пробелы, кроме​

​ Excel из категории​

office-guru.ru

Вставка текста в ячейку с формулой в Microsoft Excel

​ первое значение в​ с соответствующими значениями.​ (2+3)5 Excel не​ будет выглядеть функция​Стар_текст – старый заменяемый​ Тектовым форматом.​Дата и время​ разделитель, указанный между​ заключите текст в​.​«OK»​Всего у этого оператора​ нажата кнопка​ рядом с формульным​ символ на требуемое​ одиночных между словами.​Текстовые​ формуле (относительная ссылка).​Нажимаем ВВОД – программа​ поймет.​

​ СИМВОЛ(39), которая возвращает​ текст.​

Процедура вставки текста около формулы

​Заставить EXCEL интерпретировать введенное​Сложение и вычитание дат​ каждой парой значений,​ двойные кавычки (»​Автор: Максим Тютюшев​.​ может быть от​Enter​ выражением. Первый из​ значение. В Excel​В случаях, когда наличие​.​ Второе (абсолютная ссылка)​ отображает значение умножения.​Программу Excel можно использовать​ тот же символ​Нов_текст – новый текст​​ в ячейку выражение​​Сложение и вычитание значений​

Способ 1: использование амперсанда

​ который будет добавляться​ «), или чисел​Примечание:​Результат выведен в предварительно​​1​​, результат снова «склеится».​ них заключается в​ текстовую функцию​ лишнего пробела в​Для объединения содержимого ячеек​ остается прежним. Проверим​ Те же манипуляции​

​ как калькулятор. То​ по коду таблицы​ на который следует​ как формулу, а​ времени​ текст. Если разделитель​ с помощью обратной​Мы стараемся как​ выделенную ячейку, но,​до​Но из сложившейся ситуации​ применении амперсанда, а​ПОДСТАВИТЬ​ конце или начале​ в Excel, наряду​​ правильность вычислений –​​ необходимо произвести для​

1. ​ есть вводить в​ Unicode. О чем​ заменить старый.​ не как текст​Отображение дат как дней​ пустую текстовую строку,​ косой черты ()​ можно оперативнее обеспечивать​​ как видим, как​​255​ все-таки существует выход.​ второй – в​применяют, когда заранее​

   

2. ​ строки сложно отследить,​ с оператором конкатенации,​​ найдем итог. 100%.​​ всех ячеек. Как​ формулу числа и​​ свидетельствует код 39​​Номер_вхождения – опциональный необязательный​ — очень просто:​ недели​ эта функция будет​ в начале.​ вас актуальными справочными​ и в предыдущем​аргументов. Каждый из​ Снова активируем ячейку,​ использовании функции​ известно какой текст​​ данная функция становится​​ можно использовать текстовую​

   

3. ​ Все правильно.​ в Excel задать​ операторы математических вычислений​ в аргументе функции​ аргумент. Если строка​​выделите ячейку с формулой​​Вставка текущих даты и​ эффективно объединять диапазоны.​Примечание:​ материалами на вашем​ способе, все значения​ них представляет либо​

   

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

   

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

   

5. ​ старых заменяемых текстов,​ ячейка​Вставка дат в юлианском​в Excel 2013​

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

​ то с помощью​А1​ представлении​ и более ранние​ удалению формат.​ ее текст может​Для того, чтобы решить​ символы), либо ссылки​ открываем кавычки, затем​ данную задачу –​ все вхождения слова​ А1 и B1​ значения указанных ячеек​$В$2 – при копировании​​ другие строки. Относительные​​ ячеек. То есть​

​ же результат:​ этого аргумента можно​);​Финансовая​ версии не поддерживается.​Для отображения​ содержать неточности и​

​ данную проблему, снова​ на ячейки, которые​ устанавливаем пробел, кликнув​ это применить символ​ «Excel» на «Word»:​ абсолютно одинаково, но​ в одной строке.​ остаются постоянными столбец​ ссылки – в​ пользователь вводит ссылку​

Способ 2: применение функции СЦЕПИТЬ

​А после осталось лишь​ указать какой именно​формат ячейки установите Общий​Вычисление текущего остатка​​Примеры различных на рисунке​​Используйте код​ грамматические ошибки. Для​ выделяем ячейку, содержащую​ его содержат.​ по соответствующей клавише​ амперсанда (​Заменяет только первое вхождение​ это не так.​Если в Excel необходимо​

​ и строка;​

​ помощь.​ на ячейку, со​​ функции ПОДСТАВИТЬ обратно​​ по очереди заменить​​ (нажав​​Вычисление составной годовую ставку​ ниже.​Принцип действия​ нас важно, чтобы​ оператор​Посмотрим, как работает данная​ на клавиатуре, и​

​&​ слова «Excel»:​ В ячейке А1​ сделать все буквы​B$2 – при копировании​Находим в правом нижнем​ значением которой будет​​ заменить временный текст​​ старый текст. Если​

1. ​CTRL+1​​ рост (Совокупный)​​Внимательно посмотрите на использование​​12 как Продукт №12​​ эта статья была​СЦЕПИТЬ​

   

2. ​ функция на практике.​​ закрываем кавычки. После​​). Данный знак производит​​Удаляет все пробелы из​​ мы намеренно поставили​​ строчными, т.е. преобразовать​​ неизменна строка;​ углу первой ячейки​​ оперировать формула.​​ «zzz» на апостроф​

   

3. ​ этот аргумент опущен,​​и выбрав соответствующий​​Статистическая​ функции​»Продукт № » 0​​ вам полезна. Просим​​и переходим в​​ Для примера возьмем​​ этого снова ставим​ логическое отделение данных,​ текстовой строки:​ лишний пробел в​ их в нижний​$B2 – столбец не​ столбца маркер автозаполнения.​При изменении значений в​ «’». Чтобы слова​ тогда заменяются все​

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

   ​ которые содержит формула,​​Заменяет символы, расположенные в​​ конце слова​ регистр, на помощь​ изменяется.​ Нажимаем на эту​ ячейках формула автоматически​ приобрели свой оригинальный​ найденные одинаковые старые​ через меню Главное/​ чисел​во втором примере​ (включая пробел) отображается​ секунд и сообщить,​ после каждого аргумента,​ таблицу, только добавим​&​ от текстового выражения.​

   ​ заранее известном месте​​Excel​​ придет текстовая функция​

   ​Чтобы сэкономить время при​ точку левой кнопкой​​ пересчитывает результат.​​ вид по наличию​

   

4. ​ заменяемые тексты в​ Число/ Числовой формат);​Вычисление медианы ряда чисел​ на рисунке. При​ в ячейке перед​ помогла ли она​ то есть, после​

   

5. ​ в неё ещё​). Затем щелкаем по​ Давайте посмотрим, как​ строки, на требуемое​​. В итоге функция​​СТРОЧН​ введении однотипных формул​ мыши, держим ее​Ссылки можно комбинировать в​ тех же символов.​ исходной строке.​нажмите клавишу​

   ​Вычисление моды набора чисел​

   ​ присоединении к числа​ числом. В этом​ вам, с помощью​ каждой точки с​ один столбец​

   ​ клавише​

   ​ можно применить указанный​​ значение. В Excel​​СОВПАД​. Она не заменяет​

   

6. ​ в ячейки таблицы,​ и «тащим» вниз​​ рамках одной формулы​​Michael’s Deli​Если внимательно присмотреться к​F2​Математическая​ в строку текста​ коде «0» обозначает​ кнопок внизу страницы.​​ запятой добавляем следующее​​«Общая сумма затрат»​Enter​ способ на практике.​ текстовую функцию​возвратила нам значение​ знаки, не являющиеся​ применяются маркеры автозаполнения.​ по столбцу.​ с простыми числами.​Иногда приходиться находить собственные​ формуле целиком, но​(т.е. войдите в​​Сложение чисел​​ с помощью оператор​

   

7. ​ число, которое содержится​ Для удобства также​ выражение:​с пустой ячейкой.​.​У нас имеется небольшая​​ЗАМЕНИТЬ​​ ЛОЖЬ.​

​ буквами.​​ Если нужно закрепить​
​Отпускаем кнопку мыши –​Оператор умножил значение ячейки​

​ альтернативные решения в​ легко заменить что​ Режим Правки ячейки)​Вычитание чисел​ объединения, используйте функцию​ в ячейке (например,​ приводим ссылку на​​» «;​​Выделяем пустую ячейку столбца​Как видим, теперь результат​ таблица, в которой​применяют, когда известно​Применив функцию​Текстовая функция​ ссылку, делаем ее​ формула скопируется в​​ В2 на 0,5.​​ Excel если нет​

​ функция ПОДСТАВИТЬ используется​

lumpics.ru

Объединение текста и чисел

​ или поставьте курсор​​Умножение и деление чисел​текст​ 12).​ оригинал (на английском​Между кавычками должен находиться​«Общая сумма затрат»​ вычисления формулы и​ в двух столбцах​ где располагается текст,​СЖПРОБЕЛЫ​ПРОПИСН​ абсолютной. Для изменения​ выбранные ячейки с​ Чтобы ввести в​ стандартных предусмотренных инструментов.​ здесь 2 раза.​ в Строку формул;​Вычисление процентных значений​, чтобы управлять​12:00 как 12:00 центральноевропейское​ языке) .​

​ пробел. В целом​. Щелкаем по пиктограмме​ текстовое выражение разделены​ указаны постоянные и​ при этом сам​к значению ячейки​делает все буквы​ значений при копировании​ относительными ссылками. То​ формулу ссылку на​Формула предписывает программе Excel​ Не только в​нажмите​Округление числа​

Используйте числовой формат для отображения текста до или после числа в ячейке

​ способом отображения чисел.​ время​Предположим, что нужно создать​ в строке функций​«Вставить функцию»​ пробелом.​ переменные затраты предприятия.​ он не важен.​ А1, мы удалим​ прописными, т.е. преобразует​ относительной ссылки.​ есть в каждой​ ячейку, достаточно щелкнуть​ порядок действий с​ основе, а и​ENTER​

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

​Формула в примере ниже​

1. ​ из него все​ их в верхний​

2. ​Простейшие формулы заполнения таблиц​​ ячейке будет своя​​ по этой ячейке.​​ числами, значениями в​​ в качестве аргумента​
   

3. ​.​​Вычисление наименьшего или наибольшего​​ базовое значение из​​Текущее время показано в​​ из нескольких столбцов​ выражение:​ строки формул.​ действия проделывать не​

4. ​ находится простая формула​​ заменяет 4 символа,​​ лишние пробелы и​ регистр. Так же,​ в Excel:​ формула со своими​

   ​В нашем примере:​ ячейке или группе​ для функции ПРОПНАЧ.​Часто при работе в​ числа в диапазоне​ ячейки, на который​ формате даты/времени ч:мм​ данных для подготовки​
   ​=СЦЕПИТЬ(«Итого»;» «;D2;» «;»рублей»)​​Производится активация​ обязательно. Мы просто​ сложения, которая суммирует​

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

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

​ форматирование чисел с​. Теперь наши значения​«Текстовые»​ второго амперсанда и​ требуется в ту​ к нашему примеру,​

​полезно применять к​​ не являющиеся буквами.​

• ​ в первой графе,​Формула с абсолютной ссылкой​Щелкнули по ячейке В2​​Конструкция формулы включает в​​ разобьем ее на​​ Для решения такого​​Подсчет​ отображаемое в ячейке​​-12 как -12р. дефицит​​ текстом, не затрагивая​ разделены пробелами.​. Далее выделяем наименование​​ кавычек с пробелом,​​ же ячейку, где​​ формула заменит «2010»​​ данным, которые импортируются​Текстовая функция​ щелкаем правой кнопкой​

• ​ ссылается на одну​​ – Excel «обозначил»​ себя: константы, операторы,​ 3 части, так​ рода задач была​Подсчет непустых ячеек​ (40%). Чтобы восстановить​ и 12 как​ формулы, которые могут​При желании можно спрятать​«СЦЕПИТЬ»​​ формульные и текстовые​​ отображается общая сумма​ на «2013».​ в рабочие листы​

Примеры

​ПРОПНАЧ​ мыши. Нажимаем «Вставить».​

​ и ту же​ ее (имя ячейки​​ ссылки, функции, имена​​ как в ней​ создана функция ПОДСТАВИТЬ.​Подсчет количества вхождений значения​ форматов чисел используйте​ 12р. избыток​ использовать эти числа.​​ первый столбец​​и жмем на​ данные сольются. Вы​ затрат добавить после​Заменяет первые пять символов​ Excel из внешних​делает прописной первую​ Или жмем сначала​ ячейку. То есть​ появилось в формуле,​ диапазонов, круглые скобки​ применяется 3 функции.​Одним из примеров применения​​Подсчет количества уникальных значений​​ функцию​

См. также

• ​0.00р. «избыток»;-0.00р. «дефицит»​

• ​ В Excel существует​

• ​«Общая сумма затрат»​

• ​ кнопку​

support.office.com

Примеры часто используемых формул

​ же можете установить​​ формулы поясняющее слово​ текстовой строки, т.е.​ источников. Такие данные​ букву каждого слова,​ комбинацию клавиш: CTRL+ПРОБЕЛ,​ при автозаполнении или​ вокруг ячейки образовался​ содержащие аргументы и​​ функции ПОДСТАВИТЬ является​ среди повторяющихся​текст​Это значение отображается в​ несколько способов для​с исходной формулой,​«OK»​ правильный пробел ещё​«рублей»​ слово «Excel», на​ очень часто содержат​ а все остальные​

​ чтобы выделить весь​ копировании константа остается​ «мелькающий» прямоугольник).​ другие формулы. На​Часть 1:​ наличие дефиса в​Подсчет числа больше или​.​ формате «Денежный». Кроме​ объединения текста и​ чтобы он не​.​

​ это нарушит функцию​

support.office.com

Формула в MS EXCEL отображается как Текстовая строка

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

​ ПРОПНАЧ. Данная функция​ формата в дат​Мы стараемся как​ а если ячейка​

​ продукта, продукт #100​СЦЕПИТЬ​«Текст»​ кавычки и записываем​ либо выделяем и​

• ​ простых примерах. Надеюсь,​ПЕЧСИМВ​В Excel Вы можете​​ во вторую –​​ с помощью клавиши​
• ​ их в следующей​ и ввести равно​​ и бессмысленным, но​​ должна только первые​Преобразование чисел из текстового​ можно оперативнее обеспечивать​ содержит отрицательное значение,​
• ​ и 200 #​​, но убрать элемент​​. Их количество достигает​ текст. После этого​ жмем на функциональную​ что данный урок​
• ​.​​ подсчитать количество знаков,​​ «2». Выделяем первые​

excel2.ru

Пример функции ПОДСТАВИТЬ в формуле Excel для работы с текстом

​ F4.​ последовательности:​ (=). Так же​ это не так.​ буквы в словах​ формата в числовой​ вас актуальными справочными​

Исправляем ошибки в тексте с помощью функции ПОДСТАВИТЬ

​ после него будет​ продукта — не​ вполне можно. Кликаем​255​ закрываем кавычки. Ставим​ клавишу​ пришелся Вам как​Функция​ содержащихся в текстовой​ две ячейки –​Создадим строку «Итого». Найдем​%, ^;​ можно вводить знак​ Чтобы получить правильный​ изменять на большие,​Преобразование единицы измерения​ материалами на вашем​ показан текст «(недостаток)».​ может сортировать должным​

​ левой кнопкой мыши​, но для нашего​ знак амперсанда. Затем,​F2​ раз кстати, и​ПОВТОР​ строке, для этого​ «цепляем» левой кнопкой​ общую стоимость всех​*, /;​ равенства в строку​ результат при работе​ прописные. А в​Преобразование чисел в различные​

​ языке. Эта страница​При объединении чисел и​ образом. Можно отформатировать​ по сектору панели​ примера понадобится всего​ в случае если​. Также можно просто​ Вы получили от​повторяет текстовую строку​

​ воспользуйтесь функцией​ мыши маркер автозаполнения​ товаров. Выделяем числовые​+, -.​ формул. После введения​

1. ​ функции ПОДСТАВИТЬ и​ результате в исходном​ системы счисления​ переведена автоматически, поэтому​ текста в ячейке,​ ячейки, содержащие 15,​
2. ​ координат того столбца,​ три поля. В​
3. ​ нужно внести пробел,​ выделить ячейку, а​ него хотя бы​
4. ​ указанное количество раз.​ДЛСТР​ – тянем вниз.​ значения столбца «Стоимость»​Поменять последовательность можно посредством​ формулы нажать Enter.​ при этом не​ слове где иметься​Текстовый​ ее текст может​ текст и больше​ 100 и 200,​ который следует скрыть.​ первом мы разместим​

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

​ текст, во втором​

​ пробел и закрываем​

​ в строку формул.​ Всего доброго и​ первый аргумент функции,​Текстовые функции​ можно заполнить, например,​ ячейку. Это диапазон​ в первую очередь​ результат вычислений.​ символы отличные от​ 2 большие буквы,​Установите флажок, если ячейка​ грамматические ошибки. Для​ становятся как числовые​ на листе как​ столбец выделяется. Щелкаем​ – ссылку на​ кавычки. Щелкаем по​Сразу после формулы ставим​ успехов в изучении​ а количество повторов​ЛЕВСИМВ​ даты. Если промежутки​ D2:D9​ вычисляет значение выражения​

​В Excel применяются стандартные​

​ букв, нам придется​

​ что искажает результат​ содержит текст (без​ нас важно, чтобы​ значения. Это означает,​ 15 # продукт,​ по выделению правой​ ячейку, в которой​ клавише​ знак амперсанд (​ Excel!​ как второй.​и​ между ними одинаковые​Воспользуемся функцией автозаполнения. Кнопка​ в скобках.​ математические операторы:​ пойти на небольшую​ ее работы:​

​ учета регистра)​

​ эта статья была​

​ что больше не​ продукт #100 и​ кнопкой мыши. Запускается​ содержится формула, и​Enter​&​Автор: Антон Андронов​Текстовая функция​ПРАВСИМВ​ – день, месяц,​ находится на вкладке​​Оператор​ хитрость в Excel.​Дело в том, что​

​Установите флажок, если ячейка​ вам полезна. Просим​ может выполнять любые​ 200 # продукта.​ контекстное меню. Выбираем​ в третьем опять​.​). Далее в кавычках​

​Довольно часто при работе​

​НАЙТИ​возвращают заданное количество​ год. Введем в​ «Главная» в группе​

exceltable.com

Работа в Excel с формулами и таблицами для чайников

​Различают два вида ссылок​Операция​ Сохраняя в оригинале​ текстовая функция ПРОПНАЧ​ содержит текст (с​ вас уделить пару​ математических операций на​

​Использование пользовательского числового формата​ в нем пункт​ разместим текст.​Для записи текста вместе​ записываем слово​ в Excel существует​находит вхождение одной​ символов, начиная с​ первую ячейку «окт.15»,​

Формулы в Excel для чайников

​ инструментов «Редактирование».​ на ячейки: относительные​Пример​ наличие всех символов​ работает по принципу​ учетом регистра)​ секунд и сообщить,​ них.​ для отображения номера​«Скрыть»​Устанавливаем курсор в поле​

​ с функцией, а​«рублей»​

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

​ считается за символ.​ две ячейки и​ клавиш ALT+«=») слаживаются​ ссылки ведут себя​=В4+7​ для второй части​ букву, которые находятся​

​ или временем​ кнопок внизу страницы.​ или функции ОБЪЕДИНЕНИЯ,​ число. Таким образом​ ненужный нам столбец​«Итого»​

​ точно такие же,​ в ячейке после​ который облегчает понимание​

​ фразы относительно начала​Текстовая функция​ «протянем» за маркер​

​ выделенные числа и​ по-разному: относительные изменяются,​- (минус)​ вернет такой результат​ после символов, которые​Объединение двух или более​

​ Для удобства также​

1. ​ текст и TEXTJOIN​ изменение способа отображения​ скрыт, но при​
2. ​. Писать текстовые выражения​ как были описаны​ числа выводимого формулой.​ этих данных. Конечно,​ текста.​ПСТР​
3. ​ вниз.​ отображается результат в​ абсолютные остаются постоянными.​

​Вычитание​ для следующей обработки:​ не соответствуют буквам:​ столбцов с помощью​ приводим ссылку на​

• ​ и амперсанд (&)​
• ​ номера без изменения​
• ​ этом данные в​

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

​возвращает заданное количество​

Как в формуле Excel обозначить постоянную ячейку

​Найдем среднюю цену товаров.​ пустой ячейке.​Все ссылки на ячейки​=А9-100​MICHAELzzzS DELI​ !,?,-,’,*,/, и т.д.​ функции​

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

1. ​ Выделяем столбец с​Сделаем еще один столбец,​ программа считает относительными,​* (звездочка)​
2. ​Часть 2:​ Поэтому если внутри​Повторение знака в ячейке​ языке) .​Примечания:​Выполните следующие действия.​ расположена функция​ проставит их сама.​ в виде ссылки​ что это текст.​ но не во​… и может начинать​ указанной позиции. Пробел​
3. ​ ценами + еще​ где рассчитаем долю​ если пользователем не​Умножение​Вторая часть формулы охватывает​ слова находится символы,​Отображаются только четыре последние​В таблице ниже представлены​ ​Выделите ячейки, формат которых​СЦЕПИТЬ​Потом переходим в поле​

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

​=А3*2​ первую часть и​ которые не являются​ цифры идентификационного номера​ ссылки на статьи​В Excel 2016Excel Mobile и​ требуется изменить.​отображаются корректно.​

​«Текст2»​ которой он расположен.​

​ вывести результат в​ дополнительных элементов является​ указанной позиции. На​Функция​ меню кнопки «Сумма»​ общей стоимости. Для​ С помощью относительных​

​/ (наклонная черта)​ работает с тем,​ буквами работа функции​Вставка в ячейку текущего​ и видеоролики, в​ Excel Online с​На вкладке​

1. ​Читайте также:​. Устанавливаем туда курсор.​ В этом случае,​ ячейку, щелкаем по​ рациональным. Впрочем, в​ рисунке ниже формула​СОВПАД​
2. ​ — выбираем формулу​ этого нужно:​ ссылок можно размножить​Деление​
3. ​ что первая формула​ ПРОПНАЧ будет искажена.​ имени файла Excel,​ которых описано создание​ помощью функции​Главная​

​Функция СЦЕПИТЬ в Экселе​ Нам нужно тут​ алгоритм действий остается​ кнопке​ Экселе имеются способы​

1. ​ начинает просмотр с​позволяет сравнить две​ для автоматического расчета​Разделить стоимость одного товара​ одну и ту​=А7/А8​ возвратила ей в​Однако используя простую формулу​ пути или листа​ формул на основе​
2. ​ОБЪЕДИНЕНИЯ​в группе​Как скрыть столбцы​ указать то значение,​ прежним, только сами​Enter​ поместить формулу и​ четвертого символа, т.е.​
3. ​ текстовые строки в​ среднего значения.​ на стоимость всех​ же формулу на​^ (циркумфлекс)​ результате, а именно​ в комбинации с​Разделение имен при помощи​ данных листа. Примеры,​

​заменена​число​ в Экселе​

• ​ которое выводит формула,​ координаты ячейки в​на клавиатуре.​
• ​ текст в одну​ c буквы «​
• ​ Excel. Если они​Чтобы проверить правильность вставленной​

Как составить таблицу в Excel с формулами

​ товаров и результат​ несколько строк или​Степень​ ту же исходную​ функцией ПОДСТАВИТЬ можно​ мастера текстов​ приведенные в этих​функции СЦЕПИТЬ​щелкните стрелку.​

​Таким образом, можно сказать,​ а значит, следует​

1. ​ кавычки брать не​Как видим, после этого​ ячейку вместе. Давайте​r​ в точности совпадают,​ формулы, дважды щелкните​ умножить на 100.​ столбцов.​=6^2​ строку, но с​ легко устранить данный​Разделение текста по столбцам​
2. ​ статьях, содержат образцы​. Несмотря на​В списке​ что существуют два​ дать ссылку на​ нужно.​ действия, вслед за​ разберемся, как это​«. Но даже в​
3. ​ то возвращается значение​ по ячейке с​ Ссылка на ячейку​Вручную заполним первые графы​= (знак равенства)​ временным текстом «zzz»​ недостаток. Ниже на​ при помощи функций​ данных для начала​ то, что функция​категории​ способа вписать в​
4. ​ ячейку, её содержащую.​Также для вставки текста​ числом, которое выводит​ можно сделать при​ этом случае положение​ ИСТИНА, в противном​ результатом.​ со значением общей​

​ учебной таблицы. У​Равно​ вместо апострофа «’».​ рисунке представлен пример​

exceltable.com

​К началу страницы​

Текстовые функции Excel

ФИО, номера банковских карт, адреса клиентов или сотрудников, комментарии и многое другое –все это является строками, с которыми многие сталкиваются, работая с приложением Excel. Поэтому полезно уметь обрабатывать информацию подобного типа. В данной статье будут рассмотрены текстовые функции в Excel, но не все, а те, которые, по мнению office-menu.ru, самые полезные и интересные:

Список всех текстовых функций Вы можете найти на вкладке «Формулы» => выпадающий список «Текстовые»:

Функция ЛЕВСИМВ

Возвращает подстроку из текста в порядке слева направо в заданном количестве символов.

Синтаксис: =ЛЕВСИМВ(текст; [количество_знаков])

• текст – строка либо ссылка на ячейку, содержащую текст, из которого необходимо вернуть подстроку;
• количество_знаков – необязательный аргумент. Целое число, указывающее, какое количество символов необходимо вернуть из текста. По умолчанию принимает значение 1.

Пример использования:

Формула: =ЛЕВСИМВ(«Произвольный текст»;8) – возвращенное значение «Произвол».

Функция ПРАВСИМВ

Данная функция аналогична функции «ЛЕВСИМВ», за исключением того, что знаки возвращаются с конца строки.

Пример использования:

Формула: =ПРАВСИМВ(«произвольный текст»;5) – возвращенное значение «текст».

Функция ДЛСТР

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

Синтаксис: =ДЛСТР(текст)

Пример использования:

Функция НАЙТИ

Возвращает число, являющееся вхождением первого символа подстроки, искомого текста. Если текст не найден, то возвращается ошибка «#ЗНАЧ!».

Синтаксис: =НАЙТИ(искомый_текст; текст_для_поиска; [нач_позиция])

• искомый_текст – строка, которую необходимо найти;
• текст_для_поиска – текст, в котором осуществляется поиск первого аргумента;
• нач_позиция – необязательный элемент. Принимает целое число, которое указывает, с какого символа текст_для_поиска необходимо начинать просмотр. По умолчанию принимает значение 1.

Пример использования:

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

Функция ЗАМЕНИТЬ

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

Синтаксис: ЗАМЕНИТЬ(старый_текст; начальная_позиция; количество_знаков; новый_текст)

• старый_текст – строка либо ссылка на ячейку, содержащую текст;
• начальная_позиция – порядковый номер символа слева направо, с которого нужно производить замену;
• количество_знаков – количество символов, начиная с начальная_позиция включительно, которые необходимо заменить новым текстом;
• новый_текст – строка, которая подменяет часть старого текста, заданного аргументами начальная_позиция и количество_знаков.

Пример использования:

Здесь в строке, содержащейся в ячейке A1, подменяется слово «старый», которое начинается с 19-го символа и имеет длину 6 символов, на слово «новый».

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

• Аргумент «начальная_позиция» подменим функцией «НАЙТИ»;
• В место аргумент «количество_знаков» вложим функцию «ДЛСТР».

В результате получим формулу: =ЗАМЕНИТЬ(A1;НАЙТИ(«старый»;A1);ДЛСТР(«старый»);»новый»)

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

Функция ПОДСТАВИТЬ

Данная функция заменяет в тексте вхождения указанной подстроки на новый текст, чем схожа с функцией «ЗАМЕНИТЬ», но между ними имеется принципиальное отличие. Если функция «ЗАМЕНИТЬ» меняет текст, указанный посимвольно вручную, то функция «ПОДСТАВИТЬ» автоматически находит вхождения указанной строки и меняет их.

Синтаксис: ПОДСТАВИТЬ(текст; старый_текст; новый_текст; [номер_вхождения])

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

Пример использования:

Строка в ячейке A1 содержит текст, в котором имеются 2 подстроки «старый». Нам необходимо подставить на место первого вхождения строку «новый». В результате часть текста «…старый-старый…», заменяется на «…новый-старый…».

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

Функция ПСТР

ПСТР возвращает из указанной строки часть текста в заданном количестве символов, начиная с указанного символа.

Синтаксис: ПСТР(текст; начальная_позиция; количество_знаков)

• текст – строка или ссылка на ячейку, содержащую текст;
• начальная_позиция – порядковый номер символа, начиная с которого необходимо вернуть строку;
• количество_знаков – натуральное целое число, указывающее количество символов, которое необходимо вернуть, начиная с позиции начальная_позиция.

Пример использования:

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

Аргумент количество_знаков может превышать допустимо возможную длину возвращаемых символов. Т.е. если в рассмотренном примере вместо количество_знаков = 12, было бы указано значение 15, то результат не изменился, и функция так же вернула строку «функции ПСТР».

Для удобства использования данной функции ее аргументы можно подменить функциями «НАЙТИ» и «ДЛСТР», как это было сделано в примере с функцией «ЗАМЕНИТЬ».

Функция СЖПРОБЕЛЫ

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

Синтаксис: =СЖПРОБЕЛЫ(текст)

Пример использования:

=СЖПРОБЕЛЫ( » Текст с лишними пробелами между словами и по краям « )

Результатом выполнения функции будет строка: «Текст с лишними пробелами между словами и по краям» .

Функция СЦЕПИТЬ

С помощью функции «СЦЕПИТЬ» можно объединить несколько строк между собой. Максимальное количество строк для объединения – 255.

Синтаксис: =СЦЕПИТЬ(текст1; [текст2]; …)

Функция должна содержать не менее одного аргумента

Пример использования:

Функция возвратит строку: «Слово1 Слово2».

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

Вместо использования данной функции можно применять знак амперсанда «&». Он так же объединяет строки. Например: «=»Слово1″&» «&«Слово2″».

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

Познакомимся с вариантами преобразования формулы Excel в текст (в результате получается не значение ячейки, а формульное выражение в текстовом виде, например, «=A1+A2», «=СЕГОДНЯ()» и т.д.).

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

Преобразование формулы в текст в Excel

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

• Поменять формат ячейки на текстовый, а затем произвести вычисление формулы; При этом для каждой ячейки нужно будет вручную производить изменение.
• Добавить апостроф (символ «‘») перед знаком равно (символ «=») в формульном выражении. В данном варианте подставить апостроф можно как вручную, так и через замену («=» на «‘=» с помощью инструмента «Найти и заменить»).

Теперь перейдем к более общему случаю и рассмотрим 2 основных варианта перевода формулы в текст (т.е. получить текстовую запись):

• Функция Ф.ТЕКСТ (доступна начиная с версии Excel 2013);
• Пользовательская функция (UDF).

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

Функция Ф.ТЕКСТ в Excel

Начиная с версии Excel 2013 для применения доступна функция Ф.ТЕКСТ (FORMULATEXT в английской версии):

Ф.ТЕКСТ(ссылка)
Возвращает формулу в виде строки.

• Ссылка(обязательный аргумент) — ссылка на ячейку или диапазон ячеек.

Перейдем к примерам. Применим Ф.ТЕКСТ, в качестве аргумента укажем ссылку на произвольную ячейку, где содержится какое-либо формульное выражение:

При этом в зависимости от выбранного у вас параметра отображения стиля ссылок (A1 или R1C1) формула автоматически будет подстраиваться под формат записи:

При работе с данной функцией есть несколько важных особенностей, на которые необходимо обратить внимание:

• Аргумент «Ссылка» может ссылаться на другие листы и книги;
• Если аргумент «Ссылка» не содержит формульное выражение или содержит ссылку на закрытую книгу, то в результате будет возвращено значение ошибки.

Пользовательская функция (UDF)

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

Перейдем в редактор Visual Basic (сочетание клавиш Alt + F11), вставляем новый модуль и добавляем следующий код:

Функция ТЕКСТ() в MS EXCEL

Функция ТЕКСТ( ) , английская версия TEXT(), преобразует число в текст и позволяет задать формат отображения с помощью специальных строк форматирования, например, формула =ТЕКСТ(100;»0,00 р.») вернет текстовую строку 100,00 р., а формула =ТЕКСТ(1;»#%») вернет текст (не число!) 100%. Эта функция полезна, если числа требуется отобразить в более удобном формате или если требуется объединить числа с текстом или символами.

Синтаксис

ТЕКСТ(значение; формат)

• Значение. Численное значение или ссылка на ячейку, содержащую численное значение.
• Формат. Текстовая строка определенного вида (например, «Д.М.ГГГГ» для вывода числа в формате даты или «# ##0,00» для вывода в числовом формате с разделителями разрядов и двумя знаками после запятой. Правила форматирования см. ниже.

Форматов для отображения чисел в MS EXCEL много (например, см. здесь), также имеются форматы для отображения дат (например, см. здесь). Также приведено много форматов в статье к функции ТЕКСТ() на сайте Microsoft .

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

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

Вставка текста в ячейку с формулой в Microsoft Excel

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

Процедура вставки текста около формулы

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

Способ 1: использование амперсанда

Самый простой способ решить данную задачу – это применить символ амперсанда (&). Данный знак производит логическое отделение данных, которые содержит формула, от текстового выражения. Давайте посмотрим, как можно применить указанный способ на практике.

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

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

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

Как видим, после этого действия, вслед за числом, которое выводит формула, находится пояснительная надпись «рублей». Но у этого варианта есть один видимый недостаток: число и текстовое пояснение слились воедино без пробела.

При этом, если мы попытаемся поставить пробел вручную, то это ничего не даст. Как только будет нажата кнопка Enter, результат снова «склеится».

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

При написании текста перед формулой придерживаемся следующего синтаксиса. Сразу после знака «=» открываем кавычки и записываем текст. После этого закрываем кавычки. Ставим знак амперсанда. Затем, в случае если нужно внести пробел, открываем кавычки, ставим пробел и закрываем кавычки. Щелкаем по клавише Enter.

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

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

Способ 2: применение функции СЦЕПИТЬ

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

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

Посмотрим, как работает данная функция на практике. Для примера возьмем все ту же таблицу, только добавим в неё ещё один столбец «Общая сумма затрат» с пустой ячейкой.

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

Производится активация Мастера функций. Перемещаемся в категорию «Текстовые». Далее выделяем наименование «СЦЕПИТЬ» и жмем на кнопку «OK».

Запускается окошко аргументов оператора СЦЕПИТЬ. Данное окно состоит из полей под наименованием «Текст». Их количество достигает 255, но для нашего примера понадобится всего три поля. В первом мы разместим текст, во втором – ссылку на ячейку, в которой содержится формула, и в третьем опять разместим текст.

Устанавливаем курсор в поле «Текст1». Вписываем туда слово «Итого». Писать текстовые выражения можно без кавычек, так как программа проставит их сама.

Потом переходим в поле «Текст2». Устанавливаем туда курсор. Нам нужно тут указать то значение, которое выводит формула, а значит, следует дать ссылку на ячейку, её содержащую. Это можно сделать, просто вписав адрес вручную, но лучше установить курсор в поле и кликнуть по ячейке, содержащей формулу на листе. Адрес отобразится в окошке аргументов автоматически.

В поле «Текст3» вписываем слово «рублей».

После этого щелкаем по кнопке «OK».

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

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

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

=СЦЕПИТЬ(«Итого»;» «;D2;» «;»рублей»)

Щелкаем по клавише ENTER. Теперь наши значения разделены пробелами.

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

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

ТЕКСТ (функция ТЕКСТ)

С помощью функции ТЕКСТ можно изменить представление числа, применив к нему форматирование с кодами форматов. Это полезно в ситуации, когда нужно отобразить числа в удобочитаемом виде либо объединить их с текстом или символами.

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

Аргументы функции ТЕКСТ описаны ниже.

Числовое значение, которое нужно преобразовать в текст.

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

Общие сведения

Самая простая функция ТЕКСТ означает следующее:

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

Ниже приведены популярные примеры, которые вы можете скопировать прямо в Excel, чтобы поэкспериментировать самостоятельно. Обратите внимание: коды форматов заключены в кавычки.

Денежный формат с разделителем групп разрядов и двумя разрядами дробной части, например: 1 234,57 ₽. Обратите внимание: Excel округляет значение до двух разрядов дробной части.

Сегодняшняя дата в формате ДД/ММ/ГГ, например: 14.03.12

Сегодняшний день недели, например: понедельник

Текущее время, например: 13:29

Процентный формат, например: 28,5 %

Дробный формат, например: 4 1/3

Дробный формат, например: 1/3 Обратите внимание: функция СЖПРОБЕЛЫ используется для удаления начального пробела перед дробной частью.

Экспоненциальное представление, например: 1,22E+07

Скачивание образцов

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

Другие доступные коды форматов

Просмотреть другие доступные коды форматов можно в диалоговом окне Формат ячеек.

Нажмите клавиши CTRL+1 (на компьютере Mac — +1), чтобы открыть диалоговое окно Формат ячеек.

На вкладке Число выберите нужный формат.

Выберите пункт (все форматы).

Нужный код формата будет показан в поле Тип. В этом случае выделите всё содержимое поля Тип, кроме точки с запятой (;) и символа @. В примере ниже выделен и скопирован только код ДД.ММ.ГГГГ.

Нажмите клавиши CTRL+C, чтобы скопировать код формата, а затем — кнопку Отмена, чтобы закрыть диалоговое окно Формат ячеек.

Теперь осталось нажать клавиши CTRL+V, чтобы вставить код формата в функцию ТЕКСТ. Пример: =ТЕКСТ(B2;»ДД.ММ.ГГГГ«). Обязательно заключите скопированный код формата в кавычки («код формата»), иначе в Excel появится сообщение об ошибке.

«Ячейки» > «Число» > «Другое» для получения строк формата.» />

Коды форматов по категориям

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

• Выбор числового формата
• Нули в начале
• Разделитель групп разрядов.
• Числовые, денежные и финансовые форматы
• Даты
• Значения времени
• Проценты
• Дроби
• Экспоненциальное представление
• Дополнительные форматы

Почему программа Excel удаляет нули в начале?

Excel воспринимает последовательность цифр, введенную в ячейку, как число, а не как цифровой код, например артикул или номер SKU. Чтобы сохранить нули в начале последовательностей цифр, перед вставкой или вводом значений примените к соответствующему диапазону ячеек текстовый формат. Выделите столбец или диапазон, в который нужно поместить значения, нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат ячеек, и выберите на вкладке Число пункт Текстовый. Теперь программа Excel не будет удалять нули в начале.

Если вы уже ввели данные и Excel удалил начальные нули, вы можете снова добавить их с помощью функции ТЕКСТ. Создайте ссылку на верхнюю ячейку со значениями и используйте формат =ТЕКСТ(значение;»00000″), где число нулей представляет нужное количество символов. Затем скопируйте функцию и примените ее к остальной части диапазона.

Если по какой-либо причине потребуется преобразовать текстовые значения обратно в числа, можно умножить их на 1 (например: =D4*1) или воспользоваться двойным унарным оператором (—), например: =—D4.

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

Пробел после заполнителя цифры задает деление числа на 1000. Например, если используется код формата «# ###,0 «, число 12200000 отображается в Excel как 12 200,0.

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

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

Ниже показаны примеры стандартных числовых (только с разделителем групп разрядов и десятичными знаками), денежных и финансовых форматов. В денежном формате можно добавить нужное обозначение денежной единицы, и значения будут выровнены по нему. В финансовом формате символ рубля располагается в ячейке справа от значения (если выбрать обозначение доллара США, то эти символы будут выровнены по левому краю ячеек, а значения — по правому). Обратите внимание на разницу между кодами денежных и финансовых форматов: в финансовых форматах для отделения символа денежной единицы от значения используется звездочка (*).

Чтобы получить код формата для определенной денежной единицы, сначала нажмите клавиши CTRL+1 (на компьютере Mac — +1) и выберите нужный формат, а затем в раскрывающемся списке Обозначение выберите символ.

После этого в разделе Числовые форматы слева выберите пункт (все форматы) и скопируйте код формата вместе с обозначением денежной единицы.

Примечание: Функция ТЕКСТ не поддерживает форматирование с помощью цвета. Если скопировать в диалоговом окне «Формат ячеек» код формата, в котором используется цвет, например «# ##0,00 ₽;[Красный]# ##0,00 ₽», то функция ТЕКСТ воспримет его, но цвет отображаться не будет.

Способ отображения дат можно изменять, используя сочетания символов «Д» (для дня), «М» (для месяца) и «Г» (для года).

В функции ТЕКСТ коды форматов используются без учета регистра, поэтому допустимы символы «М» и «м», «Д» и «д», «Г» и «г».

Если вы предоставляете общий доступ к файлам и отчетам Excel пользователям из разных стран, скорее всего, потребуется, чтобы они были на разных языках. Минда Триси (Mynda Treacy), Excel MVP, предлагает отличное решение этой задачи в своей статье Отображение дат Excel на разных языках (на английском). В ней также есть пример книги, который вы можете скачать.

Способ отображения времени можно изменить с помощью сочетаний символов «Ч» (для часов), «М» (для минут) и «С» (для секунд). Кроме того, для представления времени в 12-часовом формате можно использовать символы «AM/PM».

Если не указывать символы «AM/PM», время будет отображаться в 24-часовом формате.

В функции ТЕКСТ коды форматов используются без учета регистра, поэтому допустимы символы «Ч» и «ч», «М» и «м», «С» и «с», «AM/PM» и «am/pm».

Для отображения десятичных значений можно использовать процентные (%) форматы.

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

Экспоненциальное представление — это способ отображения значения в виде десятичного числа от 1 до 10, умноженного на 10 в некоторой степени. Этот формат часто используется для краткого отображения больших чисел.

В Excel доступны четыре дополнительных формата:

«Почтовый индекс» («00000»);

«Индекс + 4» («00000-0000»);

Типичный сценарий

Функция ТЕКСТ редко используется сама по себе, а чаще применяется в сочетании с чем-то еще. Предположим, что вы хотите объединить текст и числовое значение, например, чтобы получить строку «Отчет напечатан 14.03.12» или «Еженедельный доход: 66 348,72 ₽». Такие строки можно ввести вручную, но суть в том, что Excel может сделать это за вас. К сожалению, при объединении текста и форматированных чисел, например дат, значений времени, денежных сумм и т. п., Excel убирает форматирование, так как неизвестно, в каком виде нужно их отобразить. Здесь пригодится функция ТЕКСТ, ведь с ее помощью можно принудительно отформатировать числа, задав нужный код формата, например «ДД.ММ.ГГГГ» для дат.

В примере ниже показано, что происходит, если попытаться объединить текст и число, не применяя функцию ТЕКСТ. Мы используем амперсанд (&) для сцепления текстовой строки, пробела (» «) и значения: =A2&» «&B2.

Вы видите, что значение даты, взятое из ячейки B2, не отформатировано. В следующем примере показано, как применить нужное форматирование с помощью функции ТЕКСТ.

Вот обновленная формула:

ячейка C2:=A2&» «&ТЕКСТ(B2;»дд.мм.гггг») — формат даты.

Вопросы и ответы

К сожалению, это невозможно сделать с помощью функции ТЕКСТ. Для этого нужно использовать код Visual Basic для приложений (VBA). В следующей статье описано, как это сделать: Как преобразовать числовое значение в слова в Excel

Да, вы можете использовать функции ПРОПИСН, СТРОЧН и ПРОПНАЧ. Например, формула =ПРОПИСН(«привет») возвращает результат «ПРИВЕТ».

Да, но для этого необходимо выполнить несколько действий. Сначала выделите нужные ячейки и нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат ячеек. Затем на вкладке Выравнивание в разделе «Отображение» установите флажок Переносить по словам. После этого добавьте в функцию ТЕКСТ код ASCII СИМВОЛ(10) там, где нужен разрыв строки. Вам может потребоваться настроить ширину столбца, чтобы добиться нужного выравнивания.

В этом примере использована формула =»Сегодня: «&СИМВОЛ(10)&ТЕКСТ(СЕГОДНЯ();»ДД.ММ.ГГ»).

Это экспоненциальное представление числа. Excel автоматически приводит к такому виду числа длиной более 12 цифр, если к ячейкам применен формат Общий, и числа длиннее 15 цифр, если выбран формат Числовой. Если вы вводите длинные цифровые строки, но не хотите, чтобы они отображались в таком виде, то сначала примените к соответствующим ячейкам формат Текстовый.