Функция Excel ТЕКСТ с примерами формул

В этом руководстве объясняются особенности функции Excel ТЕКСТ и показаны некоторые умные способы использования текстовых формул в Excel.

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

Функция Excel ТЕКСТ — синтаксис и основные способы использования

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

Синтаксис функции Excel ТЕКСТ следующий:

ТЕКСТ(значение, формат_текст)

Где:

  • Значение — числовое значение, которое необходимо преобразовать в текст. Это может быть число, дата, ссылка на ячейку, содержащую числовое значение, или другая функция, возвращающая число или дату.
  • Format_text — формат, который вы хотите применить. Он поставляется в виде код формата в кавычках, например, “мм/дд/гг”.

Функция ТЕКСТ доступна во всех версиях Excel 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 и более ранних версиях.

Как правило, формула Excel TEXT используется в следующих ситуациях:

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

Например, если вы хотите извлечь дату из ячейки A2 и отобразить ее в другой ячейке в традиционном формате даты, например «1 января 2016 г.», вы используете следующую формулу Excel TEXT:

=ТЕКСТ(A2, “мммм д, гггг”)

Результат будет выглядеть примерно так:
Использование функции ТЕКСТ Excel для отображения даты в пользовательском формате

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

Коды форматов функций Excel ТЕКСТ

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

Код Описание Пример кода формата 0 Заполнитель цифр, отображающий незначащие нули. #.00 – всегда отображает 2 знака после запятой.

Если вы введете 2,5 в указанную ячейку, она будет отображаться как 2,50.

# Заполнитель цифр, который не отображает лишние нули. #.## – отображает до 2 знаков после запятой.

Если вы наберете 2.5, оно будет отображаться как 2.5.

Если вы наберете 2,555, оно будет отображаться как 2,56.

? Заполнитель цифр, оставляющий место для незначащих нулей, но не отображающий их. Обычно он используется для выравнивания чисел в столбце по десятичной точке. #.?? – отображает максимум 2 десятичных знака и выравнивает десятичные точки в столбце. . (точка) Десятичная точка , (запятая) Разделитель тысяч. ###,###.## – отображает разделитель тысяч и 2 знака после запятой.

Если вы наберете 250000, оно будет отображаться как 250 000,00.

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

Символ Описание + и – Знаки плюс и минус ( ) Левая и правая круглые скобки : Двоеточие ^ Карет ‘ Апостроф { } Фигурные скобки < > Знаки меньше и больше = знак равенства / косая черта ! Восклицательный знак и амперсанд ~ символ пробела Тильда

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

A B C 1 Исходное значение Формула форматированного значения 2 5,5 5,50 =ТЕКСТ(A2, “0,00”) 3 550% =ТЕКСТ(A2,”#%”) 4 5,50 $ =ТЕКСТ(A2, “$#,##0,00”) 5 + $5.50 =ТЕКСТ(A2, “+ $#,##0.00;- $#,##0.00;$0.00”) 6 – $5.50 =ТЕКСТ(A2, “- $#,##0.00;- $#,##0.00 ;$0.00″) 7 5 1/2 =ТЕКСТ(A2,”# ?/?”) 8 5.50E+00 =ТЕКСТ(A2,”0.00E+00″) 9 ~6 ! =ТЕКСТ(A2,”~#!”)

При использовании функции ТЕКСТ Excel с датами и временем вы можете использовать любой из следующих кодов формата.

Код формата Описание Примеры d День месяца или

день недели

г – одно- или двузначное число без ведущего нуля (от 1 до 31)

дд – двузначное число с ведущим нулем (от 01 до 31)

ддд – трехбуквенная аббревиатура (с понедельника по воскресенье)

дддд – полное название дня недели (с понедельника по воскресенье)

m Месяц (при использовании как часть даты)
м – одно- или двузначное число без ведущего нуля (от 1 до 12)

мм – двузначное число с ведущим нулем (от 01 до 12)

М-м-м – сокращенный месяц (с января по декабрь)

мммм – полное название месяца (с января по декабрь)

г Год
гг – двузначное число (например, 06 означает 2006 год или 16 означает 2016 год)

гггг – четырехзначный номер (например, 2006, 2016)

ч час
час – одно- или двузначное число без ведущего нуля (от 1 до 24)

чч – двузначное число с ведущим нулем (от 01 до 24)

m Минута (при использовании как часть времени)
м – одно- или двузначное число без ведущего нуля (от 1 до 60)

мм – двузначное число с нулем в начале (от 01 до 60)

с Второй
с – одно- или двузначное число без ведущего нуля (от 1 до 60)

SS – двузначное число с нулем в начале (от 01 до 60)

AM/PM Время, представленное в виде 12-часового формата, за которым следует «AM» или «PM».

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

A B C 1 Исходная дата Форматированная дата Формула 2 09.12.2016 09 декабря 2016 =ТЕКСТ(A2, “ммм дд гггг”) 3 Пятница, 09 декабря 2016 г. =ТЕКСТ(A2, “дддд дд мммм, гггг”) 4 9-дек. -16 =ТЕКСТ(A2, “д-ммм-гг”) 5 Пятница =ТЕКСТ(A2,”дддд”)

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

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

Объединение текста и числа (или даты) в пользовательском формате

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

Пример 1. Форматирование чисел внутри текстовой строки

Предположим, вы рассчитываете общую сумму на основе цены за единицу в ячейке A2, количества в B2 и процента скидки в C2, используя следующий расчет: =A2*B2*(1-C2). Чтобы ваши пользователи точно знали, что означает выходной номер, вы хотите отобразить его вместе с пояснительным текстом, например “Ваша цена“. Кроме того, вы хотите показать символ валюты, разделитель тысяч и два десятичных знака.

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

=”Ваша цена “&TEXT(A2*B2*(1-C2), “$###,###.00”)

или же

= СЦЕПИТЬ(“Ваша цена “, ТЕКСТ(A2*B2*(1-C2), “$###,###.00”))

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

Пример 2. Объединить текст и дату в нужном формате

Когда вы возвращаете текущую дату с помощью функции СЕГОДНЯ() или СЕЙЧАС(), вы также можете отобразить ее в сочетании с каким-либо текстом, чтобы ни у кого не возникло сомнений, к какому дню относится эта дата.

Однако, если вы попытаетесь соединить текст и дату обычным способом:

=СЦЕПИТЬ(“Сегодня “, СЕГОДНЯ())

Excel вернет очень странный результат, что-то вроде “Сегодня 42198“.

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

Например, встраивание функции СЕГОДНЯ в текстовую формулу с кодом формата «dddd d mmm, yyyy» вернет строку, подобную этой: «Сегодня понедельник, 12 декабря 2016 года».

Полная формула выглядит следующим образом:

=СЦЕПИТЬ(“Сегодня “, ТЕКСТ(СЕГОДНЯ(), “дддд д ммм, гггг”))

или же

=”Сегодня ” & ТЕКСТ(СЕГОДНЯ(), “дддд д ммм, гггг”)
Объедините текст и дату в нужном формате

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

Добавление начальных нулей к числам переменной длины

Как вы знаете, Microsoft Excel автоматически удаляет начальные нули, введенные перед числом в ячейке, что нормально работает в большинстве ситуаций. Но что, если вы хотите сохранить предшествующие нули?

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

Например, чтобы отобразить 7-значные числа с ведущими нулями, используйте эту формулу (где A2 — исходное число):

=ТЕКСТ(A2;”0000000″)

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

Преобразование значений в номера телефонов в определенном формате

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

Таким образом, чтобы отобразить номер в формате A2 в традиционном местном 7-значном телефонном формате США, таком как 123-456, используйте следующую формулу:

=ТЕКСТ(A2, “###-####”)

Если некоторые исходные значения могут содержать внутренний префикс (т. е. могут быть как 7-значные, так и 10-значные числа), включите следующий код условного формата для отображения 10-значных чисел в формате (123) 456-789:

=ТЕКСТ(A2,”[<=9999999]###-####;(###) ###-####”)

На приведенном ниже снимке экрана показана эта формула Excel Text в действии:
Текстовая формула для преобразования значений в номера телефонов

Не работает функция Excel ТЕКСТ – причины и решения

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

  1. Функция ТЕКСТ возвращает #ИМЯ? ошибка, если вы опускаете кавычки вокруг кода формата.

    Например, формула =ТЕКСТ(A2, мм/дд/гг) неверна и должна быть записана следующим образом: =ТЕКСТ(A2, “мм/дд/гг”)

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

Например, формула =ТЕКСТ(A2, “мм/дд/гг”), которая отлично работает для англоязычных пользователей, может возвращать ошибку #ЗНАЧ в других языковых стандартах. В частности, в Германии вам необходимо использовать следующие коды форматов: “т” вместо “г” а также “Дж” вместо “у“, потому что “день” в немецком языке – это “tag”, а год – “jahr”; “m” (месяц) подходит, потому что в немецком языке он также начинается с “m” (monat). Таким образом, в немецком Excel приведенная выше формула будет читается следующим образом: =ТЕКСТ(A2; “mm/tt/jj”). Во Франции вы должны использовать код формата “mm/jj/aa”, потому что “день” – это “jour”, “месяц” – “mois “, а “год” – это “ан”.

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

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

Другие текстовые функции Excel

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

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *