分类：Excel 2016实用技巧大全

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

解题步骤

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

1.选择C2：C11区域，按组合键＜Ctrl+1＞，弹出“设置单元格格式”对话框。

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

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

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

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

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

图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是设置数字格式后的效果。

图4.84 数据源

图4.85 添加格式代码后

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

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

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

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

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

下图中的产品单价是美金，领导突然要求改用人民币结算，要把D列的所有单价都扩大到6.7倍（当前的美金与人民币换算比例为6.7：1）。

手工一个一个修改单价过于烦琐，是否有办法一次性修改完所有的单价呢？

解题步骤

选择性粘贴工具可以批量将单元格的值扩大若干倍或缩小到N分之一，具体操作方法如下。

1.在G1单元格输入数值6.7。

2.复制G1，并选择D2：D10区域，然后单击右键，从右键菜单中选择“选择性粘贴”，弹出“选择性粘贴”对话框。

3.在对话框中选择粘贴选项“数值”及运算选项“乘”，然后单击“确定”按钮。

4.将标题“单价（美金）”修改为“单价”，图4.76是粘贴选项设置界面，图4.77则是粘贴结果，可以看到D2：D10区域内每个单元格的值都扩大了6.7倍。

如果需要将D2：D10区域缩小1/6.7，那么按同样步骤执行选择性粘贴即可，唯一不同的地方是在“选择性粘贴”对话框中将“乘”修改为“除”。

知识扩展

1.选择性粘贴提供了加、减、乘、除等运算选项，默认状态是“无”。

2.通过选择性粘贴对一个区域扩大或缩小时需要注意的是必须在指定运算选项的同时将粘贴选项设置为“数值”，否则会将格式信息一并复制过去。

3.事实上，选择性粘贴工具不仅可以对一个区域中的每个值都扩大/缩小相同的倍数，还可以一次性将不同单元格扩大/缩小不同的倍数。例如，要求图 4.78 中 A1：A10 每个单元格都相应地减去C1：C10，那么可以复制C1：C10，然后选择A1：C10区域，弹出“选择性粘贴”对话框，选择“数值”和“减”复选框，单击“确定”按钮后A1：A10的值将分别减去对应于C1：C10区域的值，图4.79即为批量相减的结果。

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

图4.70所示的订单表中金额数量较大，想让它显示为以“万”为单位，如9850000显示为“985万”，有没有办法一次性修改所有的单元格？

解题步骤

设置单元格的数字格式可以让单元格的值显示为以“万”为单位，具体操作步骤如下。

1.选择金额区域D2：D6。

2.按下组合键＜Ctrl+1＞，弹出“设置单元格格式”对话框。

3.单击“自定义”，然后在右方的“类型”文本框中输入格式代码“0\.0，万”，输入界面如图4.71所示。

图4.71 设置单元格的数字格式

4.单击“确定”按钮保存设置，同时返回工作表界面，金额区域将显示为图4.72所示的效果。

图4.72 以万作为单位显示的金额表

知识扩展

1.在格式代码中有很多字符并不代表字段本身，而是拥有特殊含义的编码，有点像DOS命令中的通配符“*”和“？”，前者代表任意长度的任意字符，而非代表“*”这个字符，后者代表长度为 1 的任意字符，而非代表“？”这个字符本身。假设一定要使用“*”和“？”，那么采用“^”消除它们的特权即可，如用“^*”代表字符“*”，用“^？”代表字符“？”

Excel的格式代码中比较类似，如在小数点左边的0代表任意整数，小数点右边的0则只代表一位小数，因此格式代码“0.00”其实表示整数位数不限、小数位数是2。

格式代码中有很多具有特殊含义的字符，b、c、d、g、h、m、n、s、t、z、-、\、＠、＃、[]、%及小数点、半角逗号都是，如果需要去除这些字符的特殊含义仅使用字符本身，那么在它前面添加“\”即可。例如，单元格的值是123.456，设置格式代码“0”后将显示为“123”，因为格式代码中没有小数点和小数，假设将格式代码修改为“\0”，那么单元格将显示为“0”，因为此处格式代码中的0不再具有特殊含义，仅仅代表数字0本身。图4-73和图4-74分别展了格式代码“0”和“\0”的显示结果。

图4.73 格式为“0”的效果

图4.74 格式为“\0”的效果

基于以上分析，现在再来理解“0\.0，万”就比较简单了。“0\.0，万”中的“，”代表千分位分隔符，它前面的“0\.0”表示在小数点千分位分隔符的左边插入小数点和一位数值，从而使小数点出现在 4 位数值的左边，将原来的数值单位从元提升到万。例如，原本的“123.456”将显示为“1.2 万”，在千位分隔符的左边一位数字前产生小数点，千分位分隔符右方只显示“万”，不显示千分位分隔符右方的其他字符。

2.本例的格式代码只能让金额带一位小数，如果要求小数点右边显示4位小数，那么应该将格式代码修改为“0！.0000万”。无法实现小数点右方显示2位或3位小数。

3.格式代码仅修改单元格的显示方式，单元格本身的值并没有产生变化。例如通过格式代码将123显示为0时，尽管看到的是0，但用该单元格参与数值运算时仍然按123计算。

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

在图4.67所示的订单表中，单价的小数包含1位、2位、3位，显得比较凌乱，现要求将它们按小数点对齐。

一个一个单元格调整比较耗时，是否有办法一次性让它们按小数点对齐呢？

解题步骤

设置单元格的数字格式可以让数值按小数点对齐，具体操作方法如下。

1.选择单价区域B2：B6。

2.按组合键＜Ctrl+1＞，弹出“设置单元格格式”对话框。

3.单击“自定义”，然后在右方的“类型”文本框中输入数字格式代码“0.？？？”，要注意在半角状态下输入代码。输入界面如图4.68所示。

图4.68 设置单元格的数字格式

4.单击“确定”按钮保存设置，同时返回工作表界面，此时单价将显示为图4.69所示的结果。

图4.69 按小数点对齐的单价表

知识扩展

1.代码“0.？？？”表示整数部分为任意值、小数位数为3位，当小数位数不足3位时小数部分总占据3位字符的宽度。

2.改成“0.000”也可以让单价按小数点对齐，不过当小数位数不足3位时会用0占位，从而使原来的“1.1”显示为“1.100”。用户应根据自己的需求决定采用“0.000”还是“0.？？？”，或者“0.0？？”。采用“0.0？？”的好处是假设单价中有整数12，不会显示为“12.”这种不伦不类的小数，而是显示为“12.0”。

3.本例的格式代码中采用3个问号“？？？”，是因为单价中最长的小数位数为3。因此要确保小数总是按小数点对齐，在设置单元格的数字格式前应观察小数点右方的小数最大长度。

4.设置单元格的格式仅修改它的显示样式，并不会修改单元格的数值本身。

5.把其他单元格粘贴到B2：B6区域，如果不采用选择性粘贴的方式粘贴数值，而是使用快捷键＜Ctrl+V＞执行粘贴，那么粘贴后会破坏B2：B6区域原本设置的格式，从而导致粘贴后的单元格与其他单元格的小数点位置不一致。

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

图 4.50 所示的职工信息中，出生年月日合并为一个字符串存放在单个单元格里，是否可以一次性将所有日期分别提取年、月、日，然后保存在右方的3个单元格中？

图4.50 职工信息表

解题步骤

由于 C 列的出生日期统一长度为 8 位，有明显的规律，因此可以利用分列工具完成，具体操作步骤如下。

1.选择C2：C11区域。

2.单击功能区的“数据”→“分列”，弹出“文本分列向导”对话框。

3.选择“固定宽度”，然后单击“下一步”按钮，操作界面如图4.51所示。

图4.51 设置分列方式

4.分别在8位字符的第4位和第6位右方单击，从而添加两条分列线，效果如图4.52所示。

图4.52 添加分列线

5.单击“下一步”按钮，进入“文本分列向导”的第3步，然后选中“文本”单选按钮，表示让分列后的第1列显示为文本格式，操作界面如图4.53所示。

图4.53 设置分列后的单元格格式

此时在下方的“数据预览”框中可以看到第一列的标题显示为文本，第2列和第3列仍然是常规格式，因此还需要继续修改第2列与第3列的格式。

6.单击第2列的标题“常规”，然后单击上方的“文本”单选按钮。

7.单击第3列的标题“常规”，然后单击上方的“文本”单选按钮，最后单击“完成”按钮，分列效果如图4.54所示。

8.将C1：E1单元格的标题修改为年、月和日，然后对A1：E11区域添加边框，表格的最终效果如图4.55所示。

图4.54 分列结果

图4.55 添加边框及修改标题

知识扩展

1.用3个公式也可以分别从8位日期中提取年、月和日，不过难度比分列大，而且速度更慢。

2.本例分列过程中将 3 列都设置为文本格式，其目的是分列后不丢失前置的 0。例如，19890308分列后将得到1989、03和08三段字符，由于Excel的单元格默认是常规格式，无法保存前置的0，因此会显示为1989、3和8三段字符，只有设置为文本格式后才可以保存前置的0。实际工作中是否需要修改格式，由用户自身的需求而定。

3.如果数据是“1977年3月16日”、“1967年12月5日”这种形式，那么无法一次分列完成，宜用公式完成。

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

图 4.39 中地区名称的上下级之间采用了顿号作为分隔符，现要求将地区名称按级别分别存放在3个单元格中，即省、市、县名称各占一个单元格，是否有办法一次性完成呢？

解题步骤

Excel提供的分列工具可以按条件将字符串分别存放在多列中，只要有规律就可以分列成功，本例的规律是顿号，具体操作步骤如下。

1.选择待分列的区域B2：B6。

2.单击功能区的“数据”→“分列”，弹出图4.40所示的“文本分列向导”对话框。

图4.40 分列向导第1步

3.保持默认设置“分隔符号”，然后单击“下一步”按钮，打开图4.41所示的“文本分列向导”界面。在对话框中选择“其他”复选框，然后在全角状态下输入顿号。

图4.41 设置分列的分隔符

4.直接单击“完成”按钮，Excel会关闭对话框，同时对选区执行分列。图4.42是分列结果。

5.在B1：C1区域写入标题省、市、县/镇，然后对A1：D4区域添加边框，最终的地区表效果如图4.43所示。

图4.42 分列结果

图4.43 修改标题及添加边框

知识扩展

1.只要文本有规律就可以指定分列条件，配合分列工具对文本分列。例如，本例中按顿号分列是条件，每个地区名称中都有顿号则是规律。如果省名与市名、县名之间采用的是逗号，那么可以改为按逗号分列。

2.一次只能对一列进行分列，在选中多列的情况下无法弹出“文本分列向导”对话框。

3.Excel 允许使用单条件对文本分列，也可以使用多条件分列。例如，在图 4.44 中，地区名称中既有全角顿号又有半角逗号和半角空格作为分隔符，只要在“文本分列向导”中设置正确即可一次性分列成功。

图4.44 按3个条件分列

4.假设市名和省名都是3个字，那么可以按长度分列。例如，对图4.45所示的数据分列，可以在“文本分列向导”的第①步时按图4.46所示的方式设置分列选项。

图4.45 省名和市名都是3个字的地区表

图4.46 按宽度分列

然后在“文本分列向导”的第②步时分别在第3个字与第4个字之间单击，以及在第6个字与第7个字之间单击，从而创建两条分隔线，设置效果如图4.47所示，图4.48则是最终的分列结果。

图4.47 设置分列宽度

图4.48 分列结果

5.Excel 可以按指定的分隔符分列，也可以按指定的宽度分列，但是不能像图 4.49 一样按汉字、数值、字母分列。使用Excel的VBA可以开发插件来完成此类工作，Excel插件“E灵”集成了此工具，下载地址：http：//excelbbx.net/Eling.rar

图4.49 按汉字、数值、字母分列

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

如何将图4.31所示的二维表如何快速地转换成一维表？

图4.31 二维成绩表

二维表是指某一项数据同时占据多列的报表，在图4.31中，成绩占据了6行3列，像图4.26那种仅占用一列的则属于一维表。

解题步骤

使用数据透视表可以将二维表转换成一维表，然后配合定位、公式等操作即可完成需求，具体操作步骤如下。

1.单击成绩表中A1：D7区域的任意一个单元格，然后单击功能区的“插入”→“数据透视表”，弹出“创建数据透视表”对话框。

2.在“创建数据透视表”对话框中单击“现有工作表”，然后在“位置”处输入“E1”，表示将创建的数据透视表存放在E1单元格，操作界面如图4.32所示。

图4.32 设置透视表的存放位置

3.单击“创建数据透视表”对话框中的“确定”按钮，Excel 会在工作表中创建一个空白的透视表，同时在工作表右方自动出现数据透视表字段窗格，效果如图4.33所示。

图4.33 空白透视表

4.将姓名拖到下方的行字段中，将化学、数学和物理都拖到值字段中，工作表中的空白透视表将显示为图4.34所示的效果。

图4.34 设置透视表的字段

5.将列标题下方的“数值”拖到行标题的“姓名”下方，此时透视表将显示为图4.35所示的效果。

图4.35 移动数值字段

6.单击功能区的“设计”→“报表布局”→“以表格形式显示”。

7.单击功能区的“设计”→“总计”→“对行和列禁用”。

8.单击功能区的“设计”→“报表布局”→“重复所有项目标签”，此时透视表将显示为图4.36所示的效果。

9.复制E：G区域，然后右键单击H1单元格，从弹出的右键菜单中选择“值”（其图标为），操作界面如图4.37所示。

图4.36 调整透视表的显示方式

图4.37 选择性粘贴值

10.删除E：G区域，然后按组合键＜Ctrl+H＞，弹出“查找和替换”对话框。

11.将“查找内容”设置为“求和项：”，然后单击“全部替换”按钮，替换后的二维表效果如图4.38所示。

图4.38 批量删除“求和项∶”

12.最后将F1和G1单元格的值分别修改为科目和成绩。

知识扩展

1.打开“定位条件”对话框也可以按组合键一步完成，其组合键为＜Alt+H+F+D+S＞。要注意这几个键不能同时按，而是每按下一个键后松开再按下一个键。

2.透视表本身与数据源是相关联的，当数据源变化时，单击“设计”选项卡中的“刷新”菜单可使透视表的内容也相应地更新。本例通过选择性粘贴单独提取透视表中的值作为最终的转换结果，因此二维表数据源更新后一维表不可能相应地更新，必须重新执行本例的所有步骤，重新生成一维表。

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

如图4.26所示的一维表如何才能快速地转换成二维表？

一维表是指每一项数据都显示在一列中的报表，在图 4.26 中，姓名占据一列，科目占据一列，成绩也占据一列。

解题步骤

当数据量大时，手工将一维表转换为二维表相当困难，但是借用数据透视表转换则简单、快捷，具体步骤如下。

1.单击成绩表中 A1：C19 区域的任意一个单元格，然后单击功能区的“插入”→“数据透视表”，弹出“创建数据透视表”对话框。

2.在“创建数据透视表”对话框中单击“现有工作表”，然后在“位置”处输入“E1”，表示将创建的数据透视表存放在E1单元格，操作界面如图4.27所示。

3.单击“创建数据透视表”对话框中的“确定”按钮，Excel 会在工作表中创建一个空白的透视表，同时在工作表右方自动出现名为“数据透视表字段”的任务窗格，效果如图 4.28 所示。

4.将姓名拖到下方的行字段中，将科目拖到列字段中，再将成绩拖到值字段中，工作表中的空白透视表将显示为图4.29所示的效果。

5.将E2：H8复制到E10单元格，然后删除透视表，最后剩下的二维表效果如图4.30所示。

知识扩展

1.使用函数也可以将一维表转换成二维表，不过需要使用4个公式，效率低而且公式复杂，用透视表则简单、快捷。

2.E1：I9区域属于透视表，用鼠标选择其中的E2：H8区域时需要掌握一点技巧，否则可能选择单元格变成了拖动单元格。正确的操作方式是选择H8，然后向左上角拖动，不能选择E2后向右下角拖动。

3.也可以直接使用透视表作为最终结果，只是需要禁用透视表的横向与纵向汇总。方法是选择透视表区域的任意单元格，然后单击功能区的“设计”→“总计”→“对行和列禁用”。

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

在图4.21所示的工资表中，实发工资一栏中部分人员的工资为0，值为0的单元格可能有1个也可能有几百个，现要求删除所有值为0的单元格所在行，如何才能一次性删除呢？

解题步骤

删除0值所在行的重点在于找到所有0值，然后通过右键删除整行即可，具体步骤如下。

1.选择H列。

2.按组合键＜Ctrl+F＞，弹出“查找和替换”对话框。

3.将“查找内容”设置为0。

4.单击对话框右下角的“选项（T）＞＞”按钮，从而让对话框显示更多内容。

5.选择“单元格匹配”复选框。

6.将“查找范围”的默认值“公式”修改为“值”，操作界面如图4.22所示。

7.单击“查找全部”按钮，然后按下组合键＜Ctrl+A＞，表示选中所有找到的单元格。查找选项的设置界面与查找结果如图4.23所示。

8.单击键盘上的右键，或者用鼠标单击 H7 单元格调出右键菜单，然后从右键菜单中选择“删除”，弹出“删除”对话框。

9.将删除选项由“下方单元格上移”修改为“整行”，然后单击“确定”按钮，H列的0值所在行将会一次性删除。图4.24是“删除”选项的设置界面，图4.25是删除结果。

知识扩展

1.手工输入单元格中的值可以替换成公式“=0/0”，但是通过公式计算出来的 0 则无法替换。因此本例没有采用将0替换公式“=0/0”，然后定位错误值再删除整行的思路。

2.Excel提供了3种查找方式，包含从单元格的值、批注和公式中查找，其中“值”是指在单元格中显示出来的值中查找，而“公式”表示不管单元格显示的值是什么，只在公式中查找。例如，A1 单元格的公式是“=153-103-20”，计算结果是 30，那么按公式查找 20，不选择“单元格匹配”复选框，A1单元格刚好符合条件，因为公式中包含数字20。如果改为按值查找，那么A1单元格不符合条件。正确理解“值”与“公式”才能用好查找与替换。

3.“查找与替换”对话框中的“单元格匹配”被选择时表示整段字符都与查找的对象一致才符合条件，不选择时表示部分字符与查找的对象一致就符合条件。例如，A1单元格中包含“张三丰”，那么未选择“单元格匹配”而查找“三”，A1 单元格是符合查找条件的，如果选择了“单元格匹配”，那么只有查找“张三丰”时A1单元格才符合查找条件。

4.“查找与替换”对话框中的“区分大小写”和“区分全/半角”仅用于查找字母，查找汉字和数字时仅需设置好“单元格匹配”选项即可。

5.当有多个单元格符合查找条件时，单击“查找下一个”按钮可以选中其中一个符合条件的单元格，单击“查找全部”时会在下方的列表中罗列出所有符合条件的单元格地址和单元格的值，但是Excel并不会选中这些符合条件的单元格。此时按下组合键＜Ctrl+A＞可以全选所有符合条件的单元格，要注意不能在关闭对话框后全选，而是单击“查找全部”后马上按下组合键＜Ctrl+A＞。

6.查找时Excel遵循一个规则：当选择了单个单元格后进行查找，Excel会在整个工作表中查找；当选择了多个单元格后进行查找，Excel 只在选区内查找。因此本例在查找前必须先选中H列，不能在选中单个单元格时执行查找操作。

请在封面中缝指定的QQ群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。