excel操作技巧分享.ppt

《excel操作技巧分享.ppt》由会员分享，可在线阅读，更多相关《excel操作技巧分享.ppt（91页珍藏版）》请在三一文库上搜索。

1、EXCEL2007操作技巧分享,1,内容提纲,学习Excel的方法 Excel 2007界面认识 常用技巧介绍 图表功能介绍 数据透视表介绍 常用函数介绍,2,第一部分 学习Excel的方法,1、循序渐进,3,达到中级水平的3个标志： 1、理解并熟练使用各个Excel菜单命领 2、熟练使用数据透视表 3、至少掌握20个常用函数以及函数的嵌套运用，必须掌握的函数有SUM函数、IF函数、VLOOKUP函数、MATCH函数、OFFSET函数等。,4,2、善用资源，学以致用,遇到问题的时候，如果知道应该使用什么功能，但是对这个功能不太会用，此时最好的办法是单击F1键调出Excel的联机帮助，集中精力学

2、习这个需要掌握的功能。 如果遇到的问题不知从何下手，甚至不能确定Excel能否提供解决方法，可以求助于他人，如果没这样的受助机会，可以上网搜索解决方法，或者到某些Excel网站上去寻求帮助，如“Excel Home最佳学习方法”（网址：/ 当利用各种资源资源解决了自己的问题卓越，一定很有成就感，此时千万不要停止探索的脚步，争取把解决方法理解得更透彻，能做到举一反三。 暂时不用着的函数不必深究，但一定要了解，而不是简单的忽略。说不定哪天就需要用到的某个功能，Excel里面明明有，可是自己却不知道，以至于影响到寻找答案的速度。,5,3、多阅读多实践,学习Excel，阅读与实践必须并重。阅读来的东西

3、，只有亲自在电脑上实践几次，才能把别人的知识真正转化为自己的知识。通过实践，还能够举一反三，即围绕一个知识点，做各种假设来测试，以验证自己的理解是否正解和完整。,6,第二部分 Excel 2007界面认识,和以前的版本相比，Excel 2007的工作界面颜色更加柔和，更贴近于Windows Vista操作系统。Excel 2007的工作界面主要由“文件”菜单、标题栏、快速访问工具栏、功能区、编辑栏、工作表格区、滚动条和状态栏等元素组成。,7,一、“文件”菜单 单击Excel工作界面左上角的Office按钮 ，可以打开“文件”菜单。在该菜单中，用户可以利用其中的命令新建、打开、保存、打印、共享以

4、及发布工作簿。,8,二、快速访问工具栏 Excel 2007的快速访问工具栏中包含最常用操作的快捷按钮，方便用户使用。单击快速访问工具栏中的按钮，可以执行相应的功能 。,9,三、标题栏 标题栏位于窗口的最上方，用于显示当前正在运行的程序名及文件名等信息。如果是刚打开的新工作簿文件，用户所看到的文件名是Book1，这是Excel 2007默认建立的文件名。单击标题栏右端的按钮， 可以最小化、最大化或关闭窗口。,10,四、功能区 功能区是在Excel 2007工作界面中添加的新元素，它将旧版本Excel中的菜单栏与工具栏结和在一起，以选项卡的形式列出Excel 2007中的操作命令。 1、Exce

5、l 2007的功能区中的选项卡包括： “开始”选项卡、“插入”选项卡、“页面布局”选项卡、“公式”选项卡、“数据”选项卡、“审阅”选项卡、“视图”选项卡以及“加载项”选项卡。,2、隐藏和显示功能区 有时为了最大化使用工作表视图区需要将功能区隐藏，有以下三种等效的方法。 （1）双击任意一个选项卡即可隐藏功能区。当需要使用功能区时，只需单击任意选项卡，功能区就又出现了，完成当前操作后功能区又自动隐藏起来。 （2）单击【快速访问工具栏】右侧的下拉按钮，在弹出中的扩展菜单中单击【功能区最小化】命令。 （3）使用快捷键来显示或隐藏功能区。,11,四、功能区 功能区是在Excel 2007工作界面中添加的

6、新元素，它将旧版本Excel中的菜单栏与工具栏结和在一起，以选项卡的形式列出Excel 2007中的操作命令。 1、Excel 2007的功能区中的选项卡包括： “开始”选项卡、“插入”选项卡、“页面布局”选项卡、“公式”选项卡、“数据”选项卡、“审阅”选项卡、“视图”选项卡以及“加载项”选项卡。,2、隐藏和显示功能区 有时为了最大化使用工作表视图区需要将功能区隐藏，有以下三种等效的方法。 （1）双击任意一个选项卡即可隐藏功能区。当需要使用功能区时，只需单击任意选项卡，功能区就又出现了，完成当前操作后功能区又自动隐藏起来。 （2）单击【快速访问工具栏】右侧的下拉按钮，在弹出中的扩展菜单中单击【

7、功能区最小化】命令。 （3）使用快捷键来显示或隐藏功能区。,12,五、状态栏与显示模式 状态栏位于窗口底部，用来显示当前工作区的状态。 Excel 2007支持3种显示模式，分别为“普通”模式、“页面布局”模式与“分页预览”模式，单击Excel 2007窗口左下角的 按钮可以切换显示模式。,13,第三部分 常用技巧介绍,一、保护选项 1、保护工作表 如果不希望一些数据被随意复制，或公式被随意修改，甚至删除，可以先解锁允许被选定或修改的单元格，再保护工作表。具体操作步骤如下： Step 1：选定允许被操作的单元格，然后在【开始】选项卡中单击【格式】按钮，在弹出的列表中单击【锁定单元格】（该命令用

8、于切换单元格“锁定”状态），解锁这些单元格。,1,2,3,4,14,一、保护选项 1、保护工作表 Step 2：再次在【开始】选项卡中单击【格式】按钮，在弹出的列表中单击【保护工作表】按钮，打开【保护工作表】对话框，设置相应的保护选项，并且可以指定密码。例如，如果取消勾选【选定锁定单元格】，被锁定单元格则不能被选定，那它们也就不可能被复制了。,1,3,2,4,15,一、保护选项 下面介绍两种常用的工作簿保护方式。 1、保护工作簿结构及窗口 在【审阅】选项卡中依次单击【保护工作簿】【保护结构和窗口】，打开【保护结构和窗口】对话框，勾选【结构】复选框。这样工作簿就不能添加、移动或删除工作表了，隐藏

9、和重命名等操作也被禁止。如果同时勾选【窗口】复选框，则工作簿所在窗口就无法移动或重新调整大小。,1,2,3,4,5,16,一、保护选项 2、加密以增强工作簿安全性 如果把工作簿设置为加密文档，那么在打开工作簿时会要求其给出密码，这也将有助于增加文件的安全性。设置密码的方法为：依次单击【Office按钮】【准备】 【加密文档】，打开【加密文档】对话框。指定打开工作簿时的密码，单击【确定】按钮，在【确认密码】对话框中再次输入密码，单击【确定】按钮，完成加密设置。,2,1,3,4,5,6,7,17,二、彻底隐藏工作表 隐藏工作表的方法很简单，在工作表标签上右键单击，然后在弹出的快捷菜单中单击【隐藏】

10、命令即可将当前工作表隐藏。同样的，取消隐藏也很简单，在任意一个工作表标签上右键单击，在弹出的快捷菜单中单击【取消隐藏】命令，即可弹出【取消隐藏】对话框，双击需要取消隐藏的工作表名称即可。,1,2,1,2,18,三、标题行始终可见 有3种有效方式 解决这一问题。 方法1 利用表 将光标定位在数据表中任意一单元格，在【插入】选项卡中单击【表】按钮，在弹出的【创建表】对话框中单击【确定】按钮完成表的创建。现在，向下滚动表时，Excel会在工作表的列标题相应位置显示表的列标题。,1,2,3,4,19,三、标题行始终可见 方法2 利用冻结窗格 如果数据表区域不想被转换为表格式，用户可以使用冻结窗格功能在

11、当滚动工作表时使某些行或列始终可见。 1、标题行（第一行）始终可见：在【视图】选项卡中依次单击【冻结窗格】【冻结首行】，如下图：,1,2,3,20,三、标题行始终可见 方法2 利用冻结窗格 2、标题列（第一列）始终可见：在【视图】选项卡中依次单击【冻结窗格】【冻结首列】，如下图：,1,2,3,21,三、标题行始终可见 方法2 利用冻结窗格 3、多行或多列始终可见：如使下图所示的工作表中标题行和前两列始终可见，操作步骤为：将光标定位在C2单元格，然后在【视图】选项卡中依次单击【冻结拆分窗格】,2,3,1,4,要取消工作表的冻结窗格状态，可以在Excel功能区上再次单击【视图】选项卡中的【冻结窗格

12、】下拉菜单，在其扩展菜单中选择【取消冻结窗格】命令，窗口状态即恢复到冻结前的状态。,22,三、标题行始终可见 方法3 利用拆分窗口 与【冻结窗口】功能非常相似的是【拆分】窗口功能。将光标定位在某一单元格，依次单击【视图】【窗口】组的【拆分】命令，工作表窗口即被两个拆分柱以该单元格为原点拆分为4个区域。用户可以在不同区域中查看工作表的不同部分，如下图：,使用【拆分】命令同样可以做到标题行（或列）始终可见。但与【冻结窗格】不同的是，用鼠标可以拖动拆分柱以调整不同区域的大小。把拆分柱拖动屏幕的边缘，拆分柱会消失，拆分窗口也随之减少。再次单击【拆分】命令将会取消当前的拆分效果。 在使用了【拆分】功能的

13、工作表中，如果单击【冻结拆分窗格】命令，拆分柱会转换成相应的冻结线。而在使用了【冻结拆分窗格】功能的工作表中，单击【拆分】命令，则相当于取消冻结命令。,23,四、多窗口协同作业 Excel提供了多种视图模式供用户查看和处理数据，其中最为实用的功能是可以在同一屏幕下利用多个窗口协同作业。 1、同时查看工作簿的不同部分 通过【新建窗口】命令，用户可以为同一个工作簿创建多个窗口。用户可以根据需要，在不同的窗口中选择不同的工作表作为当前工作表，或者是将窗口显示定位到同一工作表中的不同位置，以满足各种浏览和编辑需求。当然，对不同窗口所做的编辑修改会同时返回该工作簿本身，并且反映在所有窗口上。具体步骤如下

14、： Step 1：在【视图】选项卡中单击【新建窗口】按钮，Excel会为当前工作簿创建一个窗口，在Excel的标题栏上会在原工作簿名称后面显示 “:1”和“:2”等表示不同的窗口，如下图,1,2,24,四、多窗口协同作业 Excel提供了多种视图模式供用户查看和处理数据，其中最为实用的功能是可以在同一屏幕下利用多个窗口协同作业。 1、同时查看工作簿的不同部分 Step 2：单击【全部重排】按钮，在打开的【重排窗口】对话框中任选一种排列方式，如【水平并排】，然后单击【确定】按钮，如下图：,1,2,25,四、多窗口协同作业 2、同时查看不同工作簿 要在同一屏幕下查看不同的工作簿，方法是一样的。打开

15、所有要对比查看的工作簿，然后在当前工作簿窗口依次单击【视图】【窗口】组的【全部重排】，在打开的【重排窗口】对话框中任选一种排列方式，如【水平并排】，然后单击【确定】按钮。这样可以在多个工作簿间对比查看。 在同时打开两个以上工作簿时单击【并排查看】命令，会弹出【并排比较】对话框。选中目标工作簿，然后单击【确定】按钮，即可将两个工作簿窗口并排显示在Excel工作窗口中。而只有两个工作簿打开时，则直接显示【并排比较】后的状态。 注意：【并排查看】只能作用于两个工作簿窗口，而无法作用于多个工作簿窗口。参与并排比较的工作簿窗口，可以是同一个工作簿的不同窗口，也可以是完全不同的两个工作簿。 另外，还可通过

16、【切换窗口】、【同步滚动】以及【重设窗口位置】等命令对同一工作簿的不同窗口，或者多个工作簿之间进行多角度的排列组合，满足用户的各种浏览和编辑处理需要。,26,五、数值自动填充 如果要在工作表中输入一列数字，如在A列中输入数字1到10，最简单的方法就是自动填充。有两种方法可以轻松实现。 方法1： Step 1 在单元格A1、A2中分别输入1、2 Step 2 选中单元格A1和A2 Step 3 把光标移动到单元格A2的右下角（也就是填充柄的位置），这时光标会变成一个小黑色实心十字。 Step 4 按住鼠标左键，然后向下拖曳，这时右下方会显示一个数字，代表鼠标指针当前位置产生的数值，当显示为10时

17、松开鼠标左键即可。 方法2： Step 1 在单元格A1输入1 Step 2 选中单元格A1并指向其右下角的填充柄，按住键的同时向下拖曳鼠标至单元格A10，先松开鼠标，然后松开键，完成数据的填充。如果不同时按住键，直接 拖曳则为复制填充模式。该方式同样可用在日期及文本的自动填充。,27,六、选择性粘贴 在Excel2007工作表中，用户可以使用“选择性粘贴”命令有选择地粘贴剪贴板中的数值、格式、公式、批注等内容，使复制和粘贴操作更灵活。在Excel2007工作表中使用“选择性粘贴”命令的步骤如下所述： Step 1：选中需要复制的单元格区域。右键单击被选中的区域，在打开的快捷菜单中选择“复制”

18、命令。 Step 2：如果目标粘贴位置为一个单元格，则右键单击该单元格；如果目标粘贴位置为一个单元格区域，则右键单击该区域左上角的单元格，然后在打开的快捷菜单中选择“选择性粘贴”命令，打开“选择性粘贴”对话框，在“粘贴”区域选中需要粘贴选项的单选框（例如“格式”单选框），并单击“确定”按钮即可，如下图所示。,28,七、快速查找重复 我们可能经常需要在Excel表格中找出重复数据并将他们删除，如果数据庞大，那么我们根本不好找出重复数据的。这里教大家一个好方法，可以帮你快突显并找到重复数据。 Step 1：先要选中你要找出重复数据的范围。比如说，你要在“姓名”里找重复项，就把“姓名”一栏全选中 S

19、tep 2：然后依次单击【开始】 【 条件格式】 【突出显示单元格规则】 【 重复值】 Step 3：在重复值对话框上，选择你要把重复项突出显示的格式，再确定。红色文本,1,2,3,4,5,29,八、用连字符“&”来合并文本 如果我们想将多列的内容合并到一列中，不需要利用函数，一个小小的连字符“&”就能将它搞定（此处假定将B、C、D列合并到一列中）。 Step 1：在D列后面插入两个空列（E、F列），然后在D1单元格中输入公式：=B1&C1&D1。 Step 2：再次选中D1单元格，用“填充柄”将上述公式复制到D列下面的单元格中，B、C、D列的内容即被合并到E列对应的单元格中。 Step 3：

20、选中E列，执行“复制”操作，然后选中F列，执行“编辑选择性粘贴”命令，打开“选择性粘贴”对话框，选中其中的“数值”选项，按下“确定”按钮，E列的内容（不是公式）即被复制到F列中。 Step 4：将B、C、D、E列删除，完成合并工作。 提示：完成第1、2步的操作，合并效果已经实现，但此时如果删除B、C、D列，公式会出现错误。故须进行第3步操作，将公式转换为不变的“值”。 如需在合并的单元格之前增加空格或字符，方式为：如下图C1输入公式：=A1&”:”&B1,30,九、在多个单元格中输入相同的内容和公式 很多时候我们需要在Excel中多个单元格里面输入相同的数值，而且这些单元格可能是多个不相邻的位

21、置，若一个一个手动输入那简直是太浪费时间了，有木有什么更好的方法可以一次性在这些单元格中输入相同的内容呢？本章就为大家详细讲解在Excel中如何在多个单元格中输入相同的内容和公式技巧！ 实例如下： 首先，选中需要输入相同内容的单元格。按“Ctrl + 鼠标左键”点选多个单元格；中多个单元格 然后在最后一个选中的单元格中输入内容，如这里我输入“Word联盟”，输入完毕后紧接着按Excel快捷键“Ctrl + 回车”即可。,31,十、设置自动保存 对于一个办公族来说，经常避免不了在制作表格的时候办公室突然停电，或者电脑突然死机，往往遇到这样的事情以后我们第一反应就是：完了，完了，一下午的劳动成果就

22、这样泡汤了。时刻记得保存表格是一种好习惯，但在我们一心一意投入到工作中的时候谁还会记得保存呢? 没关系，幸好微软考虑周全，在Excel2007中有个功能，可以设置“保存自动恢复信息时间间隔”，就是说在使用Excel2007的时候没有保存表格突然遇到突发事件，此项功能可以给予我们非常大的帮助，通过你设置的“保存自动恢复信息时间间隔”时间，可以恢复你原先的表格。 说明：此项功能只能恢复Excel在异常情况下没保存关闭程序。如果你是正常操作关闭程序的时候按的是“否”，那么表格将无法恢复。 操作方法如下： 单击“Office按钮”，在弹出的“Excel选项”中选择左边的“保存”，在右边的“保存自动恢复

23、信息时间间隔”框中输入间隔时间，建议时间越短越好。 Excel2007设置自动保存 如果以后真的遇到一些突发事件，导致表格没有保存，大家可以打开“自动恢复文件位置”后面路径中的位置，找到你之前的表格。,32,第四部分 常用图表制作,图表可以快速表达您的观点。您可以用图表转换工作表数据，来展示比较、模式和趋势。因此，您不必再分析多列工作表数据，数据的含义即可一目了然。下面将介绍三款常用的图表制作。 一、柱状图：柱状图，也称条图、长条图、条状图，是一种以长方形的长度为变量的表达图形的统计报告图，由一系列高度不等的纵向条纹表示数据分布的情况，用来比较两个或以上的价值（不同时间或者不同条件），只有一个

24、变量，通常利用于较小的数据集分析。柱状图图亦可横向排列，或用多维方式表达。,创建图表后，您可以轻松地向此图表添加新元素，如图表标题或新布局（点击【设计】【图表布局】）。,33,二、饼图：仅排列在工作表的一列或一行中的数据可以绘制到饼图中。饼图显示一个数据系列中各项的大小与各项总和的比例。饼图中的数据显示为整个饼图的百分比。使用要求： 1、仅有一个要绘制的数据系列 2、要绘制的数值没有负值 3、要绘制的数值几乎没有零值 4、类别数目无限制 5、类别分别代表整个饼图的一部分 6、各个部分需要标注百分比,34,三、折线图：排列在工作表的列或行中的数据可以绘制到折线图中。折线图可以显示随时间（根据常用

25、比例设置）而变化的连续数据，因此非常适用于显示在相等时间间隔下数据的趋势。在折线图中，类别数据沿水平轴均匀分布，所有值数据沿垂直轴均匀分布。 如果分类标签是文本并且代表均匀分布的数值（如月、季度或财政年度），则应该使用折线图。当有多个系列时，尤其适合使用折线图 对于一个系列，应该考虑使用类别图。如果有几个均匀分布的数值标签（尤其是年），也应该使用折线图。如果拥有的数值标签多于十个，请改用散点图。,35,一、什么是数据透视表： 数据透视表是一种可以快速汇总、分析大量数据表格的交互式工具。使用数据透视表可以按照数据表格的不同字段从多个角度进行透视，并建立交叉表格，用以查看数据表格不同层面的汇总信息

26、使用数据透视表可以深入分析数值数据，以帮助用户发现关键数据，并做出有关企业中关键数据的决策。数据透视表是针对以下用途特别设计的： 以直观的方式，查看大量的数据表格。 对数值数据快速分类汇总，按分类和子分类查看数据信息。 展开或折叠所关注的数据，快速查看摘要数据的明细信息。 建立交叉表格（将行移动到列或将列移动到行），以查看源数据的不同汇总。 快速的计算数值数据的汇总信息、差异、个体占总体的百分比信息等。 若要创建数据透视表，要求数据源必须是比较规则的数据，也只有比较大量的数据才能体现数据透视表的优势。如：表格的第一行是字段名称，字段名称不能为空；数据记录中最好不要有空白单元格或各并单元格；每个

27、字段中数据的数据类型必须一致（如，“订单日期”字段的值即有日期型数据又有文本型数据，则无法按照“订单日期”字段进行组合）。数据越规则，数据透视表使用起来越方便。,第五部分 数据透视表介绍,36,如上图中的表格属于交叉表，不太适合依据此表创建数据透视表（不是不能使用数据透视表， 只是使用上表创建数据透视表某些功能无法体现）。因为其月份被分为 12 个字段，互相比较起来比较麻烦。最好将其改为如下结构：,37,上表只使用一个“月份”字段，而 12 个月作为月份字段的值，这样互相比较起来比较容易。 使用此结构的表格，通过数据透视表，很容易创建上图所示的交叉表格，但反之则很麻烦。 因此，创建数据透视表之

28、前，要注意表格的结构问题。越简单越好，就类似数据库的存储方 式。或者，能纵向排列的表格就不要横向排列。,38,创建数据透视表,1、将光标点在表格数据源中任意有内容的单元格，或者将整个数据区域选中。 2、选择“插入”选项卡，单击“数据透视表”命令。,3、在弹出的“创建数据透视表”对话框中，“请选择要分析的数据”一项已经自动选中了光标所处位置的整个连续数据区域，也可以在此对话框中重新选择想要分析的数据区域（还可以使用外部数据源，请参阅后面内容）。“选择放置数据透视表位置”项，可以在新的工作表中创建数据透视表，也可以将数据透视表放置在当前的某个工作表中。,39,4、单击确定。Excel 自动创建了一

29、个空的数据透视表。,上图中左边为数据透视表的报表生成区域，会随着选择的字段不同而自动更新；右侧为数据透视表字段列表。创建数据透视表后，可以使用数据透视表字段列表来添加字段。如果要更改数据透视表，可以使用该字段列表来重新排列和删除字段。默认情况下，数据透视表字段列表显示两部分：上方的字段部分用于添加和删除字段，下方的布局部分用于重新排列和重新定位字段。可以将数据透视表字段列表停靠在窗口的任意一侧，然后沿水平方向调整其大小；也可以取消停靠数据透视表字段列表，此时既可以沿垂直方向也可以沿水平方向调整其大小。 右下方为数据透视表的 4 个区域，其中“报表筛选”、“列标签”、“行标签”区域用于放置分类字

30、段，“数值”区域放置数据汇总字段。当将字段拖动到数据透视表区域中时，左侧会自动生成数据透视表报表。,40,数据透视表字段的使用,将字段拖动到“行标签”区域，则此字段中的每类项目会成为一行；我们可以将希望按行显示的字段拖动到此区域。 将字段拖动到“列字段”区域，则此字段种的每类项目会成为列；我们可以将希望按列显示的字段拖动到此区域。 将字段拖动到“数值”区域，则会自动计算此字段的汇总信息（如求和、计数、平均值、方差等等）；我们可以将任何希望汇总的字段拖动到此区域。 将字段拖动到“报表筛选”区域，则可以根据此字段对报表实现筛选，可以显示每类项目相关的报表。我们可以将较大范围的分类拖动到此区域，以实

31、现报表筛选。 使用行、列标签区域： 例如在数据透视表字段列表中选中“部门”、“性别”和“年龄 ”字段。这时候“产品名称”字段自动出现在“行标签”区域；由于“年龄”字段是“数字”型数据，自动出现在数据透视表的“数值”区域。数值区域默认是求和模式，因我们要统计平均年龄，则在数值下拉菜单处选“值字段设置”，计算类型选“平均值”，再点“数字格式”，设置小数点位数。如下图：,41,结果如下图：,42,在 Excel 2007 的数据透视表中，如果勾选的字段是文本类型，字段默认自动出现在行标签中，如果勾选的字段是数值类型的，字段默认自动出现在数值区域中。我们也可以将关注的字段直接拖动到相应的区域中。 结果

32、如图：,43,报表筛选字段的使用,例如将“部门”字段拖动到“报表筛选”区域，将“性别”字段拖动到“列标签”区域，将“所在项目” 字段拖动到“行标签”区域，将“年龄”拖动到“数值”区域，则可以按部门查看每个部门项目部男女平均年龄。 在“报表筛选”区域，可以对报表实现筛选，查看所关注的特定地区的详细信息。直接单击“报表筛选”区域中“地区”字段右边的下拉键头，即可对数据透视表实现筛选。,44,一、函数语法： 由函数名+括号+参数组成 例： 求和函数: SUM(A1,B2,) 参数与参数之间用逗号“ , ”表示,第六部分 常用函数介绍,45,常用函数IF,根据对指定的条件计算结果为 TRUE 或 FA

33、LSE，返回不同的结果。可以使用 IF 对数值和公式执行条件检测。 Microsoft Excel 还提供了其他一些函数，它们可根据条件来分析数据。例如，如果要计算某单元格区域内某个文本字符串或数字出现的次数，则可使用 COUNTIF 和 COUNTIFS 电子表格函数。若要计算基于某区域内一个文本字符串或一个数值的总和，可使用 SUMIF 和 SUMIFS 工作表函数。,46,常用函数AND、OR,所有参数的计算结果为 TRUE 时，返回 TRUE；只要有一个参数的计算结果为 FALSE，即返回 FALSE。 通过 AND 函数可以检验多个不同的条件，而不仅仅是一个条件。,=AND(logi

34、cal1, logical2, .),注意： 参数的计算结果必须是逻辑值（如 TRUE 或 FALSE），而参数必须是包含逻辑值的数组 如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。 如果指定的单元格区域未包含逻辑值，则 函数将返回错误值 #VALUE!。,例：假如有人上街，每个人买零食或者买衣服花费了一定的金额，现在要筛选出既买了衣服，又买了零食的人，那么可以用AND函数进行如下操作：,47,常用函数AND、OR,=OR(logical1,logical2,.),在其参数组中，任何一个参数逻辑值为 TRUE，即返回 TRUE；任何一个参数的逻辑值为 FALSE，即返回 FALS

35、E。,注意： 参数的计算结果必须是逻辑值（如 TRUE 或 FALSE），而参数必须是包含逻辑值的数组 如果数组或引用参数中包含文本或空白单元格，则这些值将被忽略。 如果指定的单元格区域未包含逻辑值，则 函数将返回错误值 #VALUE!。,例：如要求下表中营业收入和合同均大于10000的，则在D2输入公式=or(B2=10000,C210000)，D列结果为FALSE为不满足条件。,48,常用函数LARGE、SMALL,=LARGE(array,k),返回数据集中第 k 个最大值。使用此函数可以根据相对标准来选择数值。 Array 为需要从中选择第 k 个最大值的数组或数据区域。 K 为返回值

36、在数组或数据单元格区域中的位置（从大到小排）。,注解 如果数组为空，函数 LARGE 返回错误值 #NUM!。 如果 k 0 或 k 大于数据点的个数，函数 LARGE 返回错误值 #NUM!。 如果区域中数据点的个数为 n，则函数 LARGE(array,1) 返回最大值，函数 LARGE(array,n) 返回最小值。,=SMALL(array,k) 返回数据集中第 k 个最小值。,49,常用函数LARGE,=SMALL(array,k) 返回数据集中第 k 个最小值。,50,常用函数MAX,=MAX(number1,number2,.),返回一组值中的最大值。 注解 参数可以是数字或者是

37、包含数字的名称、数组或引用。 逻辑值和直接键入到参数列表中代表数字的文本被计算在内。 如果参数为数组或引用，则只使用该数组或引用中的数字。数组 或引用中的空白单元格、逻辑值或文本将被忽略。 如果参数不包含数字，函数 MAX 返回 0（零）。 如果参数为错误值或为不能转换为数字的文本，将会导致错误。 如果要使计算包括引用中的逻辑值和代表数字的文本，请使用 MAXA 函数。,=MIN(number1,number2,.) 返回一组值中的最小值。 =MEDIAN(number1,number2,.) 返回给定数值的中值。中值是在一组数值中居于中间的数值。 =MODE(number1,number2,

38、.) 返回在某一数组或数据区域中出现频率最多（众数）的数值。,51,常用函数AVERAGE,=AVERAGE(number1, number2,.),返回参数的平均值（算术平均值）。,注释： 参数可以是数字或者是包含数字的名称、单元格区域 或单元格引用。 逻辑值和直接键入到参数列表中代表数字的文本被计 算在内。 如果区域或单元格引用参数包含文本、逻辑值或空单 元格，则这些值将被忽略；但包含零值的单元格将被计算在内。 如果参数为错误值或为不能转换为数字的文本，将会导致错误。 若要在计算中包含引用中的逻辑值和代表数字的文本，请使用 AVERAGEA 函数。 若要只对符合某些条件的值计算平均值，请使

39、用 AVERAGEIF 函数或 AVERAGEIFS 函数。,=AVERAGE(A2:A6,5),即(A2+A3+A4+A5+A6+5)/6的结果,52,常用函数AVERAGEIF,=AVERAGEIF(range,criteria,average_range),注解： 忽略区域中包含 TRUE 或 FALSE 的单元格。 如果 average_range 中的单元格为空单元格，AVERAGEIF 将忽略它。 如果 range 为空值或文本值，则 AVERAGEIF 会返回 #DIV0! 错误值。 如果条件中的单元格为空单元格，AVERAGEIF 就会将其视为 0 值。 如果区域中没有满足条件

40、的单元格，则 AVERAGEIF 会返回 #DIV/0! 错误值。 您可以在条件中使用通配符，即问号 (?) 和星号 (*)。 Average_range 不必与 range 的大小和形状相同。求平均值的实际单元格是通过使用 average_range 中左上方的单元格作为起始单元格，然后加入与 range 的大小和形状相对应的单元格确定的。,range 是要计算平均值的一个或多个单元格，其中包括数字或包含数字的名称、数组或引用。 criteria 是数字、表达式、单元格引用或文本形式的条件，用于定义要对哪些单元格计算平均值。例如，条件可以表示为 32、“32“、“32“、“苹果“ 或 B4。

41、 average_range 是要计算平均值的实际单元格集。如果忽略，则使用 range。,53,AVERAGE( 数值1,数值2,) A B C 1 100 20 1000 2 200 40 2000 3 300 60 3000 =AVERAGE（A1：A3）=200 =AVERAGE（A1：A3，B1）=105,常用函数AVERAGEIF,54,求和:SUM SUM( 数值1, 数值2,) A1=6 A2=7 A3=8 =SUM(6,8)=14 =SUM(A1,A3)=6+8=14 =SUM(A1:A3)=6+7+8=14,常用函数SUM,55,常用函数SUM,=SUM(number1,n

42、umber2, .),说明 直接键入到参数表中的数字、逻辑值及数字的文本表达式将被计算，请参阅下面的示例一和示例二。 如果参数是一个数组或引用，则只计算其中的数字。数组或引用中的空白单元格、逻辑值或文本将被忽略。请参阅下面的第三个示例。 如果参数为错误值或为不能转换为数字的文本，将会导致错误。,56,条件求和:SUMIF SUMIF( 范围, 条件,要求和范围) A B C 1 100 20 1000 2 200 40 2000 3 300 60 3000 =SUMIF(A1:A3, “=200”,B1:B3)=100 ,对A1至A3单元格内=200对应B1至B3单元格的数相加，即A1至A3

43、=200为A2和A3，那么对B2和B3相加 =SUMIF(A1:A3, “=200”,C1:C3)=5000,常用函数SUMIF,57,常用函数COUNT,=COUNT(value1, value2,.),注解 如果参数为数字、日期或者代表数字的文本（例如，用引号引起的数字，如 “1“），则将被计算在内。 逻辑值和直接键入到参数列表中代表数字的文本被计算在内。 如果参数为错误值或不能转换为数字的文本，则不会被计算在内。 如果参数为数组或引用，则只计算数组或引用中数字的个数。不会计算数组或引用中的空单元格、逻辑值、文本或错误值。 若要计算逻辑值、文本值或错误值的个数，请使用 COUNTA 函数。

44、 若要只计算符合某一条件的数字的个数，请使用 COUNTIF 函数或 COUNTIFS 函数。,=COUNTIF(range,criteria) 计算某个区域中满足给定条件的单元格数。例如：=COUNTIF(A2:A7,“=20“),58,求符合条件个数:COUNTIF COUNTIF( 范围,条件) A B C 1 100 20 1000 2 200 40 2000 3 300 60 3000 =COUNTIF(A1:A3,“=200”）=2 =COUNTIF(A1:A3, “200” ）=1,常用函数COUNTIF,59,求个数:COUNT COUNT( 数值1,数值2,) A B C 1

45、 100 20 1000 2 200 40 2000 3 300 60 3000 =COUNT(A1:A3)=3 =COUNT(B1:B3)=3,60,常用函数LEFT、MID、RIGHT,=LEFT(text,num_chars) =MID(text,start_num,num_chars) =RIGHT(text,num_chars),Text 是包含要提取字符的文本字符串。 Start_num 是文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为 1，以此类推。 Num_chars 指定希望函数从文本中返回字符的个数。,根据所指定的字符数，LEFT 返回文本字符串

46、中第一个字符或前几个字符。,MID 返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。,RIGHT 根据所指定的字符数返回文本字符串中最后一个或多个字符。,61,文本函数 截取函数LEFT RIGHT MID LEFT( 文本, 数值) 从左边截取 RIGHT ( 文本, 数值) 从右边截取 MID ( 文本, 开始位，数值) 从中间截取 =LEFT（ “abcdef”,2）= ab =RIGHT（ “abcdef”,2 ）=ef = MID（ “abcdef”,2,3 ）= bcd,62,计算字符长度LEN LEN( 文本) 计算字符串的长度 =LEN（ “abcdef”）=

47、6 合并字符函数CONCATENATE 或 & CONCATENATE( 文本1，) 合并字符串 =CONCATENATE（ “a”, “ef”）= aef,63,在字符串中查找特定字符FIND FIND( 文本, 范围,数值) 查找一个字符在另一个字符串中的位置 数值表示查找第几个. =FIND（ “a”, “abcaef”,1）= 1 =FIND（ “a”, “abcaef”,2）= 4,64,比较两个字符是否完全相符EXACT EXACT( 文本1,文本2) 比较两个字符是否完全相符,是则为TRUE,否则为FALSE. =EXACT（ “a”, “a”）= TRUE =EXACT（ “a

48、”, “ab”）= FALSE =EXACT（ “a”, “A”）= FALSE,65,常用函数CONCATENATE,=CONCATENATE (text1,text2,.),将两个或多个文本字符串合并为一个文本字符串。,注解 您也可使用连接符号 (&) 计算运算符代替 CONCATENATE 函数来连接文本项。例如 =A1&B1 与 =CONCATENATE(A1,B1) 返回相同的值。,66,常用函数RAND、RANDBETWEEN,=RAND( ),注解 若要生成 a 与 b 之间的随机实数，请使用： RAND()*(b-a)+a 如果要使用函数 RAND 生成一随机数，并且使之不随单元格计算而改变，可以在编辑栏中输入“=RAND()”，保持编辑状态，然后按 F9，将公式永久性地改为随机数。,返回大于等于 0 及小于 1 的均匀分布随机实数，每次计算工作表时都将返回一个新的随机实数。,=RANDBETWEEN(bottom,top),返回位于指定的两个数之间的一个随机整数。每次计算工作表时都将返回一个新的随机整数。 Bottom 函数 RANDBETWEEN 将返回的最小整数。 Top 函数 RANDBETWEEN 将返回的最大整数。,67,常用函数RANK,=RANK(number,ref,order),Number 为需要找到排位