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)

马毅

马毅

马毅

小葛

小葛

小菊

小葛

小红

小菊

李佳

小红

王恒

李佳

王恒

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.rhkb.cn/news/423960.html

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

国际快递跟集运有什么区别？怎么做才能好集运？

国际快递跟集运有什么区别？怎么做才能好集运？

在国际物流的舞台上&#xff0c;海外集运和国际快递是两种备受瞩目的运输方式&#xff0c;那两者之间有什么区别呢&#xff1f; 国际快递其实类似于国内快递&#xff0c;只是运输终点是海外。一般由公司或个人直接向海外邮寄&#xff0c;采用飞机运输&#xff0c;3 - 5 天就能…
阅读更多...
IntelliJ IDE 插件开发 | （十二）自定义项目脚手架（上）

IntelliJ IDE 插件开发 | （十二）自定义项目脚手架（上）

系列文章 本系列文章已收录到专栏&#xff0c;交流群号&#xff1a;689220994&#xff0c;也可点击链接加入。 前言 在开发创建一个新项目的时候&#xff0c;我们一般都会使用平台自带的脚手架&#xff0c;如下图所示&#xff1a; 或者是使用网页版&#xff1a; 尽管平台已经…
阅读更多...
先楫HPM6750 Windows下VSCode开发环境配置

先楫HPM6750 Windows下VSCode开发环境配置

用的是EVKmini&#xff0c;ft2232作为调试器jtag接口调试 启动start_gui.exe 以hello_world为例&#xff0c;更改一下build path&#xff0c;可以generate并使用gcc compile 最后会得到这些 点击start_gui里面的命令行&#xff0c;用命令行启动vscode 新建.vscode文件夹&…
阅读更多...
如何让Windows控制台窗口不接受鼠标点击（禁用鼠标输入）

如何让Windows控制台窗口不接受鼠标点击（禁用鼠标输入）

一、简述 在我们编写控制台应用程序时&#xff0c;默认情况下程序的打印输出会在控制台窗口中进行显示&#xff0c;我们在写服务功能时在窗口中会不断打印消息输出&#xff0c;这个时候如果使用鼠标点击了控制台窗口&#xff0c;会阻塞程序的继续运行&#xff0c;导致我们的程…
阅读更多...
vue使用TreeSelect设置带所有父级节点的回显

vue使用TreeSelect设置带所有父级节点的回显

Element Plus的el-tree-select组件 思路&#xff1a; 选中节点时&#xff0c;给选中的节点赋值 pathLabel&#xff0c;pathLabel 为函数生成的节点名字拼接&#xff0c;数据源中不包含。 在el-tree-select组件中设置 props“{ label: ‘pathLabel’ }” 控制选中时input框中回…
阅读更多...
树莓派智能语音助手实现音乐播放

树莓派智能语音助手实现音乐播放

树莓派语音助手从诞生的第一天开始&#xff0c;我就想着让它能像小爱音箱一样&#xff0c;可以语音控制播放音乐。经过这些日子的倒腾&#xff0c;今天终于实现了。 接下里&#xff0c;和大家分享下我的实现方法&#xff1a;首先音乐播放模块用的是我在上一篇博文写的《用sound…
阅读更多...
基于spring的博客系统（二）

基于spring的博客系统（二）

4. 业务代码 4.1 持久层 根据需求, 先⼤致计算有哪些DB相关操作, 完成持久层初步代码, 后续再根据业务需求进⾏完善 1. ⽤⼾登录⻚ a. 根据⽤⼾名查询⽤⼾信息 2. 博客列表⻚ a. 根据id查询user信息 b. 获取所有博客列表 3. 博客详情⻚ a. 根据博客ID查询博客信息 b. 根据博客I…
阅读更多...
现代 Web 开发工具箱：Element-UI 表单组件全攻略（二）

现代 Web 开发工具箱：Element-UI 表单组件全攻略（二）

现代 Web 开发工具箱&#xff1a;Element-UI 表单组件全攻略&#xff08;二&#xff09; 一 . Switch 开关控件1.1 Switch 组件的创建① 注册路由② 创建 Switch 组件 1.2 Switch 组件的属性① 开关的宽度② 开关 打开/关闭 的文字提示③ 开关打开或者关闭时候的值④ 开关打开或…
阅读更多...
Qt控制开发板的LED

Qt控制开发板的LED

Qt控制开发板的LED 使用开发板的IO接口进行控制是嵌入式中非常重要的一点&#xff0c;就像冯诺依曼原理说的一样&#xff0c;一个计算机最起码要有输入输出吧&#xff0c;我们有了信息的接收和处理&#xff0c;那我们就要有输出。 我们在开发板上一般都是使用开发板的GPIO接口…
阅读更多...
测试通用面试题大全

测试通用面试题大全

24年软件测试的发展如何&#xff1f; 1、IT行业还会继续升温&#xff0c;高质量人才需求相对还是短缺。 2、要求变高之后&#xff0c;很难再下降了&#xff0c;学历和经验。 3、功能测试之外的东西&#xff0c;接口、性能和自动化要掌握一点。 4、长远来看&#xff0c;软件…
阅读更多...
数据集 wider_face 人脸数据集 人脸检测 ＞＞ DataBall

数据集 wider_face 人脸数据集 人脸检测 ＞＞ DataBall

数据集 wider 人脸检测数据集 WIDER FACE: A Face Detection Benchmark inproceedings{yang2016wider, Author {Yang, Shuo and Luo, Ping and Loy, Chen Change and Tang, Xiaoou}, Booktitle {IEEE Conference on Computer Vision and Pattern Recognition (CVPR)}, Title…
阅读更多...
Radware Alteon 负载均衡-基于URL Filetype的七层负载均衡

Radware Alteon 负载均衡-基于URL Filetype的七层负载均衡

作者&#xff1a;Xiaolei Ren Radware Alteon作为一款高性能的负载均衡器&#xff0c;其基于URL Filetype的七层负载均衡功能为众多企业提供了灵活、高效的解决方案。 该案例实现如下需求&#xff1a;当客户端访问服务器时&#xff0c;默认访问10.200.1.100&#xff0c;在ht…
阅读更多...
【Ubuntu】Ubuntu双网卡配置 实现内外网互不影响同时可用

【Ubuntu】Ubuntu双网卡配置 实现内外网互不影响同时可用

【Ubuntu】Ubuntu双网卡配置 实现内外网互不影响同时可用 建议前提配置用到的命令参考文献&#xff1a; 建议 本文仅作个人记录&#xff0c;请勿完全照搬&#xff0c;建议直接看此视频&#xff0c;按作者的步骤进行配置 linux配置内外网&#xff08;ubuntu举例&#xff09;&am…
阅读更多...
决策树算法上篇

决策树算法上篇

决策树概述 决策树是属于有监督机器学习的一种&#xff0c;起源非常早&#xff0c;符合直觉并且非常直观&#xff0c;模仿人类做决策的过程&#xff0c;早期人工智能模型中有很多应用&#xff0c;现在更多的是使用基于决策树的一些集成学习的算法。 示例一&#xff1a; 上表根据…
阅读更多...
Sparse4D v1

Sparse4D v1

Sparse4D: Multi-view 3D Object Detection with Sparse Spatial-Temporal Fusion Abstract 基于鸟瞰图 (BEV) 的方法最近在多视图 3D 检测任务方面取得了重大进展。与基于 BEV 的方法相比&#xff0c;基于稀疏的方法在性能上落后&#xff0c;但仍然有很多不可忽略的优点。为了…
阅读更多...
四数之和--力扣18

四数之和--力扣18

四数之和 题目思路代码 题目 思路 类似于三数之和&#xff0c;先排序&#xff0c;利用双指针解题。 如果排序后的第一个元素大于目标值&#xff0c;直接返回&#xff0c;为什么nums[i]需要大于等于0&#xff0c;因为目标值可能为负数。比如&#xff1a;数组是[-4, -3, -2, -1…
阅读更多...
大数据安全需求分析与安全保护工程

大数据安全需求分析与安全保护工程

大数据安全威胁与需求分析 特征&#xff1a;海量是数据规模、快速的数据流转、多样的数据类型和价值密度低 种类和来源&#xff1a;结构化、半结构化和非结构化数据 数据种类&#xff1a; 结构化数据&#xff1a;关系模型数据&#xff0c;以关系数据库表形式管理的数据 非…
阅读更多...
Docker：对已有的容器，对当前容器映射的端口实时 (增删改查)

Docker：对已有的容器，对当前容器映射的端口实时 (增删改查)

首先我的docker已经起了一个容器&#xff0c;我突然想把他的80->80映射的端口改成80->8080 但是我不想去新启动容器&#xff0c;想在现有容器基础上去修改&#xff0c;或者我想删除某个端口映射&#xff08;只是大概思路&#xff09; 如何寻找容器配置文件位置 首先我这…
阅读更多...
Linux系统使用Docker安装DockerUI并实现远程管理本地容器无需公网IP

Linux系统使用Docker安装DockerUI并实现远程管理本地容器无需公网IP

文章目录 前言1. 安装部署DockerUI2. 安装cpolar内网穿透3. 配置DockerUI公网访问地址4. 公网远程访问DockerUI5. 固定DockerUI公网地址 前言 DockerUI是一个docker容器镜像的可视化图形化管理工具。DockerUI可以用来轻松构建、管理和维护docker环境。它是完全开源且免费的。基…
阅读更多...
立足本土，面向全球 | 全视通闪耀亮相Medical Fair Asia新加坡医疗展

立足本土，面向全球 | 全视通闪耀亮相Medical Fair Asia新加坡医疗展

Medical Fair Asia是亚洲地区最大的医疗设备、医疗器械和医疗技术展览会之一&#xff0c;自1997年创办以来&#xff0c;每两年在新加坡举办一次。该展会不仅是新加坡医疗行业交流的龙头平台&#xff0c;也是亚洲乃至全球医疗企业和专业人士共聚一堂、展示最新产品和技术的重要舞…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023