Как установить числовой формат в excel

Как установить числовой формат в excel

В Excel имеется множество встроенных числовых форматов, но если ни один из них не удовлетворяет пользователя, то можно создать собственный числовой формат. Например, число -5,25 можно отобразить в виде дроби -5 1/4 или как (-)5,25 или 5,25- или, вообще в произвольном формате, например, ++(5)руб.###25коп. Рассмотрены также форматы денежных сумм, процентов и экспоненциального представления.

Для отображения числа можно использовать множество форматов. Согласно российским региональным стандартам ( Кнопка Пуск/ Панель Управления/ Язык и региональные стандарты ) число принято отображать в следующем формате: 123 456 789,00 (разряды разделяются пробелами, дробная часть отделяется запятой). В EXCEL формат отображения числа в ячейке можно придумать самому. Для этого существует соответствующий механизм – пользовательский формат. Каждой ячейке можно установить определенный числовой формат. Например, число 123 456 789,00 имеет формат: # ##0,00;-# ##0,00;0

Пользовательский числовой формат не влияет на вычисления, меняется лишь отображения числа в ячейке. Пользовательский формат можно ввести через диалоговое окно Формат ячеек , вкладка Число , ( все форматы ), нажав CTRL+1 . Сам формат вводите в поле Тип , предварительно все из него удалив.

Рассмотрим для начала упомянутый выше стандартный числовой формат # ##0,00;-# ##0,00;0 В дальнейшем научимся его изменять.

Точки с запятой разделяют части формата: формат для положительных значений; для отрицательных значений; для нуля. Для описания формата используют специальные символы.

  • Символ решетка (#) означает любую цифру.
  • Символ пробела в конструкции # ##0 определяет разряд (пробел показывает, что в разряде 3 цифры). В принципе можно было написать # ###, но нуль нужен для отображения 0, когда целая часть равна нулю и есть только дробная. Без нуля (т.е. # ###) число 0,33 будет отражаться как ,33.
  • Следующие 3 символа ,00 (запятая и 00) определяют, как будет отображаться дробная часть. При вводе 3,333 будут отображаться 3,33; при вводе 3,3 – 3,30. Естественно, на вычисления это не повлияет.

Вторая часть формата – для отображения отрицательных чисел. Т.е. можно настроить разные форматы для отражения положительных и отрицательных чисел. Например, при формате # ##0,00;-###0;0 число 123456,3 будет отображаться как 123 456,30, а число -123456,3 как -123456. Если формата убрать минус, то отрицательные числа будут отображаться БЕЗ МИНУСА.

Третья часть формата – для отображения нуля. В принципе, вместо 0 можно указать любой символ или несколько символов (см. статью Отображение в MS EXCEL вместо 0 другого символа ).

Есть еще и 4 часть – она определяет вывод текста. Т.е. если в ячейку с форматом # ##0,00;-# ##0,00;0;"Вы ввели текст" ввести текстовое значение, то будет отображено Вы ввели текст .

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

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

Рассмотрим пользовательские форматы на конкретных примерах.

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

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

Для того, чтобы создать пользовательский формат необходимо открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.

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

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

[цвет]"любой текст"КодФормата"любой текст"

Посмотрите простые примеры использования форматирования. В столбце А — значение без форматирования, в столбце B — с использованием пользовательского формата (применяемый формат в столбце С)

Читайте также:  Блютуз на ноутбуке не видит колонку

Какие цвета можно применять

В квадратных скобках можно указывать один из 8 цветов на выбор:

Синий, зеленый, красный, фиолетовый, желтый, белый, черный и голубой.

Далее рассмотрим коды форматов в зависимости от типа данных.

Числовые форматы

Символ Описание применения Пример формата До форматирования После форматирования
# Символ числа. Незначащие нули в начале или конце число не отображаются ###### 001234 1234
Символ числа. Обязательное отображение незначащих нулей 000000 1234 001234
, Используется в качестве разделителя целой и дробной части ####,# 1234,12 1234,1
пробел Используется в качестве разделителя разрядов # ###,#0 1234,1 1 234,10

Форматы даты

Формат Описание применения Пример отображения
М Отображает числовое значение месяца от 1 до 12
ММ Отображает числовое значение месяца в формате 00 от 01 до 12
МММ Отображает сокращенное до 3-х букв значение месяца от Янв до Дек
ММММ Полное наименование месяца Январь — Декабрь
МММММ Отображает первую букву месяца от Я до Д
Д Выводит число даты от 1 до 31
ДД Выводит число в формате 00 от 01 до 31
ДДД Выводит день недели от Пн до Вс
ДДДД Выводит название недели целиком Понедельник — Пятница
ГГ Выводит последние 2 цифры года от 00 до 99
ГГГГ Выводит год даты полностью 1900 — 9999

Стоит обратить внимание, что форматы даты можно комбинировать между собой. Например, формат "ДД.ММ.ГГГГ" отформатирует дату в привычный нам вид 31.12.2017, а формат "ДД МММ" преобразует дату в вид 31 Дек.

Форматы времени

Аналогичные форматы есть и для времени.

Формат Описание применения Пример отображения
ч Отображает часы от 0 до 23
чч Отображает часы в формате 00 от 00 до 23
м Отображает минуты от 0 до 59
мм Минуты в формате 00 от 00 до 59
с Секунды от 0 до 59
сс Секунды в формате 00 от 00 до 59
[ч] Формат истекшего времени в часах например, [ч]:мм -> 30:15
[мм] Формат истекшего времени в минутах например, [мм]:сс -> 65:20
[сс] Формат истекшего времени в секундах
AM/PM Для вывода времени в 12-ти часовом формате например, Ч AM/PM -> 3 PM
A/P Для вывода времени в 12-ти часовом формате например, чч:мм AM/PM -> 03:26 P
чч:мм:сс.00 Для вывода времени с долями секунд

Текстовые форматы

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

ДО форматирования ПОСЛЕ форматирования Примененный формат
Россия страна — Россия "страна — "@

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

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

  1. Положительные числа
  2. Отрицательные числа
  3. Нулевые значения
  4. Текстовый формат

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

Формат положительных значений ; отрицательных ; нулевых ; текстовых

Соответственно для каждой категории можно применять формат уже описанного нами вида:

[цвет]"любой текст"КодФормата"любой текст"

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

Смотрите какой эффект это дает. В зависимости от значения, меняется форматирование, а если вместо числа указано текстовое значения, то Excel выдает "нет данных".

Редактирование и копирование пользовательских форматов

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

  1. Выделить ячейки, формат которых вы хотите отредактировать.
  2. Открыть диалоговое окно Формат ячеек и перейти на вкладку Число. Можно также воспользоваться сочетанием клавиш Ctrl + 1.
  3. Изменить строку форматирования в поле Тип.

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

  1. Использовать функцию копирования по образцу.
  2. Выделить ячейки, открыть окно Формат ячеек, на вкладке Число в списке Все форматы выбрать нужный формат и нажать ОК.

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

Читайте также:  Какую нагрузку может выдержать балкон

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

Использование пользовательских форматов может облегчить просмотр и анализ данных.

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

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

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

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

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

Структура числового формата

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

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

Отображение чисел

Способ отображения числа задается шаблоном. В шаблоне можно использовать следующие символы:

  • 0 (ноль) – метка цифры. Гарантирует отображение не менее заданного количества цифр в целой части числа и заданного количества цифр в десятичной части. Количество цифр задается числом нулей в шаблоне.
  • # – метка цифры. Гарантирует отображение не более заданного количества цифр в десятичной части. Если число имеет больше цифр в десятичной части, то отображаемое число округляется.
  • ? – метка цифры. Работает так же как и метка 0, но вместо незначащих нулей целой и десятичной части отображает пробелы. Используется для выравнивания отображаемых чисел по десятичной запятой.

В шаблоне можно использовать комбинации символов 0, # и ?. Кроме того, в шаблоне можно использовать запятую для разделения целой и дробной десятичной части и пробел в качестве разделителя групп разрядов.

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

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

Отображение дат и времени

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

М Месяцы в виде чисел от 1 до 12 ММ Месяцы в виде чисел от 01 до 12 МММ Месяцы в виде Янв ММММ Месяцы в виде Январь МММММ Месяцы первой буквой месяца Д Дни в виде чисел от 1 до 31 ДД Дни в виде чисел от 01 до 31 ДДД Дни в виде Вс ДДДД Дни в виде Воскресенье ГГ Года в виде 00 – 99 ГГГГ Года в виде 1900 – 9999 ч Часы в виде чисел от 0 до 23 чч Часы в виде чисел от 00 до 23 М Минуты в виде чисел от 0 до 59 ММ Минуты в виде чисел от 00 до 59 С Секунды в виде чисел от 0 до 59 СС Секунды в виде чисел от 00 до 59
Читайте также:  Как создать массив в excel

В одном шаблоне может присутствовать комбинация кодов даты и времени. Символы шаблона можно располагать в любом порядке. Между символами можно использовать пробелы, ставить знаки препинания, скобки и т. д. Для отображения времени в 12-ти часовом формате можно добавить код АМ/РМ или А/Р.

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

Цвет шрифта

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

Цвет шрифта можно указывать для всего формата. Например, формат

устанавливает отображение любых чисел красным шрифтом.

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

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

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

Условия в пользовательских форматах

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

В качестве операторов сравнения можно использовать знаки:

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

[Синий][ 36,6]Основной устанавливает отображение чисел меньших или равных 36,6 синим шрифтом, а чисел более 36,6 – красным шрифтом.

Текст в пользовательских форматах

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

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

Текст может располагаться и перед числом и после числа, а также одновременно перед числом и после числа.

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

0,00_ "Прибыль";0,00_ "Убыток";0,00;Основной

устанавливает отображение положительных чисел с текстом Прибыль, отрицательных – с текстом Убыток, а нулевых значений без текста.

Текст можно добавлять и в форматы с условиями. Например, формат

устанавливает отображение чисел меньших или равных 36,6 с текстом Нормальная, а чисел более 36,6 – с текстом Повышенная.

Текстовый раздел в пользовательских форматах

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

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

устанавливает в ячейке отображение слова Студент перед любым тестом в ячейке, а формат

устанавливает в ячейке отображение текста — сотрудник после любого текста в ячейке.

Если в ячейку с таким форматом вводится число или дата, то Microsoft Excel воспринимает это число не как число, а как текст.

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

устанавливает в ячейке отображение даты в формате ДД.ММ.ГГ при вводе текста или цифр, не являющихся датой, в ячейке справа от введенных данных отображается текст — Ошибка.

Можно изменять цвет шрифта при использовании текстового раздела. Например, формат

Работает также, как и в предыдущем случае, но введенные ошибочные данные и текст — Ошибка будут отображены красным шрифтом.

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

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

  1. Откройте вкладку Число диалогового окна Формат ячеек.
  2. В списке Числовые форматы выберите (все форматы)
  3. В поле Тип выберите удаляемый формат и нажмите кнопку Удалить.
  4. Нажмите кнопку Закрыть.
Ссылка на основную публикацию
Как узнать полный номер телефона по короткому
Вы находитесь в регионе: Как отправить смс или пополнить баланс на прямой городской номер? Сейчас операторы сотовой связи всё активней...
Как удалить вопрос в инстаграме
Инстаграм — одна из самых популярных социальных сетей в мире. Миллионы пользователей каждый день делятся своими фотографиями, рассказывают о событиях...
Как удалить вордовский документ
Всякий раз, когда мы создаем, открываем или сохраняем документ Microsoft Word, в нем записывается дополнительная информация, называемая метаданными. То же...
Как узнать порт на андроид
Есть ли простой способ узнать, какие порты открыты на моем устройстве Android и как закрыть открытые порты? Создайте точку доступа...
Adblock detector