Excel 2007：如何引用不连续的命名范围？
我在第一行有 5 个单元格：A1 是 1，B1 是 2，C1 是 3，D1 是 4，E1 是 5。现在我想选择奇数并将它们命名为“list_a_odd”，所以我按住 CONTROL 并选择A1、C1 和 E1，然后在名称框中键入“list_a_odd”并按 ENTER 键。现在在 A2 中，如果我输入“=SUM(list_a_odd)”，它将给出结果 9，这是正确且符合预期的。但如果我输入“=list_a_odd”，则会出现错误“#VALUE！”。
但是，如果我选择所有 5 个单元格（A1 到 E1）并将其命名为“list_a”，然后将“=list_a”放入 A2 中，它将在单元格中显示“1”，并且我可以将 A2 拖动到 E2 以显示list_a 中的所有 5 个数字。因此，当引用单元格中的非连续命名范围时，这看起来只是一个问题。这是 Excel 的错误吗？
I have 5 cells on the first row: A1 is 1, B1 is 2, C1 is 3, D1 is 4 and E1 is 5. Now I want to pick the odd numbers and name them "list_a_odd", so I hold CONTROL and select A1, C1 and E1, and type "list_a_odd" in the name box and hit ENTER. Now in A2, if I type "=SUM(list_a_odd)", it'll give me the result of 9, which is correct and expected. But if I type in "=list_a_odd", it gives me an error "#VALUE!".
However, if I select all 5 cells (A1 through E1) and name it "list_a", and I put "=list_a" in A2, it'll show "1" in the cell, and I can drag A2 to E2 to show all the 5 numbers in list_a. So that looks like only a problem when referencing non-consecutive named ranges in a cell. Is this an excel bug?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您描述的
list_a行为是范围的隐式交集功能的示例。 list_a 指的是连续范围A1:E1，当使用此范围放置在A到E列范围时，需要单个单元格的公式参考计算出交点并返回该单元格。也就是说，C2
=A1:E1或=list_a_odd中的公式实际上将引用重新调整为C1。正如您所说，
list_a_odd是一个不连续的多区域范围。并且隐式交集不适用于不连续范围。某些（但不是全部）函数适用于不连续范围，包括 SUM，这就是为什么
=SUM(list_a_odd)按预期工作，就像=SUM(A1,C1,E1)也有效。编辑
要返回代表不连续范围的连续范围，我将使用用户定义的函数与数组公式相结合。
在 UDF 中，使用 Range.Areas 属性循环遍历不连续范围中的每个子范围（请记住，Areas 中的每个项目可以是单个单元格或范围）就其本身而言）。构建一个值数组以返回工作表上的数组公式。
确切的细节将取决于您的具体要求以及您想要制作 UDF 的通用程度。尝试一下，如果您需要更多帮助，请再次发帖
The
list_abehaviour you describe is an example of the implicit intersection feature of ranges. list_a refers to a continuous rangeA1:E1, a formula expecting a single cell, when placed in theAtoEcolumn range using this range reference works out the intersection point and returns that cell.That is, a formula in C2
=A1:E1or=list_a_oddactually retuns a refence to justC1.As you say,
list_a_oddis a discontinuous multi area range. And implicit intersection doesn't apply to discontinuous ranges.Some (but not all) functions work with discontinuous ranges, including SUM, that's why
=SUM(list_a_odd)works as expected, just as=SUM(A1,C1,E1)works too.EDIT
To return a continuous range that represents a discontinuous range I would use a user defined function combined with an array formula.
In the UDF use the
Range.Areasproperty to loop through each of the sub ranges in a discontinuous range (remember that each item inAreascan be a single cell or a range in its own right). Build up an array of values to return to the array formula on the sheet.The exact details will depend on your specific requirements and how generalised you want to make the UDF. Have a go, and post again if you need more help