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.

Matriisikaavan on kaava, joka voi suorittaa laskutoimituksia useiden vähintään yhden matriisin kohteiden. Ota huomioon matriisin rivin tai sarakkeen arvojen tai yhdistelmän rivien ja sarakkeiden arvoja. Matriisikaavojen voi palauttaa useita tuloksia tai yhden tuloksen.

Syyskuussa 2018 päivitys Office 365:n alkaen kaavan, joka palauttaa useita tuloksia automaattisesti spill ne alas tai vaakasuunnassa kyselyjä muiden käyttäjien solut. Tämä toiminnan muutos mukana myös useita uuden dynaaminen matriisi funktioita. Dynaaminen Matriisikaavat henkilöt käyttävät aiemmin funktioita tai matriisin-funktioita vain tulisiko syötteen yhdeksi soluksi sitten vahvistettu painamalla Enter. Aiemman, aiempien versioiden Matriisikaavat edellyttävät ensin koko tulostusalueen valitsemalla ja sitten vahvistaminen kaavan ja Ctrl + Vaihto + Enter. Ne on kutsutaan yleisesti nimellä CSE -kaavat.

Matriisikaavojen avulla voit suorittaa seuraavia monimutkaisia tehtäviä:

  • Voit luoda nopeasti otoksen tietojoukkoja.

  • Voit laskea solualueen sisältämien merkkien määrän.

  • Lukujen laskeminen yhteen summa ainoat, jotka täyttävät tietyt ehdot, esimerkiksi alueen alimmat arvot tai ylä- ja alarajan väliset luvut desimaaleja.

  • Arvoalueen joka n: nnen arvon yhteenlaskeminen.

Seuraavat esimerkit esitellään, miten voit luoda monisoluisia ja yksisoluisia matriisikaavoja. Mahdollisuuksien mukaan että olet kirjoittanut esimerkkejä osittain dynaaminen matriisi funktioita sekä olemassa olevat Matriisikaavat syötetään dynaaminen sekä vanhojen matriiseja.

Lataa Microsoftin esimerkkejä

Lataa kaikki matriisin kaavan tämän artikkelin esimerkkejä Esimerkki sisältävää työkirjaa.

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

    Monisoluisen matriisikaavan funktion soluun H10 = F10:F19 * Yksikköhinta myymien G10:G19 autojen määrän laskeminen

  • Seuraavassa on olet laskeminen coupe-ja sedan-mallien kunkin myyjän kokonaismyynti kirjoittamalla = F19:F19 * G10:G19 soluun H10.

    Kun painat Enter, näet tulokset spill solujen H10:H19 alaspäin. Huomaa, että spill alueen näkyy korostettuna reunan kanssa, kun valitset minkä tahansa solun spill alueen sisällä. Voit myös havaita H10:H19 solujen kaavat näkyvät harmaina. Ne ovat käytettävissä vain käyttöä, joten jos haluat muuttaa kaavan, sinun on valittava soluun H10, johon perustyyli kaava sijaitsee.

  • Yksisoluisen matriisikaavan

    Yksisoluisen matriisikaavan loppusummaan =SUM(F10:F19*G10:G19) kanssa

    Työkirjan soluun H20 Kirjoita tai kopioi ja liitä =SUM(F10:F19*G10:G19)ja paina sitten Enter-näppäintä.

    Tässä tapauksessa Excel laskee matriisin (solualue F10 – G19) arvoa ja laskemista yhteen summa-funktion avulla. Tulos on Loppusumma 1,590,000 myynti.

    Tässä esimerkissä näytetään, miten tehokas tällaista kaava voi olla. Oletetaan, että sinulla on 1 000 riviä. Laskee yhteen tai osan siitä tietoja luomalla matriisikaavan yhteen soluun kaavan vetämisen – 1 000 rivien sijaan. Huomaa myös, solun H20 Yksisoluisen kaava on täysin erillinen usean solun kaava (soluissa H10 – H19 kaava). Tämä on toinen etu Matriisikaavat – joustavuutta. Voit muuttaa muiden kaavojen sarakkeessa H vaikuttamatta kaavassa H20. Se voi olla myös hyvä on itsenäinen summat, koska se tarkistaa tulokset tarkkuudella.

  • Dynaaminen Matriisikaavojen etuja:

    • Yhdenmukaisuuden    Jos valitset jonkin H10 soluista alaspäin, näet saman kaavan. Yhdenmukaisuuden voivat estää tarkempaa.

    • Turvallisuus    Et voi korvata monisoluisen matriisikaavan osa. Esimerkiksi napsauttamalla solua H11 ja paina Delete-näppäintä. Excel ei muuta taulukon tulos. Jos haluat muuttaa sitä, valitse vasemman yläkulman solua matriisissa tai soluun H10.

    • Pienet tiedostokoot    Voit käyttää matriisikaavalla usein useita keskitason kaavojen sijaan. Esimerkiksi auton myynnin esimerkissä käytetään yhtä matriisikaava laskee sarakkeen e tulokset Jos vakio kaavat olisi käyttänyt kuten = F10 * G10, F11 * G11, F12 * G12, jne, olet käyttänyt 11 erilaisia kaavoja saman tuloksen laskemiseen. Joka ei ole iso juttu, mutta jos haluat käyttää tuhansia rivien summa? Valitse se voi tehdä suuren eron.

    • Tehokkuutta    Matriisi funktioita voidaan tehokas tapa luoda monimutkaisia kaavoja. Matriisin kaavan =SUM(F10:F19*G10:G19) on sama kuin tämä: = SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).

    • Spilling    Dynaaminen Matriisikaavat automaattisesti spill tulostus-alueelle. Jos lähdetiedot on Excel-taulukon, dynaaminen Matriisikaavat automaattisesti kokoa, kuin lisäät tai poistat tiedot.

    • #SPILL! virhe    Dynaamisia taulukoita, jotka on otettu käyttöön #SPILL! virhe, joka ilmaisee, että tarkoitettu spill-alue on estetty jostain syystä. Kun olet ratkaissut Virt.aukon/tukk, kaavan automaattisesti spill.

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

= {lukuja 1,2,3,4,5} tai = {”tammikuu”, ”helmikuun”, ”maaliskuu”}

Jos kohteet erottaa toisistaan pilkulla, voit luoda vaakasuuntaisena matriisina (rivi). Jos kohteet erottaa toisistaan puolipisteillä, voit luoda pystysuuntaisen matriisin (sarake). Voit luoda matriisikaavan kaksiulotteinen rajaa kullakin rivillä pilkuilla kohteet ja rajaa kukin rivi toisistaan puolipisteillä.

Ohjeiden avulla voit harjoitella vaaka-, pysty- ja kaksiulotteisten vakioiden. Olemme näkyviin esimerkkejä luo automaattisesti Matriisivakioiden JÄRJESTYS-funktion avulla sekä manuaalisesti Matriisivakioiden.

  • Vaakasuuntaisen vakion luominen

    Käytä edellisessä kohdassa käytettyä työkirjaa tai Luo uusi työkirja. Valitse tyhjä solu ja kirjoita =SEQUENCE(1,5). SARJA-funktion muodostaa 1 rivi 5 sarakkeessa matriisin mukaan sama kuin = {lukuja 1,2,3,4,5}. Näet seuraavat tulokset:

    Luo vaakasuuntaisen matriisivakion =SEQUENCE(1,5) tai = {lukuja 1,2,3,4,5}

  • Pystysuuntaisen vakion luominen

    Valitse tyhjä solu, jonka huone sen alle ja kirjoita =SEQUENCE(5)tai = {1; 2; 3; 4; 5}. Näet seuraavat tulokset:

    Luo pystysuuntainen matriisivakio kanssa = SEQUENCE(5) tai = {1; 2; 3; 4; 5}

  • Kaksiulotteisen vakion luominen

    Valitse tyhjä solu, jonka huone oikealle ja sen alle ja kirjoita =SEQUENCE(3,4). Näet seuraavat tulokset:

    3 rivin luomalla 4 sarakkeen matriisivakio =SEQUENCE(3,4) kanssa

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

    Kuten näet, JÄRJESTYS-vaihtoehto on merkittäviä etuja verrattuna kirjoittaminen manuaalisesti matriisin vakion arvot. Ensisijaisesti, se tallentaa aikaa, mutta sen voi myös vähentää virheitä manuaalinen tekstistä. Se on helpompi lukea, myös silloin, erityisesti silloin, kun puolipisteillä voi olla vaikea erottaa pilkkuerottimia.

Tässä on esimerkki käyttää matriisikaavoissa vakiot isommaksi kaavan osana. Siirry vakion käyttäminen kaavassa laskentataulukkoon esimerkkitiedoston tai Luo uusi laskentataulukko.

Solussa D9 on syötetty =SEQUENCE(1,5,3,1)mutta voi myös syöttää solujen A9:H9 3, 4, 5, 6 ja 7. Ei ole mitään erityistä tietoja tietyn numeron valinnan, Microsoft valitsi vain jollakin muulla kuin eriyttäminen 1 – 5.

Kirjoita soluun E11 = summa (D9:H9*SEQUENCE(1,5)), tai = summa (D9:H9* {lukuja 1,2,3,4,5}). Kaavat palauttavat 85.

Matriisivakioiden käyttäminen kaavoissa. Tässä esimerkissä on käytetty = summa (D9:H(*SEQUENCE(1,5))

SARJA-funktion muodostaa matriisin vakion {lukuja 1,2,3,4,5} kokonaan. Koska Excel suorittaa laskutoimituksia ensin sulkeissa lausekkeita, seuraavat kaksi elementtejä, jotka on otettava toista ovat solujen arvot D9:H9 ja kerto-operaattoria (*). Tässä vaiheessa kaava kertoo tallennetun matriisin arvoa vakion vastaavien arvojen mukaan. Se on kokonaan:

=Sum(D9*1,E9*2,F9*3,G9*4,H9*5)tai =SUM(3*1,4*2,5*3,6*4,7*5)

Lopuksi summa-funktio laskee yhteen luvut ja palauttaa 85.

Voit välttää tallennetun matriisin käyttämisen ja säilyttää operaattorin muistissa, voit korvata sen toisella matriisivakiolla:

=Sum(Sequence(1,5,3,1)*Sequence(1,5))tai =SUM({3,4,5,6,7}*{1,2,3,4,5})

Elementit, joita voit käyttää matriisivakioita

  • Matriisivakiot voivat sisältää lukuja, tekstiä, totuusarvoja (kuten TOSI ja EPÄTOSI) ja virhearvot, kuten #puuttuu!. Voit käyttää lukujen kokonaisluku, desimaali ja tieteellisen muodossa. Jos lisäät tekstiä, sinun on ympärillä lainausmerkkejä (”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.

Matriisivakioiden käyttäminen paras tapa on nimeät ne. Vakioihin voi olla helpompi käyttää, ja ne voidaan piilottaa taulukon kaavojen muilta monimutkaisuutta. Matriisivakion nimeäminen ja käyttää kaavassa, toimi seuraavasti:

Siirry kaavojen > määritettyjä nimiä > Määritä nimi. Kirjoita nimi -ruutuun Neljännes1. Kirjoita viittaa -ruutuun seuraava vakio (muista lisätä aaltosulkeet):

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

Valintaikkunan pitäisi näyttää tältä:

Lisää nimetty matriisivakio kaavojen > määritetyt nimet > nimien hallinta-> uusi

Valitse OK, ja valitse Valitse minkä tahansa rivi, jonka kolmen tyhjän solun ja kirjoita = Neljännes1.

Näet seuraavat tulokset:

Nimetty matriisivakion käyttäminen kaavassa, kuten = Neljännes1, jossa on määritetty Neljännes1 = {”tammikuu”, ”helmikuu”, ”maaliskuu”}

Jos haluat tulokset spill pystysuunnassa sijaan vaakasuunnassa, voit käyttää =Transponoi(Neljännes1).

Jos haluat näyttää luettelon 12 kuukautta, kuten käytöstä luotaessa tilinpäätös, voit luoda yhden kuluvan vuoden JÄRJESTYS-toiminnon käytöstä. Lisätietoja tämän funktion siististi kohdetta on se, että vaikka näkyy vain kuukausi, kelvollisia päivämääriä takana, jota voit käyttää muita laskutoimituksia. Löydät esimerkit työkirjan nimi on matriisivakio ja lyhyt Esimerkki tietojoukko laskentataulukoihin.

=Text(Date(Year(Today()),Sequence(1,12),1),"mmm")

TEKSTIN ja PÄIVÄMÄÄRÄN, vuosi, tänään, JÄRJESTYS-funktioiden yhdistelmää avulla voit luoda dynaamisia 12 kuukautta

Tämä DATE-funktiota käytetään luominen kuluvan vuoden perusteella, JÄRJESTYS Luo matriisivakion väliltä 1 – 12 tammikuu – joulukuu ja valitse teksti-funktio muuntaa näyttömuodon, ”mmm” (tammi-helmi; maaliskuu, jne.). Jos haluat näyttää koko kuukauden nimi, kuten tammikuu, voit käyttää ”mmmm”.

Kun käytät nimetty vakio matriisikaavana, muista syöttää yhtäläisyysmerkin, kuten = Neljännes1, ei pelkästään Neljännes1. Jos et, Excel tulkitsee matriisin tekstimerkkijonon ja kaava ei toimi odotetulla tavalla. Lopuksi Ota huomioon, että voit käyttää funktioita, tekstin ja numeroiden yhdistelmät. Kaikki riippuu siitä, miten creative, josta haluat hakea.

Seuraavissa esimerkeissä on joitakin tapoja, jossa voit laittaa Matriisivakioiden käyttäminen matriisikaavoissa. Joissakin esimerkeissä käytetään TRANSPONOI-funktio muuntaa rivien, sarakkeiden ja päinvastoin.

  • Useita matriisin kunkin kohteen

    Kirjoita = sarjan (1,12) * 2, tai = {1,2,3,4 5,6,7,8; 9,10,11,12} * 2

    Voit myös jakaa kanssa (/), lisääminen, jossa (+) ja vähentäminen kanssa (-).

  • Matriisin kohteiden neliöjuuri

    Kirjoita = sarjan (1,12) ^ 2, tai = {1,2,3,4 5,6,7,8; 9,10,11,12} ^ 2

  • Etsi matriisin neliön kohteiden neliöjuuri

    Kirjoita =neliöjuuri(SEQUENCE(1,12)^2), tai =SQRT({1,2,3,4;5,6,7,8;9,10,11,12}^2)

  • Yksiulotteisen rivin transponoiminen

    Kirjoita =TRANSPOSE(SEQUENCE(1,5))tai =TRANSPOSE({1,2,3,4,5})

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

  • Yksiulotteisen sarakkeen transponoiminen

    Kirjoita =TRANSPOSE(SEQUENCE(5,1))tai = TRANSPONOI ({1; 2; 3; 4; 5})

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

  • Kaksiulotteisen vakion transponoiminen

    Kirjoita =TRANSPOSE(SEQUENCE(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ä kerrotaan, miten voit käyttää matriisikaavoja uuden matriisin luominen olemassa olevan matriisin.

    Kirjoita =SEQUENCE(3,6,10,10)tai = {10,20,30,40,50,60 70,80,90,100,110,120; 130,140,150,160,170,180}

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

    Kirjoita seuraavaksi tyhjään soluun = D9 #tai = D9:I11 . 3 x 6 solumatriisin tulee näkyviin ja D9: D11 näet samat arvot. #-Merkki kutsutaan spilled alueoperaattori, ja se on Excelin tapa viittaava koko taulukko sen sijaan, että voit kirjoittaa sen alueen.

    Viittaa aiemmin matriisin spilled alueoperaattori (#) avulla

  • Matriisivakion luominen olemassa olevista arvoista

    Voit siirtää spilled matriisikaava tulokset ja muuntaa sen osia, jotka. Valitse solu D9 ja paina F2-näppäintä Siirry muokkaustilassa. Seuraavaksi painamalla F9-näppäintä soluviittaukset arvoja, joita Excel muuntaa matriisivakion sitten Muunna. Kun painat Enter, kaavan = D9 #, pitäisi nyt = {10,20,30 40,50,60; 70,80,90}.

  • Solualueen merkkimäärän laskeminen

    Seuraavassa esimerkissä esitetään, kuinka voit laskea solualueen merkkien määrän. Tämä sisältää välilyöntejä.

    Alueen ja muiden matriisien käyttöä tekstimerkkijonoja merkkien kokonaismäärän laskeminen

    = SUMMA (LEN(C9:C13))

    Tässä tapauksessa LEN-funktio palauttaa kunkin merkkijonon pituuden kunkin alueen soluista. Valitse summa-funktio laskee arvot yhteen ja näyttää tuloksen (66). Jos saat merkkien määrän keskiarvo, voit käyttää:

    = KESKIARVO (LEN(C9:C13))

  • Alueen C9:C13 pisimmän solun sisältö

    =INDEX(C9:C13,MATCH(MAX(LEN(C9:C13)),LEN(C9:C13),0),1)

    Tämä kaava toimii vain, kun tietojen alue sisältää vain yhden sarakkeen solut.

    Voit lähtien sisemmän elementit ja toimi ulospäin kaava tarkemmin. LEN-funktio palauttaa kohteiden pituuden solualueen D2:D6. MAKS-funktio laskee suurimman arvon välillä kohteet, joka vastaa pisimmän tekstimerkkijonon, joka on solussa D3.

    Seuraavassa on hieman monimutkaisia asioita mistä. VASTINE-funktio laskee solut, joiden sisältämät pisimmän tekstimerkkijonon Siirtymä (suhteellisen sijainnin). Saadakseen ne se vaatii kolme argumenttia: haettavan arvon, haku-matriisi ja VASTINE-tyyppi. VASTINE-funktio hakee määritettyyn haettavan arvon haku-taulukko. Tässä tapauksessa hakuarvon on pisimmän tekstimerkkijonon:

    MAX(LEN(C9:C13)

    ja merkkijono sijaitsee tässä matriisissa:

    LEN(C9:C13)

    Tässä tapauksessa tyypin vastine-argumentti on 0. Vastine-tyyppi voi olla 1 tai 0-1-arvo.

    • 1 - palauttaa suurimman arvon, joka on pienempi kuin haun val

    • 0 - palauttaa ensimmäisen arvon tarkalleen haettavan arvon

    • luku -1 - palauttaa pienimmän arvon, joka on suurempi tai yhtä suuri kuin hakuarvo määritetyn

    • Jos sama tyyppi, Excel olettaa 1.

    Lopuksi indeksi-funktio on seuraavat argumentit: matriisin ja matriisin rivin ja sarakkeen numeron. Solualue C9:C13 on taulukko, VASTINE-funktio sisältää sen solun osoite ja lopullinen (1)-argumentti määrittää, että arvo on matriisin ensimmäisestä sarakkeesta.

    Jos haluat määrittää pienimmän tekstimerkkijonon sisältöä, sinun tilalle Maks edellä olevassa esimerkissä MIN.

  • Alueen n:n pienimmän arvon etsiminen

    Tässä esimerkissä näytetään kolme pienintä arvoa etsiä solualueen, jos matriisin solut B9:B18has mallitiedot luotu: = INT (RANDARRAY(10.1) * 100). Huomaa, että RANDARRAY muuttuvat-funktio, jotta saat uudella valikoimalla satunnaisluvut aina, kun Excel laskee.

    Matriisikaavan n: nnen pienimmän arvon etsiminen Excel: =SMALL(B9#,SEQUENCE(D9))

    Kirjoita =SMALL(B9#,SEQUENCE(D9)= Pieni (B9:B18, {1; 2; 3})

    Tämä kaava käyttää matriisivakion Pieni-funktio lasketaan kolme kertaa ja palauttaa pienimmän 3 jäsenet matriisi, joka sisältyy soluihin B9:B18, missä 3 soluun D9 muuttujan arvo. Jos haluat etsiä arvoja, Suurenna JÄRJESTYS-funktion arvo tai lisätä argumentteja vakion. Voit käyttää myös muita funktioita tämän kaavan, kuten SUMMAN tai keskiarvon. Esimerkki:

    =SUM(SMALL(B9#,SEQUENCE(D9))

    =AVERAGE(SMALL(B9#,SEQUENCE(D9))

  • Alueen n:n suurimman arvon etsiminen

    Voit etsiä alueen suurimman arvon, voit korvata pieni-funktio suuri-funktio. Lisäksi seuraavassa esimerkissä rivi - ja epäsuora -funktiot.

    Kirjoita = Suuri (B9 #-rivi (epäsuora (”1:3”)))- tai = Suuri (B9:B18,ROW(INDIRECT("1:3")))

    Tässä vaiheessa se voi auttaa tietävän hieman rivi- ja epäsuora-funktiot. RIVI-funktion voit luoda peräkkäiset kokonaislukujen taulukko. Esimerkiksi tyhjän ja kirjoita:

    =ROW(1:10)

    Kaava luo 10 peräkkäisiä kokonaislukuja sisältävä sarake. Jos haluat nähdä mahdollisen ongelman, lisää rivin yläpuolelle Matriisikaavan sisältävän alueen (eli yläpuolella rivi 1). Excel määrittää rivin viittaukset ja kaava luo kokonaisluvut nyt 2 11. Voit korjata tämän ongelman, epäsuora-funktion lisääminen kaavaan:

    = RIVIN (EPÄSUORA (”1:10”))

    Epäsuora-funktio käyttää tekstimerkkijonoja argumenttien (joka on syy 1:10-alue on lainausmerkkien sisällä). Excel ei muuta tekstiarvoja, lisätä rivejä tai Siirrä muussa matriisikaavan. Tämän vuoksi rivi-funktio luo aina kokonaislukujen, jonka haluat taulukko. Voit käyttää vain yhtä helposti järjestyksessä:

    =SEQUENCE(10)

    Tarkastellaan nyt kaavaa, jota käytit aiemmin – suuri (B9 #-rivi (epäsuora (”1:3”))) = – sisempien sulkeiden lähtien ja toimi ulospäin: epäsuora-funktio palauttaa joukon tekstiarvoja, tässä tapauksessa arvot 1 – 3. RIVI-funktio luo puolestaan kolmen solun sarake-matriisi. SUURI-funktio käyttää arvot solualueen B9:B18, ja se arvioidaan kerran kullekin viitteeksi rivi-funktion palauttama kolme kertaa. Jos haluat etsiä arvoja, voit lisätä suurempi solualueen epäsuora-funktio. Lopuksi pieni esimerkkien avulla voit käyttää tämä kaava, jossa muut Funktiot, kuten summa- ja keskiarvo.

  • Virhearvoja sisältävän alueen yhteenlaskeminen

    Excelin summa-funktio ei toimi, kun yrität sisältävän alueen, jossa on virhearvo, kuten #VALUE! tai #puuttuu!. Tässä esimerkissä näytetään, miten voit laskea alueen tiedot, jotka on virheitä:

    Matriisien avulla voit käsitellä virheet. Esimerkiksi =SUM(IF(ISERROR(Data),"",Data) laskea alueen nimeltä tietoja, vaikka se sisältää virheet, kuten #VALUE! tai #NA!.

  • =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 samalla tavalla kuin edellinen, mutta se palauttaa sen sijaan, että virhearvot suodatettaisiin pois tiedot-alueen virhearvojen määrän:

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

Voit joutua muuttamaan arvojen laskeminen yhteen ehdon perusteella.

Matriisien avulla voit laskea tiettyjen ehtojen perusteella. =SUM(IF(Sales>0,Sales)) laskea suurempi kuin 0, Myynti alueen kaikki arvot.

Esimerkiksi tämä matriisikaava laskee yhteen vain positiivisen kokonaisluvun alueen myynti, joka edustaa soluja E9:E24 yllä olevassa esimerkissä:

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

Jos-funktio luo matriisin positiivinen ja EPÄTOSI-arvoja. SUMMA-funktio ohittaa olennaisesti EPÄTOSI-arvoja, koska 0 + 0 = 0. Rivien ja sarakkeiden määrä voi olla solualue, jonka voit käyttää seuraavaa kaavaa.

Voit myös laskea yhteen useamman kuin yhden ehdon täyttävät arvot. Esimerkiksi tämä matriisikaava laskee arvo on suurempi kuin 0 ja pienempi kuin 2500:

=SUM((Sales>0)*(Sales<2500)*(Sales))

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

Voit myös luoda matriisikaavoja, jotka käyttävät tyypin tai ehto. Voit esimerkiksi laskea arvoja, jotka ovat suurempia kuin 0 tai pienempi kuin 2500:

=SUM(IF((Sales>0)+(Sales<2500),Sales))

Et voi käyttää ja ja tai-funktioita Matriisikaavoissa suoraan, koska nämä funktiot palauttavat yhden tuloksen joko TOSI tai EPÄTOSI ja matriisi funktioita vaativat tietotaulukoita tulokset. Voit ratkaista ongelman käyttämällä logiikan edellisen kaavan mukaisesti. Toisin sanoen voit suorittaa matemaattisten toimintojen, kuten lisäyksen tai kerto-tai täyttävien arvojen tai ja-ehto.

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.

Tämä matriisikaava vertaa kahden solualueen Omattiedot ja Toisentiedot arvot ja palauttaa kahden eroja. Jos kahden alueen sisällön samanlaiset, kaava palauttaa arvon 0. Voit käyttää kaavaa solualueiden on oltava sama koon ja saman dimension. Esimerkiksi jos Omattiedot on solualueen 3 riviä 5 sarakkeittain, Toisentiedot on oltava myös 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.

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

Löydät samalla esimerkkejä esimerkkitiedoston tietojoukkoja erot laskentataulukossa.

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 olevassa taulukossa ja liitä se tyhjän laskentataulukon soluun A1.

Myynti Henkilö

Auton Tyyppi

Numero Myyty

Yksikkö Hinta

Yhteensä 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. Valitse solut Nähdäksesi coupe ja kunkin myyjän sedan-mallien kokonaismyynnin valitsemalla E2: E11, kirjoittamalla kaavan = C2: C11 * D2: D11, ja paina sitten Ctrl + Vaihto + Enter-näppäintä.

  2. Jos haluat nähdä loppusumman kaikkien myytyjen autojen valitsemalla solun F11, kirjoittaa kaavan =SUM(C2:C11*D2:D11)ja paina Ctrl + Vaihto + Enter.

Kun painat Ctrl + Vaihto + Enter, Excel ympäröi kaavan aaltosulkeet ({}) ja Lisää kaava-esiintymän valitun alueen kullekin solulle. Tämä tapahtuu nopeasti, joten sarakkeen E näkemä auton mistäkin kunkin myyjän myynnin kokonaismäärän. Valitse E2, valitse Valitse E3 ja E4, näet, että samassa kaavassa on mukana: {= C2: C11 * D2: D11}.

Matriisikaava laskee sarakkeen E summat

  • Yksisoluisen matriisikaavan luominen

Kirjoita työkirjan soluun D13 seuraava kaava ja paina Ctrl + Vaihto + Enter:

=SUMMA(C2:C11*D2:D11)

Tässä tapauksessa Excel laskee matriisin (solualue C2 D11) arvoa ja laskemista yhteen Summa-funktionavulla. Tulos on Loppusumma 1,590,000 myynti. Tässä esimerkissä näytetään, miten tehokas tällaista kaava voi olla. Oletetaan, että sinulla on 1 000 riviä. Laskee yhteen tai osan siitä tietoja luomalla matriisikaavan yhteen soluun kaavan vetämisen – 1 000 rivien sijaan.

Huomaa myös, että solun D13 Yksisoluisen kaava on täysin itsenäinen usean solun kaavan (soluissa E2 – E11 kaava). Tämä on toinen etu Matriisikaavat – joustavuutta. Voi muuttaa sarakkeen E kaavoja tai poistaa sarakkeen kokonaan, vaikuttamatta D13 kaava.

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 osa. Esimerkiksi napsauttamalla solua E3 ja paina Delete-näppäintä. Sinun on valittava joko koko solualue (E2 – E11) ja koko matriisin kaavan muuttaminen tai jätä matriisissa on. Kuin turvallisuuden-mittayksikön sinun on painamalla Ctrl + Vaihto + Enter Vahvista kaavaan muutoksia.

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

Matriisikaavojen sanalla vakio kaavasyntaksi. Ne kaikki alussa on yhtäläisyysmerkki (=) ja voit käyttää valmiita Excel-funktioiden useimpia matriisikaavoissa. Tärkein ero on, että käytettäessä matriisikaavan, voit painaa Ctrl + Vaihto + Enter , kirjoita kaava. Kun teet näin, Excel lisää taulukon kaavan aaltosulkeet — Jos kirjoitat aaltosulkeet manuaalisesti kaavan muunnetaan tekstimerkkijonon ja sitä voi käyttää.

Matriisi funktioita voidaan tehokas tapa luoda monimutkaisia kaavoja. Matriisin kaavan =SUM(C2:C11*D2:D11) on sama kuin tämä: =SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Tärkeää: Paina Ctrl + Vaihto + Enter , aina, kun haluat kirjoittaa matriisikaavan. Tämä koskee Yksisoluisen ja 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 alue (esimerkiksi valitsemalla E2: E11) ja paina Delete-näppäintä.

  • Ei voi lisätä tyhjiä soluja tai poistamalla solujen monisoluisen matriisikaavan.

Joskus tarvitset matriisikaavan laajentaminen. Valitse aiemmin luotu taulukko-alueen ensimmäinen solu ja jatka, kunnes olet valinnut koko alue, johon haluat laajentaa kaavaa. Paina F2 Muokkaa kaavaa ja paina CTRL + VAIHTO + ENTER Vahvista kaava, kun kaava alue on muutettu. Avain on koko alue alkaen vasemman yläkulman solua matriisista. Vasemman yläkulman solua on käytettävä tehdystä muutoksesta.

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

  • Voi toisinaan unohdat painamalla Ctrl + Vaihto + Enter. Se voi esiintyä myös eniten kokeneilta Excel-käyttäjille. Paina tätä näppäinyhdistelmää aina, kun kirjoitat tai muokkaat matriisikaavan muista.

  • Muiden käyttäjien työkirjan ehkä ymmärrä kaavojen. Harjoitus-Matriisikaavat yleensä ole selvennetään laskentataulukon. Tämän vuoksi tarvittaessa muiden työkirjojen muokkaaminen kannattaa joko välttää Matriisikaavat tai varmista, että heille minkä tahansa Matriisikaavat seikkoja ja tietoinen siitä, miten voit muuttaa niitä, he tarvitsevat.

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

Nyt mukaan tiedät haluat painamalla Ctrl + Vaihto + Enter , kun luoda matriisikaavoja. Koska matriisivakioita Matriisikaavoissa osa, ympäröivät aaltosulkeet vakiot kirjoittamalla ne manuaalisesti. Voit käyttää Ctrl + Vaihto + Enter koko kaava.

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

Näin yhdelle riville matriisin: {1,2,3,4}. Näin matriisin yhdessä sarakkeessa: {1; 2; 3; 4}. Ja tässä matriisin kaksi riviä ja joka on neljä saraketta: {1,2,3,4; 5,6,7,8}. Kaksi riviä-matriisissa ensimmäisellä rivillä on 1, 2, 3 ja 4 ja toinen rivi on 5, 6, 7 ja 8. Yhdellä puolipisteellä erottaa kaksi riviä 4 – 5.

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.

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

Vaakasuuntaisen vakion luominen

  1. Valitse solut A1 – E1 tyhjään laskentataulukkoon.

  2. Kirjoita kaavariville seuraava kaava ja paina Ctrl + Vaihto + Enter:

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

    Tällöin on kirjoitettava avaavat ja sulkevat aaltosulkeet ({}) ja Excel lisää toinen sarja puolestasi.

    Näet seuraavat tulokset.

    Matriisikaavan vaakasuuntainen matriisivakio

Pystysuuntaisen vakion luominen

  1. Valitse työkirjassa viiden solun sarake.

  2. Kirjoita kaavariville seuraava kaava ja paina 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 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 sitten 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

Sulkeiden sisällä viimeinen elementti on matriisivakion: {lukuja 1,2,3,4,5}. Muista, että Excel ei ympäröi Matriisivakioiden aaltosulkeet; Voit kirjoittaa todella. Huomaa, että sen jälkeen, kun lisäät vakion matriisikaavan, voit painaa Ctrl + Vaihto + Enter , kirjoittamalla kaavan.

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 seuraavaa, kopioi funktion, valitse tyhjä solu työkirjan, Liitä kaava kaavariville ja paina Ctrl + Vaihto + Enter. Näet saman tuloksen samoin kuin aiemmassa Harjoitus, joka käyttää matriisikaavan:

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

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.

Yksi paras tapa käyttää matriisivakioita on nimeät ne. Vakioihin voi olla helpompi käyttää, ja ne voidaan piilottaa taulukon kaavojen muilta monimutkaisuutta. Matriisivakion nimeäminen ja käyttää kaavassa, toimi seuraavasti:

  1. Valitse kaavat -välilehden Määritetyt nimet -ryhmässä Määritä nimi.
    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 sitten Ctrl + Vaihto + Enter-näppäintä.

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

  • Joitakin osia ei voi erottaa ERISNIMI-merkillä. Jos jätät pilkuilla tai puolipisteillä tai jos asetat jokin väärään paikkaan, matriisivakion ei voi luoda oikein tai jotkut 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.

Seuraavissa esimerkeissä on joitakin tapoja, jossa voit laittaa Matriisivakioiden käyttäminen matriisikaavoissa. Joissakin esimerkeissä käytetään TRANSPONOI-funktio muuntaa rivien, sarakkeiden 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 sitten 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 sitten 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 sitten 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 Ctrl + Vaihto + Enter, joka lisää solualueen C8:E10 tässä matriisissa laskeminen matriisikaavalla. Valitse laskentataulukosta 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 Ctrl + Vaihto + Enter:

    =C8:E10

    3 x 3 solumatriisin näkyy solujen C1 – E3 soluissa C8 – E10 näet samat arvot.

Matriisivakion luominen olemassa olevista arvoista

  1. Solujen C1: C3 valittuna, paina F2-näppäintä Siirry muokkaustilassa.

  2. Paina F9 muuntaa arvot soluviittaukset. Excel muuntaa matriisivakion arvot. Kaavan pitäisi nyt olla = {10,20,30 40,50,60; 70,80,90}.

  3. Painamalla Ctrl + Vaihto + Enter matriisivakion matriisikaavana.

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 Ctrl + Vaihto + Enter , jolloin näet soluissa a2: a6 (66) merkkien kokonaismäärän.

  3. Valitse solu A10 ja paina näppäinyhdistelmää Ctrl + Vaihto + Enter pisimmän sisällön näkyviin solujen a2: a6 (solu A3).

Käytössä on seuraava kaava soluun A8 laskee soluissa A2 – A6 (66) merkkien kokonaismäärän.

=SUMMA(PITUUS(A2:A6))

Tässä tapauksessa LEN -funktio palauttaa kunkin merkkijonon pituuden kunkin alueen soluista. Valitse Summa -funktio laskee arvot yhteen ja näyttää tuloksen (66).

N pienimmän arvon etsiminen alueeksi

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

  1. Kirjoita solujen A1:A11 joitakin satunnaisia lukuja.

  2. Valitse solut C1 – C3. Tämän kohdan arvoksi solujen mahtuu matriisikaavan palauttamat tulokset.

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

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

Tämä kaava käyttää matriisivakion arvioida Pieni -funktio kolme kertaa ja palauttaa pienimmän (1), toiseksi pienin (2) ja kolmas pienin (3) jäsenten matriisista, jotka sisältyvät solut a1: a10 voit etsiä arvoja, voit lisätä Lisää argumentit Vakio. Voit käyttää myös muita funktioita tämän kaavan, kuten SUMMAN tai keskiarvon. Esimerkki:

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

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

Alueen suurimman arvon etsiminen n

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. Kaavarivin Kirjoita tämä kaava ja paina näppäinyhdistelmää Ctrl + Vaihto + Enter:

    = LARGE(A1:A10,ROW(INDIRECT("1:3")))

Tässä vaiheessa se voi auttaa hieman lisätietoja rivi - ja epäsuora -funktiot. Rivi -funktion voit luoda peräkkäiset kokonaislukujen taulukko. Esimerkiksi valitsemalla tyhjän sarakkeen 10 solujen harjoituksen työkirjan, kirjoittaa seuraavan matriisikaavan ja paina 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.

Voit tarkastella kaavaa, jota käytit aiemmin – = Suuri (A5:A14,ROW(INDIRECT("1:3"))) – lähtien sisempien sulkeiden jatkuvasti ulospäin: epäsuora -funktio palauttaa joukon tekstiarvoja, tässä tapauksessa arvot 1 – 3. Rivi -funktio luo puolestaan kolmen solun Sarakemuoto matriisi. Suuri -funktio käyttää arvojen solualueen A5:A14 ja se arvioidaan kerran kullekin viitteeksi rivi -funktion palauttama kolme kertaa. Arvot 3200, 2700 ja 2000 palautetaan kolmen solun Sarakemuoto matriisi. Jos haluat etsiä arvoja, voit lisätä suurempi solualueen epäsuora -funktio.

Aiemmissa esimerkkien, voit käyttää tätä kaava, jossa muut Funktiot, kuten Summa - ja keskiarvo.

Solualueen pisimmän tekstimerkkijonon etsiminen

Siirry takaisin aiemman tekstin merkkijonon esimerkin, kirjoita seuraava kaava on tyhjä solu ja painamalla Ctrl + Vaihto + Enter:

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

Teksti ”bunch of cells that” tulee näkyviin.

Voit lähtien sisemmän elementit ja toimi ulospäin kaava tarkemmin. LEN -funktio palauttaa pituuden, jonka kohteiden solualueen a2: a6. Maks -funktio laskee suurimman arvon välillä kohteet, joka vastaa pisimmän tekstimerkkijonon, joka on solun 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:

(MAKS (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.

Lopuksi indeksi -funktio on seuraavat argumentit: matriisin ja matriisin rivin ja sarakkeen numeron. Solualueen a2: a6 on taulukko, vastine -funktio sisältää sen solun osoite ja lopullinen (1)-argumentti 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. 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))

Kuittaus

Tämän artikkelin osat on perusteella Excel tehokäyttäjän sarakkeiden Colin Wilcox kirjoittama ja sovittaa luvuissa 14 ja 15 Excel 2002 kaavoja, John Walkenbach entisen Excel MVP-Asiantuntija kirjoittama kirjan sarjaa.

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

Dynaaminen Matriisikaavat ja aiempien versioiden CSE matriisikaavoista

SUODATA-funktio

SATUNN.MATRIISI-funktio

JONO-funktio

YKSITTÄINEN-funktio

LAJITTELE-funktio

LAJITTELE.ARVOJEN.PERUSTEELLA-funktio

AINUTKERTAISET.ARVOT-funktio

Excelin #SPILL!-virheet

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

×