Matriisikaavoihin liittyviä ohjeita ja esimerkkejä

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

Kopioi koko alla oleva taulukko ja liitä se tyhjän taulukon soluun A1.

Myynti henkilö	Auto tyyppi	Myynti määrä	Yksikkö hinta	Kokonais myynti
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)

1. Jos haluat nähdä kunkin myyjän coupe- ja sedan-kokonaismyynnin, valitse solut E2:E11, kirjoita kaava =C2:C11*D2:D11ja paina sitten näppäinyhdistelmää Ctrl+Vaihto+Enter.

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

Kun painat Ctrl+Vaihto+Enter, Excel ympäröi kaavan aaltosulkeilla ({ }) ja lisää kaavan esiintymän valitun alueen kuhunkin 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}. 

• Yksisoluisen matriisikaavan luominen

Kirjoita työkirjan soluun D13 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 D13) 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 D13 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    You cannot overwrite a component of a multi-cell array formula. Valitse esimerkiksi solu E3 ja paina Delete-näppäintä. You have to either select the entire range of cells (E2 through E11) and change the formula for the entire array, or leave the array as is. Turvallisuussyistä sinun on vahvistettava kaavaan tehdyt muutokset painamalla näppäinyhdistelmää Ctrl+Vaihto+Enter.

• Entistä pienemmät tiedostot    You can often use a single array formula instead of several intermediate formulas. Esimerkiksi työkirjassa lasketaan tulokset sarakkeessa E yhdellä matriisikaavalla. Jos olisit käyttänyt vakiokaavoja (kuten =C2*D2, C3*D3, C4*D4...), olisit käyttänyt 11 erilaista kaavaa samojen tulosten laskemiseen.

Yleensä matriisikaavoissa käytetään vakiokaavan syntaksia. Matriisikaavan alussa on aina yhtäläisyysmerkki (=), ja matriisikaavoissa voi käyttää useimpia valmiita Excel-funktiota. Tärkein ero on se, että matriisikaavaa käytettäessä kaava kirjoitetaan painamalla Ctrl+Vaihto+Enter. Kun teet tämän, Excel ympäröi matriisikaavasi aaltosulkeilla - jos kirjoitat aaltosulkeet manuaalisesti, kaava muunnetaan tekstimerkkijonoksi, eikä se toimi.

Matriisifunktiot voivat olla tehokas tapa luoda monimutkaisia kaavoja. 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).

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-alue (esimerkiksi E2:E11) ja paina sitten Delete-näppäintä.

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

At times, you may need to expand an array formula. Valitse aiemmin luodun matriisialueen ensimmäinen solu ja jatka, kunnes olet valinnut koko alueen, johon haluat laajentaa kaavan. Muokkaa kaavaa painamalla F2 ja vahvista sitten kaava painamalla näppäinyhdistelmää CTRL+VAIHTO+ENTER, kun olet säätänyt kaava-aluetta. Tärkeintä on valita koko alue alkaen matriisin vasemmasta yläkulmasta. Vasemmassa yläkulmassa oleva solu on se solu, jota muokataan.

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

• Muut työkirjan käyttäjät eivät ehkä ymmärrä kaavojasi. Käytännössä matriisikaavoja ei yleensä selitetä laskentataulukossa. Jos muiden käyttäjien on muokattava työkirjojasi, vältä matriisikaavoja tai varmista, että kyseiset henkilöt tietävät matriisikaavoista ja ymmärtävät, miten niitä voi tarvittaessa muuttaa.

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

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 on 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. The following sections explain how to create each kind of constant and how to use these constants with functions in Excel.

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

Vaakasuuntaisen vakion luominen

1. Valitse tyhjässä laskentataulukossa solut A1–E1.

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

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

   Tässä tapauksessa sinun pitää kirjoittaa avaavat ja sulkevat aaltosulkeet ({ }), ja Excel lisää toisen joukon puolestasi.

   Näet seuraavat tulokset.

   

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.

   

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:

   

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.

   

   Arvo 85 näkyy solussa A3.

Seuraavassa osassa kerrotaan, miten kaava toimii.

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

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})

Array constants can contain numbers, text, logical values (such as TRUE and FALSE), and error values ( such as #N/A). 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.

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

   

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.

   

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

Seuraavat ongelmat saattavat estää matriisivakioiden toimimisen:

• Some elements might not be separated with the proper character. 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. Conversely, if you select too few cells, Excel omits the values that don't have a corresponding cell.

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.

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. 

2. Muuta soluviittaukset arvoiksi painamalla F9-näppäintä. Excel muuntaa arvot matriisivakioksi. Kaavan pitäisi nyt olla ={10\20\30;40\50\60;70\80\90}.

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.

   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)

2. Valitse solu A8 ja paina sitten Ctrl+Vaihto+Enter nähdäksesi solujen A2:A6 merkkien kokonaismäärän (66).

3. Valitse solu A10 ja paina sitten Ctrl+Vaihto+Enter nähdäksesi solujen A2:A6 pisimmän solun (solu A3) sisällön.

Seuraava, solussa A8 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 sitten nämä arvot yhteen ja näyttää tuloksen (66).

Alueen n:n pienimmän arvon etsiminen

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

1. Kirjoita joitakin satunnaisia lukuja soluihin A1:A11.

2. Valitse solut C1–C3. Tämä solujoukko pitää sisällään matriisikaavan palauttamat tulokset.

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

   =PIENI(A1:A11,{1;2;3})

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ää argumentteja vakioon. Voit käyttää tämän kaavan kanssa myös muita funktioita, kuten SUMMA- tai KESKIARVO-funktioita. Esimerkki:

=SUMMA(PIENI(A1:A10,{1\2\3}))

=KESKIARVO(PIENI(A1:A10,{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 D1–D3.

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

   =SUURI(A1:A10,RIVI(EPÄSUORA("1:3")))

At this point, it may help to know a bit about the ROW and INDIRECT functions. You can use the ROW function to create an array of consecutive integers. Valitse esimerkiksi tyhjä 10 solun sarake harjoitustyökirjasta, kirjoita tämä matriisikaava 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.

Tarkastellaan aiemmin käyttämääsi kaavaa — =SUURI(A5:A14,RIVI(EPÄSUORA("1: 3"))) — alkaen sisemmistä sulkeista ja siirtymällä ulospäin: EPÄSUORA-funktio palauttaa joukon tekstiarvoja, tässä tapauksessa arvot 1-3. RIVI-funktio puolestaan ​​luo kolmen solun sarakematriisin. SUURI-funktio käyttää arvoja solualueella A5:A14, ja se arvioidaan kolme kertaa, kerran kutakin RIVI-funktion palauttamaa vertailua kohti. The values 3200, 2700, and 2000 are returned to the three-cell columnar array. If you want to find more values, you add a greater cell range to the INDIRECT function.

Kuten aikaisemmissa esimerkeissä, voit käyttää tätä kaavaa myös muiden funktioiden, kuten SUMMA- ja KESKIARVO-funktioiden, kanssa.

Solualueen pisimmän tekstimerkkijonon etsiminen

Palaa aiempaan tekstimerkkijonoesimerkkiin, kirjoita seuraava kaava tyhjään soluun ja paina näppäinyhdistelmää Ctrl+Vaihto+Enter:

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

Näkyy teksti "bunch of cells that".

Tutkitaanpa kaavaa hieman tarkemmin sisimmäisistä osista alkaen. PITUUS-funktio palauttaa solualueen A2:A6 kunkin kohteen pituuden. MAKS-funktio laskee kyseisten kohteiden suurimman arvon, joka vastaa pisintä merkkijonoa, joka on solussa A3.

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:

(MAX(LEN(A2:A6))

ja merkkijono sijaitsee tässä matriisissa:

LEN(A2:A6)

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.

Finally, the INDEX function takes these arguments: an array, and a row and column number within that array. Solualue A2:A6 antaa matriisin, VASTINE-funktio antaa solun osoitteen ja viimeinen argumentti (1) määrittää, että arvo tulee matriisin ensimmäisestä sarakkeesta.

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. In this case, it returns empty strings ("") for all error values because they evaluate to TRUE, and it returns the remaining values from the range (Data) because they evaluate to FALSE, meaning that they don't contain error values. The SUM function then calculates the total for the filtered array.

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. In other words, you perform math operations, such as addition or multiplication, on values that meet the OR or AND condition.

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

This array formula compares the values in two ranges of cells named MyData and YourData and returns the number of differences between the two. If the contents of the two ranges are identical, the formula returns 0. Jotta voit käyttää tätä kaavaa, solualueiden on oltava samankokoisia ja samasta dimensiosta (jos esimerkiksi MyData on kolmen rivin ja viiden sarakkeen alue, YourDatan on oltava myös kolme riviä ja viisi saraketta):

=SUMMA(JOS(MyData=YourData,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*(MyData<>YourData))

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))