CurrentRegion 속성을 이용한 합계 등 계산(1)

 

cur_reg_calc.xlsx

0.01MB

StdDev는 Stdev의 오타입니다. 이하 캡쳐 화면 동일

 

1. CurrentRegion의 주소 체계

A2셀을 기준으로 한 CurrentRegion은 전체 시트의 시각에서 보면 A2셀부터 E15셀까지이지만,
CurrentRegion의 입장에서 보면 현재 영역의 왼쪽 위가 A1셀, 다시 말해 행이 1, 열이 1인 셀부터 행이 14, 열이 5인 E14셀까지로 새로운 주소 체계를 갖고 있습니다.,

셀 주소는 A1 참조 스타일로 보면 위와 같이 A2, E15식으로 표시하지만,
파일 - 옵션 - 수식 탭에서 'R1C1 참조 스타일'의 왼쪽에 체크하고 확인 버튼을 누르면

열을 표시하는 기호가 알파벳이 아니라 숫자로 바뀌며, D14셀의 주소가 R10C4로 바뀝니다.
R10C4란 10행 4열이란 의미로서 A1 참조 스타일의 경우와 달리 행이 먼저 표기됩니다.

 

2. CurrentRegion을 이용한 Sum(합계), Average(평균), StDev(표준 편차), Var(분산) 구하기

 

가. 논리 구성하기

(1) 과목별 합계 등 구하기
과목별 합계는 2행부터 총 행 수 14에서 총계, 합계, 표준편차, 분산의 4행을 뺀 행인 11행까지 더합니다.

과목별로 합계는 11행, 평균은 12행, 표준분산은 13행, 편차는 14행에 표시하는데 열 기준으로는 2열부터 5열까지 표시합니다.

이것은 현재 영역을 기준으로 한 주소 체계이기 때문에 현재 영역이 이동하더라도 주소는 동일한 장점이 있습니다.

(2) 성명별 합계 등 구하기
성명별 합계 등은 2열부터 5열까지 구하는 것은 동일한데 합계 등을 6열부터 표기합니다.
그리고, 행은 위와 마찬가지로 10행까지만 계산하면 됩니다.

 

 

나. 과목별 합계 등 구하는 코드 짜기

Option Explicit

Sub calc_func()
    ' 현재 영역을 저장하기 위한 변수선언
    Dim cur_range As Range
    
    ' for 반복문 관련 변수 선언
    Dim i As Integer
    
    ' 줄 수, 열 수, 계산 범위 끝 행 저장을 위한 변수 선언
    Dim row_num As Integer, col_num As Integer, end_row_of_calc As Integer
    
    ' A2셀을 기준으로 한 현재 영역을 cur_range 변수에 저장
    Set cur_range = Range("a2").CurrentRegion
    
    ' 현재 영역의 줄 수, 열 수와 계산할 범위의 끝 행을 변수에 저장
    row_num = cur_range.Rows.Count
    col_num = cur_range.Columns.Count
    end_row_of_calc = row_num - 4

        '2열부터 열 수까지 반복 시행
        For i = 2 To col_num
            '합계를 구하는 수식을 Range를 이용해 지정 후 (행수-3)행 2열부터 입력
            cur_range(row_num - 3, i) = "=sum(" & Range(cur_range(2, i), _
                cur_range(end_row_of_calc, i)).Address(0, 0) & ")"
            
            '위와 동일한 수식이나 시작 셀과 끝 셀을 Address을 이용해 &(결합 연산자)로 연결
'            cur_range(end_row_of_calc+1, i) = "=sum(" & cur_range(2, i).Address _
                & ":" & cur_range(end_row_of_calc, i).Address & ")"
        Next

End Sub

 

다. 코드 설명

(1) 범위 지정
범위 지정할 때 변수 형식을 Range로 설정하고,
범위를 변수에 저장할 때는 Set 변수명 = 범위로 지정합니다.

(2) 변수 지정
정수 변수는 integer 타입으로 선언하고,
변수명 = 값으로 대입합니다.

(3) 행 수, 열 수 구하기
속성을 지정할 때는
cur_range.rows.count식으로 .(점)으로 연결합니다.

 

그리고, row_num 변수에 대입하면

row_num = cur_range.Rows.Count

이 됩니다.

 

열 수는 아래와 같이 구한 후 col_num 변수에 대입합니다.

col_num = cur_range.Columns.Count

 

계산할 마지막 행은 아래와 같이 행수에서 4를 뺀 값입니다.

end_row_of_calc = row_num - 4

(4) for next 반복문

For counter = start To end [ Step step ]
    [ statements ]
    [ Exit For ]
    [ statements ]
Next [ counter ]

 

대괄호 안에 있는 것은 선택적인 요소이므로 생략가능합니다.
counter(계수기)가 있어서 시작값부터 끝값까지 반복합니다.

Step은 간격이란 의미로 반복 실행 시 간격을 지정하는데,
for i=1 to 5 step 2라고 하면
1,3,5만 실행됩니다.

exit for는 반복문을 중간에 끝내는 명령입니다.
if i>3 then exit for라고 하면 3까지만 실행하고 끝납니다.

※ 참고로, VBA(Visual Basic for Application)는 VB(Visual Basic)를 이용해 엑셀 등에 맞게 만든 프로그램이지만 완전히 동일하지는 않습니다.

VB의 For ~ Next문은 아래와 같이
Counter의 형식을 For문에서 지정할 수 있고, Continue For가 가능합니다.

(5) 합계 수식
b12셀의 합계 수식은 =sum(b3:b11)입니다.
여기서 =sum은 문자로서 변하지 않지만, b2:b12는 현재 영역의 크기에 따라 변하므로 변수로 입력해야 하며, 큰따옴표를 붙이면 문자가 되므로 붙이면 안 됩니다.

또한 시트를 기준으로 하면 b3이지만 현재 영역을 기준으로 하면 b2가 됩니다.

범위를 지정하는 방식은 Range를 이용해서 Range(시작셀, 끝셀) 식으로 지정할 수도 있고,

 

sum함수의 범위처럼 :(콜론)을 사용해서 '시작셀 주소 : 끝 셀 주소' 식으로 지정할 수도 있습니다.

다시말해, 2행 i열의 주소와 end_row_of_calc행의 i열의 주소를 &(결합 연산자)로 연결해서 수식을 만드는 것입니다.

따라서, 수식은

 "=sum(" & Range(cur_range(2, i), cur_range(end_row_of_calc, i)).Address & ")"
또는

"=sum(" & cur_range(2, i).Address & ":" & cur_range(end_row_of_calc, i).Address & ")"
이 됩니다.

 

또한 위 수식이 들어갈 합계 수식셀은 현재는 CurrentRegion기준으로 보면 11행인데, 현재 영역이 변동될 수 있으므로

end_row_of_calc 또는 row_num을 이용해서 지정해야 합니다.

 

end_row_of_calc를 기준으로 하면

Sum은 end_row_of_calc+1행이 되고,

row_num을 기준으로 하면 row_num-3이 됩니다.

 

따라서, 수식은

cur_range(end_row_of_calc+1, i) = "=sum(" & Range(cur_range(2, i), cur_range(end_row_of_calc, i)).Address & ")"

또는 
cur_range(row_num-3, i) = "=sum(" & cur_range(2, i).Address & ":" & cur_range(end_row_of_calc, i).Address & ")"

가 됩니다.

 

수식이 길 경우는 아래와 같이 _(언더 바)를 줄 끝부분에 입력한 후 뒷 부분을 다음 줄로 내릴 수 있습니다.

cur_range(row_num - 3, i) = "=sum(" & Range(cur_range(2, i), _
                cur_range(end_row_of_calc, i)).Address(0, 0) & ")"

라. 코드 실행

표준도구 모음의 실행 아이콘 또는 F5키를 눌러 매크로를 실행하고 맨 왼쪽 엑셀 아이콘을 눌러

 

워크시트로 돌아가면 B12셀의 수식이 =SUM($B$3:$B$11)로서, 현재 영역을 기준으로 주소를 지정했지만, 워크 시트를 기준으로 한 셀 주소가 입력되어 있습니다.

또한 C열은 보면 =SUM($C$3:$C$11)이라고 B가 C로 자동 변경되어 수식이 작성됐습니다.

 

셀 주소를 절대 참조 형식이 아니라 상대 참조 형식으로 바꾸고 싶으면 Address 다음에 (0,0)을 추가하면 됩니다.
cur_range(row_num - 3, i) = "=sum(" & cur_range(2, i).Address _

    & ":" & cur_range(end_row_of_calc, i).Address(0, 0) & ")"

그리고 실행하면 D12셀의 수식이 =SUM(D3:D11)로 $표시가 사라집니다.

(6) Average 등 수식 추가
Average 등 수식은 계산할 범위는 동일하므로 수정할 필요가 없고, 결과를 입력할 행과 함수명만 수정하면 됩니다.

	cur_range(row_num - 3, i) = "=sum(" & Range(cur_range(2, i), _
		cur_range(end_row_of_calc, i)).Address(0, 0) & ")"
	cur_range(row_num - 2, i) = "=average(" & Range(cur_range(2, i), _
		cur_range(end_row_of_calc, i)).Address(0, 0) & ")"
	cur_range(row_num - 1, i) = "=stdev(" & Range(cur_range(2, i), _
		cur_range(end_row_of_calc, i)).Address(0, 0) & ")"
	cur_range(row_num, i) = "=var(" & Range(cur_range(2, i), _
		cur_range(end_row_of_calc, i)).Address(0, 0) & ")"

그러면 아래와 같이 결과가 모두 입력됩니다.

성명별 합계 등은 다음 편에서 다루겠습니다.

cur_reg_calc(final).xlsm

0.02MB