Функция ТЕКСТ для преобразования числа в особый формат Excel
Функция ТЕКСТ предназначена для преобразования числового значения в текстовое с присвоением формата. Например, если ячейке с числовым значением присвоить денежный формат ,00 ₽, то для Excel данное значение остается все еще числом. Но если применить функцию =ТЕКСТ(;"0,00\ [$₽]"), тогда для Excel это будет уже текстовая строка со значением «,00 ₽». И с ней уже нельзя выполнять математических операций.
Данная функция приносит пользу в тех случаях, когда необходимо показать числа в определенном формате или комбинировать числа и текст.
Примеры использования функции ТЕКСТ в Excel
Для функции ТЕКСТ следует указать 2 обязательных для заполнения аргументы:
- Значение – числовое значение, ссылка на числовое значение, формула, результат которой число.
- Формат – числовой формат из списка числовые форматы (CTRL+1).
Рассмотрим на примере работу функции в EXCEL. На складе имеется перечень товаров в количественном и суммовом выражении, таблица представлена ниже.
Задача: Необходимо в столбе F сформировать номенклатуру ведомости с наименованием, ценой и валютой. Цены должны быть разбиты на разряды по тысячам для лучшей читабельности (например, 28 ).
Воспользуемся функцией ТЕКСТ. Щелкнем в ячейку G3 и нажмем мастер функций нажав на кнопку fx или комбинацию клавиш SHIFT+F3. В разделе категория выберем текстовые и найдем необходимую функцию.
В окне аргументов зададим следующие значения:
Нажмем Ок и получим результат:
Все сумы приобрели денежный формат и стали удобочитаемыми в тексте номенклатуры вместе с наименованием товара. Стоит обратить внимание на то что изменение на денежный формат чисел в столбце E (Сумма) не позволило бы решить данную задачу так как Excel все равно воспринимает значения в денежном формате ячеек как числа:
Вот зачем нужна функция ТЕКСТ и ее весьма сложно заменить другими решениями.
Пример как сделать два значения в одной ячейке Excel
Экономисту организации было поручено в плане на будущий год преобразовать формат сумм расходов в тысячи рублей. Кроме того, необходимо проставить пометки долей в процентах от общей сумы.
Первоначальная таблица с данными представлена ниже:
Задача: необходимо изменить внешний вид плана закупок на более информативный удобный для визуального анализа.
Перейдите курсором клавиатуры в ячейку D2 и введите следующую на первый взгляд сложную формулу:
Заполните этой формулой весь столбец скопировав ее в каждую соответственную ячейку.
В результате в я столбце D (план закупок) получатся текстовые значение с наглядным видом сумм и в скобках указаны доли (какая часть плана припадает на закупку одной позиции) для каждой суммы.
Благодаря такой формуле с функцией ТЕКСТ мы в одной и той же ячейке одновременно вводим сразу 2 значения: суммы в рублях и доли в процентах. Да еще и в двух соответствующих форматах данных.
Определение дня недели по дате в Excel
Торговому представителю необходимо посмотреть какие товары в какие дни недели он отвозил. Таблица перевозок представлена ниже:
Задача: необходимо дату преобразовать в день недели. Воспользуемся функцией ТЕКСТ.
Поставим курсор в ячейку D2. Введем знак равенства «=», а после него название функции ТЕКСТ. Далее заполняем аргументы:
В результате в столбце D отобразится соответствующий день недели для каждой даты.
Читайте также: Как перевести сумму или число прописью в Excel.
Таким образом, рассмотрев функцию ТЕКСТ можно сделать вывод, о том, что она помогает изменить представление числа, применив к нему форматирование с кодами форматов. Это полезно в ситуации, когда нужно отобразить числа в удобочитаемом виде либо объединить их с текстом или символами.
Как отобразить текст и число в одной ячейке
Для того чтобы в одной ячейке совместить как текст так и значение можно использовать следующие способы:
- Конкатенация;
- Функция СЦЕПИТЬ;
- Функция ТЕКСТ;
- Пользовательский формат.
Разберем эти способы и рассмотрим плюсы и минусы каждого из них.
Использование конкатенации
Один из самых простых способов реализовать сочетание текста и значения - использовать конкатенацию (символ &).
Допустим ячейка A1 содержит итоговое значение ,45, тогда в любой другой ячейке можно записать формулу
="Итого: "&A1
В итоге результатом будет следующее содержание ячейки Итого: ,45.
Это простое решение, однако имеет много минусов.
- Результатом формулы будет текстовое значение, которое нельзя будет использовать при дальнейших вычислениях.
- Значение ячейки A1 будет выводится в общем формате, без возможности всякого форматирования. В следствие чего этот метод не всегда применим.
Применение функции СЦЕПИТЬ
Аналогичное простое решение, но с теми же недостатками - использование функции СЦЕПИТЬ. Применяется она так: =СЦЕПИТЬ("Итого: ";A1). Результаты ее использования аналогичные:
Применение функции ТЕКСТ
Функция ТЕКСТ позволяет не только объединить текст и значение, но еще и отформатировать значение в нужном формате. Если мы применим следующую формулу =ТЕКСТ(A1;"""Итого: ""##0"), то мы получим такой результат Итого: .
В качестве второго аргумента функция ТЕКСТ принимает строку с числовым форматом. Более подробно о числовых форматах вы можете прочитать в статье Применение пользовательских форматов.
Единственный минус этого способа в том, что полученные значения также являются текстовыми и с ними нельзя проводить дальнейшие вычисления.
Использование пользовательского формата
Не такой простой способ как предыдущие, но наиболее функциональный. Заключается в применении к итоговой ячейки пользовательского числового формата. Чтобы добавить текст "Итого" к ячейке A1 необходимы следующие действия:
- Выберите ячейку A1.
- Откройте диалоговое окно Формат ячейки.
- В поле Тип укажите нужный формат. В нашем случае "Итого: "# ##0.
В результате ячейка A1 будет содержать Итого: .
Большой плюс данного способа заключается в том, что вы можете использовать в дальнейших вычислениях ячейку A1 так же как и число, но при этом отображаться она будет в нужном вам виде.
Плюсы и минусы методов
В таблице далее сведены плюсы и минусы. В зависимости от ситуации можно пользоваться тем или иным способом обращая на особенности каждого.
Функция ТЕКСТ() в EXCEL
history 28 мая г.
Функция ТЕКСТ( ) , английская версия TEXT() , преобразует число в текст и позволяет задать формат отображения с помощью специальных строк форматирования, например, формула =ТЕКСТ(;"0,00 р.") вернет текстовую строку ,00 р., а формула =ТЕКСТ(1;"#%") вернет текст (не число!) %. Эта функция полезна, если числа требуется отобразить в более удобном формате или если требуется объединить числа с текстом или символами.
Синтаксис
ТЕКСТ(значение; формат)
- Значение. Численное значение или ссылка на ячейку, содержащую численное значение.
- Формат. Текстовая строка определенного вида (например, "profexcel.ru" для вывода числа в формате даты или "# ##0,00" для вывода в числовом формате с разделителями разрядов и двумя знаками после запятой. Правила форматирования см. ниже.
Форматов для отображения чисел в MS EXCEL много (например, см. здесь ), также имеются форматы для отображения дат (например, см. здесь ). Также приведено много форматов ]]> в статье к функции ТЕКСТ() на сайте Microsoft ]]> .
Функция ТЕКСТ() преобразует число в форматированный текст и результат больше не может быть использован в вычислениях в качестве числа. Чтобы отформатировать число, но при этом оставить его числом (с которым можно выполнять арифметические действия), щелкните ячейку правой кнопкой мыши, выберите команду Формат ячеек и в диалоговом окне Формат ячеек на вкладке Число настройте нужные параметры форматирования (см. здесь ).
Одной из самых полезных свойств функции ТЕКСТ() является возможность отображения в текстовой строке чисел и дат в нужном формате (см. подробнее об отображении чисел , дат и времени ). В файле примера приведен наглядный пример: с форматированием и без форматирования.
В файле примера приведено также множество других форматов.
© Copyright - profexcel.ru All Rights Reserved
Функция ТЕКСТ
С помощью функции ТЕКСТ можно изменить представление числа, применив к нему форматирование с кодами форматов. Это полезно в ситуации, когда нужно отобразить числа в удобочитаемом виде либо объединить их с текстом или символами.
Примечание: Функция ТЕКСТ преобразует числа в текст, что может затруднить их использование в дальнейших вычислениях. Рекомендуем сохранить исходное значение в одной ячейке, а функцию ТЕКСТ использовать в другой. Затем, если потребуется создать другие формулы, всегда ссылайтесь на исходное значение, а не на результат функции ТЕКСТ.
Синтаксис
ТЕКСТ(значение; формат)
Аргументы функции ТЕКСТ описаны ниже.
Имя аргумента | Описание |
значение | Числовое значение, которое нужно преобразовать в текст. |
формат | Текстовая строка, определяющая формат, который требуется применить к указанному значению. |
Общие сведения
Самая простая функция ТЕКСТ означает следующее:
=ТЕКСТ(значение, которое нужно отформатировать; "код формата, который требуется применить")
Ниже приведены популярные примеры, которые вы можете скопировать прямо в Excel, чтобы поэкспериментировать самостоятельно. Обратите внимание: коды форматов заключены в кавычки.
Формула | Описание |
---|---|
=ТЕКСТ(,;"# ##0,00 ₽") | Денежный формат с разделителем групп разрядов и двумя разрядами дробной части, например: 1 ,57 ₽. Обратите внимание: Excel округляет значение до двух разрядов дробной части. |
=ТЕКСТ(СЕГОДНЯ();"profexcel.ru") | Сегодняшняя дата в формате ДД/ММ/ГГ, например: |
=ТЕКСТ(СЕГОДНЯ();"ДДДД") | Сегодняшний день недели, например: понедельник |
=ТЕКСТ(ТДАТА();"ЧЧ:ММ") | Текущее время, например: |
=ТЕКСТ(0,;"0,0 %") | Процентный формат, например: 28,5 % |
=ТЕКСТ(4,34; "# ?/?") | Дробный формат, например: 4 1/3 |
=СЖПРОБЕЛЫ(ТЕКСТ(0,34;"# ?/?")) | Дробный формат, например: 1/3 Обратите внимание: функция СЖПРОБЕЛЫ используется для удаления начального пробела перед дробной частью. |
=ТЕКСТ(;"0,00E+00") | Экспоненциальное представление, например: 1,22E+07 |
=ТЕКСТ(;"[<=]###-####;(###) ###-####") | Дополнительный формат (номер телефона), например: () |
=ТЕКСТ(;"") | Добавление нулей в начале, например: |
=ТЕКСТ(;"##0° 00' 00''") | Пользовательский формат (широта или долгота), например: 12° 34' 56'' |
Примечание: Функцию ТЕКСТ можно использовать для изменения форматирования, но это не единственный способ. Чтобы изменить форматирование без формулы, нажмите клавиши CTRL+1 (на компьютере Mac — +1), а затем в диалоговом окне Формат ячеек на вкладке Число выберите нужный формат.
Скачивание образцов
Предлагаем скачать книгу, в которой содержатся все примеры применения функции ТЕКСТ из этой статьи и несколько других. Вы можете воспользоваться ими или создать собственные коды форматов для функции ТЕКСТ.
Скачать примеры применения функции ТЕКСТ
Другие доступные коды форматов
Просмотреть другие доступные коды форматов можно в диалоговом окне Формат ячеек.
Нажмите клавиши CTRL+1 (на компьютере Mac —
+1), чтобы открыть диалоговое окно Формат ячеек.
На вкладке Число выберите нужный формат.
Выберите пункт (все форматы).
Нужный код формата будет показан в поле Тип. В этом случае выделите всё содержимое поля Тип, кроме точки с запятой (;) и символа @. В примере ниже выделен и скопирован только код profexcel.ru
Нажмите клавиши CTRL+C, чтобы скопировать код формата, а затем — кнопку Отмена, чтобы закрыть диалоговое окно Формат ячеек.
Теперь осталось нажать клавиши CTRL+V, чтобы вставить код формата в функцию ТЕКСТ. Пример: =ТЕКСТ(B2;"profexcel.ru"). Обязательно заключите скопированный код формата в кавычки ("код формата"), иначе в Excel появится сообщение об ошибке.
Коды форматов по категориям
В примерах ниже показано, как применить различные числовые форматы к значениям следующим способом: открыть диалоговое окно Формат ячеек, выбрать пункт (все форматы) и скопировать нужный код формата в формулу с функцией ТЕКСТ.
- Выбор числового формата
- Нули в начале
- Разделитель групп разрядов.
- Числовые, денежные и финансовые форматы
- Даты
- Значения времени
- Проценты
- Дроби
- Экспоненциальное представление
- Дополнительные форматы
Почему программа Excel удаляет нули в начале?
Excel воспринимает последовательность цифр, введенную в ячейку, как число, а не как цифровой код, например артикул или номер SKU. Чтобы сохранить нули в начале последовательностей цифр, перед вставкой или вводом значений примените к соответствующему диапазону ячеек текстовый формат. Выделите столбец или диапазон, в который нужно поместить значения, нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат ячеек, и выберите на вкладке Число пункт Текстовый. Теперь программа Excel не будет удалять нули в начале.
Если вы уже ввели данные и Excel удалил начальные нули, вы можете снова добавить их с помощью функции ТЕКСТ. Создайте ссылку на верхнюю ячейку со значениями и используйте формат =ТЕКСТ(значение;""), где число нулей представляет нужное количество символов. Затем скопируйте функцию и примените ее к остальной части диапазона.
Если по какой-либо причине потребуется преобразовать текстовые значения обратно в числа, можно умножить их на 1 (например: =D4*1) или воспользоваться двойным унарным оператором (--), например: =--D4.
В Excel группы разрядов разделяются пробелом, если код формата содержит пробел, окруженный знаками номера (#) или нулями. Например, если используется код формата "# ###", число отображается как 12
Пробел после заполнителя цифры задает деление числа на Например, если используется код формата "# ###,0 ", число отображается в Excel как 12 ,0.
Примечания:
Разделитель групп разрядов зависит от региональных параметров. Для России это пробел, но в других странах и регионах может использоваться запятая или точка.
Разделитель групп разрядов можно применять в числовых, денежных и финансовых форматах.
Ниже показаны примеры стандартных числовых (только с разделителем групп разрядов и десятичными знаками), денежных и финансовых форматов. В денежном формате можно добавить нужное обозначение денежной единицы, и значения будут выровнены по нему. В финансовом формате символ рубля располагается в ячейке справа от значения (если выбрать обозначение доллара США, то эти символы будут выровнены по левому краю ячеек, а значения — по правому). Обратите внимание на разницу между кодами денежных и финансовых форматов: в финансовых форматах для отделения символа денежной единицы от значения используется звездочка (*).
Чтобы получить код формата для определенной денежной единицы, сначала нажмите клавиши CTRL+1 (на компьютере Mac — +1) и выберите нужный формат, а затем в раскрывающемся списке Обозначение выберите символ.
После этого в разделе Числовые форматы слева выберите пункт (все форматы) и скопируйте код формата вместе с обозначением денежной единицы.
Примечание: Функция ТЕКСТ не поддерживает форматирование с помощью цвета. Если скопировать в диалоговом окне "Формат ячеек" код формата, в котором используется цвет, например "# ##0,00 ₽;[Красный]# ##0,00 ₽", то функция ТЕКСТ воспримет его, но цвет отображаться не будет.
Способ отображения дат можно изменять, используя сочетания символов "Д" (для дня), "М" (для месяца) и "Г" (для года).
В функции ТЕКСТ коды форматов используются без учета регистра, поэтому допустимы символы "М" и "м", "Д" и "д", "Г" и "г".
Способ отображения времени можно изменить с помощью сочетаний символов "Ч" (для часов), "М" (для минут) и "С" (для секунд). Кроме того, для представления времени в часовом формате можно использовать символы "AM/PM".
Если не указывать символы "AM/PM", время будет отображаться в часовом формате.
В функции ТЕКСТ коды форматов используются без учета регистра, поэтому допустимы символы "Ч" и "ч", "М" и "м", "С" и "с", "AM/PM" и "am/pm".
Для отображения десятичных значений можно использовать процентные (%) форматы.
Десятичные числа можно отображать в виде дробей, используя коды форматов вида "?/?".
Экспоненциальное представление — это способ отображения значения в виде десятичного числа от 1 до 10, умноженного на 10 в некоторой степени. Этот формат часто используется для краткого отображения больших чисел.
В Excel доступны четыре дополнительных формата:
"Почтовый индекс" ("");
"Индекс + 4" ("");
"Номер телефона" ("[<=]###-####;(###) ###-####");
"Табельный номер" ("").
Дополнительные форматы зависят от региональных параметров. Если же дополнительные форматы недоступны для вашего региона или не подходят для ваших нужд, вы можете создать собственный формат, выбрав в диалоговом окне Формат ячеек пункт (все форматы).
Типичный сценарий
Функция ТЕКСТ редко используется сама по себе, а чаще применяется в сочетании с чем-то еще. Предположим, что вы хотите объединить текст и числовое значение, например, чтобы получить строку "Отчет напечатан " или "Еженедельный доход: 66 ,72 ₽". Такие строки можно ввести вручную, но суть в том, что Excel может сделать это за вас. К сожалению, при объединении текста и форматированных чисел, например дат, значений времени, денежных сумм и т. п., Excel убирает форматирование, так как неизвестно, в каком виде нужно их отобразить. Здесь пригодится функция ТЕКСТ, ведь с ее помощью можно принудительно отформатировать числа, задав нужный код формата, например "profexcel.ru" для дат.
В примере ниже показано, что происходит, если попытаться объединить текст и число, не применяя функцию ТЕКСТ. Мы используем амперсанд (&) для сцепления текстовой строки, пробела (" ") и значения: =A2&" "&B2.
Вы видите, что значение даты, взятое из ячейки B2, не отформатировано. В следующем примере показано, как применить нужное форматирование с помощью функции ТЕКСТ.
Вот обновленная формула:
ячейка C2:=A2&" "&ТЕКСТ(B2;"profexcel.ru") — формат даты.
Вопросы и ответы
Да, вы можете использовать функции ПРОПИСН, СТРОЧН и ПРОПНАЧ. Например, формула =ПРОПИСН("привет") возвращает результат "ПРИВЕТ".
Да, но для этого необходимо выполнить несколько действий. Сначала выделите нужные ячейки и нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат ячеек. Затем на вкладке Выравнивание в разделе "Отображение" установите флажок Переносить по словам. После этого добавьте в функцию ТЕКСТ код ASCII СИМВОЛ(10) там, где нужен разрыв строки. Вам может потребоваться настроить ширину столбца, чтобы добиться нужного выравнивания.
В этом примере использована формула ="Сегодня: "&СИМВОЛ(10)&ТЕКСТ(СЕГОДНЯ();"profexcel.ru").
Это экспоненциальное представление числа. Excel автоматически приводит к такому виду числа длиной более 12 цифр, если к ячейкам применен формат Общий, и числа длиннее 15 цифр, если выбран формат Числовой. Если вы вводите длинные цифровые строки, но не хотите, чтобы они отображались в таком виде, то сначала примените к соответствующим ячейкам формат Текстовый.
См. также
Создание и удаление пользовательских числовых форматов
Преобразование чисел из текстового формата в числовой
Функции Excel (по категориям)
.