قبلا شماره 16 رقمی کارت بانکی و شماره 10 رقمی کارت ملی را در اکسل و اکسس کنترل کرده ایم و حالا نوبت به کنترل صحت شماره شبا در اکسل رسیده است . که با استفاده از الگوریتم ساده و جذاب شماره شبا می توانیم این کار را انجام دهیم.
فهرست محتوا
الگورتیم بررسی صحت شماره شبا در اکسل
همانطور که در قبلا گفتیم الگوریتم ساده و جذابی برای کنترل صحت شماره شبا وجود دارد و روش کار به شرح ذیل است . باید بدانیم که شماره شبا یک شماره بین المللی می باشد و دو رقم ابتدایی همانطور که در تصویر هم می بینید مشخص کننده کشور صادر کننده شماره شبا می باشد.
- معادل عددی دو حرف اول شماره شبا را به دست می آوریم
- عدد به دست آمده را به انتهای شماره اضافه می کنیم ( معادل IR می شود 1827 )
- دو رقم اول بعد از کد کشور را به انتهای شماره شبا اضافه می کنیم.
- اگر باقیمانده تقسیم عدد به دست آمده به 97 مساوی 1 باشد ، شماره شبا صحیح است.
بررسی یک شماره شبای آزمایشی
همه چیز با انجام مثال شماره شبا در اکسل قابل درک تر خواهد بود ، شماره شبای زیر یک شماره شبای صحیح می باشد ، که می خواهیم با الگوریتم حاکم صحت آن را بررسی کنیم.
IR870570028180010653892101
ابتدا دو حرف IR را از ابتدا حذف کرده و عدد 1827 را به انتها اضافه می کنیم.
8705700281800106538921011827
حالا دو عدد بعد از دو حرف IR را به انتها انتقال می دهیم که اینجا 87 است.
0570028180010653892101182787
حالا باقیمانده تقسیم عدد به دست آمده بر 97 را محاسبه می کنیم.
حاصل عدد 1 می شود و بنابراین این شماره شبا صحیح است.
چگونه در اکسل شماره شبا را کنترل کنیم ؟
خب حالا که الگوریتم بررسی صحت شماره شبا را فهمیدیم ، زمان آن را رسیده که شماره شبا در اکسل را بررسی کنیم که برای این منظور مراحل ذیل را انجام میدهیم.
- با زدن کلیدهای Alt + F11 به کدنویسی بروید.
- در فضای مربوط به ماژولها کلیک راست کرده و گزینه Insert و سپس Module را انتخاب کنید.
- کدهای زیر را در آنجا قرار دهید.
- بهتر است در آن واحد فقط یک فایل باز اکسل داشته باشید
- فایل را با پسوند XLSM ماکرو اینیبل ذخیره کنید.
- فرض می کنیم شماره شبا را در A2 وارد کرده اید.
- کافیست در B2 از تابع VALIDATEIBAN(A2) استفاده نمائید.
Public Function VALIDATEIBAN(ByVal IBAN As String) As String
On Error GoTo CatchError
Dim objRegExp As Object
Dim IBANformat As Boolean
Dim IBANNR As String
Dim ReplaceChr As String
Dim ReplaceBy As String
IBAN = UCase(IBAN)
IBAN = Replace(IBAN, " ", "")
IBAN = Trim(IBAN)
Set objRegExp = CreateObject("vbscript.regexp")
objRegExp.IgnoreCase = True
objRegExp.Global = True
objRegExp.Pattern = "[a-zA-Z]{2}[0-9]{2}[a-zA-Z0-9]{4}[0-9]{7}([a-zA-Z0-9]?){0,16}"
IBANformat = objRegExp.Test(IBAN)
'Validity of country code will not be checked!
If IBANformat = False Then
VALIDATEIBAN = ChrW(1582) & ChrW(1575) & ChrW(1604) & ChrW(1740) & " " & ChrW(1740) & ChrW(1575) & " " & ChrW(1601) & ChrW(1585) & ChrW(1605) & ChrW(1578) & " " & ChrW(1606) & ChrW(1575) & ChrW(1583) & ChrW(1585) & ChrW(1587) & ChrW(1578)
Else
'Flip first 4 characters to the back
IBANNR = Right(IBAN, Len(IBAN) - 4) & Left(IBAN, 4)
'Replace letters by the right numbers
For Nr = 10 To 35
ReplaceChr = Chr(Nr + 55)
ReplaceBy = Trim(Str(Nr))
IBANNR = Replace(IBANNR, ReplaceChr, ReplaceBy)
Next Nr
'Loop through the IBAN, as it is too long to calculate at one go
CurrPart = ""
Answer = ""
For CurrDigit = 1 To Len(IBANNR)
CurrPart = CurrPart & Mid$(IBANNR, CurrDigit, 1)
CurrNumber = CLng(CurrPart)
'If the number can be divided
If 97 <= CurrNumber Then
LeftOver = CurrNumber Mod 97
WorkValue = (CurrNumber - LeftOver) / 97
Answer = Answer & CStr(WorkValue)
CurrPart = CStr(LeftOver)
Else
'If no division occurred, add a trailing zero
If Len(Answer) > 0 Then
Answer = Answer & "0"
'Exception for the last number
If CurrDigit = Len(IBANNR) Then
LeftOver = CurrNumber Mod 97
Else
End If
Else
End If
End If
Next
If LeftOver = 1 Then
VALIDATEIBAN = ChrW(1589) & ChrW(1581) & ChrW(1740) & ChrW(1581)
Else
VALIDATEIBAN = ChrW(1606) & ChrW(1575) & ChrW(1583) & ChrW(1585) & ChrW(1587) & ChrW(1578)
End If
End If
Exit Function
CatchError:
VALIDATEIBAN = "ERROR: " & Err.Description
End Function
نکات مهم صحت شماره شبا در اکسل
- شماره شبا یک شماره بین المللی است و دو رقم اول نشان دهنده کشور صادر کننده
- شماره شبا در ایران 26 رقم است
- به دلیل بزرگ بودن عدد مورد استفاده در الگوریتم اگر تعداد شماره شبا برای بررسی زیاد باشد ، احتمال پائین آمدن سرعت فایل اکسل وجود دارد.
بررسی صحت شماره شبا بدون کد نویسی
بدون نیاز به کدنویسی و با استفاده از ترکیب داده ها هم می توانیم صحت شماره شبای وارد شده در اکسل را بررسی کنیم و کافیست در سلول مورد نظر فرمول زیر را بنویسیم.
=IF(OR(LEN(B4)<26,LEN(B4)>26),(LEN(B4)-2&" " & "رقم"),IF(MOD(((53*(MOD(LEFT(RIGHT(RIGHT(B4,24),22)&1827&MID(B4,3,2),14),97))+MOD(RIGHT(RIGHT(RIGHT(B4,24),22)&1827&MID(B4,3,2),14),97))),97)=1,"ok","نادرست"))
بدیهی است که در فرمول بالا به جای B4 باید سلول مورد نظر خود را وارد کنید و همچنین باید IR در ابتدای شماره شبا باشد.
