Excel 사례 공유: 함수 수식을 사용하여 ID 번호의 진위 여부를 확인합니다.
- 青灯夜游앞으로
- 2022-09-13 10:15:5312419검색
이전 글 "Excel 사례 공유: "드래그 앤 드롭"만으로 구현 가능한 5가지 효율적인 기술"에서 Excel 표에서 "드래그 앤 드롭"만으로 구현 가능한 5가지 효율적인 기술을 배웠습니다. 오늘은 엑셀의 주민등록번호 진위여부 확인 기능에 대해 이야기해보겠습니다. 오셔서 살펴보세요!
가짜백신, 유독분유 같은 끔찍한 일들을 이야기하다니 정말 화가 나고 속수무책입니다. 일반인으로서 관련 부서에서 예방을 위한 관리와 점검 노력을 강화해 주기를 바랄 뿐입니다. 이런 악랄한 사건이 다시는 일어나지 않도록요. 동시에 작업에 더욱 주의를 기울여야 하며 오류가 발생하기 쉬운 여러 링크를 반복적으로 확인하여 부정적인 결과를 피해야 합니다. 예를 들어 직원 파일의 ID 번호는 문제가 발생하기 매우 쉬운 재난 지역입니다.
오늘은 주민등록번호에 오류가 있는지 확인하는 공식을 알려드리겠습니다.
일반적으로 ID 번호 오류에는 길이 오류와 내용 오류 두 가지 유형이 있습니다.
오류가 발생하는 원인은 대부분 입력 과정에서 발생합니다. 길이 오류의 경우 현재는 모두 18자리 숫자로 길이에 오류가 있으면 찾기 쉽지만, 하나에 오류가 있으면 더 쉽게 찾을 수 있습니다. 숫자의 경우 찾기가 매우 어렵고 향후 심각한 결과를 초래할 수 있으므로 오늘은 내용의 정확성을 확인하는 데 중점을 둡니다.
1. ID 번호 구성 원리
우선 ID 번호의 인코딩 규칙에 대해 조금 이해해야 합니다.
18자리 ID 번호 구성: dddddddyyyymmddxxsp 총 18자리 중 1~6자리 지역번호, 7~14는 생년월일, 15~17 세자리는 성별코드, 마지막 숫자는 획득한 체크숫자 앞의 17자리 숫자를 기준으로 합니다.
체크 숫자에 대한 계산 규칙은 더 복잡합니다.
(1) 첫 번째 17자리 온톨로지 코드의 가중 합산 공식
S = Sum(Ai * Wi), i = 0, ... , 16 , 먼저 처음 17자리의 가중치를 합산합니다
Ai : i번째 위치의 신분증 번호의 디지털 값을 나타냅니다.
Wi: i번째 위치의 가중치를 나타냅니다
Wi: 7 9 10 5 8 4 2 1 6 3 7 9 10 5 8 4 2
(2) 모듈러스를 계산합니다
Y = mod(S, 11)
(3) 해당 체크 코드를 얻습니다
Y 모듈로: 0 1 2 3 4 5 6 7 8 9 10
코드 확인: 1 0 숫자가 일치하지 않으면 문제가 있는 것입니다. 이 규칙을 이해하는 것은 매우 어렵습니다. 예를 들어보겠습니다.
1. ID 번호의 앞 17자리에 다른 계수를 곱합니다. 첫 번째부터 17번째까지의 계수는
7-9-10-5-8-4-2-1-6-3-7-9-10-5-8-4-2입니다.
2. 이 17자리 숫자와 계수를 곱한 결과를 더하세요.
3. 합을 11로 나누면 나머지는 얼마인가요?
4. 나머지는 11개의 숫자만 가질 수 있습니다: 0-1-2-3-4-5-6-7-8-9-10. 해당하는 마지막 ID 번호는 1-0-X-9-8-7-6-5-4-3-2입니다.
5. 인증번호 비교를 통해 나머지가 3이면 신분증 18자리가 9로 나타나는 것을 알 수 있습니다. 해당 숫자가 10인 경우 신분증의 마지막 숫자는 로마숫자 2입니다.
예를 들어 남자의 신분증 번호가 [53010219200508011x]라면, 이 신분증이 합법적인 신분증인지 확인해 보겠습니다.
먼저 처음 17자리의 곱의 합을 구합니다
【(5*7)+(3*9)+(0*10)+(1*5)+(0*8)+ (2*4)+(1*2)+(9*1)+(2*6)+(0*3)+(0*7)+(5*9)+(0*10)+(8 *5)+ (0*8)+(1*4)+(1*2)】은 189이고, 189를 11로 나누어 결과는 189/11=17----2입니다. 즉, 남은 숫자는 2입니다. 마지막으로 해당 규칙을 통해 나머지 2에 해당하는 체크코드가 X임을 알 수 있어 이것이 정확한 ID번호임을 확인할 수 있다.
2. Excel을 사용하여 ID 카드 번호의 진위 여부를 확인하세요원칙은 분명합니다. 이제 문제는 이 계산 과정을 Excel 수식을 사용하여 표현하는 방법입니다.
=IF(RIGHT(A2)=MID("10X98765432",MOD(SUM(MID(A2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),"真","假")
공식을 사용할 때 두 개의 A2를 양식의 ID 번호에 해당하는 위치로 수정하기만 하면 됩니다.
공식의 아이디어에 대해 간단히 이야기해 보겠습니다. 핵심 부분은
입니다.MID("10X98765432",MOD(SUM(MID(A2,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1)
이 문자열의 기능은 검사 코드의 규칙에 따라 이전 17자리를 계산한 다음 RIGHT(A2)와 비교하는 것입니다. RIGHT(A2)의 기능은 A2에서 가장 오른쪽의 숫자를 추출하는 것입니다. 나와서 if 함수를 사용하여 비교 결과를 바탕으로 판단하세요.
이 공식을 다시 사용하면 기사 시작 부분에 있는 사진의 ID 번호가 올바른지 확인할 수 있습니다.
결과는 거짓입니다.
이 공식을 사용하여 양식에 잘못된 ID 번호가 있는지 확인할 수도 있습니다. 잘못된 것으로 확인된 번호를 적시에 수정하여 문제를 조기에 해결할 수도 있습니다.
관련 학습 권장 사항: excel 튜토리얼
위 내용은 Excel 사례 공유: 함수 수식을 사용하여 ID 번호의 진위 여부를 확인합니다.의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!