Yearly Archives: 2017

Päivän pikanäppäimet

Kun täytät soluja allekkain ja haluat kirjoittaa soluun sen yläpuolella olevan tiedon niin paina Ctrl+=+Enter -näppäin yhdistelmää.

Kun haluat kirjoittaa soluun tekstiä jonka olet jo kertaalleen kirjoittanut sen yläpuolelle oleviin soluihin niin paina Alt+Nuoli-alas -näppäin yhdistelmää. Valitse vaihtoehto tarjottavasta listasta ja paina Enter.

 

 

Kaavan kopiointia

Kopioi kaava tai muu tieto valitsemallesi alueelle.
– Valitse alue johon haluat kaavan (tiedon)
– Kirjoita kaava
– Paina Ctrl+Enter

Näppäin yhdistelmä Ctrl+Enter kopioi kirjoitetun kaavan, tekstin numeron valitulle alueelle.
Jos haluat tehdä sen jälkikäteen, sen jälkeen, kun jo olet kerran painanut Enter-näppäintä niin:
– valitse ensin alue jolle sen haluat kopioida
– aktivoi solu jossa kopioitava tieto, esim. painamalla F2
– paina Ctrl+Enter

Tekstin korostaminen PowerPoint esityksessä

Tekstiä esitettäessä korostetaan aina sitä kappaletta josta puhutaan ja muut himmennetään.
Tähän tarvitaan animaatiota.
Animaation avulla tuodaan kappale kerrallaan esille seuraavasti:
(Oletan että teksti on kirjoitettu paikkamerkkiin)
Valitaan paikkamerkki (Place holder) (napsauta sen reunaa niin että paikkamerkin reuna on yhtenäinen viiva)
Valitaan Animaatio, Tuleminen, Tehosteasetukset; Kappaleittain (Animation, Appear, Effect Options; By Paragraph)

 

Tämä tekee sen että kun edetään esityksessä niin tekstiä tulee esiin yksi kappale kerrallaan.
Seuraavaksi himmennät jo käsitellyn asian. Teet sen seuraavasti:
Valitse Animaatio, Animaatioruutu (Animation, Animation Pane); 1
Napsauta animaatioruudussa ensimmäisen kohteen alaspäin osoittavaa nuolta (Content Place); 2
Avautuvasta valikosta valitse Tehosteasetukset (Effect Options); 3.

 

Valintaikkunassa joka avautuu, määrittelet mitä tehdään Animaation jälkeen (After animation)
Valitse listaruudusta jokin sopiva väri, esimerkiksi harmaa ja lopuksi napsauta OK.

Nyt kun siirryt esityksessä seuraavaan kappaleeseen himmentyy edellinen kappale. Tällöin on aina se kappale korostettu, eri värinen, josta puhut.

Vuosineljännes

Excelissä ei ole suoraan tähän funktiota mutta se voidaan laskea erittäin helposti seuraavanlaisella kaavalla:
(Oletan että solussa A1 on päivämäärä)
=KOKONAISLUKU((KUUKAUSI(A1)+2)/3)
=INT((MONTH(A1)+2)/3)
KOKONAISLUKU pöristää luvun alaspäin seuraavaan kokonaislukuun.
KUUKAUSI funktio poimii kuukauden päivämäärästä.
Eli, kaava lisää kuukausinumeroon kaksi ja jakaa sen kolmella, tulos pyöristetään alaspäin seuraavaan kokonaislukuun.

Joustava alasvetovalikko

Alasvetovalikon teko taulun ja EPÄSUORA (INDIRECT)-funktion avulla.

 

  1. Luodaan taulukko joka määritellään tauluksi (Table
  2. Luodaan alasvetovalikko kelpoisuustarkistuksella (Data Validation)
  3. Määritellään Salli kohtaan Lista (Allow List) ja listan lähteeksi kaava =EPÄSUORA(“Taulun_Nimi[Sarakkeen:Nimi] “) (=INDIRECT(“Table_Name[Column_Name]”)

Esimerkki:
Luodaan listan elementit ja määritellään se taulukoksi; Lisää Taulukko (Insert, Table).


Taulukolle annoin nimeksi Kohteet. Nimeäminen ei ole pakollista mutta kuvaava nimi helpottaa jos taulukoita on useita. Nimen määrittelet Taulukkotyökalun vasemmassa laidassa olevasta kohdasta Taulun nimi (Table Name).
Siirrytään soluun johon alasvetovalikko halutaan (ei tarvitse olla sama välilehti).
Valitaan Tiedot, Kelpoisuustarkistus (Data, Data Validation)

Valitse Salli kohtaan Lista (Allow, List)
Kirjoita Lähde (Source) kohtaan kaava =EPÄSUORA(“Kohteet[Paikkakunta]”)
=INDIRECT(“Kohteet[Paikkakunta]”)
Kaavassa Kohteet on sinun taulu nimi ja Paikkakunta sen sarakkeen nimi taulussa josta haluat tiedot alasvetovalikkoon.
Lopputulos:

Nyt jos taulukkoon lisätään paikkakuntia niin alasvetovalikko näyttää ne automaattisesti.

Skype for Business kokouskutsu

Laita jo Skype for Business kokouskutsussa muutama perusasetus kohdalleen niin ei tarvitse niin paljon sählätä kokouksessa.

Kun teet kutsun Outlook kalenterista niin napsauta kokousasetuksia (Meeting Options) -painiketta.

 

Kokouksen asetusikkuna aukeaa ja voit määritellä mm kuka pääsee suoraan kokoukseen, kuka on esittäjä, mykistetäänkö kaikki oletuksena jne.

Kannattaa tutustua näihin asetuksiin, jos pidät isompaa kokousta monella osallistujalla.

 

Matriisikaavoja

Joissakin tilanteissa matriisikaava voi olla paras mahdollinen tapa luoda kaava ja joissakin tapauksissa jopa ainoa tapa. Jotkut Excelin funktiot vaativat myös matriisikaavaa toimiakseen.

Matriisikaava syötetään aina näppäinyhdistelmällä Ctrl+Vaihto+Enter, (Ctrl-Shift+Enter).
Jos laitat matriisikaavan solualueelle, et voi muokata yksittäistä kaavasolua, vaan koko matriisikaava on muokattava. Valitset sen alueen, jossa matriisikaava on ja sitten muokkaat sitä.

Kaikessa yksinkertaisuudessaan se on alue, jonka kaikkia elementtejä kerrotaan samalla arvolla, esim.:

 

Esimerkissä kerrotaan alueen A1:B2 arvot solun A4 arvolla ja tulos laitetaan alueelle A6:B7.
Kaikissa alueen A6:B7 soluissa on sama kaava {=A1:B2*A4}.
Teet laskennan seuraavasti:
Valitse tulosalue (A6:B7)
Kirjoita kaava =A1:B2*A4
Vahvista kaava painamalla Ctrl+Vaihto+Enter, tämä on matriisikaavan syöttötapa.
Excel lisää kaavan ympärille aaltosulut, { }.

Toinen esimerkki

 

Laskelma, joka palauttaa virheitä, mutta haluat summata tulokset virheistä huolimatta.
Esimerkiksi seuraava laskelma:
Sarakkeessa A on arvoja, joita jaetaan sarakkeen B arvoilla.
Jos sarakkeessa B on nolla (0) tai tyhjä, saadaan virhe ja summaus ei tule onnistumaan (solussa C5 on normaali summakaava).
Mutta jos summakaava laitetaan seuraavasti {=SUMMA(JOSVIRHE(C1:C3;””))} saadaan oikea vastaus {=SUMM(IFERROR(C1:C3;””))}. Tämän kaavan tulos on solussa C6.

 

Kolmas esimerkki

 

Lasketaan sarakkeen B ja C muutoksen keskiarvo suoraan soluun B7 ilman että ensin laskettaisiin erotukset D sarakkeeseen ja sitten D sarakkeen keskiarvo.

 

Kaava on matriisikaava muotoa {=KESKIARVO(B2:B5-C2:C5)}
{=AVERAGE(B2:B5-C2:C5)}
Kaava laskee solujen B ja C:n erotuksen ja sen jälkeen keskiarvon erotuksista.

Neljäs esimerkki

 

Lasketaan kuinka monta solua alueella B2:D5 on tekstiä.

 

Tällöin kirjoitetaan seuraavanlainen kaava joka on matriisimuodossa:
{=SUMMA(ONTEKSTI(B2:D6)*1)}
{=SUM(ISTEXT(B2:D6)*1)}
Funktio ONTEKSTI palauttaa TOSI jos solussa on tekstiä ja TOSI * 1 on 1, joten kun ykköset summataan saadaan tekstisolujen määrä.

 

Viides esimerkki

 

Lasketaan SUUNTAUS (TREND) soluihin B5:B7 perustuen arvoihin B2:B4.
Funktio palauttaa matriisin, joten se on kirjoitettava matriisikaavana.
Kun kirjoitat kaavan, valitse ensin alue, johon haluat tuloksen (B5:B7).

 

Kuudes esimerkki

 

Kun käytetään Excelin toimintoa Arvotaulukko, luo toiminto tulossoluihin matriisikaavan.

 

 

Esimerkissä lasketaan tuleva arvo eri vuosille ja eri korkoprosenteilla, jos sijoitetaan 500 € kerran kuukaudessa.

Solun B7 kaava on: =TULEVA.ARVO(B1/12;B2;B3) ja se luodaan ensimmäiseksi (FV).
Tämän jälkeen kirjoitetaan maksuerät soluihin C7:I7 (itse olen kirjoittanut vuodet C6:I6 ja laittanut kaavan C7:I7 joka on =12*C6, =12*D6… jne.
Sitten valitsen alueen B7:I12 ja toiminnon Arvotaulukko kohdasta Entä jos analyysi Tiedot valintanauhasta (Data, What-If Analysis, Data Table) .
Rivin syöttösoluksi (Row input cell) valitsen B2, jossa minulla on maksuerät, ja sarakkeen syöttösoluksi (Column input cell) B1, jossa minulla on korkoprosentti.

 

Taulukko näyttää minulle tulevan arvon eri vuosille eri korkoprosenteilla.

Kansiossa olevien tiedostojen luku VBA:ssa

VBA:ssa voit lukea kansion Dir käskyllä löytääksesi haluamasi tiedoston tai vaikka kansion kaikki tiedostot.

Käytä lukemisessa Do While – Loop solmukkaa. Kun olet käsitellyt tiedoston ja haluat lukea seuraavan tiedoston suorita Dir toistamiseen ennen Loop käskyä.

Alla olevassa esimerkissä kansionkin voisi parametrisoida ja Sub:n muuttaa Function proseduuriksi.

 

Sub EtsiTiedosto()

Dim NextBook As String

NextBook = Dir(“c:\test\*.xlsx”)     ‘lue hakemistosta kaikki .xlsx tiedostot

Do While NextBook <> “”     ‘silmukka niin kauan kun tiedostoja löytyy

    Koodi joka käsittelee löydetyn tiedoston


 

NextBook = Dir()         ‘lue hakemisto uudestaan

Loop

End Sub

Etsitään alin/ylin lukuarvo alueelta ja muotoillaan se

Valitaan ensin alue (esimerkissä A1:H100)
Valitaan Aloitusvalintanuhasta Ehdollinen muotoilu (Home, Conditional Formatting) ja siitä
Valitaan Uusi sääntö (New Rule)
Valitse Määritä kaavan avulla, mitkä solut muotoillaan (Use a formula to determine which cells to format)
Kirjoita kaava siihen varattuun ruutuun: =A1=MIN($A1:$H100)
Napsauta painiketta Muotoile (Format)
Valitse haluamasi muotoilu
Paina OK-painiketta kunnes tulet takaisin taulukkoon.
Vastaavasti suurimman arvon saat korvaamalla MIN (MIN) funktion MAKS (MAX) funktiolla.

Kaavassa vertaillaan aina kulloista solua rivin pienimpään arvoon. Jos solun arvo on sama kun rivin pienin arvo se muotoillaan. Tämän takia kaavassa lukitaan sarakkeet ($A ja $H).

Kaavan kopiointi solualueelle

Kaavan voi kopioida monella eri tavalla jollekin alueelle.

  1. Valitse kaava ja napsauta Kopioi. Tämän jälkeen valitse ne solut johon kaavan haluat ja napsauta Liitä (Copy, Paste)
  2. Lista/taulukko muotoisessa tiedossa sen voi kopioida kaksoisnapsauttamalla valitun solun alaoikeassa kulmassa olevaa täyttökahvaa.
    Kaava kopioituu niin pitkälle kun naapurisolussa on tietoa.
  3. Valitse ensin solualueet, kirjoita kaava ja paina Ctrl+Enter, kaava kopioituu valittuihin soluihin.
    Ei tarvitse olla yhtenäinen alue.
  4. Kirjoita kaava ja testaa että se on oikea. Valitse sen jälkeen ao. solu ja se alue johon sen haluat kopioida. Paina F2 (aktivoi solun) ja sen jälkeen painat Ctrl+Enter.
  5. Joissakin tapauksissa voi myös käyttää tapaa että kaavan kopioi kaavakentästä toiseen soluun.
    Mennään soluun jossa kaava, valitaan kaava kaavakentässä ja painetaan Kopioi.
    Siirrytään soluun jonne se halutaan ja napsautetaan Liitä. Huomaa että soluviittaukset pysyvät samoina kun siinä solussa josta kaava kopioitiin. Eli jos kaava jonka kopioit on =A1+B1 ja liität sen vaikka soluun D10 niin kaava on edelleen =A1+B1

 

Jotta kaavat toimisi pitää kaavat luonnollisesti olla oikein kirjoitettu, eli muista lukita soluviittaukset oikein. Käytä apunasi funktiopainiketta F4 viittaustavan muuttamiseen. Joka kerta kun painat F4 muuttuu viittaustapa (A1, $A$1, A$1, $A1, A1).

 

Pivot useammasta taulusta

Tauluissa jotka haluat yhdistää tulee olla jokin yhteinen tekijä kuten asiakasnumero, henkilönumero tai jokin muu vastaava sarake (vertaa PHAKU funktion käyttöä).

Määrittele ensin listasi taulukoiksi: Osoitin listaan ja Lisää – Taulukko (Insert – Table). Kannattaa antaa tauluille kuvaava nimi Taulukko työkalut, Rakenne, Taulukon nimi).

Tämän jälkeen määrittele taulujen yhteys (pivotissa ja versio 2016 yhteyden voi myös antaa Excelin tehtäväksi, joskus onnistuu ja joskus ei).

Yhteyden määrittelet Tiedot valintanauhan ryhmästä Datatyökalut ja Yhteydet toiminnolla (Data – Data Tools, relationships).

Valitse ensin (yläriville) se taulukko jossa on yksilölliset tiedot ja avain sarake, se sarake jossa nämä yksilölliset tiedot sijaitsevat.

Seuraavalla riville valitse se taulukko jonka haluat liittää ja se sarake josta löytyy vastaavat tiedot.

Alla olevassa esimerkissä henkilönumero.

Nyt voit luoda pivotin tiedoista ja poimia sarakkeita molemmista tauluista. Huomattavasta parempi vaihtoehto kuin Phaku.

 

Nopeampi PHAKU

Tee Excel PHAKU (VLOOKUP) kaavastasi hieman nopeampi parilla pikku kikalla.

Ensiksi älä käytä & -merkkiä yhdistäessäsi hakuehtoa. Luo oma sarake yhdistelylle ja viittaa hakukaavassa siihen.

Siis älä tee näin =PHAKU(A2&B2;Tauli2!$A$1:$C$1000;2;EPÄTOSI)

Luo oma sarake yhdistelylle: =A2&B2 ja viittaa tähän sarakkeeseen PHAKU -kaavassa.

 

Toinen nopeuttava tekijä on; lajittele ensin taulukko josta haet tietoa (hakusarakkeen mukaan).

 

Kolmas mihin usein törmää on JOS -lausekkeen käyttö PHAKU -kaavassa kun halutaan virhearvo pois. Käytä JOSVIRHE -funktiota tällaisissa tilanteissa.

=JOSVIRHE(PHAKU(A2&B2;Tauli2!$A$1:$C$1000;2;EPÄTOSI);” “). Tämä esimerkkikaava jättää solun tyhjäksi jos hakuarvoa ei löydy.

 

Excel kaaviokuvan liittäminen PowerPointiin

Kopioi Excelissä kaaviokuva leikepöydälle
Siirry PowerPointissa diaan johon haluat kaaviokuvan
Napsauta kaaviokuvan sisällön paikkamerkkiä, eli valitse se (tällöin saat kaaviokuvan paikkamerkin kokoiseksi)
Liitä kaaviokuva

Kaaviokuvasta tulee näin ollen saman kokoinen kuin paikkamerkistä.

Valitse liitäntätavaksi joko upotettu (laittaa koko työkirjan dian) tai linkitetty.
Linkityksen voit purkaa Tiedosto – Info (File – Info) kohdasta. Ala oikealla on Muokkaa linkkejä (Edit Links) toiminto. (Tallenna esitys ensin).

Päivämäärälaskentaa

Ryhmittely päivämäärien kanssa

Kun Excelin Pivot-taulukossa tarvitaan päivämäärälaskentaa ja erilaisia yhteenvetoja ja vertailuja päivämäärillä niin otetaan PowerPivot avuksi.
Alla esimerkki jossa laskin kävijämäärän per viikonpäivä:

Tämän saa aikaseksi tosi kätevästi luomalla päivämäärätaulukon PowerPivottiin ja siirtämällä myös lähdetiedon PowerPivottiin.

Excel versiossa 2013 luodaan päivämäärätaulu Excelissä ja linkitetään tai viedään se PowerPivottiin.
Excel versiossa 2016 voidaan päivämäärätaulukko luoda suoraan PowerPivotissa.
Kun päivämäärätaulukko on PowerPivotissa, luodaan yhteys (suhde) päivämäärätaulun ja lähdetaulun välille. Näin saadaan pivottiin ryhmittelyt esim. viikonpäivän mukaan.
Alla on esimerkki päivämäärätaulusta jonka loin Excel 2013 versiossa.

Ja tässä kaavat:

Kaavat:

Sarake     Kaava      Suomeksi
Vuosi:     YEAR(A2)     VUOSI(A2)
Kuukausinumero     MONTH(A2)     KUUKAUSI(A2)
Kuukausinimi Suom  TEXT(MONTH(A2)     TEKSTI(KUKAUSI(A2)
Kuukausinimi Eng   TEXT(MONTH(A2);”[$-en-GB]kkkk”) TEKSTI(KUUKAUSI(A2);”[$-en-GB]kkkk”)
Viikonpäivänumero  WEEKDAY(A2)     VIIKONPÄIVÄ(A2)
Päivä Suom     TEXT(A2;”pppp”)     TEKSTI(A2;”pppp”)
Päivä Eng     TEXT(A2;” [$-en-GB]pppp”)     TEKSTI(A2;” [$-en-GB]pppp”)

Excel 2013
Siirry PowerPivot valintanauhalle ja napsauta Lisää tietomalliin (Add to Data Model)
Luo yhteys (suhde) päivämäärätaulun ja data taulun välillä PowerPivotissa:
Aloitus – Kaavionäkymä (Home – Diagram View)
Raahaa hiirellä kalenterin päivämääräkenttä linkitettävän lähdetaulun päivämääräkentän päälle.

Siirry takaisin Tietonäkymään (Data View).
Napsauta Pivot-taulukko
Pivot-taulukossa raahaa kenttäluettelosta päivän nimisarake rivit (Rows) ruutuun ja arvot (Values) ruutuun, se sarake jota haluat laskea.

Excel 2016
Luo päivämäärä taulukko PowerPivotissa sen jälkeen kun olet tuonut lähdetiedot.
Siirry Rakenne (Design) valintanauhaan
Valitse Päivämäärätaulukko – Uusi (Date Table – New)
Päivämäärätaulukko luodaan sille ajanjaksolle jolle on lähdetiedossa päivämääriä. Voit päivittää ajanjakson samasta työkalusta valitsemalla Päivitä alue (Update Range)

Ehdollinen muotoilu

Muotoillaan listan/taulukon koko rivi eri väriseksi jos jossakin sarakkeessa on ehdot täyttävä tieto.

Valitaan ensin koko alue
Valitaan Aloitusvalintanuhasta Ehdollinen muotoilu (Home, Conditional Formatting) ja siitä
Valitaan Uusi sääntö (New Rule)
Valitse Määritä kaavan avulla , mitkä solut muotoillaan (Use a formula to determine which cells to format)
Kirjoita kaava siihen varattuun ruutuun, esimerkissä: =$B1=”finland”
Napsauta painiketta Muotoile (Format)
Valitse haluamasi muotoilu
Paina OK-painiketta kunnes tulet takaisin taulukkoon.


Lopputulos:


Kaava: =$B1=”finland” tarkoittaa että kun sarakkeessa B esiintyy merkkijono finland niin koko rivi muotoillaan. Huomaa kaavan lukitus, vain sarake B lukitaan koska alueella joka valittiin ensin verrataan aina B saraketta. Rivi ei voi olla lukittu koska se on vaihduttava joka rivillä vastaamaan asianomaista riviä.
Merkkijono finland on laitettava lainausmerkkeihin, “” koska se on kaavassa olevaa tekstiä.

Korosta kaaviokuvan sektoria

Kun olet tehnyt ympyräkaaviokuvan voit korostaa sen sektoria mm. vetämällä asianomaista sektoria hieman ulospäin kaaviokuvasta, voit myös muuttaa sektorin väriä tarvittaessa.
Saat sektorin valittua napsauttamalla ensin kaaviokuvaa ja sitten toistamiseen sitä sektoria jota haluat käsitellä.
Valitun sektorin kulmiin tulee pisteet jotka osoittavat valintaa.

Lukujen esiintymiskerta

Kun tarvitaan tietää kuinka monta kertaa jokin luku esiintyy jossakin tietomäärässä ja pivot-raporttia ei voida käyttää voidaan turvautua TAAJUUS -funktioon (FREQUENCY).
Funktion syntaksi: TAAJUUS(tieto_matriisi;lohko_matriisi).
Funktion tieto_matriisi on alue josta haluat laskea esiintymät ja lohko_matriisi on välit johon haluat ryhmitellä esiintymät.
Alla oleva esimerkki laskee kuinka monta henkilöä on kussakin ryhmässä.
Kaava on matriisikaava ja muotoa {=TAAJUUS(B2:B16;E2:E5) }
Kaava kirjoitetaan valitsemalla ensin alue johon kaava halutaan, kirjoitetaan kaava ja lopuksi painetaan Ctrl+Vaihto+Enter -painiketta joka on matriisikaavan syöttötapa.
Esimerkin viimeinen lohko (solu E5) on jätetty tyhjäksi jotta saadaan kaikki yli 63 vuotiaat laskettua.
Solut D1:D5 ovat pelkästään selventävää tekstiä esim. jos halutaan edelleen luoda kaaviokuva kunnon selitteillä.

POIMI.TEKSTI (MID) -funktio

POIMI.TEKSTI funktiolla voit poimia merkkijonon keskeltä halutun määrän merkkejä kuten esimerkiksi syntymävuoden hetusta.
Esimerkki 1:
jos hetu on solussa A1 ja muotoa 241290-123A niin saat vuoden seuraavasti: =POIMI.TEKSTI(A1;5;2)
Funktion syntaksi on merkkijono;aloituskohta;merkkien määrä.
Esimerkki 2:
Paikkakunnan poiminta solusta jossa on sekä postinumero että paikkakunta.
Solussa A1 on 1600 Vantaa niin paikkakunnan saat seuraavalla kaavalla: =POIMI.TEKSTI(A1;7;PITUUS(A1)-6)
Kaavassa käytetty PITUUS (LEN) funktiota joka laskee merkkien määrän (12) miinustaen pituudesta postinumeron ja yhden välilyönnin merkkimäärän (6).