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

cur_reg_calc2.xlsm

0.02MB

 

 

 

1편에서는 표로 만들었는데, 입력하지 않은 주변 셀까지 수식이 자동으로 입력되어 범위로 변경하였습니다.

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

 

이전 편에서는 과목별 합계 등을 구해봤는데,

이번에는 성명을 기준으로 한 합계 등을 구해보겠습니다.

 

다른 점은 과목별 합계를 구할 때는 Sum 등이 아래에 있어서 4개를 뺄 수 있었는데,

성명별 합계를 구할 때는 처음에는 없다가 VBA를 실행하면 추가되기 때문에

두 가지 요소를 고려해서 매크로를 작성해야 한다는 것입니다.

 

또한 합계 등을 구할 때 행 별로 합계를 구해야 하고, 행을 내려가면서 합계 수식을 반복하는데 Sum이전의 end_row_of_calc(계산할 마지막 행) 까지만 실행해야 합니다.

 

 

1. 코드 작성

가. 계산할 마지막 열 구하기

Sum 등이 있을 경우와 없을 경우 end_col_of_calc가 달라지므로

아래와 같이 end_col_of_calc 변수를 정수 형식으로 선언하고 

    '계산할 마지막 열 구하기
    Dim end_col_of_calc As Integer
    If cur_range(1, col_num) = "Var" Then
        end_col_of_calc = col_num - 4
    Else
        end_col_of_calc = col_num
    End If

 

1행 열 수, 다시 말해 마지막 열이 Var인 경우는

end_col_of_calc = col_num - 4

다시 말해 계산할 마지막 열은 열수 - 4가 되고,

 

아닌 경우는 Sum 등이 없는 경우이므로

end_col_of_calc은 col_num이 됩니다.

 

나. Sum 등 헤더 입력하기

    ' 계산할 마지막 열 오른쪽에 순서대로 Sum부터 Var까지 입력
    If cur_range(1, col_num) <> "Var" Then
        cur_range(1, end_col_of_calc + 1) = "Sum"
        cur_range(1, end_col_of_calc + 2) = "Average"
        cur_range(1, end_col_of_calc + 3) = "Stdev"
        cur_range(1, end_col_of_calc + 4) = "Var"
    End If

 

If cur_range(1, col_num) <> "Var" Then :

  <>는 '같지 않다'는 의미의 연산자이므로,

 위 구문은 "1행 마지막 열이 Var이 아닌 경우에만"이란 의미가 됩니다.

 

VBA는 대,소문자를 구분하지 않는데,

If cur_range(1, col_num) <> "Var" Then 안에 있는 Var을 var로 수정하면

True, 다시 말해 "Var과 var은 다르다"라는 결과가 나옵니다.

 

즉, 문자가 같은지 비교할 때 대,소문자를 구분합니다.

 

cur_range(1, end_col_of_calc + 1) = "Sum" :

  1행의 '계산할 마지막 열' 다음부터 순서대로 Sum, Average, Stdev, Var이라고 입력합니다.

 

 

다. 기본 for ~ next 반복문의 조건 수정

11행 이후에 있는 Sum등 수식이 end_col_of_calc까지만 실행되도록

For i = 2 To end_col_num을

For i = 2 To end_col_of_calc로 수정해야 합니다.

왜냐하면 아래에 있는 합계 등 수식이 col_num으로 하게 되면

Var가 있는 열까지 수식이 입력되기 때문입니다.

 

 

라. 성명별 합계 등을 구할  for ~ next 반복문 작성

    '2행부터 계산할 마지막 행까지 반복 실행
    For i = 2 To end_row_of_calc
        
        '합계를 구하는 수식을 Range를 이용해 지정 후 (계산할 마지막 열+1)부터 입력
        cur_range(i, end_col_of_calc + 1) = "=sum(" & Range(cur_range(i, 2), _
            cur_range(i, end_col_of_calc)).Address(0, 0) & ")"
        cur_range(i, end_col_of_calc + 2) = "=average(" & Range(cur_range(i, 2), _
            cur_range(i, end_col_of_calc)).Address(0, 0) & ")"
        cur_range(i, end_col_of_calc + 3) = "=stdev(" & Range(cur_range(i, 2), _
            cur_range(i, end_col_of_calc)).Address(0, 0) & ")"
        cur_range(i, end_col_of_calc + 4) = "=var(" & Range(cur_range(i, 2), _
            cur_range(i, end_col_of_calc)).Address(0, 0) & ")"
    Next

 

For i = 2 To end_row_of_calc :

  계수기 i는 위 반복문과 동일하게 사용해도 되며,

  2행부터 계산할 마지막 행까지 반복합니다.

 

cur_range(i, end_col_of_calc + 1) = "=sum(" & Range(cur_range(i, 2), _
            cur_range(i, end_col_of_calc)).Address(0, 0) & ")"  :

  i행 end_col_of_calc + 1열에 i행 2열부터 계산할 마지막열까지이 합을 계산하는 수식을 입력합니다.

 

그리고,   i행 end_col_of_calc + 2열에는 Average수식을,  end_col_of_calc + 3열에는 Stdev 수식을,   end_col_of_calc + 4열에는 Var수식을 입력합니다.

 

 

2. 코드 실행

가. 코드 실행

이렇게 코드를 작성하면 오른쪽에 Sum 등 헤더가 있거나 없거나 아래와 같이 계산할 마지막 열 오른쪽에 합계 등 수식이 입력됩니다.

 

나. 성명이 추가되거나 과목이 추가되는 경우의 코드 실행

아래와 같이 한 사람을 추가하고, 과목을 하나 추가하더라도

 

매크로를 실행하면 13행부터의 합계 등 수식과 

 

G열부터의 합계 등 수식이 제대로 작성됩니다.

 

이 때 G열부터의 Sum 등은 지워도 되지만 13행부터의 Sum 등은 지우면 안됩니다.

왜냐하면 13행부터의 Sum은 있는 것을 기준으로 코드를 만들었기 때문입니다.

 

다. 현재 영역을 이동시킨 경우의 코드 실행

아래와 같이 오른쪽으로 1칸 이동하고, 아래로 2칸 이동한 후 실행해

 

아래와 같이 이상한 결과가 나오는데 

 

이것은 현재 영역을 설정할 때

Set cur_range = Range("a2").CurrentRegion

이라고 A2셀을 기준으로 현재 영역을 설정했기 때문입니다.

 

따라서, Range("a2")를 현재 셀 위치를 기준으로 하도록

ActiveCell로 바꿔야 합니다.

 

그러면 아래와 같이 됩니다.

Set cur_range = ActiveCell.CurrentRegion

 

2행을 지운 다음 셀 포인터를 표 안에 넣고(예, C5셀) 다시 실행하면 아래와 같이 잘 됩니다.

 

완성된 코드와 

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 = ActiveCell.CurrentRegion
    
    ' 현재 영역의 줄 수, 열 수와 계산할 범위의 끝 행을 변수에 저장
    row_num = cur_range.Rows.Count
    col_num = cur_range.Columns.Count
    end_row_of_calc = row_num - 4
    
    '계산할 마지막 열 구하기
    Dim end_col_of_calc As Integer
    If cur_range(1, col_num) = "Var" Then
        end_col_of_calc = col_num - 4
    Else
        end_col_of_calc = col_num
    End If

    
    
    '2열부터 열 수까지 반복 실행
    For i = 2 To end_col_of_calc
        '합계를 구하는 수식을 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) & ")"
        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) & ")"
        
        '위와 동일한 수식이나 시작 셀과 끝 셀을 Address을 이용해 &(결합 연산자)로 연결
'            cur_range(end_row_of_calc+1, i) = "=sum(" & cur_range(2, i).Address _
            & ":" & cur_range(end_row_of_calc, i).Address & ")"
    Next


    ' 계산할 마지막 열 오른쪽에 순서대로 Sum부터 Var까지 입력
    If cur_range(1, col_num) <> "Var" Then
        cur_range(1, end_col_of_calc + 1) = "Sum"
        cur_range(1, end_col_of_calc + 2) = "Average"
        cur_range(1, end_col_of_calc + 3) = "Stdev"
        cur_range(1, end_col_of_calc + 4) = "Var"
    End If
    
    
    '2행부터 계산할 마지막 행까지 반복 실행
    For i = 2 To end_row_of_calc
        
        '합계를 구하는 수식을 Range를 이용해 지정 후 (계산할 마지막 열+1)부터 입력
        cur_range(i, end_col_of_calc + 1) = "=sum(" & Range(cur_range(i, 2), _
            cur_range(i, end_col_of_calc)).Address(0, 0) & ")"
        cur_range(i, end_col_of_calc + 2) = "=average(" & Range(cur_range(i, 2), _
            cur_range(i, end_col_of_calc)).Address(0, 0) & ")"
        cur_range(i, end_col_of_calc + 3) = "=stdev(" & Range(cur_range(i, 2), _
            cur_range(i, end_col_of_calc)).Address(0, 0) & ")"
        cur_range(i, end_col_of_calc + 4) = "=var(" & Range(cur_range(i, 2), _
            cur_range(i, end_col_of_calc)).Address(0, 0) & ")"
    Next
End Sub

 

파일은 아래와 같습니다.

cur_reg_calc2(final).xlsm

0.02MB