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

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

Создание пользовательских форматов

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 Дек.

Форматы времени

Текстовые форматы

Текстовый форматов как таковых не существует. Иногда требуется продублировать значение в ячейке и дописать в начало и конец дополнительный текст. Для этих целей используют символ @.

ДО форматирования	ПОСЛЕ форматирования	Примененный формат
Россия	страна — Россия	«страна — «@

Создание пользовательских форматов для категорий значений

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

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

Формат положительных значений ; отрицательных ; нулевых ; текстовых

Соответственно для каждой категории можно применять формат уже описанного нами вида:

[цвет]»любой текст»КодФормата»любой текст»

В итоге конечно может получится длинная строка с форматом, но если приглядеться подробнее, то сложностей никаких нет.

Смотрите какой эффект это дает. В зависимости от значения, меняется форматирование, а если вместо числа указано текстовое значения, то Excel выдает «нет данных».

Редактирование и копирование пользовательских форматов

Чтобы отредактировать созданный пользовательский формат необходимо:

1. Выделить ячейки, формат которых вы хотите отредактировать.
2. Открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.
3. Изменить строку форматирования в поле Тип.

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

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

[expert_bq id=»1570″]Последний пример может вызвать затруднение, но, обратите внимание, что в коде формата ячейка В4 отсутствует символ , который определяет место, где должен отразиться введенный в ячейку текст. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq]

• Выделите мышкой часть таблицы, которую хотите отправить на печать.
• Переходим в настройку печати Excel через меню или используем комбинацию CTRL+P.
• В первом списке выбираем пункт «Напечатать выделенный фрагмент».

Как вставить в книгу Excel новые листы? Как сделать копии листа в Excel? Как добавить новые листы с заданными именами?

1. Выделить ячейки, формат которых вы хотите отредактировать.
2. Открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.
3. Изменить строку форматирования в поле Тип.

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

Формат	Описание применения	Пример отображения
М	Отображает числовое значение месяца	от 1 до 12
ММ	Отображает числовое значение месяца в формате 00	от 01 до 12
МММ	Отображает сокращенное до 3-х букв значение месяца	от Янв до Дек
ММММ	Полное наименование месяца	Январь — Декабрь
МММММ	Отображает первую букву месяца	от Я до Д
Д	Выводит число даты	от 1 до 31
ДД	Выводит число в формате 00	от 01 до 31
ДДД	Выводит день недели	от Пн до Вс
ДДДД	Выводит название недели целиком	Понедельник — Пятница
ГГ	Выводит последние 2 цифры года	от 00 до 99
ГГГГ	Выводит год даты полностью	1900 — 9999