일정한 간격으로 된 값 합계 구하기 - 매크로

사용자 정의 함수로 작성하는 것이 바람직하지만
굳이 만든다고 하면 매크로, Sub 프로시저로도 만들 수 있습니다.

sum_interval(final).xlsm

0.02MB

 
 
 
사용자 정의 함수와 매크로는 모두 VBA를 이용하지만 아래와 같이 다른 점이 있습니다.

사용자 정의 함수	매크로
사용자 정의 함수를 실행하면  VBA에서 실행한 후 값을 반환합니다.	매크로를 실행하면 VBA에서 실행한 후 워크시트 함수를 이용한 수식을 반환합니다.
=sum_interval(D4:N4,4)	=SUM(IF(MOD(COLUMN($D$3:$O$3),2)=0,$D$3:$O$3))
함수의 인수를 사용자 정의 함수에 입력함	함수의 인수를 매크로 실행시 입력 받음

 
1. 논리

범위와 간격을 입력받는 창을 실행하고, 입력 받은 값을 기준으로 배열 수식을 만들어 반환한다.
 

Sub make_formulaArray()
    Dim calc_range As Range
    Dim interval As Integer, remainder As Integer, col_row As Integer
    
    Set calc_range = Application.InputBox(Title:="일정한 간격 합계 구하기", _
        prompt:="더할 범위를 선택하세요", Type:=8)
    interval = Application.InputBox("간격을 입력하세요", Default:=2, Type:=2)
    
    'col_row는 가로 방향인 경우는 1, 세로 방향인 경우 2이상
    col_row = calc_range.Rows.Count
    
    Select Case col_row
        Case 1:
            remainder = calc_range(1, 1).Column Mod interval
            Selection.FormulaArray = "=SUM(IF(MOD(COLUMN(" & _
                calc_range.Address(0, 0) & ")," & interval & ")=" & _
                "mod(column(" & calc_range(1, 1).Address(0, 0) & ")," _
                & interval & ")" & "," & calc_range.Address(0, 0) & "))"
        Case Is > 1:
            remainder = calc_range(1, 1).Row Mod interval
            Selection.FormulaArray = "=SUM(IF(MOD(row(" & _
                calc_range.Address(0, 0) & ")," & interval & ")=" & _
                "mod(row(" & calc_range(1, 1).Address(0, 0) & ")," _
                & interval & ")" & "," & calc_range.Address(0, 0) & "))"
            Selection.NumberFormat = "#,##0_)"
    End Select
    
    '셀의 숫자 표시형식을 천 단위마다 콤마 삽입으로 지정
    Selection.NumberFormat = "#,##0_)"
End Sub

 

2. 배열 수식 입력

일반 수식은 .Formula(생략 가능)을 이용하는데, 배열 수식은 .FormulaArray를 이용해 입력합니다
 
개발 도구 > Visual Basic을 눌러 VB Editor를 실행한 후
직접 실행창에 아래 수식을 하나씩 붙여 넣은 후 엔터 키를 누르면 해당 셀에 수식이 입력됩니다.

직접 실행창이 보이지 않는다면 보기 - 직접 실행창을 클릭하면 됩니다.

Range("b3") = "=sum(d3,f3)" : B3셀에 =sum(d3,f3) 수식이 입력됩니다.

 

Range("c3").formulaarray = "=sum(e3:e4*f3:f4)" : C3셀에 배열 수식 =sum(e3:e4*f3:f4)가 입력됩니다.

 
배열 수식 {=sum((e3:e4*f3:f4)}은 =e3*f3+e4*f4이므로 =15,504*878+15,504*878이고, 수식 좌우에 중괄호가 보입니다. 중괄호는 입력하는 것이 아니며, 배열 수식이라는 것을 나타내는 것입니다.

 
Microsoft 365에서는 Ctrl + Shift + Enter키를 눌러 배열 수식을 입력하지 않고 엔터키를 눌러 입력해도 됩니다. 따라서, 아래와 같이 중괄호 표시는 없지만 결과값은 같습니다.

 
 
3. 매크로 작성
 

Sub make_FormulaArray()
    Dim calc_range As Range
    Dim interval As Integer, remainder As Integer, col_row As Integer
    
    Set calc_range = Application.InputBox(Title:="일정한 간격 합계 구하기", _
        prompt:="더할 범위를 선택하세요", Type:=8)
    interval = Application.InputBox("간격을 입력하세요", Default:=2, Type:=1)
    
    'col_row는 가로 방향인 경우는 1, 세로 방향인 경우 2이상
    col_row = calc_range.Rows.Count
    
    Select Case col_row
        Case 1:
            remainder = calc_range(1, 1).Column Mod interval
            Selection.FormulaArray = "=SUM(IF(MOD(COLUMN(" & _
                calc_range.Address(0, 0) & ")," & interval & ")=" & _
                "mod(column(" & calc_range(1, 1).Address(0, 0) & ")," _
                & interval & ")" & "," & calc_range.Address(0, 0) & "))"
        Case Is > 1:
            remainder = calc_range(1, 1).Row Mod interval
            Selection.FormulaArray = "=SUM(IF(MOD(row(" & _
                calc_range.Address(0, 0) & ")," & interval & ")=" & _
                "mod(row(" & calc_range(1, 1).Address(0, 0) & ")," _
                & interval & ")" & "," & calc_range.Address(0, 0) & "))"
    End Select
    
    '셀의 숫자 표시형식을 천 단위마다 콤마 삽입으로 지정
    Selection.NumberFormat = "#,##0_)"
End Sub

 

4. 매크로 설명
 

Sub make_FormulaArray()

프로시저명을 make_FormulaArray로 지정했고, 인수는 입력받지 않습니다.
 

Dim calc_range As Range Dim interval As Integer, remainder As Integer, col_row As Integer

사용자 정의 함수와 동일하게 계산 범위를 저장하기 위한 변수 calc_range를 범위 형식으로 선언하고,
 
간격 interval, 나머지 remainder, 가로,세로 방향 col_row를 정수 형식으로 선언합니다.
 

Set calc_range = Application.InputBox(Title:="일정한 간격 합계 구하기", _         prompt:="더할 범위를 선택하세요", Type:=8)

Application.InputBox에 대한 설명은 아래 글을 참고 바랍니다.
https://lsw3210.tistory.com/entry/%EB%B2%94%EC%9C%84-%EB%93%B1%EC%9D%84-%EC%9E%85%EB%A0%A5%EB%B0%9B%EB%8A%94-ApplicationInputBox-%EB%A9%94%EC%86%8C%EB%93%9C1
 
calc_range가 범위이기 때문에 값을 대입할 때 Set을 먼저 써야 합니다.
 
Title은 창 제목이고, Prompt는 입력 셀 위에 표시되는 설명, Type:=8은 범위 형식으로 입력받기 위한 Parameter 명입니다. Parameter에 값을 입력할 때는 :=(콜론 =) 다음에 입력합니다.
 
실행하면 아래와 같이 창이 표시됩니다.

 

interval = Application.InputBox("간격을 입력하세요", Default:=2, Type:=2)

간격을 입력받는데, 이번에는 첫번째 인수명이 Prompt이기 때문에 Prompt라고 입력하지 않았으며, 간격의 기본값, Default를 2로 입력했습니다. Type:1은 숫자 형식으로 입력받는 것입니다.
 
실행하면 아래와 같이 표시됩니다. 기본 값으로 2가 표시되며, Prompt는 입력한 값대로 표시되는데, Tilte값은 없어서 입력이라고 표시됩니다.

 

'col_row는 가로 방향인 경우는 1, 세로 방향인 경우 2이상 col_row = calc_range.Rows.Count

가로 또는 세로 방향인지를 판단하기 위한 변수 col_row는 행수가 1인지 그보다 큰지로 결정합니다.
calc_range.Rows.Count는 calc_range의 총 행 수를 반환하며, 그 값을 col_row에 대입합니다.
 

Select Case col_row     Case 1:         처리문1     Case Is > 1:         처리문2 Select case

If 조건문을 사용해도 되지만 Select Case문을 사용했습니다.
 
col_row값이 1인 경우는 Case 1 아래 처리문1을 실행하고,
col_row값이 1보다 클 때는 Case 2 아래 처리문2를 실행합니다.
 
col_row값이 특정 값일 때는 그 값을 적으면 되는데 큰 값일 때는
위와 같이 Is > 1식으로 표현합니다.
 

[가로 방향 합계일 경우 처리문]

remainder = calc_range(1, 1).Column Mod interval Selection.FormulaArray = "=SUM(IF(MOD(COLUMN(" & _     calc_range.Address(0, 0) & ")," & interval & ")=" & _     "mod(column(" & calc_range(1, 1).Address(0, 0) & ")," _     & interval & ")" & "," & calc_range.Address(0, 0) & "))"

remainder는 사용자 정의 함수에서 사용한 구문과 같습니다.
calc_range 첫 셀의 열 번호를 interval로 나눈 나머지를 remainder에 저장합니다.
 
Selection.FormulaArray = "=SUM(IF(MOD(COLUMN(" & _
    calc_range.Address(0, 0) & ")," & interval & ")=" & _
    "mod(column(" & calc_range(1, 1).Address(0, 0) & ")," _
    & interval & ")" & "," & calc_range.Address(0, 0) & "))"
 
수식이 길기 때문에 여러 줄로 표시하기 위해 줄 끝 부분에 _를 입력했습니다.
 
"MOD(COLUMN(" calc_range.Address(0, 0) & ")," & interval & ") 
  : 문자열 부분은 큰 따옴표 안에 입력하고, 변수 부분은 큰 따옴표 없이 문자열과 &로 결합해서 입력합니다.
    MOD(COLUMN(는 함수명이므로 문자열로 입력하고,
    calc_range.Address(0, 0)는 큰 따옴표 안에 넣으면 셀 주소가 아니라 아래와 같이 calc_range.Address(0, 0)란 문자열로 표시되므로

 
셀 주소를 표시하도록 큰 따옴표 밖에 입력한 것입니다. 그러면 아래와 같이 셀 주소 D3:N3로 표시됩니다.

 
셀 주소 다음에 (0, 0)이라고 입력하지 않으면 절대 참조 형식으로 표시하기 때문에

 
(0, 0)를 붙여서 상대 참조 형식으로 지정한 것입니다.
 
? 다음에 위 수식을 모두 붙여 넣고 엔터키를 누르면 아래와 같
=SUM(IF(MOD(COLUMN(D3:N3),2)=mod(column(D3),2),D3:N3))이 반환됩니다.

 
remainder 값을 구할 때는 Mod를 연산자로 사용했는데,
위 수식에서는 워크시트에서 사용해야 할 수식이기 때문에 Mod를 함수로 입력했습니다.
 
그리고, 나머지 값이 옆 셀로 복사해서 붙여 넣을 때는 0이 아니라 1로 자동으로 바뀌어야 하기 때문에 

 
숫자로 입력하지 않고, mod(column(D3),2)라고 수식으로 입력했습니다.
오른쪽 셀로 복사하면 D3가 E3셀로 바뀌기 때문에 나머지가 1이 됩니다.
 

[세로 방향 합계일 경우 처리문]

Case Is > 1:     remainder = calc_range(1, 1).Row Mod interval     Selection.FormulaArray = "=SUM(IF(MOD(row(" & _     calc_range.Address(0, 0) & ")," & interval & ")=" & _     "mod(row(" & calc_range(1, 1).Address(0, 0) & ")," _     & interval & ")" & "," & calc_range.Address(0, 0) & "))"

가로 방향과 다른 점은 Column대신 Row를 사용했다는 점입니다.
 

 
5. 매크로 실행

가. 가로방향인 경우

 
B3셀에 커서를 놓고, 개발도구 - 매크로를 누른 다음 매크로 이름 목록에서 make_FormulaArray를 더블 클릭하거나, 클릭하고 실행 버튼을 누릅니다.

 
그러면 "더할 범위를 선택하세요"라고 하는데, 일반 수식의 경우는 범위가 고정시키기 위해 $D$3:$O$3식으로 입력하는데, 매크로에서는 위치에 따라 나머지 값을 자동으로 구할 수 있도록 D3에서 N3까지 마우스로 끌어서 범위를 선택합니다.

그러면 D3:N3가 절대 참조 형식으로 입력되는데, 매크로에서 상대 참조 형식으로 바꿔주므로 문제 없습니다. 이제 확인 버튼을 누르면

 
간격의 기본 값이 2로 표시되는데, 다른 값이라면 수정 후 확인 버튼을 누릅니다.

 
그러면 B3셀에 =SUM(IF(MOD(COLUMN(D3:N3),2)=MOD(COLUMN(D3),2),D3:N3))라고 수식이 입력됩니다. 

의미는 열 번호를 간격 2로 나눈 값이 D3셀의 열 번호를 2로 나눈 나머지와 같은 열을 D3셀에서 N3셀에서 찾아 그 값을 더하는 것입니다.
 
D열의 열 번호는 2이고, 2로 나누면 나머지가 0이 되며, D3셀에서 N3셀에서 나머지가 0인 열은 D, F, H, I, L, N열이므로 이들 열의 합을 구하는 것입니다.
 
이제 B3셀의 채우기 핸들을 오른쪽으로 끈 다음 다시 아래로 끌면
B3셀부터 C4셀까지 수식이 모두 채워지는데
C4셀의 수식을 확인해보면 

 
=SUM(IF(MOD(COLUMN(E4:O4),2)=MOD(COLUMN(E4),2),E4:O4))로 범위가 E4셀에서 O4셀로 자동으로 변경됐고, 나머지를 구하는 수식 MOD(COLUMN(E4),2)를 마우스로 범위로 잡은 후 

 
F9키를 눌러 값을 확인하면 1이 반환되는데, 중괄호 사이에 있습니다.

 
원래의 수식대로 돌리기 위해 Esc키를 누릅니다.
 

나. 세로방향인 경우

 
위와 마찬가지로 해보면 가로 방향 수식의 COLUMN이 Row로 바뀌어서=SUM(IF(MOD(ROW(C4:C14),2)=MOD(ROW(C4),2),C4:C14))이 되며, 값은 12,864 같습니다.

 
마찬가지로 C2셀의 채우기 핸들을 오른쪽으로 끈 다음, 다시 아래로 내리면 C2셀에서 D3셀까지 수식이 모두 채워지는데, D3셀의 수식을 확인하면 =SUM(IF(MOD(ROW(D5:D15),2)=MOD(ROW(D5),2),D5:D15))로 위 수식이 상대적인 취치에 따라 범위가 변경된 것을 알 수 있습니다. 다시 말해 C4:C14가 D5:D15로 바뀌고, C4가 D5로 변경되었습니다.

 
간격이 3개 이상인 경우도 해보기 바랍니다.
 
표는 바꾸지 않고 범위를 C4에서 C15셀로 지정하고, 간격을 3으로 수정하고 확인 버튼을 누르면 수식이 =SUM(IF(MOD(ROW(C4:C15),3)=MOD(ROW(C4),3),C4:C15))로 입력되고, 값이 14,211,120으로 나오는데,

 
이것은 C4셀부터 시작해서 3행씩 떨어진 C7, C10, C13셀을 더한 값입니다.
 
C2셀의 채우기 핸들을 오른쪽으로 끈 다음 다시 아래로 내리면 D3셀까지 수식이 채워지는데
D3셀의 수식이  =SUM(IF(MOD(ROW(D5:D16),3)=MOD(ROW(D5),3),D5:D16))로 

 
D5셀부터 간격이 3이므로 D8, D11, D14셀의 합계이므로 Ctrl 키를 누른 상태에서 마우스로 D5, D8, D11, D14셀을 클릭하면 오른쪽에 합계 7,688,688이 표시되므로 맞습니다.

 
만약 합계가 보이지 않는다면 상태 표시줄에서 마우스 오른쪽 버튼을 누른 후 나오는데 메뉴에서 합계에 체크하면 됩니다.

 

sum_interval(formulaarray).xlsm

0.02MB