С помощью функции ТЕКСТ можно изменить представление числа, применив к нему форматирование с кодами форматов. Это полезно в ситуации, когда нужно отобразить числа в удобочитаемом виде либо объединить их с текстом или символами.
Примечание: Функция TEXT преобразует числа в текст, что может затруднить ссылку в последующих вычислениях. Лучше сохранить исходное значение в одной ячейке, а затем использовать функцию TEXT в другой ячейке. Затем, если потребуется создать другие формулы, всегда ссылайтесь на исходное значение, а не на результат функции ТЕКСТ.
Синтаксис
ТЕКСТ(значение; формат)
Аргументы функции ТЕКСТ описаны ниже.
|
Имя аргумента |
Описание |
|
значение |
Числовое значение, которое нужно преобразовать в текст. |
|
формат |
Текстовая строка, определяющая формат, который требуется применить к указанному значению. |
Общие сведения
Самая простая функция ТЕКСТ означает следующее:
-
=ТЕКСТ(значение, которое нужно отформатировать; "код формата, который требуется применить")
Ниже приведены популярные примеры, которые вы можете скопировать прямо в Excel, чтобы поэкспериментировать самостоятельно. Обратите внимание: коды форматов заключены в кавычки.
|
Формула |
Описание |
|---|---|
|
=ТЕКСТ(1234,567;"# ##0,00 ₽") |
Денежный формат с разделителем групп разрядов и двумя разрядами дробной части, например: 1 234,57 ₽. Обратите внимание: Excel округляет значение до двух разрядов дробной части. |
|
=ТЕКСТ(СЕГОДНЯ();"ДД.ММ.ГГ") |
Сегодняшняя дата в формате ММ/ДД/ГГ, например 14.03.12 |
|
=ТЕКСТ(СЕГОДНЯ();"ДДДД") |
Сегодняшний день недели, как понедельник |
|
=ТЕКСТ(ТДАТА();"ЧЧ:ММ") |
Текущее время, например: 13:29 |
|
=ТЕКСТ(0,285;"0,0 %") |
Процентный формат, например: 28,5 % |
|
=ТЕКСТ(4,34; "# ?/?") |
Дробный формат, например: 4 1/3 |
|
=СЖПРОБЕЛЫ(ТЕКСТ(0,34;"# ?/?")) |
Дробный формат, например: 1/3 Обратите внимание: функция СЖПРОБЕЛЫ используется для удаления начального пробела перед дробной частью. |
|
=ТЕКСТ(12200000;"0,00E+00") |
Экспоненциальное представление, например: 1,22E+07 |
|
=ТЕКСТ(1234567898;"[<=9999999]###-####;(###) ###-####") |
Дополнительный формат (номер телефона), например: (123) 456-7898 |
|
=ТЕКСТ(1234;"0000000") |
Добавление нулей в начале, например: 0001234 |
|
=ТЕКСТ(123456;"##0° 00' 00''") |
Пользовательский формат (широта или долгота), например: 12° 34' 56'' |
Примечания:
-
Хотя вы можете использовать функцию TEXT для изменения форматирования, это не единственный способ. Вы можете изменить формат без формулы, нажав клавиши CTRL+1 (или
-
Если вы используете только "MM" или "mm", он вернет месяц. Если вы объедините его с "HH" в часах или "SS" в секунды, он вернет минуты, например. "ЧЧ:ММ", "ММ:СС"
Скачивание образцов
Предлагаем скачать книгу, в которой содержатся все примеры применения функции ТЕКСТ из этой статьи и несколько других. Вы можете воспользоваться ими или создать собственные коды форматов для функции ТЕКСТ.Скачать примеры применения функции ТЕКСТ
Другие доступные коды форматов
С помощью диалогового окна Формат ячеек можно найти другие доступные коды форматирования:
-
Нажмите клавиши CTRL+1 (
-
На вкладке Число выберите нужный формат.
-
Выберите параметр Пользовательский .
-
Нужный код формата будет показан в поле Тип. В этом случае выделите всё содержимое поля Тип, кроме точки с запятой (;) и символа @. В примере ниже выделен и скопирован только код ДД.ММ.ГГГГ.
-
Нажмите клавиши CTRL+C , чтобы скопировать код форматирования, а затем нажмите кнопку Отмена , чтобы закрыть диалоговое окно Формат ячеек .
-
Теперь осталось нажать клавиши CTRL+V, чтобы вставить код формата в функцию ТЕКСТ. Пример: =ТЕКСТ(B2;"ДД.ММ.ГГГГ"). Убедитесь, что код формата вставляется в кавычки ("код форматирования"), в противном случае Excel выдаст сообщение об ошибке.
Коды форматов по категориям
Ниже приведены некоторые примеры того, как можно применить различные числовые форматы к значениям с помощью диалогового окна Формат ячеек , а затем с помощью параметра Custom скопировать эти коды форматирования в функцию TEXT .
Почему Excel удаляет мои начальные нули?
Excel воспринимает последовательность цифр, введенную в ячейку, как число, а не как цифровой код, например артикул или номер SKU. Чтобы сохранить нули в начале последовательностей цифр, перед вставкой или вводом значений примените к соответствующему диапазону ячеек текстовый формат. Выделите столбец или диапазон, в который нужно поместить значения, нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат ячеек, и выберите на вкладке Число пункт Текстовый. Теперь Excel сохранит начальные нули.
Если вы уже ввели данные и Excel удалили начальные нули, вы можете использовать функцию TEXT , чтобы добавить их обратно. Вы можете ссылаться на верхнюю ячейку со значениями и использовать =TEXT(value,"00000"), где число нулей в формуле представляет общее количество символов, которое вы хотите, а затем скопируйте и вставьте в остальную часть диапазона.
Если по какой-либо причине потребуется преобразовать текстовые значения обратно в числа, можно умножить их на 1 (например: =D4*1) или воспользоваться двойным унарным оператором (--), например: =--D4.
В Excel группы разрядов разделяются пробелом, если код формата содержит пробел, окруженный знаками номера (#) или нулями. Например, если используется код формата "# ###", число 12200000 отображается как 12 200 000.
Пробел после заполнителя цифры задает деление числа на 1000. Например, если используется код формата "# ###,0 ", число 12200000 отображается в Excel как 12 200,0.
Примечания:
-
Разделитель групп разрядов зависит от региональных параметров. Для России это пробел, но в других странах и регионах может использоваться запятая или точка.
-
Разделитель групп разрядов можно применять в числовых, денежных и финансовых форматах.
Ниже показаны примеры стандартных числовых (только с разделителем групп разрядов и десятичными знаками), денежных и финансовых форматов. В денежном формате можно добавить нужное обозначение денежной единицы, и значения будут выровнены по нему. В финансовом формате символ рубля располагается в ячейке справа от значения (если выбрать обозначение доллара США, то эти символы будут выровнены по левому краю ячеек, а значения — по правому). Обратите внимание на разницу между кодами денежных и финансовых форматов: в финансовых форматах для отделения символа денежной единицы от значения используется звездочка (*).
Чтобы найти код формата для символа валюты, сначала нажмите клавиши CTRL+1 (или
После этого в разделе Числовые форматы слева выберите пункт (все форматы) и скопируйте код формата вместе с обозначением денежной единицы.
Примечание: Функция ТЕКСТ не поддерживает форматирование с помощью цвета. Если скопировать в диалоговом окне "Формат ячеек" код формата, в котором используется цвет, например "# ##0,00 ₽;[Красный]# ##0,00 ₽", то функция ТЕКСТ воспримет его, но цвет отображаться не будет.
Способ отображения дат можно изменять, используя сочетания символов "Д" (для дня), "М" (для месяца) и "Г" (для года).
В функции ТЕКСТ коды форматов используются без учета регистра, поэтому допустимы символы "М" и "м", "Д" и "д", "Г" и "г".
|
|
Минда советует... Если вы предоставляете общий доступ к файлам и отчетам Excel пользователям из разных стран, скорее всего, потребуется, чтобы они были на разных языках. Минда Триси (Mynda Treacy), Excel MVP, предлагает отличное решение этой задачи в своей статье Отображение дат Excel на разных языках (на английском). В ней также есть пример книги, который вы можете скачать. |
Способ отображения времени можно изменить с помощью сочетаний символов "Ч" (для часов), "М" (для минут) и "С" (для секунд). Кроме того, для представления времени в 12-часовом формате можно использовать символы "AM/PM".
Если не указывать символы "AM/PM", время будет отображаться в 24-часовом формате.
Так как "m" может быть месяцем или минутами, для отображения минут необходимо сделать это в формате "ч:m" или "чч:мм", чтобы Excel правильно отображал минуты. Если опустить значение "h", в Excel отобразится значение месяца. Чтобы отобразить только минуты, используйте функцию RIGHT, как показано в примере.
В функции ТЕКСТ коды форматов используются без учета регистра, поэтому допустимы символы "Ч" и "ч", "М" и "м", "С" и "с", "AM/PM" и "am/pm".
Использование квадратных скобок вокруг "h" ("[h]") указывает Excel не сбрасывать количество часов каждые 24 часа. Другими словами, квадратные скобки позволяют отобразить общее затраченное время. Вы можете использовать квадратные скобки для отображения затраченного времени в часах, минутах и секундах.
Для отображения десятичных значений можно использовать процентные (%) форматы.
Десятичные числа можно отображать в виде дробей, используя коды форматов вида "?/?".
Экспоненциальное представление — это способ отображения значения в виде десятичного числа от 1 до 10, умноженного на 10 в некоторой степени. Этот формат часто используется для краткого отображения больших чисел.
В Excel доступны четыре дополнительных формата:
-
"Почтовый индекс" ("00000");
-
"Индекс + 4" ("00000-0000");
-
"Номер телефона" ("[<=9999999]###-####;(###) ###-####");
-
"Табельный номер" ("000-00-0000").
Дополнительные форматы зависят от региональных параметров. Если же дополнительные форматы недоступны для вашего региона или не подходят для ваших нужд, вы можете создать собственный формат, выбрав в диалоговом окне Формат ячеек пункт (все форматы).
Типичный сценарий
Функция ТЕКСТ редко используется сама по себе, а чаще применяется в сочетании с чем-то еще. Предположим, вы хотите объединить текст и числовое значение, например "Отчет, напечатанный: 14.03.12" или "Еженедельный доход: 66 348,72 долл. США". Такие строки можно ввести вручную, но суть в том, что Excel может сделать это за вас. К сожалению, при объединении текстовых и форматированных чисел, таких как даты, время, валюта и т. д., Excel не знает, как их отображать, поэтому он удаляет форматирование чисел. Здесь пригодится функция ТЕКСТ, ведь с ее помощью можно принудительно отформатировать числа, задав нужный код формата, например "ДД.ММ.ГГГГ" для дат.
В следующем примере вы увидите, что происходит при попытке объединить текст и число без использования функции TEXT . В этом случае мы используем амперсанд (&) для объединения текстовой строки, пробела (" "), и значения с =A2&" "&B2.
Вы видите, что значение даты, взятое из ячейки B2, не отформатировано. В следующем примере показано, как применить нужное форматирование с помощью функции ТЕКСТ.
Вот обновленная формула:
-
ячейка C2:=A2&" "&ТЕКСТ(B2;"дд.мм.гггг") — формат даты.
Вопросы и ответы
К сожалению, вы не можете сделать это с помощью функции TEXT; необходимо использовать код Visual Basic для приложений (VBA). Следующая ссылка содержит метод: Как преобразовать числовое значение в слова на английском языке в Excel.
Да, но для этого необходимо выполнить несколько действий. Сначала выберите ячейку или ячейки, в которых это произойдет, и нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат > ячеек, а затем элемент управления Выравнивание > текст > проверка параметр Обтекать текстом. После этого добавьте в функцию ТЕКСТ код ASCII СИМВОЛ(10) там, где нужен разрыв строки. Вам может потребоваться настроить ширину столбца, чтобы добиться нужного выравнивания.
В этом примере использована формула ="Сегодня: "&СИМВОЛ(10)&ТЕКСТ(СЕГОДНЯ();"ДД.ММ.ГГ").
Это называется научной нотацией, и Excel автоматически преобразует числа, превышающие 12 цифр, если ячейки форматируются как общие, и 15 цифр, если ячейки отформатированы как число. Если вам нужно ввести длинные числовые строки, но не нужно их преобразовывать, отформатируйте ячейки, о которой идет речь, как Текст , прежде чем вводить или вставлять значения в Excel.
|
|
Минда советует... Если вы предоставляете общий доступ к файлам и отчетам Excel пользователям из разных стран, скорее всего, потребуется, чтобы они были на разных языках. Минда Триси (Mynda Treacy), Excel MVP, предлагает отличное решение этой задачи в своей статье Отображение дат Excel на разных языках (на английском). В ней также есть пример книги, который вы можете скачать. |
См. также
Создание и удаление пользовательских числовых форматов
