| |
--------------------
' VBA Code
'--------------------
'----------------------------------
Sub get_avg()
'----------------------------------
Dim data As Variant, rng As Range
Set rng = Worksheets("area").Range("B2")
data = Range(rng, rng.End(xlDown)).Value
Dim colX As Scripting.Dictionary: Set colX = CreateObject("Scripting.Dictionary")
'
Dim arr As Variant: arr = Array()
Dim sCate As String, sTemp As String
Dim iCurrent As Variant
Dim bMoved As Boolean
' 구분
sCate = IIf(data(2, 1) >= data(1, 1), "Up", "Down")
Dim iEnd As Long: iEnd = UBound(data, 1) - 1
'Stop
colX.Add "Up", Array()
colX.Add "Down", Array()
'-------------------------------------------------
For r = 1 To iEnd
'-------------------------------------------------
' 현재 값
iCurrent = data(r, 1)
' get category
If data(r + 1, 1) >= iCurrent Then sTemp = "Up" Else sTemp = "Down"
If sCate = sTemp Then
arr = push(arr, iCurrent)
Else
arr = push(arr, iCurrent)
' Debug.Print VBA.Join(arr, ",")
'Stop
colX.Item(sCate) = push(colX.Item(sCate), arr)
'colX.Add arr
sCate = sTemp
arr = Array()
End If
Next r
' 마지막 거 처리
arr = push(arr, data(r, 1))
colX.Item(sCate) = push(colX.Item(sCate), arr)
Call get_result(colX)
End Sub
'----------------------------------------------
Sub get_result(colX As Scripting.Dictionary)
'----------------------------------------------
Dim sht As Worksheet: Set sht = Worksheets("result")
'기존 자료 지우기
sht.Range("A7:D10000").ClearContents
Dim keys As Variant: keys = Array("Up", "Down")
' 각 시작 셀
Dim sStart As Variant: sStart = Array("A7", "C7")
For i = 0 To 1
Debug.Print keys(i)
' Debug.Print ""
r = 0
For Each arr In colX.Item(keys(i))
'Debug.Print Join(arr, ", ")
With sht.Range(sStart(i))
.Offset(r, 0).Value = WorksheetFunction.Average(arr)
.Offset(r, 1).Value = WorksheetFunction.Count(arr)
Debug.Print Join(arr, ", ")
End With
r = r + 1
Next
Next
End Sub
' 배열에 요소 추가
'------------------------------------------------------
Function push(col As Variant, ele As Variant) As Variant
'------------------------------------------------------
Dim i As Long: i = UBound(col, 1) + 1
ReDim Preserve col(i)
col(i) = ele
push = col
End Function
|
|
엑셀에 대한 질문과
답변을 올려주세요. 단, 취지에 맞지 않는 글은 운영자가 삭제합니다.
