Пользовательские форматы в Excel

При необходимости Вы можете легко добавить к стандартным числовым форматам Excel свои собственные. Для этого выделите ячейки, к которым надо применить пользовательский формат, щелкните по ним правой кнопкой мыши и выберите в контекстном меню команду Формат ячеек (Format Cells) - вкладка Число (Number), далее - Все форматы (Custom)

custom-formats1.png

В появившееся справа поле Тип: введите маску нужного вам формата из последнего столбца этой таблицы:

custom-formats2.png

 Как это работает...

На самом деле все очень просто. Как Вы уже, наверное, заметили, Excel использует несколько спецсимволов в масках форматов:

  • 0 (ноль) - одно обязательное знакоместо (разряд), т.е. это место в маске формата будет заполнено цифрой из числа, которое пользователь введет в ячейку. Если для этого знакоместа нет числа, то будет выведен ноль. Например, если к числу 12 применить маску 0000, то получится 0012, а если к числу 1,3456 применить маску 0,00 - получится 1,35.
  • # (решетка) - одно необязательное знакоместо - примерно то же самое, что и ноль, но если для знакоместа нет числа, то ничего не выводится
  • (пробел) - используется как разделитель групп разрядов по три между тысячами, миллионами, миллиардами и т.д.
  • [ ] - в квадратных скобках перед маской формата можно указать цвет шрифта. Разрешено использовать следующие цвета: черный, белый, красный, синий, зеленый, жёлтый, голубой.

Плюс пара простых правил:

  • Любой пользовательский текст (кг, чел, шт и тому подобные) или символы (в том числе и пробелы) - надо обязательно заключать в кавычки.
  • Можно указать несколько (до 4-х) разных масок форматов через точку с запятой. Тогда первая из масок будет применяться к ячейке, если число в ней положительное, вторая - если отрицательное, третья - если содержимое ячейки равно нулю и четвертая - если в ячейке не число, а текст (см. выше пример с температурой).

Ссылки по теме

 


Страницы: 1  2  
21.02.2013 15:54:45
Уважаемый Николай
С помощью пользовательского формата [<=10]"+";Основной мне удалось следующее:
при вводе любой цыфры от 1 до 9 появляется  знак "+". Скажите а можно сделать что при нажатии клавиш с буквами тоже появлялся знак "+"
21.02.2013 22:40:30
Георгий, попробуйте:  
[<=10]"+";Основной;0;"+"
13.03.2013 12:33:46
Добрый день!
есть значения которые постоянны, а есть которые вводятся.

16/01-2345
проблема в следующем: меняется значение 4 и 5 символов (может быть от 1 до 16), и значения после тире (могут быть от одного до пяти знаков).

когда была задача менять символы после тире, я с ней легко справился, а здесь пока никак.
За ранее спасибо!
14.03.2013 22:08:54
Андрей, а в чем вопрос-то? И как это связано с темой статьи?
15.03.2013 15:18:13
Николай, вопрос в том как правильно задать формат.
нужно, чтобы 16/0 вводилось автоматически, а 1-2345 или 4-96 вводилось вручную. в итоге получаем 16/01-2345 или 16/04-96.
но когда в экселе ставишь тире, ничего не получается.
16.03.2013 14:22:06
При вводе в ячейку 12345 пользовательский формат "16/0"0-0000 выдаст вам 16/01-2345. Оно?
18.03.2013 16:28:40
все так, но не так. если номер будет состоять из двух или трех цифр данный формат не подойдет :(
к примеру если номер 16/01-23, то получим 16/00-0123
10.09.2014 08:14:56
Если я все правильно понял то вам нужен такой формат, чтобы после тире было от одного до пяти знаков и до тире еще один знак. Сразу скажу что в форматах я такого добиться тоже не смог, но сделал это при помощи формулы

=ЕСЛИ(A1="";"";СЦЕПИТЬ("16/0";ЛЕВСИМВ(A1;1);"-";ПСТР(A1;2;5)))

Если вы ведете таблицу для печати то перед печатью изначальный столбец (забиваемый вручную[в моем примере "А"]) можно скрывать, удобнее через группировку excel 2007 и старше (вкладка "Данные" - Группировать)
18.03.2013 14:44:33
Николай, огромная просьба - подскажите как мне получить следующий формат: 012/34/56/789 - вводимый текст - 0123456789.
Казалось бы очевидно, но у меня не получается.
Маска 000-00-00-000 дает 012-34-56-789, но маска 000/00/00/000 не работает.
18.03.2013 14:51:16
Я, в принципе, нашел альтернативу - ###"/"##"/"##"/"###, но пустая ячейка с данным форматом будет выглядеть так - ///.
08.05.2013 16:13:50
Леонид, попробуйте задать пустые форматы для отрицательных и нулевых значений через точку с запятой - вот так:
###"/"##"/"##"/"###;;;
08.05.2013 17:09:01
Работает. Спасибо огромное!
08.05.2013 15:03:21
Николай, добрый день!
Возникает проблема с выгружаемыми списками (из 1С прошлого века :)), где есть даты рождения в формате ДД.ММ.ГГГГ (формат ячейки при этом "числовой"), при попытке сделать в ячейке отображение только ГГГГ, ничего не меняется. Точнее меняется, но только после "прощелкивания" каждой ячейки... а их около 2,5 тыс...
Пока обходимся разделением текста по столбцам, но на будущее может есть какой-то способ победить данный недуг?
08.05.2013 16:08:23
Легко. В любую пустую ячейку вводите 1 и копируете ее. Выделяете все ячейки с кривыми датами, правой кнопкой - Специальная вставка - Умножить - ОК. Псевдо-даты превратятся в нормальные, теперь их можно форматировать обычным образом.
13.05.2013 15:36:21
Эх, позор мне, позор! :oops: Совсем забыла об этой замечательной хитрости...
Спасибо, Николай!
14.05.2013 09:53:25
Не за что ;)
19.06.2015 07:50:20
Николай, подскажите, как эту операцию выполнить с помощью макроса. Запись макроса делает не так, как ручками
12.06.2013 14:32:11
Николай, подскажите пожалуйста, как решить такую проблему:

необходимо, чтобы номера из 16 символов выводились в удобной форме - группами по 4 цифры.
Ввожу маску:
0000" "0000" "0000" "0000

И всё бы ничего, но только последнюю цифру Excel настырно заменяет на ноль. Пробовал и через "#". Но безрезультатно. Не хочет он 16-й знак усваивать в цифровом формате.

Пока получается либо в текстовом формате неразрывно 16 символов, либо группами по 4, но с нулём в 16-ой позиции.

Совсем уже замучился...
13.06.2013 09:19:26
Excel не умеет хранить и обрабатывать числа длиннее 15 разрядов. Чтобы хранить ваше 16-ти значное число нужно предварительно переключить формат ячейки в текстовый. Пользовательские форматы при этом не работают, т.к. задуманы для чисел.
13.06.2013 20:30:39
Ну всё. Теперь хоть прекращу бесплодные попытки родить какое-нибудь решение.
Спасибо, Николай!
03.08.2013 00:07:22
Николай, добрый день!
Подскажите пожалуйста, как спрятать запятую, то есть чтобы было  (525 pyб 31 кoп) без запятой.
У меня получилось вот так: 525 pyб, 31 кoп  маска: ###" руб",_ ##" коп"
04.08.2013 16:32:26
Виталий, не думаю, что это возможно в пределах одной ячейки с помощью форматов. Я бы использовал формулу
=ОТБР(A1)&" руб "&ОКРУГЛ((A1-ОТБР(A1))*100;0)&" коп"

(подразумевая, что исходная сумма лежит в A1).
09.10.2013 08:45:38
Спасибо, всё получилось, добавил рядом ячейку и вывел свой результат, поменяв цвет шрифта на белый, потом сделал как вы предложили, в соседней ячейке отобразил свой результат (который теперь не виден из-за белого шрифта) вашей формулой, 2 ячейки поместил в одну границу, получил конечный (видимый) результат тот, что мне надо (типа одна ячейка, один результат).
В одной границе 2 ячейки, 2 формулы, визуально всё как в одной ячейке.
08.10.2013 21:00:07
Николай, подскажите пожалуйста, а можно ли сделать так чтобы результат всегда выводился в инженерном формате типа 1,2E-6; 6E-3 и т.п.?
09.10.2013 08:44:09
Денис, можно просто заранее поставить для ячейки или диапазона такой формат.
09.10.2013 17:45:30
Прошу прошения за глупый вопрос! А как это сделать? Если я устанавливаю экспоненциальный формат, то степени числа всегда скачут ну например получается 1,2Е-8 ,а надо 0,012Е-6 или 12Е-9.
28.10.2013 14:38:06
Боюсь, что показатель степени в Excel обычными средствами задать не получится.
05.12.2013 10:01:00
Помогите, пожалуйста, как сделать маску ввода из 20 цифр в формате "19 пробел 1"
при использовании "??????????????????? ?" выдает такое "8970101282381510000 0", т.е. в конце все переводит в  нули, а нужен текст ?
29.12.2013 10:37:56
Посмотрите комментарии выше, Марина - Excel не умеет хранить и обрабатывать числа длиннее 15 разрядов. Единственный вариант для этого - установить в ячейке текстовый формат., но при этом не работают пользовательские форматы, т.к. они придуманы для чисел.
02.01.2014 20:14:17
Николай, как можно добавить в список "все форматы" созданный пользовательский формат, что бы не создавать его каждый раз вручную?
04.01.2014 11:12:55
Простое решение: просто копируйте ваш формат с помощью инструмента Формат по образцу (кисточка) из книги в книгу - это будет проще всего.
Сложное решение: создайте новую книгу, добавьте в нее ваш формат, сохраните книгу как шаблон (расширение xltx) и в будущем создавайте новые книги двойным щелчком по файлу шаблона. Также можно попробовать сохранить шаблон в папку автозапуска C:\Program Files\Microsoft Office\Office14\XLSTART с именем book.xltx. Тогда Excel должен автоматически создавать новые книги по этому шаблону, т.е. уже с вашим форматом внутри.
02.01.2014 20:32:41
Также интересует, каким образом настроить тот или иной формат для всех ячеек по умолчанию?
04.01.2014 11:15:16
См. выше второй вариант. Если настроить формат в шаблоне, то он будет у всех ячеек по умолчанию.  Для шаблона листа используйте имя sheet.xltx.
Однако, не советую так делать или применять осторожно - это может сильно утяжелить и замедлить файл.
12.02.2014 08:38:05
Какой формат применить к столбцу в котором будут перечислены размеры товара 37 38 41 ... до 16 шт и какой формулой их можно будет суммировать по штучно, 37 38 41 получить 3 в следующем столбце. XL-2013
Размера получаются двух видов, 34 и 34,5 , при этом строку содержащую 3234 ( Без пробела случайно, "кривые руки" формула должна читать как два размера 32 34, т.е. 3234 34,5 35 должно получиться 4. Или хотя бы выводить ошибку на присутствие варианта (отсутствие пробела) 3234
28.03.2014 04:29:49
Доброго времени суток .
Подскажите как извлечь число из "  37.14 грн. " , для арифметических действий.
Зарание благодарен .
04.06.2014 19:35:43
Используйте формулу:
=ПОДСТАВИТЬ(ЛЕВСИМВ(ПСТР(A1;МИН(ЕСЛИ(ЕЧИСЛО(ПОИСК({0:1:2:3:4:5:6:7:8:9};A1));ПОИСК({0:1:2:3:4:5:6:7:8:9};A1);""));99);ПОИСК(" ";ПСТР(A1;МИН(ЕСЛИ(ЕЧИСЛО(ПОИСК({0:1:2:3:4:5:6:7:8:9};A1));ПОИСК({0:1:2:3:4:5:6:7:8:9};A1);""));99)));".";",";1)
10.09.2014 09:39:21
Или попроще используя три функции ПСТР - для выдергивания нужных знаков, СЦЕПИТЬ для их сливания в единый текст и ЗНАЧЕН для перевода текста в число

=ЗНАЧЕН(СЦЕПИТЬ(ПСТР(A1;1;2);",";ПСТР(A1;4;2)))
или (если сумма будет превышать десятки) фунцию сцепить заменил оператором конкатенации (&) Должна работать, вся формула построена на запятой, разделяющей целое и дробное (например руб./коп.) и после запятой пишет только две цифры
=ЗНАЧЕН(ПСТР(A1;1;(НАЙТИ(",";A1))-1)&","&ПСТР(A1;(НАЙТИ(",";A1))+1;2))
04.06.2014 16:21:57
Николай, добрый день! Нужна Ваша помощь! Уже всяко перепробовал - не получается.

В ячейке записана дата со временем: 04.06.2014  12:00:00. Формат ячейки выглядит так: [$-419]Д МММ ч:мм;@ (отображается как 4 июн 12:00).

Проблема в том, что если записать в ячейку просто дату без времени, то она отображается так: 4 июн  0:00. Т.е. как будто 12 ночи.
Вопрос, можно ли скорректировать формат ячейки так, чтобы, если в ней записана дата без времени, то нули бы не отображались?

Можно, конечно, записать формат так: [$-419]Д МММ;@
Но проблема в том, что для некоторых записей необходимо вводить и дату и время, а для некоторых только дату.
Ещё добавлю, что я точно не буду записывать в ячейку время как 12 ночи (например, 04.06.2014 00:00), т.к. эта ячейка используется для "напоминалки" о задачах по работе, а рабочее время с 9 до 18.

Очень рассчитываю на Вашу помощь!
05.06.2014 19:06:54
Вопрос решён! Если кому-нибудь интересно, ответ здесь: http://forum.msexcel.ru/index.php/topic,10245.0.html
16.09.2014 18:10:16
Как говорится - "мелочь, а приятно!"))
23.10.2014 21:41:11
Может подскажите, есть группа ячеек содержащие различные значения такие как:
123.00
123.10
123.75960
Можно ли с помощью одного формата привести эти значения в следующий вид:
123
123.1
123.76
т.е. округлить до второго знака после запятой, и если второй или и первый и второй знак равняются нулю, убрать их?
26.12.2014 10:23:52
Attribute VB_Name = "Module1"
Sub Макрос1()
Attribute Макрос1.VB_ProcData.VB_Invoke_Func = "q\n14"
'
' Макрос1 Макрос
On Error Resume Next
   For Each Cell In Selection.Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
       Cell.Value = Application.Round(Cell.Value, 2)
   Next
End Sub

Вот таким макросом решил проблему
17.12.2014 19:59:22
Здравствуйте, возможно ли стандартными средствами excel сделать, чтобы при вводе отображалась маска, то есть, например при вводе телефона отображалась маска +7(___)___-__-__
или хотя бы осуществлялся контроль данных по маске, то есть, чтобы, если в ячейку с форматом +7(000)000-00-00 пользователь введет, например какой-то текст, чтобы выдавалось сообщение об ошибке
22.08.2016 16:50:35
Данные-Проверка данных-Сообщение для ввода/Сообзение об ошибке, можно ещев параметрах длину номера в символах указать от и до. Но до сих пор не могу после этого сообщения автоматически очистить ячейку
22.01.2015 12:09:24
Добрый день, Николай!
Подскажите как создать формат аналогичный процентному, но без знака % в конце. Т.е. форматирование должно заключаться в умножении значения на 100. Ну и для простоты без разрядов после запятой.
Обратный аналог тоже есть - форматы в тысячах "# ### " - фактически это же деление на 1000 и отбрасывание дробной части.

Не знаете, как сделать подобный формат?
16.02.2016 15:56:47
Игорь, добрый день
Не решили свой вопрос? Столкнулась с той же проблемой
Здравствуйте, подскажите пожалуйста, как при введении числе 2 она была красной, а при введении 5-зеленой?
04.03.2015 08:35:00
Екатерина, для этого нужно использовать условное форматирование.
01.06.2015 17:17:43
Не обязательно использовать условное форматирование. можно использовать вот такой формат ячейки:
[Красный][=2]0;[Зеленый][=5]0;#
05.03.2015 12:54:00
Добрый день подскажите пожалуйста как добавить формат типа : "текст постоянный" вводимый текст "текст постоянный" числовое значение "текст постоянный" чтобы заполнять данные через пробел  
24.04.2015 10:07:08
Николай здравствуйте!

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

Ячейка А1 - ввод текстовых значений (например "п", "р","$"
Ячейка В1 - ввод числовых значений (это точность числа знаков после запятой для С1, может быть: 1; 0,1; 0,01; 0,001; 0,0001)
Ячейка С1 - ввод числовых значений (необходимое форматированию число)

Вариант 1:
если А1="п", то ячейка С1= "п # ###"
если А1="р", то ячейка С1= "р # ###,#", если целое число "р # ###" (или чтобы запятая не выводилась)
если А1="$", то ячейка С1= "$ # ##0,00##"

Вариант 2:
если А1="п", то ячейка С1= "п # ###" точность В1
если А1="р", то ячейка С1= "р # ###" точность В1
если А1="$", то ячейка С1= "$ # ###,#" точность В1

Спасибо.
08.05.2015 11:18:01
Добрый день!
А можно ли задать формат, что если нет дробной части -- то выводится само число, а если есть -- то две цифры. Формат типа: 0,## дает ненужную запятую пр отсутвии дробной части...
12.05.2015 21:43:55
Здравствуйте! Помогите, пожалуйста, по такому вопросу. Имеем маску +7(#) 000-00-00, а нужна +7 (#) 000-00-00, как создать пробел? При создании своего формата итог упорно получается без пробела между цифрой 7 и скобкой. Проблема возникла из-за поиска. У меня телефоны в ячейку записываются в формате +7(#) 000-00-00, а копирую и ищу в формате +7 (#) 000-00-00. Эксель просто не находит их в ячейке из-за отсутствия пробела.:cry:
01.06.2015 14:25:46
Доброго времени суток, Лариса. Ваш формат должен выглядеть вот так+7_ (#) 000-00-00, т.е. после +7 должен быть  знак "_" нижнее подчеркивание, а после него должен быть пробел и потом скобка.
01.06.2015 15:55:42
Александр, всё получилось!!! :) Спасибо Вам за помощь!!!
21.05.2015 13:27:12
Николай, добрый день
у меня почему-то не получилось применить формат в виде "00000", т. е в ячейке необходимо хранить число из 20 символов (банковский счет), например 12345678901234567890. При вводе в яч число, естественно сокращается, создаю свой формат, как Вы тут советуете из 20-ти нулей, применяю - в ячейке остаются 15 символов, остальные 5 заменяются нулями...
01.06.2015 17:02:36
Доброго времени суток. Это не у вас не получается применить такой шаблон, а у Excel. Дело в том что Excel не принемает числа более 15ти знаков как число, об этом есть официальная информация на сайте Мелкомягких, вот  тут, там есть графа Точность представления чисел, разрядов, не более 15. Единственный вариант это сохранять данное число как текст, для этого перед набором числа надо поставить апостроф - '  и тогда данная ячейка будет считаться текстовой и число сохранится как есть, но стоит преобразовать формат в числовой, число опять станет 15 знаков а все что больше 15ти знаков превратится в нули и если опять преобразовать в текст то нули так и остануться.
01.06.2015 14:18:14
Добрый день.
Никак не могу побороть следующее. В одну ячейку, но в 2 строки занесено 2 № телефона в формате 11 цифр подряд без пробелов, и каких либо знаков, как сделать формат ячейки чтобы оба №№ отображались в формате 0(000) 000-00-00?  
31.07.2015 10:09:03
Добрый день!
Подскажите пожалуйста,как сделать чтоб информация выводилась вот в таком виде: A1/A2 (тоесть A1 и A2 это ячейки в которых есть числа)?
01.09.2015 10:06:11
Павел, тут формат не нужен, а нужна формула склеивания:
=A1&"/"&A2
17.08.2015 09:10:07
Николай, а есть ли возможность отображения, например, полного названия месяца, при вводе его номера. То есть при вводе 1 будет отображаться "Январь"; 2 - "Февраль" и т.д. Можно использовать только формат числа (понятно, что все это можно сделать выбором соответствующего значения из массива).
17.08.2015 09:27:47
Конкретизирую свой вопрос, пример с месяцами не очень удачный (есть формат "ММММ"), пусть 1 - "Арбуз", 2 - "Банан" ... Выбираемых значений, соответствующих определенному числу не много, до 20-ти
01.09.2015 10:09:23
Форматом точно нет, а простой формулой можно легко. Если в А1 у вас номер, то будет:
=ИНДЕКС({"Банан";"Арбуз";"Слива"};A1)
01.09.2015 09:54:09
Николай, а как столбец, уже заполненный уже  номерами формата 7917-111-44-22 , перевести в формат 79171114422 ???:)
01.09.2015 10:07:40
Если дефисы сделаны форматом (т.е. на самом деле в ячейке их нет), то достаточно будет поменять формат на Общий или Числовой.
Если дефисы в ячейках есть физически, то их можно оптом удалить с помощью замены (Ctrl+H, найти дефис, заменить на ничего).
01.09.2015 11:13:51
Николай, спасибо. только почему то и дефисы не находит
01.09.2015 12:03:24
Тогда выделите один из этих "дефисов" мышью в строке формул, скопируйте Ctrl+C, откройте окно замены Ctrl+H и вставьте скопированный символ в первое поле (Ctrl+C).
01.09.2015 12:19:12
Николай, спасибо, получилось!!!! Успехов Вам!!!
02.09.2015 06:15:41
Николай, снова я :)
в одной ячейке несколько телефонных номеров прописано через запятую:
79112112111, 79222211444
Как их разделить по столбцам?
То есть, чтобы номер 79112112111 остался в ячейке столбца E, а следующий номер 79222211444 перешел в ячейку F той же строки.и так далее
11.09.2015 09:36:30
Добрый день!
Пытаюсь сделать через маску, так чтоб при определённом значении в одной ячейки в другой отображался 0, если значение не совпадает, то число вводимое оператором.
07.10.2015 16:11:00
Здравствуйте!
Подскажите, пожалуйста, возможно ли с помощью пользовательского форматирования решить следующую задачу.
Вводится числовое значение в метрах, а отображается число с разделителем сотен метров и остатка через знак. "+".
То есть, вводим в ячейку число: 5293,24, а видим в ячейке: ПК 52 + 93,24м.
Сколько пробовал использовать знак пробела, постоянно выделяет только тысячи, а мне нужны сотни.
Решил эту задачу через формулу, но это дополнительный столбец, причём текстовый, по которому ни отсортировать, ни сгруппировать.
Заранее благодарен за ответ.
KRV
13.11.2015 12:39:49
Уважаемый Николай.
У меня такая функция:
="До конца проекта - "&ТЕКСТ(A2-A1;"hh:mm:ss"
На домашнем компе результат:
До конца проекта - 02:00:00
На рабочем
До конца проекта - hh:mm:ss
Подскажите как привести к одному виду. Спасибо
25.12.2015 10:58:07
Николай, здравствуйте,
Такой нетривиальный вопрос: А как можно, и можно ли, скопировать именно отображаемое форматом значение в ячейке?
Например, значение в ячейке 456, при помощи формата 00000 оно отображается в ячейке как 00456. И таких ячеек много. Как можно в отдельный столбец вытянуть именно значения 00456 и т. д. Сами значения могут быть от 0 до 10000, но отображается всегда 5 цифр соответсветственно.
Спасибо!
29.12.2015 10:54:32
Решил проблему использованием функции ТЕКСТ, там как раз и формат можно задать.
Страницы: 1  2