Massiivivalemite juhised ja näited

Massiivivalemite juhised ja näited

Massiivivalem on valem, mille abil saab massiivis ühes või mitmes üksuses sooritada mitu arvutust. Massiivist saate mõelda rea või veeruna või väärtuste ridade ja veergude kombinatsioonina. Massiiv-valemid võivad tagastada kas mitu tulemit või ühe tulemi.

Alates Office 365septembris 2018 värskendusest saab iga valem, mis võib tagastada mitu tulemit, automaatselt neid kas alla või üle viia naabruses asuvatele lahtritele. Selle käitumise muutmisega kaasneb ka mitu uut dünaamilist massiivi funktsiooni. Dünaamilised massiivsed valemid, olenemata sellest, kas need kasutavad olemasolevaid funktsioone või dünaamilisi massiive, peavad olema sisestatud ainult ühte lahtrisse ja seejärel vajutage sisestusklahvi (ENTER). Varasem, pärand massiivide valemid nõuavad esmalt kogu väljundi vahemiku valimist ja seejärel kinnitada valemit klahvikombinatsiooni CTRL + SHIFT + ENTERabil. Nad on tavaliselt viidatud kui CSE valemiteks valemid.

Keerukate toimingute tegemiseks saate kasutada ka järgmisi massiivide valemeid.

  • Loo kiiresti proovi andmekogud.

  • Loendage lahtrivahemikus olevate märkide arv.

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

  • Summeerige väärtuste vahemikus iga n-väärtus.

Järgmistes näidetes kirjeldatakse, kuidas luua mitme lahtri ja ühe lahtriga massiivide valemeid. Võimaluse korral oleme lisanud näited dünaamiliste massiivi funktsioonidest ja nii dünaamiliste kui ka varasemate massiivide jaoks sisestatud massiivsed valemid.

Näidiste allalaadimine

Laadige alla näide töövihik, kus on kõik selle artikli massiivse valemi 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

    Mitme lahtrivahemiku funktsioon lahtris H10 = F10: f19 * G10: G19 müüdavate autode arvu arvutamine

  • Siin oleme arvutamas iga müügiisiku riigipöörete ja sedaanide müügi kogusummat, sisestades = F10: f19 * G10: G19 lahtris H10.

    Kui vajutate sisestusklahvi ( Enter), kuvatakse tulemid allapoole lahtritele H10: H19. Pange tähele, et lekete vahemik on esile tõstetud äärisega, kui valite lekete vahemikus suvalise lahtri. Võite märgata ka seda, et lahtrites H10: H19 olevad valemid on tuhmid. Nad on seal ainult viitamiseks, nii et kui soovite valemit reguleerida, peate valima lahtri H10, kus põhivalem elab.

  • Üksik-lahtriline massiivivalem

    Ühe lahtriga massiiv, mille alusel arvutatakse kogusumma = SUM (F10: f19 * G10: G19)

    Tippige või kopeerige ja kleepige lahtrisse H20 (nt töövihik ) = SUM (F10: f19 * G10: G19)ja seejärel vajutage sisestusklahvi ( Enter).

    Sellisel juhul korrutab Excel massiivis olevad väärtused (lahtrivahemiku F10 G19 kaudu) ja seejärel kasutab funktsiooni SUM koos kogusummade liitmiseks. 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 ühe lahtri valem ei sõltu mitmest lahtrist (valem lahtrites H10 kaudu H19). See illustreerib veel ühte massiivivalemite kasutamise eelist – paindlikkust. Veerus H olevaid muid valemeid ei saa H20 valemit mõjutamata muuta. Samuti võib olla hea tava sõltumatute kogusummade leidmiseks, sest see aitab valideerida teie tulemite täpsust.

  • Dünaamilised massiivsed valemid pakuvad ka järgmisi eeliseid.

    • Konsistentsus (Consistency)    Kui klõpsate mõnda lahtrist H10 allapoole, kuvatakse sama valem. See ühtsus aitab tagada täpsuse.

    • Turvalisus.    Mitmest lahtrist koosneva massiivivalemina komponenti ei saa üle kirjutada. Klõpsake näiteks käsku lahtri H11 ja vajutage kustutusklahvi (DELETE). Excel ei muuda massiivi väljundit. Selle muutmiseks tuleb valida massiivis ülemine vasakpoolne lahter või lahter H10.

    • Väiksema suurusega failid    Mitme vahepealse valemi asemel saate sageli kasutada ühte massiivi valemit. Näiteks kasutab autode müügi näide ühte massiivivalemina veeru E tulemite arvutamiseks. Kui kasutasite tavalisi valemeid (nt = F10 * G10, F11 * G11, F12 * G12 jne), oleks samade tulemite arvutamiseks kasutatud 11 erinevat valemit. See pole suur asi, aga kui sul oleks kokku tuhandeid ridu? Siis võib see suur erinevus olla.

    • Tõhusus    Massiivide funktsioonid võivad keerukate valemite koostamiseks olla tõhus viis. Massiivivalem = SUM (F10: f19 * G10: G19) on sama: = SUM (F10 * G10, F11 * G11, F12 * G12, F13 * G13, F14 * G14, F15 * G15, F16 * G16, F17 * G17, F18 * G18, f19 * G19).

    • Kallab    Dünaamilised massiivsed valemid valguvad automaatselt väljundi vahemikku. Kui teie lähteandmed on Exceli tabelis, muudab teie dünaamilised massiivid andmete lisamisel või eemaldamisel automaatselt suurust.

    • #SPILL! tõrge    Dünaamilised massiivid tutvustasid #SPILL! tõrge, mis näitab, et soovitud lekete vahemik on mingil põhjusel blokeeritud. Kui kõrvaldate ummistuse, on valem automaatselt kõrvalmõjuks.

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 Piiritlege iga rea üksused komaga ja Piiritlege iga rida semikoolonitega.

Järgmine näide aitab teil harjutada horisontaalsete, vertikaalsete ja kahemõõtmeliste konstantide loomist. Näitame näiteid funktsiooni SEQUENCE abil massiivide konstantide automaatseks genereerimiseks ning käsitsi sisestatud massiivide konstantide jaoks.

  • Horisontaalse konstandi loomine

    Kasutage eelmistes näidetes loodud töövihikut või looge uus töövihik. Valige suvaline tühi lahter ja sisestage = SEQUENCE (1; 5). Funktsioon SEQUENCE ehitab ühe rea võrra 5 veeru massiiviga sama, mis = {1, 2, 3, 4, 5}. Kuvatakse järgmine tulem.

    Saate luua horisontaalse massiivi konstandi = SEQUENCE (1; 5) või = {1, 2, 3, 4, 5}

  • Vertikaalse konstandi loomine

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

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

  • Kahemõõtmelise konstandi loomine

    Valige tühi lahter, millel on ruumi paremale ja all ning sisestage = SEQUENCE (3; 4). Kuvatakse järgmine tulem.

    3 rea loomine 4 veeru massiivi konstandiga = SEQUENCE (3; 4)

    Võite ka sisestada: või = {1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12}, kuid te soovite pöörata tähelepanu sellele, kuhu soovite semikooloneid lisada.

    Nagu näete, pakub funktsioon SEQUENCE teie massiivi konstantsete väärtuste käsitsi sisestamise korral olulisi eeliseid. See säästab teie aega, kuid see võib samuti aidata tõrkeid käsitsi sisestamisest vähendada. Seda on ka lihtsam lugeda, eriti kuna semikooloneid saab eristada komadega eraldajatest.

Siin on näide, mis kasutab massiivi konstante suurema valemi osana. Liikuge proovi töövihikus valemi töölehel olevale konstandile või looge uus tööleht.

Lahtris D9 sisestati = SEQUENCE (1; 5; 3; 1), kuid võite sisestada ka 3, 4, 5, 6 ja 7 lahtrites A9: H9. Selle konkreetse numbri valimise osas ei ole midagi erilist, me valisime vaid midagi muud kui 1-5 diferentseerimiseks.

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

Massiivide konstantide kasutamine valemites. Selles näites kasutasime valemit = SUM (D9: H (* JADA (1; 5))

Funktsioon SEQUENCE ehitab massiivi konstandi {1, 2, 3, 4, 5} ekvivalenti. Kuna Excel sooritab sulgudes olevate avaldiste toiminguid, on järgmised kaks mängu olevat elementi: H9 ja korrutamise tehtemä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 väärtused ja annab tulemiks 85.

Talletatud massiivi kasutamise vältimiseks ja toimingu täielikuks talletamiseks mälus saate selle asendada teise massiivi konstandiga.

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

Elemendid, mida saate kasutada massiivi konstantide jaoks

  • Massiivide konstandid võivad sisaldada numbreid, teksti, loogikaväärtusi (nt TRUE ja FALSE) ning/A. (nt #Nid). Numbreid saate kasutada täisarvudes, kümnendkohas ja teaduslikes vormingutes. Kui lisate 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.

Massiivse konstantide kasutamise parim viis on neile nime 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ääratletud nimed > Määratle nimi. Tippige väljale nimi 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 selline:

Nimega massiivi konstantide lisamine valemitest > määratletud nimed > Name Manager > uus

Klõpsake nuppu OK, seejärel valige mis tahes rida kolme tühja lahtriga ja sisestage = kvartal1.

Kuvatakse järgmine tulem.

Kasutage valemis nimega massiivi konstanti (nt = Kvartal1), kus Kvartal1 on määratletud kui = {"Jaanuar"; "veebruar"; "märts"}

Kui soovite, et tulemid oleks horisontaalselt vertikaalsed, saate kasutada =üleminna (kvartal1).

Kui soovite kuvada 12-kuulise loendi, nagu võite finantsaruande koostamisel kasutada, saate praegusel aastal kasutada funktsiooni SEQUENCE. Selle funktsiooni juures on puhas asi, et kuigi ainult kuu kuvatakse, on olemas kehtiv kuupäev, mida saate kasutada ka teistes arvutustes. Nendes näidetes leiate need näited nimega massiivi konstant ja kiire valimi andmekomplekti töölehed näites töövihik.

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

12-kuulise dünaamilise loendi koostamiseks kasutage funktsiooni TEXT, DATE, YEAR, TODAY ja SEQUENCE kombinatsiooni.

See kasutab funktsiooni Date , et luua praegusel aastal põhinev kuupäev, loob jada massiivi konstantsena 1 – 12 jaanuariks kuni detsembrini, seejärel teisendab funktsioon Text ekraani vorminguks "Mmm" (Jan, veebr, Mar jne). Kui soovid kuvada kogu kuu nime (nt jaanuaris), kasutad "Mmmm".

Kui kasutate nimega konstanti massiivivalemina, pidage meeles, et sisestage võrdusmärk (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õpuks pidage meeles, et saate kasutada funktsioonide, teksti ja numbrite kombinatsioone. See kõik sõltub sellest, kui loominguline soovid saada.

Järgmised näited illustreerivad mõnda massiivikonstantide massiivivalemites kasutamise võimalust. Mõned näited kasutavad funktsiooni ülevõtmine ridade teisendamiseks veergudesse ja vastupidi.

  • Massiivis iga üksuse mitmekordne

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

    Samuti saate jagada (/), lisada (+) ja lahutada (-).

  • Massiivi üksuste ruutu võtmine

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

  • Massiivis ruudus olevate üksuste ruutjuure otsimine

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

  • Ühemõõtmelise rea transponeerimine

    Enter = üle (jada (1; 5))või = üle ({1, 2, 3, 4, 5})

    Ehkki sisestasite horisontaalse massiivikonstandi, teisendab funktsioon TRANSPOSE massiivikonstandi veeruks.

  • Ühemõõtmelise veeru transponeerimine

    Enter = üle (jada (5; 1))või = üle ({1; 2; 3; 4; 5})

    Ehkki sisestasite vertikaalse massiivikonstandi, teisendab funktsioon TRANSPOSE konstandi reaks.

  • Kahemõõtmelise konstandi transponeerimine

    Enter = üle (jada (3; 4))või = üle ({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 massiivi valemeid olemasolevast massiivist uue massiivi loomiseks.

    Sisestage = SEQUENCE (3; 6; 10; 10)või = {10, 20, 30, 40, 50, 60; 70, 80, 90100110120; 130140150160170180}

    Veenduge, et tipite numbrile {(avamis traksid) enne tippimist 10, ja} (sulgemise traksidega) pärast 180 tippimist, kuna loote arvude massiivi.

    Järgmiseks Sisestage = D9 #või = D9: i11 tühjas lahtris. 3 x 6 massiivi lahtrites kuvatakse samad väärtused, mida näete väljal D9: D11. Funktsiooni # Sign nimetatakse hajutatud vahemiku tehtemärgiksja see on Excel's viis, kuidas selle asemel tippida kogu massiivi vahemik.

    Kasutage funktsiooni kallatud vahemiku tehtemärki (#), et viidata olemasolevale massiivile.

  • Massiivikonstandi loomine olemasolevate väärtuste põhjal

    Saate kasutada lekkinud massiivivalemina tulemusi ja teisendada selle osadeks. Valige lahter D9 ja vajutage redigeerimisrežiimis aktiveerimiseks klahvi F2 . Järgmiseks vajutage klahvi F9 , et teisendada lahtri viited väärtustele, mida Excel seejärel teisendab massiiviks konstant. Kui vajutate sisestusklahvi ( Enter), 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 lahtrivahemiku märkide arvu. See sisaldab tühikuid.

    Arvu märkide loendamine vahemikus ja muud massiivid tekstistringi abil töötamiseks

    = SUM (LEN (C9: C13))

    Sellisel juhul annab funktsioon LEN iga vahemiku lahtris oleva tekstistringi pikkuse. Funktsioon SUM liidab need väärtused koos ja kuvab tulemi (66). Kui soovid saada keskmise märkide arvu, võid kasutada järgmist.

    = 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 annab ülevaate lahtri vahemikus D2: D6 olevate üksuste pikkusest. Funktsioon Max arvutab nende üksuste hulgast suurima väärtuse, mis vastab pikimale tekstistringile, mis on lahtris D3.

    Siit edasi läheb lugu veidi keerulisemaks. Funktsioon Match arvutab pikima tekstistringi sisaldava lahtri nihke (suhteline positsioon). 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)

    Argument Match Type on antud juhul 0. Vaste tüüp võib olla 1, 0 või-1 väärtus.

    • 1-annab vastuseks suurima väärtuse, mis on otsingust väiksem või sellega võrdne.

    • 0-annab vastuseks esimese väärtuse, mis on täpselt võrdne otsingu väärtusega.

    • -1-annab vastuseks väikseima väärtuse, mis on suurem või võrdne määratud otsinguveeru väärtusega.

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

    Lõpuks on funktsioon index järgmised argumendid: massiiv ning rea ja veeru number selle massiivi sees. Lahtrivahemik C9: C13 pakub massiivi, funktsioon MATCH annab lahtri aadressi ja viimase argumendi (1) määrab, et väärtus pärineb massiivi esimesest veerust.

    Kui soovid saada väikseima tekstistringi sisu, asendad Maxi Ülaltoodud näites min-ga.

  • Vahemiku n väiksema väärtuse leidmine

    Selles näites kirjeldatakse kolme väikseima väärtuse leidmist lahtrivahemikus, kus Näidisandmete massiivis on massiivid B9: B18has loodud: = int (RANDARRAY(10; 1) * 100). Pange tähele, et RANDARRAY on lenduv funktsioon, nii et saate iga kord, kui Excel arvutab iga kord juhuslikud numbrid.

    Exceli massiivne valem, et leida nda vähim väärtus: = väike (B9 #, SEQUENCE (D9))

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

    See valem kasutab massiivi konstanti, et hinnata väikest funktsiooni kolm korda ja tagastada väikseima 3 liikme massiivis, mis sisaldub lahtrites B9: B18, kus 3 on muutuja väärtus lahtris D9. Suurema arvu väärtuste leidmiseks saate funktsiooni JÄRJESTUSt suurendada või lisada konstandile veel argumente. Seejärel saate kasutada ka muid funktsioone, mis sisaldavad seda valemit, näiteks SUM või AVERAGE. Näited:

    = SUM (VÄIKE (B9 #, SEQUENCE (D9))

    = AVERAGE (VÄIKE (B9 #, SEQUENCE (D9))

  • Vahemiku n suurima väärtuse leidmine

    Vahemiku suurimate väärtuste leidmiseks saate suure funktsioonigaasendada väikese funktsiooni. Lisaks kasutatakse järgmises näites ka funktsioone ROW ja INDIRECT.

    Sisestage = Large (B9 #, Row (kaudne ("1:3"))) või = Large (B9: B18; Row (kaudne ("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 reguleerib rea viiteid ning valem genereerib nüüd täisarvud vahemikus 2 – 11. Probleemi lahendamiseks tuleb valemisse lisada funktsioon INDIRECT:

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

    KAUDNE funktsioon kasutab tekstistringi argumentidena (sellepärast on vahemik 1:10 jutumärkidega ümbritsetud). 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. Saate kasutada lihtsalt JADA:

    = SEQUENCE (10)

    Uurime valemit, mida kasutasite varem (= LARGE (B9 #; ROW (kaudne ("1:3"))) (alustades sisemistest sulgudes ja töötades edasi: kaudne funktsioon annab tulemiks teksti väärtuste kogumi, sel juhul väärtused 1 kuni 3. Funktsioon ROW loob kolme lahtriga veeru massiiv. Funktsioon LARGE kasutab lahtrite vahemiku B9: B18 väärtusi ja seda hinnatakse kolm korda, üks kord iga funktsiooni ROW tagastatud viite kohta. Kui soovite leida rohkem väärtusi, saate lisada KAUDSEle funktsioonile suurema lahtrivahemiku. Lõpuks, nagu ka väikeste näidete korral, saate kasutada seda valemit koos muude funktsioonidega (nt SUM ja AVERAGE).

  • Veaväärtusi sisaldava vahemiku liitmine

    Funktsioon SUM Excelis ei tööta, kui proovite summeerida veaväärtuse sisaldavat vahemikku (nt #VALUE!). või #N/A. Selles näites kirjeldatakse, kuidas summeerida väärtuste vahemik nimega andmed, mis sisaldavad tõrkeid.

    Kasutage vigadega tegelemiseks massiive. Näiteks = SUM (IF (ISERROR (andmed); ""; andmed) liidab vahemiku nimega andmed, isegi kui see sisaldab tõrkeid (nt #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 sarnane eelmisele valemile, kuid see annab tulemite arvu nende filtreerimise asemel vahemikku nimega andmed.

    =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, mida nimetatakse müük.

Näiteks on see massiiv valemis ainult positiivsed täisarvud vahemikus nimega Sales, mis tähistab lahtreid E9: E24 Ülaltoodud näites:

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

Funktsioon IF loob massiivid positiivsete ja valede väärtuste vahel. 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. Näiteks arvutab see massiivivalem väärtused, mis on suuremad kui 0 ja väiksem 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 summeerida 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 sooritate matemaatilised toimingud (nt liitmine või korrutamine) väärtustega, mis vastavad sellele või või ja 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 lahtri vahemikud olema sama suurusega ja sama dimensiooniga. Kui Minuandmed on näiteks vahemikus 3 rida 5 veergu, peab Sinuandmed olema ka 3 rida 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ü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))

Näites toodud näited leiate töövihikust andmekogumite 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 terve tabel ja kleepige see tühja töölehe lahtrisse a1.

Müük Inimene

Car Tippige

Arv Müüakse

Üksus Hind

Kokku Müük

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. Kõigi müügiisikute kupeede ja sedaanide kogu müügi kuvamiseks valige lahtrid E2: E11, sisestage valem = C2: C11 * D2: D11ja vajutage klahvikombinatsiooni CTRL + SHIFT + ENTER.

  2. Kõigi müügitehingute kogusumma kuvamiseks valige lahter F11, sisestage valem = SUM (C2: C11 * D2: D11)ja seejärel vajutage klahvikombinatsiooni CTRL + SHIFT + ENTER.

Kui vajutate klahvikombinatsiooni CTRL + SHIFT + ENTER, ümbritseb Excel valemit traksidega ({}) ja lisab iga valitud vahemiku lahtris oleva valemi eksemplari. 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 siis klahvikombinatsiooni CTRL + SHIFT + ENTER:

=SUM(C2:C11*D2:D11)

Sel juhul korrutab Excel massiivis olevad väärtused (lahtri vahemik C2 kuni D11) ja seejärel kasutab funktsiooni SUMkoos kogusummade liitmiseks. 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 ühe lahtri valem täiesti sõltumatu mitmest lahtrist (valem lahtrites E2 kuni E11). See illustreerib veel ühte massiivivalemite kasutamise eelist – paindlikkust. Saate muuta veerus E olevaid valemeid või kustutada selle veeru täielikult, mõjutamata valemit 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. Valige näiteks lahter 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. Lisatud ohutusalase meetmena peate valemis muudatuste kinnitamiseks vajutama klahvikombinatsiooni CTRL + SHIFT + ENTER .

  • Väiksema suurusega failid    Mitme vahepealse valemi asemel saate sageli kasutada ühte massiivi valemit. Näiteks kasutab töövihik ühe massiivi valemit tulemite arvutamiseks veerus E. Kui kasutasite standardseid valemeid (nt = C2 * D2, C3 * D3, C4 * D4...), oleks samade tulemite arvutamiseks kasutatud 11 erinevat valemit.

Üldiselt kasutavad massiivid valemite süntaksit standardhälve. Nad kõik algavad võrdse (=) märgiga ja te saate kasutada enamikku sisseehitatud Exceli funktsioonidest oma massiivi valemites. Võtme erinevus on see, et valemi kasutamisel vajutage klahvikombinatsiooni CTRL + SHIFT + sisestusklahv (ENTER ). Kui seda teete, ümbritseb Excel teie massiivi valemi traksidega – kui tipite traksid käsitsi, teisendatakse valem tekstistringiks ja see ei tööta.

Massiivide funktsioonid võivad keerukate valemite koostamiseks olla tõhus viis. 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 massiivivalemina. 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.

  • Massiivivalemina kustutamiseks valige kogu valemite vahemik (nt E2: E11) ja seejärel vajutage kustutusklahvi ( Delete).

  • Lahtreid ei saa lisada mitme lahtriga massiivist massiivivalemina või neid kustutada.

Vahel võib teil tekkida vajadus massiivivalemit laiendada. Valige olemasolev lahtrivahemik esimene lahter ja jätkake seni, kuni olete valinud kogu vahemiku, millele soovite valemi laiendada. Valemi redigeerimiseks vajutage klahvi F2 ja seejärel vajutage valemi kinnitamiseks pärast valemi kohandamist KLAHVIKOMBINATSIOONI CTRL + SHIFT + ENTER . Võtmeks on valida terve vahemik, alustades massiivi ülemisest vasakpoolsest lahtrist. Ülemine vasakpoolne lahter on see, mida saab redigeerida.

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

  • Mõnikord võite unustada 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, peate kas vältima massiivide valemeid või veenduma, et need inimesed teaksid mis tahes massiivi valemitest ja kuidas neid muuta, kui nad seda vajavad.

  • 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üüdseks peate massiivide valemite 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.

Siin on massiiv ü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}. Kahe rea massiivis 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 valemiribal järgmine valem ja vajutage siis klahvikombinatsiooni CTRL + SHIFT + ENTER:

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

    Sellisel juhul peaksite tippima avamise ja sulgemise traksid ({}) ja Excel lisab teile teise komplekti.

    Kuvatakse järgmine tulem.

    Horisontaalne massiivikonstant valemis

Vertikaalse konstandi loomine

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

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

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 massiivivalemina vajutage klahvikombinatsiooni CTRL + SHIFT + ENTER , et valem sisestada.

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

Parim viis massiivi konstantide kasutamiseks on neile nime 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 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.

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 või kui paigutate ühe valesse kohta, ei pruugita massiivi konstanti õigesti luua 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õned näited kasutavad funktsiooni ülevõtmine ridade teisendamiseks veergudesse 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 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 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 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. Sisestage 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. Sisestage 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

  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 selle massiivi arvude vahemikule C8: E10 massiivi valemi abil. Teie töölehel peaks C8-i kaudu E10 välja nägema selline:

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Valige lahtrivahemik C1 kuni E3.

  4. Sisestage valemiribal järgmine valem ja vajutage siis klahvikombinatsiooni CTRL + SHIFT + ENTER:

    =C8:E10

    Lahtrites C1 kuni E3 kuvatakse samad väärtused, mida näete punktis C8 kuni E10 3x3.

Massiivikonstandi loomine olemasolevate väärtuste põhjal

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

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

  3. Vajutage klahvikombinatsiooni CTRL + SHIFT + ENTER , et sisestada massiivi konstant massiivi valemina.

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 lahtrite A2: A6 märkide koguarvu (66).

  3. Valige lahter A10 ja vajutage klahvikombinatsiooni CTRL + SHIFT + ENTER , et näha lahtrite A2: A6 (lahter a3) pikimat sisu.

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

=SUM(LEN(A2:A6))

Selles näites tagastab funktsioon LEN iga selle vahemiku lahtris sisalduva tekstistringi pikkuse. Funktsioon SUM liidab need väärtused koos 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 mõned juhuslikud numbrid lahtrites a1: a11.

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

  3. Sisestage järgmine valem ja vajutage siis klahvikombinatsiooni CTRL + SHIFT + ENTER:

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

See valem kasutab massiivi konstanti, et hinnata väikest funktsiooni kolm korda ja tagastada väikseima (1), teise väikseima (2) ja kolmanda väikseima (3) liikme massiivis, mis sisaldub lahtrites a1: A10, et leida rohkem väärtusi, saate lisada veel argumente pidev. Seejärel saate kasutada ka muid funktsioone, mis sisaldavad seda valemit, näiteks SUM või AVERAGE. Näited:

= SUM (VÄIKE (A1: A10; {1; 2; 3})

= AVERAGE (VÄIKE (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 valemiribal järgmine valem ja vajutage siis klahvikombinatsiooni CTRL + SHIFT + ENTER:

    = LARGE (A1: A10; ROW (KAUDNE ("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 oma praktika töövihikus tühi veerg kümnest lahtrist, sisestage see 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.

Vaatame valemit, mida kasutasite varem ( = suur (A5: a14; Row (kaudne ("1:3"))) (alates sisemistest sulgudes ja töötamisest väljapoole: kaudne funktsioon annab tulemiks teksti väärtuste kogumi, sel juhul väärtused 1 kuni 3. Funktsioon Row loob kolme lahtriga pikliku massiivi. Funktsioon Large kasutab lahtrites a5: a14 olevaid väärtusi ja seda hinnatakse kolm korda, üks kord iga funktsiooni Row tagastatud viite kohta. Väärtused 3200, 2700 ja 2000 tagastatakse kolme lahtriga pikliku massiivi. Kui soovite leida rohkem väärtusi, saate lisada kaudsele funktsioonile suurema lahtrivahemiku.

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

Pikima tekstistringi leidmine lahtrivahemikus

Minge tagasi varasema tekstistringi näitesse, Sisestage tühi lahter järgmine valem ja vajutage klahvikombinatsiooni CTRL + SHIFT + ENTER:

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

Kuvatakse tekst "hunnik lahtreid, mida" kuvatakse.

Heitkem sellele valemile täpsem pilk, alustades seespoolsetest elementidest ja liikudes analüüsimisega väljapoole. Funktsioon LEN annab tulemiks lahtri vahemikus A2: A6 iga üksuse pikkuse. Funktsioon Max arvutab suurima väärtuse nende üksuste hulgast, mis vastab pikimale tekstistringi 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 annab lahtri aadressi ja viimase argumendi (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))

Kinnituse

Selle artikli osad põhinesid Exceli Power Useri veergudes, mille kirjutas Colin Wilcox ja mis on kohandatud Exceli 2002 valemite peatükitest 14 ja 15, mis on kirjutatud John Walkenbach ' i, endise Exceli MVP-i raamatuga.

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 massiivide valemid vs pärand CSE VALEMITEKS massiivsed valemid

Funktsioon FILTER

Funktsioon RANDARRAY

Funktsioon SEQUENCE

Funktsioon SORT

Funktsioon SORTBY

Funktsioon UNIQUE

#SPILL! tõrked Excelis

Kaudne ristmiku tehtemärk: @

Valemite ülevaade

Märkus.:  See leht on tõlgitud automaatselt ning sellel võib leiduda grammatikavigu ja ebatäpsusi. Tahame, et sellest sisust oleks teile abi. Andke meile teada, kui see teave oli teile abiks. Soovi korral saate ingliskeelset artiklit lugeda siit.​

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.

×