Excel自定义数字格式复杂的条件

I need following:

我需要以下:

My numbers are to be divided by 1000 and rounded, negative in brackets, zeroes as "-":

我的数字要除以1000，然后四舍五入，括号内为负数，0为“-”:

  1500 ->  2
  499  ->  -
  0    ->  -
 -499  ->  -
 -1500 -> (2)

I cannot find how to transform values [-499;499] to "-", everything else is fine.

我找不到如何将值[-499;499]转换为"-"，其他都没问题。

Currently (simplified) I have smth like this: "#,##0, ;(#,##0,);- "

目前(简化)我有这样的smth:“#，# 0，;;(#，# 0，);-”

It displays my numbers as:

它显示我的数字为:

  1500 ->  2
  499  ->  0 (I need "-" here)
  0    ->  -
 -499  -> (0)(I need "-" here)
 -1500 -> (2)

Any ideas? Thanks!

什么好主意吗?谢谢!

2 个解决方案

#1

Found the solution:

找到了解决方案:

 [>=500]_(* #,##0,_);[<=-500]_(* (#,##0,);_(* #,_ "-"_);_(@_)

Displays my numbers as:

显示我的号码:

 1500       ->  2
 499        ->  -
 0          ->  -
-499        ->  -
-1500       -> (2)
 blank cell -> blank cell
 text       -> text

Main problem was how to display -499 as "-"

主要问题是如何将-499显示为"-"

as Jeeped posted

作为吉普发布

  [>=500]#,##0, ;[<=-500](#,##0,);-;[color3]@

did this:

这样做:

 1500       ->  2
 499        ->  -
 0          ->  -
-499        ->  -- (here was the problem)
-1500       -> (2)
 blank cell -> blank cell
 text       -> text

#2

You can use a formula and formatting, in B1:

您可以使用公式和格式，在B1:

=IF(ROUND(A1/1000,0)<>0,ROUND(A1/1000,0),"-")

Number
数量
Format Cells
格式的细胞
Number (under category)
(类别)
Choose (123) and 0 for decimal places
小数部分选择(123)和0
Ok
好吧

And drag it down

并将其拖下来

Another way:
Use the formula in B1:

另一种方法:使用B1中的公式:

=ROUND(A1/1000,0)

Drag it down, select the result and Custom Format with:

拖下来，选择结果和自定义格式:

0;(0);-

0(0);-

Number
数量
Format Cells
格式的细胞
Custom (under category)
自定义(类别)
Write in the Box: 0;(0);-
在方框中写:0;(0);-
Ok
好吧

#1