Числовые форматы ячеек

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

Немного любопытного замечания

Возможно одной из причин редкого использования такой функции Excel как определенные числовые форматы ячеек, является название, которое она получила в русскоязычной версии Excel. Возможность задания особых способов отображения ячеек находится в окне «Формат ячеек» (в английской версии Format Cells), что логично, задаем же форматирование для ячейки. На вкладке «Число» (Number), отсюда делаем вывод, что с точки зрения Excel определяем форматирование числовой ячейки, хотя текстовые и прочие строковые форматы тоже в этом же списке, что уже наводит на размышления. И в списке «Числовые форматы» (Category), то есть в английском дословно это «Категории», а в русском «Числовые форматы», любопытно. И наконец, последний элемент в списке числовых форматов «(все форматы)», что в английском называется как «Custom». Обычно слово «Custom» при локализации приложений на русский язык переводят как «Настраиваемый» или «Пользовательский» что вероятнее более точно соответствует сути данного формата, но как слово «Custom» стало «Все форматы».

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

С помощью этого типа форматирования, мы можем настроить отображение ячейки как нам необходимо. Мы можем не только использовать предопределенные способы форматирования, но и задать свои. Поэтому скорее это поле можно назвать как «Пользовательский формат».

Из чего всё состоит

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

Во многих современных языках программирования используется тот же принцип для форматирования значений при их отображении. Да, по сути, и правила и символы, используемые для форматирования те же самые. Чаще всего для числовых форматов используется знак «#» или «,» или «0», хотя могут использоваться и другие. Задаваемое описание и определяет, как будет отображаться значение. При помощи этих описаний можно задать и стандартное представление значений, такое например как денежное или числовое, но логичнее применять его для случаев, когда требуется особый вид. Расширенное описание форматирования позволяет в некоторых случаях не прибегать к условному форматированию, задавая особые способы отображения отрицательных чисел.

Свои форматы

Как уже говорилось, пользователь может ввести собственные форматы для отображения данных. Сделать это в диалоговом окне «Формат ячеек», пункте «все форматы». Вновь создаваемые форматы сохраняются в рабочей книге и могут быть использованы на любом листе рабочей книги. Всего в рабочей книге можно сохранить до 200 новых определений форматирования. Если вы работаете с большой рабочей книгой, где необходимо использовать нестандартное форматирование, сохранение такого количества новых форматов позволит максимально полно удовлетворить потребности. Новый формат на самом деле, очень просто перенести в другую книгу, для этого достаточно скопировать ячейку, где применяется форматирование в новую книгу.

Новый формат задается как последовательность специальных символов вводимых в поле «Тип» вкладки «Число», диалогового окна «Формат ячеек». В целом определение формата схоже с аналогичным функционалом языков программирования, но есть ряд особенностей. Во первых в Excel новый формат можно задать как группу из четырех описаний, разделенных между собой точкой с запятой – это «формат положительных чисел»; «формат отрицательных чисел»; «формат нулевых значений»; «формат текста». В одном определении можно описать все четыре способа форматирования, но это не обязательно, можно ограничиться только необходимыми. При этом Excel будет воспринимать описание следующим образом:

Одно описание – применяется ко всем значениям в ячейке.

Два описания – первое применяется к положительным и нулевым значениям, второе к отрицательным значениям.

Три описания – первое положительные значения, второе отрицательные значения, третье нулевые значения.

Четыре описания — первое положительные значения, второе отрицательные значения, третье нулевые значения, четвертое текст.

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

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

Коды форматирования значений

Разберемся, какие коды и для чего используются при настройке форматирования:

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

0 (нуль) – значащий цифровой разряд. Может применяться для дополнения числа слева, до необходимой длинны. При этом слева дополняется нулём.

? – цифровой разряд. Применяется относительно десятичной запятой. Если число слева или справа от запятой меньше необходимого количества знаков, то значение дополняется пробелом.

, — запятая, просто десятичная запятая.

% — процент.

E+ E- e+ e- — экспоненциальный формат.

$, -, +, /, (, ), :, пробел – отображаются своим знаком и не имеют специального применения.

\ — отображает следующий за ним символ.

* — повторяет следующий за знаком «звездочка» символ, до заполнения ширины столбца.

_  — пропуск на ширину следующего за ним символа.

«текст» — отображает текст, указанный в кавычках.

@ — место для текста.

[цвет] – отображает форматирование выбранным цветом. Доступны значения – черный, синий, голубой, зеленый, фиолетовый, красный, белый, желтый. При этом регистр символов при написании цвета не учитывается.

[цвет n] – цвет из палитры цветов, где n – число от 0 до 56.

[условный шаблон] – позволяет определять собственные критерии форматирования.

Коды форматирования даты/времени

Для определения даты и времени используются другие коды:

М – номер месяца, от 1 до 12, без ведущего нуля.

ММ – номер месяца, от 01 до 12, с ведущим нулем.

МММ – аббревиатура месяца, от «Янв» до «Дек».

ММММ – полное название месяца, от «Январь» до «Декабрь».

МММММ – первая буква названия месяца, от «Я» до «Д»

Д – номер дня месяца, от 1 до 31, без ведущего нуля.

ДД – номер дня месяца, от 01 до 31, с ведущим нулем.

ДД – аббревиатура дня недели, от «Пн» до «Вс».

ДДДД – полное название дня недели, от «Понедельник» до «Воскресенье»

ГГ – последние цифры года, например для 2017 – значение 17.

ГГГГ – полное значение года.

ч – номер часа, от 0 до 23, без ведущего нуля.

чч – номер часа, от 00 до 23, с ведущим нулем.

м – минуты, от 0 до 59, без ведущего нуля.

мм – минуты, от 00 до 59, с ведущим нулем.

с – секунды, от 0 до 59, без ведущего нуля.

сс – секунды, от 00 до 59, с ведущим нулем.

AM/PM – перевод времени в 12 часовой формат. По умолчанию используется 24 часовой формат.

Обратите внимание, в форматах описания времени, имеет значение регистр букв. Так значения ММ и мм отличаются только регистром, но для Excel первое определяет форматирование номера месяца, а второе минуты.