如何引用非连续命名区域中的特定单元格并创建新命名区域？

我明白你的痛点——Excel默认的INDEX函数和Range.Item属性在处理非连续命名区域时，只会在第一个连续子区域内查找，没法跨区域按顺序定位第N个单元格。下面分工作表函数和VBA两种场景给出解决方案：

一、工作表函数实现（单元格内引用） #

如果只是想在单元格里直接引用非连续区域的第N个单元格，分两种情况处理：

1. Excel 365/2021及以后版本（推荐） #

用TOCOL函数把非连续区域转换成一维列数组，再用INDEX取对应位置，写法非常简洁：

=LET(
    source, Named_range,
    cell_list, TOCOL(source, TRUE), ' TRUE表示忽略空单元格，按需调整
    INDEX(cell_list, 6) ' 6替换成你要的目标索引
)

TOCOL会按从左到右、从上到下的顺序，把非连续区域的所有单元格合并成一列，之后用INDEX就能精准定位第N个单元格。

2. 旧版Excel（无TOCOL函数） #

用SMALL函数配合行/列偏移来定位：

=INDEX(
    Named_range,
    SMALL(ROW(Named_range)-MIN(ROW(Named_range))+1, 6),
    SMALL(COLUMN(Named_range)-MIN(COLUMN(Named_range))+1, 6)
)

原理是提取所有单元格相对于命名区域左上角的行/列偏移量，取第N小的偏移值，对应到目标单元格。

二、VBA实现（创建新命名区域） #

如果要通过VBA直接创建指向目标单元格的新命名区域，需要手动遍历非连续区域的每个子区域和单元格，找到第N个目标单元格：

Sub CreateNamedRangeFromNonContiguous()
    Dim sourceRng As Range
    Dim targetCell As Range
    Dim currentCount As Long
    Dim targetIndex As Long
    Dim newRangeName As String
    
    ' 自定义参数
    targetIndex = 6 ' 要定位的单元格索引
    newRangeName = "Named_range_cell_6" ' 新命名区域的名称
    
    ' 获取源非连续命名区域
    Set sourceRng = ThisWorkbook.Names("Named_range").RefersToRange
    
    ' 遍历所有子区域和单元格，计数找到目标
    currentCount = 0
    For Each area In sourceRng.Areas
        For Each cell In area.Cells
            currentCount = currentCount + 1
            If currentCount = targetIndex Then
                Set targetCell = cell
                Exit For ' 找到目标后跳出内层循环
            End If
        Next cell
        If Not targetCell Is Nothing Then Exit For ' 跳出外层循环
    Next area
    
    ' 创建新命名区域
    If Not targetCell Is Nothing Then
        ' 先删除已存在的同名区域（避免报错）
        On Error Resume Next
        ThisWorkbook.Names(newRangeName).Delete
        On Error GoTo 0
        
        ThisWorkbook.Names.Add Name:=newRangeName, RefersTo:=targetCell
        MsgBox "成功创建命名区域：" & newRangeName & vbCrLf & "指向单元格：" & targetCell.Address
    Else
        MsgBox "目标索引超出命名区域的总单元格数！"
    End If
End Sub

这段代码会按顺序遍历非连续区域的每个单元格，找到第N个后，直接创建新的命名区域指向它，完美解决Range.Item或INDEX函数跨区域失效的问题。

为什么默认方法会失效？ #

Excel的INDEX函数和Range.Item属性在处理非连续区域时，会把整个区域当作第一个子区域的扩展——比如你的Named_range包含K4:K7和L8:L10，INDEX(Named_range,6)会默认在第一个子区域K4:K7的行上扩展，取第6行（K4+5行=K9），而不会自动跳转到第二个子区域L8:L10。只有手动遍历或转换为一维数组，才能按实际单元格顺序定位。

内容的提问来源于stack exchange，提问作者Rafał Kowalski