计算机应用基础 中文Excel 2010电子表格处理软件.ppt

计算机应用基础,第4章 中文Excel 2010电子表格处理软件,4.1 EXCEL 2010概述,Excel 2010是Office 2010的一个组件，是一个性能优越的电子表格软件，以表格和图表的形式组织和管理数据，而且方便数据的查找。 Excel 广泛应用于财务、统计、数据分析领域，为日常数据处理带来很大的方便。本章主要通过任务驱动的方式来介绍Excel 2010的基本概念、基本操作、图表操作、数据管理等功能。,4.1 EXCEL 2010概述,Excel 2010的启动与退出操作同Word 2010类似，第一次启动Excel 2010后，系统自动建立一个名为Book1的空工作簿，供用户添加内容，工作界面如下图所示，Excel 2010窗口由快速访问工具栏、选项卡、功能区、标题栏、单元格名称栏、状态栏、编辑栏、列号、行号、滚动条、视图按纽、缩放控件和工作表窗口等组成。,4.1 EXCEL 2010概述,4.2成绩表的设计与制作,如下图所示是下面各项任务所要处理的学生成绩表，我们将对学生成绩表从建立到数据输入、格式设置、工作表的编辑、页面设置、打印、成绩表的统计与分析、函数应用、数据清单的建立、数据排序、数据筛选、分类汇总、图表制作等进行分别处理。,4.2.1 任务一 工作簿的建立,工作簿就是一个Excel文件。当启动Excel后，Excel将自动创建一个新的工作簿文件，简称工作簿，例如book1.xlsx，注意扩展名是.xlsx。一个工作簿由一个或多个工作表组成，系统一般默认为三个工作表，一个工作簿最多可包括255个工作表。 工作表是工作簿中编辑二维表格的场所。每个工作表的列标题（列号）用字母A表示，共256列，行标题（行号）用数字165536表示。为了区分不同的工作表，每个工作表都有一个名称显示在工作表标签上，如sheet1、sheet2、sheet3既为工作表名，用户可以根据表格内容更改工作表表名。每打开一个Excel就打开了一个工作簿，工作簿如同一个本子，而工作表如同本子里的一张张页面。,4.2.1 任务一 工作簿的建立,单击“桌面”上Excel图标或点击“开始”菜单“程序”|“Microsoft Office”|“Microsoft Excel 2010”，打开Excel窗口，也可以单击“文件”|“新建”打开如下图所示的对话框，可选择所需要的模板创建一个新的工作簿。,4.2.2 任务二 在工作表中输入数据,1单元格 单元格是Excel工作表中的矩形框，是工作表的基本元素，单元格中可以存放文字、数字和公式等信息，当前单元格是选中的单元格，单元格是深色的其它单元格颜色是浅色的。 2单元格区域 单元格区域是工作表中的矩形区域，进行数据处理时，常用到多个单元格中的数据（单元格引用），一组连接在一起的单元格组成一个矩形区域，称为单元格区域。标示方法：“左上角单元格标示：右下角单元格标示”。例如：选中区域A1.C3 表示由A1、A2、A3、B1、B2、B3、C1、C2、C3单元格组成的矩形区域，A1.C3 中的冒号必须是半角英文符号。 3数据类型 Excel中常用的数据类型包括数字、文本、日期、时间等常量数据，以及公式、批注、超链接等类型。,4.2.2 任务二 在工作表中输入数据,1输入文本： 文本数据主要包括数字、文本及符号等内容，一个单元格最多可以输入255个字符。默认情况下，文本在单元格中是左对齐，数字是右对齐。 （1）直接输入文本 （2）在一个单元格中输入多行文本 （3）在多个或不连续的单元格，输入数据 （4）输入文本形式的数字： （5）单元格复制、剪切，粘贴：,4.2.2 任务二 在工作表中输入数据,2输入时间和日期： Excel能够识别常用的时间和日期格式，如果输入日期，输入格式可以是“年-月-日”，“年/月/日”等格式，如果输入时间，输入格式可以是“时：分：秒”，“时：分”等格式，Excel能识别出一个有效的日期和时间格式，在单元格内以右对齐显示，如果不能识别，则输入的内容被视为文本，以左对齐方式显示。用户可以自己设置日期和时间的格式，单击“开始”|“数字”功能区右下角的按钮，弹出“设置单元格格式”窗口，选择“数字”选项卡中的 “日期”和“时间”选项卡进行设置。如果在单元格中输入当天的系统日期可以按【Ctrl+；】组合键，输入当前的系统时间按【Ctrl+Shift+；】组合键。,4.2.2 任务二 在工作表中输入数据,3输入公式： 在单元格中输入公式，必须以“=”开头。先选择公式计算后结果所在的单元格，然后直接输入“=”，再输入表达式，如下图所示，然后回车或点击编辑栏的“ ”就可以把结果输入到I3单元格。如果公式编辑错误可以点击编辑栏中的“ ”重新输入。,4.2.2 任务二 在工作表中输入数据,在单元格内输入“=”在Excel中可以有“参照”的功能，如在下图中Sheet2工作表的B3单元格中有“恭喜你”三个字，要求把这三个字用公式的方法放到Sheet1工作表中的B5单元格中，注意不是要复制，而是要求用公式。方法是在Sheet1的B5单元格中输入“=”，然后选择Sheet2工作表的B3单元格，此时B3单元格四周是闪烁的虚线，再回车，就可以把Sheet2中B3单元格中的内容放到Sheet1工作表中的B5单元格中,4.2.2 任务二 在工作表中输入数据,“参照”功能的另一个用处，如在“学生成绩表”工作簿中已经有“成绩表”工作表，现在在Sheet2中制作一个“选修课成绩表”，当然学号、姓名等基本信息和“成绩表”中一样，那么在“选修课成绩表”工作表中的基本数据可以采用复制的方法，现在我们用参照的方法也可以，先在“选修课成绩表”中选择“姓名”列的B3输入“=”，然后回到“成绩表”中选择B3，回车后就把“成绩表”中B3单元格的内容“马援”参照到“选修课成绩表”中B3单元格了，最后点填充柄填充即可，其它数据参照一样。,4.2.2 任务二 在工作表中输入数据,4输入函数： 可以使用函数对单元格数据进行计算，如SUM求和，先把光标放在计算结果要放的单元格，然后按公式编辑栏中的“ ”按钮，进入插入函数对话框，进行操作。如果要计算的单元格区域正确，选择“确定”按钮就可以把结果计算出来，如果选择的单元格区域不正确，可以在参数Mumber1中重新输入正确的区域或按“ ”按钮回到工作表中选择单元格区域，再回车回到函数窗口确定就可以了。,4.2.2 任务二 在工作表中输入数据,5. 输入序列数据： 如果在单元格中输入的是序列数据，如：星期一、星期二.或2，4，6，8.等有规律的数据，可以使用Excel的自动填充数据的功能，填充的内容可以是Excel提供的自定义序列，也可以是数字、文本、文本与数字的混合形式、公式、函数，更可以是根据需要自己定义的序列。 如我们在输入“成绩表”时“姓名”列一般是不变的，那么就可以把学生的姓名作成一个自定义序列，以后只要输入其中一个学生的姓名就可以利用填充的方法把其他学生的姓名填充出来。 自定义序列的步骤如下：,4.2.2 任务二 在工作表中输入数据,4.2.2 任务二 在工作表中输入数据,6. 数据的有效性： 使用数据有效性可以控制用户输入到单元格的数据或值的类型，保证数据的有效而不至于输入的数据错误。例如，将数据输入限制在某范围列表限制选择、输入序列、限制日期或时间的范围等。 使用列表限制选择 在某些单元格中需要输入固定格式的数据，如“学生成绩表”中“性别”字段，因为“性别”只有“男、女”，如果手动输入可能会输入错误，而不会进行错误检查，就会出现“性别”是其他内容的情况。可以利用“数据有效性”做成一个下拉列表，进行选择性输入，可以保证输入数据的正确性,4.2.2 任务二 在工作表中输入数据,先选定“学生成绩表”中“性别”字段下的单元格区域C4：C18，单击“数据”选项卡，在“数据工具”组中，单击“数据有效性”按钮下的向下箭头，选择“数据有效性”命令，打开“数据有效性”对话框。单击“设置”选项卡，并单击“允许”右侧的下拉按钮，从中选择“序列”选项。在“来源”方框中输入序列的各元素：男,女，注意男女中间的逗号必须是西文的逗号，再单击“确定”返回。 在“学生成绩表”中“性别”字段下的单元格，当选中时会出现一个下拉按钮，单击此按钮，在弹出的下拉列表中就可以选择刚才输入的序列，而不需要进行手动输入了。实际上凡是在数据输入时固定的数据都可以做成序列。,4.2.2 任务二 在工作表中输入数据,限制数据的输入范围 在“学生成绩表”中，学生的各科成绩应该在一个范围内如0100分之间，如果不进行限制就可能输入超出正常分数范围的情况了，就需要通过数据有效性进行限定设置。,4.2.3 任务三 工作表格式的设置,1选择编辑区域： 不管是对单元格还是对工作表的操作，都要首先确定操作对象。包括： （1）选择单元格： 用鼠标直接点击工作表中要进行操作的单元格。 （2）选择单元格区域： 如果是连续的单元格区域，就用鼠标拖动选择连续区域，如果连续区域很大，也可以先选择连续区域的第一个单元格，然后按住Shift，再点击连续区域右下角最后一个单元格即可。 如果是不连续区域，首先选择一个单元格，然后按住Ctrl键不松，用鼠标点击其它不连续单元格即可。 （3）选择整行或整列： 用鼠标在行标题或列标题上直接点击，即可选中该行或该列，在行标题或列标题上拖动鼠标可以选择多行或多列，也可以结合Ctrl键选择不连续的多行或多列。 （4）选择整个工作表： 选择整个工作表可以利用Ctrl+A组合键，也可以单击行标题和列标题交叉处的“全选”按钮 可以快速选择整个工作表。,4.2.3 任务三 工作表格式的设置,2编辑单元格： （1）插入一行（列）和插入单元格： 先选择准备插入的单元格位置，再选择“开始”功能区中的“插入”下的下拉按钮，在 “插入”对话框中选择插入的选项。可以在事先选择的单元格位置选择插入的类型。可以是插入一行或一列。如果事先选择单元格是连续多个，则插入所选相同数量的单元格或行列。 （2）调整单元格的行高和列宽： 为了适应数据的长度和宽度以及排版的美观，需要对行高和列宽进行调整，有以下三种方法。 简单的方法是直接用鼠标拖动行标题或列标题的边界，此时鼠标变成一个黑色双向箭头，按住鼠标左键拖动就可以改变成所需的行高或列宽。 先选择需要调整的行或列，再选择“开始”功能区中的“格式”按钮下的下拉按钮，在联级菜单下选择“行高”或“列宽”，在弹出的对话框中输入数据即可。 选定整行或整列（单击行号或列号），单击鼠标右键，在弹出的快捷菜单中选择“行高”，“列宽”命令，同样会弹出相应的对话框，输入合适的数值即可。,4.2.3 任务三 工作表格式的设置,（3）移动和复制单元格： 移动和复制单元格的基本方法在前面已经介绍，这里主要介绍“选择性粘贴”，因为在以后的操作中选择性粘贴非常重要。,4.2.3 任务三 工作表格式的设置,（4）删除或清除单元格数据： 在编辑过程中，如果要删除单元格数据，首先选择需要删除内容的单元格区域，然后按Del键或其它方法的删除方法（同复制方法）。还有一个重要的方法，在“开始”功能区中“清除”按钮，在下拉菜单中可以根据需要选择要清除格式、内容、批注等。 （5）设置单元格格式 单元格格式包括数字类型、对齐样式、字体样式、边框样式、单元格底纹和单元格保护。 选中需要进行设置的单元格区域。 选择“开始”功能区中的“格式”按钮命令，打开“设置单元格格式”对话框，或单击鼠标右键，在弹出的功能菜单中打开“设置单元格格式”对话框进行设置。,4.2.3 任务三 工作表格式的设置,4.2.4 任务四 工作表的编辑,1添加工作表： 在Excel中，一个工作簿默认有三个工作表，用户可以根据需要添加更多的工作表。 方法一：在“开始”功能区中选择“插入”按钮的下拉按钮，选择“插入工作表”命令，Excel将在当前工作表前插入一个新工作表，被插入的工作表标签依次命名为“Sheet4”，“ Sheet5”等。 方法二：用鼠标右击工作表标签，在选定的工作表标签上右击鼠标，在弹出的功能菜单中选择“插入”命令，在弹出的“插入”对话框中选择插入的类型是工作表，确定即可。 方法三：直接用鼠标单击工作表标签最右侧的 按钮，可以直接插入新工作表。 方法四：也可以改变工作簿的默认工作表的数目，在“文件”功能区中下选择“选项”命令，出现“选项”对话框，单击“包含的工作表数”的数值框，输入想要的工作表数目，下次打开时就会出现相应数目的工作表。,4.2.4 任务四 工作表的编辑,2重命名工作表： 直接用鼠标双击要重命名的工作表标签，进入编辑状态输入新的工作表名称，回车即可，如把Sheet1改名为“学生成绩表”。也可以用鼠标右键点击工作表标签，在弹出的功能菜单上选择“重命名”为工作表改名。 3删除工作表 如果要删除某个或多个工作表，可以先选择要删除的工作表标签，然后用鼠标右键点击工作表标签，在弹出的功能菜单中选择删除命令即可。也可以先选择要删除的工作表标签，然后在“开始”功能区中选择“删除”按钮下的“删除工作表”命令即可。 4移动和复制工作表 用户可以在工作簿内移动（复制）工作表，也可以将工作表移动（复制）到其它工作簿中。,4.2.4 任务四 工作表的编辑,5保护工作表： 工作表建好以后，为了防止重要的数据被改动或复制，用户可以利用Excel提供的保护功能，对单元格、工作表或工作簿采取保护措施。 （1）单元格的保护：先选择要保护的单元格区域，在“开始”功能区的“单元格”组中单击“格式”按钮下的向下箭头，出现快捷菜单，选择“设置单元格格式”命令，在对话框中单击“保护”选项卡，再选择“锁定”。 （2）工作表的保护，在“开始”功能区的“单元格”组中单击“格式”按钮下的向下箭头，出现快捷菜单，选择“保护工作表”命令，在弹出的“保护工作表”对话框，用户可以选择要保护的内容， 只有保护工作表后，对单元格的保护设置才会有效。,6工作表窗口的拆分与冻结： （1）工作表窗口的拆分：当表格行数或列数较多时，在一个屏幕窗口无法全部显示完整，可以在不隐藏行或列的情况下，在一个屏幕窗口自由的移动窗口内容进行编辑。 先选择要进行窗口拆分的工作表，选择要拆分的表内的位置，再选择“视图”功能区中的“拆分”命令按钮，就可以实现对工作表的拆分，可以根据需要拆分成两个或四个窗口，要取消拆分，再单击“拆分”命令按钮就可以了。 （2）工作表窗口的冻结：冻结窗口是为了在移动滚动条时，始终保持某些行或列在可视区域内可见，被冻结的部分一般是标题行或列，既表头部分。先选择要进行窗口冻结的工作表，选定一个单元格（一般是标题行或列的后一个），再选择“视图”功能区中的下的“冻结窗口”命令按钮，对该单元格上方和左方的列进行冻结。 在“冻结窗口”命令按钮的下拉菜单中，还有“冻结首行”和“冻结首列”命令，也可以根据需要进行冻结，要取消以上的冻结，只需要再次执行该命令即可。,4.2.4 任务四 工作表的编辑,4.2.4 任务四 工作表的编辑,7共享工作簿 共享工作簿是为了使多个用户在网络中，同时在一个工作簿中工作，加快表格中数据输入速度。在保存更改记录时，对产生的冲突立即做出适当的处理。 （1）单击“审阅”选项卡，在“更改”组中单击“共享工作簿”按钮，打开“共享工作簿”对话框， （2）在“编辑”选项卡中，单击“允许多用户同时编辑”复选框，在“高级”选项卡中，对“修订”、“更新”、“用户间的修订冲突”等进行设置，,4.2.5 任务五 页面设置与预览打印,1页面设置。 （1）纸张大小 打开“学生成绩表”工作表后，单击“页面布局”选项卡，在功能区的“页面设置”组中单击“纸张大小”按钮，打开的下拉菜单中选择纸张类型，在工作表中会出现分页线（竖虚线） （2）纸张方向 单击“页面布局”选项卡，在“页面设置”组中，单击“纸张方向”按钮，在打开的下拉菜单中选择纸张方向。 （3）纸张页边距 单击“页面布局”选项卡，在“页面设置”组中，单击“页边距”按钮，在打开的下拉菜单中选择页边距的需要形式。也可以在“页面设置”组中，单击右下角的辅助按钮 ，打开“页面设置”对话框，在该对话框的“页边距”选项卡中，设置页边距与居中方式。,4.2.5 任务五 页面设置与预览打印,4.2.5 任务五 页面设置与预览打印,2、打印设置 （1）打印区域 单击“页面布局”选项卡，在“工作表选项”组中，单击右下角的辅助按钮 ，打开“页面设置”对话框，单击“工作表”选项卡，在“打印区域”右侧，单击 按钮，进行打印区域的选择。 （2）打印标题： 表格的标题及列标题通常在每一页都要打印出来，在“页面设置”的“工作表”选项卡中，单击“顶端标题行”或“左端标题行”右侧的按钮，进行标题行区域的选择。如果所选中的顶端标题行由若干整行组成，通过拖动行号进行选择，而不仅是顶端标题行所包含的数据区域。,4.2.5 任务五 页面设置与预览打印,3、定义页眉页脚： 如果要打印的表格有多页，通常在页眉或页脚区设置每页要显示的信息，如工作簿名、工作表名、页码、日期、时间等。 在“页面设置”对话框中，单击“页眉/页脚”选项卡。单击“自定义页眉”按钮，打开“页眉”对话框，在左中右其中一个位置输入页眉的内容。再单击“自定义页脚”按钮，打开“页脚”对话框，设置页脚的内容，可以插入页码等内容。,4.2.5 任务五 页面设置与预览打印,4.文档预览 在页面设置和打印设置完成后，在“页面设置”对话框中，单击“打印预览”按钮，屏幕显示打印预览结果。单击“文件|打印”命令，也可以进行打印预览。如果打印范围超出右边界时，可以在“页面设置”对话框中单击“页面”选项卡，对“缩放比例”进行修改，正常打印为“100%”。 5.文档打印 打印预览后，当设置符合要求时，就可以打印了，在打印前需要做如下选择或设置。 选择打印机。单击“文件|打印”命令，再单击“打印机”右侧的向下箭头，出现下拉菜单，选择和计算机连接的打印机名称。 选择打印对象。单击“设置”右侧的向下箭头，出现下拉菜单和打印对象列表，选择打印的对象。 指定打印页面范围。单击“页数”计数器，设置打印页面范围。 打印缩放设置。单击“无缩放”右侧的向下箭头，出现菜单，选择所需要进行的缩放设置。,4.3成绩表的统计与分析,为了在成绩表原有基本数据基础上，添加更多有用的信息以及通过各种函数的使用从成绩表导出各种信息，强化对成绩表的管理，本节“成绩表的统计与分析”是在上一章的基础上进行的操作。,4.3.1 任务一 为“成绩表”添加相关信息,1、添加“批注” （1）首先打开“学生成绩表”工作簿进入学生成绩表。用鼠标选中第一个学生的姓名单元格。 （2）点击“审阅”选项卡，在“批注”组中选择“新建批注”命令，或用鼠标右键点击单元格，选择“插入批注”进入“批注”编辑并输入个人的基本简历。 （3）依次输入每个人的个人简历的批注，注意在姓名列中每个人的“姓名”单元格的右上角会有一个红色的三角。可以用鼠标右键点击此单元格，在弹出的功能菜单中，选择“显示/隐藏批注”、“编辑批注”、“删除批注”。 （4）也可点击某个或选择一批有批注的单元格，选择“开始”选项卡中“编辑”组中的“清除”命令，选择清除“批注”选项，可以清除批注。,4.3.1 任务一 为“成绩表”添加相关信息,4.3.1 任务一 为“成绩表”添加相关信息,2插入“文本框”、“艺术字” 在Excel中可以插入“文本框”和“艺术字”，在插入的文本框中输入相关内容，可以进行如同Word一样的设置，同时文本框相对于单元格来说是一个独立的空间，可以单独进行格式设置。 打开“插入”选项卡，在“文本”组中单击“文本框”下面的箭头，在下拉菜单中选择“横排文本框”或“竖排文本框”，就可以在工作区域画出文本框范围，大小可以调节。 右键单击所编辑的“文本框”，在弹出的快捷菜单中，选择“设置文字效果格式”命令，出现如下图所示，可以对文本框进行进行如：文本填充、文本边框、阴影等设置。插入“艺术字”也一样。,4.3.1 任务一 为“成绩表”添加相关信息,3插入“图片” 单击“插入”选项卡，选择“插图”组，根据需要可以插入“图片”、“剪贴画”、“形状”等对象。当插入这些对象后，单击鼠标右键，在弹出的快捷菜单中选择“设置图片格式”就可以对所插入的图片进行编辑处理了。 4插入“超链接” 如果在“学生成绩表”中每个学生都有照片电子文档，可以在学号列把每个人的照片进行超链接。首先选择第一个的“学号”单元格，单击“插入”选项卡，选择“超链接”按钮，或右击鼠标右键在快捷菜单中选择“超链接”命令，就进入下图界面，选择照片所在的位置和文件名，就建立了超链接。超链接的单元格颜色变成蓝色，且单元格内容的下方有一条蓝色细线，当点击具有超链接的单元格时，会进入超链接的内容。可以先选择具有超链接的单元格，用鼠标右键点击，在弹出的功能菜单下选择“取消超链接”，可以清除超链接。,4.3.2 任务二 应用函数,1求和函数SUM和求平均值函数AVERAGE 对“学生成绩表”求各科成绩的总分（求和函数SUM的操作过程在前面已经介绍过），下面介绍求平均分函数的操作过程： （1）先把光标放在计算结果要放的单元格J3，然后按公式编辑栏中的“ ”按钮，进入插入函数对话框，进行操作。选择“AVERAGE”确定后进入对话框。 （2）如果要计算的单元格区域正确，选择“确定”按钮就可以把结果计算出来，如果单元格区域不正确，在此“学生成绩表”中的数据区域就不正确（首先进入时默认是E3：I3，把总分也计算进去了，应该是E3：H3），需要重新选择，可以点击重新输入正确的区域E3：H3或按“ ”按钮回到工作表中选择单元格区域，再回车回到函数窗口确定就可以了，然后用填充柄填充函数可以把其他学生的平均分也计算出来。,4.3.2 任务二 应用函数,4.3.2 任务二 应用函数,2在不同的工作表中引用数据： 对要进行计算操作的数据如果分别在不同的工作表或不同的工作簿，就需要注意了。如在“学生成绩表”工作簿中有“成绩表”工作表和“选修课成绩表”工作表，在计算总分和平均分时需要把第二个工作表的选修课成绩计算进去。 进入函数对话框，其中单击Number1参数后的“ ”按钮回到“成绩表”工作表中选择单元格区域E3：H3，回车后选Number2参数后的“ ”按钮到“选修课成绩表”选择E3：F3区域，回车到如下图所示，确定后就可以把两个工作表的数据进行计算了。,4.3.2 任务二 应用函数,3单元格的相对引用、绝对引用和混合引用 在求“成绩表”的平均值时也可以使用公式的方法，因为平均值也可以是总分/科目数，因此在求平均值时对第一个人选择J3后可以用公式“=I3/4”，输入公式后回车然后填充即可，这里用的是相对地址。 在有些情况下，有的数据在计算时单独存放在另一单元格，而且这个数据所在的单元格是不变的，这种情况下在使用时就要注意了，例如：如果我们在求平均值时把科目数“4”放在“H13”单元格，在J3输入公式“=I3/H13”回车，求第一个人的值是对的，因为H13就是“4”，但在填充时就错了，如下图所示。,4.3.2 任务二 应用函数,为什么呢？这时你点“J4”单元格，出现 表明数据错误，在编辑栏中你会发现公式是“ ”，因为之所以我们可以用填充功能可以把其它平均值填充出来，是用了公式或函数的填充，你会发现在单元格内公式或函数参数中的单元格地址也是顺序改变的，所以填充时的结果才是正确的。所以刚才填充时对应“J4”单元格，公式中自然也变成“=I4/H14”列标没变一直是“I”和“H”，而行标就顺序改变了，而“H14”没有值默认是“0”，那么“I4”一除以“0”当然不对了。这时要把在求“J3”时的公式变成“=I3/$H$13”，再填充就不会错误了，如下图所示，这时你再看“J4”以及下面的单元格“I”后的数字在填充，而“H13”一直不变，“4”就放在里面一直不变，必须用“$H$13”。,4.3.2 任务二 应用函数,单元格引用地址的作用在于唯一表示工作簿上的单元格或区域。公式中引入单元格引用地址，其目的在于指明所使用的数据存放位置。通过单元格引用地址可以在公式中使用工作簿中不同部分的数据，或者在多个公式中使用同一个单元格的数据。使用如下三种类型的单元格地址表示法，可以创建出灵活多变的公式来。 （1）相对引用地址：是指在公式移动或复制时，该地址相对目的单元格发生变化，此类型地址由列名与行号表示，如上例中的“I3”。 （2）绝对引用地址：表示该地址不随复制或移动目的单元格的变化而变化。绝对引用地址的表示方法是在相对地址的列名和行号前分别加上一个美元符号“$”，例如$A$1。美元符号“$”就象一把“小锁”，锁住了参加运算的单元格，使它们不会因为复制或移动目的位置的变化而变化。在填充时也不会变，如上例中“$H$13”。在使用时输入“H13”然后按“F4”键，可以快速的变成“$H$13”。 （3）混合地址：如果单元格引用地址一部分为绝对引用地址，另一部分为相对引用地址组成，例如“$A1”或“A$1”，我们把这类地址称为“混合地址”。 “$A1”表明以后改变时列标不变而行标可以变，“A$1”表明列标可以变而行标不变。 在以后的使用中如果有的单元格数据不变，那么在使用时就必须用绝对引用或根据不同需要使用混合引用。,4.3.2 任务二 应用函数,4.IF（）函数的应用： 功能：根据条件的成立与否，决定两个方向的选择。 格式： if（logical_test,value_if_true,value_if_false) 其中： “logical_test” 表示设定的条件， “value_if_true” 表示当目标单元格与设定条件相符时返回的函数值， “value_if_false” 表示当目标单元格与设定条件不符时返回的函数值 IF（）函数在Excel中使用很多，它是对条件判断的基础上，决定执行哪个语句，所以条件的选择决定了IF函数的正确与否。而且在函数中经常要用到IF函数的嵌套，下面用示例来进行说明。,4.3.2 任务二 应用函数,（1）要求给“成绩表”中“高数”一列每人加20分，如果结果超过100分，就按100算，否则按实际值算。操作如下： 先用光标选择M3单元格，把第一个人的加20分后的分数放在M3，它只是临时使用。 单击编辑栏中的“ ”按钮进入插入函数对话框，选择IF函数以后进入函数参数对话框。 在logical_test,value中输入“（H3+20）100”是根据要求的条件，在value_if_true输入“100”，要求是超过100分时按100分算，是条件成立时执行的结果，在value_if_false中输入“H3+20”是条件不成立时执行的结果，是实际的加20分后的结果。选择确定后，在K3单元格就把第一个人的高数加分的结果算出来了，然后选择函数的填充就可以了。 因为加分后的结果要放到H3单元格中，接下来选择K3：K11后选择“复制”，再点H3单元格后一定要选择“选择性粘贴”，在“选择性粘贴”选择“数值”选项，按确定即可。,4.3.2 任务二 应用函数,（2）要求在“平均分”后增加一列“等级”，按照平均分计算学生等级。 等级的区分是：=90是“优秀”；=75与=60与=90, 优秀,IF(J3=75, 良好,IF(J3=60, 及格, 不及格)”，然后回车即可把结果算出来，如下图所示。,当然也可以把公式写成： “=IF(J3=60,IF(J3=75,IF(J3=90,优秀,良好),及格),不及格)” 注意：在EXCEL函数参数里使用的符号都是英文状态下的半角符号！,4.3.2 任务二 应用函数,5求名次函数RANK（）： 功能：返回一个数字在数字列表中的排位。数字的排位是其大小与列表 中其他值的比值（如果列表已排过序，则数字的排位就是它当前的位置）。 格式：RANK(number,ref,order) Number 判断顺序的数值。 Ref 判断顺序的参照地址。若是非数值型参数将被忽略。 Order 用来指明排序的方式。若输入数值“0”或忽略，则以递减方 式排序，若输入数值非“0”，则以递增方式排序。 下面通过实例来说明RANK函数的使用：,4.3.2 任务二 应用函数,在“成绩表”中每个学生的总分已经知道了，要求在“等级”列后增加一“名次”列，计算出每个人按照总分在班级的名次。操作过程： （1）选择第一个名次对应的“L3”单元格，然后按公式编辑栏中的“ ”按钮，进入插入函数对话框，在“或选择类别”中选择“统计”，再选择下面的RANK函数以后进入函数参数对话框，如下图所示。,4.3.2 任务二 应用函数,（2）在对话框中的三个参数，分别输入“I3”既第一个人的总分单元格名称；第二个参数“$I$3：$I$11”既“总分”单元格区域，因为是要求“I3”在这个区域中的排名，而且这个区域是不变的必须用绝对地址，第三个参数“1”既一个非0数值，表明是递增排名。 （3）输入好三个参数后，按确定即可。然后按J3单元格的填充柄向下填充就完成了名次的输入，如下图所示，可以看到名次都排好了，你可以点J4或其它的学生名次单元格，看一下在“编辑栏”中的函数，就会发现第二个参数都一样，就是因为我们用了绝对地址，如果不用绝对地址，就会出错。,4.3.2 任务二 应用函数,6COUNTIF（）函数： 功能：可以用来计算给定区域内满足特定条件的单元格的数目。 格式：COUNTIF(range,criteria) range是指定单元格的范围，其中包括数字或名称、数组或 包含 数字的引用。空值和文本值将被忽略。 Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式、单元格引用或文本。 例如，条件可以表示为 32、32、32 、apples 或 B4。 下面通过实例来说明COUNTIF函数的使用：,4.3.2 任务二 应用函数,在“成绩表”中要求在“E12”、“F12”、“G12”、“H12”分别统计出相应课程分数=60的学生人数。 （1）选择 “E12”单元格，然后按公式编辑栏中的“ ”按钮，进入插入函数对话框，在“或选择类别”中选择“统计”，再选择下面的COUNTIF函数以后进入函数参数对话框。 （2）在“range”参数中选择后面的“ ”按钮回到“成绩表”工作表中选择单元格区域E3：E11，既第一门课的数据区域，或在“range”中直接输入这个数据区域。在“Criteria”参数中输入统计的条件，对应要求输入“=60”注意在函数或公式用到的标点符号都必须是英文下的符号。然后按“确定”即可。 （3）在E12单元格中求出了“邓论”课程“=60”符合条件的单元格数目，然后按E12单元格的填充柄向右填充，就可以把其它课程符合相同条件的人数统计出来了。,4.3.2 任务二 应用函数,4.3.2 任务二 应用函数,7求最大值函数MAX（）与求最小值函数MIN（）： 格式：MAX/MIN(numberl,number2,.) 参数：numberl,number2,.为需要求最大值或最小值或包含需要求最大值或最小值数据的参数,最多可以有30个参数。数字、逻辑值、文本数字、空单元格、单元格和区域的引用地址、名称和标志都可以作为参数。 如果参数直接是数字、空单元格、逻辑值或文本数字,计算时都将被包括在内,空单元格被视为0,TRUE的值为1,FALSE的值为0,文本数字自动转换为数值数字。如果参数直接是非数字文本或错误值,将显示错误提示。如果参数是单元格或区域的引用,只有引用内容是数字的才被计算,其他值都将被忽略。,4.3.2 任务二 应用函数,8.SUMIF（）函数： 功能：计算符合指定条件的单元格区域内的数值和。 格式：SUMIF（Range,Criteria,Sum_Range） Range代表条件判断的单元格区域； Criteria为指定条件表达式； Sum_Range代表需要计算的数值所在的单元格区域。 在“成绩表”中分别求“性别”为“男”的学生的各课程成绩之和。操作如下： （1）选择E12单元格，进入插入函数对话框，选择“数学与三角函数”中的SUMIF函数。 （2）点击Range参数后面的“ ”按钮回到“成绩表”工作表中选择单元格区域C3：C11既要求条件判断的“性别”区域，这时要按“F4”键把区域改为$C$3：$C$11绝对地址，在Criteria后输入“男”，在Sum_Range参数后点击“ ”按钮回到“成绩表”工作表中选择单元格区域E3：E11单元格区域，就是满足前两个条件的求E3：E11单元格区域的课程分数的和。 （3）选择E12单元格后的填充柄向后填充，就可以把剩下课程符合“性别”是“男”的各课程考试成绩的和求出来了。 SUMIF（）函数实际上就是SUM（）和IF（）两个函数共同作用的结果，是对满足条件的数据求和。,4.3.2 任务二 应用函数,4.3.2 任务二 应用函数,9LEFT（）文本函数： 功能：从一个文本字符串的第一个字符开始返回指定个数的字符 格式：LEFT（text,num_chare） text：要提取字符的字符串； num_chare：要LEFT提取的字符数，如果忽略则默认为1 下面通过实例来进行说明：,4.3.2 任务二 应用函数,10VLOOKUP（）查找函数 功能：VLOOKUP（）函数用来找出指定“数据范围”的最左列中符合“特定值”的数据，然后依据“索引值”返回第几个字段的值。 格式：VLOOKUP（Lookup-value,Table_array,Col_index_num,Range_lookup） Lookup-value：查找数据的条件依据； Table_array：查找数据范围； Col_index_num：指定返回范围中符合条件的那一列； Range_lookup：此为逻辑值，TRUE或省略，则找出部分符合值；FLASH。则会找出完全符合的值； 要求根据“成绩表”为了方便查询不同学生的成绩，要创建一个成绩查询表，让使用者只要输入学生学号就可以直接查询此学生的成绩。,4.3.2 任务二 应用函数,（2）进入“学生成绩查询”表，选中“C4”单元格，进入插入函数对话框，在“或选择类别”中选择“查找与引用”，再选择VLOOKUP函数以后进入函数参数对话框。 （3）在VLOOKUP函数的第一个参数里输入“C1”,点击第二个参数的“ ”按钮，回到“成绩表”工作表里选择整个学生成绩表数据区域“A2.L11”（包括字段名），回车后回到函数对话框，在第三个参数里输入“2”，第四个参数里输入“0”，按“确定”，如下图所示。,4.3.2 任务二 应用函数,（4）在查询表里“姓名”右边的“C4”单元格是“ ”，是个错误信息，是因为在C1单元格还没有输入“学号”，当你在C1单元格输入“成绩表”里第一个学生的学号就可以了。但是还不行，因为我们在“成绩表”里把“学号”数据类型改为了“文本”，所以才可以在前面加“00”，而在“学生成绩查询”里C1单元格的数据类型还是“数值”，你输入第一个的学号“00220301”后，前面的“00”没影了，所以还得把C1单元格改为“文本”才行，这时你再输入第一个人的学号，对应的姓名就会在C4单元格出现了。 （5）下一步就可以按住C4单元格的填充柄进行函数的填充了，对E4单元格可以采用“复制”|“粘贴”再接着填充，注意是函数填充。但你会发现全部都是错误信息。任意点击一个错误信息所在的单元格，看看函数的参数，你就会明白，范围错了，需要一个一个更改才行。 （6）首先要明白VLOOKUP函数到底是什么意思才行。VLOOKUP函数是按“列”查找，根据第一个参数“C1”就是输入的“学号”在第二个参数的范围内进行查找，第二个参数我们输入的是“成绩表”整个数据范围，在数据范围内第一列进行查找符合C1也就是“学号”的数据，自然找到第一个人的学号，然后根据找到的“学号”返回第三个参数的值，第三个参数我们输入的是“2”，就返回第一列找到值所在行第二列的值，