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

Пользовательский формат

Всем известно, что данные в ячейке могут представлять собой число, текст или значение ошибки. Как отображаются те или иные данные в ячейке, зависит от примененного формата. В Excel существует множество встроенных форматов, с помощью которых мы можем видеть числа в денежном представлении или экспоненциальном, в виде даты или дроби. Форматы доступны в диалоговом окне Формат ячейки на вкладке Число. Но всё же иногда встроенных форматов не хватает, и тогда Excel предоставляет пользователю возможность создать свой формат. В этой статье рассматриваются основные правила построения пользовательского формата, и приводятся примеры различных форматов.   Правила построения формата   Для того, чтобы создать пользовательский формат, следует в диалоговом окне Формат ячейки перейти на вкладку Число, выбрать в списке числовых форматов вариант (все форматы)и в поле Типввести новый формат     Пользовательский формат может иметь от одного до четырёх разделов, разделенных точкой с запятой. Эти разделы определяют формат положительных, отрицательных чисел, нуля и текста (именно в таком порядке). Если раздел всего один, то он относится ко всем числам. Если разделов два, то первый определяет представление положительных чисел и нуля, а второй - отрицательных чисел. Если какой-то из разделов пропускается, то точку с запятой всё равно надо поставить. Например, формат ;;;@ позволяет отображать в ячейке только текст, а числа отображаться не будут. Если разделов один, два или три, то текст, введённый в ячейку, всё равно отображается.   Важно. Формат числа никак не влияет на его значение, с помощью формата мы изменяем только внешнее представление числа в ячейке   Запись формата формируется с помощью специальных символов, которые могут быть дополнены произвольным текстом, а также указанием цвета шрифта и некоторых условий.   При создании пользовательского формата используются следующие коды   Код Описание Пример формата Содержимое ячейки Результат Основной общий числовой формат, числа отображаются так, как были введены Основной 1234,56 78 0,9 1234,56 78 0,9 # любая цифра необязательная, лишние цифры после запятой округляются #,# 1234,56 78 0,9 1234,6 78, ,9 любая цифра обязательная. Недостающие цифры заменяются нулём, как в целой части, так и в дробной, лишние цифры после запятой округляются 000,0 1234,56 78 0,9 1234,6 078,0 000,9 ? любая цифра обязательная. Недостающие цифры заменяются пробелом (используется для выравнивания чисел по десятичной запятой), лишние цифры после запятой округляются ???,? 1234,56 78 0,9 -25 -3,4 1234,6 78, ,9 - 25, - 3,4 % умножает число на 100 и добавляет знак % после него 0,0% 0,9 90,0% пробел используется как разделитель разрядов, если стоит в середине формата, и уменьшает число в 1000 раз, если стоит в конце формата # ##0,0 # ##0,0 (пробел) # ##0,0 (два пробела) 123456,7 123 456,7 123,5 0,1 E+ E- используется для экспоненциальной записи числа 0,0E+00 0E+0 123456,7 1,2E+05 1E+5 @ используется для вывода текста       " " (кавычки) выводит текст, заключенный в кавычки       $- + / ( ) : пробел эти символы отображаются в ячейке, даже если записаны в формате без кавычек       \ используется для экранирования, т.е. для отображения следующего за ним символа без изменений       * заполняет ячейку символом, который следует за * *- 1234,56 ------------ _ (подчёркивание) делает символ, следующий за ним невидимым, но оставляет свободное место, равное ширине символа. Используется для выравнивания чисел по десятичной запятой, если, например, отрицательное число заключается в скобки # ##0,00_);(# ##0,00);- 1234,5 -1234,5 1 234,50 (1 234,50)

Задание в формате условия

По умолчанию в пользовательском формате для чисел установлены условия >0; <0; =0. Но при желании можно установить разный формат для данных, сгруппированных по иным условиям. Условия формируются с помощью знаков отношений: = < >.

Формат	Содержимое ячейки	Результат	Примечание
[Синий][<100]# ##0,00;[Красный][>500]# ##0,00;0,00	50 456 620 -100	50,00 456 620,00 -100
[Красный][<0]"Ошибка";[<100]00" коп.";0" руб." 00" коп."	456 56 -100	4 руб. 56 коп. 56 коп. Ошибка
[Красный][<10000]"Неверный номер"; [>9999999]#(000)##0-00-00;##0-00-00	55896 7335566 8127777777 89817249519 1234	5-58-96 733-55-66 (812)777-77-77 8(981)724-95-19 Неверный номер	Форматирует номера телефонов. Номера более 7 цифр отображаются с кодом региона, от 5 до 7 цифр - без кода региона, менее 5 цифр - ошибочны
[>1000000]0 " млн. руб.";[>1000]0 " тыс. руб.";0" руб."	123456789 123456 123	123 млн. руб. 123 тыс. руб. 123 руб.

Пользовательский формат

Всем известно, что данные в ячейке могут представлять собой число, текст или значение ошибки. Как отображаются те или иные данные в ячейке, зависит от примененного формата. В Excel существует множество встроенных форматов, с помощью которых мы можем видеть числа в денежном представлении или экспоненциальном, в виде даты или дроби. Форматы доступны в диалоговом окне Формат ячейки на вкладке Число. Но всё же иногда встроенных форматов не хватает, и тогда Excel предоставляет пользователю возможность создать свой формат. В этой статье рассматриваются основные правила построения пользовательского формата, и приводятся примеры различных форматов.   Правила построения формата   Для того, чтобы создать пользовательский формат, следует в диалоговом окне Формат ячейки перейти на вкладку Число, выбрать в списке числовых форматов вариант (все форматы)и в поле Типввести новый формат     Пользовательский формат может иметь от одного до четырёх разделов, разделенных точкой с запятой. Эти разделы определяют формат положительных, отрицательных чисел, нуля и текста (именно в таком порядке). Если раздел всего один, то он относится ко всем числам. Если разделов два, то первый определяет представление положительных чисел и нуля, а второй - отрицательных чисел. Если какой-то из разделов пропускается, то точку с запятой всё равно надо поставить. Например, формат ;;;@ позволяет отображать в ячейке только текст, а числа отображаться не будут. Если разделов один, два или три, то текст, введённый в ячейку, всё равно отображается.   Важно. Формат числа никак не влияет на его значение, с помощью формата мы изменяем только внешнее представление числа в ячейке   Запись формата формируется с помощью специальных символов, которые могут быть дополнены произвольным текстом, а также указанием цвета шрифта и некоторых условий.   При создании пользовательского формата используются следующие коды   Код Описание Пример формата Содержимое ячейки Результат Основной общий числовой формат, числа отображаются так, как были введены Основной 1234,56 78 0,9 1234,56 78 0,9 # любая цифра необязательная, лишние цифры после запятой округляются #,# 1234,56 78 0,9 1234,6 78, ,9 любая цифра обязательная. Недостающие цифры заменяются нулём, как в целой части, так и в дробной, лишние цифры после запятой округляются 000,0 1234,56 78 0,9 1234,6 078,0 000,9 ? любая цифра обязательная. Недостающие цифры заменяются пробелом (используется для выравнивания чисел по десятичной запятой), лишние цифры после запятой округляются ???,? 1234,56 78 0,9 -25 -3,4 1234,6 78, ,9 - 25, - 3,4 % умножает число на 100 и добавляет знак % после него 0,0% 0,9 90,0% пробел используется как разделитель разрядов, если стоит в середине формата, и уменьшает число в 1000 раз, если стоит в конце формата # ##0,0 # ##0,0 (пробел) # ##0,0 (два пробела) 123456,7 123 456,7 123,5 0,1 E+ E- используется для экспоненциальной записи числа 0,0E+00 0E+0 123456,7 1,2E+05 1E+5 @ используется для вывода текста       " " (кавычки) выводит текст, заключенный в кавычки       $- + / ( ) : пробел эти символы отображаются в ячейке, даже если записаны в формате без кавычек       \ используется для экранирования, т.е. для отображения следующего за ним символа без изменений       * заполняет ячейку символом, который следует за * *- 1234,56 ------------ _ (подчёркивание) делает символ, следующий за ним невидимым, но оставляет свободное место, равное ширине символа. Используется для выравнивания чисел по десятичной запятой, если, например, отрицательное число заключается в скобки # ##0,00_);(# ##0,00);- 1234,5 -1234,5 1 234,50 (1 234,50)

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

Иногда требуется к числу присоединить текст, но так, чтобы число осталось числом, и с ним можно было производить вычисления (например, добавить единицы измерения). В этом случае текст можно добавить через формат ячейки. Добавляемый текст, как правило, заключается в кавычки, без кавычек можно записывать текст, если в нём нет зарезервированных символов. Например, можно записать без кавычек т (тонн), но нельзя кг, так как символ г зарезервирован для форматирования дат. Чтобы зарезервированные символы отобразились, как обычные, можно перед ними поставить обратный слеш\ (к\г). Но на мой взгляд проще и надёжнее заключить текст в кавычки

Формат	Содержимое ячейки	Результат	Примечание
# ##0,00" кг"		456,00 кг
# ##0,00 " т"		0,46 т	пробел после нуля уменьшает число в 1000 раз
0" кг",000" г"	456,123	456 кг,123 г
"ABCD-"#		ABCD-456
"Приход"*.0,00;"Расход"*.0,00;-	456 -456 0	Приход…..456,00 Расход…..456,00 -	сочетание *. (звёздочка-точка) обеспечивает разделение текста и числа точками, т.е. текст располагается по левому краю ячейки, число располагается по правому краю, а все пространство между ними заполняется точками
"Избыток";"Недостаток";"Баланс";	456 -456 0 текст	Избыток Недостаток Баланс	так как четвёртый текстовый раздел пустой, а точка с запятой для него поставлена, то текст отображаться в ячейке не будет

Задание в формате цвета шрифта

Цвет шрифта может задаваться в любом из четырёх разделов формата. Название цвета указывается в квадратных скобках и должно быть первым в разделе. Допускается использовать один из восьми цветов: чёрный(black), зеленый(green), белый(white), синий(blue), фиолетовый(magenta), желтый(yellow), голубой(cyan), красный(red), а также задавать цвет индексом цвет n, где n может принимать значения от 1 до 56, правда 10 из них повторяются по два раза. По умолчанию используется чёрный цвет.

Формат	Содержимое ячейки	Результат	Примечание
[Синий]# ##0,00;[Красный]-# ##0,00	456 -456	456,00 -456,00
# ##0,00" кг";[Красный]"Ошибка!"	456 -456	456 кг Ошибка!
[Красный]"Избыток";[Синий]"Недостаток";[Зеленый]"Баланс";	456 -456 0	Избыток Недостаток Баланс
[Черный]Основной	456 #ЗНАЧ!	456	Если цвет шрифта установить в цвет фона ячейки (белый по белому), то при таком формате скроются значения ошибок
0,0;-0,0;-;[Цвет46]	456 -456 0 текст	456 -456 - текст	Выведет текст цветом 46 (оранжевым)

Задание в формате условия

По умолчанию в пользовательском формате для чисел установлены условия >0; <0; =0. Но при желании можно установить разный формат для данных, сгруппированных по иным условиям. Условия формируются с помощью знаков отношений: = < >.

Формат	Содержимое ячейки	Результат	Примечание
[Синий][<100]# ##0,00;[Красный][>500]# ##0,00;0,00	50 456 620 -100	50,00 456 620,00 -100
[Красный][<0]"Ошибка";[<100]00" коп.";0" руб." 00" коп."	456 56 -100	4 руб. 56 коп. 56 коп. Ошибка
[Красный][<10000]"Неверный номер"; [>9999999]#(000)##0-00-00;##0-00-00	55896 7335566 8127777777 89817249519 1234	5-58-96 733-55-66 (812)777-77-77 8(981)724-95-19 Неверный номер	Форматирует номера телефонов. Номера более 7 цифр отображаются с кодом региона, от 5 до 7 цифр - без кода региона, менее 5 цифр - ошибочны
[>1000000]0 " млн. руб.";[>1000]0 " тыс. руб.";0" руб."	123456789 123456 123	123 млн. руб. 123 тыс. руб. 123 руб.