Пользовательские форматы в Excel
При необходимости Вы можете легко добавить к стандартным числовым форматам Excel свои собственные. Для этого выделите ячейки, к которым надо применить пользовательский формат, щелкните по ним правой кнопкой мыши и выберите в контекстном меню команду Формат ячеек (Format Cells) - вкладка Число (Number), далее - Все форматы (Custom):
В появившееся справа поле Тип: введите маску нужного вам формата из последнего столбца этой таблицы:
Как это работает...
На самом деле все очень просто. Как Вы уже, наверное, заметили, Excel использует несколько спецсимволов в масках форматов:
- 0 (ноль) - одно обязательное знакоместо (разряд), т.е. это место в маске формата будет заполнено цифрой из числа, которое пользователь введет в ячейку. Если для этого знакоместа нет числа, то будет выведен ноль. Например, если к числу 12 применить маску 0000, то получится 0012, а если к числу 1,3456 применить маску 0,00 - получится 1,35.
- # (решетка) - одно необязательное знакоместо - примерно то же самое, что и ноль, но если для знакоместа нет числа, то ничего не выводится
- (пробел) - используется как разделитель групп разрядов по три между тысячами, миллионами, миллиардами и т.д.
- [ ] - в квадратных скобках перед маской формата можно указать цвет шрифта. Разрешено использовать следующие цвета: черный, белый, красный, синий, зеленый, жёлтый, голубой.
Плюс пара простых правил:
- Любой пользовательский текст (кг, чел, шт и тому подобные) или символы (в том числе и пробелы) - надо обязательно заключать в кавычки.
- Можно указать несколько (до 4-х) разных масок форматов через точку с запятой. Тогда первая из масок будет применяться к ячейке, если число в ней положительное, вторая - если отрицательное, третья - если содержимое ячейки равно нулю и четвертая - если в ячейке не число, а текст (см. выше пример с температурой).
Ссылки по теме
С помощью пользовательского формата [<=10]"+";Основной мне удалось следующее:
при вводе любой цыфры от 1 до 9 появляется знак "+". Скажите а можно сделать что при нажатии клавиш с буквами тоже появлялся знак "+"
есть значения которые постоянны, а есть которые вводятся.
16/01-2345
проблема в следующем: меняется значение 4 и 5 символов (может быть от 1 до 16), и значения после тире (могут быть от одного до пяти знаков).
когда была задача менять символы после тире, я с ней легко справился, а здесь пока никак.
За ранее спасибо!
нужно, чтобы 16/0 вводилось автоматически, а 1-2345 или 4-96 вводилось вручную. в итоге получаем 16/01-2345 или 16/04-96.
но когда в экселе ставишь тире, ничего не получается.
к примеру если номер 16/01-23, то получим 16/00-0123
=ЕСЛИ(A1="";"";СЦЕПИТЬ("16/0";ЛЕВСИМВ(A1;1);"-";ПСТР(A1;2;5)))
Если вы ведете таблицу для печати то перед печатью изначальный столбец (забиваемый вручную[в моем примере "А"]) можно скрывать, удобнее через группировку excel 2007 и старше (вкладка "Данные" - Группировать)
Казалось бы очевидно, но у меня не получается.
Маска 000-00-00-000 дает 012-34-56-789, но маска 000/00/00/000 не работает.
###"/"##"/"##"/"###;;;
Возникает проблема с выгружаемыми списками (из 1С прошлого века
Пока обходимся разделением текста по столбцам, но на будущее может есть какой-то способ победить данный недуг?
Спасибо, Николай!
необходимо, чтобы номера из 16 символов выводились в удобной форме - группами по 4 цифры.
Ввожу маску:
0000" "0000" "0000" "0000
И всё бы ничего, но только последнюю цифру Excel настырно заменяет на ноль. Пробовал и через "#". Но безрезультатно. Не хочет он 16-й знак усваивать в цифровом формате.
Пока получается либо в текстовом формате неразрывно 16 символов, либо группами по 4, но с нулём в 16-ой позиции.
Совсем уже замучился...
Спасибо, Николай!
Подскажите пожалуйста, как спрятать запятую, то есть чтобы было (525 pyб 31 кoп) без запятой.
У меня получилось вот так: 525 pyб, 31 кoп маска: ###" руб",_ ##" коп"
(подразумевая, что исходная сумма лежит в A1).
В одной границе 2 ячейки, 2 формулы, визуально всё как в одной ячейке.
при использовании "??????????????????? ?" выдает такое "8970101282381510000 0", т.е. в конце все переводит в нули, а нужен текст ?
Сложное решение: создайте новую книгу, добавьте в нее ваш формат, сохраните книгу как шаблон (расширение xltx) и в будущем создавайте новые книги двойным щелчком по файлу шаблона. Также можно попробовать сохранить шаблон в папку автозапуска C:\Program Files\Microsoft Office\Office14\XLSTART с именем book.xltx. Тогда Excel должен автоматически создавать новые книги по этому шаблону, т.е. уже с вашим форматом внутри.
Однако, не советую так делать или применять осторожно - это может сильно утяжелить и замедлить файл.
Размера получаются двух видов, 34 и 34,5 , при этом строку содержащую 3234 ( Без пробела случайно, "кривые руки" формула должна читать как два размера 32 34, т.е. 3234 34,5 35 должно получиться 4. Или хотя бы выводить ошибку на присутствие варианта (отсутствие пробела) 3234
Подскажите как извлечь число из " 37.14 грн. " , для арифметических действий.
Зарание благодарен .
=ПОДСТАВИТЬ(ЛЕВСИМВ(ПСТР(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)
=ЗНАЧЕН(СЦЕПИТЬ(ПСТР(A1;1;2);",";ПСТР(A1;4;2)))
или (если сумма будет превышать десятки) фунцию сцепить заменил оператором конкатенации (&) Должна работать, вся формула построена на запятой, разделяющей целое и дробное (например руб./коп.) и после запятой пишет только две цифры
=ЗНАЧЕН(ПСТР(A1;1;(НАЙТИ(",";A1))-1)&","&ПСТР(A1;(НАЙТИ(",";A1))+1;2))
В ячейке записана дата со временем: 04.06.2014 12:00:00. Формат ячейки выглядит так: [$-419]Д МММ ч:мм;@ (отображается как 4 июн 12:00).
Проблема в том, что если записать в ячейку просто дату без времени, то она отображается так: 4 июн 0:00. Т.е. как будто 12 ночи.
Вопрос, можно ли скорректировать формат ячейки так, чтобы, если в ней записана дата без времени, то нули бы не отображались?
Можно, конечно, записать формат так: [$-419]Д МММ;@
Но проблема в том, что для некоторых записей необходимо вводить и дату и время, а для некоторых только дату.
Ещё добавлю, что я точно не буду записывать в ячейку время как 12 ночи (например, 04.06.2014 00:00), т.к. эта ячейка используется для "напоминалки" о задачах по работе, а рабочее время с 9 до 18.
Очень рассчитываю на Вашу помощь!
123.00
123.10
123.75960
Можно ли с помощью одного формата привести эти значения в следующий вид:
123
123.1
123.76
т.е. округлить до второго знака после запятой, и если второй или и первый и второй знак равняются нулю, убрать их?
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
Вот таким макросом решил проблему
или хотя бы осуществлялся контроль данных по маске, то есть, чтобы, если в ячейку с форматом +7(000)000-00-00 пользователь введет, например какой-то текст, чтобы выдавалось сообщение об ошибке
Подскажите как создать формат аналогичный процентному, но без знака % в конце. Т.е. форматирование должно заключаться в умножении значения на 100. Ну и для простоты без разрядов после запятой.
Обратный аналог тоже есть - форматы в тысячах "# ### " - фактически это же деление на 1000 и отбрасывание дробной части.
Не знаете, как сделать подобный формат?
Не решили свой вопрос? Столкнулась с той же проблемой
[Красный][=2]0;[Зеленый][=5]0;#
Столкнулнся с необходимостью изменения числового формата в ячейке по значению из другой. Примеров воплощения своей задачи в интернете не нашел, только частности... Может Вы подскажите ее решение? Допускается любое из вариантов.
Ячейка А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
Спасибо.
А можно ли задать формат, что если нет дробной части -- то выводится само число, а если есть -- то две цифры. Формат типа: 0,## дает ненужную запятую пр отсутвии дробной части...
у меня почему-то не получилось применить формат в виде "00000", т. е в ячейке необходимо хранить число из 20 символов (банковский счет), например 12345678901234567890. При вводе в яч число, естественно сокращается, создаю свой формат, как Вы тут советуете из 20-ти нулей, применяю - в ячейке остаются 15 символов, остальные 5 заменяются нулями...
Никак не могу побороть следующее. В одну ячейку, но в 2 строки занесено 2 № телефона в формате 11 цифр подряд без пробелов, и каких либо знаков, как сделать формат ячейки чтобы оба №№ отображались в формате 0(000) 000-00-00?
Подскажите пожалуйста,как сделать чтоб информация выводилась вот в таком виде: A1/A2 (тоесть A1 и A2 это ячейки в которых есть числа)?
=A1&"/"&A2
=ИНДЕКС({"Банан";"Арбуз";"Слива"};A1)
Если дефисы в ячейках есть физически, то их можно оптом удалить с помощью замены (Ctrl+H, найти дефис, заменить на ничего).