会计常用Excel技巧.ppt

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