图 4.80 是某公司的工资表,现要求将出勤异常栏的时间后面加“小时”,实际工资的前面添加“美元”,从而不至于产生歧义。有没有办法批量添加而不影响公式运算呢?

Excel 能否批量添加前缀/后缀-Excel22

解题步骤

自定义单元格的数字格式可以批量为单元格添加前缀或后缀,而且不影响公式运算,具体操作步骤如下。

1.选择C2:C11区域,按组合键<Ctrl+1>,弹出“设置单元格格式”对话框。

2.单击“自定义”,右方的格式代码文本框中默认值是“G/通用格式”,在其后添加“小时”2字,操作界面如图4.81所示。

Excel 能否批量添加前缀/后缀-Excel22

图4.81 设置单元格格式从而添加后缀

3.单击“确定”按钮保存设置,然后选择E2:E11区域,并按组合键<Ctrl+1>,弹出“设置单元格格式”对话框。

4.单击“自定义”,右方的格式代码文本框中显示了以前设置的格式代码“0.0”,在其前方添加“美元\:”,操作界面如图4.82所示。

Excel 能否批量添加前缀/后缀-Excel22

图4.82 设置单元格格式从而添加前缀

Excel 能否批量添加前缀/后缀-Excel22

图4.83 添加前缀与后缀的结果

格式代码原本应该是“美元:0.0”,表示在数字格式“0.0”前追加“美元:”,但是由于半角冒号属于特殊字符,需要使用“\”将它还原为字符本身。

5.单击“确定”按钮保存设置,同时返回工作表界面,此时工资表将显示为图4.83所示的结果,C列添加了后缀,E列添加了前缀。

知识扩展

1.单元格的格式代码相当复杂,有太多的特殊字符,也有很多固定的字符串需要记住。“G/通用格式”代表默认的格式“常规”,也就是说,所有单元格在自定义格式之前都采用“G/通用格式”,在此情况下,单元格会自动调整数值的显示方式。例如,单元格的宽度不够显示整个数字时,会对小数进行四舍五入,还会对大于等于12位的值使用科学计数(指数)法显示。自定义数字格式时往往会固定小数的位数,或者按需求添前缀、后缀。

2.由于“G/通用格式”代表“常规”,因此“G/通用格式小时”表示在原来的数值后面添加后缀“小时”,“美元G/通用格式”则表示在原来的数值前面添加前缀“美元”。本例中E2:E11区域原本的格式代码是“0.0”而不是“G/通用格式”,因此采用的格式代码为“美元\:0.0”。

3.格式代码原本是分4个段的:“正数;负数;0;文本”,当需要添加前缀或后缀的区域有正数以外的值时,格式代码会复杂许多。例如,B2:B2 区域既可能有正数、负数、0,也可能有文本,现要求在正数前面添加“收入”、负数前面添加“支出”、0和空白都不显示、文本则添加“【”和后缀“】”,那么可以对此区域设置格式代码“收入G/通用格式;支出G/通用格式;;【@】”,图4.84是数据源,图4.85是设置数字格式后的效果。

Excel 能否批量添加前缀/后缀-Excel22

图4.84 数据源

Excel 能否批量添加前缀/后缀-Excel22

图4.85 添加格式代码后

此处的格式代码中包含3个分号,它将代码切分为4段,第一段“收入G/通用格式”对应正数,表示在正数前面添加“收入”;第二段“支出 G/通用格式”对应负数,表示在负数前面添加“支付”,由于“G/通用格式”前面没有写负号,因此负数“-12.5”将显示为“支持12.5”;第三段是空白,分号后面什么都没有,因此当单元格中的值是0时将显示为空值;第四段“【@】”对应文本,表示在文本前后分别添加“【”和“】”,代码中的“@”代表文本。

4.格式代码的前三段都是数值,最后一段代表文本,因此也可以使用两段代码来分别限制数值和文本。例如,格式代码“0元;【@】”表示对正数、负数和0都添加后缀“元”,文本则添加方括号,效果如图4.86所示。

Excel 能否批量添加前缀/后缀-Excel22

图4.86 用两段代码格式化区域

使用两段编码也基本能实现需求,不过分四段代码控制单元格能做到更精细、更有针对性。

5.格式代码可以让单元格显示额外的内容,如让数字显示为文本,但是它仅改变了单元格的显示效果、不改变单元格本身的值,因此不管如何设置单元格的格式都不会影响运算。