1、目 录,Page 1,4.数据透视表图,3.公式与函数,2.常用功能和技巧,1.工作簿和工作表,5.简述图表应用,6.初识宏与VBA,培训前言,Office是最为流行的办公软件，其家族成员主要有Excel、Word、PowerPoint、Access及Outlook。前三者应用较为广泛，而Excel无疑是最重要的成员。 Excel可以制作电子表格、进行各种数据处理及计算分析等，被广泛应用于管理、统计、财务、金融等众多领域。 在很多企业和单位,Excel已经成为数据处理和信息化建设的重要工具。无论用户身处哪个行业、所在公司有没有实施信息ERP系统，只要和数据打交道，Excel几乎是不二的选择。,
2、Page 2,培训前言,其实市面上有很多关于Excel的书籍写得很不错，如Excelhome网站组织编写的Excel 2007应用大全、Excel实战技巧精粹系列等。 出版图书是面向全国不同水平层次的读者，知识点涉及较多或为求全面难免讲解不够深入，学习不知从何入手。有些不常用的功能，花了不少的时间和精力学习，却极少使用。 本教材根据公司内部培训资料整理而成，配以电子档实例、动态操作图及配套练习题，力求培训简单有效和快速吸收。,Page 3,培训前言,笔者从事财务工作13年，因此主要以财务工作中实际应用较多的操作技能为培训重点，旨在提高财务人员的实操能力，提升工作效率，也适用于统计、数据分析和文
3、职类工作。 特别说明：Excel操作通常会有三种实现方式，菜单栏选项、单击鼠标右键和快捷功能键。本着方便快捷的原则，一般只讲解常用的方式。 本教材适用Excel 2007版本。,Page 4,第1章 工作簿和工作表1.1 认识Excel 1.2 工作簿的操作 1.3 工作表的操作,Page 5,第1章 工作簿和工作表,1.1 认识Excel 首先，我们来了解一下Excel几个重要的对象，按照包含关系依次为工作簿、工作表和单元格。 工作簿：是指Excel环境中用来储存并处理工作数据的文件，也就是我们常说的Excel文档。它是Excel工作区中一个或多个工作表的集合。受内存影响，每个工作簿最多可创
4、建255个工作表。 新建的工作簿默认有3个工作表，如果要改变默认表数量，可以通过Excel选项-常用选项卡进行修改。,Page 6,第1章 工作簿和工作表,1.1 认识Excel 工作表：显示在工作簿窗口中、由行列交叉构成的表格,一个工作表可以有1,048,576行和16,384列（Excel 2003版本为65,536行和256列）。 单元格：工作表中行与列的交叉部分，它是组成表格的最小单位，可拆分或者合并。单元格按所在的行列位置来命名，例如“B5”指的是B列与第5行交叉位置上的单元格。 还有其他对象如图形、批注、控件等，在后面的章节涉及时再详细介绍。,Page 7,第1章 工作簿和工作表,
5、1.2 工作簿的操作 1.2.1 基本操作 创建(Ctrl+N) ：在Windows窗口空白处点击右键-新建-Excel工作表，也可以在Excel程序已打开时按Ctrl+N新建一个空白工作簿。 复制(Ctrl+C) 、剪切(Ctrl+X) 、粘贴(Ctrl+V) ：通过点击鼠标右键或快捷键完成。同一文件夹下复制，按住Ctrl键、同时用鼠标拖曳则效率更高。 删除(Ctrl+D) ：通过点击鼠标右键、按Ctrl+D或Delete键完成。 重命名(F2)：选中工作簿名称，点击鼠标右键或按F2功能键进入修改状态。,Page 8,第1章 工作簿和工作表,1.2.2 直接保存 手动保存：点击菜单栏图标 或
6、使用快捷键Ctrl+S。工作簿打开后有修改内容或格式（不包括查找），点击关闭会提示是否保存修改内容，否则直接关闭。 自动保存：为避免各种意外造成工作簿未能及时保存，Excel提供了自动保存和恢复功能。 当发生断电或程序崩溃的情形，Excel重启后会在左侧弹出最后一次保存前的工作簿，但最后一次保存后编辑的内容将不会保存，无法恢复。 友情提示：自动保存不能代替手工保存，经常点击保存按钮或Ctrl+S,培养良好的工作习惯。,Page 9,第1章 工作簿和工作表,1.2.3 另存为 另存为的作用在于原工作簿修改后不能直接保存时，可以将修改后的内容以其他格式或不同名称保存至同一文件夹中。如保存至不同文件
7、夹，则不受名称重复限制，也不会出现“xx文件已经存在，是否替换现有文件”的提示。 另存成功后，当前工作簿切换为新的工作簿。如打开A修改后另存为B，则A自动关闭，B为打开状态。A修改后如果没有手动保存，A的内容不会发生改变。 工作簿打开界面，点击左上角 图标，另存为选项下有多种文件格式可以选择。,Page 10,第1章 工作簿和工作表,1.2.3 另存为 “启用宏的Excel工作簿”是指工作簿包含宏表函数、宏或者VBA代码时，必须以这种格式保存，否则无法正常保存使用宏表函数的定义名称、宏或者VBA代码。 “Excel 97-2003工作簿”则主要是考虑到版本向下兼容（不能向上兼容，即低版本不能另
8、存为高版本），使两台不同Excel版本的电脑之间可以共享资源。 还有其他一些格式可以根据需要进行选择，不过实际使用的概率不大。,Page 11,第1章 工作簿和工作表,1.2.4 工作簿加密和只读 工作簿打开界面，点击左上角 图标，准备-加密文档，两次输入密码，保存后即可完成工作簿加密。再次打开文档，需要输入正确的密码。 解除加密状态：点击左上角 图标，准备-加密文档，清除密码后保存文档，加密解除。 为防止其他使用人更改文件内容，还可以选择“标记为最终状态”，相当于标记为只读属性。 2007版还增加了一种通过添加不可见数字签名的方式，来确保工作簿的完整性。对文档进行数字签名后，文档就变成只读，
9、以防止修改。,Page 12,第1章 工作簿和工作表,1.3 工作表的操作 1.3.1 基本操作 选中工作表标签(以下简称表标签)，点击鼠标右键可以新建、删除、重命名、隐藏或取消隐藏、移动或复制、保护工作表。 选中表标签后，按下Ctrl键、按住鼠标左键向左或向右拖动，可以快速地复制工作表；不按Ctrl键，快速移动工作表。 选项：可以新建工作簿及实现不同工作簿之间工作表的复制或转移。如勾选“建立副本“为复制，否则为移动。 无论复制还是移动,前提是目标工作簿处于打开状态。 深度隐藏：即工作表界面无法显示和取消隐藏。选中工作表标签，右键-查看代码进入VBA编辑界面，选择需要隐藏的工作表，将其Visi
10、ble属性改为，然后退出即可。,Page 13,第1章 工作簿和工作表,1.3.2 保护工作表 依次选择审阅-更改组-保护工作表或者选中工作表标签右键选择保护工作表，两次输入密码，保存后完成表格加密。 工作表加密后，允许用户执行哪些操作，可以根据需要对加密内容进行勾选，如图1-1：图1-1,Page 14,第1章 工作簿和工作表,1.3.2 保护工作表 勾选“编辑对象”，加密后仍然可以对图形、图表、控件等对象进行选中和编辑，否则只能选中一部分对象。 勾选“编辑方案”，加密后仍然可以对数据-数据工具-假设分析下的方案管理器、单变量求解进行编辑。 特别提示： 如果想在工作表加密后使用自动筛选和数据
11、透视表，必须在加密前标记筛选状态和添加数据透视表，并同时勾选“使用自动筛选”和“使用数据透视表”。即使这样，数据透视表仍然有一部分操作受到限制。,Page 15,第1章 工作簿和工作表,1.3.2 保护工作表 设置允许用户编辑区域，是一个非常实用的功能。因为工作表加密后，经常是部分区域锁定，部分区域需要编辑。 提示：新建工作表的单元格默认处于锁定状态。 如果需要锁定大部分单元格区域，加密前先点击“允许用户编辑区域”按需要添加单元格范围，这部分单元格加密后仍然可以进行编辑。 如果只是锁定小部分区域，可以在加密前全选工作表（按Ctrl+A或点击左上角行列交叉的区域），然后右键-设置单元格格式-保护
12、选项卡，去掉“锁定”前面的勾，这样全表单元格取消锁定状态。再选定需要锁定的单元格区域，勾选“锁定”即可。,Page 16,第1章 工作簿和工作表,1.3.3 打印操作 点击左上角 图标选择打印或Ctrl+P调出打印对话框，根据需要修改打印设置，如打印范围、打印份数、打印内容等。 页面布局中，可以设置固定的打印区域、页边距、页眉页脚、纸张大小等。 设置顶端标题行，可以在打印多页的情况下，保证每一页都有正文标题和表头字段。 插入分页符，则可以根据自己的需要安排每页打印内容，而不必每页都保持一致。,Page 17,第1章 工作簿和工作表,1.3.3 打印操作 为提升美感或添加公司LOGO，还可以制作
13、或插入图片作为文档背景。但背景图片不能直接打印，可以在设置页眉或页脚时插入所需图片，即可以实现此功能。 特别提示： 打印预览空白文档，会提示“Microsoft Office Excel 未发现可以打印的内容。”，但只要表格中有一个空格，就不会出现以上提示。 如果你遇到明明可以打印成一页却出现两页的情况，就应该考虑是不是存在超出打印页面的空格、格式设置或标点符号之类的无效内容。,Page 18,第2章 常用功能和技巧 2.1 快速录入和快捷操作 2.2 开始菜单 2.3 排序 2.4 筛选 2.5 查找和替换 2.6 定位填充 2.7 分列与选择性粘贴 2.8 数据有效性 2.9 其他功能和常
14、见问题,Page 19,第2章 常用功能和技巧,为了便于记忆和实际应用，本章将按菜单栏分布顺序，选择常用的编辑或操作功能进行讲解。 涉及到函数应用和数据汇总分析的，分别在第三章函数和公式、第四章数据透视表图进行讲述，此章暂不涉及。 开始本章之前，先来认识一下“快速访问工具栏”，即菜单栏上方长条形区域。右键点击最后的倒三角箭头，可以通过勾选增加和删除现有快捷键。选择其他命令，还可以自定义更多快捷键。如图2-1：,Page 20,第2章 常用功能和技巧,2.1 快速录入和快捷操作 2.1.1快速录入 自动更新日期与固定日期的输入 选取任意一个单元格，输入函数或快捷键：自动更新当前日期 =TODAY
15、（）自动更新当前日期和时间 =NOW（）固定当前日期，按Ctrl+; 固定当前时间，按Ctrl+Shift+;固定当前日期和时间，按Ctrl+;然后按空格键，再按 Ctrl+Shift+; 自动更新，意味着工作表每一次重新计算，单元格内的日期和时间都会发生变化。,Page 21,第2章 常用功能和技巧,2.1.1快速录入 序列填充 通过序列填充可以快速输入相同或有序的文本和数字，实现方式一般有三种：鼠标拖动填充柄输入序列（鼠标左键或右键拖放）；通过菜单栏“编辑”“填充”“序列”；通过菜单栏“数据”“数据有效性”“设置”选项卡有效性条件选择“序列”； 和侧重于数字、文本型数字和自定义序列的输入,
16、 主要用于固定内容的文本多次选择性输入，稍后在数据有效性章节讲解。,Page 22,第2章 常用功能和技巧,2.1.1快速录入 鼠标拖曳填充 选中单元格，鼠标移到单元格边框右下角，当鼠标变成十字架，按住鼠标左键向下拖曳或右键向下拖曳后根据实际需求进行选择。 还有一种更快速的方法，就是当鼠标变成十字架时，双击鼠标左键，但前提是该填充列左边或右边列有相同行数的数据且中间不能断行。 拖曳方向不同，填充结果也不一样。向下或向右是数字的绝对值递增，向上或向左是绝对值递减。 来看两组动态操作图：序列填充1和序列填充2。,Page 23,第2章 常用功能和技巧,2.1.1快速录入 序列填充1：A列使用鼠标左
17、键拖曳（因为左右列无相同行数的数据），且必须选中两个单元格的数据，否则无法判断等差序列的步长值。 B、C、D列都是双击鼠标左键实现快速填充。操作结果显示： B列按自然数顺序填充，C列数字顺序填充、文本不变，D列按自定义序列顺序填充、达到最大值以后重新返回最小值再次顺序填充。 序列填充2：拖曳鼠标左键同时按住Ctrl键（鼠标变成大小两个十字架），同样的数据，拖曳以后结果变成了复制。,Page 24,第2章 常用功能和技巧,2.1.1快速录入 特殊情况下的数据输入 .输入分数(如1/2),应先输入“0”及一个空格后顺序输入1、/、2，结果显示“1/2” 。 .输入平方米m2，先输入m2，再双击单元
18、格进入编辑状态，抹黑数字2，鼠标右键设置单元格格式特殊效果选择上标，即可达到所需效果m2。 .输入纯数字，如身份证号码和银行卡号，超过15位会变成科学计数法形式且最后几位变成0。如果输入前加英文状态下单引号“” ，Excel 自动将其作为字符型数据处理，可以避免出现错误。 .插入特殊字符，选择插入菜单-特殊符号-符号-更多，包括特殊符号、标点符号、数学序号等。,Page 25,第2章 常用功能和技巧,2.1.2快捷操作 Ctrl+A：选择数据区域任意单元格，按1次快速选中数据区域，但区域中不能有完全的空白行或列。再按1次，全选工作表。 如图2-2：选择A1:B7任意单元格，按Ctrl+A只能选
19、中A1:B7区域；如果C1:C7任意单元格有数据，可以选中A1:D7;同理，A8:B8有一个数据且C1:C7无数据，则选中A1:B11；空白行列交叉处C8如果有数据则可以全选A1:D11。图2-2,Page 26,第2章 常用功能和技巧,2.1.2快捷操作 双击单元格边框：选择数据区域中任意单元格，双击黑色边框下边，鼠标快速移到数据区域最后一行。同理，双击上边框移到顶部，左右边框则分别移到数据区域第一列、最后一列。不管上下左右，鼠标所在行或列都不能有空行或空列，否则只能移到第一个空行或空列之前。 Shift+双击单元格边框：按住Shift键同时双击单元格边框则选中所选单元格所在行或列，其他规则
20、一致，其操作效果等同于Ctrl+Shift+方向键。 还有Ctrl+C复制、Ctrl+V粘贴、Ctrl+X剪切、Ctrl+D向下复制、Ctrl+R向右复制、F5调用定位框、Ctrl+F查找替换、双击单元格快速进入编辑状态、F4重复上一次操作(不包括录入数据)等，请依据自身需求选择学习和使用。,Page 27,第2章 常用功能和技巧,2.1.3操作技巧 快速修改单元格内容的前后左右次序 选中单元格区域，按住Shift键，鼠标停留在右边框或下边框，鼠标变成带箭头的十字架后向目标单元格拖曳，当目标单元格右侧或下方出现“工”形状，松开鼠标和按键，即可完成顺序调换。 选择多个工作表同时编辑或输入 如果想
21、一次操作多张工作表，省略后续的复制、粘贴操作，可采用以下方法。 按住Shift键或Ctrl键并配以鼠标操作，在工作簿底部选择多个彼此相邻或不相邻的工作表标签，就可以对选中的工作表实行多方面的批量处理。,Page 28,第2章 常用功能和技巧,2.1.3操作技巧 一般情况下，多表同时进行的操作主要有以下几种:1页面设置，可快速对选中工作表设置相同的页面； 2在多个工作表中一次性输入相同的数据； 3在多个工作表中进行一系列相同操作，如设置字号、字体、颜色，进行单元格的合并撤销等；4快速输入相同的公式。 不管哪种操作，前提是多张工作表应具有相同的数据位置和格式，否则操作结果会出现错行或错列的情况。,
22、Page 29,第2章 常用功能和技巧,2.2 开始菜单 开始菜单主要是一些字体及格式设置、单元格行列调整、制表的快捷键，鼠标移到旁边后都有相应的说明，不再赘述。重点说一下格式刷、合并单元格、条件格式、制作斜线表头和排序。 格式刷 ：快速将指定单元格的格式复制到目标单元格。 具体操作：选择含有所需格式的单元格区域，单击工具条上的“格式刷”按钮，此时鼠标变成了刷子形状，然后单击要格式化的单元格区域即可将格式拷贝过去。,Page 30,第2章 常用功能和技巧,2.2.1 合并居中 合并单元格：两个或多个相邻的水平或垂直单元格，合并成一个跨多列或多行显示的大单元格。选定区域左上角第一个单元格的数据出
23、现在合并单元格的中心，如第一列单元格无数据则保留第二列；第一行无数据则保留第二行第一列单元格。如图2-3，选定区域A1:B3中，保留单元格A2的内容，其余单元格被覆盖。选中合并单元格，编辑栏地址始终显示选定区域最左上角单元格。 图2-3：合并前： 合并后：,Page 31,第2章 常用功能和技巧,2.2.1 合并居中 选中已合并单元格，再次点击“合并后居中”按钮，即取消单元格合并。 为了表格更美观，经常需要用到这个功能，如图2-4。但是有合并单元格的工作表，可能会影响复制、粘贴、筛选、排序等正常编辑操作，而弹出错误提示。图2-4,Page 32,第2章 常用功能和技巧,2.2.1 合并居中 跨
24、列居中：如果是单行需要合并居中，可使用跨列居中代替，一样的效果且不影响正常编辑。选择所需单元格（只能其中一个单元格有数据，否则可能达不到所预期的效果），鼠标右键-设置单元格格式-对齐选项卡-水平对齐，选择跨列居中。 合并单元格应用技巧 合并单元格使数据或表格变得美观，却对Vlookup、Count、Sumif等查找统计公式应用带来了一些麻烦。 通过组合应用可以解决这个问题，主要思路就是复制表格后，取消合并单元格，利用定位填充功能将F列数据填充完整，再利用格式刷复制A列格式即可。 具体操作步骤如动态图合并+格式刷：,Page 33,第2章 常用功能和技巧,2.2.2 条件格式 条件格式：使用数据
25、条、颜色刻度和图标集来直观地显示数据。目的在于突出显示用户所关注的单元格或单元格区域，强调异常值；条件格式基于条件更改单元格区域的外观。 如图2-5，条件格式设置的条件为C列大于650,000的红色字体粉红色背景显示，更改单元格数据格式会随时更新(如C8改成600,000）：,Page 34,第2章 常用功能和技巧,2.2.2 条件格式 Excel 2007在条件格式这一块增加了很多功能，包括数据条、色阶、图标集等。来看一下应用数据条和图标集的效果，都是根据数值大小显示相应的长度或图标，让数据使用者看起来更直观。如图2-6和2-7：图2-6 图2-7,Page 35,第2章 常用功能和技巧,2
26、.2.2 条件格式 条件格式还可以通过设置公式更改单元格的外观。选中A2:D10,条件格式-管理规则-选择规则类型（使用公式确定要设置格式的单元格）-为符合此公式的值设置格式，在下面的方框中输入公式： =ISEVEN(ROW()+COLUMN()，选择相应的填充颜色，单元格显示如图2-8：图2-8,Page 36,第2章 常用功能和技巧,2.2.3 斜线表头 制作斜线表头：中国人习惯在表头的行列交叉部分，用斜线分隔、分别标示表格横向和纵向数据的类别。而Excel只能在单个单元格（包括合并单元格）使用斜线，所以只能通过其他辅助方式完成。 先说单斜线表头的制作方法：合并单元格-输入年度、部门-鼠标
27、移到“度”字后面，按Alt+Enter强制换行-文本左对齐-在“年度”前面输入空格-添加斜线，就可以了。操作过程如动态图单斜线表头： 另一种方法：在A3、A4单元格分别输入年度和部门，分别文本右对齐、左对齐，再画一条斜线，同样能实现这个效果。 再来看看两条斜线的表头，方法差不多，不过斜线只能通过绘图方式完成。操作过程如动态图双斜线表头：,Page 37,第2章 常用功能和技巧,2.2.4 文本换行 自动换行和强制换行 自动换行：在同一单元格通过多行显示，使单元格中所有内容都可见。 操作方法：选择要设置格式的单元格，单击自动换行按钮 即可，再次单击取消自动换行。 自动换行，单元格文本内容根据列宽
28、自动分割成多行显示以适应列宽。当更改列宽时，数据换行会自动调整。 强制换行，可以在指定的位置插入换行符Alt+Enter，实现特殊情况下的换行需求，比如制作斜线表头。 提示：使用强制换行，必须删除换行符才能取消换行。,Page 38,第2章 常用功能和技巧,2.3 排序 2.3.1 基本原则 对一列或多列数据按文本、数字、日期和时间（升序或降序）进行排序，也可以按自定义序列（如大、中、小）或格式（包括单元格颜色、字体颜色或图标集）进行排序。大多数排序操作都是按列，也可以按行进行。 在按升序排序时，Excel 使用如下排序次序(降序，则反之)。 数字：数字按从最小的负数到最大的正数进行排序。 日
29、期：日期按从最早的日期到最晚的日期进行排序。 文本 :文本及文本型数字按以下次序排序: 0 1 2 3 4 5 6 7 8 9 (空格) ! “ # $ % ? _ | + A B C D E F G H I J K L M N O P Q R S T U V W X Y Z,Page 39,第2章 常用功能和技巧,2.3.1 基本原则 字母、数字组合文本按从左到右的顺序逐字符进行排序。例如，如果一个单元格中含有文本“A100”，Excel 会将其放在含有“A1”单元格的后面、含有“A11”单元格的前面；汉字则按拼音首字母排序，第一个字母相同则按第二个汉字首字母，以此类推。 逻辑：在逻辑值中，
30、FALSE 排在 TRUE 之前。 错误值：所有错误值（如 #NUM! 和 #REF!）的优先级相同。 空白单元格：无论升序还是降序排序，空白单元格总是放在最后。 以上类型数据排序顺序（按升序）：数字日期文本逻辑值错误值。,Page 40,第2章 常用功能和技巧,2.3.2 自定义序列 创建自定义序列： 在单元格区域中，按照需要的顺序从上到下输入要排序的值。例如：董事长、总经理、经理、科长、组长。 选择包含自定义序列的单元格区域。 单击“Office 按钮” ，选择Excel 选项-常用选项卡，在“创建用于排序和填充序列的列表”右边，单击“编辑自定义列表”。 在“自定义序列”对话框中，单击“导
31、入”，然后单击确定两次，完成自定义序列。 按自定义序列排序时，排序次序应选择“自定义序列”。,Page 41,第2章 常用功能和技巧,2.3.3 排序方法 单列排序 在数据区域中选定某一列标题所在单元格，单击快速访问工具栏中的升序或降序按钮即完成排序。 多列排序 单击选项卡，选择自定义排序，调出排序对话框。然后根据多个排序关键字的优先级别，按照从高到低的原则依次添加，并选择相应的排序依据和次序。勾选“数据包含标题”，排序关键字显示行标题，否则显示列标如列A、列B等。 特别提示：对数据进行排序之前，应该先取消隐藏已隐藏的行或列，否则排序结果会出现错误。 示例如图2-9和2-10，看看相同关键字、
32、不同优先级别排序后的效果：,Page 42,第2章 常用功能和技巧,2.3.3 排序方法 按班级（升序） 总分（降序） 语文（降序）排序后的效果如图2-9。通过对比可以看出，首先按班级果果1班（首字母G在H之前）排在前面，果果1班中总分最高的排在前面，总分相同、语文成绩最高的排在前面。 原表： 排序后：,Page 43,图2-9,第2章 常用功能和技巧,2.3.3 排序方法 按总分（降序） 班级（升序） 语文（降序）排序后的效果如图2-10（第一张表）。对比一下两张表不同排序方式的差别，更有助于理解排序功能。 排序后： 图2-9排序后：,Page 44,图2-10,第2章 常用功能和技巧,2.
33、3.4 排序技巧应用 利用特别的排序方式隔行插入空行 这个技巧比较实用，主要是利用含有数字的辅助列排序后、由Excel自动插入空行。示例如动态图隔行插入空行： 隔行插入两个空行，也是一样的思路，在1.19.1序列后再加一组1.2、2.2、3.2 9.2即可，也可以是1.11、2.11、3.11 9.11。插入三行以及更多行，则以此类推。 隔两行或两行以上插入一个空行或多行，该怎么实现？ 插入辅助列的数字有什么规律，由各位自己来总结吧。,Page 45,第2章 常用功能和技巧,2.4 筛选 定义：根据指定条件，显示那些满足条件的行，隐藏不符合条件的行。筛选过的数据子集，不需要重新排列或移动就可以
34、复制、查找、编辑、设置格式、制作图表和打印。 筛选包括自动筛选和高级筛选。 自动筛选可以按多个列（即多次指定筛选条件）进行筛选，每追加一次筛选都是基于前一次的筛选结果。 使用自动筛选可以创建三种筛选类型：按条件、格式或列表值。对于每个单元格区域或列表来说，这三种筛选类型是互斥的，即不能同时应用两种以上类型。,Page 46,第2章 常用功能和技巧,2.4 筛选 特别提示：为了获得最佳效果，请不要在同一列中使用混合的存储格式（如文本和数字，或数字和日期），因为每一列只有一种类型的筛选命令可用。 如果一列单元格数据使用了混合的存储格式，则显示的命令对应于出现次数最多的存储格式。例如，该列包含3个数
35、字和4个文本单元格，点击筛选按钮 显示的筛选命令是“文本筛选”；反之，包含4个数字和3个文本则显示“数字筛选”。 Excel 2007的列表值筛选从界面和可操作性都有了很大改善。之前版本的列表值筛选不能多选，而且列表值没有正确的排序，导致用户筛选时出现错误判断。,Page 47,第2章 常用功能和技巧,2.4 筛选 2.4.1 自动筛选 通常是在一个数据区域中的一列或几列中查找相同的值，筛选范围和筛选结果都在同一个数据区域。 操作方法： 选择数据区域中的任意一个单元格，单击“开始”选项卡，编辑组-排序和筛选-筛选（或单击“数据”选项卡-筛选），数据区域首行标记筛选按钮 （选择两个以上单元格，则
36、在选中区域从首列开始，第一行标记筛选按钮），重复操作一次取消筛选。 提示：筛选按钮所在行的数据不包含在筛选数据范围内。,Page 48,第2章 常用功能和技巧,2.4.1 自动筛选 文本、数字和日期筛选，筛选命令明细项各不相同，但操作方法基本一致。首先可以在左边的筛选列表中直接勾选或取消勾选数据；如勾选不能满足需求，可以通过右边的命令细项设定筛选条件。 按多个条件筛选，则选择“自定义筛选”。多条件筛选要注意“与”和“或”的区别。两个条件都必须满足则选择“与”，否则选择“或”。 示例如动态图自动筛选，筛选花花4班数学成绩大于95或小于90的学生。,Page 49,第2章 常用功能和技巧,2.4.
37、1 自动筛选 快速筛选：选中目标单元格，点击鼠标右键-筛选，分别可以“按所选单元格的值筛选”、“按所选单元格的颜色筛选”、“按所选单元格的字体颜色筛选”和“按所选单元格的图标筛选”快速筛选出该列相同值的单元格区域。 通配符的使用 当筛选值不确定或模糊筛选时，就需要使用通配符，用问号?和星号*分别表示一个或多个字符，具体如图2-11。,Page 50,第2章 常用功能和技巧,2.4.2 高级筛选 若要通过复杂的条件来筛选单元格区域，须使用“数据”选项卡上 “高级”筛选命令。 在要筛选的单元格区域上方插入至少三个空白行，作为筛选条件区域。条件区域必须具有列标签，并确保与数据区域之间至少留有一个空白
38、行。 高级筛选的条件比较复杂，但有一个基本原则：同一行是”与”并列关系，筛选结果必须同时满足；同一列是”或”选择关系，筛选结果满足其中任意一个条件即可。 通过示例来理解筛选条件的应用更直观。如图2-12：,Page 51,第2章 常用功能和技巧,2.4.2 高级筛选 高级筛选条件示例图：,Page 52,图2-12,第2章 常用功能和技巧,2.4.2 高级筛选 利用高级筛选删除重复值或筛选唯一值 这是一个很有用的功能，工作中也经常用得到。现实中有人会利用自动筛选分批删除每一项数据的重复行，有人将数据排序后逐一删除重复行。数据少还好，几千上万行的怎么办？ 筛选唯一值和删除重复值是两个紧密相关的任
39、务，这是因为二者所显示的结果相同：一个唯一值的列表。但差别也很明显：筛选唯一值是临时隐藏重复的值，删除重复值则会永久删除重复值且无法恢复。 操作方法还是用示例来说明，筛选唯一值（分两种方式）如动态图高级筛选；删除重复值（弹出对话框后，可以勾选判断重复值的数据列）如动态图删除重复值：,Page 53,第2章 常用功能和技巧,2.5 查找和替换 按Ctrl+F,调出“查找和替换”对话框，可以搜索指定字符串；如果需要，也可以用其他字符串替换该字符串。 查找内容可以是文本、数字和错误值，也可以选择特定单元格格式，如背景颜色、字体颜色等。单元格格式可以设定，也可以从目标单元格区域提取。 点击“查找和替换
40、”对话框右下角的“选项”，有6个查找参数可以选择和勾选。 范围：选择“工作表”可将搜索范围限制为活动工作表。选择“工作簿”可搜索活动工作簿中的所有工作表。 搜索：选择“按列”按列向下搜索，选择“按行”按行向右搜索。,Page 54,第2章 常用功能和技巧,2.5 查找和替换 查找范围： 指定是要搜索目标单元格的值还是其所隐含的公式或批注内容。例如，某单元格数据显示为“11”，但实际上包含公式=10+1。查找范围选择“值”，搜索“11”时，指向该单元格；查找范围为“公式”，则显示“找不到正在搜索的数据”。在“替换”选项卡上，查找范围中“公式”是唯一的选项。 区分大小写：区分大小写字符。 单元格匹
41、配 ： 搜索与“查找内容”框中指定的内容完全匹配的字符。 查找全部：查找文档中符合搜索条件的所有内容，并在下方的显示框全部列示。,Page 55,第2章 常用功能和技巧,2.5 查找和替换 “替换”选项卡中的选项大都与“查找”选项卡相同，只不过还具有一些可用于替换搜索数据的附加选项。 替换为：输入要用来替换“查找内容”的替换字符。若要从文档中删除“查找内容”框中的字符，请将“替换为”框保留为空即可。 全部替换：替换文档中符合搜索条件的所有内容。如果希望逐项查看并有选择地替换各项，请单击“替换”，而不是单击“全部替换”。 通配符的使用与自动筛选的使用规则基本一致。参考2.4.1自动筛选通配符的使
42、用。,Page 56,第2章 常用功能和技巧,2.5 查找和替换 特别提示： 在数据区域选中任意一个单元格，查找和替换的范围默认为整张工作表。选择两个（含）以上单元格，则查找替换范围限制在选中单元格区域。 如果不注意这个问题，就可能出现这样的困扰：表格中明明看到了搜索值，Excel却提示“找不到正在搜索的数据”。 其实这是Excel一个基本的原则：选择任意一个单元格，Excel根据相应的操作自动选择全部数据区域；选择两个或两个以上单元格，默认选择选中区域。 查找和替换的操作示例参考下一节：定位填充。,Page 57,第2章 常用功能和技巧,2.6 定位填充 定位：根据指定的位置、命名区域或定位
43、条件，快速选中目标单元格区域。 工作表界面，按F5弹出定位对话框。点击“定位条件”，对话框会显示多项定位条件，如批注、常量、公式、空值等等。根据字面意思即可理解，此处重点讲解一下空值和可见单元格的定位条件应用。 空值：快速选中当前工作表数据区域中的空白单元格。 可见单元格：快速选中当前工作表非隐藏状态的单元格区域。例如，当目标单元格区域存在隐藏行或列，复制后粘贴，隐藏的行或列也一并粘贴。如果使用定位条件-可见单元格选中后复制，则隐藏行不会被复制粘贴。,Page 58,第2章 常用功能和技巧,2.6 定位填充 定位技巧应用-定位填充 操作方法：选中目标单元格区域，按F5-定位条件-空白值，选中空
44、白单元格以后，按等号(“=”），第一个空白单元格等于上一单元格的值，同时按Ctrl+Enter，然后全选表格复制、粘贴值。 示例说明：会计一般会用到带核算项目（部门）的科目余额表，用于统计分析期间费用或制造费用。本示例是一张从金蝶财务软件引出的制造费用科目余额表（带明细核算项目），需要会计按部门统计各明细科目的金额。 本示例主要是组合使用查找替换、定位填充及自动筛选功能，操作如动态图定位填充：,Page 59,第2章 常用功能和技巧,2.7 分列与选择性粘贴 2.7.1 分列 根据数据内容，可以基于分隔符(如空格或逗号)或数据中的特定分栏符位置将单元格内容拆分到不同的列中。 分隔符包括空格、逗
45、号、分号和其他任何能作为区分标识的符号或字符，也可以是固定宽度的分栏符。 分列操作向导总共三个步骤，第三个步骤可以分别对拆分后的列设置数据格式，如常规、文本和日期。选中“不导入此列(跳过)”则该列被忽略，不在拆分后的数据结果中显示。 默认状态下，分列操作结果覆盖原有数据。如果需要保留被拆分列，可以在“目标区域”的右边框重新指定单元格地址。 操作方法如动态图分列：,Page 60,第2章 常用功能和技巧,2.7.1 分列 分列技巧应用-转换数据格式 文本型数字转换数字方法比较多，分列、选择性粘贴以及鼠标右键直接“转换为数字”。 文本型日期与日期互转、数字转换成文本型数字基本上只能使用分列。 无论
46、哪种转换，操作方法基本一致。分列向导第1步选择“固定宽度”，第2步无须建立分列线、直接点击“下一步”，第3步选择需要转换的格式，然后点击完成。,Page 61,第2章 常用功能和技巧,2.7.2 选择性粘贴 大多数人，习惯使用Ctrl+C和Ctrl+V完成复制、粘贴，使用菜单栏粘贴选项完成粘贴值。 Ctrl+V同时复制源数据的公式、格式、批注、边框等；粘贴值将源数据统一粘贴为数值（常量）且不带格式、边框及批注。 使用“选择性粘贴”对话框，可复制工作表中的复杂选项，可以根据需求进行相应选择；同时还可以将复制的源数据与目标区域的数据进行运算。 开始菜单-粘贴-选择性粘贴，弹出“选择性粘贴”对话框，
47、简单介绍一下个别选项的作用，仅从字面就可以理解的不再赘述。,Page 62,第2章 常用功能和技巧,2.7.2 选择性粘贴 有效性验证 ：将所复制单元格的数据有效性验证规则粘贴到粘贴区域。 边框除外：粘贴所复制单元格的所有单元格内容和格式，边框除外。 加：指定要将所复制数据与目标单元格或单元格区域中的数据相加。减、乘同加运算。 除：指定要用所复制的数据（除数）除目标单元格或单元格区域（被除数）中的数据。 跳过空单元：选中此复选框，则当源数据中有空单元格时，可避免替换目标区域中相对应位置的值。,Page 63,第2章 常用功能和技巧,2.7.2 选择性粘贴 转置：选中此复选框，所复制数据的行列可
48、实现位置转换。 粘贴链接：粘贴后的数据链接到源数据（相当于复制了源数据的位置），因此当源数据发生改变，粘贴后的数据相应发生改变。 用示例来演示一下如何完成复制后的加运算（减、除、乘运算同理）和行列转置，如动态图选择性粘贴。,Page 64,第2章 常用功能和技巧,2.8 数据有效性 2.8.1 基本功能 作用：根据预先设定的规则，验证用户输入数据的有效性，当数据不符合预设规则时弹出警告提示，指导用户及时清除无效数据、更正输入内容。 数据有效性的应用，可以归为两大类：限制输入非法值、制作可供选择输入的下拉列表。 操作方法：选中需应用数据有效性的单元格区域，选择数据菜单-数据有效性，在”数据有效性
49、”对话框设置选项卡输入有效性条件。 限制数据输入规则包括数字大小、常量序列、日期时间区间、文本长度以及公式计算后的值。,Page 65,第2章 常用功能和技巧,2.8.1 基本功能 设置提醒框 选中I列，选择”数据有效性”菜单，单击”输入信息”选项卡,勾选”选定单元格时显示输入信息”，输入提示信息的标题和文本。如图2-13：,Page 66,第2章 常用功能和技巧,2.8.1 基本功能 设置出错警示框 选中I列，选择”数据有效性”菜单，单击”出错警告”选项卡,勾选”输入无效数据时显示出错警告”，选择警告样式、输入警告信息的标题和文本。如图2-14：,Page 67,第2章 常用功能和技巧,2.8.2 技巧应用 限制同一列中不能输入重复的内容 假设A列限制输入重复的姓名，选中A列，在对话框选择自定义，公式框输入”=COUNTIF(A:A,A2)=1”，勾选”输入无效数据时显示出错警告”。设置成功后，在A列输入重复的姓名，Excel会弹出警告信息，并根据选择的警告样式直接取消输入内容、询问是否继续或仅提示输入非法值。 制作可供选择输入的下拉列表 以制作一份省市级联菜单为例，输入省份后根据省份名称自动显示城市列表。制作方法如动态图2-13级联菜单。,
