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).

Taulukko Excelissä

Kun muotoilet listasi taulukoksi (Table) Excelissä (Lisää – Taulukko; Insert – Table) saat monta etua.
Taulukko laajenee automaattisesti kun lisäät sarakkeita ja rivejä. Jos olet luonnut siitä Pivot-taulukon niin sinun tarvitsee vain päivittää Pivotti, et tarvitse määritellä tietolähdettä uudestaan.
Mikäli sinulla on kaavoja jossakin taulukon sarakkeessa nekin kopioituvat automaattisesti uudelle taulukon riville.
Voit halutessasi lisätä taulukkoon Summa-rivin (Total row) ja siihen valita erilaisia laskukaavoja kuten summa, keskiarvo jne. Tämä summa rivi näyttää aina kulloisen suodatuksen tuloksen.
Mikäli se on mielestäsi hankala taulukon alaosassa voit lisätä vaikka muutaman tyhjän rivin taulukon yläpuolelle ja kopioida summarivin sinne. Summarivi käyttää välisumma-funktiota (subtotal).
Muita toimintoja joita taulukon määrittely tuo mukanaan on muun muassa taulukon otsikkorivin lukitus kun vierität taulukkoa alaspäin, osittajan (Slicer) käyttö suodatukseen ja enne kaikkea tietomallien teko, eli yhteyksien luominen taulujen välille (Relation). Tämä taas mahdollistaa Pivot tekemisen käden käänteessä useammasta taulusta.

PowerPivot kalenteri

Excel 2016:een ja PowerPivotiin on tullut uusi kiva ominaisuus, nimittäin päivämäärätaulun luonti.

Tämän avulla voit vaikka seurata päivittäistä kävijämäärää tarvitsematta itse kirjoittaa ja määritellä päivämääriä.

Kun olet tuonut PowerPivotiin taulun, joka sisältää päivämääriä, siirry Design-valintanauhaan ja valitse Date Table ja siitä edelleen New.

PowerPivot luo sinulle kalenteritaulun, jonka liität tietokannasta tai Excelistä tuotuun tauluun.

Design – Realtionships, Create Relationship.

Kun tämä on tehty, voit tehdä Pivot-taulukon Exceliin, jossa käytät riviotsikkona kalenteritaulun viikonpäivää ja Pivot-taulukon arvokenttänä kenttää, jota haluat laskea.

Alla esimerkki päivittäisestä kävijämäärästä eräänä ajanjaksona:

Mikäli päivämääriä tulee lisää, voit päivittää päivämäärätaulukon kätevästi PowerPivotissa. Sinun tarvitsee ainoastaan antaa raja-arvot, ja PowerPivot lisää automaattisesti uudet päivämäärät kalenteriin.

Näyttökaappaus kätevästi viestiin

Mene viestin kirjoitus osioon
Napsauta Lisää (Insert)
Napsauta Näyttökuva (Screenshot)
Lisää leike
Valitse ikkuna jonka haluat liittää

Mikäli haluat vain osan jostakin ikkunasta valitse Näyttökuva ja Näyttöleike.
Valitse vasen hiiripainike alhaalla alue jonka haluat liittää.
Ennen leikkeen ottamista järjestä niin että kohta josta leikkeen haluat on viesti ikkunan alla.

Kaavan tarkastelu

Kaavan laskenta vaihe vaiheelta

Kaavaa voi tutkia laskuvaihe laskuvaiheelta käyttämällä työkalua Laske kaava joka on Kaavat valintanauhan Kaavan tarkistaminen ryhmässä (Formulas, Formula Auditing, Evaluate Formula)
Siirry kaavaan jota haluat tarkastella ja napsauta työkalua. Toiminto avaa valintaikkunan jossa näet laskettavan kaavan. Se osa kaavasta jota seuraavaksi lasketaan on alleviivattu.

Kun painat Laske (Evaluate) suoritetaan laskenta ja näet tuloksen valintaikkunassa.
Painike Suorita rivi (Step In) näyttää kaavassa olevan alleviivatun solun sisällön ja voit sen suorittaa vaihe vaiheelta.
“Ylätasolle” pääset taas napsauttamalla Suorita toimintosarja loppuun -painiketta (Step Out).

 

Tekstisolujen korostaminen ehdollisella muotoilulla

Löydä helposti ehdollisella muotoilulla solut joissa esim. numerot ovat tekstiä.

Valitse alue josta haluat korostaa solut
Valitse Aloitus – Tyylit – Ehdollinen muotoilu (Home – Styles – Conditional Formatting)
Valitse Uusi sääntö (New Rule)
Valitse Määritä kaavan avulla, mitkä solut muotoillaan (Use a formula to determine which cells to format)
Kirjoita seuraava kaava valintaikkunan kaavaruutuun (jos alue alkaa solusta A1)
=ONTEKSTI(A1)       (=ISTEXT(A1))
Valitse haluttu muotoilu painikkeesta Muotoile (Format)
Napsauta ok
Ehdollinen muotoilu

Kaavojen piilotus

Valitse kaavat
Valitse solumuotoilut, Ctrl+1    (Format Cells)
Valitse Suojaus välilehti (Protection)
Valitse Piilotettu (Hidden) -vaihtoehto
Napsauta OK
Valitse Tarkista, Suojaa taulukko (Review, Protect Sheet)
Napsauta OK (Salasana ei ole pakollinen)

Mikäli haluat että joihinkin soluihin voidaan kirjoittaa ne tulee ennen suojausta määritellä “ei suojatuiksi”.

Valitse solut
Valitse solumuotoilut, Ctrl+1    (Format Cells)
Valitse Suojaus välilehti (Protection)
Poista valinta Lukittu (Locked) -vaihtoehto
Napsauta OK

Vauhtia makroon

Nopeampaa makro suoritusta Excelissä.

Muutamalla perusasetuksella saa koodin toimimaan nopeammin.
Tietenkin tekemällä hyvää ja lyhyttä koodia ja välttelemällä turhia soluvalintoja selectillä mutta on myös pari peruslaatua olevaa komento jolla saadaan vaihtua ohjelmaan.

Ensinnäkin näytön päivitys pois
Application.ScreenUpdating = False
ja takasin päälle silloin kun tarvitaan
Application.ScreenUpdating = True

Laskenta pois päältä
Application.Calculation = xlCalculationManual
ja takaisin päälle
Application.Calculation = xlCalculationAutomatic

Tapahtumahallinta pois päältä
Application.EnableEvents = False
ja takasin päälle
Application.EnableEvents = True

Sivukatkojen laskenta pois päältä
Activesheet.DisplayPageBreaks = False
ja takasin päälle
Activesheet.DisplayPageBreaks = True

Näytön päivitys on se mikä useimmiten puuttuu makroista. Kannatta kokeilla ainakin sitä. Jos lähdet asettamaan laskennan manuaaliseksi niin muista laittaa se päälle kun haluat että Excel taas laskee automaattisesti.

Laskenta-ajankohdan hallinta

Laskennan hallinta

Kun muutat tietoja laskentataulukossa Excel laskee kaavat uudestaan ja tämä saattaa olla tosi hidasta suurissa taulukoissa. Voit itse määritellä laskenta-ajankohdan Kaavat-valintanauhan Laskutoimitus toimintojen avulla (Formulas, Calculation).

Laita laskutoimitus “manuaalinen” ja tee muutokset, jonka jälkeen muutat sen takaisin “Automaattinen” tai käytät sopivaa pikanäppäintä.

Näppäin

Toiminto

F9

Laske uudelleen kaikkien avointen työkirjojen kaavat, joita on muutettu edellisen laskennan jälkeen, sekä niiden seuraajakaavat

VAIHTO+F9

Laske uudelleen kaikkien avointen työkirjojen kaikki kaavat riippumatta siitä, onko niitä muutettu edellisen laskennan jälkeen

CTRL+ALT+F9

Laske uudelleen kaikkien avointen työkirjojen kaikki kaavat riippumatta siitä, onko niitä muutettu edellisen laskennan jälkeen

CTRL+VAIHTO+ALT+F9

Tarkista seuraajakaavat uudelleen ja laske sitten uudelleen kaikkien avointen työkirjojen kaikki kaavat riippumatta siitä, onko niitä muutettu edellisen laskennan jälkeen

Muutama VBA-editorin asetus

Meille huononäköisille saadaan VBA-editorin fontti isommaksi kohdasta:
VBA – Editorin valikko: Tools – Options, Editor Format ja Size kohta.

Itse olen myös ottanut ponnahdusikkuna-ilmoituksen pois virheistä koodirivillä kohdasta Tools – Options, Editor, Auto Syntax Check.
Itselleni riittää kun rivi muuttuu punaiseksi, koen ponnahdusikkunan erittäin ärsyttäväksi.

Kannattaa myös laittaa päälle asetus Require Variable Declaration joka vaatii muuttujien määrittelyn ja laittaa uusien moduulien ensimmäiseksi riviksi Option Explicit.

 

Etsi ja poista rivinvaihto funktioilla

Etsitään pakollinen rivivaihto (Alt+Enter) ja korvataan se välilyönnillä.
Merkin koodi on 10 ja sen etsimiseen käytetään ETSI (SEARCH) ja MERKKI (CHAR) –funktioita.
Esimerkkinä: solussa A1 on merkkijono jossa pakollinen rivivaihto.
ETSI(MERKKI(10);A1)
SEARCH(CHAR(10);A1)
Funktiot palauttavat löydetyn merkin position ja nyt se löydetty rivinvaihto korvataan välilyönnillä käyttäen funktioita VASEN (LEFT) ja OIKEA (RIFGHT).
=VASEN(A1;ETSI(MERKKI(10);A1)-1)& ” ” & OIKEA(A1;ETSI(MERKKI(10);A1))
=LEFT(A1;SEARCH(CHAR(10);A1)-1)&” “&RIGHT(A1;SEARCH(CHAR(10);A1))
VASEN funktion yhteydessä oleva -1 tekee sen että lopputuloksessa jätetään rivivaihto huomioimatta.
& -merkki yhdistää merkkijonot.

Puuttuvat etunollat

Puuttuvat etunollat saadaan myös seuraavalla tavalla käyttäen TEKSTI (TEXT) – funktiota ja kopio – liitä toiminnolla.
TEKSTI funktion syntaksi: TEKSTI(arvo;muoto). Muoto laitetaan lainausmerkkeihin.
Esimerkissä halutaan numeromuotoilun olevan viisi merkkiä joten muoto on laitettu muotoon “00000” : =TEKSTI(A2;”00000″)
Tämän jälkeen on arvot B-sarakkeessa kopioitu ja la liitetty arvoina C-sarakkeeseen.

Esimerkki funktiosta