使用公式的Excel返回表名称？ [英] Excel Return Table name using Formula?

问题描述

我想知道是否可以使用公式返回表名？

I was wondering if there is anyway to return the name of the table using a formula?

我正在研究一种将数千个地址分解为有透视信息栏。即＃，街道，城市，州，邮政编码和电话号码。地址的格式与文本到列的格式不一致。我终于想出了公式来完成工作，但是时间很长。在一篇帖子中，我发现它建议将公式的重复部分用作定义的名称。而且它变得非常容易。现在这是问题所在。

I was working on a way to break down a few thousand addresses into there perspective column of information. ie.. #, Street, City, State, Zip-code and Phone#. The addresses are not in any consistent format that Text to Columns would work. I finally came up with the formulas to get the job done, but the are very long. In a post I found it suggested to use repeated parts of the formulas as a Defined Name. And it made it so much easier. Now Here is the problem.

具有表名 Table1的公式在 Table2中不起作用。或任何其他表名。每个表的列标题都相同。

A formula that has the table name "Table1" won't work in "Table2". Or any other table name. Column headers are the same for each table.

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

需要一种返回表名的方法。通过公式或公式作为已定义名称。

A way to return the name of the table is needed. Via formula or formula as Defined Name.

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

我希望它是一个公式。我不确定VBA解决方案是否是该问题的正确答案，因此即使它确实有效，也无法选择它作为THE答案。仍将不胜感激。我会在另一篇文章中问我是否找不到公式解决方案。

I prefer it to be a formula. I'm not sure if a VBA solution would be a correct answer to this question so I would not be able to choose it as THE answer, even if it does work. It will still be appreciated. I will ask in a separate post if I do not find a Formula Solution.

TY

我找到了这个有VBA解决方案的帖子，但我不能使用它。我将发布帖子，以便有人可以弄清楚。 Portland Runner发布此CODE以获取表名。

I found this post that has a VBA solution, but I can't use it. I will post just so someone can maybe figure this out. Portland Runner Posted this CODE to get table name.

Function GetTableName(shtName As String) As String
    GetTableName = Worksheets(shtName).ListObjects(1).Name
End Function

在该函数中，我输入名为 SheetName的我的定义名称公式

In that Function I enter My Defined Name formula named "SheetName"

=MID(CELL("filename"),FIND("]",CELL("filename"))+1,100)

所以我可以这样使用。

=MAX(SEARCH(INDIRECT(GetTableName(SheetName)&"[@State]"),INDIRECT(GetTableName(SheetName)&"[@Origin]")))

但是我仍然需要将此作为仅公式。虽然我可以在PC上运行宏，但它们不会在包含所有数据的PC中运行。

However I still need this to be Formula Only. While I can run Macros on My PC, they will not run in the PC that has all the data.

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

This is the last thing I got using a UDF. Unfortunately I still cant use it. Plus It gets the first Table's name and not the actual table the cell is in. Good if that is the only table in sheet or if the first table is the table you want.

Function GetTableName() As String 
    GetTableName = Worksheets(ActiveSheet.Name).ListObjects(1).Name
End Function

推荐答案

您将需要两个单元格才能获得表名。我的表标题从第2行开始，表数据从第3行开始，因此我将两个公式分别放在单元格A1和B1中。

You will need two cells to get the table name. My Table Headers start in row 2 and Table Data in row 3, so I put my two formulas in cells A1 and B1, respectively.

第一个单元格应引用顶部表格的左侧标题单元格。对我而言，该公式最终显示为：

The first cell should reference the top left header cell of your table. For me, the formula ends up reading:

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

并等同于状态。

第二个单元格的公式应该是：

The second cell's formula should be:

=MID(FORMULATEXT(A1),2,FIND("[",FORMULATEXT(A1))-2)

并等同于 My2016Data。

and equates to "My2016Data".

这篇关于使用公式的Excel返回表名称？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！