logo标签列表

使用公式在Excel中返回表名？

excelformuladefinedtablename
11

我想知道是否有办法使用公式返回表格的名称？

我正在研究一种将数千个地址分解为相应信息列的方法。例如：＃，街道，城市，州，邮编和电话号码。这些地址没有任何一致的格式可以使用“文本分列”工具。我最终想出了可以完成任务的公式，但它们非常冗长。在一篇文章中，我发现建议使用公式的重复部分作为定义名称。这使得工作变得更加容易。现在问题来了。

在一个名为“Table1”的表格中有效的公式在“Table2”或其他任何表格中都无法使用。每个表格的列标题都相同。

MAX(SEARCH(Table1[@State],Table1[@Origin]))

需要一种返回表格名称的方法。可以通过公式或定义名称的公式实现。
MAX(SEARCH(GetTableName[@State],GetTableName[@Origin]))

我希望您能提供公式。我不确定VBA解决方案是否是这个问题的正确答案，所以即使它可以工作，我也无法选择它作为“答案”。但仍将非常感激。如果我找不到公式解决方案，我会在另一篇帖子中询问。
TY
我发现了这篇帖子，它有一个VBA解决方案，但我不能使用它。只是想发布看看是否有人能够解决。Portland Runner发布了这段代码来获取表名。
Function GetTableName(shtName As String) As String
    GetTableName = Worksheets(shtName).ListObjects(1).Name
End Function

在这个函数中，我输入了我定义的名为“SheetName”的公式。
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,100)

我可以这样使用它。
=MAX(SEARCH(INDIRECT(GetTableName(SheetName)&"[@State]"),INDIRECT(GetTableName(SheetName)&"[@Origin]")))

然而，我仍然需要它仅为公式。虽然我可以在我的电脑上运行宏，但它们无法在拥有所有数据的电脑上运行。

这是我使用UDF得到的最后一件事。不幸的是，我仍然无法使用它。另外，它获取的是第一个表格的名称，而不是实际单元格所在的表格。如果这是工作表中唯一的表格或如果第一个表格就是您想要的表格，则很好。

Function GetTableName() As String 
    GetTableName = Worksheets(ActiveSheet.Name).ListObjects(1).Name
End Function
2
请勿在多个网站上发布相同的问题。 - teylyn
仍然，VBA将是一个简单的解决方案。您可以创建一个带有输入参数的UDF，例如 =MyFunction(TableName,Field,someOtherValuesYouWant,....)。这样，您就可以获得简单的“复制/粘贴”公式来使用，而不是无尽的公式 ;) - Dirk Reichel
@DirkReichel，我确信VBA是更好、更容易的选择，但存储信息的计算机不属于我，由于公司政策，宏被禁用了。提取数据并在自己的电脑上操作也不是一个选项，因为涉及隐私问题。然而，如果您有VBA解决方案，我很想看看。但在这种情况下需要使用公式。 - Mouthpear
你能详细说明你想做什么吗？如何知道你正在使用哪个表？ - Dirk Reichel
1
那么@DirkReichel就会很高兴了。在我的原始帖子的编辑中，我写了一个这样的宏。宏是由另一个人提供的。我将其与我已经拥有的公式结合起来，从而可以获得表的名称。简化为以下代码：'Function GetTableName() As String GetTableName = Worksheets(ActiveSheet.Name).ListObjects(1).Name End Function' 不幸的是，我仍然无法使用它。此外，它只能获取第一个表的名称，而不是您所在的实际表名。但如果表格只有一个，则没问题。 - Mouthpear
显示剩余7条评论
6个回答
9

你需要两个单元格来获取表名。我的表头从第二行开始，在第三行开始是表数据，因此我分别在单元格A1和B1中放置了两个公式。

第一个单元格应该引用您的表格左上角的标题单元格。对于我来说，公式最终读取：

=My2016Data[[#Headers],[State]]

第一个单元格的值是“状态”。
第二个单元格的公式应为：
=MID(FORMULATEXT(A1),2,FIND("[",FORMULATEXT(A1))-2)

它等同于"My2016Data"。

3
我很惊讶地发现，尽管答案可能有点不太清晰，但这种策略在实践中确实有效。基本上，你的第一个公式应该是 =<TableName>[[#Headers],[<SomeColumnInTable>]]，第二个公式应该是=MID(FORMULATEXT(<CellContainingFirstFormula>),2,FIND("[",FORMULATEXT(<CellContainingFirstFormula>))-2)。由于Excel会在表格名称更改时自动更新公式中的表格名称，只要你不自己更改第一个单元格，第二个公式将始终显示表格的当前名称。 - Ben Seymour
1
注意：更改表名后，您可能需要强制 Excel 执行重新计算。您可以通过 公式->立即计算 或打开第二个公式的单元格进行编辑并按 Enter 键来完成此操作。 - Ben Seymour
这样做行不通。表名是变量，我不知道它的名称。我试图编写一个公式，可以在禁用宏的计算机上输入。这台电脑不是我的，也不属于我的朋友，而是他所在公司的财产。我（他）需要这个公式在任何表格中都能正常工作。 - Mouthpear
6

既然您希望看到VBA的解决方案，那么这里提供一个UDF（用户定义函数）的解决方法。您可以使用VBA创建此函数，并在单元格内作为公式使用。请将此代码保存在标准代码模块中（不要保存在工作表模块或“ThisWorkbook”模块中）：

Function GetTableName(cellInTable As Range) As String
    Dim tblName As String
    tblName = vbNullString
    On Error Resume Next
    tblName = cellInTable.ListObject.Name
    GetTableName = tblName
End Function

一旦保存到模块中，您可以像这样在单元格公式中使用它：

=GetTableName(A1)

或者这个

=GetTableName(B:B)

或者这个

=GetTableName(B2:W900)

如果您使用的范围跨越多个表格，则会返回第一个表格的名称。
1
谢谢。我已经尝试过了，它有效。但是我仍然无法在我原来的项目中使用它。存储信息的计算机是公司计算机，禁用了宏或VBA的使用。但我确实发现了您的UDF的用途。我真的很想将您的UDF选为答案，但我不确定是否允许，因为问题是关于公式解决方案的。我想知道管理员是否会允许。无论如何，非常感谢您。 - Mouthpear
2
在单个单元格中，您可以使用=TableName()公式将表名作为文本返回。例如，=TableName(TblAccounts)返回"TblAccounts"并随着表名的更新而更新！
随着Excel中LAMBDA函数的引入，可以通过Name Manager中的此函数实现返回表名。

公式

=LAMBDA(reference, LET(thisFormula,FORMULATEXT(INDIRECT(ADDRESS(ROW(),COLUMN()))),
TEXTBEFORE(TEXTAFTER(thisFormula,"("),IF(ISERROR(FIND("[",ThisFormula)),")","["))))

在名称管理器中创建一个名称（例如称为“TableName”），并在“引用范围”字段中使用上述公式。然后，此自定义函数将接受对表格的引用，并返回表格的名称。这可以使用包含表格名称的任何表格引用，正如@Marat所指出的：

您可以使用表格的任何部分，不仅仅是Table1[#Headers]。 例如，它可以是：Table1[#All]或Table1[Column1]或Table1[[#Headers],[Column1]]。

此解决方案提供了更广泛的兼容性，可在Excel Online Web版本中使用此功能，而VBA无法工作。
如果在更改表格名称后此公式没有自动更新，请在功能区的公式选项卡下点击“立即计算”，以使值得到更新。它是动态的，并且将返回您给表格命名的任何内容，就像Excel会更改引用表格的其他公式中的名称一样！

如何使用

在名称管理器中将此公式输入为TableName后，键入
=TableName(x)

其中x是一个引用，包括表的名称。例如，如果我的表叫做"TblAccounts"，那么x可以是"TblAccounts"、"TblAccounts[#Headers]"、"TblAccounts[@Colulmn]"等。

解释

以下是具有缩进的上述代码：

=LAMBDA(reference,
    LET(
        thisFormula, FORMULATEXT(INDIRECT(ADDRESS(ROW(), COLUMN()))),
        TEXTBEFORE(
            TEXTAFTER(thisFormula,"("),
            IF(ISERROR(FIND("[",ThisFormula)),
                ")",
                "["
            )
        )
    )
)

这个LAMBDA函数实际上从未引用它的参数。相反，FORMULATEXT函数返回单元格的公式，看起来像"=TableName(Table1[#Headers])"。
嵌套的TEXTBEFORE(TEXTAFTER(...)函数获取引用参数并将其修剪为仅包含表名。如果表格没有使用方括号进行引用，则IF函数会通过")"字符进行修剪。
1
另外，我尝试使用=TableName(Address(Row()+1, Column()))的方式，计划直接将其放在每个表格上方，但这样行不通。经过仔细研究函数与字符串解析的工作原理后，我明白了其中的原因。但是，如果其他人像我一样盲目使用此方法，请注意。 - Alex Kwitny
1

以下公式返回表名，该表在其中被引用：

=LET(x, Table1[#Headers], y, FORMULATEXT(INDIRECT(ADDRESS(ROW(),COLUMN()))), z, LEFT(y, FIND("[", y)-1), TRIM(RIGHT(z, LEN(z)-FIND("x", z)-1)))

注意：您可以使用表格的任何部分，不仅仅是Table1[#Headers]。
例如，它可以是：Table1[#All]或Table1[Column1]或Table1[[#Headers],[Column1]]。
最后一个很有用，因为您只需单击具有列名的单元格即可粘贴它。
虽然有两个小问题，但我很喜欢这个方法——它使用了易失函数“INDIRECT”，并且在表名更改时不会立即更新。我制作了这个扩展版本来解决这些问题：(1)添加“表：”文本前缀和(2)将下划线转换为空格：="Table:" & MID(SUBSTITUTE(LET(x,Manifold_Descriptions[#All], y, FORMULATEXT(INDIRECT(ADDRESS(ROW(),COLUMN()))), z, LEFT(y, FIND("[", y)-1), TRIM(RIGHT(z, LEN(z)-FIND("x", z)-1))), "_", " "), 6, 999)。这将返回“表：Manifold Descriptions”，并（最终）随着表名的更改而自动更新。 - Micah Lindstrom
0
=MID(
    FORMULATEXT(<CurrentCell>),
    53+2*<LengthOf<CurrentCell>>),
    LEN(FORMULATEXT(<CurrentCell>))-(53+2*<LengthOf<CurrentCell>>))-3
)&IF(0,<TableName>,"")
欢迎来到 Stack Overflow！请尽量提供一个关于你的解决方案如何工作的好描述。参见：如何撰写优秀答案？ 谢谢。 - 4b0
我会尽快查看。 - Mouthpear
0
如果所有表格都在不同的工作表中，并且工作表名称与表格名称相同，那么您可以尝试以下操作：
=LET(filename,CELL("filename",Table[#Headers]),RIGHT(filename,LEN(filename)-FIND("]",filename)))

这将返回表格所在的工作表名称，该名称将与表格名称匹配。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接