Massiivivalemite juhised ja näited

Massiivivalemite juhised ja näited

Märkus.: Soovime pakkuda teie keeles kõige ajakohasemat spikrisisu niipea kui võimalik. See leht on tõlgitud automaatselt ja sellel võib leiduda grammatikavigu või ebatäpsusi. Tahame, et sellest sisust oleks teile abi. Palun märkige selle lehe allservas, kas sellest teabest oli teile kasu või mitte. Soovi korral saab ingliskeelset artiklit lugeda siit.

Massiivivalemi on valem, mis saab ühe või mitme massiivi üksuste mitme arvutusi teha. Kui arvate, rea või veeru väärtuste või kombinatsiooni ridade ja veergude väärtuste massiivi. Massiivivalemite võib tagastada mitu tulemit või ühe tulemi.

Alates mai 2018 värskendamine Office 365jaoks, mis tahes valemi, mis võib tagastada mitu tulemit kuvatakse automaatselt kõrvalmõju need alla või üle arvesse naabruses lahtrid. See muutus käitumine ka koos mitu uut dünaamiline massiivi funktsioone. Dünaamiliste Massiivivalemite, kas nad kasutavad olemasolevaid funktsioone või dünaamiline massiivi funktsioone, tuleb ainult sisestatud ühest lahtrist ja seejärel kinnitab sisestusklahvi Enter. Varasemate versioonide Massiivivalemite jaoks on vaja esmalt valides kogu vahemiku ja seejärel Klahvikombinatsiooni Ctrl + Shift + Enterja valem, mis kinnitab. Need on tihti edaspidi CSE valemid.

Massiivivalemite abil saate teha keerukaid ülesandeid, näiteks:

  • Valimi andmekomplektide loomine

  • Lahtrivahemiku märkide loendamine.

  • Liita üksnes arvud, mis vastavad teatud tingimused, nagu on vahemiku kõige väiksemad väärtused või arvud, mis jäävad ülemise ja alumise vahele.

  • Liita n. väärtuse massiivi iga vahemikus väärtused.

Järgmised näited näitavad, kuidas luua Mitmelahtriliste kui ka ühelahtrilise Massiivivalemi kasutamisest. Võimaluse korral oleme lisanud mõned dünaamiline massiivi funktsioone nagu olemasolevad Massiivivalemid sisestatud nii dünaamiline ja pärand massiivi näited.

Näidiste allalaadimine

Kui laadite alla kõik massiivi valemi näiteid selle artikli töövihiku näide.

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

    Mitmelahtrilise Massiivivalemi funktsioon lahtris H10 = F10:F19 * G10:G19 auto arvu arvutamiseks müüs ühiku hind

  • Siin me ei arvutamise kupeede ja sedaanide iga müügiesindaja kogumüük sisestades = F19:F19 * G10:G19 lahtris H10.

    Kui vajutate sisestusklahvi, kuvatakse teile tulemused kõrvalmõju lahtrite H10:H19 allapoole. Pange tähele, et spill vahemiku tõstetakse äärisega suvalist lahtrit vahemikus spill valimisel. Võite märgata ka valemites lahtrite H10:H19 tuhm. Need on just seal viide, nii, et kui soovite valemi muuta, peate lahtri H10, kus asub juhtslaidi valem valimiseks.

  • Ühelahtrilise Massiivivalemi

    Ühelahtrilise Massiivivalemi koos =SUM(F10:F19*G10:G19) üldkokkuvõtte arvutamiseks

    Näide töövihiku lahtrisse H20 tippige või kopeerida ja kleepida =SUM(F10:F19*G10:G19)ja vajutage sisestusklahvi Enter.

    Sel juhul Exceli korrutab väärtused massiivi (lahtrivahemik F10 kuni G19) ja kokkuvõtete lisamiseks koos kasutab funktsiooni SUM. Tulem on $1,590,000 müük üldsumma kokku.

    Selles näites on näha, kuidas võimas saab seda tüüpi valemit. Oletame näiteks, et teil on 1000 andmeread. Massiivivalemi loomine ühes lahtris lohistamise valem, kuni 1000 ridade asemel saate liita osaliselt või tervenisti andmeid. Lisaks teade, et ühelahtrilise valem lahtris H20 on täielikult sõltumatu Mitmelahtriliste valem (valem lahtrites H10 kuni H19). See on Massiivivalemite eelis – paindlikkust. Muud valemid veerus H võib muutuda, kui valem H20 mõjutamata. See võib olla ka hea tava sõltumatu kokkuvõtete sellisel viisil, kuna see aitab kinnitada tulemused täpsuse.

  • Dünaamiliste massiivivalemitega kaasnevad ka järgmised eelised:

    • Järjepidevus    Kui klõpsate lahtreid kaudu H10 allapoole, kuvatakse sama valemi. Selle järjepidevuse aitavad tagada täpsuse suurem.

    • Turve    Mitmelahtriline massiivivalem osa ei saa üle kirjutada. Näiteks Klõpsake lahtrit H11 ja klõpsake nuppu Kustuta. Excel ei muuda väljund on massiiv. Selle muutmiseks peate valima ülemist vasakut lahtrit massiivi või lahtri H10.

    • Väiksemad failimahud.    Sageli saate asemel mitu vahe valemid ühe massiivivalemiga. Näiteks auto müügi näide kasutab ühe massiivivalemiga arvutamiseks tulemused veergu E. Kui teie kasutatud standard valemid näiteks = F10 * välistes g-10, F11 * G11, F12 * G12, jne, mida oleks kasutatud 11 erinevad valemid sama tulemi arvutamine. See pole suurt, kuid mida teha, kui teil oli tuhandete ridade kokku? Seejärel saate seda teha suur erinevus.

    • Tõhusust    Massiivi funktsioone saab tõhusalt keerukate valemite koostamiseks. Massiivi valemi =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).

    • Kallab    Dünaamiliste Massiivivalemite kuvatakse automaatselt kõrvalmõju väljundi vahemikku. Kui teie andmed on Exceli tabelis, siis teie dünaamiline Massiivivalemite automaatselt suurust, kui lisate või eemaldate andmeid.

    • #SPILL! tõrke    Dünaamiline massiivid kasutusele selle #SPILL! tõrke, mis näitab, et ettenähtud spill vahemikus on blokeeritud mingil põhjusel. Kui ummistus lahendamiseks valem automaatselt kõrvalmõju.

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 tuleb eraldada komadega abil üksused, saate luua horisontaalne massiiv (rida). Kui eraldate semikoolonitega üksusi, saate luua vertikaalse massiivi (veerus). Kahemõõtmeline massiiv loomiseks piiritleda üksused komadega igal real ja iga rea semikoolonitega piiritleda.

Järgmistest toimingutest aitab teil harjutada horisontaalsete, vertikaalsete ja kahemõõtmeliste konstantide loomist. Me näitan näited JADA funktsioon automaatselt genereerida Massiivikonstantide kasutamine, kui ka käsitsi sisestatud Massiivikonstantide.

  • Horisontaalse konstandi loomine

    Kasutage eelmistes näidetes töövihik või looge uus töövihik. Valige suvaline tühi lahter ja sisestage =SEQUENCE(1,5). Funktsiooni JADA ehitatakse 1 rea ning 5 veergu massiivi sama = {1,2,3,4,5}. Kuvatakse järgmine tulem:

    Luua horisontaalne massiivikonstant =SEQUENCE(1,5) või = {1,2,3,4,5}

  • Vertikaalse konstandi loomine

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

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

  • Kahemõõtmelise konstandi loomine

    Valige suvaline tühi lahter ruumiga paremale ja selle all ja sisestage =SEQUENCE(3,4). Kuvatakse järgmine tulem.

    4 veeru massiivikonstandi koos =SEQUENCE(3,4) 3 rea loomiseks

    Võite ka sisestada: või = {1,2,3,4; 5,6,7,8; 9,10,11,12}, kuid peaksite tähelepanu sellele, kus saate panna semikoolonit ja komad.

    Nagu näete, järjestuse suvand pakub olulisi eeliseid käsitsi sisestama oma massiivi konstandi väärtusi. Eelkõige salvestab aega, kuid see aitab vähendada vigade käsitsi sisestamine. Samuti on lihtsam lugeda, eriti siis, kui selle semikoolonit võib olla keeruline eristada koma eraldajad.

Siin on näide, et kasutab massiiv konstantide suurem valemi osana. Näidistöövihikus konstandi valemis töölehe avage või looge uus tööleht.

Lahtris D9 me sisestatud =SEQUENCE(1,5,3,1), kuid võite ka sisestada 3, 4, 5, 6 ja 7 lahtrite A9:H9 sisse. Pole midagi selle kindla arvu valiku eriline, valisime lihtsalt millegi muuga kui number 1-5 eristamine.

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

Massiivikonstantide kasutamine valemites. Selles näites kasutatakse = SUM (D9:H(*SEQUENCE(1,5))

Funktsiooni JADA koostab võrdub massiiv konstandi {1,2,3,4,5}. Kuna Excel tehete avaldiste ümbritsetud sulgudes esmalt, on kaks järgmist elemendid, mis tulevad mängu lahtri väärtused D9:H9 ja korrutamine tehtemärk (*). Selles etapis valem korrutab väärtused talletatud massiivi vastavate väärtuste konstandi alusel. See on samaväärne:

=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 funktsiooni SUM liidab väärtused ja tagastab 85.

Et vältida talletatud massiivi ja tehte tervenisti mällu jätta, saate selle teise massiivikonstandiga asendada:

=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 kasutada massiivikonstante

  • Massiivikonstandid võivad sisaldada arve, teksti, loogikaväärtusi (nt TRUE ja FALSE) ja veaväärtusi, nt #N/A. Saate arve täisarv, decimal ja teadus-vormingus. Kui lisate teksti, peate ümbritsevate jutumärke ("tekst").

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

Üks parimaid viise, kuidas kasutada massiivikonstante on nimi neid. Konstantidele võib olla märksa lihtsam kasutada, ja ta saate peita mõned teised teie Massiivivalemite keerukus. Massiivikonstandile nime ja kasutage seda valemis, tehke järgmist.

Minge valemite > määratletud nimed > nime määratlus. Väljale nimi tippige kvartal1. Väljale viitab sisestage järgmine konstant (Pidage meeles, et looksulud käsitsi tippida):

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

Dialoogiboks peaks olema nüüd selline:

Lisage nimega massiivikonstandi valemite > määratletud nimed > Nimehaldur > uus

Klõpsake nuppu OKja seejärel valige ükskõik millisele reale koos kolmest tühjast lahtrist ja sisestage = kvartal1.

Kuvatakse järgmine tulem:

Nimega massiivikonstant valemis kasutada, nt = kvartal1, kus kvartal1 on määratletud järgmiselt: = {"Jaanuar", "Veebruar", "märts"}

Kui soovite tulemused lekkekohad vertikaalselt, mitte horisontaalselt, saate kasutada =TRANSPONEERIMINE(kvartal1).

Kui soovite 12 kuu jooksul, nagu võib kasutada, kui hoone rahandus lause loendi kuvamiseks, saate alusena ühekordne käesoleva aasta funktsiooniga JADA. Kena asi see funktsioon on, et isegi juhul, kui on kuvatud ainult kuu, ei taha seda, mida saate kasutada teistes arvutustes lubatud kuupäev. Leiate need näited nimega massiivikonstandi ja kiire proovi andmekomplekti töölehtedel näide töövihikus.

=Text(Date(Year(Today()),SEQUENCE(1,12),1),"Mmm")

12 kuu dünaamilise loendi koostamine, kuupäev, aasta täna ja järjestuse funktsioonide kombinatsiooni abil

See kasutab funktsiooni DATE , et luua põhjal praeguse aasta kuupäeva, järjestuse loob massiivikonstant vahemikus 1 – 12 jaanuar kuni detsember, siis funktsioon TEXT teisendab kuvavorming, et väärtust "mmm" (jaan, veebr, märts, jne). Kui soovite kuvada kuu täisnime, nagu jaanuar kasutaksite "mmmm".

Kui kasutate nimega konstant massiivivalemina, ärge unustage sisestage võrdusmärk, näiteks = kvartal1, mitte ainult kvartal1. Kui te ei tee, Exceli massiiv tõlgendab tekstistring ja valem ei tööta ootuspäraselt. Lõpetuseks, pidage meeles, et saate kasutada funktsioone, teksti ja numbrite kombinatsioonid. Kõik sõltub sellest, kuidas loominguline, mida soovite leida.

Järgmised näited demonstreerivad seda võimalust, kus saate panna Massiivikonstantide kasutamine massiivivalemites mõne. Mõned näited kasutavad funktsioon TRANSPOSE muuta ridade, veergude ja vastupidi.

  • Mitme massiivi iga üksuse

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

    Saate jagada koos (/), (+) lisamine ja lahutamine koos (-).

  • Massiivi üksuste ruutu võtmine

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

  • Massiivi ruutude üksuste ruutjuure leidmine

    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 luua uus massiiv olemasoleva massiivist Massiivivalemite abil.

    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 kui tipite 10, ja} (paremlooksulg) pärast 180, kuna loote arvumassiivi.

    Seejärel sisestage tühja lahtrisse = D9 #või = D9:I11 . Lahtrite 3 x 6 massiivi kuvatakse näete D9:D11 sama väärtusega. Märgi # nimetatakse peale vahemiku tehtemärk, ja see on Exceli viis selle välja tippimise asemel kogu massiiv vahemikus viitamine.

    Olemasoleva massiivi viide mahavalgunud vahemiku märk (#) abil

  • Massiivikonstandi loomine olemasolevate väärtuste põhjal

    Saate teha mahavalgunud massiivivalem tulemuste ja teisendada selle osi. Valige lahter D9, seejärel vajutage redigeerimisrežiimi aktiveerimiseks klahvi F2 . Järgmiseks vajutage klahvi F9 , et teisendada väärtusi, mis Exceli seejärel teisendab massiivikonstant lahtriviiteid. Kui vajutate sisestusklahvi, kuvatakse valem = D9 #, peaks nüüd olema = {10,20,30 40,50,60; 70,80,90}.

  • Lahtrivahemiku märkide loendamine.

    Järgmises näites näitab, kuidas lahtrivahemiku märkide loendamine. See sisaldab tühikuid.

    Vahemiku ja muude massiivi töötamiseks tekstistringi märkide koguarvu loendamine

    = SUM (LEN(C9:C13))

    Sel juhul, tagastab funktsioon LEN iga stringi pikkus iga lahtrid vahemikus. Funktsioon SUM, siis neid väärtusi liidab ja kuvab tulemi (66). Kui soovite leida keskmise arvu märke, võite kasutada:

    = AVERAGE (LEN(C9:C13))

  • Pikima lahtri vahemiku C9:C13 sisu

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

    See valem toimib ainult siis, kui vahemik sisaldab ühe veeru lahtrid.

    Vaatame lähemalt valemit, alates sisemine elemente ja töötamise väljapoole. Funktsioon LEN tagastab kõigi üksuste pikkus lahtrivahemik D2: D6. Funktsioon MAX arvutab hulgast suurimat väärtust need üksused, mis vastab pikima tekstistringi, mis on lahtris D3.

    Siin on, kus asjad veidi keerukas. Funktsioon MATCH arvutab offset (suhteline asukoht), pikima tekstistringi sisaldav lahter. Selleks, et see nõuab kolme argumenti: otsing väärtus, lookup massiivkuju ja match tüüp. Funktsioon MATCH otsib otsing massiiv määratud otsing väärtus. Sel juhul otsing väärtus on pikima tekstistringi:

    MAX(LEN(C9:C13)

    ja see string asub selles massiivis:

    LEN(C9:C13)

    Match argumendi tüüp on sel juhul 0. Otsingu tüüp võib olla 1, 0 või -1 väärtust.

    • 1 – tagastab suurima väärtuse, mis on väiksem või võrdne otsingut val

    • 0 – tagastab esimese väärtuse täpselt võrdne väärtusega otsing

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

    • Kui argument otsingu tüüp, eeldab Excel 1.

    Lõpetuseks, funktsioon INDEX kasutab järgmisi argumente: massiivi ja selle massiivi ridade ja veergude arvu. Lahtrivahemik C9:C13 pakub massiiv, funktsioon MATCH loob lahtri aadressi ja lõplik argumendi (1) saate määrata, et väärtus pärineb massiivi esimesest veerust.

    Kui soovite leida väikseim tekstistringi sisu, tuleks asendada MAX mineeltoodud näites.

  • Vahemiku n väiksema väärtuse leidmine

    Selles näites kirjeldatakse kolme kõige väiksema väärtuse leidmist lahtrivahemik, kus massiivi näidisandmed lahtrite B9:B18has loodud: = INT (RANDARRAY(10,1) * 100). Pöörake tähelepanu sellele RANDARRAY muutlikku funktsiooni, seega kuvatakse uus komplekt juhuslike arvude iga kord, kui Excel arvutab.

    Exceli massiivivalem nda väikseima väärtuse leidmiseks: =SMALL(B9#,SEQUENCE(D9))

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

    See valem kasutab funktsioon SMALL väärtustatakse kolm korda ja tagastada väikseim 3 liikmete massiiv, mis sisaldub B9:B18 lahtrid, kus 3 on muutuja väärtuse lahtris D9 massiivikonstant. Veel väärtuste leidmiseks saate suurendada funktsiooni järjestus väärtus, või lisada rohkem argumente konstandi. Saate täiendavate funktsioonide valemiga, näiteks SUM ja AVERAGE. Näiteks:

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

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

  • Vahemiku n suurima väärtuse leidmine

    Vahemiku suurima väärtuse leidmine, saate asendada funktsioon LARGEfunktsiooni SMALL. Järgmine näide kasutab peale rea - ja INDIRECT funktsioone.

    Sisestage näiteks = suur (B9 #, rida (INDIRECT ("1:3"))), või = suur (B9:B18,ROW(INDIRECT("1:3")))

    Selles etapis võib aidata leida veidi rea- ja INDIRECT funktsioonide kohta. Saate luua hulgaliselt järjestikust täisarvude funktsiooni ROW. Näiteks valige tühi ja sisestage:

    =ROW(1:10)

    Valemiga luuakse veerg 10 järjestikust murdosa. Massiivivalemit sisaldava vahemiku kohal rea lisamine võimalikke probleeme vaatamiseks (st kohal rida 1). Excel kohandab rea viited ja valem loob täisarvude nüüd 2 11. Selle probleemi lahendamiseks saate lisada funktsiooni INDIRECT valem:

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

    Funktsioon INDIRECT kasutab tekstistringid oma argumentide (mistõttu vahemikus 1:10 on ümbritsetud jutumärkidega). Exceli reguleerida tekstiväärtused, kui ridade lisamine või muul viisil liikumine massiivivalemit. Seetõttu on funktsioon ROW alati loob massiivi täisarvud, mida soovite. Võite kasutada sama lihtsalt JADA:

    =SEQUENCE(10)

    Analüüsime valem, mida kasutasite varem – suur (B9 #, rida (INDIRECT ("1:3"))) = – alates sisemine sulgude ja tööd väljapoole: tagastab funktsioon The INDIRECT tekstiliste väärtuste kogumi sel juhul väärtused 1 – 3. Funktsioon ROW loob omakorda kolme-lahtri veeru massiivi. Funktsioon LARGE kasutab väärtused lahtrivahemik B9:B18 ja seda hinnatakse kolm korda, kui iga tagastatud funktsioon ROW viite. Kui soovite leida rohkem väärtusi, saate lisada funktsiooni INDIRECT suurem sisaldav lahtrivahemik. Lõpuks nagu näidetega väike, saate selle valemi koos muude funktsioonidega, näiteks SUM ja AVERAGE.

  • Veaväärtusi sisaldava vahemiku liitmine

    Exceli funktsiooni SUM ei tööta, kui proovite liita vahemik, mis sisaldab veaväärtust, nt #VALUE! või veaväärtuse #N/A. Selles näites kirjeldatakse, kuidas liita vahemikus nimega andmed, mis sisaldab tõrgete väärtused:

    Kasutage massiivi tõrgete lahendamiseks. Näiteks kuvatakse =SUM(IF(ISERROR(Data),"",Data) liita vahemiku andmed isegi juhul, kui see sisaldab tõrgete, 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 sarnaneb eelmise valemiga, kuid see annab Veaväärtuste arvu vahemikus nimega andmed, mitte ei Filtreeri neid välja:

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

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

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

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

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

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

Võib juhtuda, et väärtuste liitmine tingimuste põhjal.

Massiivi abil saate arvutada põhjal teatud kindlatele tingimustele. =SUM(IF(Sales>0,Sales)) kuvatakse kõik väärtused, mis on suurem kui 0 müügitulemused vahemikus liita.

Näiteks see massiivivalem summeerib lihtsalt positiivsed täisarvud vahemiku Sales, mis tähistab lahtrite E9:E24 eeltoodud näites:

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

Funktsioon IF loob massiivi positiivsete ja false väärtused. Funktsiooni SUM sisuliselt ignoreerib false väärtusi, kuna 0 + 0 = 0. Lahtrivahemik, mida kasutada seda valemit võib koosneda mis tahes arv ridu ja veerge.

Saate liita ka rohkem kui ühe tingimuse vastavad väärtused. Näiteks see massiivivalem arvutatakse väärtused, mis on suurem kui 0 ja väiksem kui 2500:

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

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

Samuti saate luua massiivivalemeid kasutada tüüpi OR tingimus. Näiteks saate liita väärtusi, mis on suurem kui 0 või vähem kui 2500:

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

Te ei saa kasutada AND ja OR töötab massiivivalemites otse, kuna need funktsioonid tagastavad ühe tulemi, tõene või väär ja massiivi funktsioonid nõuavad tulemuste massiivi. Selle probleemi lahendamiseks saate töötada, kasutades loogika näidatud eelmise valemiga. Teisisõnu, matemaatika toiminguid, nagu liitmine või korrutamine väärtusi, mis vastavad või teha või ja tingimus.

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 kahe lahtrivahemike nimedega Minuandmed ja Sinuandmed väärtused ja annab vastuseks arvu kahe erinevused. Kui kaks vahemikud sisu on täpselt ühesugused, valem väärtuse 0. Kui soovite kasutada seda valemit, lahtrivahemike peavad olema sama suurus ja dimensiooni. Näiteks kui Minuandmed on mitmesuguseid 3 rida 5 veerus, Sinuandmed peab olema ka 3 rida 5 veerus:

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

Sarnaseid näiteid leiate näidistöövihikus töölehel andmekomplektide erinevused .

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

  • Mitmelahtriline massiivivalem

Kopeerige kogu Allolev tabel ja kleepige need tühja töölehe lahtrisse A1.

Müük Isiku

Auto Tüüp

Arv Müüdud

Ühiku Hind

Kogusumma 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. Müük kokku kupeede ja sedaanide iga müügiesindaja vaatamiseks lahtris valige lahtrid E2: E11, sisestage valem = C2: C11 * D2: D11, ja seejärel vajutage Klahvikombinatsiooni Ctrl + Shift + Enter.

  2. Lugege teemat kogusumma kõikide müükide, valige lahter F11, sisestage valem =SUM(C2:C11*D2:D11)ja vajutage Klahvikombinatsiooni Ctrl + Shift + Enter.

Kui vajutate Klahvikombinatsiooni Ctrl + Shift + Enter, Excel ümbritseb valemi koos looksulgudega ({}) ja lisab iga valitud vahemiku lahtrisse valem eksemplari. See juhtub väga kiiresti, seega kuvatakse veerus E iga autotüüp iga müügiesindaja müügi kogusumma. Kui te E2 valige E3, E4 ja jne, näete, kuvatakse sama valemi: {= C2: C11 * D2: D11}.

Summad veerus E on arvutatud massiivivalemiga

  • Ühelahtrilise massiivivalemi koostamine

Töövihiku lahtrisse D13 tippige järgmine valem ja vajutage Klahvikombinatsiooni Ctrl + Shift + Enter:

=SUM(C2:C11*D2:D11)

Sel juhul Excel korrutab väärtused massiivi (lahtrivahemik C2 kuni D11) ja kokkuvõtete lisamiseks koos kasutab funktsiooni SUM. Tulem on $1,590,000 müük üldsumma kokku. Selles näites on näha, kuidas võimas saab seda tüüpi valemit. Oletame näiteks, et teil on 1000 andmeread. Massiivivalemi loomine ühes lahtris lohistamise valem, kuni 1000 ridade asemel saate liita osaliselt või tervenisti andmeid.

Lisaks teade, et ühelahtrilise valem lahtris D13 on täielikult sõltumatu Mitmelahtriliste valem (valemit lahtrid E2 – E11). See on Massiivivalemite eelis – paindlikkust. Te muuta valemid veerus E või kustutada selle veeru täielikult eemaldada, mõjutamata D13 valemit.

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.

  • Turve    Mitmelahtriline massiivivalem osa ei saa üle kirjutada. Näiteks Klõpsake lahtrit E3 ja vajutage kustutada. Teil on kas valige terve lahtrivahemik (E2 – E11) ja muutke valemit kogu massiivi või jätke on massiiv. Kui on lisatud Turve, peate vajutage Klahvikombinatsiooni Ctrl + Shift + Enter valemi muudatuste kinnitamiseks.

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

Üldiselt massiivivalemeid kasutada standard valemisüntaksit. Need kõik algavad võrdusmärgiga (=), ja enamik sisseehitatud Exceli funktsioonide saate kasutada massiivivalemites. Võtme erinevus on kasutamisel massiivivalem, vajutage Klahvikombinatsiooni Ctrl + Shift + Enter , et sisestage soovitud valem. Kui te ei tee seda, ümbritseb Excel teie massiivivalem looksulud koos – kui tipite looksulud käsitsi, teisendatakse valem tekstistringi ja see ei tööta.

Massiivi funktsioone saab tõhusalt keerukate valemite koostamiseks. Massiivi valem =SUM(C2:C11*D2:D11) on sama, mis see: =SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

NB!: Iga kord, kui soovite massiivivalemit sisestada, vajutage Klahvikombinatsiooni Ctrl + Shift + Enter . See kehtib ühelahtrilise nii Mitmelahtriliste valemid.

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 vahemik (nt lahtrid E2: E11) ja seejärel vajutage kustutada.

  • Ei saa rakendusse tühjade lahtrite või lahtrite mitmelahtrilise Massiivivalemi kustutamine.

Aeg-ajalt, peate võib-olla Massiivivalemi laiendamine. Valige esimene lahter olemasoleva massiivi vahemik ja jätkake, kuni olete valinud terve lahtrivahemik, kelle soovite valemit laiendada. Vajutage klahvi F2 , redigeerige valemit ja seejärel vajutage Klahvikombinatsiooni CTRL + SHIFT + ENTER , et valem kinnitada, kui te olete kohandatud valemi vahemikus. Oluline on valige terve lahtrivahemik, alustades ülemist vasakut lahtrit massiivis. Ülemist vasakut lahtrit on see, mida saab redigeerida.

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

  • Aeg-ajalt võib unustage vajutage Klahvikombinatsiooni Ctrl + Shift + Enter. Võib juhtuda, et isegi Exceli kõige kogenud kasutajad. Ärge unustage klahvikombinatsiooni, seda iga kord, kui sisestate või redigeerite Massiivivalemi.

  • Teiste kasutajate töövihikut ei pruugi aru saada valemeid. Praktikas Massiivivalemite üldiselt ei selgitatakse töölehele. Seetõttu, kui teised on vaja muuta oma töövihikuid, saate tuleks massiivivalemeid vältimiseks või veenduge, et need inimesed, mis tahes Massiivivalemite kohta teada ja aru saada, kuidas neid muuta, kui need on vaja.

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

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

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

Nüüd, teate, et peate vajutama Klahvikombinatsiooni Ctrl + Shift + Enter , kui massiivivalemeid luua. Massiivikonstantide on Massiivivalemite osa, ümbritsevad konstantide looksulud koos kirjutades neid käsitsi. Seejärel saate Klahvikombinatsiooni Ctrl + Shift + Enter ja kogu valemi sisestada.

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 ühes reas massiiv: {1,2,3,4}. Siin on massiiv ühes veerus: {1; 2; 3; 4}. Ja siin on kaks rida ja neli veergu: {1,2,3,4; 5,6,7,8}. Massiivis kahe rea esimene rida on 1, 2, 3 ja 4 ja teine rida on 5, 6, 7 ja 8. Üks semikoolon eraldab kaks rida, 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. Valemiribal, sisestage järgmine valem ja vajutage Klahvikombinatsiooni Ctrl + Shift + Enter:

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

    Sel juhul tuleks tippige avamine ja lõpulooksulud ({}) ja lisab Excel teise määramine teie eest.

    Kuvatakse järgmine tulem.

    Horisontaalne massiivikonstant valemis

Vertikaalse konstandi loomine

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

  2. Valemiribal, sisestage järgmine valem ja vajutage Klahvikombinatsiooni Ctrl + Shift + Enter:

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

    Kuvatakse järgmine tulem.

    Vertikaalne massiivikonstant massiivivalemis

Kahemõõtmelise konstandi loomine

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

  2. Valemiribal, sisestage järgmine valem ja vajutage Klahvikombinatsiooni Ctrl + Shift + Enter:

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

    Kuvatakse järgmine tulem.

    Kahemõõtmeline massiivikonstant massiivivalemis

Konstantide kasutamine valemites

Konstante kasutatakse järgmises lihtsas näites.

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

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

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

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

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

    Massiivikonstandiga massiivivalem

    Lahtris A3 kuvatakse väärtus 85.

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

Valem, mida te just kasutasite, koosneb mitmest osast.

Massiivikonstandiga massiivivalemi süntaks

1. Funktsioon

2. Talletatud massiiv

3. Tehtemärk

4. Massiivikonstant

Sulgudesse viimase element on massiivikonstandi: {1,2,3,4,5}. Pidage meeles, et Excel ei ümbritsege Massiivikonstantide koos looksulud; tipite tegelikult neid. Ka pidage meeles, kui lisate konstandi massiivivalem, vajutage Klahvikombinatsiooni Ctrl + Shift + Enter sisestage valem.

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

Lahendus, kopeerige funktsiooni, valige tühi lahter töövihikus, kleepida valem valemiribale ja vajutage Klahvikombinatsiooni Ctrl + Shift + Enter. Näete sama tulemuse nagu tegite varasema kasutamise, mida kasutatakse 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 parim viis kasutada massiivikonstante on nimi neid. Konstantidele võib olla märksa lihtsam kasutada, ja ta saate peita mõned teised teie Massiivivalemite keerukus. Massiivikonstandile nime ja kasutage seda valemis, tehke järgmist.

  1. Klõpsake menüü valemid jaotises Määratletud nimed nuppu Nime määratlus.
    Kuvatakse dialoogiboks Nime määratlus .

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

  • Teatud elemente võib eraldatud pole õige märgiga. Kui jätate koma või semikooloniga või panete ühe vales kohas, ei pruugi massiivikonstandi õigesti loodud või võidakse kuvada hoiatusteade.

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

Järgmised näited demonstreerivad seda võimalust, kus saate panna Massiivikonstantide kasutamine massiivivalemites mõne. Mõned näited kasutavad funktsioon TRANSPOSE muuta ridade, veergude 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 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 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 Klahvikombinatsiooni Ctrl + Shift + Enter:

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

    Ehkki sisestasite horisontaalse massiivikonstandi, teisendab funktsioon TRANSPOSE massiivikonstandi veeruks.

Ühemõõtmelise veeru transponeerimine

  1. Valige viiest tühjast lahtrist koosnev rida.

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

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

Ehkki sisestasite vertikaalse massiivikonstandi, teisendab funktsioon TRANSPOSE konstandi reaks.

Kahemõõtmelise konstandi transponeerimine

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

  2. Tippige valemiribale järgmine konstant ja vajutage Klahvikombinatsiooni Ctrl + Shift + Enter:

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

    Funktsioon TRANSPOSE teisendab iga rea veerujadaks.

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

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

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

Massiivi loomine olemasolevate väärtuste põhjal

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

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

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

  2. Vajutage Klahvikombinatsiooni Ctrl + Shift + Enter, mis sisestab selle arvumassiivi lahtrivahemik C8: E10 Massiivivalemi abil. Töölehel C8 kuni E10 peaks välja nägema umbes järgmine:

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Valige lahtrivahemik C1 kuni E3.

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

    =C8:E10

    Näete C8 kuni E10 samad väärtused lahtrites C1 kuni E3 kuvatakse 3 x 3 massiiv lahtrid.

Massiivikonstandi loomine olemasolevate väärtuste põhjal

  1. Lahtritega C1: C3 valitud, vajutage klahvi F2 redigeerimisrežiimi aktiveerimiseks.

  2. Vajutage klahvi F9 lahtriviited väärtusteks teisendada. Excel teisendab väärtused massiivikonstant. Valem peaks nüüd olema = {10,20,30 40,50,60; 70,80,90}.

  3. Vajutage Klahvikombinatsiooni Ctrl + Shift + Enter , et massiivikonstandi sisestamiseks massiivivalemina.

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 seejärel vajutage Klahvikombinatsiooni Ctrl + Shift + Enter , et näha lahtrite A2: A6 (66) märkide arv.

  3. Valige lahter A10 ja seejärel vajutage Klahvikombinatsiooni Ctrl + Shift + Enter , et näha pikemat sisu (lahter A3) lahtrite A2: A6 kohta.

Kasutatakse järgmist valemit lahtrisse A8 loendab märkide (66) lahtrites A2 kuni A6 maksimaalne arv.

=SUM(LEN(A2:A6))

Sel juhul, tagastab funktsioon LEN iga stringi pikkus iga lahtrid vahemikus. Funktsioon SUM , siis neid väärtusi liidab ja kuvab tulemi (66).

N väiksema väärtuse leidmine vahemikus

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

  1. Sisestage mõnda juhusliku arvu lahtrite A1:A11.

  2. Valige lahtrid C1 kuni C3. Nende lahtrite hoiab massiivivalemit tulemid.

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

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

See valem kasutab massiivikonstant Väiksed funktsiooni väärtustada kolm korda ja väikseim (1), teine vähim (2) ja kolmas väikseim (3) liikmete massiivis, mis sisaldub lahtrites a1: A10 otsimiseks veel väärtusi, saate lisada mitu argumenti on konstandi. Saate täiendavate funktsioonide valemiga, näiteks SUM ja AVERAGE. Näiteks:

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

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

N vahemiku 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 valem valemiribale ja vajutage Klahvikombinatsiooni Ctrl + Shift + Enter:

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

Selles etapis võib aidata leida veidi rida ja INDIRECT funktsioonide kohta. Saate luua hulgaliselt järjestikust täisarvude funktsiooni ROW . Näiteks valige tühja veergu 10 lahtrite harjutused töövihikus, sisestage 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 varem kasutatud valem – = suur (A5:A14,ROW(INDIRECT("1:3"))) – alates sisemine sulgude ja tööd väljapoole: INDIRECT , tagastab funktsioon tekstiliste väärtuste kogumi sel juhul väärtused 1 – 3. Funktsioon ROW loob omakorda kolme-lahtri piklikku massiiv. Funktsiooni LARGE kasutab väärtused lahtrivahemik A5:A14 ja seda hinnatakse kolm korda, kui iga tagastatud funktsioon ROW viite. Kolme-lahtri piklikku massiiv tagastatakse väärtused 3200, 2700 ja 2000. Kui soovite leida rohkem väärtusi, saate lisada funktsiooni INDIRECT suurem sisaldav lahtrivahemik.

Nimega varasemaid näiteid, saate selle valemi koos muude funktsioonidega, näiteks SUM ja AVERAGE.

Pikima tekstistringi leidmine lahtrivahemikus

Minge tagasi varasema teksti stringi näide, sisestage järgmine valem tühi lahter ja vajutage Klahvikombinatsiooni Ctrl + Shift + Enter:

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

Tekst "lahtrivahemik, mis" kuvatakse.

Vaatame lähemalt valemit, alates sisemine elemente ja töötamise väljapoole. Funktsioon LEN tagastab kõigi üksuste pikkus lahtrit vahemikus A2: a6. Funktsioon MAX arvutab hulgast suurimat väärtust need üksused, mis vastab pikima tekstistringi, mis on 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.

Lõpuks funktsiooni INDEX on need argumenti: massiivi ja selle massiivi ridade ja veergude arvu. Lahtrivahemiku A2: A6 pakub massiiv, funktsioon MATCH loob lahtri aadressi ja lõplik argumendi (1) saate määrata, 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))

Vastuvõtuteatis

Selles artiklis osad põhinevad Exceli Power kasutaja veergude Colin Wilcox kirjutanud ja kohandatud lõikudesse 14 ja 15 Exceli 2002 valemeid, John Walkenbach endise Exceli MVP raamat sarja.

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ünaamiliste Massiivivalemite vs pärand CSE Massiivivalemite

Funktsioon FILTER

Funktsioon RANDARRAY

Funktsioon SEQUENCE

Funktsioon SINGLE

Funktsioon SORT

Funktsioon SORTBY

Funktsioon UNIQUE

Exceli tõrked #SPILL!

Valemite ülevaade

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.

×