分类：效率篇

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

解题步骤

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

1.选择C2：C11区域。

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

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

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

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

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

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

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

8.将C1：E1单元格的标题修改为年、月和日，然后对A1：E11区域添加边框，表格的最终效果如图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所示的“文本分列向导”对话框。

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

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

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

知识扩展

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

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

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

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

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

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

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

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

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

解题步骤

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

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

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

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

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

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

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

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

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

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

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

11.将“查找内容”设置为“求和项：”，然后单击“全部替换”按钮，替换后的二维表效果如图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群下载文件和视频教材，从而方便练习，同时验证本案例中的技巧与操作结果。

由于部分人员离职，提前领了工资，在图 4.11 所示的工资表中有若干行资料已经删除。现要求将这些空行一并删除，只保留有数据的行，有何方法一次性删除所有空行呢？

解题步骤

删除空行的重点在于定位空行，删除空行比较简单。具体操作步骤如下。

1.观察工资表的每一列，看是否有哪一列符合此条件——当整行都空白时此列在该行对应的单元格才空白，否则所有单元格都有数据。显然，在数据区域中除了I列以外，其他每一列都符合条件。

2.选择A、B、C、D、E、F、G、H列中的任意一列，假设是A列（但不能包含标题，标题行是不允许删除的）。

3.按下组合键＜Ctrl+G＞，弹出如图4.12所示的“定位”对话框，然后单击“定位条件”按钮，弹出如图4.13所示的对话框。

4.选中“定位”对话框中的“空值”单击按钮，表示选中当前选区中的空白单元格。

5.单击“确定”按钮保存设置，同时返回工作表界面。此时工作表中除标题行以外，A 列的所有空白单元格都处于选中状态，效果如图4.14所示。

6.单击键盘中的右键键，然后在弹出的右键菜单中选择“删除”，弹出“删除”对话框。图4.15是键盘上的右键键，图4.16则是“删除”对话框。

7.在“删除”对话框中选择“整行”，然后单击“确定”按钮执行删除。图4.17是删除空行后的工资表。

注意：有些小键盘有可能没有配备右键键，笔记本电脑也可能没有右键键，此时只能用鼠标调用右键菜单，不过要注意用鼠标单击时必须单击选中的所有空白单元格中的一个，否则无法删除空行。例如，通过“定位条件”对话框定位了A7、A11和A20以后，此时应用鼠标单击A7、A11和A20中的任意单元格调用右键菜单。

知识扩展

1.Excel的定位工具可以定位单个空白单元格，不会定位空白行。即只要单元格本身是空白的就符合定位条件，不管单元格所在的行是否全部空白。因此删除空白行的重点不在于定位空白单元格，而在于找到所有空白单元格都刚好位于空白行的列。在本例中，I列的部分空白单元格处于空白行中，部分空白单元格处于非空行中，因此不能对I列定位。

2.定位空白单元格后，调出右键菜单时，默认只能删除空白单元格，必须自行调整“删除”对话框的选项为“整行”，从而删除所有空白单元格所在的行。

3.如果数据太多，很难肉眼判断哪一列的空白单元格刚好处于空白行中，那么可以采用公式作为辅助工具来区分。仍以本例的数据为例，在所有数据的右方一列 J 列输入公式“=COUNTA（A3：I3）”，公式计算结果等于 0 者表示该行是空行，公式计算结果大于 0 者则表示该行至少有一个非空单元格，公式计算结果如图4.18所示。

由于Excel的定位工具无法定位0值，因此还需要改进公式，采用“=1/COUNTA（A3：I3）”，公式的含义是1除以非空单元格的数量，当非空单元格的数量为0时，只能得到错误值；当非空单元格的数量大于0时，则返回一个正数，效果如图4.19所示。

此时选择I列，然后打开“定位条件”对话框，按图4.20所示的方式设置定位条件为公式错误值，当单击“确定”按钮后，I列的错误值所在单元格都会被自动选中。

最后只需要从右键菜单中调出“删除”对话框，删除整行即可。

此方法步骤较多，但是判断更精确，适用于数据较多的工作表，避免肉眼判断失误。

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

当工作表中需要大量输入数据时，输完一行资料后总需要改用鼠标来激活第二行的第一个单元格，然后才能继续输入。例如，在图 4.3 中，当在 F2 单元格输入身高后需要用鼠标单击 A3单元格，从而输入第二人的姓名。

时而键盘操作时而鼠标操作会降低输入数据的效率，如果全程都采用键盘操作则输入速度能大大提高。问题是如何才能实现仅用键盘快速输入图4.3所示的资料表呢？

解题步骤

Excel内置的记录单工具可以提升数据输入速度，具体操作步骤如下。

1.选择A1：F1区域的标题栏。

2.使用组合键＜Alt+D+O＞调用记录单工具。此处要注意按键方式，不能三键同时按，而是按下Alt键后松开再按D，松开D键后再按O。

按下以上组合键后并不能立即弹出记录单工具，而是弹出图4.4所示的对话框，此时单击“确定”按钮后才会弹出图4.5所示的记录单工具。

3.由于光标默认处于姓名框中，因此不需要使用鼠标操作，直接输入姓名，然后按Tab键跳转到籍贯处。当输入籍贯后按下Tab键，光标会跳转到性别处……当输入完第一笔资料后，对话框中的“新建”按钮将处于激活状态，效果如图4.6所示。

4.单击Enter键，Excel会将已经输入窗体中的第一笔资料一次性写入到工作表中，然后将光标切换到姓名框，等待输入下一笔资料，效果如图4.7所示。

5.继续输入第二笔资料，当所有项目输入完成后按下 Enter 键就自动将当前资料导入工作表中，且保存在第一笔资料的下一行，然后Excel会将光标再次移到姓名框中，从而使输入全程不需要用到鼠标，提升了输入效率。

知识扩展

1.输入资料时，时而鼠标操作、时而键盘操作会严重影响输入效率，只有全程键盘操作才是最高效的。记录单可以避免使用鼠标，执行“新建”命令后会自动返回输入框中，等待用户输入下一笔资料，删除一行资料或查看上一条、下一条都比较方便。

2.记录单在Excel 2003版本中出现在“数据”菜单中，从Excel 2007开始使用功能区替代了传统菜单，而记录单并没有出现在功能区的所有选项卡中，因此只能使用快捷键调用记录单。

3.假设不用记录单，还可以选中A2：F14区域后再输入资料，每输入一项数据后都按Enter键即可，当第一笔资料输入完后成，Excel会自动切换到第二行的第1个单元格中，等待输入第二笔资料。

4.记录单除了用于快捷输入资料以外，还可用于搜索资料。选择所有已经输入的资料（包含标题行），然后按组合键＜Alt+D+O＞打开记录单界面，然后单击“条件”按钮，在任意一项标题后面输入条件，再单击“上一条”或“下一条”按钮就能查看所有符合条件的数据了。

例如，在图4.8中，条件设置为年龄大于30，当单击“上一条”按钮后窗体中将显示第一条满足条件的资料，同时会注明当前资料属于第几条，效果如图4.9所示。

单击“下一条”按钮，Excel会在窗体中罗列出第二条符合条件的资料，效果如图4.10所示。

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

当打开了多个工作簿，需要对这些工作簿的内容相互比较时，反复单击要查看的工作簿显然效率太低，是否有办法同时看到多个工作簿的内容，从而快速、高效地比较数据呢？

解题步骤

Excel提供了专用的工具来排列工作簿窗口，使用户可以同时看到多个窗口内容，具体步骤如下。

1.打开3个需要并排查看的工作簿。

2.单击功能区的“视图”→“全部重排”，弹出“重排窗口”对话框，如图4.1所示。

3.从“重排窗口”对话框中将排列方式改为“垂直并排”，然后单击“确定”按钮，Excel会将当前的3个工作簿并排显示，效果如图4.2所示。

知识扩展

1.Excel可以将10个、8个工作簿窗口并排显示，不过由于屏幕大小问题，不宜将大于等于5个工作簿并排显示，否则每个窗口能显示的空间极其有限，并不会给查看数据带来便利。

2.“重排窗口”对话框中有平铺、水平并排、垂直并排和层叠4个选项，其中平铺表示将所有窗口同时显示在屏幕中，其中活动工作簿窗口占据左边最大的区域，其他所有工作簿窗口平均分布在右方的区域。水平并排表示将所有工作簿窗口从上向下平均分布，垂直并排表示将所有窗口从左向右平均分布，层叠表示所有窗口重叠显示，但是上层窗口不覆盖下层窗口的标题栏。

3.重排窗口仅针对同时打开多个工作簿时有效。

请在封底所指定的网站下载案例文件，从而方便练习，同时验证本案例中的技巧与操作结果。