Massiivivalemite juhised ja näited

Massiivivalemite juhised ja näited

Massiivivalem on valem, mis saab teha massiivis ühe või mitme üksusega mitu arvutust. Massiivi võib mõelda väärtuste rea või veeruna või väärtuste ridade ja veergude kombinatsioonina. Massiivivalemid võivad tagastada mitu tulemit või ühe tulemi.

Alates Microsoft 365 2018. aasta septembri värskendusest. Iga valem,mis võib tagastada mitu tulemit, voolab need automaatselt kas alla või üle naaber lahtritesse. Selle käitumise muutusega kaasnevad ka mitmed uued dünaamilised massiivifunktsioonid. Dünaamilised massiivivalemid (olenemata sellest, kas nad kasutavad olemasolevaid funktsioone või dünaamilisi massiivifunktsioone) tuleb sisestada ainult ühte lahtrisse ja seejärel kinnitada sisestusklahvi (Enter) vajutamise abil. Varasemalt nõuavad pärandmassiivivalemid esmalt kogu väljundvahemiku valimist ja seejärel valemi kinnitamist klahvikombinatsiooniga Ctrl+Shift+Enter. Neid nimetatakse tavaliselt CSE valemiks.

Massiivivalemite abil saate teha keerukaid toiminguid, näiteks:

  • Saate kiiresti luua näidisandmekomplektid.

  • Loendage lahtrivahemikus sisalduvate märkide arv.

  • Summeerige ainult teatud tingimustele vastavad arvud (nt vahemiku väikseimad väärtused või ülemise ja alumise piiri vahele jäävad arvud).

  • Väärtustevahemiku iga N-nda väärtuse liitmine.

Järgmistes näidetes kirjeldatakse, kuidas luua mitmelahtmelist ja ühelahtse massiivivalemi. Võimaluse korral oleme lisanud näiteid mõnede dünaamiliste massiivifunktsioonidega ja olemasolevate massiivivalemitega, mis on sisestatud nii dünaamiliste kui ka pärandmassiividena.

Näidiste allalaadimine

Laadige alla näidistöövihik koos kõigi selles artiklis toodud massiivivalemite näidetega.

Selles harjutuses kirjeldatakse mitmelahtriliste ja ühelahtriliste massiivivalemite kasutamist müüginäitajate komplekti arvutamiseks. Esimeses näites antakse ülevaade mitmelahtrilise valemi kasutamisest vahekokkuvõtete komplekti arvutamiseks. Teises näites kasutatakse ühelahtrilist valemit üldkokkuvõtte arvutamiseks.

  • Mitmelahtriline massiivivalem

    Mitmelahtmeline massiivifunktsioon lahtris H10 =F10:F19*G10:G19 ühikuhinna alusel müüdud autode arvu arvutamiseks

  • Siin arvutame iga müügiesindaja kupeede ja sedaanide kogumüüki, sisestades lahtrisse H10 väärtuse =F10:F19*G10:G19.

    Sisestusklahvi (Enter)vajutamisel näete, et tulemid voolavad lahtritesse H10:H19. Pange tähele, et lekete vahemik on esile tõstetud äärisega, kui valite lekete vahemikus mis tahes lahtri. Samuti võite märgata, et lahtrite H10:H19 valemid on tuhmid. Need on lihtsalt viite jaoks olemas, nii et kui soovite valemit kohandada, peate valima lahtri H10, kus asub põhivalem.

  • Ühelahviline massiivivalem

    Ühelahtne massiivivalem üldsumma arvutamiseks valemiga =SUM(F10:F19*G10:G19)

    Tippige või kopeerige ja kleepige näidistöövihiku lahtrisse H20 =SUM(F10:F19*G10:G19)ja vajutage sisestusklahvi (Enter).

    Sel juhul Excel massiivi väärtused (lahtrivahemik F10 kuni G19) ja seejärel kasutab kokkuvõtteid liitmiseks funktsiooni SUM. Tulemuseks on müügi üldkokkuvõte ehk 1 590 000 eurot.

    Nagu näete, on seda tüüpi valem äärmiselt võimas. Oletagem näiteks, et teil on 1000 andmerida. Kõik need andmed (või vajadusel osa neist) saate kiiresti liita ühes lahtris loodava massiivivalemiga, mitte ei pea valemit läbi tuhande rea allapoole lohistama. Pange tähele, et lahtris H20 on ühelahtriline valem täiesti sõltumatu mitmelahtrilisest valemist (valem lahtrites H10 kuni H19). See illustreerib veel ühte massiivivalemite kasutamise eelist – paindlikkust. Saate muuta veeru H muid valemeid, ilma et see mõjutaks valemit lahtris H20. Samuti võib olla hea tava omada iseseisvaid kokkuvõtteid, kuna see aitab kontrollida teie tulemuste täpsust.

  • Dünaamilised massiivivalemid pakuvad ka järgmisi eeliseid.

    • Konsistentsus (Consistency)    Kui klõpsate mõnda H10 lahtrit allapoole, näete sama valemit. See ühtsus aitab tagada täpsuse.

    • Turvalisus.    Mitmelahtmelise massiivivalemi komponenti ei saa üle kirjutada. Näiteks klõpsake lahtrit H11 ja vajutage kustutusklahvi (Delete). Excel ei muuda massiivi väljundit. Selle muutmiseks peate valima massiivi ülemise vasakpoolse lahtri või lahtri H10.

    • Väiksemad failimahud    Sageli saate mitme vahevalemi asemel kasutada ühte massiivivalemit. Näiteks auto müügi näide kasutab ühte massiivivalemi, et arvutada tulemid veerus E. Kui kasutasite standardvalemeid (nt =F10*G10, F11*G11, F12*G12 jne), oleks samade tulemite arvutamiseks kasutatud 11 erinevat valemit. See pole küll suur asi, aga mis siis, kui teil oleks kokku tuhandeid ridu? Siis võib sellel olla suur erinevus.

    • Tõhusus    Massiivifunktsioonid võivad olla tõhus viis keerukate valemite järgus. Massiivivalem =SUM(F10:F19*G10:G19) on sama: =SUM(F10*G10;F11*G11;F12*G12;F 13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).

    • Voolav    Dünaamilised massiivivalemid voolavad automaatselt väljundvahemikku. Kui lähteandmed on tabelis Excel, muudetakse teie dünaamiliste massiivivalemite suurust andmete lisamisel või eemaldamisel automaatselt.

    • #SPILL! tõrge    Dünaamilised massiivid on #SPILL!, mis näitab, et kavandatud lekete vahemik on mingil põhjusel blokeeritud. Blokeerimise lahendamisel voolab valem automaatselt.

Massiivikonstandid on massiivivalemite üks komponente. Massiivikonstantide loomiseks tuleb sisestada üksuste loend ja seejärel ümbritseda loend käsitsi looksulgudega ({ }), näiteks nii:

={1;2;3;4;5} või ={"Jaanuar","Veebruar","Märts"}

Kui eraldate üksused längkriipsudega, loote sellega horisontaalse massiivi (rea). Kui eraldate üksused semikoolonitega, loote vertikaalse massiivi (veeru). Kahemõõtmelise massiivi loomiseks eraldage iga rea üksused komadega ja eraldage iga rida semikoolonitega.

Järgmine näide aitab teil harjutada horisontaalsete, vertikaalsete ja kahemõõtmeliste konstantide loomist. Näitame näiteid funktsiooni SEQUENCE abil, et genereerida automaatselt massiivikonstandid ja käsitsi sisestatud massiivikonstandid.

  • Horisontaalse konstandi loomine

    Kasutage eelmistes näidetes loodud töövihikut või looge uus töövihik. Valige mõni tühi lahter ja sisestage =SEQUENCE(1;5). Funktsioon SEQUENCE loob 1 rea 5 veerumassiiviga, mis on sama mis ={1;2;3;4;5}. Kuvatakse järgmine tulem.

    Horisontaalse massiivikonstandi loomine valemiga =SEQUENCE(1;5) või ={1;2;3;4;5}

  • Vertikaalse konstandi loomine

    Valige tühi lahter, mille all on ruum, ja sisestage =SEQUENCE(5)või ={1;2;3;4;5}. Kuvatakse järgmine tulem.

    Vertikaalse massiivikonstandi loomine valemiga =SEQUENCE(5) või ={1;2;3;4;5}

  • Kahemõõtmelise konstandi loomine

    Valige mis tahes tühi lahter, kus on paremal ja selle all ruumi, ja sisestage =SEQUENCE(3;4). Kuvatakse järgmine tulem.

    3 rea loomine 4 veeru massiivikonstandiga = SEQUENCE(3;4)

    Samuti saate sisestada: või ={1,2;3;4;5,6,7;8;9,10,11,12}, kuid soovite pöörata tähelepanu sellele, kuhu panete semikoolonid komadega võrreldes.

    Nagu näete, pakub funktsioon SEQUENCE olulisi eeliseid massiivikonstandi väärtuste käsitsi sisestamisel. Peamiselt säästab see aega, kuid aitab vähendada ka käsitsi sisestamisel ilmneda võidavad vead. Samuti on seda lihtsam lugeda, eriti kuna semikoolonit võib komaeraldajatest eristada.

Siin on näide, mis kasutab massiivikonstandid osana suuremast valemist. Avage näidistöövihikus töölehel Konstant või looge uus tööleht.

Lahtrisse D9 sisestasime väärtuse =SEQUENCE(1;5;3;1),kuid lahtritesse A9:H9 võite sisestada ka 3, 4, 5, 6 ja 7. Selles numbrivalikus pole midagi erilist, valisime lihtsalt midagi muud kui 1-5.

Sisestage lahtrisse E11 väärtus =SUM(D9:H9*SEQUENCE(1;5))või =SUM(D9:H9*{1;2;3;4;5}). Valemid tagastavad 85.

Kasutage valemites massiivikonstandid. Selles näites kasutasime funktsiooni =SUM(D9:H(*SEQUENCE(1;5))

Funktsioon SEQUENCE loob massiivikonstandi {1,2;3;4;5} ekvivalendi. Kuna Excel sooritab toiminguid esmalt sulgudes ümbritsetud avaldistega, on järgmised kaks taasesitatud elementi lahtriväärtused lahtris D9:H9 ja korrutustehtemärk (*). Valem korrutab talletatud massiivi väärtused konstandi vastavate väärtustega. See on võrdväärne järgmise valemiga:

=SUM(D9*1;E9*2;F9*3;G9*4;H9*5)või =SUM(3*1,4*2,5*3,6*4,7*5)

Lõpuks lisab funktsioon SUM väärtused ja tagastab väärtuse 85.

Salvestatud massiivi kasutamise vältimiseks ja toimingu täielikult mällu talletamiseks saate selle asendada mõne muu massiivikonstandiga.

=SUM(SEQUENCE(1;5;3;1)*SEQUENCE(1;5))või =SUM({3;4;5;6;7}*{1;2;3;4;5})

Elemendid, mida saate massiivikonstandites kasutada

  • Massiivikonstandid võivad sisaldada arve, teksti, loogikaväärtusi (nt TRUE ja FALSE) ning veaväärtusi (nt #N/A). Arvud saate kasutada täisarvudes, kümnendarvudes ja teaduslikes vormingutes. Kui kaasate teksti, peate selle ümbritsema jutumärkidega ("tekst").

  • Massiivikonstandid ei tohi sisaldada täiendavaid massiive, valemeid ega funktsioone. Teisisõnu tohib nendes kasutada üksnes längkriipsude või semikoolonitega eraldatud teksti või arve. Excel kuvab hoiatusteate või jätab valemi arvestamata, kui sisestate valemina näiteks {1\2;A1:D4} või {1\2\SUM(Q2:Z8)}. Samuti ei tohi arvväärtused sisaldada protsendimärke, dollarimärke, punkte ega ümarsulge.

Üks parimaid viise massiivikonstandite kasutamiseks on neile nimi. Nimega konstante on sageli märksa lihtsam kasutada ja nende abil saab osa massiivivalemite keerukusest teiste eest varju jätta. Kui soovite massiivikonstandile nime panna ja seda siis valemis kasutada, toimige järgmiselt.

Avage Valemid >Määratletud > Määratle nimi. Tippige väljale Nimi väärtus Kvartal1. Väljale Viitab sisestage järgmine konstant (pidage meeles, et ka looksulud tuleb teil endal käsitsi tippida):

={"Jaanuar"\"Veebruar"\"Märts"}

Dialoogiboks peaks nüüd välja nägema järgmine:

Nimega massiivikonstandi lisamine valemitest > Määratletud nimed > Name Manager > New

Klõpsake nuppu OK, seejärel valige mis tahes rida, kus on kolm tühja lahtrit, ja sisestage =Kvartal1.

Kuvatakse järgmine tulem.

Nimega massiivikonstandi kasutamine valemis(nt =Kvartal1), kus kvartal1 on määratletud kui ={"Jaanuar","Veebruar","Märts"}

Kui soovite, et tulemid voolaks horisontaalselt vertikaalselt, saate kasutada funktsiooni =TRANSPOSE(kvartal1).

Kui soovite kuvada 12-kuulise loendi (nt finantsaruande loomiseks), saate selle aluseks võtta praeguse aasta ja funktsiooni SEQUENCE. Selle funktsiooni puhul on hea mõte see, et kuigi kuvatakse ainult kuu, on selle taga kehtiv kuupäev, mida saate kasutada ka muudes arvutustes. Need näited leiate näidistöövihiku töölehtedelt Nimega massiivikonstant ja Kiirandmekogum.

=TEXT(DATE(YEAR(TODAY());SEQUENCE(1;12);1);"mmm")

Funktsioonide TEXT, DATE, YEAR, TODAY ja SEQUENCE kombineerimine 12-kuulise dünaamilise loendi järgus

See kasutab funktsiooni DATE praeguse aasta põhjal kuupäeva loomiseks, funktsioon SEQUENCE loob massiivikonstandi 1-st 12-ni jaanuarist detsembrini, seejärel teisendab funktsioon TEXT kuvavorminguks "mmm" (jaanuar, veebruar, märts jne). Kui soovite kuvada täiskuu nime (nt jaanuar), kasutage teksti "mmmm".

Kui kasutate nimega konstanti massiivivalemina, ärge unustage sisestada võrdusmärki( nt =Kvartal1, mitte ainult Kvartal1). Kui jätate võrdusmärgi lisamata, tõlgendab Excel massiivi tekstistringina ja teie valem ei anna eeldatud tulemust. Lõpetuseks pidage meeles, et saate kasutada funktsioonide, teksti ja arvude kombinatsioone. See kõik sõltub sellest, kui loominguline soovite saada.

Järgmised näited illustreerivad mõnda massiivikonstantide massiivivalemites kasutamise võimalust. Mõned näited kasutavad funktsiooni TRANSPOSE ridade veergudeks teisendamiseks ja vastupidi.

  • Massiivis on mitu üksust

    Sisestage =SEQUENCE(1;12)*2või ={1;2;3;4;5,6;7,8;9,10,11,12}*2

    Samuti saate jagada väärtusega (/), liita väärtusega (+) ja lahutada väärtusega (-).

  • Massiivi üksuste ruutu võtmine

    Sisestage =SEQUENCE(1;12)^2või ={1;2;3;4;5,6;7,8;9,10,11,12}^2

  • Massiivi ruutjuure find the squared items in a array

    Enter =SQRT(SEQUENCE(1;12)^2)või =SQRT({1;2;3;4;5,6;7;8;9,10,11,12}^2)

  • Ühemõõtmelise rea transponeerimine

    Enter =TRANSPOSE(SEQUENCE(1;5))või =TRANSPOSE({1;2;3;4;5})

    Ehkki sisestasite horisontaalse massiivikonstandi, teisendab funktsioon TRANSPOSE massiivikonstandi veeruks.

  • Ühemõõtmelise veeru transponeerimine

    Enter =TRANSPOSE(SEQUENCE(5;1))või =TRANSPOSE({1;2;3;4;5})

    Ehkki sisestasite vertikaalse massiivikonstandi, teisendab funktsioon TRANSPOSE konstandi reaks.

  • Kahemõõtmelise konstandi transponeerimine

    Enter =TRANSPOSE(SEQUENCE(3;4))või =TRANSPOSE({1;2;3;4;5,6;7;8;9;10;11,12})

    Funktsioon TRANSPOSE teisendab iga rea veerujadaks.

Käesolevas jaotises antakse ülevaade mõne lihtsama massiivivalemi kasutamisest.

  • Massiivi loomine olemasolevate väärtuste põhjal

    Järgmises näites selgitatakse, kuidas kasutada massiivivalemeid olemasolevast massiivist uue massiivi loomiseks.

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

    Tippige kindlasti enne 10 tippimist { (ava looksulud) ja pärast 180 tippimist väärtus } (sulgemissulgu), kuna loote arvumassiivi.

    Järgmiseks sisestage tühja lahtrisse =D9#või =D9:I11. Kuvatakse 3 x 6 lahtrimassiiv, mis sisaldab samu väärtusi, mida näete D9:D11-s. Märki #nimetatakse voolanudvahemikumärgiks ja see on Excel viis viidata tervele massiivivahemikule, mitte tippimiseks.

    Olemasolevale massiivile viitamiseks kasutage voolanud vahemiku tehtemärki (#)

  • Massiivikonstandi loomine olemasolevate väärtuste põhjal

    Saate võtta voolanud massiivivalemi tulemid ja teisendada selle komponendiosadeks. Valige lahter D9 ja vajutage redigeerimisrežiimi aktiveerimiseks klahvi F2. Järgmiseks vajutage lahtriviidete teisendamiseks väärtusteks klahvi F9, Excel seejärel teisendatakse massiivikonstandiks. Sisestusklahvi (Enter)vajutamisel peaks valem =D9#olema nüüd ={10,20;30;40,50,60;70,80,90}.

  • Lahtrivahemiku märkide loendamine.

    Järgmises näites kirjeldatakse, kuidas loendada lahtrivahemikus märkide arvu. See hõlmab tühikuid.

    Märkide koguarvu loendamine vahemikus ja muud massiivid tekstistringidega töötamiseks

    =SUM(LEN(C9:C13))

    Sel juhul tagastab funktsioon LEN iga vahemiku lahtri iga tekstistringi pikkuse. Seejärel lisab funktsioon SUM need väärtused kokku ja kuvab tulemi (66). Kui soovite saada keskmist märkide arvu, võiksite kasutada:

    =AVERAGE(LEN(C9:C13))

  • Pikima lahtri sisu vahemikus C9:C13

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

    Valemit saab kasutada ainult juhul, kui andmevahemik sisaldab ainult ühte lahtriveergu.

    Heitkem sellele valemile täpsem pilk, alustades seespoolsetest elementidest ja liikudes analüüsimisega väljapoole. Funktsioon LEN tagastab iga lahtrivahemiku D2:D6 üksuse pikkuse. Funktsioon MAX arvutab nende üksuste suurima väärtuse, mis vastab pikimale tekstistringile, mis asub lahtris D3.

    Siit edasi läheb lugu veidi keerulisemaks. Funktsioon MATCH arvutab pikimat tekstistringi sisaldava lahtri nihke (suhtelise asukoha). Selleks läheb vaja kolme argumenti: otsinguväärtust, otsingumassiivi ja vastendustüüpi. Funktsioon MATCH otsib otsingumassiivist määratud otsinguväärtust. Käesoleval juhul on otsinguväärtus pikim tekstistring:

    MAX(LEN(C9:C13)

    ja see string asub selles massiivis:

    LEN(C9:C13)

    Argumendi vaste tüüp väärtus on 0. Vaste tüüp võib olla väärtus 1, 0 või -1.

    • 1 – tagastab suurima väärtuse, mis on otsinguväärtusest väiksem või sellega võrdne.

    • 0 – tagastab esimese väärtuse, mis on täpselt võrdne otsinguväärtusega.

    • -1 - tagastab väikseima väärtuse, mis on määratud otsinguväärtusest suurem või sellega võrdne.

    • Kui jätate vastendustüübi ära, eeldab Excel, et vastendustüüp on 1.

    Lõpuks kasutab funktsioon INDEX järgmisi argumente: massiivi ning rea- ja veerunumbrit selles massiivis. Lahtrivahemik C9:C13 sisaldab massiivi, funktsioon MATCH lahtri aadressi ja viimane argument (1) määrab, et väärtus pärineb massiivi esimesest veerust.

    Kui soovite saada väikseima tekstistringi sisu, asendate ülaltoodud näites MAX väärtusega MIN.

  • Vahemiku n väiksema väärtuse leidmine

    Selles näites kirjeldatakse, kuidas leida lahtrivahemikus kolm väikseimat väärtust, kus lahtriteS B9:B18 on loodud näidisandmete massiiv: =INT(RANDARRAY(10;1)*100). Arvestage, et FUNKTSIOON RANDARRAY on muutlik funktsioon, nii et iga kord, kui funktsioon arvutab, saate Excel juhuarvude komplekti.

    Excel massiivivalem n-nda väikseima väärtuse leidmiseks: =SMALL(B9#;SEQUENCE(D9))

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

    See valem kasutab funktsiooni SMALL kolm korda hindamiseks massiivikonstandit ja tagastab lahtrites B9:B18 sisalduva massiivi väikseimad kolm liiget, kus 3 on lahtris D9 muutuv väärtus. Rohkemate väärtuste leidmiseks saate funktsioonis SEQUENCE väärtust suurendada või konstandile veel argumente lisada. Seejärel saate kasutada ka muid funktsioone, mis sisaldavad seda valemit, näiteks SUM või AVERAGE. Näited:

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

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

  • Vahemiku n suurima väärtuse leidmine

    Vahemiku suurimate väärtuste leidmiseks saate funktsiooni SMALL asendada funktsiooniga LARGE. Lisaks kasutatakse järgmises näites ka funktsioone ROW ja INDIRECT.

    Sisestage =LARGE(B9#;ROW(INDIRECT("1:3")))või =LARGE(B9:B18;ROW(INDIRECT("1:3")))

    Siinkohal on mõistlik anda ka põgus ülevaade funktsioonidest ROW ja INDIRECT. Funktsiooniga ROW saate luua järjestikuste täisarvude massiivi. Näiteks valige tühi ja sisestage:

    =ROW(1:10)

    Valem loob kümmet järjestikust täisarvu sisaldava veeru. Võimalike probleemide kuvamiseks lisage massiivivalemit sisaldava vahemiku kohale (ehk rea 1 kohale) uus rida. Excel reaviiteid ja valem genereerib nüüd täisarvud vahemikus 2 kuni 11. Probleemi lahendamiseks tuleb valemisse lisada funktsioon INDIRECT:

    =ROW(INDIRECT("1:10"))

    Funktsioon INDIRECT kasutab argumentidena tekstistringe (seetõttu ümbritseb vahemik 1:10 jutumärkidega). Excel ei kohanda tekstväärtusi, kui lisate ridu või teisaldate massiivivalemi. Seetõttu loob funktsioon ROW alati täpselt selle täisarvude massiivi, mida soovite. Samuti võite kasutada funktsiooni SEQUENCE.

    =SEQUENCE(10)

    Uurime valemit, mida kasutasite varem – =LARGE(B9#;ROW(INDIRECT("1:3"))) alates sisemistest sulgudest ja töötades väljapoole. Funktsioon INDIRECT tagastab tekstiväärtuste komplekti, sel juhul väärtused 1 kuni 3. Funktsioon ROW genereerib omakorda kolmelahtse veerumassiivi. Funktsioon LARGE kasutab lahtrivahemiku B9:B18 väärtusi ja seda hinnatakse kolm korda, üks kord iga funktsiooni ROW tagastatud viite kohta. Kui soovite leida rohkem väärtusi, lisage funktsioonile INDIRECT suurem lahtrivahemik. Lõpetuseks saate seda valemit kasutada koos muude funktsioonidega (nt SUM ja AVERAGE).

  • Veaväärtusi sisaldava vahemiku liitmine

    Funktsioon SUM Excel ei tööta, kui proovite summeerida vahemikku, mis sisaldab veaväärtust (nt #VALUE! või #N/A. Selles näites kirjeldatakse, kuidas summeerida väärtusi vahemikus Nimega Andmed, mis sisaldab tõrkeid.

    Massiivide abil saate vigadega tegeleda. Näiteks =SUM(IF(ISERROR(Data),""";Data) summeerib vahemiku nimega Andmed isegi siis, kui see sisaldab tõrkeid, näiteks #VALUE! või #NA!.

  • =SUM(IF(ISERROR(Andmed);"";Andmed))

    Valem loob uue massiivi, mis sisaldab algseid väärtusi ilma veaväärtusteta. Sisemistest funktsioonidest alustades ja väljapoole liikudes otsib funktsioon ISERROR lahtrivahemikust (Andmed) vigu. Funktsioon IF tagastab kindla väärtuse, kui teie määratud tingimus annab vastuseks TRUE, ja teise väärtuse, kui tingimus annab vastuseks FALSE. Sel juhul tagastab see kõigi veaväärtuste kohta tühjad stringid (""), kuna need annavad vastuseks TRUE, ja vahemiku (Andmed) ülejäänud väärtused, kuna need annavad vastuseks FALSE, mis tähendab, et need ei sisalda veaväärtusi. Seejärel arvutab funktsioon SUM filtreeritud massiivi kogusumma.

  • Vahemiku veaväärtuste loendamine

    See näide on nagu eelmine valem, kuid tagastab veaväärtuste arvu vahemikus Andmed, mitte ei filtreeri neid välja.

    =SUM(IF(ISERROR(Andmed);1;0))

    Valem loob massiivi, mis sisaldab väärtust 1 nende lahtrite jaoks, milles leidub vigu, ja väärtust 0 nende lahtrite jaoks, mis ei sisalda vigu. Soovi korral saate valemit lihtsustada ja saada sama tulemuse, kui eemaldate funktsiooni IF kolmanda argumendi, näiteks nii:

    =SUM(IF(ISERROR(Andmed);1))

    Kui te argumenti ei määra, tagastab funktsioon IF väärtuse FALSE, kui lahter ei sisalda veaväärtust. Vajadusel saate valemit veelgi lihtsustada:

    =SUM(IF(ISERROR(Andmed)*1))

    See versioon töötab, kuna TRUE*1=1 ja FALSE*1=0.

Vahel võib teil tekkida vajadus liita väärtused teatud tingimuste põhjal.

Massiivide abil saate arvutada teatud tingimuste alusel. =SUM(IF(Müük>0;Müük)) summeerib kõik väärtused, mis on suuremad kui 0 vahemikus Müük.

See massiivivalem summeerib näiteks ainult positiivsed täisarvud vahemikus Müük, mis tähistab ülaltoodud näites lahtreid E9:E24.

=SUM(IF(Müük>0;Müük))

Funktsioon IF loob positiivsete ja väärade väärtuste massiivi. Funktsioon SUM sisuliselt ignoreerib vääraid väärtusi, kuna 0+0=0. Selles valemis kasutatav lahtrivahemik võib koosneda suvalisest arvust ridadest ja veergudest.

Liita saate ka sellised väärtused, mis täidavad rohkem kui ühe tingimuse. See massiivivalem arvutab näiteks väärtused, mis on suuremad kui 0 JA vähem kui 2500.

=SUM((Müük>0)*(Müük<2500)*(Müük))

Pidage meeles, et see valem tagastab vea, kui vahemik sisaldab vähemalt ühte mittearvulist lahtrit.

Samuti saate luua massiivivalemeid, mis kasutavad teatud tüüpi OR-tingimust. Näiteks saate liita väärtused, mis on suuremad kui 0 VÕI vähem kui 2500.

=SUM(IF((Müük>0)+(Müük<2500),Müük))

Funktsioone AND ja OR ei saa massiivivalemites otse kasutada, kuna need funktsioonid tagastavad ühe tulemi (kas TRUE või FALSE), kuid massiivifunktsioonid nõuavad tulemite massiive. Probleemi lahendamiseks võite kasutada eelmises valemis näidatud loogikat. Teisisõnu saate teha matemaatikatoiminguid (nt lisada või korrutada väärtusi, mis vastavad tingimusele OR või AND).

Selles näites kirjeldatakse nullide eemaldamist vahemikust, mille väärtuste keskmist soovite arvutada. Valemis kasutatakse andmevahemikku nimega Müük.

=AVERAGE(IF(Müük<>0;Müük))

Funktsioon IF loob massiivi väärtustest, mis ei võrdu nulliga, ja edastab need väärtused siis funktsioonile AVERAGE.

See massiivivalem võrdleb lahtrivahemikes MinuAndmed ja SinuAndmed asuvaid väärtusi ning tagastab nende kahe vahemiku vaheliste erinevuste arvu. Kui mõlema vahemiku sisu on samane, tagastab valem väärtuse 0. Selle valemi kasutamiseks peavad lahtrivahemikud olema sama suurusega ja sama mõõtmega. Näiteks kui MyData on vahemik, mis sisaldab 3 rida 5 veeru järgi, peab teie andmed olema ka 3 rida 5 veeru kaupa.

=SUM(IF(MinuAndmed=SinuAndmed;0;1))

See valem loob uue massiivi, mis on võrreldavate vahemikega ühesuurune. Funktsioon IF täidab massiivi väärtusega 0 ja väärtusega 1 (0 lahknevuste ja 1 identsete lahtrite korral). Funktsioon SUM tagastab seejärel massiivi väärtuste summa.

Vajadusel saate valemit lihtsustada:

=SUM(1*(MyData<>YourData))

Sarnaselt valemiga, mis loendab vahemikus leiduvaid veaväärtusi, on ka see valem kasutatav, kuna TRUE*1=1 ja FALSE*1=0.

See massiivivalem tagastab üheveerulise vahemiku Andmed suurima väärtuse reanumbri:

=MIN(IF(Andmed=MAX(Andmed);ROW(Andmed);""))

Funktsioon IF loob uue massiivi, mis vastab vahemikule nimega Andmed. Kui vastav lahter sisaldab vahemiku suurimat väärtust, sisaldab massiiv reanumbrit. Muul juhul sisaldab massiiv tühje stringi (""). Funktsioon MIN kasutab uut massiivi oma teise argumendina ja tagastab väikseima väärtuse, mis vastav vahemiku Andmed suurima väärtuse reanumbrile. Kui vahemik Andmed sisaldab mitut identset suurimat väärtust, tagastab valem esimese väärtuse rea.

Kui soovite tagastada suurima väärtuse tegeliku lahtriaadressi, kasutage seda valemit:

=ADDRESS(MIN(IF(Andmed=MAX(Andmed);ROW(Andmed);""));COLUMN(Andmed))

Sarnased näited leiate näidistöövihikust töölehel Andmekomplektide erinevused.

Selles harjutuses kirjeldatakse mitmelahtriliste ja ühelahtriliste massiivivalemite kasutamist müüginäitajate komplekti arvutamiseks. Esimeses näites antakse ülevaade mitmelahtrilise valemi kasutamisest vahekokkuvõtete komplekti arvutamiseks. Teises näites kasutatakse ühelahtrilist valemit üldkokkuvõtte arvutamiseks.

  • Mitmelahtriline massiivivalem

Kopeerige kogu all olev tabel ja kleepige see tühja töölehe lahtrisse A1.

Müügiesindaja

Auto tüüp

Müüdud number

Ühiku hind

Müük kokku

Vares

Sedaan

5

33000

Kupee

4

37000

Kana

Sedaan

6

24000

Kupee

8

21000

Savi

Sedaan

3

29000

Kupee

1

31000

Post

Sedaan

9

24000

Kupee

5

37000

Lill

Sedaan

6

33000

Kupee

8

31000

Valem (kogusumma)

Kogusumma

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

=SUM(C2:C11*D2:D11)

  1. Et näha iga müügiesindaja kupeede ja sedaanide kogumüüki, valige lahtrid E2:E11, sisestage valem =C2:C11*D2:D11ja seejärel vajutage klahvikombinatsiooni Ctrl+Shift+Enter.

  2. Kõigi müügisummade üldsumma miseks valige lahter F11, sisestage valem =SUM(C2:C11*D2:D11)ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter.

Klahvikombinatsiooni Ctrl+Shift+Entervajutamisel ümbritseb Excel valemi looksulgudega ({ }) ja lisab valemi eksemplari valitud vahemiku igas lahtris. Kuna see toimub väga kiiresti, näete veerus E kohe iga müügiesindaja iga autotüübi läbimüügi kogusummat. Kui valite lahtri E2, siis E3, E4 jne, näete, et neis kõigis kuvatakse sama valem: {=C2:C11*D2:D11}

Summad veerus E on arvutatud massiivivalemiga

  • Ühte lahtrit hõlmava massiivivalemi koostamine

Tippige töövihiku lahtrisse D13 järgmine valem ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter:

=SUM(C2:C11*D2:D11)

Sel juhul Excel massiivi väärtused (lahtrivahemik C2 kuni D11) ja seejärel liita kokkuvõtted funktsiooni SUMabil. Tulemuseks on müügi üldkokkuvõte ehk 1 590 000 eurot. Nagu näete, on seda tüüpi valem äärmiselt võimas. Oletagem näiteks, et teil on 1000 andmerida. Kõik need andmed (või vajadusel osa neist) saate kiiresti liita ühes lahtris loodava massiivivalemiga, mitte ei pea valemit läbi tuhande rea allapoole lohistama.

Pange tähele, et lahtris D13 on ühelahtriline valem täiesti sõltumatu mitmelahtrilisest valemist (valemist lahtrites E2 kuni E11). See illustreerib veel ühte massiivivalemite kasutamise eelist – paindlikkust. Saate veeru E valemeid muuta või veeru täielikult kustutada, ilma et see mõjutaks valemit lahtris D13.

Massiivivalemitega kaasnevad ka järgmised eelised.

  • Konsistentsus (Consistency)    Kui klõpsate mõnda lahtrit alates lahtrist E2, näete kõigis veeru E lahtrites sama valemit. See ühtsus aitab tagada täpsuse.

  • Turvalisus.    Mitmelahtrilise massiivivalemi komponente ei saa üle kirjutada. Näiteks klõpsake lahtrit E3 ja vajutage kustutusklahvi (Delete). Teil tuleb valida terve lahtrivahemik (E2 kuni E11) ja muuta kogu massiivi valemit või jätta massiiv selle praegusele kujule. Lisaohutusmeetmena peate valemi mis tahes muudatuse kinnitamiseks vajutama klahvikombinatsiooni Ctrl+Shift+Enter.

  • Väiksemad failimahud    Sageli saate mitme vahevalemi asemel kasutada ühte massiivivalemit. Näiteks kasutab töövihik ühte massiivivalemi, et arvutada tulemid veerus E. Kui kasutasite standardvalemeid (nt =C2*D2, C3*D3, C4*D4...), oleks samade tulemite arvutamiseks kasutatud 11 erinevat valemit.

Üldiselt kasutavad massiivivalemid standardset valemisüntaksit. Kõik need algavad võrdusmärgiga (=) ja saate kasutada enamikku Excel massiivivalemite funktsioone. Peamine erinevus on selles, et massiivivalemi kasutamisel vajutage valemi sisestamiseks klahvikombinatsiooni Ctrl+Shift+Enter. Sel juhul ümbritseb Excel massiivivalem looksulgudega – kui tipite looksulud käsitsi, teisendatakse valem tekstistringiks ja see ei toimi.

Massiivifunktsioonid võivad olla tõhus viis keerukate valemite järgus. Massiivivalem =SUM(C2:C11*D2:D11) on näiteks sama nagu valem =SUM(C2*D2;C3*D3;C4*D4;C5*D5;C6*D6;C7*D7;C8*D8;C9*D9;C10*D10;C11*D11).

NB!: Vajutage klahvikombinatsiooni Ctrl+Shift+Enter iga kord, kui peate sisestama massiivivalemi. See reegel kehtib nii ühelahtriliste kui ka mitmelahtriliste valemite kohta.

Mitmelahtriliste valemitega töötamisel tuleb teil silmas pidada ka järgmisi reegleid.

  • Valige tulemite talletamiseks soovitud lahtrivahemik enne valemi sisestamist. Seda tegite mitmelahtrilise massiivivalemi loomisel, kui valisite lahtrid E2 kuni E11.

  • Üksiku lahtri sisu massiivivalemis ei saa muuta. Selles veendumiseks valige töövihikus lahter E3 ja vajutage kustutusklahvi (Delete). Excel kuvab teate selle kohta, et te ei saa massiivi osa muuta.

  • Soovi korral saate teisaldada või kustutada terve massiivivalemi, kuid osaliselt ei saa seda ei teisaldada ega kustutada. Massiivivalemiga hõlmatud lahtrite arvu vähendamiseks tuleb olemasolev valem esmalt kustutada ja siis otsast alustada.

  • Massiivivalemi kustutamiseks valige kogu valemivahemik (nt E2:E11), seejärelvajutage kustutusklahvi (Delete).

  • Tühje lahtreid ei saa mitmelahtrilisesse massiivivalemisse lisada ega kustutada.

Vahel võib teil tekkida vajadus massiivivalemit laiendada. Valige olemasoleva massiivivahemiku esimene lahter ja jätkake, kuni olete valinud kogu vahemiku, millele soovite valemit laiendada. Valemi redigeerimiseks vajutage klahvi F2 ja seejärel vajutage klahvikombinatsiooni CTRL+SHIFT+ENTER, et kinnitada valem pärast valemivahemiku kohandamist. Võtmeks on valida kogu vahemik, alustades massiivi ülemisest vasakpoolsest lahtrist. Ülemine vasakpoolne lahter on see, mida redigeeritakse.

Massiivivalemid on küll väga toredad, kuid nende kasutamisel on siiski ka teatavaid puuduseid.

  • Mõnikord võite unustada vajutada klahvikombinatsiooni Ctrl+Shift+Enter. See võib juhtuda ka siis, kui olete väga kogenud Exceli kasutaja. Pidage meeles, et seda klahvikombinatsiooni tuleb vajutada alati, kui sisestate massiivivalemi või redigeerite seda.

  • Teie töövihiku teised kasutajad ei pruugi teie valemeid mõista. Üldjuhul ei lisata töölehel massiivivalemitele selgitust. Seega, kui teised inimesed peavad teie töövihikuid muutma, peaksite massiivivalemeid vältima või veenduma, et need inimesed teaksid massiivivalemeid ja mõistaksid, kuidas neid vajaduse korral muuta.

  • Sõltuvalt teie arvuti protsessori töökiirusest ja mälust võib juhtuda, et suured massiivivalemid muudavad arvutamise aeglaseks.

Massiivikonstandid on massiivivalemite üks komponente. Massiivikonstantide loomiseks tuleb sisestada üksuste loend ja seejärel ümbritseda loend käsitsi looksulgudega ({ }), näiteks nii:

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

Nüüd peate massiivivalemite loomisel vajutama klahvikombinatsiooni Ctrl+Shift+Enter. Kuna massiivikonstandid on üks massiivivalemite komponente, tuleb konstandid looksulgudega ümbritseda käsitsi ehk looksulud ise valemiribale tippida. Seejärel saate kogu valemi sisestamiseks kasutada klahvikombinatsiooni Ctrl+Shift+Enter.

Kui eraldate üksused längkriipsudega, loote sellega horisontaalse massiivi (rea). Kui eraldate üksused semikoolonitega, loote vertikaalse massiivi (veeru). Kahemõõtmelise massiivi loomiseks tuleb iga rea üksused eraldada längkriipsudega ja iga rida eraldada semikooloniga.

Massiiv on ühes reas: {1,2;3;4}. Üheveeruline massiiv on järgmine: {1;2;3;4}. Kahest reast ja neljast veerust koosnev massiiv aga tuleb sisestada sellisel kujul: {1\2\3\4;5\6\7\8}. Kahes reamassiivis on esimene rida 1, 2, 3 ja 4 ning teine rida on 5, 6, 7 ja 8. Kahte rida eraldab üks semikoolon (4 ja 5 vahel).

Sarnaselt massiivivalemitega saab massiivikonstante kasutada koos enamiku Exceli sisefunktsioonidega. Järgmises jaotises kirjeldatakse iga konstanditüübi loomist ja ka seda, kuidas neid konstante koos Exceli funktsioonidega kasutada.

Järgmine näide aitab teil harjutada horisontaalsete, vertikaalsete ja kahemõõtmeliste konstantide loomist.

Horisontaalse konstandi loomine

  1. Valige tühjal töölehel lahtrid A1 kuni E1.

  2. Sisestage valemiribale järgmine valem ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter:

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

    Sel juhul peaksite tippima avamis- ja sulgemis looksulud ({ } )ja Excel lisab teie eest teise komplekti.

    Kuvatakse järgmine tulem.

    Horisontaalne massiivikonstant valemis

Vertikaalse konstandi loomine

  1. Valige töövihikus viiest lahtrist koosnev veerg.

  2. Sisestage valemiribale järgmine valem ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter:

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

    Kuvatakse järgmine tulem.

    Vertikaalne massiivikonstant massiivivalemis

Kahemõõtmelise konstandi loomine

  1. Valige töövihikus nelja veeru laiune ja kolme rea kõrgune lahtriplokk.

  2. Sisestage valemiribale järgmine valem ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter:

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

    Kuvatakse järgmine tulem.

    Kahemõõtmeline massiivikonstant massiivivalemis

Konstantide kasutamine valemites

Konstante kasutatakse järgmises lihtsas näites.

  1. Looge näidistöövihikus uus tööleht.

  2. Tippige lahtrisse A1 arv 3. Seejärel tippige arv 4 lahtrisse B1, 5 lahtrisse C1, 6 lahtrisse D1 ja 7 lahtrisse E1.

  3. Tippige lahtrisse A3 järgmine valem ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter:

    =SUM(A1:E1*{1\2\3\4\5})

    Nagu näete, ümbritseb Excel konstandi veel teisegi looksulgude komplektiga, kuna sisestasite valemi massiivivalemina.

    Massiivikonstandiga massiivivalem

    Lahtris A3 kuvatakse väärtus 85.

Järgmises jaotises selgitatakse valemi tööpõhimõtteid.

Valem, mida te just kasutasite, koosneb mitmest osast.

Massiivikonstandiga massiivivalemi süntaks

1. Funktsioon

2. Talletatud massiiv

3. Tehtemärk

4. Massiivikonstant

Viimane ümarsulgudesse kaasatud element on massiivikonstant: {1\2\3\4\5}. Pidage meeles, et Excel ei ümbritse massiivikonstante automaatselt looksulgudega, vaid peate seda ise tegema. Pidage meeles ka seda, et pärast massiivivalemile konstandi sisestamist vajutage valemi sisestamiseks klahvikombinatsiooni Ctrl+Shift+Enter.

Kuna Excel sooritab esmalt ümarsulgudega raamitud avaldiste tehted, on järgmised kaks elementi, mida arvutamisel arvesse võetakse, töövihikus talletatud väärtused (A1:E1) ja tehtemärk. Valem korrutab talletatud massiivi väärtused konstandi vastavate väärtustega. See on võrdväärne järgmise valemiga:

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

Lõpuks liidab funktsioon SUM saadud väärtused ning lahtris A3 kuvataksegi summana 85.

Kui te ei soovi talletatud massiivi kasutada ning eelistate kogu tehte tervenisti mällu jätta, asendage talletatud massiiv teise massiivikonstandiga:

=SUM({3\4\5\6\7}*{1\2\3\4\5})

Selle proovimiseks kopeerige funktsioon, valige töövihikus tühi lahter, kleepige valem valemiribale ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter. Peaksite saama sama tulemi, nagu käesolevas harjutuses eespool, kui kasutasite massiivivalemit

=SUM(A1:E1*{1\2\3\4\5})

Massiivikonstandid võivad sisaldada arve, teksti, loogikaväärtusi (nt TRUE ja FALSE) ning veaväärtusi (nt #N/A). Arve saate kasutada nii täis- ja kümnendarvudena kui ka teaduskujul (eksponentkujul). Teksti kaasamisel tuleb tekst panna jutumärkidesse (").

Massiivikonstandid ei tohi sisaldada täiendavaid massiive, valemeid ega funktsioone. Teisisõnu tohib nendes kasutada üksnes längkriipsude või semikoolonitega eraldatud teksti või arve. Excel kuvab hoiatusteate või jätab valemi arvestamata, kui sisestate valemina näiteks {1\2;A1:D4} või {1\2\SUM(Q2:Z8)}. Samuti ei tohi arvväärtused sisaldada protsendimärke, dollarimärke, punkte ega ümarsulge.

Üks parimaid viise massiivikonstandite kasutamiseks on neile nimi. Nimega konstante on sageli märksa lihtsam kasutada ja nende abil saab osa massiivivalemite keerukusest teiste eest varju jätta. Kui soovite massiivikonstandile nime panna ja seda siis valemis kasutada, toimige järgmiselt.

  1. Klõpsake menüü Valemid jaotise Määratud nimed nuppu Nime määratlus.
    Kuvatakse dialoogiboks Nime määratlemine.

  2. Väljale Nimi tippige Kvartal1.

  3. Väljale Viitab sisestage järgmine konstant (pidage meeles, et ka looksulud tuleb teil endal käsitsi tippida):

    ={"Jaanuar"\"Veebruar"\"Märts"}

    Dialoogiboksi sisu on nüüd järgmine.

    Nime redigeerimise dialoogiboks koos valemiga

  4. Klõpsake nuppu OK ja valige töölehel kolmest tühjast lahtrist koosnev rida.

  5. Tippige järgmine valem ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter.

    =Kvartal1

    Kuvatakse järgmine tulem.

    Valemina sisestatud nimega massiiv

Nimega konstandi kasutamisel massiivivalemina ärge unustage sisestada võrdusmärki. Kui jätate võrdusmärgi lisamata, tõlgendab Excel massiivi tekstistringina ja teie valem ei anna eeldatud tulemust. Samuti pidage meeles, et saate kasutada ka teksti ja arvude kombinatsioone.

Kui teie massiivikonstandid ei tööta, siis vaadake, kas neis ei esine ehk mõnda järgmistest levinud probleemidest.

  • Osa elemente ei pruugi olla eraldatud õige märgiga. Kui jätate koma või semikooloni vahele või paigutate selle valesse kohta, ei pruugita massiivikonstandit õigesti luua või võidakse kuvada hoiatusteade.

  • On võimalik, et valisite sellise lahtrivahemiku, mis ei vasta teie konstanti kaasatud elementide arvule. Kui valite näiteks kuuest lahtrist koosneva veeru, kuid proovite kasutada viit lahtrit sisaldavat konstanti, kuvatakse tühjas lahtris veaväärtus #N/A. Kui aga olete valinud liiga vähe lahtreid, jätab Excel ära väärtused, millele vastavaid lahtreid pole.

Järgmised näited illustreerivad mõnda massiivikonstantide massiivivalemites kasutamise võimalust. Mõned näited kasutavad funktsiooni TRANSPOSE ridade veergudeks teisendamiseks ja vastupidi.

Massiivi üksuste korrutamine

  1. Looge uus tööleht ning valige neli veergu lai ja kolm rida kõrge tühjade lahtrite plokk.

  2. Tippige järgmine valem ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter:

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

Massiivi üksuste ruutu võtmine

  1. Valige neli veergu lai ja kolm rida kõrge tühjade lahtrite plokk.

  2. Tippige järgmine massiivivalem ja vajutage klahvikombinatsiooni Ctrl+Shift+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}

    Teise võimalusena sisestage massiivivalem, mis kasutab katuse tehtemärki (^):

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

Ühemõõtmelise rea transponeerimine

  1. Valige viiest tühjast lahtrist koosnev veerg.

  2. Tippige järgmine valem ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter:

    =TRANSPOSE({1\2\3\4\5})

    Ehkki sisestasite horisontaalse massiivikonstandi, teisendab funktsioon TRANSPOSE massiivikonstandi veeruks.

Ühemõõtmelise veeru transponeerimine

  1. Valige viiest tühjast lahtrist koosnev rida.

  2. Sisestage järgmine valem ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter:

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

Ehkki sisestasite vertikaalse massiivikonstandi, teisendab funktsioon TRANSPOSE konstandi reaks.

Kahemõõtmelise konstandi transponeerimine

  1. Valige kolm veergu lai ja neli rida kõrge lahtriplokk.

  2. Sisestage järgmine konstant ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter:

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

    Funktsioon TRANSPOSE teisendab iga rea veerujadaks.

Käesolevas jaotises antakse ülevaade mõne lihtsama massiivivalemi kasutamisest.

Massiivide ja massiivikonstantide loomine olemasolevate väärtuste põhjal

Järgmises näites kirjeldatakse massiivivalemite kasutamist erinevatel töölehtedel asuvate lahtrivahemike vahel linkide loomiseks. Samuti saate teada, kuidas luua massiivikonstant sama väärtustekomplekti põhjal.

Massiivi loomine olemasolevate väärtuste põhjal

  1. Valige Exceli töölehel lahtrid C8:E10 ja sisestage järgmine valem:

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

    Tippige kindlasti kõigepealt { (vasaklooksulg), enne kui tipite 10, ja } (paremlooksulg) pärast 90 sisestamist, kuna loote arvumassiivi.

  2. Vajutage klahvikombinatsiooni Ctrl+Shift+Enter, mis sisestab massiivivalemi abil selle arvumassiivi lahtrivahemikku C8:E10. Töölehel peaks C8–E10 välja nägema selline:

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Valige lahtrivahemik C1 kuni E3.

  4. Sisestage valemiribale järgmine valem ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter:

    =C8:E10

    Lahtrites C1 kuni E3 kuvatakse lahtrimassiiv 3x3 samade väärtustega, mida näete lahtrites C8–E10.

Massiivikonstandi loomine olemasolevate väärtuste põhjal

  1. Kui lahtrid C1:C3 on valitud, vajutage redigeerimisrežiimi aktiveerimiseks klahvi F2. 

  2. Lahtriviidete teisendamiseks väärtusteks vajutage klahvi F9. Excel teisendab väärtused massiivikonstandiks. Valem peaks nüüd olema ={10,20;30;40,50;60;70,80,90}.

  3. Massiivikonstandi sisestamiseks massiivivalemina vajutage klahvikombinatsiooni Ctrl+Shift+Enter.

Lahtrivahemiku märkide loendamine.

Järgmises näites kirjeldatakse lahtrivahemikus leiduvate märkide (sh tühikute) arvu loendamist.

  1. Kopeerige kogu see tabel ja kleepige töölehe lahtrisse A1.

    Andmed

    See on

    lahtrite kogum,

    mis moodustab

    kokku

    ühe lause.

    Märke kokku vahemikus A2:A6

    =SUM(LEN(A2:A6))

    Pikima lahtri sisu (A3)

    =INDEX(A2:A6;MATCH(MAX(LEN(A2:A6));LEN(A2:A6);0);1)

  2. Valige lahter A8 ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter, et näha lahtrites A2:A6 (66) märkide koguarvu.

  3. Valige lahter A10 ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter, et näha lahtrite A2:A6 (lahter A3) pikima sisu.

Lahtris A8 kasutatakse järgmist valemit, mis loendab lahtrites A2–A6 märkide koguarvu (66).

=SUM(LEN(A2:A6))

Selles näites tagastab funktsioon LEN iga selle vahemiku lahtris sisalduva tekstistringi pikkuse. Seejärel lisab funktsioon SUM need väärtused kokku ja kuvab tulemi (66).

Vahemiku n väiksema väärtuse leidmine

Selles näites kirjeldatakse kolme kõige väiksema väärtuse leidmist lahtrivahemikus.

  1. Sisestage lahtritesse A1:A11 juhuslikud arvud.

  2. Valige lahtrid C1–C3. Selles lahtrikomplektis kuvatakse massiivivalemi tagastatavad andmed.

  3. Sisestage järgmine valem ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter:

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

See valem kasutab funktsiooni SMALL kolm korda hindamiseks massiivikonstandit ja tagastab lahtrites A1:A10 sisalduvate massiivi väikseimad (1), teise väikseimad (2) ja kolmandad väikseimad (3) liikmed, et leida rohkem väärtusi, lisage konstandile veel argumente. Seejärel saate kasutada ka muid funktsioone, mis sisaldavad seda valemit, näiteks SUM või AVERAGE. Näited:

=SUM(SMALL(A1:A10;{1;2;3})

=AVERAGE(SMALL(A1:A10;{1;2;3})

Vahemiku n suurima väärtuse leidmine

Vahemiku kõige suuremate väärtuste leidmiseks võite funktsiooni SMALL asendada funktsiooniga LARGE. Lisaks kasutatakse järgmises näites ka funktsioone ROW ja INDIRECT.

  1. Valige lahtrid D1 kuni D3.

  2. Sisestage valemiribale see valem ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter:

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

Siinkohal on mõistlik anda ka põgus ülevaade funktsioonidest ROW ja INDIRECT. Funktsiooniga ROW saate luua järjestikuste täisarvude massiivi. Näiteks valige harjutustöövihikus tühi veerg, mis sisaldab 10 lahtrit, sisestage see massiivivalem ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter:

=ROW(1:10)

Valem loob kümmet järjestikust täisarvu sisaldava veeru. Võimalike probleemide kuvamiseks lisage massiivivalemit sisaldava vahemiku kohale (ehk rea 1 kohale) uus rida. Excel kohandab reaviited ja valem loob nüüd täisarvude jada 2–11. Probleemi lahendamiseks tuleb valemisse lisada funktsioon INDIRECT:

=ROW(INDIRECT("1:10"))

Funktsioon INDIRECT kasutab argumentidena tekstistringe (seetõttu on vahemik 1:10 jutumärkides). Excel ei kohanda tekstväärtusi, kui lisate ridu või teisaldate massiivivalemi. Seetõttu loob funktsioon ROW alati täpselt selle täisarvude massiivi, mida soovite.

Vaatame valemit, mida kasutasite varem – =LARGE(A5:A14;ROW(INDIRECT("1:3"))) alates sisemistest sulgudest ja töötades väljapoole. Funktsioon INDIRECT tagastab tekstiväärtuste komplekti, sel juhul väärtused 1 kuni 3. Funktsioon ROW genereerib omakorda kolmelahtmelise veerumassiivi. Funktsioon LARGE kasutab lahtrivahemiku A5:A14 väärtusi ja seda hinnatakse kolm korda, üks kord iga funktsiooni ROW tagastatud viite kohta. Väärtused 3200, 2700 ja 2000 tagastatakse kolmelahtmelisele veerumassiivile. Kui soovite leida rohkem väärtusi, lisage funktsioonile INDIRECT suurem lahtrivahemik.

Nagu varasemates näidetes, saate seda valemit kasutada ka muude funktsioonidega (nt SUM jaAVERAGE).

Pikima tekstistringi leidmine lahtrivahemikus

Minge tagasi varasema tekstistringi näite juurde, sisestage tühja lahtrisse järgmine valem ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter:

=INDEX(A2:A6;MATCH(MAX(LEN(A2:A6));LEN(A2:A6);0);1)

Kuvatakse tekst "hulk lahtreid".

Heitkem sellele valemile täpsem pilk, alustades seespoolsetest elementidest ja liikudes analüüsimisega väljapoole. Funktsioon LEN tagastab iga lahtrivahemiku A2:A6 üksuse pikkuse. Funktsioon MAX arvutab nende üksuste suurima väärtuse, mis vastab pikimale tekstistringile, mis asub lahtris A3.

Siit edasi läheb lugu veidi keerulisemaks. Funktsioon MATCH arvutab pikimat tekstistringi sisaldava lahtri nihke (suhtelise asukoha). Selleks läheb vaja kolme argumenti: otsinguväärtust, otsingumassiivi ja vastendustüüpi. Funktsioon MATCH otsib otsingumassiivist määratud otsinguväärtust. Käesoleval juhul on otsinguväärtus pikim tekstistring:

(MAX(LEN(A2:A6))

ja see string asub selles massiivis:

LEN(A2:A6)

Vastendustüübi argument on 0. Vastendustüüp võib koosneda väärtusest 1, 0 või -1. Kui määrate väärtuse 1, tagastab MATCH suurima väärtuse, mis on otsinguväärtusest väiksem või sellega võrdne. Kui määrate väärtuse 0, tagastab MATCH esimese väärtuse, mis on otsinguväärtusega täpselt võrdne. Kui määrate väärtuse -1, leiab MATCH väikseima väärtuse, mis on määratud otsinguväärtusest suurem või sellega võrdne. Kui jätate vastendustüübi ära, eeldab Excel, et vastendustüüp on 1.

Viimaks kasutab funktsioon INDEX järgmisi argumente: massiivi ning selles massiivis asuvat rea- ja veerunumbrit. Lahtrivahemik A2:A6 annab massiivi, funktsioon MATCH lahtri aadressi ja viimane argument (1) määrab, et väärtus pärineb massiivi esimesest veerust.

Käesolevas jaotises antakse ülevaade mõne keerukama massiivivalemi kasutamisest.

Veaväärtusi sisaldava vahemiku liitmine

Exceli funktsioon SUM ei toimi, kui proovite liita vahemikku, mis sisaldab mõnda veaväärtust (nt #N/A). Käesolevas näite abil näete, kuidas liita väärtused vahemikus Andmed, mis sisaldab vigu.

=SUM(IF(ISERROR(Andmed);"";Andmed))

Valem loob uue massiivi, mis sisaldab algseid väärtusi ilma veaväärtusteta. Sisemistest funktsioonidest alustades ja väljapoole liikudes otsib funktsioon ISERROR lahtrivahemikust (Andmed) vigu. Funktsioon IF tagastab kindla väärtuse, kui teie määratud tingimus annab vastuseks TRUE, ja teise väärtuse, kui tingimus annab vastuseks FALSE. Sel juhul tagastab see kõigi veaväärtuste kohta tühjad stringid (""), kuna need annavad vastuseks TRUE, ja vahemiku (Andmed) ülejäänud väärtused, kuna need annavad vastuseks FALSE, mis tähendab, et need ei sisalda veaväärtusi. Seejärel arvutab funktsioon SUM filtreeritud massiivi kogusumma.

Vahemiku veaväärtuste loendamine

See näide sarnaneb eelmise valemiga, kuid tagastab vahemikus nimega Andmed leiduvate veaväärtuste arvu, mitte ei filtreeri neid välja:

=SUM(IF(ISERROR(Andmed);1;0))

Valem loob massiivi, mis sisaldab väärtust 1 nende lahtrite jaoks, milles leidub vigu, ja väärtust 0 nende lahtrite jaoks, mis ei sisalda vigu. Soovi korral saate valemit lihtsustada ja saada sama tulemuse, kui eemaldate funktsiooni IF kolmanda argumendi, näiteks nii:

=SUM(IF(ISERROR(Andmed);1))

Kui te argumenti ei määra, tagastab funktsioon IF väärtuse FALSE, kui lahter ei sisalda veaväärtust. Vajadusel saate valemit veelgi lihtsustada:

=SUM(IF(ISERROR(Andmed)*1))

See versioon töötab, kuna TRUE*1=1 ja FALSE*1=0.

Väärtuste liitmine tingimuste põhjal

Vahel võib teil tekkida vajadus liita väärtused teatud tingimuste põhjal. Järgmine massiivivalem näiteks liidab vahemikus nimega Müük ainult positiivsed täisarvud:

=SUM(IF(Müük>0;Müük))

Funktsioon IF loob positiivsete väärtuste ja väärate väärtuste massiivi. Funktsioon SUM sisuliselt ignoreerib vääraid väärtusi, kuna 0+0=0. Selles valemis kasutatav lahtrivahemik võib koosneda suvalisest arvust ridadest ja veergudest.

Liita saate ka sellised väärtused, mis täidavad rohkem kui ühe tingimuse. Järgmine massiivivalem näiteks arvutab väärtused, mis on suuremad kui 0 ja väiksemad kui 5 või viiega võrdsed:

=SUM((Müük>0)*(Müük<=5)*(Müük))

Pidage meeles, et see valem tagastab vea, kui vahemik sisaldab vähemalt ühte mittearvulist lahtrit.

Samuti saate luua massiivivalemeid, mis kasutavad teatud tüüpi OR-tingimust. Näiteks võite liita väärtused, mis on väiksemad kui 5 ja suuremad kui 15:

=SUM(IF((Müük<5)+(Müük>15);Müük))

Funktsioon IF leiab kõik väärtused, mis on väiksemad kui 5 ja suuremad kui 15, ning edastab need siis funktsioonile SUM.

Funktsioone AND ja OR ei saa massiivivalemites otse kasutada, kuna need funktsioonid tagastavad ühe tulemi (kas TRUE või FALSE), kuid massiivifunktsioonid nõuavad tulemite massiive. Probleemi lahendamiseks võite kasutada eelmises valemis näidatud loogikat. Teisisõnu saate matemaatilisi tehteid sooritada (nt liita või korrutada) ka selliste väärtustega, mis vastavad OR- või AND-tingimusele.

Keskmise arvutamine nullid välja jättes

Selles näites kirjeldatakse nullide eemaldamist vahemikust, mille väärtuste keskmist soovite arvutada. Valemis kasutatakse andmevahemikku nimega Müük.

=AVERAGE(IF(Müük<>0;Müük))

Funktsioon IF loob massiivi väärtustest, mis ei võrdu nulliga, ja edastab need väärtused siis funktsioonile AVERAGE.

Kahe lahtrivahemiku vaheliste erinevuste arvu loendamine

See massiivivalem võrdleb lahtrivahemikes MinuAndmed ja SinuAndmed asuvaid väärtusi ning tagastab nende kahe vahemiku vaheliste erinevuste arvu. Kui mõlema vahemiku sisu on samane, tagastab valem väärtuse 0. Selle valemi kasutamiseks peavad lahtrivahemikud olema ühesuurused ja samade mõõtmetega (kui MinuAndmed on näiteks vahemik, mis koosneb 3 reast ja 5 veerust, peab ka SinuAndmed olema vahemik suurusega 3 rida korda 5 veergu):

=SUM(IF(MinuAndmed=SinuAndmed;0;1))

See valem loob uue massiivi, mis on võrreldavate vahemikega ühesuurune. Funktsioon IF täidab massiivi väärtusega 0 ja väärtusega 1 (0 lahknevuste ja 1 identsete lahtrite korral). Funktsioon SUM tagastab seejärel massiivi väärtuste summa.

Vajadusel saate valemit lihtsustada:

=SUM(1*(MyData<>YourData))

Sarnaselt valemiga, mis loendab vahemikus leiduvaid veaväärtusi, on ka see valem kasutatav, kuna TRUE*1=1 ja FALSE*1=0.

Vahemiku suurima väärtuse asukoha leidmine

See massiivivalem tagastab üheveerulise vahemiku Andmed suurima väärtuse reanumbri:

=MIN(IF(Andmed=MAX(Andmed);ROW(Andmed);""))

Funktsioon IF loob uue massiivi, mis vastab vahemikule nimega Andmed. Kui vastav lahter sisaldab vahemiku suurimat väärtust, sisaldab massiiv reanumbrit. Muul juhul sisaldab massiiv tühje stringi (""). Funktsioon MIN kasutab uut massiivi oma teise argumendina ja tagastab väikseima väärtuse, mis vastav vahemiku Andmed suurima väärtuse reanumbrile. Kui vahemik Andmed sisaldab mitut identset suurimat väärtust, tagastab valem esimese väärtuse rea.

Kui soovite tagastada suurima väärtuse tegeliku lahtriaadressi, kasutage seda valemit:

=ADDRESS(MIN(IF(Andmed=MAX(Andmed);ROW(Andmed);""));COLUMN(Andmed))

Kinnitus

Selle artikli osad põhinesid colin Wilcoxi kirjutatud Excel Power User'i veergude sarjal, mida kohandati Excel 2002. aasta valemite peatükkide 14 ja 15 põhjal Excel.

Kas vajate rohkem abi?

Võite oma küsimuse alati esitada mõnele Exceli tehnikakogukonna eksperdile, otsida abi vastustefoorumist või soovitada mõnd uut funktsiooni või täiustust Exceli User Voice’i lehel.

Vt ka

Dünaamilised massiivid ja ülevoolanud massiivide käitumine

Dünaamilised massiivivalemid vs pärand-CSE massiivivalemid

Funktsioon FILTER

Funktsioon RANDARRAY

Funktsioon SEQUENCE

Funktsioon SORT

Funktsioon SORTBY

Funktsioon UNIQUE

#SPILL! tõrked Excelis

Kaudselt ristumiskoha tehtemärk: @

Valemite ülevaade

Kas vajate veel abi?

Täiendage Office'i kasutamise oskusi
Tutvuge koolitusmaterjalidega
Kasutage uusi funktsioone enne teisi
Liituge Office Insideri programmiga

Kas sellest teabest oli abi?

Täname tagasiside eest!

Täname tagasiside eest! Tundub, et võiksime teid kokku viia ühega meie Office'i tugiagentidest, kes aitab teil probleemi lahendada.

×