Excel формат ячеек: Форматы ячеек в Excel / Skillbox Media

Содержание

Формат ячеек в Excel? Виды форматов. Разрядность

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В Excel можно создать формулу, вычисляющую разницу между значениями даты и времени, введенными в ячейки рабочего листа. Единственная проблема состоит в том, что Excel вычитает одну дату из другой или одно время из другого и автоматически преобразует результат вычислений в соответствующий числовой формат даты или времени. Например, если ввести в ячейку В4 15-8-19, в ячейку С4 — 15/4/19, а в ячейку D4 — формулу, вычисляющую количество дней между этими датами (=В4-С4), то получится результат 122 (количество дней). Это значение отображается с помощью формата Общий, назначенного ячейке D4. Учтите, что при создании формул, подсчитывающих разницу между двумя значениями времени на листе, ячейке результата следует назначить формат Общий. Предположим, что в ячейке В5 указано значение 8:00 AM, а в ячейке С5 — 4:00 РМ. Для вычисления разницы двух показаний времени в ячейке D5 определяется следующая формула: =С5-В5.

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

Тэги: Microsoft Excel

Формат ячеек в Excel: полный курс от Бородача

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

Содержание

Какие форматы бывают?
Числовой
Денежный
Финансовый
Дата и Время
Процентный
Дробный
Экспоненциальный
Текстовый
Дополнительные
Как программа определяет формат?
Как можно изменить формат ячеек в Excel?
Способ 1: Через панель управления
Способ 2: Раздел «Ячейки»
Способ 3: Быстрые клавиши
Задать вопрос автору статьи

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

Посмотрите на картинку ниже – у нас выделена ячейка с текстом, но программа автоматически определила «Общий» формат, в котором могут храниться как текстовые данные, так и числовые. Также там могут быть специальные символы.

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

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

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

Что обозначает буква E в числе Excel таблицы? Буква E обозначает то, что данное число нужно умножить на 10 в положительной или отрицательной степени (все зависит от знака после буквы E). А вот степень возведения – это как раз последнее число записи, то есть то, которое стоит после знака плюс (+) или минус (-).

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

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

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

Если у вас не получается их использоваться, делаем так – сначала зажимаем клавиши Ctrl и Shift, а теперь, не отпуская, жмем по цифре, после отпускаем кнопки. Как видите, нет ничего сложного. Если даже вам стало что-то не понятно в плане формата ячеек – ничего страшного. Общее понимание придет при расчетах и работе с рабочими таблицами. В любом случае вы всегда можете обратиться за помощью к специалистам портала WiFiGiD.RU – просто напишите свой вопрос в комментариях в самом низу статьи.

Применение форматов чисел, таких как даты, валюта или дроби, к ячейкам на листе. Например, если вы работаете над квартальным бюджетом, вы можете использовать числовой формат Валюта , чтобы ваши числа представляли деньги. Или, если у вас есть столбец дат, вы можете указать, что вы хотите, чтобы даты отображались как 14 марта 2012 года, 14 марта – 12 или 3/14.

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

Иногда, если вы вводите числа в ячейку или импортируете их из другого источника данных, Excel не форматирует их должным образом. Если, например, вы вводите число вместе со знаком косой черты (/) или дефисом (-), Excel может применить формат даты . Вы можете предотвратить это автоматическое форматирование чисел, применив к ячейкам формат Text .

Это легко сделать:
Выберите ячейки, содержащие числа, которые вы не хотите автоматически форматировать в Excel.
Выберите CTRL+1.
На Mac выберите Control+1 или Command+1.
На вкладке Номер выберите Текст в списке Категория .
Нужна дополнительная помощь?
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

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

Хотя встроенные форматы Excel полезны, в некоторых случаях они могут не включать нужный формат. В этом случае вы можете использовать пользовательское форматирование в Excel, чтобы создать именно тот формат, который вы хотите для своих ячеек.
Использование пользовательского форматирования в Excel
В Excel много встроенных числовых форматов, но что делать, если конкретный формат, который вам нужен, не включен во встроенные форматы?
Для этого вам нужно будет создать свой собственный формат в Excel. Как правило, вы можете настроить почти все в Excel. Вы даже можете создать кнопку с пользовательской функцией в Excel.
Создавать пользовательские числовые форматы в Excel с самого начала непросто, но как только вы освоите символы и то, что они делают, это станет легкой задачей. Для начала ознакомьтесь с таблицей ниже. Эта таблица представляет собой сводку некоторых важных символов пользовательского форматирования.
333 Номер
Символ
Функция
#
Если пользовательский формат равен #, то в ячейке будет отображаться любое число в ней.
.0
Десятичная точка. Десятичные точки определяются количеством нулей, которые вы ставите после точки. Если пользовательский формат — #.00, а значение ячейки — 1,5, ячейка будет отображать 1,50.
,
Разделитель тысяч. Если пользовательский формат — #,### , а значение ячейки — 5000, в ячейке будет отображаться 5000.

\
Отображает следующий за ним символ. Если пользовательский формат — #\M , а значение ячейки — 500, ячейка будет отображать 500M.
@
Текстовый заполнитель. Если пользовательский формат — @[Red] , а значение ячейки — MUO, ячейка будет отображать MUO красным цветом.
» »
Отображает пользовательский текст. Если пользовательский формат — # «Годы» , а значение ячейки равно 5, в ячейке будет отображаться 5 лет.
%
Умножает число на 100 и показывает его в процентах. Если пользовательский формат — #%
, а значение ячейки — 0,05, то в ячейке будет отображаться 5%
[ ]
Создает условие. Если пользовательский формат [>10]#;; и значение ячейки меньше 10, ячейка будет пустой.
Другим важным фактором, который необходимо учитывать при создании пользовательского формата, является порядок. Вы можете включить различные форматы для положительных чисел, отрицательных чисел и нуля в свой собственный формат, поставив между ними точку с запятой ( ; ). Рассмотрим формат ниже:
«Положительный»; «Отрицательный»; «Ноль»
При применении этого форматирования, если значение ячейки положительное, ячейка будет отображать строку Positive . Для отрицательных значений будет отображаться Отрицательное , а для нуля будет отображаться
Ноль . Пользовательский формат не обязательно должен включать все три условия. Если вы введете только первый, он будет использоваться для всех трех.
То, о чем мы здесь говорили, — это не все, что нужно для пользовательского форматирования в Excel, но это хорошая отправная точка. Теперь давайте превратим эти знания в навыки на нескольких примерах.
1. Пример пользовательского форматирования Excel: текстовые суффиксы
Чтобы дать вам представление о пользовательских форматах в Excel, мы начнем с простого примера. Допустим, вы хотите ввести продолжительность некоторых операций в диапазон ячеек.
Создав собственный формат, который добавляет к ячейке суффикс часов , вы можете просто ввести числовое значение лет и не вводить текст. Вот как это сделать:
На вкладке Домашний перейдите в раздел Номер и нажмите Общие .
В меню выберите Дополнительные числовые форматы . Это откроет новое окно.
В новом окне под Категория , выберите Пользовательский .
Выберите любой из форматов.
Введите строку ниже под Введите :
# "Часы"
Нажмите OK .
Теперь ваш пользовательский формат готов. Выберите ячейки, к которым вы хотите применить это пользовательское форматирование, затем повторите шаги, описанные выше, и выберите только что созданный пользовательский формат. Обратите внимание, что значения ячеек не изменились, несмотря на то, что в ячейках отображается суффикс часов .
Хэштег ( # ) в коде представляет любое необязательное число, но поскольку мы не включили в формат десятичные дроби, десятичные дроби не отображаются в ячейках. На самом деле все цифры округлены.
2. Пример пользовательского форматирования Excel: разделитель десятичных и тысячных разрядов
Пришло время получить пример пользовательского форматирования для различных значений в Excel. Как упоминалось ранее, вы можете создавать разные форматы для положительных, отрицательных и нулевых значений в одном пользовательском формате ячейки.
Давайте продолжим предыдущий пример. Здесь у нас та же электронная таблица, но мы хотим изменить отображаемые значения для отрицательных значений и нулей. Чтобы повысить точность и удобочитаемость отображаемых чисел, мы также добавим разделитель тысяч и десятичную точку.
Во вкладке Домашний перейдите в раздел Номер и нажмите Общие .
В меню выберите Дополнительные числовые форматы . Это откроет новое окно.
В новом окне в разделе Категория выберите Пользовательский .
Выберите формат, созданный в предыдущем разделе.
Введите строку ниже под Введите :
###,###.0 «Часы»; "Значение отрицательное!"; "-"
Нажмите OK .
Теперь, если вы примените этот формат к ячейкам, вы увидите, что он добавляет суффикс часов к положительным значениям, отображает Значение отрицательное! , где значения отрицательные, и отображает — для нулей. Добавление десятичной точки к этому пользовательскому формату не позволяло Excel округлять числа, а разделитель тысяч облегчал чтение чисел.
Пользовательские форматы с текстовыми строками позволяют создавать новые объекты в Excel. Например, вы можете использовать пользовательское форматирование для создания маркированного списка в Excel.
3. Пример пользовательского форматирования Excel: цвета и условия
Теперь давайте попробуем кое-что новое с пользовательским форматированием. В этом примере у нас есть оценки некоторых учеников. Оценки выставляются из 20, и если студент получил 10 или выше, он сдал экзамен. Если нет, то они, к сожалению, потерпели неудачу.
Целью здесь является создание пользовательского формата, который будет отображать Pass зеленым цветом, если значение ячейки равно 10 или выше, и отображать Fail красным цветом, если значение ячейки ниже 10.

Как упоминалось ранее, вы можно создавать собственные условия внутри квадратных скобок ( [ ] ) и использовать скобки для указания цвета шрифта. Давайте приступим к делу. Имея это в виду, давайте создадим собственный формат с условиями.
В новом окне в разделе Категория выберите Пользовательский .
Выберите любой из форматов.
Введите строку ниже под Введите :
[>=10][Зеленый]"Пройдено";[<10][Красный]"Не пройдено"
Нажмите OK .
После применения формата к ячейкам ячейки ниже 10 будут отображать Fail красным цветом, а остальные будут отображать Pass зеленым цветом. Опять же, обратите внимание, что значения ячеек не изменились по сравнению с исходными числами.
С помощью пользовательского форматирования можно сохранить значения ячеек без изменений и отображать текст на основе значений ячеек.

Штирлиц — Сеть печатных салонов в Перми

Excel формат ячеек: Форматы ячеек в Excel / Skillbox Media