Excel 中的单元格像个任人打扮的小姑娘，有太多的格式可以应用。打开 Format Cells 对话框，我们可以看到单元格可用的格式都分成了好几个大类：Number 用来指定键盘输入的内容如何显示；Alignment 提供各种各样的对齐方式，包括文本显示的角度； Font 指定字体类型、大小、颜色、风格，以及各种各样的装饰，比如下划线什么的；单元格的边框设置精细到四条边的每一边和中间的对角线都可以分别设置线型和颜色；Patterns 还可以设置单元格的填充色和填充图案。
除了 Number 格式，其它的格式都很直观，操练一下就知道它们的功用了。自定义 Number 格式的知识还是有必要记录一下的。对于同样的键盘输入，应用不同的 Number 格式，显示的效果就会改变。比如键盘输入5，格式指定为 Number，小数点后显示两位 ，单元格的显示就是“5.00”；如果指定格式为 Percentage，小数点后也是显示两位，单元格的显示就是“500.00%”。改变 Number 格式，并不改变 Excel 实际存储的值，通过 formula bar，我们通常可以观察到单元格实际输入的内容。
自定义的 Number 格式储存在当前工作簿中，不能用到新建的工作簿中。如果我们想让自定义的格式也能用在新建的工作簿中，可以创建一个模板。
按照下列步骤创建或删除自定义格式：
- 选中要自定义格式的单元格，点击 Format 菜单->Cells 命令。或点鼠标右键，选 Format Cells 快捷菜单项。
- 在 Number 配置页，选中 Category 列表里的 Custom。
- 在 Type 列表里，选择要编辑或删除的格式。选中的格式出现在 Type 列表上方的 Type 输入框中。当我们从 Type 列表选择一个已有的格式进行编辑时，Excel 会保留原有的格式，我们所做的修改则生成一个新的格式。
自定义的格式可以被删除，但 Excel 在 Type 列表里提供的固有的格式是不能被删除的。如果某个自定义的格式被删除，Excel 就把通用格式应用到所有用到这个自定义格式的单元格。
要想自定义一个 Number 格式，先要了解自定义 Number 格式的编码规范。Number 格式的编码一共有四个组成部分，每部分之间用分号(semicolons)隔开。这四个部分的编码分别定义了正数、负数、零值和文本的格式，顺序如下：
<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>
在自定义格式时，我们不必四个部分都包括进来。如果我们指定了两个部分，第一部分就用于正数和零的格式，第二部分用于负数的格式。如果我们只指定了一个格式部分，这个编码就用在所有的数字上。如果我们想跳过一个部分但包括后面的部分，那么中间分割的分号是不能省略的。
注意：对于我们没有定义的部分，Excel 会自动应用该部分默认的格式。但是有时候，我们的写法会让 Excel 认为我们定义了该部分。请看下面的例子：
输入 #.##。根据前面讲的格式编码规则，我们这里只定义了一个部分，那么这个编码定义的格式就用在所有的数字上。具体到这个自定义的格式，# 表示数字的占位符，我们还定义了小数点要显示。当输入任何数字时，小数点前面如果大于等于一位，就照原样显示 ，小数点也要显示，比如输入1234，显示 1234.；小数点后面如果是一位或两位，也照原样显示，比如输入 -3.5，显示 -3.5；如果小数点后面超过两位，就四舍五入成两位显示，比如输入 6.785，显示为 6.79，我们在 formula bar 里可以看到实际输入的数值；对于0 值，因为我们定义了要显示小数点，所以显示为 . 。在这个例子中，我们没有定义文本的格式，所以当我们输入非数字的字符时，就照原样显示；数字和字母混合在一起输入当作文本对待，比如输入 123abc456，就显示为 123abc456。
修改一下我们刚才的例子，输入 #.##;;; 。虽然我们在文本部分什么都没有写，但是 Excel 认为这就是我们的定义，即文本部分不要显示。其实在这个例子中，输入负数、零值和文本都是不会有显示的。我们以为跳过的部分，Excel 认为是定义了，所以我是不赞成自定义时跳过某个部分的。
极端一点，我们自定义一个格式 ;;; 。这样我们在单元格里无论输入什么都是不会显示的。只能通过 formula bar 一个个看单元格的内容。
下面还是通过具体的例子来看一下如何自定义 Number 格式。
1. 给输入的数字加上前缀或后缀字符，数字本身照原样显示
假设我们工作表里的零件是用数字编号的，但是我们希望它显示为 Part:123 这个样子，我们不必在每个单元格中再补充输入 Part:，我们只要把格式定义为 "Part:"G/通用格式。同理，如果我们显示时想在表示电话分机的数字后面加上“ext”这个后缀， 我们就把格式定义为 G/通用格式"ext"。规则就是把作为前缀或后缀的字符放在双引号里(" ")。
注意：在这个例子中，输入非数字的字符时就不会应用自定义的格式，而是照输入的原样显示，因为只定义一个部分时，格式编码用在数字上而不用在文本上。
下表中的字符不用双引号括起来就能显示：
| $ | 美元符号 | - | 减号 |
| + | 加号 | / | 斜线号 |
| ( | 左圆括号 | ) | 右圆括号 |
| : | 冒号 | ! | 感叹号 |
| ^ | 抑扬音符号 (脱字符号) | & | 表示和的符号 |
| ' | 重音符号 | ~ | 波浪号 |
| { | 左花括号 | } | 右花括号 |
| < | 小于号 | > | 大于号 |
| = | 等于号 | 空格 |
2. 显示定长的数字位数
当我们用数字给商品编码的时候，比如从1 开始编码，但我们不想显示为1，而想显示为 00001 这样定长的五位数字。一种办法是设置格式为“Text”，然后输入 00001，00002，10001 这样的数字。还有种办法就是自定义格式让 Excel 帮你显示定长的数字位数，输入位数不足时用 0 补足。自定义格式为 00000，当我们在单元格中输入 1 时，就显示为 00001。
这里顺便把跟数字格式相关的占位符讲一下，#，0 和 ? 都是数字占位符，它们的功能和差别如下：
- # 当输入的数字位数比格式中用 # 定义的数字位数少时，少了就少了，只显示有意义的数字位。比如，格式定义为 ###.##，当输入 34.2 时，就显示 34.2；输入 0.47，就显示 .47。
- 0 当输入的数字位数比格式中用 0 定义的数字位数少时，少掉的数字位要用 0 补足。比如，格式定义为 000.00，当输入 34.2 时，就显示为 034.20。当然，我们通常不这样显示数字，如果想显示固定的小数位，定义格式为 #.00 或 0.00。把多个 0 用在格式编码里通常是为了如上例一样显示定长的编号。
- ? 问号这个占位符跟 0 差不多，但是少掉的数字位是用空格补足的，主要的目的是为了让小数点在显示时对齐。? 也用在变长数字位的分数中。
在使用上述的数字占位符时，如果小数点右边输入的数字位数比格式中占位符的位数多，小数部分就四舍五入到占位符指定的位数。如果小数点左边输入的数字位数比格式中占位符的位数多，多出来的数字也是显示的。口诀就是：左边多了就多了，右边多了就四舍五入。
| 实际输入 | 显示效果 | 自定义的格式 | 说明 |
|---|---|---|---|
| 1234.59 | 1234.6 | ####.# | 小数点后最多只显示一位 |
| 8.9 | 8.900 | #.000 | 小数点后必须显示三位 |
| .631 | 0.6 | 0.# | 小数点前至少显示一位，小数点后最多显示一位 |
| 12 1234.568 | 12.0 1234.57 | #.0# | 小数点后至少显示一位，但至多显示两位 |
| 44.398 102.65 2.8 | 44.398 102.65 2.8 | ???.??? | 小数点对齐，小数点后有三个数的位置 |
| 5.25 5.3 | 5 1/4 5 3/10 | # ???/??? | 显示分数以及分数线对齐 |
对于 # 这个占位符再说几句。出现在小数点左边的 # 占位符，规则是多了就多了，少了就少了；因此无论定义了几个 #，实际输入的位数都是如实显示的，所以在小数点左边的 # 定义一个就够用了。
上面的例子中还显示了小数点(.)的表示方法，如果要显示千分号，在自定义格式里使用逗号(,)。比如，格式定义为 #,###，输入 12000，就显示为 12,000。逗号跟在数字占位符后面，并且逗号后面没有其它数字占位符时，显示效果表示这个数字是 1000 的倍数，比如，格式定义为 #.0, ，输入 12,200,000，显示为 12.200.0。数字占位符后面有几个逗号，显示的结果就表示是 1000 的几次方，比如格式定义为 0.0,, ，输入 12200000，显示为 12.2。
3. 在自定义格式中指定字体颜色
要想在格式里指定字符的颜色，我们可以用以下 8 个颜色的名字，颜色的名字要放在方括号里，并且颜色的名字要是格式编码中出现的第一个元素。可用的颜色是[Black]，[Blue]，[Cyan]，[Green]，[Magenta]，[Red]，[White]，[Yellow]。我个人觉得通过自定义格式的方式来定义字符显示的颜色不如从 Font 配置页来得快和直观。但是在自定义格式里指定颜色有种特殊的灵活性，比如定义格式为 [BLUE]0;[RED]-0;[GREEN]0;[MAGENTA]@ ，输入正数时用蓝色显示，输入负数时用红色显示，输入 0 时用绿色显示，输入非数字字符时用洋红色显示。
颜色还经常和条件在一起使用。当数字满足我们指定的条件时，就应用指定的颜色来显示。条件表达式也要放在方括号里。例如，[Red][<=100];[Blue][>100] 表示小于等于 100 的数用红色字体表示，大于 100 的数用蓝色字体表示。其它的条件格式化功能请用 Format 菜单的 Conditional Formatting 命令。
4. 自定义日期和时间的表示方法
请看下表中日期的占位符的意义：
| m | 用数字显示月份，小于 10 的月份前面不带 0。如 1-12。 | |
| mm | 用数字显示月份，小于 10 的月份前面用 0补足。如 01-12。 | |
| mmm | 用英文缩写表示月份，如 Jan 到 Dec。 | |
| mmmm | 用英文全称表示月份，如 January 到 December。 | |
| mmmmm | 用单个字母表示月份，如 J 到 D。 | |
| d | 用数字表示日期，前面没有 0。 | |
| dd | 用数字表示日期，小于 10 的前面用 0 补足。 | |
| ddd | 用英文缩写表示星期几，如 Sun 到 Sat。 | |
| dddd | 用英文全称表示星期几，如 Sunday 到 Saturday。 | |
| yy | 用两位数字表示年份，如 00-99。 | |
| yyyy | 用四位数字表示年份，如 1900-9999。 |
请看下表中时间占位符表示的意义：
| h | 用数字显示小时，前面不带 0，如 0-23。 |
| [h] | 显示过去的小时数。如果使用的公式返回的时间的小时数超过 24，用如下格式 [h]:mm:ss。 |
| hh | 用数字示小时，需要时前面用 0 补足。如果格式定义里包含了 AM 或 PM，时间是 12 进制的。否则就是 24 进制的时间表示法。 |
| m | 用数字显示分钟，前面不带 0，如 0-59。 注意： m 或 mm 占位符必须出现在 h 或 hh 占位符紧后面，或 ss 占位符紧前面；否则，Excel 显示月份而不是分钟。 |
| [m] | 显示过去的分钟数。如果使用的公式返回的时间的分钟数超过 60，用如下格式 [mm]:ss。 |
| mm | 用数字显示分钟，需要时前面用 0 补足，如 00-59。注意事项参见占位符 m。 |
| s | 用数字显示秒数，前面不带 0，如 0-59。 |
| [s] | 显示过去的秒数。如果使用的公式返回的时间的秒数超过 60，用如下格式 [ss]。 |
| ss | 用数字显示秒数，需要时前面用 0 补足，如 00-59。如果想显示秒的小数部分，用如下格式 h:mm:ss.00。 |
| AM/PM,am/pm,A/P,a/p | 用 12 小时制来显示小时。对于半夜到中午段内的时间，Excel 显示 AM，am，A，或 a；对于中午到半夜段内的时间，Excel 显示PM，pm，P，或 p。 |
5. 所有的输入都以文本显示
我们只要把格式定义为 @ 就可以了。@ 符号是字符串的占位符。当然，@ 符号通常应该出现在格式定义编码的第四节。我们前面已经举例讲过，如果定义了第四节，但是什么占位符都没提供的话，输入的字符串不会显示。如果想为输入的字符串也加个前缀或后缀，做法跟数字加前后缀一样，把字符放在双引号里面(" ")。
6. 让空格的宽度跟某个指定的字符一样长
这个功能我估计很少有人用。不过 Excel 在它预先提供的自定义格式列表里列出了这种用法，例如 $#,##0_);($#,##0) 。如果想让空格的宽度跟某个指定的字符一样长，在这个字符前面加上下划线(_)。在上面的例子中，输入负数的显示效果是要用圆括号括起来的，但输入正数的显示效果是没有圆括号的。为了让输入的正数和负数在显示时位置对齐，正数后面就要补空格。我们可以在正数格式定义部分直接加空格，但有的字体每个字符的宽度是不一样的，为了精确对齐，我们就要让正数后面补的空格跟负数用的某个符号宽度一样，方法就是下划线(_)加这个字符，用来表示跟这个字符一样宽的空格。
7. 重复字符
如果我们想用某个字符填满单元格的宽度，就输入一个星号(*)，后面跟着要重复的那个字符。例如，格式 0*- 的显示效果就是在输入的数字后面填上足够多的“-”，填的多少视单元格的宽度而定。*0G/通用格式 ，在输入的数字前面填上足够多的 0 。
8. 显示特殊的货币符号
美元符($)是可以直接放在格式编码里的，如果想显示其它货币符号，比如 ￠，￡ 等，就要用数字小键盘输入。当我们自定义格式时，按住 ALT 键，在小键盘输入 0162，显示 ￠；ALT+0163 显示 ￡；ALT+0165 显示 ￥；ALT+0128 显示 €。
9. 用百分比显示数字
在数字格式编码后面加上百分号(%)。例如定义格式为 0.00% ，当我们输入 28，就显示 28.00%。
10. 用科学计数法显示数字
要想用科学计数法显示数字，在格式编码里使用指数占位符 E-，E+，e-，或 e+。指数占位符后面一定要有 0 或 #，指数占位符后面的 0 或 # 用来指定指数显示的位数。“E-”或“e-”在负的指数前面加个负号(-)，“E+”或“e+”除了在负的指数前面加个负号(-)外，还在正的指数前面加个正号(+)。例如，格式定义为 0.00E-00 ，输入 123000，显示为 1.23E05。格式定义为 0.00E+### ，同样的输入 123000，显示为 1.23E+5.。
11. 对于任意数字输入，只显示两种状态，YES/NO 或 TRUE/FALSE
格式的编码如下："Yes";"Yes";"No" 或 "True";"True";"False" 。输入 0 就显示 No 或 False，对于其它任意数字，都显示 Yes 或 True。
最后，试一下这个自定义格式的效果 [Blue]#,##0.00_);[Red](#,##0.00);0.00;"sales "@ 。
扫一扫
552
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
