엑셀 MOD 함수는 두 수의 나눗셈에서 나머지만 구할 때 사용합니다.
쉽게 말해 A를 B로 나누었을 때 몫이 C 나머지가 D라고 하면 D만을 반환합니다.(몫은 쓰이지 않습니다.)
사실 나머지를 구하는 함수는 엑셀에서 빈도수가 그렇게 높지는 않습니다.
하지만 표의 형태에 따라서 유용한 응용을 할 수 있습니다.
구체적으로 응용하는 패턴은 행이나 열에서 일정 간격으로 떨어져 있는 셀들만을 추출 할 수 있습니다.
예를들어 1, 3, 5, 7, 9와 같은 패턴은 2로 나누었을 때 나머지가 1이 되는 수들입니다. 이것을 응용하여
1행, 3행, 5행, 7행, 9행, ... 이렇게 두칸 간격으로 떨어진 셀들을 뽑아서 어떤 작업을 할 수 있습니다. (예를들어 MOD 함수를 사용하여 행을 2로 나누었을 때 나머지가 1인 행들만 뽑아낼 수 있습니다.)
이렇게 일정한 간격으로 떨어진 셀들을 뽑아낼 때 MOD 함수를 사용하면 편리합니다.
엑셀 MOD 함수
MOD(피제수, 제수)
피제수 ÷ 제수 를 하여 나머지를 반환합니다.
피제수 : 나누어지는 수
제수 : 나누는 수
사용 예)
3÷2의 몫은 1 나머지는 1 따라서 MOD(3, 2)는 1
3.14÷2의 몫은 1 나머지는 1.14 따라서 MOD(3.14, 2)는 1.14
5÷2의 몫은 2 나머지는 1 따라서 MOD(5, 2)는 1
일정 간격 떨어진 셀들의 합계를 구하는 방법
위 표를 보면 하나의 물품에 3가지 명목(원가, 부가세, 운송비)의 가격이 붙어 있습니다. 이 때 총 운송비를 구하는 방법을 알아보겠습니다.
먼저 이 표에서 가격들이 일정한 패턴을 가지며 나열되어 있습니다. 그리고 운송비에 해당하는 가격만 뽑아내는 것은 수열의 규칙을 찾는 것과 같은데 운송비에 해당하는 행번호를 보면 차례대로 4, 7, 10의 3행 간격으로 나열되어 있습니다.
이 패턴은 3칸 간격으로 되어 있으므로 3으로 나누었을 때 나머지가 1이 되는 셀들입니다.
이 성질을 이용해서 더하려는 값들만 추출할 수 있습니다.
① {=SUM((MOD(ROW(D2:D10), 3)=1)*D2:D10)}
위 수식은 운송비(주황색)에 해당하는 값들만 더하는 수식입니다.
② =SUM(D2:D10)은 모든 값들을 다 더하는 수식입니다.
① 수식은 ②수식에서 붉은 부분을 추가하여 곱한 후 배열수식(CTRL+SHIFT +Enter)으로 만든 수식입니다.
그러면 붉은 부분이 무엇을 뜻하는지와 배열수식에 대해서 이해하면 됩니다.
배열 수식에 대한 설명은 위 링크를 참조하세요. 그리고 붉은 부분을 설명하겠습니다.
배열수식으로 만든 수식이므로 ROW(D2:D10)은 D2:D10 범위에서 순서대로 행의 값을 반환합니다. 반환된 행의 값들은 (2,3,4,5,6,7,8,9,10)이 됩니다.
반환된 행의 값 각각에 대해서 MOD(행의 값, 3)=1을 계산합니다. 이 수식은 행의 값을 3으로 나누어서 나머지가 1이 되면 TRUE를 반환합니다. (4, 7, 10 행입니다.)
만약 3으로 나누어 나머지가 1이 아니면 FALSE를 반환합니다. (TRUE는 숫자 1, FALSE는 숫자 0이라고 생각하면 됩니다.)
따라서 셀 범위 D2:D10에서의 합을 구할 때 MOD(행의 값, 3)=1이 TRUE를 반환하는 행(4, 7, 10)에 대해서만 합을 구하게 됩니다. 왜냐면 다음과 같이 False 값을 곱한 값은 0이되고 True 값을 곱한 행(운송비에 해당하는 행들)은 값의 변화가 없기 때문이죠.
참고로 umproduct 함수를 사용하면 배열수식 없이 위와 동일한 합을 구할 수 있습니다.
=SUMPRODUCT((MOD(ROW(D2:D10), 3)=1)*D2:D10)
이것은 sumproduct 함수의 특성 때문이므로 자세한 것은 이 함수의 설명을 참고하세요.