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

В статье «Используйте пользовательский формат в Excel для облегчения чтения миллионов» используется пользовательский формат, чтобы сделать большие значения более читабельными. Например, вместо этого 1 200 000 отображается как 1,2 М — формат также округляется в Excel. Если значение меньше миллиона, пользовательский формат Excel по-прежнему отображает значение с буквой M, обозначающей миллион, но оно опускается до десятичной точки. Например, 669227 отображается как 0,67 М. На самом деле, 0,67 М не намного более читабельно, чем 669227. На самом деле, 0,67 М можно легко прочитать неправильно. Вот почему в этой статье я покажу вам два правила условного форматирования Excel, которые сокращают большие значения: одно для миллионов и одно для тысяч.

СМОТРИТЕ: Политика установки программного обеспечения (TechRepublic Premium)

Я использую Microsoft 365 в 64-разрядной системе Windows 10, но вы можете использовать более раннюю версию Excel. Я рекомендую вам отложить обновление до Windows 11, пока все проблемы не будут устранены. Для вашего удобства вы можете скачать демонстрационные файлы Excel в форматах .xlsx и .xls. Excel в Интернете будет правильно отображать существующее условное форматирование, но пока не поддерживает пользовательские форматы значений.

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

Во-первых, давайте рассмотрим формат миллионов, используемый в статье по ссылке выше.

$#.##, «М»;

Каждый код форматирования состоит из четырех разделов, разделенных точкой с запятой (;). В приведенном выше пользовательском формате используется только первый раздел, который определяет форматирование положительных значений. Для полноты ниже приводится объяснение четырех разделов, хотя мы будем использовать только первый раздел:

Первый раздел относится к положительным значениям. Второй раздел относится к отрицательным значениям. Третий раздел относится к 0 Четвертый раздел относится к тексту.

Теперь давайте разберем пользовательский формат, который мы будем использовать для значений, больших или равных одному миллиону. В этом формате слева отображается символ доллара $. Далее #.##, обрабатывает фактические цифры. Во-первых, две запятые обозначают разделитель тысяч, а ,, опускает значения в тысячах и сотнях разрядов. Например, 1780379 отображается как 1,78 млн долларов США. Для преобразования необходимо использовать десятичное число; 1,78 М – это не то же самое, что 178 М.

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

На вкладке «Главная» щелкните диалоговое окно запуска группы «Значение». При необходимости перейдите на вкладку «Значение». В списке «Категория» выберите «Пользовательский». В поле «Тип» справа введите пользовательский формат $#.##, «M»; (Рисунок А).

Рисунок А

Нажмите ОК.

На рисунке B показан примененный пользовательский формат.

Рисунок Б

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

Пользовательский формат тысяч Excel

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

$###», «К»;

ты прав! Важно отметить компонент ###,. Это представляет возможные три цифры в позиции тысяч и опускает цифры в позиции сотен. Кроме того, общеизвестно, что K представляет тысячи, поэтому мы будем использовать его вместо T. Используя приведенные выше инструкции, добавьте этот собственный формат. После добавления обоих пользовательских форматов пришло время создать правила условного форматирования для их применения.

Как создать правила условного форматирования в Excel

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

Рисунок С

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

Выберите набор данных (B3:E8). На вкладке «Главная» нажмите «Условное форматирование» в группе «Стили». Выберите «Новое правило» из раскрывающегося списка. В появившемся диалоговом окне выберите «Форматировать только те ячейки, которые содержат» на верхней панели. На нижней панели выберите «Больше или равно» во втором раскрывающемся списке. Первый раскрывающийся список — «Ячейки», и это то, что нам нужно. В третьем поле введите 1000000. Нажмите «Формат». Откройте вкладку «Число». В списке «Категория» выберите «Пользовательский». Справа выберите пользовательский формат: $#.##, «M»; (Рисунок D) и нажмите «ОК».

Рисунок D

На рисунке C показано и правило, и формат. Нажмите ОК, чтобы применить его. Как вы можете видеть на рисунке E, форматируются только значения, большие или равные одному миллиону.

Рисунок Е

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

$###», «К»;

используя рисунок F в качестве руководства для шагов 6 и 10.

Рисунок F

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

Рисунок G