Yearly Archives: 2013

Kuukausien lukumäärä

Leave a reply

Kuinka monta kuukautta on kahden päivämäärän välillä?

Ohessa kaava, joka laskee täysien kuukausien lukumäärän (lukumäärä siis pyöristetään alaspäin).

Huomaa, että tulossolu on muotoiltava numeroksi tai muotoon yleinen.

=JOS(PÄIVÄ(B1)>=PÄIVÄ(A1);0;-1)+(VUOSI(B1)-VUOSI(A1))*12+KUUKAUSI((B1)-KUUKAUSI(A1))

=IF(DAY(B1)>=DAY(A1);0;-1)+(YEAR(B1)-YEAR(A1)) *12+MONTH(B1)-MONTH(A1)

Excelissä on myös piilotettu funktio PVMERO, (DATEDIF) jota voi käyttää.

Syntaksi: PVMERO(ens_pvm;viim_pvm;yksikkö)

Yksikkö:

y – kokonaisien vuosien määrä

m – kokonaisten kuukausien määrä

d – päivien määrä

md – Ens_pvm:n ja viim_pvm:n välisten kuukausien määrä. Päivämäärien päivillä ja vuosiluvuilla ei ole merkitystä

yd – Ens_pvm:n ja viim_pvm:n välisten päivien määrä. Päivämäärien vuosiluvuilla ei ole merkitystä.

Esim. joka palauttaa kahden päivämäärän välisen kuukausien määrän:

=PVMERO(A2;B2;”m”)

Office 2013 kaatuilee

Leave a reply

Office 2013 (Word, Excel, PowerPoint) ei käynnisty kunnolla, vaan kaatuu saman tien. Tämän kaatumisen voi aiheuttaa esimerkiksi apuohjelma, joka ei ole yhteensopiva kyseisen Office-ohjelmaversion kanssa.

Käynnistä tällöin ohjelma vikasietotilassa kirjoittamalla komentoriville

    winword /safe

tai

    excel /safe

(Windows 72:ssa: Käynnistä ja hakukenttään winword /safe).

Siirry tämän jälkeen asetuksiin, Tiedosto/Asetukset ja Lisäohjelmat (File/Options, Add-Ins).

Poista lisäohjelmat ja kokeile ohjelman käynnistämistä uudestaan.

 

Etunollien lisääminen makrojen avulla

Leave a reply

Hävinneet etunollat voidaan lisätä jälkikäteen helposti myös makroja käyttäen.

Ohessa esimerkki postinumeroista.
Olen jakanut toiminnon kahtia:
1. SUB-toimintosarja:
KorjaaPnro käy läpi valitun alueen FOR EACH … NEXT silmukassa ja kutsuu
2. Function-toimintosarjaa:
LisääEtuNollat, joka taas lisää tarvittavat etunollat sille annettuun merkkijonoon ja palauttaa korjatun postinumeron sitä kutsuvalle SUB-toimintosarjalle.

Sub KorjaaPnro()
 'valitaan ensin alue ja käynnistetään sen jälkeen makro
    Dim rngS As Range
    For Each rngS In Selection         'muotoillaan solu tekstiksi
       rngS.NumberFormat = "@"         'kuts.funkt.joka lisää tarvittavat
                                       'etunollat
       rngS = LisääEtuNollat(rngS.Value)
    Next
 End Sub
Function LisääEtuNollat(PNro As String) As String
 'funktion tarkistaa sille annetun merkkijonon pituuden Len -funktiolla
 'tämän jälkeen piituudesta riippuen lisätään etunollat
 'nollien lisäämisessä voidaan myös käyttää muotoa "'0000", eli heitto merkki 'ensimmäiseksi
      Select Case (Len(PNro))
        Case 1
          LisääEtuNollat = "0000" & PNro
        Case 2
          LisääEtuNollat = "000" & PNro
        Case 3
          LisääEtuNollat = "00" & PNro
        Case 4
          LisääEtuNollat = "0" & PNro
        Case Else
          LisääEtuNollat = PNro
        End Select
 End Function

Pikatoiminnot (Quick Steps)

Leave a reply

Outlook 2010 toi mukanaan Pikatoiminnot (Quick Steps). Se on näppärä työkalu viestien hallintaan.

Itse muokkaan viestejä aina tarpeen mukaan, laitan uusia ja poistan vanhoja tarpeettomia.

Kun napsautat laittamaasi Pikatoimintoa (Quick Step), siirrät viestin määrittelemääsi kansioon, lähetät sen eteenpäin tai esim. kopioit kalenteriin, ilman että sitä tarvitsee hiirellä raahata.

Määrittele Pikatoiminto (Quick Step) seuraavasti:

– Napsauta nuolta Pikatoiminto-ryhmän (Quick Step) alaoikealla (Pikatoimintojen hallinta).

– Valitse Uusi (New) ja haluamasi toiminto, esim. siirrä kansioon tai Mukautettu (Custom) kohdasta, jossa pääset määrittelemään tarkemmin haluamaasi toimintoa.

– Anna toiminnolle nimi ja valitse kohdekansio sekä määritä, haluatko merkitä sen luetuksi toiminnon yhteydessä.

Asetukset-painikkeen alta löydät tarvittaessa lisää määrityksiä kuten pikanäppäimen määritystoiminnolle jne.

Käytät Pikatoimintoa (Quick Step) valitsemalla viestin tai viestit jonka jälkeen napsauta luomaasi pikatoimintoa (Quik Step).

Kun Excel ei ymmärrä numeroita

Leave a reply

Joskus saattaa esiintyä sellainen tapaus, että esim. SUMMA (SUM) funktio (koskee myös muita funktioita) ei laske yhteen lukuja, vaikka kaava tyyppiä =A1+A2+… jne. edelleen toimii.

Tällöin luku saattaa olla sellaisessa muodossa että Excel tulkitsee sen tekstiksi ja solun muotoileminen numeroksi ei auta.

Ongelman korjaamiseksi on monta keinoa ja ohessa muutama, joita itse käytän usein.

– Uusimmissa Exceleissä aluetta valittaessa (numerot) saa toimintotunnisteen, josta valitaan vaihtoehto “muunna numeroiksi” (Convert to number)

– Valitse alue (numerot) ja muotoile valittu alue numeroksi. Tämän jälkeen kirjoita numero 1 johonkin tyhjään soluun ja kopioi se leikepöydälle.

Valitse alue, jossa juuri numeroiksi muotoilemasi luvut ovat ja valitse Liitä, Liitä määräten ja valintaikkunassa valitse Arvot ja Kerro (Paste, Paste Special – Values, Multiply).

– Valitse alue (numerot) ja muotoile valittu alue numeroksi. Kirjoita kaava uuteen sarakkeeseen ja kerro tämän solun arvon 1:llä.

Muita keinoja:

Funktio POISTA.VÄLIT (TRIM) poistaa välilyönnit.

POIMI.TEKSTI (MID) -funktiolla voit poimia merkkijonosta haluamasi merkit tietystä positiosta lähtien.

Funktio SOLU (CELL) kertoo mitä solussa on. Esim. SOLU(“tyyppi”;A1), palauttaa “h” jos solu on tyhjä, “o” jos solussa on teksti arvo ja “r” jos solussa jotakin muuta.

Vastaavat englanniksi =CELL(“type”;A1) “b” jos solu on tyhjä, “l” jos solussa tekstiä, “v” jos arvo.

Huomaa eri palautusarvot eri kieliversioissa.

Jos oikein muistan, niin joissakin Excel-versioissa oli käytettävä englanninkielisiä nimiä argumenteissa, jotta funktio toimisi, vaikka itse Excel olisikin suomenkielinen.

Jos desimaalierotin on pisteenä, Excel pyrkii muuttamaan arvon päivämääräksi (riippuu koneen maa-asetuksista).

Voit muuttaa Excelin tulkitsemaan pisteet desimaalierottimiksi Excelin asetuksista. Tämän asetuksen voit tehdä tilapäisesti tietojen tuonnin tai liittämisen ajaksi. Kun tiedot on tuotu onnistuneesti, muuta asetus takaisin ja sinulla on (toivottavasti) pisteet pilkkuina.

– Valitse Tiedosto/Asetukset, (File/Options).

– Valitse Lisäasetukset (Advanced).

– Ota valinta pois kohdasta “Käytä järjestelmän erottimia” (Use system separators) ja laita Desimaalierotin (Decimal separator) ruutuun piste ja muuta tarvittaessa myös Tuhaterotin (Thousend separator).

Exceliin saattaa tulla myös joskus koodi (esim. tuhat erottimena) joka on vaikea tunnistaa ja joka estää luvun muotoilemista numeroksi.
Eräs tällainen on mm ASCII koodi 160
Tapoja on monia mutta ehkä helpoin tapa päästä tästä eroon on seuraava:

1. Mene soluun jossa on tämä luku
2. Valitse kaavakentässä merkki (välilyönti) joka on lukujen välissä
3. Kopioi tämä ”väli” (Ctrl+C)
4. Valitse alue jonka haluat muuttaa
5. Valitse Aloitusvalintanauhan Etsi ja korvaa toiminto
6. Etsittävä ruutuun liitä (Ctrl+V) tämä kopioitu väli
7. Korvataan ruutuun älä laita mitään
8. Napsauta korvaa kaikki

Muutama käyttökelpoinen tekstifunktio

Leave a reply

Alla esimerkki muutamasta yleisesti ja usein käytetystä tekstifunktioista, joista on paljon apua.

VASEN (merkkijono, merkkien lukumäärä) (LEFT)
Poimii vasemmalta lukien annetun merkkimäärän merkkijonosta.

OIKEA (RIGHT)
Poimii oikealta laskettuna.

POIMI.TEKSTI(teksti; aloitusnro; merkkien lukumäärä) (MID)
Poimii merkkijonosta antamasi määrän merkkejä alkaen määrittämästäsi paikasta.

ISOT(teksti) (UPPER)
Muuntaa tekstin versaaleiksi.

PIENET(teksti) (LOWER)
Muuntaa versaalit gemenoiksi.

ERISNIMI(teksti) (PROPER)
Muuntaa versaaliksi tekstin ensimmäisen merkin ja kirjaimet, jotka ovat jonkin muun merkin kun kirjaimen jäljessä. Funktio muuttaa kaikki muut kirjaimet gemenoiksi.

POISTA.VÄLIT(teksti) (TRIM)
Poistaa kaikki välilyönnit paitsi yhden sanojen välistä.

KETJUTA (teksti1; teksti2…) (CONCATENATE)
Yhdistää merkkijonoja toisiinsa. Lyhempi tapa on käyttää merkkiä &.

PITUUS (teksti) (LEN)
Merkkijonon pituus.

Esimerkki yllä olevista funktioista

Tekstifunktioesimerkkejä

Tekstifunktioesimerkkejä

Inquire Excel 2013

Leave a reply

Löysinpä mielenkiintoisen apuohjelman Excel versiosta 2013, nimittäin Spreadsheet Inquiren.

Apuohjelma on käytössä Microsoft Office Professional Plus 2013-versiossa.

Ohjelma tuli tarpeeseen, kun jouduin työstämään vierasta työkirjaa, jossa oli paljon linkkejä taulujen välillä ja “epämääräisiä” kaavoja.

Itse koin hyväksi taulujen yhteyskartan ja tilaston kaavoista/nimistä, joissa oli viittausvirheitä tai ne viittasivat “metsään”.

Inquire valintanauha:

Inquire valintanauha

Inquire valintanauha

Workbook Analysis (Työkirjan analyysi)
– luo raportin, jossa näkyy tietoja työkirjasta ja sen rakenteesta

Työkirjan yhteenvetotietoa

Työkirjan yhteenvetotietoa

Raportin saa vietyä/tallennettua Excel-tiedostoon tarkempaa analyysiä varten.

Workbook Relationship (Näytä työkirjan yhteydet)
– kartta työkirjan riippuvuuksista

Worksheet Relationship (Laskentataulukon yhteys)
– kartta työkirjan taulujen yhteyksistä

Taulujen yhteydet

Taulujen yhteydet

Cell Relationship (Solujen yhteydet)
– kartta solun yhteyksistä

Compare Files (Vertaile tiedostoja)
– työkirja-tiedostojen vertailu, jossa näytetään kahden työkirjan väliset erot

Clear Excess Cell Formatting (Ylimääräisen solumuotoilun poistaminen)
– poistaa työkirjasta ylimääräiset muotoilut. Tämä saattaa pienentää työkirjan kokoa ja nopeuttaa sen käyttöä.

Workbook Passwords (Salasanojen hallinta)
– suojattujen kyselyiden salasanahallinta

Puuttuvat etunollat

Leave a reply

Jäivätkö etunollat pois kun toit tiedot Exceliin?
Ne saadaan takaisin erittäin kätevästi parilla funktiolla, jos tieto on määrämittaista.

Otetaan esimerkiksi postinumerot.
Excelissä on A-sarakkeessa seuraavanlaiset tiedot (etunollat puuttuvat) ja tiedon pituus pitää olla 5-merkkiä:
100
2200
16100

Rivillä 1 puuttuu 2 nollaa, rivillä 2 puuttuu 1 nolla ja rivi 3 on oikeanpituinen.

Monet ratkaisevat tämän sisäkkäisillä JOF (IF) lausekkeilla. Se voidaan tehdä yksinkertaisemmin TOISTA (REPT) ja PITUUS (LEN) funktioilla.

Kaava, jonka rakennan B-sarakkeeseen, on seuraavanlainen:

=TOISTA(“0”;5-PITUUS(A1))&A1
=REPT(“0”;5-LEN(A1))&A1

Eli: TOISTA-funktio toistaa tekstin “0” niin monta kertaa kuin se puuttuu merkkijonosta A1:ssä.
Tämä toistojen määrä lasketaan kaavalla 5-PITUUS(A1).
5 on merkkijonon maksimipituus. A1:n merkkimäärä saadaan funktiolla PITUUS(A1).
Näiden erotus kertoo, kuinka monta nollaa laitetaan lisää.
Kaikki tämä yhdistetään olemassa olevaan arvoon solussa A1 funktiolla &. Tämä “et” merkki on toiminnoltaan sama kun funktio KETJUTA (CONCATENATE), mutta helpompi ja nopeampi kirjoittaa kuin varsinainen funktio.

Lopputulos on B-sarakkeessa:
00100
02200
16100

Leikepöydän käyttö

Leave a reply

Aihe saattaa kuulostaa arkipäiväseltä, mutta monella pitämälläni kurssilla olen huomannut, että leikepöydän ominaisuudet ja liittämisasetukset saattavat olla hieman vieraita.

24 leikettä

Tämä toiminto toimii kopioitaessa MS Office -ohjelmien välillä.

Moni käyttää leikepöytää vain viimeisemmän leikkeen kopiointiin ja liittämiseen. Pompitaan sovelluksien tai asiakirjojen välillä, kun halutaan kopioida useampi tekstikappale tai taulukon osa.

Esimerkki

Halutaan kopioida useampi tekstikappale vanhasta asiakirjasta uuteen tai useampi kaaviokuva Excelistä esimerkiksi PowerPointiin. Kun leikepöydän tehtäväruutu avataan, voidaan kopioida yhteen menoon useampi kohta, maksimissaan 24 eri kohtaa, ja liittää ne kohdeasiakirjaan siinä järjestyksessä kun halutaan.

1. Avaa leikepöytä -tehtäväruutu (katso kuvaa)

Leikepöytä

Leikepöytä

2. Kopioi haluamasi kohdat.
3. Siirry asiakirjaan siihen kohtaan johon ne haluat (Word, Excel, PowerPoint tai Outlook-viesti).
4. Avaa leikepöydän tehtäväruutu, mikäli se ei ole avoinna.
5. Napsauta leikepöydän sitä leikettä, jonka haluat liittää.
6. Mene seuraavaan kohtaan ja liitä seuraava leike jne.  

Leikepöydän tehtäväruutu

Kun osoitat leikepöydällä olevaa leikettä, saat näkyviin nuolen, jonka avulla voit valita leikkeen liittämisen tai poiston leikepöydältä.

Tehtäväruudun ylälaidassa ovat painikkeet, josta voit liittää kaikki leikkeet tai tyhjentää kertaheitolla koko leikepöydän.
Alalaidassa taas löydät Asetukset -painikkeen (Options). Täältä voit määritellä leikepöydän käyttäytymistä säätelevät asetukset kuten Näytä Officen leikepöytä automaattisesti (Show Office Clipboard Automatically).  

Liitosasetukset  

Mitä liität ja mihin, vaikuttaa tarjolla oleviin asetuksiin.   Liitämisessä on kaksi tapaa.

– Viimeiseksi kopioidun leikkeen kohdalla voit valita Liitä, Liitä määräten ja sieltä valita liittämisasetukset.

– Liittäessäsi leikepöydällä olevaa leikkettä tai myös liittäessäsi viimeiseksi kopiotua saat liitetyn alueen alaoikeaan reunaan toimintotunnisteen, josta voit valita liittämisasetukset.
Kun viet hiiren osoittimen toimintotunnisteen päälle ja napsautat näkyvää nuolta saat liittämisasetukset.  

Kuvassa on Excelin kaaviokuvan liitosasetukset PowerPointiin (versio 2010).  

Liittämisasetukset

Liittämisasetukset

 
Ole tarkkana mitä liität ja miten, niin että ei tule tietovuotoja!

Jos liität esimerkiksi Excelin kaaviokuvan suoraan asiakirjaan tai esitykseen objektina, niin koko Excelin työkirja liitetään!

Liitosasetuksista löydät myös tavan saada vain teksti liitettyä ja päästä eroon linkeistä ym. Erityisen hyödyllinen kun kopioit Internetistä ja liität asiakirjaan.  
Word ja PowerPoint: valitse Liitä vain teksti (Keep Text Only).
Excel: Käytä kohteen muotoilua (Match Destination Formatting).
 Outlook: Säilytä vain teksti (Keep Text Only).
(Suomalaisissa termeissä erilainen käännös; Liitä vain teksti – Säilytä vain teksti)    

Kuvan pakkaus  

Huomaathan myös, että liitättäessäsi kuvia on syytä tarkkailla tiedoston kokoa. Kun olet liittänyt kuvan, valitse asianomainen kuva ja Kuvatyökalut, Muotoile -valintanauhan Säädä ‑ryhmässä toiminto Pakkaa kuvat (Picture Tools, Format, Adjust, Compress Pictures). Jos et tarvitse alkuperäisen kuvan tarkkuutta, voit mahdollisesti pienentää sitä avautuvasta valintaikkunasta. Tämä on tärkeää etenkin, jos olet liittämässä kuvaa sähköpostiin. Tuolloin yleensä riittää pienempi tarkkuus.  

Muista kuitenkin, että pakkaus huonontaa alkuperäisen kuvan laatua. Joskus on hyvä tallentaa alkuperäinen kuva erilliseksi tiedostoksi ennekuin alkaa kokeilla eri asetuksia.  

Jos olet muokannut kuvaa, rajannut tms., pakkaa kuvat ja valitse vaihtoehto Poista rajatut alueet, muutoin ne jäävät tiedostoon ja kasvattavat turhaan tiedostokokoa.  

Pakkausvalintaikkunan vaihtoehto: Käytä tiedoston tarkkuutta (Use dokument resolution) tarkoittaa oletusarvoisesti 220 ppi:tä, ellei sitä ole asetuksissa muutettu.  

Kun poistat valintaikkunan vaihtoehdon ”Käytä vain tässä kuvassa” (Apply only to this picture), ohjelma suorittaa pakkauksen asiakirjan kaikkiin kuviin. Tämän saman saat aikaiseksi tallennettaessa tiedostoa valinnalla Tallenna nimellä ja valitsemalla tallennusvalintaikkunan Työkalut, Pakkaa kuvat (Save as, Tools, Compress Pictures).

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.

PowerPointin kuvien kielen vaihtaminen

Leave a reply

Kuinka moni on tuskaillut kuvien eri tekstiosien kieliasetuksilla?

Itse olen monesti, mutta nyt sain ainakin omissa kuvissani kieliasetukset toimimaan seuraavalla pienellä makrolla. Makro käy läpi kaikki aktiivisen esityksen kuvat ja niiden tekstiruudut (shapes) ja muuttaa kieleksi us english.

Jos kieli halutaan uk englanniksi tai suomeksi, kommentoidaan us englanti -rivi ja poistetaan kommenttimerkki (‘) sen rivin kohdalta, mihin kieleen halutaan vaihtaa.

Esim. halutaan vaihtaa kieli suomeksi: poista kommenttimerkki tältä riviltä koodissa
‘sh.TextFrame.TextRange.LanguageID = msoLanguageIDFinnish

Sub ChgLng() 
'change language to us/uk- english/finnish
    Dim sld As Slide
    Dim sh As Shape
        On Error Resume Next
        For Each sld In ActivePresentation.Slides
            For Each sh In sld.Shapes
                'us englanti
                sh.TextFrame.TextRange.LanguageID = msoLanguageIDEnglishUS
                'uk englanti
                'sh.TextFrame.TextRange.LanguageID = msoLanguageIDEnglishUK
                'suomi
                'sh.TextFrame.TextRange.LanguageID = msoLanguageIDFinnish
            Next
        Next
End Sub

Makron kopioit PowerPoint moduuliin seuraavasti:

Valitse koodi.
Paina Ctrl + c (kopioi).
Paina Alt + F11.
Valitse Insert – Module.
Paina Ctrl + v (liitä).

Suorita koodi esimerkiksi seuraavasti: laita osoitin koodin ja paina F5.

Suorita koodi toisessa esityksessä:

Pidä avoinna esitys, johon koodin kopioit, ja avaa esitys, jota haluat muuttaa.
Valitse Näytä – Makrot, Makrot (View – Macros, Macros).
Avautuvasta valintaikkunasta valitse makrojen lähteeksi Kaikki avoimet esitykset ja tämä kyseinen makro ChgLng.

Taulujen välinen laskenta PowerPivotissa

Leave a reply

Halutaan poimia tietoja toisesta taulusta, johon on muodostettu yhteys (relation).

Tällöin voidaan käyttää funktiota RELATED, jonka syntaksi on RELATED(Sarakenimi).

Esimerkissä on kaava:

    =RELATED(Products[ProductName])

Se poimii tuotetaulusta tuotteen nimen.

Tämän saisi myös haettua LOOKUPVALUE-funktiolla, mutta tämän kaava on huomattavasti pitempi ja aikaavievämpi kirjoittaa:

    =LOOKUPVALUE(Products[ProductName];Products[ProductID];’Order Details'[ProductID])

 

Toisessa esimerkissä lasken, mitä tuotteen myynti olisi listahinnalla, eli kerron tilatun kappalemäärän tuotteen listahinnalla:

    =’Order Details'[Quantity]*RELATED(Products[UnitPrice])

Tässäkin käytän RELATED-funktiota, koska taulujen välillä on yhteys (Order Details ja Products).

 

Huomaa myös, että kirjoittaessasi kaavoja PowerPivotissa teet sen kaavakentässä. Muista kaikki sulut! PowerPivot ei korjaa kaavoja samalla tavalla kun varsinainen Excel.

 

Taulujen liitokset/suhteet

 

Taulujen yhteyksiä pääset tutkimaan PowerPivot ikkunan Aloitus – valintanauhan Näytä ryhmän Kaavionäkymä -työkalun avulla (Home, View, Diagram View).

 

Jos yhteyksiä on, voit kaksoisnapsauttaa yhteysviivaa ja pääset tarkastelemaan yhteyttä.

 

Takaisin normaalinäkymään pääset napsauttamalla Tietokantanäkymä-työkalua, Näytä ryhmässä (Data View).

 

Jos yhteyksiä ei ole määritelty, voit itse määritellä taulujen yhteydet PowerPivotissa.

Siirry PowerPivotin Rakenne (Design) -valintanauhaan.

Suhteet ryhmässä on kaksi työkalua, Luo suhde ja Suhteiden hallinta, joilla määrittelet ja tarkastelet taulujen välisiä liitoksia/suhteita (Create Relationships, Manage Relationships).

 

Taulujen ominaisuus (Table Properties) -työkalulla hallitset suodatusta ja pääset määrittelemään SQL-lauseketta jolla tietoja poimitaan (Vaihda kohteeseen – Kyselyeditori, Switch to – Query Editor)

PowerPivot – tietojen tuonti

Leave a reply

PowerPivot on käytännöllinen, kun tietomäärä on suuri ja tarvitaan useampi taulu samanaikaisesti analysoitavaksi. PowerPivotista teet edelleen Pivot-taulukon kädenkäänteestä.

PowerPivotiin voit teoriassa tuoda taulun, jossa on enintään 1 999 999 997 riviä!

PowerPivot tuli Excelin versioon 2010 ja se on Microsoftin ilmainen lisäohjelma.

Löydät versiot 2013 ja 2010 ladattavaksi näistä linkistä:
Suomeksi:
http://office.microsoft.com/fi-fi/excel/powerpivotin-lataaminen-HA101959985.aspx

Englanniksi:
http://office.microsoft.com/en-us/excel/download-powerpivot-HA101959985.aspx

 Taulujen tuonti

Avaa PowerPivot ikkuna (PowerPivot, PowerPivot Window).

Valitse ulkoinen tietolähde (External data):
tietokannasta (SQL-Server, Access, Analysis service), muista lähteistä (Oracle, Teradata, Sybase jne.), (From Database, From Other Sources).

Tuonti Accessista:

Etsi tietokanta napsauttamalla avautuvassa valintaikkunassa painiketta Selaa (Browse).
Seuraavassa valintaikkunassa voit joko kirjoittaa itse SQL-lauseen, jolla tiedot tuodaan,
tai valita vaihtoehdon, jolla valitset taulut suoraan tietokannasta. Tämä vaihtoehto on oletuksena. Napsauta seuraava painiketta.

 Valitse avautuvasta valintaikkunasta taulut, jotka haluat tuoda. Valintaikkunassa on myös painike Valitse liittyvät taulukot (Select Related Tables), joka valitsee valittujen taulujen yhteyksiin perustuvat muut taulut, ja painike Esikatsele ja suodata (Preview & Filter), joka näyttää esikatselunäkymän valitusta taulusta sekä mahdollistaa suodatuksen.

 Napsauta Valmis (Finish).

 

Taulujen valinta

Taulujen valinta

Tuonnista näytetään tilanneikkuna, joka kertoo, miten tuontiprosessi edistyy ja kuinka monta tietuetta tuotiin. Kun tuonti on valmis, sulje ikkuna.

Tuodut taulut näkyvät PowerPivot ikkunassa eri välilehdillä.

Tuonti SQL-Serveristä:

Valitse SQL-Server ulkoiseksi tietolähteeksi.
Kirjoita serverin nimi tai valitse se listasta.
Määrittele kirjautumistunnukset.
Valitse tietokanta.

Tietokannan määrittely

Tietokannan määrittely

Tämän jälkeen toimi samalla tavalla kun Accessista tuotaessa.

 

 

TÄMÄ.PÄIVÄ ja kaavan optimointi

Leave a reply

Tarvittiin listaan sarake, jossa lasketaan aina tietyn päivä erotus tähän päivään nähden.

Nopeasti ajateltuna sen tekisi esim. näin

    =TÄMÄ.PÄIVÄ()-A3

jos päivämäärä on solussa A3.

 

Jos listassa on kuitenkin paljon rivejä, tällainen kaava on Excelissä hidas.

Funktio TÄMÄ.PÄIVÄ (TODAY()) on niin sanottu Volatile-funktio, joka lasketaan aina uudestaan kun Excel suorittaa laskutoimituksen ja näin ollen saattaa hidastaa Exceliä.

 

Toinen tapa, tässä tapauksessa edullisempi laskennan kannalta, olisi esim. näin:

Kirjoitetaan funktio TÄMÄ.PÄIVÄ erilliseen soluun, esim. B1, ja sitten kirjoitetaan kaava, joka viittaa tähän soluun:

    =$B$1-A3.

 

Jos työkirjan käsittely on hidasta, voi myös asettaa laskutoimituksen manuaaliseksi, ja kun muutokset on tehty, sitten suorittaa laskutoimituksen.

Laskutoimitusasetukset löytyvät kohdasta Kaavat – Laskutoimitus, Laskennan asetukset (Formulas –Calculation, Calculation Options).

 

Virheiden hallinta

Leave a reply

ON ERROR

Virhehallinta otetaan käyttöön ON ERROR GOTO xxx -komennolla.

xxx on nimi, johon siirrytään virheen tapahtuessa.

Esim.:

ON ERROR GOTO VirheHallinta

Virheenhallintarutiini on usein proseduurin lopussa ja kirjoitetaan muodossa:

VirheHallinta:

Huomaa kaksoispiste.

Virhehallinnasta palataan takaisin ohjelmaan komennolla: ON ERROR RESUME tai ON ERROR RESUME NEXT.

ON ERROR RESUME palauttaa ohjelman sille riville joka aiheutti virheen kun taas ON ERROR RESUME NEXT palauttaa seuraavalle riville siitä rivistä, joka aiheutti virheen.

Virhehallinta palautetaan Excelille komennolla ON ERROR GOTO 0 minkä jälkeen Excelin normaali virhehallinta on taas käytössä.

Voit poistaa ja ottaa käyttöön myös sisäiset virheilmoitukset komennolla:

APPLICATION.DISPLAYALERTS = FALSE ja APPLICATION.DISPLAYALERTS = TRUE

Käytä tätä harkitusti ja jos käytät, tarkista että virhehallintasi todella toimii!

Muista laittaa DISPLAY ALERTS TRUE kun et sitä enää tarvitse.

Ennen virhehallintariviä kirjoita EXIT SUB jos kyseessä SUB-proseduuri tai EXIT FUNCTION jos kyseessä FUNCTION-proseduuri. Näin vältytään päätymästä virhehallintaan jos ohjelma suoritetaan loppuun virheittä.

Esimerkki:

Virheen hallinta

Virheen hallinta

 

Sisällysluettelo Wordiin

Leave a reply

Sisällysluettelo Wordiin

 

Kirjoita tekstisi ja siihen liittyvät otsikot.

Määrittele otsikot eri otsikkotasoiksi tyyleillä (Aloitus, Tyyli (Home, Styles)).

Valitse otsikko ja sen jälkeen valitse otsikkotaso tyyleistä.

Siirry kohtaan, johon haluat sisällysluettelon.

Tarvittaessa tee osanvaihdot (Sivuasettelu, Sivun asetukset, Vaihdot: Seuraava sivu (Page Layout, Breaks: Next Page)).
Näin saat sisällysluettelolle eri ylä-/alatunnisteen ja erilaiset sivunumerot kuin mitä sinulla on muussa tekstissä.

Valitse Viittaukset, Sisällysluettelo, Sisällysluettelo (References, Table of Contents, Table of Contents) ja haluamasi sisällysluettelon tyyli.

 

Jos myöhemmin päivität tekstissä otsikkoja tai lisäät/poistat otsikkoja, saat sisällysluettelon päivitettyä menemällä luetteloon ja painamalla F9-funktionäppäintä.

 

Lisää otsikkotasoja

 

Otsikkotasoja on 9 ja oletuksena näkyy kolme.

Muita tasoja voit käyttää esim. seuraavasti:

Paina Ctrl + Vaihto + S tai valitse Käytä tyylejä (Apply Styles) ja Tyyligallerian alin vaihtoehto, kun se on avattuna.

Valintaikkunassa näkyy se otsikkotaso, joka sinulla on tekstissä valittuna. Valitse avautuvasta listaruudusta otsikkotaso. Voit tarvittaessa muuttaa tässä listaruudussa otsikkotason numeroa haluamaksesi tasoksi ja tämän jälkeen napsauta Käytä (Apply).

 

Numeroidut otsikot

 

Jos haluat otsikoihin numerot, esim. 1, 1.1, 1.1.1, 1.2 ja niin edelleen, toimi seuraavasti:

Siirry johonkin määrittelemääsi otsikkoon tekstissäsi.

Valitse Aloitus, Kappale, Monitasoinen luettelo (painike Kappale-ryhmässä), ja avautuvasta listasta vaihtoehto, jossa on otsikko numeroiden perässä.

 

 

Päivämäärämuotoiluja

Leave a reply

Voit muotoilla päivämääriä monella eri tavalla Excelissä.
Löydät muotoilut esimerkiksi painamalla Ctrl+1 (Muotoile solut).
Siirry avautuvassa valintaikkunassa Luku-välilehdelle ja valitse Luokka Oma.
(Valmiita päivämäärämuotoiluja löytyy myös kohdasta Päivämäärä.)
Jos valittuna on solu, jossa sinulla on päivämäärä, näet esimerkin kohdassa Malli.
Laji-kohtaan voit määritellä esitysmuodon.
Alla olevassa kuvassa on esimerkkejä muotoilukoodeista, rivillä 3 ja rivillä 4 koodin esitysmuoto.
p = päivä, k = kuukausi, v = vuosi.
Englanninkielisissä asetuksissa D = Date, M = Month, Y = Year

Päivämäärämuotoiluja

Päivämäärämuotoiluja

Kalenteri

Seuraavassa esimerkissä on luotu Excelissä kalenteri. Siinä hyödynnetään päivämäärämuotoilua, ehdollista muotoilua (Conditional Formatting) ja funktioita VIIKONPÄIVÄ (WEEKDAY), VIIKKO.NRO (WEEKNUM) ja JOS (IF).

Kalenteri

Kalenteri

Soluun A3 on kirjoitettu aloituspäivä, joka on muotoiltu muotoon pp.kk.vv
Solussa A4 on kaava =A3+1 (lisätään yksi päivä edelliseen päivämäärään, muotoiltu pp.kk.vv).
Solussa A4 kaava on kopioitu niin pitkälle alas kun päivämääriä halutaan.
Soluun B3 on laitettu kaava =A3 ja kopioitu alas. Muotoilu on ppp.
Soluun C3 on laitettu kaava =JOS(VIIKONPÄIVÄ(A3;2)=1;VIIKKO.NRO(A3;2);””) joka tutkii mikä viikonpäivä on kyseessä. Jos se on maanantai, viikkonumero kirjoitetaan soluun, muussa tapauksessa ei mitään.

VIIKONPÄIVÄ-funktio palauttaa päivän järjestysnumeron ja argumentilla 2 kerrotaan että viikko alkaa maanantaista. Eli jos funktio palauttaa 1, niin kyse on maanantaista ja tällöin kirjoitetaan soluun viikon numero.

VIIKKO.NRO-funktion argumentit ovat mistä päivästä lasketaan viikkonumero ja miten vuoden ensimmäinen viikko käsitellään.

Tämän jälkeen on valittu koko alue, esimerkissä kohdasta A3 aina kalenterin loppuun D302 ja valittu
Aloitus – Ehdollinen muotoilu, Uusi sääntö (Home – Conditional Formatting, New Rule).

Valintaikkunasta valitaan Määritä kaavan avulla, mitkä solut muotoillaan (Use a formula to determine which cells to format).

Kirjoitetaan kaava =VIIKONPÄIVÄ($A3;2)>5 kohtaan Muotoile arvot, joissa tämä kaava on tosi: (Format values where this formula is true:)
ja valitaan haluttu muotoilu kohdasta Muotoile (Format).

Huomaa kaavan lukitus! Sarake A lukitaan, koska päivä määritellään sarakkeen A:n mukaan.

Lopuksi napsautetaan OK:ta, jotta päästään takaisin laskentatauluun.