目录
- 1. 聚合函数(Aggregation Function)
- 2. 逻辑函数(Logical Function)
- 3.条件型聚合函数(Conditional Aggregation Function)
- 4. 数据透视表(Pivot Tables)
- 5. 已命名区域(Named Range)
- 6. 查找函数(VLOOKUP)
1. 聚合函数(Aggregation Function)
聚合是一种将多个数字转换为较少数字(通常为一个数字)的方法。 可以同时对一组数据进行运算,并形成单个值, 我们感兴趣的数据本质上是统计量,例如函数SUM, AVERAGE, MAX, MEDIAN, STDEV
2. 逻辑函数(Logical Function)
-
比较运算符
含义 | 运算符 |
---|---|
大于 | > |
小于 | < |
等于 | = |
小于等于 | <= |
大于等于 | >= |
不等于 | <> |
-
IF函数
=IF(condition, value if TRUE, [value if FALSE])
- 如果条件为真, 执行
value if True
语句- 如果条件为假, 则执行
[value if FALSE]
语句
🔐如果图示订单中苹果多余橘子, 则输出Apples Rule!, 否则输出"Oranges Rock"
🔑解析:
condition:苹果比橘子多
value if TRUE: "Apples Rule"
[value if FALSE]: "Oranges Rock"
=IF(B2>C2,"Apples Ruls","Oranges Rock")
-
AND函数
=AND(condition1, condition2, ...)
- 只有所有条件为真时,才为真
- 和if一样AND是函数, 不是运算符
在IF语句中使用AND函数
=IF(AND(condition1, condition2, ...), value if TRUE, [value if FALSE])
-
OR函数
=OR(condition1, condition2, ...)
- 只要有一个条件为真,则为真
- 和if一样OR是函数, 不是运算符
在IF语句中使用OR函数
=IF(OR(condition1, condition2, ...), value if TRUE, [value if FALSE])
-
NOT函数
略
3.条件型聚合函数(Conditional Aggregation Function)
用逻辑条件进行聚合的统计学函数, 它们使用一些逻辑条件对一组数据进行运算.
-
COUNTIF
=COUNTIF(range, condition)
- 如果条件为真, 则计数
- 注意: 条件判断中的字符或数字要加引号
🔐假设有一个棒球数据集, 你的分析问题是, 球队阵容中有多少为pitcher(投手)?
🔑
=COUNTIF(D:D,"=Pitcher")
🔐假设如果想计数薪资大于一千万的人数?
🔑
=COUNTIF(C:C,">10000000")
-
SUMIF
=SUMIF(range, condition)
- 与COUNTIF原理类似, 如果条件为真, 则求和
- 注意: 条件判断中的字符或数字要加引号
🔐假设如果想计数薪资大于一千万的人的薪资之和?
🔑
=SUMIF(C:C,">10000000")
4. 数据透视表(Pivot Tables)
数据透视表一步到位的聚合汇总.
image.png在以上棒球数据集中, 每个球队有好几行, 每个position也有好几行, 数据透视表可以对所有球队和所有位置分组.
- 首先选中数据集
- 点击菜单栏中的
insert
(插入), 然后点击最左侧的PivotTable
- 在弹出得对话框中, 选择默认得
New Worksheet
然后点击OK
- 在新的工作表选中
Team
和Position
, 后将position
拖动到Columns
,Team
在Rows
得到下图
image.png
现在有Salary
(薪资)和Names
可以选择来进行汇总, 因为我们只想知道每到球队每个位置有多少名球员, 因此只需要将姓名进行聚合. 默认得聚合函数是count(计数)
- 将
Name
拖动到Values
列进行聚合, 即可得到想要的答案
image.png
如果想知道每个球队在各个位置球员上花费多少成本?
为了解决这个问题, 可以将Values
的中Name
替换成Salary
, **同时将Value Field Settings
默认的计数函数, 更改为Sum
5. 已命名区域(Named Range)
当我们介绍区域时,我提到可以对区域命名,不必使用列和行地址来引用它们,为了展示命名区域的作用,使用如下的水果摊电子表格
image.png对单元格或区域进行命名的基本方法是:
- 选中单元格或区域,我将选中具有苹果价格的单元格
- 然后转到
Formulas
“公式”菜单并点击Define Name
“定义名称”.
image.png
我可以在这里输入想要的名称,但是 Excel 已经推断出我想要使用,相邻单元格里的文本作为标签,Excel 还显示出了将被命名的区域地址,包含工作表名称 紧跟着感叹号, 然后是绝对地址,我们要的就是这个地址,因此接受该地址并点击确定
现在该单元格不仅可以用行和列来引用,而且可以用名称“apple_price”,如果我选择该单元格 左侧名称框中甚至显示出该名称,确认该单元格名称为 apple_price,对于其他价格,可以重复相同的流程
image.png
之前 当我们为水果摊创建收入计算公式时,我们将价格设为了绝对地址,公式是这样的 很长并且很难看出其中的含义
image.png我将使用命名区域来重写该公式,我们将苹果数量乘以价格 也就是 apple_price,我开始输入内容,按下 Tab 键自动填充,继续对每个水果进行修改 直到输入整个公式
image.png现在更容易读懂了,我可以快速明白该公式的作用 向下填充,这就是命名区域的一个用途,用名称来识别我们将在公式中用到的单个值,命名区域还可以用来命名更大的区域,并用于查找 接下来我们将了解如何执行这一操作
image.png
6. 查找函数(VLOOKUP)
假设有个零件编号或缩写列表,对应的是价格或说明,我们可能在一个表格中有简短信息,但是需要在其他地方查找更多信息,Lookup 函数就是这个作用,它们使我们能够使用关键字在表格中查找其他信息,有水平和垂直查找函数,重点讲解一种函数 VLOOKUP
我有一个机场代码列表,假设我想知道哪个机场叫做 MCI,最左侧的列是键 第二列是答案,向下滚动 发现 MCI 是堪萨斯城国际机场
image.png执行这一操作的函数是 VLOOKUP
- 输入 =VLOOKUP 要查找的值,也就是 ATL
- 然后是数据区域,选择这两列
- 然后是列索引,即第二列,然后输入 false 和右括号
如果机场代码不在列表中呢?
image.png我们试试 ZZZ 它不在列表中,出现报错 这是因为我在公式中输入了 FALSE,表明我只想完全匹配,如果没有在公式中输入 FALSE 呢?,Excel 将返回最接近的匹配项 对这道题来说错了,一个规则是,使用 VLOOKUP 查找信息时,在此可选参数中输入 FALSE,找到错误的机场比出现报错更糟糕.
还有一点,这里使用列作为区域,虽然也可行,但是使用命名区域更好,更容易阅读并且不容易出错,我将这个命名为 airport_lookup,效果是一样的,甚至可以填充到其他单元格 不用担心丢失绝对地址
网友评论