Пользовательский формат в excel: Пользовательский ЧИСЛОвой формат в EXCEL (через Формат ячеек). Примеры и описание

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

Сегодня хотелось бы поговорить о пользовательском формате в Excel. Если необходимо, то вы можете изменить формат ячеек по-своему усмотрению.

Например, давайте рассмотрим такой пример, у нас есть баланс на какие-то даты. Баланс может быть как отрицательный так и положительный. Нам необходимо, чтобы плюсовой баланс был закрашен синим цветом и со знаком плюс, а отрицательный в красный цвет со знаком минус. По умолчанию, если мы введем положительное число в ячейку с плюсом, то плюс будет убран автоматически программой Excel.

Давайте рассмотрим на предыдущем примере как работают пользовательские форматы.

Выделим весь диапазон с цифрами, далее необходимо нажать на правую кнопку мыши выбрать «Формат ячеек» (Format Cells) , и на вкладке «Число»  (Number) выберете пункт «Все форматы» (Custom).

Далее в поле «Тип» введем следующий текст

[Синий]+0,0;[Красный]-0,0;0,0

Нажимаем «ОК» и получим желаемый результат.

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

В данное поле можно ввести 4 формата через «;» (точка с запятой) в следующей последовательности:

1. положительное число;
2. отрицательное число;
3. ноль;
4. если формат — текст;

Три последних — необязательные форматы, их, как правило, можно вообще не писать. В некоторых случаях пишется только один формат.

Например, чтобы отображать номер телефона в формате +7(905)222-35-96
Необходимо написать следующий формат +7(#)000-00-00
в данном случае введенное число 79052223596 будет отображено как +7(905)222-35-96

Если указать квадратные скобки («[]«) перед форматом, то в них можно указать цвет текста, всего поддерживается около 7 основных цветов. В этом есть некоторые неудобство, так, например, зеленый цвет слишком яркий и сливается с фоном и оттенок поменять нельзя, поэтому я выбрал синий цвет.

В нашем примере для положительного формата числа я написал:
[Синий]+0,0
где [Синий] — это цвет ячейки
«+» — это необходимость всегда вставлять знак «+» в положительных числах
0,0 — это формат самого числа. 0 (ноль) — это обязательное знакоместо, которое показывает, что в данном месте будет обязательно введено какое-то целое число, потом запятая и после запятой будет только одна цифра (один разряд). Например, если число было бы 256,253 — то оно будет отображено как +256,2 (256 — как целое число и одна двойка после запятой).

Еще пример, если указать формат «000,00», то число 25,2365 будет отражено как 025,23 и так далее.

Далее мы указали формат отрицательного числа
[Красный]-0,0
Тут аналогично  [Красный] — это цвет числа, «—» — указать минус перед отрицательным числом в формате 0,0 (одна цифра после запятой)

Можно использовать (пробел), чтобы разделитель группы разрядов по три между тысячами, миллионами, миллиардами и так далее.

# (решетка) — в примере выше, это одно необязательное знакоместо, практически то же самое, что и ноль, но в случае отсутствия числа ничего выводиться не будет, а если бы был ноль, то был бы отражен ноль.

Далее в нашем примере для нулевого значения мы указали формат «0,0«, а для текста просто пропустили.

Надеюсь, теперь вас стало понятно как сделать пользовательский формат ячеек.

[ads]

Давайте рассмотрим еще парочку примеров для закрепления пройденного материала

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

0,00″ кг»

В данном случае введено число 25,2336 будет отражено как 25,23 кг

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

Еще пример отображения дюймов в ячейке
Введите следующую маску формата

0,0″»» (два апострофа в кавычках)

Как отобразить логические значения? Введем формат

«Да»;»Да»;»Нет»

в данном случае, любое положительное число и отрицательное значение будет отражено как «Да», а ноль будет отражен как «Нет»

Вы так же можете указать любой символ в формате, например если нам необходимо указать стрелки вверх при положительной динамике и вниз — при отрицательной, то можно указать данные символы в формате. Например, вот так:

[Синий]↑+0,0%;[Красный] ↓-0,0%;0,0

Специальные символы вы можете найти в Excel на вкладке «Вставка», панель «Символы», значок «Символ»

Если вам понравилась статья, то, пожалуйста ставьте лайки («мне нравится») и до встречи в новых уроках.

Создаем собственный числовой формат в MS Excel

Знаете сколько числовых форматов предлагает MS Excel? 9 штук! А ведь почти каждый из них имеет ещё и свои внутренние настройки – количество знаков после запятой, спецобозначения валют и т. п. Казалось бы во всем этом многообразии могут найтись варианты на все случаи жизни… Однако это далеко не так.

Ладно, дроби, числа с особой точностью, даты и валюты – с этим MS Excel прекрасно справляется и без нашей помощи. Но как прикажете объяснить ему, что номера кредитных карт выводятся исключительно в формате “ХХХХ ХХХХ ХХХХ ХХХХ”, а телефоны в формате “+7-9ХХ-ХХХ-ХХ-ХХ”? Впрочем, номера и телефоны это ещё не самый запущенный случай. Гораздо хуже приходится несчастным забивающим в таблицы сложные составные артикулы содержащие не только и цифры и буквы, но ещё и тире, косые черточки, точки и символы.

Если вас “накрыло” именно такой работой, самый лучший выход из положения – не проклинать начальство, а создать в MS Excele свой собственный числовой формат.

Создаем новый числовой формат в MS Excel

Как создать новый числовой формат в Excel

В примере приведенном далее, я буду использовать за образец такие требования: мне необходимо создать новый формат для ввода телефонных номеров. Причем в ячейку я буду вводить простой ряд цифр, а к соответствующему формату (“+7-9ХХ-ХХХ-ХХ-ХХ“) этот ряд цифр должен будет привести уже сам MS Excel.

Первым делом не мешало бы выделить столбец нашего будущего шаблона, а затем на вкладке главная вызвать выпадающий список “Числовой формат” (в группе “число”) и выбрать самый нижний пункт списка – “Другие числовые форматы“.

Выбираем шаблон похожий на то что нам нужно и модифицируем его

Нужная нам вкладка “Число” доступна здесь по умолчанию. Во всем многообразии предложенных числовых форматов я разбираться не стану и сразу же выберу пункт “(все форматы)”, чтобы увидеть всё и сразу.

Обратите внимание: в самом низу в этом списке уже есть шаблон телефонного номера который выглядит как “(###) ###-####”. Очевидно, что значки “решетки” в этом шаблоне заменяют цифры, и введи я реальный номер, например, в виде числа 9141108501, он отобразился бы как “(914) 110-8501”.

Как видите – неплохо, однако на мой задуманный шаблон походит только отчасти. Поэтому я просто переписываю шаблон на “+7 (###) ###-##-##” и нажимаю кнопку “Ок” (обратите внимание – если в экспериментальной ячейке введено число, при изменении шаблона вы сможете видеть как оно будет выглядеть после изменений).

Ввожу обычный числовой ряд, а MS Excel делает всю остальную работу по введенному ранее шаблону

Все отлично работает, особенно хорошо это заметно по строке формул – в ней мой телефонный номер выглядит как обычный числовой ряд, а в ячейке отображается как “нормальный” телефон.

Также обратите внимание: если второй раз открыть окно “Другие числовые форматы”, то вы обнаружите в нем не только “новый” шаблон для ввода телефонных номеров, но и “старый”. Сохранение это происходит автоматически и служит надежной страховкой от того, чтобы всерьез что-то в программе “сломать”.

Копирую форматирование на соседние числовые ряды…

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

…и получаю аккуратный список телефонных номеров

Проблемы пользовательских числовых форматов MS Excel

К сожалению, описываемый метод не решает некоторых проблем, самая главная из которых заключается в отсутствии фильтрации ввода и как следствия, возможности ошибок и опечаток. Пользовательский формат контролирует ТОЛЬКО расположение символов, вводимых в пределах этого формата, но не будет учитывать, сколько символов было введено.

Например, если рабочий лист содержит артикулы продуктов, такие как “Заготовка: А15-22”, то создание пользовательского формата “”Заготовка:” А##-##” и последующий ввод числа “1522” приведет к формированию ожидаемого результата. Но если вместо “1522” я по ошибке введу 311522, шаблон все равно окажется заполнен и выдаст итоговый результат вида “Заготовка: А3115-22”.

Однако… нам ведь уже знакома такая штука как проверка ввода данных? Так давайте разберемся с её помощью с возникшей проблемой.

Используем проверку данных в MS Excel, чтобы исключить неправильные варианты ввода

Выделите столбец в котором будет осуществляться проверка ввода данных (в данном случае столбец с моими артикулами).

Перейдите на вкладку «Данные» в группе «Работа с данным» откройте инструмент «Проверка данных«, и на вкладке «Параметры» укажите “тип”: “Длина текста”, “значение”: “Равно”, а в поле “длина”, введите число 4. Нажмите «Ок».

Пытаюсь ввести в свой шаблон заведомо неверную комбинацию из 5 цифр и получаю ожидаемое сообщение об ошбке

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

Форматируйте, фильтруйте и наслаждайтесь!

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

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

Возможно, Вам придется создать произвольный текстовый формат для чисел, чтобы использовать его в качестве текстовой метки. Например, если ввести почтовый индекс, который начинается с нуля (0), приложение Numbers, как правило, принимает его за число и автоматически удаляет ноль (0) в начале. Это не произойдет, если для почтового индекса выбрать формат текста.

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

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

1. Выберите ячейки, которые нужно отформатировать.

2. В боковой панели «Формат»  нажмите вкладку «Ячейка».

3. Нажмите всплывающее меню «Формат данных», затем выберите «Создать пользовательский формат».

4. Введите имя формата, затем нажмите всплывающее меню «Тип» и выберите «Число».

5. Для настройки формата выполните любое из описанных ниже действий.

   • Введите текст в поле. Введенный текст отображается в ячейке.

   • Используйте маркеры для представления типа информации, которая будет вводиться в ячейке. Перетяните маркеры в поле произвольного формата. Например, при вводе почтовых индексов США в пользовательском формате можно применить маркер целого числа с пятью цифрами.

     Для изменения порядка маркеров их можно перетягивать в пределах поля.

   • Настройте элемент маркера. Нажмите стрелку на маркере для отображения параметров форматирования, таких как тип символа валюты или количество отображаемых цифр.

   • Укажите правила для охвата различных условий. Добавьте до трех правил, чтобы изменять форматирование ячейки на основе заданных Вами условий.

     В следующем примере при вводе любых положительных чисел они отображаются как телефонные номера с кодом города (952). Когда Вы вводите в ячейке номер «5555103», он отображается в виде «(952) 555-5103». Вы вводите «0» в ячейке, и в ней отображается «В файле нет числа».

Создание произвольного формата для даты и времени

1. Выберите ячейки, которые нужно отформатировать.

2. В боковой панели «Формат»  нажмите вкладку «Ячейка».

3. Нажмите всплывающее меню «Формат данных», затем выберите «Создать пользовательский формат».

4. Введите название формата, затем нажмите всплывающее меню «Тип» и выберите «Дата и время».

5. Установите произвольный формат, выполнив одно из следующих действий.

   • Введите текст в поле. Введенный текст отображается в ячейке.

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

   • Настройте элемент маркера. Нажмите стрелку на маркере для отображения параметров форматирования даты.

     В следующем примере применяется маркер порядкового номера дня в году (заданного как «5») и пользовательский текст, поэтому при вводе значения «2/25/2014» в ячейке отображается текст «56 день года».

      

     Если ввести значение, в котором содержится дефис (-) или косая черта (/), например «1/4/2014», приложение Numbers будет считать такое значение датой и автоматически присвоит ему формат даты и времени.

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

1. Выберите ячейки, которые нужно отформатировать.

2. В боковой панели «Формат»  нажмите вкладку «Ячейка».

3. Нажмите всплывающее меню «Формат данных», затем выберите «Создать пользовательский формат».

4. Введите название формата, затем нажмите всплывающее меню «Тип» и выберите «Текст».

5. В текстовом поле введите текст, который должен автоматически отображаться в каждой ячейке с этим форматом.

   Синий маркер «текст», по умолчанию отображаемый в поле «Пользовательский формат», обозначает текст, вводимый в ячейке. В данном примере перед любым содержимым ячейки будет отображаться текст «Уведомить покупателя:». Если ввести текст «нужно обновить адрес», отображается «Уведомить покупателя: нужно обновить адрес».

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

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

2. В боковой панели «Формат»  нажмите вкладку «Ячейка».

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

4. Нажмите «Править польз. формат», затем выполните одно из следующих действий.

   • Изменение формата ячейки. Внесите изменения, затем нажмите «ОК».

   • Удаление формата ячейки. Нажмите «Удалить формат».

     Любым ячейкам в таблице, где использовался удаленный формат, будут возвращено форматирование «Автоматически».

Пользовательский числовой формат в Эксель | Excel, Google: автоматизируй это

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

Первый раз, когда я полез в настройки формата, я испугался. Честно, там было столько непонятного, больше чем в сложных формулах 🙂 Все вот эти нолики, решетки, точка-запятые. Когда спустя время я все-таки взял себя в руки и решил разобраться в вопросе, я был поражен возможностями пользовательских форматов.

Сначала я расскажу, как попасть в меню настройки формата. Есть несколько способов (листайте галерею):

Нажать Ctrl+1Нажать Ctrl+1Нажать Ctrl+1Нажать Ctrl+1

• Нажать Ctrl+1
• Или в выпадающем списке с форматами выбрать «Другие числовые форматы» (На английском «More number formats»)
• Нажать правой кнопкой мыши на ячейке и выбрать «Формат ячеек» или «Числовой формат» («Format Cell»)
• Или в ленте возле выбора формата нажать на маленькую стрелочку вправо-вниз

Открывшееся окно с настройками формата ячеек. Пользовательский формат можно задать в списке «(все форматы)».

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

формат для положительного числа; формат для отрицательного числа; формат для нуля; формат для текста

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

Для описания формата используют специальные символы:

• символ решетка означает любую цифру (## ) — сложно указать ее в Zen, т. к. он заменяет решетку на хэштэг.
• Символ пробела определяет разряд (в других региональных настройках вместо пробела может быть использована запятая)
• Запятая и нули после нее указывают, как отображается дробная часть (в других региональных настройках может использоваться точка вместо запятой)
• В кавычках можно указать дополнительные символы, в том числе, эмоджи.
• В квадратных скобках перед форматом можно указать цвет, которым будет выведен текст.

На самом деле, можно много рассказывать, но лучше показать на примерах!

Красивее всего это выглядит в Эксель онлайн — там эмодзи цветные!

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

Тысячи и миллионы

Дополнение ведущими нулями, разделение на разряды

Указание единиц измерения

Дроби

Особые обозначения, цвета, символы

Дата и время

Больше информации

Я подготовил и выложил пример: https://1drv.ms/x/s!Ah8FgdGyLnmvqYYx3PqZ82d1I0NRXw?e=dwVG1z

Обязательно откройте файл в онлайн-версии эксель, нажав по ссылке или загрузите его на компьютер. В онлайн версии эмодзи выглядят веселее, цветные 🙂

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

excel — Пользовательский формат ячеек для добавления предложения с использованием кода VBA

Мне нужно написать макрос для пользовательского форматирования ячейки таким образом, чтобы, если кто-то набирает число в ячейке (скажем, например, 10), результат должен составлять «10% маржи EBITDA». Как мне это сделать?

0

TotalGadha

2 Мар 2019 в 05:28

2 ответа

Лучший ответ

Попробуйте этот код VBA

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyRNG As Range: Set MyRNG = Range("B2:B20") 'change your required range here
If Not Application. Intersect(MyRNG, Target) Is Nothing Then
ActiveCell.NumberFormat = "0.00 ""of EBITDA margin"""
End If
End Sub

1

Gaurav Jain
2 Мар 2019 в 06:38

Создайте пользовательский NumberFormat для ячейки (Format Cells> Number> Custom), например

0.00% "of EBITDA margin"

Часть 0.00% может быть любого формата для числа. Пробел после него может быть внутри кавычек или перед ними. Кавычки могут содержать любую строку, которую вы хотите, одно слово или длинное предложение (хотя, вероятно, всего не более 255 символов).

Код ниже будет форматировать ячейку A1 с тем же NumberFormat, показанным выше. Формат может быть изменен аналогичным образом.

Private Sub SetCellFormat()

    Cells(1, 1).NumberFormat = "0.00% ""of EBITDA margin"""
End Sub

0

Variatus
2 Мар 2019 в 03:03

Числовые форматы ячеек — WiseData

Просмотров:
231

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

Немного любопытного замечания

Возможно одной из причин редкого использования такой функции Excel как определенные числовые форматы ячеек, является название, которое она получила в русскоязычной версии Excel. Возможность задания особых способов отображения ячеек находится в окне «Формат ячеек» (в английской версии Format Cells), что логично, задаем же форматирование для ячейки. На вкладке «Число» (Number), отсюда делаем вывод, что с точки зрения Excel определяем форматирование числовой ячейки, хотя текстовые и прочие строковые форматы тоже в этом же списке, что уже наводит на размышления. И в списке «Числовые форматы» (Category), то есть в английском дословно это «Категории», а в русском «Числовые форматы», любопытно. И наконец, последний элемент в списке числовых форматов «(все форматы)», что в английском называется как «Custom». Обычно слово «Custom» при локализации приложений на русский язык переводят как «Настраиваемый» или «Пользовательский» что вероятнее более точно соответствует сути данного формата, но как слово «Custom» стало «Все форматы».

На первый взгляд, да и на второй ни чего не понятно, что же за странные символы в списке справа. Да это такие же предопределенные форматы, как и в других группах, да в поле «Образец» можно посмотреть, как будет выглядеть значение в выделенной ячейке с текущим форматированием. Но неизвестность пугает, что это, как это. Всё не так страшно как кажется на первый взгляд.

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

Из чего всё состоит

Форматирование позволяет изменить, порой до неузнаваемости, данные в ячейке. Что важно сами по себе данные остаются теми же самыми, изменяется только их отображение.

Во многих современных языках программирования используется тот же принцип для форматирования значений при их отображении. Да, по сути, и правила и символы, используемые для форматирования те же самые. Чаще всего для числовых форматов используется знак «#» или «,» или «0», хотя могут использоваться и другие. Задаваемое описание и определяет, как будет отображаться значение. При помощи этих описаний можно задать и стандартное представление значений, такое например как денежное или числовое, но логичнее применять его для случаев, когда требуется особый вид. Расширенное описание форматирования позволяет в некоторых случаях не прибегать к условному форматированию, задавая особые способы отображения отрицательных чисел.

Свои форматы

Как уже говорилось, пользователь может ввести собственные форматы для отображения данных. Сделать это в диалоговом окне «Формат ячеек», пункте «все форматы». Вновь создаваемые форматы сохраняются в рабочей книге и могут быть использованы на любом листе рабочей книги. Всего в рабочей книге можно сохранить до 200 новых определений форматирования. Если вы работаете с большой рабочей книгой, где необходимо использовать нестандартное форматирование, сохранение такого количества новых форматов позволит максимально полно удовлетворить потребности. Новый формат на самом деле, очень просто перенести в другую книгу, для этого достаточно скопировать ячейку, где применяется форматирование в новую книгу.

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

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

Два описания – первое применяется к положительным и нулевым значениям, второе к отрицательным значениям.

Три описания – первое положительные значения, второе отрицательные значения, третье нулевые значения.

Четыре описания — первое положительные значения, второе отрицательные значения, третье нулевые значения, четвертое текст.

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

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

Коды форматирования значений

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

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

0 (нуль) – значащий цифровой разряд. Может применяться для дополнения числа слева, до необходимой длинны. При этом слева дополняется нулём.

? – цифровой разряд. Применяется относительно десятичной запятой. Если число слева или справа от запятой меньше необходимого количества знаков, то значение дополняется пробелом.

, — запятая, просто десятичная запятая.

% — процент.

E+ E- e+ e- — экспоненциальный формат.

$, -, +, /, (, ), :, пробел – отображаются своим знаком и не имеют специального применения.

\ — отображает следующий за ним символ.

* — повторяет следующий за знаком «звездочка» символ, до заполнения ширины столбца.

_  — пропуск на ширину следующего за ним символа.

«текст» — отображает текст, указанный в кавычках.

@ — место для текста.

[цвет] – отображает форматирование выбранным цветом. Доступны значения – черный, синий, голубой, зеленый, фиолетовый, красный, белый, желтый. При этом регистр символов при написании цвета не учитывается.

[цвет n] – цвет из палитры цветов, где n – число от 0 до 56.

[условный шаблон] – позволяет определять собственные критерии форматирования.

Коды форматирования даты/времени

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

М – номер месяца, от 1 до 12, без ведущего нуля.

ММ – номер месяца, от 01 до 12, с ведущим нулем.

МММ – аббревиатура месяца, от «Янв» до «Дек».

ММММ – полное название месяца, от «Январь» до «Декабрь».

МММММ – первая буква названия месяца, от «Я» до «Д»

Д – номер дня месяца, от 1 до 31, без ведущего нуля.

ДД – номер дня месяца, от 01 до 31, с ведущим нулем.

ДД – аббревиатура дня недели, от «Пн» до «Вс».

ДДДД – полное название дня недели, от «Понедельник» до «Воскресенье»

ГГ – последние цифры года, например для 2017 – значение 17.

ГГГГ – полное значение года.

ч – номер часа, от 0 до 23, без ведущего нуля.

чч – номер часа, от 00 до 23, с ведущим нулем.

м – минуты, от 0 до 59, без ведущего нуля.

мм – минуты, от 00 до 59, с ведущим нулем.

с – секунды, от 0 до 59, без ведущего нуля.

сс – секунды, от 00 до 59, с ведущим нулем.

AM/PM – перевод времени в 12 часовой формат. По умолчанию используется 24 часовой формат.

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

Поделиться ссылкой:

Похожее

Трюк №32. Как создать в Excel пользовательский числовой формат — MS Excel

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

Перед тем как приступать к изучению этих трюков, полезно понять, как Excel распознает форматы ячеек. С точки зрения Excel, формат ячейки состоит из четырех разделов (слева направо): положительные числа, отрицательные числа, нулевые значения и текстовые значения. Они отделяются друг от друга точкой с запятой (;).

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

Не следует считать, что пользовательские форматы применяются только к числовым данным. Числовые форматы применяются и к тексту.

Пользовательский числовой формат, показанный на рис. 2.18, — это стандартный формат валюты Excel, который отображает отрицательные значения валюты красным цветом. Мы модифицировали его, добавив отдельный формат для нулевых значений и текста. Если в качестве значения валюты вы введете положительное число, Excel автоматически отформатирует его, чтобы на месте разделительного знака для разряда тысяч вставлялась запятая, и в числе использовалось два десятичных разряда. То же самое будет сделано и для отрицательных значений, но они будут отображаться красным цветом. Для любого нулевого значения символ валюты отображаться не будет, но будут присутствовать два десятичных разряда. Если вы введете в ячейку текст, Excel отобразит сообщение «No Text Please», независимо от того, какой это будет текст.

Рис. 2.18. Разделы пользовательского числового формата

Важно помнить, что форматирование значения в ячейке не влияет на его настоящее значение. Например, введите любое число в ячейку А1. Выберите команду Главная → Формат → Ячейки → Число → Все форматы (Home → Format → Cells → Number → Custom) и, выбрав любой формат, введите «Hello» (в кавычках). Затем щелкните кнопку ОК.

Во всем мире индустрия туризма является высокоэффективной, и для некоторых стран доход от нее составляет существенную часть ВВП. В России туризм бурно развивается, особенно в последнее десятилетие. Эффективность и качество работы туристских предприятий, таких как краснодар гостиница уют напрямую зависят от скорости и качества обработки туристской информации. Успешное функционирование предприятия туриндустрии практически немыслимо без использования современных информационных технологий.

Хотя в ячейке появится слово Hello, вы можете увидеть ее настоящее значение, выделив ячейку и взглянув на строку формул или нажав клавишу F2. Если вы хотите сослаться на эту ячейку в формуле, например, =А1+20, то в результирующей ячейке также будет применяться пользовательский формат. Если вы хотите сослаться на ячейку А1 вместе со многими другими ячейками со стандартным форматом Excel, например, =SUM(A1:A10), то в результирующей ячейке также будет применяться пользовательский формат ячейки Al. Excel считает, что вы хотите, чтобы результирующая ячейка была отформатирована так же, как и ячейки, на которые ссылается формула. Если эти ячейки содержат несколько типов форматирования, любой пользовательский формат будет иметь преимущественное значение.

Это означает, что всегда необходимо помнить: Excel использует для вычислений настоящее значение ячейки, а не отображаемое. При этом могут возникнуть сюрпризы, если вычисления проводятся с использованием ячеек, отформатированных, например, без десятичных разрядов или всего с несколькими. Чтобы увидеть, как это работает, введите (в английской версии) 1.4 в ячейке А1 и 1.4 в ячейке А2, отформатируйте обе ячейки так, чтобы они не показывали десятичные разряды, а затем в другую ячейку поместите формулу =А1+А2. В результате, конечно же, получится 3, так как Excel округляет значение.

В Excel предусмотрен флажок Точность, как на экране (Precision as Displayed), который можно установить, выбрав команду Сервис → Параметры → Вычисления (Tools → Options → Calculation), но необходимо помнить, что этот параметр навсегда изменяет хранимые в ячейках значения с полной точности (15 цифр) до того формата, включая десятичные разряды, в котором значение выводится на экран. Другими словами, если вы установите этот флажок и щелкните кнопку ОК, пути назад уже не будет. (Можно попробовать, но дополнительная информация о точности значения исчезнет навсегда.)

Формат по умолчанию для любой ячейки — Общий (General). Если вы вводите в ячейку число, Excel часто угадывает наиболее подходящий числовой формат. Например, если вы введете 10%, Excel отформатирует эту ячейку процентным форматированием. Чаще всего Excel угадывает правильно, однако иногда формат приходится менять вручную.

Находясь в диалоговом окне Формат → Ячейки (Format → Cells), постарайтесь избежать искушения принудительно включить форматирование по левому или правому краю или по центру. По умолчанию числа выравниваются по правому краю, а текст — по левому. Если вы не будете менять эти параметры, то сможете с первого взгляда сказать, является ячейка текстовой или числовой, как в предыдущем примере, когда в ячейке А1 выводился текст, но на самом деле она содержала число.

В каждом разделе данного формата используется собственный набор кодов форматов. Эти коды заставляют Excel выводить данные так, как вам это нужно. Так, например, предположим, что вы хотите, чтобы отрицательные числа выводились в круглых скобках, а для всех чисел (положительных, отрицательных и нулевого значения) выводились два десятичных разряда. Для этого используйте следующий пользовательский формат: 0.00_ :(-0.00). Если вы также хотите, чтобы отрицательные числа выводились красным цветом, используйте формат 0.00_ :[Red](-0.00), в русской версии Excel: 0.00_ ;[КРАСНЫЙ](-0.00). Обратите внимание на квадратные скобки в предыдущем коде. Этот код формата заставляет Excel выводить число красным цветом.
В разделах пользовательского формата можно применять множество кодов форматов. В таблицах 2.1-2.5 [скачать], полученных из документации Microsoft, объясняется значение этих кодов (в английской версии).
Обратите особое внимание на последний код формата в табл. 2.5 — операторы сравнения. Предположим, вы хотите, чтобы пользовательский числовой формат 0.00_ ;[Red] (-0.00) (0,00_ ;[Красный] (-0,00)) отображал отрицательные числа красным цветом и в скобках, только если они меньше -100. Для этого укажите следующий формат: 0.00_ :[Red][, в русской версии Excel 0.00_ ;[Красный][.

Добиться нужного результата помогают коды формата

[Red][ в разделе для отрицательных чисел. При помощи этого метода и условного форматирования вы можете увеличить количество условий в условном формате с трех до шести.

Часто пользователи хотят отобразить знак доллара словами. Для этого используйте следующий пользовательский формат: 0 "Dollars and" .00 "Cents". Этот формат позволит числа, например, 55.25 отобразить как 55 Dollars and 25 Cents. Если вы хотите преобразовывать числа в доллары и центы, то, чтобы узнать об этих двух пользовательских функциях Microsoft, обратитесь на сайты http://www.ozgrid.com/VBA/ValueToWords.htm и http://www.ozgrid.com/VBA/CurrencyToWords.htm.

Кроме того, при помощи пользовательского формата можно отображать слова Low, Average или High вместе с введенными словами. Укажите следующий код формата:

[<11]"Low"* 0;[>20]"High"* 0:"Average"* 0. Обратите внимание на звездочку. Она повторяет следующий символ в формате, заполняя столбец на всю ширину, благодаря чему слова Low, Average или High будут отформатированы по левому краю, а числа — по правому.

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

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

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

Создать собственный числовой формат

1. На вкладке Home в группе Number щелкните More Number Formats внизу списка Number Format

   .

2. org/ListItem»>

   В диалоговом окне Формат ячеек в разделе Категория щелкните Custom .

3. В списке Тип выберите встроенный формат, который больше всего похож на тот, который вы хотите создать. Например, 0,00 .

   Выбранный числовой формат отображается в поле Тип .

4. В поле Тип измените коды числового формата, чтобы создать точный формат, который вам нужен.Например, 000-000-0000 .

   Ваши изменения не повлияют на встроенный формат. Вместо этого ваши изменения создают новый настраиваемый числовой формат.

5. org/ListItem»>

   По завершении щелкните ОК .

Применить собственный числовой формат

1. Выберите ячейку или диапазон ячеек, которые вы хотите отформатировать.

2. На вкладке Home в группе Number щелкните More Number Formats внизу списка Number Format

   .

3. В диалоговом окне Формат ячеек в разделе Категория щелкните Custom .

4. Внизу списка Тип выберите только что созданный встроенный формат.Например, 000-000-0000 .

   Выбранный числовой формат отображается в поле Тип .

5. Щелкните ОК .

Удалить произвольный числовой формат

1. На вкладке Home в группе Number щелкните More Number Formats внизу списка Number Format

   .

2. org/ListItem»>

   В диалоговом окне Формат ячеек в разделе Категория щелкните Custom .

3. В списке Тип выберите произвольный числовой формат и нажмите Удалить .

   Примечания:

   • Встроенные числовые форматы удалить нельзя.

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

Создать собственный числовой формат

org/ItemList»>

1. На вкладке Home , под Number , во всплывающем меню Number Format

   , щелкните Custom .

2. В диалоговом окне Формат ячеек в разделе Категория щелкните Custom .

3. В списке Тип выберите встроенный формат, который больше всего похож на тот, который вы хотите создать. Например, 0,00 .

   Выбранный числовой формат отображается в поле Тип .

4. org/ListItem»>

   В поле Тип измените коды числового формата, чтобы создать точный формат, который вам нужен. Например, 000-000-0000 .

   Ваши изменения не повлияют на встроенный формат. Вместо этого ваши изменения создают новый настраиваемый числовой формат.

5. По завершении щелкните ОК .

Применить собственный числовой формат

1. Выберите ячейку или диапазон ячеек, которые вы хотите отформатировать.

2. org/ListItem»>

   На вкладке Home , под Number , во всплывающем меню Number Format

   , щелкните Custom .

3. В диалоговом окне Формат ячеек в разделе Категория щелкните Custom .

4. Внизу списка Тип выберите только что созданный встроенный формат. Например, 000-000-0000 .

   Выбранный числовой формат отображается в поле Тип .

5. Щелкните ОК .

Удалить произвольный числовой формат

org/ItemList»>

1. На вкладке Home , под Number , во всплывающем меню Number Format

   , щелкните Custom .

2. В диалоговом окне Формат ячеек в разделе Категория щелкните Custom .

3. В списке Тип выберите произвольный числовой формат и нажмите Удалить .

   Примечания:

   • Встроенные числовые форматы удалить нельзя.

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

См. Также

Коды числовых форматов

Отображение даты, времени, валюты, дробей или процентов

Выделяйте закономерности и тенденции с помощью условного форматирования

Отображение или скрытие нулевых значений

Отображать номера как почтовые индексы, номера социального страхования или номера телефонов

Создать собственный числовой формат

Создайте и создайте собственный числовой формат для отображения чисел в виде процентов, валюты, дат и т. Д.Чтобы узнать больше о том, как изменить коды числового формата, прочтите рекомендации по настройке числового формата.

1. Выберите числовые данные.

2. На вкладке Home в группе Number щелкните средство запуска диалогового окна.

3. Выберите Custom .

4. В списке Тип выберите существующий формат или введите новый в поле.

5. Чтобы добавить текст в свой числовой формат:

6. Выберите ОК .

Excel предоставляет множество вариантов для отображения чисел в различных форматах, таких как проценты, валюта и даты.Если встроенные форматы вам не подходят, вы можете создать собственный числовой формат.

Вы не можете создавать собственные форматы в Excel в Интернете, но если у вас есть настольное приложение Excel, вы можете нажать кнопку Открыть в Excel , чтобы открыть книгу и создать их. Дополнительные сведения см. В разделе Создание настраиваемого числового формата.

Коды числовых форматов — Excel для Mac

Вы можете использовать коды числового формата для управления отображением цифр до и после десятичного разряда. Используйте числовой знак (#), если вы хотите, чтобы в числе отображались только значащие цифры. Этот знак не позволяет отображать незначащие нули. Используйте числовой символ для нуля (0), если вы хотите отображать незначащие нули, когда число может содержать меньше цифр, чем указано в коде формата. Используйте вопросительный знак (?), Если вы хотите добавить пробелы для незначащих нулей по обе стороны от десятичной точки, чтобы десятичные точки были выровнены, когда они отформатированы шрифтом фиксированной ширины, например Courier New.Вы также можете использовать вопросительный знак (?) Для отображения дробей с различным количеством цифр в числителе и знаменателе.

Если число имеет больше цифр слева от десятичной точки, чем заполнителей в коде формата, в ячейке отображаются дополнительные цифры. Однако, если число имеет больше цифр справа от десятичной точки, чем заполнителей в коде формата, число округляется до того же количества десятичных знаков, что и заполнителей. Если код формата содержит только числовые знаки (#) слева от десятичной точки, числа со значением меньше 1 начинаются с десятичной точки, а не с нуля, за которым следует десятичная точка.

Чтобы отобразить запятую как разделитель тысяч или масштабировать число, кратное 1000, включите запятую (,) в код числового формата.

Для отображения начальных и конечных нулей перед или после целого числа используйте коды из следующей таблицы.

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

[Черный] [Синий] [Голубой] [Зеленый] [Пурпурный] [Красный] [Белый] [Желтый]

Чтобы указать, что числовой формат будет применяться только в том случае, если число соответствует указанному вами условию, заключите условие в квадратные скобки. Условие состоит из оператора сравнения и значения. Например, в следующем числовом формате числа, которые меньше или равны 100, будут отображаться красным шрифтом, а числа больше 100 — синим шрифтом.

[Красный] [<= 100]; [Синий] [> 100]

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

Изучение пользовательского форматирования чисел Excel — Xelplus

Excel, как мы все знаем, часто называют «электронной таблицей». Он был разработан, чтобы заменить зеленый лист бухгалтерской книги, состоящий из места для написания комментариев, за которым следуют маленькие зеленые «квадраты (ячейки)», где одно число или буква напечатано с помощью ручки или карандаша.

К сожалению, внешний вид «электронной таблицы»

Excel — это электронная иллюзия. Помните, как я сравнивал Excel с пишущей машинкой? Что ж, может быть Excel следует назвать пишущей машинкой «нового века». Excel поднимает «WYSIWYG» на завораживающую высоту!

Excel отображается на экране вашего компьютера в виде «квадратов» бухгалтерской книги (Примечание: каждая ячейка может содержать 255 символов, а не 1 символ).Хотя кажется, что данные вводятся в «бухгалтерскую книгу (электронную таблицу)», на самом деле они вводятся в панель формул (белая текстовая панель, расположенная над электронной «бухгалтерской книгой»). Данные отображаются в электронной таблице, а не на панели формул.

Excel обрабатывает ввод данных визуально в 3 этапа (от пола вверх, а не до крыши)

Данные
1. введены в панель формул, но, похоже, введены в ячейку электронной таблицы
2. к записи применяется числовой формат (либо число, либо общий формат по умолчанию)
3. на экране вашего компьютера отображается отформатированный, настроенный ввод данных.

Запись панели формул остается неизменной, но внешний вид значения рабочего листа каким-то образом «оформлен» (отформатирован). Этот «нарядный» вид является одновременно «видом с высоты птичьего полета» и местом, где ваши данные «обрабатываются / изменяются» в формате. И наоборот, ваши данные на панели формул просто остаются там, как они были введены.

Введите 1000 в любую ячейку. Excel внутренне видит 1000.00, а не 1000 — 1000 с последующими десятичными значениями! Excel делает это из-за того, что он запрограммирован для хранения даты и времени.

Если ваша ячейка содержит формат времени, например « ч: мм: сс» , в Excel происходит что-то визуально странное. Вы вводите 1000 в строке формул, но это каким-то образом заменяется на «26.09.1902 12:00:00 AM». Затем, поскольку выбран формат времени, 1000 в представлении электронной таблицы изменится на «0:00:00». Помните, внешний вид в Excel может быть обманчивым — вот почему это не пишущая машинка с функцией WYSIWYG!

И разве я не сказал, что исходные записи ячеек всегда остаются неизменными?

Так почему же формат времени / даты заменяет 1000 в строке формул на «26. 09.1902 12:00:00 AM»? Этот переключатель вызывается, как только что упомянуто, внутренним программированием даты / времени Excel.

Хотя вы видите значение даты и времени на панели формул, исходное значение — 1000 — все еще находится под этой строкой даты / времени. В противном случае Excel не отобразит это значение даты и времени.

А, внешность в Excel обманчива, согласны? Форматы даты и времени, а также «вид с высоты птичьего полета» служат примером этой аксиомы. Это единственный случай, когда на панели формул не отображается исходное значение.

Форматированные значения даты и времени — единственные форматы, которые, кажется, нарушают исходное правило ввода строки формул, но это не так — они располагаются поверх ваших данных, они не заменяют их.Загадочно, не правда ли?

Вот почему это делают форматы даты и времени.

Началом времени в Excel было произвольно выбрано 12 часов утра 1 января 1900 года. Excel хранит значения даты и времени как десятичные порядковые номера. Компонент дня выражается целым числом до десятичной дроби, а компонент времени — числами после десятичной дроби. Одна секунда равна примерно 0,00001157407 секундам (1 день, разделенный на 86400 секунд в день).

Другими словами, программа Excel распознает числа до десятичной дроби как даты и значения («0.00 »в этом сценарии) после десятичной дроби в качестве времени -« десятичная »часть 24-часового дня. Например, 125 будет означать 3:00:00 утра. Или .78 переводится как 18:43.

Итак, когда 1000 было введено в панель формул, Excel увидел 1000.00 как 1000 полных дней после «начала времени» без какого-либо истечения времени. 1000, как значение, по-прежнему находится в строке формул, но скрыто под значением «26.09.1902 12:00:00 AM». Excel не может отображать форматированное значение, если значение отсутствует в ячейке!

Если вы измените формат на «м / д» — формат даты — на панели формул теперь будет «26 сентября 1900», а в вашей электронной таблице будет отображаться «9/26», потому что символы года («гггг») не указаны. Не включается в формат только даты.Измените формат даты на денежный формат (без нулей). 1000 теперь беспрепятственно виден в баре Formula Bar — там, где он стоял все время, — но «вид с высоты птичьего полета» теперь превратился в «1000 долларов»!

Рисунок 6 — Значение, отображаемое в строке формул, в сравнении с ячейкой

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

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

Они считаются отдельными форматами, потому что добавленный вручную формат выполняется «на лету» (т. е. вы решаете, когда и как изменить формат ячеек). Однако условное форматирование зависит от истинных / ложных условий (правил), которые определяют, когда формат активируется. Для запуска условных форматов требуется больше памяти, чем для ручного применения настраиваемых форматов из-за логики триггера «истина / ложь».

Имейте в виду, что добавление к вашему файлу различных заливок, границ, шрифтов, условных форматов и т. Д. Увеличит его размер и может вызвать «раздувание» файла. Слишком много пользовательских форматов в файле также в конечном итоге замедлит время вычислений в Excel.

Таким образом, и пользовательский, и условный форматы потребляют много ресурсов компьютера. С другой стороны, числовые форматы обычно потребляют гораздо меньше.

Excel 2016 — это первая версия, в отличие от предыдущих версий Excel, в которой нет ограничений на настраиваемый формат, и она не зависает и не «дает сбой», как предыдущие версии.Более ранние версии Excel имеют ограничение настраиваемого формата — от 4000 до 64000 настраиваемых форматов — в зависимости от версии Excel. Вы можете найти это ограничение для своей версии Excel в Интернете. Рекомендуется просмотреть эту документацию для вашей версии, чтобы понять ограничения вашей версии Excel, если таковые имеются.

Итак, если ваши файлы вычисляются или обновляются медленно, сокращение количества настраиваемых форматов и количества условных форматов может помочь ускорить их — при условии, что у вас нет многочисленных формул массива, скрывающихся где-либо в ваших электронных таблицах (но это совершенно другая сказка на ночь ).

Для всех неформатированных ячеек по умолчанию применяется «Общий» формат. Перевод: запись в ячейке отображается как напечатанная («WYSIWYG»). Чтобы изменить «одежду» ячейки, используйте числовые форматы, указанные в подменю «Число» (на главной ленте) или на вкладке «Категории на числовом элементе» в инструменте «Формат ячеек» ( чат).

Пользовательское форматирование чисел в Excel: Часть 2

В предыдущем совете мы представили основные функции и синтаксис пользовательского форматирования чисел.Marquee использует настраиваемое форматирование, чтобы наши модели были Динамическими, гибкими, интуитивно понятными и прозрачными : ключевые элементы нашей философии моделирования.

Сегодня мы хотели бы расширить эту тему, продемонстрировав, как комбинировать буквы и цифры в ячейке. Рассмотрим ситуацию, когда мы хотели бы показать мультипликатор P / E для компаний A, B и C в таблице ниже. Эти значения затем будут использоваться в расчетах в различных частях электронной таблицы.

Если мы просто введем « 10.0x» в ячейку для компании A, значение будет прозрачный и интуитивно понятный , но не динамический или гибкий , потому что его нельзя использовать в качестве входных данных для каких-либо вычислений. . Почему? Поскольку Excel распознает ячейку как текст, потому что «x» находится непосредственно в ячейке.

Пользовательское форматирование чисел спешит на помощь! В диалоговом окне формата ниже (нажмите CTRL 1) выберите вкладку «Число», выберите «Пользовательский» и перейдите к разделу с надписью « T ype».Теперь мы можем определить наш пользовательский числовой формат как 0,0x . Нули действуют как заполнители для каждой цифры отображаемого числа. Поскольку мы хотим, чтобы наш номер заканчивался символом «x», который является текстовым символом, мы помещаем x после нулевого заполнителя. Нам не нужны кавычки вокруг x, как это часто бывает с отдельными буквами в других пользовательских форматах.

Теперь мы можем ввести число 10 в ячейку, но оно будет отображаться как 10.0x . Используя настраиваемое форматирование чисел, мы обеспечили возможность использования кратного числа в расчетах по всей электронной таблице.

Если бы это были входы для графика оборотного капитала, то мы могли бы захотеть, чтобы входные данные читались как «10,0 дней». Мы снова можем использовать настраиваемое форматирование чисел и ввести 0,0 «дней» в поле настраиваемого форматирования. Обратите внимание, что двойные кавычки необходимы, когда текстовых символов более одного.

Это лишь один из многих способов использования произвольного форматирования чисел в файлах Excel.

Пользовательский формат ячеек — Microsoft Excel 2016

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

Чтобы определить собственный формат, выполните следующие действия:

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

2. Выполните одно из следующих действий:

• Щелкните выделение правой кнопкой мыши и выберите Форматировать ячейки… во всплывающем меню:
• На вкладке Home в группе Number щелкните значок стартовой панели:

3. На вкладке Номер в поле Категория выберите
Заказ шт.

4. В поле списка Тип выберите пользовательский формат, в котором вы
хотите создать свой новый собственный формат. Excel отображает сведения о типе в тексте Тип
коробка.

5. Если сведения о типе выходят за пределы текстового поля Тип ,
дважды щелкните текстовое поле Тип , чтобы выделить все его содержимое, введите команду Копировать (для
Например, нажмите Ctrl + C ), а затем вставьте скопированный текст в текстовый редактор, например
Блокнот . (Для более короткого шрифта вы можете эффективно работать в текстовом поле Type . Для более длинного
типа, проще иметь достаточно места, чтобы увидеть весь шрифт сразу.)

6. Введите данные для четырех частей типа, разделяя
части друг от друга через точку с запятой.

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

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

8. Нажмите кнопку ОК .

См. Также этот совет на французском языке:
Format de cellule personnalisé.

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

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

Шаги по созданию пользовательского формата Excel

1. Выберите ячейки, к которым нужно применить новый пользовательский формат.
2. На вкладке «Главная» в группе «Число» щелкните средство запуска диалогового окна.
3. В диалоговом окне «Формат ячеек» в списке «Категория» щелкните «Пользовательский».
4. Если формат, который вы хотите использовать, уже существует, выберите его, в противном случае введите свой собственный формат поверх формата, отображаемого в настоящее время в текстовом поле «Тип». Вы можете выбрать собственный формат, аналогичный тому, который вы хотите создать, внести изменения и нажать Enter.
5. Нажмите «ОК», чтобы применить настраиваемый формат к выбранным ячейкам.
6. Пользовательский формат сохраняется вместе с книгой. Если вы создаете новую книгу, пользовательский
   созданный вами формат будет недоступен.

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

Шаги по удалению пользовательского формата Excel

1. На вкладке «Главная» щелкните средство запуска диалогового окна «Число».
2. В диалоговом окне «Формат ячеек» в списке «Категория» щелкните «Пользовательский

».

3. В списке Тип выберите созданный вами пользовательский формат.Вы можете удалить только созданные вами пользовательские форматы, но не
   встроенные пользовательские форматы. Вы можете определить собственный формат, созданный вами, из встроенного, посмотрев на кнопку «Удалить».
   когда вы нажимаете на настраиваемый формат. Если кнопка «Удалить» включена, вы знаете, что она создана вами и может быть удалена.
4. Щелкните кнопку Удалить. Щелкните кнопку ОК.

Рисунок A

Глядя на рисунок A, вы можете увидеть, что в выбранной в данный момент ячейке содержится дата 4/5/2012.Поскольку ячейка содержит дату, когда
Была выбрана категория пользовательского формата, она автоматически перешла к соответствующему пользовательскому формату. Excel предоставляет четыре настраиваемых формата даты. При переходе на другой формат даты результаты будут такими, как показано ниже.

В таблице ниже показаны некоторые из настраиваемых форматов, которые вы, возможно, захотите создать самостоятельно.
Их можно ввести в текстовое поле Type в диалоговом окне «Формат ячеек».

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

* Обратите внимание, что форматы минут — это те же символы формата, которые используются для месяцев. Чтобы Excel
отличать минуты от месяцев, они должны быть помещены после часового формата или до второго формата

Вы также можете создать формат для сотых долей секунды.Формат может быть ч: мм: сс.00, где 00 — это
дробная часть секунды.

Заполнители мест пользовательского форматирования

Мы рассмотрели, как создавать настраиваемое форматирование для дат и времени, теперь давайте обратим наше внимание на то, как создавать настраиваемое форматирование для числовых значений. Excel
имеет 3 заполнителя, чтобы представить, как должна отображаться каждая цифра в числе.

Форматирование чисел и валюты

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

Знак доллара, запятую и десятичную дробь можно использовать со знаками-заполнителями.

Добавление текста в ячейку с сохранением числового значения.

Иногда вы хотите добавить текст до или после числа, но вы хотите, чтобы ячейка оставалась числовой, чтобы вы могли использовать значения ячеек в формулах. Когда вы выбираете Custom на панели Category, в поле Type появляется слово General . Текст можно заключить в кавычки до или после слова «Общие» в поле «Тип». Пример: вы можете добавить слово case после значения ячейки, введя слова case в кавычках после слова General.Не забудьте оставить пробел после левой кавычки, чтобы между числовым значением и добавляемым текстом был пробел.

Рисунок — Добавить текст справа от числового значения

Рисунок — Добавление текста к обеим сторонам числового значения

Настало время для всех хороших людей

Назначение цвета пользовательскому формату

Вы можете применить любой из следующих цветов переднего плана к настраиваемому формату.Цвет должен быть заключен в квадратные скобки и должен быть первым элементом в формате.

Пример: [Красный] ч: мм: сс AM / PM

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

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

[положительный]; [Негатив]; [Ноль]; [Текст]

Пример: «Положительный»; «Отрицательный»; «Ноль»; «Это текст»

Пользовательский формат, указанный выше, заменит:

• Положительное значение со словом Positive
• Отрицательное значение со словом Negative
• Нулевое значение со словом Zero
• текст со словами «Это текст»

Нет необходимости указывать все 4 раздела в собственном формате.Если вы не используете все 4 раздела, применяются следующие правила.

• Если указать только один раздел, он будет использоваться для всех номеров.
• Если вы укажете два раздела, первый раздел будет использоваться для положительных чисел и нулей, второй раздел будет использоваться для отрицательных чисел.
• Если вам нужно пропустить раздел кода, введите точку с запятой, чтобы заменить пропущенный раздел.

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

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


#, ###; {Красный] #, ###; 0.0; @ "отправлено"

При использовании следующего формата перед любой ячейкой, содержащей текст, будет стоять слово Mr.
#, ###; [Красный] #, ###; 0.0; "Мистер" @

Условные операторы
Excel позволяет использовать условные операторы в настраиваемом формате, но только для первых двух разделов.

Условные операторы: , =,> =,

Проверяемое условие должно быть заключено в квадратные скобки, как и цвета.

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

• Значение, выделенное синим цветом, за которым следует слово High, если его значение больше или равно 1000.
• Значение красного цвета, за которым следует слово «Низкий», если его значение меньше 200.
• Значения от 200 до 999 (включительно) будут отображаться в общем формате по умолчанию.

Пример: [Синий] [> = 1000] # "Высокий"; [Красный] [<300] # "Низкий"; Общий
.