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

Добавил пользователь Morpheus
Обновлено: 18.09.2024

Данные в ячейках могут иметь различный смысл: текст, числа, дроби, даты, проценты. Вот пример: какой процент от 20000 составляет 4000?

Но догадаться, что речь идет о процентах, можно только из объяснения или из подписи.

Excel, как программа, работает с числами, поэтому важно указать программе какой формат числе сейчас в работе.

Задается формат ячейки на ленте Главная → группа команд Число → кнопка выпадающего меню Числовой формат:

Общий – только для чисел и текста
Числовой – для чисел с возможностью десятичных дробей
Денежный – понятно, что рубли фунты, тугрики. Причем назначение формата британский фунт будет учитывать всю сложность британской системы счисления денежных знаков
Финансовый – я не финансист и поэтому не знаю, чем этот формат отличается от денежного
Краткий формат даты – день, месяц и год показаны числами
Длинный формат даты – месяц прописан полностью с учетом нашей русской орфографии, за что большое спасибо переводчикам
Время – сразу обращаем внимание на синтаксис написания времени – часы, минуты и секунды разделяются двоеточием
Процентный – пояснять не надо
Дробный – показывает простые дроби
Экспоненциальный ‒ используется для отображения числа в экспоненциальном представлении. При этом происходит замена части числа на E + n, где E обозначает экспоненциальное представление, то есть умножение предшествующего числа на 10 в степени n (честно списала с официального руководства пользователя)
Текстовый – пояснять не надо.

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

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

Команды с присвоением формата Денежный и Процент находятся на ленте Главная → группа команд Число:

Денежный
Процент
Формат с разделителем
Увеличить разрядность (увеличить количество знаков после запятой в десятичной дроби)
Уменьшить разрядность (уменьшить количество знаков после запятой в десятичной дроби)

Формат с разделителем очень полезная опция – позволяет создать в числах промежутки между разрядами:

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

Понять и запомнить!

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

Если мы сложим 54+32, то получим в результате 86, а если мы сложим 54 мин+32 мин, то получим 1 час 26 минут. Так что идентификатор ячейки очень важен при вычислениях. Вот небольшой кусочек из моего отчета по созданию дистанционного курса:

Рассмотрим пример числового формата Дата:

Как видите, выбор форматов даты очень обширен.

А вот пример выбора денежного формата:

2. Копирование форматов в другие ячейки

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

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

3. Пользовательский формат

Не будем пугаться, а попробуем разобраться.

0 (ноль)	одно обязательное знакоместо (разряд), т.е. это место в маске формата будет заполнено цифрой из числа, которое пользователь введет в ячейку. Если для этого знакоместа нет числа, то будет выведен ноль. Например, если к числу 135 применить формат 0000, то получится 0135, а если к числу 3,14159265358979 применить формат 0,00 – получится 3,14
(решетка)	одно необязательное знакоместо – примерно то же самое, что и ноль, но если для знакоместа нет числа, то ничего не выводится
(пробел)	используется как разделитель групп разрядов по три между тысячами, миллионами, миллиардами и т.д.
[ ]	в квадратных скобках указывается цвет шрифта. Разрешено использовать следующие цвета: черный, белый, красный, синий, зеленый, жёлтый, голубой

Это мой номер телефона, звоните – буду рада пообщаться.

Что касается текста, то существует правило:

Если мы хотим добавить десятичные дроби, то:

То получим в результате:

75, 00 кг

34,00 кг

54,00 кг

К ячейке можно применить до 4 видов числовых форматов:

Форматы разделяются точкой с запятой (и никаких пробелов!)

Например. Если в поле Тип мы введем:

то получим такой результат:

Усложним задачу и поработаем с цветом:

Получаем такую красоту:

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

Несмотря на то что Excel предлагает большое разнообразие встроенных числовых форматов; вы можете прийти к выводу, что ни один из них не соответствует вашим потребностям. В таком случае, вероятно, вы захотите создать собственный формат чисел. Это делается на вкладке Число диалогового окна Формат ячеек (рис. 49.1).

Самый простой способ вызвать это окно — нажать Ctrl+1. Вы также можете нажать кнопку его запуска в группе Число вкладки Главная (маленькая кнопка со стрелкой расположена справа от слова Число).

Рис. 49.1. Пользовательские (настраиваемые) числовые форматы можно создать на вкладке Число окна Формат ячеек

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

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

Части строки формата числа

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

Следующие рекомендации помогут вам определить, какие из этих четырех разделов необходимо указывать.

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

Следующий пример пользовательского числового формата задает различные форматы для каждого из этих типов:
[3еленый]0сновной;[Красный]-Основной;[Черный]Основной;[Синий]Основной

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

Когда вы создаете пользовательские числовые форматы, не забывайте поле Образец на вкладке Число диалогового окна Формат ячеек. Здесь выводится значение активной ячейки, отформатированное тем способом, что задан в поле Тип.

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

В MS Excel существует бесчисленное множество форматов, в которых можно представить свои числовые данные. Тут вам и даты, и валюты и даже номера телефонов. Но что делать если нам не подходит ни один из предустановленных форматов? Создать свой.

Делается это очень просто. Давайте попробуем для примера создать парочку форматов:

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

В открывшемся диалоговом окне во вкладке "Число" слева в списке "Числовые форматы:" кликните мышкой по пункту "(все форматы)":

Обратите внимание что в окошке "Образец" данные поменяли свой вид. Если вас устраивает такой вид данных нажмите "ОК":

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

Теперь ваш формат сохранен в MS Excel и его можно применять к другим ячейкам. Для этого выделите необходимые ячейки и нажмите сочетание клавиш " Ctrl+1 " или нажмите правую клавишу мышки и в контекстном меню выберите пункт "Формат ячеек. ". В открывшемся диалоговом окне во вкладке "Число" слева в списке "Числовые форматы:" кликните мышкой по пункту "(все форматы)". В списке форматов найдите свой (он будет последним), выделите его и нажмите "ОК":

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

Чтобы удалить ненужный формат - откройте диалоговое окно "Формат Ячеек" найдите в списке формат, который вы хотите удалить и нажмите кнопку "Удалить":

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

Виды форматов ячеек

Для начала перечислим основные форматы ячеек, которые доступны в табличном редакторе:

Общий;
Числовой;
Денежный;
Финансовый;
Дата;
Время;
Процентный;
Дробный;
Экспоненциальный;
Текстовый;
Дополнительный.

Примечание: для большинства из перечисленных форматов можно настроить вид отображения данных (например, “Дата”). Ненастраиваемыми являются форматы: Общий и Текстовый.

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

Метод 1: через контекстное меню

Данный метод является самым распространенным среди пользователей. Вот что мы делаем:

Щелкаем правой кнопкой мыши по ячейке, формат которой требуется задать/изменить, и в появившемся списке команд выбираем “Формат ячеек”.Если данное действие требуется выполнить для диапазона ячеек, сначала любым удобным способом выделяем его, после чего, также, правой кнопкой мыши кликаем по любому месту внутри выделенной области и выбираем нужный пункт.
На экране отобразится окно “Формат ячеек”. Автоматически должна быть выбрана вкладка “Число” (если по каким-то причинам этого не произошло, переключаемся в нее). В перечне слева определяемся с форматом, после чего справа задаем детальные параметры (если предлагаются). По готовности щелкаем OK.
В результате нам удалось сменить формат ячеек (в нашем случае – числовой, с разделителем разрядов, две цифры после запятой).

Метод 2: применение инструментов на лене

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

Блок “Число”

Блок “Ячейки”

Метод 3: использование горячих клавиш

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

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

Ctrl+Shift+1 – числовой (с разделителем и двумя цифрами после запятой);
Ctrl+Shift+4 – дата (ДД.ММ.ГГГГ);
Ctrl+Shift+5 – процентный (без десятичных знаков);
Ctrl+Shift+6 – время (Часы:Минуты).

Заключение

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

В этой статье вы узнаете, как изменить формат ячеек в Excel, какие бываю форматы и как изменить количество знаков после запятой?

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

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

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

Диалоговое окно Формат ячеек, которое открывается после выбора этих команд, содержит шесть вкладок: Число, Выравнивание, Шрифт, Граница, Заливка и Защита.

Учтите, что в комбинации клавиш используется цифра 1, а не функциональная клавиша .

Формат ячеек в Excel: Числовыми форматы

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

Если вы вводите значение, дополненное знаком доллара (или другой валюты) и содержащее два десятичных знака, то Excel назначит ему финансовый числовой формат.
Если вы вводите целочисленное значение, дополненное символом процента, то Excel назначит ему процентный формат.
Если вводится значение в одном из стандартных форматов дат Excel, например 06/11/19 или 6-ноя-19, то ему будет назначен формат даты. Данные в формате дат хранятся в виде числовых значений.

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

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

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

Формат ячеек в Excel: Финансовый числовой формат

Поскольку Excel обычно применяется для выполнения финансовых расчетов, вам чаще придется иметь дело с финансовым числовым форматом. Чтобы применить этот формат к выделенным ячейкам, щелкните на кнопке Финансовый числовой формат, находящейся на вкладке Главная.

На скриншоте выделены ячейки с итогами (диапазон ЕЗ:Е17). Данные ячейки были отформатированы с помощью кнопки Финансовый числовой формат, находящейся в группе Число вкладки Главная. Отличительный признак этого формата — знак валюты (рубля в данном случае).

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

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

Формат ячеек в Excel: Числовой формат с разделителями

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

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

Формат с разделителями удобен для создания отчета о продажах за первый квартал. Обратите внимание на скриншоте ниже, на котором представлены стоимость работ за 1 м 2 , отформатированные с помощью данного формата. Для создания примера сначала был выделен диапазон B3:D16, а затем выполнен щелчок на кнопке Формат с разделителями, обозначенной тремя нулями. Эта кнопка находится в группе Число вкладки Главная.

Формат ячеек в Excel: Процентный формат

На многих рабочих листах используются процентные значения для отображения годовых ставок, процента инфляции, прибыльности и т.п. Чтобы вставить процентное значение в ячейку, введите числовое значение, а затем укажите символ процента. Например, чтобы ввести годовую ставку в 12 процентов, следует ввести в ячейку значение 12%. В ответ на это Excel автоматически назначит ячейке процентный формат и в то же время разделит введенное значение на 100. В данном случае в ячейке мы увидим 0,12.

Не все проценты вводятся в рабочий лист вручную таким способом. Некоторые могут вычисляться по формуле в виде обычных десятичных значений. К подобным ячейкам нужно применить процентный формат (Percent), чтобы преобразовать десятичные значения в проценты (это выполняется путем умножения результата на 100 и добавления символа процента).

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

Как изменить количество знаков после запятой в Excel?

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

За форматами скрываются обычные числа

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

Теперь предположим, что для данной ячейки изменяется формат после щелчка на кнопке Финансовый числовой формат вкладки Главная. Исходное значение примет вид 25,65р.

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

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

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

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

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

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

Откроется окно с предупреждением о том, что данные потеряют свою точность.

Щелкните на кнопке ОК или нажмите клавишу.Все данные изменятся в соответствии с экранным представлением.

Рекомендуется предварительно сохранить рабочую книгу с вычисленными значениями. После завершения преобразования всех значений листа путем установки флажка Задать указанную точность откройте диалоговое окно Сохранить как. Измените имя файла, отображаемое в поле Имя файла перед щелчком на кнопке Сохранить или нажатием клавиши . Для изменения имени файла можно к текущему имени файла добавить слова как на экране. В вашем распоряжении окажутся две копии файла: исходный файл книги, включающий введенные вами значения и значения, подсчитанные Excel, а также новый файл книги, содержащий отображаемые значения (с добавленными к названию словами как на экране).

Формат ячеек в Excel: Формат даты

Результат вычисления формулы, который отображается в ячейке D5 в виде 8:00 AM, нужно преобразовать в формат Общий. В результате вместо значения 8:00 AM в ячейке D5 появится дробное значение 0,333333, которое представляет собой результат деления 8 (значение времени) на 24 (количество часов в сутках). Чтобы преобразовать это дробное число в эквивалентное количество часов, следует умножить его на 24, а к результату применить формат Общий.

Читайте также: