Monthly Archives: July 2013

Windowsin rekisterin hyödyntäminen

Leave a reply

Silloin tällöin tulee tilanne, että luodessamme uutta asiakirjaa tarvittaisiin edellisestä asiakirjasta viimeiseksi käytettyä tietoa, esimerkiksi juokseva numero Excelissä tai asiakirjan juokseva numero Wordissa.

Tapoja on useampia. Eräs on Windowsin rekisterin hyödyntäminen, jos työ suoritetaan aina samalla koneella.

Jos sitä vastoin tarvitaan enemmän joustavuutta, voitaisiin arvo tallentaa esimerkiksi tekstitiedostona johonkin sitä varten luotuun kansioon.

Windowsin rekisterin hyödyntäminen

Excel-esimerkki:
GetValue hakee arvon rekisteristä ja SaveValue tallentaa.

Sub SaveValue()
    Dim lngArvo As Long
    On Error GoTo ErrH:
    lngArvo = Range("A1")
    SaveSetting appname:="Minun_sovellus", section:="Alustus", Key:="ViimNro", setting:=lngArvo
    Exit Sub
ErrH:
    MsgBox "Solussa A1 on oltava numeerinen arvo", vbExclamation + vbOKOnly, "Virheellinen arvo"
End Sub

Sub GetValue()
    Range("A1") = GetSetting(appname:="Minun_sovellus", section:="Alustus", Key:="ViimNro") + 1
End Sub

Word esimerkki:

Word-asiakirjassa käytetään kirjanmerkkiä AsNro, joka on valmiina asiakirjassa. Kirjoitetaan siihen arvo, joka poimitaan rekisteristä. Lisäyksen jälkeen arvo tallennetaan rekisteriin uudestaan.

Sub AsetaNro()
    Dim lngArvo As Long
    On Error GoTo ErrH
    Selection.GoTo What:=wdGoToBookmark, Name:="AsNro"

    lngArvo = GetSetting(appname:="Minun_sovellus", Section:="Alustus", Key:="ViimNro") + 1
    Selection.TypeText Text:=lngArvo
    SaveSetting appname:="Minun_sovellus", Section:="Alustus", Key:="ViimNro", setting:=lngArvo
    Exit Sub
ErrH:
    MsgBox "Kirjanmerkkiä AsNro ei löytynyt.", vbExclamation + vbOKOnly, "Asiakirjavirhe"
End Sub

Omat rivi- ja sarakeotsikot

Leave a reply

Omat rivi- ja sarakeotsikot
Jos käytät usein samoja rivi- sarakeotsikoita, voit tallentaa ne Excelin omiin listoihin.

Kun tarvitset niitä, kirjoita soluun listaelementti ja tämän jälkeen vedät täyttökahvasta haluttuun suuntaan ja saat tallennetun listan soluihin.

Esimerkki:
Luodaan lista, jossa on elementit Turku, Helsinki ja Espoo.

Valitaan Tiedosto – Asetukset, Lisäasetukset, Muokkaa omia luetteloita (File – Options, Advanced, Edit Custom Lists…). (Kohta löytyy valintaikkunan alaosasta.)

Napsauta Uusi Luettelo (New List), Kirjoita listan ensimmäinen elementti Turku), paina Enter, kirjoita toinen elementti (Helsinki) ja paina Enter, toista kunnes olet kirjoittanut kaikki elementit ja napsauta lopuksi Lisää (Add).
Napsauta OK:ta kunnes oleta taas laskentataulussa.

Jos sinulla on jo työkirja, jossa otsikot ovat valmiina, voit myös tuoda ne valintaikkunan Tuo (Import) painikkeen avulla.

Käyttö:
Kirjoita listan jokin elementti soluun, isoilla ja pienillä kirjaimilla ei ole väliä.
Ota täyttökahvasta kiinni ja vedä haluttuun suuntaan solujen yli ja lista ilmaantuu soluihin.

Oman listan käyttö

Oman listan käyttö

Oman listan käyttö lajittelussa 

Näin luotua listaa voit hyödyntää myös lajittelussa.

Osoitin soluun lista-alueella ja valitse Tiedot – Lajittele ja suodata, Lajittele (Data – Data Tools, Sort).
Valitse lajitteluperusteeksi sarake, ja listaruudussa Järjestä (Order) valitse Mukautettu luettelo (Custom List…)
Valitse haluttu lista lajitteluperusteeksi. Voit avautuvassa valintaikkunassa tarvittaessa myös luoda uuden listan, poistaa vanhan jne.
Lista lajitellaan oman listan mukaan. Jos lista-alueella on tietoja, joita ei ole sinun omassa listassasi, lajitellaan ne sinun listasi jälkeen joko nousevassa tai laskevassa järjestyksessä riippuen valinnastasi.

Jos valitsit lajitteluperustaksi esimerkkilistan Turku, Helsinki, Espoo ja listassa, jota lajittelet, esiintyy myös paikkakunnat Pori ja Iisalmi, lista lajitellaan muotoon Turku, Helsinki, Espoo, Iisalmi, Pori.

Suomenkieliset kuukaudet englanniksi

Leave a reply

Itselleni tulee aika usein esiin tilanteita, että jo tehdystä taulusta tulisi muuttaa kuukauden nimet suomesta englanniksi, esimerkiksi kun olen ryhmitellyt Pivot-taulukossa päivämäärät kuukausiksi.

Tein makron, jonka suoritan aina tarvittaessa.
Tallensin makron omaan apuohjelma-makrotyökirjaan, jonka avaan aina tarvittaessa.

Alla kaksi esimerkkiä makrosta.
Ensimmäinen on (Sub Replace_Fin_Months_With_Eng), jossa valitsen ensin alueen ja sitten suoritan makron. Toinen on (Sub Replace_Fin_Months_With_Eng_v2), jossa makro kysyy muunnettavaa aluetta.
Molemmissa makroissa käytän FOR EACH NEXT silmukkaa, jolloin vältyn hitaasta SELECT-käskystä.

Sub Replace_Fin_Months_With_Eng()
'2013 / BI
'Replace finnish month namnes with english
'Select first cells and then run makro
    Dim c As Range
    For Each c In Selection
        Select Case c.Value
            Case "tammi"
                c.Value = "Jan"
            Case "helmi"
                c.Value = "Feb"
            Case "maalis"
                c.Value = "Mar"
            Case "huhti"
                c.Value = "Apr"
            Case "touko"
                c.Value = "May"
            Case "kesä"
                c.Value = "Jun"
            Case "heinä"
                c.Value = "Jul"
            Case "elo"
                c.Value = "Aug"
            Case "syys"
                c.Value = "Sep"
            Case "loka"
                c.Value = "Oct"
            Case "marras"
                c.Value = "Nov"
            Case "joulu"
                c.Value = "Dec"
        End Select
    Next
End Sub

Sub Replace_Fin_Months_With_Eng_v2()
'2013 / BI
'Replace finnish month namnes with english
'Makro that asks what range should be converted

    Dim c As Range
    On Error Resume Next
    Set c = Application.InputBox("Valitse alue joka muunnetaan", _
        "Kuukausien vaihto", , , , , , 8)
    On Error GoTo 0
    If Not c Is Nothing Then
        c.Select
        For Each c In Selection
            Select Case LCase(c.Value)
                Case "tammi"
                    c.Value = "Jan"
                Case "helmi"
                    c.Value = "Feb"
                Case "maalis"
                    c.Value = "Mar"
                Case "huhti"
                    c.Value = "Apr"
                Case "touko"
                    c.Value = "May"
                Case "kesä"
                    c.Value = "Jun"
                Case "heinä"
                    c.Value = "Jul"
                Case "elo"
                    c.Value = "Aug"
                Case "syys"
                    c.Value = "Sep"
                Case "loka"
                    c.Value = "Oct"
                Case "marras"
                    c.Value = "Nov"
                Case "joulu"
                    c.Value = "Dec"
            End Select
        Next
    End If
End Sub

Erkoissuodatus

Leave a reply

Erikoissuodatus

Voit etsiä ja poimia tietoja listasta monella eri tavalla. Yksi tapa on hyödyntää erikoissuodatusta. Erikoissuodatus vaatii oman ehtoalueen, johon voit rakentaa erittäin monimutkaisia ehtoja, joiden avulla poimit tietoja. Voit myös tehdä ja käyttää laskennallisia kenttiä ehtojen asettelussa.

Luomisehtoja voit myös hyödyntää tietokantafunktioilla. Erikoissuodatuksen löydät Tiedot-valintanauhan Lajittele ja suodata -ryhmästä olevasta Lisäasetukset-toiminnosta. (Data – Sort & Filter. Advanced).

Huomaa: jos haluat kopioida ehtoja vastaavat tiedot toiseen tauluun, aloita siitä taulusta jonne haluat lopputuloksen.

Sinulla voi myös olla useampi ehtoalue. Silloin ne kannattaa nimetä ja lista kannattaa määritellä taulukoksi, jolle antaa nimen (Aloitus – Tyyli, Muotoile taulukoksi (Home – Styles, Format as Table)).

Vierekkäiset ehdot muodostavat JA-ehdon ja allekkaiset TAI-ehdon. Ehtojen kenttänimien täytyy täsmätä listan kenttänimiin ja itse kopioin ne useimmiten välttyäkseni kirjoitusvirheiltä.
Alla muutama yksinkertaisempi ehto ja sitten pari laskentakenttää ehtoina.

Lista josta esimerkki suodatukset tehdään:

Lista josta suodatukset tehdään

Lähde

Kaikki tanskalaiset:

Country
=Denmark

Huomaa, että joissakin tapauksissa Excel saattaa antaa virheellisen tuloksen jos kirjoitat soluun vain ehdon. Tämän takia olen kirjoittanut sen kaavaan ja ehdon merkkijonona =”=Denmark” Näin pyrin varmentamaan, että Excel tulkitsee ehtoni oikein.

TAI-ehto
Kaikki tanskalaiset tai ranskalaiset:

Country
=Denmark
=France

Tai-ehdossa kirjoitetaan ehdot allekkain.

JA-ehto
Kaikki tanskalaiset JA jossa tilauspäivä on joko 16.1.2013 tai uudempi. Huomaa >= -merkkien järjestys.

Country Order Date
=Denmark >=16.1.2013

JA-ehdossa kirjoitetaan ehdot vierekkäin.

Tanskalaiset JA jossa tilauspäivä on joko 16.1.2013 tai uudempi TAI sitten kaikki tilaukset jossa hinta suurempi kuin 10,50.

Country Order Date Price
=Denmark >=16.1.2013
10,50

Laskennallinen kenttä
Kun käytetään laskennallista kenttää, otsikot jätetään pois ja kaavassa viitataan listan/kannan ensimmäiselle riville suhteellisella viittauksella. Ehtoalueeseen on kuitenkin sisällytettävä tyhjät solut ehtojen yläpuolelle.

Näytä kaikki tilaukset joissa toimituspäivä on tyhjä ja tilauspäivästä kulunut yli 10 päivää verrattuna tähän päivään. TODAY = TÄMÄ.PÄIVÄ

 
TRUE FALSE

Kaavat ovat seuraavat:

=TODAY()-C11>10 =D11=””

Tietokantafunktiot

Tietokantafunktioilla voidaan suorittaa monenlaisia yhteenvetolaskelmia listoista moninaisilla ehdoilla. Ehtoalue kannattaa pitää erillään listasta, niin että ehdot on helppo muuttaa ja ennen kaikkea näet suoraan yhdellä silmäyksellä mitä funktiot laskevat. Ehtoalue voi sijaita omassa taulussaan erillään koko listasta.

Funktioiden syntaksi on:
FUNKTIO(Tietokanta;Sarake;Ehdot)
Tietokantafunktiot ovat luokassa Tietokanta (Database) ja alkavat kirjaimella T, englanniksi D.

Tietokanta on joko alue, nimetty alue tai lista joka on määritelty taulukoksi.   Sarake on se sarake jota halutaan käyttää, joko sarakeindeksinä tai nimenä kirjoitettuna lainausmerkeissä.

Ehdot ovat erillinen ehtoalue, joka voi koostua useammasta sarakkeesta ja rivistä. Ehtoalueessa on listan/tietokannan sarakeotsikko ja sen alapuolella ehto. Vierekkäiset ehdot muodostavat JA (AND) ehdon ja allekkain olevat TAI (OR) ehdon.

Esimerkiksi lasketaan yhteensä tammikuun tilausten arvo
Ehto:

Order Date Order Date
>=1.1.2013 <1.2.2013

Kaava soluun johon haluat summan:
=TSUMMA(A8:I20;”Price”;A1:B2)
=DSUM(A8:F20;”Price”;A1:B2)

Huomaa että laskettavan sarakkeen voi antaa joko lista-alueen sarakeindeksinä (numero) tai sarakkeen nimi merkkijonona (”” -merkkien sisälle).   Jos olet käyttänyt pakollisia rivivaihtoja (Ctrl + Enter) listan sarakeotsikoissa, on helpompaa käyttää sarakeindeksiä. Tällöin kaava olisi muotoa:
=TSUMMA(A8:F20;6;A1:B2) jossa numero 6 on sarakkeen positio listassa.

Aputoiminnosta löydät esimerkkejä muista tietokantafunktioista.