Täna tuli üles üks vana küsimus – kuidas Excelis kontrollida isikukoodi. Olen kunagi sellise asja teinud, aga ei leidnud üles. Otsustasin, et proovin siis uuesti
Tulemuseks oli kolm vist seni keerukaimat valemit Excelis, mis ma olen suutnud kokku panna.
Või valetan – üks lahendus sisaldas veel keerukamaid valemeid
Kuidas kontrollida isikukood kuupäevaosa ja teha seda ÜHE VALEMINA.
Kuupäevaga ei ole keeruline, see on isikukoodi numbrid 2-7. Kuupäevad on kõigis sajandites ja nii meestel kui maistel samad, n ii et sugudega ei ole probleemi. Ainus erand on 29. veebruar 00. 1800 ja 1900 seda ei ole, 2000 on (miks nii, see on juba pikem jutt), seega
Õigeks tuleb lugeda KÕIK kuupäevad, mis on tõelised ja 29. veebruar, vaid 5 ja 6 algavatel isikukoodidel
Kui on võimalik leida arv, mis on kujul yyyymmdd, siis selle sobivust kuupäevaks on lihtne kontrollida, see käib nii. Võtan prooviks oma sünnipäeva
19550307
=TEXT(19550307;"0000\.00\.00")
annab tulemuseks 1955.03.07 –nb, see on tekst!
Nüüd kontrollime, kas see on kuupäev
=TEXT(19550307;"0000\.00.0")+0
annab õige kuupäeva puhul arvu (minul siis 20155) vale kuupäeva puhul aga annab vea
Nüüd oleks vaja meil teha teisendus – isikukoodi esimene number muuta õigeks sajandiks. See aga ei lähe mitte just XIX sajandi inimeste pärast. Nimelt ei tunnista Excel kuupäevi enne aastat 1900. Nii teengi ma väikese võltsingu. Kuna 1900 ja 2100 algusega kuupäevad on samad (erand on meil just vaid 2000 – loe kuskilt Gregoriuse kalendri reegleid), siis teemegi teisenduse: 5 ja 6 lähevad 2000 ja kõik muud koodid 2100 alguseks. Seda teisendust võiks teha keerulise IF lausete jadana, aga lihtsamaks pidasin väikest “sulitamist”. Eks sa vaata ja proovi ise – lahtris A11 on minu näitel isikukood
=IF(ISERROR(TEXT((CODE(MID("FEDCA@";LEFT(A11;1);1))-50)*1000000+LEFT(A11;7);"0000\.00\.00")+0);"isikukood vigane";"isikukoodi kuupäev õige")
Kuidas aga kontrollida isikukoodi kontrollnumbrit ja taas ÜHE VALEMINA
Isikukoodi kontrolljärk arvutatakse lihtsa ja keeruliselt interpreteeritava valemiga
3550307xyz (siin xyz on järjekorra number samal kuupäeval)
Arvutatakse korrutis, kus kõik need numbrid korrutatakse järjest numbritega 1, 2, 3, 4, 5, 6, 7, 8, 9, 1 ning saadud korrutised liidetakse
3*1 + 5*2 + 5*3 + 0*4 + 3*5 + 0*6 + 7*7 + x*8 + y*9 + z*1
Saadud tulemus jagatakse 11ga ja leitakse jääk. Kui jääk on 1-kohaline, siis see ongi kontrollnumber. Kui jääk on 10, arvutatakse uus summa, kus kordajad on pisut teised: 3, 4, 5, 6, 7, 8, 9, 1, 2, 3
3*3+ 5*4 + 5*5 + 0*6 + 3*7 + 0*8 + 7*9 + x*1 + y*2 + z*3
Saadud tulemus jagatakse uuesti 11ga ja leitakse jääk – kui ka nüüd on jääk 10, siis jääb kontrollnumbriks 0, muidu saadud uus jääk
Kõige keerukam ongi selle isikukoodi numbrite välja noppimine ja korrutamine, see osutub aga võimalikuks ühe vahva funktsiooniga =SUMPRODUCT (see on nagu mesilaste taruvaik, mis sobib iga haiguse vastu, kui piirituses leotada pisut ja siis see piiritus nahka panna)
Ära küsi, kuidas ma seda tegin aga proovi (isikukood taas lahtris A11, teiste aadresside taga ei pruugi midagi olla)
=SUMPRODUCT((MID(A11;COLUMN(A1:J1);1)+0);(MID("1234567891";COLUMN(A1:J1);1)+0))
See annab selle keeruka summa
Nüüd natuke keerukam on siis variant kontrolli ja nende jääkidega ning teiste kordajatega (a1:j1 katsin dollaritega, siis saab valemit igale poole kopeerida – NB! Lahtrite A1 kuni J1 sisu ei ome siin mingit tähtsust):
=IF(MOD(SUMPRODUCT((MID(A11;COLUMN($A$1:$J$1);1)+0);(MID("1234567891";COLUMN($A$1:$J$1);1)+0));11)=10;MOD(MOD(SUMPRODUCT((MID(A11;COLUMN($A$1:$J$1);1)+0);(MID("3456789123";COLUMN($A$1:$J$1);1)+0));11);10);MOD(SUMPRODUCT((MID(A11;COLUMN($A$1:$J$1);1)+0);(MID("1234567891";COLUMN($A$1:$J$1);1)+0));11))
Selline on siis kontrolljärk ning seda tuleb lihtsalt kontrollida etteantud isikukoodi viimase märgiga
=IF(IF(MOD(SUMPRODUCT((MID(A11;COLUMN($A$1:$J$1);1)+0);(MID("1234567891";COLUMN($A$1:$J$1);1)+0));11)=10;MOD(MOD(SUMPRODUCT((MID(A11;COLUMN($A$1:$J$1);1)+0);(MID("3456789123";COLUMN($A$1:$J$1);1)+0));11);10);MOD(SUMPRODUCT((MID(A11;COLUMN($A$1:$J$1);1)+0);(MID("1234567891";COLUMN($A$1:$J$1);1)+0));11))=MID(A11;11;1)+0;"isikukood õige";"kontrolljärk vale")
… uhh!
Nüüd tuleks need kaks asja kokku panna:
=IF(ISERROR(TEXT((CODE(MID("FEDCA@";LEFT(A11;1);1))-50)*1000000+LEFT(A11;7);"0000\.00\.00")+0);"isikukood vigane";IF(IF(MOD(SUMPRODUCT((MID(A11;COLUMN($A$1:$J$1);1)+0);(MID("1234567891";COLUMN($A$1:$J$1);1)+0));11)=10;MOD(MOD(SUMPRODUCT((MID(A11;COLUMN($A$1:$J$1);1)+0);(MID("3456789123";COLUMN($A$1:$J$1);1)+0));11);10);MOD(SUMPRODUCT((MID(A11;COLUMN($A$1:$J$1);1)+0);(MID("1234567891";COLUMN($A$1:$J$1);1)+0));11))=MID(A11;11;1)+0;"isikukood õige";"kontrolljärk vale"))
KOMMENTAARIKS:
Loomulikult võiks sama asja teha oma funktsiooniga ja VBA-s, aga toodud näide ehk demonstreerib Exceli erinevaid võimekusi ja valemites peituvaid võimalusi.
Ja loomulikult on kõik kommentaarid ja küsimused teretulnud, kas siis siia või mulle meilile (henn@sarv.ee)
Henn Sarv
sarviktaat 
MID(“FEDCA@”;… on küll kaval “sulitamine” 🙂
SUMPRODUCT-i kiidan ka väga taruvaiguks, sellega annab edukalt ise ka Pivot-tabeleid teha.
Kui veel lisada ka kontrollid, et:
– len(isikukood) 11;
– left(right(isikukood;4);3)+0 > 0;
siis võiks juba täitsa OK valem olla 🙂
Muideks, selline tore isikukood nagu 37207180000 (viimased neli nullid) ei tohiks teoreetiliselt olemas olla aga teadjad olevat Rahvastikuregistris täitsa näinud olevat.
Ja üks tore paar isikukoode, kus kontrollnumber ei näita viga eelnevas sisestuses: 51107121760 ja 61107121760. Tõenäoliselt on neid veel aga jätan ka teistele avastamisrõõmu 🙂
Aitähh Toomas kommentaarist 🙂
lisan veel, et ise pean seda Column(A1:J1) kasutamist jubekavalaks leiuks 🙂
len = 11 kontrolli pole vaja. Piisab, kui pisut muuta valemit ja üsna lõpus see MID ära muuta kujule:
=MID(Aa1;11;2) või igaks juhuks näiteks =MID(A11;11;1000)
see tekitab automaatselt pikkusekontrolli
Järjekorranumbri kontrolli ( MID(A11;8;3)+0>0 ) vajalikkust eriti ei näe
katsetasin ja uurisin, kõik vale algusega variandid (seega ka lühemad), kippusid andma õige tulemuse (st vigase koodi)
Henn
Kuigi – selle 000 kontrollimine numbri kohal pole keerukas, kohe peale FEDCA nippi, esimene A11 tuleks asendada:
LEFT(A11*(MID(A11;8;3)/MID(A11;8;3))
see kontrollib (igaks juhuks) et kogu isikukood oleks arv ja et kohad 8..10 ei oleks nullid
Henn
Oki-doki!
Column(A1:J1) on tõesti ka väga kaval trikk 🙂
len(isikukood) != 11 pean siiski vajalikuks. Nii määratleb https://www.riigiteataja.ee/akt/983873?leiaKehtiv Ja miks teha seda “keeruliselt” MID abiga kui selleks on olemas ka LEN()?
Samuti jään enda juurde, et päris pädevaks kontrolliks oleks mõistlik veenduda, et positsioon 10 (järjekorranumber samal päeval sündinute eristamiseks (1…9)) ei oleks 0. Positsiooni 8 ja 9 võivad olla 00 (Kuressaare haigla) aga järjekorranumber ei tohi olla.
Veel keerukamat kontrolli, kas positsioonid 8 ja 9 kajastavad ka väljaandmise hetkel tegutsenud haigla koodi, ei oleks enam mõistlik ei Excelis ega üheski teises vahendis realiseerida. Seda enam, et praktikas on välja antud ka isikukoode, mis niikuinii ei allu neile kontrollimeetoditele.
Ja nüüd oleks aeg kõik see teadmine ka leedulastele maha müüa 😀
column(a1;j1) asemel võiks pigem kasutada märksa inimloetavamat {1;2;3;4;5;6;7;8;9;10}. Looksulud annavad Excelis massiivi.
Jahh võib küll, aga see columni asi on palju kavalam – lisaks annab see võimaluse moodustada “dünaamilisi” massiive. MA tegin samasuguse näite ka panga kontonumbri, viitenumbri, EAN-i ja muudu analoogiliste asjade jaoks. Seal vaja (välja arvatud EANis) kasutada muutuva pikkuksega massiive.
Henn
praegusel juhul annaks see jälle funktsioonile parema inimloetavuse ning võimaluse paigutada valemeid mistahes lahtritesse, ilma ringviidet kartmata. Teise mid(“… peaks saama aga üldse ära jätta ning teoreetiliselt funktsiooni kiiremaks teha.Kuigi jah, kiiruse erinevuse muutuse mõõtmiseks tuleks ilmselt ekstreemseid näiteid kasutada.
Massiivide (array) kasutamise üks häda tundub olevat, et neid ei saa kasutada Exceli enda tabelites (kui on Format As Table). Simply, multi-cell array formulas are not permitted in a formatted table.
Proovisin küll eri variante, näiteks defineerisin algselt nime kontrolljärgu_arvutamine = {1;2;3;4;5;6;7;8;9;10} ja siis kasutasin valemis massiivi koha peal seda nime ‘kontrolljärgu_arvutamine’ aga pole abiks seegi.
Nii kaua kui tabel sisaldab vaid ühte rida, on kõik korras aga alates teise rea sisestamisest läheb “katki”,
Sama häda on muidugi ka COLUMN($A$1:$J$1) pruukimisel.
Saab sellest Exceli formaaditud tabelite puhul mingi muu trikiga üle?
Ei tahaks nagu hakata pruukima umbes sellist 1564-märgilist kontrollkoodi arvutajat … =IF(MOD((1*MID([Isikukood];1;1)+2*MID([Isikukood];2;1)+3*MID([Isikukood];3;1)+4*MID([Isikukood];4;1)+5*MID([Isikukood];5;1)+6*MID([Isikukood];6;1)+7*MID([Isikukood];7;1)+8*MID([Isikukood];8;1)+9*MID([Isikukood];9;1)+1*MID([Isikukood];10;1));11)=10;IF(MOD((3*MID([Isikukood];1;1)+4*MID([Isikukood];2;1)+5*MID([Isikukood];3;1)+6*MID([Isikukood];4;1)+7*MID([Isikukood];5;1)+8*MID([Isikukood];6;1)+9*MID([Isikukood];7;1)+1*MID([Isikukood];8;1)+2*MID([Isikukood];9;1)+3*MID([Isikukood];10;1));11)=10;0;MOD((3*MID([Isikukood];1;1)+4*MID([Isikukood];2;1)+5*MID([Isikukood];3;1)+6*MID([Isikukood];4;1)+7*MID([Isikukood];5;1)+8*MID([Isikukood];6;1)+9*MID([Isikukood];7;1)+1*MID([Isikukood];8;1)+2*MID([Isikukood];9;1)+3*MID([Isikukood];10;1));11));MOD((1*MID([Isikukood];1;1)+2*MID([Isikukood];2;1)+3*MID([Isikukood];3;1)+4*MID([Isikukood];4;1)+5*MID([Isikukood];5;1)+6*MID([Isikukood];6;1)+7*MID([Isikukood];7;1)+8*MID([Isikukood];8;1)+9*MID([Isikukood];9;1)+1*MID([Isikukood];10;1));11))
kõik toimib suurepäraselt ka tabeli formaadis tabeli puhul
1. Tegin tabeli nimega Table1
2. Esimesse veergu [isikukkod] panin portsu isikukoode
3. hoolitsesin, et mu tabelil oleks TÄPSELT 10 veergu (kui vähem, tuleb veerge lisaga, kui rohkem, tuleb valemit kohendada)
4. panin ühte veergu valemi:
=IF(ISERROR(TEXT((CODE(MID(“FEDCA@”;LEFT([@Isikukood];1);1))-50)*1000000+LEFT([@Isikukood];7);”0000\.00\.00″)+0);”isikukood vigane”;IF(IF(MOD(SUMPRODUCT((MID([@Isikukood];COLUMN(Table1[#Headers]);1)+0);(MID(“1234567891”;COLUMN(Table1[#Headers]);1)+0));11)=10;MOD(MOD(SUMPRODUCT((MID([@Isikukood];COLUMN(Table1[#Headers]);1)+0);(MID(“3456789123”;COLUMN(Table1[#Headers]);1)+0));11);10);MOD(SUMPRODUCT((MID([@Isikukood];COLUMN(Table1[#Headers]);1)+0);(MID(“1234567891″;COLUMN(Table1[#Headers]);1)+0));11))=MID([@Isikukood];11;1)+0;”isikukood õige”;”kontrolljärk vale”))
Ja voilaa – toimibki 🙂
ja kui sa ei saa garanteerida tabeli laiust, siis proovi sellist valemit
=IF(ISERROR(TEXT((CODE(MID(“FEDCA@”;LEFT([@Isikukood];1);1))-50)*1000000+LEFT([@Isikukood];7);”0000\.00\.00″)+0);”isikukood vigane”;IF(IF(MOD(SUMPRODUCT((MID([@Isikukood];COLUMN(OFFSET(Table1;0;0;1;10));1)+0);(MID(“1234567891”;COLUMN(OFFSET(Table1;0;0;1;10));1)+0));11)=10;MOD(MOD(SUMPRODUCT((MID([@Isikukood];COLUMN(OFFSET(Table1;0;0;1;10));1)+0);(MID(“3456789123”;COLUMN(OFFSET(Table1;0;0;1;10));1)+0));11);10);MOD(SUMPRODUCT((MID([@Isikukood];COLUMN(OFFSET(Table1;0;0;1;10));1)+0);(MID(“1234567891″;COLUMN(OFFSET(Table1;0;0;1;10));1)+0));11))=MID([@Isikukood];11;1)+0;”isikukood õige”;”kontrolljärk vale”))
Ega juhuslikult pole ette juhtunud isikukoodi valideerivat restrictionit XSD-s?
Piirang vaid pikkusele tüüpi – on selgelt ebapiisav.