Excel数字格式的缩写 [英] Excel number format for abbreviation

问题描述

这是我要完成的事情：

 值显示
 1 1 
 11 11 
 111111 
 1111 1.11k 
 11111 11.11k 
 111111 111.11k 
 1111111 1.11M 
 11111111 11.11M 
 111111111 111.11M 
 1111111111 1.11B 
 11111111111 11.11B 
 111111111111 111.11B 
  

彭博终端用于显示货币数字的格式。数字不会超过 B 。

这是我到目前为止尝试过的：

  [> 999999999.999]＃,, B; [> 999999.999]＃,, M;＃，## 0_M 
  

我从

要获得第四个条件，您需要将会覆盖自定义数字格式的内容。条件格式设置规则在这里似乎很合适。根据以下公式创建一个，

  = $ b2< 10 ^ 3 
  

单击格式，然后单击数字选项卡，然后为 0 _）设置自定义数字格式。

当然，CFR数字格式可能是另外两个条件和一个默认值，但是您只需要一个附加数字格式。 / p>

Here is what I want to accomplish:

Value           Display
1               1
11              11
111             111
1111            1.11k
11111           11.11k
111111          111.11k
1111111         1.11M
11111111        11.11M
111111111       111.11M
1111111111      1.11B
11111111111     11.11B
111111111111    111.11B

This is the format that a Bloomberg terminal uses to display currency figures. Numbers don't go past B.

Here is what I have tried so far:

[>999999999.999]#,,,"B";[>999999.999]#,,"M";#,##0_M

I stole it from here and I cannot find documentation that shows how to improve it.

解决方案

When using conditions in a custom number format, you can have a maximum of two conditions with one default number format and one default text format.

[>=1000000000]#0.0#,,, \B;[>=1000000]#0.0#,, \M;#0.0#, K

To attain your fourth criteria, you need something that will override the custom number format. A Conditional Formatting Rule seems appropriate here. Create one based on the following formula,

=$b2<10^3

Click Format then the Number tab and set the custom number format for 0_).

Of course, that CFR number format potentially could be two more conditions and a default but you only require a single additional number format.

这篇关于Excel数字格式的缩写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！