آیا شماره کارت 16 رقمی در اکسل درست وارد شده است ؟ آیا میتوان بررسی کرد که شماره کارت در اکسل قابل بررسی هست یا خیر؟ در این آموزش منظورمان شماره کارت بانکی در اکسل می باشد، که میتوان با استفاده از الگوریتم حاکم بر آن به آسانی کنترل کنیم . کارت بانکی هم مانند کارت ملی از الگوریتم خاصی ایجاد شده است.
- 6 رقم اول که نشان دهنده بانک صادر کننده می باشد و BIN نامیده می شود.
- 2 رقم بعدی مشخص می کند که کارت از چه نوعی می باشد ، هدیه و…
- 7 رقم بعید یعنی از 9 تا 15 یک شناسه منحصر به فرد برای هر کارت می باشد
- رقم آخر هم کنترل کننده است که در ادامه خواهید دید چگونه به تشخیص صحت کارت بانکی کمک میکند.
فهرست محتوا
آموزش درست بودن شماره کارت در اکسل
الگوریتم موجود در شماره کارت بانکی در اکسل
اگر قبل از اینکه بخواهیم تراکنش انجام دهیم بتوانیم صحت شماره کارت بانکی وارد شده را تشخیص دهیم بسیار مفید خواهد بود ، خب برای اینکه بفهمیم کارت وارد شده در سیستم بانکی وجود دارد یا نه باید از الگروریتمی که در ادامه وجود دارد بهره ببریم.
- ابتدا باید اعدادی که موقعیت مکانی فرد دارند ( عدد اول و سوم و … تا پانزدهم ) را تک به تک در 2 ضرب کنیم.
- اگر حاصل ضرب بزرگتر از عدد 9 شد باید عدد 9 را از آن کم کنیم .
- حاصل محاسبات فوق را با اعدادی که در موقیعت زوج قرار دارند ( عدد دوم و چهارم و ششم و…تا شانزدهم ) جمع کنیم .
- اگر عدد به دست آمده قابل تقسیم به 10 بود و باقیمانده نداشت ، کارت وارد شده صحیح است.
مثال بررسی شماره کارت 16 رقمی در اکسل
شماره بانکی 5022291090214428 را می خواهیم بررسی کنیم .
اعدادی که در موقعیت زوج هستند در عدد 2 ضرب می کنیم
5*2=10 -9 =1
2*2=4
2*2=4
1*2=2
9*2=18-9 =9
2*2=4
4*2=8
2*2=4
1+4+4+2+9+4+8+4=36
اعداد موقیع زوج را هم با هم جمع می کنم
0+2+9+0+0+1+4+8=24
حالا اعداد بدست آمده را با هم جمع می کنیم و حاصل عدد 60 می شود که مضربی از 10 هست و صحیح می باشد.
فرمول استفاده شده در اکسل
=IF(MOD(IF(MID($D$2,1,1)*2>9,MID($D$2,1,1)*2-9,MID($D$2,1,1)*2)+MID($D$2,2,1)+IF(MID($D$2,3,1)*2>9,MID($D$2,3,1)*2-9,MID($D$2,3,1)*2)+MID($D$2,4,1)+IF(MID($D$2,5,1)*2>9,MID($D$2,5,1)*2-9,MID($D$2,5,1)*2)+MID($D$2,6,1)+IF(MID($D$2,7,1)*2>9,MID($D$2,7,1)*2-9,MID($D$2,7,1)*2)+MID($D$2,8,1)+IF(MID($D$2,9,1)*2>9,MID($D$2,9,1)*2-9,MID($D$2,9,1)*2)+MID($D$2,10,1)+IF(MID($D$2,11,1)*2>9,MID($D$2,11,1)*2-9,MID($D$2,11,1)*2)+MID($D$2,12,1)+IF(MID($D$2,13,1)*2>9,MID($D$2,13,1)*2-9,MID($D$2,13,1)*2)+MID($D$2,14,1)+IF(MID($D$2,15,1)*2>9,MID($D$2,15,1)*2-9,MID($D$2,15,1)*2)+MID($D$2,16,1),10)=0,VLOOKUP(--LEFT($D$2,6),A1:B100,2,0),"NOT OK")
فرض می کنیم مانند فایلی که پیوست این پست می باشد و می توانید دانلود کنید ، شماره کارت 16 رقمی را در سلول D2 وارد کرده اید ، و 6 رقم اول به همراه نام بانک هم در A1:A100 می باشد ، پس باید در E2 فرمول زیر را بنویسید.
با این کار اگر شماره درست باشد نام بانک و در غیر اینصورت NOT OK نمایش داده می شود.
لطفا اگر شما راه بهتر و یا فرمول کوتاه تری می توانید بنویسید در نظرات یا پیج اینستاگرام آفیس باز درج کنید ، حتما استقبال می شود.
استفاده از آرایه ها و فرمول کوتاه تر
خب همان طور که می دانید چالشهایی را در پیج اینستاگرام آفیس باز منتشر می کنیم که دوستان لطف کرده و راه حل های مناسب تری پیشنهاد می دهند و در این خصوص هم آقای رضا حسینیان نائینی فرمولی را ارائه کردند که در ادامه می بینید .
روش استفاده از فرمول آرایه ای
- کدهای زیر را کپی کرده و در فایل اکسل خود قرار دهید .
- در این مثال شماره کارت در D2 لحاظ شده و اگر برای شما متفاوت بود باید در فرمول تغییر دهید.
- چون در فرمول از آرایه استفاده شده باید کلیدهای Ctrl+Shift+Enter را وقتی درون سلول هستید بزنید.
=IF(MOD(SUM(IFERROR(IF((MID($D4,IF(ISODD(ROW(INDIRECT("1:16"))),ROW(INDIRECT("1:16"))," "),1)*2)>9,MID($D4,IF(ISODD(ROW(INDIRECT("1:16"))),ROW(INDIRECT("1:16")),""),1)*2-9,MID($D4,IF(ISODD(ROW(INDIRECT("1:16"))),ROW(INDIRECT("1:16"))," "),1)*2),"0"))+SUM(IFERROR(MID($D4,IF(ISEVEN(ROW(INDIRECT("1:16"))),ROW(INDIRECT("1:16"))," "),1)*1,"0")),10)=0,INDEX(B:B,MATCH(--LEFT($D4,6),A:A,0)),"NOT OK")رنگی کردن شماره کارتهای اشتباه
- ستونی که در آن شماره کارت ملی وارد شده را انتخاب کنید
- به کاندیشنال فرمتینگ Conditional Formatting بروید.
- روی Manage Rules کلیک کرده و New Rule را انتخاب کنید.
- در پنجره باز شده گزینه Use a formula … را انتخاب کنید.
- فرمول زیر را در آنجا قرار دهید.
=MOD(SUM(IFERROR(IF((MID($D3,IF(ISODD(ROW(INDIRECT("1:16"))),ROW(INDIRECT("1:16"))," "),1)*2)>9,MID($D3,IF(ISODD(ROW(INDIRECT("1:16"))),ROW(INDIRECT("1:16")),""),1)*2-9,MID($D3,IF(ISODD(ROW(INDIRECT("1:16"))),ROW(INDIRECT("1:16"))," "),1)*2),"0"))+SUM(IFERROR(MID($D3,IF(ISEVEN(ROW(INDIRECT("1:16"))),ROW(INDIRECT("1:16"))," "),1)*1,"0")),10)دانلود فایل نمونه و ویدیو آموزشی
اینها روشهایی بود که در آفیس باز معرفی شد و اگر شما هم نقطه نظری در مورد شماره کارت 16 رقمی در اکسل دارید حتما در نظرات عنوان نمائید.
فرمول تشخیص کارت بانکی در آفیس 2021
اگر از آفیس 2021 استفاده می کنید ، توابع آرایه ای میتواند به کمک شما بیاید ، یکی از این توابع ، تابع SEQUENCE می باشد .
با استفاده از این تابع فرمول کمی خلاصه تر می شود و همچنین دیگر نیازی به استفاده از کلیدهای Alt + Enter نیست . و فرمول به شکل زیر تغییر میکند.
=IF(MOD(SUM(IFERROR(IF((MID($D4,IF(ISODD(SEQUENCE(16)),SEQUENCE(16)," "),1)*2)>9,MID($D4,IF(ISODD(SEQUENCE(16)),SEQUENCE(16),""),1)*2-9,MID($D4,IF(ISODD(SEQUENCE(16)),SEQUENCE(16)," "),1)*2),"0"))+SUM(IFERROR(MID($D4,IF(ISEVEN(SEQUENCE(16)),SEQUENCE(16)," "),1)*1,"0")),10)=0,INDEX(B:B,MATCH(--LEFT($D4,6),A:A,0)),"NOT OK")
نوشتن فرمول به صورت آرایه ای و نمایش تصاویر
اگر آفیس 365 یا 2021 داشته باشید ، تابع IMAGE را هم دارید و می توانید از آن استفاده کنید ، اولین آپدیت ، مربوط به خود فرمول هست که به شکل زیر هم می توانید بنویسید که با استفاده از تابع SUMPRODUCT به صورت آرایه ای نوشته شده است.
=IF(MOD(SUMPRODUCT((MID(D2,{1,3,5,7,9,11,13,15},1)*2-((MID(D2,{1,3,5,7,9,11,13,15},1)*2>9)*9)) + MID(D2,{2,4,6,8,10,12,14,16},1)),10)=0,VLOOKUP(LEFT(D2,6)+0,A1:B100,2,0),"NOT OK")
برای نمایش تصویر در سلول هم تابع زیر بهره ببرید.
=IFERROR(IF(E2="NOT OK",IMAGE("https:/dl.officebaz.ir/free/Bank/0.jpg"),IMAGE("https:/dl.officebaz.ir/free/Bank/"&LEFT(D2,6)&".jpg")),"")همانطور که میبینید تصاویر با استفاده از تابع IMAGE به صورت آنلاین فراخوانی می شود که در سایت آفیس باز بارگذاری شده است .
یعنی تصاویر با نام 6 رقم اول کات بانکی و با پسوند jpg می باشد .
