2010Excel培训教程.ppt

1 Excel培训教程 上 2 开始语 Excel是MicrosoftOffice软件包的组成部分 按启动Windows应用的方法运行它后 屏幕上就会显示其操作窗口 并且打开一张名为Book1 工作薄1 的电子报表 如图所示 标题栏 下拉菜单 工具栏 状态栏 工作表区 列标 行号 滚动条 工作表标签 单元格 3 建立第一份电子报表 工作簿 在MicrosoftExcel中是处理和存储数据的文件 每一个工作簿都可以包含多张工作表 因此可在一份文件中管理多种类型的相关信息 工作表 就是显示在屏幕上的 由表格组成的一个区域 此区域称为 工作表区 各种数据将通过它来输入显示 下面将以建立一张员工工资表的操作来说明Excel的一些基本操作 步骤一 参见右图 单击A1单元格 工作表提供了一系列的单元格 各单元格也各有一个名称 将光标移至单元格内后 其状态将变成一个十字形 4 步骤二 输入 姓名 参见下图 按一下键盘上的右方向键 然后输入 年龄 接着在位于右旁的两列中分别输入 职务 工资额 使用键盘上的左 右 上 下方向键可以将光标移至各单元格上 这就选定了当前单元格 从而让您在各单元格中输入文字信息 5 步骤四 移动鼠标单击A2单元格 然后参照上面的内容 输入其它单元格中的内容 结果参见下图 完成上述操作后 员工工资表 就编制好了 如图所示 在这一份电子表格中 所包含的员工数目有限 但说明了基本的操作方法 中文Excel关闭当前操作窗口时不会自动保存操作结果 因此必须先执行 文件 下拉菜单中的 保存 命令 否则会出现 是否保存 的提示 6 设置单元格的格式与MicrosoftWord一样 在Excel中也能设置文字的格式 而且此格式将表现在最终的电子表格中 此外 还可以设置其它与数据信息相关的属性 下面的操作将结合 员工工资表 的特点来进行说明 请先单击左下方的Sheet1标签 返回 员工工资表 步骤一 单击行号1 选定此行中的文字内容 然后右击鼠标器 进入图12所示的快捷菜单 并从中选择 设置单元格格式 命令 7 步骤二 进入 单元格格式 对话框后 单击 字体 选项卡 如图所示 步骤三 从 字体 窗口中选择一种新的字体后 从 字形 窗口中选择一种字形 接着可在 字号 窗口中设置文字的大小尺寸 此后 您还可以在 字体 选项卡中设置文字的颜色 以及下划线 删除线等属性 还能预览设置结果 单击 确定 按钮后 选定文字就将按新设置的字体格式显示在电子报表中 8 设置货币符号步骤一 单击D列号 选定此列中的所有内容 接着右击鼠标右键 然后从快捷菜单中选择 设置单元格格式 命令 进入 单元格格式 对话框后选择 数字 选项卡 参见下图 步骤二 在 分类 列表窗中选择 货币 项 步骤三 设置好 小数位数 与 货币符号 然后单击 确定 按钮 9 加入标题为了加入标题 首先需要在报表将插入一行 以便于书写它 其次还要合并此行中的各列 步骤一 单击行号1 然后从 插入 下拉菜单中选择 行 命令 步骤二 单击行号1 确保选定新行 然后右击鼠标进入快捷菜单 接着从中选择 设置单元格格式 命令 然后在 单元格格式 对话框中选择 对齐 选项卡 参见右图 打开 合并单元格 检测框 最后单击 确定 按钮 步骤三 在 行1 书写好标题 员工工资表 并参照前面的内容 设置好标题的文字字体与大小尺寸 10 添加 总计 单元格Excel提供有各种用于计算的函数 其中SUM将用于 总计 计算指定列中所有数值的和 请按下列步骤进行操作 步骤一 单击在 工资 列最后的第一个空白单元格 接着从 插入 下拉菜单中选择 函数 命令 参见左图 在 函数名 列表窗中选定某个函数后 它的功能描述字将显示在 函数分类 列表窗的下方 您可以据此了解到这个所提供的各函数功能 操作时 可先从 函数分类 列表窗中选定某类函数 然后从 函数名 列表窗中选择此类函数的某一个 11 步骤二 在 粘贴函数 对话框的 函数名 列表窗中选定SUM函数 然后单击 确定 按钮 参见下图 步骤三 在选定求和范围后 单击SUM函数计算的结果信息框中的 确定 按钮 注 Excel提供有自动计算功能 如选定 工资额 列中的各单元格后 状态栏中就将显示各项数据记录的总值 若选定单元格后右击状态栏 然后从一份快捷菜单中还能选择显示这些选定单元格内各数字的平均值 最大值或最小值等 12 制作员工工资图表利用Excel提供的图表功能 可以基于工作表中的数据建立图形表格 这是一种使用图形来描述数据的方法 用于直观地表达各统计值大小差异 为了制作员工工资图表 请按下列步骤进行操作 步骤一 将光标移至A3单元格上 然后单击它并向下拖动 选定各员工的姓名 并按住键盘上的Ctrl键 将光标移至D3单元格上 向下拖动 选定各员工的工资额后 结束拖动并释放Ctrl键 结果应如右图所示 13 步骤二 单击工具箱中的 图表向导 按钮 进入 图表向导 的第一步操作对话框 注 若按住 按下不放可查看示例 按钮 子图形类型 将变成预览窗口 并且显示您所选定的图表类型与电子报表中数据的图表外观 14 步骤三 在 图表向导 的第二步操作对话框中 打开 行 单选按钮 15 步骤四单击 下一步 按钮后 在 图表选项 对话框的 图表标题 栏中输入 员工工资图表 16 步骤五 单击 下一步 按钮后 在 图表位置 对话框中打开 作为新工作表插入 单选按钮 17 最后 单击 完成 按钮 一张工作图表就产生了 18 单元格和区域引用引用的作用在于标识工作表上的单元格或单元格区域 并指明公式中所使用的数据的位置 通过引用 可以在公式中使用工作表不同部分的数据 或者在多个公式中使用同一单元格的数值 还可以引用同一工作簿不同工作表的单元格 不同工作簿的单元格 甚至其它应用程序中的数据 引用不同工作簿中的单元格称为外部引用 引用其它程序中的数据称为远程引用 在EXCEL中 存在两种引用类型 1 A1 引用类型 用字母标志列 从A到IV 共256列 用数字标志行 从1到65536 2 R1C1 引用类型 用 R 加行数字和 C 加列数字来指示单元格的位置 19 在默认状态下 MicrosoftExcel使用A1引用类型 如果要引用单元格 请顺序输入列字母和行数字 例如 D50引用了列D和行50交叉处的单元格 如果要引用单元格区域 请输入区域左上角单元格的引用 冒号 和区域右下角单元格的引用 示例如右图所示 20 引用工作表外部数据随着大量的数据积累 还需要通过链接或者外部引用来共享其它工作簿或工作表中的数据 如下例中 AVERAGE工作表函数将计算同一工作簿中Marketing工作表B1 B10区域内的平均值 21 一 什么是函数Excel中所提的函数其实是一些预定义的公式 它们使用一些称为参数的特定数值按特定的顺序或结构进行计算 用户可以直接用它们对某个区域内的数值进行一系列运算 如分析和处理日期值和时间值 确定贷款的支付额 确定单元格中的数据类型 计算平均值 排序显示和运算文本数据等等 例如 SUM函数对单元格或单元格区域进行加法运算 参数参数可以是数字 文本 形如TRUE或FALSE的逻辑值 数组 形如 N A的错误值或单元格引用 给定的参数必须能产生有效的值 参数也可以是常量 公式或其它函数 参数不仅仅是常量 公式或函数 还可以是数组 单元格引用 常量常量是直接键入到单元格或公式中的数字或文本值 或由名称所代表的数字或文本值 例如 日期10 9 96 数字210和文本 QuarterlyEarnings 都是常量 公式或由公式得出的数值都不是常量 22 函数的结构如下图所示 函数的结构以函数名称开始 后面是左圆括号 以逗号分隔的参数和右圆括号 如果函数以公式的形式出现 请在函数名称前面键入等号 23 嵌套函数所谓嵌套函数 就是指在某些情况下 您可能需要将某函数作为另一函数的参数使用 也就是说一个函数可以是另一个函数的参数 例如下图中所示的公式使用了嵌套的AVERAGE函数 并将结果与50相比较 这个公式的含义是 如果单元格F2到F5的平均值大于50 则求G2到G5的和 否则显示数值0 24 Excel的计算公式 在Excel中 公式 是在单元格中执行计算的方程式 如一个执行数学计算的加 减就是一种简单的公式 在前面的操作中 对工资额的总计就使用了这样的公式 此时若单击显示工资额总计的单元格 D9 当它处于选定状态 编辑栏 中就会显示所使用的公式 如图所示 25 只要您记住公式的应用法则 无论在单元格中 还是在 编辑栏 中都能建立并使用公式的 以下是一个公式范例 如果正确的创建了计算公式 那么在中文Excel2000的默认状态下 其计算值就会显示在单元格中 公式则显示在 编辑栏 中 如果要使工作表中所有的公式在显示公式内容与显示结果之间切换 可按下CTRL 组合键 位于键盘左上侧 26 Excel的运算符 1 算术运算符用于完成基本的数学运算 如加法 减法和乘法 连接数字和产生数字结果等 各算术运算与用途如下表所列 27 2 比较运算符用于比较两个值 结果将是一个逻辑值 即不是TRUE 真 就是FALSE 假 与其它的计算机程序语言查类似 这类运算符还用于按条件做下一步运算 各算术运算符名称与用途如下表所列 28 3 文本运算符这实际上是一个文字串联符 用于加入或连接一个或更多字符串来产生一大段文本 如 North wind 结果将是Northwind 4 引用运算符引用下表的运算符可以将单元格区域合并起来进行计算 29 如果公式中使用了多个运算符 Excel将按下表所列的顺序进行运算 如果公式中包含了相同优先级的运算符 例如 同时包含了乘法和除法运算符 则将从左到右进行计算 如果要修改计算的顺序 可把需要首先计算的部分放在一对圆括号内 30 应用计算公式的范例 1 求和应用 SUM F6 D7 E7 2 合并 姓 和 名 D5 E53 合并日期与文本 时间 TEXT F5 d mmm yy 4 按百分比增加 F 5 1 5 5 基于单个条件求和 SUMIF B3 B8 20 D3 D8 31 认识TEXT工作函数上面 合并日期与文本 公式中的TEXT是一个常用的工作表函数 能将一数值转换为按指定数字格式来表示的文本 语法 TEXT value format text Value为数值 计算结果为数值的公式 或对数值单元格的引用 Format text所要选用的文本型数字格式 即 单元格格式 对话框 数字 选项卡的 分类 列表框中显示的格式 Format text不能包含星号 也不能是 常规 型 TEXT工作函数还能圆整数值 或者转换日期格式示例 TEXT 2 715 0 00 等于 2 72 TEXT 4 15 91 mmmmdd yyyy 等于 April15 1991 说明 通过 格式 菜单调用 单元格格式 对话框 然后在 数字 选项卡上设置单元格的格式 只会改变单元格的格式而不会影响其中的数值 使用函数TEXT可以将数值转换为带格式的文本 而其结果将不再作为数字参与计算 32 认识SUMIF工作函数这是上面 使用单个条件求和 公式中出现的工作函数 用于根据指定条件对若干单元格求和 语法 SUMIF range criteria sum range 其中 Range为用于条件判断的单元格区域 Criteria为确定哪些单元格将被相加求和的条件 其形式可以为数字 表达式或文本 例如 条件可以表示为32 32 32 apples Sum range为需要求和的实际单元格 只有当Range中的相应单元格满足条件时 才对sum range中的单元格求和 如果省略sum range 则直接对Range中的单元格求和 33 相对引用与绝对引用根据所要完成的任务 既可以使用相对单元格引用 也可以使用绝对单元格引用 前者引用的是相对于公式单元格位于某一位置处的单元格 后者引用的是特定位置处的单元格 当使用相对引用生成公式时 对单元格或区域的引用通常基于它们与公式单元格的相对位置 当复制使用相对引用的公式时 被粘贴公式中的引用将被更新 并指向与当前公式位置相对应的其它单元格 如果不希望在复制公式时 引用发生改变 请使用绝对引用 例如 如果公式将单元格A5乘以单元格C1 A5 C1 将公式复制到另一单元格中 则公式中的两个引用都将改变 如果在不希望改变的引用前加一个美元符号 就可以对单元格C1进行绝对引用 如果要对单元格C1进行绝对引用 请如下在公式中加入美元符号 A5 C 1相对引用与绝对引用也可混合使用 如 C1或 C 1 34 数据清单创建数据清单 数据清单 是工作表中包含相关数据的一系列数据行 如前面所建立的 员工工资表 这张电子报表就包含有这样的数据行 它可以像数据库一样接受浏览与编辑等操作 数据清单中的列是数据库中的字段 数据清单中的列标志是数据库中的字段名称 数据清单中的每一行对应数据库中的一个记录使用鼠标器选定单元格区域 Excel会在需要的时候自动建立一份数据清单 注意 在每张工作表上只能建立并使用一份数据清单 也应避免在一张工作表上建立多份数据清单 因为某些数据清单管理功能 如筛选 等一次也只能在一份数据清单中使用 35 使用数据清单 应当注意遵循下列准则 1 将类型相同的数据项置于同一列中2 使数据清单独立于其它数据在工作表中 数据清单与其他数据间至少要留出一个空列和一个空行 以便在执行排序 筛选或插入自动汇总等操作时 有利于Excel检测和选定数据清单 3 将关键数据置于清单的顶部或底部这样可避免将关键数据放到数据清单的左右两侧 因为这些数据在Excel筛选数据清单时可能会被隐藏 4 注意显示行和列在修改数据清单之前 应确保隐藏的行或列也被显示 因为 如果清单中的行和列没有被显示 那么数据有可能会被删除 5 避免空行和空列避免在数据清单中随便放置空行和空列 将有利于Excel检测和选定数据清单 因为单元格开头和末尾的多余空格会影响排序与搜索 所以不要在单元格内文本前面或后面键入空格 可采用缩进单元格内文本的办法来代替键入空格 36 编辑数据记录对于数据清单中的数据记录 Excel允许新建 删除数据记录 按某些条件查询数据记录 而且操作简单 只需要从 数据 下拉菜单中选择 记录单 命令 进入下图所示的数据记录单对话框就能完成这些操作 37 查询数据记录要制定一个查询条件 只需单击 条件 按钮 进入如图所示的数据记录单对话框 然后在各字段框中输入查询内容即可 注意 在数据记录单中一次最多只能显示32个字段 38 排序数据记录在Excel中也可以根据现有的数据资料对数据值进行排序 按递增方式排序的数据类型及其数据的顺序为 1 数字 顺序是从小数到大数 从负数到正数 2 文字和包含数字的文字 其顺序是 0123456789 空格 ABCDEFGHIJKLMNOPQRSTUVWXYZ 3 逻辑值 False在True之前 4 错误值 所有的错误值都是相等的 5 空白 不是空格 单元格总是排在最后 递减排序的顺序与递增顺序恰好相反 但空白单元格将排在最后 日期 时间和汉字也当文字处理 是根据它们内部表示的基础值排序 39 最简单的排序操作是使用 常用 工具栏中的按钮 在这个工具栏上有两个用于排序的按钮 图标上有AZ与向下的箭头的按钮 用于按降序方式重排数据 按钮上标有ZA与向下的箭头的按钮用于递减排序 注意 若选定了某一列后来使用以上所示的操作 排序将只发生在这一列中 其它列的数据排列将保持不变 其结果可能会破坏原始记录结构 造成数据错误 可通过 数据 菜单中的 排序 命令 打开右图所示对话框 对排序条件进行相应设置 排序 对话框 可以使用的各选项功能所述如下 40 1 主要关键字通过一份下拉菜单选择排序字段 打开位于右旁的单选按钮 可控制按递增或递减的方式进行排序 2 次要关键字前面设置的 主要关键字 列中出现了重复项 就将按次要关键字来排序重复的部分 3 第三关键字4 有标题行在数据排序时 包含清单的第一行 5 无标题行在数据排序时 不包含清单的第一行 注意 如果排序结果与所预期的不同 说明排序数据的类型有出入 若想得到最好的结果 就要确保列中所有单元格属于同一数据类型 应避免在连续的单元格中交替输入数字或文字 若要将数字以文字方式输入 如邮政编码或产品编号 请在数字之前加上一个省略符号 如 610000 或在输入之前把该列格式化为文字 或输成中文符号 如 610000 41 筛选数据Excel提供有两条用于筛选的命令 自动筛选 和 高级筛选 为了使用 自动筛选 命令 可以按下列步骤进行操作 步骤一 从 数据 下拉菜单中选择 筛选 命令 然后从 筛选 子菜单中选择 自动筛选 命令 此后 数据清单中第一行的各列中将分别显示出一个下拉按钮 如下图所示 自动筛选就将通过它们来进行 42 高级筛选数据使用高级筛选功能可以对某个列或者多个列应用多个筛选条件 为了使用此功能 在工作表的数据清单上方 至少应有三个能用作条件区域的空行 而且数据清单必须有列标 条件区域 包含一组搜索条件的单元格区域 可以用它在高级筛选筛选数据清单的数据 它包含一个条件标志行 同时至少有一行用来定义搜索条件 有了条件区域 就可以按下列操作步骤来进行高级筛选 步骤一 选择数据清单中含有要筛选值的列标 将其复制到条件区域中的第一空行里的某个单元格 步骤二 在条件区域中输入筛选条件 如图所示 43 步骤三 从 数据 下拉菜单中选择 筛选 命令 然后从 筛选 子菜单中选择 高级筛选 命令 进入图所示的 高级筛选 对话框 步骤四 单击 高级筛选 对话框中 条件区域 设置按钮后 单击选定条件区域中的条件 然后再单击此按钮返回 高级筛选 对话框 最后单击 确定 按钮 结束操作 在 高级筛选 对话框中进行操作时 若筛选后要隐藏不符合条件的数据行 并让筛选的结果显示在数据清单中 可打开 在原有区域显示筛选结果 单选按扭 若要将符合条件的数据行复制到工作表的其他位置 则需要打开 将筛选结果复制到其他位置 单选按钮 并通过 复制到 编辑框指定粘贴区域的左上角 从而设置复制位置 44 Excel培训教程 下 45 Excel函数函数的种类 1 数学和三角函数 2 逻辑函数 3 文本 日期与时间函数 4 查询和引用函数 5 统计函数 6 工程函数 7 财务函数 8 信息函数 9 数据库函数 10 用户自定义函数 46 1 数学和三角函数 47 48 49 2 逻辑函数 逻辑函数是用来判断真假值 或者进行复合检验的Excel函数 在Excel中提供了六种逻辑函数 即AND OR NOT FALSE IF TRUE函数 一 AND函数所有参数的逻辑值为真时返回TRUE 只要一个参数的逻辑值为假即返回FALSE 语法 AND logical1 logical2 其中Logical1 logical2 表示待检测的1到30个条件值 参数必须是逻辑值 或者包含逻辑值的数组或引用 例 AND B2 30 B2 60 AND B1 B3 二 OR函数OR函数指在其参数组中 任何一个参数逻辑值为TRUE 即返回TRUE 它与AND函数的区别在于 AND函数要求所有函数逻辑值均为真 结果方为真 而OR函数仅需其中任何一个为真即可为真 50 三 NOT函数NOT函数用于对参数值求反 当要确保一个值不等于某一特定值时 可以使用NOT函数 简言之 就是当参数值为TRUE时 NOT函数返回的结果恰与之相反 结果为FALSE 四 TRUE FALSE函数TRUE FALSE函数用来返回参数的逻辑值 由于可以直接在单元格或公式中键入值TRUE或者FALSE 因此这两个函数通常可以不使用 五 IF函数IF函数用于执行真假值判断后 根据逻辑测试的真假值返回不同的结果 因此If函数也称之为条件函数 语法 IF logical test value if true value if false 例 IF SUM C5 F5 SUM C5 F5 IF B11 60 合格 不合格 51 3 文本 日期与时间函数 一 文本函数 一 大小写转换LOWER 将一个文字串中的所有大写字母转换为小写字母 UPPER 将文本转换成大写形式 PROPER 将文字串的首字母及任何非字母字符之后的首字母转换成大写 将其余的字母转换成小写 示例 Lower pLeaseComEHere pleasecomehere upper pLeaseComEHere PLEASECOMEHERE proper pLeaseComEHere PleaseComeHere 52 二 取出字符串中的部分字符可以使用Mid Left Right等函数从长字符串内获取一部分字符 具体语法为 LEFT函数 LEFT text num chars 其中Text是包含要提取字符的文本串 Num chars指定要由LEFT所提取的字符数 MID函数 MID text start num num chars 其中Text是包含要提取字符的文本串 Start num是文本中要提取的第一个字符的位置 RIGHT函数 RIGHT text num chars 其中Text是包含要提取字符的文本串 Num chars指定希望RIGHT提取的字符数 比如 LEFT Thisisanapple 4 ThisRIGHT Thisisanapple 5 appleMID Thisisanapple 6 2 is 53 三 去除字符串的空白在字符串形态中 空白也是一个有效的字符 但是如果字符串中出现空白字符时 容易在判断或对比数据是发生错误 在Excel中 可以使用Trim函数清除字符串中的空白 语法 TRIM text 其中Text为需要清除其中空格的文本 比如 TRIM MynameisMary MynameisMary注意 Trim函数不会清除单词之间的单个空格 如果连这部分空格都需清除的话 建议使用替换功能 54 四 字符串的比较EXACT函数测试两个字符串是否完全相同 如果它们完全相同 则返回TRUE 否则 返回FALSE 函数EXACT能区分大小写 但忽略格式上的差异 语法 EXACT text1 text2 Text1为待比较的第一个字符串 Text2为待比较的第二个字符串 例如 EXACT China china False 55 文本函数集 56 57 58 二 日期与时间函数 一 取出当前系统时间 日期信息用于取出当前系统时间 日期信息的函数主要有NOW TODAY 语法形式均为函数名 二 取得日期 时间的部分字段值如果需要单独的年份 月份 日数或小时的数据时 可以使用HOUR DAY MONTH YEAR函数直接从日期 时间中取出需要的数据 示例 设E5 2003 5 3012 30PM 如果要取E5的年份 月份 日数及小时数 可以分别采用相应函数实现 YEAR E5 2003MONTH E5 5DAY E5 30HOUR E5 12 59 日期与时间函数集 60 61 4 查询和引用函数 1 引用的作用在Excel中引用的作用在于标识工作表上的单元格或单元格区域 并指明公式中所使用的数据的位置 通过引用 可以在公式中使用工作表不同部分的数据 或者在多个公式中使用同一单元格的数值 还可以引用同一工作簿不同工作表的单元格 不同工作簿的单元格 甚至其它应用程序中的数据 2 引用的含义关于引用需要了解如下几种情况的含义 外部引用 不同工作簿中的单元格的引用称为外部引用 远程引用 引用其它程序中的数据称为远程引用 相对引用 在创建公式时 单元格或单元格区域的引用通常是相对于包含公式的单元格的相对位置 绝对引用 如果在复制公式时不希望Excel调整引用 那么请使用绝对引用 即加入美元符号 如 C 1 62 3 引用的表示方法关于引用有两种表示的方法 即A1和R1C1引用样式 1 引用样式一 默认 A1A1的引用样式是Excel的默认引用类型 这种类型引用字母标志列 从A到IV 共256列 和数字标志行 从1到65536 这些字母和数字被称为行和列标题 如果要引用单元格 请顺序输入列字母和行数字 例如 C25引用了列C和行25交叉处的单元格 如果要引用单元格区域 请输入区域左上角单元格的引用 冒号 和区域右下角单元格的引用 如A20 C35 2 引用样式二 R1C1在R1C1引用样式中 Excel使用 R 加行数字和 C 加列数字来指示单元格的位置 例如 单元格绝对引用R1C1与A1引用样式中的绝对引用 A 1等价 63 一 ADDRESS COLUMN ROW1 ADDRESS用于按照给定的行号和列标 建立文本类型的单元格地址 语法 ADDRESS Row num Column num Abs num A1 Sheet text Row num指在单元格引用中使用的行号 Column num指在单元格引用中使用的列标 Abs num指明返回的引用类型 1代表绝对引用 2代表绝对行号 相对列标 3代表相对行号 绝对列标 4为相对引用 A1用以指明A1或R1C1引用样式的逻辑值 如果A1为TRUE或省略 函数ADDRESS返回A1样式的引用 如果A1为FALSE 函数ADDRESS返回R1C1样式的引用 Sheet text为一文本 指明作为外部引用的工作表的名称 如果省略sheet text 则不使用任何工作表名 即 ADDRESS 行号 列标 引用类型 引用样式 工作表名称 比如 ADDRESS 4 5 1 FALSE Book1 Sheet1 等于 Book1 Sheet1 R4C5 64 2 COLUMN用于返回给定引用的列标 语法形式为 COLUMN reference Reference为需要得到其列标的单元格或单元格区域 如果省略reference 则假定为是对函数COLUMN所在单元格的引用 如果reference为一个单元格区域 并且函数COLUMN作为水平数组输入 则函数COLUMN将reference中的列标以水平数组的形式返回 但是Reference不能引用多个区域 3 ROW用于返回给定引用的行号 语法形式为 ROW reference Reference为需要得到其行号的单元格或单元格区域 如果省略reference 则假定是对函数ROW所在单元格的引用 如果reference为一个单元格区域 并且函数ROW作为垂直数组输入 则函数ROW将reference的行号以垂直数组的形式返回 但是Reference不能对多个区域进行引用 65 二 AREAS COLUMNS INDEX ROWS1 AREAS用于返回引用中包含的区域个数 其中区域表示连续的单元格组或某个单元格 语法 AREAS reference Reference为对某一单元格或单元格区域的引用 也可以引用多个区域 如果需要将几个引用指定为一个参数 则必须用括号括起来 例如 AREAS B2 D4 等于1 AREAS B2 D4 E5 F6 I9 等于32 COLUMNS用于返回数组或引用的列数 语法 COLUMNS array Array为需要得到其列数的数组 数组公式或对单元格区域的引用 例如 COLUMNS A1 C4 等于3 COLUMNS 1 2 3 4 5 6 等于3 66 3 ROWS用于返回引用或数组的行数 语法 ROWS array Array为需要得到其行数的数组 数组公式或对单元格区域的引用 例如 ROWS A1 C4 等于4 ROWS 1 2 3 4 5 6 等于24 INDEX用于返回表格或区域中的数值或对数值的引用 函数INDEX 有两种形式 数组和引用 数组形式通常返回数值或数值数组 引用形式通常返回引用 语法 INDEX array或reference row num column num area num 返回数组中指定单元格或单元格数组的数值 Array为单元格区域或数组常数 Row num为数组中某行的行序号 函数从该行返回数值 Column num为数组中某列的列序号 函数从该列返回数值 需注意的是Row num和column num必须指向array中的某一单元格 否则 函数INDEX返回错误值 REF area num指定的区域 假如存在多个区域引用的话 示例 INDEX 1 2 3 4 2 2 等于4 67 三 HLOOKUP LOOKUP MATCH VLOOKUP1 LOOKUP函数与MATCH函数LOOKUP函数可以返回向量 单行区域或单列区域 或数组中的数值 此系列函数用于在表格或数值数组的首行查找指定的数值 并由此返回表格或数组当前列中指定行处的数值 当比较值位于数据表的首行 并且要查找下面给定行中的数据时 使用函数HLOOKUP 当比较值位于要进行数据查找的左边一列时 使用函数VLOOKUP 如果需要找出匹配元素的位置而不是匹配元素本身 则应该使用函数MATCH而不是函数LOOKUP MATCH函数用来返回在指定方式下与指定数值匹配的数组中元素的相应位置 从以上分析可知 查找函数的功能 一是按搜索条件 返回被搜索区域内数据的一个数据值 二是按搜索条件 返回被搜索区域内某一数据所在的位置值 68 2 LOOKUP用于返回向量 单行区域或单列区域 或数组中的数值 函数LOOKUP有两种语法形式 向量和数组 1 向量形式函数LOOKUP的向量形式是在单行区域或单列区域 向量 中查找数值 然后返回第二个单行区域或单列区域中相同位置的数值 语法 LOOKUP lookup value lookup vector result vector Lookup value为函数LOOKUP在第一个向量中所要查找的数值 可以为数字 文本 逻辑值或包含数值的名称或引用 Lookup vector为只包含一行或一列的区域 Lookup vector的数值可以为文本 数字或逻辑值 注意 Lookup vector的数值必须按升序排序 2 1 0 1 2 A Z FALSE TRUE 否则 函数LOOKUP不能返回正确的结果 文本不区分大小写 Result vector只包含一行或一列的区域 其大小必须与lookup vector相同 如果函数LOOKUP找不到lookup value 则查找lookup vector中小于或等于lookup value的最大数值 如果lookup value小于lookup vector中的最小值 函数LOOKUP返回错误值 N A 示例 LOOKUP ddd A2 A8 B2 B8 等于18 LOOKUP bbb A2 A8 B2 B8 等于21 69 2 数组形式函数LOOKUP的数组形式在数组的第一行或第一列查找指定的数值 然后返回数组的最后一行或最后一列中相同位置的数值 语法 LOOKUP lookup value array 示例 LOOKUP C a b c d 1 2 3 4 等于3 LOOKUP bump a 1 b 2 c 3 等于2要点 这些数值必须按升序排列 2 1 0 1 2 A Z FALSE TRUE 否则 函数LOOKUP不能返回正确的结果 通常情况下 最好使用函数HLOOKUP或函数VLOOKUP来替代函数LOOKUP的数组形式 函数LOOKUP的这种形式主要用于与其他电子表格兼容 70 3 HLOOKUP与VLOOKUPHLOOKUP用于在表格或数值数组的首行查找指定的数值 并由此返回表格或数组当前列中指定行处的数值 VLOOKUP用于在表格或数值数组的首列查找指定的数值 并由此返回表格或数组当前行中指定列处的数值 语法 HLOOKUP lookup value table array row index num range lookup VLOOKUP lookup value table array col index num range lookup 其中 Lookup value表示要查找的值 它必须位于自定义查找区域的最左列 Lookup value可以为数值 引用或文字串 Table array查找的区域 用于查找数据的区域 上面的查找值必须位于这个区域的最左列 可以使用对区域或区域名称的引用 Row index num为table array中待返回的匹配值的行序号 Row index num为1时 返回table array第一行的数值 row index num为2时 返回table array第二行的数值 以此类推 Col index num为相对列号 最左列为1 其右边一列为2 依此类推 Range lookup为一逻辑值 指明函数HLOOKUP查找时是精确匹配 还是近似匹配 示例 HLOOKUP 年龄 A1 D8 5 TRUE 等于18 VLOOKUP ddd A1 D8 4 TRUE 等于900 71 4 MATCH函数MATCH函数有两方面的功能 两种操作都返回一个位置值 一是确定区域中的一个值在一列中的准确位置 这种精确的查询与列表是否排序无关 二是确定一个给定值位于已排序列表中的位置 这不需要准确的匹配 语法结构为 MATCH lookup value lookup array match type lookup value为要搜索的值 lookup array 要查找的区域 必须是一行或一列 match type 匹配形式 有0 1和 1三种选择 0 表示一个准确的搜索 1 表示搜索小于或等于查换值的最大值 查找区域必须为升序排列 1 表示搜索大于或等于查找值的最小值 查找区域必须降序排开 以上的搜索 如果没有匹配值 则返回 N A 示例 MATCH 1000 D2 D8 0 等于4 MATCH 1000 D2 D8 1 等于4 MATCH 1000 D2 D8 1 等于 N A因为没有按降序排列 72 四 HYPERLINK所谓HYPERLINK 也就是创建快捷方式 以打开文档或网络驱动器 甚至INTERNET地址 在某个Excel文档中 也许您需要引用别的Excel文档或Word文档等等 其步骤和方法是这样的 1 选中您要输入此函数的单元格 比如B6 2 单击常用工具栏中的 粘贴函数 图标 将出现 粘贴函数 对话框 在 函数分类 框中选择 常用 在 函数名 框中选择HYPERLINK 3 单击 确定 后将弹出HYPERLINK函数参数设置对话框 4 在 Link location 中键入要链接的文件或INTERNET地址 比如 c mydocuments Excel函数 doc 在 Friendly name 中键入 Excel函数 这里是假设我们要打开的文档位于c mydocuments下的文件 Excel函数 doc 5 单击 确定 回到您正编辑的Excel文档 此时再单击B6单元格就可立即打开用Word编辑的会议纪要文档 73 六 CHOOSE TRANSPOSE1 CHOOSE函数函数CHOOSE可以使用index num返回数值参数清单中的数值 使用函数CHOOSE可以基于索引号返回多达29个待选数值中的任一数值 语法 CHOOSE index num value1 value2 Index num用以指明待选参数序号的参数值 Index num必须为1到29之间的数字 或者是包含数字1到29的公式或单元格引用 Value1 value2 为1到29个数值参数 函数CHOOSE基于index num 从中选择一个数值或执行相应的操作 参数可以为数字 单元格引用 已定义的名称 公式 函数或文本 示例 CHOOSE 2 1st 2nd 3rd Finished 等于 2nd SUM A1 CHOOSE 3 A10 A20 A30 等于SUM A1 A30 74 2 TRANSPOSE函数TRANSPOSE用于返回区域的转置 函数TRANSPOSE必须在某个区域中以数组公式的形式输入 该区域的行数和列数分别与array的列数和行数相同 使用函数TRANSPOSE可以改变工作表或宏表中数组的垂直或水平走向 语法 TRANSPOSE array Array为需要进行转置的数组或工作表中的单元格区域 所谓数组的转置就是 将数组的第一行作为新数组的第一列 数组的第二行作为新数组的第二列 以此类推 示例 第一步 由于需要转置的多个单元格形式 因此需要以数组公式的方法输入公式 故首先选定需转置的范围 此处我们设定转置后存放的范围为A18 G19 第二步 单击常用工具栏中的 粘贴函数 图标 将出现 粘贴函数 对话框 在 函数分类 框中选择 查找与引用函数 框中选择TRANSPOSE 单击 确定 后将弹出TRANSPOSE函数参数设置对话框 第三步 选择数组的范围即A2 B8第四步 由于此处是以数组公式输入 因此需要按CRTL SHIFT ENTER组合键来确定为数组公式 此时会在公式中显示 随即转置成功 75 5 统计函数 Excel的统计工作表函数用于对数据区域进行统计分析 例如 统计工作表函数可以用来统计样本的方差 数据区间的频率分布等 统计工作表函数中提供了很多属于统计学范畴的函数 但也有些函数其实在你我的日常生活中是很常用的 比如求班级平均成绩 排名等 以下主要介绍一些常见的统计函数 一 用于求平均值的统计函数AVERAGE TRIMMEAN1 求参数的算术平均值函数AVERAGE语法 AVERAGE number1 number2 其中Number1 number2 为要计算平均值的1 30个参数 这些参数可以是数字 或者是涉及数字的名称 数组或引用 如果数组或单元格引用参数中有文字 逻辑值或空单元格 则忽略其值 但是 如果单元格包含零值则计算在内 76 2 求数据集的内部平均值TRIMMEAN函数TRIMMEAN先从数据集的头部和尾部除去一定百分比的数据点 然后再求平均值 当希望在分析中剔除一部分数据的计算时 可以使用此函数 语法 TRIMMEAN array percent 其中Array为需要进行筛选并求平均值的数组或数据区域 Percent为计算时所要除去的数据点的比例 例如 如果percent 0 2 在20个数据点的集合中 就要除去4个数据点 20 x0 2 头部除去2个 尾部除去2个 说明 如果percent1 函数TRIMMEAN返回错误值 NUM 函数TRIMMEAN将除去的数据点数目向下舍为最接近的2的倍数 如果percent 0 1 30个数据点的10 等于3个数据点 函数TRIMMEAN将对称地在数据集的头部和尾部各除去一个数据 示例 TRIMMEAN 4 5 6 7 2 3 4 5 1 2 3 0 2 等于3 777778 TRIMMEAN D2 D8 0 3 等于 1 119 80 77 二 用于求单元格个数的统计函数COUNT语法形式为COUNT value1 value2 其中Value1 value2 为包含或引用各种类型数据的参数 1 30个 但只有数字类型的数据才被计数 函数COUNT在计数时 将把数字 空值 逻辑值 日期或以文字代表的数计算进去 但是错误值或其他无法转化成数字的文字则被忽略 如果参数是一个数组或引用 那么只统计数组或引用中的数字 数组中或引用的空单元格 逻辑值 文字或错误值都将忽略 如果要统计逻辑值 文字或错误值 应当使用函数COUNTA 示例 COUNT D2 D8 等于7 78 三 求区域中数据的频率分布FREQUENCY语法 FREQUENCY data array bins array Data array为一数组或对一组数值的引用 用来计算频率 如果data array中不包含任何数值 函数FREQUENCY返回零数组 Bins array数据接收区间 为一数组或对数组区域的引用 设定对data array进行频率计算的分段点 如果bins array中不包含任何数值 函数FREQUENCY返回data array里全体元素的数目 说明 1 首先选定用于存放频率分布结果的相邻单元格区域 然后以数组公式的形式输入函数FREQUENCY 按CRTL SHIFT ENTER组合键 2 返回数组中的元素个数比bins array的元素数目要多出一个 3 函数FREQUENCY将忽略空白单元格和文本值 79 四 一组用于求数据集的满足不同要求的数值的函数1 求数据集的最大值MAX与最小值MIN这两个函数MAX MIN就是用来求解数据集的极值 即最大值 最小值 函数的用法非常简单 语法 函数 number1 number2 其中Number1 number2 为需要找出最大数值的1到30个数值 如果要计算数组或引用中的空白单元格 逻辑值或文本将被忽略 因此如果逻辑值和文本不能忽略 请使用带A的函数MAXA或者MINA来代替 2 求数据集中第K个最大值LARGE与第k个最小值SMALL这两个函数LARGE SMALL与MAX MIN非常想像 区别在于它们返回的不是极值 而是第K个值 语法 函数 array k 其中Array为需要找到第k个最小值的数组或数字型数据区域 K为返回的数据在数组或数据区域里的位置 如果是LARGE为从大到小排 若为SMALL函数则从小到大排 如果K 1或者K n 假定数据集中有n个数据 的时候 就可以返回数据集的最大值或者最小值了呢 80 3 求数据集中的中位数MEDIANMEDIAN函数返回给定数值集合的中位数 所谓中位数是指在一组数据中居于中间的数 语法 MEDIAN number1 number2 其中Number1 number2 是需要找出中位数的1到30个数字参数 如果数组或引用参数中包含有文字 逻辑值或空白单元格 则忽略这些值 但是其值为零的单元格会计算在内 注意 如果参数集合中包含有偶数个数字 函数MEDIAN将返回位于中间的两个数的平均值 4 求数据集中出现频率最多的数MODEMODE函数用来返回在某一数组或数据区域中出现频率最多的数值 跟MEDIAN一样 MODE也是一个位置测量函数 语法 MODE number1 number2 其中Number1 number2 是用于众数 众数指在一组数值中出现频率最高的数值 计算的1到30个参数 也可以使用单一数组 即对数组区域的引用 来代替由逗号分隔的参数 81 统计函数集 82 83 84 85 86