엑셀 MOD함수 - 나머지 구하기

Excel MOD 기능 - 구문 및 기본 사용

Excel의 MOD 함수는 한 숫자 (피제수)를 다른 숫자 (제수)로 나눈 후 나머지를 찾는 데 사용됩니다. 수학에서는 이 연산을 모듈로 연산 또는 모듈러스라고 하므로 MOD라는 이름이 붙습니다.

MOD 함수의 구문은 다음과 같습니다.

MOD(number, divisor)

 

• Number (필수) - 나눌 숫자입니다.
• Divisor (필수) - 나누는 기준입니다.

예를 들어, 10을 3으로 나눈 값은 3이고 나머지 값은 1(10=3*3+1)이므로 MOD(10,3)는 1을 반환합니다. 10을 나머지 없이 5로 나누기 때문에 MOD(10,5) 공식은 0을 반환합니다.

 

Excel의 MOD에 대해 알아야 할 3가지 사항

1. MOD 함수의 결과는 디바이저와 동일한 부호를 가집니다.
   디바이저가 0인 경우 0으로 나눌 수 없으므로 MOD는 #DIV/0! 오류를 반환합니다.
   숫자 또는 디바이어스가 텍스트 값이면 Excel Mod 수식은 #VALUE! 오류를 반환합니다.
2.  

 

셀을 계산하는 MOD 수식

실제 워크시트에서 Excel MOD 기능은 단독으로 거의 사용되지 않습니다. 더 자주, 나눗셈 나머지를 기반으로 다양한 계산을 수행하는 더 큰 공식의 일부로 찾을 수 있습니다.

 

모든 N 번째 행 또는 열을 합산하는 수식 수정

Microsoft Excel에는 워크시트의 두 번째, 세 번째 등의 행을 모두 합하는 내장 함수가 없습니다. 그러나 다음 공식 중 하나를 사용하여 쉽게 수행할 수 있습니다.

 

다른 모든 행의 합계

두 번째 행마다 값을 더하려면 MOD를 ROW 및 SUMPRODUCT와 함께 사용합니다.

짝수 행 합계 :

SUMPRODUCT((MOD(ROW(range),2)=0)*(range))

홀수 행 합계:

SUMPRODUCT((MOD(ROW(range),2)=1)*(range))

B2:B7이 합산할 셀이라고 가정하면 수식은 다음과 같습니다.

 

짝수 행 합계 :
=SUMPRODUCT((MOD(ROW($B$2:$B$7),2)=0)*($B$2:$B$7))

 

홀수 행 합계:
=SUMPRODUCT((MOD(ROW($B$2:$B$7),2)=1)*($B$2:$B$7))

 

아래 스크린 샷은 결과를 보여줍니다.

 

이러한 수식에서 MOD ROW 조합은 합계를 계산할 행을 결정하고 SUMPRODUCT는 값을 더합니다.

 

방법은 다음과 같습니다.

ROW는 MOD 함수의 number 인수에 행 번호의 배열을 제공합니다. MOD는 각 행 번호를 2로 나누고 결과를 확인합니다.

• 짝수 행의 합을 구하려면 짝수가 나머지 없이 균등하게 0로 나눌 수 있으므로 결과가 2이어야 합니다.
• 홀수 행의 합을 구하려면 홀수를 1로 나눈 값이 2의 나머지를 남기므로 결과는 1이어야 합니다.

 

SUM PRODUCT 대신 IF 기능을 사용하여 조건을 평가하고 SUM을 사용하여 숫자를 합산할 수 있습니다(Ctrl + Shift + Enter 키를 눌러 입력하는 배열 공식입니다)

=SUM(IF(MOD(ROW($B$2:$B$7),2)=0,$B$2:$B$7,0))

 

ROW는 휘발성 함수이고 배열 수식에서 사용하면 전체 수식이 휘발성이기 때문에 SUMPRODUCT를 고수합니다. 즉, 통합 문서를 변경할 때마다 수식이 다시 계산되므로 Excel 속도가 눈에 띄게 느려질 수 있습니다.

 

N번째 행마다 합계

3일, 4일, 5일 등마다 합산합니다. 행, 다음 일반 공식을 사용하십시오.

SUMPRODUCT((MOD(ROW(range)-ROW(first_cell)+1,n)=0)*(range))

예를 들어 C3 : C2 범위의 10번째 셀마다 합산하려면 수식은 다음과 같습니다.

=SUMPRODUCT((MOD(ROW($C$2:$C$10)-ROW($C$2)+1,3)=0)*($C$2:$C$10))

 

이전 예제와 비교할 때 이 수식에는 좀 더 복잡한 "필터링 논리"가 있습니다.

MOD(ROW($C$2:$C$10)-ROW($C$2)+1,3)=0

 

먼저 범위의 다른 모든 행에서 첫 번째 행을 빼고 상대 행 번호 배열 {0;1;2;3;4;5;6;7;8}을 가져옵니다. 여기에 1을 추가하여 1부터 카운트를 시작합니다. {1;2;3;4;5;6;7;9}
위의 배열은 MOD 함수의 number 인수로 들어갑니다. MOD 함수는 각 숫자를 3으로 나눈 후 나머지를 반환합니다. {1;2;0;1;0}

세 번째 행마다 0이 나타나고, MOD()=0은 Excel에 이 행에 대해서만 TRUE를 반환하고, 다른 모든 행에 대해서는 FALSE를 반환하도록 지시합니다. TRUE가 1이고 FALSE가 0이면 {0,0,1,0,0,1,0,0,0,1}의 어레이가 남습니다

마지막으로 SUM PRODUCT는 위의 배열과 C2:C10을 곱한 다음 곱을 합합니다. 0을 곱하면 항상 0이 되므로 첫 번째 배열에 1을 포함하는 행만 곱하면 됩니다.

 

N번째 열마다 합계

매 2번째, 3번째 등의 숫자를 합산합니다. 열을 바꾸려면 다음과 같이 위의 공식에서 ROW를 COLUMN으로 바꾸기만 하면 됩니다.

SUMPRODUCT((MOD(COLUMN(range)-COLUMN(first_cell)+1,n)=0)*(range))

 

예를 들어, B4 : I2 범위의 2번째 열마다 숫자를 더하려면 다음 공식을 사용합니다.

=SUMPRODUCT((MOD(COLUMN($B2:$I2)-COLUMN($B2)+1,4)=0)*($B2:$I2))

 

모든 N 셀을 연결하는 수식 수정

동일한 항목과 관련된 세부 정보가 여러 셀로 분할되는 경우 N개의 모든 셀 수의 내용을 하나의 셀로 결합하는 것이 어려울 수 있습니다.

이 예에서는 B 열의 세 개의 서로 다른 셀에 주문 세부 정보가 있으므로 B2부터 시작하여 세 셀의 각 블록을 연결하려고 합니다. 이것은 다음 공식으로 수행 할 수 있습니다.

=IF(MOD(ROW()-1, 3)=0, CONCATENATE(B2, "-", B3, "-", B4), "")

 

먼저 주어진 행이 나머지없이 3으로 나눌 수 있는지 확인합니다 : MOD(ROW()-1, 3)=0. 현재 행의 상대적 위치를 얻으려면 실제 행 번호에서 1을 뺍니다(데이터가 2행이 아닌 1행에서 시작되기 때문에). 나눗셈 나머지가 0과 같으면  CONCATENATE 함수는 3개 셀의 값을 결합하여 선택한 구분 기호(이 예에서는 "-")로 구분합니다. 상대 셀 참조를 사용하면 매번 세 개의 서로 다른 셀이 연결됩니다.

 

홀수 또는 짝수를 포함하는 셀을 계산하는 수식 수정

이미 알고 있듯이 짝수 정수를 식별하는 가장 쉬운 방법은 숫자를 2로 나누고 나머지가 0인지 확인하는 것입니다. 홀수를 식별하려면 나머지 1을 확인합니다. 다시 MOD 함수를 사용하여 나머지를 찾고 SUM PRODUCT를 사용하여 "필터링된 셀"을 계산합니다.

 

홀수로 셀 수 계산:

SUMPRODUCT((MOD(range,2)=1)*1)

짝수로 셀 수 계산:

SUMPRODUCT((MOD(range,2)=0)*1)

A2에서 A8 사이의 숫자가 있는 수식의 모양은 다음과 같습니다.

홀수를 포함하는 셀 수:

=SUMPRODUCT((MOD(A2:A8,2)=1)*1)

 

짝수를 포함하는 셀 수 계산:

=SUMPRODUCT((MOD(A2:A8,2)=0)*1)

 

Excel에서 MOD를 사용하여 셀을 강조 표시하는 방법

특정 나머지를 기준으로 셀을 계산하는 것 외에도 Excel의 MOD 함수를 사용하여 해당 셀을 강조 표시 할 수 있습니다. 이를 위해서는 수식 기반 조건부 서식 규칙을 만들어야 합니다. 

 

홀수와 짝수 강조 표시

홀수 또는 짝수가 포함된 셀을 강조 표시하려면 숫자를 2로 나눈 다음 Excel Mod 수식을 사용하여 각각 1과 0의 나머지를 확인합니다.

 

홀수로 셀 강조 표시:

=MOD(A2,2)=1

 

짝수로 셀 강조 표시:

=MOD(A2,2)=0

여기서 A2는 데이터가 있는 가장 왼쪽 셀입니다.

 

정수와 소수를 강조 표시

홀수 또는 짝수 숫자가 포함된 셀을 강조 표시하려면 숫자를 2로 나눈 다음 나머지 1과 0을 각각 확인하는 다음 Excel Mod 공식을 사용합니다:

=MOD(A2,1)=0

나머지가 0보다 크면 분수(소수점 포함)를 처리하는 것입니다.

=MOD(A2,1)>0

 

수식 기반 조건부 서식 규칙에서 셀 참조는 선택한 범위의 왼쪽 상단 셀을 기준으로 하므로 이 예에서는 A2를 참조합니다.

 

숫자의 배수 강조 표시

특정 값의 배수를 강조 표시하려면 숫자를 해당 값으로 나누고 나머지가 0인지 확인하기만 하면 됩니다.

예를 들어 10과 모든 배수가 포함된 셀을 강조 표시하려면 다음 수식을 사용합니다.

 

=MOD(A3,10)=0

여기서 A3은 규칙을 만드는 범위의 왼쪽 위 셀입니다(아래 스크린샷의 A3:C9).

 

또는 C1과 같은 빈 셀에 관심 있는 수를 입력하고 수식에서 해당 셀을 참조할 수 있습니다

=MOD(A3, $C$1)=0

 

데이터 유효성 검사가 포함된 Excel MOD

Excel MOD 기능의 또 다른 영리한 사용은 홀수, 짝수 또는 소수와 같은 선택한 셀에 특정 데이터를 입력하지 못하게 하는 것입니다. 이 작업은 셀을 강조 표시하는 데 사용한 것과 동일한 MOD 수식을 사용하여 수행할 수 있지만 이번에는 데이터 유효성 검사 규칙을 정의합니다.

 

수식을 기반으로 데이터 유효성 검사 규칙을 만들려면 데이터 > 데이터 유효성 검사를 클릭합니다. Settings(설정) 탭의 Allow(허용) 상자에서 Custom(사용자 지정)을 선택하고 Formula(수식) 상자에 수식을 입력합니다.

 

예를 들어 A2:A8 셀에 정수만 허용하려면 다음 셀을 선택하고 다음 수식을 사용하여 규칙을 구성합니다.

=MOD(A2,1)=0

 

비슷한 방식으로 데이터 입력을 짝수 또는 홀수로 제한할 수 있습니다.

홀수만 허용:

=MOD(A2,2)=1

 

짝수만 허용:

=MOD(A2,2)=0

여기서 A2는 선택한 범위의 맨 왼쪽 맨 위 셀입니다.