Excel 19. Числовой формат

По окончании этого урока вы сможете:

1. Пользоваться числовым форматом
2. Копировать формат по образцу
3. Создавать собственный формат чисел

1. Числовой формат ячейки

Данные в ячейках могут иметь различный смысл: текст, числа, дроби, даты, проценты. Вот пример: какой процент от 20000 составляет 4000?

Но догадаться, что речь идет о процентах, можно только из объяснения или из подписи.

Если мы видим, что рядом с числом стоит значок «₽», то сразу понятно, что речь идет о рублях, а значок «%» говорит о процентах. Если нам надо складывать минуты, то речь идет о 60-тиричной системе счисления, а если часы – то 12-тиричной системе счисления.

Excel, как программа, работает с числами, поэтому важно указать программе какой формат числе сейчас в работе.

Задается формат ячейки на ленте Главная → группа команд Число → кнопка выпадающего меню Числовой формат:

1. Общий – только для чисел и текста
2. Числовой – для чисел с возможностью десятичных дробей
3. Денежный – понятно, что рубли фунты, тугрики. Причем назначение формата британский фунт будет учитывать всю сложность британской системы счисления денежных знаков
4. Финансовый – я не финансист и поэтому не знаю, чем этот формат отличается от денежного
5. Краткий формат даты – день, месяц и год показаны числами
6. Длинный формат даты – месяц прописан полностью с учетом нашей русской орфографии, за что большое спасибо переводчикам
7. Время – сразу обращаем внимание на синтаксис написания времени – часы, минуты и секунды разделяются двоеточием
8. Процентный – пояснять не надо
9. Дробный – показывает простые дроби
10. Экспоненциальный ‒ используется для отображения числа в экспоненциальном представлении. При этом происходит замена части числа на E + n, где E обозначает экспоненциальное представление, то есть умножение предшествующего числа на 10 в степени n (честно списала с официального руководства пользователя)
11. Текстовый – пояснять не надо.

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

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

1. Выделить ячейку (или диапазон ячеек), к которым нужно присвоить формат.
2. Нажать на кнопке со стрелкой в поле списка Формат чисел и щелкнуть на нужном формате.

Команды с присвоением формата Денежный и Процент находятся на ленте Главная → группа команд Число:

1. Денежный
2. Процент
3. Формат с разделителем
4. Увеличить разрядность (увеличить количество знаков после запятой в десятичной дроби)
5. Уменьшить разрядность (уменьшить количество знаков после запятой в десятичной дроби)

Формат с разделителем очень полезная опция – позволяет создать в числах промежутки между разрядами:

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

Понять и запомнить!

Числовой формат – это идентификатор ячейки, который показывает тип данных. При этом в строке формул идентификатор не отображается.

Если мы сложим 54+32, то получим в результате 86, а если мы сложим 54 мин+32 мин, то получим 1 час 26 минут. Так что идентификатор ячейки очень важен при вычислениях. Вот небольшой кусочек из моего отчета по созданию дистанционного курса:

Поскольку формат ячеек «Время», то общая длительность курса подсчитана в шестидесятеричной системе (1 час = 60 минут и 1 минута = 60 секунд спасибо древним шумерам за изобретение этой системы счисления).

Числовой формат можно настроить. Для этого работаем с уже знакомым окном «Формат ячеек». Открыть это окно можно несколькими способами

1. Команда «Формат ячеек» из контекстного меню
2. Команда «Другие форматы ячеек» из выпадающего меню «Числовой формат»
3. Лента Главная → группа команд Число → кнопка группы

Рассмотрим пример числового формата Дата:

Как видите, выбор форматов даты очень обширен.

А вот пример выбора денежного формата:

2. Копирование форматов в другие ячейки

Применить к данным формат, уже использованный в какой-то части рабочего листа, можно с помощью кнопки «Формат по образцу» (выделяем ячейку как образец для формата → лента Главная → группа команд Буфер обмена → щелчок ЛМ по команде Формат по образцу → щелчок ЛМ по любой другой ячейке):

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

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

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

Не смотря на большое количество форматов, часто возникает необходимость в создании собственных форматов, например, в диапазоне надо указать «кг» или «м».

Для создания собственного формата открываем диалоговое окно «Формат ячейки» и выбираем «(все форматы)»:

Не будем пугаться, а попробуем разобраться.

0 (ноль)	одно обязательное знакоместо (разряд), т.е. это место в маске формата будет заполнено цифрой из числа, которое пользователь введет в ячейку. Если для этого знакоместа нет числа, то будет выведен ноль. Например, если к числу 135 применить формат 0000, то получится 0135, а если к числу 3,14159265358979 применить формат 0,00 – получится 3,14
(решетка)	одно необязательное знакоместо – примерно то же самое, что и ноль, но если для знакоместа нет числа, то ничего не выводится
(пробел)	используется как разделитель групп разрядов по три между тысячами, миллионами, миллиардами и т.д.
[ ]	в квадратных скобках указывается цвет шрифта. Разрешено использовать следующие цвета: черный, белый, красный, синий, зеленый, жёлтый, голубой

Сделаем пользовательский формат для отображения мобильных телефонов. В поле «Тип» записываем выражение:

Тип	+7пробел(000)пробел000-00-00

Получаем:

Это мой номер телефона, звоните – буду рада пообщаться.

Что касается текста, то существует правило:

Понять и запомнить!

Любой пользовательский текст (кг, чел, шт и тому подобные) или символы (в том числе и пробелы) – надо обязательно заключать в кавычки

Тип	# ##0″ кг»

Перед «кг» ставим пробел.

Если мы хотим добавить десятичные дроби, то:

Тип	# ##0,00″ кг»

То получим в результате:

75, 00 кг

34,00 кг

54,00 кг

К ячейке можно применить до 4 видов числовых форматов:

1 позиция	2 позиция	3 позиция	4 позиция
Если в ячейке положительное число	Если в ячейке отрицательное число	Если в ячейке «0»	Если в ячейке текст

Форматы разделяются точкой с запятой (и никаких пробелов!)

Например. Если в поле Тип мы введем:

Тип	«да»;»нет»;»да»

то получим такой результат:

Усложним задачу и поработаем с цветом:

Тип	[Красный]+0″°С»;[Синий]-0″°С»;[Зеленый]0″°С»

Получаем такую красоту:

Вообще пользовательский формат – это очень обширная тема. Надеюсь, собраться с духом и написать отдельную статью.

Понять и запомнить!

Пользовательский формат можно удалить, встроенный формат – нельзя!

Теперь вы сможете:

1. Пользоваться числовым форматом
2. Копировать формат по образцу
3. Создавать собственный формат чисел