说明：本文仅做个人笔记记录，如有疑问欢迎戳我~

宏表函数

1：利用宏表函数获取信息

① get.cell获取单元格信息

函数公式：=GET.CELL(type_num, reference)

Type_num：指明单元格中信息的类型

Reference：提供信息的单元格或单元格范围。参数形式：[ABC.XLS]sheet1!A1

如果引用的是单元格范围，使用引用中第一个范围的左上角的单元格

如果引用被省略，默认为活动单元格

示例1：需要知道红颜色有哪些，黄颜色有哪些（已有A、D列）

思路：需按颜色排序，计算背景颜色，进行排序

步骤：选中B2单元格 - 公式选项卡 - 定义名称 - 引用位置【=get.cell(63,A2)】确定 - B2单元格输入公式【=定义名称】

示例2：提取D列的公式

作用等同于Excel公式【=FORMULATEXT(reference)】

步骤：选中E2单元格 - 公式选项卡 - 定义名称 - 引用位置【=get.cell(6,D2)】确定 - E2单元格输入公式【=定义名称】

② get.workbook获取当前工作簿信息

函数公式：=GET.WORKBOOK(type_num, name_text)

Type_num：指明要得到的工作簿信息类型的数

Name_text：打开的工作簿的名字。如果name_text被省略，默认为活动工作簿

示例1：提取工作表名

步骤：选中任意单元格-公式选项卡 - 定义名称 - 引用位置【=get.workbook(1)】确定 - 空单元格输入公式【=定义名称】（此时只取出第一个工作表名）

取全部工作表名【=index(定义名称,ROW())】

示例2：为提取到的表名加入超链接

函数公式：=HYPERLINK(link_location,[friendly_name])

link_location：链接到的地址

[friendly_name]：显示的名称，省略时显示link_location的名字

思路：超链接必须指定某一单元格，故将提取到的名称链接到A1单元格

步骤：选中空单元格-输入公式【=HYPERLINK(index(定义名称,ROW())&"!A1")】确定

2：宏表函数常见应用

EVALUATE函数公式：=EVALUATE(formula_text)

① 已知公式，计算结果

步骤：选中B3单元格 - 公式选项卡 - 定义名称 - 名称【公式】，引用位置【=EVALUATE(A3)】确定 - B3单元格输入公式【=公式】

② 同一单元格逗号分隔的数值合计

思路：使用SUBSTITUTE函数将A9单元格的逗号替换为加号，得到公式

步骤：选中B9单元格 - 公式选项卡 - 定义名称 - 名称【合计】，引用位置【=EVALUATE(SUBSTITUTE(A9,",","+"))】确定 - B9单元格输入公式【=合计】

③ 提取一组字符串中的某一段

示例1：使用find函数实现思路

第一个空格位置：=find(" ",A16)

第二个空格位置：=find(" ",A16,find(" ",A16)+1)

第三个空格位置：=find(" ",A16,find(" ",A16,find(" ",A16)+1)+1)

...

开始位置=第三个空格位置+1

需要找的位数=第四个空格位置-第三个空格位置

再根据位置信息提取文本，实现起来非常麻烦，且当字符串很长时难以实现

示例2：进阶方法（思路-使用SUBSTITUTE替换实现定位）

使用SUBSTITUTE函数将第三个空格替换为“-”【=SUBSTITUTE(A16," ","-",3)】

使用SUBSTITUTE函数将第四个空格替换为“+”

再根据位置信息提取文本，虽没有上个方法的多层嵌套，但公式也比较长

示例3：高级方法（思路-使用EVALUATE将文本转化为数组）

将字符串转换为数组的公式【="{"&SUBSTITUTE(A16," ",";")&"}"】

执行公式获得数组：B16单元格 - 定义名称 - 名称【数组】，引用位置【=EVALUATE("{"&SUBSTITUTE(A16," ",";")&"}")】

提取定位数组：B16输入公式【=index(数组,4)】

④ 标记选区

选中单元格时使用颜色定位当前单元格

步骤：

定义名称：名称【当前单元格】，引用地址【=REFTEXT(ACTIVE.CELL())&T(NOW())】

设置条件格式：选中数据区域-开始 - 条件格式 - 管理规则 - 添加两条规则

规则1：使用公式确定要设计格式的单元格，公式【=COLUMN()=MID(当前单元格,FIND("C",当前单元格)+1,100)*1】，填充黄色

规则2：使用公式确定要设计格式的单元格，公式【=ROW()=MID(当前单元格,2,FIND("C",当前单元格)-2)*1】，填充黄色

点选某一单元格，按住F9键点击可查看数据效果