Yearly Archives: 2015

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

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

Power Query

Uusi apuohjelma millä saadaan noudettua tietoa ulkoisista tietolähteistä Exceliin.
Itse olen tykästynyt ohjelmaan ja sen tuomiin mahdollisuuksiin tuoda tietoja niin SQL-Server tietokannoista kuin muista tietolähteistä. Saan ne helposti ja nopeasti työkirjaan ja kyselyn muokkaaminenkaan ei ole maailman vaikein asia J

Ohjelma toimii itselläni niin versiossa 2010 kuin 2013.

Linkki ohjelman ohjesivuille

Power Query valintanauha:

Power Wuery valintanauha


Query editori:

Query editorin valintanauha


Kannattaa tietysti tarkistaa järjestelmä vaatimukset lataussivulla ennekuin ryhtyy sitä lataamaan ja koneeseen asentamaan.

Linkki lataussivulle

Outlook ryhmän tallennus tiedostoksi

Voit tallentaa luomasi kontaktiryhmän Outlookissa tiedostoksi ja sitten vaikka tuoda sen Exceliin.
Teet sen seuraavasti:
Avaa kontaktiryhmä
Valitse tiedosto tallenna nimellä (File Save as)
Valitse tiedostotyypiksi teksti .txt (Save as type ja Text only)

Exceliin saat ryhmän esim. valitsemalla Tiedot, Hae ulkoiset tiedot, Tekstistä (Data, Get External Data, From Text).

Vaihda (Substitute) funktio

Kun saamme Exceliin tuontitiedoston jossa luku on muotoa 1,234.85 ja se halutaan muuntaa meidän ymmärtämään muotoon ja vielä arvoksi sen voi tehdä funktioilla ARVO ja VAIHDA (VALUE, SUBSTITUTE).

Kaava kirjoitetaan muotoon (solussa A1 on muunnettava arvo)
=ARVO(VAIHDA(VAIHDA(A1;”,”;””);”.”;”,”))
=VALUE(SUBSTITUTE(SUBSTITUTE(A1;”,”;””);”.”;”,”))

Kaavassa vaihdetaan ensin pois pilkku VAIHDA(A1;”,”;””) ja sen jälkeen piste pilkuksi VAIHDA(A1;”.”;”,”). Joudutaan vielä käyttämään funktiota ARVO joka muuntaa kaikki arvoksi laskentaa varten.

Mikäli alkuarvo oli ollut muodossa 1234.85 niin kaava olisi muodossa =ARVO(VAIHDA(A1; “.”;”,”))
=VALUE(SUBSTITUTE(A1; “.”;”,”)).

Tulosta useampi laskentataulu yhdellä kertaa

Kun ryhmittelet Excelissä taulut, voit tulostaa ne yhdellä kertaa.
1. Taulut peräkkäin:
– Osoita ensimmäistä taulua
– Paina alas ja pidä alhaalla vaihtonäppäin (Shift)
– Napsauta viimeistä taulua jonka haluat mukaan
2. Taulut eivät ole peräkkäisiä
– Valitse ensimmäinen taulu
– Paina ja pidä alhaalla Ctrl näppäin
– Napsauta niitä tauluja joita haluta mukaan valintaan

(Vertaa tiedostojen valintaa resurssihallinnassa)

Valitse Tiedosto ja tulosta (File Print) ja varmista että asetuksissa (Settings) on valittuna Tulosta aktiiviset laskentataulukot (Print Active Sheets)
Mikäli sinulla on ylä-/ala- tunniste ja siellä sivunumero saat yhtenäisen sivunumeroinnin kaikkiin tauluihin.