目录

• 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"

image.png

🔑解析:
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(投手)?

image.png

🔑
=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

image.png

• 在新的工作表选中Team和Position, 后将position拖动到Columns, Team在Rows得到下图
  image.png

现在有Salary(薪资)和Names可以选择来进行汇总, 因为我们只想知道每到球队每个位置有多少名球员, 因此只需要将姓名进行聚合. 默认得聚合函数是count(计数)

• 将Name拖动到Values列进行聚合, 即可得到想要的答案
  image.png

如果想知道每个球队在各个位置球员上花费多少成本?

为了解决这个问题, 可以将Values的中Name替换成Salary, **同时将Value Field Settings默认的计数函数, 更改为Sum

image.png

 
 

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

image.png

• 然后是数据区域,选择这两列

image.png

• 然后是列索引,即第二列,然后输入 false 和右括号

image.png

如果机场代码不在列表中呢?

image.png

我们试试 ZZZ 它不在列表中,出现报错 这是因为我在公式中输入了 FALSE,表明我只想完全匹配,如果没有在公式中输入 FALSE 呢？,Excel 将返回最接近的匹配项 对这道题来说错了,一个规则是,使用 VLOOKUP 查找信息时,在此可选参数中输入 FALSE,找到错误的机场比出现报错更糟糕.

还有一点,这里使用列作为区域,虽然也可行,但是使用命名区域更好,更容易阅读并且不容易出错,我将这个命名为 airport_lookup,效果是一样的,甚至可以填充到其他单元格 不用担心丢失绝对地址