Excel数据分析高级应用-公式

缩小； 将最重要的放在左上角，对于报表的核心数据应用 下划线标出，把不重要的放在下方； 对于重点区域用不同的符号进行标注，但标注不宜 过多； 报表正文字型一般选择“宋体”较为严谨，“黑体 ”作为标题或需特别强调的区域，显得较为庄重； 设置单元格格式：数字显示格式、对齐方式、填充 颜色等，使报表更清晰明了。
3、名字的应用
2）名称结合Indirect函数的应用
在D2中输入公式：
指定A2:B11区域 的“最左列”为名 称
=INDIRECT(B2) 向下复制该公式！
什么是数据有效性? 数据有效性一个包含帮助你在工作表中输入资料提 示信息的工具. 它有如下功能:

--给用户提供一个选择列表 --限定输入内容的类型或大小 --自定义设置
条件格式选项
编辑条件 格式规则
条件格式：对 不同的数据， 按不同的条件 设置其显示格 式
Indirect(x)

其中的X可以是单元格引用或名称。 若X为单元格引用，它必须用“”引起来，如： =INDIRECT(“A1”)，将返回A1单元格中的内容 若X为单元格名称，它将返回该名称所对应的单元格的 内容。
重庆邮电大学经济管理学院 周玉敏





要求掌握基本的Excel操作，例如：单元格格式的设 置、简单的公式使用等。 对基本概念熟悉，如：绝对引用、相对应用。 以应用案例的方式讲解Excel的相关知识，不再讲解 基本操作部分。 理论学时16学时、实验学时16学时，缺席3次取消考 试资格。 推荐教材：
电子工业出版社 《Excel与管理数据分析（第3版）》杜茂康 主编

推荐学习网站： /forum.php
1.
2.
3. 4. 5.
认识Excel 如何设计表格（2h） 函数的使用（6h） 图表的制作（4h） 数据分析（4h）

Excel的发展历史
你认为利用Excel能做哪些工作？
完成本表涉及到的 知识点： • 单元格命名 • 数据有效性 • 条件格式 • 函数indirect的 用法
1、名字概述
可以给一个单元格或单元格区域取一个名字，这个名字可 以出现在公式中，用来指代引用的单元格或单元格区域， 这会使公式的意义更加明确。 名字其实是一个标识符，由字母或下划线开头的一个或多 个字符组成。在Excel中，还可以用汉字字符作为名字。 名字不区分字母的大小写，比如ABC、abc、Abc都是同 一个名字。 可以为同一单元格或单元格区域定义多个不同的名字。 名字一旦定义就可以在同一工作薄的不同工作表之间共用

销售金额>=100000 合格 70000=<销售金额<100000 警戒数 销售金额<70000 不合格
1.
2.
3. 4. 5.
认识Excel 如何设计表格 函数的使用 图表的制作 数据分析
1.
常用函数使用范例
◦ ◦ ◦ ◦ ◦ 条件函数：If() 统计函数：countif()、countifs() sumif() sumifs() 查找函数：Vlookup()、Index()、Match() 随机函数：rand() 字符函数：len()、right()、left()、mid()
功能： 返回大于等于 0 及小于 1 的均匀分布随机实数

另外，Excel2007增加函数 randbetween(bottom,top)，可生成两个数之间的 随机数。
怎样快速将七年级同学随机安排考场考试？

常用字符函数
◦ LEN(text)
◦ RIGHT(text,n)
◦ LEFT(text,n)
◦ 功能
SUMIFS函数对某一区域内满足多重条件的单元格求和。
【例题】某家电商场的销售记录如下图所示的 A1:G13所示。现要统计出统计每位职工销售各种产 品的总数量，并将它统计在I8:L13区域中。
求和 范围
条件1
条件2
（1）在J10单元格中输入统计李本成销售的彩电总数量的计算公式： =SUMIFS($E$3:$E$13,$D$3:$D$13,$I10,$A$3:$A$13,J$9)
如果我们来做这 张报表，一般会 是什么样呢？
按照我们的习惯， 做出的表格一般是 这样的
同专业人士制作的表 格相比有什么区别呢？
不能看清表格的 内容，但依然能 清楚地了解表格 内容的层次
没有规律的空间 分隔，像蜘蛛网 一样的表格线， 不知从何看起
根据项目类别将不同项目间距扩大，相同项目间距
3、名字的定义
在Excel中，可以为一个独立的单元格、连续的单元格区 域或许多不连续的单元格构成的单元格组合定义一个名字 （或多个名字）
方法一： 选中单元格，名称 框直接输入 方法二： 公式|定义名称
3、名字的应用
1）通过名称在不同的工作表之间传递数据
通过名称查找蔬菜单价
指定A2:B12最 左边为名称
Excel 2007还提供了一个多条件统计函数COUNTIFS，它可 以一次对多个不同区域进行不同条件的计数，其用法如下：
COUNTIFS(range1， criteria1，range2， criteria2， ……，range127， criteria127)
【例题】有学生成绩表如下图所示。统计其中总分 220分以上的人数；统计总分200分以上，物理 80分以下，地理70分以下，化学65分以上的人 数；统计姓王的同学人数。

功能
Match查找的方式
取值 -1 0 1 函数功能 r必须按降序排列，查找大于或等于 x的最小数值 r 不必排序，查找等于x的第一个数值 r必须按升序排列，查找小于或等于x的最大数值

Index(Area，r，c，n)
其中，Area是1个或多个单元格区域；r是某行的行序号，c是某 列的列序号，该函数返回指定的行与列交叉处的单元格引用。如 果r等于0，则返回整行单元格引用，如果c等于0，则返回整列单 元格引用。 当Area包括多个单元格区域时，n=1就表示结果来自于Area中 的第1个区域，n=2表示结果来源于第2个单元格区域……。如 果省略n表示结果来源于第1个单元格区域。
计算text文本的长度
该函数从text文本的右边取出n个字符。 该函数从text文本的左边取出n个字符 该函数从text文本的第n个字符起，取出m个字符。
◦ MID(text,n,m)
=--TEXT(MID(B2,7,6+(LEN(B2)=18)*2),"#-00-00")
概述
数组公式：能够同时对一组或两组以上的数据进行计算，计算 的结果可能是一个，也可能是多个。普通公式只执行一个简单 计算，并且返回一个运算结果。
(2)将此公式向下角复制到I39，

Match格式
◦ Match ( x， r，f ) ◦ 其中x是要查找的数值，r可以是一个数组常量，或某列 （或行）连续的单元格区域，其中可能包含有要查找的 x。f用于指定match的查找方式，它可以是-1，0或1。 ◦ Match(x，r，f)表示的意思是：在数组或连续的单元格 区域r中查找x，并返回x在r中的位置编号。 ◦ 当f为0是，match进行精确查找，当f为1（或-1）时， match进行模糊查找。
(2)将此公式向下角复制到L13，

功能：
Vlookup按列查找的方式从指定数据表区域的最左列查找特定数据，它 能够返回查找区域中与找到单元格位于相同行不同列的单元格内容 。 f是一个逻辑值，表示查找的方 式。 当其为true（或1）时，表 语法： 示模糊查找；当它为false（或0） VLOOKUP(x‚ table ‚n ‚f) 时，表示精确查找。
（2）“工号”查询公式： =VLOOKUP($C$4,按人月汇总!$B:$D,2,0)
（3）查询“单位缴费_养老保险”的公式： =INDEX(按人月汇总!$E$5:$P$31,MATCH(按人分月调阅表!$C$4,按 人月汇总!$B$5:$B$31,),MATCH(按人分月调阅表!$E$4,按人月汇 总!$E$1:$P$1,))
数组公式的建立方法


（1）选中需要保存数组公式结果的单元格或 单元格区域。 （2）输入公式的内容。 （3）按Ctrl+Shift+Enter键。

问题1：在D2:D4求出商品的销售金额。

问题1：在D2:D4求出商品的销售金额。
一般解决方法：三个普通公式
问题1：在D2:D4求出商品的销售金额。 数组公式解决：
◦ 画表格 ◦ 计算：求和、计算平均值
结论：Excel易学、功能简单
你能得多少 分呢？
咱们做一点测试题吧
1.
2.
3. 4. 5.
认识Excel 如何设计表格 函数的使用 图表的制作 数据分析

专业化表格设计原则 利用“条件格式”和“数据有效性”Байду номын сангаас计友好表格

•整张报表很少用表 格线，却非常清晰、 明了 •作为一个局外人我 们可以在最短的时 间了解报表的内容 和所要表达的意图
SUMIF（Range‚Criteria‚Sum_Range）
Sum_range为需要求和的单 元格、区域或引用
Range为用于条件判断 的单元格区域
Criteria是由数字、逻辑表 达式等组成的判定条件
条件求和函数SUMIFS
用法 SUMIFS(sum_range, range1,criteria1, range2,criteria2…) 其中range1, range2, … 是计算关联条件的 1 至 127 个区域 ；Criteria1, criteria2, … 是数字、表达式、单元格引用或文本 形式的 1 至 127 个条件，用于定义要对哪些单元格求和。这些 区域与条件是对应的，即 Criteria1 是用于 range1 区域的条件， criteria2是用于 range2的条件，以此类推。 Sum_range是求和 区域。