Обработка отрицательных чисел в сложном настраиваемом формате

У Дугласа возникли проблемы с определением нужного ему нестандартного формата. Он создал собственный формат, в котором большие числа отображаются так, как он хочет, так что 1000000 долларов отображается как 1,0 млн долларов, а 1000 долларов – как 1,0 тыс. Долларов. Этот формат выглядит следующим образом: [> 1000000] $ #. 0 ,, “M”; [> 1000] $ #. 0, “K”; $ #, ## 0.0. Дуглас хочет знать, как настроить пользовательский формат так, чтобы отрицательные числа отображались таким же образом, но были выделены красным цветом с круглыми скобками вокруг них, например ($ 1,0K).

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

positive; отрицательный; нуль; текст

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

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

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

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

[> = 1000000] $ #. 0 ,, «M»; [> = 1000] $ #. 0, «K» ; $ #, ## 0.0 [Красный] [ Первый формат должен использоваться в случае положительных значений ; это вариант исходного формата, предложенного в первой подсказке. Второй формат должен использоваться с отрицательными значениями. Эти настраиваемые форматы необходимо будет применить вручную в зависимости от значения в ячейке.

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

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

Источник: frestage.ru

Бытовой вопрос