《Excel数据分析高级应用-公式》由会员分享，可在线阅读，更多相关《Excel数据分析高级应用-公式（76页珍藏版）》请在金锄头文库上搜索。
1、重庆邮电大学经济管理学院 周玉敏 要求掌握基本的Excel操作 例如 单元格格式的设 置 简单的公式使用等 对基本概念熟悉 如 绝对引用 相对应用 以应用案例的方式讲解Excel的相关知识 不再讲解 基本操作部分 理论学时16学时 实验学时16学时 缺席3次取消考 试资格 推荐教材 电子工业出版社 Excel与管理数据分析 第3版 杜茂康 主编 推荐学习网站 1 认识Excel 2 如何设计表格 2h 3 函数的使用 6h 4 图表的制作 4h 5 数据分析 4h Excel的发展历史 你认为利用Excel能做哪些工作 画表格 计算 求和 计算平均值 结论 Excel易学 功能简单 咱们做一点
2、测试题吧 你能得多少 分呢 1 认识Excel 2 如何设计表格 3 函数的使用 4 图表的制作 5 数据分析 专业化表格设计原则 利用 条件格式 和 数据有效性 设计友好表格 整张报表很少用表 格线 却非常清晰 明了 作为一个局外人我 们可以在最短的时 间了解报表的内容 和所要表达的意图 如果我们来做这 张报表 一般会 是什么样呢 按照我们的习惯 做出的表格一般是 这样的 同专业人士制作的表 格相比有什么区别呢 不能看清表格的 内容 但依然能 清楚地了解表格 内容的层次 没有规律的空间 分隔 像蜘蛛网 一样的表格线 不知从何看起 根据项目类别将不同项目间距扩大 相同项目间距 缩小 将最重要的
3、放在左上角 对于报表的核心数据应用 下划线标出 把不重要的放在下方 对于重点区域用不同的符号进行标注 但标注不宜 过多 报表正文字型一般选择 宋体 较为严谨 黑体 作为标题或需特别强调的区域 显得较为庄重 设置单元格格式 数字显示格式 对齐方式 填充 颜色等 使报表更清晰明了 完成本表涉及到的 知识点 单元格命名 数据有效性 条件格式 函数indirect的 用法 1 1 名字概述 名字概述 可以给一个单元格或单元格区域取一个名字 这个名字可 以出现在公式中 用来指代引用的单元格或单元格区域 这会使公式的意义更加明确 名字其实是一个标识符 由字母或下划线开头的一个或多 个字符组成 在Excel
4、中 还可以用汉字字符作为名字 名字不区分字母的大小写 比如ABC abc Abc都是同 一个名字 可以为同一单元格或单元格区域定义多个不同的名字 名字一旦定义就可以在同一工作薄的不同工作表之间共用 2 名字的意义 在Excel中 同一个工作簿中的名字是共享的 在一个工 作表中定义的名字 可以被另一个工作表的公式引用 这 使公式具有更强大的功能 它能够利用单元格的名字在不 同的工作表中查找到正确的单元格或单元格区域 名字给单元格的引用带来了许多方便 因为它使人们不必 记住单元格在工作表中的引用位置 用名字就能找到它 3 名字的定义 在Excel中 可以为一个独立的单元格 连续的单元格区 域或许多
5、不连续的单元格构成的单元格组合定义一个名字 或多个名字 方法二 公式 定义名称 方法一 选中单元格 名称 框直接输入 3 名字的应用 1 通过名称在不同的工作表之间 通过名称在不同的工作表之间传递数据传递数据 通过名称查找蔬菜单价通过名称查找蔬菜单价 指定指定A2 B12最最 左边为名称左边为名称 3 名字的应用 2 名称结合Indirect函数的应用 指定指定A2 B11区域区域 的的 最左列最左列 为名为名 称称 在D2中输入公式 INDIRECT B2 向下复制该公式 什么是数据有效性什么是数据有效性 数据有效性一个包含帮助你在工作表中输入资料提 示信息的工具 它有如下功能 给用户提供一
6、个选择列表 限定输入内容的类型或大小 自定义设置 条件格式选项 编辑条件 格式规则 条件格式 对 不同的数据 按不同的条件 设置其显示格 式 Indirect x 其中的其中的X可以是单元格引用或名称 可以是单元格引用或名称 若若X为单元格引用 它必须用为单元格引用 它必须用 引起来 如 引起来 如 INDIRECT A1 将返回 将返回A1单元格中的内容单元格中的内容 若若X为单元格名称 它将返回该名称所对应的单元格的为单元格名称 它将返回该名称所对应的单元格的 内容 内容 销售金额 100000 合格 70000 销售金额 100000 警戒数 销售金额 70000 不合格 1 认识Exc
7、el 2 如何设计表格 3 函数的使用 4 图表的制作 5 数据分析 1 常用函数使用范例 条件函数 If 统计函数 countif countifs sumif sumifs 查找函数 Vlookup Index Match 随机函数 rand 字符函数 len right left mid 2 数组公式的应用 IF E2 0 D2 IF Logical Value if true Value if false Logical代表逻辑 判断表达式 Value if true表示当判断条件为 逻辑 真 TRUE 时的显示 内容 如果忽略返回 TRUE Value if false表示当判断条件
8、为 逻辑 假 FALSE 时的显示 内容 如果忽略返回 FALSE 库存数 30 补货 50 库存数 60 下单 30 库存数60 充足 COUNTIF range criteria 用来搜索指定条件的单元格个数 RANGE 指定作为搜索对象的单元格 区域 即找是否有与条件相配的单元格 区域 CRITERIA 指定搜索RANGE单元格的 条件 即说明条件 Excel 2007还提供了一个多条件统计函数COUNTIFS 它可 以一次对多个不同区域进行不同条件的计数 其用法如下 COUNTIFS range1 criteria1 range2 criteria2 range127 criteria
9、127 例题 有学生成绩表如下图所示 统计其中总分 220分以上的人数 统计总分200分以上 物理 80分以下 地理70分以下 化学65分以上的人 数 统计姓王的同学人数 SUMIF Range Criteria Sum Range Sum rangeSum range为需要求和的单为需要求和的单 元格 区域或引用元格 区域或引用 RangeRange为用于条件判断为用于条件判断 的单元格区域的单元格区域 CriteriaCriteria是由数字 逻辑表是由数字 逻辑表 达式等组成的判定条件达式等组成的判定条件 条件求和函数SUMIFS 用法 SUMIFS sum range range1 c
10、riteria1 range2 criteria2 其中range1 range2 是计算关联条件的 1 至 127 个区域 Criteria1 criteria2 是数字 表达式 单元格引用或文本 形式的 1 至 127 个条件 用于定义要对哪些单元格求和 这些 区域与条件是对应的 即Criteria1是用于range1区域的条件 criteria2是用于range2的条件 以此类推 Sum range是求和 区域 功能 SUMIFS函数对某一区域内满足多重条件的单元格求和 例题 某家电商场的销售记录如下图所示的 A1 G13所示 现要统计出统计每位职工销售各种产 品的总数量 并将它统计在I
11、8 L13区域中 1 在J10单元格中输入统计李本成销售的彩电总数量的计算公式 SUMIFS E 3 E 13 D 3 D 13 I10 A 3 A 13 J 9 2 将此公式向下角复制到将此公式向下角复制到L13 求和 范围 条件1 条件2 功能 Vlookup按列查找的方式从指定数据表区域的最左列查找特定数据 它 能够返回查找区域中与找到单元格位于相同行不同列的单元格内容 语法 VLOOKUP x table n f x是要查找的值是要查找的值 table是一个单元格区域是一个单元格区域 n是table区域中要返回的数据所在 列的序号 n 1时 返回 table 第1 列中的数值 n 2时
12、 返回 table 第2列中的数值 以此类推 f是一个逻辑值 表示查找的方 式 当其为true 或1 时 表 示模糊查找 当它为false 或0 时 表示精确查找 1 在I31单元格中输入如下的查询公式 LOOKUP H31 A 31 B 38 2 TRUE 2 将此公式向下角复制到将此公式向下角复制到I39 Match格式 Match x r f 其中x是要查找的数值 r可以是一个数组常量 或某列 或行 连续的单元格区域 其中可能包含有要查找的 x f用于指定match的查找方式 它可以是 1 0或1 功能 Match x r f 表示的意思是 在数组或连续的单元格 区域r中查找x 并返回x
13、在r中的位置编号 当f为0是 match进行精确查找 当f为1 或 1 时 match进行模糊查找 Match查找的方式查找的方式 取值取值 函函 数数 功功 能能 1 r必须按降序排列 查找大于或等于必须按降序排列 查找大于或等于 x的最小数值的最小数值 0 r 不必排序 查找等于不必排序 查找等于x的第一个数值的第一个数值 1 r必须按升序排列 查找小于或等于必须按升序排列 查找小于或等于x的最大数值的最大数值 Index Area r c n 其中 Area是1个或多个单元格区域 r是某行的行序号 c是某 列的列序号 该函数返回指定的行与列交叉处的单元格引用 如 果r等于0 则返回整行单
14、元格引用 如果c等于0 则返回整列单 元格引用 当Area包括多个单元格区域时 n 1就表示结果来自于Area中 的第1个区域 n 2表示结果来源于第2个单元格区域 如 果省略n表示结果来源于第1个单元格区域 功能 Index Area r c n 的功能是返回Area中第n个单元格区域 中的r行 c列交叉处的单元格引用 已有 员工参保资料 表和 按人月汇总 表 制作如下表格 选择姓名 月份后查阅该职工工号和 当月社保缴纳情况 1 姓名 月份用 数据有效性 进行设定 姓名数据来源与 员工 参保资料 表 月份可直接在 数据有效性 对话框中输入 2 工号 查询公式 VLOOKUP C 4 按人月汇
15、总 B D 2 0 3 查询 单位缴费 养老保险 的公式 INDEX 按人月汇总按人月汇总 E 5 P 31 MATCH 按人分月调阅表按人分月调阅表 C 4 按按 人月汇总人月汇总 B 5 B 31 MATCH 按人分月调阅表按人分月调阅表 E 4 按人月汇按人月汇 总总 E 1 P 1 功能 返回大于等于 0 及小于 1 的均匀分布随机实数 另外 Excel2007增加函数 randbetween bottom top 可生成两个数之间的 随机数 怎样快速将七年级同学随机安排考场考试 常用字符函数 LEN text 计算text文本的长度 RIGHT text n 该函数从text文本的右
16、边取出n个字符 LEFT text n 该函数从text文本的左边取出n个字符 MID text n m 该函数从text文本的第n个字符起 取出m个字符 TEXT MID B2 7 6 LEN B2 18 2 00 00 概述 数组公式 能够同时对一组或两组以上的数据进行计算 计算 的结果可能是一个 也可能是多个 普通公式只执行一个简单 计算 并且返回一个运算结果 数组公式的建立方法数组公式的建立方法 1 选中需要保存数组公式结果的单元格或 选中需要保存数组公式结果的单元格或 单元格区域 单元格区域 2 输入公式的内容 输入公式的内容 3 按 按Ctrl Shift Enter键 键 问题1 在在D2 D4求出商品的销售金额 求出商品的销售金额 问题1 在在D2 D4求出商品的销售金额 求出商品的销售金额 一般解决方法 三个普通公式一般解决方法 三个普通公式 问题1 在在D2 D4求出商品的销售金额 求出商品的销售金额 数组公式解决 数组公式的优点 1 批量计算 节省计算时间 2 不能修改其中任一部分 保持公式集合的完整 性 问题2 在在F1求出商品的销售总金额求出商品的销售总金额
