自动筛选、高级筛选、分类汇总、单元格选项卡和自定义格式

自动筛选、高级筛选、分类汇总、单元格选项卡和自定义格式 一、自动筛选 1、自动筛选操作，选中筛选区域~菜单栏~数据~筛选~自动筛选 2、筛选项 (1)升序排列，降序排列，全部，前10项(前10项设置数字必须1-500之间)，自定义。 (2)自定义自动筛选:可以区分串列的“与”和并列的“或”两个条件选项，也可以运用,和*两个通配符进行自定义筛选条件。 (3)如果两个以上条件绑定筛选，则运用辅助列:连接符(=&)进行筛选。 3、注意的地方 (1)当前列的筛选，只需要选中当前列的标题行单元格和所属的第一个单元格，然后点自动筛选。 (2)如果数据区域中间有空格行的话，则需先选中整个筛选区域，然后点自动筛选。 4、合并单元格的自动筛选 (1)取消标题行单元格合并，而后进行自动筛选，然后重新合并原单元格区域。 (2)标题行下所属数据是合并的单元格时，先复制备份单元格列的合并格式到其它列，取消原单元格合并~F5定位~定位条件~空值~第一个活动单元格输入=再向上键~Ctrl+Enter，复制转换过的区域~选择性粘贴~数值~确定，再将备份的单元格列的合并格式复制粘贴到原列，最后才能进行自动筛选。 5、筛选结果的修改 通过筛选后，可以直接选中筛选后的区域进行修改，而不影响其它未被筛选到的单元格数据。 二、高级筛选 1、高级筛选操作，选中筛选区域~菜单栏~数据~筛选~高级筛选 2、高级筛选对话框 (1)方式:在原有区域显示筛选结果，将筛选结果复制到其他位置 (2)列区域:指的是被筛选的区域 (3)条件区域:指的是用来筛选的条件区域 (4)复制到:如果选中将筛选结果复制到其他位置，可以选择筛选结果放在哪个地方 (5)选择不重复的记录:指的是去除原数据列的重复值所产生的筛选结果 3、了解一般的高级筛选操作过程 4、将筛选结果复制到其它的工作表:必须先选中需要显示筛选结果的工作表，然后调出高级筛选对话框，选择将筛选结果复制到其他位置，然后按一般的高级筛选的操作进行。 5、筛选部分字段的结果 设置好指定的筛选标题字段，按一般的高级筛选操作进行，需要注意的是复制到这一栏需要选择设置好指定的标题区域，而不能选单元个指定字段。 6、利用视图管理器配合高级筛选，实现快速切换筛选结果。 筛选条件的“与”和“或”:必须同时满足的条件，写在不同的列，是并列的“与”的关系;只需满足其中之一的是“或”的关系，写在不同的行 筛选结果在其他工作表，应先激活存放结果的工作表，然后单击菜单数据-筛选-高级筛选 筛选结果较多需要反复筛选的，可以通过视图管理器存放结果，便于快速切换结果 筛选结果为部分字段的操作:首先在结果区域设置好套筛选的字段，在选择筛选区域，数据-筛选-高级筛选-列表区域-条件区域，“复制到”选择所有的设置好的字段区域，而不是单个的单元格 高级筛选自定义名称，在进行高级筛选时也可以给条件区域，列表区域及结果区域定义各自的名称，以便于方便在条件，结果和列表区域进行快捷的选择和切换 7、筛选里的自定义名称 (1)筛选后，名称框内的Criteria和Extract即自动对筛选条件及结果的一个自定义名称。 (2)可以选自定义条件、数据列表、结果区域的自定义名称，就可以直接进行高级筛选，不用再去一步一步设定筛选选项。 三、分类汇总 1、分类汇总对话框: (1) 分类字段:指定分类的字段。 (2) 汇总方式:有求和、计数、平均值、最大值等等。 (3) 选定汇总项:指定汇总的项目。 (4) 替换当前分类汇总:删除之前分类汇总结果，不选中则是叠加汇总。 (5) 每组数据分页:对分类汇总进行分页显示。 (6) 汇总结果显示在数据下方:指的是将汇总结果显示在数据下方。 (7) 全部删除:删除当前区域所有的分类汇总结果，(参照分类后的打印预览)。 2、分类汇总的操作 选中需要分类的区域~菜单栏~数据~分类汇总~设置相关汇总选项~确定。 3、注意:分类汇总前，必须选对相应列数据进行排序，否则分类汇总结果凌乱。 4、提取分类汇总显示的结果 分类汇总完成~F5定位~定位条件~可见单元格~复制定位到的单元格区域~粘贴到其它结果区域。 四、单元格选项卡和自定义格式 1、设置单元格格式 (1)了解并掌握常用格式工具栏的使用 (2)鼠标右键调用:选中单元格区域~鼠标右键~设置单元格格式 2、单元格格式选项卡对话框 (1)对齐 a) 文本对齐方式:水平对齐、垂直对齐;具体对跨列居中、及垂直左中操作讲解。 b) 文本方向:设置文本显示的方向。 c) 文本控制:自动换行、缩小字体填充、合并单元格。 d) 从右到左:根据内容、总是从左到右、总是从右到左。 (2)字体 a) 字体:指的是字体类型设置。 b) 字形:常规、倾斜、加粗、加粗倾斜。 c) 字号:指的是调整设置字符的大小，1-409的字号区域范围。 d) 下划线:无、单线下划、双线下划、会计单双线下划。 e) 颜色:设置字体的颜色。 f) 普通字体:清除原字体的设置并恢复到默认状态。 g) 特殊效果:删除线、上标、下标，尤其对一些特殊单位如平方(Alt+178)、立方(Alt+179)之类的显示设置。 h) 预览:对当前设置的字体结果预览。 (3)边框 a) 预置:无、外边框、内部。 b) 边框:可以具体设置外边框、内部、及倾斜线可设置表头。 c) 线条:可以指定线条的各种格式，级线条的颜色。 (4)图案 a) 设置单元格底纹 b) 单元格底纹也可以设置成图案 (5)数字选项卡:常规、数值、货币、会计专用、日期、时间、百分比、分数、科学记数、文本、特殊、自定义。 3、自定义单元格格式 打开单元格格式对话框的方法: 1.依次单击“格式”?“单元格”; 2.在键盘上按组合键; 3.单击鼠标右键，选择“设置单元格格式”。 自动筛选、高级筛选、分类汇总、单元格选项卡和自定义格式 一、自动筛选 1、自动筛选操作，选中筛选区域~菜单栏~数据~筛选~自动筛选 2、筛选项 (1)升序排列，降序排列，全部，前10项(前10项设置数字必须1-500之间)，自定义。 (2)自定义自动筛选:可以区分串列的“与”和并列的“或”两个条件选项，也可以运用,和*两个通配符进行自定义筛选条件。 (3)如果两个以上条件绑定筛选，则运用辅助列:连接符(=&)进行筛选。 3、注意的地方 (1)当前列的筛选，只需要选中当前列的标题行单元格和所属的第一个单元格，然后点自动筛选。 (2)如果数据区域中间有空格行的话，则需先选中整个筛选区域，然后点自动筛选。 4、合并单元格的自动筛选 (1)取消标题行单元格合并，而后进行自动筛选，然后重新合并原单元格区域。 (2)标题行下所属数据是合并的单元格时，先复制备份单元格列的合并格式到其它列，取消原单元格合并~F5定位~定位条件~空值~第一个活动单元格输入=再向上键~Ctrl+Enter，复制转换过的区域~选择性粘贴~数值~确定，再将备份的单元格列的合并格式复制粘贴到原列，最后才能进行自动筛选。 5、筛选结果的修改 通过筛选后，可以直接选中筛选后的区域进行修改，而不影响其它未被筛选到的单元格数据。 二、高级筛选 1、高级筛选操作，选中筛选区域~菜单栏~数据~筛选~高级筛选 2、高级筛选对话框 (1)方式:在原有区域显示筛选结果，将筛选结果复制到其他位置 (2)列表区域:指的是被筛选的区域 (3)条件区域:指的是用来筛选的条件区域 (4)复制到:如果选中将筛选结果复制到其他位置，可以选择筛选结果放在哪个地方 (5)选择不重复的记录:指的是去除原数据列的重复值所产生的筛选结果 3、了解一般的高级筛选操作过程 4、将筛选结果复制到其它的工作表:必须先选中需要显示筛选结果的工作表，然后调出高级筛选对话框，选择将筛选结果复制到其他位置，然后按一般的高级筛选的操作进行。 5、筛选部分字段的结果 设置好指定的筛选标题字段，按一般的高级筛选操作进行，需要注意的是复制到这一栏需要选择设置好指定的标题区域，而不能选单元个指定字段。 6、利用视图管理器配合高级筛选，实现快速切换筛选结果。 筛选条件的“与”和“或”:必须同时满足的条件，写在不同的列，是并列的“与”的关系;只需满足其中之一的是“或”的关系，写在不同的行 筛选结果在其他工作表，应先激活存放结果的工作表，然后单击菜单数据-筛选-高级筛选 筛选结果较多需要反复筛选的，可以通过视图管理器存放结果，便于快速切换结果 筛选结果为部分字段的操作:首先在结果区域设置好套筛选的字段，在选择筛选区域，数据-筛选-高级筛选-列表区域-条件区域，“复制到”选择所有的设置好的字段区域，而不是单个的单元格 高级筛选自定义名称，在进行高级筛选时也可以给条件区域，列表区域及结果区域定义各自的名称，以便于方便在条件，结果和列表区域进行快捷的选择和切换 7、筛选里的自定义名称 (1)筛选后，名称框内的Criteria和Extract即自动对筛选条件及结果的一个自定义名称。 (2)可以选自定义条件、数据列表、结果区域的自定义名称，就可以直接进行高级筛选，不用再去一步一步设定筛选选项。 三、分类汇总 1、分类汇总对话框: (1) 分类字段:指定分类的字段。 (2) 汇总方式:有求和、计数、平均值、最大值等等。 (3) 选定汇总项:指定汇总的项目。 (4) 替换当前分类汇总:删除之前分类汇总结果，不选中则是 叠加汇总。 (5) 每组数据分页:对分类汇总进行分页显示。 (6) 汇总结果显示在数据下方:指的是将汇总结果显示在数据下方。 (7) 全部删除:删除当前区域所有的分类汇总结果，(参照分类后的打印预览)。 2、分类汇总的操作 选中需要分类的区域~菜单栏~数据~分类汇总~设置相关汇总选项~确定。 3、注意:分类汇总前，必须选对相应列数据进行排序，否则分类汇总结果凌乱。 4、提取分类汇总显示的结果 分类汇总完成~F5定位~定位条件~可见单元格~复制定位到的单元格区域~粘贴到其它结果区域。 四、单元格选项卡和自定义格式 1、设置单元格格式 (1)了解并掌握常用格式工具栏的使用 (2)鼠标右键调用:选中单元格区域~鼠标右键~设置单元格格式 2、单元格格式选项卡对话框 (1)对齐 a) 文本对齐方式:水平对齐、垂直对齐;具体对跨列居中、及垂直左中操作讲解。 b) 文本方向:设置文本显示的方向。 c) 文本控制:自动换行、缩小字体填充、合并单元格。 d) 从右到左:根据内容、总是从左到右、总是从右到左。 (2)字体 a) 字体:指的是字体类型设置。 b) 字形:常规、倾斜、加粗、加粗倾斜。 c) 字号:指的是调整设置字符的大小，1-409的字号区域范围。 d) 下划线:无、单线下划、双线下划、会计单双线下划。 e) 颜色:设置字体的颜色。 f) 普通字体:清除原字体的设置并恢复到默认状态。 g) 特殊效果:删除线、上标、下标，尤其对一些特殊单位如平方(Alt+178)、立方(Alt+179)之类的显示设置。 h) 预览:对当前设置的字体结果预览。 (3)边框 a) 预置:无、外边框、内部。 b) 边框:可以具体设置外边框、内部、及倾斜线可设置表头。 c) 线条:可以指定线条的各种格式，级线条的颜色。 (4)图案 a) 设置单元格底纹 b) 单元格底纹也可以设置成图案 (5)数字选项卡:常规、数值、货币、会计专用、日期、时间、百分比、分数、科学记数、文本、特殊、自定义。 3、自定义单元格格式 打开单元格格式对话框的方法: 1.依次单击“格式”?“单元格”; 2.在键盘上按组合键; 3.单击鼠标右键，选择“设置单元格格式”。 查找、替换和定位 本节课主要学习以下几个方面的内容:查找、替换和定位，是我们日 常生活和工作中常常应用最为广泛的。 查找与替换 一、进入查找与替换方式 1、编辑—查找(或)编辑—替换 2、快捷键: a、查找:ctrl+f b、替换:ctrl+h 二、选项 1、格式。包括对单元格格式中等方面的设置。 2、区分大小写。在英文字母中常常要区分大小写。 3、单元格匹配。精确查找中的一种。 4、区分全/半角 同上。 5、范围:工作表、工作薄 6、搜索:按行、按列 7、查找范围:公式、值、批注 三、高级查找(使用通配符) 1. 查找对象及对应的符号 请使用 若要查找 ?(问号) 任何单个字符 *(星号) 任何字符数 ~(波形符)后跟 ?、* 或 ~ 问号、星号或波形符 2. 搜索目标及模糊搜索写法 搜索目标 模糊搜索写法 以B开头 B* 以B结尾 *B 含有B *B* 以?号开头 ~, * ~* 四、查找替换按钮的使用 其中查找、查找下一个、和替换、替换全部的要根据实际需要。并配合CTRL+A的全选的能够快速的选取所要的内容。 定位 (用于快速一次性选中比较特殊的单元格或区域) 一、打开方式 1、编辑-定位 2、ctrl+G 3、f5 二、定位条件 1、批注 是指选定区域中带有批注的单元格 2、常量 是指选定区域中内容为常量的单元格(数字、文本、日期或逻辑值等，公式的计算结果不是常量) 3、公式 指选定区域中包含公式的单元格(公式得到的结果包含有数字、文本、逻辑值、错误) 4、空值 指选定空单元格(没有任何内容的单元格) 5、当前区域 指选定活动单元格周围的矩形单元格区域，区域的边界为空行或空列 6、当前数组 指选定活动单元格所在的数组区域单元格 7、对象 指选定所有插入的对象 8、行内容差异单元格 指目标区域中每行与其他单元格不同的单元格 9、列内容差异单元格 指目标区域中每列与其他单元格不同的单元格 10、引用单元格 指选定活动单元格或目标区域中公式所引用的单元格 11、从属单元格 指选定引用了活动单元格或目标区域的公式所在的单元格 12、最后一个单元格 指选定目标区域中右下角带有数据或格式设置的单元格 13、可见单元格 指选定可以看见的单元格(不包含被隐藏的单元格) 14、条件格式 指选定应用了条件格式的单元格 15、数据有效性 指选定设置了数据有效性的单元格 一、自定义代码结构 1、自定义格式数字格式 “自定义数字格式”允许用户创建符合一定规则的数字格式，自定义格式并不会改变数值或文本本身，只改变数据的显示方式。 2、其调用自定义对话框 格式菜单~单元格~数字选项卡 或Ctrl+1 3、自定义格式代码结构详解: 结构一:未指定条件时的结构:以"分号"分隔组成相应的区段，每个区段代码作用于特定的对象 代码结构 区段数 说明 作用于正数;作用于负第一区段;第二区段;完整的结构共4个 数;作用于0值;作用于文 第三区段;第四区段 区段 本 第一区段;第二区段;作用于正数;作用于负 3个区段 第三区段 数;作用于0值 作用于0和正数;作用于 第一区段;第二区段 2个区段 负数 唯一区段 1个区段 作用于所有数据 结构二:根据实际需要自定义条件时的结构 代码结构 区段数 说明 第一区段;第二区 完整的结构共大于条件值;小于条件值;等于 段;第三区段;第四4个区段 条件值;作用于文本 区 第一区段;第二区作用于满足条件值1;作用于满 3个区段 段;第三区段 足条件值2;作用于其它情况 作用于满足条件值1;作用于满第一区段;第二区段 2个区段 足条件值2 代码作用于满足指定的唯一条唯一区段 1个区段 件的数据 4、自定义格式实例 在自定义格式中可设置字体颜色(但不能设置背景及边框颜色) 格式1:,#,##0;[红色],-#,##0;[蓝色]G/通用格式;[洋红]G/通用格式 (货币符号,带头的千分隔符数值，正数自定义色;负数红色;0蓝色;文本格式洋红色) 格式2:,#,##0;[红色],-#,##0;[蓝色]G/通用格式 (货币符号,带头的千分隔符数值，正数自定义色;负数红色;文本格式未设置默认为自定义色) 格式3:,#,##0;[红色],-#,##0 (货币符号,带头的千分隔符数值，负数为红色，其它均为自定义色) 格式4:[蓝色],#,##0 (货币符号,带头的千分隔符数值，数值单元格内容为蓝色，其它为自定义色) 格式7:,#,##0; (货币符号,带头的千分隔符数值，未自定义颜色格式) 格式5:,#,##0;[红色],-#,##0;[蓝色]G/通用格式; (货币符号,带头的千分隔符数值，正数自定义色;负数红色;0蓝色;不显示文本格式的单元格) 格式6:,#,##0;[红色],-#,##0; (货币符号,带头的千分隔符数值，正数自定义色;负数红色;不显示0值;文本单元格为自定义色) 格式8:,#,##0;; (隐藏负数及0值，显示货币符号,带头的千分隔符数值，及文本单元格内容) 格式9:,#,##0;;; (仅显示货币符号,带头的千分隔符数值，隐藏负数、0值及文本单元格内容) 格式10:;;; (隐藏单元格内所有的数值及文本格式的内容) 格式11:[洋红][>80]G/通用格式;[蓝色][<60]G/通用格式;[绿色]G/通用格式;@ (>80显示为洋红;<60显示为蓝色;其余数据显示为绿色;文本内容正常显示) 格式12:[洋红][>80]G/通用格式;[蓝色][<60]G/通用格式 (>80显示洋红;<60显示蓝色;其它数值隐藏;文本显示为洋红) 格式13:[洋红][>80]G/通用格式;[蓝色]G/通用格式 (>80显示为洋红;其它数值及文本单元格显示为蓝色) 格式14:[洋红][>80]G/通用格式 (>80显示为洋红;其它数值及文本单元格正常显示) 小结: 1)有分号而省略代码的，将被"隐藏" 2)根据条件书写的不同，条件中不包含的部分如果没有指定格式显示，将被"隐藏",在没有指定文本格式显示的情况下(没有第四区段),文本显示方式受第一区段的影响 3)要在自定义格式的某个段中设置颜色，只需在该段中增加用方括号括住的颜色名或颜色编号。Excel2003识别的颜色名为:[黑色]、[红色]、[白色]、[蓝色]、[蓝绿色]、[绿色]、[黄色]和[洋红]。Excel2003也识别按[颜色X]指定的颜色，其中X是1至56之间的数字，代表56种颜色。 二、数字及文本格式代码介绍 1) G/通用格式 按原始输入显示,不设置任何格式,同"常规"格式 2) # 数字占位符，只显示有效数字，不显示无意义的零值.(如果小数点后数字位数大于“#”的数量，按照“#”的位数四舍五入) 3) 0 数字占位符，当数字比代码的数量少时，显示无意义的0. 4) 可以利用代码0来让数值显示前导零，并让数值固定按指定位 与0组合为最常用的带小数的数字格式. 数显示。使用# 5) ? 数字占位符，需要的时候在小数点两侧增加空格;也可以用于具有不同位数的分数 6) . 小数点 7) , 千位分隔符 8) % 以百分数显示 9) * 重复下一个字符来填充列宽 10) _(下划线) 留出与下一个字符等宽的空格(利用这种格式可以实现"对齐") 11) ～ 强制显示下一个文本字符,可用于分号(;)点号(.)问号(?)等特殊符号的显示. 12) @ 文本占位符，如果只使用单个@，作用是引用原始文本,如果使用多个@，则可以重复文本 13) “文本” 显示双引号里面的文本 14) \ 显示后面的字符。(和""用途相同,如后面是字符，输入后自动转变为双引号表达。不同的“\”是显示后面的文本，双引号是显示双引号中间的文本。) 15) [颜色] 显示相应的颜色 [黑色] [蓝色] [蓝绿色] [绿色] [洋红色] [红色] [白色] [黄色](要注意的是，在英文版用英文代码，在中文版则必须用中文代码) 16) [颜色 n] 显示以数值n代表的调色板的颜色,n在1~56之间 17) [条件值] 设置条件使用,一般由比较运算符和数值构成 18) [DBnum1] 转换数值为中文小写数字 19) [DBnum2] 转换数值为中文大写数字 20) [DBnum3] 显示全角的阿拉伯数字与小写中文单位的结合,如"356"显示为 “ 3 百 5 十 6” 21) E 科学记数的符号 三、日期时间格式代码介绍 1) m 使用没有前导零的数字来显示月份(1,12) 使用没有前导零的数字来显示分钟(0,59) 2) mm 使用有前导零的数字来显示月份(01,12) 使用有前导零的数字来显示分钟(00,59) 3) mmm 使用英文缩写来显示月份(Jan,Dec) 4) mmmm 使用英文全称来显示月份(January,December) 5) mmmmm 显示月份的英文首字母(J,D) 6) d 使用没有前导零的数字来显示日期(1,31) 7) dd 使用有前导零的数字来显示日期(01,31) 8) ddd 使用英文缩写来显示星期几(Sun,Sat) 9) dddd 使用英文全称来显示星期几(Sunday,Saturday) 10) aaa 使用中文简称显示星期几(一,日)，不显示“星期”两字 11) aaaa 使用中文全称来显示星期几(星期一,星期日) 12) y/yy 使用两位数显示年份(00,99) 13) yyyy 使用4位数显示年份(1900,9999) 14) e 使用4位数显示年份(1900,9999)，2003版本有效。 15) h 使用没有前导零的数字来显示小时(0,23) 16) hh 使用有前导零的数字来显示小时(00,23) 17) s 使用没有前导零的数字来显示秒钟(0,59) 18) ss 使用有前导零的数字来显示秒钟(00,59) 19) AM/PM(A/P) 上午/下午 使用12小时制显示小时 20) [h][m][s] 显示超出进制的时间(如大于24的小时数或大于60的分与秒) 21) bbbb 使用4位数显示四位佛历年份，即以公元前543年为纪年元年，对1900年以后的日期有效。 四、自定义格式实战 1) 自定义格式中运用文本与@符号配合，实现输入部分内容前后自动添加前缀与后缀内容; 2) 运用!后跟.符号，或"."以强制数值小数点移位，实现万亿等单位的转换; 3) 运用?和.两个字符自定义数值单元格，以达到同列下小数点位数对齐显示; 4) 运用剪贴板转换自定义单元格成为实际值; 步骤1: 选定应用了数字格式单元格或区域; 步骤2: 快速连续两次组合键，调出Office剪贴板; 步骤3: 选定目标单元格或区域，单击剪贴板刚才复制项目旁边的下拉箭头，选择“粘贴”; 步骤4: 右键-选择性粘贴，对话框中选择“文本”，确定。 五、技巧综合运用 技巧一:运用剪贴板和查找与替换功能，实现行列甚至于区域单元格内容合并到同一单元格内。 技巧二: 1、通过分类汇总及格式刷，实现列下同一内容的合并单元格功能。 2、通过辅助列错位和定位条件中的行列内容差异单元格，实现合并单元格功能。 技巧三:含行列标签的表格，转换为以细化列标签的行列表格。(操作动画来自小月的基础操作题) 技巧四:运用辅助列1、2、3…和1.1、1.2、1.3…重新排序的方法实现规律化隔行的标题插入功能