Monthly Archives: April 2013

Eri sivunumero asiakirjan eri osiin

 

Esimerkkinä toimikoon asiakirja, jossa on kansisivu, sisällysluettelo ja varsinainen sisältö.

Kansisivussa ei saa olla sivunumeroa. Sisällysluettelossa sivunumerot ovat muotoa i, ii, iii ja niin edelleen.

Varsinaisen asiakirjan sisällön sivunumerointi alkaa 1:stä.

Avain onnistumiseen on asiakirjan jako osiin. Toiminto sijaitsee valintanauhassa Sivun asettelu (Page Layout) ja ryhmässä Sivun asetukset (Page Setup).

Toiminto on nimeltään Vaihdot (Breaks) ja sen alta avautuvasta valikosta Osanvaihdot (Section Breaks).

Eli:

Kirjoita kansisivu ja valitse Sivun asettelu/ Sivun asetukset, Vaihdot – Seuraava sivu (Page Layout/Page Setup, Breaks – Next Page).

Laita esim. sisällysluettelon otsikko paikalleen ja paina Enteriä.

Valitse Sivun asettelu/ Sivun asetukset, Vaihdot – Seuraava sivu.

Kirjoita asiakirjan varsinainen teksti.

Siirry sisällysluettelo-sivulle ja lisää sisällysluettelo (Viittaukset, Sisällysluettelo – Sisällysluettelo (Refernces, Table of Contents – Table of Contens)

Siirry sisällysluettelon ylätunnisteeseen, esim. kaksoisnapsauttamalla asianomaista kohtaa.

Poista Linkki edelliseen (Link to Previous) napsauttamalla ao. toimintoa Ylä- ja alatunnistetyökalujen valintanauhan Rakenne (Design) ryhmässä Siirtyminen (Navigation).

Laita kohdistin kohtaan johon haluat sivunumeron ja valitse Sivunumero, Nykyinen sijainti, Normaali numero (Ryhmässä Ylä- alatunniste) (Header & Footer, Page Number, Current Position, Plain Number).

Valitse näin lisätty sivunumero ja valitse Sivunumero, Muotoile sivunumero (Fotmat Page Numbers).

Valintaikkunassa valitse haluttu lukumuotoilu ja Aloittava numero (numero, mistä sivunumerointi alkaa).

Siirry varsinaisen tekstin ylätunnisteeseen.

Poista Linkki edelliseen (Link to Previous) napsauttamalla ao. toimintoa: Ylä- ja alatunnistetyökalut, Rakenne, Siirtyminen ryhmässä (Header & Footer Tools, Design, Navigation).

Valitse sivunumero ja valitse Sivunumero, Muotoile sivunumero (Page Number, Format Page Numbers).

Valintaikkunassa valitse haluttu lukumuotoilu ja Aloittava numero (numero, mistä sivunumerointi alkaa).

 

Samalla tavalla voit luoda omat ylätunnisteet esimerkiksi lähdeluettelosivuille, kuvaluetteloille ja niin edelleen.

Linkki artikkeliin sivunumeroista

Infoa käyttäjää ohjelman edistymisestä

Kolme esimerkkiä informoida käyttäjää miten ohjelma edistyy.
Kahdessa ensimmäisessä käytän tilariviä (StatusBar) ja kolmannessa käytän valintaikkunaa.

Koodi lukee soluja ja kertoo kuinka ohjelma edistyy.
Tilarivillä voit esittää esim. laskurilla tai tekstillä missä mennään.

Application.StausBar = ”Teksti tai luku jota näytetään”

Tilarivin tyhjennät seuraavalla tavalla:

Application.StausBar = ””

Mikäli et tyhjennä tilariviä jää merkintä siihen kunnes tilariviä päivitetään uudelleen tai kun Excel käynnistetään uudestaan.

 Esimerkki 1

Tilarivillä kerrotaan laskurilla i, monesko solu menossa.

Sub NaytaEdistyminen()
    Dim i As Long                  'laskuri
    Dim s As Range                 'solu jota käsitellään
    Range(Selection, Selection.End(xlDown)).Select
    i = 1
    For Each s In Selection
        Application.StatusBar = i  'tilarivi kertoo laskurin avulla monennetta solua luetaan
       i = i + 1
        'Laita oma koodisi tähän
    Next
    Application.StatusBar = ""      'tilarivi tyhjennetään
End Sub

Esimerkki 2

Tilarivillä kerrotaan kuinka monta prosenttia on käsitelty.
Muuttuja i kertoo mitä rivi käsitellään ja muuttuja j kuinka monta riviä on alueessa. Kuinka monta prosenttia on luettu saadaan kaavalla i/j muotoiltuna prosentiksi.

Sub NaytaEdistyminen2()
    Dim i As Long        'laskuri
    Dim j As Long        'muuttuja joka sisältää alueen kokonaisrivimäärän
    Dim s As Range
    Range(Selection, Selection.End(xlDown)).Select
    j = Selection.Rows.Count       'lasketaan alueen rivit
    i = 1
    For Each s In Selection
        'lasketaan prosenteissa kuinka paljon tehty
        Application.StatusBar = Format(i / j, "0.00%") 
        i = i + 1
        'Laita oma koodisi tähän
    Next
    Application.StatusBar = ""      'tilarivi tyhjennetään
End Sub

Esimerkki 3

Käytetään lomaketta jossa kerrotaan missä mennään. Se voidaan asettaa keskelle näyttöä jolloin se varmasti huomataan.
Koodi on kuten esimerkissä kaksi paisti että edistymisen näyttämiseksi käytetään lomaketta.
Lokmake on Form joka on määritelty ei modaaliseksi (ShowModal = False). Lomakkeen nimeksi on annettu FrmEdistyminen.
Lisäksi lomakkeelle on määritelty kaksi Label –objeltia, yksi johon laitettu teksti ”Luetteu” ja toinen joka on nimetty lblNayta ja jota ohjelma päivittää.

Objektien fonttikokoa on myös laitettu isommaksi.

Sub NaytaEdistyminen3()
    Dim i As Long
    Dim j As Long
    Dim s As Range
    Range(Selection, Selection.End(xlDown)).Select
    j = Selection.Rows.Count
    i = 1
    FrmEdistyminen.Show      'tuodaan lomake näyttöön
    For Each s In Selection
                  'lomakkeen objektia (lblNayta) päivitetään
        FrmEdistyminen.lblNayta.Caption = Format(i / j, "0.00%") 
        i = i + 1
    Next
    Unload FrmEdistyminen    'suljetaan lomake ja poistetaan muistista
End Sub

Lomakkeen asetukset kuvana

Lomakkeen asetukset

Lomakkeen asetukset

 

PowerPivot päivitys

Automaattinen Pivot ja PowerPivot päivitys toimii Excel versiossa 2013.

Excel versioon 2013 on lisätty ominaisuus jolla päivitys voidaan hoitaa automaattisesti.

Jos se halutaan tehdä normaalissa moduulissa, lisätään seuraava koodirivi makroon:

ActiveWorkbook.Model.Refresh

Jos päivitys halutaan taas tehdä kun työkirja avataan se lisätään ThisWorkbook kohtaan Sub_ Workbook_Open:

Private Sub Workbook_Open()
     Me.Model.Refresh
End Sub

PowerPivot päivitys

Automaattinen Pivot ja PowerPivot päivitys toimii Excel versiossa 2013.

Excel versioon 2013 on lisätty ominaisuus jolla päivitys voidaan hoitaa automaattisesti.

Jos se halutaan tehdä normaalissa moduulissa, lisätään seuraava koodirivi makroon:

ActiveWorkbook.Model.Refresh

Jos päivitys halutaan taas tehdä kun työkirja avataan se lisätään ThisWorkbook kohtaan Sub_ Workbook_Open:

Private Sub Workbook_Open()
     Me.Model.Refresh
End Sub

Tyhjien solujen täyttö

Saat listan joka on suunnilleen alla olevan näköinen:

A

B

C

D

1 Alue Ryhmä Nimike Arvo
2 Suomi Helsinki Tuote 1

10

3 Tuote 2

15

4 Tuote 3

10

5 Turku Tuote 1

20

6 Tuote 2

25

7 Ruotsi Tukholma Tuote 2

30

8 Tuote 3

35

9 Tuote 4

30

Tästä halutaan tehdä yhteenveto Pivotilla mutta ensin on täytettävä sarakkeen A ja B tyhjät solut jotta se onnistuisi.

Lopputulos tulisi olla seuraavan lainen:

A

B

C

D

1 Alue Ryhmä Nimike Arvo
2 Suomi Helsinki Tuote 1

10

3 Suomi Helsinki Tuote 2

15

4 Suomi Helsinki Tuote 3

10

5 Suomi Turku Tuote 1

20

6 Suomi Turku Tuote 2

25

7 Ruotsi Tukholma Tuote 2

30

8 Ruotsi Tukholma Tuote 3

35

9 Ruotsi Tukholma Tuote 4

30

Tämä tehdään esimerkiksi seuraavalla tavalla:

  1. Valitaan sarake A ja B
  2. Valitaan Aloitus – Muokkaa – Etsi ja Korvaa, Siirry määräten (Home – Editing – Find & Select, Go To Special).
  3. Avautuvasta valintaikkunasta valitse vaihtoehto Tyhjät (Blanks) vasemmassa sarakkeessa ja napsauta OK. (Kaikki sarakkeen A ja B tyhjät solut ovat valittuna)
  4. Kirjoita kaava = ja paina nuoli ylöspäin. Esim. jos aktiivinen solu on B3 niin kaavaksi tulee =B2.
    Huomaa suhteellinen viittaus)
  5. Paina Ctrl + Enter (paina ensin Ctrl – näppäin alas ja tämän jälkeen painat Enter).
    Kaava kirjoitetaan kaikkiin tyhjiin valittuihin soluihin.

Solujen viittaukset

Kun kaavaa kopioidaan on viittaustavoilla merkitystä.

Yleensä kaavoissa on suhteelliset viittaukset (A1, B1 jne.), eli solujen osoitteet muuttuvat sitä mukaan kun kaavaa kopioidaan ja viittaukset pysyvät samassa suhteessa soluun jossa on kaava.

Heti kun lähdetään laskemaan esim. prosenttiosuuksia tai muuttamaan jotakin lukua kertoimella joka on aina samassa solussa, ja kaavaa halutaan kopioida, tulee eteen tarve muuttaa viittaustapaa esim. suoraksi ($A$1, $B$1 jne.).

Tämä muutos tehdään kätevämmin painamalla funktionäppäintä F4.

Eli kun kaavaa kirjoitetaan ja osoitin vilkkuu viittauksen perässä painetaan F4 viittaustavan muuttamiseksi. Kun painetaan toistuvasti F4:sta Excel muuttaa joka kerta viittaustapaa:

Ensimmäisen kerran F4:sta -> $A$1 (lukitaan koko solu)
Toisen kerran -> A$1 (lukitaan rivi)
Kolmannen kerran -> $A1 (lukitaan sarake)
Neljännen kerran -> A1 (suhteellinen)

Jos kaava on jo valmiina voit asettaa osoittimen sen viittauksen sisään tai heti perään jonka viittaustapaa haluat muuttaa ja painaa F4:sta.

Jos on useampi viittaus kaavassa, esim. alue jonka haluat “lukita” voit valita ne viittaukset kaavassa (maalata) ja sitten painaa F4:sta.

Esim.:

A

B

C

D

E

F

1

Sar 1

Sar 2

Yhteensä
Alue

Yhteensä yksittäiset solut

% osuus totaalista

2

Nimike 1

10

20

=SUMMA(B2:C2)

=B2+C2

=E2/$E$4*100

3

Nimike 2

15

25

40

40

57,14

4

Totaali

25

45

70

70

100,00

 

Kaavat sarakkeissa D, E ja F voidaan kopioida alas. Sarakkeessa F solu E4 on lukittu koska se on yhteinen kaikille kaavoilla asianomaisessa sarakkeessa. Jos viittausta ei lukittaisi jouduttaisiin kaava kirjoittamaan uusiksi joka riville.

Sarakkeiden D ja E kaavat eivät ole lukittuja koska siinä viittaus on aina muututtava vastaamaan asianomaista riviä, eli kaavasolua nähden viitataan aina samalla tavalla.

 

 

Tietojen ryhmittelyä

Tässä kun Pivot- ja PowerPivot kurssia valmistelen tuli taas esiin kysymys tietojen ryhmittelystä ryhmiin, ryhmiin joita ei ole lähdetiedoissa.

Alla kaksi eri esimerkkiä miten sen voisi ratkaista.

Esimerkissäni käytän Accessin Northwind-esimerkki kannasta ottamaani dataa.

Esimerkkidata

Esimerkkidata

Pivot esimerkki:

Haluan laskea myynnin maantieteellisesti jaettuna. Lähdetiedoissa on vain maiden nimet joten joutuisin tämän tekemään manuaalisesti pivotissa joka on hankalaa.

Eräs kiertotapa on että poimin kaikki yksilölliset maat eri tauluun ja laitan siihen mihin maantieteelliseen alueeseen ao. maa kuuluu.

Tietysti voin tämän tehdä suoraan lähdetietoonkin mutta tuhansien rivien päivitys manuaalisesti on hankalaa.

Joten poimin lähteestä yksilölliset maat seuraavasti:

1. Valitsen B-sarakkeen
2. Tiedot, Lajittele ja suodata, Lisäasetukset – Vain ainutkertaiset tiedot (Data, Sort & Filter, Advanced – Unique records only)
3. Kopioin tuloksen ja liitän sen uuteen taulukkoon
(Olisin kyllä voinut lähteä liikkeelle uudesta taulukosta ja erikoissuodatuksella saada sinne kaikki yksilölliset maat mutta tällä kertaa teen sen näin)
4. Määrittelen mihin alueeseen kukin maa kuuluu (manuaalisesti) uuden taulukon B -sarake.

Ryhmät

Ryhmät

5. Tämän jälkeen siirryn lähdetietoon ja luon sinne uuden sarakkeen PHAKU (VLOOKUP) kaavalla joka poimii luomastani uudesta taulusta ryhmän.

H-sarakkeen kaava: =PHAKU(B2;Groups!A:B;2;EPÄTOSI), (=VLOOKUP(B2;Groups!A:B;2;FALSE))

Jos taulu on määritelty Taulukoksi niin kaava kopioituu automaattisesti kaikille riveille.

Valmis data

Valmis data

Nyt voin ryhmitellä pivotissani tiedot tämän ryhmän mukaan.

 PowerPivot esimerkki:

Kopioin yksilölliset maat omaksi tauluksi PowerPivottiin ja lisäsin varisainaiseen tauluun uuden sarakkeen johon laitoin kaavan:

=LOOKUPVALUE(Ryhmat[Ryhmä];Ryhmat[Country];[Country])
(Ryhmat on uusi taulu jonka loin)

PowerPivot esimerkki

PowerPivot esimerkki

Kaava etsii maan luomastani taulusta ja palauttaa ryhmän johon ao. maa kuluu.
Funktion LOOKUPVALUE syntaksi on:
LOOKUPVALUE( <result_columnName>, <search_columnName>, <search_value>[, <search_columnName>, <search_value>]…)
result_columnName = sarake josta arvo palautetaan
search_columnName = sarake josta hakuarvo etsitään
search_value = arvo jota etsitään

PowerPivotista saat yksilölliset rivit esimerkiksi viemällä ne pivot-taulukoksi ja sitten kopioi tuloksen takasin uuteen taulukkoon PowerPivottiin.

 

Oma valintanauha (ribbon)

Kerää usein käyttämäsi työkalut samaan valintanauhaan ja helpotat ohjelman käyttöä.

Voit luoda Office-ohjelmiin (versio 2010/13) kätevästi omia valintanauhoja seuraavasti:

Valitse Tiedosto/Asetukset (File/Options)

Valitse Muokkaa valintanauhaa (Customize Ribbon) (1).

Napsauta vasemman sarakkeen alaosassa olevaa painiketta Uusi välilehti (New Tab) (2)

Vasempaan sarakkeeseen ilmestyy “Uusi välilehti (Mukautettu) (New Tab (Custom)) ja sen alle “Uusi ryhmä (Mukautettu)” (New Group (Custom)) (3).

Voit muuttaa molempien nimeä valitsemalla kohdan ja napsauttamalla painiketta Nimeä uudelleen (Rename) (4).

Näiden toimenpiteiden jälkeen voit etsiä komentoja oikeanpuoleisesta sarakkeesta ja lisätä niitä luomaasi välilehden ryhmään joko hiirellä raahaamalla tai valitsemalla ja napsuttamalla painiketta Lisää (Add) (5).

Oikean puoleiseen sarakkeeseen saat valittua näkyviin (suodatettua) jonkun tietyn välilehden komennot tai kaikki komennot. Voit tällä tavalla helpottaa komentojen löytämistä (6).

Huomaa että saat näkyviin myös niitä komentoja joita ei ole valintanauhoissa.

Voit siirtää luomasi valintanauhan paikkaa hiirellä raahaamalla kohtaan jonne sen haluat.

Komentopainikkeita saat pois valitsemalla asianomainen komento ja napsauta Poista (Remove).

Asetukset tallentuvat koneellesi sinun profiiliisi.

Tällä tavalla voit luoda ja muokata valintanauhoja kaikissa Office-ohjelmissa.

Valintanauhan muokkaus

Valintanauhan muokkaus

Kahden listan vertailu

Verrataan kahta listaa keskenään ja poimitaan toisesta listasta tietoja ensimmäiseen.
Tähän on kätevä käyttää funktiota PHAKU (VLOOKUP).
Esimerkissä poimitaan henkilön palkka palkat.xlsx –työkirjasta.
Kaava solussa C2 on =PHAKU(A2;[Palkat.xlsx]Palkkaus!$A:$C;3;EPÄTOSI).
=VLOOKUP(A2;[Palkat.xlsx]Palkkaus!$A:$C;3;FALSE)

PHAKU (VLOOKUP)

PHAKU (VLOOKUP)

Hakuarvo on tässä tapauksessa se arvo jonka perusteella toisesta taulukosta haetaan tietoja.
Taulukko_matriisi on se taulukko alue jonka vasemmanpuoleisesta sarakkeesta arvoa haetaan.
Sar_indeksi_nro on sen sarakkeen numero valitulla taulukkoalueella josta arvo poimitaan.
Alue_halu on epätosi koska halutaan löytää täsmällinen vastine.

Taulu josta haetaan tietoja kannattaa lajitella hakuarvon perusteella.

Mikäli tietoja ei toisesta taulusta löydy palauttaa funktio virhearvon #PUUTTUU! (#N/A).
Tämän saat pois seuraavalla tavalla kaavalla (Excel versiot 2007 ja uudemmat):
=JOSVIRHE(PHAKU(A2;[Palkat.xlsx]Palkkaus!$A:$C;3;EPÄTOSI);”Ei löydy”)
JOSVIRHE = IFERROR
EPÄTOSI = FALSE
Kaavan tekstin “Ei löydy” sijasta voit käyttää mitä tahansa haluamaasi tekstiä tai sitten “” jotta solu jäisi tyhjäksi.
=JOSVIRHE(PHAKU(A2;[Palkat.xlsx]Palkkaus!$A:$C;3;EPÄTOSI);””)

Jos käytössäsi on vanhempi Excel voit poistaa virhesanoman esimerkiksi seuraavanlaisella kaavalla:
=JOS(ONVIRHE(PHAKU(A3;[Palkat.xlsx]Palkkaus!$A:$C;3;EPÄTOSI));””;PHAKU(A3;[Palkat.xlsx]Palkkaus!$A:$C;3;EPÄTOSI)).
JOS = IF
ONVIRHE = ISERROR
Huomaa että tässä kaavassa haku suoritetaan kaksi kertaa joka tekee kaavasta hitaan.