Search algorithm for a set of areas containing a cell

Question

I work with some data from a spreadsheet, and I have a set of cell areas that have arbitrary borders. For any cell, what is the fastest way to determine a subset of the areas containing a cell?

Currently, it’s best to sort the regions with the main sort field being the beginning index of the row row, and then the ending row index, the starting column index, and then the column index. When I want to perform a search based on a given cell, I do a binary search in the first region, the index of the starting line of which is after the index of the row of the cell, and then I check all the regions before that to see if they contain the cell, but it's too slow.

Answer 1

Based on some kind of Googling, this is an example of a search problem in a two-dimensional point application, or "stabbing problem." Cm:

http://www.cs.nthu.edu.tw/~wkhon/ds/ds10/tutorial/tutorial6.pdf

here (starting on p. 21/52):

http://www.cs.brown.edu/courses/cs252/misc/slides/orthsearch.pdf

The key data structure is the segment tree:

http://en.wikipedia.org/wiki/Segment_tree

For the 2-D case, it looks like you can build a segment tree containing segment trees and get O (log ^ 2 (n)) query complexity. (I think your current solution is O (n), because on average you simply exclude half of your regions using binary search.)

" ", , , , . , . "", , , , .

, , - -, , Excel/VBA , O (1 ) :

Public Sub brutishButShort()
    Dim posns(1 To 65536, 1 To 256) As Collection

    Dim regions As Collection
    Set regions = New Collection

    Call regions.Add([q42:z99])
    Call regions.Add([a1:s100])
    Call regions.Add([r45])

    Dim rng As Range
    Dim cell As Range
    Dim r As Long
    Dim c As Long

    For Each rng In regions
        For Each cell In rng
            r = cell.Row
            c = cell.Column

            If posns(r, c) Is Nothing Then
                Set posns(r, c) = New Collection
            End If

            Call posns(r, c).Add(rng)
        Next cell
    Next rng

    Dim query As Range
    Set query = [r45]

    If Not posns(query.Row, query.Column) Is Nothing Then
        Dim result As Range
        For Each result In posns(query.Row, query.Column)
            Debug.Print result.address
        Next result
    End If
End Sub

, , , D-D. , , .

Answer 2

, , Nothing.

Sub RegionsContainingCell(rCell As Range, ParamArray vRegions() As Variant)

    Dim i As Long

    For i = LBound(vRegions) To UBound(vRegions)
        If TypeName(vRegions(i)) = "Range" Then
            If Not Intersect(rCell, vRegions(i)) Is Nothing Then
                Debug.Print vRegions(i).Address
            End If
        End If
    Next i

End Sub

Sub test()

    RegionsContainingCell Range("B50"), Range("A1:Z100"), Range("C2:C10"), Range("B1:B70"), Range("A1:C30")

End Sub