- PGR21 관련된 질문 및 건의는 [건의 게시판]을 이용바랍니다.
- (2013년 3월 이전) 오래된 질문글은 [이전 질문 게시판]에 있습니다.
통합 규정을 준수해 주십시오. (2015.12.25.)
Date 2019/11/19 16:55:56
Name 산양
File #1 1차원을_3차원_도표로_전환.png (193.0 KB), Download : 0
Subject [VBA]중첩 for문 최적화 질문 드립니다


위 사진처럼 1차원으로 자료를 수집한 시트의 내용을 참고하여 3차원 매트릭스의 값을 추가하는 코드를 작성중입니다.

완성된 결과물은 사진 하단에 첨부한 피벗테이블과 유사한 형태가 되겠지요.


이를 위해서 코드를 짜봤는데 다음과 같은 문제가 발생하였습니다.

1. 중첩 for문이 무려 6개나 들어갑니다.
아무래도 이것보다 훨씬 깔끔하게 데이터를 뽑아내는 코드가 있을 것 같은데 제 깜으론 알 수가 없습니다.

2. 'next 컨트롤 변수 참조가 잘못되었습니다'라는 오류가 출력됩니다. 이를 찾아보니 상위 for순환문에서 참조한 순환변수를 하위 for순환문에서 참조할 때 오류가 발생한다고 하는데, 변수명을 다르게 선언하였음에도 오류가 출력됩니다.

3. 지난번에도 몇번 이런식으로 중첩 순환문을 이용해 코딩을 했었는데 그때마다 극심하게 느린 연산속도 때문에 몇번이나 포기하고 수기로 데이터 생산 작업을 진행했었습니다. 가능한 방법이 있다면 도와주십시오.



이하는 제가 짠 코드 전문입니다.

Option Explicit

Sub matrix()


Dim wkMat As Worksheet              '// 3차원 변환 시트
Dim wkTable As Worksheet            '// 기존 1차원 시트
Dim targetVal As Range              '// 3차원 변환 시트 합계값이 들어갈 셀
Dim tgtResult As Long               '// 조건에 맞는 합계값

Dim lastRow_Mat As Integer          '// 3차원 변환 시트의 마지막 행
Dim lastColumn_Mat As Integer       '// 3차원 변환 시트의 마지막 열

Dim lastRow_Table As Integer        '// 기존 1차원 시트의 마지막 행

Dim i As Integer
Dim j As Integer
Dim k As Integer                    '// 3차원 변환 시트의 행, 열 돌이

Dim ii As Integer
Dim jj As Integer
Dim kk As Integer                    '// 1차원 변환 시트의 행 돌이


Set wkMat = ThisWorkbook.Worksheets("matrix")
Set wkTable = ThisWorkbook.Worksheets("table")

lastRow_Mat = wkMat.Cells(Rows.Count, "A").End(xlUp).Row
lastColumn_Mat = wkMat.Cells(1, Columns.Count).End(xlToLeft).Column

lastRow_Table = wkTable.Cells(Rows.Count, "A").End(xlUp).Row

Set targetVal = wkMat.Cells(j, k).Offset(1, 2)
tgtResult = 0

For i = 1 To lastRow_Mat
For j = 1 To lastRow_Mat
For k = 1 To lastColumn_Mat
    
    For ii = 1 To lastRow_Table
    For jj = 1 To lastRow_Table
    For kk = 1 To lastRow_Table
            
            If wkMat.Cells(i, "A").Value = wkTable.Cells(ii, "A").Value And _
            wkMat.Cells(j, "B").Value = wkTable.Cells(jj, "B").Value And _
            wkMat.Cells(1, k).Value = wkTable.Cells(kk, "C").Value Then
                
                If tgtResult = 0 Then
                    tgtResult = wkTable.Cells(kk, "D").Value
                Else
                    tgtResult = tgtResult + wkTable.Cells(kk, "D").Value
                End If
            
            End If
    
    Next ii
    Next jj
    Next kk
    
    targetVal.Value = tgtResult
    tgtResult = 0

Next k
Next j
Next i

End Sub



질문 읽어주시는 분들께 모두 감사의 인사를 드립니다.

통합규정 1.3 이용안내 인용

"Pgr에는 "명문화된 삭제규정"이 반드시 필요한 사람은 안왔으면 좋겠습니다.
법 없이도 사는 사람, 남에게 상처 안주면서 같이 이야기 나눌수 있는 분이면 좋겠습니다."
Lord of Cinder
19/11/19 17:46
수정 아이콘
(수정됨) VBA에서 For~Next 사이에 DoEvents 한 줄 넣는 것만으로도 아주 큰 퍼포먼스 향상이 이루어질 수 있습니다.
그리고 함수를 실행하기 전에 엑셀 시트의 자동 계산 업데이트 기능을 꺼 두는 것도 괜찮습니다. (Application.EnableEvents = False와 Application.ScreenUpdating = False를 해 두고, 계산이 끝난 후에 다시 True로 돌림. 수식이 많은 시트라면 Application.Calculation = xlCalculationManual 후 다시 Auto로 돌리는 것도...)

일단 제 경험상으로는 For 다음 줄에 DoEvents 하나 치고 안 치고의 차이가 어마어마했습니다.
19/11/19 17:53
수정 아이콘
애초에 지금 현재 하시려고하는게 굳이 저런 VBA로 해야하는건지 의문이 듭니다. 좌측 2열만 쓴다면 나머지는 일반 함수로도 가능할것 같은데요..
19/11/19 19:30
수정 아이콘
사진은 예시입니다.

테이블에 입력된 행이 최소 3만, 최대 15만행을 넘어가고, 매트릭스도 30*30 규모인 경우가 많다보니 sumif를 사용한 함수 입력시 많이 느려지더라구요.
또한, 함수값 반환시 그대로 끝나는 게 아니라 해당 값을 2차 가공해야 하는 경우가 잦아 가급적 함수를 쓰지 않는 형태로 개선해보고자 새로 짜고 있습니다.
19/11/19 19:31
수정 아이콘
Screenupdating은 현재 사용하고 있습니다.

DoEvents를 찾아보니 vba에 물린 cpu를 잠시 놔주는 명령어인 듯 싶네요. 감사합니다.
이달의소녀
19/11/19 21:46
수정 아이콘
(수정됨) 조건1,2,3이 결정되는순간 대입하는 곳이 결정됩니다.
그래서 원칙적으로는 for문은 table에 대해서 딱 1번만 돌리면 됩니다.

그런데 이걸 대입하는 곳을 검색해서 찾기 때문에 검색에
for문에 중첩해서 들어가고 이 때문에 속도가 매우 느려집니다.
이부분이 비효율적이기 때문에 돌아가는 속도가 느린겁니다.

조건1이 고정되었을때 조건2=1, 조건3='가'의 위치가 결정되면
조건2와 조건3이 바뀌어도 matrix에 어디에 대입하는지 바로 결정됩니다.
(조건1,조건2=1,조건3='가') 만 검색하시고 나머지는 수동으로 cell의 위치를 이동해 주세요
이러면 table에 대해서 for문 한개 검색에 for문 한개 총 두개의 중첩된 for문이 됩니다.

아니면 조건1에 대해서 미리 검색하시고(이게 효율이 좋습니다)
(조건1,조건2=1,조건3='가')의 위치를 미리 기록해두시고
table에 대해서 for문 한개로 돌릴때 기록된 위치를 참조해서 위치를 찾아주세요
이러면 처음 검색에 for문 한번 이후 변환에 for문 한번이 되는데
중첩되지 않은 for문이 됩니다.

예시
조건1=B, 조건2=1, 조건3=가 의 위치는 (4,3)이고
조건2=2 조건3=다 의 위치는
바로 (4+1,3+2) = (5,5)로 알 수 있습니다.


더 편하게 하시려면
조건1들에 대해서 조건2와 조건3의 길이를 완전히 같게 맞추세요 (matrix에서 빈칸이 생기는 수준이 될겁니다)
(지금은 조건1이 다른값이면 조건2의 길이가 달라서 문제가 있습니다)
그러면 조건1, 조건2, 조건3이 결정되면
matrix에서 어디에 대입해야하는지 쉬운 관계식으로 결정할 수 있습니다 검색이 필요없어요
이러면 for문 한개로 대입이 끝납니다.
이후에 빈줄을 찾아서 지우시거나 하면 될겁니다.
19/11/19 22:04
수정 아이콘
첫번째 방법은 가능한 경우의 수를 vba의 사용자정의 함수와 select case를 통해 갈라서 경로를 잡아주는, 예를 들면

if문 중첩을 통해서 조건1=A, 조건2=1, 조건3=가인 경우 var를 반환하는 사용자정의 함수 설정 후

for i = 1 to lastRow
select case 함수값
next i

의 꼴로 코드를 짜라는 말씀이신거죠?

아래의 방법은 조건2와 조건3에 각각 select case를 걸어서 해당 값에 맞는 숫자를 반환한 뒤, 조건1의 행과 열 값에 offset으로 추가하라는 말씀이시구요.

제가 이해한 게 다르다면 다시 코멘트 달아주시면 고민해보겠습니다.

답변 감사합니다.
이달의소녀
19/11/19 22:44
수정 아이콘
네 다 비슷한 아이디어입니다.

for i = 1 to lastRow
조건1 = cell(1 , i+1).Value
조건2 = cell(2 , i+1).Value
조건3 = cell(3 , i+1).Value
ii,jj= MatrixPosition( 조건1, 조건 2, 조건3 )
Matrix(ii,jj) = cell(4, i+1).Value
next i
의 구조는 동일하구요

MatrixPosition 내부 구조는 이해하신게 맞습니다.
[조건2와 조건3에 각각 select case를 걸어서 해당 값에 맞는 숫자를 반환한 뒤, 조건1의 행과 열 값에 offset으로 추가하라는 말씀이시구요.]
MatrixPosition(조건1,조건2,조건3) = Matrix조건1Positon(조건1) + 조건2 , 조건3+1 (대충 썼습니다)

offset은 조건2 조건3으로 구하면 되고
Matrix조건1Positon(조건1) 이걸 구해야합니다.

첫번째는 Matrix조건1Positon라는 표현안에 검색이 들어가는겁니다. for를 한번 더 쓰겠죠
두번째는 Matrix조건1Positon라는 표현안에 미리 검색해서 만들어놓은 테이블을 이용하는거구요.
(코드 위쪽에 검색해서 테이블 만드는 코드가 추가되어야하죠)
미리 테이블 만들때 for를 쓰고 이용할때는 값만 가져오면 됩니다.

세번째는 MatrixPosition라는 표현안 검색을 안하고 수식으로 바로 집어넣는겁니다.
ii = (조건1-1)*(조건2 갯수) + 조건2 +1
jj = 조건3 + 1
이런식으로요 검색을 안하니 for가 필요없습니다.
Supervenience
19/11/20 02:14
수정 아이콘
최적화와는 다른 이야기지만
Next ii
Next jj
Next kk
이 부분이 역순으로 되어서 변수 참조 오류가 나오는 것 같네요.
ii, jj, kk 순으로 for 반복문을 여셨으므로
Next kk
Next jj
Next ii
가 되는게 맞지 않을까 싶습니다.

그나저나 iterator i, j, k 등을 돌이라 표현하신 부분에서 감탄하고 갑니다.
19/11/20 19:31
수정 아이콘
아, 이게 문제였군요. 감사합니다.

반복문의 변수는... 제가 비전공자라.. 딱히 어찌 설명해야 할지 몰라서 그래 썼습니다. 혹시 필드에선 뭐라고 부르는지 알 수 있을까요?
19/11/20 19:32
수정 아이콘
확인해보고 적용해본 뒤 답글 드리도록 하겠습니다.

조언 정말 감사합니다!
Supervenience
19/11/20 19:40
수정 아이콘
Iterator 라 부릅니다 정확하게 말하자면 경우가 다르지만요. 선형적 데이터 구조가 있을 때 iterator를 씁니다.
여튼 돌이란 말과 거의 같은 말이고 순 우리말 돌이가 너무 적합한 말인데 처음보다 보니 감탄했습니다.
19/11/20 20:05
수정 아이콘
아 제가 필요한 부분만 그때그때 배우다보니 그런 건 미처 알지 못했어요. 답변 감사합니다!
목록 삭게로! 맨위로
번호 제목 이름 날짜 조회
공지 댓글잠금 [질문] 통합 규정(2019.11.8. 개정) jjohny=쿠마 19/11/08 2472
공지 [질문] [삭제예정] 카테고리가 생겼습니다. [9] 유스티스 18/05/08 20550
공지 [질문] 성인 정보를 포함하는 글에 대한 공지입니다 [37] OrBef 16/05/03 54160
공지 [질문] 19금 질문은 되도록 자제해주십시오 [8] OrBef 15/10/28 77956
공지 [질문] 통합 공지사항 + 질문 게시판 이용에 관하여. [22] 항즐이 08/07/22 118367
140264 [질문] 서울역~삼청동 사이 저녁 식사 질문입니다. [1] 피를마시는새148 19/12/08 148
140263 [질문] 젤다 야숨 관련 질문입니다 [4] 베르나르399 19/12/08 399
140262 [질문] 스마트폰으로 인터넷을 하면 광고가 뜹니다 [3] 다레니안220 19/12/08 220
140261 [질문] 일상 사진(스냅샷) 잘 올라오는 사이트 있을까요? [1] 오스피디189 19/12/08 189
140260 [질문] 유튜브 채널 추천 부탁드립니다. [1] Rays154 19/12/08 154
140258 [질문] 안톤버그(Anthon berg) 초콜릿 내용물은 다 똑같은 건가요? norrell94 19/12/08 94
140257 [질문] 교통사고 형사처벌관련 질문입니다. [3] 완성형폭풍저그329 19/12/08 329
140256 [질문] 대출 관련 질문 드립니다. (신혼집) [6] deadbody475 19/12/08 475
140254 [삭제예정] 오늘 격전 같이 하실분 또는 자리 있으신분? [3] 삭제됨425 19/12/08 425
140253 [질문] 10만원 아래로 안경테 좋은게 있을까요? [4] HEM15636 19/12/08 636
140251 [질문] 이순신 장군님 능력치는 어느정도일까요? [21] 서린언니1310 19/12/08 1310
140250 [질문] 우리나라와 일본의 문화적/사회적 유사성 및 경제관계에 대해 알고 싶습니다. 빅게임 피쳐219 19/12/08 219
140249 [질문] 무선 리시버 샀는데 엑박원 패드가 연결이 안됩니다. [2] 카서스194 19/12/08 194
140248 [질문] 방 기계음 해결 방법 향티182 19/12/08 182
140247 [질문] 이거 뭔뜻인지 아시나요 [5] 진리902 19/12/08 902
140246 [질문] 이런 야구선수가 있다면 가치가 있을까요? [31] Dunn1405 19/12/08 1405
140245 [질문] 추위 많이 타시는 분들 [15] Pi970 19/12/08 970
140244 [질문] 혼자 사시는 분들(전세) 가구/가전 많이 사시나요? [16] 박근혜875 19/12/08 875
목록 이전 다음
댓글

+ : 최근 1시간내에 달린 댓글
+ : 최근 2시간내에 달린 댓글
맨 위로