Tag Archives: Excel

Kahden listan vertailu

Verrataan kahta listaa keskenään ja poimitaan toisesta listasta tietoja ensimmäiseen.
Tähän on kätevä käyttää funktiota PHAKU (VLOOKUP).
Esimerkissä poimitaan henkilön palkka palkat.xlsx –työkirjasta.
Kaava solussa C2 on =PHAKU(A2;[Palkat.xlsx]Palkkaus!$A:$C;3;EPÄTOSI).
=VLOOKUP(A2;[Palkat.xlsx]Palkkaus!$A:$C;3;FALSE)

PHAKU (VLOOKUP)

PHAKU (VLOOKUP)

Hakuarvo on tässä tapauksessa se arvo jonka perusteella toisesta taulukosta haetaan tietoja.
Taulukko_matriisi on se taulukko alue jonka vasemmanpuoleisesta sarakkeesta arvoa haetaan.
Sar_indeksi_nro on sen sarakkeen numero valitulla taulukkoalueella josta arvo poimitaan.
Alue_halu on epätosi koska halutaan löytää täsmällinen vastine.

Taulu josta haetaan tietoja kannattaa lajitella hakuarvon perusteella.

Mikäli tietoja ei toisesta taulusta löydy palauttaa funktio virhearvon #PUUTTUU! (#N/A).
Tämän saat pois seuraavalla tavalla kaavalla (Excel versiot 2007 ja uudemmat):
=JOSVIRHE(PHAKU(A2;[Palkat.xlsx]Palkkaus!$A:$C;3;EPÄTOSI);”Ei löydy”)
JOSVIRHE = IFERROR
EPÄTOSI = FALSE
Kaavan tekstin “Ei löydy” sijasta voit käyttää mitä tahansa haluamaasi tekstiä tai sitten “” jotta solu jäisi tyhjäksi.
=JOSVIRHE(PHAKU(A2;[Palkat.xlsx]Palkkaus!$A:$C;3;EPÄTOSI);””)

Jos käytössäsi on vanhempi Excel voit poistaa virhesanoman esimerkiksi seuraavanlaisella kaavalla:
=JOS(ONVIRHE(PHAKU(A3;[Palkat.xlsx]Palkkaus!$A:$C;3;EPÄTOSI));””;PHAKU(A3;[Palkat.xlsx]Palkkaus!$A:$C;3;EPÄTOSI)).
JOS = IF
ONVIRHE = ISERROR
Huomaa että tässä kaavassa haku suoritetaan kaksi kertaa joka tekee kaavasta hitaan.

Dynaamiset nimet 2

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.

Sähköpostiosoitteen poiminta hyperlinkkisolusta

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ä!

Näyttökuvan lisäys asiakirjaan

(Word, Excel, PowerPoint, Outlook versiot 2010).

Toiminto toimii samalla tavalla eri Office-ohjelmissa.

Toimi seuraavasti kun haluat lisätä ruutukaappauskuvan tai kuvaosan näyttöruudusta:

Ruutukaappaus (esimerkkinä Word)

  • Siirry Wordiin
  • Laita osoitin kohtaan johon kuvan haluat
  • Valitse Lisää, Näyttökuva, ja valitse kuva kohdasta Käytettävissä olevat ikkunat (Insert, Screenshot, Available Windows)
    (Huomaa että ikkuinat joita näytetään eivät saa olla pienennettyjä tilariville).
    (Huomaa että Wordissa tämä ei toimi jos sinulla on dokumentti yhteensopivuus tilassa (Compatibility mode). Muunna asiakirja ensin uuteen muotoon ja tee sitten uudestaan ruutukaappaus).
  • Muokkaa tarvittaessa kuvaa kuten Rajaa ja Pakkaa kuvat (Crop, Compress Picture)

Kuva osasta näyttöä (esimerkkinä Outlook)

  • Aloita uusi viesti
  • Siirrä osoitin viestin tekstiosaan
  • Pienennä Outlookin ikkuna niin että aloittamasi viestin alle jää se osa ikkunasta josta haluat kuvan
  • Valitse Lisää, Näyttökuva (Insert, Screenshot, Screen Clipping)
  • Viesti pienenee jotta sen alla oleva kuva näkyisi ja näyttö muuttuu harmaaksi
  • Pidä hiiren vasen painike alhaalla ja valitse alue josta haluat kuvan
  • Vapauta hiiren painike ja kuva on viestissäsi.

Joka toinen rivi eriväriseksi

Joka toinen rivi eriväriseksi
Valitse alue
Valitse Aloitus, Tyyli, Ehdollinen muotoilu, Uusi sääntö (Home, Styles, Conditional Formatting, New Rule)
Valitse valintaikkunasta Määritä kaavan avulla, mitkä solut muotoillaan (Use a formula to determine which cells to format)
Kirjoita kaava =JAKOJ(RIVI();2) ruutuun Muotoile arvot, jossa tämä kaava on tosi (Format values where this formula is true). Englanniksi =MOD(ROW();2)
Napsauta OK.
Kaavassa funktio RIVI() (ROW()) palauttaa rivinumeron jokaiselle riville valitulla alueella. Rivinumero jaetaan 2:lla ja tarkistetaan jakojäännös, funktio JAKOJ() (MOD()). Jos jakojäännös on 0 niin ollaan parillisella rivillä, muuten parittomalla.

Kopiointia Excelissä

Tiedon/kaavan kopiointi

Muut tavat kuin Ctrl+c ja Ctrl+v

1) Täyttökahvasta vetäminen:

Ota solun täyttökahvasta kiinni ja vedä (huomaa hiiriosoittimen muoto)

Solun kopiointi

 

 

 

 

 

2) Täyttökahvan kaksoisnapsautusNapsauta täyttökahvaa kaksi kertaa niin tieto/kaava kopioituu niin pitkälle alas kun naapurisolussa on tietoa.

3) Ctrl +Enter

Valitse solut

Kirjoita tieto/kaava

Paina Ctrl + Enter

Tieto tai kaava kopioituu kaikkiin valitsemiisi soluihin.

4) F2, Ctrl+Enter

Jo kirjoitetun tiedon/kaavan kopiointi

Valitse solut lähtien liikkeelle solusta jossa on haluttu tieto

Paina F2 (aktivoi solun)

Paina Ctrl+Enter

Tyhjien rivien poisto

Esimerkki miten poistan koko taulukon rivin jos A-sarkkeen solu on tyhjä.

Tallenna ensin työkirjasi
Valitse A-sarake
Valitse Aloitus, Muokkaaminen, Etsi ja valitse, Siirry määräten (Home, Editing, Find & Select, Go To Special)
Valitse valintaikkunasta Tyhjät (Blanks)
Valitse Aloitus, Solut, Poista, Poista taulukon rivejä (Home, Cells, Delete, Delete Sheet Rows)