PGR21.com
- PGR21 관련된 질문 및 건의는 [건의 게시판]을 이용바랍니다.
- (2013년 3월 이전) 오래된 질문글은 [이전 질문 게시판]에 있습니다.
통합 규정을 준수해 주십시오. (2015.12.25.)
Date 2019/11/19 16:55:56
Name 산양
File #1 1차원을_3차원_도표로_전환.png (193.0 KB), Download : 19
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 19:31
수정 아이콘
Screenupdating은 현재 사용하고 있습니다.

DoEvents를 찾아보니 vba에 물린 cpu를 잠시 놔주는 명령어인 듯 싶네요. 감사합니다.
19/11/19 17:53
수정 아이콘
애초에 지금 현재 하시려고하는게 굳이 저런 VBA로 해야하는건지 의문이 듭니다. 좌측 2열만 쓴다면 나머지는 일반 함수로도 가능할것 같은데요..
19/11/19 19:30
수정 아이콘
사진은 예시입니다.

테이블에 입력된 행이 최소 3만, 최대 15만행을 넘어가고, 매트릭스도 30*30 규모인 경우가 많다보니 sumif를 사용한 함수 입력시 많이 느려지더라구요.
또한, 함수값 반환시 그대로 끝나는 게 아니라 해당 값을 2차 가공해야 하는 경우가 잦아 가급적 함수를 쓰지 않는 형태로 개선해보고자 새로 짜고 있습니다.
이달의소녀
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가 필요없습니다.
19/11/20 19:32
수정 아이콘
확인해보고 적용해본 뒤 답글 드리도록 하겠습니다.

조언 정말 감사합니다!
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
수정 아이콘
아, 이게 문제였군요. 감사합니다.

반복문의 변수는... 제가 비전공자라.. 딱히 어찌 설명해야 할지 몰라서 그래 썼습니다. 혹시 필드에선 뭐라고 부르는지 알 수 있을까요?
Supervenience
19/11/20 19:40
수정 아이콘
Iterator 라 부릅니다 정확하게 말하자면 경우가 다르지만요. 선형적 데이터 구조가 있을 때 iterator를 씁니다.
여튼 돌이란 말과 거의 같은 말이고 순 우리말 돌이가 너무 적합한 말인데 처음보다 보니 감탄했습니다.
19/11/20 20:05
수정 아이콘
아 제가 필요한 부분만 그때그때 배우다보니 그런 건 미처 알지 못했어요. 답변 감사합니다!
목록 삭게로! 맨위로
번호 제목 이름 날짜 조회
공지 댓글잠금 [질문] 통합 규정(2019.11.8. 개정) jjohny=쿠마 19/11/08 98760
공지 [질문] [삭제예정] 카테고리가 생겼습니다. [9] 유스티스 18/05/08 119842
공지 [질문] 성인 정보를 포함하는 글에 대한 공지입니다 [38] OrBef 16/05/03 167452
공지 [질문] 19금 질문은 되도록 자제해주십시오 [8] OrBef 15/10/28 201789
공지 [질문] 통합 공지사항 + 질문 게시판 이용에 관하여. [22] 항즐이 08/07/22 251566
175533 [질문] 엑셀 질문입니다.. [4] 아스날272 24/03/28 272
175532 [질문] 요즘에 핸드폰 어떻게들 구매하시나요? [17] 유유할때유1336 24/03/28 1336
175531 [질문] 1인칭 3인칭 게임 멀미 차이 심한가요 [11] 2차대전의 서막959 24/03/28 959
175530 [질문] 페페로니 피자에서 잡내가 나요 [13] 어제본꿈2593 24/03/28 2593
175529 [질문] 대형마트에 식품코너, 메리트가 궁금합니다. [24] 푸끆이2351 24/03/27 2351
175528 [질문] 초1,2 아이들과 주말 여행 질문드립니다 이리세1425 24/03/27 1425
175527 [질문] 혹시 영어 공부로 스픽 어플 프리미엄 써보신분 있으신가요? [3] Bronx Bombers1513 24/03/27 1513
175526 [삭제예정] 엉덩이 주변에 종기가 났는데... [8] 삭제됨1877 24/03/27 1877
175525 [질문] 본체 견적 이륙 가능할까요? 2번째 [2] JOE832 24/03/27 832
175524 [질문] 컴퓨터 조립 추천 요청 [9] 어니닷1018 24/03/27 1018
175523 [질문] 아이패드 에어 6세대(?) 출시일이 대략 언제쯤일까요? [4] AquaMarine1007 24/03/27 1007
175522 [질문] 스펨메일 하나가 왔는데요 [8] nekorean1265 24/03/27 1265
175521 [질문] 가공식품에서의 닭고기 [4] backtoback872 24/03/27 872
175520 [질문] (젖툭튀가리는)티셔츠에 입을만한 속티쳐스 혹은 니플패치 추천부탁드립니다. [9] 라온하제964 24/03/27 964
175519 [질문] 아래 관련 질문 (차용증 vs 전자계약) [1] 하아아아암312 24/03/27 312
175518 [질문] 부모님에게 돈을 빌리는 문제 질문드립니다 [11] 굼시1653 24/03/27 1653
175517 [질문] 만화를 찾습니다. [6] 어제내린비1474 24/03/27 1474
175516 [질문] 노트북 추천 부탁드립니다(상세조건 기입) [1] 망고치즈케이크893 24/03/27 893
목록 이전 다음
댓글

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