Kuidas Exceliga teha kasutust veebilehest aadressraamat

Tänase loo põhjuseks on kolm juhtumit, mis sattusid ühele päevale ja samale ajale.

Tutvustasin täna Exceli mõningaid nippe seminarikoolitusel. Lõunavaheajal helistas mulle mu tütar ja palus abi ühe aadressiraamatu “kasutuskõlblikustamisel”. Avasin ta viidatud veebilehe ja see oli tõesti inimese jaoks suht veidra ja kasutu ülesehitusega.

Asusin siis asja kallale. Eesmärk ikka üks – näidata, kuidas Exceliga saab S-S teisendust teha (et mitte kasutada väljendit, kus teatavast asjast teatavat pagaritoodet tehakse)

Hakatuseks siis mainitud veebileht: http://www.eesti.ee/est/riik/omavalitsused/vallavalitsused/

Siin on valdade kontaktide loend. Kahjuks mõeldud ekraanilt kasutamiseks. Kui tahaks seda omale aadressraamatuks või veel hullem otsepostituseks kasutada, siis on ta täiesti kasutu.

Hakkame siis teisendama

  1. Samm valime (select) kogu selle tabeli ja kopime-pasteme Exceli lehele. Pilt saab umbes selline

Nagu näha on kõik andmed ilusti olemas, aga nii kasutul mittetöödeldaval kujul, kui veel võimalik

  1. Tabelina ei saa kasutada – vahel on tühjad read
  2. Erinevatel valdadel on erinev arv ridu
  3. Siin seal on mestitud ja tühjad lahtrid

Meie asume nüüd toimetama

  1. Analüüs näitab, et iga vallavalitsuse alguses on tühi rida. Selle võikski võtta tunnuseks. Kahjuks ei anna =ISBLANK(Ax) mitte midagi, kuna ka vahepealsetes ridades (mitu telefoni) on tühje ridu. Ka ei saa tunnusena kasutada sõna “Vallavalitsus päises”. Järgmine samasugune tabel oleks Linnavalitsused. Tahaks teha nii, et ka sinna sobib.

Leidsin sellise valemi (pistetud veergu G ja paljundatud kuni nimekirja lõpuni)

    =IF(AND(ISBLANK(A1);ISBLANK(D1));”Tyhi”;””)

Tulemuseks midagi sellist:

 

  1. Nüüd lisan veel ühe veeru. See loeb kokku, mitmenda vallavalitsusega on tegu. Veergu H kirjutan sellise valemi:

=IF(G1=””;””;COUNTIF($G$1:G1;”Tyhi”))

See annab mulle nüüd veergu H numbrid – iga vallavalitsuse algusse järgmise. See võimaldab hõlpsalt leida, kust algab vallavalitsuse blokk. Valem ise tuleb paljundada tabeli lõpuni. Nüüd on see lihtne – vasakul ei ole enam tühje ridu. Tulemus umbes selline:

Nüüd ei ole muud teha kui leida märgitud read ja selle najal otsida andmed.

  1. Koostan tabeli (lihtsuse mõttes samale lehele veerust J. Panen esimesele veerule nimeks ReaNr ja vormindan asja tabelina. Sinna veergu kirjutan järgmise valemi

=MATCH(ROW()-1;H:H)

See annab mulle igale reale vastava vallavalitsuse ploki alguse. Kohe järgmise veeru nimeks panen Nimetus ja sinna selline valem:

    =OFFSET($A$1;[ReaNr];0)

Hakates nüüd tabeli ridu “allapoole sikutama” täitub tabel kui nõiaväel vallavalitsuste nimekirjaga:

  1. Järgmisena katsetame aadressi ja telefoninumbriga. Need saab kahe pea samalaadse valemiga:

    =OFFSET($A$1;[ReaNr]+1;1)

    =OFFSET($A$1;[ReaNr]+1;3)&””

Kui vaadata vallavalitsuse blokki, siis seal on näha:

Iga vald hakkab vallavalitsuse nimega esimeses reas esimeses veerus: =OFFSET($A$1;[ReaNr];0)

Valla aadress on teises reas teises veerus: =OFFSET($A$1;[ReaNr]+1;1)

Valla telefoninumbri leiab (vähemalt ühe) teise rea neljandast veerust: =OFFSET($A$1;[ReaNr]+1;3)

E-posti ja faksiga on keerukam. Need on kas kolmandas või neljandas reas. Aitab aga see, et nende ees on kindlalt sõna “Faks:” või “E-post:”

Kunagi ei saa kindel olla, et mõnel vallal ei ole rohkem telefoninumbreid. Seega peab otsima hakkama

 

NB! Lisasin telefoninumbrile lõppu &”” – selle mõte on tagada, et tulemus oleks TEKST mitte ARV

  1. Leiame siis E-posti ja faksi rea numbri. Need saame valemitega (vastavalt veerud EMailiReaNr ja FaksiReaNr)

    =MATCH(“E-post:”;OFFSET($A$1;[ReaNr];0;10;1);0)

    =MATCH(“Faks:”;OFFSET($A$1;[ReaNr];2;10;1);0)

        Valisin otsimisbloki suuruseks 10 rida – sellest peaks piisama. Korrektsem oleks olnud määrata mitu rida vallal on.

Reaalselt on küll näha, et need numbrid on alati samad – E-post ja Faks on alati samal real. Aga selles ei saa kunagi kindel olla, “mine sa neid mesilinnukesi tea”.

Need kaks valemit ütlevad, mitmendalt realt tuleks lugeda e-posti ja faksi andmed (faksi puhul ei tea küll, milleks toda kasutatakse, aga ehk on vaja)

Nende numbrite najal siis järgmised kaks valemit (oleks saanud üll kahte veergu vältida ja kirjutada ÜKS ning keerulisem valem kummagi jaoks)

    =OFFSET($A$1;[ReaNr]+[EmailiReaNr]-1;1)&””

    =OFFSET($A$1;[ReaNr]+[FaksiReaNr]-1;3)&””

See keerulisem valem oleks olnud näiteks:

    =OFFSET($A$1;[ReaNr]+ MATCH(“Faks:”;OFFSET($A$1;[ReaNr];2;10;1);0)-1;3)&””

Võib olla on niimoodi kahe veeruga ikkagi lihtsam lugeda ja aru saada

  1. Viimane samm – kogu tabel (kõik valemid) tuleks siis lohistada õigele kaugusele – kuni ülenurme vallani

Kogu tabel siis selline:

 

Panin valmis lehe koos kõigi valemitega ka oma “hoidlasse”: http://www.sarv.ee/ftp/henn/excel

Faili nimi on vallavalitsused.xlsx

Jõudu katsetamisel ja uurimisel. Kõik kommentaarid on tere ja tulnud.

Henn

This entry was posted in Excel, Funktsioonid, Office. 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