Excel arvutab „valesti“

Üks hää sõber jutustas mulle, et Excel kipub valesti arvutama – SUM ja SUMIF saavad erineva tulemuse.

Mina muidugi ei uskunud ja asusin kontrollima. Avasin Exceli ja tegin uue dokumendi. Veergu A:A lisasin miljon juhuslikku arvu (funktsioon =RAND()). Ning siis hakkasin neid liitma.

  • Ühte lahtrisse    =SUM(A:A)        // liidame kokku kõik arvud veerus A
  • Teise lahtrisse    =SUMIF(A:A;”<0,5″)    // liidame kokku kõik arvud, mis on väiksemad kui pool
  • Kolmandasse     =SUMIF(A:A;”>=0,5″)    // liidame kokku need, mis on suuremad kui pool (kaasa arvatud)
  • Neljandasse teise ja kolmanda summa    // liidame need kaks poolt ja võrdleme esimesega
  • Viiendasse esimese ja neljanda vahe – ja ongi:


Vahe pole suur aga ikkagi. On kohti (VLOOKUP näiteks), mis otsib täpseid asju

Hakkasin siis asja uurima – veebis soovitatakse kahte viisi – Precision as displayed ja ROUND. Paraku ei aita kumbki. Roundi, mis sa roundid, viga jääb alles. PRecision as displayed aga on saatanast ja teeb su kogu töö katki.

Vaatame seda roundimist – ümmardamist

  

5

=SUMPRODUCT((ROUND(A:A;$E$8)))

  

=SUMPRODUCT((ROUND(A:A;$E$8))*(A:A>0,5))

  

=SUMPRODUCT((ROUND(A:A;$E$8))*(A:A<=0,5))

  

=D10+D11

  

=D9-D12

  

 
 

Kirjutasin lahtrisse E8 5 (kohtade arv) ja kasutades müstilismugavat funktsiooni proovisin ümmardamisega 5 kohta peale koma. Viga on ikka alles

  

5

499887,0305

  

374597,1802

  

125289,8504

  

499887,0305

  

4,36557E-09

  

 
 

Muutes kohtade arvu ümmardades, selgus et viga kaob alles, kui ümmardada täisarvuni ( =ROUND(x;0) )

Natuke selgituseks. Excel EI SAAGI täpselt arvutada, kuna Excelis on kõik arvud UJUKOMA arvud. Veebis on rikkalikult näiteid, kuidas arvutamisel tekivad pisivead. Enamasti on need tähtsusetud, aga meie näites näe ei ole. Erandiks on aga arvud, mis on “täisarvud” (Excel tunneb sellised ära) ja nendega arvutades Excel “hoidub vigadest”. Kuidas ta seda teeb, on keerukam teema

Seega arv, millel on 5 kohta peale koma on umbmäärane, arv, mis täisarv aga täpne. Aga kui korrutaks arvu 100 000ga. Siis tal ju ei ole kohti peale koma? Mõeldud tehtud. Proovisin nii – korrutan oma juhuarvud saja tuhandega, ümmardan täisarvuni, liidan seejärel ja jagan tulemuse saja tuhandega. Peaks ju õnnestuma?

Proovisin:

  

100000

=SUMPRODUCT((ROUND(A:A*$E$15;0)))/$E$15

  

=SUMPRODUCT((ROUND(A:A*$E$15;0)*(A:A<0,5)))/$E$15

  

=SUMPRODUCT((ROUND(A:A*$E$15;0)*(A:A>=0,5)))/$E$15

  

=D17+D18

  

=D16-D19

  

 
 

Ja tulemus

  

100000

499887,03051

  

125289,85036

  

374597,18015

  

499887,03051

  

0

  

 
 

Seega – väikeste arvude asemel tegele suurtega ja väikeselt mõtlemise asemel mõtle suurelt

Panin näidise aadressile http://bit.ly/ExcelArvutabValesti – võid ise järele proovida. NB! Muutsin mõned valemid, et nad toimiks ka veebilehel. NB! Näidises tuleb valem =RAND() dubleerida lahtrini A1000000, muidu viga ei paista.

Henn – 9.9.2014

  

Posted in Excel, Funktsioonid, Office | Lisa kommentaar

SQL and bit different Partitioned View

Last week I had to play with new old feature – Partitioned View. And I get in trouble. Fortunately only shortly.

For start – some recall – what is Partitioned View. This is technique putting several similar tables to behive as one. Each of tables can have different supporting indexes, they could reside in different (incl readonly) filegroups etc.

The good thing with Partitioned Views is that query against this view containing suitable where-clause will touch only tables needed for producing result. Some conditions allow threat Partitioned View as updatable view – this allow partitioned table looks like “real table” but more flexible and performing.

Short example:

create table T1 (kood int primary key (check kood between 1 and 100, .... )
create table T2 (kood int primary key (check kood between 101 and 200, .... )
create table T3 (kood int primary key (check kood between 201 and 300, .... )
GO
create view TT as
select * from T1
union all
select * from T2
union all
select * from T3
GO

Resulting view TT is actually partitioned view. Query like:

select ... from TT where kood = 107

touch only table T2 and query:

select ... from TT where kood between 170 and 210

touch tables T2 and T3.

Usually the partioning key will be some date period. This allow older rows to be placed into readonly filegroup for example and the queries will perform much better.

This feature I teach and use long time. This presume thar partitipating tables have partition key column and this column have correct and controlled constraint to divide big picture into small, non-overlaping ones.

That mean – I need to have Data Model under my control. But what to do when I haven’t?

I had a following case. Database contain hundred of tables and there is no way to change Data Model. Tables contain data for different departments and years and the name of table point to the department and year. Something like: TabXX2012, TabXX2013, Tabxx2014, TabYY2012, TabYY2013, TabYY2014. For reporting I need some summary view of those tables.

When I create union view like following

create view TabAll
as
select 'XX' as Department, 2012 as Year, * from TabXX2012
union all
select 'XX' as Department, 2013 as Year, * from TabXX2013
union all
select 'XX' as Department, 2014 as Year, * from TabXX2014
union all
select 'YY' as Department, 2012 as Year, * from TabYY2012
union all
select 'YY' as Department, 2013 as Year, * from TabYY2013
union all
select 'YY' as Department, 2014 as Year, * from TabYY2014

and query this view like:


select * from TabAll where Year = 2013

then my query plan shows that query touch all tables. Query optimizer “don’t see”, that Year 2013 contains only in 2 of tables. Now I find solution how to make real “partitioned” view (I use a script to generate that view).

create view TabAll
as
select * from (select 'XX' as Department, 2012 as Year, * from TabXX2012) T where Department = 'XX' and Year = 2012
union all
select * from (select 'XX' as Department, 2013 as Year, * from TabXX2013) T where Department = 'XX' and Year = 2013
union all
select * from (select 'XX' as Department, 2014 as Year, * from TabXX2014) T where Department = 'XX' and Year = 2014
union all
select * from (select 'YY' as Department, 2012 as Year, * from TabYY2012) T where Department = 'YY' and Year = 2012
union all
select * from (select 'YY' as Department, 2013 as Year, * from TabYY2013) T where Department = 'YY' and Year = 2013
union all
select * from (select 'YY' as Department, 2014 as Year, * from TabYY2014) T where Department = 'YY' and Year = 2014

This view works like “classical” partitioned view. Query like

select * from TabAll where Year = 2012

touch only tables TabXX2012 ja TabYY2012. Secret is in where clouse. Query optimizer “see” this as condition and knows how to optimize this execution plan.

I have some other ideas how to use that kind of view – more closer in follow up posts

Posted in Määratlemata, MS SQL Server | Tagged | Lisa kommentaar

SQL ja pisut teistmoodi Partitioned View

Selle loo kirjutan nii eesti kui inglise keeles.

Sel nädalal pidin mängima uue vana vahendiga: Partitioned Views. Ja jäin hätta. Õnneks ainult natukeseks.

Hakatuseks meenutus, mis on partitioned view. See on tehnika, mis võimaldab mitu ühesugust tabelit liita näiliselt üheks. Igal võivad olla omad tugiindeksid, nad võivad paikneda erinevates (ka lugemiseks vaid) failigruppides.

Partitioned view hea omadus on, et kui ta on päringu sihmärk ja päringus on sobiv where-fraas, siis päringu täitmisplaani lülitatakse ainult vajalikud tabelid. Mõnede tingimuste täitmisel on partitioned view ka uuendatav, sel juhul ta “paistab” nagu “päris tabel”, aga on palju paindlikum ja kiirem.

Väike näide:

create table T1 (kood int primary key (check kood between 1 and 100, .... )
create table T2 (kood int primary key (check kood between 101 and 200, .... )
create table T3 (kood int primary key (check kood between 201 and 300, .... )
GO
create view TT as
select * from T1
union all
select * from T2
union all
select * from T3
GO

Saadud view TT ongi partitioned view. Päring:

select ... from TT where kood = 107

puudutab vaid tabelit T2 ja päring

select ... from TT where kood between 170 and 210

puudutab vaid tabeleid T2 ja T3.

Tavaliselt tükeldatakse tabel mingi kuupäeva järgi, siis saab lausa vanemad kirjed paigutada lugemiseks ainult (read only) failigruppi ja päringud lähevad kordi kiiremaks.

Seda vigurit õpetan ja kasutan ma juba ammu. Eelduseks on, et mul on osalistel tabelitel veerg, mille kaupa tükeldada ja sellel veerul on korrektne check-constraint, mis jaotab suure pildi mittekattuvateks ja kontrollitud osadeks.

Ja see eeldab, et andmemudel on minu käes ja minu kontrolli all. Aga kui ei ole?

Sattusin lahendama ülesannet, kus osalisi tabeleid on sadu, nende andmemudelit ma muuta ei saa ning nad on jaotatud ettevõtete ja aastate kaupa. Tabeli nimi määrab, millise ettevõtte ja millise aasta andmed seal on. Aruandluse jaoks on aga vaja teha vaade, mis haarab kokku andmed mõnedest või kõigist sellistest tabelitest.

Lihtsuse mõttes teen näited väikese arvu tabelitega: TabXX2012, TabXX2013, Tabxx2014, TabYY2012, TabYY2013, TabYY2014.

Kui ma panen kokku union view kujul

create view TabAll
as
select 'XX' as Ettevote, 2012 as Aasta, * from TabXX2012
union all
select 'XX' as Ettevote, 2013 as Aasta, * from TabXX2013
union all
select 'XX' as Ettevote, 2014 as Aasta, * from TabXX2014
union all
select 'YY' as Ettevote, 2012 as Aasta, * from TabYY2012
union all
select 'YY' as Ettevote, 2013 as Aasta, * from TabYY2013
union all
select 'YY' as Ettevote, 2014 as Aasta, * from TabYY2014

siis sellest pole palju kasu. Päring


select * from TabAll where Aasta = 2013

päringuplaan näitab, et läbi käiakse kõik tabelid. Päringu optimeeria “ei näe”, et aasta 2013 sialdub ainult kahes tabelis. Leidsin aga vahva lahenduse, kõnealune view tuleb teha pisut keerukam (neid viewsid teeb mulle 1 script – ise ma neid ei tee)

create view TabAll
as
select * from (select 'XX' as Ettevote, 2012 as Aasta, * from TabXX2012) T where Ettevote = 'XX' and Aasta = 2012
union all
select * from (select 'XX' as Ettevote, 2013 as Aasta, * from TabXX2013) T where Ettevote = 'XX' and Aasta = 2013
union all
select * from (select 'XX' as Ettevote, 2014 as Aasta, * from TabXX2014) T where Ettevote = 'XX' and Aasta = 2014
union all
select * from (select 'YY' as Ettevote, 2012 as Aasta, * from TabYY2012) T where Ettevote = 'YY' and Aasta = 2012
union all
select * from (select 'YY' as Ettevote, 2013 as Aasta, * from TabYY2013) T where Ettevote = 'YY' and Aasta = 2013
union all
select * from (select 'YY' as Ettevote, 2014 as Aasta, * from TabYY2014) T where Ettevote = 'YY' and Aasta = 2014

Selline view töötab samamoodi nagu “klassikaline” partitioned view. Päring

select * from TabAll where Aasta = 2012

puudutab vaid tabeleid TabXX2012 ja TabYY2012. Saladus peitub selles where lauses. Päringu optimeerija näeb where lauses olevat predikaati ja oskab optimeerida nii, et pöördutakse vaid nende osade poole, kus where predikaadid “kattuvad”.

Lisan peagi ka jätkupostituse, kus kirjeldan, kuidas sellist viewd ja tehnikat veel ära kasutada saab.

Posted in MS SQL Server | Tagged | Lisa kommentaar

Jätkuks koordinaatide teisendamisele

Lubasin oma koordinaatide teisendamise loos (paar postitust tagasi) lubasin panna üles ka näidistabeli ja jutustada pisut koordinaatide tagasiteisendamisest. Päevast sai küll 10 päeva, aga parem hilja kui mitte kunagi J

Tegin teise tabeli, kus veergudes [Pikkus] ja [Laius] on koordinaadid kujul 26°41’47.240″E ja 58°14’13.493″N.

Tabelis on ka mõned teisendusvalemid, mida kasutasin, et saada koordinaadid kümnendkujule veergudes [EPikkus] ja [NLaius]: 26,69646 ja 58,23708. Funktsiooniga =RADIANS saab need teisendada radiaanideks – radiaanidega arvutavad igasugused siinused ja koonused. Veergudes [ERPikkus] ja [NRLaius] ongi vastavalt arvud 0,047062 ja 1,016429.

Arvutamiseks on vaja kaks abimuutujat (nii tuleb koguvalem lühem)

[Bvm] =[ERpikkus]*0,854175858

[Pvm] =11473298 / POWER(TAN(PI()/4+[NRlaius]/2)/ POWER(TAN(PI()/4+ASIN(0,0818191909028683*SIN([NRlaius]))/2);0,0818191909028683);0,85417585805)

Ning lõpuks siis põhjakoordinaat [North] ning isakoordinaat [East] ristkoordinaatides valemitega:

[North] =4020205-[Pvm]*COS([Bvm])+6375000

[East] =[Pvm]*SIN([Bvm])+500000

Kust need numbrid seal valemites ja mis moodi see kokku pandud on – ära parem küsi, ega ma ise ka päris hästi aru ei saa. Peaasi et toimib. Lõpuks lisasin kontrolltabeli – ühe teisendusega ühtepidi, teisega tagasi ja siis tulemuse võrdlus. Nagu näha, on suurim viga 3 cm.

Tulemusele paigutasin ka ühe kaardi – toimib paraku vaid Excel 2013ga, seal saad erinevaid punkte kaardil vaadata. Muuda lehel Sheet3 tabelis numbrid nullist mingiks muuks ja “mummud” ilmuvad kaardile.

Näidis ise on aadressil: http://www.sarv.ee/ftp/henn/Excel – fail nimega KoordinaatideTeisendus.xlsx

Igasugune tagasiside on teretulnud.

Posted in Excel, Funktsioonid, Funktsioonid, Kaardirakendused, Koordinaatide teisendus | 1 kommentaar

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

Posted in Excel, Funktsioonid | Lisa kommentaar

Muuda oma Exceli-valemid loetavaks

Excelit kasutavad paljud ja ka valemeid Excelis

Enamik ei teadvusta, et valemite kirjutamine on ka programmeerimine ja seal kehtivad mõned programmeerimismaailma mängureeglid:

  • alguses lihtsad valemid kuid pikapeale lähevad need pikaks ja keeruliseks;
  • kunagi kirjutatud valemeid on vaja aegajalt muuta;
  • ammu kirjutatud valemist ei saa paraku täna enam midagi aru;
  • … ja palju muid sarnaseid programmeerimisega ühisjooni.

Exceli valemid on suhteliselt raskesti loetavad ja kui lähed neid muutma, siis kipuvad sulud ja funktsioonid siia-sinna sassi minema. Pikkadest ja keerukatest valemitest on mõni hea näide minu varasemates postitustes ja Excelis näevad nad sellised välja:

Aga ka lihtsamaid valemeid on keerukas lugeda:

Vaataks viimast valemit nüüd natuke teisel kujul:

Või seda esimest – hästikeerulist:

Selline pilt avaneb, kui lähed valemit muutma (F2) – aga valem paistab selliselt trepituna ka valemiribal (mis tuleb tõsi küll “laiemaks” venitada):

Kavalus on aga selles, et see “treppimine” tuleb valemisse sisse kirjutada.

Exceli valemisse võib suvalises kohas lisada:

  • tühikuid
  • reavahetusi (Alt-Enter)

Proovi ja naudi – ja ühel hetkel näed, kuidas valemid muutuvad lihtsamaks ja loetavamaks. Ja nii just keerulisemad valemid.

 

 

Posted in Excel, Funktsioonid | Lisa kommentaar

Kaardid ja koordinaatite teisendus Excelis

Eelmisel nädalal sai kõigile huvilistele kättesaadavaks Exceli uus lisa – GeoFlow. Võimalus koostada ajalis-ruumilisi diagramme ja aruandeid.

Juba mõnda aega on meil laual Excel 2013, kus kaks vahvat kaardirakendust (mis tuleb lihtsalt üles leida) – Bing Maps rakendus Excelile ja PowerView aruanded. Mõlemad lubavad kuvada numbrilisi andmeid kaardil diagrammidena.

Kõik kolm eeldavad, et aruande lähteandmetes on kohainfo – kas siis leitava nimena või koordinaatidena. Selle nimeleidmisega on nii nagu on – päris iga küla ja talu ei ole bingi kaardilt leitav, või tuleb natuke vaeva näha, et kohanimi kujutada täpselt leitavaks. Koordinaatidega on aga veel keerukam. Eesti kaardirakendustes, registrites ja muudes andmeallikates on ristkoordinaadid (L-Est’92), eelpoolmainitud (ja ka paljud teised) kaardirakendused eeldavad aga geograafilisi koordinaate (pikkus ja laiuskraade). Nende kahe omavaheline sidumine ja teisendamine on aga hulk keerukat matemaatikat. Põhjus on kera ja tasapinna erinevuses.

Maakera on enamvähem ümmargune. Kaardid aga reeglina tasapinnalised. Maapeal on lihtsam mõõta kaugusi meetrites. Merel aga tuleb leida pikkus- ja laiuskraadid.

Ma ei hakka kogu seda kaardimajandamise teooriat ja praktikat ümber jutustama – liiga pikk jutt ja ega ma väga asjatundja ka pole. Külla aga leidsin ma võimaluse Exclis tavaliste valemitega kirja panna mõlematpidine teisendus – valemid on üsna keerukad ja ega ma ise ka päris hästi aru ei saa, kuidas nad toimivad. Aga vähemalt tulemuse nad annavad ja minu arvutustes jäi edasitagasi teisendamisel viga kuhugi alla paari sentimeetri.

Teisendamisvahendeid on ka teisi – kõik on aga sellised “teenusepõhised”. Kui sul on suur hulk analüüsiandmeid ja neid oleks vaja kaardile või registrile sobivasse vormi panna, siis on raske leida kedagi vabatahtlikku kopipastejat, kes andmeid näiteks veebilehele ja sealt tagasi viitsiks kopeerida. Seepärast on valem palju tõhusam abivahend.

Koostasin valemid Exceli tabelivalemitena (A1 viidete asemel veerunimed). Samad valemid on (võib-olla mõne pisimuudatusega) kasutatavad ka andmebaasipäringutes (SQL) ja PoverPivoti andmemudelites (DAX)

Hakatuseks pisut siiski mõned põhiasjad

Ristkoordinaatides on meil valitud ühikuks meeter, Eesti on paigutatud ühe kokkuleppelise 400×500 km ristküliku keskele. Selle ristküliku alumine serv jookseb umbes Riia kohal ja ülemine pisut põhjapool Helsingit. Vasak serv on umbes Liepaja ja Ahvenamaa joonel, parem ülemine nurk on enamvähem Peterburi ja Viiburi vahel, parem alumine nurk on Pihkva oblastis (lähim linnake on Opochka). Ristkoordinaadid on alt üles (põhja poole) vahemikus 6300000-6700000 (vahe siis 400km) ning vasakult paremale (ida suunas) vahemikus 300000-800000 (vahe 500km).

Polaarkoordinaadid (need geograafilised laius ja pikkuskraadid) antakse ette nurgana. Mitu kraadi ekvaatorist põhja (või lõuna) poole – laiuskraad ja mitu kraadi nullmeridiaanist ida (või lääne) poole. Laiuskraadid on vahemikus 0-90 kraadi (põhja või lõunalaiust). Pikkuskraadid on vahemikus 0-180 kraadi (kas siis ida või lääne poole).

Kraadide ülesmärkimiseks on mitu erinevat süsteemi:

  • 56,80046 – see on 56 koma veel natuke kraadi (koma asemele kasutatakse enamasti punkti)
  • 56°48’1.641″ – see on 56 kraadi, 48 minutit ja 1 koma veel pisut sekundit (komakohana kasutatakse reeglina punkti)
  • 0,991354973 – see on peaaegu 1 radiaan. Kõikvõimalikud funktsioonid tahavad nurkasid saada just sellisel moel

Inimene kasutab enamasti seda kraadides märkimist, arvuti tahab arvutamiseks seda radiaanides arvu. Kõik kolm eelpooltoodud kuju on täpselt sama nurk

Segaduse vältimiseks lisatakse nurgale üks suunatähis:

  • 20°43’36.148″E 56°48’1.641″N – 20 kraadi ja natuke veel idapikkust ja 56 kraadi millegagi põhjalaiust

Mõnel pool märgitakse ka tähtede asemel pluss-miinus (pluss on põhja ja ida suunas, miinus on lõuna ja lääne suunas)

Meie neli ristküliku nurka on:

  • 20°43’36.148″E 56°48’1.641″N – see on see Liepaja juures, vasak alumine nurk (6300000    ja 300000)
  • 28°54’18.279″E 56°44’44.758″N – see on seal Pihkva oblastis, parem alumine nurk (6300000 ja 800000)
  • 29°26’1.813″E 60°19’33.148″N – see on seal Peterburi kandis, parem ülemine (6700000 ja 800000)
  • 20°22’22.929″E 60°23’11.147″N – ning see on vasakul üleval Ahvenamaa kandis (6700000 ja 300000)

Läheme nüüd selle teisendamise juurde. Tunnistan, valemid on keerukad, aga ka keerukaid valemeid saab kopida-pasteda. Kui kasutada tabelivalemeid, siis täidetakse nendega kohe terve veerg.

Tegin näidiseks kaks tabelit – LestGeo – ristkoordinaadid geograafilisteks ja GeoLest – vastupidiseks teisenduseks. Minu tabelis on katsetuseks mõned punktid algul ristkoordinaatidest geograafiliseteks ja siis vastupidi. Lisasin ka väikese kontrolltabeli, mis näitab selle edasitagasi teisenduse viga. Nagu eelnevalt mainisin – suurim viga oli 2cm suurusjärgus.

Tehtud tabelid panen ka üles oma “veebisaiti” – www.sarv.ee/ftp/henn/Excel – nimega Koordinaatide teisendus. Eks siis igaüks vaata, kas ja kuidas neid kasutada.

Nüüd väike seletus.

Esimene tabel LestGeo

  • Esimesed kaks veergu on ristkoordinaadid nimedega [North] ja [East]
  • Järgmine veerg [Pik] on pikkuskraad – arvutatud kogu selle mängu kõige lihtsama valemiga:
    • [Pik] =DEGREES(ATAN(([East]-500000)/(4020205+6375000-[North]))/0,85417585805)+24
  • Laiuskraadi arvutamiseks on lisatud üks vahemuutuja [Lvm] – ilma selleta oleks valem LIIGA keeruline (kuigi saaks)
    • [Lvm] =PI()/2-2*ATAN(POWER(SQRT(POWER([East]-500000;2)+POWER(4020205+6375000-[North];2))/11473298,03838;1,17071910962562))
  • Laiuskraadi enda saab siis valemiga:
    • [Lai] =DEGREES([Lvm]+0,003356551*SIN(2*[Lvm])+0,000006571*SIN(4*[Lvm])+0,000000017*SIN(6*[Lvm]))
  • Ülejäänud on juba vormistuslikud valemid
  • PikRound ja LaiRound on ümmardused tuhandik sekundi täpsuseks. Kes tahab võib seda ümmardust kohendada
    • [PikRound] =IF(Ümardus;ROUND([Pik]*3600000;0)/3600000;[Pik])
    • [LaiRound] =IF(Ümardus;ROUND([Lai]*3600000;0)/3600000;[Lai])
  • Veerud Pikkus ja Laius on teisendus kraadikujul esitamiseks
    • [Pikkus] =INT([PikRound])&”°”&INT(MOD([PikRound]*60;60))&”‘”&INT(MOD([PikRound]*3600;60))&”.”&MID(([PikRound]-FLOOR.PRECISE([PikRound];1/3600))*3600+0,0000000001;3;3)&””””
    • [Laius] =INT([LaiRound])&”°”&INT(MOD([LaiRound]*60;60))&”‘”&INT(MOD([LaiRound]*3600;60))&”.”&MID(([LaiRound]-FLOOR.PRECISE([LaiRound];1/3600))*3600+0,0000000001;3;3)&””””
  • Viimased kaks veergu on sidumiseks GoogleMapi ja BingMapiga. Esimene sobib Googlist, teine nii Googlist kui Bingist otsimiseks:
    • [KoosGoogle] =[@Pikkus]&”E “&[@Laius]&”N”
    • [KoosBing] =SUBSTITUTE([Lai];”,”;”.”)&”, “&SUBSTITUTE([Pik];”,”;”.”)

Kogu tabel mõnede kontrollpunktidega on siis umbes selline:

North East Pik Lvm Lai PikRound LaiRound Pikkus Laius KoosGoogle KoosBing
6540115

500000

24

1,026777

59

24

59

24°0’0.000″ 59°0’0.000″ 24°0’0.000″E 59°0’0.000″N 59.0000001345021, 24
6540117

500100

24,00174

1,026777

59,00002

24,00174

59,00002

24°0’6.264″ 59°0’0.059″ 24°0’6.264″E 59°0’0.059″N 59.0000163768951, 24.0017399671282
6540544

557470,2

25

1,026777

59

25

59

25°0’0.000″ 59°0’0.000″ 25°0’0.000″E 59°0’0.000″N 59.0000001345021, 24.9999999999415
6387062

653237

26,56321

1,002261

57,59956

26,56321

57,59956

26°33’47.559″ 57°35’58.424″ 26°33’47.559″E 57°35’58.424″N 57.5995621225819, 26.5632107320944
6419843

647076

26,48052

1,007446

57,89575

26,48052

57,89575

26°28’49.866″ 57°53’44.700″ 26°28’49.866″E 57°53’44.700″N 57.8957500370986, 26.4805183004908
6458323

658345

26,69646

1,01342

58,23708

26,69646

58,23708

26°41’47.240″ 58°14’13.493″ 26°41’47.240″E 58°14’13.493″N 58.2370812881714, 26.6964555842604
6462151

648773

26,53608

1,01408

58,27478

26,53608

58,27478

26°32’9.880″ 58°16’29.194″ 26°32’9.880″E 58°16’29.194″N 58.2747759845022, 26.5360776428389
6300000

300000

20,72671

0,988276

56,80046

20,72671

56,80046

20°43’36.148″ 56°48’1.641″ 20°43’36.148″E 56°48’1.641″N 56.8004559657799, 20.7267078817767
6300000

800000

28,90508

0,987319

56,74577

28,90508

56,74577

28°54’18.279″ 56°44’44.758″ 28°54’18.279″E 56°44’44.758″N 56.7457659806527, 28.9050774349304
6700000

800000

29,43384

1,049993

60,32587

29,43384

60,32587

29°26’1.813″ 60°19’33.148″ 29°26’1.813″E 60°19’33.148″N 60.3258745054734, 29.4338368664975
6700000

300000

20,37304

1,051054

60,38643

20,37304

60,38643

20°22’22.929″ 60°23’11.147″ 20°22’22.929″E 60°23’11.147″N 60.3864297768834, 20.3730358932703

Viimased neli punkti on Eesti aluskaardi nurgad

Proovisin saadud punkte nii GoogleMapis kui BingMapis ja kuvasin nad ka BingMap rwakenduses Excelis:

Esialgu jätan pooleli. Homme lisan ka teise poole – kuidas Geograafilised koordinaadid “meie” koordinaatideks teisendada. Peale seda panen ka näidised veebi üles nagu lubatud.

Posted in Excel, Funktsioonid, Kaardirakendused, Koordinaatide teisendus | Lisa kommentaar