Vahel võib mõni asi lihtne olla

Äsja seisin silmitsi ühe sageli ettetuleva probleemiga.

Tabelis (Excelis) on koodid ja tooted ja miskid müügiandmed. Toodetel on kood ja nimetus, vaja oleks pivooti kokku võtta. Ainult et – sama koodiga toodet on vahel minetatud ühtemoodi (“Valged Hiired”) vahel teist moodi (“Hiired (valged)”). Kui pivooti panna ridadesse koodid, on kõik valged hiired kokku arvatud, aga kole, kui panna nimetus, on ilus, aga hiired eraldi ridades.

Seekord hästi lihtne valem, mis selle probleemi kõrvaldab. Selleks teed ühe lisaveeru oma tabelisse (näiteks [Ühine Nimetus]) ja kirjutad sinna sellise valemi (eeldame, et tootekood on veerus [Kood] ja nimetus veerus [Nimetus]).

=INDEX([Nimetus];MATCH([@Kood];[Kood];0))

Lihtne ja toimib

Henn

Posted in Excel | Lisa kommentaar

Kuidas arvutada Lihavõtteid

Paari kuu pärast tuleb jälle hakata pühadeks mune värvima. Heal perenaisel on kokku kogutud hulk sibulakoori, aga suur osa inimesi ei mäleta, millal on Lihavõtted e Ülestõusmispüha, millal Suur Reede (siis ju töövaba päev) ja ammugi, et millal Vastlapäev (näe jäigi sel aastal liug laskmata) või Suvistepüha.

Et elu lihtsamaks teha (ja ikka Exceli abiga) õpetan täna, kui imelihtne on arvutada igaks aastaks Lihavõtteid.

Piisab, kui kirjutad kolm “lihtsat” valemit:

[@temp] =MOD(INT((LEFT([@Aasta];2)-15) / 2)+202-11*MOD([@Aasta];19)-MID(“111111111111222220″; (FIND(“*”&LEFT([@Aasta];2);”*21*24*25*27*28*29*30*31*32*34*35*38*33*36*37*39*40*”&LEFT([@Aasta];2))+2)/3;1);30)

[@tA]=[@temp]+21-IF([@temp]=29;1; IF([@temp]=28;IF(MOD([@Aasta];19)>10;1;0);0))

[@tE] =MOD(20-MOD([@tA]-19;7)-MID(“0135″; MOD(40-LEFT([@Aasta];2);4)+1;1)-MOD(MOD([@Aasta];100)+INT(MOD([@Aasta];100)/4);7);7)+1

Neile kes Excelit uut moodi – tabelina – ei mõista väike seletus

    Esimese valemi (alates =-märgist) paned lahtrisse ja [@Aasta] asemele pistad aastaarvu või viite lahtrile, kus aastaarv

    Teise valemi (alates =-märgist) mõnda teise lahtrisse ja viitad eelmisele ja nii edasi

Neile, kes Excelit tabelina oskavad, lihtsam seletus

    Tee omale tabel, kus veerud Aasta, temp, tA, tE ja Lihavõtted

    Aastad pista esimesse veergu

    Näidatud valemid kirjuta (alates võrdusmärgist) vastavasse tabeliveergu

Lisavõtted saad siis arvutada sellise valemiga

[@Lihavõtted] =DATE([Aasta];3;[@tE]+[@tA])

Ja niisama lihtne see ongi. Soovi korral võid veel veerge lisada

[SuurReede] = [Lihavõtted]-2

[Vastlapäev] = [Lihavõtted]-47

[Nelipüha] = [Lihavõtted] +49

Väike hoiatus ka – see arvutus annab õige tulemuse ainult aastani 4000. Kui see mõnele probleem, siis peab parema lahenduse otsima. Aastal 4000 tuleb niiehknii midagi ette võtta, kuna rooma numbrid saavad siis otsa ja Vatikan ei saa enam oma kalendreid trükkida.

PS. Ma mõtlesin lihavõtteks ka üheainsa valemi kirjutada, aga see on juba keeruline (lihtsamat ma ei oska või ei viitsi teha):

[Lihavõtted] =DATE([@Aasta];3;MOD(20-MOD(MOD(INT((LEFT([@Aasta];2)-15) / 2)+202-11*MOD([@Aasta];19)-MID(“111111111111222220″; (FIND(“*”&LEFT([@Aasta];2);”*21*24*25*27*28*29*30*31*32*34*35*38*33*36*37*39*40*”&LEFT([@Aasta];2))+2)/3;1);30)+21-IF(MOD(INT((LEFT([@Aasta];2)-15) / 2)+202-11*MOD([@Aasta];19)-MID(“111111111111222220″; (FIND(“*”&LEFT([@Aasta];2);”*21*24*25*27*28*29*30*31*32*34*35*38*33*36*37*39*40*”&LEFT([@Aasta];2))+2)/3;1);30)=29;1;IF(MOD(INT((LEFT([@Aasta];2)-15) / 2)+202-11*MOD([@Aasta];19)-MID(“111111111111222220″; (FIND(“*”&LEFT([@Aasta];2);”*21*24*25*27*28*29*30*31*32*34*35*38*33*36*37*39*40*”&LEFT([@Aasta];2))+2)/3;1);30)=28;IF(MOD([@Aasta];19)>10;1;0);0))-19;7)-MID(“0135″; MOD(40-LEFT([@Aasta];2);4)+1;1)-MOD(MOD([@Aasta];100)+INT(MOD([@Aasta];100)/4);7);7)+1+MOD(INT((LEFT([@Aasta];2)-15) / 2)+202-11*MOD([@Aasta];19)-MID(“111111111111222220″; (FIND(“*”&LEFT([@Aasta];2);”*21*24*25*27*28*29*30*31*32*34*35*38*33*36*37*39*40*”&LEFT([@Aasta];2))+2)/3;1);30)+21-IF(MOD(INT((LEFT([@Aasta];2)-15) / 2)+202-11*MOD([@Aasta];19)-MID(“111111111111222220″; (FIND(“*”&LEFT([@Aasta];2);”*21*24*25*27*28*29*30*31*32*34*35*38*33*36*37*39*40*”&LEFT([@Aasta];2))+2)/3;1);30)=29;1;IF(MOD(INT((LEFT([@Aasta];2)-15) / 2)+202-11*MOD([@Aasta];19)-MID(“111111111111222220″; (FIND(“*”&LEFT([@Aasta];2);”*21*24*25*27*28*29*30*31*32*34*35*38*33*36*37*39*40*”&LEFT([@Aasta];2))+2)/3;1);30)=28;IF(MOD([@Aasta];19)>10;1;0);0)))

Posted in Excel | Lisa kommentaar

Valimistulemus kvootides

Kuulan ajakirjanduslikku analüüsi valimistulemustest ja siin on üks pisike nüanss, mida ajakirjanikud kipuvad unustama ja no ega veebilehed seda viitsi pakkuda ka. Nimelt ei ole korrektne kõrvutada eri ringkondadest korjatud hääli, kuna ringkondade koguhääl on erinev. Õigem oleks võrrelda ringkonnakvoote.

Panin väikese analüüsi aadressile

http://bit.ly/ValmisedKvootides

Siin on lehel “sheet3″ valimiskomisjoni lehelt nopitud andmetega valitute nimekiri järjestatud kvootides (erinevus valimiskomisjoni tabeliga on see, et siia on lisatud ka kompensatsioonimandaadid arvutatuna kvoodiks. Kõrval on ka väike tabel ringkonnakvoodi suurusega (ei pruugi olla täpne, on arvutatud, kuna valimiskomisjoni lehelt kvoodi suurust ei leidnud). Kvoot saadakse kui ringkonnas antud häälte arv jagada ringkonna mandaatide arvuga + 1 (minu mäletamist mööda).

Lehel “sheet4″ on kaks pivooti – ühes erakondade kogutud kvoodid, teises seesama ringkondade kaupa.

Kokku tuli neid “kvoote” pisut üle 57 – see tähendab siis, et niipalju hääli-kvoote kogusid riigikokku (esialgu) pääsenud. Ülejäänud “44 kvooti” siis läks asendusliikmetele ja väljajäänutele.

Et ikka Excel süüdi oleks

Henn

Posted in Excel | Lisa kommentaar

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 | 1 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