会计常用Excel技巧.ppt

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