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.

Poista PowerPoint 2013 linkit

PowerPointissa 2013 et pysty valitsemaan useita linkkejä samanaikaisesti ja poistaa niitä.
Siihen tarvitaan pieni VBA-koodi avuksi joka löytyy mm tältä sivustolta:

http://www.pptfaq.com/FAQ01172-Break-all-of-the-links-in-a-presentation.htm

Mikäli sivu ei aukeaisi niin ohessa koodi:

Sub BreakAllLinks()
    Dim oSld As Slide
    Dim oSh As Shape
    For Each oSld In ActivePresentation.Slides
           For Each oSh In oSld.Shapes
           If oSh.Type = msoLinkedOLEObject Then
                   oSh.LinkFormat.BreakLink
           End If
           Next   ' Shape
    Next   ' Slide
 End Sub

 

Lisää potkua kaaviokuvaan

Voit muotoilla kaaviokuvan arvosarjaa (Data Series) omilla kuvilla saadaksesi lisää ilmettä kuvaasi.
Voit käyttää kuvia sarjan täytteenä joko muotoilemalla sitä asianomaisesta kohdasta tai vielä yksinkertaisemmin kopioimalla kuva ensin leikepöydälle ja sen jälkeen liittämällä kuva valittuun datasarjaan.
Esimerkki sarjan muotoilusta

Muotoilu vaihtoehto:
Osoita sarjaa ja napsauta hiiren kakkospainiketta
Valitse Muotoile arvosarjat (Format Data Series)
Valitse Täyttö ja Kuva tai materiaalitäyttö (Fill, Picture or texture fill)
Valitse joko Tiedostosta, leikepöydältä tai ClipArt (File, Clipboard, Clipart (Online v. 2013)
Pinoa vaihtoehto pinoaa kuvan eikä venytä sitä (Stack)

Kopiointi leikepöydän kautta:
Valitse kuva
Kopioi valittu kuva
Napsauta kaaviokuvan sarjaa
Valitse liitä
Tarvittaessa muotoile esim. valitsemalla muotoilusta pinoa.

Ensimmäinen numero merkkijonossa

Oheisella funktiolla etsitään merkkijonon ensimmäinen numeropositio.
Esimerkiksi merkkijonoista abc123 tai a234, tai xyyz23 halutaan pomia teksti ja numerot erikseen. Merkkijono on vaihtelevan pituinen.
Kun ensimmäisen numeron postio tiedetään voidaan edelleen laskentataulussa funktioilla vasen (left), oikea (right), pituus (len) ja poimi.teksti (mid) poimia merkkijonosta tietoja.

Function FirstNumPos(code As String) As Long
    Dim lngLenght As Long
    Dim i As Long
    
    lngLenght = Len(code)
    For i = 1 To lngLenght
        If Mid(code, i) >= Chr(48) And Mid(code, i) <= Chr(57) Then
            FirstNumPos = i
            Exit Function
        End If
    Next
    FirstNumPos = 0
End Function

 

Outlook avaa 2 ikkunaa

Mikäli olet avannut Outlookissa toisen ikkunan, ja et ole sitä sulkenut, Outlook muistaa sen seuraavan kerran kun Outlook avataan ja avaa sinulle kaksi ikkunaa.

Jos olet sulkenut toisen ikkunan ennen ohjelman sulkemista ja se sattui olemaan pääikkuna Outlook muistaa edelleen ne kaksi ikkunaa.

Lisäikkuna tulee sulkea ennen ohjelmasta poistumista jotta ohjelma aukeaisi taas vain yhdessä ikkunassa.

 

Alueen täyttö

Halutessasi voit täyttää nopeasti valitsemasi alue samoilla tiedoilla tai kopioida kaava alueelle seuraavasti:
1. Valitse alue
2. Kirjoita tieto
3. Paina Ctrl – näppäin alas ja pidä alhaalla
4. Paina Enter

Kaavan kopioit samalla menettelyllä seuraavasti:

1. Valitse alue
2. Kirjoita kaava
3. Paina Ctrl – näppäin alas ja pidä alhaalla
4. Paina Enter.
Riippuen miten olet kirjoittanut soluviittaukset, ne sovitetaan aina kyseiseen soluun johon kaava kopioituu.
(Esim. D1 kaava =A1+B1 muuttuu solussa D2 kaavaksi =A2+B2)

Jos sinulla on jo valmis kaava ja haluat sen kopioida alueelle teet sen seuraavasti:
1. Valitse alue lähtien solusta jossa kaava
2. Paina F2 (aktivoi solun)
3. Paina Ctrl – näppäin alas ja pidä alhaalla
4. Paina Enter.
Yllämainittu tapa on kätevä esim. kun haluat kopioida kaavan useammalle riville ja sarakkeelle samanaikaisesti.