Massiivivalemite juhised ja näited

Massiivivalemite juhised ja näited

Kui soovite saada kogenud Exceli kasutajaks, tuleb teil õppida kasutama ka massiivivalemeid, mille abil saab teha selliseid arvutusi, mida teised valemid ei võimalda. Järgmine artikkel põhineb Exceli lauskasutajatele mõeldud arvamuslugude sarjal, mille on kirjutanud Colin Wilcox, mugandades Exceli tippspetsialisti John Walkenbachi kirjutatud raamatu Excel 2002 Formulas („Excel 2002 valemid”) 14. ja 15. peatükki.

Massiivivalemite ülevaade

Massiivivalemeid nimetatakse vahel ka CSE-valemiteks (klahvikombinatsiooni Ctrl+Shift+Enter järgi), kuna lihtsalt sisestusklahvi (Enter) vajutamise asemel tuleb valemi sisestamiseks vajutada klahvikombinatsiooni Ctrl+Shift+Enter.

Miks tuleks massiivivalemeid kasutada?

Kui olete Excelis varem juba valemeid kasutanud, siis teate, et saate nende abil teha üpris keerukaid arvutusi. Nii näiteks saate valemite abil arvutada laenu kogumaksumuse teatud aastate jooksul. Massiivivalemid võimaldavad lahendada vägagi keerulisi ülesandeid, näiteks:

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

Massiivid ja massiivivalemid: sissejuhatus

Massiivivalem on valem, mille abil saab massiivi ühe või mitme üksusega teha mitu arvutust. Massiivi võite käsitleda kui väärtuserida, väärtuseveergu või väärtuseridade ja -veergude kombinatsiooni. Massiivivalemid võivad tagastada nii mitu tulemit kui ka ainult ühe tulemi. Näiteks võite massiivivalemi paigutada lahtrivahemikku ja kasutada seda valemit vahekokkuvõtete veeru või rea arvutamiseks. Samuti võite massiivivalemi paigutada ühte lahtrisse ja seejärel arvutada ühe summa. Mitmes lahtris asuvat massiivivalemit nimetatakse mitmelahtriliseks valemiks, ühes lahtris asuvat massiivivalemit aga ühelahtriliseks valemiks.

Järgmise jaotise näidetes kirjeldatakse nii mitmelahtriliste kui ka ühelahtriliste massiivivalemite koostamist.

Proovige järele!

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 on brauseris manustatud töövihik. Ehkki see sisaldab näidisandmeid, peaksite teadma, et manustatud töövihikus ei saa massiivivalemeid luua ega muuta – selleks peate kasutama Exceli töölauarakendust. Küll aga saate manustatud töövihikus vaadata näidisvastuseid koos tekstiga, mis selgitab massiivivalemi tööpõhimõtteid. Selleks, et massiivivalemeid päriselt tundma õppida, peate töövihiku siiski Excelis avama.

Mitmelahtrilise massiivivalemi koostamine

  1. Kopeerige kogu allolev tabel ja kleepige see Exceli tühja töölehe lahtrisse A1.

    Müügi-
    esindaja

    Auto-
    tüü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)

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

  3. Kõikide müükide kogusumma nägemiseks valige lahter F11, sisestage valem =SUM(C2:C11*D2:D11) ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter.

Selle töövihiku allalaadimiseks klõpsake töövihiku allservas mustal ribal asuvat Exceli nuppu. Seejärel saate faili Excelis avada, valida massiivivalemit sisaldavad lahtrid ja vajutada klahvikombinatsiooni Ctrl+Shift+Enter, et valem töötaks.

Excelis töötades veenduge, et Leht1 oleks aktiivne, ja valige siis lahtrid E2:E11. Vajutage klahvi F2 ja tippige aktiivsesse lahtrisse E2 valem =C2:C11*D2:D11. Kui vajutate sisestusklahvi (Enter), siis näete, et valem sisestatakse üksnes lahtrisse E2 ja seal kuvatakse tulem 165000. Seetõttu vajutage pärast valemi tippimist sisestusklahvi asemel hoopis klahvikombinatsiooni Ctrl+Shift+Enter. Nüüd kuvatakse tulemid lahtrites E2:E11. Pöörake tähelepanu valemiribale, kus valem on kuvatud kujul {=C2:C11*D2:D11}. See annab teile teada, et tegemist on massiivivalemiga, nagu on näidatud järgmises tabelis.

Klahvikombinatsiooni Ctrl+Shift+Enter vajutamisel ümbritseb Excel valemi looksulgudega ({ }) ja lisab valemi eksemplari igasse valitud vahemiku 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}.

Summad veerus E on arvutatud massiivivalemiga

Ühte lahtrit hõlmava massiivivalemi koostamine

Tippige töölehe lahtrisse F10 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 G11) 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 G11 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 uuesti vajutama klahvikombinatsiooni Ctrl+Shift+Enter.

  • Väiksemad failimahud.    Sageli saate mitme vahepealse valemi asemel kasutada ühtainsat 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.

Massiivivalemite süntaks

Üldjuhul kasutavad massiivivalemid standardset valemisüntaksit. Kõik valemid algavad võrdusmärgiga (=) ja massiivivalemites saate kasutada ka enamikku Exceli sisefunktsioone. Põhierinevus on selles, et massiivivalemi kasutamisel peate valemi sisestamiseks vajutama valemiribal klahvikombinatsiooni Ctrl+Shift+Enter. Seejärel ümbritseb Excel teie massiivivalemi looksulgudega. Kui tipite looksulud käsitsi, teisendatakse teie valem tekstistringiks ega toimi.

Massiivifunktsioonid aitavad luua keerukaid valemeid. 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).

Massiivivalemite sisestamine ja muutmine

Oluline!    Vajutage klahvikombinatsiooni Ctrl+Shift+Enter alati, kui teil on vaja massiivivalem sisestada või seda redigeerida. 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 terve valem (nt =C2:C11*D2:D11), vajutage kustutusklahvi (Delete) ja seejärel vajutage klahvikombinatsiooni Ctrl+Shift+Enter.

  • Mitmelahtrilisse massiivivalemisse ei saa tühje lahtreid lisada. Samuti ei saa valemist lahtreid kustutada.

Massiivivalemi laiendamine

Vahel võib teil tekkida vajadus massiivivalemit laiendada. See protsess pole küll keeruline, kuid täitke kindlasti eeltoodud juhised.

Sellel töölehel oleme lisanud veel mõne müügirea (read 12 kuni 17). Soovime massiivivalemeid värskendada, et kaasata ka need uued read.

Tehke seda kindlasti Exceli töölauarakenduses (pärast töövihiku allalaadimist arvutisse).

Massiivivalemi laiendamine

  1. Kopeerige kogu see tabel Exceli töölehe lahtrisse A1.

    Müügi-
    esindaja

    Auto-
    tüüp

    Müüdud
    kogus

    Ühiku
    hind

    Müük
    kokku

    Vares

    Sedaan

    5

    33000

    165000

    Kupee

    4

    37000

    148000

    Kana

    Sedaan

    6

    24000

    144000

    Kupee

    8

    21000

    168000

    Savi

    Sedaan

    3

    29000

    87000

    Kupee

    1

    31000

    31000

    Post

    Sedaan

    9

    24000

    216000

    Kupee

    5

    37000

    185000

    Lill

    Sedaan

    6

    33000

    198000

    Kupee

    8

    31000

    248000

    Kuusk

    Sedaan

    2

    27000

    Kupee

    3

    30000

    Luik

    Sedaan

    4

    22000

    Kupee

    1

    41000

    Hunt

    Sedaan

    5

    32000

    Kupee

    3

    36000

    Kogusumma

  2. Valige lahter E18, sisestage lahtrisse A20 kogusumma valem =SUM(C2:C17*D2:D17) ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter.
    Vastus peaks olema 2 131 000.

  3. Valige praegust massiivivalemit sisaldav lahtrivahemik (E2:E11) ja tühjad lahtrid (E12:E17), mis asuvad uute andmete kõrval. Teisisõnu valige lahtrid E2:E17.

  4. Redigeerimisrežiimi aktiveerimiseks vajutage klahvi F2.

  5. Asendage valemiribal C11 lahtriviitega C17 ja D11 lahtriviitega D17 ning vajutage siis klahvikombinatsiooni Ctrl+Shift+Enter.
    Excel värskendab valemit lahtrites E2 kuni E11 ja paigutab sama valemi ka uutesse lahtritesse E12 kuni E17.

  6. Tippige lahtrisse F17 massiivivalem = SUM(C2:C17*D2*D17), mis viitab lahtritele ridades 2 kuni 17, ja vajutage siis massiivivalemi sisestamiseks klahvikombinatsiooni Ctrl+Shift+Enter.
    Uus kogusumma peaks olema 2 131 000.

Massiivivalemite puudused

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

  • Vahel võite unustada, et tuleb 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. 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.

Lehe algusse

Massiivikonstantide ülevaade

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.

Lehe algusse

Ühemõõtmeliste ja kahemõõtmeliste konstantide loomine

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

Horisontaalse konstandi loomine

  1. Kasutage eelmistes näidetes loodud töövihikut või looge uus töövihik.

  2. Valige lahtrid A1 kuni E1.

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

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

    Käesoleval juhul peaksite tippima nii algus- kui ka lõpulooksulud ({ }).

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

    Massiivikonstandiga massiivivalem

    Lahtris A3 kuvatakse väärtus 85.

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

Massiivikonstandi süntaks

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

Elemendid, mida saate konstantides kasutada

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.

Massiivikonstantidele nimede panemine

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

  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 valemiribale järgmine valem ja vajutage siis 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.

Massiivikonstantide tõrkeotsing

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.

Massiivikonstantide kasutamine

Järgmised näited illustreerivad mõnda massiivikonstantide massiivivalemites kasutamise võimalust. Mõnes näites kasutatakse funktsiooni TRANSPOSE ridade teisendamiseks veergudeks 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 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

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

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

  1. Valige viiest tühjast lahtrist koosnev veerg.

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

  1. Valige viiest tühjast lahtrist koosnev rida.

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

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

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

Lehe algusse

Lihtsate massiivivalemite kasutamine

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

  3. Valige lahtrivahemik C1 kuni E3.

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

  1. Kui lahtrid C1:C3 on valitud, vajutage redigeerimisrežiimi aktiveerimiseks klahvi F2.
    Massiivi valem peaks olema ikkagi = C8:E10.

  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} (täpselt nagu lahtrites C8:E10)

  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.

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

  3. Valige lahter A12 ja vajutage klahvikombinatsiooni Ctrl+Shift+Enter, et näha vahemiku A2:A6 lahtri kõige pikemat sisu (lahter A3).

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)

Järgmine lahtris A9 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) lahtris A9, kuhu sisestasite valemi.

Vahemiku n väiksema väärtuse leidmine

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

  1. Valige lahtrid A16 kuni A18.
    Selles lahtrikomplektis kuvatakse massiivivalemi tagastatavad andmed.

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

    =SMALL(A5:A14,{1;2;3})

Lahtrites A16 kuni A18 kuvatakse vastavalt väärtused 400, 475 ja 500.

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 ning vahemikule A12:A14 tuleb lisada soovitud lisatulemite arvule vastav arv tulemilahtreid. Seejärel saate kasutada ka muid funktsioone, mis sisaldavad seda valemit, näiteks SUM või AVERAGE. Näited:

=SUM(SMALL(A 5 :A1 4 ,{1;2;3}))

=AVERAGE(SMALL(A 5 :A1 4 ,{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 A1 kuni A3.

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

    =LARGE(A5:A14,ROW(INDIRECT("1:3")))

Lahtrites A1 kuni A13 kuvatakse vastavalt väärtused 3200, 2700 ja 2000.

Siinkohal on mõistlik anda ka põgus ülevaade funktsioonidest ROW ja INDIRECT. Funktsiooniga ROW saate luua järjestikuste täisarvude massiivi. Valige oma harjutustöövihikus näiteks kümnest lahtrist koosnev tühi veerg, sisestage lahtritesse A5:A14 järgmine massiivivalem ja vajutage siis 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.

Heitkem pilk valemile, mida te just veidi aja eest kasutasite – =LARGE(A5:A14;ROW(INDIRECT("1:3"))) –, alustades sisemistest sulgudest ja liikudes analüüsimisega seestpoolt väljapoole: funktsioon INDIRECT tagastab tekstväärtuste komplekti, mis käesoleval juhul on väärtused 1 kuni 3. Funktsioon ROW omakorda genereerib kolmest lahtrist koosneva veerumassiivi. Funktsioon LARGE kasutab lahtrivahemiku A5:A14 väärtusi ja seda väärtustatakse kolm korda, üks kord iga funktsiooni ROW tagastatava viite kohta. Kolmelahtrilisse veerumassiivi tagastatakse väärtused 3200, 2700 ja 2000. Kui soovite leida rohkem väärtusi, tuleb funktsiooni INDIRECT lisada suurem lahtrivahemik.

Seda valemit saate kasutada ka koos muude funktsioonidega, näiteks SUM ja AVERAGE.

Pikima tekstistringi leidmine lahtrivahemikus

Valemit saab kasutada ainult juhul, kui andmevahemik sisaldab ainult ühte lahtriveergu. Sisestage töölehel Leht3 lahtrisse A16 järgmine valem ja vajutage siis klahvikombinatsiooni Ctrl+Shift+Enter:

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

Lahtris A16 kuvatakse tekst "lahtrivahemik, mis".

Heitkem sellele valemile täpsem pilk, alustades seespoolsetest elementidest ja liikudes analüüsimisega väljapoole. Funktsioon LEN tagastab iga lahtrivahemiku A6:A9 üksuse pikkuse. Funktsioon MAX arvutab nende üksuste seast suurima väärtuse, mis vastab kõige pikemale tekstistringile, mis asub lahtris A7.

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( A6 : A9 ))

ja see string asub selles massiivis:

LEN( A6:A9 )

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 A6:A9 on massiiv, funktsioon MATCH annab lahtri aadressi ja viimane argument (1) määrab, et väärtus pärineb massiivi esimesest veerust.

Lehe algusse

Keerukate massiivivalemite kasutamine

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

Lehe algusse

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

Valemite ülevaade

Täiendage oma 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.

×