17  Yleisiä toimintoja Excelissä

Tästä luvusta löytyy yleisiä toimintoja ja menetelmiä Excelissä. Luettavuuden kannalta olen ympäröinyt jokaisen funktion sulun sisällön välilyönnellä: ( tekstiä ( lisää tekstiä )) Tämä ei ole tarpeellista Excelissä omassa työssäsi. Joissain tilanteissä tämä voi myös rikkoa laskukaavan, tarkista aina toimivuus omalla koneellasi.

Wordin lainausmerkit eivät toimi Excelissä

Suomenkielisessä Office-paketissa Excel ja Word tuottavat eri lainausmerkit kun kirjoitat lainausmerkin näppäimistöllä. Excelin lainausmerkki näyttää suorilta viivoilta (“). Wordin lainausmerkit taas näyttävät pieniltä pilkuilta (”). Ero on hiuksenhieno, mutta vaikuttaa tuloksiin: Excel ei osaa kääntää Wordin lainausmerkit omakseen, jolloin Wordista kopioidut lainausmerkit eivät toimi Excelin kaavoissa. Lisätietoja lainausmerkkien eroista löydät esimerkiksi tästä Markus Kuhnin kirjoittamasta verkkoartikkelista.

Koska Excelissä voi olla hankalaa kirjoittaa pitkiä kaavoja, voi joskus kuitenkin olla tarpeen kirjoittaa kaavat ensin jossain tekstinkäsittelyohjelmassa ja sitten kopioida ne Exceliin. Suosittelen tähän Muistio-ohjelmaa (Notepad), mikä löytyy Windows-käyttöjärjestelmästä valmiina. Jos käytät Mac OS X- tai Linux-käyttöjärjestelmiä, voit käyttää jotain toista raakatekstin käsittelyohjelmaa. Älä kuitenkaan käytä ohjelmia, joissa tekstiä automaattisesti formatoidaan ohjelman puolesta - näissä voi esiintyä samoja ongelmia kuin Excelin ja Wordin välillä.

Toinen suositeltava tapa on laajentaa funktioalue tarpeeksi suureksi, että voit rivittää funktiot ja siten nähdä niiden sisäiset osiot.

17.1 Suodata pois rivit, joissa yhdellä tai useammalla muuttujalla on tyhjiä arvoja

Jos tarvitset poistaa kaikki epätäydelliset rivit jotain analyysia varten, voit käyttää seuraavan tapaista kaavaa:

=SUODATA( alue; ( Muuttuja1 <> "" ) * ( Muuttuja2 <> "" ) * ( ... ))

Jokainen muuttujan rivipoisto määritellään omissa suluissa, muodossa (Muuttuja <> ""). Merkkijono <> on Boolen logiikkaa, ja tarkoittaa samaa kuin “ei ole yhtä kuin” (ks. Alaluku 14.2). Merkkijono "" esittää tyhjää solua, eli lainausmerkkien välillä ei ole mitään sisältöä. Toisin sanoen, jokainen suodatusehto sanoo “Valitse ne rivit, joiden arvo muuttujalla X ei ole yhtä kuin tyhjä merkki.

Voit määritellä niin monta ehtomuuttujaa kuin haluat. Ne lisätään peräkkäin ja kerrotaan yhteen (*). Tämä käytännössä toimii samalla tavalla kuin Boolen logiikan JA-toiminto: kertominen määrittelee, että kaikkien ehtojen tulisi täyttyä jotta rivi otetaan mukaan.

Suodatus kannattaa aina tehdä tarpeen mukaan. Jos mahdollista, kannattaa myös käyttää SUODATA-funktiota suoraan seuraavan analyysin funktion sisällä. Monet funktiot eivät nimittäin osaa tulkita SUODATA-funktion tuloksia oikein1.

1 Ongelma liittyy dynaamisiin matriisikaavoihin ja levittyineisiin matriiseihin, jotka voivat olla hankalia ymmärtää. Jos haluat lukea lisää, tietoa löytyy Microsoftin apusivuilta.

Funktio ei osaa käsitellä virhearvoa #TYHJÄ!. Jos syötät alueen, jossa kyseinen virhearvo esiintyy, funktio palauttaa #TYHJÄ!-virheen (engl. #NULL!). Muuta ensin alueen virhearvot joksin muuksi arvoksi (esim. "" eli oikeasti tyhjä solu; ks. Alaluku 17.7), ja suodata sitten tämä tulos.

Tärkeää

Jos suodatuksen jälkeen riveillä vielä on tyhjiä soluja jossain (eli ei olla vaadittu täydellistä datamatriisia), SUODATA-funktio täyttää nämä arvolla 0. Jos yrität suodattaa tämän tuloksen uudelleen yllä olevalla tavalla, funktio ei enää löydä tyhjiä soluja jotka täyttäisivät ehdot - nolla-arvo ei ole sama asia kuin tyhjä arvo. Peräkkäisiä tyhjien arvojen suodatuksia ei siis oikein voi tehdä, parempi on aina käyttää vain yhtä SUODATA-funktiota kerralla.

17.2 Hae kaikkien muuttujien nimet

Jos haluat kaikki muuttujasi nimet, esimerkiksi sijantilukutaulukkoa varten, voit käyttää seuraavaa kaavaa:

=TRANSPONOI( AINUTKERTAISET.ARVOT( VALITSERIVIT( taulukko; 1 )))

Jotta toiminto toimisi, sinun tulee ensin varmistaa seuraavat asiat:

  • Datamatriisin ensimmäisellä rivillä on muuttujien nimet.

  • Jos olet määritellyt nimen koko datamatriisille, varmista että määrittely sisältää nimirivin.

Kaava valitsee ensin ensimmäisen rivin datamatriisista (VALITSERIVIT(taulukko; 1)). Sen jälkeen haetaan kaikki uniikit arvot tältä riviltä (AINUTKERTAISET.ARVOT()). Viimeiseksi tämä rivi transponoidaan sarakemuotoon (TRANSPONOI()), jolloin jokaisen muuttujan nimi tulee omalle riville yhteen sarakkeeseen.

Jos haluat pitää muuttujien nimet yhdellä rivillä (eli jakaa ne sarakkeisiin, niin kuin ovat datamatriisissa), jätä pois TRANSPONOI-funktio.

17.3 Laske muuttujan ryhmien määrä (k)

Jos haluat laskea, kuinka monta uniikkia ryhmää muuttujassa on, voit käyttää kaavaa:

=LASKE.A( AINUTKERTAISET.ARVOT( VALITSESARAKKEET( alue; sarakenumero )))

Kaava valitsee ensin sarakkeen funktiolla VALITSESARAKKEET(alue; sarakenumero). Jos sinulla on valmiiksi nimetty sarake, voit korvata koko tämän osuuden sarakkeen nimellä (katso alla). Seuraavaksi kaava hakee kaikki uniikit arvot sarakkeesta funktiolla AINUTKERTAISET.ARVOT(). Viimeiseksi kaavassa lasketaan, kuinka monta uniikkia arvoa esiintyi funktiolla LASKE.A().

Kaava toimii myös ei-numeroiden määrän laskemisessa, LASKE.A()-funktion avulla. Jos funktion kanssa esiintyy ongelmia, tai jos haluat laskea vain numeroiden määrän, käytä funktion sijaan LASKE()-funktiota.

Jos olet jo määritellyt sarakkeen nimen, käytä tätä kaavaa:

=LASKE.A(AINUTKERTAISET.ARVOT(sarakenimi))

Tärkeää

VALITSESARAKKEET-funktio toimii sarakkeen numerolla, ei nimellä tai kirjaimella!

17.4 Hae muuttujan uniikit arvot suuruusjärjestykseen riveihin tai sarakkeisiin

Jos haluat listata muuttujan kaikki uniikit arvot nousevassa järjestyksessä, voit käyttää kaavoja:

  • Riveihin: =LAJITTELE( AINUTKERTAISET.ARVOT( alue ) ; ; 1 )

  • Sarakkeisiin: =TRANSPONOI( LAJITTELE( AINUTKERTAISET.ARVOT( alue ) ; ; 1 )

Kaava hakee ensin kaikki uniikit arvot määritetyltä alueelta funktiolla AINUTKERTAISET.ARVOT(). Seuraavaksi arvot lajitellaan funktiolla LAJITTELE(). Tässä funktiossa on tärkeää muistaa kaksi puolipilkkua. Ensimmäisenä argumenttina syötetään lajiteltava matriisi. Toinen argumentti määrittelee lajitteluindeksin, mutta sitä emme tarvitse tässä, joten jätämme sen tyhjäksi. Kolmas argumentti määrittelee lajittelujärjestyksen, ja se me haluamme määritellä arvoksi 1, eli nousevaan järjestykseen.

Voit vaihtaa järjestyksen nousevan 1 ja laskevan -1 välillä vapaasti.

17.5 Laske N yhdelle muuttujan kategorialle

Jos haluat laskea muuttujan kategorian/ryhmän arvopisteiden määrän, voit käyttää kaavaa:

=LASKE.A( SUODATA( muuttuja; muuttuja = arvo ) )

Kaava suodattaa ensin muuttuja-alueen arvon mukaan funktiolla SUODATA(), jonka jälkeen se laskee, kuinka monta arvoa suodatetussa muuttujassa on jäljellä funktiolla LASKE.A().

Kaava toimii myös tekstin kanssa LASKE.A()-funktion ansiosta. Valinnaisesti voit laskea vain numeroarvot funktiolla LASKE().

17.6 Laske N muuttujan kaikille kategorioille suuruusjärjestyksessä

Jos haluat laskea kaikkien muuttuja-arvojen määrät, voit käyttää seuraavaa kahta kaavaa:

  • Ensimmäinen solu: =LAJITTELE( AINUTKERTAISET.ARVOT( muuttuja-alue ) ; ; 1 )

  • Toinen solu, ensimmäisen solun oikealla puolella: =TAAJUUS( muuttuja-alue; ensimmäinen_solu# )

Ensimmäisessä solussa haetaan kaikki muuttujan uniikit arvot ja lajitellaan ne nousevaan järjestykseen (ks. Alaluku 17.4).

Toisessa solussa käytetään TAAJUUS-funktiota. Funktion ensimmäinen argumentti on sama muuttuja-alue (nimetty muuttuja, esim. “sukupuoli” tai suora osoite, esim C2:C105) kuin ensimmäisen solun kaavassa.

TAAJUUS-funktion toisena argumenttina käytämme ensimmäisen funktion palautusta, mutta huomaa risuaidan # lisääminen soluviittauksen loppuun. Tämä kertoo Excelille, että soluviittaus on levitetty matriisialue, ei yksittäinen solu, jolloin Excel toimittaa funktion jokaiselle matriisissa määritetylle arvolle, ei vain yhdelle. Jos esimerkiksi ensimmäisen solun osoite on C2, kirjoita C2#.

Tärkeää

TAAJUUS-funktio tuottaa aina yhden solun enemmän kuin mitä sille syötettiin: esimerkiksi jos kategorioita on viisi, funktio palauttaa kuusi solua. Viimeinen solu sisältää määrän kaikista arvoista, jotka ylittävät suurimman määritetyn arvon. Kvalitatiiviselle muuttujalle (jossa kategoriat ovat määriteltyjä) tämä arvo on aina 0.

Tämä tuki löytyy, koska TAAJUUS-funktiota voi myös käyttää rakentaakseen kategorioita kvantitatiivisesta muuttujasta, jolloin kategoriat eivät välttämättä kata koko jakauman pituutta.

Jos raportoit kategorisen muuttujan määriä, jätä pois viimeinen 0-solu, koska sitä ei ole mahdollista saada arvoksi.

17.7 Muuta kaikki virheelliset arvot tyhjiksi soluiksi

Jos sinulla on virhearvoja data-alueellasi (esim. #TYHJÄ!, #JAKO/0!), useimmat kaavat eivät toimi alueen kanssa. Silloin sinun kannattaa tehdä uusi muuttuja data-alueellesi, jonka soluihin syötät seuraavan kaavan:

=JOS( ONLUKU (solu) ; solu ; "" )

Kaava tarkistaa, onko tarkistettu solu luku (muuta tämä ONTEKSTI-funktioksi, jos suodatat tekstiä). Jos se on luku, kaava palauttaa alkuperäisen solun arvon. Jos ei ole luku (esim. virhearvo), kaava palauttaa tyhjän solun "". Voit myös määritellä toisen palautusarvon, jos et halua käyttää tyhjää solua (esim. 999).

Lisää kaava ensimmäiseen soluun ja levitä se kaikkien rivien yli vetotoiminnolla.

Voit halutessasi vaihtaa palautettavan arvon, jos Excel ei löydä lukua. Useimmiten kuitenkin tyhjä solu on helposti käsiteltävissä (esim. ONTYHJÄ-funktiolla), joten sen käyttö voi olla suositeltavaa, etteivät arvot mene sekaisin analyysissa.

Toiminnon voi myös automatisoida koko datajärjestykselle käyttämällä BYROW-funktiota ja LAMBDA-rakennetta. Kaava näyttää seuraavalta:

=BYROW( muuttuja; LAMBDA( x; JOS( ONLUKU( x ); x; "" ) ) )

BYROW-funktio suorittaa määritetyn LAMBDA-funktion tietyn alueen jokaiselle riville - tämä vastaa manuaalista kaavan lisäämistä jokaiselle riville. LAMBDA-funktiossa määritetään sisääntulevan datan nimi (x). Sen lisäksi määritetään, mitä sille tulisi tehdä, eli aikaisempi JOS-rakenne.

BYROW-rakenne palauttaa yhtä monta riviä kuin alkuperäisessä on. Tämä tarkoittaa myös tyhjiä rivejä niille riveille, jotka eivät täytä JOS-funktion vaatimuksia.