Excel公式如何连接字符串以供外部参考 [英] Excel formula how to concatenate string for external reference
问题描述
假设我在一个单元格中有以下公式,该公式从另一个工作簿中读取单元格的值
Say I have the following formula in a cell that reads the value of a cell from another work book
='c: emp[external book.xlsx]SheetX'!$E$4
并且我希望 c: emp[external book.xlsx]SheetX
的值来自此工作表中的另一个单元格.我将如何重写此公式以连接此值和 "!$E$4"
and I want the value of c: emp[external book.xlsx]SheetX
to come from another cell in this sheet. How would I rewrite this formula to concatenate this value and "!$E$4"
假设单元格 A1
包含 c: emp[external book.xlsx]SheetX
推荐答案
由于以下内容不适用于封闭的工作簿,因此这里有一个笨拙的概念验证可以用 VBA 做到这一点(我想有更好的方法来做到这一点):
As the below won't work on a closed workbook, here is a clunky proof-of-concept of how you could do it with VBA (I imagine there are better ways of doing this):
Sub ClosedWorkbook()
Dim currSht As Worksheet
Dim targetWbk As Workbook
Dim Path As String
' Turn off updating
Application.ScreenUpdating = False
' Set a reference to the current sheet
Set currSht = ActiveWorkbook.ActiveSheet
' Get the value in the formula cell (A1 here, could be ActiveCell if in a loop, etc.)
PathSheet = currSht.Range("A1").Value
' Split out the path - this is very clunky, more of a proof (?) of concept
' This is depends on the path being as you mentioned, and the IF block is to
' check if the apostrophe is present in the cell (since it is the escape character,
' it is usually skipped)
If Left(PathSheet, 1) = "'" Then
Path = Replace(Mid(PathSheet, 2, InStr(PathSheet, "]") - 2), "[", "")
Sheet = Mid(PathSheet, InStr(PathSheet, "]"))
Sheet = Left(Sheet, Len(Sheet) - 2)
Else
Path = Replace(Mid(PathSheet, 1, InStr(PathSheet, "]") - 1), "[", "")
Sheet = Mid(PathSheet, InStr(PathSheet, "]") + 1)
Sheet = Left(Sheet, Len(Sheet) - 2)
End If
' Open the target workbook
Set targetWbk = Workbooks.Open(Path)
' Grab the value from E4 and drop it in a cell (D1 here, could be ActiveCell, etc.)
MyValue = targetWbk.Sheets(Sheet).Range("E4").Value
currSht.Range("D5").Value = MyValue
' Close the target
targetWbk.Close
Application.ScreenUpdating = True
End Sub
<小时>
旧方法(不适用于封闭的工作簿)
OLD WAY (doesn't work on closed workbooks)
我相信这可以捕捉到您正在寻找的内容.在这个例子中,单元格 A1
有我的工作表路径:
I believe this captures what you're looking for. In this example, cell A1
had my sheet path:
'C: emp[externalworkbook.xlsx]Sheet1'!
在单元格 B1 中,我有公式:
In cell B1 I have the formula:
=INDIRECT(A1 & "$E$4")
将工作表值与 $E$4
连接以生成完整路径,然后由 INDIRECT
将其转换为值.需要注意的一件事:撇号是转义字符,因此根据您的数据,您可能需要在公式中特别说明:
Which concatenates the sheet value with $E$4
in order to generate the full path, which is then turned into a value by INDIRECT
. One thing to note: the apostrophe is an escape character, so depending on your data, you may have to account for it specially in your formula:
=INDIRECT("'" & A1 & "$E$4")
如果您使用的是 Excel 2007 或更高版本,您可以将其包装在 IFERROR
公式中以排除猜测:
If you're using Excel 2007 or above, you could wrap it in an IFERROR
formula to take out the guesswork:
=IFERROR(INDIRECT(A1 & "$E$4"),INDIRECT("'" & A1 & "$E$4"))
这篇关于Excel公式如何连接字符串以供外部参考的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!