Mõned valemid mu praktikast

Et kord välja mõeldud kavalus, ei jääks kahe kaane vahele, siis panen mõne valemi ja selle saamisloo siia kirja. Ehk on ka teistele neist kasu.

Kuupäeva väljalugemine numbrijadast

 

Ühel kliendil oli vaja lahendada kuupäevad (neid on andmebaasides tuhandeid) kujul 20130417. Tekkis küsimus, kuidas sellist numbrijaga päris kuupäevaks teisendada (17. aprill 2013 on kuupäevana arv 41381).

Üks võimalus on kasutada funktsioone =DATE ja =MID (või =LEFT ja =RIGHT). Kuupäeva erinevad osad saab kaheksakohalisest arvust kõige lihtsamalt tekstifunktsiooniga (eeldame, et see kuupäevatekst on lahtris A1):

=LEFT(A1;4) <- see annab esimesed neli numbrit ehk aasta

=MID(A1;5;2) <- see annab järgmised kaks numbrit ehk kuu

=RIGHT(A1;2) <- see annab viimase kaks numbrit ehk päeva

Ning kokku:

=DATE(LEFT(A1;4);MID(A1;5;2);RIGHT(A1;2))

Mulle hakkas aga meeldima üks teine võimalus – kas lihtsam, aga huvitavam – =TEXT

=TEXT(A1;”0000\.00\.00″) <- annab tulemuseks arvu vormistatud numbrijadana, kus vahele on pistetud punktid (2013.04.17)

NB! Need kaldkriipsud seal punktide ees on olulised – need ignoreerivad punkti “kümnendpunkti” tähendust.

Kui nüüd kuidagi sundida Excelit seda “teksti” arvuna käsitleda, ongi kogu lahendus käes. Viimaseks piisab kui “tekstile” liita arv 0:

    =TEXT(A1;”0000\.00\.00″)+0

See viimane moodustabki kuupäevaarvu 41381.

Sünnikuupäev isikukoodist

 

Pisut jätkuks eelmisele, aga numbrijada on pisut erinev, 19 ja 20 asemel on numbrijada alguses 3-4 (19) või 5-6 (20). Seda saab kõige lihtsamini nii (isikukood on taas lahtris A1 – võtame näiteks 5130427xxxx – isikukoodi viimased neli numbrit meid ei huvita)

Esimese numbri saame valemiga

    =LEFT(A1;1) <- annab tulemuseks 5

Kui nüüd see jagada kahega ja ümmardada üles ning liita 17, saamegi tulemuse – 3 ja 4 annavad tulemuseks 19, 5 ja 6 annavad 20

    =ROUNDUP(LEFT(A1;1)/2;0)+17 <- annab tulemuseks 20

NB! Isikukoodid algusega 1 ja 2 jäävad paraku mängust välja. Excel ei arvuta kuupäevadega ennem 1. jaanuari 1900.

Kui nüüd saadud arvule “sappa kirjutada” kuus järgmist numbrit isikukoodist

    =(ROUNDUP(LEFT(A1;1)/2;0)+17)&MID(A1;2;6) <- saamegi tulemuseks 20130427, sama numbrijada, mis eelmises näites

Seega siis kokkuvõtlikult:

    =TEXT((ROUNDUP(LEFT(A1;1)/2;0)+17)&MID(A1;2;6);”0000\.00\.00″)+0

This entry was posted in Excel, Funktsioonid. Bookmark the permalink.

Lisa kommentaar

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Muuda )

Twitter picture

You are commenting using your Twitter account. Log Out / Muuda )

Facebook photo

You are commenting using your Facebook account. Log Out / Muuda )

Google+ photo

You are commenting using your Google+ account. Log Out / Muuda )

Connecting to %s