十、查找与引用函数
Excel中的查找与引用函数非常丰富，以下是一些重要的函数及其使用示例：
1. **VLOOKUP**
- 语法：`VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`
- 示例：假设A列是员工编号，B列是员工姓名，你想根据员工编号查找员工姓名。
公式： =VLOOKUP(123, A2:B100, 2, FALSE)
这将在A2:B100的范围内查找编号123，并返回同一行的B列中的员工姓名。
2. **HLOOKUP**
- 语法：`HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])`
- 示例：假设第一行是产物名称，第一列是季度，你想找到特定产物在第二季度的销售数据。
公式： =HLOOKUP("产物X", A1 5, 2, FALSE)
这将在A15的范围内查找“产物X”，并返回第二行的数据。
3. **LOOKUP**
- 语法：`LOOKUP(lookup_value, lookup_vector, [result_vector])`
- 示例：假设A列是分数，B列是品级，你想根据分数查找品级。
公式： =LOOKUP(85, A1:B5)
这将在A1:B5的范围内查找85，并返回相应的品级。
4. **INDEX**
- 语法：`INDEX(array, row_num, [column_num])`
- 示例：假设A1:C10是一个数据表，你想获取第二行第三列的数据。
公式： =INDEX(A1:C10, 2, 3)
这将返回A1:C10范围内第二行第三列的值。
5. **MATCH**
- 语法：`MATCH(lookup_value, lookup_array, [match_type])`
- 示例：假设A列是员工编号，你想找出编号123在A列中的位置。
公式： =MATCH(123, A1:A100, 0)
这将返回编号123在A1:A100中的位置。
6. **OFFSET**
- 语法：`OFFSET(reference, rows, cols, [height], [width])`
- 示例：假设你想引用A1单元格下方5行、右侧3列的单元格。
公式： =OFFSET(A1, 5, 3)
这将返回A1下方5行、右侧3列的单元格引用。
7. **CHOOSE**
- 语法：`CHOOSE(index_num, value1, [value2], ...)`
- 示例：假设你想根据条件选择返回值，条件是1返回"Apple"，是2返回"Banana"。
公式： =CHOOSE(2, "Apple", "Banana")
这将返回"Banana"。
8. **ROW**
- 语法：`ROW([cell])`
- 示例：获取当前单元格的行号。
公式：=ROW()
如果这个公式在第5行，它将返回5。
9. **COLUMN**
- 语法：`COLUMN([cell])`
- 示例：获取当前单元格的列号。
公式： =COLUMN()
如果这个公式在B列，它将返回2。
10. **INDIRECT**
- 语法：`INDIRECT(ref_text, [a1])`
- 示例：假设A1单元格包含文本"B2"，你想引用B2单元格的值。
公式：=INDIRECT(A1)
这将返回B2单元格的值。
11. **ADDRESS**
- 语法：`ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])`
- 示例：获取第3行第4列单元格的地址。
公式： =ADDRESS(3, 4)
这将返回"$D$3"。
12. **AREAS**
- 语法：`AREAS(reference)`
- 示例：如果一个单元格包含一个由多个区域构成的数组公式，你想找出区域的数目。
公式： =AREAS(A1)
如果A1包含一个跨越多个区域的数组公式，它将返回区域的数目。
十一、LOOKUP函数查找
以下是 `XLOOKUP` 函数的根本语法：
XLOOKUP(
lookup_value,
lookup_array,
return_array,
[if_not_found],
[match_mode],
[search_mode]
)
参数说明：
- `lookup_value`：要查找的值。
- `lookup_array`：要搜索的数组或范围。
- `return_array`：返回结果的数组或范围。
- `[if_not_found]`：如果找不到 `lookup_value`，返回的值（可选）。
- `[match_mode]`：指定匹配范例，0 表示准确匹配，1 表示近似匹配（可选）。
- `[search_mode]`：指定搜索方式，-1 表示从后向前搜索，1 表示从前向后搜索（可选）。
以下是一些 `XLOOKUP` 函数的常见用法示例：
1. **根本查找**：
```excel
=XLOOKUP("查找值", A1:A10, B1:B10)
```
2. **查找并返回默认值**：
```excel
=XLOOKUP("查找值", A1:A10, B1:B10, "未找到")
```
举例：现有D47~G51列为员工信息表，要求根据I48列的员工号查询并返回员工姓名。如果查无匹配结果，则返回字符串：“查无此人”。
公式：=XLOOKUP(I51,E51:E62,D5162,"查无此人") I51：查找值
| 姓名
| 工号
| 籍贯
| 学历
|
| 工号
| 姓名
| | 小燕
| EHS-01
| 甘肃
| 本科
|
| EHS-01
| 小燕
| | 小红
| EHS-02
| 合肥
| 专科
|
| EHS-02
| 小红
| | 小葛
| EHS-03
| 上海
| 硕士
|
| EHS-03
| 小葛
| | 小菊
| EHS-04
| 蚌埠
| 中专
|
| EHS-19
| 查无此人
|
3. **准确匹配**：
```excel
=XLOOKUP("查找值", A1:A10, B1:B10, "未找到", 0)
```
举例：考核品级表
公式：=XLOOKUP(N48,Q$49 $52,R$49:R$52,"",-1)
| 姓名
| 序号
| 成绩
| 品级
|
| 品级对照表
| | 小燕
| 1
| 86
| 良好
|
| 分数
| 品级
| | 小红
| 2
| 88
| 良好
|
| 80
| 良好
| | 小葛
| 3
| 78
| 合格
|
| 0
| 不合格
| | 小菊
| 4
| 84
| 良好
|
| 90
| 优秀
| | 小康
| 5
| 49
| 不合格
|
| 60
| 合格
|
4. **近似匹配**（通常用于数值数据）：
```excel
=XLOOKUP(9.5, A1:A10, B1:B10, "未找到", 1)
```
5. **从后向前搜索**：
```excel
=XLOOKUP("查找值", A1:A10, B1:B10, "未找到", 0, -1)
```
6. **查找并返回多个值**：
```excel
=XLOOKUP("查找值", A1:A10, B1:B10, "未找到", 0, 1, "C1:C10,D110")
```
7. **使用行和列索引**：
```excel
=XLOOKUP("查找值", A1:A10, B1:B10, "未找到", 0, 1, 2)
```
8. **查找并返回数组**：
```excel
=XLOOKUP("查找值", A1:A10, B1:B10, "未找到", 0, 1, {1,2})
```
9. **使用 `XLOOKUP` 进行错误处理**：
```excel
=IFERROR(XLOOKUP("查找值", A1:A10, B1:B10), "错误处理")
```
10. **使用 `XLOOKUP` 进行条件查找**：
```excel
=XLOOKUP("查找值", A1:A10, B1:B10, "未找到", 0, 1, "条件范围")
```
十二、统计与求和
在Excel中，有多种函数可以用来进行统计和求和操作。以下是一些最常用的统计和求和函数：
### 1. SUM
用于对一系列数值进行求和。
```excel
=SUM(number1, [number2], ...)
```
例如：
```excel
=SUM(A1:A10)
```
### 2. SUMIF
用于对满足特定条件的单元格进行求和。
```excel
=SUMIF(range, criteria, [sum_range])
```
例如：
```excel
=SUMIF(A1:A10, ">10", B1:B10)
```
### 3. SUMIFS
用于对满足多个条件的单元格进行求和。
```excel
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
```
例如：
```excel
=SUMIFS(B1:B10, A1:A10, ">10", C1:C10, "<100")
```
### 4. SUMPRODUCT
用于对数组中对应元素的乘积进行求和。
```excel
=SUMPRODUCT(array1, [array2], ...)
```
例如：
```excel
=SUMPRODUCT(A1:A10, B1:B10)
```
### 5. COUNT
用于统计范围内的数值单元格数目。
```excel
=COUNT(value1, [value2], ...)
```
例如：
```excel
=COUNT(A1:A10)
```
### 6. COUNTA
用于统计范围内非空单元格的数目。
```excel
=COUNTA(value1, [value2], ...)
```
例如：
```excel
=COUNTA(A1:A10)
```
### 7. COUNTIF
用于统计满足特定条件的单元格数目。
```excel
=COUNTIF(range, criteria)
```
例如：
```excel
=COUNTIF(A1:A10, ">10")
```
### 8. COUNTIFS
用于统计满足多个条件的单元格数目。
```excel
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
```
例如：
```excel
=COUNTIFS(A1:A10, ">10", B1:B10, "<100")
```
### 9. AVERAGE
用于计算一系列数值的平均值。
```excel
=AVERAGE(number1, [number2], ...)
```
例如：
```excel
=AVERAGE(A1:A10)
```
### 10. AVERAGEIF
用于计算满足特定条件的单元格的平均值。
```excel
=AVERAGEIF(range, criteria, [average_range])
```
例如：
```excel
=AVERAGEIF(A1:A10, ">10", B1:B10)
```
### 11. AVERAGEIFS
用于计算满足多个条件的单元格的平均值。
```excel
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
```
例如：
```excel
=AVERAGEIFS(B1:B10, A1:A10, ">10", C1:C10, "<100")
```
### 12. SUMSQ
用于计算一系列数值的平方和。
```excel
=SUMSQ(number1, [number2], ...)
```
例如：
```excel
=SUMSQ(A1:A10)
```
###13.中位数
计算一组数值的中位数可以使用 MEDIAN 函数。
公式=MEDIAN(number1, [number2], ...)
十三、财政金融函数
- 单利与复利
- 单利：指按照固定的本金计算利息，即本金固定，到期后一次性结算利息，而本金所产生的利息不再计算利息，比如：银行的定期存款。
- 复利：指在每颠末一个计息期后，都要将所产生利息参加本金，以计算下期的利息。
单利：公式：=$E$46*$E$47*$D50
举例：E46:利率值，E47：本金值，D50：期数值
复利：公式：=$E$47*((1+$E$46)^$D50-1)
| 利率
| 8%
|
| | 本金
| 200
|
|
|
|
| | 期数
| 单利
| 复利
| | 1
| 16
| 16
| | 2
| 32
| 33
| | 3
| 48
| 52
| | 4
| 64
| 72
| | 5
| 80
| 94
| | 6
| 96
| 117
| | 7
| 112
| 143
|
十四、工程函数：
在Excel中，你可以使用一些内置函数来实现数字的进制转换。以下是一些常用的进制转换函数：
1. **DEC2BIN**：将十进制数转换为二进制数。
- 语法：`DEC2BIN(number, [places])`
- 例如：`=DEC2BIN(10, 8)` 会返回 `1010`
2. **DEC2HEX**：将十进制数转换为十六进制数。
- 语法：`DEC2HEX(number, [places])`
- 例如：`=DEC2HEX(255, 3)` 会返回 `FF`
3. **DEC2OCT**：将十进制数转换为八进制数。
- 语法：`DEC2OCT(number, [places])`
- 例如：`=DEC2OCT(10, 4)` 会返回 `12`
4. **BIN2DEC**：将二进制数转换为十进制数。
- 语法：`BIN2DEC(number)`
- 例如：`=BIN2DEC(1010)` 会返回 `10`
5. **HEX2DEC**：将十六进制数转换为十进制数。
- 语法：`HEX2DEC(number)`
- 例如：`=HEX2DEC(FF)` 会返回 `255`
6. **OCT2DEC**：将八进制数转换为十进制数。
- 语法：`OCT2DEC(number)`
- 例如：`=OCT2DEC(12)` 会返回 `10`
8.提取子数组
常常需要从一列或多列数据中取出部分数据进行再处理。
公式：FILTER(array, include, [if_empty])
- array：要筛选的数组或范围。
- include：筛选条件。可以是一个逻辑表达式，也可以是一个数组或范围。
- if_empty：（可选）如果没有符合条件的数据，返回的替代值。
举例：这将返回 A1:A10 范围内所有大于 50 的值。
公式：=FILTER(A1:A10, A1:A10 > 50)
某公司员工信息表的部分内容
8.1提取区域中第二行的数据，返回一个一维横向的内存数组：
公式：=INDEX(H46:K57,2,0)
8.2截取区域第三列的数据，返回一个一维纵向的内存数组：
公式：=INDEX(H46:K57,0,3)
8.3 筛选出区域内学历为“本科”的员工的数据
公式：=FILTER(H46:K57,I46:I57="本科")
| 员工号
| 学历
| 姓名
| 籍贯
|
| 员工号
| 学历
| 姓名
| 籍贯
| | EHS-01
| 本科
| 小燕
| 甘肃
|
| EHS-02
| 专科
| 小红
| 合肥
| | EHS-02
| 专科
| 小红
| 合肥
|
| EHS-01
| 本科
| 小燕
| 甘肃
| | EHS-03
| 硕士
| 小葛
| 上海
|
| EHS-05
| 本科
| 小康
| 西宁
| | EHS-04
| 中专
| 小菊
| 蚌埠
|
| EHS-06
| 本科
| 李佳
| 成都
| | EHS-05
| 本科
| 小康
| 西宁
|
| EHS-10
| 本科
| 何泽
| 北京
| | EHS-06
| 本科
| 李佳
| 成都
|
| EHS-12
| 本科
| 小花
| 呼和浩特
| | EHS-07
| 专科
| 王恒
| 重庆
|
|
|
|
|
|
注意：FILTER筛选了学历为本科的所有员工信息
9、去重查询应用
公式：=UNIQUE(L14 40)
| 马毅
|
| 马毅
| | 马毅
|
| 小葛
| | 小葛
|
| 小菊
| | 小葛
|
| 小红
| | 小菊
|
| 李佳
| | 小红
|
| 王恒
| | 李佳
|
|
| | 王恒
|
|
|
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。 |
