Funktioita ja työkaluja - Luento 9

• Luentotaltiointi
• Kertausta muutamista työkaluista
• Rahoitusfunktiot
• Tietokantafunktiot
• Erikoissuodatus
• Makrot
  • Makrojen käyttäminen
  • Makroviruksilta suojaantuminen

Tällä luennolla perehdymme rahoitus- ja tietokantafunktioiden käyttöön. Lisäksi perehdymme erikoissuodatuksen ja makrojen hyötyihin.

Kertausta muutamista työkaluista

• Erkka
• Hakufunktioiden hyödyntäminen tuote-, säätila- ja opiskelijataulukoissa. Lajittelu.
• Tietojen suojaaminen ja oikeellisuustarkistukset - tiedon syöttö turvallisesti.
• Suodattaminen - halutut tiedot näkyville (myös kaavioon).
• Pivot-taulukon raportti
  • Voidaan tehdä kätevästi erilaisia koosteita taulukoiduista tiedoista sekä kaavioita tehdyistä koosteista.
  • Pivot-taulukosta pystyy piilottamaan myös sivukenttiä (engl. Page Fields). Tuplanapsauttamalla sivukenttää saadaan näkyville sivukentän asetukset, joista voidaan valita näytettävät kentät.
• Monia matemaattisia ja tilastollisia työkaluja.

Rahoitusfunktiot

Rahoitusfunktiot on tarkoitettu erilaisen lainojen ja sijoitusten arvojen laskemiseen. Seuraavassa on esitelty muutamia tavallisen käyttäjän tarvitsemia rahoitusfunktioita.

• FV(korko;kaudet_yht;erä;nykyarvo;laji) - (suom. TULEVA.ARVO) - laskee lainan tai sijoituksen tulevaisuuden arvon.
• IPMT(korko;kausi;kaudet_yht;nykyarvo;ta;laji) - (suom. IPMT) - palauttaa lainasta tai sijoituksesta kertyvän kokonaiskoron ajanjaksoittain.
• NPER(korko; erä; nykyarvo; ta; laji) - (suom. NJAKSO) - palauttaa lainan maksuerien lukumäärän.
• PMT(korko;kaudet_yht;nykyarvo;ta;laji) - (suom. MAKSU) - palauttaa tasaeräisen lainan maksuerän suuruuden.
• PPMT(korko;kausi;kaudet_yht;nykyarvo;ta;laji) - (suom. PPMT) - palauttaa tasaeräisen lainan halutun maksuerän lyhennyksen suuruuden.

Funktioissa on useita tietyssä järjestyksessä annettavia parametreja, joten niiden lisäämiseksi on tiedettävä tarkkaan parametrien merkitys. Lisäksi funktioiden ajanjakso täytyy täsmätä maksuerien ja koron osalta, joten parametrien antamisssa kannattaa olla tarkkana. Tarkemmat esimerkit rahoitusfunktioiden käytöstä voit katsoa erillisestä luento9.xls tiedostosta.

Tietokantafunktiot

Tietokantafunktioita ei pidä sekoittaa oikeisiin tietokantoihin, koska niillä ei ole mitään yhteistä. Tietokantafunktiot on tarkoitettu tehokkaaseen luettelomuotoisen datan laskemiseen. Tietokantafunktiot eroavat vastaavista tavallisista funktioista siten, että niille voidaan antaa erilaisia ehtoja laskennan tueksi. Seuraavassa muutamia funktioiden käytössä huomioitavia asioita.

• Ensimmäisenä parametrina määritellään tietokanta eli luettelo, jonka tietojen perusteella suoritetaan laskutoimituksia.
• Toisena parametrina annetaan sarakeotsikko, jonka määräämässä sarakkeessa laskutoimitus tehdään. Sarakeotsikko voidaan antaa tekstinä, sarakkeen järjestysnumerona (luettelon sisällä) tai soluviittauksena sarakeotsikkoon.
• Kolmantena parametrina annetaan ehtoalue, jonka ehtojen perusteella varsinainen laskenta suoritetaan. Ehtoalueelle asetetaan seuraavia rajoituksia:
  • Ehtoalueen ensimmäisellä rivillä on oltava samat otsikot kuin luettelossa, josta tietoja haetaan.
  • Kaikkia otsikoita ei tarvitse olla ehtoalueella, vaan ainoastaan niiden sarakkeiden otsikot, joiden mukaan laskentaa halutaan rajoittaa.
  • Samalla ehtorivillä olevien ehtojen on oltava voimassa yhtäaikaa samalla luettelon rivillä eli ehto on JA-tyyppinen.
  • Eri riveillä ja samaa saraketta rajoittavien ehtojen voimassa olo on TAI-tyyppinen.
  • Alueessa ei saa olla yhtään kokonaan tyhjää ehtoriviä.

Tarkempia esimerkkejä voit tarkastella erillisestä luento9.xls -tiedostosta. Lisää tietokantafunktioista voit lukea luentomonisteen sähköisestä versiosta.

Erikoissuodatus

Erikoissuodatuksella saadaan annettua monipuolisia suodatusehtoja alueelle. Lisäksi pikasuodatuksesta poiketen tiedot voidaan myös kopioida toiseen laskentataulukkoon. Erikoissuodatuksesta kannattaa huomata muutama asia.

• Voidaan käyttää täsmälleen samanlaisia ehtoalueita kuin tietokantafunktioiden yhteydessä.
• Voidaan suodattaa paikallaan tai voidaan kopioida suodatetut tiedot jollekin toiselle laskentataulukolle.
• TAI-ehto määritellään useammalla rivillä.
• JA-ehto määritellään samalla rivillä, jolloin saman sarakkeen otsikoita voi olla useampiakin.
• Tietojen kopioinnin yhteydessä täytyy ensin mennä laskentataulukkoon, jonne tiedot tuodaan. Kopiointi onnistuu vain aktiiviselle laskentataulukolle.
• Tiedoista voidaan kopioida myös yksittäisiä sarakkeita. Tällöin määritellään haluttujen sarakkeiden otsikot kopionnin kohteeseen.
• Erikoissuodatus sopii hyvin käytettäväksi esimerkiksi Pivot-taulukkoon tai muun työkalun käyttöön haluttavien tietojen "alkusuodattamisessa".

Makrot

Makrot ovat käteviä työkaluja erilaisten toistuvien toimenpiteiden automatisointiin. Makrojen avulla usein toistuva ja useita toimintoja vaativa toimenpide saadaan yhden napin painalluksen taakse. Seuraavassa muutama makrojen huomion arvoinen piirre.

• Makroja käytetään sekä tekstinkäsittelyssä, taulukkolaskennassa että esitysgrafiikassa.
• Makro on kokoelma erilaisia toimenpiteitä, jotka on koottu yhteen.
• Makrot koostuvat ohjelmointikielisistä (esimerkiksi VBA) komennoista.
• Makroja voidaan tehdä nauhoittamalla tai käsin kirjoittamalla.
  • Nauhoitetaessa makroon tallentuvat tiedot suorittamistamme toimenpiteistä, kuten esimerkiksi solujen valinnasta.
  • Yksinkertaisten makrojen tekemiseen ei siis tarvitse osata ohjelmoida.
  • Esimerkiksi taulukon suodattaminen voidaan nauhoittaa makroksi.
  • Monimutkaisemmat makrot joutuu kirjoittamaan käsin ainakin osittain.
  • Makroja käsin korjailemalla saadaan tehokkaita ja yleiskäyttöisiä työkaluja.
• Nauhoittaminen aloitetaan valikkokomennolla Tools | Macro | Record New Macro (suom. Työkalut | Makro | Nauhoita uusi makro), jonka jälkeen tehdään makroon tulevat toimenpiteet rauhassa.
• Nauhoittaminen loppuu Macro-työkalurivin Stop Recording (Lopeta nauhoitus)-painikkeella tai Tools | Macro (suom. Työkalut | Makro) valikon vastaavalla valinnalla.
• Makron nauhoitusnopeus on eri nopeus kuin ajonopeus.
• Makroilla voidaan myös tehdä omia funktioita.
• Makrojen nimiin ei laittaa välilyöntejä.
• Makroviruksien olemassaolo täytyy muistaa!

Seuraavassa muutamia makroesimerkkejä työkalujen käyttämiseksi.

Seuraava erikoissuodatuksen tekevä makro on täysin nauhoitettu ja sen tarkoituksena on suodattaa paikallaan kaikkitiedot-alueen tietoja riviehto-alueen ehtojen mukaisesti. Makro on siitä kätevä, että riviehtoalueen ehtoja voidaan muuttaa vapaasti, joten makroa voidaan käyttää useammilla suodatusehdoilla.

Sub suodatus()
' Käytetään erikoissuodatustyökalua luettelo-nimiseen alueeseen
' Suodatusehdot löytyvät ehdot-nimiseltä solualueelta

    Range("luettelo").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _
        :=Range("ehdot"), Unique:=False
End Sub

Tiedot on suodatettu voidaan kaikkien tietojen näyttämiseksi nauhoittaa toinen makro, jossa tehdään valikkokomennon Data | Filter | Show All toiminto.

Sub poista_suodatus()
' Poistetaan suodatus
    ActiveSheet.ShowAllData
End Sub

Makro uuden säätyypin lisäämiseksi:

' Lisätään uusi säätilatyyppi kysymysikkunan avulla
Sub Lisaa_saatilatyyppi()
    Sheets("Säätyypit").Select
    Rows("1:1").Select
    Selection.Insert Shift:=xlDown
    ActiveCell.FormulaR1C1 = InputBox("Anna uusi säätilatyyppi:")
End Sub

Nauhoittamalla voidaan tehdä myös muita käteviä automatisointeja esimerkiksi lajittelua, pikasuodatusta, tietolomaketta ja välisummien lisäämistä varten. Nauhoittamista vaativampien makrojen tekemiseksi tarvitaan enemmän ohjelmointitaitoa. Esimerkkinä monimutkaisemmasta ja käsin tehdystä makrosta toimii Excel-taulukon muuttaminen HTML-taulukoksi. Jos makrojen kanssa työskentely kiinnostaa enemmän, niin VBA-ohjelmoinnista kannattaa lukea enemmän artikkelista VBA-perusteet erittäin lyhyesti.

Makrojen käyttäminen

Makroja voidaan käyttää (suorittaa) kätevästi esimerkiksi laskentataulukolle lisätyn painikkeen avulla. Painikkeen lisääminen onnistuu seuraavasti:

• Välitse näkyville Forms- (suom. Lomakkeet) -työkalurivi valikosta View | Toolbars (suom. Näytä | Työkalurivit).
• Valitse työkalurivin työkaluista Button (suom. Painike). Oikeassa reunassa toinen ylhäältä.
• Raahaa hiiren vasen pohjassa laskentataulukkoon haluamasi painikkeen kokoinen alue.
• Valitse avautuvasta Assign Marco (suom. Yhdistä makro) -ikkunasta painikkeeseen haluttu makro.
• Anna painikkeelle kuvaava nimi hiiren oikean alta löytyvällä Edit Text (suom. Muokkaa tekstiä) -komennolla.
• Makroa voit nyt käyttää painiketta napsauttamalla.

Makroja voidaan käyttää myös valikkokomennolla Tools | Macro | Macros (suom. Työkalut | Makro | Makrot) avatuvasta ikkunasta. Ensin ajettava makro täytyy valita aktiiviseksi ja sen jälkeen sen voi ajaa Run-komennolla. Makroihin voi myös yhdistää pikanäppäimiä.

Makroviruksilta suojaantuminen

Vaikka nykyisellään makroviruksia on liikkeellä vähän, niin niiden kanssa kannattaa edelleen olla varovainen. Jos työkirja sisältää makroja, mutta niitä ei tiedä tarvitsevansa, niin niitä ei kannata ottaa käyttöön.

Makrosuojauksista johtuu, ettei edes hyödyllisiä makroja päästä ajamaan oletusasetuksilla. Tällöin kannattaa tarkistaa makrojen suojaustaso valikkokomennolla Tools | Macro | Security (suom. Työkalulut | Makro | Suojaus). Avautuvasta ikkunasta suojaustason saa näkyville Security Level (suom. Suojaustaso) -välilehdeltä. Makrojen ajamiseksi suojaustaso kannattaa laskea Medium (suom. Normaali)-tasolle, jolloin makrojen käyttö varmistetaan käyttäjältä asiakirjan avaamisen yhteydessä. Kun asetukset on hyväksytty, niin työkirja täytyy avata uudelleen, jolloin näkyville pitäisi tulla makroista aiheutuva kysely.