Форматирование пользовательских чисел Excel - это одежда для данных в ячейках Excel. Вы можете одевать их так, как хотите. Все, что вам нужно, это немного ноу-хау о том, как работает пользовательский числовой формат Excel.
С помощью настраиваемого форматирования чисел в Excel вы можете изменить способ отображения значений в ячейках, но в то же время сохранить исходное значение без изменений.
Например, я могу сделать так, чтобы значение даты отображалось в разных форматах даты, в то время как базовое значение (число, представляющее дату) осталось бы прежним.
В этом уроке я расскажу все о пользовательском формате чисел в Excel, а также покажу вам несколько действительно полезных примеров, которые вы можете использовать в своей повседневной работе.
Итак, приступим!
Конструкция настраиваемого числового формата Excel
Прежде чем я продемонстрирую вам всю его прелесть, позвольте мне вкратце объяснить, как настраиваемое форматирование чисел работает в Excel.
По умолчанию Excel может принимать в ячейку следующие четыре типа данных:
- Положительное число
- отрицательное число
- 0
- Текстовые строки (это могут быть как чисто текстовые, так и буквенно-цифровые строки)
Для любой ячейки на листе в Excel вы можете указать, как каждый из этих типов данных должен отображаться в ячейке.
А ниже представлен синтаксис, в котором вы можете указать этот формат.
;;;
Обратите внимание, что все они разделены точкой с запятой (;).
Все, что вы вводите в ячейку в Excel, попадет в любую из этих четырех категорий, и, следовательно, для него можно указать собственный формат.
Если вы упомянули только:
- Единый формат: Применяется ко всем четырем разделам. Например, если вы просто напишете General, он будет применен ко всем четырем разделам.
- Два формата: Первый применяется к положительным числам и нулям, а второй - к отрицательным числам. Текстовый формат по умолчанию становится общим.
- Три формата: Первый применяется к положительным числам, второй применяется к отрицательным числам, третий применяется к нулю, и текст исчезает, поскольку для текста ничего не указано.
Если вы хотите узнать больше о настраиваемом форматировании чисел, я настоятельно рекомендую раздел справки Office.
Как это работает и где вводить эти форматы, мы рассмотрим позже в этом руководстве. А пока имейте в виду, что это синтаксис формата любой ячейки.Как настраиваемый числовой формат работает в Excel
Затем вы открываете новую книгу Excel, по умолчанию все ячейки на всех листах в этой книге имеют общий формат, где:
- Положительные числа отображаются как есть (с выравниванием по левому краю)
- Отрицательные числа отображаются со знаком минус (с выравниванием по левому краю)
- Нуль отображается как 0 (с выравниванием по левому краю)
- Текст отображается как есть (с выравниванием по правому краю)
А если вы вводите дату, она отображается в соответствии с вашими региональными настройками (в формате ДД-ММ-ГГГГ или ММ-ДД-ГГГГ).
Поэтому по умолчанию числовое форматирование ячейки следующее:
Общие; Общие; Общие; Общие
Если каждый тип данных отформатирован как общий (и следуйте правилам, упомянутым выше, в зависимости от того, положительное ли это число, отрицательное число, 0 или текст)
Теперь настройка по умолчанию в порядке, но как изменить формат. Например, если я хочу, чтобы мои отрицательные числа отображались красным цветом или я хочу, чтобы мои даты отображались в определенном формате, как мне это сделать?
Использование раскрывающегося списка формата на ленте
Вы можете найти некоторые из наиболее часто используемых числовых форматов на вкладке «Главная» в группе «Число».
Это раскрывающийся список, в котором показаны многие из часто используемых форматов, которые можно применить к ячейке, просто выбрав ячейку, а затем выбрав формат из этого списка.
Например, если вы хотите преобразовать числа в проценты или преобразовать даты в краткий или длинный формат даты, вы можете легко сделать это, используя параметры в раскрывающемся списке.
Диалоговое окно "Форматирование ячеек"
Диалоговые окна форматирования ячеек, в которых вы получаете максимальную гибкость при применении форматов к ячейкам в Excel.
В нем уже есть много готовых форматов, которые вы можете использовать, или, если вы хотите создать свой собственный числовой формат, вы также можете это сделать.
Вот как открыть диалоговое окно «Формат ячеек»;
- Щелкните вкладку "Главная"
- В группе «Число» щелкните значок средства запуска диалогового окна (небольшая наклонная стрелка в правом нижнем углу группы).
Откроется диалоговое окно «Формат ячеек». В нем вы найдете все форматы на вкладке «Число» (вместе с возможностью создания пользовательских форматов ячеек).
Вы также можете использовать следующую комбинацию клавиш, чтобы открыть диалоговое окно форматирования ячеек:
CONTROL + 1 (нажмите все клавиши Control, а затем нажмите клавишу 1).
Если вы используете Mac, вы можете использовать CMD + 1
Теперь давайте рассмотрим несколько практических примеров, в которых мы создадим собственные числовые форматы.
Примеры использования произвольного форматирования чисел в Excel
Теперь давайте рассмотрим несколько практических примеров, в которых мы создадим собственные числовые форматы.
Скрыть значение ячейки (или скрыть все значения в диапазоне ячеек)
Иногда вам может потребоваться скрыть содержимое ячейки (не скрывая строку или столбец, в котором есть ячейка).
На практике это происходит, когда вы создаете информационную панель Excel, и на ней есть несколько значений, которые вы используете, но не хотите, чтобы пользователи видели ее. так что вы можете просто скрыть эти значения
Предположим, у вас есть набор данных, как показано ниже, и вы хотите скрыть все числовые значения:
Вот как это сделать с помощью настраиваемого форматирования в Excel:
- Выберите ячейку или диапазон ячеек, для которых вы хотите скрыть содержимое ячейки
- Откройте диалоговое окно Формат ячеек (сочетание клавиш Control + 1)
- На вкладке Number щелкните Custom
- Введите следующий формат в поле Тип:
;;;
Как это работает?
Формат для четырех различных типов данных разделен точкой с запятой в следующем формате:
;;;
В этом методе мы удалили формат и указали только точку с запятой, которая указывает, что мы не хотим никакого форматирования для ячеек. Это указание для Excel скрыть любое содержимое в ячейке.
Обратите внимание, что, хотя это скрывает содержимое ячейки, оно все еще там. поэтому, если у вас есть числа, которые вы скрыли, вы все равно можете использовать их в расчетах (что делает это отличным трюком для панелей мониторинга и отчетов Excel).
Отображать отрицательные числа красным цветом
По умолчанию отрицательные числа отображаются со знаком минус (или в скобках в некоторых региональных настройках).
Вы можете использовать настраиваемое форматирование чисел, чтобы изменить цвет отрицательных чисел и сделать их красным.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите выделить все отрицательные значения красным цветом.
Ниже приведены шаги для этого:
- Выберите диапазон ячеек, в котором отрицательные числа должны отображаться красным цветом.
- Откройте диалоговое окно Формат ячеек (сочетание клавиш Control + 1)
- На вкладке Number щелкните Custom
- Введите следующий формат в поле Тип:
General; [Красный] -General
В приведенном выше формате ячейки с отрицательными значениями будут отображаться красным цветом.
Как это работает?
Когда вы указываете только два формата в качестве пользовательского числового формата, первый рассматривается для положительных чисел и 0, а второй - для отрицательных чисел (следующее значение по умолчанию становится общим форматом).
В приведенном выше формате, который мы использовали, мы указали цвет, который мы хотим, чтобы этот формат занимал в квадратных скобках.
Таким образом, хотя отрицательные числа имеют общий формат, они отображаются красным цветом со знаком минус.
Если вы хотите, чтобы отрицательные числа останавливались красным цветом в круглых скобках (вместо знака минус), вы можете использовать следующий формат:
Общий; [Красный] (Общий)
Добавить текст к числам (например, миллионы / миллиарды)
Одна удивительная особенность пользовательского форматирования чисел заключается в том, что вы можете добавить к числу префикс или суффиксный текст, сохранив исходное число в ячейке.
Например, я могу отобразить «100» как «100 миллионов долларов», сохранив при этом исходное значение в ячейке, чтобы я мог использовать его в расчетах.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите показать эти числа со знаком доллара и словом миллион перед ним.
Ниже приведены шаги для этого:
- Выберите набор данных, в котором вы хотите добавить текст к числам
- Откройте диалоговое окно Формат ячеек (сочетание клавиш Control + 1)
- На вкладке Number щелкните Custom
- Введите следующий формат в поле Тип:
Общий "Миллион"
Обратите внимание, что этот пользовательский числовой формат влияет только на числа. Если вы введете текст в ту же ячейку, он будет выглядеть как есть.
Как это работает?
В этом примере к общему формату я добавил слово миллион в двойных кавычках. все, что я добавляю в двойные кавычки к формату, отображается как в ячейке.
Я также добавил знак доллара перед общим форматом, чтобы все мои числа также отображали знак доллара перед числом.
Примечание. Если вам интересно, почему знак доллара не заключен в двойные кавычки, а слово «миллион» заключено в двойные кавычки, это потому, что Excel распознает доллар как символ, который часто используется в пользовательском форматировании чисел и не требует от вас чтобы добавить к нему двойные кавычки. Вы можете пойти дальше и добавить двойные кавычки к знаку доллара, но когда вы закроете диалоговое окно ячеек формата и откроете его снова, вы заметите, что Excel удалил его.
Маскировка чисел и текста
С помощью настраиваемого форматирования чисел вы можете оценить максимум два условия, и на основе этих условий вы можете применить формат к ячейке.
Например, предположим, что у вас есть список оценок для некоторых учеников, вы хотите показать текст «Пройдено» или «Не сдано» в зависимости от результата (где для любой оценки менее 35 должно быть указано «Не сдано», в противном случае должно отображаться «Пройдено».
Ниже приведены инструкции по отображению оценок как «Пройдено» или «Не сдано» вместо значения в пользовательском числовом формате:
- Выберите набор данных, в котором у вас есть оценки
- Откройте диалоговое окно Формат ячеек (сочетание клавиш Control + 1)
- На вкладке Number щелкните Custom
- Введите следующий формат в поле Тип:
[<35] «Неудачно»; «Пройдено»
Ниже показано, как будут выглядеть ваши данные после применения указанного выше формата.
Обратите внимание, что это не меняет значения в ячейках. В ячейке по-прежнему отображаются оценки в числовых значениях при отображении «Пройден» или «Не пройден» в зависимости от значения ячейки.
Как это работает?
В этом примере условие необходимо указать в квадратных скобках. Пользовательское форматирование чисел затем оценивает значение в ячейке и соответствующим образом применяет формат.
Таким образом, если значение в ячейке меньше 35, то число скрыто, и вместо него отображается текст «Неудача», а все остальные случаи - «Пройден».
Точно так же, если вы хотите преобразовать диапазон ячеек, содержащих 0 и 1, в ИСТИНА и ЛОЖЬ, вы можете использовать следующий формат:
[= 0] «ЛОЖЬ»; [= 1] «ИСТИНА»
Помните, что в произвольном форматировании чисел можно использовать только два условия. Если вы попытаетесь использовать более двух условий, это будет так, что ваше приглашение сообщит вам, что оно не может принять этот формат.
Скрыть текст, но отобразить числа
Иногда, когда вы загружаете свои данные из баз данных, поскольку они пусты, они заполняются такими значениями, как Not Available или NA или -
Используя настраиваемое форматирование чисел, вы можете скрыть все текстовые значения, сохранив числа как есть. это также гарантирует, что ваши исходные данные не будут повреждены (поскольку вы не удаляете текстовое значение, вы просто скрываете его)
Предположим, у вас есть набор данных, показанный ниже, в котором вы хотите скрыть все текстовые значения:
Ниже приведены шаги для этого:
- Выберите набор данных, в котором у вас есть данные
- Откройте диалоговое окно Формат ячеек (сочетание клавиш Control + 1)
- На вкладке Number щелкните Custom
- Введите следующий формат в поле Тип:
Общий; -Общий; Общий;
Обратите внимание, что я использовал общий формат для чисел. Вы можете выбрать любой формат (например, 0, 0. #, # 0.0%). Также обратите внимание, что перед вторым общим форматом стоит знак минуса (-), поскольку он представляет формат для отрицательных чисел.
Как это работает?
В этом примере, поскольку мы хотели скрыть все текстовые значения, мы просто не указали для него формат.
И поскольку мы указали формат для трех других типов данных (положительное число, отрицательное число и ноль), Excel понял, что мы намеренно оставили формат для текстового значения пустым, и он не должен ничего показывать, если в ячейке есть текст. нить.
Отображение чисел в процентах (%)
С помощью пользовательского форматирования чисел вы также можете изменять числа и отображать их в процентах.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите преобразовать эти числа, чтобы показать эквивалентное процентное значение:
Ниже приведены шаги для этого:
- Выберите ячейки, в которых есть числа (которые вы хотите преобразовать в проценты)
- Откройте диалоговое окно Формат ячеек (сочетание клавиш Control + 1)
- На вкладке Number щелкните Custom
- Введите следующий формат в поле Тип:
0%
Это изменит числа на их проценты. Например, он меняется от 0,11 до 11%.
Если все, что вы хотите сделать, это преобразовать число в его эквивалентное процентное значение, лучший способ сделать это - использовать процентную опцию в диалоговом окне форматирования ячеек.
Пользовательское форматирование может быть полезно, когда вам нужно больше контроля над тем, как вы хотите отображать процентное значение.
Например, если вы хотите показать дополнительный текст с процентами, все, что вы хотите показать отрицательные проценты красным цветом, тогда лучше использовать настраиваемое форматирование.
Отображение чисел в другой единице (миллионы в миллиардах или граммы в килограммах)
Если вы работаете с большими числами, вы можете показать их в другой единице. например, если у вас есть данные о продажах некоторых компаний, вы можете показать продажи в миллионах или миллиардах (сохраняя при этом исходную стоимость продажи в ячейках).
К счастью, вы можете легко сделать это с помощью пользовательского форматирования чисел.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите показать эти значения продаж в миллионах.
Ниже приведены шаги для этого:
- Выделите ячейки, в которых есть числа
- Откройте диалоговое окно Формат ячеек (сочетание клавиш Control + 1)
- На вкладке Number щелкните Custom
- Введите следующий формат в поле Тип:
0.0,,
Вышеупомянутое даст вам результат, как показано ниже (значения в миллионах в одной цифре после десятичной дроби):
Как это работает?
В приведенном выше формате ноль перед десятичной дробью означает, что Excel должен отображать все число (точно так же, как в ячейке), а 0 после десятичной дроби означает, что после десятичной точки должна отображаться только одна значащая цифра.
Таким образом, 0,0 всегда будет числом с одной цифрой после десятичной точки.
Когда вы добавляете запятую после 0,0, последняя трехзначная цифра номера удаляется. Например, 123456 станет 123,4 (последние три цифры удаляются, а поскольку после десятичной точки должна отображаться 1 цифра, отображается 4)
Если задуматься, это похоже на деление числа на 1000. Итак, если вы хотите отобразить число в единицах «миллионы», вам нужно добавить 2 запятые, чтобы оно сбрило последние 6 цифр.
И если вы также хотите показать слово миллион или алфавит M после числа, используйте следующий формат
0,0 ,, "М"
