[OfficeExcel] OfficeExcel2010 第24讲 宏表函数
王佩丰老师OfficeExcel2010 第24讲 宏表函数 学习笔记
利用宏表函数获取信息
-
get.cell函数 提取格式信息
GET.CELL(type_num, reference)
type_num 指明单元格中信息的类型。
reference 是提供信息的单元格或单元格范围。 参数形式: [ABC.XLS]sheet1!A1
常用type_num参数:
type_num | 返回 |
---|---|
6 | 文字,以工作区设置决定的 A1 或 R1 C1 类型引用公式。 |
7 | 文字的单元格的数字格式(如“m/d/yy”或“General”)。 |
24 | 是1-56的一个数字,代表单元格中第一个字符的字体颜色。如果字体颜色为自动生成,返回0。 |
41 | 不经翻译返回活动单元格中的公式(用于图别设定的宏表)。 |
62 | 返回工作簿和"[book1]sheet1"形式的当前表的文件名。【注:应为“活动工作表”】 |
63 | 返回单元格的填充(背景)颜色。 |
返回单元格背景颜色值
选中B1单元格>>公式>>定义名称: 名称:计算颜色; 引用位置=get.cell(63,a2)
(相对引用)
B2单元格输入:=计算颜色
提取公式
选中E2单元格>>公式>>定义名称>>名称:提取公式; 引用位置=get.cell(6,d2)
B2单元格输入:=提取公式
Excel2013中可以直接使用=formulatext(d2)
提取公式
-
get.workbook函数 提取文件信息
GET.WORKBOOK(type_num, name_text)
type_num 指明要得到的工作簿信息类型的数。
name_text 是打开的工作簿的名字。如果name_text被省略,默认为活动工作簿。
常用type_num参数:
type_num | 返回 |
---|---|
1 | 正文值的水平数组,返回工作簿中所有表的名字。 |
3 | 正文值的水平数组,返回工作簿中当前选择的表的名称。 |
4 | 工作簿中表的数。 |
38 | 活动工作表的名字。 |
选中任意一个空单元格>>公式>>定义名称>>名称:工作表名; 引用位置: =get.workbook(1)
A1单元格输入:=工作表名
返回的是第一张表的名字
编辑区按F9,返回:={"[课件:Excel第24讲.xls]EXCEL课件","[课件:Excel第24讲.xls]get.cell","[课件:Excel第24讲.xls]提取格式信息","[课件:Excel第24讲.xls]get.workbook","[课件:Excel第24讲.xls]提取工作表名","[课件:Excel第24讲.xls]EVALUATE","[课件:Excel第24讲.xls]标记选区"}
所有表的名字
实际上是以数组形式返回所有表的名字
要想一个单元格显示一个表名,在A1单元格输入=INDEX(工作表名,ROW())
再下拉
- 将表格名字做超链接: HYPERLINK函数
HYPERLING(link_location,[friendly_name])
link_location地址
friendly_name显示出来的名字,可省略
例如:=hyperlink("http://www.baidu.com","百度")
注意!
超链接必须是指向工作表中的某个单元格
A1单元格:=HYPERLINK(INDEX(工作表名,ROW())&"!a1")
宏表函数常见应用
EVALUATE函数 :计算公式EVALUATE(formula_text)
- 计算结果:
选中B3单元格>>公式>>定义名称>>名称:运算; 引用位置: =evaluate(a3)
B3单元格输入=运算
- 计算总分:
=substitute(A9,",","+")
替换函数,将A9单元格中的 ,逗号替换为 +加号
选中B9单元格>>公式>>定义名称>>名称:计算1; 引用位置=evaluate(substitute(A9,",","+"))
B9单元格输入:=计算1
下拉
或者 定义名称>>名称:数据; 引用位置=evaluate("{"&A9&"}"
单元格输入=sum(数据)
计算三个单元格和加和,格式为=sum{90,88,95}
,上述引用位置的格式即为 左大括号 连上 A9单元格 连上 右大括号
- 提取第四段数据:
法1:(麻烦)=SUBSTITUTE(A16," ","@",3)
将第三个空格替换为@=FIND("@",SUBSTITUTE(A16," ","@",3))
找到@的位置
提取:=MID(A16,FIND("@",SUBSTITUTE(A16," ","@",3)),FIND("@",SUBSTITUTE(A16," ","@",4))-FIND("@",SUBSTITUTE(A16," ","@",3)))
法2:
选中B16>>公式>>定义名称>>名称:数据3; 引用位置:=evaluate("{"&SUBSTITUTE(A16," ",";")&"}")
(将A16数据转换为数组)
B16单元格输入=index(数据3,4)