Формат ячеек в Excel: где находится и готовые коды
Ctrl+1, какие бывают форматы и почему формат не меняет само значение. Коды для рублей, нулей, телефонов и отрицательных чисел — копируются одной кнопкой.
Коротко
Где находится: выделите ячейки и нажмите Ctrl+1 — откроется окно «Формат ячеек». То же окно — правой кнопкой → Формат ячеек или Главная → Число, стрелка в углу группы.
Главное, что надо понимать: формат меняет вид числа, но не само число. В ячейке с форматом «0» лежит 12,6, а видно 13 — и в сумме будет 12,6. Отсюда половина «ошибок округления»: сумма видимых чисел не сходится с итогом, потому что складываются настоящие.
Вкладка «Число» — основные форматы:
- Общий — как ввели, так и показано; Excel сам решает, число это, дата или текст.
- Числовой и Денежный — знаки после запятой и разделитель разрядов.
- Дата и Время — внутри это обычное число: сутки равны единице.
- Процентный — умножает на экране на сто: 0,2 выглядит как 20 %.
- Текстовый — всё, что введено дальше, хранится как текст, даже цифры. Формулы в такой ячейке не считаются.
- (все форматы) — свой код, как в блоке ниже.
То же самое формулой в Excel
# ##0,00 ₽ — код формата: разряды, копейки, знак рубля — вставлять в «(все форматы)» и проверить на миллионе# ##0;-# ##0;;@ — код формата: ноль показывается пустой ячейкой, текст — как есть# ##0;[Красный](# ##0) — код формата: отрицательные числа красным и в скобках, без минуса00000 — код формата: ведущие нули — 42 выглядит как 00042+7 (000) 000-00-00 — код формата: телефон из одиннадцати цифр, введённых без пробелов=ТЕКСТ(A2;"ДД.ММ.ГГГГ") — формула: дата текстом в нужном виде — когда её надо склеить с другим текстомПошагово
Поставить свой формат
- Выделите ячейки, нажмите Ctrl+1.
- Вкладка Число → внизу списка (все форматы).
- В поле Тип вставьте код из блока выше и нажмите ОК. В окне сразу видно, как будет выглядеть первое выделенное число.
Как устроен код формата. До четырёх частей через точку с запятой: положительные; отрицательные; ноль; текст. Пустая часть означает «не показывать». 0 — обязательная цифра, # — цифра, если она есть, пробел между ними — разделитель разрядов, запятая — дробная часть. Цвет пишется в квадратных скобках первым: [Красный], [Синий].
Скопировать формат на другие ячейки — кистью Формат по образцу на вкладке «Главная» или через Специальную вставку → Форматы.
Убрать формат — Главная → Очистить → Очистить форматы. Значения останутся, вид вернётся к «Общему».
Где обычно ломается
- Поменял формат на числовой, а число не считается. Формат не превращает текст в число задним числом: если цифры вводили в текстовую ячейку, они так текстом и остались. Лечится повторным вводом, умножением на единицу через Специальную вставку или формулой
=--A2. - Формат не меняется вовсе. Чаще всего лист защищён (пункт «Формат ячеек» тогда серый) или у ячеек стоит условное форматирование — оно рисуется поверх обычного.
- Вместо числа решётки ####. Число не помещается в столбец. Расширьте столбец двойным щелчком по границе заголовка. У даты решётки означают ещё и отрицательное значение.
- Код с точкой не работает. В русской версии дробная часть в коде пишется запятой, разряды — пробелом. Коды из англоязычных статей (
#,##0.00) надо перевести. - Миллион выглядит как «1000 000». Пробел в коде — разделитель разрядов, только если он совпадает с тем, что задан в Windows. Если в системе разделитель — неразрывный пробел, обычный пробел из скопированного кода Excel считает просто символом, и цифры сверх трёх групп слипаются. Надёжный путь: выберите формат Числовой с галочкой Разделитель групп разрядов, перейдите в (все форматы) — там уже стоит код с правильным разделителем, — и допишите к нему остальное. Проверяйте формат на числе от миллиона: на тысячах ошибку не видно. Мы сами на этом попались — в наших файлах так выглядели все суммы от миллиона, пока мы не поставили проверку.
- В Google Таблицах свои форматы задаются через Формат → Числа → Пользовательский числовой формат, коды почти те же, но цвета пишутся по-английски:
[Red].