تشخیص صحت کد ملی در اکسل

ساختار کد ملی:

کد ملی شماره‌ای است ۱۰ رقمی که از سمت چپ سه رقم کد شهرستان محل صدور شناسنامه، شش رقم بعدی کد منحصربه‌فرد برای فرد دارنده شناسنامه در شهرستان محل صدور و رقم آخر آن هم یک رقم کنترل است که از روی ۹ رقم سمت چپ به‌دست می‌آید. برای راستی‌آزمایی کد کافی است از روی ۹ رقم سمت چپ رقم کنترل را محاسبه کرده و برابر بودن آن با آخرین رقم از سمت راست را بررسی کنیم.

اعتبار سنجی کد ملی:

ثبت احوال کشور در راستای ارائه کارت ملی روشی را برای تولید کدهای ملی استفاده نموده و رابطه‌ای بین رقم‌های کد ملی برقرار کرده است که برای بررسی کنترل این کد کافی است چند محاسبه ساده عملیاتی و منطقی را انجام دهید.

به منظور بررسی صحت کد ملی، هر یک از ارقام کد ملی دارای موقعیت یا ضریب خاص خود بوده که با اعداد ۲ تا ۱۰ نمایش داده می‌شود و به شرح زیر می‌باشد (به عنوان مثال برای کد ملی ۰۹۳۹۶۸۵۷۳۶):

برای بررسی این رابطه مراحل زیر را به ترتیب می‌توانید انجام دهید:

۱. در مرحله اول باید بررسی بر روی تعداد کاراکترها را انجام دهید، از آنجایی که در سیستم کد ملی معمولاً قبل از کد تعدادی صفر وجود دارد (رقم اول و رقم دوم از سمت چپ کد ملی ممکن است صفر باشد) و در بسیاری از موارد ممکن است کاربر این صفرها را وارد نکرده باشد و یا نرم افزار این صفرها را ذخیره نکرده باشد بهتر است قبل از هر کاری در صورتی که طول کد بزرگ‌تر مساوی ۸ و کمتر از ۱۰ بود به تعداد لازم (یک تا دو تا صفر) به سمت چپ عدد اضافه کنید.

۲. در مرحله دوم بعد از اطمینان از ۱۰ رقم بودن کد ملی، ضرایب ۲ الی ۱۰ را در ۹ رقم سمت چپ کد ملی نظیر به نظیر ضرب کرده و سپس تمامی اعداد را جمع می‌کنیم.

(2*3)+(3*7)+(4*5)+(5*8)+(6*6)+(7*9)+(8*3)+(9*9)+(10*0)=291

مجموع بدست آمده از مرحله دو را بر عدد ۱۱ تقسیم می‌کنیم و باقیمانده آن را محاسبه می‌کنیم. (۵ = ۱۱ / ۲۹۱)

باقیمانده تقسیم برابر ۵ می‌باشد.

حالت اول:

اگر باقیمانده کمتر از ۲ باشد، رقم کنترل ( رقم سمت راست کد ملی) باید برابر باقیمانده این تقسیم باشد. یعنی یا صفر و یا یک، که در این مثال ۵ می‌باشد و شامل حالت اول نمی‌گردد پس باید حالت دوم را مد نظر قرار داد.

حالت دوم:

اگر عدد باقیمانده تقسیم بزرگتر مساوی ۲ باشد عدد ۱۱ را منهای باقیمانده تقسیم مرحله قبل می‌کنیم، حال باید عدد حاصل شده برابر رقم کنترل(رقم سمت راست کد ملی) باشد.

برای این مثال: ۶ = ۵ – ۱۱ بوده پس باید رقم کنترلی کد ملی برابر ۶ باشد که نتیجه می‌گیریم کد ملی صحیح است.

در صورتی که یکی از این دو حالت را نداشته باشیم کد ملی یک کد صحیح نمی‌باشد.

نکته: لازم به ذکر است کدهای ملی که همه ارقام آنها مثل هم باشند معتبر نیستند.

بررسی اعتبار‌سنجی کد ملی در اکسل:

۱. روش اول: فرمول‌نویسی با استفاده از توابع در اکسل:

در روش اول می‌توان برای تک‌تک مراحل فوق جهت پیاده‌سازی و بررسی اعتبار سنجی کد ملی در نرم‌افزار اکسل تابع تعریف نمود تا صحت کد ملی بررسی گردد. جهت این کار برای مراحل زیر می‌توانید از فرمول‌های پیش‌فرض اکسل استفاده نمایید:

۱. شمارش تعداد کاراکترها یک سلول تابع LEN

۲. جدا کردن کاراکترها از یک رشته MID

۳. بررسی شرطی حالت‌های مختلف IF

۴. باقیمانده یک تقسیم MOD

نمونه محاسبات با کمک توابع در اکسل:

=IFERROR(IF(AND(LEN($G$7)=10,AND(LEFT($G$7,10)<>REPT(ROW(1:9),10)),OR(AND(MOD(SUM(MID($G$7,ROW(1:9),1)*(11-ROW(1:9))),11)<2,--RIGHT($G$7)=MOD(SUM(MID($G$7,ROW(1:9),1)*(11-ROW(1:9))),11)),--RIGHT($G$7)=(11-MOD(SUM(MID($G$7,ROW(1:9),1)*(11-ROW(1:9))),11)))),TRUE,FALSE),FALSE)

۲. روش دوم: کدنویسی در ویژوال بیسیک (VBA):

در روش دوم شما می‌توانید مراحل بالا را در قالب کدنویسی ویژوال بیسیک در محیط اکسل نیز انجام دهید.

نمونه محاسبات با کمک VBA:

Function ISMELLICODE(CodeMelli As String) As Boolean
' Code written by: Excel Iran Team - Amir Ghasemiyan
Dim first_number As Integer, num As Integer, counter As Integer, s As Integer, r As Integer, i As Integer
If IsNumeric(CodeMelli) And Len(CodeMelli) = 10 Then
    first_number = Left(CodeMelli, 1)
    For i = 1 To 9
        num = Mid(CodeMelli, i, 1)
        If num = first_number Then counter = counter + 1
        s = s + num * (11 - i)
    Next i
    r = s Mod 11
    If r > 1 Then r = 11 - r
    If r = Val(Right(CodeMelli, 1)) And counter < 9 Then ISMELLICODE = True
End If
End Function