У Дугласа возникли проблемы с определением нужного ему нестандартного формата. Он создал собственный формат, в котором большие числа отображаются так, как он хочет, так что 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)

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

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

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

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

Этот совет (10226) применим к Microsoft Excel 97, 2000, 2002 и 2003.

Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь:

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