Category Archives: Excel

Kaaviokuvan tallennus malliksi

Leave a reply

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

Leave a reply

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

Vaihda (Substitute) funktio

Leave a reply

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

Leave a reply

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.

Lisää potkua kaaviokuvaan

Leave a reply

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.

Alueen täyttö

Leave a reply

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.

 

Eroon jako/0 ilmoituksesta

Leave a reply

Mikäli jaetaan luku nollalla (0) tai tyhjällä solulla saadaan virheilmoitus #Jako/0! (#Div/0!).
Lomakepohjissa tämä saattaa olla riesa ja kauneusvirhe josta halutaan päästä eroon.

Helpoin tapa on käyttää funktiota JOSVIRHE (IFERROR).
Eli esimerkiksi jos jaetaan solu A1 solulla B1 ja soluun C1 halutaan jakotulos mutta ei virheilmoitusta niissä tapauksissa jossa B1 on nolla tai tyhjä. Tällöin kaava C1:ssä olisi seuraavan lainen =JOSVIRHE (A1/B1;””), =IFERROR(A1/B1;””).

 

 

 

Kahden solun yhdistäminen

Leave a reply

Yhdistetään A ja B sarakkeen solut ja laitetaan välilyönti väliin
Esim.:
Solussa A1 = Aku ja B1 = Ankka -> C1 = Aku Ankka
Funktioilla:
KETJUTA (CONCATENATE)
=KETJUTA(A1;” “;B1)
tai
=A1&” “&B1
(Välilyönti laitetaan kahden lainausmerkin väliin koska se on tekstiä kaavassa)

Makrolla voit tehdä sen esim. seuraavalla tavalla:

Sub Yhdista()
'ohjelma yhdistää A ja B sarakkeen solut C sarakkeeseen
 'lähdetään liikkeelle A1:stä (cells(1,1))
 'Cells parametreja muuttaen voidaan valita toinen lähtösolu ja
 'myös sarake jonne yhdistetty tulos laitetaan

Dim rwIndex As Long
Dim i As Long
'lasketaan täytettyjen solujen määrä A1:stä alaspäin
rwIndex = Sheets(1).Cells(1, 1).End(xlDown).Row
For i = 1 To rwIndex
     Cells(i, 3).Value = Cells(i, 1) & " " & Cells(i, 2)
Next
End Sub

 

 

GETPIVOTDATA

Leave a reply

Joskus kun halutaan viitata Pivot-taulukon kenttään, taulukon ulkopuolelta niin että kaava voitaisiin kopioida, aiheuttaa Excelin oletus asetus hieman harmia.
Nimittäin kun kaava kirjoitetaan ja jos napsautetaan Pivot-taulukon kenttää saadaan automaattisesti funktio GETPIVOTDATA.
Tästä asetuksesta pääsee pois seuraavasti:
Valitaan Tiedosto – Asetukset (File –Options)
Valitaan Kaavat (Formulas) kohta
Otetaan valinta pois kohdasta “Käytä Pivot-tietojen hakufunktiota käytettäessä Pivot-taulukon viittauksia” (Use GetPivotData functions for PivotTable references).
Nyt viittaukseksi tulee suhteellinen viittaus joka on helppo kopioida tarvittaessa.

 

 

Poimi satunnaisotos taulukosta/listasta Excelissä

Leave a reply

Poimi satunnaisotos taulukosta/listasta Excelissä

Lisää sarake taulukkoon/listaan.
Kirjoita uuteen sarakkeeseen otsikko.
Siirry otsikon alla olevaan soluun ja kirjoita kaava =SATUNNAISLUKU()
(englanniksi =RAND()).
Kopioi kaava listan jokaiselle riville asianomaisessa sarakkeessa.
Lajittele tämän sarakkeen mukaan, ja sinulla on joka lajittelun jälkeen uusi lopputulos.

Nyt kun kopioit alusta ne rivit, joita haluat analysoida, sinulla on satunnaispoiminta.

Excel laskee satunnaisluvun uudestaan joka kerran, kun teet muutoksia taulukkoon.

Satunnaisluku

Kuukausien lukumäärä

Leave a reply

Kuinka monta kuukautta on kahden päivämäärän välillä?

Ohessa kaava, joka laskee täysien kuukausien lukumäärän (lukumäärä siis pyöristetään alaspäin).

Huomaa, että tulossolu on muotoiltava numeroksi tai muotoon yleinen.

=JOS(PÄIVÄ(B1)>=PÄIVÄ(A1);0;-1)+(VUOSI(B1)-VUOSI(A1))*12+KUUKAUSI((B1)-KUUKAUSI(A1))

=IF(DAY(B1)>=DAY(A1);0;-1)+(YEAR(B1)-YEAR(A1)) *12+MONTH(B1)-MONTH(A1)

Excelissä on myös piilotettu funktio PVMERO, (DATEDIF) jota voi käyttää.

Syntaksi: PVMERO(ens_pvm;viim_pvm;yksikkö)

Yksikkö:

y – kokonaisien vuosien määrä

m – kokonaisten kuukausien määrä

d – päivien määrä

md – Ens_pvm:n ja viim_pvm:n välisten kuukausien määrä. Päivämäärien päivillä ja vuosiluvuilla ei ole merkitystä

yd – Ens_pvm:n ja viim_pvm:n välisten päivien määrä. Päivämäärien vuosiluvuilla ei ole merkitystä.

Esim. joka palauttaa kahden päivämäärän välisen kuukausien määrän:

=PVMERO(A2;B2;”m”)

Kun Excel ei ymmärrä numeroita

Leave a reply

Joskus saattaa esiintyä sellainen tapaus, että esim. SUMMA (SUM) funktio (koskee myös muita funktioita) ei laske yhteen lukuja, vaikka kaava tyyppiä =A1+A2+… jne. edelleen toimii.

Tällöin luku saattaa olla sellaisessa muodossa että Excel tulkitsee sen tekstiksi ja solun muotoileminen numeroksi ei auta.

Ongelman korjaamiseksi on monta keinoa ja ohessa muutama, joita itse käytän usein.

– Uusimmissa Exceleissä aluetta valittaessa (numerot) saa toimintotunnisteen, josta valitaan vaihtoehto “muunna numeroiksi” (Convert to number)

– Valitse alue (numerot) ja muotoile valittu alue numeroksi. Tämän jälkeen kirjoita numero 1 johonkin tyhjään soluun ja kopioi se leikepöydälle.

Valitse alue, jossa juuri numeroiksi muotoilemasi luvut ovat ja valitse Liitä, Liitä määräten ja valintaikkunassa valitse Arvot ja Kerro (Paste, Paste Special – Values, Multiply).

– Valitse alue (numerot) ja muotoile valittu alue numeroksi. Kirjoita kaava uuteen sarakkeeseen ja kerro tämän solun arvon 1:llä.

Muita keinoja:

Funktio POISTA.VÄLIT (TRIM) poistaa välilyönnit.

POIMI.TEKSTI (MID) -funktiolla voit poimia merkkijonosta haluamasi merkit tietystä positiosta lähtien.

Funktio SOLU (CELL) kertoo mitä solussa on. Esim. SOLU(“tyyppi”;A1), palauttaa “h” jos solu on tyhjä, “o” jos solussa on teksti arvo ja “r” jos solussa jotakin muuta.

Vastaavat englanniksi =CELL(“type”;A1) “b” jos solu on tyhjä, “l” jos solussa tekstiä, “v” jos arvo.

Huomaa eri palautusarvot eri kieliversioissa.

Jos oikein muistan, niin joissakin Excel-versioissa oli käytettävä englanninkielisiä nimiä argumenteissa, jotta funktio toimisi, vaikka itse Excel olisikin suomenkielinen.

Jos desimaalierotin on pisteenä, Excel pyrkii muuttamaan arvon päivämääräksi (riippuu koneen maa-asetuksista).

Voit muuttaa Excelin tulkitsemaan pisteet desimaalierottimiksi Excelin asetuksista. Tämän asetuksen voit tehdä tilapäisesti tietojen tuonnin tai liittämisen ajaksi. Kun tiedot on tuotu onnistuneesti, muuta asetus takaisin ja sinulla on (toivottavasti) pisteet pilkkuina.

– Valitse Tiedosto/Asetukset, (File/Options).

– Valitse Lisäasetukset (Advanced).

– Ota valinta pois kohdasta “Käytä järjestelmän erottimia” (Use system separators) ja laita Desimaalierotin (Decimal separator) ruutuun piste ja muuta tarvittaessa myös Tuhaterotin (Thousend separator).

Exceliin saattaa tulla myös joskus koodi (esim. tuhat erottimena) joka on vaikea tunnistaa ja joka estää luvun muotoilemista numeroksi.
Eräs tällainen on mm ASCII koodi 160
Tapoja on monia mutta ehkä helpoin tapa päästä tästä eroon on seuraava:

1. Mene soluun jossa on tämä luku
2. Valitse kaavakentässä merkki (välilyönti) joka on lukujen välissä
3. Kopioi tämä ”väli” (Ctrl+C)
4. Valitse alue jonka haluat muuttaa
5. Valitse Aloitusvalintanauhan Etsi ja korvaa toiminto
6. Etsittävä ruutuun liitä (Ctrl+V) tämä kopioitu väli
7. Korvataan ruutuun älä laita mitään
8. Napsauta korvaa kaikki

Muutama käyttökelpoinen tekstifunktio

Leave a reply

Alla esimerkki muutamasta yleisesti ja usein käytetystä tekstifunktioista, joista on paljon apua.

VASEN (merkkijono, merkkien lukumäärä) (LEFT)
Poimii vasemmalta lukien annetun merkkimäärän merkkijonosta.

OIKEA (RIGHT)
Poimii oikealta laskettuna.

POIMI.TEKSTI(teksti; aloitusnro; merkkien lukumäärä) (MID)
Poimii merkkijonosta antamasi määrän merkkejä alkaen määrittämästäsi paikasta.

ISOT(teksti) (UPPER)
Muuntaa tekstin versaaleiksi.

PIENET(teksti) (LOWER)
Muuntaa versaalit gemenoiksi.

ERISNIMI(teksti) (PROPER)
Muuntaa versaaliksi tekstin ensimmäisen merkin ja kirjaimet, jotka ovat jonkin muun merkin kun kirjaimen jäljessä. Funktio muuttaa kaikki muut kirjaimet gemenoiksi.

POISTA.VÄLIT(teksti) (TRIM)
Poistaa kaikki välilyönnit paitsi yhden sanojen välistä.

KETJUTA (teksti1; teksti2…) (CONCATENATE)
Yhdistää merkkijonoja toisiinsa. Lyhempi tapa on käyttää merkkiä &.

PITUUS (teksti) (LEN)
Merkkijonon pituus.

Esimerkki yllä olevista funktioista

Tekstifunktioesimerkkejä

Tekstifunktioesimerkkejä

Inquire Excel 2013

Leave a reply

Löysinpä mielenkiintoisen apuohjelman Excel versiosta 2013, nimittäin Spreadsheet Inquiren.

Apuohjelma on käytössä Microsoft Office Professional Plus 2013-versiossa.

Ohjelma tuli tarpeeseen, kun jouduin työstämään vierasta työkirjaa, jossa oli paljon linkkejä taulujen välillä ja “epämääräisiä” kaavoja.

Itse koin hyväksi taulujen yhteyskartan ja tilaston kaavoista/nimistä, joissa oli viittausvirheitä tai ne viittasivat “metsään”.

Inquire valintanauha:

Inquire valintanauha

Inquire valintanauha

Workbook Analysis (Työkirjan analyysi)
– luo raportin, jossa näkyy tietoja työkirjasta ja sen rakenteesta

Työkirjan yhteenvetotietoa

Työkirjan yhteenvetotietoa

Raportin saa vietyä/tallennettua Excel-tiedostoon tarkempaa analyysiä varten.

Workbook Relationship (Näytä työkirjan yhteydet)
– kartta työkirjan riippuvuuksista

Worksheet Relationship (Laskentataulukon yhteys)
– kartta työkirjan taulujen yhteyksistä

Taulujen yhteydet

Taulujen yhteydet

Cell Relationship (Solujen yhteydet)
– kartta solun yhteyksistä

Compare Files (Vertaile tiedostoja)
– työkirja-tiedostojen vertailu, jossa näytetään kahden työkirjan väliset erot

Clear Excess Cell Formatting (Ylimääräisen solumuotoilun poistaminen)
– poistaa työkirjasta ylimääräiset muotoilut. Tämä saattaa pienentää työkirjan kokoa ja nopeuttaa sen käyttöä.

Workbook Passwords (Salasanojen hallinta)
– suojattujen kyselyiden salasanahallinta

Puuttuvat etunollat

Leave a reply

Jäivätkö etunollat pois kun toit tiedot Exceliin?
Ne saadaan takaisin erittäin kätevästi parilla funktiolla, jos tieto on määrämittaista.

Otetaan esimerkiksi postinumerot.
Excelissä on A-sarakkeessa seuraavanlaiset tiedot (etunollat puuttuvat) ja tiedon pituus pitää olla 5-merkkiä:
100
2200
16100

Rivillä 1 puuttuu 2 nollaa, rivillä 2 puuttuu 1 nolla ja rivi 3 on oikeanpituinen.

Monet ratkaisevat tämän sisäkkäisillä JOF (IF) lausekkeilla. Se voidaan tehdä yksinkertaisemmin TOISTA (REPT) ja PITUUS (LEN) funktioilla.

Kaava, jonka rakennan B-sarakkeeseen, on seuraavanlainen:

=TOISTA(“0”;5-PITUUS(A1))&A1
=REPT(“0”;5-LEN(A1))&A1

Eli: TOISTA-funktio toistaa tekstin “0” niin monta kertaa kuin se puuttuu merkkijonosta A1:ssä.
Tämä toistojen määrä lasketaan kaavalla 5-PITUUS(A1).
5 on merkkijonon maksimipituus. A1:n merkkimäärä saadaan funktiolla PITUUS(A1).
Näiden erotus kertoo, kuinka monta nollaa laitetaan lisää.
Kaikki tämä yhdistetään olemassa olevaan arvoon solussa A1 funktiolla &. Tämä “et” merkki on toiminnoltaan sama kun funktio KETJUTA (CONCATENATE), mutta helpompi ja nopeampi kirjoittaa kuin varsinainen funktio.

Lopputulos on B-sarakkeessa:
00100
02200
16100

Leikepöydän käyttö

Leave a reply

Aihe saattaa kuulostaa arkipäiväseltä, mutta monella pitämälläni kurssilla olen huomannut, että leikepöydän ominaisuudet ja liittämisasetukset saattavat olla hieman vieraita.

24 leikettä

Tämä toiminto toimii kopioitaessa MS Office -ohjelmien välillä.

Moni käyttää leikepöytää vain viimeisemmän leikkeen kopiointiin ja liittämiseen. Pompitaan sovelluksien tai asiakirjojen välillä, kun halutaan kopioida useampi tekstikappale tai taulukon osa.

Esimerkki

Halutaan kopioida useampi tekstikappale vanhasta asiakirjasta uuteen tai useampi kaaviokuva Excelistä esimerkiksi PowerPointiin. Kun leikepöydän tehtäväruutu avataan, voidaan kopioida yhteen menoon useampi kohta, maksimissaan 24 eri kohtaa, ja liittää ne kohdeasiakirjaan siinä järjestyksessä kun halutaan.

1. Avaa leikepöytä -tehtäväruutu (katso kuvaa)

Leikepöytä

Leikepöytä

2. Kopioi haluamasi kohdat.
3. Siirry asiakirjaan siihen kohtaan johon ne haluat (Word, Excel, PowerPoint tai Outlook-viesti).
4. Avaa leikepöydän tehtäväruutu, mikäli se ei ole avoinna.
5. Napsauta leikepöydän sitä leikettä, jonka haluat liittää.
6. Mene seuraavaan kohtaan ja liitä seuraava leike jne.  

Leikepöydän tehtäväruutu

Kun osoitat leikepöydällä olevaa leikettä, saat näkyviin nuolen, jonka avulla voit valita leikkeen liittämisen tai poiston leikepöydältä.

Tehtäväruudun ylälaidassa ovat painikkeet, josta voit liittää kaikki leikkeet tai tyhjentää kertaheitolla koko leikepöydän.
Alalaidassa taas löydät Asetukset -painikkeen (Options). Täältä voit määritellä leikepöydän käyttäytymistä säätelevät asetukset kuten Näytä Officen leikepöytä automaattisesti (Show Office Clipboard Automatically).  

Liitosasetukset  

Mitä liität ja mihin, vaikuttaa tarjolla oleviin asetuksiin.   Liitämisessä on kaksi tapaa.

– Viimeiseksi kopioidun leikkeen kohdalla voit valita Liitä, Liitä määräten ja sieltä valita liittämisasetukset.

– Liittäessäsi leikepöydällä olevaa leikkettä tai myös liittäessäsi viimeiseksi kopiotua saat liitetyn alueen alaoikeaan reunaan toimintotunnisteen, josta voit valita liittämisasetukset.
Kun viet hiiren osoittimen toimintotunnisteen päälle ja napsautat näkyvää nuolta saat liittämisasetukset.  

Kuvassa on Excelin kaaviokuvan liitosasetukset PowerPointiin (versio 2010).  

Liittämisasetukset

Liittämisasetukset

 
Ole tarkkana mitä liität ja miten, niin että ei tule tietovuotoja!

Jos liität esimerkiksi Excelin kaaviokuvan suoraan asiakirjaan tai esitykseen objektina, niin koko Excelin työkirja liitetään!

Liitosasetuksista löydät myös tavan saada vain teksti liitettyä ja päästä eroon linkeistä ym. Erityisen hyödyllinen kun kopioit Internetistä ja liität asiakirjaan.  
Word ja PowerPoint: valitse Liitä vain teksti (Keep Text Only).
Excel: Käytä kohteen muotoilua (Match Destination Formatting).
 Outlook: Säilytä vain teksti (Keep Text Only).
(Suomalaisissa termeissä erilainen käännös; Liitä vain teksti – Säilytä vain teksti)    

Kuvan pakkaus  

Huomaathan myös, että liitättäessäsi kuvia on syytä tarkkailla tiedoston kokoa. Kun olet liittänyt kuvan, valitse asianomainen kuva ja Kuvatyökalut, Muotoile -valintanauhan Säädä ‑ryhmässä toiminto Pakkaa kuvat (Picture Tools, Format, Adjust, Compress Pictures). Jos et tarvitse alkuperäisen kuvan tarkkuutta, voit mahdollisesti pienentää sitä avautuvasta valintaikkunasta. Tämä on tärkeää etenkin, jos olet liittämässä kuvaa sähköpostiin. Tuolloin yleensä riittää pienempi tarkkuus.  

Muista kuitenkin, että pakkaus huonontaa alkuperäisen kuvan laatua. Joskus on hyvä tallentaa alkuperäinen kuva erilliseksi tiedostoksi ennekuin alkaa kokeilla eri asetuksia.  

Jos olet muokannut kuvaa, rajannut tms., pakkaa kuvat ja valitse vaihtoehto Poista rajatut alueet, muutoin ne jäävät tiedostoon ja kasvattavat turhaan tiedostokokoa.  

Pakkausvalintaikkunan vaihtoehto: Käytä tiedoston tarkkuutta (Use dokument resolution) tarkoittaa oletusarvoisesti 220 ppi:tä, ellei sitä ole asetuksissa muutettu.  

Kun poistat valintaikkunan vaihtoehdon ”Käytä vain tässä kuvassa” (Apply only to this picture), ohjelma suorittaa pakkauksen asiakirjan kaikkiin kuviin. Tämän saman saat aikaiseksi tallennettaessa tiedostoa valinnalla Tallenna nimellä ja valitsemalla tallennusvalintaikkunan Työkalut, Pakkaa kuvat (Save as, Tools, Compress Pictures).

Omat rivi- ja sarakeotsikot

Leave a reply

Omat rivi- ja sarakeotsikot
Jos käytät usein samoja rivi- sarakeotsikoita, voit tallentaa ne Excelin omiin listoihin.

Kun tarvitset niitä, kirjoita soluun listaelementti ja tämän jälkeen vedät täyttökahvasta haluttuun suuntaan ja saat tallennetun listan soluihin.

Esimerkki:
Luodaan lista, jossa on elementit Turku, Helsinki ja Espoo.

Valitaan Tiedosto – Asetukset, Lisäasetukset, Muokkaa omia luetteloita (File – Options, Advanced, Edit Custom Lists…). (Kohta löytyy valintaikkunan alaosasta.)

Napsauta Uusi Luettelo (New List), Kirjoita listan ensimmäinen elementti Turku), paina Enter, kirjoita toinen elementti (Helsinki) ja paina Enter, toista kunnes olet kirjoittanut kaikki elementit ja napsauta lopuksi Lisää (Add).
Napsauta OK:ta kunnes oleta taas laskentataulussa.

Jos sinulla on jo työkirja, jossa otsikot ovat valmiina, voit myös tuoda ne valintaikkunan Tuo (Import) painikkeen avulla.

Käyttö:
Kirjoita listan jokin elementti soluun, isoilla ja pienillä kirjaimilla ei ole väliä.
Ota täyttökahvasta kiinni ja vedä haluttuun suuntaan solujen yli ja lista ilmaantuu soluihin.

Oman listan käyttö

Oman listan käyttö

Oman listan käyttö lajittelussa 

Näin luotua listaa voit hyödyntää myös lajittelussa.

Osoitin soluun lista-alueella ja valitse Tiedot – Lajittele ja suodata, Lajittele (Data – Data Tools, Sort).
Valitse lajitteluperusteeksi sarake, ja listaruudussa Järjestä (Order) valitse Mukautettu luettelo (Custom List…)
Valitse haluttu lista lajitteluperusteeksi. Voit avautuvassa valintaikkunassa tarvittaessa myös luoda uuden listan, poistaa vanhan jne.
Lista lajitellaan oman listan mukaan. Jos lista-alueella on tietoja, joita ei ole sinun omassa listassasi, lajitellaan ne sinun listasi jälkeen joko nousevassa tai laskevassa järjestyksessä riippuen valinnastasi.

Jos valitsit lajitteluperustaksi esimerkkilistan Turku, Helsinki, Espoo ja listassa, jota lajittelet, esiintyy myös paikkakunnat Pori ja Iisalmi, lista lajitellaan muotoon Turku, Helsinki, Espoo, Iisalmi, Pori.

Suomenkieliset kuukaudet englanniksi

Leave a reply

Itselleni tulee aika usein esiin tilanteita, että jo tehdystä taulusta tulisi muuttaa kuukauden nimet suomesta englanniksi, esimerkiksi kun olen ryhmitellyt Pivot-taulukossa päivämäärät kuukausiksi.

Tein makron, jonka suoritan aina tarvittaessa.
Tallensin makron omaan apuohjelma-makrotyökirjaan, jonka avaan aina tarvittaessa.

Alla kaksi esimerkkiä makrosta.
Ensimmäinen on (Sub Replace_Fin_Months_With_Eng), jossa valitsen ensin alueen ja sitten suoritan makron. Toinen on (Sub Replace_Fin_Months_With_Eng_v2), jossa makro kysyy muunnettavaa aluetta.
Molemmissa makroissa käytän FOR EACH NEXT silmukkaa, jolloin vältyn hitaasta SELECT-käskystä.

Sub Replace_Fin_Months_With_Eng()
'2013 / BI
'Replace finnish month namnes with english
'Select first cells and then run makro
    Dim c As Range
    For Each c In Selection
        Select Case c.Value
            Case "tammi"
                c.Value = "Jan"
            Case "helmi"
                c.Value = "Feb"
            Case "maalis"
                c.Value = "Mar"
            Case "huhti"
                c.Value = "Apr"
            Case "touko"
                c.Value = "May"
            Case "kesä"
                c.Value = "Jun"
            Case "heinä"
                c.Value = "Jul"
            Case "elo"
                c.Value = "Aug"
            Case "syys"
                c.Value = "Sep"
            Case "loka"
                c.Value = "Oct"
            Case "marras"
                c.Value = "Nov"
            Case "joulu"
                c.Value = "Dec"
        End Select
    Next
End Sub

Sub Replace_Fin_Months_With_Eng_v2()
'2013 / BI
'Replace finnish month namnes with english
'Makro that asks what range should be converted

    Dim c As Range
    On Error Resume Next
    Set c = Application.InputBox("Valitse alue joka muunnetaan", _
        "Kuukausien vaihto", , , , , , 8)
    On Error GoTo 0
    If Not c Is Nothing Then
        c.Select
        For Each c In Selection
            Select Case LCase(c.Value)
                Case "tammi"
                    c.Value = "Jan"
                Case "helmi"
                    c.Value = "Feb"
                Case "maalis"
                    c.Value = "Mar"
                Case "huhti"
                    c.Value = "Apr"
                Case "touko"
                    c.Value = "May"
                Case "kesä"
                    c.Value = "Jun"
                Case "heinä"
                    c.Value = "Jul"
                Case "elo"
                    c.Value = "Aug"
                Case "syys"
                    c.Value = "Sep"
                Case "loka"
                    c.Value = "Oct"
                Case "marras"
                    c.Value = "Nov"
                Case "joulu"
                    c.Value = "Dec"
            End Select
        Next
    End If
End Sub

Erkoissuodatus

Leave a reply

Erikoissuodatus

Voit etsiä ja poimia tietoja listasta monella eri tavalla. Yksi tapa on hyödyntää erikoissuodatusta. Erikoissuodatus vaatii oman ehtoalueen, johon voit rakentaa erittäin monimutkaisia ehtoja, joiden avulla poimit tietoja. Voit myös tehdä ja käyttää laskennallisia kenttiä ehtojen asettelussa.

Luomisehtoja voit myös hyödyntää tietokantafunktioilla. Erikoissuodatuksen löydät Tiedot-valintanauhan Lajittele ja suodata -ryhmästä olevasta Lisäasetukset-toiminnosta. (Data – Sort & Filter. Advanced).

Huomaa: jos haluat kopioida ehtoja vastaavat tiedot toiseen tauluun, aloita siitä taulusta jonne haluat lopputuloksen.

Sinulla voi myös olla useampi ehtoalue. Silloin ne kannattaa nimetä ja lista kannattaa määritellä taulukoksi, jolle antaa nimen (Aloitus – Tyyli, Muotoile taulukoksi (Home – Styles, Format as Table)).

Vierekkäiset ehdot muodostavat JA-ehdon ja allekkaiset TAI-ehdon. Ehtojen kenttänimien täytyy täsmätä listan kenttänimiin ja itse kopioin ne useimmiten välttyäkseni kirjoitusvirheiltä.
Alla muutama yksinkertaisempi ehto ja sitten pari laskentakenttää ehtoina.

Lista josta esimerkki suodatukset tehdään:

Lista josta suodatukset tehdään

Lähde

Kaikki tanskalaiset:

Country
=Denmark

Huomaa, että joissakin tapauksissa Excel saattaa antaa virheellisen tuloksen jos kirjoitat soluun vain ehdon. Tämän takia olen kirjoittanut sen kaavaan ja ehdon merkkijonona =”=Denmark” Näin pyrin varmentamaan, että Excel tulkitsee ehtoni oikein.

TAI-ehto
Kaikki tanskalaiset tai ranskalaiset:

Country
=Denmark
=France

Tai-ehdossa kirjoitetaan ehdot allekkain.

JA-ehto
Kaikki tanskalaiset JA jossa tilauspäivä on joko 16.1.2013 tai uudempi. Huomaa >= -merkkien järjestys.

Country Order Date
=Denmark >=16.1.2013

JA-ehdossa kirjoitetaan ehdot vierekkäin.

Tanskalaiset JA jossa tilauspäivä on joko 16.1.2013 tai uudempi TAI sitten kaikki tilaukset jossa hinta suurempi kuin 10,50.

Country Order Date Price
=Denmark >=16.1.2013
10,50

Laskennallinen kenttä
Kun käytetään laskennallista kenttää, otsikot jätetään pois ja kaavassa viitataan listan/kannan ensimmäiselle riville suhteellisella viittauksella. Ehtoalueeseen on kuitenkin sisällytettävä tyhjät solut ehtojen yläpuolelle.

Näytä kaikki tilaukset joissa toimituspäivä on tyhjä ja tilauspäivästä kulunut yli 10 päivää verrattuna tähän päivään. TODAY = TÄMÄ.PÄIVÄ

 
TRUE FALSE

Kaavat ovat seuraavat:

=TODAY()-C11>10 =D11=””

Tietokantafunktiot

Tietokantafunktioilla voidaan suorittaa monenlaisia yhteenvetolaskelmia listoista moninaisilla ehdoilla. Ehtoalue kannattaa pitää erillään listasta, niin että ehdot on helppo muuttaa ja ennen kaikkea näet suoraan yhdellä silmäyksellä mitä funktiot laskevat. Ehtoalue voi sijaita omassa taulussaan erillään koko listasta.

Funktioiden syntaksi on:
FUNKTIO(Tietokanta;Sarake;Ehdot)
Tietokantafunktiot ovat luokassa Tietokanta (Database) ja alkavat kirjaimella T, englanniksi D.

Tietokanta on joko alue, nimetty alue tai lista joka on määritelty taulukoksi.   Sarake on se sarake jota halutaan käyttää, joko sarakeindeksinä tai nimenä kirjoitettuna lainausmerkeissä.

Ehdot ovat erillinen ehtoalue, joka voi koostua useammasta sarakkeesta ja rivistä. Ehtoalueessa on listan/tietokannan sarakeotsikko ja sen alapuolella ehto. Vierekkäiset ehdot muodostavat JA (AND) ehdon ja allekkain olevat TAI (OR) ehdon.

Esimerkiksi lasketaan yhteensä tammikuun tilausten arvo
Ehto:

Order Date Order Date
>=1.1.2013 <1.2.2013

Kaava soluun johon haluat summan:
=TSUMMA(A8:I20;”Price”;A1:B2)
=DSUM(A8:F20;”Price”;A1:B2)

Huomaa että laskettavan sarakkeen voi antaa joko lista-alueen sarakeindeksinä (numero) tai sarakkeen nimi merkkijonona (”” -merkkien sisälle).   Jos olet käyttänyt pakollisia rivivaihtoja (Ctrl + Enter) listan sarakeotsikoissa, on helpompaa käyttää sarakeindeksiä. Tällöin kaava olisi muotoa:
=TSUMMA(A8:F20;6;A1:B2) jossa numero 6 on sarakkeen positio listassa.

Aputoiminnosta löydät esimerkkejä muista tietokantafunktioista.