计算机基础案例解析指导教程案例6Excel2010表格处理.pptxVIP

Copyright 2013 计算机基础案例解析指导教程 计算机基础案例计算机基础案例 案例案例6 6 ExcelExcel文档编排文档编排 Copyright 2013 计算机基础案例解析指导教程 案例案例6 Excel6 Excel文档编排文档编排 l功能盘点 l应用聚焦 l应用解析 l操作准备 l具体实现 Copyright Copyright 20201313计算机基础案例解析指 导教程 3 lExcel 是由Microsoft公司开发设计的电子表格处理软件 ，是Microsoft Office 的系列组件之一，它是被公认的 Microsoft Office应用最广泛的中心组件。 lExcel提供了进行各种数据处理、统计分析和辅助决策 的操作环境和多种工具，广泛地应用于管理、财经、统计 、审计和金融等众多领域。 l在Excel中，可以应用丰富的公式和函数，结合多种灵 活方便的单元格引用方式，可以执行计算、分析信息并管 理电子表格。由于电子表格中的数据与数据之间，基本都 是通过公式和单元格引用等方式关联，所以，只要原始数 据改变了，其他所有与这个原始数据相关的单元格数据就 会自动同步做相应的改变，这使得用Excel管理的数据表格 真正的活起来！ 功能盘点 Copyright Copyright 20201313计算机基础案例解析指 导教程 4 功能盘点 工作薄 工作表 单元格 编辑：复制、粘贴（选 择性粘贴）、剪切、删 除、清除、查找、替 换、选定、填充、定位 等 格式：单元格格式、条 件格式、格式套用、单 元格样式、格式刷等 视图：普通视图、页面 布局、分页预览、自定 义视图、编辑栏、标 题、网格线、冻结、拆 分等 布局：主题、页边距、纸 张方向、纸张大小、分隔 符、背景、打印区域、打 印标题、对齐排列等 元素：工作表、单元 格、行、列、公式、函 数、名称、图片、图 表、对象、文本框、迷 你图等 数据：导入、记录单、 排序、筛选、分类汇 总、数据有效性、数据 透视图表、数据分析图 表等 审阅：保护工作薄、保 护工作表、共享工作 薄、批注、修订等 Copyright 2013 计算机基础案例解析指导教程 案例案例6 Excel6 Excel文档编排文档编排 l功能盘点 l应用聚焦 l应用解析 l操作准备 l具体实现 Copyright Copyright 20201313计算机基础案例解析指 导教程 6 l虽然Excel具有丰富的功能和广泛的应用，但是从 日常主流的应用来看，运用Excel操作的主要目标 却基本一致，即数据输入和格式设置、数据计算 和统计以及数据分析和管理等。 l也许不同应用的具体侧重有所不同，但基本都是 围绕着上面三类主要处理。比如：通讯录管理或 学生基本信息，就侧重于数据输入和格式管理； 学生成绩统计或商品销量利润统计，就侧重于数 据计算和统计；而商品销售汇总或学生成绩排名 分布图表分析，就侧重于数据分析和管理。 应用聚焦 Copyright Copyright 20201313计算机基础案例解析指 导教程 7 l关于数据输入和格式设置，需要考虑不同类型的 数据输入方法、数据的有效性和数据自动填充的 设置以及单元格格式、条件格式和样式格式套用 的设置方法等。 l关于数据计算和统计，需要考虑公式的运用，其 中涉及各种运算符的使用、单元格引用方式以及 各种函数的使用等。 l关于数据分析和管理，需要考虑数据的排序、筛 选、分类汇总以及各种图表的制作等。 应用聚焦 Copyright Copyright 20201313计算机基础案例解析指 导教程 8 应用聚焦 Copyright Copyright 20201313计算机基础案例解析指 导教程 9 应用聚焦 Copyright Copyright 20201313计算机基础案例解析指 导教程 10 应用聚焦 Copyright Copyright 20201313计算机基础案例解析指 导教程 11 应用聚焦 操 作 步 骤 命名文件名 存储在指定文件夹 插入工作表并命名工作表名 新建工作薄 选中单元格区域，插入表格以建立数据清单框架结构 输入数值信息、输入文本信息、输入日期信息 建立数据有效性下拉列表和数据填充自定义序列 输入数据 普通视图、页面布局、分页预览、全屏显示 网格线、编辑栏、标题 拆分窗格、冻结窗格 视图显示 公式、运算符 单元格引用（相对引用、绝对应用、混合应用、三维引用） 函数（常用、统计、财务、逻辑、文本、日期、数学和三角函数、查找与引用、数据库等） 计算和统计 排序（单关键字、多关键字） 自动筛选、高级筛选、分类汇总 图表、数据透视表、数据透视图 分析与管理 单元格格式（数字、对齐、字体、边框、填充、保护） 条件格式（突出显示满足规则的单元格、数据条、色阶、图标集） 套用样式（表格样式、单元格样式） 设置格式 主题（颜色、字体和效果）、背景图片 页面设置（页边距、纸张大小、纸张方向、打印区域） 页眉页脚、图片、形状、smartart等对象的插入、叠放和组合 页面装饰 Copyright 2013 计算机基础案例解析指导教程 案例案例6 Excel6 Excel文档编排文档编排 l功能盘点 l应用聚焦 l应用解析 l操作准备 l具体实现 Copyright Copyright 20201313计算机基础案例解析指 导教程 13 l确定工作表数目及内容 l学生office系列作品比赛的“office作品成绩管理”， 涉及到各类基本信息的输入、格式设置以及对成绩 的各种计算和汇总、分析和管理。按照每张工作表 存放一类数据信息来处理，本案例共需要12张工作 表。 l基础数据、学生信息、WORD成绩、EXCEL成绩、 POWERPOINT成绩、各作品汇总成绩、按专业分 类汇总、排名前二十专业分布情况、排名前十名单 、部分学生成绩比较分析、筛选满足条件的信息、 排名前十的学生成绩及专业分布数据透视图表。 应用解析定位 Copyright Copyright 20201313计算机基础案例解析指 导教程 14 l数据信息的输入和格式设置 l对于各类数据的输入，需要考虑不同数据类型 的特点，如数值类型、文本类型和日期类型的 不同输入方式。 l同时，为了确保数据输入的准确性，可以设置 数据输入的有效性验证或下拉列表以及对频繁 出现的数据进行自动填充序列的设置。 l另外，对于数据格式的设置，可以通过单元格 格式设置、条件格式设置和套用样式格式等。 应用解析定位 Copyright Copyright 20201313计算机基础案例解析指 导教程 15 l数据计算和统计 l在本案例中，多张数据表中的数据需要通过计算和 统计的公式得出，其中包括各种运算符的运用、单 元格引用、各种函数等，如：求和、计数、求平均 值、求最大最小值、逻辑判断、日期运算、自动查 找运算、小数位数的舍入运算以及排名统计等。 l数据分析管理 l在本案例中，多张数据表需要运用排序、分类汇总 、高级筛选以及图表分析和数据透视图或透视表分 析等，实现对数据的分析和管理。 应用解析定位 Copyright Copyright 20201313计算机基础案例解析指 导教程 16 应用解析规划 案例实现的规划方案 建立工作薄 建立原始数据 工作表 基础数据 学生信息 Word成绩 Excel成绩 PowerPoint成 绩 输入数据 数值信息 文本信息 日期信息 数据有效性 数据填充 设置单元格格 式 单元格格式 条件格式 表格样式格式 单元格样式格 式 统计数据 建立统计数据 工作表 公式、运算 符、单元格引 用、函数 计算学生出生 年月和年龄 计算单项成绩 平均分 计算汇总成绩 平均分、排名 管理数据 建立管理数据 工作表 排序 筛选 汇总 分析数据 建立分析数据 工作表 普通图表 数据透视表或 图 Copyright Copyright 20201313计算机基础案例解析指 导教程 17 l关于数据输入 l对于学生信息工作表中专业的输入，可以通过建立数据有 效性的下拉列表，在确保数据正确性的同时还方便输入。 l对于多次出现的评审教师姓名的输入，可以通过建立数据 的自定义序列，实现自动填充。 应用解析技巧 Copyright Copyright 20201313计算机基础案例解析指 导教程 18 l关于格式设置 l对于学生作品的平均成绩，需要突出显示成绩的高低，可 以通过条件格式的数据条，用不同颜色标识不同成绩状态，成 绩高低一目了然。 l对于学生的成绩排名，也同样可以通过条件格式的图标集 ，用不同的图标体现排名状态。 l对于案例要求突出标识“单项分值均超过单项平均值”的 学生，可以通过条件格式的突出显示单元格规则，指定规则条 件和突出显示格式，即可将满足条件的数据项突出显示。 应用解析技巧 Copyright Copyright 20201313计算机基础案例解析指 导教程 19 l关于成绩汇总 l将分别在三个工作表中的三项成绩（Word、Excel和 PowerPoint）集中汇总到一个工作表中，需要运用查找函数 Vlookup来查找和关联对应学生的各项成绩，这样即使数据源 发生变化，关联的数据也会同步改变。而简单地复制或者输入 却无法达到联动的效果。 应用解析技巧 Copyright Copyright 20201313计算机基础案例解析指 导教程 20 l关于数据筛选和汇总 l案例要求筛选出Word成绩超过90分、总平均成绩超过80分 的“金融”专业的男生，可以通过高级筛选来实现，但必须事 先设置筛选条件单元格区域。 l对于生成排名前二十的各个专业的人数分布情况饼图，必 须首先进行按专业的分类汇总，而分类汇总的第一步是先按专 业进行排序，然后才成按专业分类汇总计数。 应用解析技巧 Copyright Copyright 20201313计算机基础案例解析指 导教程 21 l关于数据图表分析 l在Excel中，对数据的图表分析有多种选择，可以选择 普通的图表（二维呈现），也可以选择数据透视图表（三维 呈现）。生成图表的数据源可以是连续的区域，也可以是多 个不连续的区域。另外，Excel2010还增加了迷你图和切片器 的功能。 应用解析技巧 Copyright 2013 计算机基础案例解析指导教程 案例案例6 Excel6 Excel文档编排文档编排 l功能盘点 l应用聚焦 l应用解析 l操作准备 l具体实现 Copyright Copyright 20201313计算机基础案例解析指 导教程 操作准备 l概述 l数据输入 l单元格编辑 l格式设置 l创建名称 l公式概述 l单元格的引用 l函数 l排序 l筛选 l汇总 l图表分析 23 Copyright Copyright 20201313计算机基础案例解析指 导教程 24 l建立工作簿并添加工作表 l工作簿是用于存储并处理数据的文件，其扩展名为 .Xlsx。启动Excel后，会自动打开一个包含3张空白工 作表的空白工作簿，其默认名为工作薄1。 l一个工作簿中可以包含多张工作表sheet，可以单击工 作表右侧的“插入工作表” 图标新建工作表，或者右 击已有工作表名增添更多的工作表或对工作表改名。 操作准备概述 Copyright Copyright 20201313计算机基础案例解析指 导教程 25 l建立工作簿并添加工作表 l每个工作表由（214）16384列和（220）1048576行组 成，每个网格称为一个单元格，每个单元格的名字 由其所在的行号和列标表示。 l单元格是Excel存储数据的基本单位，在工作表中单 击某个单元格，此单元格的边框行号和列标都突出 显示，称为活动单元格，是当前操作的对象。 操作准备概述 Copyright Copyright 20201313计算机基础案例解析指 导教程 操作准备 l概述 l数据输入 l单元格编辑 l格式设置 l创建名称 l公式概述 l单元格的引用 l函数 l排序 l筛选 l汇总 l图表分析 26 Copyright Copyright 20201313计算机基础案例解析指 导教程 27 l单击相应的单元格，就可以输入数字、文字、日 期等类型的数据。 l按Enter键或Tab键，可以对下一行或同行的下一 单元格继续输入数据。 l输入的信息将同时显示在编辑栏和活动单元格中 。数值和日期类型的数据自动向右对齐，文本类 型的数据自动向左对齐。 l通过自定义填充序列和数据有效性下拉列表，可 以实现快速输入数据的目的。 操作准备数据输入 Copyright Copyright 20201313计算机基础案例解析指 导教程 28 l数据输入技巧 l特殊数据输入 l输入分数 l输入负数 l输入文本类型的数字 l输入特殊字符 l快速输入大写中文数字 l自动超链接的处理 l取消自动超链接 l关闭自动超链接 l取消多个的超链接 操作准备数据输入 Copyright Copyright 20201313计算机基础案例解析指 导教程 29 l数值型数据的输入 l数值型的数据有以下几种形式：正数表示为1.23 ，负数可表示为-1.23或（123）; l货币表示为$56.78，百分比表示为50%; l科学记数法表示为3.14E+15（相当于3.14*1015， 通常用于表示超出精度的实数，精度为15位数）; l分数表示为5 1/4（相当于5又4分之1，5与1/4之 间要加一个空格），纯分数表示为0 1/3（0与1/3 之间加空格，以便与日期型数据相区别）。 操作准备数据输入 Copyright Copyright 20201313计算机基础案例解析指 导教程 30 l文本数据的输入 l文本数据可以直接输入，Excel会自动判断输入数 据的类型。 l但如果输入的文本完全由数字组成，那么必须先 输入半角单引号“”，如057187658978，否 则系统会将其视为数值，将隐去数值前的0，而 且如果数值位数超出精度表示范围，会自动转换 为科学记数法表示。 操作准备数据输入 Copyright Copyright 20201313计算机基础案例解析指 导教程 31 l日期和时间数据的输入 l输入日期时，要用斜杠（/）或减号（-）分隔年 、月、日，如“2013/07/02”或“2013-9-17”等。 l输入时间时，则要用冒号（：）分隔时、分、秒 ，如“9：30 pm”、“21：30：10”等。 操作准备数据输入 Copyright Copyright 20201313计算机基础案例解析指 导教程 32 l自定义下拉列表输入 l作用 l使用自定义下拉列表的方式进行数据的输入，可以 提高数据输入的速度和准确性。 l建立方法 l数据数据有效性“数据有效性” l普通方法设置：直接键入列表选项 l使用名称设置：使用名称建立列表选项 操作准备数据输入 Copyright Copyright 20201313计算机基础案例解析指 导教程 l自定义下拉列表输入 操作准备数据输入 Copyright Copyright 20201313计算机基础案例解析指 导教程 34 l自定义序列与填充柄 l自定义序列：一组数据，可按重复方式填充列。 l创建自定义序列 l文件选项高级常规编辑自定义列表 l利用工作表中现有的数据项 l采用临时输入的方式 l自定义序列可以包含文字或带数字的文本。如果 是包含数字，则需要进行格式的设置，之后才能 在单元格中输入序列项，选择列表并导入列表。 l创建好自定义序列之后，采用填充柄进行填充能 达到快速、有效的结果。 l 操作准备数据输入 Copyright Copyright 20201313计算机基础案例解析指 导教程 操作准备数据输入 l自定义序列与填充柄 Copyright Copyright 20201313计算机基础案例解析指 导教程 操作准备数据输入 l自定义序列与填充柄 Copyright Copyright 20201313计算机基础案例解析指 导教程 操作准备 l概述 l数据输入 l单元格编辑 l格式设置 l创建名称 l公式概述 l单元格的引用 l函数 l排序 l筛选 l汇总 l图表分析 37 Copyright Copyright 20201313计算机基础案例解析指 导教程 38 l编辑输入 l双击要编辑的单元格，鼠标会变成一根竖杠，称 为插入点，此时便可对其内容进行编辑。 l或单击单元格，并单击编辑栏，即将插入点定位 在编辑栏，也同样可以对其内容进行修改。 操作准备编辑单元格 Copyright Copyright 20201313计算机基础案例解析指 导教程 39 l区域选择 l单击单元格可以选中单元格； l单击行号可以选择一行（按住鼠标左键拖动鼠标可 以选中连续的多行）； l单击列标可以选择一列（按住鼠标左键拖动鼠标可 以选中连续的多列）； l单击行号和列标的交叉处，可以选中整张工作表。 操作准备编辑单元格 Copyright Copyright 20201313计算机基础案例解析指 导教程 40 l区域选择 l单击单元格并按住鼠标左键拖动鼠标可以选中一块单 元格区域，如果在重复上述操作的同时按住Ctrl键，则 可以选中不同的单元格区域，被选中的单元格区域将 突出显示。 l单击一个单元格，按住Shift键，再单击另一个单元格 ，可以选中以两个单元格为区域对角点的连续区域。 操作准备编辑单元格 Copyright Copyright 20201313计算机基础案例解析指 导教程 41 l移动复制 l对选中的单元格区域，点击右键打开快捷菜单，可对 选中的单元格区域进行移动、复制、清除等操作。 l也可以将鼠标移到被选中区域的边缘，当鼠标变成 形 状后，拖动鼠标到指定位置来完成单元格区域的移动 操作，如果在拖动鼠标的同时按住Ctrl键，则可以实 现单元格区域的复制操作。 操作准备编辑单元格 Copyright Copyright 20201313计算机基础案例解析指 导教程 42 l清除与删除 l清除单元格是指清除其内容、格式、批注或超 链接，单元格依然存在； l而删除单元格则意味着剔除此单元格，将其右 面的单元格左移或下面的单元格上移来填补空 缺。 操作准备编辑单元格 Copyright Copyright 20201313计算机基础案例解析指 导教程 43 l粘贴与选择性粘贴 l由于excel单元格中的数据存在多种特征，比如： 被设置为特定的格式或者由公式组成等，所以在 复制粘贴时，可以通过“选择性粘贴”来选择粘 贴的内容，可以只保留数值、保留格式、保留链 接或者保留所有。 操作准备编辑单元格 Copyright Copyright 20201313计算机基础案例解析指 导教程 操作准备 l概述 l数据输入 l单元格编辑 l格式设置 l创建名称 l公式概述 l单元格的引用 l函数 l排序 l筛选 l汇总 l图表分析 44 Copyright Copyright 20201313计算机基础案例解析指 导教程 45 l单元格格式 l通过调整行号或列标的尺寸，或通过“开始”选 项卡的“单元格”组，选择“格式”下拉菜单中 的行高和列宽选项，可以改变选中区域中单元格 的行高和列宽。 l同样在“格式”下拉菜单中选择“设置单元格格 式”选项，或者在右键快捷菜单中选择“设置单 元格格式”选项，都可以对选中的单元格区域的 多项格式。 操作准备格式设置 Copyright Copyright 20201313计算机基础案例解析指 导教程 46 l单元格格式 l设置数据格式：可设置数值格式、货币格式、日 期格式、时间格式、分数格式、文本格式、分数 格式、百分比格式及自定义格式等； l设置对齐方式：可设置水平或垂直对齐、文字方 向以及合并或拆分单元格区域等； l另外还可以对字体、边框和填充以及保护工作表 设置。 操作准备格式设置 Copyright Copyright 20201313计算机基础案例解析指 导教程 47 l条件格式 l在Excel中提供了一个功能非常独特的数据管理功 能条件格式。该功能可以让单元格根据不同 的应用环境所设置的条件发生变化。 l作用：通过设置数据条件格式，可以让单元格中 的数据满足指定条件时就以特殊的标记（如：以 红色、数据条、图标等）显示出来。 l关于条件格式的设置主要通过： l开始样式条件格式新建（管理）规则 l条件格式标记包含格式色彩、数据条、色阶和图标 集等 操作准备数据输入 Copyright Copyright 20201313计算机基础案例解析指 导教程 48 l条件格式 l通过“开始”选项卡的“样式”组，在“条件格式” 的下拉菜单中，可以选择多种形式的条件格式。 l突出显示单元格规则：指定条件和格式，就可以让选 中单元格的数据在满足指定条件时，以指定的格式突 出显示。单元格内容可以是数值类型、文本类型和日 期类型。 操作准备格式设置 Copyright Copyright 20201313计算机基础案例解析指 导教程 49 l条件格式 l项目选取规则：指定对单元格按值的大小排名的 首尾筛选条件以及格式，就可以让选中单元格的 数据在满足筛选条件时，以指定的格式突出显示 。单元格内容可以是数值类型和日期类型。 l数据条、色阶和图标集：基于选中单元格的现有 值，按照彼此值的大小对比，设置长短不同的色 彩数据条、不同颜色的色阶或者不同形式的图标 来显示所有单元格。单元格内容可以是数值类型 和日期类型。 操作准备格式设置 Copyright Copyright 20201313计算机基础案例解析指 导教程 操作准备数据输入 l条件格式 Copyright Copyright 20201313计算机基础案例解析指 导教程 l条件格式 操作准备数据输入 Copyright Copyright 20201313计算机基础案例解析指 导教程 l条件格式 l还可以在单 元格中设置 彩色的数据 条（或色阶 、图标集） ，以数据条 （色阶、图 标集）的长 度来表示数 值的大小 操作准备数据输入 Copyright Copyright 20201313计算机基础案例解析指 导教程 53 l套用样式 l通过“开始”选项卡的“样式”组，在“套用表 格样式”或者“单元格样式”的下拉列表中，可 以套用系统内置样式，快速地装饰选中区域的单 元格的边框、底纹和字体等格式。 操作准备格式设置 Copyright Copyright 20201313计算机基础案例解析指 导教程 操作准备 l概述 l数据输入 l单元格编辑 l格式设置 l创建名称 l公式概述 l单元格的引用 l函数 l排序 l筛选 l汇总 l图表分析 54 Copyright Copyright 20201313计算机基础案例解析指 导教程 55 l创建名称及其使用 l在Excel中，可以通过一个名称来代表工作表、单元 格、常量、图表或公式等。 l创建方法：公式定义名称定义名称 l如果在Excel中定义一个名称，就可以在公式中直接 使用它。 操作准备创建名称 Copyright Copyright 20201313计算机基础案例解析指 导教程 l创建名称及其使用 操作准备创建名称 Copyright Copyright 20201313计算机基础案例解析指 导教程 操作准备 l概述 l数据输入 l单元格编辑 l格式设置 l创建名称 l公式概述 l单元格的引用 l函数 l排序 l筛选 l汇总 l图表分析 57 Copyright Copyright 20201313计算机基础案例解析指 导教程 58 l公式概述 l公式就是对工作表中的数值进行计算的式子。 l公式由操作符和运算符两个基本部分组成。 l操作符可以是常量、名称。数组、单元格引用和函数 等。 l运算符用于连接公式中的操作符，是工作表处理数据 的指令。 l公式元素 l运算符、单元格引用、值或字符串、函数其参数、括 号等。 操作准备公式概述 Copyright Copyright 20201313计算机基础案例解析指 导教程 59 l公式的概述 l运算符 l算术运算符 、逻辑运算符 、文本运算符 、引用运算符 l运算符的优先级 l在公式中，每个运算符都有一个优先级。对于不同优先 级的运算，按照从高到低的优先级顺序进行计算。对于 同一优先级的运算，按照从左到右的书讯进行计算。 操作准备公式概述 Copyright Copyright 20201313计算机基础案例解析指 导教程 60 l公式的概述 l数学运算符： l加（+），减（-），乘（*），除（/），百分号（%） ，乘方（）； l比较运算符： l=，=，（不等于）； l文字运算符： l&（连接字符）； l引用运算符： l冒号“：”（连续区域引用）、逗号“，”（离散区 域引用）、空格“ ”（交叉区域引用）、感叹号“！ ”（三维区域引用）。 操作准备公式概述 Copyright Copyright 20201313计算机基础案例解析指 导教程 操作准备 l概述 l数据输入 l单元格编辑 l格式设置 l创建名称 l公式概述 l单元格的引用 l函数 l排序 l筛选 l汇总 l图表分析 61 Copyright Copyright 20201313计算机基础案例解析指 导教程 62 l单元格的引用 l相对引用 l绝对引用 l混合引用 l三维引用 l循环引用 操作准备单元格引用 Copyright Copyright 20201313计算机基础案例解析指 导教程 63 l单元格的引用 l相对引用 l总是以当前单元格位置为基准，在复制公式时，当前单 元格改变了，在单元格中引入的地址也随之发生变化。 l相对地址引用的表示是，直接写列字母和行号，如A1， D8等。 l绝对引用 l在复制公式时，不想改变公式中的某些数据，即所引用 的单元格地址在工作表中的位置固定不变，它的位置与 包含公式的单元格无关 l绝对地址引用的表示是，在相应的单元格地址的列字母 和行号前加“$”符号 l 操作准备单元格引用 Copyright Copyright 20201313计算机基础案例解析指 导教程 64 l单元格的引用 l混合引用 l公式中参数的行采用相对引用、列采用绝对引用， 或者列采用相对引用、行采用绝对引用，如$A1、 A$1 。 l三维引用 l引用工作簿中多个工作表的单元格 l三维引用的一般格式为：工作表标签！单元格引用 l循环引用 l一个公式直接或者间接引用了自己的值，即出现循 环引用 操作准备单元格引用 Copyright Copyright 20201313计算机基础案例解析指 导教程 操作准备 l概述 l数据输入 l单元格编辑 l格式设置 l创建名称 l公式的概述 l单元格的引用 l函数 l排序 l筛选 l汇总 l图表分析 65 Copyright Copyright 20201313计算机基础案例解析指 导教程 66 lExcel提供了功能丰富的函数。所谓函数就是系统已经编程 实现的某种功能操作。函数包括函数名和函数参数。 l如果公式中要使用函数，可以直接输入函数名和参数。也 可以通过以下方式编辑函数： l通过“公式”选项卡中的“函数库”组，单击组中的某类函数选 项，在其下拉列表中选择所需的函数，通过“函数参数”对话框 ，编辑参数。 l通过“公式”选项卡中的“函数库”组，单击“自动求和” 选 项，可以选择求和、平均值、计数、最大值、最小值以及其他函 数的计算。 l通过“公式”选项卡中的“函数库”组，单击“插入函数” 选 项，可以打开“插入函数”对话框，同样可以实现插入和编辑函 数。 操作准备函数 Copyright Copyright 20201313计算机基础案例解析指 导教程 67 操作准备函数 语 法作 用 SUM（num1，num2，）计算一组数值的总和 AVERAGE（num1，num2，）计算一组数值的平均值 COUNT（num1，num2，）计算出某区域内数值的个数 MAX/ MIN（num1，num2，）计算一组数值的最大值或最小值 IF（logical_test，value1，value2 ） 逻辑表达式为真，返回value1的值，否则返回value2的值。 AND/OR（logical1，logical2，. ） 多个逻辑式是与（AND）或者是或（OR）的运算关系 NOT（logical）逻辑非运算 SUMIF（range，criteria， sum_range） 计算符合指定条件的单元格和值 COUNTIF（range,criteria）计算某区域内满足条件的数值个数。Range：为需要计算数 目的单元格区域。Criteria：为确定条件，其形式可以为数字、 表达式、单元格引用或文本等。 RANK (number, ref, order) RANK.EQ (number, ref, order) 在指定区域中针对 某一个数的最佳排名。Number 为需要排 名的数字，Ref 为包含一组数字的数组或引用，Ref 中的非数 值型参数将被忽略，Order 为一数字，指明排名的方式（升序1 或降序0） Copyright Copyright 20201313计算机基础案例解析指 导教程 68 操作准备函数 TODAY（）/NOW（）返回系统当前日期或日期时间 DATE（year，month，day）返回由给定的年、月、日数值组成的日期 YEAR（date）、MONTH（date ）、DAY（date） 返回对应日期的数值型年份、月份和日数 HOUR（time）、MINUTE（time ）、SECOND（time） 返回对应时间的数值型小时、分钟和秒数 ABS（number）返回指定数值的绝对值 MOD（num1，num2）计算两个整数相除的余数 INT（number）截尾取不超过这个数的最大整数 SIN（number）/COS（number）返回指定弧度角度的正弦值/余弦值 PI（）返回圆周率的值 EXP（number）返回e的幂次值 FACT（number）返回指定整数的阶乘值 LOG（number，base）/LN（ number） 返回指定底数的整数对数值/自然对数值 SQRT（number）返回数值的平方根值 ROUND（number，num_digits）返回指定小数位数的四舍五入数值 Copyright Copyright 20201313计算机基础案例解析指 导教程 69 操作准备函数 LEFT（text，num_chars） RIGHT（text，num_chars） 返回从左边/右边截取指定个数长度的指定文本子串 MID（text，start_num，num_chars）返回指定位置截取指定个数长度的指定文本子串 LEN（text）返回指定文本包含字符的个数 REPLACE（old_text，start_num， num_chars，new_text） 返回用新文本串替换指定位置和指定个数的旧文本串之 后的文本 VLOOKUP（lookup_value, talbe_array, col_index_num, range_lookup） 可以在一个表格或指定的区域的最左列中查找某一指 定的值，并由此返回该值对应 当前行中指定列的数值。 参数lookup_value是要在表格第一列中要搜索的值，参 数table_array是要搜索的数据表格，col_index_num代表 所要返回的值位于table_array列中的第几列， range_lookup是个逻辑值 ，如果其值为 “TRUE”或被省略 ，则返回部分符合的数值，如果为“FALSE”,VLOOKUP 函数只会查找完全符合的数值。 Copyright Copyright 20201313计算机基础案例解析指 导教程 70 操作准备函数 DCOUNT（database，field，criteria）返回列表或数据库中满足指定条件的记录 字段（列） 中包含数值的单元格的个数。 database：构成数据清单或数据库的单元格区域 ； field：指定函数所使用的数据列； criteria：一组包含给定条件的单元格区域。 DGET（database，field，criteria）从列表或数据库的列中提取符合指定条件的单个值。 DMAX（database，field，criteria）返回列表或数据库的列中满足条件的最大值。 DSUM（database，field，criteria）返回列表或数据库中满足指定条件的记录字段（列） 中的数字之和。 DAVERAGE（database，field，criteria）计算列表或数据库的列中满足指定条件的数值的平均 值。 Copyright Copyright 20201313计算机基础案例解析指 导教程 71 l统计函数 lSUM函数、SUMIF函数 lAVERAGE函数、AVERAGEIF函数 lCOUNT函数、COUNTIF函数 lRANK.EQ函数、RANK.AVG函数 操作准备函数介绍 Copyright Copyright 20201313计算机基础案例解析指 导教程 72 lSUM函数的应用 l功能：该函数是返回指定参数所对应的数值之和。 l格式：SUM(number1,number2,.) l参数：number1，number2等这些是指定所要进行求 和的参数，参数类型可以是数字、逻辑值和数字的 文字表示等形式。 操作准备函数 Copyright Copyright 20201313计算机基础案例解析指 导教程 73 lSUMIF函数的应用 l功能：该函数是用于计算符合指定条件的单元格区 域内的数值进行求和。 l格式：SUMIF(range, criteria, sum_range) l参数： lrange表示的是条件判断的单元格区域； lcriteria表示的是指定条件表达式； lsum_range表示的是需要计算的数值所在的单元格区域。 如果省略 sum_range 参数，Excel 会对在范围参数中指 定的单元格（即应用条件的单元格）求和。 操作准备函数 Copyright Copyright 20201313计算机基础案例解析指 导教程 74 lSUMIF函数的应用 操作准备函数 Copyright Copyright 20201313计算机基础案例解析指 导教程 75 lAVERAGE函数的应用 l功能：该函数是返回指定参数所对应数值的算术 平均数。 l格式：AVERAGE(number1, number2, .) l参数：number1，number2等这些是指定所要进行 求平均值的参数。该函数只对参数的数值求平均 数，如区域引用中包含了非数值的数据，则 AVERAGE不把它包含在内。 操作准备函数 Copyright Copyright 20201313计算机基础案例解析指 导教程 76 lAVERAGE函数的应用 l例如：A1：A4中分别存放着数据14，如果输 入在A5中输入“AVERAGE（A1：A4，10） ”，则A5中的值为4，即为（123410 ）/5。 l但如果在上例中的A2和A3单元格分别输入了文 本，比如“语文”和“英语”，则A5的值就变 成了5，即为（1410）/3，A2和A3虽然包 含在区域引用内，但并没有参与平均值计算。 操作准备函数 Copyright Copyright 20201313计算机基础案例解析指 导教程 操作准备函数 lAVERAGE函数的应用 Copyright Copyright 20201313计算机基础案例解析指 导教程 78 lAVERAGEIF函数 lAVERAGEIF函数返回某个区域内满足给定条件 的所有单元格的平均值（算术平均值）。 操作准备函数 Copyright Copyright 20201313计算机基础案例解析指 导教程 lCOUNT类函数 lCOUNTA函数返回参数组中非空值的数目； lCOUNTBLANK函数计算某个单元格区域中空白 单元格的数目； lCOUNTIF函数计算区域内符合给定条件的单元格 的数量； lCOUNTIFS函数计算区域内符合多个条件的单元 格的数量。 操作准备函数 Copyright Copyright 20201313计算机基础案例解析指 导教程 lCOUNT函数是用于返回数值参数的个数，即统计数 组或单元格区域中含有数值类型的单元格个数。 操作准备函数 Copyright Copyright 20201313计算机基础案例解析指 导教程 lCOUNTIF函数 lCOUNTIF函数计算区域内符合给定条件的单元格的数量 操作准备函数 Copyright Copyright 20201313计算机基础案例解析指 导教程 lRANK.EQ函数 l功能：RANK.EQ函数的功能是返回一个数值在一组 数值中的排位，如果多个值具有相同的排位，则返回 该组数值的最高排位。 l格式：RANK.EQ( number,ref,order) l参数： lNumber需要排名的数据 lRef排名范围（列表数组或引用） lOrder排名升降序方式，取0或缺省，为降序，取1为 升序。 操作准备函数 Copyright Copyright 20201313计算机基础案例解析指 导教程 lRANK.EQ函数 操作准备函数 Copyright Copyright 20201313计算机基础案例解析指 导教程 lRANK.AVG函数 l另外还有RANK.AVG函数，也是返回一个数字在数字 列表中的排位，数字的排位是其大小与列表中其他值 的比值；如果多个值具有相同的排位，则将返回平均 排位。在前面的例子中，RANK.AVG(A4,A1:A5,1)等 于3.5。 lExcel以前版本中的RANK函数现在被归类在兼容性函 数中，其功能同RANK.EQ函数。 操作准备函数 Copyright Copyright 20201313计算机基础案例解析指 导教程 85 l逻辑判断函数 lIF函数 lAND函数 lOR函数 lNOT函数 操作准备函数介绍 Copyright Copyright 20201313计算机基础案例解析指 导教程 86 lIF函数的应用 l功能：IF函数是一个条件函数。能为对满足条件的 数据进行处理，条件满足则输出value _if _ true，不 满足则输出value _if _ false。 l格式： IF（logical _ test，value _if _ true，value _if _ false） 操作准备函数 Copyright Copyright 20201313计算机基础案例解析指 导教程 87 lIF函数的应用 l参数： lLogical _ test：函数的逻辑条件式 lValue _if _ true：当值为“真”时的返回值 lValue _if _ false：当值为“假”时的返回值 l注意：在IF函数中三个参数中可以省略value _if _ true或value _if _ false，但不能同时省略 l在IF函数中使用嵌套函数，最多可用嵌套7层 操作准备函数 Copyright Copyright 20201313计算机基础案例解析指 导教程 88 lAND函数的应用 lAnd(logical_test ,logical_test ,logical_test,) lLogical _ test：函数的逻辑条件式 l所有条件式的值为真，结果为真 lOR函数的应用 lor(logical_test ,logical_test ,logical_test,) lLogical _ test：函数的逻辑条件式 l只要有一个条件式的值为真，结果为真 lNOT函数的应用 lNot(logical_test) lLogical _ test：函数的逻辑条件式 l条件式值为真，结果为假；条件式值为假，结果为真 操作准备函数 Copyright Copyright 20201313计算机基础案例解析指 导教程 lIF函数的应用 操作准备函数 Copyright Copyright 20201313计算机基础案例解析指 导教程 lIF函数的应用 操作准备函数 Copyright Copyright 20201313计算机基础案例解析指 导教程 91 l文本处理类函数 lREPLACE函数 lMID函数 lLEFT函数 lRIGHT函数 操作准备函数介绍 Copyright Copyright 20201313计算机基础案例解析指 导教程 92 lREPLACE函数 l功能：将某几位的文字以新的字符串替换。其 替换功能与SUBSTITUTE函数大致类似 l格式： REPLACE（old_text，start_num，num_chars，new_text） 操作准备函数 Copyright Copyright 20201313计算机基础案例解析指 导教程 93 lREPLACE函数 l参数： lold_text：原始的文本数据 lstart_num：设置要从old_text的第几个字符位置开 始替换 lnum_chars：设置共有多少字符要被替换 lnew_text：要用来替换的新字符串 l例: l=REPLACE(“你好!同学好!老师好!“,4,6,“大家“) l值为: “你好!大家好” 操作准备函数 Copyright Copyright 20201313计算机基础案例解析指 导教程 94 lMID函数 l功能：用来返回在指定字符串中从指定位置开始 截取指定数目的字符串。 l格式： MID（text，start_num，num_chars） l参数： ltext：文本字符串 lstart_num：从text的第几个字符开始截取 lnum_chars：截取字符串的个数 l例： lMID(“你好!同学好!老