بررسی درست بودن کد ملی در اکسل و الگوریتم آن

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

با استفاده از الگوریتم ماژول ۱۰، صحت کد ملی با بررسی روابط میان رقم‌ها و تعیین رقم کنترلی آن بررسی می‌شود. برای این منظور، رقم کنترلی با استفاده از یک الگوریتم ساده محاسبه می‌شود و سپس با رقم کنترلی موجود در کد ملی مقایسه می‌شود. در صورت تطابق، کد ملی به عنوان صحیح شناخته می‌شود و در غیر این صورت خطا نمایش داده می‌شود.

از آنجایی که تشخیص صحت کد ملی بسیار حساس است، برنامه نویسان باید با دقت و دانش کافی از الگوریتم‌های مختلف استفاده کنند تا اطلاعات کاربران را در برابر خطاهای محتمل حفظ کنند. همچنین، تأیید صحت کد ملی نباید تنها معیار بررسی صحت اطلاعات کاربر باشد و سایر اطلاعات مانند شماره تلفن و آدرس نیز باید به دقت بررسی شوند.

الگوریتم تشخیص صحت کد ملی ایران

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

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

برای محاسبه رقم کنترل کد ملی به روش زیر عمل می‌شود:

1. هر یک از رقم‌های کد ملی را در موقعیت خود ضرب کرده و حاصل را با هم جمع می‌کنیم.

2. مجموع حاصل شده را بر ۱۱ تقسیم می‌کنیم.

3. اگر باقیمانده کمتر از ۲ باشد، رقم کنترل برابر باقیمانده خواهد بود، در غیر اینصورت، رقم کنترل برابر یازده منهای باقیمانده خواهد بود.

مثال : آیا کد ۷۷۳۱۶۸۹۹۵۱ یک کد ملی معتبر است؟

برای این منظور کد

حاصل جمع ضرب ارقام ۲ الی ۱۰ را در موقعیت آنها محاسبه می کنیم

۷*۱۰+۷*۹+۳*۸+۱*۷+۶*۶+۸*۵+۹*۴+۹*۳+۵*۲=۳۱۳

۳۱۳÷۱۱=۲۸ و R=5

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

این مطلب را هم بخوانید: بررسی صحت شماره کارت بانکی در اکسل

بررسی صحت کد ملی در اکسل بدون کد نویسی

برای استفاده از این روش، بهتر است مراحل زیر را دنبال کنید:

فرمول زیر را در فایل اکسل خود کپی کنید:

1. فرمول زیر را در فایل اکسل خود کپی کنید:
2. در سلول مورد نظر، فرمول را قرار دهید و در محتویات سلول A2، آدرس سلولی که کد ملی را وارد کرده اید را وارد کنید.
3.  قبل از فرمول، "مساوی" را قرار داده و سپس CTRL + Shift + Enter را بزنید تا فرمول به عنوان یک فرمول آرایه اجرا شود.
4. اکنون باید پیغام "کد ملی صحیح است" یا "کد ملی نامعتبر است" در سلول مورد نظر را مشاهده کنید.

لازم به ذکر است که در این روش، از تابع IF برای بررسی صحت کد ملی استفاده شده است و اگر باقیمانده محاسبه شده با کد ملی برابر باشد، پیغام "کد ملی صحیح است" چاپ می شود و در غیر این صورت، پیغام "کد ملی نامعتبر است" نمایش داده می شود.

نکته مهم : فرمول زیر آرایه ای بوده و حتما باید بعد از تغییر کلید کنترل را نگاه داشته و کلید شیفت و اینتر را همزمان بزنید.

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

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

استفاده از کدنویسی برای تشخیص درستی کد ملی

برای استفاده از این روش، لازم است فایل خود را به صورت ماکرو اینیبل و با پسوند XLSM ذخیره کنید. همچنین، در صورت استفاده از فایل در کامپیوتر دیگری، باید حتماً فایل را ماکرو اینیبل کنید.

برای استفاده از کدهای VBA مراحل زیر را دنبال کنید

ترجیحاً، فایل اکسل مورد نظر را باز کرده و سپس کدهای زیر را کپی کنید. سپس با فشردن کلیدهای Alt + F11 وارد محیط کدنویسی شوید و یک ماژول جدید ایجاد کنید. سپس کدها را در ماژول جدید قرار دهید. در نهایت، محیط کدنویسی را ببندید.

توصیه می‌کنیم که فایل اکسل خود را قبل از استفاده از کدها، در یک نسخه پشتیبان ذخیره کنید تا در صورت بروز مشکل، بتوانید به فایل اصلی خود بازگردید.

کدها:

```
Function CheckNationalCode(ByVal NationalCode As String) As Boolean
    Dim L As Integer
    Dim sum As Integer
    Dim i As Integer
    Dim j As Integer

    If Len(NationalCode) <> 10 Then
        CheckNationalCode = False
        Exit Function
    End If
    
    If Not IsNumeric(NationalCode) Then
        CheckNationalCode = False
        Exit Function
    End If
    
    sum = 0
    
    For i = 1 To 9
        sum = sum + Val(Mid(NationalCode, i, 1)) * (10 - i)
    Next i
    
    L = Val(Mid(NationalCode, 10, 1))
    If ((sum + L) Mod 11) = 0 Then
        CheckNationalCode = True
    ElseIf ((sum + L) Mod 11) = 1 And L = 1 And Mid(NationalCode, 2, 3) <> "000" Then
        CheckNationalCode = True
    ElseIf ((sum + L) Mod 11) > 1 And ((sum + L) Mod 11) = (11 - L) Then
        CheckNationalCode = True
    Else
        CheckNationalCode = False
    End If

End Function
```

بعد از قرار دادن کد، می‌توانید به صورت زیر از آن استفاده کنید:

```
Sub Main()
    Dim NationalCode As String
    NationalCode = Range("A2").Value
    
    If CheckNationalCode(NationalCode) Then
        MsgBox "کد ملی صحیح است."
    Else
        MsgBox "کد ملی نامعتبر است."
    End If
    
End Sub
```

در این کد، مقدار ورودی به عنوان کد ملی در سلول A2 در نظر گرفته شده است. شما می‌توانید آدرس سلول مورد نظر را در کد تغییر دهید.

با توجه به نکته ارائه شده، در فایل اکسل خود در سلول مورد نظر، از فرمول زیر استفاده نمایید:

=ISMELLICODE(A2)

با قرار دادن این فرمول در سلول مورد نظر، کد ملی وارد شده در سلول A2 بررسی و بررسی می‌شود که آیا این کد ملی به فرمت درستی وارد شده است یا خیر. با توجه به خروجی این فرمول، می‌توانید متوجه شوید که آیا کد ملی وارد شده صحیح است یا خیر.

ماجرا پیچیده تر از تابع LEN است !!

با تابع LEN می توان طول کد ملی را تشخیص داد، اما شرایط دیگری نیز وجود دارد که ممکن است پیچیدگی‌هایی ایجاد کنند و ما را مجبور به استفاده از توابع بالا و یا کد نویسی کنند.

به عنوان مثال، احتمال وجود صفر یا صفر صفر در ابتدای کد ملی وجود دارد که ممکن است اکسل آن را حذف کند. همچنین، اگر ده رقم کد ملی وارد شده باشد، این به معنای آن نیست که صحت آن تضمین شده باشد، به عنوان مثال اگر یک کد ملی با ۱۱۱۱۱۱۱۱۱۱۱ وارد شود، طول آن ده رقم است اما درست نیست. اصولی برای بررسی صحت کد ملی وجود دارد که توسط سازمان ثبت احوال لحاظ شده است که باید آن را بررسی کرد.