Office/Excel 数字格式整理

O

Office 或者 Excel 当中可以设置数字的格式,也就是说一个数字的值应该用怎样的方式显示出来。简单点来说,”1″ 这个数字可以显示为 “1.00” 又或者是 “¥1” 甚至是“1 个馒头”之类,然而单元格里面输入的就只有 “1”.

这些格式的设置可以变得挺复杂,自己参照网上的帮助文档整理了一下,也当作是方便自己以后参考。

以下说起的标点符号全部为英文半角;示例当中的粗体表示输入的值,不代表格式。

1. 基本的基本

Excel 的数字格式代码分为四部分,中间用分号隔开:

<正数>;<负数>;<0 值>;<文本>

分别对应显示正数、负数、0 值以及后续文本四个部分。这四个部分不需要都用上:

  • 如果只用两个,第一个会用于正数和 0 值,第二个会用于负数
  • 如果只用一个,则会应用于所有数字
  • 如果想跳过其中一部分,则需要包括其对应的分号 ; 来表示这部分没有用

2. 添加数字或空白

2.1 显示文本与数字

要显示文字,要么用引号 (” “) 包起来,要么用反斜杆 (\) 放在特殊字符的前面。例如:

"剩余 "0" 个";"还差 "0" 个"

就会正数或者 0 值的时候显示诸如“剩余 10 个”,负数的时候显示“还差 -10 个”

至于无需用引号括起来就可以直接显示的字符有以下这些:

$ + ( : ^ ` { < = - / ) ! & ~ } > 以及空格

2.2 添加输入内容时附带的文本

显示附带的文本不仅可以应用于数字,还能应用于输入的文字。例如:

"给"@"的一封信"

@ 所代表的则是单元格输入的内容,可以是数字或者是文本;所以这个会有“给的一封信” “给25的一封信”等等。

但是如果已经设定了数字的格式的话,这样@ 的用法就只能应用于数字了,毕竟文本部分在四部分当中排在最后:

"剩余 "0" 个";"还差 "0" 个";"你倒是叫"@"说说还有多少"

输入数字的时候会应用前面的规则,而如果不是数字而是文本的话就会有“你倒是叫小明说说还有多少”之类的结果。

2.3 添加对齐用的空格

有些时候如果负数用括号来表示,那么括号右边就会占用一些空间,使得数字不能右边对齐。这个时候,对正数和 0 的部分用下划线 (_) 添加一个空格再加上一个右括号 ) 就可以对齐:

0_);(0)

这样的话正数或者 0 的时候右边会有个空格。

2.4 重复的字符

如果想单元格里面根据格子的宽度无限重复显示某个字符的话,就可以用星号 (*) 放在这个字符的前面:

0*-

就会在数字后面加上无限个横线占满整个单元格。星号可以在数字的前面或者后面使用,但是同一个单元格里面不能重复使用,否则数字后面的会优先生效。

3. 显示小数的位数、空白、颜色以及条件格式

3.1 显示小数的位数,或者是有效数字的位数

在决定一个小数显示多少位的时候,会用上以下几种:

  • 0: 代表一个数位,例如 “0.00” 这样的代码会把 “8.9” 显示为 “8.90”
  • #: 代表一个有效数字的数位,有效数字是第一个非 0 的数字到最后一个非 0 的数字。
    • “#.##” 这样的代码会把 “8.9” 依然显示为 “8.9”, 因为小数点后面第二位的 0 不是有效数字
    • “#.##” 会把 “0.12” 显示为 “.12”, 因为第一个 0 不是有效数字
  • ?: 和 0 一样,不过显示出来会把非有效数字的 0 变成空格,来让 诸如 8.9 和 88.99 在不同的单元格里面也能对齐小数点
  • .: 这是小数点

如果代码的小数点位数比数值本身要少,那就会进行四舍五入。不过整数部分不受限制;”#.##” 这样的代码会把 “12.345” 显示为 “12.35”.

以下是一些例子:

要把……显示为……用这段代码
1234.591234.6####.#
8.98.900#.000
.6310.60.#
12
1234.568
12.0
1234.57
#.0#
44.398
102.65
2.8
44.398
102.65
2.8
(对齐小数点,Wordpress 没办法显示)
???.???
5.25
5.3
5 1/4
5 3/10
(对齐分数线, WordPress 没办法显示 )
# ???/???

3.2 显示千位符

数字格式当中的千位符用逗号 (,) 来表示,而之前或者之后需要有 # 或者 0 等来代表数字。在数字后面的逗号代表 1,000, 两个逗号就是 1,000,000:

要把……显示为……用这段代码
1200012,000#,###
1200012#,
1220000012.20.0,,

3.3 指定文字颜色

数字格式也可以用方括号 ([ ]) 当中指明特地颜色;指定颜色的这个方括号必须要在每个部分首先出现。能够使用的颜色有:

  • 黑色:[Black]
  • 绿色:[Green]
  • 白色:[White]
  • 蓝色:[Blue]
  • 洋红:[Magenta]
  • 黄色:[Yellow]
  • 青色:[Cyan]
  • 红色:[Red]

3.4 指定条件

数字格式还能指定特定的条件,然后显示特定的内容。指定条件用的还是方括号 ([ ]), 里面用的是大于、等于或者小于。

在平时制作的报告当中,经常需要把小于 0.5% (即 0.005)但是又大于 0 的数字用星号 (*) 来表示:

  • 例如 0.4% 如果四舍五入之后就会变成 0%,然而 0.4% 并非没有或者不存在,所以用 * 来代表。
  • 而真正的 0 则用横线 (-) 来表示。

这种情况下,就会用到以下的代码:

[=0]-;[<0.005]"*";0%

从左到右分别是:

  • 首先就会看是不是 0,是的话就会显示 –
  • 不是的话就继续看下一个条件,是不是小于 0.005 (即 0.5%),是的话就会显示 *
  • 不是的话就显示为不带小数点的百分比,例如 5%

如果需要更多复杂的颜色或者背景色等等跟随条件变化,那就需要用到条件格式功能了。

4. 百分比和科学记数法

4.1 显示百分比

其实很简单,就加一个 % 在最后面就完事了。

4.2 科学记数法

科学记数法就是把 2,800 表示成为 2.8 × 103 之类;而在 Excel 当中用的是 E 来表示,例如 2,800 变成 2.8E+3 (注意这里是正 3)。

具体来说,Excel 里面把小数点向左移动了多少位,那就在 E 后面出现什么数字。对于其实是向右移动的情况就会显示负数。

如果要把 12,200,000 显示为 1.22E+07, 则需要用到 0.00E+00 的格式。0.00 则是和上面所说的一样,是三个数字,没有的时候显示 0 而不是 $ 代表的空白。

5. 显示日期和时间

先要说一下这里更多是英文环境下面的设置,自己并没有去尝试在中文当中会显示什么。

5.1 显示日期,月份和年份

按照下面的设置就好了。

m把月份显示为一个数字,没有前面的补 0(例如 5)
mm把月份显示为一个数字,需要时有前面的补 0(例如 05)
mmm把月份显示为缩写(Jan 到 Dec)
mmmm把月份显示为完整单词(January 到 December)
mmmmm把月份显示为一个字母(J 到 D)
d把日期显示为一个数字,没有前面的补 0(例如 06)
dd把日期显示为一个数字,需要时有前面的补 0(例如 06)
ddd显示星期的缩写(Sun 到 Sat)
dddd显示星期的完整单词(Sunday 到 Saturday)
yy把年份显示为两位数字
yyyy把年份显示为四位数字

5.2 显示时分秒

同样根据下面的格式来设置。

h把小时显示为一个数字,没有前面的补 0(例如 3)
[h]把所经过的时段显示为小时;特别是超过 24 之后依然能显示为小时的数量,需要用 [h]:mm:ss
hh把小时显示为一个数字,需要的时候前面补 0(例如 03)。如果代码当中有 AM 或者 PM, 小时就是 1-12; 否则就是 1-24.
m把分钟显示为一个数字,没有前面的补 0(例如 9)
[m]把所经过的时段显示为分钟;特别是超过 60 之后依然能显示为分钟的数量,需要用 [mm]:ss
mm把分钟显示为一个数字,需要的时候前面补 0(例如 09)。需要注意的是,这里的 mm 必须要出现在小时的代码 (h/hh) 后面或者秒的代码 (s/ss) 前面,否则就会变成上述的月份
s把秒显示为一个数字,没有前面的补 0(例如 7)
[s]把所经过的时段显示为秒;特别是超过 60 之后依然能显示为秒的数量,需要用 [ss]
ss把秒显示为一个数字,需要的时候前面补 0(例如 07)。 如果还要显示秒后面的小数点,可以用 h:mm:ss.00
AM/PM, am/pm, A/P, a/p代码当中包括这些的话就会显示为 12 小时制

其实整理这些更多是为了方便自己,毕竟经常都要在 Excel 或者其他 Office 程序里面设置这样的数字格式。

知道多一点会好一点。

About the author

secangel

双子座 AB 型,资深女校男生

Add comment

About Author

secangel

双子座 AB 型,资深女校男生

Keep In Touch