Matriisikaavoihin liittyviä ohjeita ja esimerkkejä

Matriisikaavoihin liittyviä ohjeita ja esimerkkejä

Huomautus: Microsoft haluaa tarjota sinulle ajantasaisinta ohjesisältöä mahdollisimman nopeasti omalla kielelläsi. Tämä sivu on käännetty automaation avulla, ja siinä saattaa olla kielioppivirheitä tai epätarkkuuksia. Tarkoitus on, että sisällöstä on sinulle hyötyä. Kertoisitko sivun alareunassa olevan toiminnon avulla, oliko tiedoista hyötyä? Tästä pääset helposti artikkelin englanninkieliseen versioon.

Matriisi kaava on kaava, joka voi suorittaa useita lasku toimituksia yhtä tai useampaa matriisin kohdetta varten. Voit ajatella, että matriisi on arvojen rivi tai sarake tai arvojen rivien ja sarakkeiden yhdistelmä. Matriisi kaavat voivat palauttaa joko useita tuloksia tai yhden tuloksen.

Office 365: n syyskuun 2018-päivityksestä alkaen kaikki kaavat, jotka voivat palauttaa useita tuloksia, leviävät automaattisesti joko alaspäin tai viereisiin soluihin. Tämä toiminnan muutos liittyy myös useisiin uusiin dynaamisiin matriisi funktioihin. Dynaamisia matriisi kaavoja, olivatpa ne käytössä aiemmin luoduissa funktioissa tai dynaamisissa matriisi funktioissa, tarvitsee syöttää vain yhteen soluun ja vahvistaa painamalla ENTER-näppäintä. Aiemmat matriisi kaavat edellyttävät ensin koko tulostus alueeksi valitsemista ja sen jälkeen kaavan vahvistamista painamalla näppäin yhdistelmää CTRL + VAIHTO + ENTER. Niitä kutsutaan yleensä Täsmähakukonekaavoiksi.

Matriisi kaavojen avulla voit suorittaa monimutkaisia tehtäviä, kuten:

  • Luo nopeasti esimerkki joukkoja.

  • Laskee solu alueella olevien merkkien määrän.

  • Laskee yhteen vain ne luvut, jotka täyttävät tietyt ehdot, kuten alueella olevat pienimmät arvot tai ylä-ja alarajan väliset luvut.

  • Arvo joukon jokaisen N:nnen arvon summa.

Seuraavissa esimerkeissä näytetään, miten voit luoda monisoluisia ja yksisoluisia matriisi kaavoja. Jos mahdollista, olemme lisänneet esimerkkejä joistakin dynaamisista matriisi funktioista sekä dynaamisiksi ja vanhempina matriisina kirjoitetuista matriisi kaavoista.

Lataa Microsoftin esimerkkejä

Lataa tässä artikkelissa esimerkki työkirja, jossa on kaikki matriisi kaava esimerkit.

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

    Usean solun matriisi-funktio solussa H10 = F10: F19 * G10: G19 laskemaan yksikkö hinnan myymien autojen määrän.

  • Tässä lasketaan kunkin myyjän kokonaismyynti-ja Sedan-luvut kirjoittamalla = F10: F19 * G10: G19 soluun H10.

    Kun painat ENTER-näppäintä, tulokset leviävät soluihin H10: H19. Huomaa, että vuoto-alueet on korostettu reunuksella, kun valitset minkä tahansa solun vuoto alueella. Saatat myös huomata, että kaavat soluissa H10: H19 näkyvät harmaana. Ne ovat vain viitteenä, joten jos haluat muuttaa kaavaa, sinun on valittava solu H10, jossa pääkaava asuu.

  • Yksisoluinen matriisi kaava

    Yksisoluinen matriisi kaava, jonka avulla lasketaan kokonaissumma = summa (F10: F19 * G10: G19)

    Kirjoita tai kopioi ja liitä esimerkki työkirjan soluun H2O = summa (F10: F19 * G10: G19)ja paina sitten ENTER-näppäintä.

    Tässä tapa uksessa Excel kertoo matriisin arvot (solu alueella F10 – G19) ja lisää sitten summat yhteen summa-funktion avulla. 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ä solun H2O yksisoluinen kaava on täysin riippumaton usean solun kaavasta (kaava on soluissa H10 – H19). Tämä joustavuus on yksi matriisikaavojen käytön eduista. Voit muuttaa muita sarakkeessa H olevia kaavoja vaikuttamatta kaavoihin, jotka ovat kohdassa H2O. Se voi myös olla hyvä käytäntö, jos haluat, että nämä summat ovat riippumattomia, sillä se auttaa vahvistamaan tulosten tarkkuuden.

  • Dynaamiset matriisi kaavat tarjoavat myös seuraavat edut:

    • Yhdenmukaisuus    Jos napsautat mitä tahansa solua kohteesta H10 alaspäin, näet saman kaavan. Yhdenmukaisuus auttaa varmistamaan tietojen tarkkuuden.

    • Safety    Et voi korvata monisoluisen matriisi kaavan komponenttia. Valitse esimerkiksi solu H11 ja paina Delete-näppäintä. Excel ei muuta matriisin tulostetta. Jos haluat muuttaa sitä, sinun on valittava matriisin vasemmassa yläkulmassa oleva solu tai solu H10.

    • Smaller file sizes    You can often use a single array formula instead of several intermediate formulas. Esimerkiksi auto myynti-esimerkissä käytetään yhtä matriisi kaavaa sarakkeen E tulosten laskemiseen. Jos käytit vakio kaavoja, kuten = F10 * G10, F11 * G11, F12 * G12, jne., olet käyttänyt 11 eri kaavaa samojen tulosten laskemiseen. Se ei ole iso juttu, mutta entä jos sinulla olisi tuhansia rivejä yhteensä? Se voi vaikuttaa suurelta osin.

    • Tehokkuus    Matriisi funktiot voivat olla tehokas keino monimutkaisten kaavojen luomiseen. Matriisi kaava = summa (F10: F19 * G10: G19) on sama kuin tämä: = summa (F10 * G10, F11 * G11, F12 * G12, F13 * G13, F14 * G14, F15 * G15, F16 * G16).

    • Valuu    Dynaamiset matriisi kaavat leviävät automaattisesti tulostus alueelle. Jos lähde tiedot ovat Excel-taulukossa, dynaamisia matriisi kaavoja muutetaan automaattisesti, kun lisäät tai poistat tietoja.

    • #SPILL! Virhe    Dynaamiset matriisit #SPILL!-virheen käyttöön, joka osoittaa, että suunniteltu vuoto-väli on jostain syystä jumiutunut. Kun ratkaiset tukoksen, kaava kaatuu automaattisesti.

Array constants are a component of array formulas. Matriisivakioita voi luoda määrittämällä kohdeluettelo ja lisäämällä luettelon ympärille aaltosulkeet ({ }), esimerkiksi

= {1, 2, 3, 4, 5} tai = {"tammikuu", "helmikuu", "maaliskuu"}

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, voit rajata kunkin rivin kohteet pilkuilla ja rajata kunkin rivin puoli pisteillä.

Seuraavien toimien avulla voit harjoitella vaaka- ja pystysuuntaisten sekä kaksiulotteisten vakioiden luomista Näkyviin tulee esimerkkejä sekvenssi-funktion avulla, joka luo matriisi vakiot automaattisesti, sekä manuaalisesti kirjoitetut matriisi vakiot.

  • Vaakasuuntaisen vakion luominen

    Käytä edellisessä kohdassa käytettyä työkirjaa tai avaa uusi työkirja. Valitse mikä tahansa tyhjä solu ja kirjoita = järjestys (1; 5). JÄRJESTYS-funktio muodostaa yhden rivin 5 sarakkeen matriisina samalla tavalla kuin = {1, 2, 3, 4, 5}. Näyttöön tulee seuraava tulos:

    Luo vaakasuuntainen matriisi vakio, jossa on = järjestys (1; 5) tai = {1, 2, 3, 4, 5}

  • Pystysuuntaisen vakion luominen

    Valitse mikä tahansa tyhjä solu, jonka alapuolella on huone, ja kirjoita = järjestys (5)tai = {1; 2; 3; 4; 5}. Näyttöön tulee seuraava tulos:

    Luo pystysuuntainen matriisi vakio, jossa on = järjestys (5) tai = {1; 2; 3; 4; 5}

  • Kaksiulotteisen vakion luominen

    Valitse mikä tahansa tyhjä solu, jossa on tilaa oikealle ja alapuolelle, ja kirjoita = järjestys (3; 4). Näet seuraavat tulokset:

    Luo kolme riviä neljän sarakkeen matriisi vakiolla = järjestys (3; 4)

    Voit myös kirjoittaa: tai = {1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12}, mutta haluat kiinnittää huomiota siihen, mihin laitat puoli pistettä ja pilkkuja.

    Kuten huomaat, järjestys-vaihto ehto tarjoaa merkittäviä etuja, jotka liittyvät matriisi vakion arvojen manuaaliseen syöttämisessä. Ennen kaikkea se säästää aikaa, mutta se voi myös vähentää manuaalisten syöttö virheiden virheitä. Se on myös helppolukuisempaa, varsinkin kun puoli pisteillä voi olla vaikea erottaa toisistaan pilkku erottimia.

Tässä on esimerkki, jossa käytetään matriisi vakioita suuremman kaavan osana. Siirry esimerkki työkirjassa kaava laskenta taulukon vakion kohdalle tai luo uusi laskenta taulukko.

Soluun D9, tulimme = järjestys (1, 5, 3, 1), mutta voit myös kirjoittaa 3, 4, 5, 6 ja 7 soluihin A9: H9. Tietyn numeron valinnassa ei ole mitään erityistä, vaan valitsimme vain jotain muuta kuin 1-5 eriyttämistä varten.

Kirjoita soluun E11 = summa (D9: H9 * järjestys (1; 5))tai = summa (D9: H9 * {1; 2; 3; 4; 5}). Kaavat palauttavat 85.

Käytä kaavoissa matriisi vakioita. Tässä esimerkissä käytimme = summa (D9: H (* järjestys (1; 5))

JÄRJESTYS-funktio muodostaa matriisin vakion {1, 2, 3, 4, 5}. Koska Excel suorittaa operaatioita sulkeiden sisällä olevissa lausekkeissa ensin, seuraavat kaksi osaa ovat D9: H9 ja kertolasku operaattorin (*) solujen arvoja. Tässä vaiheessa kaava kertoo tallennetun matriisin arvot vakion vastaavilla arvoilla. Tämä vaihe vastaa kaavaa:

= Summa (D9 * 1; E9 * 2; F9 * 3; G9 * 4; H9 * 5)tai = summa (3 * 1; 4 * 2; 5 * 3; 6 * 4; 7 * 5)

Lopuksi summa-funktio laskee arvot yhteen ja palauttaa arvon 85.

Jos haluat välttää tallennetun matriisin käyttämisen ja pitää toiminnon kokonaan muistissa, voit vaihtaa sen toisella matriisi vakiolla:

= Summa (järjestys (1; 5; 3; 1) * järjestys (1; 5))tai = summa ({3, 4, 5, 6, 7} * {1, 2, 3, 4, 5})

Matriisi vakioissa käytettävät ominaisuudet

  • Matriisi vakiot voivat sisältää lukuja, tekstiä, totuus arvoja (kuten tosi ja EPÄTOSI) sekä virhe arvoja, kuten #N/A. Voit käyttää lukuja kokonaislukumuodossa, desimaali lukuina ja tieteellisissä muodoissa. Jos sisällytät tekstiä, sinun on ympäröidä se lainaus merkeillä ("teksti").

  • 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ää matriisi vakioita 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:

Valitse kaavat _T_ määritetyt nimet _Gt_ Määritä nimi. Kirjoita nimi -ruutuun Neljännes1. Kirjoita Viittaa-ruutuun seuraava vakio (muista lisätä aaltosulkeet):

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

Valinta ikkunan pitäisi nyt näyttää tältä:

Nimetyn matriisi vakion lisääminen kaavoista _t_ määritetyt nimet > Name emäntä _Ng_ uusi

Valitse OKja valitse sitten mikä tahansa rivi, jossa on kolme tyhjää solua ja kirjoita = Neljännes1.

Näyttöön tulee seuraava tulos:

Käytä kaavassa nimettyä matriisi vakiota, kuten = Neljännes1, jossa Neljännes1 on määritetty muodossa = {"tammikuu", "helmikuu", "maaliskuu"}

Jos haluat, että tulokset leviävät pystysuunnassa vaakasuuntaiseksi eikä vaaka suunnassa, voit käyttää =Transponoi(Neljännes1).

Jos haluat näyttää 12 kuukauden luettelon, kuten voit käyttää tilin päätöksen luomisessa, voit perustaa kuluvan vuoden ja järjestys-funktion. Siisti juttu tästä funktiosta on se, että vaikka vain kuukausi on näkyvissä, sen takana on kelvollinen päivä määrä, jota voit käyttää muissa lasku toimituksissa. Nämä esimerkit ovat esimerkki työkirjan nimetyssä matriisi vakion ja pikanäytteen tieto joukko -laskenta taulukossa.

= TEKSTI (päiväys (vuosi (tänään ()), järjestys (1, 12), 1), "MMM")

Voit luoda 12 kuukauden dynaamisen luettelon yhdistämällä teksti-, päivä määrä-, vuosi-, tänään-ja järjestys-funktioita.

Tämä käyttää Date-funktiota päivä määrän luomiseen kuluvan vuoden perusteella, järjestys luo matriisi vakion väliltä 1 – 12 tammi kuusta joulu kuuhun, jolloin teksti-funktio muuntaa näyttö muodoksi "MMM" (tammikuu, helmikuu, maaliskuu, jne.). Jos haluat näyttää koko kuukauden nimen, esimerkiksi tammi kuun, käytä "mmmm"-nimeä.

Kun käytät nimettyä vakiota matriisi kaavana, muista kirjoittaa yhtäläisyys merkki, kuten = Neljännes1, ei vain Neljännes1. Jos et lisää yhtäläisyysmerkkiä, Excel tulkitsee matriisin tekstimerkkijonoksi eikä kaava toimi odotetulla tavalla. Muista myös, että voit käyttää funktioiden, tekstin ja lukujen yhdistelmiä. Kaikki riippuu siitä, miten luova haluat saada.

Seuraavissa esimerkeissä esitellään muutamia matriisivakioiden käyttötapoja matriisikaavoissa. Joissakin esimerkeissä rivit muunnetaan sarakkeisiin ja päinvastoin. Transponoi-funktion avulla.

  • Useita kutakin kohdetta matriisissa

    ENTER = järjestys (1; 12) * 2tai = {1; 2; 3; 4; 5; 6; 7; 8; 9, 10, 11, 12} * 2

    Voit jakaa sen myös (/), lisätä käyttämällä (+) ja vähentää kanssa (-).

  • Matriisin kohteiden neliöjuuri

    ENTER = järjestys (1; 12) ^ 2tai = {1; 2; 3; 4; 5; 6; 7; 8; 9; 10, 11, 12} ^ 2

  • Matriisin kohtien neliö juuren etsiminen

    ENTER =SQRT(järjestys (1; 12) ^ 2)tai = SQRT ({1; 2; 3; 4; 5; 6; 7; 8; 9; 10, 11, 12} ^ 2)

  • Yksiulotteisen rivin transponoiminen

    ENTER = Transponoi (järjestys (1; 5))tai = Transponoi ({1; 2; 3; 4; 5})

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

  • Yksiulotteisen sarakkeen transponoiminen

    ENTER = Transponoi (järjestys (5; 1))tai = Transponoi ({1; 2; 3; 4; 5})

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

  • Kaksiulotteisen vakion transponoiminen

    ENTER = Transponoi (järjestys (3; 4))tai = 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.

  • Matriisin luominen olemassa olevista arvoista

    Seuraavassa esimerkissä selitetään, miten voit luoda uuden matriisin olemassa olevasta matriisista matriisi kaavojen avulla.

    ENTER = järjestys (3, 6, 10, 10)tai = {10, 20, 30, 40, 50, 60; 70; 80; 90100110120; 130140150160170180}

    Kirjoita {(avaava aalto Sulje), ennen kuin kirjoitat 10, ja} (Sulje aalto Sulje), kun olet luonut 180, koska luot luku matriisin.

    Kirjoita sitten = D9 #tai = D9: i11 tyhjään soluun. Näkyviin tulee 3 x 6 solu matriisi, jossa on samat arvot, jotka näkyvät D9: D11. #-Merkin nimi on vuotanut Range-operaattori, ja se on Excelin tapa viitata koko matriisi alueelle sen sijaan, että kirjoittaisi sen ulos.

    Olemassa olevaan matriisiin viittaminen käyttämällä vuotanut Range-operaattoria (#)

  • Matriisivakion luominen olemassa olevista arvoista

    Voit ottaa valuvan matriisi kaavan tulokset ja muuntaa ne sen osina. Valitse solun D9 ja siirry sitten Muokkaus tilaan painamalla F2 -näppäintä. Muunna seuraavaksi solu viittaukset arvoiksi painamalla F9 -näppäintä, minkä jälkeen Excel muuntaa matriisi vakioksi. Kun painat ENTER-näppäintä, kaava, = D9 #, pitäisi nyt olla = {10; 20; 30; 40; 50; 60; 70; 80; 90}.

  • Solualueen merkkimäärän laskeminen

    Seuraavassa esimerkissä näytetään, miten voit laskee solu alueella olevien merkkien määrän. Tämä sisältää väli lyöntejä.

    Alueella olevien merkkien kokonaismäärän ja muiden teksti merkki jonojen käsittelemiseen tarkoitettujen matriisien määrän laskeminen

    = SUMMA (LEN (C9: C13))

    Tässä tapa uksessa pituus- funktio palauttaa kunkin solu alueella olevan teksti merkki jonon pituuden. SUMMA-funktio lisää arvot yhteen ja näyttää tuloksen (66). Jos haluat saada keskimääräisen määrän merkkejä, voit käyttää:

    = KESKI ARVO (PITUUS (C9: C13))

  • Pisimmän solun sisältö alueella C9: C13

    = INDEX (C9: C13, VASTINE (MAKS (C9: C13)), LEN (C9: C13); 0); 1)

    This formula works only when a data range contains a single column of cells.

    Tutkitaanpa kaavaa hieman tarkemmin sisimmäisistä osista alkaen. Pituus- funktio palauttaa kaikkien kohteiden pituuden D2: D6. Maks-funktio laskee niiden kohteiden suurimman arvon, jotka vastaavat solussa D3 olevaa pisintä teksti merkki jonoa.

    Tässä vaiheessa asia muuttuu hieman monimutkaisemmaksi. Vastine-funktio laskee pisimmän teksti merkki jonon sisältävän solun siirtymän (suhteellisen sijainnin). 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 (C9: C13)

    ja merkkijono sijaitsee tässä matriisissa:

    PITUUS (C9: C13)

    Tässä tapa uksessa vastine tyyppi-argumentti on 0. Vastine tyyppi voi olla 1, 0 tai-1-arvo.

    • 1-palauttaa suurimman arvon, joka on pienempi tai yhtä suuri kuin haku Val.

    • 0-palauttaa ensimmäisen arvon, joka on täsmälleen sama kuin haku arvo

    • -1-palauttaa pienimmän arvon, joka on suurempi tai yhtä suuri kuin määritetty haku arvo.

    • Jos et määritä vastinetyyppiä, Excel käyttää oletusarvoa 1.

    Lopuksi indeksi- funktio suorittaa seuraavat argumentit: matriisi sekä rivi-ja sarake numerot kyseisessä matriisissa. Solu alueella C9: C13 tarjoaa matriisin, vastine-funktio määrittää solun osoitteen ja viimeisen argumentin (1) mukaan arvo on perä isin matriisin ensimmäisestä sarakkeesta.

    Jos haluat saada pienimmän teksti merkki jonon sisällön, korvaat yllä olevassa esimerkissä enintään min.

  • Alueen n:n pienimmän arvon etsiminen

    Tässä esimerkissä näytetään, miten voit etsiä solu alueelta kolme pienintä arvoa, joiden soluissa B9: B18has luotu malli tietojen matriisi: = int (randarray(10; 1) * 100). Huomaa, että RANDARRAY on epävakaa funktio, joten saat uuden Satunnaislukujen sarjan aina, kun Excel laskee.

    Excelin matriisi kaava, joka etsii N:nnen pienimmän arvon: = pieni (B9 #, järjestys (D9))

    ENTER = pieni (B9 #, järjestys (D9), = pieni (B9: mallin b18; {1; 2; 3})

    Tämä kaava käyttää matriisi vakiota, jos haluat laskea Small-funktion kolme kertaan ja palauttaa pienimmät kolme jäsentä matriisissa, jotka sisältyvät soluihin B9: mallin b18, jossa 3 on muuttujan arvo solussa D9. Jos haluat etsiä lisää arvoja, voit suurentaa järjestys-funktion arvoa tai lisätä lisää argumentteja vakioon. Voit käyttää tämän kaavan kanssa myös muita funktioita, kuten SUMMA- tai KESKIARVO-funktioita. Esimerkki:

    = SUMMA (PIENI (B9 #; JÄRJESTYS (D9))

    = KESKI ARVO (PIENI (B9 #; JÄRJESTYS (D9))

  • Alueen n:n suurimman arvon etsiminen

    Jos haluat löytää alueelta suurimmat arvot, voit vaihtaa SMALL-funktion suurella funktiolla. Seuraavassa esimerkissä käytetään lisäksi RIVI- ja EPÄSUORA-funktioita.

    ENTER = suuri (B9 #, rivi (epäsuora ("1:3")))tai = suuri (B9: mallin b18, 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ä ja kirjoita:

    =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 muuttaa rivi viittauksia, ja kaava luo nyt kokonaislukuja väliltä 2 – 11. Voit korjata ongelman lisäämällä kaavaan EPÄSUORA-funktion:

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

    EPÄSUORA-funktio käyttää teksti merkki jonoja argumentteina (minkä vuoksi alueen 1:10 ympärillä on lainaus merkit). Excel ei muuta tekstiarvoja, kun lisäät rivejä tai siirrät matriisikaavaa. Funktion ansiosta RIVI-funktio luo aina matriisin ainoastaan haluamistasi kokonaisluvuista. Voit yhtä helposti käyttää JÄRJESTYSTÄ:

    = JÄRJESTYS (10)

    Tarkastellaan aiemmin käytettyä kaavaa – = suuri (B9 #, rivi (epäsuora ("1:3"))) – alkaen sisemmät sulkeet ja ulospäin: epäsuora-funktio palauttaa teksti arvojen sarjan, tässä tapa uksessa arvot 1 – 3. RIVI-funktio puolestaan luo kolmen solun sarake matriisin. SUURI-funktio käyttää solu alueella B9: mallin b18 arvoja, ja se lasketaan kolme kertaa kerran kutakin rivi-funktion palauttamaa viittausta kohden. If you want to find more values, you add a greater cell range to the INDIRECT function. Kun käytät pieniä esimerkkejä, voit käyttää tätä kaavaa myös muiden funktioiden, kuten summa ja Keski arvo, kanssa.

  • Virhearvoja sisältävän alueen yhteenlaskeminen

    Excelin summa-funktio ei toimi, kun yrität laskea yhteen sen arvon, jossa on virhe arvo, kuten #VALUE! tai #N/A. Tässä esimerkissä näytetään, miten voit laskea yhteen arvot alueella, joka sisältää virheitä:

    Käytä matriiseja virheiden käsittelyyn. Esimerkiksi = summa (jos (ONVIRHE (tiedot), "", tiedot) laskee alueella nimetyt tiedot, vaikka se sisältää virheitä, kuten #VALUE! tai #NA!.

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

    Kaava luo uuden taulukon, joka sisältää alkuperäiset arvot lukuun ottamatta virhearvoja. Starting from the inner functions and working outward, the ISERROR function searches the cell range (Data) for errors. The IF function returns a specific value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE. 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ämä esimerkki on kuin edellinen kaava, mutta se palauttaa niiden virhe arvojen määrän, jotka on nimetty tietojen suodattamisen sijaan.

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

Saatat joutua laskemaan arvot yhteen ehtojen perusteella.

Matriisien avulla voit laskea tiettyjen ehtojen perusteella. = SUMMA (jos (Sales>0; myynti)) laskee yhteen kaikki arvot, joiden arvo on suurempi kuin 0, myynti-alueella.

Esimerkiksi Tämä matriisi kaava laskee yhteen luvut, jotka ovat vain positiivisia kokonaislukuja myynti-nimisen alueella, joka edustaa soluja E9: E24 yllä olevassa esimerkissä:

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

Jos-funktio luo positiivisten ja väärien 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. Matriisi kaava laskee esimerkiksi arvot, joiden arvo on suurempi kuin 0 ja pienempi kuin 2500:

= SUMMA ((Sales>0) * (Sales<2500) * (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, joiden arvo on suurempi kuin 0 tai pienempi kuin 2500:

= SUMMA (jos ((Sales>0) + (Sales<2500); myynti))

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. You can work around the problem by using the logic shown in the previous formula. Toisin sanoen suoritat matemaattisia lasku toimituksia, kuten yhteenlaskua tai kertolaskua, jotka vastaavat arvoja tai-ja-ehtoja.

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.

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. Jos haluat käyttää tätä kaavaa, solu alueiden on oltava samankokoisia ja samassa dimensiossa. Jos esimerkiksi MyData-arvo on kolme riviä viisi saraketta, oman datan on sisällettävä myös kolme riviä viidellä sarakkeilla:

=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 * (MyData<>YourData 3))

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

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

Esimerkki työkirjasta on samankaltaisia esimerkkejä DataSet-laskenta taulukon välisistä eroista .

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 taulukko ja liitä se tyhjään laskenta taulukkoon soluun a1.

Myynti Henkilö

Autossa Type (tyyppi )

Number (numero ) Myyty

Unit (yksikkö ) Hinta

Summa 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 kokonaismyynnin ja Sedan-arvon, valitse solut E2: E11, kirjoita kaava = C2: C11 * D2: D11ja paina sitten näppäin yhdistelmää CTRL + VAIHTO + ENTER.

  2. Jos haluat nähdä kaikkien myyntien kokonaissumman, valitse solu F11, kirjoita kaava = summa (C2: C11 * D2: D11)ja paina sitten näppäin yhdistelmää CTRL + VAIHTO + ENTER.

Kun painat Ctrl + Vaihto + Enter-näppäin yhdistelmää, Excel ympäröi kaavan aalto sulkeilla ({}) ja lisää kaavan esiintymän kunkin valitun solu välin 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 D13 seuraava kaava ja paina näppäin yhdistelmää CTRL + VAIHTO + ENTER:

=SUMMA(C2:C11*D2:D11)

Tässä tapa uksessa Excel kertoo matriisin arvot (solu alueella C2 – D11) ja laskee sitten summat yhteen summa-funktiolla. 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ä solun D13 yksisoluinen kaava on täysin riippumaton usean solun kaavasta (kaava solussa E2 – E11). Tämä joustavuus on yksi matriisikaavojen käytön eduista. Voit muuttaa sarakkeen E kaavoja tai poistaa sarakkeen kokonaan vaikuttamatta kaavaan D13.

Muita matriisikaavojen etuja ovat:

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

  • Safety    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. Lisä turva toimenpiteenä sinun on vahvistettava kaikki kaavan muutokset painamalla näppäin yhdistelmää CTRL + VAIHTO + ENTER .

  • Smaller file sizes    You can often use a single array formula instead of several intermediate formulas. Esimerkiksi työkirjassa käytetään yhtä matriisi kaavaa sarakkeen E tulosten laskemiseen. Jos käytit vakio kaavoja (kuten = C2 * D2, C3 * D3, C4 * D4...), olet käyttänyt 11 eri kaavaa samojen tulosten laskemiseen.

In general, array formulas use standard formula syntax. Kaikki alkaa yhtäläisyys merkillä (=), ja voit käyttää useimpia matriisi kaavoissa olevia valmiita Excel-funktioita. Tärkein ero on siinä, että kun käytät matriisi kaavaa, voit kirjoittaa kaavan painamalla näppäin yhdistelmää CTRL + VAIHTO + ENTER . Kun teet näin, Excel ympäröi matriisi kaavan aalto sulkeilla – Jos kirjoitat aalto sulkeet manuaalisesti, kaava muunnetaan teksti merkki jonoksi, eikä se toimi.

Matriisi funktiot voivat olla tehokas keino monimutkaisten kaavojen luomiseen. 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).

Tärkeää: Paina näppäin yhdistelmää CTRL + VAIHTO + ENTER aina, kun matriisi kaava on kirjoitettava. 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 matriisi kaavan, valitse koko kaava väli (esimerkiksi E2: E11) ja paina sitten Delete-näppäintä.

  • Et voi lisätä tyhjiä soluja tai poistaa soluja monisoluisen matriisi kaavasta.

At times, you may need to expand an array formula. Valitse aiemmin luodun matriisi välin ensimmäinen solu ja jatka, kunnes olet valinnut koko sen välin, johon haluat laajentaa kaavan. Voit muokata kaavaa painamalla F2 -näppäintä ja vahvistaa kaavan, kun olet säätänyt kaava välin, painamalla näppäin YHDISTELMÄÄ CTRL + VAIHTO + ENTER . Avain on valita koko solu alueelta alkaen matriisin vasemmassa yläkulmassa olevasta solusta. Vasemmassa yläkulmassa oleva solu on se, jota muokataan.

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

  • Saatat joskus unohtaa painaa näppäin yhdistelmää 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ä kaavoja. Käytännössä matriisi kaavoja ei yleensä selitetä laskenta taulukossa. Jos muut käyttäjät haluavat muokata työkirjojasi, sinun on joko vältettävä matriisi kaavoja tai varmistettava, että kyseiset henkilöt tietävät kaikista matriisi kaavoista ja miten he voivat muuttaa niitä, jos ne ovat tarpeen.

  • 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ällä hetkellä tiedät, että sinun on painettava näppäin yhdistelmää CTRL + VAIHTO + ENTER , kun luot matriisi kaavoja. Koska matriisivakiot ovat matriisikaavojen osia, vakioiden ympärille lisätään aaltosulkeet manuaalisesti kirjoittamalla. Sen jälkeen voit kirjoittaa koko kaavan painamalla näppäin yhdistelmää CTRL + VAIHTO + ENTER .

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

Tässä on matriisi yhdellä rivillä: {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ä laskenta taulukossa solut a1 – E1.

  2. Kirjoita kaava riville seuraava kaava ja paina näppäin yhdistelmää CTRL + VAIHTO + ENTER:

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

    Tässä tapa uksessa sinun on kirjoitettava avaava ja sulkeva aalto sulkeet ({}), jolloin Excel lisää toisen sarjan puolestasi.

    Näet seuraavat tulokset.

    Matriisikaavan vaakasuuntainen matriisivakio

Pystysuuntaisen vakion luominen

  1. Valitse työkirjassa viiden solun sarake.

  2. Kirjoita kaava riville seuraava kaava ja paina näppäin yhdistelmää 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 kaava riville seuraava kaava ja paina näppäin yhdistelmää 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äin yhdistelmää 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.

Ä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, että kun olet lisännyt vakion matriisi kaavaan, voit kirjoittaa kaavan painamalla näppäin yhdistelmää CTRL + VAIHTO + ENTER .

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

Jos haluat kokeilla tätä, kopioi funktio, valitse työkirjasta tyhjä solu, liitä kaava Kaava riville ja paina näppäin yhdistelmää 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 paras keino käyttää matriisi vakioita 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 -valinta ikkuna.

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

Seuraavat ongelmat saattavat estää matriisivakioiden toimimisen:

  • Some elements might not be separated with the proper character. Jos jätät pilkun tai puoli pisteen pois tai jos laitat sen väärässä paikassa, matriisi vakiota ei ehkä luoda oikein tai näkyviin voi tulla varoitus sanoma.

  • 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. Joissakin esimerkeissä rivit muunnetaan sarakkeisiin ja päinvastoin. Transponoi-funktion avulla.

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äin yhdistelmää 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 matriisi kaava ja paina näppäin yhdistelmää 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äin yhdistelmää 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äin yhdistelmää 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äin yhdistelmää 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äin yhdistelmää CTRL + VAIHTO + ENTER, joka lisää tämän luku joukon solu alueelle C8: E10 matriisi kaavan 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 kaava riville seuraava kaava ja paina näppäin yhdistelmää CTRL + VAIHTO + ENTER:

    =C8:E10

    3x3-matriisi koostuu soluista C1 – E3 ja samoista arvoista, jotka näkyvät C8 – E10.

Matriisivakion luominen olemassa olevista arvoista

  1. Kun solut C1: C3 on valittuna, siirry muokkaus tilaan painamalla F2 -näppäintä. 

  2. Muunna solu viittaukset arvoiksi painamalla F9 -näppäintä. Excel converts the values into an array constant. Kaavan pitäisi nyt olla = {10; 20; 30; 40; 50; 60; 70; 80; 90}.

  3. Paina näppäin yhdistelmää CTRL + VAIHTO + ENTER , jos haluat määrittää matriisi vakion matriisi kaavana.

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 näppäin yhdistelmää CTRL + VAIHTO + ENTER , jos haluat nähdä merkkien kokonaismäärän solussa A2: A6 (66).

  3. Valitse solu A10 ja paina sitten näppäin yhdistelmää CTRL + VAIHTO + ENTER , jos haluat nähdä pisimmän solun A2: A6 (solu a3).

Solussa A8 käytetään seuraavaa kaavaa, joka laskee yhteen 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 lisää 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 satunnaislukuja soluihin a1: A11.

  2. Valitse solut C1 – C3. This set of cells will hold the results returned by the array formula.

  3. Kirjoita seuraava kaava ja paina näppäin yhdistelmää CTRL + VAIHTO + ENTER:

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

Tämä kaava käyttää matriisi vakiota pienen funktion arviointiin kolme aikaa ja palauttaa pienimmän (1), toiseksi pienimmän (2) ja kolmanneksi pienimmän (3) jäsenen soluihin a1: A10, jos haluat etsiä lisää arvoja, lisää argumentteja jatkuvasti. 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})

= KESKI ARVO (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 kaava riville Tämä kaava ja paina näppäin yhdistelmää CTRL + VAIHTO + ENTER:

    =LARGE(A1:A10,ROW(INDIRECT("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. Voit esimerkiksi valita tyhjän sarakkeen, joka on 10 solua harjoitus työkirjassa, kirjoittamalla tämän matriisi kaavan ja painamalla näppäin yhdistelmää 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.

Katsotaan, mitä kaavaa käytit aiemmin – = suuri (A5: A14, rivi (epäsuora ("1:3"))) – alkaen sisempien sulkeiden ja ulospäin: epäsuora -funktio palauttaa teksti arvo sarjan, tässä tapa uksessa arvot 1 – 3. The ROW function in turn generates a three-cell columnar array. Suuri -funktio käyttää solu alueella A5: A14 olevia arvoja, ja se lasketaan kolme kertaa, kerran kunkin rivi -funktion palauttaman viitta uksen kohdalla. 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 aiemmilla esimerkeillä, voit käyttää tätä kaavaa muiden funktioiden, kuten summa ja Keski arvo, kanssa.

Solualueen pisimmän tekstimerkkijonon etsiminen

Siirry takaisin aikaisemman teksti merkki jonon esimerkkiin, kirjoita seuraava kaava tyhjään soluun ja paina näppäin yhdistelmää CTRL + VAIHTO + ENTER:

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

Teksti "joukko soluja, jotka" tulee näkyviin.

Tutkitaanpa kaavaa hieman tarkemmin sisimmäisistä osista alkaen. Pituus -funktio palauttaa kaikkien solu alueella a2: A6 olevien kohteiden pituuden. Maks -funktio laskee niiden kohteiden suurimman arvon, jotka vastaavat solussa a3 olevaa pisintä teksti merkki jonoa.

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:

(ENINTÄÄN (PITUUS (A2: A6))

ja merkkijono sijaitsee tässä matriisissa:

PITUUS (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. Solu alueella a2: A6 on matriisi, vastine -funktio antaa solun osoitteen ja viimeinen argumentti (1) määrittää, että arvo on 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. Jos haluat käyttää tätä kaavaa, solu alueiden on oltava samankokoisia ja samassa ulottuvuudessa (Jos esimerkiksi MyData on kolmen rivin alue viiden sarakkeen verran, Omatietojen on oltava myös kolme riviä viiden sarakkeen verran):

=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 * (MyData<>YourData 3))

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

Kuittaus

Tämän artikkelin osat perustuvat Colin Wilcoxin kirjoittamien Excel Power User-sarakkeiden sarjaan, ja ne on mukautettu Excel 2002-kaavojen lukujen 14 ja 15 mukaan, joka on entisen Excel MVP-asia kirjan kirjoittanut John Walkenbach.

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.

Katso myös

Dynaamiset matriisit ja levittyneiden matriisien erityispiirteet

Dynaamiset matriisi kaavat vs. Vanhat TÄSMÄHAKUKONEEN matriisi kaavat

SUODATA-funktio

SATUNN.MATRIISI-funktio

JONO-funktio

YKSITTÄINEN-funktio

LAJITTELE-funktio

LAJITTELE.ARVOJEN.PERUSTEELLA-funktio

AINUTKERTAISET.ARVOT-funktio

#SPILL! -virheet Excelissä

Yleistä kaavoista

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

×