王佩丰Excel24讲_第24讲：宏表函数

毛媛媛

已于 2023-08-07 19:59:49 修改

阅读量901

点赞数

分类专栏：数据分析四大工具之一：Excel 文章标签： excel 数据分析职场和发展程序人生学习

于 2023-08-05 15:49:50 首次发布

本文链接：https://blog.csdn.net/myy12456789/article/details/131904939

版权

30 篇文章 22 订阅

订阅专栏

一、利用宏表函数获取信息

GET.CELL(type_num, reference)
获取单元格信息。第二个参数含义是：获取哪个单元格的信息；第一个参数的含义是：获取这个单元格的什么信息。

常用的type_num如下：
在这里插入图片描述

示例1：如何计算单元格的颜色，并用数字标记出来

宏表函数不能直接在excel表中写，要怎么写呢？
- 点击A2单元格，“插入-名称管理器-新建名称”，“名称：计算颜色，引用位置：=get.cell(63,a2)（宏表函数）”
- 向单元格中输入“=计算颜色”，回车，发现红色被标记为了数字3，黄色被标记为数字6了
示例2：计算D列的公式是什么
- 点击D2单元格，“插入-名称管理器-新建名称”，“名称：提取公式，引用位置：=get.cell(6,d2)（宏表函数）”
- 向单元格中输入“=提取公式”，回车，发现D列数字对应的公式都显示出来了

GET.WORKBOOK(type_num, name_text)
获取当前工作簿的信息。第二个参数是：打开的工作簿的名字；第一个参数是：指明要得到的工作簿信息类型的数。

HYPERLINK(链接位置,[显示文本])
创建一个快捷方式（跳转），用以打开存储在网络服务器、Intranet或Internet中的文件

EVALUATE(文本公式)
对以文字表示的一个公式或表达式求值，并返回结果（将不可以运算的公式变成可以运算的公式）

示例1：如何将没有等号的公式运算出结果
- 点击B3单元格，“插入-名称管理器-新建名称”，“名称：运算，引用位置：=evaluate(a3)（宏表函数）”
- 向单元格中输入“=运算”，回车，发现A列数字对应的公式都在B列计算出结果了
示例2：如何将没有等号且没有运算符的数据运算出结果
- 首先利用substitute()函数将单元格中的逗号替换成加号，然后再利用evaluate函数运算出结果
- 先复制公式，然后点击B3单元格，“插入-名称管理器-新建名称”，“名称：计算总分，引用位置：=evaluate(SUBSTITUTE(A9,“,”,“+”))（宏表函数）”
- 向单元格中输入“=计算总分”，回车，发现A列对应的数字都在B列计算出结果了

SUBSTITUTE(字符串,原字符串,新字符串,[替换序号])
将字符串中的部分字符替换成新字符串

示例2：还可以用数组的形式写，如下：
- 将A9单元格左右两边分别加上大括号"{}"，中间用&连接
- 复制公式，然后点击D9单元格，“插入-名称管理器-新建名称”，“名称：计算总分2，引用位置：=evaluate(“{”&a9&“}”)（宏表函数）”
- 向单元格中输入“=sum(计算总分2)”，回车，发现A列对应的数字都在D列计算出结果了
示例3：任意得到一大组字符串的某一段
- 首先将这组字符串转换成数组，字符串之间用分号;隔开，公式为“=“{”&SUBSTITUTE(A16," “,”;“)&”}"”
- 复制公式，然后点击B16单元格，“插入-名称管理器-新建名称”，“名称：计算提取，引用位置：=evaluate(“{”&SUBSTITUTE(A16," “,”;“)&”}") （宏表函数）”
- 向单元格中输入“=INDEX(计算提取,4)”，回车，发现要取的字符串都放在B列了

REFTEXT(ACTIVE.CELL())
获取当前活动单元格的地址

示例1
- 首先自命名一个新公式“当前单元格”，“公式=REFTEXT(ACTIVE.CELL())&T(NOW())”
- 然后选中全部数据，点击“开始-条件格式-新建规则”，分别设置行和列的规则，选择“使用公式确定要设置格式的单元格”，输入公式“=COLUMN()=MID(当前单元格,FIND(“C”,当前单元格)+1,100)*1”和“=ROW()=MID(当前单元格,2,FIND(“C”,当前单元格)-2)*1”，并将预览颜色设置为黄色。
- 最后，按住F9键可点击数据查看效果，即按住F9，点击哪个单元格，哪个单元格所在的行列就会显示为黄色