У Дугласа возникли проблемы с определением нужного ему нестандартного формата. Он создал собственный формат, в котором большие числа отображаются так, как он хочет, так что 1000000 долларов отображается как 1,0 млн долларов, а 1000 долларов — как 1,0 тыс. Долларов. Этот формат выглядит следующим образом:

[> 1000000] $ . 0 ,, «M»; [> 1000] $ #. 0, «K»; $ #, # 0.0. Дуглас хочет знать, как настроить пользовательский формат, чтобы отрицательные числа отображались таким же образом, но красным цветом с круглыми скобками вокруг них, например (1,0 тыс. Долларов США).

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

positive; negative; zero; text

Обратите внимание, что первый формат используется, когда значение положительное, второй — когда отрицательное, третий — когда значение точно равно нулю, а четвертый — когда значение является текстом. Хотя это общий синтаксис для пользовательских форматов, вы можете немного «подделать» форматы, как вы это делаете. Учтите, какой формат вы используете:

[>1000000]$#.0,,"M";[>1000]$#.0,"K";$#,##0.0

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

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

[>=1000000]$#.0,,"M ";[>=1000]$#.0,"K ";$#,##0.0 [Red][<=-1000000]($#.0,,"M");[Red][<=-1000]($#.0,"K");[Red]($#,##0.0)

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

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

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

_($#.0_K_);[Red]($#.0_K);;@ _($#.0,"K"_);[Red]($#.0,"K");;@ _($#.0,,"M"_);[Red]($#.0,,"M");;@

Затем вы можете использовать возможности условного форматирования (вкладка «Главная» на ленте | Условное форматирование | Управление правилами), чтобы определить шесть различных правил форматирования. Когда вы нажимаете кнопку «Новое правило», чтобы начать определение каждого правила, вы выбираете «Форматировать только ячейки, которые содержат» в верхней части диалогового окна «Новое правило форматирования». Вот шесть правил, которые вы определите:

Cell Value <= -1000000 Cell Value >= 1000000 Cell Value between -999999 and -1000 Cell Value between 1000 and 999999 Cell Value between -999 and -1 Cell Value between 0 and 999

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

Вот те, которые вам следует выбрать:

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

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

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

Это оно; правила условного форматирования проверяют ваши диапазоны значений, а затем применяют соответствующие настраиваемые форматы для этих чисел.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (10227) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

link: / excel-Handling_Negative_Numbers_in_a_Complex_Custom_Format [Обработка отрицательных чисел в сложном настраиваемом формате].