Oppikerta 6

Excel käyttää käsitettä luettelo sellaisista tiedoista, jotka on järjestetty riveihin ja sarakkeisiin. Luettelo voi esimerkiksi olla osoiterekisteri, asiakasrekisteri, taulukko tai hinnasto. Jotta luettelo olisi luettelo, sen pitää kuitenkin täyttää joitakin vaatimuksia:

Ensimmäisellä rivillä pitää olla sarakeotsikot.
Kaikkien samassa sarakkeessa olevien tietojen pitää olla samaa tyyppiä.
Rivien pitää sisältää yhteenkuuluvia tietoja.

Kun luot luetteloa, sinun pitää muistaa joitakin asioita:

Pidä yhdessä laskentataulukossa vain yhtä luetteloa. Luetteloita voi olla enemmänkin, mutta jotkut Excelin luettelofunktioista vaativat, että laskentataulukossa on vain yksi luettelo.
Jätä aina vähintään yksi rivi ja yksi sarake luettelon ja muiden laskentataulukon tietojen väliin. Tällöin voit käyttää niitä Excelin funktioita, jotka tunnistavat luetteloalueen automaattisesti.
Kirjoita aina sarakeotsikot luettelon ensimmäiselle riville. Otsikoiden pitää olla paikallaan, kun etsit ja järjestät tietoja.

Luettelo voi olla yhtä suuri kuin laskentataulukko 16384 riviä ja 256 saraketta.

Luettelon käsittely

Kun olet luonut luettelon, voit käyttää seuraavia Excelin toimintoja/komentoja:

Voit järjestää luettelon aakkos-, numero- tai aikajärjestykseen.
Voit muokata luetteloa tietolomakkeen avulla.
Voit suodattaa luettelon niin, että siitä näytetään vain määritetyt ehdot toteuttava osajoukko.

Tämä luku käsittelee tietokannankäsittelyä, mutta koska tietokanta on sama kuin luettelo, voidaan tässä luvussa kuvattuja komentoja käyttää myös luetteloihin.

Laskentataulukko tietokantana

Tietokanta, kuten luettelokin, muodostuu tietueista eli riveistä ja kentistä eli soluista. Tietokannan ensimmäinen rivi sisältää kenttänimet tai sarakeotsikot. Tietokannan tietueiden näyttämiseen, lisäämiseen, muuttamiseen, etsimiseen ja poistamiseen käytetään tietolomaketta. Voit pitää yhdessä laskentataulukossa useita tietokantoja, mutta yksi on parempi, koska jotkut funktiot vaativat, että laskentataulukossa on vain yksi tietokanta.

Tietokannan käsittelyyn on olemassa useita komentoja. Kaikki nämä komennot löytyvät Tiedot-valikosta.

Käytämme tässä valikon seuraavia komentoja:

Lajittele
Suodata
Lomake

Musiikin tietokannan luominen

Klikkaa hyperlinkkiä!

Halutat luoda tietokannan firmasi Musa-Musa Oy:n varastossa olevista levyistä tai (omaa levykokoelmaasi) varten.

Tee näin

Valitse A3. Tämä tarkoittaa, että teet A3 aktiiviseksi soluksi napsauttamalla solua A3 tai siirtämällä soluosoitin nuolinäppäimellä siihen.
Kirjoita soluun A3 ensimmäisen kentän nimi CDNo.
Kirjoita muiden kenttien nimet oikealla oleviin soluihin samalle riville:

- Esittäjä

- Nimi

- Yhtiö

- Lkm

- Hinta

- Yht.mk

Kirjoita soluun G4 kaava =E4*F4. Varastossa olevien levyjen lukumäärä kerrotaan levyn hinnalla.

Muista, että kaava alkaa aina yhtäsuurusmerkillä (=).

Tietokantasi on jo valmis! Nyt voit ryhtyä toimeen ja rekisteröidä levysi. Voit tietysti kirjoittaa jokaisen levyn tiedot rivi riviltä suoraan laskentataulukkoon, jos niin haluat. Mutta se ei olisi kätevää. Sinun pitäisi esim. kirjoittaa Yht.mk –kentän kaava uudestaan joka riville, jonka lisäät tietokantaan. Onneksi Excelissä on toiminto tietokannan ylläpitoon.

Ylläpito tietolomakkeella

Kun työskentelet luettelon tai tietokannan, voit käyttää Excelin tietolomaketta. Se on valinta ikkuna, jonka avulla voit yksinkertaisesti etsiä, näyttää, lisätä tai poistaa tietueita.

Tietolomakkeen avaaminen

Sijoita soluosoitin johonkin luetteloalueen sisäpuolelle, esimerkiksi kenttänimien riville. Sinun ei siis tarvitse ensin valita aluetta. Excel päättelee itse, mitä aluetta haluat käyttää.
Avaa tietolomake komennolla Tiedot, Tietolomake.

Tietolomake on valintaikkuna, joka sisältää kenttiä ja painikkeita. Koska tietokanta on tyhjä, tietolomake on tyhjä ja sinun pitää alkaa vain kirjoittaa ensimmäistä tietuetta.

Vierityspalkki

Laskettu kenttä

Tietokenttä

Kentän nimi

Kun muutat tietolomaketta, päivitetään laskentataulukon tietokanta välittömästi. Jos olet kirjoittanut tietokanta-alueen alapuolella oleville riveille joitakin tietoja, Excel protestoi, kun yrität lisätä uusia tietueita.

Tietojen lisääminen tietokantaan

Oletetaan, että sinulla on kaksi kappaletta Wishbone –yhtyeen Argus nimistä CD-levyä. Levyn numero on MCAD-10234, julkaisija MCA ja hinta 95 mk.

Toimi näin:

Kirjoita CDNo-kenttään MCAD-10234.
Valitse seuraava kenttä. Pääset seuraavaan kenttään painamalla SARKAIN-näppäintä tai napsauttamalla kenttää.
Kirjoita Esittäjä-kenttään Wishbone Ash.
Kirjoita muut tiedot vastaaviin kenttiin.

Kun olet valmis, tietolomakkeen pitäisi näyttää seuraavalta:

Napsauta Uusi-painiketta, jos haluat lisätä uuden tietueen.

Palauta-painikkeella voit kumota kirjoittamasi tiedot (jos se on aktiivinen)

Napsauta Sulje-painiketta kun haluat lopettaa.

Ensimmäinen tietue on kirjoitettu.

Nyt sinulla on tietokanta, josta voit noutaa tietoja. Seuraavaksi katsomme tietueiden etsimistä, muuttamista ja poistamista.

Lisää tietolomakkeesta

Voit selailla tietueita YLÖS- ja NUOLI ALAS –näppäimillä tai napsauttamalla vierityspalkkia. Jos siirryt viimeisen tietueen ohi, Excel näyttää tyhjän tietolomakkeen ja voit kirjoittaa uuden tietueen aivan samaan tapaan kuin Uusi-painikkeen napsautuksella. Jos tietokannassa on useita kenttiä, tietolomakkeessa näytetään vain niin monta kenttää kuin siihen mahtuu. Tämän ongelman voit korjata luomalla oman tietolomakkeen.

Oikean tietueen hakeminen

Tietokanta sisältää usein valtavasti tietoja. Jos haluat katsoa, muuttaa tai poistaa tietueen, ensimmäisenä tehtävänäsi on usein löytää oikea tietue. Voit tietysti avata tietolomakkeen ja selata haluamasi tietueen esiin. Entä sitten, kun tietokannassa on useita tuhansia tietueita? Tällöin tarvitset jonkin toiminnon, jonka avulla voit nopeasti etsiä tietokannasta sen tietueen tai ne tietueet, jotka sisältävät haluamasi tiedot. Tietoja voidaan hakea tietokannasta monella tavalla. Ensin yksinkertaisin – tietolomakkeen Etsi-toiminto.

Hakuehdot

Olet lisännyt tietokantaasi joukon tietueita ja haluat nyt löytää jonkin määrätyn tietueen. Tai jos palaamme esimerkkiimme – haluat löytää tietueen, joka sisältää tiedot jostakin määrätystä varastosi tai levykokoelmasi CD-levystä. Voit tehdä tämän antamalla tietolomakkeeseen hakuehdon. Oletetaan, että haluat etsiä Genesis-yhtyeen levyn Duke.

Tietueen hakeminen

Kun napsautat Seuraava- tai Edellinen –painiketta, ja hakuehdot täyttävä tietue löytyy, se näytetään, muuten kuulet äänimerkin ja edellinen tietue tulee näkyviin. Jos et ole asettanut hakuehtoja, näytetään edellinen tai seuraava tietue.

Jos olet antanut useita hakuehtoja, niiden kaikkien pitää toteutua, ennen kuin tietue näytetään.

Korvausmerkit

Joskus käy niin, ettemme tiedä, miten haettava tieto kirjoitetaan. Esimerkki: Halutat hakea kaikki Nick Lowen levyt. Olet unohtanut Lowen sukunimen, etkä tiedä onko nimi kirjoitettu muodossa Lowe vai Love. Ratkaisuna on käyttää nk. korvausmerkkejä (wildcards).

Korvausmerkki on erikoismerkki, joka voi edustaa yhtä tai useampaa merkkiä. On olemassa kaksi korvausmerkkiä.

Korvausmerkkejä voidaan yhdistää. Huomaa, että Excel käsittelee jokaista hakuehtoa ikään kuin hakuehdon perässä olisi tähti.

Esimerkki. Löytääksesi kaikki Nick Lowen CD-levyt kirjoita Esittäjät-kenttään: *Lo?e. Sukunimen eteen lisätty tähti aiheuttaa sen, että tietueet löytyvät riippumatta siitä, onko etunimi kirjoitettu sukunimeä ennen vai sen jälkeen. Nimen perässä ei tarvita tähteä.

Entä sitten, jos etsitään tekstiä, joka sisältää kysymysmerkin? Yksinkertaisesti kysymysmerkin eteen lisätään vain tilde-merkki (~). Näin ilmoitat Excelille, että haluat etsiä kysymysmerkkiä, etkä käyttää sitä korvausmerkkinä. Esimerkiksi jos kirjoitat What’s now baby~?, teksti What’s now baby? löytyy.

Vertailuehdot

Tähän mennessä olemme hakeneet tietueita, jotka vastaavat täsmälleen jotakin hakuehtoa. Oletetaan, että haluat löytää kaikki CD-levyt, jotka maksavat yli 80 mk. Tämän voit tehdä kirjoittamalla Hinta-kenttään vertailuehdon.

Tee seuraavasti

Merkkiä > kutsutaan vertailuoperaattoriksi. On olemassa kuusi vertailuoperaattoria, joita voit käyttää hakuehtoja kirjoittaessasi:

Esimerkki. Haluat löytää kaikki CD-levyt, joilla ei ole numeroa CDNo-kentässä. Kirjoita CDNo-kenttään hakuehdoksi pelkkä yhtäsuuruusmerkki (=).

Voit käyttää vertailuoperaattoreita myös merkkijonojen vertailuun. Vertailuperusteena käytetään tällöin aakkosjärjestystä.

Esimerkki. Beatles < Who toteuttaa ehdon, koska Beatles on aakkosjärjestyksessä ennen Whota.

Tietueiden muuttaminen ja poistaminen

Tietueen muuttaminen

Tietueen poistaminen

Tietolomake on mainio apuväline tietokannan tietueiden etsimisessä, muuttamisessa. lisäämisessä ja poistamisessa. Mutta joskus haluat ehkä valita osan tietueista, muotoilla niitä, tulostaa ne tai siirtää toiseen laskentataulukkoon. Tähän tarvitset suodatusta. Excelissä on tätä varten toiminto, jonka nimi on Pikasuodatus.

Pikasuodatus

Suodatuksen käyttäminen

Tietojen suodattaminen tietokannasta tarkoittaa sitä, että valitset näytettävät tietueet antamalla suodatusehdot. Ne laskentataulukon tietueet eli rivit, jotka eivät toteuta ehtoja, piilotetaan. Kun kaikki tietueet ovat siis tallella, mutta näet vain ne tietueet, jotka täyttävät ehdot. Kuten tulet huomaamaan, Excelissä on nokkela tapa suodatusehtojen antamiseen.

Tietokannan suodattaminen

Jokaisessa sarakkeessa on nyt kenttänimen oikealla puolella nuoli, jota napsauttamalla saat esiin luettelon kaikista sarakkeen ainutkertaisista arvoista.

Vain ne tietueet, jotka toteuttavat ehtosi, näytetään. Jos olet valinnut Yhtiö-kentän ehdoksi Polydor, näytetään vain ne levyt, jotka on julkaissut Polydor.

Voit valita suodatusehdon useasta sarakkeesta samanaikaisesti. Tällöin tietue näytetään vain, jos se toteuttaa kaikki valitut ehdot.

Esimerkki. Oletetaan, että haluat poimia kaikki ne CD-levyt, joissa esittäjänä on Who ja levy-yhtiönä Polydor. Tällöin sinun pitää valita Esittäjä-luettelosta Who ja Yhtiö-luettelosta Polydor.

Muista, että kun tietokannasta piilotetaan rivejä, niin myös tietokanta-alueen oikealla ja vasemmalla puolella mahdollisesti olevat tiedot piilotetaan. Tästä syystä on hyvä välttää tietojen kirjoittamista tietokannan oikealle ja vasemmalle puolelle.

Omat suodatusehdot

Pikasuodatus-toiminnolla voit luoda myös omia ehtoja jokaiselle kentälle. Näiden omien suodatusehtojen avulla voit löytää

Esimerkki. Haluat löytää sellaiset CD-levyt, joiden hinta on välillä 80 mk – 95 mk. Napsauta Hinta-kentän nuolta ja valitse luettelosta vaihtoehto Oma. Esiin tulee Oma pikasuodatus –valintaikkuna:

Voit antaa kaksi Hinta-kenttää koskevaa suodatusehtoa. Ehto muodostuu kahdesta osasta: vertailuoperaattorista ja arvosta. Valitse vertailuoperaattori vasemmalla olevasta luetteloruudusta ja kirjoita arvo oikealla olevaan kenttään. Edellisen kuvan ehto voidaan lukea näin: Näytä kaikki tietueet, joiden Hinta-kentän arvo on suurempi kuin 80 ja pienempi kuin 95. Tämän seurauksena kaikki muut tietueet piilotetaan:

Kun annat suodatusehtoja, voit tässäkin käyttää korvausmerkkejä (? ja *) etsiessäsi epätarkkoja arvoja.

Erikoissuodatus

Jos sinun pitää valita useita monimutkaisia ehtoja sinun on käytettävä Erikoissuodatusta.

Erikoissuodatuksen avulla, voit muodostaa tietokannasta osajoukon, joka toteuttaa antamasi ehdot. Voit kopioida osajoukon laskentataulukon toiseen kohtaan tai toiseen laskentataulukkoon. Osa joukon muodostamiseksi Excelin pitää tietää

Ennen jatkamista, sinun pitää ymmärtää, mikä ehtoalue on ja miten sitä käytetään.

Ehtoalue on laskentataulukon erikoisalue, johon sijoitetaan näytettäviä tietueita koskevat ehdot. Ehtoalue muodostuu yhdestä tai useammasta tietokannan kenttänimestä, ehdoista ja ainakin yhdestä tyhjästä rivistä ehtojen jälkeen. Sinun tarvitsee ottaa mukaan vain ne kentät, joita käytät. Mihin laskentataulukon kohtaan ehtoalue on sitten paras sijoittaa? Suosittelen, että siirrät tietokantaa joitakin rivejä alemmaksi ja sijoitat ehtoalueen laskentataulukon yläosaan.

Ehtoalueen luominen

Sen lisäksi että voit kirjoittaa useita hakuehtoja eri kenttiin, voit myös kirjoittaa useita hakuehtoja sisältäviä rivejä.

Suodatettu luettelo

Kun tietokannasta poimitaan jokin osajoukko, niin hakuehdot toteuttavat tietueet kopioidaan toiseen laskentataulukon alueeseen tai kokonaan toiseen laskentalomakseen. Tätä aluetta sanotaan kohdealueeksi. Suodatetulle luettelolle löytyy käyttöä monessa tilanteessa. Haluat ehkä kopioida suodatetun luettelon toiseen laskentataulukkoon tai tulostaa sen kirjoittimelle.

Suodatetun luettelon luominen

Kun käytät Erikoissuodatusta ensimmäisen kerran, ehto- ja kohdealueet määritellään automaattisesti. Kun käytät Erikoissuodatusta samassa laskentataulukossa seuraavan kerran, valinta ikkunassa ehdotetaan näitä alueita.

Muista, että et pidä tietoja kohdealueen alapuolella olevissa soluissa, koska tämä alue tyhjennetään aina. kun suodatettu luettelo luodaan.

Järjestystä tietokantaan

Tiedot valikosta löytyy myös Lajittele-komento. Sitä käytetään, kun laskentataulukon jonkin alueen tiedot halutaan järjestää uudelleen. Tiedot voidaan järjestää riveittäin tai sarakkeittain aakkos-, numero tai aika järjestykseen. Tietokantojen lisäksi voit lajitella muutakin tietoa; voit lajitella minkä tahansa luettelon, mutta tavallisesti lajittelu kohdistuu juuri tietokantoihin, kun kirjoitat tietokannan tietoja, tietueet lisätään kirjoitusjärjestyksessä. Myöhemmin voit lajitella ne uudestaan paremman yleiskatsauksen saamiseksi.

Lajittelu

Kun lajittelet, niin haluat, että kaikki tietokannan tietueet järjestetään yhden kentän sisällön mukaan (tai sarakkeen, jos lajittelet luetteloa). Lisäksi voit valita, onko lajittelujärjestys nouseva tai laskeva. Nouseva lajittelu tarkoittaa, että luvut lajitellaan ykkösestä ylöspäin, teksti A:sta Ö:hön ja päivämäärät viimeisimmästä vanhimpaan.

Tietokannan lajitteleminen

Jos haluat lajitella tietokannan nopeasti, sinun pitää vain valita jokin sarakkeen solu ja napsauttaa sitten toista Vakio-työkalurivin Lajittele-painikkeesta

(nouseva, laskeva).

Laskeminen tietokannoissa

Koska käsittelemme vielä tietokantoja, tarkastelemme joitakin sellaisia funktioita, jotka on tarkoitettu erikoisesti tietokannoissa suoritettaviin laskutoimituksiin. Haluat ehkä tietää kenttien arvojen summan sellaisissa tietueissa, jotka toteuttavat jonkin määrätyn ehdon tai sellaisten tietueiden lukumäärän, jotka toteuttavat ehdon.

Kaikilla tietokantafunktioilla on sama rakenne: Funktion nimi ja kolme argumenttia. Argumentti on funktioon liitettävä arvo. Funktio, joka laskee ehdon toteuttavien tietueiden lukumäärän, kirjoitetaan seuraavasti:

TLASKE on funktion nimi. Tietokanta, kenttä ja ehdot ovat argumentteja. Ne erotetaan toisistaan aina puolipisteellä (;). Funktiota kirjoittaessasi saat paljon apua Ohjattu funktio –toiminnosta. Näytän yksinkertaisen esimerkin tietokantafunktion käytöstä.

CD-levyjen tietokannassa olisi käytännöllistä olla solu, joka ilmoittaa aina niiden levyjen lukumäärän, jotka toteuttavat määrittelemäsi ehdot. Voidaksesi antaa ehtoja, sinulla pitää olla ehtoalue. Olet jo tutustunut ehtoalueeseen. Oletetaan, että haluat tietää, kuinka monen levyn numero alkaa kirjaimilla CD ja MC. Kirjoita ehdot ehtoalueeseen:

Funktion luominen

Lopputuloksen tulisi näyttää tämän kaltaiselta. Solussa A6 näet arvon: tietokannasta löytyi 4 ehdot täyttävää levyä.

Ota tavaksesi antaa tietokanta-alueen nimeksi Tietokanta ja ehtoalueen nimeksi Ehdot. Tällöin voit käyttää näitä nimiä suoraan tietokantafunktioissa.

Operaattori	Merkitys
=	Yhtäsuuri kuin. Jos et kirjoita merkin jälkeen mitään. saat näkyviin vain ne tietueet, joissa ko. kenttä on tyhjä.
>	Suurempi kuin
<	Pienempi kuin
>=	Suurempi tai yhtäsuuri kuin
<=	Pienempi tai yhtäsuuri kuin
<>	Erisuuri kuin. Jos et kirjoita merkin jälkeen mitään, saat näkyviin vain ne tietueet, joilla on ko. kentässä jokin sisältö.