Индексирование в большие непрерывные диапазоны

excel vba excel-vba

369 просмотра

4 ответа

1006 Репутация автора

Скажем, у меня определен большой непрерывный диапазон, возможно Range("B:B,E:E,F:F"). Как бы мне заняться индексацией в диапазоне, чтобы рассматривать его как смежный.

Например, я хотел бы сделать что-то вроде

Set myRange = Range("B:B,E:E,F:F")
v = myRange.ContiguousIndex(5, 3).Value 'retrieves the value in cell F5 (row 5 col 3)

Каждый известный мне метод будет смещаться на основе первой ячейки в диапазоне («B1») и с радостью выйдет за пределы этого диапазона, перетекая в остальное содержимое книги. Это означает, что при попытке доступа к строке 5, столбец 3 вы получите D5, как если бы столбцы C и D находились в диапазоне, который я пытаюсь проиндексировать.

Я пробовал Range.Cells, Range.Offset и Range.Range, но, похоже, все демонстрируют тот же эффект.

Другой подход, который я имел в виду, состоял в том, чтобы присвоить значения массиву вариантов и вручную оттуда индексировать, но это очень быстро усложняется, потому что такой простой фрагмент, как

Dim v() As Variant
v = myRange

только назначит первую область непрерывного диапазона в массив, оставляя меня с (20 ^ 20-1) x1 массивом и полностью игнорируя остальную часть myRange. Так что, вероятно, выполнимо поместить весь myRange в массив, если я переберу все области и по отдельности назначу их в массив, который я перераспределяю, но это далеко не просто, и я получаю массив, который использует гораздо больше памяти, чем мне нужно (если я не добавлю больше накладных расходов на обрезку или не выберу произвольно меньшее количество строк для копирования).

В этот момент было бы гораздо эффективнее и проще просто выполнить обход областей вручную и выполнить индексацию самостоятельно, не затрачивая затраты на размещение элементов в массиве. Этот последний подход - то, чем я сейчас занимаюсь.

Вопрос

Существуют ли какие-либо методы или приемы, которые я могу использовать для обработки myRange, как если бы он был смежным, как я описал, и для индексации в myRange таким образом, чтобы игнорировать разрывы?

TL; DR Если у меня есть

Set myRange = Range("B:B,E:E,F:F")
v = myRange.ContiguousIndex(5, 3).Value

Я хочу, чтобы какой-то метод ContiguousIndex возвращал Range ("F5"). Значение без необходимости выполнять всю работу по ручной проверке Range.Areas и обработке всей индексации.


Бонусный вопрос

Скажем, myRange были Range("E:E,B:B,F:F")(обратите внимание на другой порядок столбцов). Есть ли хороший способ рассматривать E как первый столбец, B как второй и F как третий, такой, что

Set myRange = Range("E:E,B:B,F:F")
v = myRange.ContiguousIndex(5, 2).Value 'retrieves the value in cell B5

возвращает значение B5? Это свойство метода, которым я пользуюсь, и которое я бы хотел продолжить.

Опять же, функция, которая у меня есть, работает, но я предполагаю, что есть какой-то замечательный метод или трюк, скрытый во всех причудах Excel, который был бы еще лучше.

Автор: Mikegrann Источник Размещён: 18.07.2016 07:01

Ответы (4)


0 плюса

2432 Репутация автора

Как насчет:

v = myRange.Areas(2).Rows(5).Value 'retrieves the value in cell B5

Похоже, что это работает как для исходного, так и для бонусного вопроса, если каждый поддиапазон представляет собой один столбец. Вы также можете создать простую функцию-обертку ContiguousIndex(Row,Column)в VBA, чтобы дать интерфейс, который вы описали.

Надеюсь, это поможет.

Автор: xidgel Размещён: 19.07.2016 12:29

2 плюса

17447 Репутация автора

Что - то следует отметить, что с .Cells/ .Rows/ .Columns/ ._DefaultВы можете получить значения вне вашего диапазона:

Set myRange = Range("E2:E4,C4:B2,F2:F4")   ' C4:B2 gets B2:C4 
Debug.Print myRange.Areas(2)(1).Address ' $B$2

Debug.Print myRange.Areas(2)(0, 0).Address              ' $A$1
Debug.Print myRange.Areas(2).Cells(0, 0).Address        ' $A$1
Debug.Print myRange.Areas(2).Rows(0).Columns(0).Address ' $A$1

Если вместо этого вы индексируете значения:

Debug.Print myRange.Areas(2).Value2(1, 1) ' value of B2
Debug.Print myRange.Areas(2).Value2(0, 0) ' Run-time error '9': Subscript out of range

Если по какой-либо причине у вас есть области с несколькими столбцами, например, "E:E,A:B"их будет немного легче проиндексировать, если вы укажете каждый столбец как отдельную область:"E:E,A:A,B:B"

Автор: Slai Размещён: 19.07.2016 01:02

2 плюса

1006 Репутация автора

Я опубликую свое собственное решение на случай, если кто-то столкнется с подобной проблемой. Это единственное, что сработало для меня, так как другие ответы и комментарии основаны на знании чего-либо об Областях в диапазоне (например, полагаясь на то, что каждая Область - это целый столбец, что я не мог гарантировать, потому что мои диапазоны были вход и может занимать несколько столбцов или конечное количество строк).

' Indexes into a discontiguous area as expected, ignoring cells not in Range r
' and treating areas as concatenated (and top-aligned) in the order they are specified
Public Function ContiguousIndex(r As Range, row As Long, col As Long)
    Dim area As Range

    For Each area In r.Areas
        If col <= area.Columns.count Then
            If row <= area.Rows.count Then
                ContiguousIndex = area.Cells(row, col)
                Exit Function
            Else
                Err.Raise vbObjectError + 9, , "Row Index out of bounds"
            End If
        Else
            col = col - area.Columns.count
        End If
    Next

    ' col argument > sum of all cols in all areas
    Err.Raise vbObjectError + 9, , "Col Index out of bounds"
End Function

Стоит упомянуть кое-что, что я освещал в комментариях, но может быть неожиданным: этот код выровняет все области так, что первая строка в области 1 будет иметь тот же индекс, что и первая строка в области 2, и т. Д. Это приводит к причуде при вызове чего-то вроде ContiguousIndex(Range("A1:B7,A8:B10"), 9, 2). Хотя кажется очевидным, что это должно вернуться B9, это не так - он на самом деле попытается получить доступ к 9-й строке, 2-му столбцу A1:B7, что приведет к ошибке. Это потому, что два смежных диапазона, хотя они четко расположены сверху вниз на реальном листе, обрабатываются так, как если бы они были из стороны в сторону. Так B9что доступно через команду ContiguousIndex(Range("A1:B7,A8:B10"), 2, 4)(неинтуитивно). Это поведение - то, что я требовал, но это может быть не то, что вы ожидаете.

Чтобы обойти это, вы можете использовать встроенные методы Application.Union или Application.Intersect . Они автоматически разрушают смежные области, когда это возможно. Все следующие работы:

' Every statement will print "A1:B10" - the areas are merged

' Union of separate areas
Debug.Print Union(Range("A1:B7"), Range("A8:B10")).Address

' Union of range with a known subrange
Debug.Print Union(Range("A1:B7,A8:B10"), Range("A1:B7,A8:B10").Cells(1, 1)).Address

' Union of range with itself
Debug.Print Union(Range("A1:B7,A8:B10"), Range("A1:B7,A8:B10")).Address

' Intersect of range with itself
Debug.Print Intersect(Range("A1:B7,A8:B10"), Range("A1:B7,A8:B10")).Address

Если при индексировании это желаемое поведение, то перед вызовом выполните одно из перечисленных слияний ContiguousIndex. Обратите внимание, что если области объединяются в операции объединения, их относительные разрозненные индексы остаются неизменными. Например

' Yields "A:A,F:F,C:D" not "A:A,C:D,F:F" as you might desire
Debug.Print Union(Range("A:A,F:F,C:C,D:D"), Range("A:A,F:F,C:C,D:D")).Address
Автор: Mikegrann Размещён: 03.08.2016 03:03

2 плюса

17447 Репутация автора

Я думаю, что понимаю ваш вопрос немного лучше, увидев ваш пример. Это можно немного упростить, перечислив столбцы вместо диапазонов:

Public Function ContiguousIndex(r As Range, row As Long, col As Long) As Range
    Dim column As Range

    For Each column In r.Columns
        If col > 1 Then
            col = col - 1
        ElseIf col = 1 Then
            If row <= column.Rows.Count And row > 0 Then
                Set ContiguousIndex = column.Rows(row)
                Exit Function
            End If
            Err.Raise vbObjectError + 9, , "Row Index out of bounds"
        ElseIf col < 1 Then
            Err.Raise vbObjectError + 9, , "Column Index out of bounds"
        End If
    Next
End Function

Я не смог найти способ доступа к перечислителю напрямую (например
r.Columns.[_NewEnum].Item(col)не работает)

Обновить

Просто к примеру

Public Function veryContiguousIndex(r As Range, row As Long, col As Long) As Range
    Dim cell As Range, i As Long: i = col * row

    For Each cell In r.Cells
        If i = 1 Then Set veryContiguousIndex = cell: Exit Function
        i = i - 1
    Next
End Function

тогда

Dim r As Range: Set r = [A1:B7,A8:B10]
Debug.Print r.Cells.Count; r.Columns.Count; r.Rows.Count  ' 20  2  7
Debug.Print veryContiguousIndex(r             , 9, 2).Address(0, 0) ' B9
Debug.Print veryContiguousIndex(r.EntireColumn, 9, 2).Address(0, 0) ' B9
Debug.Print veryContiguousIndex(r.EntireRow   , 9, 2).Address(0, 0) ' R1
Автор: Slai Размещён: 03.08.2016 06:43
32x32