批量生成带超链接目录且自动更新 Excel

您所在的位置：网站首页 › excel表格公式链接 › 批量生成带超链接目录且自动更新 Excel

批量生成带超链接目录且自动更新 Excel

2024-07-11 20:09| 来源: 网络整理| 查看: 265

文章目录自动生成带超链接的目录一、知识点get.workbook宏表函数使用INDEX()FIND()REPLACE()HYPERLINK() 二、举例第一步：定义名称第二步：提取所有工作表名称第三步：批量生成超链接第四步：设置"返出目录"超链接

自动生成带超链接的目录

提示：以下是本篇文章正文内容，下面案例可供参考

一、知识点 get.workbook宏表函数使用作用：提取工作表信息语法：get.workbook（type_num,name_text）解释：get.workbook（类型编号,打开的工作表名称）注意：参数type_num包含的代码较多，我们主要使用的是1，表示“正文值的水平数组，返回工作簿中所有工作表的名称”。参数name_text如果省略则表示当前活动工作簿。 INDEX() 作用：返回表格或区域中的值或值的应用。语法：INDEX(array,row_num,[column_num])。解释：INDEX（数组或区域，行号，列号）。举例 “=INDEX(目录,ROW(A2))” INDEX引用之前定义的宏函数。ROW(A1)目的是为了INDEX函数的第二个参数随之向下填充而变化，这样我们就能依次提取第1、2、3、4……N个工作表的名称。 FIND() 作用：对要查找的文本进行定位，以确定其位置，返回的是位移量。语法：FIND(find_text,within_text,start_num)解释：FIND（要查找的文本，文本所在的单元格，从第几个字符开始查找[可选，省略默认为1，从第一个开始查找]）。举例：“=FIND(“]”,INDEX(目录,ROW(A2))”，“]” 在单元格内的字符串排第六的位置，即得到6。在这里插入图片描述

REPLACE() 作用：用新字符串替换旧字符串，而且替换的位置和数量都是指定的。语法：REPLACE(old_text,start_num,num_chars，new_text)解释：REPLACE（要替换的字符串，开始位置，替换个数，新的文本）注意：第四个参数是文本，要加上引号。举例：“=REPLACE(INDEX(目录,ROW(A2)),1,FIND(“]”,INDEX(目录,ROW(A2))),“”)” INDEX(目录,ROW(A2))是要替换的字符串，1表示从第一个字符串开始， FIND(“]”,INDEX(目录,ROW(A2))表示替换个数为6，""表示替换为空值，即从第一个到第六个字符替换为空值。 HYPERLINK() 作用：超链接的函数 – ① 打开本地某个文件夹 – ② 打开本地某个文件（任意扩展名） – ③ 跳转到当前工作表的某个单元格 – ④ 跳转到当前工作薄的/某个工作表/某个单元格 – ⑤ 跳转到其他工作薄的/某个工作表/某个单元格 – ⑥ 跳转到网页 – ⑦ 跳转到名称管理器的引用范围 – ⑧ 跳转到VBA代码 – ⑨ 引用单元格内容，形成动态超链接语法：HYPERLINK（LINK_LOCATION，[FRIENDLY_NAME]）解释：HYPERLINK（链接的地址，显示的标题）举例：

① 打开本地某个文件夹输入公式：“=HYPERLINK(“C:\Users\xxx\Desktop\早日退休”,“打开文件夹早日退休”)” 在这里插入图片描述

② 打开本地某个文件（任意扩展名）输入公式：“=HYPERLINK(“F:\机器学习\机器学习实战高清PDF.pdf”,“打开测试文件 pdf”)” 在这里插入图片描述

③ 跳转到当前工作表的某个单元格输入公式：“=HYPERLINK(“#D2”,“转到D2单元格”)” 注意：引用单元格地址的时候，是一定要在单元格地址前面加上“#”的，否则就是“无效引用” 在这里插入图片描述

④ 跳转到当前工作薄的/某个工作表/某个单元格输入公式：“ =HYPERLINK(“#a!D2”,“转到其他sheet名称为a 的D2”)” 在这里插入图片描述

⑤ 跳转到其他工作薄的/某个工作表/某个单元格输入公式：“ =HYPERLINK(“[C:\Users\周晓婷\Desktop\早日退休\模型汇总.xlsx]sheet1!D2”,“其他工作薄的D2”)” 注意：链接其他工作薄的话，就用“[工作薄的完整路径]”。在这里插入图片描述

⑥ 跳转到网页输入公式：“=HYPERLINK(“https://blog.csdn.net/sodaloveer/article/details/124866941?spm=1001.2014.3001.5502”,“链接到其他网址”)” 在这里插入图片描述

⑦ 跳转到名称管理器的引用范围输入公式：“ =HYPERLINK(“#测试名称”,“名称引用的单元格”)” 在这里插入图片描述

⑧ 跳转到VBA代码输入公式：“ =HYPERLINK(“#测试代码”,“VBA工程”)”

⑨ 引用单元格内容，形成动态超链接输入公式：“ =HYPERLINK(CONCATENATE(“#”,A3,“!”,A4),A5)” 在这里插入图片描述

二、举例目标：目录A列是工作表名称，现在需要将工作表名称提取到A列第一步：定义名称

1、单击【公式】选项中的【定义名称】

在这里插入图片描述 2、弹出【新建名称】的框，在【名称】输入“目录”，在【引用位置】使用宏表函数，输入“=GET.WORKBOOK(1)”。单击【确定】后可以在【名称管理器】中看到刚刚新建的一条记录。

在这里插入图片描述

第二步：提取所有工作表名称

1、使用公式：“=INDEX(目录,ROW(A2))”提取出名称为”目录“中的所有工作表的名称。在这里插入图片描述 2、因为现在提取出来的工作表名称是带有工作薄名称的，所以进一步优化，达到只保留工作表名称的效果。在A2单元格输入公式：“=REPLACE(INDEX(目录,ROW(A2)),1,FIND(“]”,INDEX(目录,ROW(A2))),“”)”，下拉后即可提取出不带工作薄的工作表名称。在这里插入图片描述

第三步：批量生成超链接

使用HYPERLINK函数，在B2单元格输入公式：” =HYPERLINK(“#”&A2&“!A1”,A2) “，往下拉。点击生成超链接的文字，即可跳转到相应工作表下。在这里插入图片描述

第四步：设置"返出目录"超链接

1、点击【A模型】工作表名称，按 “shift” 键选择除了【目录】外的所有工作表，在其中一张工作表的A1中输入公式：”=HYPERLINK(“#目录！A1”,“返回目录”)“，生成返回【目录】工作表的超链接。在这里插入图片描述

【本文地址】

批量生成带超链接目录且自动更新 Excel

批量生成带超链接目录且自动更新 Excel

今日新闻

推荐新闻