☰

Excel 表格中的参考值是否具有语义参考？

tag-icon • tag-icon microsoft-excel vba vlookup named-ranges
Excel 表格中的参考值是否具有语义参考？

问题

我有一个 Excel 表格对象：

在此处输入图片描述

我在其中存储了公式的假设。

是否有使用语义引用来引用该表中值的最佳实践？

更深层次的解释

尝试 #1：经典引用

假设我需要计算 B2 和 B3 的乘积。

=$B$2*$B$3

这个解决方案不太可靠 - 如果有人复制粘贴某些内容或移动表格怎么办？一定有更好的方法！

尝试＃2：VLOOKUP

我可以创建一个 VLOOKUP。但这不太可靠：如果值的名称发生变化，这将失败。此外，如果我想获得 5 个表值的乘积，我需要创建一个包含 5 个 VLOOKUP 的难以理解的公式！

=VLOOKUP("Lambda Requests per Month [Requests/Month]";Table145[[#All];[Name]:[Value]];2;FALSE)

尝试 #3：命名范围

在此处输入图片描述

并像这样引用它：

=Lambda_Requests_per_Month__Requests_Month

这可行，但现在我无法轻易分辨公式中的单位。包含特殊字符的单位（例如 [%]）会完全消失。

还有更好的方法吗？

答案1

我决定使用命名范围，并使用更改 NamedRanges 名称的脚本。

它需要一个名称列，其右侧有一个值列。要使用它，请选择值列并运行 VBA 宏。

Sub rename_name_manager()
'Written by Dustin
'Adds references and names to the NameManager, iff a reference does not yet exist with a name in the name manager.
'If a reference already exists, the name is updated with the value stored to the left of the cell
'Names have to be sanitized first, i.e. only alphabet, number, _ is allowed. No space!

'Create a dictionary, in which the NameManagers cell-references are stored.
Dim d
Set d = CreateObject("Scripting.Dictionary")
For Each n In ActiveWorkbook.Names
    d.Add n.RefersTo, n.Name
Next n


Dim wksname As String
wksname = "'YOUR_WORKSHEET_NAME'"

Dim cl As Range

For Each cl In Selection
    reference_in_NameManager = "=" & wksname & "!" & cl.Address
    If d.exists(reference_in_NameManager) Then
        'already exists in NameManager, only need to rename
        name_ = cl.Offset(0, -1).Value2
           
        'Sanitize string
        name_ = Replace(name_, "$", "USD")
        name_ = Replace(name_, "%", "Percent")
        name_ = Replace(name_, "/", "_")
        name_ = Replace(name_, " ", "_")
        name_ = Replace(name_, "___", "_")
        name_ = Replace(name_, "__", "_")
        name_ = Replace(name_, "-", "")
        name_ = Replace(name_, ",", "_")
        name_ = Replace(name_, "[", "__")
        name_ = Replace(name_, "]", "_")
        name_ = Replace(name_, "(", "__")
        name_ = Replace(name_, ")", "_")
        If Right(name_, 1) = "_" Then
            name_ = Left(name_, Len(name_) - 1)
        End If
        
        'Rename the name in NameManager
        Names.Item(RefersTo:=reference_in_NameManager).Name = name_
        
        
    Else
        'Not yet referenced in NameManager. Create new Name
        Debug.Print "="&wksname&"!" & cl.Address
        ActiveWorkbook.Names.Add Name:=name_, RefersTo:="=" & wksname & "!" & cl.Address
        
        ActiveWorkbook.Names(name_).Comment = ""
    End If
Next cl

End Sub

相关内容