به عنوان فردی که سالها در حوزه آموزش ICDL و کار با نرمافزارهای اداری تجربه دارد ، بارها با این چالش مواجه شدهام: لیستی بلندبالا از کدهای ملی در اکسل که باید از صحت آنها مطمئن شویم. ورود دستی اطلاعات همیشه با خطا همراه است و داشتن کدهای ملی نامعتبر در یک پایگاه داده، میتواند مشکلات زیادی ایجاد کند.
خبر خوب این است که اکسل ابزارهای قدرتمندی برای حل این مشکل دارد. کد ملی در ایران یک الگوریتم ریاضی مشخص دارد و ما میتوانیم با استفاده از توابع اکسل، این الگوریتم را پیادهسازی کنیم تا در کسری از ثانیه، هزاران کد را بررسی کنیم.
در این مقاله، به شما نشان خواهم داد که چگونه یک فرمول دقیق برای اعتبارسنجی کد ملی در اکسل بنویسید.
الگوریتم اعتبارسنجی کد ملی چگونه کار میکند؟
قبل از پریدن به داخل اکسل، باید منطق کار را درک کنیم. کد ملی یک عدد ۱۰ رقمی است که ۹ رقم اول آن، شماره شناسایی فرد و رقم دهم، رقم کنترلی (Check Digit) است.
این رقم کنترلی بر اساس ۹ رقم اول محاسبه میشود. اگر محاسبات ما روی ۹ رقم اول با رقم دهم همخوانی داشت، کد ملی معتبر است.
الگوریتم رسمی به این صورت است (بر اساس منابع معتبر برنامهنویسی و محاسباتی):
- ۹ رقم اول (از چپ به راست) را در نظر بگیرید.
- رقم اول را در ۱۰، رقم دوم را در ۹، رقم سوم را در ۸، ... و رقم نهم را در ۲ ضرب کنید.
- حاصلضربهای به دست آمده را با هم جمع کنید.
- حاصل جمع را بر ۱۱ تقسیم کرده و باقیمانده را به دست آورید.
- قانون اصلی:
- اگر باقیمانده کمتر از ۲ بود، خود باقیمانده باید با رقم کنترلی (رقم دهم) برابر باشد.
- اگر باقیمانده مساوی یا بیشتر از ۲ بود، باید عدد ۱۱ را منهای باقیمانده کنیم. عدد به دست آمده باید با رقم کنترلی برابر باشد.
حالا که منطق را میدانیم، بیایید آن را به زبان اکسل ترجمه کنیم.
فرمول نهایی اعتبارسنجی کد ملی در اکسل
فرض کنید کد ملی مورد نظر در سلول A2 قرار دارد. فرمول زیر به شما TRUE (برای معتبر) یا FALSE (برای نامعتبر) را برمیگرداند.
نکته مهم: قبل از استفاده از این فرمول، مطمئن شوید که ستون حاوی کدهای ملی به صورت Text فرمتبندی شده است تا صفرهای احتمالی اول کد حذف نشوند.
=(LEN(A2)=10) * (--RIGHT(A2,1) = IF(MOD(SUMPRODUCT(--MID(A2,ROW(INDIRECT("1:9")),1),{10,9,8,7,6,5,4,3,2}),11)<2, MOD(SUMPRODUCT(--MID(A2,ROW(INDIRECT("1:9")),1),{10,9,8,7,6,5,4,3,2}),11), 11-MOD(SUMPRODUCT(--MID(A2,ROW(INDIRECT("1:9")),1),{10,9,8,7,6,5,4,3,2}),11)))
این فرمول کمی طولانی و پیچیده به نظر میرسد، اما در بخش بعد آن را کاملاً تشریح خواهیم کرد.
کالبدشکافی فرمول: هر بخش چه کاری انجام میدهد؟
بیایید فرمول را به بخشهای کوچکتر تقسیم کنیم تا درک آن سادهتر شود.
۱. جدا کردن ۹ رقم اول
--MID(A2, ROW(INDIRECT("1:9")), 1)
ROW(INDIRECT("1:9"))یک آرایه از اعداد ۱ تا ۹ ایجاد میکند:{1;2;3;4;5;6;7;8;9}.MID(A2, ..., 1)به اکسل میگوید که از سلول A2، به ترتیب از موقعیت ۱، ۱ کاراکتر، از موقعیت ۲، ۱ کاراکتر و... تا موقعیت ۹ بردار.--(دو خط تیره) یک ترفند اکسلی است که متنهای جدا شده (خروجیMIDهمیشه متن است) را به عدد تبدیل میکند.
۲. محاسبه مجموع وزندار
SUMPRODUCT(..., {10,9,8,7,6,5,4,3,2})
SUMPRODUCTدو کار همزمان انجام میدهد:- آرایه ۹ رقمی ما را (
{رقم۱, رقم۲, ...}) در آرایه وزنها ({10,9,8,...}) ضرب میکند (رقم اول در ۱۰، دوم در ۹، و...). - سپس همه نتایج را با هم جمع میکند.
- آرایه ۹ رقمی ما را (
- این دقیقاً همان چیزی است که مراحل ۲ و ۳ الگوریتم از ما میخواست. بیایید این حاصل جمع را
Sبنامیم.
۳. محاسبه باقیمانده و اعمال قانون
IF(MOD(S, 11) < 2, MOD(S, 11), 11 - MOD(S, 11))
MOD(S, 11)باقیمانده تقسیمSبر ۱۱ را محاسبه میکند (مرحله ۴ الگوریتم).- تابع
IFقانون مرحله ۵ را پیاده میکند:- اگر باقیمانده (
MOD) کمتر از ۲ بود، خود باقیمانده را برگردان. - در غیر این صورت (یعنی اگر ۲ یا بیشتر بود)،
11 - باقیماندهرا برگردان.
- اگر باقیمانده (
- خروجی این بخش، رقم کنترلی محاسبه شده ما است.
۴. مقایسه نهایی
(--RIGHT(A2,1) = ...)
--RIGHT(A2,1)رقم دهم (رقم کنترلی واقعی) را از سلول A2 جدا کرده و با--آن را به عدد تبدیل میکند.- سپس بررسی میکند که آیا این رقم، با رقم کنترلی محاسبه شده ما (از بخش ۳) برابر است یا خیر.
۵. بررسی طول کد
(LEN(A2)=10) * ...
- در نهایت،
(LEN(A2)=10)بررسی میکند که آیا کد ملی اصلاً ۱۰ رقمی است یا خیر. - در اکسل،
TRUEمعادل1وFALSEمعادل0است. - با ضرب کردن این بخش در نتیجه اصلی، اگر طول کد ۱۰ نباشد (FALSE یا 0)، کل نتیجه
0(یا FALSE) خواهد شد، که تضمین میکند کدهای کوتاهتر یا بلندتر به اشتباه معتبر شناخته نشوند.
نسخه مدرن: استفاده از تابع LET برای خوانایی بهتر
اگر از Excel 2021 یا Microsoft 365 استفاده میکنید، میتوانید با تابع LET فرمول را بسیار خواناتر بنویسید. این تابع به شما اجازه میدهد تا برای بخشهای محاسباتی خود نام انتخاب کنید.
=LET(
code, A2,
digits, --MID(code, ROW(INDIRECT("1:9")), 1),
weights, {10,9,8,7,6,5,4,3,2},
s, SUMPRODUCT(digits, weights),
r, MOD(s, 11),
calc_check, IF(r < 2, r, 11 - r),
actual_check, --RIGHT(code, 1),
AND(LEN(code) = 10, calc_check = actual_check)
)
این فرمول دقیقاً همان کار را انجام میدهد، اما درک و خطایابی آن بسیار سادهتر است.
جمع بندی
تبریک میگویم! شما اکنون یک ابزار قدرتمند در اکسل ساختهاید. با استفاده از این فرمول، دیگر نگران دادههای نامعتبر کد ملی نخواهید بود. به عنوان یک مدرس ICDL ، همیشه تاکید میکنم که قدرت واقعی اکسل در توانایی آن برای حل مسائل دنیای واقعی، مانند همین اعتبارسنجی دادهها، نهفته است.
نظرات کاربران (0)