Monthly Archives: March 2013

Dynaamiset nimet 2

Leave a reply

Dynaamisille asioille riittää paljon eri soveltuvuus aloja tässä yksi esimerkki lisää.

Valitaan listaruudusta osasto, tuote tai jokin muu ehto ja sen perusteella tuodaan lähdetiedoista tietoja.

Listaruudun tekoon käytän tässä esimerkissä toimintoa Tietojen kelpoisuuden tarkistaminen joka on Datatyökaluryhmässä Tiedot valintanauhalla. (Data,Data tools, Data Validation).

Esimerkki:

Esimerkki nimen käytöstä

Esimerkki nimen käytöstä

Esimerkissä olen laittanut kaikki valittavissa olevat maat omaan tauluun ja nimennyt sen parametrit.

Tämän jälkeen olen määrittänyt nimen Maat jonka viittaus on:
=SIIRTYMÄ(Parametrit!$A$1;1;0;LASKE.A(Parametrit!$A:$A)-1)

SIIRTYMÄ (OFFSET), LAKE.A (COUNTA)

Nimi määritellään kohdasta Kaavat, Määritetyt nimet, Määritä nimi (Formulas, Defined Names, Define Name).

Annetaan Nimi -ruutuun kuvaava nimi, Laajuus on työkirja ja viittaukseen yllä mainittu kaava.
Olen laittanut kaavaan -1 koska nimisarakkeessa on otsikko ja sitä en halua mukaan listaan.
Tämän jälkeen olen laittanut soluun B3 kelpoisuustarkistuksen joka näyttää luettelon.
Lähteeksi olen laittanut nimen jonka loin aiemmin.

Huomaa että lähteen luettelo alkaa = -merkillä.

Kelpoisuustarkistus

Kelpoisuustarkistus

Tämän jälkeen rakennetaan kaavat jotka hakee tai poimii tietoja valitun arvon mukaan.
Esimerkkiin olen laittanut esimerkinomaisesti seuraavat kaavat:
=LASKE.JOS(C11:C2165;B3) (COUNTIF)
=SUMMA.JOS(C11:C2165;B3;L11:L2165) (SUMIF)

Jos haluat poimia monta tietoa samasta lähteestä, samalta riviltä harkitse VASTINE (MATCH) ja INDEKSI (INDEX) funktioiden käyttöä PHAKU (VLOOKUP) funktion sijaan. Tämä on monesti tehokkaampaa.

Dynaaminen kaavio

2 Replies

Taas kerran jouduin tappelemaan dynaamisen kaavion kanssa ja laitan esimerkin nyt myös tähän.

Syy, miksi käytän dynaamista kaaviota, on esimerkiksi kun haluan kaaviokuvan jatkuvan automaattisesti, kun datasarjaan tulee uusia elementtejä.

Voihan sen myös tehdä kopioi-liitä menetelmällä, mutta kun on kiire ja tapahtuma on toistuva, haluaa mielellään jotakin “helpompaa”.

Dynaaminen kaaviokuva rakentuu datasarjoista, joille on annettu nimet. Nimet taas saadaan dynaamisiksi käyttäen funktioita SIIRTYMÄ (OFFSET) ja LASKE.A (COUNTA).

SIIRTYMÄ-funktion syntaksi on:
SIIRTYMÄ(viittaus; rivit; sarakkeet; [korkeus]; [leveys])

Kun olen määritellyt nimet, luon kaaviokuvan normaalilla tavalla.
Tämän jälkeen lähden muokkaamaan datasarjoja ja korvaan siinä olevat viittaukset luomillani nimillä.

=SARJA(Esimerkki!$A$2;kd_kaavio.xlsx!Kuukaudet;kd_kaavio.xlsx!Tuote_1;1)

Huomaa, että sinun on annettava datasarjassa koko työkirjan nimi, pelkkä datasarjan nimi ei riitä, vaikka nimen laajuudeksi on määritelty työkirja.

Alla oleva kuvassa on esitelty kaavio, työkirjassa olevat nimet ja kaaviokuvan sarja.

Dynaaminen kaavio määrityksineen

Dynaaminen kaavio

Nimen määrittely

Valitse Kaavat/Määritetyt nimet, Määritä nimi (Formulas/Defined name, Define name).
Anna nimi valintaikkunassa. Viittauskohtaan kirjoitat kaavan.

 

Määritä nimi

Määritä nimi

Kuvassa on kuukaudet-nimen määrittely.
=SIIRTYMÄ(Esimerkki!$A$1;0;1;;LASKE.A(Esimerkki!$1:$1))
SIIRTYMÄ(viittaus; rivit; sarakkeet; [korkeus]; [leveys])
viittaus = taulun nimi ja soluviittaus josta aloitetaan
rivit = 0 koska ollaan samalla rivillä kuin viittaus
sarakkeet = 1 koska kuukaudet alkaa viittauksesta nähden seuraavasta sarakkeesta
korkeus = ;; ei tarvita koska ollaan samalla rivillä
leveys = LASKE.A(Esimerkki!$1:$1), lasketaan rivin 1 täytetyt solut

Tuotenimet on määritelty vastaavasti:

=SIIRTYMÄ(Esimerkki!$A$2;0;1;;LASKE.A(Esimerkki!$2:$2)-1)
Huomaa nimen muoto: Tuote_1 koska nimissä ei saa olla välilyöntejä.
-1 kaavassa tarvitaan, koska tieto alkaa toisesta sarakkeesta, ensimmäisessä kun on riviotsikko.

Kun nimet on määritelty, valitsen kaaviokuvan datasarjan ja korvaan siinä olevat viittaukset luomillani nimillä.
Tässä työskentelen kaavarivillä, koska se on mielestäni helpointa näin.

=SARJA(Esimerkki!$A$2;kd_kaavio.xlsx!Kuukaudet;kd_kaavio.xlsx!Tuote_1;1)
Huomaa tosiaan työkirjan nimi: kd_kaavio.xlsx!

Kun työskentelet valintaikkunoissa, kuten tuo nimenmäärittelyikkuna, ja haluat siirtyä kirjoittamassasi kaavassa nuolinäppäimellä, paina ensin funktionäppäintä F2, muussa tapauksessa se onnistu.

 

Kuukauden lisäys ja solujen tyhjennys

Leave a reply

Kuukauden lisäys

Taas kerran tarvitsin toiminnon jolla lisätään yksi kuukausi annettuun päivämäärään.
Ratkaisin sen omalla funktiolla “LisaaKuukausi”
VBA:ssa voit käyttää DateAdd komentoa päivämäärien laskentaan.

Oheisessa funktiossa syötän funktiolle päivämäärä johon lisätään kuukausi.
Funktiossa muotoillaan palautettava päivämäärä ja jotta se palautuisi muunlaisena kun päivän sarananumerona olen vielä määritellyt funktion palautustyypiksi variantin.
Kuukausi lisätään komennolla DateAdd(“m”, 1, pvm) jossa
m – kertoo että lisätään kuukausi
1 – kuinka monta kuukautta lisätään
pvm – funktiolle annettu argumentti

Tämä kaikki muotoillaan Format komennolla muotoon dd.mm.yyyy joka on sama kuin pp.kk.vvvv.

Function LisaaKuukausi(pvm As Date) As Variant

     LisaaKuukausi =  Format(DateAdd("m", 1, pvm), "dd.mm.yyyy")

End Function

Solujen tyhjennys

Tarvitsin myös toiminnon joka poistaa soluista kaiken muun paisti kaavat.
Ohessa proseduuri jota käytin.
Se ei ole täydellinen mutta toimi juuri siinä kun sitä tarvitsin.
Valitaan alue ja suoritetaan ohjelma.
Ohjelma tarkistaa jos solussa on kaava (HasFormula) ja jos ei ole niin solu tyhjennetään.

Sub PoistaSisalto()

Dim s As Range
For Each s In Selection
    If Not s.HasFormula Then
       s.ClearContents
    End If
Next
End Sub


Lisää esimerkit koodi-ikkunaan

Yllä olevat proseduurit voit lisätä työkirjan makroiksi esim. seuraavalla tavalla:
Kopioi esimerkin koodi

Valitse koodi
Paina Ctrl + c (kopioi)
Paina Alt+F11
Valitse Insert – Module
Paina Ctrl + v (liitä)

Tämän jälkeen löydät funktion työkirjassa kohdasta lisää funktio, luokka käyttäjän määrittämä (User defined)

SUB –proceduurin voit suorittaa esim. kohdasta Näytä (valintanauha), Makrot (ryhmä), Makrot, Näytä makrot. (View, Macros, Macros, Show Macros)

 

Sivunumerot

Leave a reply

Sivunumero

  • Wordin sivunumeron lisäät ylä- alatunnisteeseeen kätevimmin kaksoisnapsauttamalla ylä- alatunnisteta.
  • Aseta kohdistin haluttuun kohtaan
  • Valitse Ylä- alatunnistetyökalujen Rakenne valintanauhasta Sivunumero, Nykyinen sijainti (Page number, Current position)

    Ylä- ja alatunniste valintanauha

    Ylä- ja alatunniste

Kokonaissivumäärä

Mikäli haluat laittaa asiakirjaan kokonaissivumäärän ylä- alatunnisteeseen, saat sen seuraavalla tavalla:

  • Mene kohtaan johon sen haluat
  • Valitse Pikaosat ja sen valikosta Kenttä (Quick parts, Field)
  • Valitse kenttäluettelosta kenttä NumPages.
Lisää kenttä valintaikkuna

Kentän lisäys

 

Osan sivujen määrä

Jos olet jakanut asiakirjasi osiin ja haluat osan sivumäärän, löydät sen Pikaosien kentistä nimellä  SectionPages.

Tiedon jako sarakkeisiin 2

Leave a reply

Tiedon jako sarakkeisiin toimintoa voit myös hyödyntää esim. päivämäärän muokkaukseen.

Saat Exceliin päivämäärän joka on tekstiä ja esim. muodossa 20130315.

Muutetaan Excelin ymmärtämään päivämäärämuotoon:

  • Valitse solu, sarake tai alue jossa tämä päivämäärä esiintyy.
  • Sen jälkeen valitse Tiedot valintanauhan Datatyökaluryhmästä Teksti sarakkeisiin – toiminnon (Data/Data tools, Text to columns).
  • Vaiheessa kolme määrittele päivämäärän muoto.

P (D)=päivä, K (M)=kuukausi, V (Y)=Vuosi.

  • Esimerkissä valitaan muoto VKP (YMD)
  • Napsauta Valmis (Finish).
Tietotyypin valinta

Tietotyypin valinta

Esimerkin tyyppisestä merkkijonosta voidaan myös poimia tietoja funktioiden avulla.

Ohessa kolme käyttökelpoista funktiota:
VASEN (LEFT), poimii vasemmalta määritellyn määrän merkkejä
OIKEA (RIGHT) , poimii oikealta määritellyn määrän merkkejä
POIMI.TEKSTI (MID), poimii merkkijonon annetusta kohdasta ja määritellyn määrän merkkejä.

Syntaksi: POIMI.TEKSTI(teksti; aloitusnro; merkit_luku)

Esim.:

A

B

1

20130315

 =VASEN(A1;4) → 2013

2

20130315

 =OIKEA(A1;2) → 15

3

20130315

 =POIMI.TEKSTI(A1;5;2) → 03

Jaa tieto sarakkeisiin

Leave a reply

Halutaan jakaa samassa solussa oleva tieto useisiin sarakkeisiin.

Esim. alla olevan kaltainen tieto

A

1

 Sari;Sarake;00100;Helsinki;10.5

halutaan muotoon:

A

B

C

D

E

1

 Sari Sarake 00100

Helsinki

10,5

Kohteessa on kaksi kohtaa jotka vaativat erikoishuomiota.

1) Postinumeron etunollat on säilyttävä
2) Desimaaliluku 10.5 on muutettava muotoon 10,5. Muutoin Excel muuntaa sen päivämääräksi.

Jako tehdään seuraavasti:

Valitse alue jossa tieto sijaitsee
Valitse Tiedot/Datatyökalut, Jaa teksti sarakkeisiin (Data/Data tools, Text to Columns)

Data/Datatyökalut

Data/Datatyökalut

Saat kolmivaiheisen opasteen

Ensimmäisessä vaiheessa valitse Eroteltu (Delimited) ja napsauta Seuraava (Next)
Toisessa vaiheessa valitse erotinmerkki joka on esimerkissä puolipiste (;) (Semicolon) ja napsauta Seuraava (Next)
Vaiheessa kolme valitse sarake jossa postinumero (00100) ja määrittele sen tietotyyppi tekstiksi (Text)
Seuraavaksi napsauta painiketta Lisäasetukset (Advanced)

Määrittele avautuvassa valintaikkunassa desimaalierottimeksi piste (.) (jos datassa on tuhat erottimena pilkku (,) määrittele se myös tähän valintaikkunaan.
Napsauta OK

Tietotyypin määrittely

Tietotyypin määrittely

Napsauta kolmannen vaiheen valintaikkunan Valmis (Finish) painiketta.

 Tämä toimii samalla tavalla kun tuot ulkoisen teksti tai csv tiedoston Exceliin. Tuonnin aloitat kohdasta Tiedot/Hae ulkoiset tiedot, Tekstistä (Data/Get External data, From Text)

Hae ulkoiset tiedot

Hae ulkoiset tiedot

 

Sähköpostiosoitteen poiminta hyperlinkkisolusta

Leave a reply

Viikko takana ja mm tämä asia viikolla esiin.

Osoitelista Excelissä jossa on hyperlinkit sähköpostiositteissa.
Eli kun osoitat solua Excel näyttää sinulle “mailto:nimi@osoite.com”
Jos haluat vaikka kopioida kaikki sähköpostiositteet erikseen liittääksesi ne johonkin niin se on mahdotonta.

Otetaan VBA käyttöön ja homma hoituu.

Luodaan funktio VBA:han joka poistaa mailto: -osan ja kirjoittaa soluun pelkän sähköpostiosoitteen.

Paina Alt+F11 ja VBA avautuu
Valitse VBA –editorissa Insert, Module
Kopioi alla oleva koodi moduuliin

Function SahkopostiOsoite(HyperlinkkiSolu As Range)
       SahkopostiOsoite = Replace(HyperlinkkiSolu.Hyperlinks(1).Address, “mailto:”, “”)
End Function

Siirry taulukkoosi ja valitse Kaavat/Lisää funktio, valitse luokka Käyttäjän määrittämät (Formulas/Insert Function, User defined)
Valitse luomasi funktio ja anna sille argumentiksi solu joka sisältää hyperlinkin.
Esim:
=SahkopostiOsoite(B2)

Se on siinä!

Työkirjan hallintaa

Leave a reply

Työtila

Excel 2010:ssä (versiossa 2013 tämä ominaisuus on poistettu) voit tallentaa työtilan jossa sinulla on useampi työkirja auki samanaikaisesti.

Kun avaat tallennetun työtilan Excel avaa kaikki työkirjat samaan järjestykseen mitä sinulla oli työtilan tallennettaessa.
Työtilan tiedostomuoto on .xlw.
Toiminnon Tallenna työtila (Save Workspace) löydät Näytä – valintanauhan ryhmästä Ikkuna (View, Window).

 Sulje kaikki työkirjat

Jos haluat sulkea kaikki avatut työkirjat yhdellä komennolla, laita Sulje kaikki (Close all) komento pikatyökaluriville. Toiminto sulkee kaikki avoinna olevat työkirjat. Kysyy tallennuksesta mikäli niitä ei ole tallennettu.

Pikatyökalurivin muokkaus on kuvattu kohdassa: Tulostuksen koko ikkunan esikatselu Excel 2010:ssä.

Näytä -valintanauha

Taulun kopiointo

Tapa 1
Nopein tapa kopioida taulu on seuraava:

Osoita taulun nimeä
Paina ja pidä näppäimistön CTRL –näppäin alhaalla
Vedä hiirellä taulun nimestä hiiren vasenpainike alas painettuna haluttuun kohtaan jonne kopion haluat
Vapauta hiiren vasenpainike, vapauta CTRL –näppäin.

Hiiren osoittimen kohdalla näkyy kuvake + -merkillä.

Tapa 2
Toinen tapa on osoittaa taulun nimeä ja napsauttaa hiiren kakkospainiketta ja valita valikosta Siirrä tai kopioi (Move or Copy)
Muista rastittaa vaihtoehto Tee kopio (Create copy). Mikäli unohdat tämän taulu siirretään.
Tämä on tapa kun haluat kopioida tai siirtää taulun toiseen työkirjaan joko olemassa olevaan tai uuteen.

Siirrä tai kopioi taulu

Taulujen ryhmittely työkirjassa

Kun haluat tulostaa useamman taulun samasta työkirjasta ryhmittele ne ja saat samalla tulostuskomennolla kaikki tulostettua yhdellä kertaa.
Tällä tavalla saat myös jatkuvan sivunumeroinnin kaikille tauluille jos olet sen ylä- tai alatunnisteeseen luonnut.

Jos sinulla on samankaltaiset taulut ja haluat lisätä jotakin niihin tai muuttaa muotoilua voit päästä helpommalla ryhmittelemällä taulut ensin.
Huomaa että kaikki tekemäsi muutokset laitetaan kaikkiin ryhmiteltyihin tauluihin samoihin kohtaan.

Taulujen ryhmittely:

Peräkkäiset taulut:
Valitse ensimmäinen taulu
Pidä Vaihto (shift) –näppäin alhaalla ja napsauta viimeisen mukaan halutun taulun nimeä.

Ei peräkkäisten taulujen valinta:
Valitse ensimmäinen taulu
Paina ja pidä näppäimistön Ctrl – näppäintä ja napsauta niitä taulujen nimiä jotka haluat valintaan.

 

 

Excel-apuohjelma (Add-In)

Leave a reply

Kun luodaan omia funktioita joita tarvitaan usein, voi ne tallentaa Excel-apuohjelmiksi (AddIn) jolloin ne ovat käyttöönoton jälkeen aina käytettävissä.

Tallennus Excel-apuohjelmaksi

Tiedoston tallentaminen AddIn (Excel apuohjelma) –tidedostoksi:
Tiedosto, Tallenna nimellä, Tiedostomuoto: Excel-apuohjelma (.xlam).
Valittuasi tiedostomuodon Excel ehdottaa AddIns –kansiota.

Excel-apuohjelman käyttöönotto

Valitse Tiedosto, Asetukset, Apuohjelmat
Valitse Excel-apuohjelmat listaruudusta Hallitse
Napsauta Siirry
Valitse valintaikkunasta Apuohjelmat luomasi apuohjelma.
Napsauta OK

 Oman funktion käyttö

Luomasi funktion saat käyttöön valintanauhan Kaavat Lisää funktio työkalulla.
Luomasi funktion löydät funktioluokasta Käyttäjän määrittämä tai siitä luokasta jonne sen määrittelit.

Huomaa että voit myös luoda omia funktioluokkia tai lisätä funktion johonkin olemassa olevaan funktioluokkaan.

Apuohjelman poisto

Vaikka poistat apuohjelman käytöstä näkyy se edelleen Apuohjelmat valintaikkunassa.
Poista tiedosto siitä kansiosta jonne olet sen tallentanut
Valitse uudestaan apuohjelmat
Valitse poistettu apuohjelma valintaikkunasta
Saat ilmoituksen että ohjelmaa ei löydy ja poistetaanko se listasta
Vastaa Kyllä ja poistettu ohjelma häviää listasta.

Funktion asetukset

Voit antaa luomallesi funktiolle kuvauksen ja muita asetuksia vba:sta käsin menetelmällä:
Application.MacroOptions.
Syntaxi:
expression.MacroOptions(Macro, Description, HasMenu, MenuText, HasShortcutKey, ShortcutKey, Category, StatusBar, HelpContextID, HelpFile, ArgumentDescriptions)

Määrittelemällä Cetegory voit liittää luomasi funktion johonkin olemassa olevaan funktioluokkaan tai luoda oma luokka funktiollesi.

Menetelmän suoritat vain kerran joten voit suorittaa sen vaikka Immediate windows:ta (Ctrl+G)

Esimerkki:
Application.MacroOptions macro:=”Pankkiviitenumero”, Category:= 1,
Description:=”Laskee pankkiviitenumeron annetusta numerosarjasta”, _
ArgumentDescriptions:=Array(“Numero josta viitenumero lasketaan”)

Liittää makron nimeltä Pankkiviitenumero luokkaan 1 joka on rahoitusfunktiot. Description kohta luo selitteen funktiolle ja ArgumentDescriptions taas selitteen argumentille.

PowerPivotin käyttöönotto Excel 2013 versiossa

Leave a reply

Tiedosto, Asetukset – Apuohjelmat
Valitse valintaikkunan alaosasta kohdasta Hallitse COM-apuohjelmat ja napsauta Siirry
Rastita vaihtoehto Microsoft Office PowerPivot for Excel 2013
Napsauta OK

 

PowerPivot tuli Excelin versioon 2010 ja se on Microsoftin ilmainen lisäohjelma.

Löydät versiot 2013 ja 2010 ladattavaksi näistä linkistä:

Suomeksi:

http://office.microsoft.com/fi-fi/excel/powerpivotin-lataaminen-HA101959985.aspx

Englanniksi:

http://office.microsoft.com/en-us/excel/download-powerpivot-HA101959985.aspx