Progress-servis55.ru

Новости из мира ПК
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel text format

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

The Excel TEXT Function

Function Description

The Excel Text function converts a supplied numeric value into text, in a user-specified format.

The syntax of the function is:

Where the function arguments are:

valueA numeric value, that you want to be converted into text.
format_textA text string that defines the formatting that you want to be applied to the supplied value .

The format definitions that can be used in the Excel Text function are shown in the table below. These definitions have the same meaning when used in the custom style of Excel Cell Formatting.

Forces the display of a digit in its place
#Display digit if it adds to the accuracy of the number (but don’t display if a leading zero or a zero at the end of a decimal)
.Defines the position that the decimal place takes
dDay of the month or day of week d = one or two digit representation (e.g. 1, 12) dd = 2 digit representation (e.g. 01, 12) ddd = abbreviated day of week (e.g. Mon, Tue) dddd = full name of day of week (e.g. Monday, Tuesday)
mMonth (when used as part of a date) m = one or two digit representation (e.g. 1, 12) mm = two digit representation (e.g. 01, 12) mmm = abbreviated month name (e.g. Jan, Dec) mmmm = full name of month (e.g. January, December)
yYear yy = 2-digit representation of year(e.g. 99, 08) yyyy = 4-digit representation of year(e.g. 1999, 2008)
hHour h = one or two digit representation (e.g. 1, 20) h = two digit representation (e.g. 01, 20)
mMinute (when used as a part of a time) m = one or two digit representation (e.g. 1, 55) m = two digit representation (e.g. 01, 55)
sSecond s = one or two digit representation (e.g. 1, 45) ss = two digit representation (e.g. 01, 45)
AM/PMIndicates that a time should be represented using a 12-hour clock, followed by «AM» or «PM»
Читать еще:  Подстрока в excel

Text Function Examples

Example 1

The following spreadsheet shows the Excel Text function, used to apply different formatting types to various numeric values.

AB
1ValueFormatted Value
207/07/2015=TEXT(A2, «mm/dd/yyyy»)
342192=TEXT(A3, «mm/dd/yyyy»)
442192=TEXT(A4, «mmm dd yyyy»)
518:00=TEXT(A5, «hh:mm»)
60.75=TEXT(A6, «hh:mm»)
736.363636=TEXT(A7, «0.00»)
80.5555=TEXT(A8, «0.0%»)
9567.9=TEXT(A9, «$#,##0.00»)
10-5=TEXT(A10, «+ $#,##0.00;- $#,##0.00;$0.00»)
115=TEXT(A11, «+ $#,##0.00;- $#,##0.00;$0.00»)
12=TEXT(A12, «+ $#,##0.00;- $#,##0.00;$0.00»)
AB
1ValueFormatted Value
207/07/201507/07/2015
34219207/07/2015
442192Jul 07 2015
518:0018:00
60.7518:00
736.36363636.36
80.555555.6%
9567.9$567.90
10-5— $5.00
115+ $5.00
12$0.00

Note that the results returned from the Text function are all text values.

Example 2

One of the most common uses of the Excel Text function is to insert dates into text strings.

Without the use of the Text function, the simple concatenation of a date returns the date’s underlying integer value. This is shown in the example below:

ABC
1NameDOBName & DOB
2John SMITH03/03/1976=A2 & » » & B2— simple concatenation of date
3=A2 & » » & TEXT(B2,»mm/dd/yyyy»)— concatenation using Text function
ABC
1NameDOBName & DOB
2John SMITH03/03/1976John SMITH 27822— uses date’s underlying integer value
3John SMITH 03/03/1976— gives the expected result

For further details and examples of the Excel Text function, see the Microsoft Office website.

Excel Text Function Common Error

Some users have problems when the Excel Text function returns the #NAME? error:

This is returned from the Excel Text function, if you omit the quotation marks from around the format_text argument.

For example, the formula

will return the #NAME? error.

Solution: Add quotes around the formatting definition.

Функция ТЕКСТ (TEXT)

Например, чтобы отформатировать число 23,5 как сумму в рублях, можно использовать указанную ниже формулу:

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

Результат: 23,50р. в час .

ЗаполнительОписание
0 (ноль)Отображает незначащие нули, если число имеет меньше разрядов, чем количество нулей в строке форматирования. Например, формат #,00 позволяет отобразить значение 8,9 как 8,90.
#Работает так же, как 0 (ноль). Но в этом случае функция Text не возвращает дополнительные нули, если число имеет слева или справа от десятичного разделителя меньше цифр, чем количество символов # в строке форматирования. Например, число 8,9 при использовании формата #,## будет отображаться как 8,9.
?Действует по тем же правилам, что и 0 (нуль). Однако, до и после запятой вместо незначащих нулей отображаются пробелы, чтобы десятичные разделители были выровнены в столбце. Например, формат 0,0? обеспечивает выравнивание запятых для чисел 8,9 и 88,99. (Правда, для чистоты эксперимента, следует выбрать моноширинный шрифт)
, (запятая)Отображает символ десятичного разделителя. Поведение этого символа зависит от языка пользовательского формата.
Читать еще:  Финансовые отчеты в excel

Если в дробной части числа больше цифр, чем заполнителей в формате, число округляется до количества разрядов, равного количеству заполнителей. Если в целой части числа больше цифр, чем заполнителей в формате, отображаются дополнительные цифры. Если формат содержит слева от запятой только знаки номера ( #), то числа, меньшие 1, будут начинаться с запятой, например ,47.

Для отображенияВ видеИспользуемый формат
1234,591234,6«####,#»
8,98,900«#,000»
0,6310,6«0,#»
12
1234,568
12,0
1234,57
«#,0#»
44,398
102,65
2,8
44,398
102,65
2,8
(с выравниванием запятых)
«. »
5,25
5,3
5 1/4
5 3/10
(с выравниванием дробных частей)
«# . /. »

Разделитель групп разрядов

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

Для отображенияВ видеИспользуемый формат
1200012 000«# ###»
1200012«# «
1220000012,2«0,0 «

Форматы даты и времени. Дни, месяцы и годы. Для отображения чисел в формате даты (с указанием числа, месяца и года) в аргументе формат используются коды, указанные ниже.

ФорматОписание
МОтображение месяца в виде числа без начального нуля.
ММОтображение месяца в виде числа с начальным нулем, если он необходим.
МММОтображение сокращенного названия месяца (Янв–Дек).
ММММОтображение полного названия месяца (Январь–Декабрь).
МММММОтображение месяца в виде одной буквы (Я–Д).
ДОтображение дня в виде числа без начального нуля.
ДДОтображение дня в виде числа с начальным нулем, если он необходим.
ДДДОтображение сокращенного названия дня недели (пн–вс).
ДДДДОтображение полного названия дня недели (понедельник–воскресенье).
ГГОтображение года в виде двузначного числа.
ГГГГОтображение года в виде четырехзначного числа.
Для отображенияВ видеИспользуется форма
Месяцев1-12«М»
Месяцев01-12«ММ»
МесяцевЯнв-Дек«МММ»
МесяцевЯнварь-Декабрь«ММММ»
МесяцевЯ-Д«МММММ»
Дней1-31«Д»
Дней01-31«ДД»
Днейпн-вс«ДДД»
Днейпонедельник-воскресенье«ДДДД»
Лет00-99«ГГ»
Лет1900-9999«ГГГГ»

Часы, минуты и секунды

Для отображения чисел в формате времени (с указанием часов, минут и секунд) в аргументе формат используются коды, указанные ниже.

ФорматОписание
чОтображение часов в виде числа без начального нуля.
[ч]Отображение истекшего времени в часах. Если формула возвращает значение времени, в котором число часов превышает 24, используйте числовой формат наподобие следующего: [ч]:мм:сс.
ччОтображение часов в виде числа с начальным нулем, если он необходим. Если формат содержит пометку AM или PM, часы отображаются на основе 12-часового цикла. В противном случае используется 24-часовой цикл.
мОтображение минут в виде числа без начального нуля.

Примечание. Код м или мм должен быть указан непосредственно после кода ч или чч или непосредственно перед кодом сс, иначе вместо минут отображается месяц.

[м]Отображение истекшего времени в минутах. Если формула возвращает значение времени, в котором число минут превышает 60, используйте числовой формат наподобие следующего: [мм]:сс.
ммОтображение минут в виде числа с начальным нулем, если он необходим.

Примечание. Код м или мм должен быть указан непосредственно после кода ч или чч или непосредственно перед кодом сс, иначе вместо минут отображается месяц.

сОтображение секунд в виде числа без начального нуля.
[с]Отображение истекшего времени в секундах. Если формула возвращает значение времени, в котором число секунд превышает 60, используйте числовой формат наподобие следующего: [сс].
ссОтображение секунд в виде числа с начальным нулем, если он необходим. Для отображения долей секунды используйте числовой формат наподобие следующего: ч:мм:сс,00.
AM/PM, am/pm, A/P, a/pОтображение часов на основе 12-часового цикла. Коды AM, am, A и a означают время суток от полуночи до полудня, а PM, pm, P и p — время от полудня до полуночи.
Для отображенияВ видеИспользуется форма
Часов0-23«ч»
Часов00-23«чч»
Минут0-59«м»
Минут00-59«мм»
Секунд0-59«с»
Секунд00-59«сс»
Времени4:00«ч:мм»
Времени16:04«чч:мм»
Времени3:32:05«ч:мм:сс»
Времени3:32:05,75«ч:мм:сс,00»
Времени1:02«[ч]:мм»
Истекшего времени (в часах и минутах)63:23«[мм]:сс»
Истекшего времени (в часах и минутах)3456,70«[сс],00»

Форматы денежных сумм, процентов и экспоненциального представления

Символы валют. Чтобы указать перед числом знак доллара ($), введите его в начале аргумента формат (например, «$# ##0,00»). Чтобы включить в числовой формат один из указанных ниже символов валют, нажмите клавишу NUM LOCK и введите с цифровой клавиатуры код ANSI соответствующего символа.

СимволСочетание клавиш
¢ALT+0162
£ALT+0163
¥ALT+0165
ЕвроALT+0128
  • Примечание. Необходимо использовать цифровую панель клавиатуры. При использовании клавиши ALT с цифрами верхнего ряда клавиш коды ANSI не генерируются.
  • Процентные значения. Чтобы отображать числа в виде процентов — например, 0,08 как 8% или 2,8 как 280%, — включите в аргумент формат знак процента (%).
  • Экспоненциальное представление. Для отображения чисел в экспоненциальном формате включите в аргумент формат код из числа указанных ниже.
ФорматОписание
E (E-, E+, e-, e+)Задает отображение числа в экспоненциальном представлении. В Microsoft Excel отображается знак экспоненты («E» или «e») и справа от него число, показывающее, на сколько разрядов перенесена запятая. Например, если аргумент формат содержит значение «0,00E+00», то число 12200000, отображается как 1,22E+07. Если аргумент формат изменить на «#0,0E+0», будет отображено число 12,2E+6.

Включение текста и добавление пробелов

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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