分页符

腻如玉指涂朱粉 光似金刀剪紫霞

——Excel 2007综合班I课时小结之二

 

第二课时同样非常丰富，木兰老师也讲了一些操作的技巧，特别是一些细节的地方以前都没有注意过，以前用一些笨办法做成的东东，现在感觉很简单了，比如自定义序列来排序，感觉太方便了。

一、定位功能应用

1、打开方式

（1）开始—编辑组—查找和选择—转到/定位条件，如图

（2）快捷键：Ctrl+G

（3）功能键：F5

其中，开始—编辑组—查找和选择—转到、快捷键Ctrl+G、功能键F5打开的是定位窗口，如图

而点击定位窗口下的“定位条件”，可打开定位条件窗口，此处效果与开始—编辑组—查找和选择—定位条件相同。

2、单元格的合并与反合并

很多时候，为使单元格更美观，要将同类的单元格合并，但这会给分类汇总、数据透视表、公式计算等带来麻烦，因为合并单元格时，会以所选单元格区域的第一个单元格作为显示值，其它单元格都显示为空。如图

要解决这个问题，有几种方法，在前面的综合技巧精粹里已经学过，这里复习一下其中一种。先选中所有的合并单元格，取消合并后居中，选中取消合并后的单元格区域，按功能键F5，在定位条件中选择“空值”，因此时激活的是第一个空单元格，直接在编辑栏中输入公式“=上一个单元格”，按Ctrl+Enter键，将空单元格都填充其之上的非空单元格，为避免因单元格变化引起的公式错误，复制后选择性粘贴为数值。如图

通过以上步骤，将原先由于合并后导致的空单元格都填充进相应内容。反过来，若想将同类单元格合并，又能正常的分类汇总等，即合并后的单元格非空，方法也比较多，常用为：首先，对要合并同类项的单元格区域排序，选中后选择分类汇总，会在该列之前添加一列分类汇总列，如图

定位分类汇总列的空值，恰好为需要合并单元格的前一列，此时直接按合并后居中，合并定位后的空单元格，再选择分类汇总列，通过定位条件将常量所在行删除。复制合并后的单元格，在需要合并的单元格区域使用格式刷刷一下，这样就将相关单元格合并了，且在取消合并后每个单元格都有值。

3、定位可见单元格

这个功能用得比较多，主要是需要将分类汇总或筛选后的可见数据复制到其它区域。主要使用Alt+;或定位可见单元格来选中可见单元格，如图

4、行/列内容差异单元格应用

这个功能是以当前活动单元格所在行或列为依据，每一行或每列额所有数据跟其做对比，定位不同的单元格。

在最初选择要定位的单元格区域时，要基准行或列开始选择，及时基准行或列在最下方或最右列也应如此。

二、条件格式

1、快速标识重复值或唯一值

2007版的条件格式相对于2003版变化非常大，提供了更强大的功能。条件格式位于开始功能区的样式组，提供了非常多的选择。如图

既可以通过已设定的一些条件格式进行设置，也可以自己新建规则来设置条件格式。如上图，选择重复或唯一，同时设置需要的格式，以快速标识重复值或唯一值。

这个里面的规则就基于日常工作中的需要自行多设置。

2、将条件格式设置的值转换为真实值

可利用剪贴板将含有条件格式的单元格转换为真实值，如图

此时，对粘贴后的单元格，可通过常规设置变换格式。此种方法具有一定的局限性，并不是所有的都可以。

3、屏蔽错误值

错误值并不一定都是不需要的，如我在成果里面与序时进度的差值，因每天都要更新，对超过序时进度的不显示，所以直接应用条件格式屏蔽，而不是直接予以删除。其主要方法是在条件格式中利用ISERROR函数，此处应为相对引用，通过改变错误值的字体颜色为背景填充色。如图

4、07条件格式新体验

2007版中的条件格式，新增了数据条、色阶等新功能，在日常工作过程中，可根据具体需要灵活改变单元格的显示方式，快速标识数据，令工作表更加丰富多彩。

三、排序

1、单元格背景色排序

在2007版中新增了按单元格颜色排序，其实这个功能应用的倒不是很多，但本次课程的成绩统计中，我用到了该项功能（见成果里面）。该项功能可以单元格颜色、字体颜色、单元格图标作为排序依据。如图

2、自定义序列排序

目前，Excel的排序功能是以默认的序列作为排序依据，如数字、字母、拼音等，中文也可以改用笔划，如图

在自定义序列中，可导入需要的序列，再按需要的排列方式进行排序，如图

自定义序列只能基于值（文本、数字以及日期或时间）创建自定义列表， 而不能基于格式（单元格颜色、字体颜色或图标）创建自定义列表同时，自定义序列里的排序条件最多添加64个，每个条件里面的字符串不能超过255个，且第一个字符不得以数字开头，如果超过这个就自己想办法解决吧。

四、分级显示

建立或取消方法：数据—创建组或清除分级显示。如图

同样可使用定位可见单元格将需要的显示结果复制。

在这个地方注意F4键的使用，真的很有用哦！

五、分类汇总

在分类汇总前，针对分类字段进行排序，排序时，可使用自定义序列。选择需分类汇总的单元格区域，注意标题行也需一起选择。之后在“数据”功能区中的“分级显示”组中点击分类汇总命令，如图

在分类汇总对话框中：勾选“汇总结果显示在数据下方”，则汇总结果显示在数据下方；若不勾选，则汇总结果显示在数据上面。勾选“替换当前分类汇总”，则只显示当前汇总结果；如果不勾选，则会累加显示二级、三级分类汇总等。勾选“每组数据分页”，则汇总的结果在打印时，每组分别各占一页，对于把每组单独打印的数据比较方便。

分类汇总是通过使用 SUBTOTAL 函数与汇总函数 （汇总函数：是一种计算类型，用于在数据透视表或合并计算表中合并源数据，或在列表或数据库中插入自动分类汇总。汇总函数的例子包括 Sum、Count 和 Average。）（如“求和”或“平均值”）一起计算得到的。可以为每列显示多个汇总函数类型。这部分在前期的课程中已经学习过了，可根据不同的需求进行设置。

六、合并计算

若要汇总和报告多个单独工作表中数据的结果，可以将每个单独工作表中的数据合并到一个工作表（或主工作表）中。所合并的工作表可以与主工作表位于同一工作簿中，也可以位于其他工作簿中。如果在一个工作表中对数据进行合并计算，则可以更加轻松地对数据进行定期或不定期的更新和汇总。

方法是先选择需要合并计算的若干个单元格区域，使用“数据”选项卡中 “数据工具”组“合并计算”的命令。如图

七、分列

可以基于分隔符（例如空格或逗号、句号或分号等字符）或基于数据中的特定分栏符位置来拆分单元格内容，具体情况则取决于数据的排列方式，注意只能针对列数据。方法是选择需分列的数据，选择“数据”选项卡“数据工具”组中的“分列”命令，如图

（1）以分隔符号分列

若数据中含有一些统一的符号，且想以该种符号作为分隔符，可在分列向导的第1步中选“分隔符号”，在第2步中选择相应的分隔符，在第3步中选择列数据的格式，同时选定分列到的目标区域，并可在下方预览分列后的效果。如图

（2）ERP导出的非日期格式批量转换为日期格式

分列时，一路默认选择，在第3步中的列数据格式选择“日期”，此时会转换为系统默认的日期格式。对系统中不存在的日期，则不会转换。如图

（3）固定宽度分隔以去除多余的空格

对长度相同的工号后的空格，可使用此方法去除。如图

这是针对文本长度相同时，若不相同，只能使用TRIM函数去除空格。

八、筛选

使用自动筛选来筛选数据，可以快速而又方便地查找和使用单元格区域或表中数据的子集。对单元格区域或表中的数据进行筛选后，可以重新应用筛选以获得最新的结果，或者清除筛选以重新显示所有数据。

筛选过的数据仅显示那些满足指定条件 （条件：所指定的限制查询或筛选的结果集中包含哪些记录的条件。）的行，并隐藏那些不希望显示的行。筛选数据之后，对于筛选过的数据的子集，不需要重新排列或移动就可以复制、查找、编辑、设置格式、制作图表和打印。

还可以按多个列进行筛选。筛选器是累加的，这意味着每个追加的筛选器都基于当前筛选器，从而进一步减少了所显示数据的子集。

1、自动筛选

使用自动筛选可以创建三种筛选类型：按值列表、按格式或按条件。对于每个单元格区域或列表来说，这三种筛选类型是互斥的。

操作方法是选择需要筛选的单元格区域，选择“数据”选项卡“排序和筛选”组下的“筛选”命令（或者“开始”选项卡“排序和筛选”组，或者快捷键Ctrl+Shift+L），在数据区域标题行的每个字段后都出现下拉箭头，可点击下拉箭头进行相应的筛选。如图

下拉箭头 表示已启用但未应用筛选，筛选按钮 表示已应用筛选。当鼠标悬停在下拉箭头上时会出现“全部显示”的屏幕提示，当鼠标悬停在筛选按钮上时会出现一个应用该列的屏幕提示。如图

另外，2007版还可以按照颜色进行筛选，也是一个不错的应用，如图

2、高级筛选

如果要筛选的数据需要复杂条件，则可以使用“高级筛选”对话框。在使用高级筛选前，需先建立条件区域，输入需要筛选的条件。选择列表区域后，点击“数据”选项卡“排序和筛选”组下的“高级”，弹出“高级筛选”对话框，如图

高级筛选中，有一个列表区域，一个条件区域，如果不在原有区域显示筛选结果，还可选择一个空白区域的单元格作为首行首列，将结果复制过去。

高级筛选的条件区域设定方法有较多，可以运用比较运算符让列表区域的相应值与条件区域比较，结果为逻辑值TRUE或FALSE。需要注意的是，由于在单元格中键入文本或值时等号 (=) 用来表示一个公式，因此 Excel 会评估键入的内容；不过，这可能会产生意外的筛选结果。为了表示文本或值的相等比较运算符，应在条件区域的相应单元格中键入作为字符串表达式的条件：=''=条目''。如图

另外，对筛选条件，也可使用*、？、~等通配符。同时，条件区域中，同行表示“与”的关系，同列表示“或”的关系。如图

 

可以使用日期格式，筛选两日期之间的相关内容，如图

最后， 也可使用高级筛选删除数据区域中的重复项，只需勾选“高级筛选”选项卡下的“选择不重复的记录”即可。

 

通过本次课的学习，对2007版 的功能有了新的认识，特别是条件格式、自定义排序、合并计算、分列等功能的一些新应用，相信熟练掌握后对日后的工作会非常有用。

非常感谢木兰老师，感谢两位班主任的辛勤工作！