如何在工作表上显示Excel表名称

If you have an Excel workbook with lots of tables and pivot tables, it can be hard to remember what they’re named, or what source data their using. To help you keep track of them, AlexJ is sharing the User Defined Function (UDF) that he uses in his files. With this code in your file, just add a formula, click on a cell, and show the Excel table name on the sheet.

如果您有一个包含大量表和数据透视表的Excel工作簿，则可能很难记住它们的名称或使用的源数据。 为了帮助您跟踪它们，AlexJ共享了他在文件中使用的用户定义函数(UDF)。 在文件中添加此代码后，只需添加一个公式，单击一个单元格，然后在工作表上显示Excel表名称即可。

The code is shown below, and there is also a link for downloading a sample file with the code installed in it.

该代码如下所示，并且还有一个链接，用于下载安装了代码的示例文件。

复制显示Excel表名称代码 (Copy the Show Excel Table Name Code)

To use this UDF in your file, copy the code shown below, and paste it into a regular code module in your workbook. There are instructions on my website, if you’re not sure how to do that.

要在文件中使用该UDF，请复制以下代码，然后将其粘贴到工作簿中的常规代码模块中。 如果您不确定该怎么做，可以在我的网站上找到说明。

Here is the code from AlexJ, to show an Excel table name on the sheet:

这是AlexJ的代码，用于在工作表上显示Excel表名称：

Function getObjName(rng As Range) As String
  Dim tbl As ListObject
  Dim pt As PivotTable
  Dim PivotName As String
  Dim TableName As String
  Dim qName As String
  Dim ptsName As String
  
  On Error GoTo Err_NoTable
  Set tbl = rng.Cells(1).ListObject
  TableName = "Table [" & tbl.Name & "]"
  
  On Error GoTo Err_NoQuery
  qName = "[" & _
    tbl.QueryTable.WorkbookConnection.Name _
      & "]"
        
Res_Table:
  TableName = TableName & qName
        
Res_Pivot:
  On Error GoTo Err_NoPivot
  Set pt = rng.Cells(1).PivotTable
  PivotName = "Pivot [" & _
    rng.Cells(1).PivotTable.Name & "]"
        
Res_PTS:
  On Error GoTo Err_NoPTSource
  ptsName = "[" & pt.SourceData & "]"
        
Res_PTName:
  PivotName = PivotName & ptsName
        
XIT:
  getObjName = TableName & PivotName
  Set tbl = Nothing
  Set pt = Nothing
  Exit Function
        
Err_NoTable:
  'Not a table check for Pivot
  TableName = ""
  Resume Res_Pivot
  'Resume
        
Err_NoQuery:
  'No Query on the table
  qName = ""
  Resume Res_Table
        
Err_NoPivot:
  'Not a Pivot Table - exit
  PivotName = ""
  Resume XIT
        
Err_NoPTSource:
  'No Pivot source identified
  ptsName = ""
  Resume Res_PTName

End Function

如何使用显示表名称功能 (How to Use the Show Table Name Function)

After you paste the Show Excel Table Name UDF code into your workbook, it’s ready to use. You can follow the steps below, to show the information about any table or pivot table in your file.

将“显示Excel表名” UDF代码粘贴到工作簿中之后，就可以使用它了。 您可以按照以下步骤操作，以显示有关文件中任何表或数据透视表的信息。

In this example, there are a couple of empty rows above the pivot table, so I added the formula there, in cell B1.

在此示例中，数据透视表上方有几个空行，因此我在单元格B1中添加了公式。

1. Select the cell where you want to see the table name or pivot table name.
   选择要在其中查看表名称或数据透视表名称的单元格。

2. Type an equal sign and the UDF name, followed by an opening bracket:  =getObjName(

   键入等号和UDF名称，后跟方括号： = getObjName(

4. To complete the formula, press Enter
   要完成公式，请按Enter

Excel will automatically add the closing bracket, and the formula displays the table or pivot table information.

Excel将自动添加右括号，并且公式将显示表格或数据透视表信息。

公式结果 (The Formula Results)

In the screen shot below, the formula referred to a pivot table cell, and you can see the formula results in cell B1.

在下面的屏幕快照中，该公式引用了数据透视表单元格，您可以在单元格B1中看到该公式的结果。

• NOTE: If the table name changes, the formula won’t update immediately. It will update when the workbook calculates.
  注意：如果表名称更改，该公式将不会立即更新。 当工作簿计算时，它将更新。

There are three sections in the results, underlined and numbered in the screen shot.

结果分为三部分，在屏幕截图中用下划线和数字编号。

1. The type of object – Table or Pivot
   对象的类型–表格或数据透视
2. The object’s name
   对象的名称
3. The object’s source name, if available
   对象的源名称(如果有)

In the next screen shot, the formula refers to a cell in a named table. The data is typed into the table, so there isn’t a source name available.

在下一个屏幕截图中，该公式引用命名表中的单元格。 数据被键入到表中，因此没有可用的源名称。

If the formula refers to a cell that isn’t in a named Excel table or a pivot table, the formula result will be an empty string.

如果公式引用的单元格不在命名的Excel表或数据透视表中，则公式结果将为空字符串。

下载样本文件 (Download the Sample File)

To download the sample file, and test the Show Excel Table Name code, go to the AlexJ Sample Files page on my Contextures website. In the VBA section, look for VBA0003 – Show Table or Pivot Name on Sheet.

若要下载示例文件，并测试Show Excel Table Name代码，请转到Contextures网站上的AlexJ Sample Files页面 。 在“ VBA”部分中，查找“ VBA0003 –在图纸上显示表或数据透视表名称” 。

The zipped file is in xlsm format, and contains a macro – the Show Excel Table Name UDF code.

压缩文件为xlsm格式，并包含一个宏-Show Excel Table Name UDF代码。

翻译自: https://contexturesblog.com/archives/2016/07/21/how-to-show-excel-table-name-on-the-sheet/