Применение пользовательских форматов
Создание пользовательских форматов
Excel позволяет создать свой (пользовательский) формат ячейки. Многие знают об этом, но очень редко пользуются из-за кажущейся сложности. Однако это достаточно просто, главное понять основной принцип задания формата.
Для того, чтобы создать пользовательский формат необходимо открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.
В поле Тип вводится пользовательские форматы, варианты написания которых мы рассмотрим далее.
В поле Тип вы можете задать формат значения ячейки следующей строкой:
[цвет]"любой текст"КодФормата"любой текст"
Посмотрите простые примеры использования форматирования. В столбце А — значение без форматирования, в столбце B — с использованием пользовательского формата (применяемый формат в столбце С)
Какие цвета можно применять
В квадратных скобках можно указывать один из 8 цветов на выбор:
Синий, зеленый, красный, фиолетовый, желтый, белый, черный и голубой.
Далее рассмотрим коды форматов в зависимости от типа данных.
Числовые форматы
Символ | Описание применения | Пример формата | До форматирования | После форматирования |
---|---|---|---|---|
# | Символ числа. Незначащие нули в начале или конце число не отображаются | ###### | 001234 | 1234 |
0 | Символ числа. Обязательное отображение незначащих нулей | 000000 | 1234 | 001234 |
, | Используется в качестве разделителя целой и дробной части | ####,# | 1234,12 | 1234,1 |
пробел | Используется в качестве разделителя разрядов | # ###,#0 | 1234,1 | 1 234,10 |
Форматы даты
Формат | Описание применения | Пример отображения |
---|---|---|
М | Отображает числовое значение месяца | от 1 до 12 |
ММ | Отображает числовое значение месяца в формате 00 | от 01 до 12 |
МММ | Отображает сокращенное до 3-х букв значение месяца | от Янв до Дек |
ММММ | Полное наименование месяца | Январь — Декабрь |
МММММ | Отображает первую букву месяца | от Я до Д |
Д | Выводит число даты | от 1 до 31 |
ДД | Выводит число в формате 00 | от 01 до 31 |
ДДД | Выводит день недели | от Пн до Вс |
ДДДД | Выводит название недели целиком | Понедельник — Пятница |
ГГ | Выводит последние 2 цифры года | от 00 до 99 |
ГГГГ | Выводит год даты полностью | 1900 — 9999 |
Стоит обратить внимание, что форматы даты можно комбинировать между собой. Например, формат "ДД.ММ.ГГГГ" отформатирует дату в привычный нам вид 31.12.2017, а формат "ДД МММ" преобразует дату в вид 31 Дек.
Форматы времени
Аналогичные форматы есть и для времени.
Формат | Описание применения | Пример отображения |
---|---|---|
ч | Отображает часы | от 0 до 23 |
чч | Отображает часы в формате 00 | от 00 до 23 |
м | Отображает минуты | от 0 до 59 |
мм | Минуты в формате 00 | от 00 до 59 |
с | Секунды | от 0 до 59 |
сс | Секунды в формате 00 | от 00 до 59 |
[ч] | Формат истекшего времени в часах | например, [ч]:мм -> 30:15 |
[мм] | Формат истекшего времени в минутах | например, [мм]:сс -> 65:20 |
[сс] | Формат истекшего времени в секундах | — |
AM/PM | Для вывода времени в 12-ти часовом формате | например, Ч AM/PM -> 3 PM |
A/P | Для вывода времени в 12-ти часовом формате | например, чч:мм AM/PM -> 03:26 P |
чч:мм:сс.00 | Для вывода времени с долями секунд |
Текстовые форматы
Текстовый форматов как таковых не существует. Иногда требуется продублировать значение в ячейке и дописать в начало и конец дополнительный текст. Для этих целей используют символ @.
ДО форматирования | ПОСЛЕ форматирования | Примененный формат |
---|---|---|
Россия | страна — Россия | "страна — "@ |
Создание пользовательских форматов для категорий значений
Все что мы описали выше применяется к ячейке вне зависимости от ее значения. Однако существует возможность указывать различные форматы, в зависимости от следующих категорий значений:
- Положительные числа
- Отрицательные числа
- Нулевые значения
- Текстовый формат
Для этого мы можем в поле Тип указать следующую конструкцию:
Формат положительных значений ; отрицательных ; нулевых ; текстовых
Соответственно для каждой категории можно применять формат уже описанного нами вида:
[цвет]"любой текст"КодФормата"любой текст"
В итоге конечно может получится длинная строка с форматом, но если приглядеться подробнее, то сложностей никаких нет.
Смотрите какой эффект это дает. В зависимости от значения, меняется форматирование, а если вместо числа указано текстовое значения, то Excel выдает "нет данных".
Редактирование и копирование пользовательских форматов
Чтобы отредактировать созданный пользовательский формат необходимо:
- Выделить ячейки, формат которых вы хотите отредактировать.
- Открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.
- Изменить строку форматирования в поле Тип.
Распространить созданный пользовательский формат на другие ячейки можно следующими способами:
- Использовать функцию копирования по образцу.
- Выделить ячейки, открыть окно Формат ячеек, на вкладке Число в списке Все форматы выбрать нужный формат и нажать ОК.
Для удаления установленного формата ячейки, можно просто задать другой формат или удалить созданный из списка:
Как установить числовые форматы в excel
На этом шаге мы рассматрим числовые форматы, которые можно использовать в Excel .
В Excel предусмотрено несколько числовых форматов. На рис. 1 показано диалоговое окно Формат ячеек , содержащее несколько вкладок. Для форматирования чисел используется вкладка Число .
Рис. 1. Диалоговое окно Формат ячеек . Вкладка Число
Существует несколько способов вызова диалогового окна Формат ячеек . Прежде всего необходимо выделить ячейку или ячейки, которые надо отформатировать, а затем выполнить одно из приведенных действий:
- Выберать команду Формат | Ячейки .
- Щелкнуть правой кнопкой мыши на выделенных ячейках и из контекстного меню выберать команду Формат ячеек .
- Воспользоваться комбинацией клавиш Ctrl + 1 .
Во вкладке Число диалогового окна Формат ячеек предусмотрено 12 категорий числовых форматов. При выборе соответствующей категории из списка правая сторона панели изменяется так, чтобы отобразить соответствующие опции. Например, на рис. 2 показан вид диалогового окна после выбора категории Числовой .
Рис. 2. Категория Числовой
Категория Числовой представлена тремя опциями: число отображаемых десятичных знаков, использование разделителя тысяч и способ отображения отрицательных чисел. Обратите внимание на то, что в окне списка Отрицательные числа представлено четыре варианта отображения отрицательных чисел (два из них выделены красным цветом), которые изменяются в зависимости от количества десятичных знаков и использования символа разделителя тысяч. В верхней части вкладки будет показан образец отформатированного числа. Выбрав все необходимые опции, щелкните на кнопке ОК , чтобы применить числовой формат к выделенным ячейкам.
Список категорий числовых форматов:
- Общий. Этот формат принят по умолчанию. При его выборе числа отображаются в виде целых чисел, десятичных дробей или в экспоненциальном формате, если число слишком большое и не помещается в ячейке.
- Числовой. Этот формат позволяет указать число десятичных знаков, определить использование разделителя групп разрядов и способ отображения отрицательных чисел (со знаком "минус", красным цветом или красным цветом со знаком "минус").
- Денежный. Этот формат позволяет указать число десятичных знаков, использование знака денежной единицы и способ отображения отрицательных чисел (со знаком "минус", красным цветом или красным цветом со знаком "минус"). В этом формате всегда используется разделитель групп разрядов.
- Финансовый. Этот формат отличается от денежного тем, что знаки денежной единицы всегда выровнены по вертикали.
- Дата. Эта категория позволяет выбрать один из 15 форматов отображения дат.
- Время. Эта категория предоставляет на выбор 8 форматов отображения времени.
- Процентный. Эта категория позволяет выбрать число десятичных знаков. Символ процента отображается всегда.
- Дробный. Эта категория позволяет выбрать один из 9 дробных форматов отображения чисел.
- Экспоненциальный. В этом формате число всегда отображается с буквой Е . Можно выбрать число десятичных знаков для отображения.
- Текстовый. Применение текстового формата к числовому значению заставляет Excel рассматривать это число как текст.
- Дополнительный. В эту категорию включено четыре дополнительных числовых формата ( Почтовый индекс , Индекс+4 , Номер телефона и Табельный номер ).
- Все форматы. Эта категория позволяет создавать собственные числовые форматы, не входящие ни в одну из других категорий.
На рис. 3 показаны примеры чисел, к которым применены все перечисленные категории форматирования.
Рис. 3. Примеры применения числовых форматов к значениям
Примечание . Если в ячейке находится ряд символов "решетка" (#########), то это значит, что столбец недостаточно широк для отображения числа в выбранном формате. Необходимо увеличить ширину столбца или изменить числовой формат.
На следующем шаге мы рассмотрим числовые форматы, определяемые пользователем.