Excel：格式化单元格时省略减号

First of all, I realize that there are threads like this already, but I cannot get the examples I've seen to work. What I want to accomplish is that a cell value should be presented as "x,xx" and in red color if the value is higher than 0, and otherwise in Color 14.

首先,我意识到已经存在这样的线程,但我无法得到我见过的示例。我想要实现的是,单元格值应显示为“x,xx”,如果值大于0,则显示为红色,否则显示为颜色14。

For instance, if this formatting would be used on

例如,如果使用此格式

0,05-0,03

It would return (in red):

它会返回(红色):

0,02

What I have done is to format the cell as:

我所做的是将单元格格式化为:

[Red][>0]0,##;[Color14][<=0]0,##;

but it prints out a minus sign for values less than or equal to 0, which I do not want. Changing to:

但它打印出一个小于或等于0的值的减号,这是我不想要的。改为:

[Red][>0]0,##;[Color14][<=0]0.##;

Omits the minus sign, as I want, but it makes the cell red which is not intended.

根据需要省略减号,但它会使单元格变为红色,而不是意图。

I've also tried a numerous combinations of ",", "." and "#" but it just wont work as intended.

我也尝试了很多“,”,“。”的组合。和“#”,但它不会按预期工作。

I hope anyone can shed some light into this.

我希望任何人都能对此有所了解。

EDIT Using "General" instead of "0,##" prints out many more decimals than desired.

编辑使用“常规”而不是“0,##”打印出比预期更多的小数。

EDIT2 I use a Swedish Excel and hence I translate the formulas to Swedish formulae. Perhaps of importance?

EDIT2我使用瑞典语Excel,因此我将公式转换为瑞典公式。也许重要?

2 个解决方案

#1

See: https://support.office.com/en-us/article/Create-or-delete-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4

A number format can have up to four sections of code, separated by semicolons. These code sections define the format for positive numbers, negative numbers, zero values, and text, in that order.

数字格式最多可包含四个代码段,以分号分隔。这些代码部分按顺序定义正数,负数,零值和文本的格式。

<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>

So for your requirement no [conditions] are needed.

因此,根据您的要求,不需要[条件]。

[Red]0,##;[Color14]0,##;[Color14]0,##

will suffice.

As for your further descriptions

至于你的进一步描述

[Red]0,00;[Color14]0,00;[Color14]0,00

may be better.

可能更好。

#2

You cannot change the value of the cell with formatting, only the appearance. So if you want the cell value to actually be positive it is not possible to do this way.

您无法使用格式更改单元格的值,只能更改外观。因此,如果您希望单元格值实际上是正数,则不可能这样做。

If this is what you actually want to do, try =ABS(A1) (will have to be done in another cell)

如果这是你真正想要做的,请尝试= ABS(A1)(必须在另一个单元格中完成)

#1