Monthly Archives: February 2013

Tehosta Excel kaavoja

Leave a reply

Järkeä PHAKU (VLOOKUP) –funktion käyttöön.

Kun halutaan välttää PHAKU-funktion palauttama virhe, niissä tapauksissa kun hakuarvoa ei löydetä, käytetään usein rakenteeltaan seuraavan laista kaavaa:

=JOS(ONVIRHE(PHAKU(A1;Product!C1:AF1691;3;EPÄTOSI));””;PHAKU(A1;Product!C1:AF1691;3;EPÄTOSI))

Eli JOS (IF) ja ONVIRHE (ISERROR) funktiolla testataan palauttaako PHAKU virheen. Oheisessa esimerkkikaavassa PHAKU suoritetaan kaksi kertaa, mikäli arvo löydetään. Ensin tarkistetaan palauttaako virheen ja mikäli ei palauta virhettä suoritetaan haku uudestaan arvon palauttamiseksi.

Kaava voidaan optimoida käyttämällä JOSVIRHE (IFERROR) funktiota seuraavasti:
=JOSVIRHE(PHAKU(A1;Product!C1:AF1691;3;EPÄTOSI);””)
Nyt suoritetaan PHAKU vain kerran jolloin laskenta nopeutuu.

Kannattaa myös lajitella se sarake josta PHAKU-funktiolla haetaan.

Usein etsitään tietoja myös samasta taulusta samalla hakuarvolla useista sarakkeista PHAKU –funktiolla.
Edullisempi tapa saattaa usein olla PHAKU –funktion korvaaminen VASTINEN (MATCH) ja INDEKSI (INDEX) –funktiolla.
VASTINE palauttaa rivinumeron jolla hakuarvo sijaitsee ja INDEKSI –funktiolla poimitaan tieto halutusta sarakkeesta. Täten tehdään haku vain kerran.

Esimerkki:

Hakufunktiot

PowerPoint oletus mallipohja

Leave a reply

Oma mallipohja oletukseksi kun avaat PowerPointin (2010).

Saat vaihdettua PowerPointin oman valkoisen pohjan omaan malliisi seuraavalla tavalla:

  1. Avaa mallisi tai luo uusi
  2. Valitse tallenna nimellä
  3. Valitse tallennusmuodoksi malli (.potx)
  4. PowerPoint avaa automaattisesti kansion johon malli tallennetaan
    (C:\Users\Käyttäjänimesi\AppData\Roaming\Microsoft\Templates)
  5. Anna mallille nimeksi blank.pot

Seuraavan kerran kun avaat PowerPointin tämä malli aukeaa automaattisesti.

Muutama Outlook asetus (2010)

Leave a reply

Viestin saapumisilmoitus

Valitse millä tavoin Outlook ilmoittaa sinulle saapuneesta viestistä.
Sininen ilmoitusruutu alaoikealla saattaa olla joissakin tilanteissa häiritsevä ja näin saat sen pois:
(Alla olevat numerot viittaavat asianomaiseen kohtaan kuvassa).

Tiedosto, Asetukset, Sähköposti (1), Viestin saapuminen (2) (File, Options, Mail, Message arrival)

Jos otat merkinnän pois kohdasta Näytä työpöytäilmoitus (3) (Display a Desktop Alert) et saa sinistä saapumisilmoitusta ala oikeassa kulmassa.

Työpöytäilmoituksen asetukset (4)– painikkeesta (Desktop Alert Settings) voit säätää sen läpinäkyvyyttä ja miten pitkään se on näkyvillä.

Tyhjennä roskakori poistuessasi Outlookista

Saat Outlookin tyhjentämään roskakorin kun suljet ohjelman. Ohjelma kysyy vielä varmistuksen ennen tyhjentämistä.

Tiedosto, Asetukset, Lisäasetukset, Tyhjennä Poistetut-kansio, kun Outlook suljetaan (File, Options, Advanced, Outlook start and exit, Empty Deleted Items folder when exiting Outlook)

Kommenttimerkintä viestiin

Kun vastaat viestiin saattaa joskus olla tarvetta vastata itse saapuneen viestin tekstiin.
Tämä voi olla vaikeasta huomattavissa joten on hyvä merkata vastaus tai kommentti.
Voit asettaa Outlookissa asetuksen joka laittaa merkinnän vastaukseesi automaattisesti.

Tiedosto, Asetukset, Sähköposti (1), Vastaukset ja välitykset (5) (File, Options, Mail)
Rastita Lisää kommenttien alkuunk (6) (Preface comments with)
Voit muuttaa oletus kommenttimerkinnän niin halutessasi.

 Poissaoloilmoitus

Toimii vain jos on MS Exchange käytössä.
Tiedosto, Automaattiset vastaukset (Poissa) (File, Info, Automatic Replies (Out of Office)
Valitse Lähetä automaattiset vastaukset (Send automatic replies)
Valitse ajanjakso (voit määritellä myös päivän tulevaisuudessa)

Kirjoita poissaoloviestisi kohtaan Oman organisaation sisällä ja Oman organisaation ulkopuolella (Inside My Organization, Outside My Organization)
Napsauta OK.

Allekirjoitus

Voit luoda useita allekirjoituksia. Laita useimmiten käytetty oletukseksi.
Voit vaihtaa niitä helposti viestissä napsauttamalla hiiren oikeata hiiripainiketta allekirjoituksen päällä.
Saat listan allekirjoituksistasi josta voit valita sopivan.

Luo allekirjoitus:
Tiedosto, Asetukset, Sähköposti (1), Allekirjoitukset (7) (File, Options, Mail, Signatures…)
Napsauta Uusi (New)
Kirjoita allekirjoituksesi ja paina Tallenna (Save).

Luo muut mahdolliset muut allekirjoitukset

Kohdassa Valitse oletusallekirjoitus (Choose default signature) asetat sen allekirjoituksen oletukseksi jota useimmiten käytät, sekä uudet että vastatut.

Allekirjoituksen vaihto viestissä:
Osoita allekirjoitusta viestissäsi
Napsauta hiiren oikeanpuolista painiketta
Valitse allekirjoitus

Outlook asetuksia

Näyttökuvan lisäys asiakirjaan

Leave a reply

(Word, Excel, PowerPoint, Outlook versiot 2010).

Toiminto toimii samalla tavalla eri Office-ohjelmissa.

Toimi seuraavasti kun haluat lisätä ruutukaappauskuvan tai kuvaosan näyttöruudusta:

Ruutukaappaus (esimerkkinä Word)

  • Siirry Wordiin
  • Laita osoitin kohtaan johon kuvan haluat
  • Valitse Lisää, Näyttökuva, ja valitse kuva kohdasta Käytettävissä olevat ikkunat (Insert, Screenshot, Available Windows)
    (Huomaa että ikkuinat joita näytetään eivät saa olla pienennettyjä tilariville).
    (Huomaa että Wordissa tämä ei toimi jos sinulla on dokumentti yhteensopivuus tilassa (Compatibility mode). Muunna asiakirja ensin uuteen muotoon ja tee sitten uudestaan ruutukaappaus).
  • Muokkaa tarvittaessa kuvaa kuten Rajaa ja Pakkaa kuvat (Crop, Compress Picture)

Kuva osasta näyttöä (esimerkkinä Outlook)

  • Aloita uusi viesti
  • Siirrä osoitin viestin tekstiosaan
  • Pienennä Outlookin ikkuna niin että aloittamasi viestin alle jää se osa ikkunasta josta haluat kuvan
  • Valitse Lisää, Näyttökuva (Insert, Screenshot, Screen Clipping)
  • Viesti pienenee jotta sen alla oleva kuva näkyisi ja näyttö muuttuu harmaaksi
  • Pidä hiiren vasen painike alhaalla ja valitse alue josta haluat kuvan
  • Vapauta hiiren painike ja kuva on viestissäsi.

Joka toinen rivi eriväriseksi

Leave a reply

Joka toinen rivi eriväriseksi
Valitse alue
Valitse Aloitus, Tyyli, Ehdollinen muotoilu, Uusi sääntö (Home, Styles, Conditional Formatting, New Rule)
Valitse valintaikkunasta Määritä kaavan avulla, mitkä solut muotoillaan (Use a formula to determine which cells to format)
Kirjoita kaava =JAKOJ(RIVI();2) ruutuun Muotoile arvot, jossa tämä kaava on tosi (Format values where this formula is true). Englanniksi =MOD(ROW();2)
Napsauta OK.
Kaavassa funktio RIVI() (ROW()) palauttaa rivinumeron jokaiselle riville valitulla alueella. Rivinumero jaetaan 2:lla ja tarkistetaan jakojäännös, funktio JAKOJ() (MOD()). Jos jakojäännös on 0 niin ollaan parillisella rivillä, muuten parittomalla.

Mallivastaus sähköpostiin (Pikaosien hyödyntäminen)

Leave a reply

Kun haluat käyttää usein samaa vastausta sähköpostissa tallenna se Pikaosiin.

Kirjoita ensin haluttu teksti viestiin ja sen jälkeen tallenna se pikaosiin.

Pikaosiin viennin jälkeen voit uusiokäyttää tallennetun tekstin muissa viesteissäsi.

  • Avaa uusi viesti
  • Kirjoita haluamasi teksti
  • Valitse kirjoitettu teksti
  • Valitse valintanauha Lisää, ryhmästä Teksti komento Pikaosat ja Tallenna valinta pikaosavalikoimaan

Lisää pikaosa

  • Kirjoita kuvaava nimi Nimi-ruutuun

Rakenneosan nimi

  • Napsauta OK.

Käyttö:

  1. Luo uusi viesti
  2. Laita osoitin kohtaan johon tekstin haluat
  3. Valitse Lisää – Pikaosat
  4. Valitse haluamasi pikaosa.

Esimerkkejä

Leave a reply

Seuraavassa kahdessa esimerkissä käydään läpi kaikki taulut ja ensimmäisesssä suojataan ne ja toisessa puretaan suojaus.

Sub ProTectSh()
‘käy läpi jokaisen taulun ja suojaa sen ilman salasanaa
    Dim ws
    For Each ws In Worksheets
        ws.Protect Password:=””
    Next
    MsgBox “Done”, vbOKOnly, “End of process”
End Sub

 Sub UProTectSh()
‘käy jokaisen taulun läpi ja purkaa sujauksen, ilman salasanaa
    Dim ws
    For Each ws In Worksheets
        ws.Unprotect Password:=””
    Next
    MsgBox “Done”, vbOKOnly, “End of process”
End Sub

Alla oleva esimerkki estsii Excelissä viimesien käytetyn rivin A-sarakkeesta ja kertoo sen MsbBoxissa.
Cells(Rows.Count, 1) ← numero kertoo sarakkeen (1 = A).

Sub FindLastRow()
Dim LastRow As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox LastRow
End Sub

 

Kopiointia Excelissä

Leave a reply

Tiedon/kaavan kopiointi

Muut tavat kuin Ctrl+c ja Ctrl+v

1) Täyttökahvasta vetäminen:

Ota solun täyttökahvasta kiinni ja vedä (huomaa hiiriosoittimen muoto)

Solun kopiointi

 

 

 

 

 

2) Täyttökahvan kaksoisnapsautusNapsauta täyttökahvaa kaksi kertaa niin tieto/kaava kopioituu niin pitkälle alas kun naapurisolussa on tietoa.

3) Ctrl +Enter

Valitse solut

Kirjoita tieto/kaava

Paina Ctrl + Enter

Tieto tai kaava kopioituu kaikkiin valitsemiisi soluihin.

4) F2, Ctrl+Enter

Jo kirjoitetun tiedon/kaavan kopiointi

Valitse solut lähtien liikkeelle solusta jossa on haluttu tieto

Paina F2 (aktivoi solun)

Paina Ctrl+Enter

Tyhjien rivien poisto

Leave a reply

Esimerkki miten poistan koko taulukon rivin jos A-sarkkeen solu on tyhjä.

Tallenna ensin työkirjasi
Valitse A-sarake
Valitse Aloitus, Muokkaaminen, Etsi ja valitse, Siirry määräten (Home, Editing, Find & Select, Go To Special)
Valitse valintaikkunasta Tyhjät (Blanks)
Valitse Aloitus, Solut, Poista, Poista taulukon rivejä (Home, Cells, Delete, Delete Sheet Rows)

Ehdollinen muotoilu

Leave a reply

“Best Price”

Vertaa soluja ja merkitse alin arvo, esim. halvimman hinnan löytämiseksi.

A

B

C

D

E

1

 Nimi Hinta 1 Hinta 2 Hinta 3 Hinta 4

2

 Tuote 1

10,00

10,25

9,90

9,95

3

 Tuote 2

12,00

10,20

11,00

10,00

4

 Tuote 3

14,00

14,00

14,10

14,10

5

 Tuote 4

8,00

9,30

8,90

8,10

alitse alue (esimerkissä alue B2:E5)

Valitaan Aloitus, Tyyli, Ehdollinen muotoilu, Uusi sääntö (Home, Styles, Conditional Formatting, New Rule)

Valitse valintaikkunasta Määritä kaavan avulla, mitkä solut muotoillaan (Use a formula to determine which cells to format)

Kirjoita kaava =B2=MIN($B2:$E2) ruutuun Muotoile arvot, jossa tämä kaava on tosi (Format values where this formula is true)

(Huomaa solu-viittaukset, lukitukset)

Valitse painikkeen Muotoile (Format) -alta haluamasi solun muotoilu

Napsauta OK ja OK

Vinkki:
Kaavan kirjoittaminen voi olla tuskallista valintaikkunoissa kun nuolinäppäimen käyttö muuttaa soluviittausta.
Paina F2 ja voit käyttää nuolinäppäimiä siirtyessäsi kaavassa ilman että viittaus muuttuu.
F2 vie sinut muokkaus tilaan.

 

 

Excel-pikanäppäimiä

Leave a reply
Muutamia Excel-pikanäppäimiä
Painike Selite
Alt+F4 Sulje sovellus
Alt+Nuoli alas Näyttää listan solun yläpuolelle kirjatuista teksteistä josta voi valita soluun arvon
Alt+Vaihto+= Lisää funktio summa (autosumma)
Ctrl+0 Piilota sarake
Ctrl+1 Muotoile solut valintaikkuna
Ctrl+9 Piilota rivi
Ctrl+a
  1. Valitse koko taulukko
  2. Jos alueella tietoja, valitsee kyseisen alueen
  3. Kaavan kirjoituksessa käytettäessä heti vasemman sulun jälkeen niin näyttää funktion argumentti ikkunan
Ctrl+c Kopio
Ctrl+F4 Sulje ikkuna
Ctrl+g Avaa Siirry-valintaikkunan
Ctrl+Home Siirry soluun A1
Ctrl+n Uusi työkirja
Ctrl+o Tiedosto/Avaa valintaikkuna
Ctrl+p Tulostusikkuna
Ctrl+Page Down Siirry seuraavaan taulukkoon
Ctrl+Page Up Siirry edelliseen taulukkoon
Ctrl+s Tallenna
Ctrl+v Liitä
Ctrl+Vaihto+* Valitse kyseinen alue
Ctrl+Vaihto+: Järjestelmän kellonajan
Ctrl+Vaihto+; Järjestelmän päivämäärän
Ctrl+Vaihto+” Kopio solun yläpuolella oleva arvo soluun
Ctrl+x Leikkaa
Ctrl+y Toista viimeinen toiminto, jos mahdollista
Ctrl+z Kumoa
F1 Excelin ohje
F11 Luodaan oletuskaaviokuva
F2 Muokkaa / aktivoi solu
F4
  1. Toista viimeinen toiminto, jos mahdollista
  2. Kaavan kirjoituksessa toistattaessa muuttaa solun viittaustavan ($A$1, A$1, $A1, A1)
F8 Solujen valinta tila Päälle/pois päältä. Valinta voidaan tehdä nuolinäppäimillä.
F9 Laskee kaikkien avointen työkirjojen laskentataulukot
Vaihto+F9 Laskee aktiivisen laskentataulukon
Vaihto+Välilyönti Valitse rivi
Ctrl+ Välilyönti Valitse sarake
Home Siirry sarakkeeseen A

Muutama sana minusta

Koulutan yrityksissä MS Office -ohjelmien käyttöä sekä MS SQL ja MS SQL reporting service:ä.

Tämän lisäksi kehitän sovelluksia hyödyntäen MS SQL-, Internet- ja MS Office -ohjelmia.

Luon työkaluja ja apuohjelmia toimisto-ohjelmien jokapäiväiseen käyttöön. Minulta saa apuohjelmia niin tietojen käsittelyyn Excelissä kuin myös eritasoisia asiakirjamalleja Wordiin.

Tarjoan myös ongelmaratkaisuja sähköpostitse MS Office -ohjelmille sopimuksen mukaan.

Toimin ruotsin, suomen ja englannin kielellä.

Tässä blogissa on tarkoitus esitellä kikkoja ja niksejä ohjelmien käyttöön, ja toivon siitä pikkuhiljaa tulevan kattava tietopaketti, tavalla tai toisella, ja aina pieni pilke silmäkulmassa. 🙂

Kun tarvitset apua joko koulutuksessa tai ongelmaratkaisuissa Excelissä tai muissa Office-ohjelmissa niin ota yhteyttä boris (at) isaksson (piste) fi