چگونه از درست وارد شدن کد ملی در اکسل مطمئن شویم؟و صحت کدملی وارد شده را تشخیص دهیم؟ آیا صرفا بررسی کردن 10 رقم بودن طول آن کافیست؟ این اموزش تا حدودی شباهت به بررسی شماره کارت بانکی در اکسل دارد.
فهرست محتوا
بررسی صحت کد ملی در اکسل بدون کد نویسی
در این روش از ترکیب توابع استفاده خواهیم کرد و نیازی به کد نویسی نخواهیم داشت و فقط کافیست که فرمول زیر را در فایل اکسل خود قرار دهید و مراحل را طی نمائید.
- فرمول نوشته شده در باکس زیر را کپی کنید .
- وارد فایل اکسل خود شده و در سلول مورد نظر فرمول را قرار دهید.
- داخل فرمول 2$A$ را به آدرس سلولی که کد ملی را در آن وارد کرده اید تغییر دهید.
- برای خارج شدن از سلول حتما CTRL + Shift + Enter را بزنید .
نکته مهم : فرمول زیر آرایه ای بوده و حتما باید بعد از تغییر کلید کنترل را نگاه داشته و کلید شیفت و اینتر را همزمان بزنید.
البته اگر از آفیس 2021 استفاده می کنید و یا آفیس 365 نیازی به این کار ندارید .
=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)
البته اگر از آفیس 2021 استفاده کنید می توانید از تابع SEQUENCE به جای تابع Row استفاده نمائید که نتیجه تشخیص صحت کد ملی در اکسل مانند تصویر زیر خواهد شد.
استفاده از کدنویسی برای تشخیص درستی کد ملی
در این روش نیاز است که فایل خود را به صورت ماکرو اینیبل و با پسوند XLSM ذخیره نمائید و اگر در کامپیوتر دیگر مورد استفاده قرار گیرد باید حتما فایل خود را ماکرو اینیبل کند.
Function ISMELLICODE(CodeMelli As String) As Boolean
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برای استفاده از کدهای VBA مراحل زیر را دنبال کنید .
- ترجیحا فقط همان فایل اکسلی که میخواهید کد ملی را چک کنید باز باشد.
- کدهای بالا را کپی کنید.
- با زدن کلیدهای Alt + F11 وارد محیط کدنویسی شوید.
- در فضای خالی سمت چپ مانند فیلم کلیک راست کرده و یک ماژول ایجاد نمائید.
- کدهای کپی شده را در آنجا قرار دهید.
- محیط کدنویسی را ببنیدید.
- در فایل اکسل خود در سلول مد نظر از فرمول زیر استفاده نمائید.
=ISMELLICODE(A2)
نکته : به جای A2 در فرمول بالا آدرس سلولی که کد ملی را در آن وارد کرده اید قرار دهید.
ماجرا پیچیده تر از تابع LEN است !!
ابتدای مقاله گفتم که با تابع LEN می توانیم طول کد ملی را تشخیص دهیم و مرحله اول بررسی صحت کد ملی را انجام دهیم . اما واقعیت این است که شرایط دیگری هم هست که کار را پیچیده تر کرده و ما را مجبور به استفاده از توابع بالا و یا کد نویسی می کند.
- احتمال دارد که کد ملی با 0 و یا 00 شروع شده باشد که اکسل آن را حذف کرده.
- ممکن است ده رقم وارد شده باشد اما صحیح نباشد. مانند 1111111111
- اصولی بر بررسی صحت کد ملی وجود دارد که توسط ثبت احوال لحاظ شده که باید بررسی شود
آپدیت ، بررسی صحیح کد ملی
همانطور که در تصویر می بینید با استفاده از تابع TEXT حالت نمایش کد ملی را اصلاح کرده ایم و همچنین با استفاده از کاندیشنال فرمتینگ Conditional Formatting توانستیم برای کد ملی ها حاشیه ایجاد کرده و رنگ ردیف را تغییر دهیم.
شما از کدام روش استفاده می کنید
پاسخ به این پرسش کاملا بستگی به فایل اکسلی دارد که میخواهیم در آن صحت کدملی را بررسی کنیم ، اگر فایل من بنا به دلایل دیگر مانند الزام به استفاده از توابع تاریخ شمسی اکسل با پسوند xlsm بود از کدها و تابع اختصاصی استفاده میکنم .
اما اگر داده ها کم باشد ، مسلما از روش ترکیب توابع بهره می برم.
استفاده از تابع کد ملی در اکسل فایل را کند نمی کند؟
خود من برای پروژه ای استفاده کردم که بیش 12 هزار کد ملی باید بررسی میشد و افت سرعت مشاهده نکردم.
آیا باید حتما از آفیس 2021 استفاده کنیم ؟
همانطور که در آموزش دیدید خیر لزومی به داشتن آفیس 2021 نیست و شما می توانید از ورژنهای پائین تر هم بهره ببرید
اگر تعداد کد ملی کم بود ، پیشنهاد می کنیم از روش ترکیب توابع در اکسل استفاده نمائید و در غیر اینصورت از کد نویسی استفاده کنید و تابع شخصی را به اکسل اضافه کنید ، نتیجه در هر دو روش همانطور که در تصویر و ویدیو هم دیدید یکسان است و تنها د ر سرعت عملکرد متفاوت هستند .
اگر نظر یا پیشنهاد و انتقادی در خصوص نحوه تشخیص صحت کد ملی وارد شده در اکسل دارید حتما در قسمت نظرات با ما در میان بگذارید.
روشهایی که در آفیس باز معرفی کردیم مزیتهایی نسبت به سایر سایتها دارد که یکی از آنها بررسی کردن اعداد منفی و بررسی تعداد کاراکترها و در کل سعی شده است تمام اشتباهاتی که احتمال دارد کاربر انجام دهد را پوشش دهیم.
