Excel формула текст

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

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

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

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

Использование конкатенации

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

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

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

Это простое решение, однако имеет много минусов.

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

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

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

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

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

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

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

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

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

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

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

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

Плюсы и минусы методов

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

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

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

Синтаксис

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

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

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

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

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

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

Функция ТЕКСТ предназначена для преобразования числового значения в текстовое с присвоением формата. Например, если ячейке с числовым значением 100 присвоить денежный формат 100,00 ₽, то для Excel данное значение остается все еще числом. Но если применить функцию =ТЕКСТ(100;»0,00 [$₽-419]»), тогда для Excel это будет уже текстовая строка со значением «100,00 ₽». И с ней уже нельзя выполнять математических операций.

Читать еще:  Excel объединить текст в ячейках

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

Примеры использования функции ТЕКСТ в Excel

Для функции ТЕКСТ следует указать 2 обязательных для заполнения аргументы:

  1. Значение – числовое значение, ссылка на числовое значение, формула, результат которой число.
  2. Формат – числовой формат из списка числовые форматы (CTRL+1).

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

Задача: Необходимо в столбе F сформировать номенклатуру ведомости с наименованием, ценой и валютой. Цены должны быть разбиты на разряды по тысячам для лучшей читабельности (например, 28 000).

Воспользуемся функцией ТЕКСТ. Щелкнем в ячейку G3 и нажмем мастер функций нажав на кнопку fx или комбинацию клавиш SHIFT+F3. В разделе категория выберем текстовые и найдем необходимую функцию.

В окне аргументов зададим следующие значения:

Нажмем Ок и получим результат:

Все сумы приобрели денежный формат и стали удобочитаемыми в тексте номенклатуры вместе с наименованием товара. Стоит обратить внимание на то что изменение на денежный формат чисел в столбце E (Сумма) не позволило бы решить данную задачу так как Excel все равно воспринимает значения в денежном формате ячеек как числа:

Вот зачем нужна функция ТЕКСТ и ее весьма сложно заменить другими решениями.

Пример как сделать два значения в одной ячейке Excel

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

Первоначальная таблица с данными представлена ниже:

Задача: необходимо изменить внешний вид плана закупок на более информативный удобный для визуального анализа.

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

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

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

Благодаря такой формуле с функцией ТЕКСТ мы в одной и той же ячейке одновременно вводим сразу 2 значения: суммы в рублях и доли в процентах. Да еще и в двух соответствующих форматах данных.

Определение дня недели по дате в Excel

Торговому представителю необходимо посмотреть какие товары в какие дни недели он отвозил. Таблица перевозок представлена ниже:

Задача: необходимо дату преобразовать в день недели. Воспользуемся функцией ТЕКСТ.

Поставим курсор в ячейку D2. Введем знак равенства «=», а после него название функции ТЕКСТ. Далее заполняем аргументы:

В результате в столбце D отобразится соответствующий день недели для каждой даты.

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

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

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

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

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

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

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

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

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

Читать еще:  Преобразовать столбец в строку в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функция ПСТР

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

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

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

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

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

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

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

Читать еще:  Excel показать все скрытые строки

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

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

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

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

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

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

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

С помощью функции «СЦЕПИТЬ» можно объединить несколько строк между собой. Максимальное количество строк для объединения – 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), вставляем новый модуль и добавляем следующий код:

Похожие статьи

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