Как создать пользовательский формат ячейки в Excel
Обновлено: 20.11.2024
Пользовательские числовые форматы управляют тем, как числа выглядят в Excel. Основное преимущество числовых форматов заключается в том, что они изменяют внешний вид числа без изменения каких-либо данных. Это отличный способ сэкономить время в Excel, потому что они автоматически выполняют огромное количество операций форматирования. В качестве бонуса они делают рабочие листы более профессиональными.
Введение
Числовые форматы определяют способ отображения чисел в Excel. Основное преимущество числовых форматов заключается в том, что они изменяют внешний вид числа без изменения каких-либо данных. Это отличный способ сэкономить время в Excel, потому что они автоматически выполняют огромное количество операций форматирования. В качестве бонуса они делают рабочие листы более последовательными и профессиональными.
Что можно делать с пользовательскими числовыми форматами?
Пользовательские числовые форматы могут управлять отображением чисел, дат, времени, дробей, процентов и других числовых значений. Используя настраиваемые форматы, вы можете выполнять такие действия, как форматирование дат для отображения только названий месяцев, форматирование больших чисел в миллионах или тысячах и отображение отрицательных чисел красным цветом.
Где вы можете использовать собственные числовые форматы?
Многие области в Excel поддерживают числовые форматы. Их можно использовать в таблицах, диаграммах, сводных таблицах, формулах и непосредственно на листе.
- Рабочий лист — диалоговое окно форматирования ячеек
- Сводные таблицы — через настройки полей значений
- Диаграммы — метки данных и параметры осей
- Формулы — через функцию ТЕКСТ
Что такое числовой формат?
Числовой формат — это специальный код, управляющий отображением значения в Excel. Например, в таблице ниже показано 7 различных числовых форматов, примененных к одной и той же дате 1 января 2019 года:
| Ввод | Код | Результат |
|---|---|---|
| 1 января 2019 г. | гггг | 2019 |
| 1 января 2019 г. | гг | td>19 |
| 1-янв-2019 | ммм | янв | 1 января 2019 г. | мммм | Январь |
| 1 января 2019 г. | < td>d1 | |
| 1 января 2019 г. | ddd | Вт |
| 1 января 2019 г. | dddd | вторник |
Главное, что нужно понять, это то, что числовые форматы изменяют способ отображения числовых значений, но они не меняют фактические значения.
Где можно найти числовые форматы?
На главной вкладке ленты вы найдете меню встроенных числовых форматов. Под этим меню справа есть небольшая кнопка для доступа ко всем числовым форматам, включая пользовательские форматы:
Эта кнопка открывает диалоговое окно «Формат ячеек». Вы найдете полный список числовых форматов, упорядоченный по категориям, на вкладке «Число»:
Примечание: диалоговое окно "Формат ячеек" можно открыть с помощью сочетания клавиш Control + 1.
Общие по умолчанию
По умолчанию ячейки начинаются с примененного общего формата. Отображение чисел в общем числовом формате несколько «плавное». Excel будет отображать столько знаков после запятой, сколько позволяет место, и будет округлять десятичные числа и использовать формат научного числа, когда пространство ограничено. На приведенном ниже экране показаны те же значения в столбцах B и D, но D более узкий, и Excel вносит коррективы на лету.
Как изменить числовой формат
Вы можете выбрать стандартные числовые форматы (Общий, Числовой, Валюта, Бухгалтерский учет, Короткая дата, Полная дата, Время, Проценты, Дробь, Научный, Текст) на главной вкладке ленты с помощью меню "Числовой формат".
Примечание. При вводе данных Excel иногда автоматически меняет числовой формат. Например, если вы введете допустимую дату, Excel изменит формат на «Дата». Если вы введете процент, например 5 %, Excel изменится на Проценты и т. д.
Ярлыки для числовых форматов
Excel предоставляет ряд сочетаний клавиш для некоторых распространенных форматов:
Где вводить пользовательские форматы
Внизу предустановленных форматов вы увидите категорию "Пользовательские".В категории «Пользовательский» отображается список кодов, которые можно использовать для пользовательских числовых форматов, а также область ввода для ввода кодов вручную в различных комбинациях.
При выборе кода из списка он отображается в поле ввода Тип. Здесь вы можете изменить существующий пользовательский код или ввести свои собственные коды с нуля. Excel покажет небольшой предварительный просмотр кода, примененного к первому выбранному значению над областью ввода.
Примечание. Пользовательские числовые форматы обычно хранятся в рабочей книге, а не в Excel. Если вы скопируете значение, отформатированное в пользовательском формате, из одной книги в другую, пользовательский числовой формат будет перенесен в книгу вместе со значением.
Как создать собственный числовой формат
Чтобы создать собственный числовой формат, выполните простой четырехэтапный процесс:
- Выберите ячейки со значениями, которые вы хотите отформатировать.
- Control + 1 > Числа > Пользовательский
- Введите коды и просмотрите область предварительного просмотра, чтобы увидеть результат.
- Нажмите OK, чтобы сохранить и применить
Совет. Если вы хотите создать собственный формат на основе существующего формата, сначала примените базовый формат, затем выберите категорию "Пользовательский" и отредактируйте коды по своему усмотрению.
Как изменить пользовательский числовой формат
Вы не можете редактировать пользовательский числовой формат как таковой. При изменении существующего пользовательского числового формата создается новый формат, который отображается в списке в категории «Пользовательский». Вы можете использовать кнопку Удалить, чтобы удалить пользовательские форматы, которые вам больше не нужны.
Внимание! После удаления пользовательского числового формата "отменить" нельзя!
Структура и ссылки
Пользовательские числовые форматы Excel имеют особую структуру. Каждый числовой формат может иметь до четырех разделов, разделенных точкой с запятой следующим образом:
Из-за этой структуры пользовательские числовые форматы могут выглядеть слишком сложными. Чтобы прочитать пользовательский числовой формат, научитесь замечать точки с запятой и мысленно разбирать код на следующие разделы:
- Положительные значения
- Отрицательные значения
- Нулевые значения
- Текстовые значения
Обязательны не все разделы
Хотя числовой формат может включать до четырех разделов, требуется только один раздел. По умолчанию первый раздел применяется к положительным числам, второй — к отрицательным, третий — к нулевым значениям, а четвертый — к тексту.
- Если указан только один формат, Excel будет использовать этот формат для всех значений.
- Если вы предоставляете числовой формат только с двумя разделами, первый раздел используется для положительных чисел и нулей, а второй раздел — для отрицательных чисел.
- Чтобы пропустить раздел, поставьте точку с запятой в нужном месте, но не указывайте код формата.
Символы, которые отображаются изначально
Некоторые символы обычно отображаются в числовом формате, а другие требуют специальной обработки. Следующие символы можно использовать без какой-либо специальной обработки:
| Символ | Комментарий |
|---|---|
| $ | Доллар |
| +- | Плюс, минус |
| () | Круглые скобки |
| <> | Фигурные скобки |
| <> | Меньше чем, больше, чем |
| = | Равно |
| : | Двоеточие |
| ^ | Каретка |
| ' | Апостроф |
| / | Косая черта |
| ! | Восклицательный знак |
| & | амперсанд |
| ~ | тильда |
| Пробел |
Эскейп-символы
Заполнители
Некоторые символы имеют особое значение в кодах пользовательских числовых форматов. Следующие символы являются ключевыми строительными блоками:
Ноль (0) используется для принудительного отображения незначащих нулей, когда в числе меньше цифр, чем нулей в формате. Например, пользовательский формат 0,00 будет отображать ноль как 0,00, 1,1 как 1,10 и 0,5 как 0,50.
Вопросительный знак (?) используется для выравнивания цифр. Когда вопросительный знак занимает в числе ненужное место, для визуального выравнивания добавляется пробел.
Точка (.) – это заполнитель для десятичной точки в числе. Когда точка используется в пользовательском числовом формате, она всегда будет отображаться, независимо от того, содержит ли число десятичные значения.
Запятая (,) – это разделитель тысяч в отображаемом числе. Его можно использовать для определения поведения цифр по отношению к тысячам или миллионам цифр.
Звездочка (*) используется для повторения символов. Символ, следующий за звездочкой, будет повторяться, чтобы заполнить оставшееся место в ячейке.
Подчеркивание (_) используется для добавления пробела в числовом формате. Символ, следующий сразу за символом подчеркивания, определяет, сколько места нужно добавить. Обычно символ подчеркивания используется для добавления пробела для выравнивания положительных и отрицательных значений, когда числовой формат добавляет круглые скобки только к отрицательным числам. Например, числовой формат "0_);(0)" добавляет немного пробела справа от положительных чисел, чтобы они оставались выровненными с отрицательными числами, заключенными в круглые скобки.
В (@) — заполнитель для текста. Например, в следующем числовом формате текстовые значения будут отображаться синим цветом:
Подробнее об использовании цвета см. ниже.
Автоматическое округление
Важно понимать, что Excel будет выполнять "визуальное округление" со всеми пользовательскими числовыми форматами. Если число содержит больше цифр, чем заполнителей справа от десятичной точки, число округляется до количества заполнителей. Когда в числе больше цифр, чем заполнителей слева от десятичной точки, отображаются дополнительные цифры. Это только визуальный эффект; фактические значения не изменяются.
Числовые форматы для ТЕКСТА
Чтобы отобразить текст вместе с числами, заключите текст в двойные кавычки (""). Вы можете использовать этот подход для добавления или добавления текстовых строк в произвольном числовом формате, как показано в таблице ниже.
Числовые форматы для ДАТЫ
Даты в Excel — это просто числа, поэтому вы можете использовать собственные числовые форматы, чтобы изменить способ их отображения. В Excel есть много специальных кодов, которые можно использовать для отображения компонентов даты по-разному. На приведенном ниже экране показано, как Excel отображает дату в формате D5, 3 сентября 2018 года, с различными форматами пользовательских чисел:
Числовые форматы для TIME
Время в Excel — это дробные части дня. Например, 12:00 — это 0,5, а 18:00 — 0,75. Вы можете использовать следующие коды в пользовательских форматах времени для отображения компонентов времени различными способами. На приведенном ниже экране показано, как Excel отображает время в D5, 9:35:07, с различными форматами пользовательских чисел:
Примечание. М и мм нельзя использовать по отдельности в пользовательском числовом формате, поскольку они конфликтуют с кодом номера месяца в кодах формата даты.
Форматы чисел для ПРОШЕДШЕГО ВРЕМЕНИ
Прошедшее время — это особый случай, требующий специальной обработки. Используя квадратные скобки, Excel предоставляет особый способ отображения прошедших часов, минут и секунд. На следующем экране показано, как Excel отображает прошедшее время на основе значения в D5, которое соответствует 1,25 дням:
Числовые форматы для ЦВЕТА
Excel обеспечивает базовую поддержку цветов в настраиваемых числовых форматах. Следующие 8 цветов могут быть указаны по имени в числовом формате: [черный] [белый] [красный] [зеленый] [синий] [желтый] [пурпурный] [голубой]. Названия цветов должны быть указаны в квадратных скобках.
Цвета по индексу
В дополнение к названиям цветов также можно указать цвета с помощью порядкового номера (Цвет1,Цвет2,Цвет3 и т. д.). В приведенных ниже примерах используется пользовательский числовой формат: [ЦветX]0"▲▼", где X число от 1 до 56:
Треугольные символы были добавлены только для того, чтобы цвета было лучше видно. На первом изображении показаны все 56 цветов на стандартном белом фоне. На втором изображении показаны те же цвета на сером фоне. Обратите внимание, что первые 8 показанных цветов соответствуют названному выше списку цветов.
Применить числовой формат в формуле
Хотя большинство числовых форматов применяются непосредственно к ячейкам на листе, вы также можете применять числовые форматы внутри формулы с помощью функции ТЕКСТ. Например, с допустимой датой в формате A1 следующая формула будет отображать только название месяца:
Результатом функции ТЕКСТ всегда является текст, поэтому вы можете объединять результат ТЕКСТ с другими строками:
На приведенном ниже экране показано, как форматы чисел в столбце C применяются к числам в столбце B с помощью функции ТЕКСТ:
Измерения
Эти результаты являются упрощенными и не могут быть объединены в единый числовой формат. Однако вы можете использовать формулу для отображения футов вместе с дюймами.
Условные выражения
Пользовательские числовые форматы также могут содержать до двух условий, которые записываются в квадратных скобках, например [>100] или [=100]0
Чтобы применить более двух условий или изменить другие атрибуты ячейки, такие как цвет заливки и т. д., вам потребуется переключиться на условное форматирование, которое может применить форматирование с гораздо большей силой и гибкостью с помощью формул.
Множественные текстовые метки
Вы можете использовать условные операторы, чтобы добавить "s" к меткам больше нуля в пользовательском формате, подобном этому:
Номера телефонов
Пользовательские форматы номеров также можно использовать для телефонных номеров, как показано на экране ниже:
Обратите внимание, что в третьем и четвертом примерах используется условный формат для проверки номеров, содержащих код города. Если у вас есть данные, содержащие телефонные номера с жестко запрограммированной пунктуацией (круглые скобки, дефисы и т. д.), вам потребуется сначала очистить телефонные номера, чтобы они содержали только цифры.
Скрыть все содержимое
Вы можете использовать собственный числовой формат, чтобы скрыть все содержимое в ячейке. Код состоит из трех точек с запятой и больше ничего ;;;
Чтобы снова открыть содержимое, вы можете использовать сочетание клавиш Control + Shift + ~, которое применяет общий формат.
Excel имеет множество встроенных форматов, которые вы можете использовать: Валюта, Бухгалтерский учет, Дата, Время, Проценты и т. д. Если вы не можете найти правильный формат, вы можете создать собственный числовой формат.
Ведущие нули
Например, у вас могут быть коды, состоящие из 5 цифр. Вместо ввода 00041 просто введите 41 и позвольте Excel добавить начальные нули.
<р>1. Введите значение 41 в ячейку A1. <р>2.Выберите ячейку A1, щелкните правой кнопкой мыши и выберите "Формат ячеек". <р>3. Выберите Пользовательский. <р>4. Введите следующий код числового формата: 00000
Примечание. Excel дает предварительный просмотр того, как число будет отформатировано (в разделе «Образец»).
Примечание: ячейка A1 по-прежнему содержит число 41. Мы изменили только внешний вид этого числа, но не само число.
Десятичные разряды
Вы также можете контролировать количество знаков после запятой. Используйте 0 для отображения ближайшего целочисленного значения. Используйте 0,0 для одного десятичного знака. Используйте 0,00 для двух знаков после запятой и т. д.
<р>1. Введите значение 839,1274 в ячейку A1. <р>2. Используйте следующий код числового формата: 0,00
Добавить текст
К номерам также можно добавить текст. Например, добавьте "ft".
<р>1. Введите значение 839,1274 в ячейку A1. <р>2. Используйте следующий код числового формата: 0,0 "ft"
Примечание: помните, мы изменили только внешний вид этого номера, а не сам номер. Вы по-прежнему можете использовать это число в своих расчетах.
Большие числа
Вы также можете управлять большими числами. Используйте одну запятую (,) для отображения тысяч и две запятые (,,) для отображения миллионов.
<р>1. Введите следующие значения в ячейки A1, B1, C1 и D1: 1000000, 2500000, 81000000 и 700000. <р>2. Используйте следующий код числового формата: 0.0,, "M" Ширина
Примечание: мы использовали 0,0 для одного десятичного разряда и "М" для добавления буквы М.
Повторяющиеся символы
Используйте звездочку (*), за которой следует символ, чтобы заполнить ячейку этим символом.
<р>1. Введите Hi в ячейку A1. <р>2. Используйте следующий код числового формата: @ *-
Примечание: символ @ используется для ввода текста.
Цвета
Вы можете управлять положительными числами, отрицательными числами, нулевыми значениями и текстом одновременно! Каждая часть в коде числового формата отделяется точкой с запятой (;).
<р>1. Введите следующие значения в ячейки A1, B1, C1 и A2: 5000000, 0, Hi и -5,89.
Даты и время
Вы также можете управлять датами и временем. Используйте один из существующих форматов даты или времени в качестве отправной точки.
<р>1. Введите значение 42855 в ячейку A1. <р>2. Выберите ячейку A1, щелкните правой кнопкой мыши и выберите "Формат ячеек". <р>3. Выберите «Дата» и выберите «Длинная дата».
Примечание. Excel дает предварительный просмотр того, как число будет отформатировано (в разделе «Образец»).
<р>4. Выберите Пользовательский. <р>5. Немного измените код числового формата на: мм/дд/гггг, дддд
Общее примечание. Пользовательский числовой формат сохраняется в книге, в которой вы его создаете. Если вы скопируете значение в пользовательском числовом формате в другую книгу, оно также будет доступно в этой книге.
Блог, ориентированный в основном на Microsoft Excel, PowerPoint и Word, со статьями, призванными вывести ваши навыки анализа данных и работы с электронными таблицами на новый уровень. Научитесь всему: от создания информационных панелей до автоматизации задач с помощью кода VBA!
Мне нравится объяснять числовые форматы в Excel как одежду ячейки электронной таблицы.Вы можете оформить значения ячеек как угодно, но даже при изменении внешнего вида базовое значение останется неизменным.
Эта концепция чаще всего используется с датами.
Знаете ли вы, что дата на самом деле представляет собой просто числовое число? Вы можете увидеть истинное значение использования даты в приведенном выше примере, посмотрев на результат формата «Общий». Однако большинство людей украшают это число так, чтобы оно выглядело как свидание с косой чертой.
Правило Пользовательский числовой формат состоит из четырех частей или разделов. Первый раздел является обязательным, а остальные три не являются обязательными. Каждый раздел разделен точкой с запятой ( ; ). Вот что представляет собой каждая часть правила числового формата:
Если число положительное, сделайте следующее…
Если число отрицательное, сделайте следующее…
Если число равно нулю, сделайте следующее…
Если значение не является числом, сделайте следующее…
Несколько предостережений
Если только в первом разделе есть правило формата, оно будет применяться ко всем числовым значениям, как положительным, так и отрицательным (текстовые значения останутся в силе)
Если только первые два раздела имеют правила формата, для нулевых значений будет использоваться формат положительного значения
Чтобы написать собственные правила числового формата, вам потребуется перейти в редактор правил. Редактор находится в диалоговом окне «Формат ячеек», где вы можете изменить все свойства/форматы ячейки.
Существует несколько способов перехода к диалоговому окну "Формат ячеек":
[Метод 1] Щелкните правой кнопкой мыши ячейку >> Выберите «Формат ячеек…»
[Метод 2] Вкладка «Главная» >> Группа цифровых кнопок >> щелкните серую стрелку в нижнем углу
[Способ 3] Используйте сочетание клавиш: ctrl + 1 (ПК) | cmd + 1 (Mac)
Открыв диалоговое окно «Формат ячеек», вам нужно будет перейти на вкладку «Число». На этой вкладке будет показано множество предустановленных правил числового формата, по которым вы можете перемещаться, или, если вы хотите написать свое собственное правило, вы можете перейти в самый низ панели категорий и нажать "Пользовательский".
Существует несколько специальных символов, которые можно использовать при написании правила Пользовательский числовой формат, чтобы добавить еще больше разнообразия внешнему виду значения. Давайте сначала рассмотрим доступные вам специальные символы, а затем перейдем к некоторым примерам.
Просто включив один из этих символов, ваше правило Пользовательский числовой формат автоматически использует его особую возможность. Если вы хотите включить один из этих символов без их возможности, узнайте, как это сделать, в разделе «Экранирование» этой статьи (прокрутите несколько разделов вниз).
Символ @
Символ @ используется для управления тем, где ваше текстовое значение отображается в вашем правиле. Вы можете вносить изменения в текстовое значение до или после текста, перемещая символ @ внутри правила.
Символ запятой
Запятая может использоваться для разделения ваших чисел на тысячи или для округления больших чисел до определенного разряда (миллионы, миллиарды и т. д.).
Если вы поставите запятую перед единицей, вы сможете увидеть запятую, разделяющую ваше значение через каждые три позиции. Вам нужно использовать только одну запятую, чтобы активировать этот формат.
Если вы поставите запятую после единицы, значение ВИЗУАЛЬНО потеряет три позиции (фактически делит 1000). Такое поведение сохраняется для каждой дополнительной запятой, которую вы добавляете после единицы.
Символ звездочки
Символ звездочки можно использовать для заполнения оставшегося пространства в ячейке символом, следующим сразу за ним. Обычно я вижу это только тогда, когда кто-то создает список контактов, где он хотел бы, чтобы точки соединяли значения в столбце 1 и столбце 2.
Нулевой номер
Использование нуля в правиле числового формата приведет к визуальному отображению этого числа. Если вы хотите, чтобы все ваши числа отображали три цифры, вставьте три нуля в свое правило, и 1 будет равняться 001. Это может быть очень обычным делом при очистке ваших числовых значений, чтобы убедиться, что все они визуально совпадают друг с другом.
Символ фунта стерлингов/решетки
Символ фунта стерлингов (или решетка) служит дополнительным заполнителем для цифр, если они существуют. Если значение превышает количество знаков фунта справа от вашего десятичного знака, правило формата округлит ваше значение, чтобы оно соответствовало указанному количеству знаков фунта. Если в вашем значении меньше цифр, чем знаков фунта, ноль не будет стоять на его месте.
Знак вопроса
Вопросительные знаки можно использовать для выравнивания цифр, если вы не хотите, чтобы нули отображались в качестве числовых заполнителей. Когда вопросительный знак находится в месте, где не указано значение, будет добавлен пробел (показан серым цветом ниже), чтобы сохранить выравнивание числа.
Символ подчеркивания
Используя символ подчеркивания, вы можете добавить один пробел до или после значения ячейки.Символ, следующий за символом подчеркивания, определяет размер пробела. В большинстве случаев пользователи Excel используют символ подчеркивания для выравнивания положительных и отрицательных чисел, в которых используются круглые скобки.
Могут быть случаи, когда вы буквально захотите использовать одного из вышеперечисленных персонажей вместо использования их особых способностей. Чтобы удалить специальную способность (или «убежать» от способности), просто поставьте перед персонажем обратную косую черту. Вам нужно будет поставить обратную косую черту перед каждым отдельным символом, который вы хотите экранировать.
Могут быть случаи, когда вы хотели бы добавить текст до или после ваших значений, но все еще хотели бы выполнить математику электронной таблицы с вашими данными. С помощью правил Пользовательский числовой формат мы можем легко добиться того, чтобы числовые значения отображались в виде текстовых изображений, сохраняя при этом их значения в ячейках.
Есть много других вещей, которые вы можете делать с помощью правил Пользовательского числового формата. Ниже приведены ссылки на другие статьи/инструменты, которые могут оказаться полезными, поскольку вы продолжаете узнавать больше о создании этих настраиваемых правил.
Об авторе
Привет! Меня зовут Крис, и в свободное время я веду сайт TheSpreadsheetGuru. Днём я на самом деле профессионал в области финансов, который довольно сильно полагается на Microsoft Excel в корпоративном мире. Мне нравится брать то, что я узнаю в «реальном мире», и делиться им со всеми здесь, на этом сайте, чтобы вы тоже могли стать гуру электронных таблиц в своей компании.
За годы работы в корпоративном мире я смог использовать возможности для улучшения работы с Excel и создал множество надстроек Excel, от вставки символов деления до автоматического копирования/вставки из Excel в Силовая установка. Если вы хотите быть в курсе последних новостей Excel и напрямую получать по электронной почте наиболее важные советы по Excel, которые я узнал за эти годы, вы можете подписаться на мои бесплатные информационные бюллетени. Я надеюсь, что смог принести вам некоторую пользу сегодня, и надеюсь скоро увидеть вас снова здесь! - Крис
Узнайте, как использовать Excel, из руководств, советов и рекомендаций по функциям, формулам и возможностям.
В Excel есть множество встроенных числовых форматов, но иногда вам нужно что-то особенное. Независимо от того, представляете ли вы малоиспользуемую валюту, отслеживаете единицы на складе или хотите раскрасить прибыли и убытки, вам нужен настраиваемый числовой формат Excel. Форматирование чисел в Excel довольно мощное, но это означает, что оно также несколько сложное. Это исчерпывающее руководство по пользовательским числовым форматам Excel…
Использование пользовательских числовых форматов в Excel
По умолчанию каждая ячейка отформатирована как «Общая», что означает отсутствие каких-либо специальных правил форматирования. Когда вы вводите данные в ячейку, Excel пытается угадать, в каком формате они должны быть. Когда он не угадывается правильно, вам нужно изменить формат. В Excel есть несколько предустановленных параметров форматирования, прикрепленных к кнопкам в главном меню, но если они вам не подходят, вам нужно использовать все параметры, доступные в меню «Формат ячеек».
Чтобы получить доступ к этому меню, найдите раздел «Число» на вкладке «Главное меню». Нажмите на стрелку в правом нижнем углу раздела "Число".
Откроется меню "Формат ячеек" на вкладке "Числа":
Под предопределенными числовыми форматами для общих элементов, таких как валюта и проценты, есть категория, называемая "Пользовательский". Типы форматов в этом разделе отличаются от предустановленных параметров. Они заполнены символами и кодами:
Код числового формата вводится в поле «Тип» в категории «Пользовательский». Эти коды являются ключом к созданию любого пользовательского числового формата в Excel. Однако сначала нам нужно понять, как они работают…
Знакомство с кодами числовых форматов
Коды числового формата — это строка символов, определяющая, как Excel отображает данные, хранящиеся в ячейках. Мы рассмотрим способы описания форматов через минуту, но сначала нам нужно рассмотреть, как Excel интерпретирует эти символы. Каждый код числового формата состоит из 4 разделов, разделенных точкой с запятой (;).
Эти разделы управляют форматированием одной или нескольких частей числовой строки, включая положительные числа, отрицательные числа и нули.Они также могут управлять форматированием подмножеств этих частей, таких как все числа больше 100 и текстовые данные. То, что контролирует каждый раздел, зависит от того, сколько разделов содержится в коде числового формата. Код полного числового формата будет введен следующим образом:
Поведение различных частей числовой прямой будет следующим:
Как указано выше, если предоставляется только один раздел, он описывает формат для всех чисел. С двумя первый раздел описывает формат положительных, нулевых и текстовых значений, а второй раздел описывает формат отрицательных значений и т. д.
Вы можете пропустить форматирование любого из средних разделов, введя «Общие» вместо других данных форматирования. Например, если вы хотите воздействовать только на положительные числа и текст, вы можете ввести код числового формата следующим образом:
Общий удаляет все форматирование из введенных данных, поэтому будьте осторожны при его использовании. Отрицательные числа с кодом общего формата не будут отображать знак минус перед своим числом.
Важное примечание. Использование одного кода формата номеров разделов не всегда дает тот же результат, что и распространение одних и тех же правил на все разделы. Например:
Не то же самое, что:
Посмотрите на примеры ниже, чтобы увидеть примеры различий…
Теперь, когда мы понимаем, что такое код числового формата, что мы можем с ним сделать?
Изменение цвета шрифта с помощью кодов числового формата
Одна из самых простых вещей, которые вы можете сделать с кодами числового формата, — это изменить цвет шрифта в затронутых ячейках. Синтаксис для этого прост:
Просто выберите раздел, соответствующий той части числовой строки, цвет которой вы хотите изменить, и укажите цвет в скобках. Варианты цвета следующие (фон в таблице серый для контраста, но код формата чисел не влияет на фон):
Например, мы можем предоставить отдельный цветовой код для каждой части кода числового формата:
Сообщение "Общие" просто сообщает Excel о необходимости представления чисел, введенных пользователем. Вывод этого кода числового формата выглядит следующим образом:
Обратите внимание, что перед отрицательным числом в строке 3 автоматически не ставится знак минус (-). Мы переопределяем формат отрицательных чисел по умолчанию в ячейке. Также обратите внимание, что цветовой формат никак не влияет на презентацию; количество десятичных разрядов остается прежним, равно как и выравнивание данных слева или справа от ячеек.
Добавление текста с кодами числового формата
Вы можете добавить текст вокруг чисел с кодами числового формата, вставив текст в раздел одним из двух способов:
Отдельные символы
Для одиночных символов (например, символа @ перед числом) введите обратную косую черту (\), за которой следует символ. Код числового формата:
Результаты в следующем выводе:
Обратите внимание, что перед отрицательным числом по-прежнему стоит знак минус. Также обратите внимание, что на значение Text не влияет добавление символа @.
Важно отметить, что это отличается от результата, если бы мы расширили наше руководство по формату до каждого раздела кода числового формата:
Результаты в следующем выводе:
Обратите внимание, что из отрицательного числа исчез знак минус, а значение Text теперь получает символ @.
Текстовые строки
Чтобы добавить к числу целую текстовую строку (например, добавить "единицы" в конец числа), мы заключаем текстовую строку в кавычки (""). Код числового формата:
Результаты в следующем выводе:
Еще раз, это отличается от:
Что приводит к:
Специальные символы
В дополнение к этим двум методам существует набор специальных символов, которые не требуют включения обратной косой черты или кавычек в код числового формата. Список выглядит следующим образом:
Excel также поддерживает большинство других нематематических символов, таких как символы валюты, отличной от доллара, символы авторских прав/торговых марок и греческие буквы. Эти символы недоступны на большинстве стандартных клавиатур, но их можно ввести, удерживая клавишу ALT при вводе четырехзначного числа. Некоторые из наиболее полезных приведены ниже:
Полный список кодов символов ANSI можно найти в Википедии здесь.
Изменение десятичных разрядов, значащих цифр и запятых
Добавление символов и цветов полезно, но большая часть работы, которую вам, скорее всего, придется выполнить с пользовательскими числовыми форматами, — это изменить способ отображения чисел в Excel. Коды числового формата используют набор символов для представления того, как данные должны отображаться в ячейке. Вот краткое описание символов:
Давайте рассмотрим их по очереди…
Ноль (0)
Нали в коде числового формата представляют собой принудительную цифру. Это означает, что независимо от того, соответствует ли цифра значению, она будет показана. Отличным примером этого является стандартное обозначение долларов и центов, которое используется для представления цен в Соединенных Штатах: 0,00 доллара США. Даже если в сумме нет лишних центов, в обозначении все равно отображаются два нуля.
Вот пример нулевого кода в действии. В следующих примерах используется этот код числового формата:
Вопросительный знак (?)
Вопросительные знаки в коде числового формата представляют собой цифру выравнивания. Это означает, что когда отображаемое число не нуждается в рассматриваемой цифре, используется пробел того же размера. Это используется для выравнивания десятичных разрядов и запятых для более удобного ранжирования значений и т. д.
Вот пример кода вопросительного знака в действии. В следующих примерах используется этот код числового формата:
Знак решетки в коде числового формата, который иногда называют решеткой, представляет собой необязательную цифру. Это означает, что когда отображаемому номеру не нужна рассматриваемая цифра, она будет исключена из отображаемого номера. Это чаще всего используется для представления чисел в их наиболее удобной для чтения форме.
Вот пример кода со знаком решетки в действии. В следующих примерах используется этот код числового формата:
Точка (.)
Точка в коде числового формата представляет собой расположение десятичной точки в отображаемом числе. В сочетании с кодом запятой он может отображать числа в тысячах или миллионах, изменяя, например, 1200 на 1,2. Он похож на приведенные выше коды текстового формата в том, что он всегда отображается, когда он является частью числового кода, даже если отображаемое число не пересекает десятичную точку. См. приведенные выше примеры запятой, знака фунта и вопросительного знака для полезных иллюстраций используемой точки.
Запятая (,)
Запятая в коде числового формата представляет разделители тысяч в отображаемом числе. Он позволяет вам описать поведение цифр по отношению к тысячам или миллионам цифр.
Вот пример кода запятой в действии. В следующих примерах используется этот код числового формата:
Звездочка (*)
Звездочка в коде числового формата обозначает модификатор повторяющегося символа. Он используется вместе с символом для отображения повторяющейся цифры, заполняющей пустое место в ячейке.
Вот пример кода звездочки в действии. В следующих примерах используется этот код числового формата:
Подчеркивание (_)
Подчеркивание в числовом коде обозначает модификатор пробела. Он используется вместе с символом для отображения пробела, равного по размеру указанному символу. Его можно использовать, например, для правильного выравнивания положительных и отрицательных чисел, когда круглые скобки используются только в отрицательном регистре.
Вот пример кода подчеркивания в действии. В следующих примерах используется этот код числового формата:
Использование дробей, процентов и экспоненциального представления
Некоторые типы обозначений требуют использования символов для обозначения изменения формата, включая дроби, проценты и экспоненциальное представление. Вот сводка символов для каждого:
Мы подробно рассмотрим каждый…
Дроби (/)
Дроби особенные, так как они требуют изменения единиц измерения. Число 0,23 представлено как 23/100, но 0,25 может быть упрощено до 1/4 или показано как 25/100. Точно так же 1,25 можно представить как 1 1/4 или неправильную дробь 5/4. То, как Excel отображает число, зависит от того, как вы создаете код числового формата.
Дроби эффективно округляют значения до ближайшей возможной дроби. Они также следуют указаниям знаков фунта и вопросительных знаков, с которыми они сочетаются.
Целое число с сокращенными дробями
Довольно типичным представлением дробей является то, что целые числа не зависят от остатка дроби. Представление для этого относительно простое и может быть выполнено с помощью знаков фунта и вопросительных знаков для немного другого эффекта…
Используя вопросительный знак (?), следующий код числового формата:
Выдает остаток до трех цифр:
Выравнивание дробной черты сохраняется независимо от количества используемых цифр. Если мы ограничим количество цифр с каждой стороны дроби до 2, Excel округлит число до ближайшего значения дроби. Следующий формат числового кода:
Изменяет представление 0,23 с 23/100 на 3/13.
Если вы не хотите сохранять выравнивание по дробной черте, вы можете использовать аналогичный код формата числа дроби, в котором используются знаки решетки.
Со знаком фунта следующий код числового формата:
Создает более удобочитаемый остаток дроби, который можно выровнять по ширине или центрировать в ячейке:
Неправильные дроби
Если вы предпочитаете объединять целую числовую часть значения в саму дробь, вы можете указать это в коде числового формата.
Со знаком фунта следующий код числового формата:
Выдает неправильную дробь, содержащую до трех цифр:
Дроби с фиксированным основанием
Также можно заставить Excel округлять дроби до определенного знаменателя, указав его в коде числового формата.
Вот пример фиксированного базового кода в действии. В следующих примерах используется этот код числового формата:
Результатом является округленный остаток дроби, доходящий до ближайшего числа 15.
Проценты (%)
Как и дроби, проценты контролируются кодами числового формата, которые их сопровождают. Базовый процент можно получить с помощью символа фунта в коде числового формата:
Результаты в следующем выводе:
Вы также можете указать дробные проценты, как показано в следующем коде числового формата:
Результаты представлены однозначными дробями в процентах, где это необходимо:
Наконец, как всегда, вы можете указать количество значащих цифр с десятичными разрядами:
Результаты в десятичном разряде с выравниванием по 10-му разряду:
Научная нотация (E)
Чрезвычайно маленькие и очень большие числа сложно читать традиционным способом из-за множества нулей в начале и в конце. Научная запись исправляет это, перемещая десятичную дробь в соответствующие цифры, поэтому 0,0000001 может стать 1 x 10 -7 . Excel использует для этого нотацию E, так что тот же номер будет 1E-07. Так что, как и следовало ожидать, заглавная буква Е обозначает экспоненциальное представление в коде числового формата.
В противном случае экспоненциальное представление в Excel управляется теми же числовыми кодами, что и проценты и дроби. Ему нужен код числового формата перед буквой E для описания соответствующих цифр, плюс (+) и другой код числового формата позади для описания обработки экспоненциальной цифры.
Вот пример кода экспоненциальной записи в действии. В следующих примерах используется этот код числового формата:
Вы также можете добиться более последовательной записи с помощью нулей. В следующих примерах используется этот код числового формата:
Обратите внимание, что в этом случае десятичная дробь и показатель степени ограничены двумя значащими цифрами, независимо от того, необходимы ли они. Компромисс заключается в том, что вывод остается гораздо более согласованным с предсказуемой длиной строки.
Даты и время
Даты и время в Excel — это особый случай. Подробное обсуждение того, как их использует Excel, см. в Полном руководстве по использованию дат и времени в Excel. Коды числового формата работают так же, как ввод format_text для команды TEXT, и их можно просмотреть здесь.
Получите последние советы и рекомендации по Excel, подписавшись на информационный бюллетень!
Эндрю Робертс уже более десяти лет решает бизнес-задачи с помощью Microsoft Excel. Excel Tactics призван помочь вам в этом.
Подпишитесь на информационный бюллетень, чтобы быть в курсе последних статей. Зарегистрируйтесь, и вы получите бесплатное руководство с 10 быстрыми клавишами для экономии времени!
Читайте также: