С помощью функции ТЕКСТ можно изменить представление числа, применив к нему форматирование с кодами форматов. Это полезно в ситуации, когда нужно отобразить числа в удобочитаемом виде либо объединить их с текстом или символами.
Примечание
Функция ТЕКСТ преобразует числа в текст, что может затруднить их использование в дальнейших вычислениях. Рекомендуется сохранить исходное значение в одной ячейке, а затем использовать функцию ТЕКСТ в другой. Затем, если потребуется создать другие формулы, всегда ссылайтесь на исходное значение, а не на результат функции ТЕКСТ.
Технические сведения
Синтаксис
ТЕКСТ(значение; формат)
Аргументы функции ТЕКСТ описаны ниже.
| Имя аргумента | Описание |
|---|---|
| значение | Числовое значение, которое нужно преобразовать в текст. |
| формат | Текстовая строка, определяющая формат, который требуется применить к указанному значению. |
Общие сведения
Самая простая функция ТЕКСТ означает следующее:
- =ТЕКСТ(значение, которое нужно отформатировать; "код формата, который требуется применить")
Ниже приведены популярные примеры, которые вы можете скопировать прямо в 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 |
| =TEXT(1234567898;"[<=9999999]###-###;( ###) ###-###") | Дополнительный формат (номер телефона), например: (123) 456-7898 |
| =ТЕКСТ(1234;"0000000") | Добавление нулей в начале, например: 0001234 |
| =ТЕКСТ(123456;"##0° 00' 00''") | Пользовательский формат (широта или долгота), например: 12° 34' 56'' |
Примечание
- Функцию ТЕКСТ можно использовать для изменения форматирования, но это не единственный способ. Вы можете изменить формат без формулы, нажав клавиши CTRL+1 (или
+1 на компьютере Mac), а затем выберите нужный формат в диалоговом окне Форматномераячеек>. - Если использовать только "ММ" или "мм", будет возвращен месяц. Если объединить с "ЧЧ" для часов или "СС" для секунд, будет возвращаться количество минут, например "ЧЧ:ММ", "ММ:СС"
Скачивание образцов
Предлагаем скачать книгу, в которой содержатся все примеры применения функции ТЕКСТ из этой статьи и несколько других. Вы можете воспользоваться ими или создать собственные коды форматов для функции ТЕКСТ.
Скачать примеры функций Excel TEXT
Другие доступные коды форматов
Просмотреть другие доступные коды форматов можно в диалоговом окне Формат ячеек.
- Нажмите клавиши CTRL+1 (+1 на компьютере Mac), чтобы открыть диалоговое окно Формат ячеек .
- Выберите нужный формат на вкладке Число .
- Выберите параметр (все форматы).
- Нужный код формата будет показан в поле Тип. В этом случае выделите всё содержимое поля Тип, кроме точки с запятой (;) и символа @. В примере ниже выделен и скопирован только код ДД.ММ.ГГГГ.
- Нажмите клавиши CTRL+C, чтобы скопировать код формата, а затем — Отмена, чтобы закрыть диалоговое окно Формат ячеек.
- Теперь осталось нажать клавиши CTRL+V, чтобы вставить код формата в функцию ТЕКСТ. Пример: =ТЕКСТ(B2;"ДД.ММ.ГГГГ"). Обязательно заключите скопированный код формата в кавычки ("код формата"), иначе в Excel возникнет сообщение об ошибке.
Коды форматов по категориям
В примерах ниже показано, как применить различные числовые форматы к значениям следующим способом: открыть диалоговое окно Формат ячеек и выбрать параметр (все форматы), чтобы скопировать нужный код формата в функцию ТЕКСТ.
Выбор числового формата
Начальные нули
Почему Excel удаляет нули в начале?
Excel воспринимает последовательность цифр, введенную в ячейку, как число, а не как цифровой код, например артикул или номер SKU. Чтобы сохранить нули в начале последовательностей цифр, перед вставкой или вводом значений примените к соответствующему диапазону ячеек текстовый формат. Выберите столбец или диапазон, в который вы будете помещать значения, а затем нажмите клавиши CTRL+1 , чтобы открыть диалоговое окно Формат > ячеек , а затем на вкладке Число выберите Текст. Теперь Excel не будет удалять нули в начале.
Если вы уже ввели данные и Excel удалил начальные нули, вы можете снова добавить их с помощью функции ТЕКСТ. Создайте ссылку на верхнюю ячейку со значениями и используйте формат =ТЕКСТ(значение;"00000"), где число нулей представляет нужное количество символов. Затем скопируйте функцию и примените ее к остальной части диапазона.
Если по какой-либо причине необходимо преобразовать текстовые значения обратно в числа, можно умножить на 1, например =D4*1, или использовать двойной унарный оператор (--), например =--D4.
Разделитель групп разрядов.
В Excel группы разрядов разделяются пробелом, если код формата содержит пробел, окруженный знаками номера (#) или нулями. Например, если строка форматирования имеет значение "#,###", excel отображает число 12200000 как 12 200 000.
Пробел после заполнителя цифры задает деление числа на 1000. Например, если используется код формата "# ###,0 ", число 12200000 отображается в Excel как 12 200,0.
Примечание
- Разделитель групп разрядов зависит от региональных параметров. Для России это пробел, но в других странах и регионах может использоваться запятая или точка.
- Разделитель групп разрядов можно применять в числовых, денежных и финансовых форматах.
Числовые, денежные и финансовые форматы
Ниже показаны примеры стандартных числовых (только с разделителем групп разрядов и десятичными знаками), денежных и финансовых форматов. В денежном формате можно добавить нужное обозначение денежной единицы, и значения будут выровнены по нему. В финансовом формате символ рубля располагается в ячейке справа от значения (если выбрать обозначение доллара США, то эти символы будут выровнены по левому краю ячеек, а значения — по правому). Обратите внимание на разницу между кодами денежных и финансовых форматов: в финансовых форматах для отделения символа денежной единицы от значения используется звездочка (*).
Чтобы найти код формата для символа валюты, сначала нажмите клавиши CTRL+1 (или +1 на компьютере Mac), выберите нужный формат, а затем выберите символ в раскрывающемся списке Символ :
Затем щелкните Пользовательский слева в разделе Категория и скопируйте код формата, включая символ валюты.
Примечание
Функция ТЕКСТ не поддерживает форматирование с помощью цвета. Если скопировать в диалоговом окне "Формат ячеек" код формата, в котором используется цвет, например "# ##0,00 ₽;[Красный]# ##0,00 ₽", то функция ТЕКСТ воспримет его, но цвет отображаться не будет.
Даты
Способ отображения дат можно изменять, используя сочетания символов "Д" (для дня), "М" (для месяца) и "Г" (для года).
Коды форматирования в функции TEXT не чувствительны к регистру, поэтому можно использовать "M" или "m", "D" или "d", "Y" или "y".
|
Минда советует... Если вы предоставляете общий доступ к файлам и отчетам Excel пользователям из разных стран, скорее всего, потребуется, чтобы они были на разных языках. Excel MVP, Mynda Treacy имеет отличное решение в этой статье Даты Excel, отображаемые на разных языках . В ней также есть пример книги, который вы можете скачать. |
|---|
Значения времени
Способ отображения времени можно изменить с помощью сочетаний символов "Ч" (для часов), "М" (для минут) и "С" (для секунд). Кроме того, для представления времени в 12-часовом формате можно использовать символы "AM/PM".
Если оставить значение "AM/PM" или "A/P", время будет отображаться на основе 24-часовых часов.
Поскольку "м" может означать месяцы или минуты, чтобы правильно отобразить минуты, используйте формат "ч:м" или "чч:мм". В этом случае Excel корректно отобразит минуты. Если опустить "ч", Excel отобразит значение месяца. Чтобы отобразить только минуты, используйте функцию ПРАВСИМВ, как показано в примере.
В функции ТЕКСТ коды форматов используются без учета регистра, поэтому допустимы символы "Ч" и "ч", "М" и "м", "С" и "с", "AM/PM" и "am/pm".
Квадратные скобки вокруг "ч" ("[ч]") сообщают Excel, что не нужно сбрасывать счетчик часов каждые 24 часа. Иными словами, квадратные скобки позволяют отобразить общее прошедшее время. Квадратные скобки можно использовать для отображения прошедшего времени в часах, минутах и секундах.
Проценты
Для отображения десятичных значений можно использовать процентные (%) форматы.
Дроби
Десятичные числа можно отображать в виде дробей, используя коды форматов вида "?/?".
Экспоненциальное представление
Экспоненциальное представление — это способ отображения значения в виде десятичного числа от 1 до 10, умноженного на 10 в некоторой степени. Этот формат часто используется для краткого отображения больших чисел.
Дополнительные форматы
В Excel доступны четыре дополнительных формата:
- "Почтовый индекс" ("00000");
- "Индекс + 4" ("00000-0000");
- Номер телефона — "[<=9999999]###-###;( ###) ###-####"
- "Табельный номер" ("000-00-0000").
Специальные форматы будут отличаться в зависимости от языкового стандарта, но если для вашего языкового стандарта нет специальных форматов или если они не соответствуют вашим потребностям, вы можете создать собственный формат с помощью диалогового окна Формат ячеек>Настраиваемое .
Типичный сценарий
Функция ТЕКСТ редко используется сама по себе, а чаще применяется в сочетании с чем-то еще. Предположим, что вы хотите объединить текст и числовое значение, например, чтобы получить строку "Отчет напечатан 14.03.12" или "Еженедельный доход: 66 348,72 ₽". Такие строки можно ввести вручную, но суть в том, что Excel может сделать это за вас. К сожалению, при объединении текста и форматированных чисел, например дат, значений времени, денежных сумм и т. п., Excel убирает форматирование, так как неизвестно, в каком виде нужно их отобразить. Здесь функция TEXT неоценима, так как она позволяет заставить Excel форматировать значения так, как требуется, используя код форматирования, например "ММ/ДД/ГГ" для формата даты.
В следующем примере вы увидите, что происходит при попытке объединить текст и число без использования функции TEXT . В этом случае мы используем амперсанд (&) для объединения текстовой строки, пробела (" "), и значения с =A2&" "&B2.
Как видите, Excel удалил форматирование из даты в ячейке B2. В следующем примере показано, как применить нужное форматирование с помощью функции ТЕКСТ.
Наша обновленная формула:
- ячейка C2:=A2&" "&ТЕКСТ(B2;"дд.мм.гггг") — формат даты.
Вопросы и ответы
Как преобразовать числа в текст, например 123 в "сто двадцать три"?
К сожалению, это невозможно сделать с помощью функции ТЕКСТ. Для этого нужно использовать код Visual Basic для приложений (VBA). Следующая ссылка содержит метод: Как преобразовать числовое значение в слова на английском языке в Excel.
Можно ли изменить регистр текста?
Да, вы можете использовать функции ПРОПИСН, СТРОЧН и ПРОПНАЧ. Например, формула =ПРОПИСН("привет") возвращает результат "ПРИВЕТ".
Можно ли с помощью функции ТЕКСТ добавить новую строку (разрыв строки) в ячейке, как при нажатии клавиш ALT+ВВОД?
Да, но для этого необходимо выполнить несколько действий. Сначала выберите ячейку или ячейки, в которых это произойдет, и нажмите клавиши CTRL+1, чтобы открыть диалоговое окно Формат > ячеек, а затем элемент управления Выравнивание > текста проверка параметр Обтекать текст.> После этого добавьте в функцию ТЕКСТ код ASCII СИМВОЛ(10) там, где нужен разрыв строки. Вам может потребоваться настроить ширину столбца, чтобы добиться нужного выравнивания.
В этом примере использована формула ="Сегодня: "&СИМВОЛ(10)&ТЕКСТ(СЕГОДНЯ();"ДД.ММ.ГГ").
Почему Excel преобразует введенные числа во что-то вроде "1,22E+07"?
Это экспоненциальное представление числа. Excel автоматически приводит к такому виду числа длиной более 12 цифр, если к ячейкам применен формат Общий, и числа длиннее 15 цифр, если выбран формат Числовой. Если вы вводите длинные цифровые строки, но не хотите, чтобы они преобразовывались, примените к соответствующим ячейкам формат Текстовый, прежде чем вставлять значения в Excel.
Даты на разных языках
|
|
Минда советует... Если вы предоставляете общий доступ к файлам и отчетам Excel пользователям из разных стран, скорее всего, потребуется, чтобы они были на разных языках. Excel MVP, Mynda Treacy имеет отличное решение в этой статье Даты Excel, отображаемые на разных языках . В ней также есть пример книги, который вы можете скачать. |
|---|