Category Archives: Excel

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.

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

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

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

SIIRTYMÄ – VASTINE (OFFSET – MATCH)

Esimerkki Siirtymä (Offset) ja Vastine (Match) -funktioiden käytöstä dynaamiseen summaamiseen.

Halutaan laskea listasta aina annetusta päivämäärästä seitsemän seuraava lukua yhteen.

Aloituspäivä kirjoitetaan soluun ja kaavassa käytetään Vastine-funktiota hakemaan päivän sijainti.

Koska vastinefunktioon on annettu koko A-sarake ja ensimmäisellä rivillä on otsikko, palautettavasta rivistä miinustetaan 1.
Kaava on muotoa:
=VASTINE(D2;A:A;0)-1, =MATCH(D2;A:A;0)-1

Esimerkissä se palauttaa päivämäärän positioksi 6.
Positiosta 6 halutaan laskea seuraavat 7 lukua yhteen B-sarakkeesta.
Käytetään Summa-funktiota, johon yhdistetään Siirtymä-funktio.
Siirtymä-funktiolla kerrotaan, mistä lähdetään liikkeelle (A1), monenneltako riviltä laskenta (Vastineen palauttama arvo) aloitetaan ja kuinka monta riviä lasketaan (E2).
=SUMMA(SIIRTYMÄ(A1;D4;1;E2)), =SUM(OFFSET(A1;D4;1;E2))
Kaikki voidaan laittaa yhteen ja samaan kaavaan, tällöin kaavan muoto on:
=SUMMA(SIIRTYMÄ(A1;VASTINE(D2;A:A;0)-1;1;E2))
=SUM(OFFSET(A1;MATCH(D2;A:A;0)-1;1;E2))

Esimerkki Summa, Siirtymä ja Vastine funktiosta

Solun kaava näkyviin

Joskus haluaa saada kaavan syystä tai toisesta esille viereiseen soluun eikä halua käyttää Kaavat – Näytä -kaavat (Formulas – Show Formulas) toimintoa. Kaavat näytä kaavat näyttää kaikki laskentataulun kaavat.

Excel 2013 on tullut uusi funktio KAAVA.TEKSTI(Viittaus), (FORMULA.TEXT(Reference) joka palauttaa kaavan merkkijonona.

Esimerkki:

Jos solussa B1 on kaava =A1+A2 ja kirjoitetaan C1 kaava =KAAVA.TEKSTI(B1) niin saadaan tulokseksi =A1+A2

Summan laskeminen ei onnistu

Kun summan laskeminen ei onnistu sen takia kun alueella on virhearvoja niin tällöin voidaan käyttää matriisikaavaa ja JOSVIRHE (ISERROR) funktiota.

Esimerkki:

Sarakkeissa A ja B on arvoja. A-sarakkeen arvot jaetaan B sarakkeiden arvoilla jolloin saadaan C sarakkeeseen virhearvo jos B:ssä on nolla (0) tai on tyhjä.

Tällöin kuten esimerkissä näkyy saadaan myös virhe soluun C5 jossa yritetään summata C-sarakkeen arvoja. Jotta summa voitaisiin laskea C:n virhearvoista huolimatta käytetään matriisikaavaa kaavaa ja funktiota JOSVIRHE (IFERROR) summakaavassa:
{=SUMMA(JOSVIRHE(C1:C4;””))}
{=SUM(IFERROR(C1:C4;””))}

Kaava kirjoitetaan normaalisti mutta enterin sijaista painetaan Ctrl+Vaihto+Enter jolloin saadaan aikaan matriisikaava.

Esimerkin D-sarakkeessa on käytetyt kaavat.

Esimerkki

Esimerkki


 

Piste pilkuksi Excel 2013

Excel 2013 tuli uusi funktio NROARVO (NUMBERVALUE) jolla voidaan muuntaa “numero” tai oikeasti teksti numeroksi alueasetuksista riippumattomalla tavalla.
Eli kun on teksti jossa desimaalierotin on esimerkiksi piste saadaan se muunnettua pilkuksi ja numeroksi alla olevan esimerkin mukaisesti.
Funktion syntaksi on: NROARVO (teksti, [desimaalierotin], [ryhmäerotin ]).
Esimerkki:

 

A

B

C

 

1

Currency

Rate

   

2

EUR/USD

1.0896

=NROARVO(B2;”.”;” “)

lopputulos:

1,0896

3

EUR/JPY

135.36

=NROARVO(B3;”.”;” “)

 

135,36

4

EUR/SEK

9.2617

=NROARVO(B4;”.”;” “)

 

9,2617

 

Negatiiviset luvut nolliksi

Silloin tällöin on ollut tarvetta muuntaa kaikki negatiiviset arvot nolliksi (0).
Se voidaan tehdä esimerkiksi seuraavalla makrolla jos ensin valitaan asianomainen alue ja sitten suoritetaan makro:

Sub ChangeNegativeToZero()     
    Dim c As Range     
    For Each c In Selection
         If c.Value < 0 Then
             c.Value = 0
         End If     
    Next
End Sub

Voit kopioida makron työkirjaan seuraavasti:
Kopioi tämä makro leikepöydälle
Työkirjassa paina Alt+F11
Valitse VBA ikkunassa Insert – Module
Valitse Edit – Paste

Suorita makro:
Valitse alue taulussa jonka haluat muuttaa
Valitse View – Macros – View Macros (Näytä – Makrot – Näytä Makrot)
Valitse ikkunasta juuri liittämäsi makro ja napsauta Run (Suorita)

Arkipäivät

Kahden päivämäärän väliset arkipäivät

Excelin TYÖPÄIVÄT funktiolla (NETWORKDAYS) voidaan laskea kahden päivämäärän väliset arkipäivät.
TYÖPÄIVÄT(aloituspäivä; lopetuspäivä; [vapaapäivä])
Esim.:
=TYÖPÄIVÄT(1.2.2015;15.2.2015) on 10
Funktion vapaapäivä argumentti voi olla lista vapaapäivistä ja pyhistä joita ei lasketa arkipäiviksi. Argumentiksi voidaan antaa solu-alue (esimerkissä E1:E17)
Esim.:
=TYÖPÄIVÄT(A1;B1;E1:E17)

Työpäivät esimerkki

Kaaviokuvan tallennus malliksi

Voit tallentaa tekemäsi kaaviokuvan, yksi tai useamman malliksi seuraavan kertaan.

1. Luo ja muotoile kaaviokuva

Excel versiossa 2010:

2. Valitse Tallenna mallina, kaaviokuvan Rakenne valintanauhan tyyppi ryhmästä. (Design – Type, Save as Template)
Älä muuta Excelin tarjoamaa kansiota C:\Users\Käyttäjänimi\AppData\Roaming\Microsoft\Templates\Charts

Excel versiossa 2013

2. Napsauta hiiren kakkospainiketta kaaviokuvan päällä ja valitse kohdevalokoista Tallenna mallina (Save as Template)

Saat mallin käyttöön joko luontivaiheessa tai myös myöhemmin kun kaaviokuva on jo luotu.

Lisää tai aktivoi kaaviokuva ja valitse kaikki kaaviokuvat ja siitä valintaikkunasta edelleen Mallit ja luomasi malli. (Chart, All Charts, Templates).