Array Excel VBA 요소의 반환 색인

Array Excel VBA 요소의 반환 색인

정수 목록인 배열 prLst가 있습니다.배열에서 정수의 위치가 스프레드시트의 특정 열을 나타내기 때문에 정수는 정렬되지 않습니다.배열에서 특정 정수를 찾고 인덱스를 반환하는 방법을 알고 싶습니다.

워크시트에서 어레이를 범위로 변환하지 않고 방법을 보여 줄 수 있는 리소스는 없는 것 같습니다.이거 좀 복잡한 것 같은데.VBA에서는 불가능합니까?

Dim pos, arr, val

arr=Array(1,2,4,5)
val = 4

pos=Application.Match(val, arr, False)

if not iserror(pos) then
   Msgbox val & " is at position " & pos
else
   Msgbox val & " not found!"
end if

[ Match ](와 함께)를 사용하여 표시되도록 갱신되었습니다.색인) 2차원 배열의 차원으로 값을 찾으려면:

Dim arr(1 To 10, 1 To 2)
Dim x

For x = 1 To 10
    arr(x, 1) = x
    arr(x, 2) = 11 - x
Next x

Debug.Print Application.Match(3, Application.Index(arr, 0, 1), 0)
Debug.Print Application.Match(3, Application.Index(arr, 0, 2), 0)

---

편집: @ARICH가 코멘트에서 지적한 내용을 여기서 설명할 필요가 있습니다.Index()어레이를 슬라이스하는 것은 루프에서 수행하는 경우 성능이 저하됩니다.

테스트(아래 코드)에서 Index() 접근법은 네스트루프를 사용하는 것보다 거의 2000배 느립니다.

Sub PerfTest()

    Const VAL_TO_FIND As String = "R1800:C8"
    Dim a(1 To 2000, 1 To 10)
    Dim r As Long, c As Long, t

    For r = 1 To 2000
        For c = 1 To 10
            a(r, c) = "R" & r & ":C" & c
        Next c
    Next r

    t = Timer
    Debug.Print FindLoop(a, VAL_TO_FIND), Timer - t
    ' >> 0.00781 sec

     t = Timer
    Debug.Print FindIndex(a, VAL_TO_FIND), Timer - t
    ' >> 14.18 sec

End Sub

Function FindLoop(arr, val) As Boolean
    Dim r As Long, c As Long
    For r = 1 To UBound(arr, 1)
    For c = 1 To UBound(arr, 2)
        If arr(r, c) = val Then
            FindLoop = True
            Exit Function
        End If
    Next c
    Next r
End Function

Function FindIndex(arr, val)
    Dim r As Long
    For r = 1 To UBound(arr, 1)
        If Not IsError(Application.Match(val, Application.Index(arr, r, 0), 0)) Then
            FindIndex = True
            Exit Function
        End If
    Next r
End Function

다양한 종류:

    Public Function GetIndex(ByRef iaList() As Variant, ByVal value As Variant) As Long

    Dim i As Long

     For i = LBound(iaList) To UBound(iaList)
      If value = iaList(i) Then
       GetIndex = i
       Exit For
      End If
     Next i

    End Function

정수의 가장 빠른 버전(아래의 사전 테스트와 같이)

    Public Function GetIndex(ByRef iaList() As Integer, ByVal value As Integer) As Integer
     Dim i As Integer

     For i = LBound(iaList) To UBound(iaList)
      If iaList(i) = value Then: GetIndex = i: Exit For:
     Next i

    End Function

' a snippet, replace myList and myValue to your varible names: (also have not tested)

---

스니펫, 인수로서의 참조 전달이 무언가를 의미한다는 가정을 시험해 보자.myList 및 myValue를 변수 이름으로 바꾸려면 (답은 '아니오')를 사용합니다.

  Dim found As Integer, foundi As Integer ' put only once
  found = -1
  For foundi = LBound(myList) To UBound(myList):
   If myList(foundi) = myValue Then
    found = foundi: Exit For
   End If
  Next
  result = found

내가 몇 가지 벤치마크를 했다는 것을 증명하기 위해

결과는 다음과 같습니다.

---------------------------
Milliseconds
---------------------------
result0: 5 ' just empty loop

result1: 2702  ' function variant array

result2: 1498  ' function integer array

result3: 2511 ' snippet variant array

result4: 1508 ' snippet integer array

result5: 58493 ' excel function Application.Match on variant array

result6: 136128 ' excel function Application.Match on integer array
---------------------------
OK   
---------------------------

모듈:

Public Declare Function GetTickCount Lib "kernel32.dll" () As Long
#If VBA7 Then
    Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) 'For 64 Bit Systems
#Else
    Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) 'For 32 Bit Systems
#End If

    Public Function GetIndex1(ByRef iaList() As Variant, ByVal value As Variant) As Long

    Dim i As Long

     For i = LBound(iaList) To UBound(iaList)
      If value = iaList(i) Then
       GetIndex = i
       Exit For
      End If
     Next i

    End Function


'maybe a faster variant for integers

    Public Function GetIndex2(ByRef iaList() As Integer, ByVal value As Integer) As Integer
     Dim i As Integer

     For i = LBound(iaList) To UBound(iaList)
      If iaList(i) = value Then: GetIndex = i: Exit For:
     Next i

    End Function

' a snippet, replace myList and myValue to your varible names: (also have not tested)



    Public Sub test1()
     Dim i As Integer

     For i = LBound(iaList) To UBound(iaList)
      If iaList(i) = value Then: GetIndex = i: Exit For:
     Next i

    End Sub


Sub testTimer()

Dim myList(500) As Variant, myValue As Variant
Dim myList2(500) As Integer, myValue2 As Integer
Dim n

For n = 1 To 500
myList(n) = n
Next

For n = 1 To 500
myList2(n) = n
Next

myValue = 100
myValue2 = 100


Dim oPM
Set oPM = New PerformanceMonitor
Dim result0 As Long
Dim result1 As Long
Dim result2 As Long
Dim result3 As Long
Dim result4 As Long
Dim result5 As Long
Dim result6 As Long

Dim t As Long

Dim a As Long

a = 0
Dim i
't = GetTickCount
oPM.StartCounter
For i = 1 To 1000000

Next
result0 = oPM.TimeElapsed() '  GetTickCount - t

a = 0

't = GetTickCount
oPM.StartCounter
For i = 1 To 1000000
a = GetIndex1(myList, myValue)
Next
result1 = oPM.TimeElapsed()
'result1 = GetTickCount - t


a = 0

't = GetTickCount
oPM.StartCounter
For i = 1 To 1000000
a = GetIndex2(myList2, myValue2)
Next
result2 = oPM.TimeElapsed()
'result2 = GetTickCount - t



a = 0

't = GetTickCount

oPM.StartCounter
Dim found As Integer, foundi As Integer ' put only once
For i = 1 To 1000000
found = -1
For foundi = LBound(myList) To UBound(myList):
 If myList(foundi) = myValue Then
  found = foundi: Exit For
 End If
Next
a = found
Next
result3 = oPM.TimeElapsed()
'result3 = GetTickCount - t



a = 0

't = GetTickCount

oPM.StartCounter
For i = 1 To 1000000
found = -1
For foundi = LBound(myList2) To UBound(myList2):
 If myList2(foundi) = myValue2 Then
  found = foundi: Exit For
 End If
Next
a = found
Next
result4 = oPM.TimeElapsed()
'result4 = GetTickCount - t


a = 0

't = GetTickCount
oPM.StartCounter
For i = 1 To 1000000
a = pos = Application.Match(myValue, myList, False)
Next
result5 = oPM.TimeElapsed()
'result5 = GetTickCount - t



a = 0

't = GetTickCount
oPM.StartCounter
For i = 1 To 1000000
a = pos = Application.Match(myValue2, myList2, False)
Next
result6 = oPM.TimeElapsed()
'result6 = GetTickCount - t


MsgBox "result0: " & result0 & vbCrLf & "result1: " & result1 & vbCrLf & "result2: " & result2 & vbCrLf & "result3: " & result3 & vbCrLf & "result4: " & result4 & vbCrLf & "result5: " & result5 & vbCrLf & "result6: " & result6, , "Milliseconds"
End Sub

PerformanceMonitor라는 클래스

Option Explicit

Private Type LARGE_INTEGER
    lowpart As Long
    highpart As Long
End Type

Private Declare Function QueryPerformanceCounter Lib "kernel32" (lpPerformanceCount As LARGE_INTEGER) As Long
Private Declare Function QueryPerformanceFrequency Lib "kernel32" (lpFrequency As LARGE_INTEGER) As Long

Private m_CounterStart As LARGE_INTEGER
Private m_CounterEnd As LARGE_INTEGER
Private m_crFrequency As Double

Private Const TWO_32 = 4294967296# ' = 256# * 256# * 256# * 256#

Private Function LI2Double(LI As LARGE_INTEGER) As Double
Dim Low As Double
    Low = LI.lowpart
    If Low < 0 Then
        Low = Low + TWO_32
    End If
    LI2Double = LI.highpart * TWO_32 + Low
End Function

Private Sub Class_Initialize()
Dim PerfFrequency As LARGE_INTEGER
    QueryPerformanceFrequency PerfFrequency
    m_crFrequency = LI2Double(PerfFrequency)
End Sub

Public Sub StartCounter()
    QueryPerformanceCounter m_CounterStart
End Sub

Property Get TimeElapsed() As Double
Dim crStart As Double
Dim crStop As Double
    QueryPerformanceCounter m_CounterEnd
    crStart = LI2Double(m_CounterStart)
    crStop = LI2Double(m_CounterEnd)
    TimeElapsed = 1000# * (crStop - crStart) / m_crFrequency
End Property

또 다른 방법이 있습니다.

Option Explicit

' Just a little test stub. 
Sub Tester()

    Dim pList(500) As Integer
    Dim i As Integer

    For i = 0 To UBound(pList)

        pList(i) = 500 - i

    Next i

    MsgBox "Value 18 is at array position " & FindInArray(pList, 18) & "."
    MsgBox "Value 217 is at array position " & FindInArray(pList, 217) & "."
    MsgBox "Value 1001 is at array position " & FindInArray(pList, 1001) & "."

End Sub

Function FindInArray(pList() As Integer, value As Integer)

    Dim i As Integer
    Dim FoundValueLocation As Integer

    FoundValueLocation = -1

    For i = 0 To UBound(pList)

        If pList(i) = value Then

            FoundValueLocation = i
            Exit For

        End If

    Next i

    FindInArray = FoundValueLocation

End Function

이것이 당신이 찾고 있던 것이에요?

public function GetIndex(byref iaList() as integer, byval iInteger as integer) as integer

dim i as integer

 for i=lbound(ialist) to ubound(ialist)
  if iInteger=ialist(i) then
   GetIndex=i
   exit for
  end if
 next i

end function

어레이가 0에서 시작되는지 또는 1에서 시작되는지 처리한다.또한 함수에 의해 위치 0 또는 1이 반환될 때 함수에 의해 반환되는 True 또는 False와 혼동되지 않도록 합니다.

Function array_return_index(arr As Variant, val As Variant, Optional array_start_at_zero As Boolean = True) As Variant

Dim pos
pos = Application.Match(val, arr, False)

If Not IsError(pos) Then
    If array_start_at_zero = True Then
        pos = pos - 1
        'initializing array at 0
    End If
   array_return_index = pos
Else
   array_return_index = False
End If

End Function

Sub array_return_index_test()
Dim pos, arr, val

arr = Array(1, 2, 4, 5)
val = 1

'When array starts at zero
pos = array_return_index(arr, val)
If IsNumeric(pos) Then
MsgBox "Array starting at 0; Value found at : " & pos
Else
MsgBox "Not found"
End If

'When array starts at one
pos = array_return_index(arr, val, False)
If IsNumeric(pos) Then
MsgBox "Array starting at 1; Value found at : " & pos
Else
MsgBox "Not found"
End If



End Sub

'To return the position of an element within any-dimension array  
'Returns 0 if the element is not in the array, and -1 if there is an error  
Public Function posInArray(ByVal itemSearched As Variant, ByVal aArray As Variant) As Long  
Dim pos As Long, item As Variant  

posInArray = -1  
If IsArray(aArray) Then  
    If not IsEmpty(aArray) Then  
        pos = 1  
        For Each item In aArray  
            If itemSearched = item Then  
                posInArray = pos  
                Exit Function  
            End If  
            pos = pos + 1  
        Next item  
        posInArray = 0  
    End If  
End If

End Function

이 작업을 수행할 수 있는 유일한 방법은 (& 비록 번거롭지만 편리하고 비교적 빠른) 임의의 차원 배열을 연결하고 구분 기호로 "/[column number]//\|"를 사용하여 1차원으로 줄이는 것입니다.

이 1-d 열에 단일 셀 결과 다중 조회 매크로 함수를 사용합니다.

그리고 인덱스 매칭을 통해 포지션을 추출합니다.(복수 검색 일치 사용)

그러면 원래 임의 차원 배열에서 찾고 있는 요소/문자열과 해당 위치에 일치하는 모든 항목을 얻을 수 있습니다.한 감방에서.

이 전체 프로세스에 대해 매크로/함수를 작성할 수 있으면 좋겠습니다.그래야 내가 더 소란을 피울 수 있을 거야.

언급URL : https://stackoverflow.com/questions/7031416/return-index-of-an-element-in-an-array-excel-vba