Excel 基础知识-操作手册2

十、查找与引用函数

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:D5, 2, FALSE)

     这将在A1:D5的范围内查找“产品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,D51:D62,"查无此人")  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:Q$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,D1:D10")

   ```

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], ...)

十三、财务金融函数

1. 单利与复利
2. 单利：指按照固定的本金计算利息，即本金固定，到期后一次性结算利息，而本金所产生的利息不再计算利息，比如：银行的定期存款。
3. 复利：指在每经过一个计息期后，都要将所产生利息加入本金，以计算下期的利息。

单利：公式：=$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

十四、工程函数：

1. 数字进制转换函数

在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)

1. 将二维数组中提取子数组

某公司员工信息表的部分内容

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:L40)

马毅		马毅
马毅		小葛
小葛		小菊
小葛		小红
小菊		李佳
小红		王恒
李佳
王恒