메뉴
실무 위키›응용 공식›사업자등록번호 입력 오류 검사 공식

사업자등록번호 입력 오류 검사 공식

2021
지원 버전 자세히 보기
엑셀 2021엑셀 2024M365웹 엑셀

사업자등록번호의 검증 숫자를 계산해 입력 오류를 확인합니다.

예제파일 다운로드
사업자등록번호 입력 오류 검사 공식 예제 미리보기
예제 미리보기 크게 보기

인수 설명

=IFERROR ( LET ( n, SUBSTITUTE ( 사업자등록번호, "-", "" ), s, SUMPRODUCT ( --MID ( n, {1;2;3;4;5;6;7;8;9}, 1 ), 검증 가중치 ) + INT ( --MID ( n, 9, 1 ) * 5 / 10 ), IF ( AND ( LEN ( n ) = 10, --MID ( n, 10, 1 ) = MOD ( 10 - MOD ( s, 10 ), 10 ) ), "정상", "확인 필요" ) ), "확인 필요" )
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
사업자등록번호필수검사할 사업자등록번호가 적힌 셀입니다. 하이픈의 유무와 관계없이 사용할 수 있는 입력값입니다.
검증 가중치필수앞 9자리에 차례대로 곱할 1, 3, 7, 1, 3, 7, 1, 3, 5가 적힌 범위입니다. 순서와 값을 그대로 유지해야 하는 검증 기준입니다.
"정상"필수번호가 10자리이고 마지막 숫자가 계산한 검증 숫자와 같을 때 반환할 표시입니다.
"확인 필요"필수자릿수, 숫자 입력 또는 검증 숫자가 맞지 않을 때 반환할 표시입니다.
C2=IFERROR(LET(n,SUBSTITUTE(B2,"-",""),s,SUMPRODUCT(--MID(n,{1;2;3;4;5;6;7;8;9},1),$F$2:$F$10)+INT(--MID(n,9,1)*5/10),IF(AND(LEN(n)=10,--MID(n,10,1)=MOD(10-MOD(s,10),10)),"정상","확인 필요")),"확인 필요")
A
B
C
D
E
F
G
1
거래처
사업자등록번호
검사 결과
자리
가중치
2
가온상사
123-45-67891
정상
1
1
3
새롬물산
220-81-62517
정상
2
3
4
한빛유통
101-23-45672
정상
3
7
5
미래사무
312-34-56780
확인 필요
4
1
6
다온테크
123-45-6789
확인 필요
5
3
7
푸른기획
12A-45-67891
확인 필요
6
7
8
7
1
9
8
3
10
9
5
11
검증 숫자는 앞 9자리와 가중치로 계산합니다. 하이픈의 위치와 실제 등록 여부는 확인하지 않으므로 '정상' 결과는 번호 체계의 검증 숫자가 맞다는 뜻입니다.
이 공식이 사용하는 함수
IFERROR 함수 LET 함수 SUBSTITUTE 함수 SUMPRODUCT 함수 MID 함수 INT 함수 IF 함수 AND 함수 LEN 함수 MOD 함수

동작 원리

SUBSTITUTE 함수로 하이픈을 없애고 SUMPRODUCT 함수로 앞 9자리의 가중합을 구한 뒤, 마지막 숫자와 계산한 검증 숫자가 같으면 정상으로 표시합니다.

예제 시트에서 가온상사의 사업자등록번호를 검사하는 C2 셀을 추적합니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

하이픈을 없애 10자리 숫자로 정리합니다

가온상사의 번호에서 하이픈을 제거하면 검증에 사용할 1234567891이 남습니다.

=SUBSTITUTE ( B2, "-", "" )
= 1234567891
2

앞 9자리에 검증 가중치를 곱합니다

첫째 자리부터 아홉째 자리까지 정해진 가중치를 곱하면 1, 6, 21, 4, 15, 42, 7, 24, 45가 됩니다.

=--MID ( "1234567891", {1;2;3;4;5;6;7;8;9}, 1 ) * $F$2:$F$10
= {1;6;21;4;15;42;7;24;45}
3

가중합과 아홉째 자리 보정값을 더합니다

아홉 개의 곱을 더한 값은 165입니다. 아홉째 자리의 곱 45를 10으로 나눈 몫 4를 더하면 검증에 사용할 합계는 169가 됩니다.

=SUMPRODUCT ( --MID ( "1234567891", {1;2;3;4;5;6;7;8;9}, 1 ), $F$2:$F$10 ) + INT ( 9 * 5 / 10 )
= 169
4

계산한 검증 숫자와 마지막 숫자를 비교합니다

169의 일의 자리를 10에서 빼고 다시 10으로 나눈 나머지는 1입니다. 번호의 마지막 숫자도 1이므로 정상으로 표시합니다.

=IF ( AND ( LEN ( "1234567891" ) = 10, 1 = MOD ( 10 - MOD ( 169, 10 ), 10 ) ), "정상", "확인 필요" )
= 정상
댓글 0
아직 댓글이 없습니다. 첫 번째 댓글을 작성해보세요!
스크랩 완료