아래와 같이 일정한 간격으로 떨어진 값을 구하는 것을 배열 수식과 VBA 사용자 정의함수를 이용해 구해 보겠습니다.
1. 배열 수식
https://support.microsoft.com/ko-kr/office/%EB%B0%B0%EC%97%B4-%EC%88%98%EC%8B%9D-%EC%A7%80%EC%B9%A8-%EB%B0%8F-%EC%98%88%EC%A0%9C-7d94a64e-3ff3-4686-9372-ecfd5caa57c7
위 사이트에
"배열 수식은 배열의 하나 이상의 항목에 대해 여러 계산을 수행할 수 있는 수식입니다. 배열을 값의 행 또는 열 또는 행과 값 열의 조합(an array as a row or column of values, or a combination of rows and columns of values)으로 생각할 수 있습니다. 배열 수식은 여러 결과 또는 단일 결과를 반환할 수 있습니다." 라고 되어 있으며,
"2018년 9월 업데이트부터 Microsoft 365에서는Ctrl+Shift+Enter이 아니라 Enter만을 눌러 배열 수식을 입력할 수 있다."라는 내용도 있습니다.
2. Mod 함수와 column 함수
가. Mod 함수Mod(피젯수, 제수) : 피젯수를 제수로 나눴을 때 나머지를 구해줍니다.예) =mod(3,2)는 1이 됩니다.
나. Column 함수
칼럼을 숫자로 바꾸줍니다.
예를 들어 =column(d3)라고 하면 D열이므로 4가 반환됩니다.
다, If 함수조건식이 참인지 거짓인지에 따라 값을 반환해주는 함수입니다.구문은 if(조건식, 참일 때 값, 거짓일 때 값)입니다.
예를 들어 =if(d3>30,1,0)이라고 하면 D3셀의 값이 30보다 크므로 1이 반환됩니다.
3. Mod, Column, If함수와 배열 수식의 결합
가. 가로 방향 합계 구하기
(1) 수량 합계 수식
수량 합계는 수량을 더하는데 D열, F열 등이므로 해당하는 열의 수 4와 6 등을 Column함수를 이용해 구하고, 그 값을 2로 나눴을 때 나머지가 0이 되는 값을 Mod함수를 이용해 구한 다음 해당하는 열의 합계를 Sum 함수를 이용해 구하면 됩니다.
따라서 B3셀의 수식은
=SUM(IF(MOD(COLUMN($D$3:$O$3),2)=0,D3:$O$3)) 또는
=SUM((MOD(COLUMN($D$3:$O$3),2)=0)*$D$3:$O$3) 이 됩니다.
첫번째 수식에서 IF(MOD(COLUMN($D$3:$O$3),2)=0의 값이 0 또는 1이기 때문에 두번째 수식으로 바꿀 수 있는 것입니다.
Microsoft 365의 경우는 CSE(Ctrl + Shift + Enter)를 누르지 않고 Enter키만으로 입력할 수 있는데,
CSE를 눌러서 배열 수식을 입력하면 수식 양쪽에 중괄호 표시({ })가 보입니다.
(2) 수식 계산 과정 확인하기
=SUM((MOD(COLUMN($D$3:$O$3),2)=0)*$D$3:$O$3)의 계산 과정을 살펴보기 위해
Formuals(수식) > Evaluate Formula(수식 계산)을 누릅니다.
그럼 먼저 COLUMN($D$3:$O$3)에 밑줄이 쳐져 있는데 아래 Evaluate(계산) 버튼을 누르면
D3셀부터 O3셀까지의 열에 해당하는 숫자 4부터 15까지를 배열(중괄호 사이에 숫자가 컴마로 구분되어 있음)로 반환해줍니다. 여기서 Evaluate 버튼을 누르면 4부터 15까지를 2로 나눴을 때 나머지를 Mod함수로 구하므로
0,1,0,1,0,1,0,1,0,1,0,1이 반환됩니다. 그리고, Evaluate 버튼을 누르면 배열과 0이 일치하는지 값을 구해줍니다.
그러면 True, False 등이 번갈아 가면서 표시됩니다. 다시 Evaluate 버튼을 누르면
{True, False...} 바깥에 있는 괄호를 제거합니다. 그리고 다시 Evaluate 버튼을 누르면 True, False 등과 D3에서 O3셀까지의 값을 곱하는데, True는 1, False는 0이므
34,0,878,0 등의 값이 반환됩니다. 이제 Evaluate 버튼을 누르면 sum(합계)을 하므로
최종적으로 12,864가 구해집니다.
(2) 금액 합계 구하기
금액은 E,G 등 열에 있으므로 5를 2로 나누면 나머지가 1이 됩니다.
따라서, 수식에서 0을 1로 바꾸면 됩니다.
=SUM((MOD(COLUMN($D$3:$O$3),2)=1)*$D$3:$O$3)
=sum(e4,g4,i4,k4,m4,o4)를 해보면 합계 45,474,904 맞습니다.
하나씩 떨어진 값을 구할 때 잘못 클릭하면 값이 달라질 수 있는데,
이렇게 수식을 작성하면 정확한 값을 구할 수 있습니다.
만약 3칸씩 떨어진 값이라면 D열인 경우 4이므로 3으로 나눴을 때 1이 됩니다.
따라서, 수식은 =SUM((MOD(COLUMN($D$3:$O$3),3)=1)*$D$3:$O$3)이 됩니다.
위 수식으로 구한 값이 맞는지 확인하기 위해 , D3셀부터 3칸씩 떨어진 셀 G3, J3, M3셀을 Ctrl 키를 누른 상태에서 마우스로 클릭하면 상태 표시줄에 합계 14,211,120이 표시되므로, 맞습니다.
나. 세로 방향 합계 구하기
(1) 수량 합계 수식
세로 방향으로 일정한 간격으로 떨어진 값을 구할 때는 Column함수가 아니라 Row(행)함수를 구하는 점만 다릅니다.
C2셀에 =SUM((MOD(ROW(C$4:C$15),2)=0)*(C$4:C$15))라고 입력하고
CSE 또는 Enter키를 누르면
12,864라는 값이 구해집니다.
위 수식에서 C$4:C$15라고 입력한 것은 오른쪽 또는 아래로 수식을 복사할 때 변하지 말아야 할 것에는 $표시를 붙이고,변해도 되는 것에는 $표시를 붙이지 않은 것입니다.
다시 말해 오른쪽으로 복사할 때는 C열이 D열이 되어야 하지만
아래로 복사하더라도 4행에서 15행은 변하면 안되기 때문에 4와 15에는 $표시를 붙인 것입니다.
(2) 수식 계산 과정 확인하기
마우스를 끌어서 위 수식에서 ROW(C$4:C$15)를 범위로 지정한 후
F9키를 누르면 4행부터 15행까지의 행 수, 4부터 15까지가 반환합니다.
그리고, MOD(ROW(C$4:C$15),2)라는 수식으로 위에서 구한 행 수를 2로 나눴을 때의 나머지를 구하므로 0,1,0,1 식으로 값이 반환되고,
이를 MOD(ROW(C$4:C$15),2)=0라는 수식으로 0과 비교해서 True, False 등 값을 반환하고,
(MOD(ROW(C$4:C$15),2)=0)*(C$4:C$15)라는 수식으로 위에서 구한 값을 C$4:C$15와 곱하면 1일 때는 C$4:C$15에서 해당 셀의 값이 반환되는데 0이면 0이 반환됩니다.
그리고, 최종적으로 SUM함수를 이용해 합계를 구합니다.
원래대로 수식을 돌리기 위해 Esc키를 누릅니다.
(3) 수식을 오른쪽과 아래에 채우기
C2셀의 채우기 핸들을 오른쪽으로 D열까지 끌고, 다시 아래로 3행까지 끌면
아래와 같이 수식이 채워지는데, 3행의 결과값이 2행과 같습니다.
이것은 나머지가 1이어야 하는데 0이라서 그런 것이므로
C3과 D3셀 두개를 선택한 다음 수식 =SUM((MOD(ROW(C$4:C$15),2)=0)*(C$4:C$15))에서 0을 1로 수정하고 Ctrl + Shift +Enter키를 누르면 됩니다.
'Excel' 카테고리의 다른 글
| Lambda 함수를 이용한 일정 간격의 값 합계 구하기 (0) | 2023.03.23 |
|---|---|
| Let 함수 - 변수에 값 할당 및 수식 계산 (0) | 2023.03.22 |
| 엑셀에서 전체 범위 선택하기 - 단축키 (0) | 2023.03.14 |
| 조건부 서식과 색 기준 필터(2) (2) | 2023.03.01 |
| 조건부 서식과 색 기준 필터(1) (0) | 2023.02.28 |