Matriisikaavoihin liittyviä ohjeita ja esimerkkejä

Matriisikaavoihin liittyviä ohjeita ja esimerkkejä

Tärkeää: Tämä on artikkeli on käännetty koneellisesti. Lue vastuuvapauslauseke. Tämän artikkelin englanninkielinen versio on täällä .

Pääset Excel power käyttäjäksi haluat tietää, miten voit käyttää matriisikaavoja, joka voi suorittaa laskelmia, et voi tehdä matriisi kaavojen avulla. Seuraavassa artikkelissa perustuu numerosarjan Excel tehokäyttäjän sarakkeiden Colin Wilcox kirjoittama ja sovittaa 14 ja 15 Excel 2002-kaavat, John Walkenbach, Excel MVP-Asiantuntija kirjoittama kirjan lukuja.

Tietoja matriisikaavoista

Matriisikaavoihin viitataan joskus nimellä CSE-kaavat, koska kaavat lisätään työkirjoihin näppäinyhdistelmällä Ctrl+Vaihto+Enter eikä Enter-näppäintä painamalla.

Miksi kannattaa käyttää matriisikaavoja?

Jos olet tottunut käyttämään Excelin kaavoja, tiedät, että kaavojen avulla voi suorittaa monimutkaisiakin toimintoja. Voit esimerkiksi laskea lainan kokonaiskustannukset tietyn vuosimäärän mukaan. Matriisikaavojen avulla voit suorittaa esimerkiksi seuraavia monimutkaisia tehtäviä:

  • solualueen sisältämien merkkimäärien laskeminen.

  • tiettyjen ehtojen mukaisten lukujen yhteenlaskeminen (esimerkiksi alueen alimmat arvot tai ylä- ja alarajan väliset luvut)

  • arvoalueen joka n:nnen arvon yhteenlaskeminen.

Matriisien ja matriisikaavojen lyhyt esittely

Matriisikaava on kaava, joka voi suorittaa samalla kertaa useita laskutoimituksia matriisin yhdelle tai usealle kohteelle. Matriisissa on siis kyse arvorivistä, arvosarakkeesta tai näiden yhdistelmästä. Matriisikaavat voivat palauttaa useita tuloksia tai yhden tuloksen. Voit esimerkiksi sijoittaa matriisikaavan solualueeseen ja käyttää kaavaa sarakkeen tai rivin välisummien laskemiseen. Matriisikaavan voi sijoittaa myös yksittäiseen soluun ja käyttää kaavaa yksittäisen summan laskemiseen. Jos matriisikaavaa käytetään useassa solussa, sitä kutsutaan monisoluiseksi kaavaksi. Jos matriisikaavaa käytetään vain yhdessä solussa, sitä kutsutaan yksisoluiseksi kaavaksi.

Seuraavan kohdan esimerkeissä näytetään, miten voit luoda monisoluisia ja yksisoluisia matriisikaavoja.

Kokeile itse!

Tässä harjoituksessa näet, miten voit käyttää monisoluisia ja yksisoluisia matriisikaavoja myyntilukujen laskemiseen. Ensimmäisessä vaiheessa käytetään monisoluista kaavaa välisummien laskemiseen. Toisessa vaiheessa käytetään yksisoluista kaavaa loppusumman laskemiseen.

Monisoluinen matriisikaava

Tämä on selaimeen upotettu työkirja. Vaikka työkirja sisältää mallitiedot, muista, ettet voi luoda tai muuttaa matriisikaavoja upotetussa työkirjassa vaan tarvitset siihen Excel-ohjelman. Näet upotetussa työkirjassa vastaukset sekä tekstiä, joka selittää, miten matriisikaavat toimivat, mutta kunnollisen käsityksen matriisikaavoista saa vasta, kun avaa työkirjan Excelissä.

Monisoluisen matriisikaavan luominen
  1. Kopioi koko alla oleva taulukko ja liitä se tyhjän Excel-taulukon soluun A1.

    Myyjä

    Automalli

    Myyntimäärä

    Yksikköhinta

    Kokonaismyynti

    Barnhill

    Sedan

    5

    33000

    Coupe

    4

    37000

    Ingle

    Sedan

    6

    24000

    Coupe

    8

    21000

    Jordania

    Sedan

    3

    29000

    Coupe

    1

    31000

    Pica

    Sedan

    9

    24000

    Coupe

    5

    37000

    Sanchez

    Sedan

    6

    33000

    Coupe

    8

    31000

    Kaava (loppusumma)

    Loppusumma

    '=SUMMA(C2:C11*D2:D11)

    =SUMMA(C2:C11*D2:D11)

  2. Näet kunkin myyjän Coupe- ja Sedan-mallien kokonaismyynnin valitsemalla E2:E11, kirjoittamalla kaavan =C2:C11*D2:D11 ja painamalla näppäinyhdistelmää Ctrl+Vaihto+Enter.

  3. Saat kaikkien myytyjen autojen loppusumman valitsemalla solun F11, kirjoittamalla kaavan =SUMMA(C2:C11*D2:D11) ja painamalla näppäinyhdistelmää Ctrl+Vaihto+Enter.

Voit ladata tämän työkirjan napsauttamalla vihreää Excel-painiketta mustassa palkissa työkirjan alareunassa. Voit sitten avata tiedoston Excelissä, valita matriisikaavat sisältävät solut ja ottaa kaavan käyttöön painamalla näppäinyhdistelmää Ctrl+Vaihto+Enter.

Jos käytät Exceliä, varmista, että Taul1 on aktiivinen, ja valitse sitten solut E2:E11. Paina F2-näppäintä ja kirjoita kaava =C2:C11*D2:D11 nykyiseen soluun E2. Jos painat Enter-näppäintä, huomaat, että kaava kirjoitetaan vain soluun E2 ja että solussa näkyy 165000. Enter-näppäimen painamisen sijaan sinun onkin painettava kaavan kirjoittamisen jälkeen näppäinyhdistelmää Ctrl+Vaihto+Enter. Tulokset näkyvät soluissa E2:E11. Huomaat myös, että kaava näkyy kaavarivillä muodossa {=C2:C11*D2:D11}. Tämä osoittaa, että kyse on matriisikaavasta. Esimerkki on seuraavassa taulukossa.

Kun painat näppäinyhdistelmää Ctrl+Vaihto+Enter, Excel lisää kaavan ympärille aaltosulkeet ({ }) ja sijoittaa kaavaesiintymän kuhunkin valitun alueen soluun. Kaavan lisääminen tapahtuu nopeasti, ja sarakkeessa E näkyy kunkin automallin kokonaismyyntimäärä myyjittäin. Jos valitset ensin E2, sitten E3, E4 ja niin edelleen, huomaat, että käytössä on koko ajan sama kaava: {=C2:C11*D2:D11}.

Matriisikaava laskee sarakkeen E summat

Yksisoluisen matriisikaavan luominen

Kirjoita työkirjan soluun F10 seuraava kaava ja paina näppäinyhdistelmää Ctrl+Vaihto+Enter:

=SUMMA(C2:C11*D2:D11)

Tässä tapauksessa Excel kertoo matriisin arvot (solualue C2–D11) ja käyttää SUMMA-funktiota summien yhteenlaskemiseen. Kokonaismyynnin tulos on 1 590 000 dollaria. Tämä esimerkki osoittaa, miten tehokas matriisikaava voi olla. Oletetaan esimerkiksi, että taulukossa on 1 000 riviä tietoja. Voit laskea yhteen osan tiedoista tai kaikki tiedot luomalla yksisoluisen matriisikaavan sen sijaan, että vetäisit kaavan kaikkiin 1 000 riviin.

Huomaa myös, että yksisoluinen kaava (solussa G11) on täysin riippumaton monisoluisesta kaavasta (kaava soluissa E2–E11). Tämä joustavuus on yksi matriisikaavojen käytön eduista. Voit muuttaa sarakkeessa E olevia kaavoja tai poistaa koko sarakkeen ilman, että solun G11 kaavassa tapahtuu muutoksia.

Muita matriisikaavojen etuja ovat:

  • Yhdenmukaisuus    Jos valitset minkä tahansa solun solusta E2 alaspäin, näet saman kaavan. Yhdenmukaisuus auttaa varmistamaan tietojen tarkkuuden.

  • Turvallisuus    Et voi korvata monisoluisen matriisikaavan osaa. Valitse esimerkiksi solu E3 ja paina Delete-näppäintä. Sinun on valittava joko koko solualue (E2–E11) ja muutettava koko matriisin kaavaa tai jätettävä matriisi ennalleen. Lisäksi kaavan muutos pitää vahvistaa painamalla näppäinyhdistelmää Ctrl+Vaihto+Enter.

  • Pienet tiedostokoot    Usein voit käyttää vain yhtä matriisikaavaa useiden välikaavojen sijaan. Esimerkiksi tässä työkirjassa käytetään yhtä matriisikaavaa sarakkeen E tulosten laskemiseen. Jos työkirjassa olisi käytetty tavallisia kaavoja (kuten =C2*D2; C3*D3; C4*D4…), sinun olisi pitänyt käyttää 11 eri kaavaa samojen tulosten laskemiseen.

Matriisikaavan syntaksi

Matriisikaavojen osissa käytetään yleensä vakiokaavasyntaksia. Matriisikaavan alussa on aina yhtäläisyysmerkki (=), ja matriisikaavoissa voi käyttää mitä tahansa valmista Excel-funktiota. Suurin ero tavallisiin kaavoihin verrattuna on se, että kaavan kirjoittaminen aloitetaan painamalla näppäinyhdistelmää Ctrl+Vaihto+Enter. Kun valitset tämän näppäinyhdistelmän, Excel lisää kaavan ympärille aaltosulkeet. Jos kirjoitat aaltosulkeet manuaalisesti, ne muunnetaan tekstimerkkijonoksi eikä matriisikaava toimi.

Matriisifunktiot ovat erittäin tehokas tapa muodostaa monimutkainen kaava. Matriisikaava =SUMMA(C2:C11*D2:D11) vastaa kaavaa =SUMMA(C2*D2;C3*D3;C4*D4;C5*D5;C6*D6;C7*D7;C8*D8;C9*D9;C10*D10;C11*D11).

Matriisikaavojen kirjoittaminen ja muuttaminen

Tärkeää    Paina näppäinyhdistelmää Ctrl+Vaihto+Enter aina, kun haluat kirjoittaa matriisikaavan tai muuttaa sitä. Tämä koskee sekä yksisoluisia että monisoluisia kaavoja.

Muista monisoluisia kaavoja käsiteltäessä seuraavat seikat:

  • Solualue on valittava ennen kaavan kirjoittamista. Harjoittelit tätä monisoluisen matriisikaavan luonnin yhteydessä, kun valitsit solut E2–E11.

  • Yksittäisen solun sisältöä ei voi muuttaa matriisikaavassa. Yritä muuttaa solun sisältöä valitsemalla työkirjan solu E3 ja painamalla Delete-näppäintä. Excelissä avautuu sanoma, joka ilmoittaa, että matriisin osaa ei voi muuttaa.

  • Voit siirtää tai poistaa kokonaisen matriisikaavan, mutta et voi siirtää tai poistaa osaa siitä. Toisin sanoen, jos haluat lyhentää matriisikaavaa, sinun on ensin poistettava aiemmin luotu kaava ja luotava kaava sitten uudelleen.

  • Jos haluat poistaa matriisikaavan, valitse koko kaava (esimerkiksi =C2:C11*D2:D11), paina ensin Delete-näppäintä ja sitten näppäinyhdistelmää Ctrl+Vaihto+Enter.

  • Monisoluiseen matriisikaavaan ei voi lisätä tyhjiä soluja eikä siitä voi poistaa soluja.

Matriisikaavan laajentaminen

Joskus saatat joutua laajentamaan matriisikaavaa. Prosessi ei ole monimutkainen, mutta pidä edellä mainitut ohjeet mielessä.

Tähän työkirjaan on lisätty uusia myyntirivejä riveille 12‑17. Haluamme seuraavaksi päivittää matriisikaavat siten, että myös lisärivit sisältyvät kaavaan.

Muista tehdä tämä Excel-työpöytäohjelmassa (kun olet ladannut työkirjan tietokoneeseesi).

Matriisikaavan laajentaminen
  1. Kopioi koko taulukko Excel-taulukon soluun A1.

    Myyjä

    Automalli

    Myyntimäärä

    Yksikköhinta

    Kokonaismyynti

    Barnhill

    Sedan

    5

    33000

    165000

    Coupe

    4

    37000

    148000

    Ingle

    Sedan

    6

    24000

    144000

    Coupe

    8

    21000

    168000

    Jordania

    Sedan

    3

    29000

    87000

    Coupe

    1

    31000

    31000

    Pica

    Sedan

    9

    24000

    216000

    Coupe

    5

    37000

    185000

    Sanchez

    Sedan

    6

    33000

    198000

    Coupe

    8

    31000

    248000

    Toth

    Sedan

    2

    esittää muodossa 27 000

    Coupe

    3

    30000

    Wang

    Sedan

    4

    22000

    Coupe

    1

    41000

    Young

    Sedan

    5

    32000

    Coupe

    3

    36000

    Loppusumma

  2. Valitse solu E18, kirjoita loppusumman kaava =SUMMA(C2:C17*D2:D17) soluun A20 ja paina näppäinyhdistelmää Ctrl+Vaihto+Enter.
    Vastauksen pitäisi olla 2 131 000.

  3. Valitse solualue, joka sisältää nykyisen matriisikaavan (E2:E11) sekä uusien tietojen vieressä olevat tyhjät solut (E12:E17). Toisin sanoen, valitse solut E2:E17.

  4. Siirry muokkaustilaan painamalla F2-näppäintä.

  5. Muuta kaavarivillä solu C11 soluksi C17 ja solu D11 soluksi D17. Paina sitten näppäinyhdistelmää Ctrl+Vaihto+Enter.
    Excel päivittää kaavan soluissa E2–E11 ja lisää kaavan uusiin soluihin E12–E17.

  6. Kirjoita matriisikaava = SUMMA(C2:C17*D2*D17) soluun F17, jotta se viittaa soluihin riveillä 2–17, ja lisää kaava painamalla näppäinyhdistelmää Ctrl+Vaihto+Enter.
    Uuden loppusumman pitäisi olla nyt 2 131 000.

Matriisikaavojen käyttämisen haittapuolia

Matriisikaavat ovat todella käteviä, mutta niiden käyttämiseen liittyy myös tiettyjä haittapuolia:

  • Saatat unohtaa painaa näppäinyhdistelmää Ctrl+Vaihto+Enter. Myös kokeneet Excel-käyttäjät voivat tehdä tämän virheen. Muista painaa tätä näppäinyhdistelmää aina, kun kirjoitat matriisikaavan tai muokkaat sitä.

  • Työkirjan muut käyttäjät eivät välttämättä ymmärrä kaavoja. Matriisikaavoja ei yleensä selitetä työkirjassa, joten jos muiden käyttäjien pitää muokata työkirjoja, matriisikaavojen käyttämistä on vältettävä tai muiden käyttäjien pitää tietää, miten kaavoja voi muuttaa.

  • Suuret matriisikaavat saattavat hidastaa laskutoimituksia tietokoneen käsittelynopeuden ja muistin määrän mukaan.

Sivun alkuun

Tietoja matriisivakioista

Matriisivakiot ovat matriisikaavojen osia. Matriisivakioita voi luoda määrittämällä kohdeluettelo ja lisäämällä luettelon ympärille aaltosulkeet ({ }), esimerkiksi

={1,2,3,4,5}

Tässä vaiheessa tiedät jo, että matriisikaavoja luotaessa on painettava näppäinyhdistelmää Ctrl+Vaihto+Enter. Koska matriisivakiot ovat matriisikaavojen osia, vakioiden ympärille lisätään aaltosulkeet manuaalisesti kirjoittamalla. Tämän jälkeen näppäinyhdistelmää Ctrl+Vaihto+Enter käytetään koko kaavan kirjoittamiseen.

Jos erotat kohteet toisistaan pilkkujen avulla, luot vaakasuuntaisen matriisin (rivi). Jos erotat kohteet toisistaan puolipisteiden avulla, luot pystysuuntaisen matriisin (sarake). Jos haluat luoda kaksiulotteisen matriisin, erota kunkin rivin kohteet toisistaan pilkuilla ja erota rivit toisistaan puolipisteillä.

Yhden rivin matriisi: {1,2,3,4}. Yhden sarakkeen matriisi: {1,2,3,4}. Kaksi riviä ja neljä saraketta sisältävä matriisi: {1,2,3,4;5,6,7,8}. Kahden rivin matriisissa ensimmäinen rivi on 1, 2, 3 ja 4 ja toinen rivi 5, 6, 7 ja 8. Yksi puolipiste erottaa rivit toisistaan 4:n ja 5:n välissä.

Matriisivakiota voi käyttää matriisikaavojen tapaan yhdessä Excelin valmiiden funktioiden kanssa. Seuraavassa osassa kuvataan, miten erityyppisiä vakioita luodaan ja miten vakioita voi käyttää yhdessä Excelin funktioiden kanssa.

Sivun alkuun

Yksi- ja kaksiulotteisten vakioiden luominen

Seuraavien toimien avulla voit harjoitella vaaka- ja pystysuuntaisten sekä kaksiulotteisten vakioiden luomista

Vaakasuuntaisen vakion luominen

  1. Käytä edellisessä kohdassa käytettyä työkirjaa tai avaa uusi työkirja.

  2. Valitse solut A1–E1.

  3. Kirjoita kaavariville seuraava kaava ja paina sitten näppäinyhdistelmää Ctrl+Vaihto+Enter:

    ={1,2,3,4,5}

    Tässä tapauksessa sinun on kirjoitettava avaavat ja sulkevat aaltosulkeet ({ }).

    Näet seuraavat tulokset.

    Matriisikaavan vaakasuuntainen matriisivakio

Pystysuuntaisen vakion luominen

  1. Valitse työkirjassa viiden solun sarake.

  2. Kirjoita kaavariville seuraava kaava ja paina sitten näppäinyhdistelmää Ctrl+Vaihto+Enter:

    ={1;2;3;4;5}

    Näet seuraavat tulokset.

    Matriisikaavan pystysuuntainen matriisivakio

Kaksiulotteisen vakion luominen

  1. Valitse työkirjassa alue, joka on neljä saraketta leveä ja kolme riviä korkea.

  2. Kirjoita kaavariville seuraava kaava ja paina sitten näppäinyhdistelmää Ctrl+Vaihto+Enter:

    ={1,2,3,4;5,6,7,8;9,10,11,12}

    Näet seuraavat tulokset:

    Matriisikaavan kaksiulotteinen vakio

Vakioiden käyttäminen kaavoissa

Tässä yksikertaisessa esimerkissä käytetään vakioita:

  1. Luo mallityökirjaan uusi laskentataulukko.

  2. Kirjoita 3 soluun A1, 4 soluun B1, 5 soluun C1, 6 soluun D1 ja 7 soluun E1.

  3. Kirjoita soluun A3 seuraava kaava ja paina näppäinyhdistelmää Ctrl+Vaihto+Enter:

    =SUMMA(A1:E1*{1,2,3,4,5})

    Huomaa, että Excel lisää vakion ympärille toiset aaltosulkeet, koska olet määrittänyt kaavan matriisikaavaksi.

    Matriisikaava, jossa on matriisivakio

    Arvo 85 näkyy solussa A3.

Seuraavassa osassa kerrotaan, miten kaava toimii.

Matriisivakion syntaksi

Äsken käyttämäsi kaava sisältää useita osia.

Sellaisen matriisikaavan syntaksi, jossa on matriisivakio

1. Funktio

2. Tallennettu matriisi

3. Kuvaus:

4. Matriisivakio

Viimeinen osa sulkeiden sisäpuolella on matriisivakio: {1,2,3,4,5}. Muista, että Excel ei lisää aaltosulkeita matriisivakioiden ympärille vaan sinun on kirjoitettava ne. Muista myös painaa näppäinyhdistelmää Ctrl+Vaihto+Enter, kun olet lisännyt matriisikaavaan vakion.

Koska Excel suorittaa ensin sulkeissa olevat lausekkeet, seuraavat kaksi osaa ovat työkirjaan tallennetut arvot (A1:E1) sekä operaattori. Tässä vaiheessa kaava kertoo tallennetun matriisin arvot vakion vastaavilla arvoilla. Tämä vaihe vastaa kaavaa:

=SUMMA(A1*1;B1*2;C1*3;D1*4;E1*5)

Lopuksi SUMMA-funktio lisää arvot ja solussa A3 näkyy summa 85.

Voit välttää tallennetun matriisin käyttämisen ja säilyttää operaattorin muistissa korvaamalla tallennetun matriisin toisella matriisivakiolla:

=SUMMA({3,4,5,6,7}*{1,2,3,4,5})

Kokeile tätä kopioimalla funktio, valitsemalla työkirjasta tyhjä solu, liittämällä kaava kaavariville ja painamalla sitten näppäinyhdistelmää Ctrl+Vaihto+Enter. Näet saman tuloksen kuin aiemmassa harjoituksessa, jossa käytettiin matriisikaavaa:

=SUMMA(A1:E1*{1,2,3,4,5})

Vakioissa käytettävät osat

Matriisivakiot voivat sisältää numeroita, tekstiä, loogisia arvoja (kuten TOSI ja EPÄTOSI) sekä virhearvoja (kuten #PUUTTUU). Numeroita voi käyttää kokonaislukuina, desimaalilukuina tai tieteellisinä lukuina. Jos käytät vakiossa tekstiä, tekstin ympärille on lisättävä lainausmerkit (").

Matriisivakiot eivät voi sisältää muita matriiseja, kaavoja tai funktioita. Toisin sanoen ne voivat sisältää vain pilkuilla tai puolipisteillä erotettuja numeroita tai tekstejä. Excel näyttää varoitussanoman, jos kirjoitat esimerkiksi kaavan {1,2,A1:D4} tai {1,2,SUMMA(Q2:Z8)}. Numeeriset arvot eivät voi sisältää prosenttimerkkejä, dollarin merkkejä, pilkkuja tai sulkeita.

Matriisivakioiden nimeäminen

Yksi parhaista tavoista käyttää matriisivakioita on nimetä ne. Nimettyjä vakioita on helppo käyttää, ja niiden avulla matriisikaavoista voidaan tehdä helpommin ymmärrettäviä muille käyttäjille. Voit nimetä matriisivakion ja käyttää sitä kaavassa seuraavalla tavalla:

  1. Valitse Kaavat-välilehden Määritetyt nimet ryhmästä Määritä nimi.
    Näkyviin tulee Määritä nimi -valintaikkuna.

  2. Kirjoita Nimi-ruutuun Neljännes1.

  3. Kirjoita Viittaa-ruutuun seuraava vakio (muista lisätä aaltosulkeet):

    ={"tammikuu","helmikuu","maaliskuu"}

    Valintaikkunan sisältö näyttää nyt tältä:

    Kaavan Muokkaa nimeä -valintaruutu

  4. Valitse ensin OK ja sitten kolmen tyhjän solun rivi.

  5. Kirjoita seuraava kaava ja paina näppäinyhdistelmää Ctrl+Vaihto+Enter:

    =Neljännes1

    Näet seuraavat tulokset.

    Kaavana lisätty nimetty matriisi

Kun käytät nimettyä vakiota matriisikaavana, muista lisätä yhtäläisyysmerkki. Jos et lisää yhtäläisyysmerkkiä, Excel tulkitsee matriisin tekstimerkkijonoksi eikä kaava toimi odotetulla tavalla. Muista myös, että voit käyttää tekstin ja numeroiden yhdistelmiä.

Matriisivakioiden vianmääritys

Seuraavat ongelmat saattavat estää matriisivakioiden toimimisen:

  • Tiettyjä osia ei ole erotettu oikealla merkillä. Jos jätät pilkun tai puolipisteen pois tai jos sijoitat sen väärään paikkaan, matriisivakiota ei välttämättä luoda oikein tai näyttöön saattaa tulla varoitussanoma.

  • Olet ehkä valinnut solualueen, joka ei vastaa vakion osien määrää. Jos esimerkiksi valitset kuuden solun sarakkeen käytettäväksi yhdessä viisisoluisen vakion kanssa, tyhjässä solussa näkyy virhearvo #PUUTTUU. Jos olet valinnut liian vähän soluja, Excel poistaa arvot, joilla ei ole vastaavaa solua.

Matriisivakioiden käyttäminen

Seuraavissa esimerkeissä esitellään muutamia matriisivakioiden käyttötapoja matriisikaavoissa. Muutamissa esimerkeissä käytetään TRANSPONOI-funktiota, joka muuttaa rivit sarakkeiksi ja päinvastoin.

Matriisin kunkin kohteen kertominen

  1. Luo uusi laskentataulukko ja valitse tyhjä solualue, joka on neljä saraketta leveä ja kolme riviä korkea.

  2. Kirjoita seuraava kaava ja paina näppäinyhdistelmää Ctrl+Vaihto+Enter:

    ={1,2,3,4;5,6,7,8;9,10,11,12}*2

Matriisin kohteiden neliöjuuri

  1. Valitse tyhjä solualue, joka on neljä saraketta leveä ja kolme riviä korkea.

  2. Kirjoita seuraava matriisikaava ja paina näppäinyhdistelmää Ctrl+Vaihto+Enter:

    ={1,2,3,4;5,6,7,8;9,10,11,12}*{1,2,3,4;5,6,7,8;9,10,11,12}

    Voit myös kirjoittaa seuraavan matriisikaavan, jossa käytetään sirkumfleksioperaattoria (^):

    ={1,2,3,4;5,6,7,8;9,10,11,12}^2

Yksiulotteisen rivin transponoiminen

  1. Valitse viiden tyhjän solun kokoinen sarake.

  2. Kirjoita seuraava kaava ja paina näppäinyhdistelmää Ctrl+Vaihto+Enter:

    =TRANSPONOI({1,2,3,4,5})

Vaikka olet määrittänyt vaakasuuntaisen matriisivakion, TRANSPONOI-funktio muuntaa matriisivakion sarakkeeksi.

Yksiulotteisen sarakkeen transponoiminen

  1. Valitse viiden tyhjän solun kokoinen rivi.

  2. Kirjoita seuraava kaava ja paina näppäinyhdistelmää Ctrl+Vaihto+Enter:

    =TRANSPONOI({1;2;3;4;5})

Vaikka olet määrittänyt pystysuuntaisen matriisivakion, TRANSPONOI-funktio muuntaa matriisivakion riviksi.

Kaksiulotteisen vakion transponoiminen

  1. Valitse solualue, joka on kolme saraketta leveä ja neljä riviä korkea.

  2. Kirjoita seuraava vakio ja paina näppäinyhdistelmää Ctrl+Vaihto+Enter:

    =TRANSPONOI({1,2,3,4;5,6,7,8;9,10,11,12})

TRANSPONOI-funktio muuntaa kunkin rivin sarakejoukoksi.

Sivun alkuun

Perusmatriisikaavojen käyttäminen

Tässä osassa on esimerkkejä perusmatriisikaavoista.

Matriisien ja matriisivakioiden luominen olemassa olevista arvoista

Seuraavassa esimerkissä kerrotaan, miten voit luoda yhteyden eri laskentataulukoiden solualueiden välille käyttämällä matriisikaavoja. Esimerkistä käy myös ilmi, miten voi luoda matriisivakion samasta arvojoukosta.

Matriisin luominen olemassa olevista arvoista

  1. Valitse Excel-taulukosta solut C8:E10 ja kirjoita tämä kaava:

    ={10,20,30;40,50,60;70,80,90}

    Muista kirjoittaa { (aloittava aaltosulje) ennen lukua 10 ja } (päättävä aaltosulje) luvun 90 jälkeen, sillä luot lukumatriisia.

  2. Paina näppäinyhdistelmää Ctrl+Vaihto+Enter, joka syöttää tämän lukumatriisin solualueeseen C8:E10 matriisikaavan avulla.
    Laskentataulukon solujen C8–E10 pitäisi näyttää tältä:

    10

    20

    30

    40

    50

    60

    70

    80

    90.

  3. Valitse solualue C1–E3.

  4. Kirjoita seuraava kaava kaavariville ja paina sitten näppäinyhdistelmää Ctrl+Vaihto+Enter:

    =C8:E10

    Soluihin C1–E3 tulee näkyviin 3x3-solumatriisi, jossa on samat arvot kuin soluissa C8–E10.

Matriisivakion luominen olemassa olevista arvoista

  1. Siirry muokkaustilaan painamalla F2-näppäintä solujen C1:C3 ollessa valittuina.
    Matriisikaavan pitäisi edelleen olla = C8:E10.

  2. Muunna soluviittaukset arvoiksi painamalla F9-näppäintä.
    Excel muuntaa arvot matriisivakioksi. Kaavan pitäisi olla ={10,20,30;40,50,60;70,80,90}, aivan kuten C8:E10.

  3. Määritä matriisivakio matriisikaavaksi painamalla näppäinyhdistelmää Ctrl+Vaihto+Enter.

Solualueen merkkimäärän laskeminen

Seuraavassa esimerkissä kerrotaan, miten voit laskea solualueen merkkien määrän (mukaan lukien välilyönnit).

  1. Kopioi koko taulukko ja liitä se laskentataulukon soluun A1.

  2. Saat merkkien kokonaismäärän näkyviin soluihin A2:A6 (66) valitsemalla solun A9 ja painamalla näppäinyhdistelmää Ctrl+Vaihto+Enter.

  3. Saat solujen A2:A6 pisimmän solun (A3) sisällön näkyviin valitsemalla solun A12 ja painamalla näppäinyhdistelmää Ctrl+Vaihto+Enter.

Tiedot

Tämä on

solujoukko, joka

muodostaa

yhdistettynä

yhden lauseen.

Solujen A2:A6 merkit yhteensä

=SUMMA(PITUUS(A2:A6))

Pisimmän solun sisältö (A3)

=INDEKSI(A2:A6,VASTINE(MAKS(PITUUS(A2:A6)),PITUUS(A2:A6),0),1)

Seuraava, solussa A9 käytettävä kaava, laskee merkkien kokonaismäärän (66) soluissa A2–A6.

=SUMMA(PITUUS(A2:A6))

Tässä tapauksessa PITUUS-funktio laskee solualueen kunkin solun tekstimerkkijonon pituuden. SUMMA-funktio laskee arvot yhteen ja näyttää tuloksen (66) solussa A9, eli solussa, joka sisältää kaavan.

Alueen n:n pienimmän arvon etsiminen

Tässä esimerkissä kerrotaan, miten voit etsiä solualueen kolme pienintä arvoa.

  1. Valitse solut A16–A18.
    Tämä solujoukko pitää sisällään matriisikaavan palauttamat tulokset.

  2. Kirjoita kaavariville seuraava kaava ja paina sitten näppäinyhdistelmää Ctrl+Vaihto+Enter:

    =PIENI(A5:A14,{1;2;3})

Arvot 400, 475 ja 500 näkyvät soluissa A16–A18.

Tämä kaava käyttää matriisivakiota PIENI-funktion kolminkertaiseen arviointiin ja palauttaa soluihin A1:A10 sisältyvän pienimmän (1), toiseksi pienimmän (2) ja kolmanneksi pienimmän (3) matriisin jäsenen. Jos haluat etsiä lisää arvoja, lisää vakioon argumentteja ja vastaava määrä tulossoluja A12:A14-alueelle. Voit käyttää tämän kaavan kanssa myös muita funktioita, kuten SUMMA- tai KESKIARVO-funktioita. Esimerkki:

=SUMMA(PIENI(A 5 :A1 4 ,{1;2;3}))

=KESKIARVO(PIENI(A 5 :A1 4 ,{1;2;3}))

Alueen n:n suurimman arvon etsiminen

Jos haluat etsiä alueen suurimmat arvot, korvaa PIENI-funktio SUURI-funktiolla. Seuraavassa esimerkissä käytetään lisäksi RIVI- ja EPÄSUORA-funktioita.

  1. Valitse solut A1–A3.

  2. Kirjoita kaavariville tämä kaava ja paina sitten näppäinyhdistelmää Ctrl+Vaihto+Enter:

    =SUURI(A5:A14,RIVI(EPÄSUORA("1:3")))

Arvot 3200, 2700 ja 2000 näkyvät soluissa A1–A3.

Tässä vaiheessa sinun on ehkä hyvä tietää hieman lisää RIVI- ja EPÄSUORA-funktioista. Voit käyttää RIVI-funktiota peräkkäisten kokonaislukujen taulukon luomiseen. Valitse esimerkiksi tyhjä kymmenen solun sarake harjoitustyökirjasta, kirjoita seuraava matriisikaava soluihin A5:A14 ja paina sitten näppäinyhdistelmää Ctrl+Vaihto+Enter:

=RIVI(1:10)

Kaava luo kymmenen peräkkäisen kokonaisluvun sarakkeen. Jos haluat tarkastella mahdollisia ongelmia, lisää rivi matriisikaavan sisältävän alueen yläpuolelle (eli rivin 1 yläpuolelle). Excel tarkistaa riviviittaukset ja kaava luo kokonaisluvut 2–11. Voit korjata ongelman lisäämällä kaavaan EPÄSUORA-funktion:

=RIVI(EPÄSUORA("1:10"))

EPÄSUORA-funktio käyttää tekstimerkkijonoja argumentteina (tästä syystä alueen 1:10 ympärillä on lainausmerkit). Excel ei muuta tekstiarvoja, kun lisäät rivejä tai siirrät matriisikaavaa. Funktion ansiosta RIVI-funktio luo aina matriisin ainoastaan haluamistasi kokonaisluvuista.

Tutkitaanpa hieman tarkemmin aiemmin käyttämääsi kaavaa =SUURI(A1:A10,RIVI(EPÄSUORA("1:3"))) sisemmistä sulkeista alkaen. EPÄSUORA-funktio palauttaa joukon tekstiarvoja (tässä tapauksessa arvot 1–3). RIVI-funktio luo puolestaan kolmisoluisen sarakemuotoisen taulukon. SUURI-funktio käyttää solualueen A1:A10 arvoja kolmesti (kerran jokaiselle RIVI-funktion palauttamalle viittaukselle). Arvot 3200, 2700 ja 2000 palautetaan kolmisoluiseen saraketaulukkoon. Jos haluat etsiä lisää arvoja, suurenna EPÄSUORA-funktion solualuetta.

Voit käyttää tätä kaavaa myös muiden funktioiden, kuten SUMMA- ja KESKIARVO-funktioiden, kanssa.

Solualueen pisimmän tekstimerkkijonon etsiminen

Kaava toimii vain silloin, kun tietoalue sisältää yksittäisen solusarakkeen. Kirjoita seuraava kaava Taul3-laskentataulukon soluun A16 ja paina näppäinyhdistelmää Ctrl+Vaihto+Enter:

=INDEKSI(A6:A9;VASTINE(MAKS(PITUUS(A6:A9));PITUUS(A6:A9);0);1)

Solussa A16 näkyy teksti "bunch of cells that".

Tutkitaanpa kaavaa hieman tarkemmin sisimmäisistä osista alkaen. PITUUS-funktio palauttaa solualueen A6:A9 kunkin kohteen pituuden. MAKS-funktio laskee kohteiden suurimman arvon, joka vastaa pisintä tekstimerkkijonoa (tässä tapauksessa solu A7).

Tässä vaiheessa asia muuttuu hieman monimutkaisemmaksi. VASTINE-funktio laskee sen solun siirtymän (suhteellisen sijainnin), joka sisältää pisimmän tekstimerkkijonon. Funktio tarvitsee laskemisessa kolmea argumenttia hakuarvo, hakumatriisi sekä vastinetyyppi. VASTINE-funktio etsii hakumatriisin määritetylle hakuarvolle. Tässä tapauksessa hakuarvo on pisin tekstimerkkijono:

(MAKS(PITUUS( A6 : A9 ))

ja merkkijono sijaitsee tässä matriisissa:

PITUUS( A6:A9 )

Vastinetyyppi on 0. Vastinetyyppi voi muodostua arvosta 1, 0 tai -1. Jos määrität arvon 1, VASTINE-funktio palauttaa suurimman arvon, joka on pienempi tai yhtä suuri kuin hakuarvo. Jos määrität arvon 0, VASTINE-funktio palauttaa ensimmäisen arvon, joka on täsmälleen yhtä suuri kuin hakuarvo. Jos määrität arvon -1, VASTINE-funktio palauttaa pienimmän arvon, joka on suurempi tai yhtä suuri kuin määritetty hakuarvo. Jos et määritä vastinetyyppiä, Excel käyttää oletusarvoa 1.

Lopuksi INDEKSI-funktio käyttää seuraavia argumentteja: matriisi, matriisin rivinumero ja matriisin sarakenumero. Solualue A6:A9 määrittää matriisin, VASTINE-funktio määrittää solun osoitteen ja viimeinen argumentti (1) määrittää, että arvo tulee matriisin ensimmäisestä sarakkeesta.

Sivun alkuun

Laajennettujen matriisikaavojen käyttäminen

Tässä osassa on esimerkkejä laajennetuista matriisikaavoista.

Virhearvoja sisältävän alueen yhteenlaskeminen

Excelin SUMMA-funktio ei toimi, jos yrität laskea yhteen alueen, joka sisältää virhearvon, kuten #PUUTTUU. Tässä esimerkissä näytetään, miten voit laskea yhteen Tiedot-nimisen alueen, joka sisältää virheitä.

=SUMMA(JOS(ONVIRHE(Tiedot);"";Tiedot))

Kaava luo uuden taulukon, joka sisältää alkuperäiset arvot lukuun ottamatta virhearvoja. Sisimmistä funktioista aloitettaessa ONVIRHE-funktio etsii virheitä solualueelta (Tiedot). JOS-funktio palauttaa tietyn arvon, jos määrittämäsi arvo on TOSI, ja tietyn arvon, jos määrittämäsi arvo on EPÄTOSI. Tässä esimerkissä funktio palauttaa tyhjän merkkijonon ("") virhearvoille, koska niiden arvo on TOSI ja loput alueen (Tiedot) arvot, koska niiden arvo on EPÄTOSI.Tämä tarkoittaa sitä, että loput arvot eivät sisällä virhearvoja. SUMMA-funktio laskee lopuksi suodatetun taulukon kokonaissumman.

Alueen virhearvojen määrän laskeminen

Tässä esimerkissä käytetään samantyyppistä kaavaa kuin edellisessä esimerkissä, mutta kaava palauttaa Tiedot-alueen virhearvojen määrän sen sijaan, että virhearvot suodatettaisiin pois:

=SUMMA(JOS(ONVIRHE(Tiedot);1;0))

Tämä kaava luo matriisin, joka sisältää arvon 1 soluille, joissa on virheitä, ja arvon 0 soluille, joissa ei ole virheitä. Voit yksinkertaistaa kaavaa ja saada saman tuloksen poistamalla JOS-funktion kolmannen argumentin:

=SUMMA(JOS(ONVIRHE(Tiedot);1))

Jos et määritä argumenttia, JOS-funktio palauttaa arvon EPÄTOSI, jos solu ei sisällä virhearvoa. Voit yksinkertaistaa kaavaa edelleen:

=SUMMA(JOS(ONVIRHE(Tiedot)*1))

Tämä kaava toimii, koska TOSI*1=1 ja EPÄTOSI*1=0.

Arvojen laskeminen yhteen ehtojen perusteella

Saatat joutua laskemaan arvot yhteen ehtojen perusteella. Esimerkiksi seuraava matriisikaava laskee yhteen vain positiiviset kokonaisluvut Myynti-nimisellä alueella:

=SUMMA(JOS(Myynti>0;Myynti))

JOS-funktio luo positiivisten arvojen ja epätosien arvojen matriisin. SUMMA-funktio ohittaa virheelliset arvot, koska 0+0=0. Kaavassa käytettävä solualue voi sisältää haluamasi määrän rivejä ja sarakkeita.

Voit myös laskea yhteen arvot, jotka vastaavat useampaa kuin yhtä ehtoa. Esimerkiksi seuraava matriisikaava laskee arvot, jotka ovat suurempia kuin 0, mutta pienempiä tai yhtä suuria kuin 5:

=SUMMA(Myynti>0)*(Myynti<=5)*(Myynti))

Huomaa, että tämä kaava palauttaa virheen, jos alue sisältää ei-numeerisia soluja.

Voit myös luoda matriisikaavoja, jotka käyttävät TAI-ehtoa. Voit esimerkiksi laskea yhteen arvot, jotka ovat pienempiä kuin 5 tai suurempia kuin 15:

=SUMMA(JOS((Myynti<5)+(Myynti>15);Myynti))

JOS-funktio etsii kaikki arvot, jotka ovat pienempiä kuin 5 tai suurempia kuin 15 ja siirtää arvot sitten SUMMA-funktiolle.

Matriisikaavoissa ei voi käyttää suoraan JA- ja TAI-funktioita, koska nämä funktiot palauttavat yksittäisen tuloksen (joko TOSI tai EPÄTOSI) ja matriisifunktiot edellyttävät tulosmatriiseja. Voit ohittaa ongelman käyttämällä edellisessä kaavassa esiteltyä logiikkaa. Toisin sanoen suoritat matemaattisia toimintoja, kuten yhteenlaskuja ja kertolaskuja, arvoille, jotka vastaavat TAI- tai JA-ehtoa.

Nollat pois jättävän keskiarvon laskeminen

Tässä esimerkissä näytetään, miten voit poistaa nollat alueelta, kun haluat laskea alueen arvojen keskiarvon. Kaavassa käytetään Myynti-nimistä tietoaluetta:

=KESKIARVO(JOS(Myynti<>0;Myynti))

JOS-funktio luo matriisin arvoista, jotka ovat eri suuria kuin 0, ja siirtää arvot sitten KESKIARVO-funktiolle.

Kahden solualueen välisten eroavaisuuksien määrän laskeminen

Tämä matriisikaava vertaa OmatTiedot- ja ToisenTiedot-nimisten solualueiden arvoja ja palauttaa solualueiden välisten eroavaisuuksien määrän. Jos kahden solualueen sisällöt vastaavat toisiaan, kaava palauttaa arvon 0. Kaavan käyttäminen edellyttää sitä, että solualueet ovat samankokoisia ja että niillä on sama ulottuvuus. (Jos esimerkiksi OmatTiedot-alue käsittää 3 riviä ja 5 saraketta, myös ToisenTiedot-alueessa on oltava 3 riviä ja 5 saraketta.)

=SUMMA(JOS( OmatTiedot =ToisenTiedot,0,1))

Kaava luo uuden matriisin, joka on samankokoinen kuin vertailtavat alueet. JOS-funktio lisää matriisiin arvon 0 ja arvon 1 (0 tarkoittaa eroavaisuuksia ja 1 identtisiä soluja). SUMMA-funktio palauttaa matriisin arvojen summan.

Voit yksinkertaistaa kaavaa seuraavasti:

=SUMMA(1*( OmatTiedot <> ToisenTiedot ))

Tämä kaava toimii samalla tavalla kuin alueen virhearvoja laskeva kaava, koska TOSI*1=1 ja EPÄTOSI*1=0.

Alueen suurimman arvon sijainnin etsiminen

Tämä matriisikaava palauttaa yksisarakkeisen Tiedot-nimisen alueen suurimman arvon rivinumeron:

=MIN(JOS(Tiedot=MAKS(Tiedot);RIVI(Tiedot);""))

JOS-funktio luo uuden matriisin, joka vastaa Tiedot-aluetta. Jos solu sisältää alueen suurimman arvon, matriisissa on rivinumero. Muissa tapauksissa matriisi sisältää tyhjän merkkijonon (""). MIN-funktio käyttää uutta matriisia toisena argumenttinaan ja palauttaa pienimmän arvon, joka vastaa Tiedot-alueen suurimman arvon rivinumeroa. Jos Tiedot-alueella on useita täysin samanlaisia suurimpia arvoja, kaava palauttaa ensimmäisen arvon rivinumeron.

Jos haluat selvittää suurimman arvon todellisen solun osoitteen, käytä seuraavaa kaavaa:

=OSOITE(MIN(JOS(Tiedot=MAKS(Tiedot);RIVI(Tiedot);""));SARAKE(Tiedot))

Sivun alkuun

Tarvitsetko lisätietoja?

Voit aina kysyä neuvoa Excel Tech Community -yhteisön asiantuntijalta, saada tukea yhteisön vastauksista tai ehdottaa uutta ominaisuutta tai parannusta Excel User Voice -sivustolla.

Huomautus: Konekäännöksestä ilmoittava vastuusvapauslauseke: Tämä artikkeli on käännetty tietokonejärjestelmällä, eikä kieliasiantuntija ole muokannut sitä. Microsoft tarjoaa nämä konekäännökset avuksi muille kuin englantia puhuville käyttäjille, jotta he saavat lisätietoja Microsoftin tuotteista, palveluista ja tekniikoista. Koska artikkeli on koneellisesti käännetty, se saattaa sisältää sanasto-, lauseoppi- ja kielioppivirheitä.

Katso myös

Yleistä kaavoista

Kehitä taitojasi
Tutustu koulutusmateriaaliin
Saat uudet ominaisuudet ensimmäisten joukossa
Liity Office Insider -käyttäjiin

Oliko näistä tiedoista hyötyä?

Kiitos palautteesta!

Kiitos palautteestasi! Näyttää siltä, että Office-tukiedustajamme avusta voi olla sinulle hyötyä.

×