Excel'de IBAN Doğrulama Formülü: Makrosuz MOD 97

Excel'de bir IBAN'ı makro yazmadan doğrulamak mümkün, ama akla ilk gelen formül yanlış sonuç verir. IBAN'ı sayıya çevirip MOD(...;97) almak işe yaramaz, çünkü Excel 26 haneli bir Türkiye IBAN'ından türeyen sayıyı tam tutamaz. Bu yazıdaki formüller bu sorunu, sayıyı küçük parçalara bölerek aşıyor. Hepsini Excel'in hesaplama kurallarını adım adım taklit eden bir Python betiğiyle, geçerli ve hatalı IBAN'lar üzerinde çalıştırıp sınadık.

Formüllere geçmeden önce sınırı netleştirelim: Excel ne kadar akıllı formül kurarsanız kurun yalnızca biçimi kontrol eder. Formülün "DOĞRU" demesi, o hesabın açık olduğunu ya da listedeki isme ait olduğunu göstermez. Bu farkı geçerli IBAN ile aktif hesap arasındaki ayrımı anlatan rehberde ayrıntılı bulabilirsiniz.

Önce hızlı eleme: uzunluk ve ülke kodu

Maaş ya da tedarikçi listesinin büyük kısmı Türkiye IBAN'ıysa, en ucuz kontrol uzunluktur. TR IBAN'ı boşluksuz yazıldığında her zaman 26 karakterdir. Temizlenmiş IBAN B sütunundaysa şu formül yeter:

=VE(UZUNLUK(B2)=26;SOLDAN(B2;2)="TR")

Bu kontrol, baştaki sıfırı düşmüş, sonu kesilmiş ya da ülke kodu kopmuş satırların çoğunu yakalar. Yakalayamadığı şey, uzunluğu doğru ama bir hanesi yanlış yazılmış IBAN'dır. Sekizi üç okunmuş bir numara hâlâ 26 karakterdir. Bunun için kontrol hanelerine, yani MOD 97'ye ihtiyaç var. Algoritmanın matematiğini IBAN MOD 97 nedir? sayfasında anlattık; burada yalnızca Excel'e nasıl taşındığına odaklanıyoruz.

Naif MOD neden yanlış sonuç verir?

MOD 97 için IBAN'ın ilk dört karakteri sona alınır, harfler sayıya çevrilir (T=29, R=27) ve ortaya çıkan dev sayının 97'ye bölümünden kalana bakılır. Örnek IBAN TR140001005001234567890123 için bu sayı 0001005001234567890123292714 olur. Baştaki sıfırlar atılınca geriye 25 anlamlı hane kalır.

Microsoft'un Excel belirtimlerinde sayı hassasiyeti 15 basamak olarak geçer. Excel bu sayıyı saklarken 15. haneden sonrasını yuvarlar; elinizde 1,00500123456789E+24 gibi bir değer kalır. Yuvarlanmış bir sayının kalanı ise orijinal sayının kalanıyla ilgisizdir. Ondalık olarak yuvarlanmış bu değerin 97'ye bölümünden kalan 10 çıkıyor, doğru cevap 1. Yani formül geçerli bir IBAN'a "hatalı" der ve daha kötüsü, bazı hatalı IBAN'lara tesadüfen "geçerli" diyebilir. Bu yüzden tek hücrede SAYIYAÇEVİR ile başlayan bir kısayol görürseniz kullanmayın.

Çözüm, kalanı parça parça taşımak. Modüler aritmetikte büyük sayının kalanı, soldan başlayıp her adımda "önceki kalan + sonraki birkaç hane" birleştirilerek hesaplanabilir. Önceki kalan en fazla iki haneli olduğu için, yanına yedi hane eklendiğinde en büyük ara değer dokuz haneyi geçmez. Dokuz hane, 15 basamaklık sınırın rahatça altındadır ve sonuç tamsayı olarak kesindir.

Her Excel sürümünde çalışan yardımcı sütun yöntemi

Excel 2016 veya 2019 kullanıyorsanız LET ve LAMBDA yok. Bu durumda işi dört yardımcı sütuna bölmek hem çalışır hem de hata ayıklamayı kolaylaştırır. Ham IBAN A sütununda, formüller ikinci satırda olsun. Türkçe Excel'de işlev adları çevrilidir ve argüman ayırıcısı noktalı virgüldür, bu yüzden önce Türkçe sürümü veriyoruz:

B2  (temiz IBAN)
=BÜYÜKHARF(YERİNEKOY(YERİNEKOY(A2;" ";"");DAMGA(160);""))

C2  (yapı: 26 karakter, TR ile başlıyor, geri kalanı rakam)
=VE(UZUNLUK(B2)=26;SOLDAN(B2;2)="TR";UZUNLUK(YERİNEKOY(YERİNEKOY(YERİNEKOY(YERİNEKOY(YERİNEKOY(YERİNEKOY(YERİNEKOY(YERİNEKOY(YERİNEKOY(YERİNEKOY(PARÇAAL(B2;3;24);"0";"");"1";"");"2";"");"3";"");"4";"");"5";"");"6";"");"7";"");"8";"");"9";""))=0)

D2  (MOD 97, 7'şer haneli parçalarla)
=EĞERHATA(MOD(MOD(MOD(MOD(PARÇAAL(B2;5;7);97)&PARÇAAL(B2;12;7);97)&PARÇAAL(B2;19;7);97)&PARÇAAL(B2;26;1)&"2927"&PARÇAAL(B2;3;2);97)=1;YANLIŞ)

E2  (sonuç)
=VE(C2;D2)

B sütunu normal boşlukları ve web sayfalarından kopyalarken gelen bölünemez boşluğu (karakter kodu 160) siler, harfleri büyütür. Tire veya nokta gibi başka ayraçlar varsa bunları da aynı şekilde YERİNEKOY ile eklemeniz gerekir; daha dağınık listeler için önce Excel'de IBAN temizleme rehberindeki adımları uygulayın.

C sütunundaki uzun iç içe YERİNEKOY zinciri çirkin ama bilinçli bir tercih. 24 hanelik gövdeden on rakamı tek tek silip geriye bir şey kalıp kalmadığına bakıyor. Neden bu kadar uğraştığımızı D sütunu açıklıyor: Excel, MOD içine verilen metni sayıya çevirmeye çalışırken 12345E1 gibi bir parçayı bilimsel gösterim sanıp 123450 kabul edebilir. Gövdenin yalnızca rakam olduğunu önceden kanıtlarsak bu tuzak ortadan kalkar.

D sütunu hesabın kalbi. Yeniden düzenlenmiş dizi, IBAN'ın 5. karakterinden başlayan 22 hane, ardından TR'nin sayısal karşılığı 2927 ve en sonda iki kontrol hanesinden oluşur. Toplam 28 hane, 7-7-7-7 olarak dört parçaya bölünür. Son parça, IBAN'ın son karakteri, 2927 ve kontrol haneleri birleştirilerek kurulur. & işareti önceki MOD sonucunu metne çevirip bir sonraki parçanın önüne yapıştırır. E sütunu iki kontrolü birleştirir.

İngilizce arayüzlü Excel için karşılıkları (C sütunu aynı mantıkla SUBSTITUTE ile kurulur):

B2: =UPPER(SUBSTITUTE(SUBSTITUTE(A2," ",""),CHAR(160),""))
D2: =IFERROR(MOD(MOD(MOD(MOD(MID(B2,5,7),97)&MID(B2,12,7),97)&MID(B2,19,7),97)&MID(B2,26,1)&"2927"&MID(B2,3,2),97)=1,FALSE)
E2: =AND(C2,D2)

Microsoft 365: tek hücrede, her ülke için

LET, LAMBDA, REDUCE ve SEQUENCE (Türkçesi SIRALI) bir arada gerektiği için bu formül Microsoft 365 abonelerine yöneliktir. Microsoft'un belgelerine göre LET Excel 2021 ve 2024'te de var, ancak REDUCE sayfası yalnızca Microsoft 365'i listeliyor. Kalıcı lisanslı bir sürümde çalışıp çalışmadığını kendi dosyanızda denemeden listeye uygulamayın.

=LET(temiz;BÜYÜKHARF(YERİNEKOY(YERİNEKOY(A2;" ";"");DAMGA(160);""));
 dizi;PARÇAAL(temiz;5;UZUNLUK(temiz))&SOLDAN(temiz;4);
 kalan;REDUCE(0;SIRALI(UZUNLUK(dizi));LAMBDA(acc;no;
   LET(kk;KOD(PARÇAAL(dizi;no;1));
       hv;EĞER(VE(kk>=48;kk<=57);kk-48;EĞER(VE(kk>=65;kk<=90);kk-55;YOKSAY()));
       MOD(acc*EĞER(hv<10;10;100)+hv;97))));
 EĞERHATA(VE(UZUNLUK(temiz)>=15;UZUNLUK(temiz)<=34;
   KOD(temiz)>=65;KOD(PARÇAAL(temiz;2;1))>=65;
   KOD(PARÇAAL(temiz;3;1))<=57;KOD(PARÇAAL(temiz;4;1))<=57;
   EĞER(SOLDAN(temiz;2)="TR";UZUNLUK(temiz)=26;DOĞRU);
   kalan=1);YANLIŞ))

=LET(temiz,UPPER(SUBSTITUTE(SUBSTITUTE(A2," ",""),CHAR(160),"")),
 dizi,MID(temiz,5,LEN(temiz))&LEFT(temiz,4),
 kalan,REDUCE(0,SEQUENCE(LEN(dizi)),LAMBDA(acc,no,
   LET(kk,CODE(MID(dizi,no,1)),
       hv,IF(AND(kk>=48,kk<=57),kk-48,IF(AND(kk>=65,kk<=90),kk-55,NA())),
       MOD(acc*IF(hv<10,10,100)+hv,97)))),
 IFERROR(AND(LEN(temiz)>=15,LEN(temiz)<=34,
   CODE(temiz)>=65,CODE(MID(temiz,2,1))>=65,
   CODE(MID(temiz,3,1))<=57,CODE(MID(temiz,4,1))<=57,
   IF(LEFT(temiz,2)="TR",LEN(temiz)=26,TRUE),
   kalan=1),FALSE))

Formül dev sayıyı hiç kurmuyor. Her karakter için kod değerine bakıyor: rakamsa 0-9, harfse 10-35 değerini alıyor. Tek haneli değerde birikmiş kalanı 10 ile, iki haneli değerde 100 ile çarpıp ekliyor ve hemen 97'ye göre kalanı alıyor. Ara değer hiçbir zaman 9.700'ü geçmediği için hassasiyet sorunu doğmuyor. Harf ya da rakam dışında bir karakter kalırsa YOKSAY() hata üretir, en dıştaki EĞERHATA bunu YANLIŞ'a çevirir. Boş hücreler de aynı yoldan YANLIŞ döner.

Ek koşullar ilk iki karakterin harf, sonraki ikisinin rakam olmasını ve toplam uzunluğun 15 ile 34 karakter arasında kalmasını denetliyor. Üst sınır standardın izin verdiği en uzun değer, alt sınır ise kayıttaki en kısa IBAN olan Norveç'in uzunluğu. TR için ayrıca 26 karakter şartı var. Diğer ülkelerin kesin uzunluklarını da kontrol etmek istiyorsanız, ülke kodu ve uzunluktan oluşan küçük bir tabloyu DÜŞEYARA ya da ÇAPRAZARA ile bu koşula bağlayabilirsiniz. Uzunluk değerleri için ülke IBAN formatları sayfasına bakın.

Nasıl sınadık?

Excel'i doğrudan çalıştıramadığımız için formülleri işlev işlev Python'a aktardık: PARÇAAL'ın 1'den başlayan konum mantığını, MOD'un metni sayıya çevirmesini, 15 basamaklık yuvarlamayı ve VE'nin tüm argümanları değerlendirmesini taklit ettik. Ardından TR140001005001234567890123, DE89370400440532013000 ve GB82WEST12345698765432 için DOĞRU; TR150001005001234567890123, bir hanesi eksik ve bir hanesi fazla TR IBAN'ı için YANLIŞ sonucunu doğruladık. Üstüne rastgele üretilmiş 20 bin geçerli ve bozulmuş IBAN'da formül sonuçlarını bağımsız bir referans hesapla karşılaştırdık; fark çıkmadı. Yine de kendi dosyanızda şu üç örnekle deneme yapmanızı öneririz, çünkü bölgesel ayarlar ve kopyalama sırasında bozulan tırnak işaretleri formülü sessizce değiştirebilir.

Sık düşülen tuzaklar

• IBAN sütununun sayı biçiminde olması. TR ön eki silinmiş bir liste Excel'e sayı olarak girerse son haneler daha formüle gelmeden kaybolur. Formül bunu geri getiremez; kaynağa dönmek gerekir.
• Akıllı tırnaklar. Formülü bir e-posta veya kelime işlemciden kopyalarsanız düz tırnaklar kıvrık tırnağa dönebilir ve Excel formülü reddeder.
• Ayırıcı karışıklığı. İngilizce formülü Türkçe Excel'e yapıştırmak çalışmaz; hem işlev adları hem virgül-noktalı virgül farkı sorun çıkarır.

Formül kurmakla uğraşmak istemiyorsanız ya da dosyada birkaç bin satır varsa Excel IBAN temizleme aracı sütunu tarayıcıda temizleyip doğrular. Aynı işi Google E-Tablolar'da yapmak için Google Sheets'te IBAN doğrulama yazısına, kodla yapmak için Python ile IBAN doğrulama yazısına bakabilirsiniz.