Masyvo formulių gairės ir pavyzdžiai

Masyvo formulių gairės ir pavyzdžiai

Pastaba: Norėtume jums kuo greičiau pateikti naujausią žinyno turinį jūsų kalba. Šis puslapis išverstas automatiškai, todėl gali būti gramatikos klaidų ar netikslumų. Mūsų tikslas – padaryti, kad šis turinys būtų jums naudingas. Gal galite šio puslapio apačioje mums pranešti, ar informacija buvo naudinga? Čia yra straipsnis anglų kalba, kuriuo galite pasinaudoti kaip patogia nuoroda.

Masyvo formulė yra formulė, kuri gali atlikti kelis skaičiavimus viename arba keliuose masyvo elementuose. Galite pagalvoti apie masyvą kaip reikšmių eilutę arba stulpelį arba reikšmių eilučių ir stulpelių kombinaciją. Masyvo formulės gali pateikti kelis rezultatus arba vieną rezultatą.

Pradedant rugsėjo 2018 naujinimas, skirtas " Office 365", bet kuri formulė, kuri gali pateikti kelis rezultatus, automatiškai išsiskirtų juos žemyn arba į kaimyninius langelius. Šis veikimo pokytis taip pat susijęs su keliomis naujomis dinaminio masyvo funkcijomis. Dinaminių masyvo formulių, neatsižvelgiant į tai, ar jos naudoja esamas funkcijas, ar dinaminio masyvo funkcijas, tereikia įvesti į vieną langelį, tada patvirtinti paspausdami " įvesti". Anksčiau senstelėjusios masyvo formulės reikalauja pirmiausia pažymėti visą išvesties intervalą, tada patvirtinti formulę "Ctrl" + "Shift" +". Jos paprastai vadinamos CSE formulėmis.

Masyvo formules galite naudoti sudėtingoms užduotims atlikti, pvz.:

  • Greitai kurkite pavyzdinių duomenų rinkinius.

  • Suskaičiuok langelių diapazonui esančių simbolių skaičių.

  • Sumuoti tik tuos skaičius, kurie atitinka tam tikras sąlygas, pvz., mažiausias reikšmes intervale, arba skaičius, kurie patenka tarp viršutinės ir apatinės ribos.

  • Sumuoti kiekvieną Nth reikšmę reikšmių intervale.

Toliau pateiktuose pavyzdžiuose parodyta, kaip kurti kelių langelių ir vieno langelio masyvo formules. Kai įmanoma, pateikiame pavyzdžius su kai kuriomis dinaminio masyvo funkcijomis, taip pat esamas masyvo formules, įvestas kaip dinaminės ir senstelėjusios matricos.

Atsisiųskite mūsų pavyzdžius

Atsisiųskite pavyzdinę darbaknygę su visais masyvo formulių pavyzdžiais šiame straipsnyje.

Šis pratimas vaizduoja, kaip naudoti kelių langelių ir vieno langelio formules pardavimo skaičių rinkiniui apskaičiuoti. Pirmasis veiksmų rinkinys naudoja kelių langelių formulę, kad apskaičiuotų tarpinių sumų rinkinį. Antrasis rinkinys naudoja vieno langelio formulę bendrajai sumai apskaičiuoti.

  • Kelių langelių masyvo formulės

    Kelių langelių masyvo funkcija, esanti langelyje H10 = F10: F19 * G10: G19 norint apskaičiuoti pagal vieneto kainą parduotų automobilių skaičių

  • Čia apskaičiuojame bendrą "kupė" ir sedanų pardavimą kiekvienam pardavėjui įvesdami = F10: F19 * G10: G19 langelyje H10.

    Paspaudę klavišą "" ", matysite rezultatus, kurie bus nuplepę iki langelių H10: H19. Atkreipkite dėmesį, kad pašalinis diapazonas paryškinamas su kraštine, kai pasirenkate bet kurį langelį išsiliejimų diapazono ribose. Taip pat galite pastebėti, kad langelių formulės H10: H19 yra pilkos. Jie tiesiog yra nuoroda, todėl jei norite pakoreguoti formulę, turėsite pasirinkti langelį H10, kur yra pagrindinė formulė.

  • Vieno langelio masyvo formulė

    Vieno langelio masyvo formulė, skirta sumuoti bendrąją sumą su = SUM (F10: F19 * G10: G19)

    Darbaknygės pavyzdžio langelyje, įveskite arba nukopijuokite ir įklijuokite = SUM (F10: F19 * G10: G19), tada paspauskite klavišą " įvesti".

    Šiuo atveju "Excel" Sudaugina masyvo reikšmes (langelių diapazono F10 per G19), tada naudoja funkciją SUM, kad įtrauktumėte sumas kartu. Gautas rezultatas yra bendroji pardavimo suma, lygi 1 590 000 EUR.

    Šis pavyzdys vaizduoja, kokios naudingos gali būti tokio tipo formulės. Pavyzdžiui, įsivaizduokite, kad turite 1 000 duomenų eilučių. Galite sumuoti dalį arba visus duomenis sukurdami masyvo formulę viename langelyje, o ne vilkti formulę per 1 000 eilučių. Taip pat Atkreipkite dėmesį, kad vieno langelio formulė, esanti langelyje H20, yra visiškai nepriklausoma nuo kelių langelių formulės (formulė langeliuose H10 per H19). Tai dar vienas masyvo formulių naudojimo privalumas – lankstumas. Galite pakeisti kitas formules stulpelyje H nepažeisdami formulės H20. Taip pat gali būti naudinga, kad tokios bendrosios sumos būtų nepriklausomos, nes padeda patvirtinti jūsų rezultatų tikslumą.

  • Dinaminės masyvo formulės taip pat siūlo šiuos pranašumus:

    • Nuoseklumas    Jei spustelėsite bet kurį iš "H10" esančių langelių, matysite tą pačią formulę. Toks nuoseklumas padeda užtikrinti didesnį tikslumą.

    • Saugumas    Negalite perrašyti kelių langelių masyvo formulės komponento. Pavyzdžiui, spustelėkite langelio H11 ir paspauskite klavišą DELETE. "Excel" nekeis masyvo išvesties. Norėdami jį pakeisti, turite pažymėti masyvo arba langelio H10 viršutinį kairįjį langelį.

    • Mažesni failų dydžiai    Dažnai galite naudoti vieną masyvo formulę vietoj kelių tarpinių formulių. Pavyzdžiui, automobilių pardavimo pavyzdyje naudojama viena masyvo formulė, kuri apskaičiuoja rezultatus stulpelyje E. Jei naudojote standartines formules, pvz., = F10 * G10, F11 * G11, F12 * G12 ir kt., jūs naudojote 11 skirtingų formulių, kad apskaičiuotumėte tuos pačius rezultatus. Tai nėra baisi, bet ką daryti, jei turėjote tūkstančius eilučių iš viso? Tada jis gali padaryti didelį skirtumą.

    • Veiksmingumas    Masyvo funkcijos gali būti efektyvus būdas sudėtingoms formulėms kurti. Masyvo formulė = SUM (F10: F19 * G10: G19) yra tokia pati: = SUM (F10 * G10, F11 * G11, F12 * G12, F13 * G13, F14 * G14, F15 * G15, F16 * G16, F17 * G17, F18 * G18, F19 * G19).

    • Praliejimas    Dinaminio masyvo formulės bus automatiškai išliejamos į išvesties intervalą. Jei šaltinio duomenys yra "Excel" lentelėje, jūsų dinaminių masyvo formulių duomenys bus automatiškai pakeisti pridedant arba šalinant duomenis.

    • #SPILL! klaidos    Dinaminiai masyvai pristatė #SPILL! klaidą, kuriame nurodoma, kad dėl kažkokių priežasčių blokuojamas numatytas išsiliejimų intervalas. Kai išspręsite užsikimšimą, formulė automatiškai išsiliejs.

Masyvo konstantos yra masyvo formulių komponentas. Masyvo konstantos kuriamos įvedant elementų sąrašą ir rankiniu būdu įtraukiant sąrašą į riestinius skliaustus ({ }), štai taip:

= {1, 2, 3, 4, 5} arba = {"sausis", "Vasaris", "kovas"}

Jei atskirsite elementus naudodami kablelius, sukursite horizontalųjį masyvą (eilutę). Jei atskirsite elementus naudodami kabliataškius, sukursite vertikalųjį masyvą (stulpelį). Norėdami sukurti dvimatę masyvą, atskirkite elementus kiekvienoje eilutėje kableliais ir kiekvieną eilutę atskirkite kabliataškiais.

Šios procedūros padės išmokti kurti horizontaliąsias, vertikaliąsias ir dvimates konstantas. Rodysime pavyzdžius, naudodami funkciją seka , kad automatiškai sugeneruotumėte masyvo konstantas, taip pat rankiniu būdu įvestos masyvo konstantos.

  • Horizontaliosios konstantos kūrimas

    Naudokite ankstesnių pavyzdžių darbaknygę arba sukurkite naują. Pažymėkite bet kurį tuščią langelį ir įvedimo = seka (1,5). Funkcija SEQUENCE sukuria 1 eilutę 5 stulpelių masyvą taip pat, kaip = {1, 2, 3, 4, 5}. Rodomas toks rezultatas:

    Horizontalaus masyvo konstantos su = sekos (1,5) arba = {1, 2, 3, 4, 5} kūrimas

  • Vertikaliosios konstantos kūrimas

    Pažymėkite bet kurį tuščią langelį, kuriame yra po juo esanti patalpa, ir įveskite= seka (5)arba = {1; 2; 3; 4; 5}. Rodomas toks rezultatas:

    Vertikalaus masyvo konstantos kūrimas naudojant = SEQUENCE (5) arba = {1; 2; 3; 4; 5}

  • Dvimatės konstantos kūrimas

    Pažymėkite bet kurį tuščią langelį, kuriame yra kambario dešinėje ir po juo, ir įveskite= seka (3, 4). Rodomas toks rezultatas:

    3 eilutės kūrimas 4 stulpelių masyvo konstanta su = seka (3, 4)

    Taip pat galite įvesti: arba = {1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12}, bet jūs norite atkreipti dėmesį į vietą, kur galite įdėti pusiau dvitaškiais.

    Kaip matote, parinktis seka suteikia daug pranašumų neautomatiškai įvedant masyvo konstantas. Pirmiausia, jis taupo jūsų laiką, tačiau taip pat gali padėti sumažinti klaidų neautomatinį įvedimą. Taip pat lengviau skaityti, ypač, nes pusiau dvitaškiais gali būti sunku atskirti nuo kableliais skyriklių.

Toliau pateikiamas pavyzdys, kuris naudoja masyvo konstantas kaip didesnės formulės dalį. Darbaknygės pavyzdyje eikite į formulės darbalapio konstantą arba sukurkite naują darbalapį.

Langelyje D9 įvedome = SEQUENCE (1, 5, 3, 1), bet taip pat galite įvesti 3, 4, 5, 6 ir 7 langelius A9: H9. Nėra nieko ypatingo apie tam tikrą skaičių žymėjimą, mes tiesiog pasirinkome ką nors, išskyrus 1-5, skirtą diferencijavimui.

Langelyje E11 = SUM (D9: H9 * seka (1, 5))arba = SUM (D9: H9 * {1, 2, 3, 4, 5}). Formulės grąžina 85.

Naudokite masyvo konstantas formulėse. Šiame pavyzdyje panaudojome = SUM (D9: H (* seka (1, 5))

Funkcija SEQUENCE sukuria masyvo konstantos ekvivalentą {1, 2, 3, 4, 5}. Kadangi programa "Excel" atlieka išraiškų operacijas, esančias skliaustuose, pirmiausia, kiti du elementai, kurie bus paleisti, yra langelių reikšmės D9: H9 ir daugybos operatorių (*). Šioje vietoje formulė padaugina reikšmes, esančias saugomajame masyve, iš atitinkamų konstantos reikšmių. Ji atitinka:

= SUM (D9 * 1, E9 * 2, F9 * 3, G9 * 4, H9 * 5)arba = SUM (3 * 1,4 * 2, 5 * 3, 6 * 4, 7 * 5)

Galiausiai funkcija SUM sudeda reikšmes ir grąžina 85.

Jei nenorite naudoti saugomo masyvo ir visiškai išlaikyti operaciją atmintyje, galite ją pakeisti kitu masyvo konstanta:

= SUM (1, 5, 3, 1) * seka (1, 5))arba = SUM ({3, 4, 5, 6, 7} * {1; 2; 3, 4, 5})

Elementai, kuriuos galite naudoti masyvo konstantoms

  • Masyvo konstantose gali būti skaičiai, tekstas, loginės reikšmės (pvz., TRUE (teisinga) ir klaidinga) ir klaidų reikšmės, pvz., #N/A. Galite naudoti skaičius sveikaisiais skaičiais, dešimtainiais skaičiais ir moksliniais formatais. Jei įtraukiate tekstą, jums reikia jį supa su kabutėmis ("tekstas").

  • Masyvų konstantose negali būti papildomų masyvų, formulių, arba funkcijų. Kitaip sakant, jose gali būti tik tekstas arba skaičiai, atskirti kableliais arba kabliataškiais. „Excel“ rodo įspėjimo pranešimą, kai įvedate tokią formulę, pvz.,1,2,A1:D4} arba {1,2,SUM(Q2:Z8)}. Be to, skaitinėse reikšmėse negali būti procentų ženklų, dolerio ženklų, kablelių ar kabučių.

Vienas iš geriausių masyvo konstantų naudojimo būdų yra juos pavadinti. Pavadintas konstantas galima daug lengviau naudoti, jos gali iš dalies paslėpti nuo kitų jūsų masyvo formulių sudėtingumą. Norėdami pavadinti masyvo konstantą ir naudoti ją formulėje, atlikite šiuos veiksmus:

Eikite į formulės _ Gt_ apibrėžtų vardų _ gt_ apibrėžti pavadinimą. Lauke pavadinimas įveskite ketvirtis1. Lauke Susijęs su įveskite šią konstantą (nepamirškite rankiniu būdu įvesti riestinių skliaustų):

={"Sausis","Vasaris","Kovas"}

Dialogo langas dabar turėtų atrodyti taip:

Pavadinto masyvo konstantos įtraukimas iš formulių _ Gt_ apibrėžtų vardų _ Gt_ name Manager _ Gt_ naujas

Spustelėkite gerai, tada pasirinkite bet kurią eilutę, kurioje yra trys tušti langeliai, ir įveskite = ketvirtis1.

Rodomas toks rezultatas:

Naudokite pavadintą masyvo konstantą formulėje, pvz., = Ketvirtis1, kur Ketvirtis1 buvo apibrėžtas kaip = {"sausis", "Vasaris", "kovas"}

Jei norite, kad rezultatai būtų išsilieję vertikaliai, o ne horizontaliai, galite naudoti =transponuoti(ketvirtis1).

Jei norite Rodyti 12 mėnesių sąrašą, pvz., galite naudoti finansinę ataskaitą, galite sukurti vieną iš dabartinių metų naudodami funkciją seka. Tvarkingas dalykas apie šią funkciją yra tas, kad nors rodo tik mėnuo, yra galiojanti data, kurią galite naudoti kituose skaičiavimuose. Šiuos pavyzdžius rasite "Excel" pavyzdžio masyvo konstantų ir sparčiųjų pavyzdžių rinkinio darbalapiuose.

= TEKSTAS (data (metai (šiandien), seka (1, 12), 1), "mmm")

TEKSTO, datos, metų, šiandienos ir sekos funkcijų derinio naudojimas norint sukurti dinaminį 12 mėnesių sąrašą

Tai naudoja funkciją DATE , kad sukurtų datą pagal dabartinius metus, o seka sukurs masyvo konstantą nuo 1 iki 12 sausio iki gruodžio, tada funkcija TEXT konvertuoja rodymo formatą į "mmm" (Jan, vasaris, Maras ir kt.). Jei norite, kad būtų rodomas visas mėnuo, pvz., sausis, turite naudoti "MMMM".

Kai naudojate pavadintą konstantą kaip masyvo formulę, Atminkite, kad įvestumėte lygybės ženklą, kaip "Ketvirtis1", o ne tik Ketvirtis1. Jei to nepadarysite, programa „Excel“ laikys masyvą tekstine eilute ir jūsų formulė tinkamai neveiks. Galiausiai Turėkite omenyje, kad galite naudoti funkcijų, teksto ir skaičių derinius. Viskas priklauso nuo to, kaip kūrybiškai norite gauti.

Toliau pavaizduoti keli būdai, kaip galima naudoti masyvo konstantas masyvo formulėse. Kai kurie pavyzdžiai naudoja funkciją transponuoti , kad konvertuotų eilutes į stulpelius ir atvirkščiai.

  • Kelių elementų masyvas

    Įvedimas = seka (1, 12) * 2arba = {1, 2, 3, 4; 5; 6, 7, 8; 9, 10, 11, 12} * 2

    Taip pat galite padalinti su (/), įtraukti (+) ir atimti (-).

  • Masyvo elementų kėlimas kvadratu

    Įvedimas = seka (1, 12) ^ 2arba = {1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12} ^ 2

  • Kvadratinės šaknies iš masyvo elementų radimas

    Įvedimas =SQRT(seka (1, 12) ^ 2)arba = SQRT ({1; 2; 3; 4; 5; 6, 7, 8; 9, 10, 11, 12} ^ 2)

  • Vienmatės eilutės transponavimas

    Įvedimas = transponavimas (seka (1, 5))arba = transponuoti ({1, 2, 3, 4, 5})

    Nors įvedėte horizontaliojo masyvo konstantą, funkcija TRANSPOSE konvertuoja masyvo konstantą į stulpelį.

  • Vienmačio stulpelio transponavimas

    Įvedimas = transponavimas (seka (5, 1))arba = transponavimas ({1; 2; 3; 4; 5})

    Nors įvedėte vertikaliojo masyvo konstantą, funkcija TRANSPOSE konvertuoja masyvo konstantą į eilutę.

  • Dvimatės konstantos transponavimas

    Įvedimas = transponavimas (seka (3, 4))arba = transponuoti ({1; 2; 3; 4; 5; 6, 7, 8; 9, 10, 11, 12})

    Funkcija TRANSPOSE konvertuos kiekvieną eilutę į stulpelių seką.

Šiame skyriuje pateikiami pagrindinių masyvo formulių pavyzdžiai.

  • Masyvo kūrimas pagal esamas reikšmes

    Toliau pateiktame pavyzdyje aiškinama, kaip naudoti masyvo formules norint sukurti naują masyvą iš esamo masyvo.

    Įvedimas = seka (3, 6, 10, 10)arba = {10, 20, 30, 40, 50, 60; 70, 80, 90100110120; 130140150160170180}

    Įsitikinkite, kad įvesite {(atidaromąjį riestinį skliaustelį) prieš įvesdami 10 ir} (uždarymo skliausteliai) įvedus 180, nes kuriate skaičių masyvą.

    Tada lauke įrašyti = D9 #arba = D9: I11 tuščiame langelyje. Rodomas 3 x 6 langelių masyvas su tomis pačiomis reikšmėmis, kurios rodomos D9: D11. # Ženklas vadinamas išsiliejo intervalo operatoriumiir jis yra "Excel" būdas nurodyti visą masyvo sritį, o ne įvesti jį.

    Naudoti išsiliejo intervalo operatorių (#), kad būtų galima nurodyti esamą masyvą

  • Masyvo konstantos kūrimas pagal esamas reikšmes

    Galite naudoti išsiliejuto masyvo formulės rezultatus ir konvertuoti į jo sudedamąsias dalis. Pasirinkite langelio D9, tada paspauskite F2 , kad pereitumėte į redagavimo režimą. Tada paspauskite F9 , kad konvertuotumėte langelio nuorodas į reikšmes, kurios "Excel" konvertuoja į masyvo konstantą. Kai paspausite klavišą "" ", formulė = D9 #, dabar turėtų būti = {10, 20, 30; 40, 50, 60; 70, 80, 90}.

  • Langelių diapazono simbolių skaičiavimas

    Toliau pateiktame pavyzdyje parodyta, kaip perskaičiuoti simbolių skaičių langelių intervale. Tai yra tarpai.

    Skaičiuoti bendrą simbolių skaičių ir kitus masyvus, skirtus dirbti su teksto eilutėmis

    = SUM (LEN (C9: C13))

    Šiuo atveju Funkcija LEN pateikia kiekvienos teksto eilutės ilgį kiekviename srities langelyje. Funkcija SUM tada įtraukia šias reikšmes kartu ir rodo rezultatą (66). Jei norite gauti vidutinį simbolių skaičių, galite naudoti:

    = AVERAGE (LEN (C9: C13))

  • Ilgiausio langelio kiekis diapazono C9: C13

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

    Ši formulė veikia tik tada, kai duomenų diapazoną sudaro vienas langelių stulpelis.

    Žvilgtelėkime į formulę pradėdami nuo vidinių elementų. Funkcija LEN grąžina kiekvieno elemento, esančio langelių diapazono D2: D6, ilgį. Funkcija Max apskaičiuoja didžiausią reikšmę tarp tų elementų, kurie atitinka ilgiausią teksto eilutę, esančią langelyje D3.

    Čia viskas tampa sudėtingiau. Funkcija Match apskaičiuoja langelio, kuriame yra ilgiausia teksto eilutė, poslinkį (sąlyginę padėtį). Norint tai padaryti, reikalingi trys argumentai: peržvalgos reikšmė, peržvalgos masyvas ir atitikimo tipas. Funkcija MATCH peržvalgos masyve ieško nurodytos peržvalgos reikšmės. Šiuo atveju peržvalgos reikšmė yra ilgiausia tekstinė eilutė:

    MAKS (LEN (C9: C13)

    Ši eilutė yra šiame masyve:

    LEN (C9: C13)

    Šiuo atveju argumentas Match Type yra 0. Atitikties tipas gali būti 1, 0 arba-1 reikšmė.

    • 1 – pateikia didžiausią reikšmę, kuri yra mažesnė arba lygi peržvalgos val

    • 0 – grąžina pirmąją reikšmę, tiksliai lygią peržvalgos reikšmei

    • -1 – pateikia mažiausią reikšmę, kuri yra didesnė už nurodytą peržvalgos reikšmę arba jai lygi.

    • Jei praleisite atitikimo tipą, programa „Excel“ laikys, kad ji yra 1.

    Galiausiai funkcija index atsižvelgia į šiuos argumentus: masyvas, eilutės ir stulpelio numeris tame masyve. Langelių diapazonas C9: C13 pateikia masyvas, funkcija MATCH pateikia langelio adresą ir galutinis argumentas (1) nurodo, kad reikšmė gaunama iš pirmo masyvo stulpelio.

    Jei norite gauti mažiausios teksto eilutės turinį, aukščiau pateiktame pavyzdyje pakeistumėte min. maksimalų.

  • n mažiausių reikšmių radimas diapazone

    Šiame pavyzdyje parodyta, kaip ieškoti trijų mažiausių reikšmių langelių diapazonuose, kuriuose yra duomenų pavyzdys langeliuose B9: B18has buvo sukurtas: = int (Randarray(10, 1) * 100). Nepamirškite, kad funkcija RANDARRAY yra nepastovi funkcija, todėl kiekvieną kartą, kai "Excel" apskaičiuoja, gausite naują atsitiktinių skaičių rinkinį.

    "Excel" masyvo formulė, skirta Nth mažiausiai reikšmei: = SMALL (B9 #, seka (D9))

    Įvedimas = Small (B9 #, seka (D9), = Small (B9: B18, {1; 2; 3})

    Ši formulė naudoja masyvo konstantą, kad tris kartus įvertintų mažą funkciją ir grąžintų mažiausius 3 narius masyve, esančiame langeliuose B9: B18, kur 3 yra kintamojo dydžio langelyje D9. Jei norite sužinoti daugiau reikšmių, galite padidinti funkciją seka arba pridėti daugiau argumentų prie konstantos. Taip pat su šia formule galite naudoti papildomas funkcijas, pvz., SUM arba AVERAGE. Pavyzdžiui:

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

    = AVERAGE (MAŽA (B9 #, SEKA (D9))

  • n didžiausių reikšmių radimas diapazone

    Norėdami surasti didžiausias intervalo reikšmes, galite pakeisti MAŽĄ funkciją, naudodami funkciją Large. Be to, šiuose pavyzdžiuose naudojamos funkcijos ROW ir INDIRECT.

    Įvestumėte = Large (B9 #, Row (netiesioginė ("1:3"))) arba = Large (B9: B18, Row (netiesioginė ("1:3 ")))

    Čia būtų naudinga žinoti šiek tiek apie funkcijas ROW ir INDIRECT. Galite naudoti funkciją ROW, kad sukurtumėte iš eilės einančių sveikųjų skaičių masyvą. Pvz., pažymėkite tuščią ir įvedimo:

    =ROW(1:10)

    Formulė sukurs 10 iš eilės einančių sveikųjų skaičių stulpelį. Norėdami peržiūrėti galimą problemą, įterpkite eilutę virš diapazono, kuriame yra masyvo formulė (t. y. virš 1 eilutės). "Excel" koreguoja eilučių nuorodas, o formulė dabar generuoja sveikuosius skaičius nuo 2 iki 11. Norėdami pašalinti šią problemą, į formulę įtraukite funkciją INDIRECT:

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

    Funkcija netiesiogiai naudoja teksto eilutes kaip argumentus (todėl intervalas 1:10 yra apsuptas kabučių). „Excel“ nekoreguoja tekstinių reikšmių, kai įterpiate eilutes arba kitaip perkeliate masyvo formulę. Funkcija ROW sugeneruoja jūsų norimų sveikųjų skaičių masyvą. Galite taip pat lengvai naudoti SEKĄ:

    = SEKA (10)

    Išnagrinėkime anksčiau naudotą formulę – = LARGE (B9 #, ROW (netiesioginė ("1:3"))), pradedant nuo vidinių skliaustelių ir darbo išorėje: funkcija netiesioginė pateikia teksto reikšmių rinkinį, šiuo atveju – reikšmes nuo 1 iki 3. Funkcija ROW sukuria trijų langelių stulpelių masyvą. Funkcija LARGE naudoja langelių diapazono B9: B18 reikšmes ir ji vertinama tris kartus, kiekvieną kartą nurodant kiekvieną nuorodą, kurią grąžina funkcija ROW. Jei norite sužinoti daugiau reikšmių, į netiesioginę funkciją įtraukiate daugiau langelių diapazono. Galiausiai, kaip ir su mažais pavyzdžiais, galite naudoti šią formulę su kitomis funkcijomis, pvz., SUM ir AVERAGE.

  • Diapazono, kuriame yra klaidos reikšmių, sumavimas

    "Excel" funkcija SUM neveikia, kai bandote sudėti intervalą, kuriame yra klaidos reikšmė, pvz., #VALUE! arba #N/A. Šiame pavyzdyje parodyta, kaip susumuoti reikšmes, esančias diapazonui pavadinti duomenys, kuriuose yra klaidų:

    Norėdami susidoroti su klaidomis, naudokite masyvus. Pvz., = SUM (IF (ISERROR (duomenys), "", duomenys) susumuoja intervalą pavadintus duomenis, net jei jame yra klaidų, pvz., #VALUE! arba #NA!.

  • =SUM(IF(ISERROR(Duomenys),"",Duomenys))

    Formulė sukuria naują masyvą, kuriame yra pirminės reikšmės, atėmus visas klaidos reikšmes. Pradedant nuo vidinių funkcijų ir darbo išorėje, funkcija ISERROR ieško klaidų langelių diapazono (duomenys). Funkcija IF pateikia konkrečią reikšmę, jei jūsų nurodyta sąlyga įvertinama kaip TRUE ir kitą reikšmę, jei ji įvertinama kaip KLAIDINGAI. Šiuo atveju ji pateikia tuščias eilutes ("") visoms klaidų reikšmėms, nes jos įvertina kaip TRUE, ir pateikia likusias reikšmes iš intervalo (duomenys), nes jos įvertina kaip klaidinga, o tai reiškia, kad juose nėra klaidų reikšmių. Funkcija SUM apskaičiuoja filtruoto masyvo sumą.

  • Diapazono klaidos reikšmių skaičiaus apskaičiavimas

    Šis pavyzdys yra panašus į ankstesnę formulę, tačiau jis grąžina klaidų reikšmių skaičių diapazonui pavadinti duomenys, o ne filtruoti:

    =SUM(IF(ISERROR(Duomenys),1,0))

    Ši formulė sukuria masyvą, kuriame yra langelių su klaidomis reikšmė 1 ir langelių be klaidų reikšmė 0. Galite supaprastinti formulę ir gauti tą patį rezultatą pašalindami trečiąjį funkcijos IF argumentą, kaip pvz.:

    =SUM(IF(ISERROR(Duomenys),1))

    Jei nenurodysite argumento, funkcija IF grąžins reikšmę FALSE, kai langelyje nebus klaidos reikšmės. Galite supaprastinti formulę dar labiau:

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

    Ši versija veikia, nes TRUE*1=1 ir FALSE*1=0.

Kartais reikia sumuoti reikšmes pagal sąlygas.

Naudodami matricas galite apskaičiuoti pagal tam tikras sąlygas. = SUM (IF (Sales>0, pardavimas)) susumuos visas reikšmes, kurios yra didesnės nei 0 diapazonui, vadinamam pardavimais.

Pvz., ši masyvo formulė sumuoja tik teigiamus sveikųjų skaičių diapazonus, pavadintus pardavime, kuris nurodo langelius E9: E24 aukščiau pateiktame pavyzdyje:

=SUM(IF(Pardavimas>0,Pardavimas))

Funkcija IF sukuria teigiamų ir neteisingų reikšmių masyvą. Funkcija SUM nepaiso klaidingų reikšmių, nes 0+0=0. Langelių diapazonas, kurį naudojate šioje formulėje, gali būti sudarytas iš bet kokio eilučių ir stulpelių skaičiaus.

Taip pat galite sumuoti reikšmes, kurios atitinka daugiau nei vieną sąlygą. Pvz., ši masyvo formulė apskaičiuoja reikšmes, kurios yra didesnės nei 0 ir mažiau nei 2500:

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

Turėkite omenyje, kad ši formulė grąžina klaidą, jei diapazone yra vienas arba keli ne skaitiniai langeliai.

Taip pat galite kurti masyvo formules, kurios naudoja sąlygos OR tipą. Pavyzdžiui, galite sumuoti reikšmes, kurios yra didesnės nei 0 arba mažiau nei 2500:

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

Negalite naudoti funkcijų AND ir OR tiesiogiai masyvo formulėse, nes šios funkcijos grąžina vieną rezultatą, TRUE arba FALSE, o masyvo funkcijoms būtini rezultatų masyvai. Galite išvengti problemos naudodami logiką, pavaizduotą ankstesnėje formulėje. Kitaip tariant, atliekate matematines operacijas, pvz., sudėties arba daugybos reikšmes, atitinkančias arba ir sąlygą.

Šiame pavyzdyje pavaizduota, kaip pašalinti nulius iš diapazono, kai norite apskaičiuoti to diapazono reikšmių vidurkį. Formulė naudoja duomenų intervalą, pavadintą Pardavimas:

=AVERAGE(IF(Pardavimas<>0,Pardavimas))

Funkcija IF sukuria reikšmių, kurios nėra lygios 0, masyvą ir perduoda tas reikšmes funkcijai AVERAGE.

Ši masyvo formulė palygina dviejų langelių diapazonų, pavadintų ManoDuomenys ir TavoDuomenys, reikšmes ir grąžina jų skirtumų skaičių. Jei dviejų diapazonų turinys yra identiškas, formulė grąžina reikšmę 0. Norint naudoti šią formulę, langelių diapazonai turi būti tokio pat dydžio ir tos pačios dimensijos. Pavyzdžiui, jei MyData yra 3 eilučių pagal 5 stulpelius intervalas, YourData taip pat turi būti 3 eilutės pagal 5 stulpelius:

=SUM(IF(ManoDuomenys=TavoDuomenys,0,1))

Formulė sukuria naują masyvą, kurio dydis yra toks pat kaip diapazonai, kuriuos lyginate. Funkcija IF užpildo masyvą reikšme 0 ir 1 (0 – neatitikimai, 1 – identiški langeliai). Funkcija SUM grąžina masyvo reikšmių sumą.

Galite supaprastinti formulę:

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

Kaip formulė, skaičiuojanti klaidos reikšmes diapazone, ši formulė veikia todėl, kad TRUE*1=1, o FALSE*1=0.

Ši masyvo formulė grąžina vieno stulpelio diapazono Duomenys maksimalios reikšmės eilutės numerį:

=MIN(IF(Duomenys=MAX(Duomenys),ROW(Duomenys),""))

Funkcija IF sukuria naują masyvą, kuris atitinka diapazoną, pavadintą Duomenys. Jei atitinkamame langelyje yra maksimali diapazono reikšmė, masyve yra eilutės numeris. Kitu atveju masyve yra tuščia eilutė (""). Funkcija MIN naudoja naująjį masyvą kaip savo antrąjį argumentą ir grąžina mažiausią reikšmę, kuri atitinka maksimalios diapazono Duomenys reikšmės eilutės numerį. Jei diapazone Duomenys yra identiškos maksimalios reikšmės, formulės grąžina pirmosios reikšmės eilutę.

Jei norite grąžinti faktinę maksimalios reikšmės langelio vietą, naudokite šią formulę:

=ADDRESS(MIN(IF(Duomenys=MAX(Duomenys),ROW(Duomenys),"")),COLUMN(Duomenys))

Panašių pavyzdžių rasite darbaknygės duomenų rinkinių skirtumai .

Šis pratimas vaizduoja, kaip naudoti kelių langelių ir vieno langelio formules pardavimo skaičių rinkiniui apskaičiuoti. Pirmasis veiksmų rinkinys naudoja kelių langelių formulę, kad apskaičiuotų tarpinių sumų rinkinį. Antrasis rinkinys naudoja vieno langelio formulę bendrajai sumai apskaičiuoti.

  • Kelių langelių masyvo formulės

Nukopijuokite visą lentelę ir įklijuokite ją į tuščią darbalapio langelį a1.

Pardavimas Asmuo

Automobilinė Įveskite

Numeris Parduota

Vienetas Kaina

Bendra suma Pardavimas

Juška

Sedanas

5

33000

Kupė

4

37000

Stankevičius

Sedanas

6

24000

Kupė

8

21000

Dūda

Sedanas

3

29000

Kupė

1

31000

Vilutis

Sedanas

9

24000

Kupė

5

37000

Barkauskas

Sedanas

6

33000

Kupė

8

31000

Formulė (Bendroji suma)

Bendroji suma

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

=SUM(C2:C11*D2:D11)

  1. Norėdami matyti bendrą kiekvieno pardavėjo kupė ir sedanų pardavimą, pažymėkite langelius E2: E11, įveskite formulę = C2: C11 * D2: D11, tada paspauskite Ctrl + Shift + "įveskite".

  2. Norėdami matyti visų pardavimų bendrąją sumą, pažymėkite langelį F11, įveskite formulę = SUM (C2: C11 * D2: D11), tada paspauskite Ctrl + Shift + įveskite.

Kai paspaudžiate Ctrl + Shift +"Excel", "Excel" sulygina formulę su skliausteliais ({}) ir įterpia formulės egzempliorių kiekviename pasirinkto diapazono langelyje. Tai atsitinka labai greitai, todėl stulpelyje E matysite kiekvieno pardavėjo kiekvieno automobilio tipo pardavimo bendrąją sumą. Pažymėję E2, pažymėkite E3, E4 ir t. t, pamatysite, kad rodoma ta pati formulė: {=C2:C11*D2:D11}

E stulpelio sumos apskaičiuojamos masyvo formule

  • Vieno langelio masyvo formulės kūrimas

Darbaknygės langelyje D13 įveskite toliau nurodytą formulę, tada paspauskite Ctrl + Shift +Type:

=SUM(C2:C11*D2:D11)

Šiuo atveju "Excel" Sudaugina masyvo reikšmes (langelių diapazonas C2 per D11), tada naudojama funkcija SUM, kad įtrauktumėte sumas kartu. Gautas rezultatas yra bendroji pardavimo suma, lygi 1 590 000 EUR. Šis pavyzdys vaizduoja, kokios naudingos gali būti tokio tipo formulės. Pavyzdžiui, įsivaizduokite, kad turite 1 000 duomenų eilučių. Galite sumuoti dalį arba visus duomenis sukurdami masyvo formulę viename langelyje, o ne vilkti formulę per 1 000 eilučių.

Taip pat Atkreipkite dėmesį, kad vieno langelio formulė langelyje D13 yra visiškai nepriklausoma nuo kelių langelių formulės (formulė langeliuose E2 per E11). Tai dar vienas masyvo formulių naudojimo privalumas – lankstumas. Galite pakeisti stulpelio E formules arba visiškai panaikinti šį stulpelį, nedarydami poveikio formulės D13 atveju.

Masyvo formulės taip pat užtikrina šiuos pranašumus:

  • Nuoseklumas    Jei spustelėsite bet kurį langelį žemyn nuo E2, visur bus rodoma ta pati formulė. Toks nuoseklumas padeda užtikrinti didesnį tikslumą.

  • Saugumas    Kelių langelių masyvo formulės komponento perrašyti negalima. Pavyzdžiui, spustelėkite langelį E3 ir paspauskite Delete. Turite pažymėti visą langelių diapazoną (E2–E11) ir pakeisti viso masyvo formulę, arba palikti masyvą tokį, koks jis yra. Kaip papildomą saugos priemonę, turite paspausti Ctrl + Shift + , kad patvirtintumėte bet kokį formulės pakeitimą.

  • Mažesni failų dydžiai    Dažnai galite naudoti vieną masyvo formulę vietoj kelių tarpinių formulių. Pvz., darbaknygė naudoja vieną masyvo formulę, kad apskaičiuotų rezultatus stulpelyje E. Jei naudojote standartines formules (pvz., = C2 * D2, C3 * D3, C4 * D4...), turėtumėte naudoti 11 skirtingų formulių, kad apskaičiuotumėte tuos pačius rezultatus.

Paprastai masyvo formulės naudoja standartinę formulės sintaksę. Visi jie prasideda lygybės (=) ženklu, o jūs galite naudoti daugumą įtaisytųjų "Excel" funkcijų masyvo formulėse. Esminis skirtumas yra tas, kad, naudodami masyvo formulę, paspauskite Ctrl + Shift + enteras , kad įvestumėte formulę. Kai tai padarysite, programa "Excel" apsups masyvo formulę su skliausteliais, jei skliaustelius įvesite neautomatiniu būdu, formulė bus konvertuota į teksto eilutę ir ji neveiks.

Masyvo funkcijos gali būti efektyvus būdas sudėtingoms formulėms kurti. Masyvo formulė =SUM(C2:C11*D2:D11) yra tokia pati kaip ši: =SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Svarbu: Paspauskite Ctrl + Shift + įvedate , kai reikia įtraukti masyvo formulę. Ši taisyklė taikoma tiek vieno langelio, tiek kelių langelių formulėms.

Kiekvieną kartą, kai dirbate su kelių langelių formulėmis, taip pat turite laikytis šių taisyklių:

  • Pažymėkite langelių, kuriuose bus jūsų rezultatai, diapazoną prieš įvesdami formulę. Tai darėte kurdami kelių langelių masyvo formulę, kai pažymėjote langelius nuo E2 iki E11.

  • Masyvo formulėje negalite keisti atskiro langelio turinio. Norėdami tai išbandyti, darbaknygėje pažymėkite langelį E3 ir paspauskite DELETE. "Excel" rodo pranešimą, nurodantį, kad negalite keisti masyvo dalies.

  • Galite perkelti arba panaikinti visą masyvo formulę, bet negalite perkelti arba naikinti jos dalies. Kitaip tariant, norėdami sutraukti masyvo formulę, pirmiausia panaikinkite esamą formulę ir pradėkite iš pradžių.

  • Norėdami panaikinti masyvo formulę, pažymėkite visą formulės intervalą (pvz ., E2: E11), tada paspauskite Delete.

  • Negalite įterpti tuščių langelių į kelių langelių masyvo formulę arba naikinti juos.

Kartais reikia išplėsti masyvo formulę. Pažymėkite pirmąjį esamo masyvo diapazono langelį ir tęskite, kol pažymėsite visą intervalą, kuriam norite taikyti formulę. Paspauskite F2 , kad redaguotumėte formulę, tada paspauskite Ctrl + Shift + įveskite , kad patvirtintumėte formulę, kai tik pakoreguojate formulės intervalą. Raktas yra pažymėti visą spektrą, pradedant nuo viršutinio kairiojo masyvo langelio. Viršutinis kairysis langelis yra tas, kuris bus redaguojamas.

Masyvo formulės yra nuostabios, bet jos turi ir trūkumų:

  • Kartais gali būti pamiršta paspausti Ctrl + Shift + SHIFT +. Tai gali nutikti net labiausiai patyrusiems "Excel" vartotojams. Nepamirškite paspausti šio klavišų derinio, kai įvedate arba redaguojate masyvo formulę.

  • Kiti vartotojai gali nesuprasti jūsų formulių. Paprastai masyvo formulės nedokumentuojamos darbalapyje. Todėl, jei kiti žmonės turi modifikuoti jūsų darbaknyges, turite vengti masyvo formulių arba įsitikinti, kad žmonės žino apie masyvo formules ir suprasti, kaip jas pakeisti, jei reikia.

  • Atsižvelgiant į jūsų kompiuterio apdorojimo greitį ir atmintį, didelės masyvo formulės gali sulėtinti skaičiavimą.

Masyvo konstantos yra masyvo formulių komponentas. Masyvo konstantos kuriamos įvedant elementų sąrašą ir rankiniu būdu įtraukiant sąrašą į riestinius skliaustus ({ }), štai taip:

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

Dabar jūs žinote, kad kurdami masyvo formules, turite paspausti Ctrl + Shift + "pradėti ". Kadangi masyvo konstantos yra masyvo formulių komponentas, konstantas su skliausteliais galite juos įvesti rankiniu būdu. Tada galite naudoti Ctrl + Shift + "įveskite ", kad įvestumėte visą formulę.

Jei atskirsite elementus naudodami kablelius, sukursite horizontalųjį masyvą (eilutę). Jei atskirsite elementus naudodami kabliataškius, sukursite vertikalųjį masyvą (stulpelį). Norėdami sukurti dvimatį masyvą, atskirkite kiekvienos eilutės elementus kableliais, o kiekvieną eilutę – kabliataškiais.

Štai masyvas vienoje eilutėje: {1, 2, 3, 4}. Štai masyvas viename stulpelyje: {1;2;3;4}. O štai dviejų eilučių ir keturių stulpelių masyvas: {1,2,3,4;5,6,7,8}. Dviejų eilučių masyve pirmoji eilutė yra 1, 2, 3 ir 4, o antroji 5, 6, 7 ir 8. Vienas kabliataškis skiria dvi eilutes, tarp 4 ir 5.

Kai dirbate su masyvo formulėmis, galite naudoti masyvo konstantas su bet kuriomis įtaisytosiomis „Enter“ funkcijomis. Šiuose skyriuose paaiškinama, kaip kurti kiekvieno tipo konstantą ir kaip naudoti šias konstantas su „Enter“ funkcijomis.

Šios procedūros padės išmokti kurti horizontaliąsias, vertikaliąsias ir dvimates konstantas.

Horizontaliosios konstantos kūrimas

  1. Tuščiame darbalapyje pažymėkite langelius nuo a1 iki E1.

  2. Formulės juostoje įveskite toliau nurodytą formulę ir paspauskite Ctrl + Shift + įveskite:

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

    Šiuo atveju turite įvesti atidarymo ir uždarymo skliaustelius ({}) ir "Excel" įtrauks antrąjį rinkinį.

    Rodomas toks rezultatas.

    Horizontaliojo masyvo konstanta formulėje

Vertikaliosios konstantos kūrimas

  1. Darbaknygėje pažymėkite penkių langelių stulpelį.

  2. Formulės juostoje įveskite toliau nurodytą formulę ir paspauskite Ctrl + Shift + įveskite:

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

    Rodomas toks rezultatas.

    Vertikaliojo masyvo konstanta masyvo formulėje

Dvimatės konstantos kūrimas

  1. Darbaknygėje pažymėkite keturių stulpelių pločio ir trijų eilučių aukščio langelių bloką.

  2. Formulės juostoje įveskite toliau nurodytą formulę ir paspauskite Ctrl + Shift + įveskite:

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

    Rodomas toks rezultatas:

    Dvimačio masyvo konstanta masyvo formulėje

Konstantų naudojimas formulėse

Štai paprastas konstantų naudojimo pavyzdys:

  1. Darbaknygės pavyzdyje sukurkite naują darbalapį.

  2. Langelyje A1 įrašykite 3, tada įrašykite 4 langelyje B1, 5 langelyje C1, 6 langelyje D1 ir 7 langelyje E1.

  3. A3 langelyje įveskite toliau nurodytą formulę, tada paspauskite Ctrl + Shift +Type:

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

    Atkreipkite dėmesį, kad „Excel“ įterpia konstantą į kitus riestinius skliaustus, nes įvedėte ją kaip masyvo formulę.

    Masyvo formulė su masyvo konstanta

    Langelyje A3 bus rodoma reikšmė 85.

Kitame skyriuje paaiškintas formulės veikimas.

Jūsų ką tik naudota formulė sudaryta iš kelių dalių.

Masyvo formulės su masyvo konstanta sintaksė

1. Funkcija

2. Saugomasis masyvas

3. Operatorius

4. Masyvo konstanta

Paskutinis elementas skliaustuose yra masyvo konstanta: {1,2,3,4,5}. Įsidėmėkite, kad programa „Excel“ neįrašo masyvo konstantų į skliaustus; jums reikia patiems juos įrašyti. Taip pat atminkite, kad pridėjus konstantą prie masyvo formulės, paspauskite Ctrl + Shift + įveskite , kad įvestumėte formulę.

Programa „Excel“ pirmiausia atlieka apskliaustų išraiškų operacijas, todėl paskesni du elementai yra darbaknygėje saugomos reikšmės (A1:E1) ir operatorius. Šioje vietoje formulė padaugina reikšmes, esančias saugomajame masyve, iš atitinkamų konstantos reikšmių. Ji atitinka:

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

Galiausiai, funkcija SUM prideda reikšmes, o suma 85 pasirodo langelyje A3.

Jei norite nenaudoti saugomojo masyvo ir palikti operaciją tik atmintyje, pakeiskite saugomąjį masyvą kita masyvo konstanta:

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

Norėdami tai išbandyti, nukopijuokite funkciją, pasirinkite tuščią darbaknygės langelį, įklijuokite formulę į formulės juostą, tada paspauskite Ctrl + Shift + "įveskite". Pamatysite tą patį rezultatą, kaip ankstesniame pratime, kur buvo naudota masyvo formulė:

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

Masyvo konstantose gali būti skaičių, teksto, loginių reikšmių (pvz., TRUE arba FALSE) ir klaidos reikšmių (pvz., #N/A). Galite naudoti sveikųjų skaičių, dešimtainės ir standartinės išraiškos formatus. Jeigu įtraukiate tekstą, turite išskirti tekstą kabutėmis (").

Masyvų konstantose negali būti papildomų masyvų, formulių, arba funkcijų. Kitaip sakant, jose gali būti tik tekstas arba skaičiai, atskirti kableliais arba kabliataškiais. „Excel“ rodo įspėjimo pranešimą, kai įvedate tokią formulę, pvz.,1,2,A1:D4} arba {1,2,SUM(Q2:Z8)}. Be to, skaitinėse reikšmėse negali būti procentų ženklų, dolerio ženklų, kablelių ar kabučių.

Vienas iš geriausių masyvo konstantų naudojimo būdų yra juos pavadinti. Pavadintas konstantas galima daug lengviau naudoti, jos gali iš dalies paslėpti nuo kitų jūsų masyvo formulių sudėtingumą. Norėdami pavadinti masyvo konstantą ir naudoti ją formulėje, atlikite šiuos veiksmus:

  1. Skirtuko Formulės grupėje Apibrėžti pavadinimai spustelėkite Apibrėžti pavadinimą.
    Rodomas dialogo langas apibrėžti pavadinimą .

  2. Lauke Pavadinimas įveskite Ketvirtis1.

  3. Lauke Susijęs su įveskite šią konstantą (nepamirškite rankiniu būdu įvesti riestinių skliaustų):

    ={"Sausis","Vasaris","Kovas"}

    Dialogo lango turinys atrodys taip:

    Dialogo langas Pavadinimo redagavimas su formule

  4. Spustelėkite Gerai, tada pažymėkite eilutę iš trijų tuščių langelių.

  5. Įveskite toliau nurodytą formulę ir paspauskite Ctrl + Shift + "įvesti".

    = Ketvirtis1

    Rodomas toks rezultatas.

    Pavadintasis masyvas, įvestas kaip formulė

Kai naudojate pavadintąją konstantą kaip masyvo formulę, nepamirškite įvesti lygybės ženklo. Jei to nepadarysite, programa „Excel“ laikys masyvą tekstine eilute ir jūsų formulė tinkamai neveiks. Galiausiai nepamirškite, kad galite naudoti teksto ir skaitmenų derinius.

Jei masyvo konstanta neveikia, gali būti , kad įvyko viena iš šių problemų:

  • Kai kurie elementai atskirti netinkamu simboliu. Jei praleidžiate kablelį arba kabliatašką arba padėjote po vieną netinkamoje vietoje, masyvo konstanta gali būti netinkamai sukurta arba gali būti rodomas įspėjamasis pranešimas.

  • Gali būti, kad pažymėjote langelių diapazoną, kuris neatitinka jūsų konstantos elementų skaičiaus. Pavyzdžiui, jei pažymėjote iš šešių langelių sudarytą stulpelį, kurį naudosite su penkių langelių konstanta, tuščiajame langelyje bus rodoma klaidos reikšmė #N/A. Taip pat, jei pažymėsite per mažai langelių, programa „Enter“ praleis reikšmes, neturinčias atitinkamo langelio.

Toliau pavaizduoti keli būdai, kaip galima naudoti masyvo konstantas masyvo formulėse. Kai kurie pavyzdžiai naudoja funkciją transponuoti , kad konvertuotų eilutes į stulpelius ir atvirkščiai.

Kiekvieno masyvo elemento dauginimas

  1. Sukurkite naują darbalapį ir pažymėkite keturių stulpelių pločio ir trijų eilučių aukščio tuščių langelių bloką.

  2. Įveskite toliau nurodytą formulę, tada paspauskite Ctrl + Shift +Type:

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

Masyvo elementų kėlimas kvadratu

  1. Pažymėkite keturių stulpelių pločio ir trijų eilučių aukščio tuščių langelių bloką.

  2. Įveskite šią masyvo formulę, tada paspauskite Ctrl + Shift +Type:

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

    Arba įveskite šią masyvo formulę, kuri naudoja intarpo operatorių (^):

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

Vienmatės eilutės transponavimas

  1. Pažymėkite iš penkių tuščių langelių sudarytą stulpelį.

  2. Įveskite toliau nurodytą formulę, tada paspauskite Ctrl + Shift +Type:

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

    Nors įvedėte horizontaliojo masyvo konstantą, funkcija TRANSPOSE konvertuoja masyvo konstantą į stulpelį.

Vienmačio stulpelio transponavimas

  1. Pažymėkite iš penkių tuščių langelių sudarytą eilutę.

  2. Įveskite toliau nurodytą formulę ir paspauskite Ctrl + Shift + įveskite:

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

Nors įvedėte vertikaliojo masyvo konstantą, funkcija TRANSPOSE konvertuoja masyvo konstantą į eilutę.

Dvimatės konstantos transponavimas

  1. Pažymėkite trijų stulpelių pločio ir keturių eilučių aukščio langelių bloką.

  2. Įveskite toliau nurodytą konstantą ir paspauskite Ctrl + Shift + "įveskite":

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

    Funkcija TRANSPOSE konvertuos kiekvieną eilutę į stulpelių seką.

Šiame skyriuje pateikiami pagrindinių masyvo formulių pavyzdžiai.

Masyvų ir masyvo konstantų kūrimas pagal esamas reikšmes

Šiame pavyzdyje paaiškinama, kaip naudoti masyvo formules norint sukurti saitus tarp skirtingų darbaknygių langelių diapazonų. Taip pat vaizduojama, kaip sukurti masyvo konstantą naudojant tą patį reikšmių rinkinį.

Masyvo kūrimas pagal esamas reikšmes

  1. Programos „Excel“ darbalapyje pažymėkite C8:E10 langelius ir įveskite formulę:

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

    Nepamirškite įvesti { (atidarantįjį riestinį skliaustą) prieš įvesdami 10 ir } (uždarantįjį riestinį skliaustą) įvedę 90, nes kuriate skaičių masyvą.

  2. Paspauskite Ctrl + Shift + enteras, kurie įveda šį skaičių masyvą langelių diapazono C8: E10 naudodami masyvo formulę. Darbalapyje, langeliai nuo C8 iki E10 turėtų atrodyti šitaip:

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Pažymėkite langelių nuo C1 iki E3 diapazoną.

  4. Formulės juostoje įveskite šią formulę, tada paspauskite Ctrl + Shift + įveskite:

    =C8:E10

    3x3 langelių masyvas pasirodo langeliuose C1 per E3 su tomis pačiomis reikšmėmis, kurias matote "C8" per E10.

Masyvo konstantos kūrimas pagal esamas reikšmes

  1. Pažymėję langelius C1: C3, paspauskite F2 , kad pereitumėte į redagavimo režimą. 

  2. Paspauskite F9 , kad konvertuotumėte langelio nuorodas į reikšmes. Programoje „Excel“ reikšmės konvertuojamos į masyvo konstantą. Formulė dabar turi būti = {10, 20, 30; 40, 50, 60; 70, 80, 90}.

  3. Paspauskite Ctrl + Shift + , kad įvestumėte masyvo konstantą kaip masyvo formulę.

Langelių diapazono simbolių skaičiavimas

Toliau parodyta, kaip apskaičiuoti langelių diapazono simbolių skaičių, įskaitant tarpus.

  1. Nukopijuokite visą lentelę ir įklijuokite ją į darbalapio A1 langelį.

    Duomenys

    Tai yra

    keli langeliai, kurie

    susijungia,

    kad suformuotų

    vieną sakinį.

    Bendra simbolių A2:A6 langeliuose suma

    =SUM(LEN(A2:A6))

    Ilgiausio langelio (A3) turinys

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

  2. Pažymėkite langelį A8, tada paspauskite Ctrl + Shift + įveskite , kad pamatytumėte bendrą simbolių skaičių langeliuose A2: A6 (66).

  3. Pažymėkite langelį A10, tada paspauskite Ctrl + Shift + , kad matytumėte ilgiausių langelių a2: A6 (A3 langelio) turinį.

Ši formulė naudojama langelyje A8 apskaičiuoja bendrą simbolių skaičių (66) langeliuose nuo a2 iki A6.

=SUM(LEN(A2:A6))

Šiuo atveju funkcija LEN grąžins kiekvienos kiekvieno diapazono langelio tekstinės eilutės ilgį. Funkcija SUM tada įtraukia šias reikšmes kartu ir rodo rezultatą (66).

n mažiausių reikšmių radimas diapazone

Čia parodyta, kaip rasti tris mažiausias langelių diapazono reikšmes.

  1. Langelyje A1: A11 surinkite keletą atsitiktinių skaičių.

  2. Pažymėkite langelius nuo C1 iki C3. Šiame langelių rinkinyje bus rodomi masyvo formulės grąžinti rezultatai.

  3. Įveskite toliau nurodytą formulę ir paspauskite Ctrl + Shift + įveskite:

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

Ši formulė naudoja masyvo konstantą, kad tris kartus įvertintų mažą funkciją ir grąžintų mažiausią (1), antrą mažiausią (2) ir trečią mažiausią (3) masyvo narius, esančius langeliuose a1: A10, kad rastumėte daugiau reikšmių, įtraukdami daugiau argumentų nuolat. Taip pat su šia formule galite naudoti papildomas funkcijas, pvz., SUM arba AVERAGE. Pavyzdžiui:

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

= AVERAGE (MAŽAS (A1: A10, {1; 2; 3})

n didžiausių reikšmių radimas diapazone

Norėdami rasti didžiausias diapazono reikšmes, galite pakeisti funkciją SMALL funkcija LARGE. Be to, šiuose pavyzdžiuose naudojamos funkcijos ROW ir INDIRECT.

  1. Pažymėkite langelius nuo D1 iki D3.

  2. Formulės juostoje įveskite šią formulę, tada paspauskite Ctrl + Shift + įveskite:

    = LARGE (A1: A10, ROW (NETIESIOGINĖ ("1:3")))

Čia būtų naudinga žinoti šiek tiek apie funkcijas ROW ir INDIRECT. Galite naudoti funkciją ROW, kad sukurtumėte iš eilės einančių sveikųjų skaičių masyvą. Pavyzdžiui, pasirinkite tuščią 10 langelių stulpelį, esantį jūsų pratybų darbaknygėje, įveskite šią masyvo formulę, tada paspauskite Ctrl + Shift + įveskite:

=ROW(1:10)

Formulė sukurs 10 iš eilės einančių sveikųjų skaičių stulpelį. Norėdami peržiūrėti galimą problemą, įterpkite eilutę virš diapazono, kuriame yra masyvo formulė (t. y. virš 1 eilutės). „Excel“ koreguos eilutės nuorodas, o formulės sugeneruos sveikuosius skaičius nuo 2 iki 11. Norėdami pašalinti šią problemą, į formulę įtraukite funkciją INDIRECT:

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

Funkcija INDIRECT kaip argumentus naudoja skirtingas tekstines eilutes (todėl diapazonas 1:10 įtrauktas į dvigubas kabutes). „Excel“ nekoreguoja tekstinių reikšmių, kai įterpiate eilutes arba kitaip perkeliate masyvo formulę. Funkcija ROW sugeneruoja jūsų norimų sveikųjų skaičių masyvą.

Pažvelkime į formulę, kurią naudojote anksčiau – = Large (A5: A14, Row (netiesioginė ("1:3"))) , pradedant nuo vidinių skliaustelių ir darbo išorėje: funkcija Netiesioginė pateikia teksto reikšmių rinkinį, šiuo atveju – reikšmes nuo 1 iki 3. Funkcija Row sukuria trijų langelių stulpelį. Funkcija Large naudoja reikšmes, esančias langelių diapazono A5: A14, ir ji vertinama tris kartus po kiekvienos nuorodos, kurią grąžino funkcija Row . Reikšmės 3200, 2700 ir 2000 yra grąžinamos į trijų langelių stulpelį. Jei norite sužinoti daugiau reikšmių, į netiesioginę funkciją įtraukiate daugiau langelių diapazono.

Kaip ir ankstesniuose pavyzdžiuose, šią formulę galite naudoti su kitomis funkcijomis, pvz., SUM ir AVERAGE.

Ilgiausios tekstinės eilutės radimas langelių diapazone

Grįžkite į ankstesnį teksto eilutės pavyzdį, tuščiame langelyje Įveskit šią formulę, tada paspauskite Ctrl + Shift +"klavišą":

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

Rodomas tekstas "langelių krūva".

Žvilgtelėkime į formulę pradėdami nuo vidinių elementų. Funkcija LEN pateikia kiekvieno langelio diapazono a2: A6 ilgį. Funkcija Max apskaičiuoja didžiausią reikšmę tarp tų elementų, kurie atitinka ilgiausią teksto eilutę, esančią A3 langelyje.

Čia viskas tampa sudėtingiau. Funkcija MATCH apskaičiuoja langelio, kuriame yra ilgiausia tekstinė reikšmė, poslinkį (santykinę padėtį). Norint tai padaryti, reikalingi trys argumentai: peržvalgos reikšmė, peržvalgos masyvas ir atitikimo tipas. Funkcija MATCH peržvalgos masyve ieško nurodytos peržvalgos reikšmės. Šiuo atveju peržvalgos reikšmė yra ilgiausia tekstinė eilutė:

(MAX (LEN (A2: A6))

Ši eilutė yra šiame masyve:

LEN (A2: A6)

Atitikimo tipo argumentas yra 0. Atitikimo tipą gali sudaryti reikšmė 1, 0 arba -1. Jei nurodysite 1, MATCH grąžins didžiausią reikšmę, kuri yra mažesnė nei peržvalgos reikšmė arba jai lygi. Jei nurodysite 0, MATCH grąžins pirmą reikšmę, kuri visiškai atitinka peržvalgos reikšmę. Jei nurodysite -1, funkcija MATCH ras mažiausią reikšmę, kuri yra didesnė nei nurodyta peržvalgos reikšmė arba jai lygi. Jei praleisite atitikimo tipą, programa „Excel“ laikys, kad ji yra 1.

O funkcija INDEX naudoja šiuos argumentus: masyvas ir jame esantis eilutės bei stulpelio numeris. Langelių diapazonas a2: A6 pateikia masyvą, funkcija Match pateikia langelio adresą ir galutinis argumentas (1) nurodo, kad reikšmė gaunama iš pirmo masyvo stulpelio.

Šiame skyriuje pateikiami išplėstinių masyvo formulių pavyzdžiai.

Diapazono, kuriame yra klaidos reikšmių, sumavimas

Programos „Excel“ funkcija SUM neveikia, jei bandote sumuoti diapazoną, kuriame yra klaidos reikšmė, pvz., #N/A. Šiame pavyzdyje pavaizduota, kaip sumuoti reikšmes, esančias diapazone Duomenys, kuriame yra klaidų.

=SUM(IF(ISERROR(Duomenys),"",Duomenys))

Formulė sukuria naują masyvą, kuriame yra pirminės reikšmės, atėmus visas klaidos reikšmes. Pradedant nuo vidinių funkcijų ir darbo išorėje, funkcija ISERROR ieško klaidų langelių diapazono (duomenys). Funkcija IF pateikia konkrečią reikšmę, jei jūsų nurodyta sąlyga ĮVERTINAMA kaip TRUE ir kitą reikšmę, jei ji ĮVERTINAMA kaip klaidingai. Šiuo atveju ji pateikia tuščias eilutes ("") visoms klaidų reikšmėms, nes jos ĮVERTINA kaip TRUE, ir pateikia likusias reikšmes iš intervalo (duomenys), nes jos įvertina kaip klaidinga, o tai reiškia, kad juose nėra klaidų reikšmių. Funkcija SUM apskaičiuoja filtruoto masyvo sumą.

Diapazono klaidos reikšmių skaičiaus apskaičiavimas

Šis pavyzdys yra panašus į ankstesnę formulę, bet grąžina diapazone Duomenys esančias klaidos reikšmes užuot jas atfiltravęs:

=SUM(IF(ISERROR(Duomenys),1,0))

Ši formulė sukuria masyvą, kuriame yra langelių su klaidomis reikšmė 1 ir langelių be klaidų reikšmė 0. Galite supaprastinti formulę ir gauti tą patį rezultatą pašalindami trečiąjį funkcijos IF argumentą, kaip pvz.:

=SUM(IF(ISERROR(Duomenys),1))

Jei nenurodysite argumento, funkcija IF grąžins reikšmę FALSE, kai langelyje nebus klaidos reikšmės. Galite supaprastinti formulę dar labiau:

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

Ši versija veikia, nes TRUE*1=1 ir FALSE*1=0.

Reikšmių sumavimas pagal sąlygas

Kartais reikia sumuoti reikšmes pagal sąlygas. Pavyzdžiui, ši masyvo formulė sumuoja tik teigiamus sveikuosius diapazono Pardavimas skaičius:

=SUM(IF(Pardavimas>0,Pardavimas))

Funkcija IF sukuria teigiamų ir klaidingų reikšmių masyvą. Funkcija SUM nepaiso klaidingų reikšmių, nes 0+0=0. Langelių diapazonas, kurį naudojate šioje formulėje, gali būti sudarytas iš bet kokio eilučių ir stulpelių skaičiaus.

Taip pat galite sumuoti reikšmes, kurios atitinka daugiau nei vieną sąlygą. Pavyzdžiui, ši masyvo formulė apskaičiuoja reikšmes, didesnes nei 0 ir lygias 5 arba mažesnes:

=SUM((Pardavimas>0)*(Pardavimas<=5)*(Pardavimas))

Turėkite omenyje, kad ši formulė grąžina klaidą, jei diapazone yra vienas arba keli ne skaitiniai langeliai.

Taip pat galite kurti masyvo formules, kurios naudoja sąlygos OR tipą. Pavyzdžiui, galite sumuoti reikšmes, kurios yra mažesnės nei 5 ir didesnės nei 15:

=SUM(IF((Pardavimas<5)+(Pardavimas>15),Pardavimas))

Funkcija IF randa visas reikšmes, kurios yra mažesnės nei 5 ir didesnės nei 15, tada perduoda tas reikšmes funkcijai SUM.

Negalite naudoti funkcijų AND ir OR tiesiogiai masyvo formulėse, nes šios funkcijos grąžina vieną rezultatą, TRUE arba FALSE, o masyvo funkcijoms būtini rezultatų masyvai. Galite išvengti problemos naudodami logiką, pavaizduotą ankstesnėje formulėje. Kitaip tariant, galite atlikti reikšmių, atitinkančių sąlygą OR arba AND, matematines operacijas.

Vidurkio, į kurį neįtraukti nuliai, skaičiavimas

Šiame pavyzdyje pavaizduota, kaip pašalinti nulius iš diapazono, kai norite apskaičiuoti to diapazono reikšmių vidurkį. Formulė naudoja duomenų intervalą, pavadintą Pardavimas:

=AVERAGE(IF(Pardavimas<>0,Pardavimas))

Funkcija IF sukuria reikšmių, kurios nėra lygios 0, masyvą ir perduoda tas reikšmes funkcijai AVERAGE.

Skirtumo tarp dviejų langelių diapazonų skaičiaus apskaičiavimas

Ši masyvo formulė palygina dviejų langelių diapazonų, pavadintų ManoDuomenys ir TavoDuomenys, reikšmes ir grąžina jų skirtumų skaičių. Jei dviejų diapazonų turinys yra identiškas, formulė grąžina reikšmę 0. Norint naudoti šią formulę, langelių diapazonai turi būti to paties dydžio ir matmens (pvz., jei ManoDuomenys yra diapazonas iš 3 eilučių ir 5 stulpelių, Tavoduomenys taip pat turi būti iš 3 eilučių ir 5 stulpelių):

=SUM(IF(ManoDuomenys=TavoDuomenys,0,1))

Formulė sukuria naują masyvą, kurio dydis yra toks pat kaip diapazonai, kuriuos lyginate. Funkcija IF užpildo masyvą reikšme 0 ir 1 (0 – neatitikimai, 1 – identiški langeliai). Funkcija SUM grąžina masyvo reikšmių sumą.

Galite supaprastinti formulę:

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

Kaip formulė, skaičiuojanti klaidos reikšmes diapazone, ši formulė veikia todėl, kad TRUE*1=1, o FALSE*1=0.

Maksimalios reikšmės vietos radimas diapazone

Ši masyvo formulė grąžina vieno stulpelio diapazono Duomenys maksimalios reikšmės eilutės numerį:

=MIN(IF(Duomenys=MAX(Duomenys),ROW(Duomenys),""))

Funkcija IF sukuria naują masyvą, kuris atitinka diapazoną, pavadintą Duomenys. Jei atitinkamame langelyje yra maksimali diapazono reikšmė, masyve yra eilutės numeris. Kitu atveju masyve yra tuščia eilutė (""). Funkcija MIN naudoja naująjį masyvą kaip savo antrąjį argumentą ir grąžina mažiausią reikšmę, kuri atitinka maksimalios diapazono Duomenys reikšmės eilutės numerį. Jei diapazone Duomenys yra identiškos maksimalios reikšmės, formulės grąžina pirmosios reikšmės eilutę.

Jei norite grąžinti faktinę maksimalios reikšmės langelio vietą, naudokite šią formulę:

=ADDRESS(MIN(IF(Duomenys=MAX(Duomenys),ROW(Duomenys),"")),COLUMN(Duomenys))

Patvirtinimo

Šio straipsnio dalys buvo pagrįstos serija "Excel Power user" stulpelių, parašytų Colin Wilcox, ir pritaikyta iš 14 ir 15 skyrių "Excel" 2002 formulių, knygos, parašytų John Walkenbach, buvusio "Excel" MVP.

Reikia daugiau pagalbos?

Visada galite paprašyti specialisto iš „Excel“ technologijų bendruomenės, gauti pagalbos iš Atsakymų bendruomenės arba siūlyti naują funkciją ar patobulinimą „Excel“ vartotojo balse.

Taip pat žr.

Dinaminiai masyvai ir išplėsto masyvo elgesys

Dinaminių masyvo formulių ir senstelėjusių CSE masyvo formulių palyginimas

Funkcija FILTER

Funkcija RANDARRAY

Funkcija SEQUENCE

Funkcija SINGLE

Funkcija SORT

Funkcija SORTBY

Funkcija UNIQUE

#SPILL! klaidos programoje „Excel“

Formulių apžvalga

Tobulinkite savo „Office“ įgūdžius
Ieškoti mokymo
Pirmiausia gaukite naujų funkcijų
Prisijunkite prie „Office Insider“ dalyvių

Ar ši informacija buvo naudinga?

Dėkojame už jūsų atsiliepimus!

Dėkojame už jūsų atsiliepimą! Panašu, kad gali būti naudinga jus sujungti su vienu iš mūsų „Office“ palaikymo agentų.

×