1. 배열 수식의 단점
배열 수식을 이용할 경우 편리한 것 같기도 하지만
아래와 같이 여러 가지 함수를 결합해서 사용해야 하고,
=SUM((MOD(COLUMN($D$3:$O$3),2)=0)*$D$3:$O$3)
합계를 구하는 셀의 위치가 달라지면 나머지 값을 바꿔줘야 하는 불편함이 있습니다.
그래서 VBA로 사용자 정의 함수를 만드는 것을 해보겠습니다.
2. 사용자 정의(지정) 함수 만들기
가. Function과 Sub 프로시저
사용자 정의 함수는 Sub 프러시저와 같이 모듈에 만드는데
약간 다른 점이 있습니다.
| Function Procedure(사용자 정의 함수) | Sub Procedure |
| Function 함수명(인수1 as 형식, ...) as 형식 처리 프로세스 함수명 = 값 End Function |
Sub 매크로명(인수1 as 형식, ...) 처리 프로세스 End Sub |
| 함수명의 처리한 값을 반환 | 처리만 하고 반환하는 값이 없음 |
나. 논리
입력받은 범위에서 셀을 돌아가면서 나머지가 같은 경우만 합계를 구해서 합계를 반환하면 됩니다.
다. 일정한 간격으로 떨어진 값의 합계를 구하는 사용자 정의 함수 작성
(1) 사용자 정의 함수 작성
배열 수식을 이용할 때는 가로 방향인지 세로 방향인지에 따라 Column과 Row함수를 사용했는데,
함수를 만들 때는 가로 방향인지 세로 방향인지를 0 또는 1로 입력받아 쉽게 처리할 수 있습니다.
작성된 Function 프로시저의 내용은 아래와 같습니다.
Function sum_interval(calc_range As Range, interval As Integer, Optional col_row As Boolean = 0) As Double
' 나머지를 저장하기 위한 remainder와 for 반복문의 counter인 n 선언
Dim remainder As Integer, n As Integer
' 가로 방향일 경우, 첫 열(first_col)과 마지막 열(end_col), 그리고, 처리할 행(proc_row) 변수 선언
Dim first_col As Integer, end_col As Integer, proc_row As Integer
' 세로 방향일 경우, 첫 줄(first_row)과 마지막 줄(end_row), 그리고, 처리할 열(proc_col) 변수 선언
Dim first_row As Integer, end_row As Integer, proc_col As Integer
' sub 프로시저 실행 안됨
' Call number_format
Select Case col_row
' 가로 방향 합산일 경우
Case 0:
' remainder 변수에 계산 범위의 첫 셀에 해당하는 열을 간격으로 나눈 나머지를 저장
remainder = calc_range(1, 1).Column Mod interval
' first_col에 계산 범위의 첫 셀의 열을 저장
first_col = calc_range(1, 1).Column
' end_col에 first_col에 계산 범위의 열 개수를 더한 후 1을 뺀 값을 저장
end_col = first_col + calc_range.Columns.Count - 1
' proc_row에 계산 범위의 행을 저장
proc_row = calc_range.Row
' first_col부터 end_col까지 interval 간격으로 실행
For n = first_col To end_col Step interval
' sum_inteval에 자기 자신의 값에 계산 범위 행, n번째 열의 값을 더한 값을 저장
sum_interval = sum_interval + Cells(proc_row, n)
Next
' 세로 방향 합산일 경우
Case Else:
' remainder 변수에 계산 범위의 첫 셀에 해당하는 행 번호를 간격으로 나눈 나머지를 저장
remainder = calc_range(1, 1).Row Mod interval
' first_row에 계산 범위의 첫 셀에 해당하는 행 번호를 저장
first_row = calc_range(1, 1).Row
' end_row에 first_row에 계산 범위의 행의 개수를 더한 후 1을 뺀 값을 저장
end_row = first_row + calc_range.Rows.Count - 1
' proc_col에 계산 범위의 열 번호를 저장
proc_col = calc_range.Column
For n = first_row To end_row Step interval
sum_interval = sum_interval + Cells(n, proc_col)
Next
End Select
End Function
(2) 사용자 정의 함수 설명
| Function sum_interval(calc_range As Range, interval As Integer, Optional col_row As Boolean = 0) As Double |
Function sum_interval : 함수명을 sum_interval로 지정했으며,
calc_range As Range, interval As Integer, Optional col_row As Boolean = 0 :
인수는 calc_range와 interval, col_row 3개이며, 각각 범위, 정수, 참 거짓 타입입니다.
세 번째 인수인 col_row는 기본값을 0(가로 방향)으로 지정하고, 입력하지 않으면 초기값인 0이 적용되도록 Optional로 지정했습니다.
As Double : 반환되는 값의 형식을 Double로 지정했습니다.
| ' 나머지를 저장하기 위한 remainder와 for 반복문의 counter인 n 선언 Dim remainder As Integer, n As Integer ' 가로 방향일 경우, 첫 열(first_col)과 마지막 열(end_col), 그리고, 처리할 행(proc_row) 변수 선언 Dim first_col As Integer, end_col As Integer, proc_row As Integer ' 세로 방향일 경우, 첫 줄(first_row)과 마지막 줄(end_row), 그리고, 처리할 열(proc_col) 변수 선언 Dim first_row As Integer, end_row As Integer, proc_col As Integer |
프로시저 내부에서 사용하는 변수들을 지정하는 부분입니다.
변수는 비교할 나머지 값을 저장하기 위한 remainder,
for ~ next 반복문의 counter인 n이 있고,
더하는 방향이 가로방향인지, 세로 방향인지에 따라 변수가 다릅니다.
가로방향일 때는 첫 번째 열인 first_col, 마지막 열인 end_col이 필요하며, 셀 주소를 지정할 때 행이 필요한 데 행을 구해서 저장할 proc_row변수가 있고,
세로 방향일 때는 첫 번째 행인 first_row, 마지막 행인 end_row가 필요하며, 셀 주소를 지정할 때 열이 필요하므로, 열을 구해서 저장할 proc_col변수를 지정합니다.
모두 As Integer로 정수 형식입니다.
다른 언어의 경우는 변수를 선언하면서 값을 지정할 수 있는데,
예) Integer n = 1
VBA에서는 안됩니다.
① 가로 방향일 경우 처리
| Select Case col_row Case 0: 처리 프로세스1 Case Else: 처리 프로세스2 End Select |
Select Case문은 조건에 따라 처리하는 조건문의 일종입니다.
위 조건문은 col_row값이 0(가로 방향) 일 때와 0이 아닐 경우(세로 방향)로 나눠서 처리합니다.
Case 1: 이라고 하면 1일 때만 세로 방향 처리가 가능한데, Case Else: 면 0이 아닌 값은 모두 세로 방향으로 처리되므로 처리되지 않는 경우가 발생하지 않는 장점이 있습니다.
| Case 0: ' remainder 변수에 계산 범위의 첫 셀에 해당하는 열을 간격으로 나눈 나머지를 저장 remainder = calc_range(1, 1).Column Mod interval ' first_col에 계산 범위의 첫 셀의 열을 저장 first_col = calc_range(1, 1).Column ' end_col에 first_col에 계산 범위의 열 개수를 더한 후 1을 뺀 값을 저장 end_col = first_col + calc_range.Columns.Count - 1 ' proc_row에 계산 범위의 행을 저장 proc_row = calc_range.Row ' first_col부터 end_col까지 interval 간격으로 실행 For n = first_col To end_col Step interval ' sum_inteval에 자기 자신의 값에 계산 범위 행, n번째 열의 값을 더한 값을 저장 sum_interval = sum_interval + Cells(proc_row, n) Next |
remainder = calc_range(1, 1).Column Mod interval
나머지는 계산 범위인 calc_range의 A1셀, 첫 번째 셀의 열 번호를 간격인 interval로 나눈 후 나머지를 remainder에 저장합니다.
(Coumn 함수와 Column 속성)
워크시트에서 Column은 함수인데, VB A에서는 속성인 점이 다릅니다.
함수는 Column(D4) 식으로 함수명 안에 셀 주소를 입력하는데, 속성은 Range("D4").Column 식으로 범위 다음에 점(.)을 찍고 속성명 Column을 입력합니다.
(Mod 함수와 Mod 연산자)
| Mod 함수 | Mod 연산자 |
| 워크시트에서는 Mod가 함수로서 Mod(피젯수, 제수) 식으로 입력해서 나머지를 구함 |
VBA에서는 Mod가 연산자로서 피젯수 Mod 제수 형식으로 입력해서 나머지를 구함 |
first_col = calc_range(1, 1).Column
첫 번째 열인 first_col은 calc_range의 첫 번째 셀의 열 번호입니다.
범위 내 셀 주소에 대해서는 아래 글을 참고 바랍니다.
end_col = first_col + calc_range.Columns.Count - 1
마지막 열인 end_col은 first_col에 계산 범위의 열 개수를 더하고 1입니다.
아래 화면을 기준으로 설명하면 first_col은 D열이므로 4가 되고, end_col은 N열이므로, 14입니다. 그리고, calc_range.columns.count는 calc_range가 D4:N4인 경우는 D열부터 N열이므로 11이고, D4:O4인 경우는 12가 됩니다.
따라서, D4:N4를 기준으로 설명하면 end_col은 4 + 11 - 1는 14이며, N열의 열 번호입니다.
N열은 calc_range의 끝 셀의 열 번호이므로
end_col = calc_range(calc_range.Rows.Count, calc_range.Columns.Count).column
라고 바꿀 수 있습니다.
proc_row = calc_range.Row : 위에서는 열에 해당하는 변숫값만을 구했는데 셀은 행과 열로 구성되므로 행에 해당하는 숫자를 구해야 하므로 calc_range.Row라고 구해서 proc_row에 저장합니다.
| For n = first_col To end_col Step interval sum_interval = sum_interval + Cells(proc_row, n) Next |
For ~ Next 반복문을 이용하는데, Step을 이용해서 interval 간격으로 반복문을 실행하도록 합니다.
For Each ~ Next 반복문을 사용하면 셀을 모두 이동하면서 반복하는데, Step을 이용해서 필요 없는 부분을 건너뛸 수 있는 장점이 있습니다. 또한 모든 셀을 이동하면서 처리한다고 하면 나머지가 같은 열만 처리하도록 하는 조건문이 추가되어야 하는 불편함도 있습니다.
sum_interval = sum_interval + Cells(proc_row, n)
sum_interval에 자신의 값에 Cells(proc_row, n)을 더함으로써 누적된 값, 합계가 구해집니다.
Cells함수는 행과 열 번호를 받아 셀 주소를 지정하며, Cells(proc_row, n)은 셀 주소를 가리키지만 다음에 .Value(값)이 생략된 것이므로 셀 주소에 해당하는 값입니다.
셀 주소를 반환하려면 .Address라고 해야 합니다.
sum_interval값은 셀을 반복할 때마다 증가하게 됩니다. 다시 말해 sum_interval값이 D열을 처리할 때는 0+34=34가 되고, F열을 처리할 때는 34+878=912가 됩니다.
② 세로 방향일 경우 처리
| Case Else: ' remainder 변수에 계산 범위의 첫 셀에 해당하는 행 번호를 간격으로 나눈 나머지를 저장 remainder = calc_range(1, 1).Row Mod interval ' first_row에 계산 범위의 첫 셀에 해당하는 행 번호를 저장 first_row = calc_range(1, 1).Row ' end_row에 first_row에 계산 범위의 행의 개수를 더한 후 1을 뺀 값을 저장 end_row = first_row + calc_range.Rows.Count - 1 ' proc_col에 계산 범위의 열 번호를 저장 proc_col = calc_range.Column For n = first_row To end_row Step interval sum_interval = sum_interval + Cells(n, proc_col) Next End Select |
세로 방향으로 합계를 구할 때는 Column이 아니라 Row인 것만 다릅니다. 다만, proc_col만 Row가 아니라 Column입니다.
(3) 사용자 정의 함수 적용
① 직접 함수 식 입력
B4셀에 =sum까지 입력하면 sum_interval함수가 보이므로 아래로 이동 후 탭 키를 누르거나 마우스로 함수명을 더블 클릭해서 함수명을 입력하고,
계산할 범위(calc_ranage)는 D4셀부터 N4셀 또는 O4셀까지 지정한 다음
간격으로 두 칸씩 떨어져 있으니까 2를 입력하고,
가로 또는 세로 방향(col_row)은 가로 방향이므로 0을 입력하거나 0이 기본값이므로 입력하지 않아도 됩니다.
완성된 수식은 =sum_interval(D4:N4,2,0)이며, 결괏값이 위에서 구한 12,864와 같습니다(3행과 4행의 데이터가 같습니다).
② 함수 마법사 이용 입력
수식 입력줄 왼쪽의 fx 함수 삽입 아이콘을 클릭하면
Insert Function(함수 마법사) 창이 표시되는데,
category(범주)를 User Defined(사용자 정의)로 변경하고, 사용자 정의 함수 목록에서 sum_interval을 찾아 클릭하고 OK 버튼을 누릅니다.
그러면 Function Arguments(함수 인수) 창에 인수명 Calc_range, Interval, Col_row과 입력 칸이 표시되는데,
Calc_range로는 금액부터 금액까지 입력해야 하므로 E4셀부터 O4셀까지 마우스로 끌어서 범위를 지정하고,
Interval은 2를 입력합니다.
그러면 함수의 결괏값 45474904가 인수명 아래 오른쪽에 표시됩니다. 이제 OK 버튼을 누르면
위에서 나왔던 45474904가 입력됩니다.
여기서 주의할 것은 일반 함수의 경우는 절대 참조 형식으로 입력하는데,
sum_interval함수는 오른쪽 또는 아래로 채우기 핸들을 끌 때 셀 주소가 자동으로 변하도록 상대참조형식으로 입력하는 것이 편리하고, 범위도 더할 값에 해당하는 첫 번째 셀부터 지정해야 한다는 것입니다.
다시 말해, 수량의 합계를 구할 때는 D열부터 범위를 지정해야 하고, 금액의 합계를 구할 때는 E열부터 범위를 지정해야 합니다.
그런, B4셀의 채우기 핸들을 오른쪽으로 끌면 자동으로 셀 주소가 바뀌기 때문에 정확한 값이 자동으로 구해집니다.
③ 세로 방향에 적용
C2셀에
=sum_interval(C4:C14,2,1)라고 범위는 수량부터 수량까지인 C4셀에서 C14셀까지 지정하고, 간격은 2, 세로 방향이므로 0이 아닌 1을 입력합니다. 그러면 가로 방향으로 했을 때 수량 합계 12,864이 구해집니다.
이제 C2셀의 채우기 핸들을 오른쪽으로 끌고, 아래로 끌면 C2셀부터 D3셀까지 수식이 채워지는데,
수량의 합계는 12,864, 금액의 합계는 45,474,904로 같습니다.
C3셀의 수식을 확인해 보면 =sum_interval(C5:C15,2,1)로 C4:C14가 아래로 이동했으므로 C5:C15로 자동으로 수정됐습니다.
셀 서식에 컴마를 넣기 위해 사용자 정의 함수에
sum_interval = FormatNumber(sum_interval, 0)을 추가해도 적용되지 않고,
아래와 같이 매크로를 작성한 후 매크로를 실행해야 적용됩니다.
Sub number_format()
Selection.NumberFormat = "#,##0_)"
End Sub
'EXCEL - VBA' 카테고리의 다른 글
| 날짜간의 차이 계산(4) - VBA DateDiff 함수 (0) | 2023.04.10 |
|---|---|
| 일정한 간격으로 된 값 합계 구하기 - 매크로 (2) | 2023.03.21 |
| 엑셀에서 전체 범위 선택하기 - VBA (2) | 2023.03.15 |
| Range.AutoFilter Method(8) - 필터된 영역에 붙여넣기(2-3) (0) | 2023.03.13 |
| Range.AutoFilter Method(2) - Field, Criteria, Operator, SubField, VisibleDropDown (2) | 2023.03.12 |