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

文章目录

• 一、利用宏表函数获取信息
• • 1.get.cell函数
  • 2.get.workbook函数
• 二、宏表函数常见应用
• • 1.EVALUATE函数
  • 2.REFTEXT(ACTIVE.CELL())
• 系列文章目录

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

1.get.cell函数

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

常用的type_num如下：

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

  宏表函数不能直接在excel表中写，要怎么写呢？

  • 点击A2单元格，“插入-名称管理器-新建名称”，“名称：计算颜色，引用位置：=get.cell(63,a2)（宏表函数）”
    
  • 向单元格中输入“=计算颜色”，回车，发现红色被标记为了数字3，黄色被标记为数字6了
    
    

• 示例2：计算D列的公式是什么

  • 点击D2单元格，“插入-名称管理器-新建名称”，“名称：提取公式，引用位置：=get.cell(6,d2)（宏表函数）”
    
  • 向单元格中输入“=提取公式”，回车，发现D列数字对应的公式都显示出来了
    
    
    
    

2.get.workbook函数

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

• 示例1：如何获取整个文件所有工作簿的名字
  • 任意选中一个单元格，点击“插入-名称管理器-新建名称”，“名称：获取表名，引用位置：=get.workbook(1)（宏表函数）”
    
  • 向A1单元格中输入“=获取表名”，回车并下拉单元格，发现显示的表名都是第一个表的名字，这是因为“工作表名”这4个字指的并不是一个表的名字，按下F9，即可看到所有表名
    
    
    
  • 那么如何获取所有表名呢？可以用index()和row()函数
    
  • 如何一点击表名就跳转到具体的工作簿？
    利用hyperlink()函数，输入公式“=HYPERLINK(INDEX(获取表名,ROW())&“!a1”)”，然后回车，可以看到表名变蓝了，点击即可跳转
    

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

二、宏表函数常见应用

1.EVALUATE函数

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列了
    

2.REFTEXT(ACTIVE.CELL())

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，点击哪个单元格，哪个单元格所在的行列就会显示为黄色

系列文章目录

第1讲：excel常用的基础操作
第2讲：excel单元格格式设置
第3讲：查找、替换、定位
第4讲：排序与筛选
第5讲：分类汇总、数据有效性
第6讲：数据透视表
第7讲：excel常用函数与公式
第8讲：if函数
第9讲：countif函数
第10讲：sumif函数
第11讲：vlookup函数
第12讲：match+index函数
第14讲：日期函数
第15讲：条件格式与公式
第16讲：简单文本函数
第17讲：数学函数
第18讲：lookup和数组
第19讲：indirect函数
第20讲：图表基础
第21讲：经典excel动态图表实现原理
第22讲：制作甘特图与动态甘特图
第23讲：双坐标柱形图、饼图美化与ppt图表
第24讲：宏表函数

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！