Massiivivalem on valem, mille abil saab massiivi ühe või mitme üksusega teha mitu arvutust. Massiivi võite käsitleda kui väärtuserida või -veergu või väärtuseridade ja -veergude kombinatsiooni. Massiivivalemid võivad tagastada nii mitu tulemit kui ka ainult ühe tulemi.
Alates 2018. aasta septembris tehtud Microsoft 365 värskendusest toimub mis tahes valemi puhul, mis saab tagastada mitu tulemit, automaatselt ülevoolamine allapoole või naaberlahtritesse. Selle käitumise muutusega kaasneb ka mitu uut dünaamilist massiivifunktsiooni. Dünaamilised massiivivalemid, ükskõik kas need kasutavad olemasolevaid funktsioone või dünaamilisi massiivifunktsioone, tuleb sisestada ainult ühte lahtrisse ja siis tuleb see kinnitada sisestusklahvi (Enter) vajutamisega. Varasemad pärandmassiivivalemid nõuavad esmalt kogu väljundvahemiku valimist ja siis valemi kinnitamist klahvikombinatsiooniga Ctrl+Shift+Enter. Neid nimetatakse tavaliselt CSE valemiteks.
Massiivivalemid võimaldavad lahendada vägagi keerulisi ülesandeid, näiteks:
-
kiiresti luua näidisandmekomplekte;
-
loendada lahtrivahemikus sisalduvate märkide arvu;
-
liita üksnes teatud kindlatele tingimustele vastavad arvud (nt vahemiku kõige väiksemad väärtused või arvud, mis jäävad teatud ülem- ja alampiiri vahele);
-
liita iga N. väärtuse väärtuste vahemikus.
Järgmistes näidetes kirjeldatakse nii mitmelahtriliste kui ka ühelahtriliste massiivivalemite koostamist. Võimaluse korral oleme lisanud näiteid nii dünaamiliste massiivifunktsioonidega kui ka olemasolevate massiivivalemitega, mis on sisestatud nii dünaamiliste kui ka pärandmassiividena.
Näidiste allalaadimine
Laadige alla näidistöövihik, kus on kõik selles artiklis kirjeldatud massiivivalemite näited.
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
-
Siin arvutame kupeede ja sedaanide kogumüügi iga müügiesindaja kohta, sisestades lahtrisse H10 =F10:F19*G10:G19.
Kui vajutate sisestusklahvi (Enter), kuvatakse tulemused, mis on üle voolanud allapoole lahtritesse H10:H19. Pange tähele, et kui valite ülevooluvahemikus mis tahes lahtri, on ülevooluvahemik äärisega esile tõstetud. Samuti võite märgata, et lahtrites H10:H19 olevad valemid on tuhmid. Need on lisatud lihtsalt viiteks, nii et kui soovite valemit kohandada, peate valima lahtri H10, kus asub põhivalem.
-
Ühelahtriline massiivivalem
Tippige või kopeerige ja kleepige näidistöövihiku lahtrisse H20 =SUM(F10:F19*G10:G19) ja seejärel vajutage sisestusklahvi (Enter).
Selles näites korrutab Excel massiivi (lahtrivahemiku F10 kuni G19) väärtused ja liidab saadud summad seejärel funktsiooniga 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. Nagu näete, on ühelahtriline valem (lahtris H20) mitmelahtrilisest valemist (valem lahtrites H10 kuni H19) täiesti sõltumatu. See illustreerib veel ühte massiivivalemite kasutamise eelist – paindlikkust. Saate veerus H asuvaid muid valemeid muuta, ilma et see mõjutaks lahtris H20 olevat valemit. Selliseid sõltumatuid kogusummasid võib olla mõistlik kasutada ka seetõttu, et see aitab kontrollida teie tulemite täpsust.
-
Dünaamiliste massiivivalemitega kaasnevad ka järgmised eelised.
-
Ühtsus Kui klõpsate mõnda lahtrit alates lahtrist H10, näete kõikjal sama valemit. See ühtsus aitab tagada täpsuse.
-
Turvalisus. Mitmelahtrilise massiivivalemi komponente ei saa üle kirjutada. Proovige näiteks klõpsata lahtrit H11 ja vajutada kustutusklahvi (Delete). Excel ei muuda massiivi väljundit. Selle muutmiseks peate valima massiivis ülemise vasakpoolse lahtri või lahtri H10.
-
Väiksemad failimahud. Sageli saate mitme vahevalemi asemel kasutada ühte massiivivalemit. Näiteks kasutatakse automüüginäites veerus E olevate tulemite arvutamiseks üht massiivivalemit. Kui oleksite kasutanud standardvalemeid, nt =F10*G10, F11*G11, F12*G12 jne, oleksite sama tulemi arvutamiseks pidanud kasutama 11 erinevat valemit. See pole midagi erilist, aga mis oleks siis, kui teil oleks kokku tuhandeid ridu? Siis võib see olla väga suur asi.
-
Tõhusus Massiivifunktsioonid võivad olla tõhus viis keerukate valemite koostamises. Massiivivalem =SUM(F10:F19*G10:G19) on sama mis see: =SUM(F10*G10;F11*G11;F12*G12;F13*G13;F14*G14;F15*G15;F16*G16;F17*G17;F18*G18;F19*G19).
-
Ülevool Dünaamilised massiivivalemid voolavad automaatselt üle väljundvahemikku. Kui lähteandmed on Exceli tabelis, siis muudetakse andmete lisamisel või eemaldamisel automaatselt teie dünaamiliste massiivivalemite suurust.
-
Tõrketeade #SPILL! Dünaamiliste massiividega käib kaasas tõrketeade #SPILL!, mis näitab, et ettenähtud ülevooluvahemik on mingil põhjusel blokeeritud. Blokeeringu kõrvaldamise korral voolab valem automaatselt üle.
-
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 tuleb iga rea üksused eraldada längkriipsudega ja iga rida eraldada semikooloniga.
Järgmine näide aitab teil harjutada horisontaalsete, vertikaalsete ja kahemõõtmeliste konstantide loomist. Kasutame näites funktsiooni SEQUENCE, et automaatselt genereerida massiivikonstandid, ning kasutame ka käsitsi sisestatavaid massiivikonstante.
-
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 koostab ühe viie veeruga reamassiivi samamoodi nagu ={1\2\3\4\5}. Kuvatakse järgmine tulem.
-
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.
-
Kahemõõtmelise konstandi loomine
Valige mis tahes tühi lahter, mille paremal ja all on rühi ruum, ning sisestage =SEQUENCE(3;4). Kuvatakse järgmine tulem.
Võite sisestada ka: või ={1\2\3\4;5\6\7\8;9\10\11\12}, aga te peate pöörama tähelepanu sellele, kuhu paigutate semikoolonid ja längkriipsud.
Nagu näete, pakub funktsioon SEQUENCE olulisi eeliseid võrreldes massiivikonstantide väärtuste käsitsi sisestamisega. Põhiliselt säästab see aega, kuid aitab vähendada ka käsitsi sisestamisel tekkivaid vigu. Samuti on seda lihtsam lugeda, kuna semikoolonid ja längkriipsud võivad omavahel segamini minna.
Järgmises näites kasutatakse massiivikonstante suurema valemi osana. Avage näidistöövihiku tööleht Konstant valemis või looge uus tööleht.
Lahtrisse D9 sisestasime =SEQUENCE(1;5;3;1), aga te võite lahtritesse A9:H9 sisestada ka 3, 4, 5, 6, ja 7. Selle konkreetse numbrivaliku juures pole midagi erilist, valisime lihtsalt midagi muud, mis pole 1–5.
Lahtrisse E11 sisestage =SUM(D9:H9*SEQUENCE(1;5)) või =SUM(D9:H9*{1\2\3\4\5}). Valemid tagastavad väärtuse 85.
Funktsioon SEQUENCE koostab massiivikonstandi {1\2\3\4\5} ekvivalendi. Kuna Excel sooritab esmalt ümarsulgudega raamitud avaldiste tehted, on järgmised kaks elementi, mida arvutamisel arvesse võetakse, lahtriväärtused lahtrites D9:H9 ja korrutusmä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 liidab funktsioon SUM saadud väärtused ning tagastab 85.
Kui te ei soovi talletatud massiivi kasutada ning eelistate kogu tehte tervenisti mällu jätta, saate selle asendada teise 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 massiivikonstantides kasutada
-
Massiivikonstandid võivad sisaldada arve, teksti, loogikaväärtusi (nt TRUE ja FALSE) ja 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 ("text”).
-
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 massiivikonstantide kasutamiseks on neile nimed panna. 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.
Valige Valemid > Määratud nimed > Määra nimi. Väljale Nimi sisestage 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 välja nägema umbes järgmine:
Klõpsake nuppu OK. Seejärel valige suvaline kolme tühja lahtriga rida ja sisestage =Kvartal1.
Kuvatakse järgmine tulem.
Kui soovite, et tulemid voolaksid üle mitte horisontaalselt, vaid vertikaalselt, saate kasutada =TRANSPOSE(Kvartal1).
Kui soovite kuvada 12-kuulise loendi (nt finantsaruande koostamiseks), saate funktsiooni SEQUENCE abil võtta selle aluseks jooksva aasta. Hea asi selle funktsiooni juures on see, et kuigi kuvatakse ainult kuu, on selle taga kehtiv kuupäev, mida saate kasutada teistes arvutustes. Need näited leiate näidistöövihiku töölehtedelt Nimega massiivikonstantja Kiirnäidisandmestik.
=TEXT(DATE(YEAR(TODAY()),SEQUENCE(1;12);1),"mmm")
See kasutab funktsiooni DATE, et luua kuupäev jooksva aasta põhjal, SEQUENCE loob massiivikonstandi 1-st kuni 12-ni jaanuari kuni detsembri kohta ning funktsioon TEXT teisendab kuvatava vormingu kujule "mmm" (jaan, veebr, märts jne). Kui soovite kuvada kuu täisnime, nt jaanuar, kasutage vormingut "mmmm".
Nimega konstandi kasutamisel massiivivalemina ärge unustage sisestada võrdusmärki: kirjutage =Kvartal1, mitte lihtsalt Kvartal1. 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 funktsioonide, teksti ja arvude kombinatsioone. Kõik oleneb sellest, kui loominguline soovite olla.
Järgmised näited illustreerivad mõnda massiivikonstantide massiivivalemites kasutamise võimalust. Mõnes näites kasutatakse funktsiooni TRANSPOSE, et teisendada read veergudeks ja vastupidi.
-
Massiivi üksuste korrutamine
Sisestage =SEQUENCE(1;12)*2 või ={1\2\3\4;5\6\7\8;9\10\11\12}*2
Samuti saate märgiga (/) jagada, märgiga (+) liita ja märgiga (-) lahutada.
-
Massiivi üksuste ruutu võtmine
Sisestage =SEQUENCE(1;12)^2 või ={1\2\3\4;5\6\7\8;9\10\11\12}^2
-
Ruutu võetud üksuste ruutjuure leidmine massiivis
Sisestage =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
Sisestage =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
Sisestage =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
Sisestage =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 massiivivalemite abil luua olemasolevast massiivist uus massiiv.
Sisestage =SEQUENCE(3;6;10;10) või ={10\20\30\40\50\60;70\80\90\100\110\120;130\140\150\160\170\180}
Tippige kindlasti { (vasaklooksulg) enne 10 sisestamist ja } (paremlooksulg) pärast 180 sisestamist, kuna loote arvumassiivi.
Järgmiseks sisestage tühja lahtrisse =D9# või =D9:I11. Kuvatakse 3x6 lahtrimassiiv samade väärtustega, mis on kuvatud lahtrites D9:D11. Märki # nimetatakse ülevoolanud vahemiku tehtemärgiks ja sellega viitab Excel kogu massiivivahemikule, ilma et peaksite seda välja kirjutama.
-
Massiivikonstandi loomine olemasolevate väärtuste põhjal
Saate võtta ülevoolanud massiivivalemi tulemid ja teisendada selle komponentosadeks. Redigeerimisrežiimi aktiveerimiseks valige lahter D9 ja vajutage klahvi F2. Järgmiseks vajutage klahvi F9, et teisendada lahtriviited väärtusteks, mille Excel teisendab seejärel massiivikonstandiks. Kui vajutate sisestusklahvi (Enter) peaks valem =D9# nüüd olema ={10\20\30;40\50\60;70\80\90}.
-
Lahtrivahemiku märkide loendamine.
Järgmises näites kirjeldatakse lahtrivahemikus leiduvate märkide arvu loendamist. Siia kuuluvad ka tühikud.
=SUM(LEN(C9:C13))
Selles näites tagastab funktsioon LEN iga selle vahemiku lahtris sisalduva tekstistringi pikkuse. Seejärel liidab funktsioon SUM need väärtused kokku ja kuvab tulemi (66). Kui soovite saada keskmist märkide arvu, võite kasutada järgmist:
=AVERAGE(LEN(C9:C13))
-
Vahemiku C9:C13 pikima lahtri sisu
=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 lahtrivahemiku D2:D6 iga üksuse pikkuse. Funktsioon MAX arvutab nende üksuste suurima väärtuse, mis vastab pikimale tekstistringile (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)
Vastendustüübi argument on praegusel juhul 0. Vastendustüü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 otsinguväärtusega täpselt võrdne.
-
-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.
Viimaks kasutab funktsioon INDEX järgmisi argumente: massiivi ning selles massiivis asuvat rea- ja veerunumbrit. Lahtrivahemik C9:C13 on massiiv, funktsioon MATCH annab lahtri aadressi ja viimane argument (1) määrab, et väärtus pärineb massiivi esimesest veerust.
Kui soovite tuua väikseima tekstistringi sisu, peaksite ülaltoodud näites asendama MAX-i MIN-iga.
-
-
Vahemiku n väikseimate väärtuste leidmine
Selles näites kirjeldatakse kolme väikseima väärtuse leidmist lahtrivahemikus, kus lahtrites B9:B18 on loodud näidisandmete massiiv: =INT(RANDARRAY(10;1)*100). Võtke arvesse, et RANDARRAY on muutuv funktsioon, nii et iga kord, kui Excel arvutab, saate uue juhuslike arvude kogumi.
Sisestage =SMALL(B9#;SEQUENCE(D9)), =SMALL(B9:B18;{1;2;3})
See valem kasutab massiivikonstanti, et arvutada funktsiooni SMALL väärtust kolm korda ja tagastada kolm väikseimat liiget lahtrites B9:B18 sisalduvas massiivis, kus 3 on muutuv väärtus lahtris D9. Kui soovite leida rohkem väärtusi, saate suurendada funktsioonis SEQUENCE väärtust või lisada konstanti rohkem argumente. 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 kõige suuremate väärtuste leidmiseks võite 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 lahter 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 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. Võiksite samahästi kasutada funktsiooni SEQUENCE:
=SEQUENCE(10)
Vaatame valemit, mida kasutasite varem – =LARGE(B9#;ROW(INDIRECT("1:3"))) – alustades sisemistest sulgudest ja liikudes väljapoole: funktsioon INDIRECT tagastab tekstiväärtuste kogumi, praegusel juhul väärtused 1–3. Funktsioon ROW omakorda genereerib kolmelahtrilise veerumassiivi. Funktsioon LARGE kasutab väärtusi lahtrivahemikus B9:B18 ja see väärtustatakse kolm korda, üks kord iga funktsiooni ROW tagastatud viite kohta. Kui soovite leida rohkem väärtusi, lisage funktsioonile INDIRECT suurem lahtrivahemik. Nagu funktsiooni SMALL näidete puhul saate seda valemit kasutada ka koos muude funktsioonidega, näiteks SUM ja AVERAGE.
-
Veaväärtusi sisaldava vahemiku liitmine
Exceli funktsioon SUM ei toimi, kui proovite liita vahemikku, mis sisaldab mõnda veaväärtust, nt #VALUE! või #N/A. Selle näite abil näete, kuidas liita väärtusi 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.
Vahel võib teil tekkida vajadus liita väärtused teatud tingimuste põhjal.
Järgmine massiivivalem näiteks liidab vahemikus nimega Müük, mida tähistavad ülalolevas näites lahtrid E9:E24, 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. See massiivivalem näiteks arvutab väärtused, mis on suuremad kui 0 JA väiksemad 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äiksemad 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 matemaatilisi tehteid sooritada (nt liita või korrutada) ka selliste väärtustega, mis vastavad OR- või AND-tingimusele.
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 ü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*(MinuAndmed<>SinuAndmed))
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ühja 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))
Sarnaseid näiteid leiate näidistöövihikust töölehelt 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 allolev tabel ja kleepige see tühja töölehe lahtrisse A1.
|
Müügiesindaja |
Autotüüp |
Müüdud kogus |
Ü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) |
-
Iga müügiesindaja kupeede ja sedaanide müügisumma (Müük kokku) nägemiseks valige lahtrid E2:E11, sisestage valem =C2:C11*D2:D11 ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter.
-
Kõikide müükide kogusumma nägemiseks valige lahter F11, sisestage valem =SUM(C2:C11*D2:D11) ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter.
Kui vajutate klahvikombinatsiooni Ctrl+Shift+Enter, ümbritseb Excel valemi looksulgudega ({ }) ja lisab valemi eksemplari valitud vahemiku igasse lahtrisse. 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}.
-
Ühte lahtrit hõlmava massiivivalemi koostamine
Tippige töölehe lahtrisse D13 järgmine valem ja vajutage siis klahvikombinatsiooni Ctrl+Shift+Enter:
=SUM(C2:C11*D2:D11)
Selles näites korrutab Excel massiivi (lahtrivahemiku C2 kuni D11) väärtused ja liidab saadud summad seejärel funktsiooniga 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.
Nagu näete, on ühelahtriline valem (lahtris D13) mitmelahtrilisest valemist (valem lahtrites E2 kuni E11) täiesti sõltumatu. See illustreerib veel ühte massiivivalemite kasutamise eelist – paindlikkust. Saate veerus E asuvaid valemeid muuta või selle veeru hoopis kustutada, ilma lahtris D13 asuva valemi käitumist mõjutamata.
Massiivivalemitega kaasnevad ka järgmised eelised.
-
Ühtsus 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. Proovige näiteks klõpsata lahtrit E3 ja vajutada kustutusklahvi (Delete). Teil tuleb valida terve lahtrivahemik (E2 kuni E11) ja muuta kogu massiivi valemit või jätta massiiv selle praegusele kujule. Täiendava turbemeetmena peate valemi muutmise kinnitamiseks vajutama klahvikombinatsiooni Ctrl+Shift+Enter.
-
Väiksemad failimahud. Sageli saate mitme vahevalemi asemel kasutada ühte massiivivalemit. Käesolev töövihik näiteks kasutab tulemite arvutamiseks veerus E ühte massiivivalemit. Kui oleksite kasutanud standardvalemeid (nt =C2*D2; C3*D3; C4*D4…), oleks teil samade tulemite arvutamiseks vaja läinud 11 erinevat valemit.
Üldiselt kasutavad massiivivalemid standardset valemisüntaksit. Kõik valemid algavad võrdusmärgiga (=) ja massiivivalemites saate kasutada ka enamikku Exceli sisefunktsioone. Peamine erinevus on see, et massiivivalemi kasutamisel tuleb valemi sisestamiseks vajutada klahvikombinatsiooni Ctrl+Shift+Enter. Sel juhul ümbritseb Excel massiivivalemi looksulgudega – kui tipite looksulud käsitsi, teisendatakse valem tekstistringiks ja see ei tööta.
Massiivifunktsioonid võivad olla tõhus viis keerukate valemite koostamises. 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!: Alati, kui soovite sisestada massiivivalemit, vajutage klahvikombinatsiooni Ctrl+Shift+Enter. 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) jaseejärel vajutage kustutusklahvi (Delete).
-
Mitmelahtrilisse massiivivalemisse ei saa tühje lahtreid lisada. Samuti ei saa valemist lahtreid 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 kui olete valemivahemiku kohandamise lõpetanud, vajutage valemi kinnitamiseks klahvikombinatsiooni CTRL+SHIFT+ENTER. Oluline on valida kogu vahemik, alustades massiivi vasakus ülanurgas asuvast lahtrist. Ülemine vasakpoolne lahter on see, mida redigeeritakse.
Massiivivalemid on küll väga toredad, kuid nende kasutamisel on siiski ka teatavaid puuduseid.
-
Vahel võite unustada, et vajutada tuleb 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. Juhul, kui teie töövihikuid peavad muutma ka teised inimesed, võiksite seetõttu kas massiivivalemite kasutamist vältida või veenduda, et need kasutajad oleksid massiivivalemitest teadlikud ja mõistaksid, kuidas neid valemeid vajadusel 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}
Seda te juba teate, et massiivivalemite loomisel tuleb alati vajutada klahvikombinatsiooni Ctrl+Shift+Enter. Kuna massiivikonstandid on üks massiivivalemite komponente, tuleb konstandid looksulgudega ümbritseda käsitsi ehk looksulud ise valemiribale tippida. Seejärel tuleb terve valemi sisestamiseks vajutada 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.
Üherealine massiiv on järgmine: {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}. Kaherealises massiivis on esimese rea väärtused 1, 2, 3 ja 4 ning teise rea väärtused 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
-
Valige tühjal töölehel lahtrid A1 kuni E1.
-
Sisestage valemiribale järgmine valem ja vajutage siis klahvikombinatsiooni Ctrl+Shift+Enter:
={1\2\3\4\5}
Praegusel juhul peaksite tippima algus- ja lõpulooksulud ({ }) ning Excel lisab teise kogumi teie eest.
Kuvatakse järgmine tulem.
Vertikaalse konstandi loomine
-
Valige töövihikus viiest lahtrist koosnev veerg.
-
Sisestage valemiribale järgmine valem ja vajutage siis klahvikombinatsiooni Ctrl+Shift+Enter:
={1;2;3;4;5}
Kuvatakse järgmine tulem.
Kahemõõtmelise konstandi loomine
-
Valige töövihikus nelja veeru laiune ja kolme rea kõrgune lahtriplokk.
-
Sisestage valemiribale järgmine valem ja vajutage siis klahvikombinatsiooni Ctrl+Shift+Enter:
={1\2\3\4;5\6\7\8;9\10\11\12}
Kuvatakse järgmine tulem.
Konstantide kasutamine valemites
Konstante kasutatakse järgmises lihtsas näites.
-
Looge näidistöövihikus uus tööleht.
-
Tippige lahtrisse A1 arv 3. Seejärel tippige arv 4 lahtrisse B1, 5 lahtrisse C1, 6 lahtrisse D1 ja 7 lahtrisse E1.
-
Tippige lahtrisse A3 järgmine valem ja vajutage siis 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.
Lahtris A3 kuvatakse väärtus 85.
Järgmises jaotises selgitatakse valemi tööpõhimõtteid.
Valem, mida te just kasutasite, koosneb mitmest osast.
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. Samuti pidage meeles, et pärast konstandi lisamist massiivivalemisse tuleb valemi sisestamiseks vajutada 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})
Kui soovite seda ise järele proovida, siis kopeerige funktsioon, valige töölehel tühi lahter, kleepige valem valemiribale ja vajutage siis 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 massiivikonstantide kasutamiseks on neile nimed panna. 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.
-
Klõpsake menüü Valemid jaotise Määratud nimed nuppu Nime määratlus. Kuvatakse dialoogiboks Nime määramine.
-
Väljale Nimi tippige Kvartal1.
-
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.
-
Klõpsake nuppu OK ja valige töölehel kolmest tühjast lahtrist koosnev rida.
-
Tippige valemiribale järgmine valem ja vajutage siis klahvikombinatsiooni Ctrl+Shift+Enter.
=Kvartal1
Kuvatakse järgmine tulem.
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 olete mõne längkriipsu või semikooloni ära jätnud või valesse kohta pannud, ei pruugi massiivikonstandi loomine õnnestuda või kuvatakse 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õnes näites kasutatakse funktsiooni TRANSPOSE, et teisendada read veergudeks ja vastupidi.
Massiivi üksuste korrutamine
-
Looge uus tööleht ning valige neli veergu lai ja kolm rida kõrge tühjade lahtrite plokk.
-
Tippige valemiribale järgmine valem ja vajutage siis klahvikombinatsiooni Ctrl+Shift+Enter:
={1\2\3\4;5\6\7\8;9\10\11\12}*2
Massiivi üksuste ruutu võtmine
-
Valige neli veergu lai ja kolm rida kõrge tühjade lahtrite plokk.
-
Tippige valemiribale järgmine massiivivalem ja vajutage siis 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
-
Valige viiest tühjast lahtrist koosnev veerg.
-
Tippige valemiribale järgmine valem ja vajutage siis klahvikombinatsiooni Ctrl+Shift+Enter:
=TRANSPOSE({1\2\3\4\5})
Ehkki sisestasite horisontaalse massiivikonstandi, teisendab funktsioon TRANSPOSE massiivikonstandi veeruks.
Ühemõõtmelise veeru transponeerimine
-
Valige viiest tühjast lahtrist koosnev rida.
-
Tippige valemiribale järgmine valem ja vajutage siis klahvikombinatsiooni Ctrl+Shift+Enter:
=TRANSPOSE({1;2;3;4;5})
Ehkki sisestasite vertikaalse massiivikonstandi, teisendab funktsioon TRANSPOSE konstandi reaks.
Kahemõõtmelise konstandi transponeerimine
-
Valige kolm veergu lai ja neli rida kõrge lahtriplokk.
-
Tippige valemiribale järgmine konstant ja vajutage siis 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
-
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.
-
Vajutage klahvikombinatsiooni Ctrl+Shift+Enter. Sellega sisestatakse see arvumassiiv lahtrivahemikku C8:E10, kasutades massiivivalemit. Teie töölehel peaks vahemik C8 kuni E10 välja nägema selline:
10
20
30
40
50
60
70
80
90
-
Valige lahtrivahemik C1 kuni E3.
-
Tippige valemiribale järgmine valem ja vajutage siis klahvikombinatsiooni Ctrl+Shift+Enter:
=C8:E10
Lahtrites C1 kuni E3 kuvatakse 3x3 lahtrimassiiv, mille väärtused on lahtritega C8 kuni E10 samad.
Massiivikonstandi loomine olemasolevate väärtuste põhjal
-
Kui lahtrid C1:C3 on valitud, vajutage redigeerimisrežiimi aktiveerimiseks klahvi F2.
-
Et teisendada lahtriviited väärtusteks, vajutage klahvi F9. Excel teisendab väärtused massiivikonstandiks. Valem peaks nüüd olema ={10\20\30;40\50\60;70\80\90}.
-
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.
-
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)
-
Valige lahter A8 ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter, et näha lahtrite A2:A6 märkide koguarvu (66).
-
Valige lahter A10 ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter, et näha vahemiku A2:A6 lahtri kõige pikemat sisu (lahter A3).
Järgmine lahtris A8 kasutatav valem loendab märkide koguarvu (66) lahtrites A2 kuni A6.
=SUM(LEN(A2:A6))
Selles näites tagastab funktsioon LEN iga selle vahemiku lahtris sisalduva tekstistringi pikkuse. Seejärel liidab 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.
-
Sisestage lahtritesse A1:A11 juhuslikud arvud.
-
Valige lahtrid C1 kuni C3. Selles lahtrikomplektis kuvatakse massiivivalemi tagastatavad andmed.
-
Tippige valemiribale järgmine valem ja vajutage siis klahvikombinatsiooni Ctrl+Shift+Enter:
=SMALL(A1:A11;{1;2;3})
Selles valemis kasutatakse massiivikonstanti funktsiooni SMALL väärtustamiseks kolm korda ja selleks, et tagastada väikseim (1), väiksuselt järgmine (2) ja väiksuselt kolmas (3) liige massiivis, mis asub lahtrites A1:A10. Kui soovite leida rohkem väärtusi, tuleb konstanti lisada rohkem 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.
-
Valige lahtrid D1 kuni D3.
-
Sisestage valemiribale järgmine valem ja vajutage siis 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. Valige näiteks oma harjutustöövihikus kümnest lahtrist koosnev tühi veerg, 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"))) – alustades sisemistest sulgudest ja liikudes väljapoole: funktsioon INDIRECT tagastab tekstiväärtuste kogumi, praegusel juhul väärtused 1–3. Funktsioon ROW omakorda genereerib kolmelahtrilise veerumassiivi. Funktsioon LARGE kasutab väärtusi lahtrivahemikus A5:A14 ja see väärtustatakse kolm korda, üks kord iga funktsiooni ROW tagastatud viite kohta. Väärtused 3200, 2700 ja 2000 tagastatakse kolmelahtrilisele veerumassiivile. Kui soovite leida rohkem väärtusi, lisage funktsioonile INDIRECT suurem lahtrivahemik.
Nagu varasemate näidete puhul saate seda valemit kasutada ka koos muude funktsioonidega, näiteks SUM ja AVERAGE.
Pikima tekstistringi leidmine lahtrivahemikus
Minge tagasi varasemasse tekstistringi näitesse, 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 "lahtrivahemik, mis".
Heitkem sellele valemile täpsem pilk, alustades seespoolsetest elementidest ja liikudes analüüsimisega väljapoole. Funktsioon LEN tagastab lahtrivahemiku A2:A6 iga üksuse pikkuse. Funktsioon MAX arvutab nende üksuste seas suurima väärtuse, mis vastab lahtris A3 olevale pikimale tekstistringile.
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 on massiiv, funktsioon MATCH annab 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*(MinuAndmed<>SinuAndmed))
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))
Tänusõnad
Mõned selle artikli osad põhinevad Exceli lauskasutajatele mõeldud arvamuslugude sarjal, mille on kirjutanud Colin Wilcox, mugandades Exceli endise tippspetsialisti John Walkenbachi kirjutatud raamatu „Excel 2002 Formulas“ („Excel 2002 valemid”) 14. ja 15. peatükki.
Kas vajate rohkem abi?
Kui teil on küsimusi, saate need esitada Exceli tehnikakogukonnafoorumis, kus teile vastavad asjatundjad, või teistele kasutajatele kogukonnafoorumis.
Lisateave
Dünaamilised massiivid ja ülevoolanud massiivide käitumine
