跳到主要内容

如何在Excel中的单元格中引用选项卡名称？

要在Excel的单元格中引用当前工作表标签名称，可以使用公式或用户定义函数来完成。 本教程将指导您完成以下操作。

使用公式引用单元格中的当前工作表标签名称
使用用户定义功能在单元格中引用当前工作表标签名称
使用 Kutools for Excel 轻松引用单元格中的当前工作表选项卡名称

使用公式引用单元格中的当前工作表标签名称

请执行以下操作，以在Excel中的特定单元格中引用活动工作表标签的名称。

1.选择一个空白单元格，复制并粘贴公式 = MID（CELL（“ filename”，A1），FIND（“]”，CELL（“ filename”，A1））+ 1,255） 进入公式栏，然后按 输入 键。 看截图：

现在，工作表标签名称在单元格中被引用。

在工作表中的特定单元格，页眉或页脚中轻松插入标签页名称：

Kutools for Excel's 插入工作簿信息 实用程序可帮助轻松地将活动选项卡名称插入特定的单元格。 此外，您可以根据需要将工作簿名称，工作簿路径，用户名等引用到单元格，工作表的页眉或页脚中。 点击查看更多细节。
立即下载 Excel 版 Kutools！ （30 天免费试用）

使用用户定义功能在单元格中引用当前工作表标签名称

除了上述方法外，您还可以使用“用户定义功能”在单元格中引用工作表标签名称。

1。 按 其他 + F11 打开 Microsoft Visual Basic应用程序 窗口。

2.在 Microsoft Visual Basic应用程序 窗口中，单击 插页 > 模块。 看截图：

3.将以下代码复制并粘贴到“代码”窗口中。 然后按 其他 + Q 关闭键 Microsoft Visual Basic应用程序 窗口。

VBA代码：参考标签名称

Function TabName()
  TabName = ActiveSheet.Name
End Function

4.转到要引用当前工作表标签名称的单元格，请输入 = TabName（） 然后按 输入 键。 然后，当前工作表标签名称将显示在单元格中。

使用 Kutools for Excel 引用单元格中的当前工作表选项卡名称

随着 插入工作簿信息 实用程序 Kutools for Excel，您可以在所需的任何单元格中轻松引用工作表标签名称。 请执行以下操作。

1。 点击 Kutools 加 > 工作簿 > 插入工作簿信息。 看截图：

2.在 插入工作簿信息 对话框中选择 工作表名称 ，在 资讯 部分，并在 插入于 部分，选择 范围 选项，然后选择一个空白单元格以查找工作表名称，最后单击 OK 按钮。

您可以看到当前工作表名称已引用到所选单元格中。 看截图：

  如果您想免费试用（30天）此实用程序， 请点击下载，然后按照上述步骤进行操作。

演示：使用 Kutools for Excel 轻松引用单元格中的当前工作表选项卡名称

Kutools for Excel 包括300多个便捷的Excel工具。 30天免费试用，不受限制。 立即下载免费试用版！

最佳办公生产力工具

🤖 Kutools 人工智能助手：基于以下内容彻底改变数据分析： 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup  |   多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧   |  工作簿和工作表管理器   |  资源库 （自动文本）   |  日期选择器   |  合并工作表   |  加密/解密单元格   |  按列表发送电子邮件   |  超级筛选   |   特殊过滤器 （过滤粗体/斜体/删除线...）...
前 15 个工具集:  12 文本 工具 (添加文本, 删除字符，...）   |   50+ 图表 类型 (甘特图，...）   |   40+ 实用 公式 (根据生日计算年龄，...）   |   19 插入 工具 (插入二维码, 从路径插入图片，...）   |   12 转化 工具 (小写金额转大写, 货币兑换，...）   |   7 合并与拆分 工具 (高级组合行, 分裂细胞，...）   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能，体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

产品描述

Office Tab 为 Office 带来选项卡式界面，让您的工作更加轻松

  • 在Word，Excel，PowerPoint中启用选项卡式编辑和阅读，发布者，Access，Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50％，并减少数百次鼠标单击！
Comments (24)
Rated 0.5 out of 5 · 1 ratings
Julia
Julia
This comment was minimized by the moderator on the site
sorry your formulas and everything are incorrect on this page.
after direct copy/paste your THEORIES were WRONG
ZERO OUT OF TWO ATTEMPTS WORKED USING YOUR SUGGESTIONS
Reply
0
0
Rated 0.5 out of 5
crystal
crystal     Julia
This comment was minimized by the moderator on the site
Hi Julia,
What kind of erro did you get?
To be mentioned, the formula provided in the post can only be applied in English system environment Excel. If you have Excel in a different language than English, please convert the formula from English to the language you are currently using.
You can translate the formula in this page: https://de.excel-translator.de/translator/
Reply
Report
0
0
petercat
petercat
This comment was minimized by the moderator on the site
The formula at the top won't work until you save the file to your hard drive.
Reply
0
0
ScottCE
ScottCE
This comment was minimized by the moderator on the site
If you're using Excel Online. I found a solution for this issue.

You need to use 2 cells to make it work. As long as you have a cell that has the reference of a tab in its name, you can use FORMULATEXT() to turn that cells formula into a string and then extract the name that way. I can't imagine a scenario where you will want to reference another sheet and you wont already have a cell referencing that sheet for information on it, but you can always just make a blank/hidden (however you want to do it) cell that is just referencing the other sheet to set this formula up. In the below example, it is referencing cell B1 and cell B1 just contains =Sheet2!A1 as a reference to the sheet I want to name. If you use a name for a sheet with a space in it (example: Sheet 1) it would show up as (='Sheet 1'A1). I used an IF function and an ISERROR function to search if the string contains a space or not and two MID functions to extract the name of the sheet from both scenarios. If you update the name of the referenced sheet, cell B1 will change its formula and subsequently change the string and output the correct name each time.

=IF(ISERROR(FIND(" ",FORMULATEXT(B1))),MID(FORMULATEXT(B1),2,FIND("!",FORMULATEXT(B1))-2),MID(FORMULATEXT(B1),3,FIND("!",FORMULATEXT(B1))-4))
Reply
Report
0
0
Scott
Scott
This comment was minimized by the moderator on the site
If you're using Excel Online. I found a solution for this issue.

You need to use 2 cells to make it work. As long as you have a cell that has the reference of a tab in its name, you can use FORMULATEXT() to turn that cells formula into a string and then extract the name that way. I can't imagine a scenario where you will want to reference another sheet and you wont already have a cell referencing that sheet for information on it, but you can always just make a blank/hidden (however you want to do it) cell that is just referencing the other sheet to set this formula up. In the below example, it is referencing cell B1 and cell B1 just contains =Sheet2!A1 as a reference to the sheet I want to name. If you use a name for a sheet with a space in it (example: Sheet 1) it would show up as (='Sheet 1'A1). I used an IF function and an ISERROR function to search if the string contains a space or not and two MID functions to extract the name of the sheet from both scenarios. If you update the name of the referenced sheet, cell B1 will change its formula and subsequently change the string and output the correct name each time.

=IF(ISERROR(FIND(" ",FORMULATEXT(B1))),MID(FORMULATEXT(B1),2,FIND("!",FORMULATEXT(B1))-2),MID(FORMULATEXT(B1),3,FIND("!",FORMULATEXT(B1))-4))
Reply
0
0
Ailec
Ailec
This comment was minimized by the moderator on the site
What I really want is to name some tabs using with the names on some cells from another sheet (same document of course). How can I do that?
Reply
0
0
vanessa
vanessa
This comment was minimized by the moderator on the site
Hi, I used the above suggestions but the first formula to reference the tab sheet name will not work on "excel" online. The second works, but the "Tabname" keeps showing the same tab name for all tabs on the workbook.
Reply
0
0
crystal
crystal     vanessa
This comment was minimized by the moderator on the site
Hi,
Sorry we didn't test in excel online. Can't help you with that yet. Thank you for your comment.
Reply
Report
0
0
vanessa
vanessa
This comment was minimized by the moderator on the site
hello, i am having trouble referencing a sheet name into a cell with online excel
Reply
0
0
Kurt
Kurt
This comment was minimized by the moderator on the site
Excellent, thanks! Per: Reference the current sheet tab name in cell with formula
Reply
0
0
Alister Hood
Alister Hood
This comment was minimized by the moderator on the site
Sorry, the comment line is wrong - should be Ctrl-Alt-Shift-F9
Reply
0
0
LuisV
LuisV
This comment was minimized by the moderator on the site
FYI, you need to have more than one sheet in your workbook for this to work. Otherwise, the formula will display the filename but no sheet name.
Reply
0
0
crystal
crystal     LuisV
This comment was minimized by the moderator on the site
Good Day,
Thank you for your comment. I have tested the formula in a workbook that has only one sheet, but everything was running smoothly. The formula only displays the sheet name no matter how many worksheets existing in a workbook.
Reply
Report
0
0
There are no comments posted here yet
Load More
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations