Validare CNP și IBAN în #Excel

În urmă cu ceva timp scriam un articol pentru validarea numerelor CNP ale cetățenilor români cu aplicabilitate în Excel și SharePoint.

În ultimul timp a devenit necesară într-una din activitățile pe care le desfășor implementarea unui algoritm de validare a IBAN-urilor românești. Având în vedere că în ultima perioadă am început o cercetare amânunțită de rezolvare a unor probleme clasice de algoritmică în Excel, mi-am propus să restructurez și modul de validare a IBAN-ului. (Nu am renunțat la seria de articole, doar că nu este suficient timp pentru cercetare).

Reamintesc că funcțiile din acest articol sunt scrise în Excel 365 cu localizare în limba română, ceea ce înseamnă că separatorul de parametri în formule este ; (punct și virgulă).

Pentru cei care dorescă să descarce fișierul Excel de validate îl puteți descărca de aici:

În cazul în care nu aveți Excel 365, am forțat funcționarea unei versiuni în Google Docs disponibilă în editare valori la această adresa.

Validarea CNP-ului românesc

Pentru validarea CNP-ului se folosește o cifră de control cu care se obține rezultatul sumei produselor primelor 12 cifre din CNP cu numărul de control, apoi se calculează MOD 11 din suma de control și se compară cu a 13-a cifră din CNP.

Codul funcției din A2:

=LET(
    cnp;A2;
    control; "279146358279";
    validare_lungime; LEN(cnp) = 13;
    validare_numere; SUMPRODUCT(--ISNUMBER(--MID(cnp; SEQUENCE(LEN(cnp)); 1))) = 13;
    vControl; MID(control; SEQUENCE(12); 1);
    vCNP; MID(cnp; SEQUENCE(12); 1);
    sumControl; SUM(vCNP * vControl);
    cifra_control; IF(MOD(sumControl; 11) = 10; 1; MOD(sumControl; 11));
    validare_cifra_control; cifra_control = --RIGHT(cnp; 1);
    valid; AND(validare_lungime; validare_numere; validare_cifra_control);
    IF(valid; "Valid"; "Invalid")
)

în care folosesc un tehnică de a obține valori de validare intermediare (lungime, să fie doar numere, cifra de control). rezultatul validărilor intermediare îl determin final în variabila valid în care trebuie să aibă toate verificările intermediare valoare true.

Validarea IBAN-ului românesc

Pentru validarea IBAN-urilor există o tehnică internațională descrisă pe larg cu toate cazurile în legătura de pe Wikipedia: International_Bank_Account_Number

Pe scurt, în procesul de validare al doilea grup de 4 cifre trebuie adus la început, apoi toate literele din IBAN se transformă în CODUL lor Ascii +55, pentru ca litera A să devină 10, B 11 și așa mai departe.

Pausul următor este împărțirea numărului rezultat în grupuri de 9 și aplicarea MOD 97 asupra acestor rezultate. Dacă la final obținem valoarea 1 la ultimul MOD atunci IBAN-ul este valid. Dacă nu înseamnă că este Invalid.

Funcția propusă de mine este disponibilă doar în versiunea Excel 365 sau în fișierul Google Docs în mod fortaț pus la dispoziție la începutul acestui articol.

=LET(
    iban; SUBSTITUTE(TRIM(D8);" ";"");
    rearrangedIBAN; MID(iban; 5; LEN(iban) - 4) & LEFT(iban; 4);
    charToNum; LAMBDA(ch; IF(ISNUMBER(--ch); ch; TEXT(CODE(UPPER(ch)) - 55; "0")));
    convertToNumbers; TEXTJOIN(""; TRUE; MAP(MID(rearrangedIBAN; ROW(INDIRECT("1:" & LEN(rearrangedIBAN))); 1); charToNum));
    numericIBAN; TEXTJOIN(""; TRUE; MID(convertToNumbers; ROW(INDIRECT("1:" & LEN(convertToNumbers))); 1));
  segments;  MID(numericIBAN; SEQUENCE(ROUNDUP(LEN(numericIBAN)/9; 0);;1;9); 9);
    calcModnoua; REDUCE(0; segments; LAMBDA(acc;seg; MOD(--(acc & seg); 97)));
    result; IF(AND(LEN(iban); calcModnoua = 1); "Valid"; "Invalid");
convertToNumbers)

În această funcție în variabila charToNum este o funcție lambda recursivă care o folosesc în convertToNumbers pentru a putea calcula acea transformare a caracterelor in numere prin Code() de literă -55.

În imagine sunt prezentate rezultatele intermediare:

Variabila calcModnoua este de fapt o funcție REDUCE() dar ca să o pot prezenta în această imaginine am înlocuit-o cu un SCAN care arată rezultatul MOD 97 la fiecare număr din variabila segments.

Mi-am dat seama de omisiunea de verificare a lungimii din variabila result în timpul redactării articolului, așa că în variantele disponibile pe internet, este posibil ca anumite cuvinte să fie considerate IBAN corect. :)

Cam atât!

Sper să fie util cuiva!