我看到人们2005 年就遇到过这个问题但问题还没有解决。
在我的工作表中,我有以下命名范围:
A1 - “name1”
B1 - “name2”
C1 - “name3”
在其他 3 个单元格中我有以下公式:
=A1
=A1+B1
=COUNT(A1:C1)
运行后Apply Names...我期望得到以下结果:
=name1
=name1+name2
=COUNT(name1:name3)
我绝不无论我选择什么选项,都会得到这个结果。而且,通常我的公式会被彻底破坏……
=name1
=name1
=name1
我怎样才能得到我需要的结果?
我精通 VBA,所以我尝试使用 VBA 设计一个解决此问题的解决方案,但代码也不起作用。
我正在使用 Excel 2013,32 位 (MS Office Professional Plus)。操作系统 - Windows 7 Enterprise,64 位。
答案1
我无法复制此操作(使用我的 Windows 7 上的 Excel 版本)-
如果我定义你的名字,然后创建公式
然后应用名称
我得到了想要的结果 -
您如何定义名称?我选择单元格,然后单击公式栏左侧的标题并输入名称。
与你一起 -
应用名称 -
如图所示
不“展示公式”就不会有改变——
应用名称
好的,我们开始吧。Office 2013,Windows 7
应用名称
看起来像是一次复制的失败。
好的,让我们在 OSX Yosemite 上尝试一下 Excel 2016
让我们定义我们的名字和公式 -
好好好,咱们就报上名字吧
什么?警报 公式太长
现在它选择了我的count
并且..什么?警报 Microsoft Excel 找不到任何要替换的引用
这是...部分失败吗?
好吧,我们手动来做一下 -
奇怪,它没有突出显示范围,只有两个单元格?
但它有效吗?
仅供比较,常规count
突出显示范围 -
答案2
假设我们从以下开始:
我们已经分配了名称A2和B2。 在里面公式选项卡, 拉下:
定义名称>应用名称...
确保我们突出显示两个名字并触摸OK
我们得到:
所以名字就被应用了!
答案3
正如指出的那样代码审查,如果它正在寻找“A1”并找到“A10”等,这将导致问题。
好的,这是我尝试的解决方案。这样,你的公式必须使用绝对引用始终如此。它适用于大于 1 个单元格的命名范围。
请注意,我正在搜索usedrange
- 但您可以通过重置来缩小搜索范围srchRng
。
Option Explicit
Sub FixNames()
Dim ClctNames As Variant
Set ClctNames = ActiveWorkbook.Names
Dim rngName As String
Dim rngNameLoc As String
Dim strFrmla As String
Dim c As Range
Dim n As Integer
'Define as needed
Dim srchRng As Range
Set srchRng = ActiveSheet.UsedRange
'For each name (n) in the collection
For n = 1 To ClctNames.Count
'I'm storing the Named Range's name and address as strings to use below
rngName = ClctNames(n).Name
rngNameLoc = ClctNames(n).RefersToRange.Address
'--Should I break this out into a function? If so, at what point?
For Each c In srchRng
'We only want to test cells with formulas
If c.HasFormula = True Then
'We have to check if the cell contains the current named range's address
If InStr(1, c.Formula, rngNameLoc, vbTextCompare) <> 0 Then
'Since these are perfect matches, no need to look for length or location, just replace
strFrmla = Replace(c.Formula, rngNameLoc, rngName)
c.Formula = strFrmla
End If
End If
Next
Next
'No error handling should be needed
End Sub
您需要使用绝对引用,因为当我拉取命名范围时,RefersToRange.Address
它会返回一个范围对象- 不是一个范围,所以我将其设置为字符串。我想您可以编写一个函数来删除$
绝对引用(如果您愿意的话)。
这是一个有趣的
答案4
我没有看到提到这一点,但您给出的确切公式可能是问题所在。
它们没有绝对引用。因此,如果您在选择单元格 A2 时创建它们,然后应用名称,则该功能将像您的 Excel 版本允许的那样工作。对我来说,版本 2205,也就是说它将只提供应用 name1 和 name3,但不提供 name2,并且会成功做到这一点,甚至达到第二个公式的程度,A1+B1
成为name1+B1
。但在我能找到或创建的任何情况下,它甚至不会提供应用 name2。
但是,如果您保留在创建命名范围时选择的单元格,则缺少绝对引用意味着它们的Refers to
公式会相对于从选定单元格到新选定单元格的移动而更改为新地址。
因此 name1Refers to
可能会从 更改为=A1
。=H3
现在命名区域中的引用与具有公式 的单元格中的引用不同=A1
。因此不会发生任何变化。
当我使用相对引用进行设置时,就会发生这种情况。一旦我将其更改为绝对引用,name1 就会保留=A1
而不是更改,它会正确应用该命名范围。如上所述,它会进入第二个公式以将其应用于第一个引用,但不应用于第二个引用。在第三个公式中,它很好地将两个不同的名称应用于与它们匹配的公式部分。
因此,为了获得那么多功能,请确保在命名范围中具有绝对引用:所以=$A$1
,而不是=A1
。
但是,我仍然无法让它将命名范围应用于作为一个单位/单个实体的多个单元格地址。因此,将 的每个部分更改为 是可以的$A$1:$C$1
,name1:name3
将单个范围地址中的每个单元格识别为与命名范围匹配并转换每个单元格。但它不会将不属于单个引用实体的两个引用单元格识别为可由包含这两个单元格的命名范围替换的单个事物。该功能似乎不存在。
顺便提一下,但很重要的一点是,如果您不选择(突出显示)某个范围,而只是选择了一个单元格,Excel 将应用到整个工作表。如果引用不是绝对的,您可能会得到意想不到的结果。在上面的示例中,在应用之前更改选定单元格的名称,将名称更改Refers to
为=H3
,工作表上任何引用的单元格H3
都将看到它更改为命名范围。
这本身可能就是一个问题。但更糟糕的是,既然已经进行了更改,而您没有看到预期的更改,您可能无法UNDO
撤消所做的任何操作。您可以转到命名范围的定义并再次将其更改为 A1,这次可能使用绝对引用,也可能不,然后再试一次。当您看到它在需要的地方工作时,您会很高兴。但是,以前查找单元格 H3 的 14 个公式现在将查找单元格 A1...
因此,选择/突出显示您想要应用的单元格。这将防止出现杂散应用。
最后,对于您意识到在此过程中不会调整的公式字符串(或如果您愿意的话,可以称为“片段”),请Find and Replace
在它们可能应用新命名范围的地方执行。