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ės yra formulė, kuri su masyvo vieną ar daugiau elementų, galite atlikti įvairius skaičiavimus. Jums masyvo eilutės ar stulpelio reikšmių arba derinys, eilučių ir stulpelių reikšmes. Masyvo formulės gali pateikti keletą rezultatų, arba vieną rezultatą.

Pradedant rugsėjo 2018 naujinimas, skirtas "Office 365", formulė, kuri gali pateikti keli rezultatai bus automatiškai išsiliejimo juos žemyn arba skersai į kaimyninių langelius. Šis pokytis elgesį taip pat pateikiamas keletą naujų dinaminis masyvas funkcijų. Dinaminis masyvo formules, ar jie naudoja esamus arba dinaminis masyvas funkcijos, tik reikia įvesti į vieną langelį, tada paspausdami Enterpatvirtinta. Anksčiau, senstelėjusių masyvo formules reikia pirmiausia pasirinkdami visą išvesties diapazoną, tada patvirtinimas formulę su Ctrl + Shift + Enter. Jie esate dažnai nurodomos kaip CSE formulės.

Atlikti sudėtingas užduotis, pvz., galite naudoti masyvo formules:

  • Greitai sukurti pavyzdžio duomenų rinkinius.

  • Apskaičiuoti langelių diapazone esančių simbolių skaičių.

  • Sumuoti tik skaičius, kurie atitinka tam tikrus kriterijus, pvz., mažiausias reikšmes diapazone, arba skaičius, kurie yra tarp viršutinės ir apatinės ribos.

  • Sumuoti kiekvieną n-tąją vertę verčių diapazone.

Pateikti pavyzdžiai rodo, kaip kurto kelių langelių ir vieno langelio masyvo formules. Jei įmanoma, pateikiame kai dinaminis masyvas funkcijas, taip pat esamas masyvo formules, įvestas kaip dinaminių ir senstelėjusių masyvų pavyzdžiai.

Atsisiųskite mūsų pavyzdžius

Atsisiųskite pavyzdys darbaknygę su visų masyvo formulių pavyzdžiai š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 formulė langelyje H10 = F10:F19 * G10:G19 suskaičiuoti automobilių paduotų vieneto kaina

  • Čia mes esate skaičiuojant kupė ir sedanų kiekvieno pardavėjo pardavimo sumos pagal įvesti = F19:F19 * G10:G19 langelyje H10.

    Paspaudus klavišą " Enter", matysite išsiliejimų iki langelių H10:H19 rezultatus. Atkreipkite dėmesį, poveikis diapazonas yra pažymimi kraštinės pasirinkus bet kurį langelį poveikis diapazoną. Taip pat galite pastebėti, langelių H10:H19 formulėse yra blanki. Jie taip pat yra nuorodos, todėl, jei norite keisti į formulę, reikia pasirinkti langelyje H10, kur gyvena puslapių formulę.

  • Vieno langelio masyvo formulę

    Bendroji suma su =SUM(F10:F19*G10:G19) vieno langelio masyvo formulę

    Darbaknygės pavyzdyje H20 langelyje įveskite arba nukopijuokite ir įklijuokite =SUM(F10:F19*G10:G19), ir paspauskite klavišą Enter.

    Tokiu atveju, "Excel" Sudaugina reikšmes masyvo (langelių diapazoną F10 per G19), ir ją naudoja funkciją SUM įtraukti sumos kartu. Rezultatas yra $1,590,000 pardavimų bendra.

    Čia parodyta, kaip efektyvi šio tipo formulę gali būti. Pavyzdžiui, Tarkime, turite 1 000 duomenų eilutes. Užuot vilkę formulę iki 1 000 eilučių viename langelyje sukurti masyvo formulę galite sudėti dalį arba visą tuos duomenis. Be to, Atkreipkite dėmesį, kad vieno langelio formulę į langelį H20 visiškai nepriklauso kelių langelių formulės (formulė – H19 H10 langelių). Tai kita masyvo formulių naudojimo privalumas – lankstumas. Galite pakeisti kitas formules, esančius H stulpelyje nekeičiant H20 formulę. Taip pat galima gera praktika yra nepriklausomas sumos tokia, nes ji padeda patvirtinti savo rezultatų tikslumą.

  • Dinaminis masyvo formulės taip pat užtikrina šiuos pranašumus:

    • Suderinamumas    Spustelėjus bet kurį iš H10 žemyn, matysite tą pačią formulę. Šį nuoseklumą gali padėti užtikrinti didesnį tikslumą.

    • Saugos    Negalima perrašyti dalis kelių langelių masyvo formulės. Pavyzdžiui, spustelėkite langelį H11 ir paspauskite klavišą Delete. "Excel" nekeičia į masyvo išvesties. Norėdami pakeisti tai, jums reikia pasirinkti viršutiniame kairiajame langelio masyvo arba langelyje H10.

    • Mažesnio dydžio failai    Dažnai galite naudoti viena masyvo formule, užuot kelias tarpinis formules. Pvz., automobilių pardavimo pavyzdys vieną masyvo formulę apskaičiuojamos pagal rezultatus e stulpelį. Jei jums buvo naudojamas standartinės formulės, pvz., = F10 * G10, F11 * G11, F12 * G12, ir kt., kurį būtų naudojamas 11 skirtingas formules apskaičiuoti tuos pačius rezultatus. Tai nėra didelė investicija, bet tai, ką daryti, jei jums buvo tūkstančius eilučių bendrą? Tada ji gali padaryti didelį skirtumą.

    • Efektyvumo    Masyvo funkcijos gali būti efektyvus būdas kurti sudėtingas formules. Masyvo formulės =SUM(F10:F19*G10:G19) veikia taip pat, kaip tai: = SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).

    • Praliejimas    Dinaminis masyvo formulės bus automatiškai išsiliejimo į išvesties diapazone. Jei jūsų šaltinio duomenys yra programoje "Excel" lentelės, tada dinaminių masyvo formulių bus automatiškai būtų pakeistas, galite įtraukti arba pašalinti duomenų.

    • #SPILL! klaidos    Dinaminiai masyvai, įvedama į #SPILL! klaidos, kuris rodo, kad numatytą poveikis diapazonas yra užblokuotas dėl kokių nors priežasčių. Kai sprendžiate, blokavimo, formulė automatiškai išsiliejimo.

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 naudodami kableliais atskirkite elementus, galite sukurti horizontalų masyvą (eilutės). Jei atskirkite atskirkite kabliataškiais, galite sukurti vertikalų masyvą (stulpelį). Norėdami sukurti dvimačio masyvo, elementų kiekvienoje eilutėje atskirkite kableliais atskirti ir atskirti kiekvienos eilutės kabliataškiais.

Šios procedūros padės išmokti kurti horizontaliąsias, vertikaliąsias ir dvimačių konstantų. Mes parodysime pavyzdžiai, kaip naudoti seka funkcija automatiškai generuoti konstantų, taip pat rankiniu būdu įvesti masyvo konstantas.

  • Horizontaliosios konstantos kūrimas

    Naudokite ankstesnių pavyzdžių darbaknygę arba sukurkite naują darbaknygę. Pasirinkite bet kurį tuščią langelį ir įveskite =SEQUENCE(1,5). SEKA funkcija sukuria 1 eilutę iš 5 stulpelio masyvo taip pat, kaip = {1,2,3,4,5}. Rodomas toks rezultatas:

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

  • Vertikaliosios konstantos kūrimas

    Pasirinkite bet kurį tuščią langelį su kambario po juo, ir įveskite =SEQUENCE(5)arba = {1; 2; 3; 4; 5}. Rodomas toks rezultatas:

    Vienmačio masyvo konstantos kūrimas su = SEQUENCE(5) arba = {1; 2; 3; 4; 5}

  • Dvimatės konstantos kūrimas

    Pasirinkite bet kurį tuščią langelį su kambario į dešinę ir po juo, ir įveskite =SEQUENCE(3,4). Rodomas toks rezultatas:

    3 eilutės iš 4 stulpelio masyvo konstanta su =SEQUENCE(3,4), kūrimas

    Taip pat galite įvesti: arba = {1,2,3,4 5,6,7,8; 9,10,11,12}, bet jūs norite atkreipti dėmesį į tai, kur padėjote kabliataškiais arba kableliais.

    Kaip matote, sekos parinktis siūlo daug pranašumų rankiniu būdu įvesti savo masyvo konstantų reikšmes. Visų pirma, įrašykite jūs laiku, tačiau jis gali padėti sumažinti klaidų iš rankiniu būdu įrašo. Jis taip pat lengviau skaityti, ypač į kabliataškiais gali būti sunku atskirti nuo kablelių skyriklių.

Čia pateikiamas pavyzdys, kad naudoja masyvo konstantų kaip didesnis formulės dalį. Darbaknygės pavyzdys, eikite į pastovaus formulėje darbalapį arba sukurkite naują darbalapį.

Langelyje D9, mes įrašytas =SEQUENCE(1,5,3,1), bet taip pat galima įvesti 3, 4, 5, 6 ir 7 – langelių A9:H9. Nėra nieko ypatingo apie skaičių pasirinktus, tiesiog, o ne 1-5 skirti pasirinkome.

E11 langelyje = SUM (D9:H9*SEQUENCE(1,5)), arba = SUM (D9:H9* {1,2,3,4,5}). Toliau formulės pateikiama 85.

Masyvo konstantų naudojimas formulėse. Šiame pavyzdyje panaudojome = SUM (D9:H(*SEQUENCE(1,5))

SEKA funkcija sukuria nuo masyvo konstantą {1,2,3,4,5}. Kadangi "Excel" atlieka operacijas išraiškų skliaustuose pirmą kartą, kitus du elementai, kurie imama yra langelio reikšmės D9:H9 ir daugybos operatorių (*). Šiuo metu formulė Sudaugina reikšmes saugomojo masyvo konstantoje atitinkamas reikšmes. Tai nuo:

=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 pateikia 85.

Norėdami išvengti saugomojo masyvo ir palikti operaciją tik atmintyje, galite pakeisti jį kita masyvo konstanta:

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

Elementai, kuriuos galite naudoti masyvo konstantas

  • Masyvo konstantose gali būti skaičiai, tekstą, logines reikšmes (pvz., TRUE ir FALSE) ir klaidos reikšmių, pvz., #N/A. Sveikasis skaičius, dešimtainis ir mokslo formatais, galite naudoti skaičių. Jei norite įtraukti tekstą, turite aplink jį 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ų būdų, kaip naudoti masyvo konstantų yra jiems vardus. Pavadintas konstantas gali būti daug lengviau naudoti ir jie gali slėpti kai kurias masyvo formulių iš kitų sudėtingumą. Masyvo konstantų pavadinimo ir jį naudoti formulėje, atlikite šiuos veiksmus:

Eikite į formules > apibrėžti pavadinimai > 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 turėtų atrodyti taip:

Iš formulės > apibrėžti pavadinimai > pavadinimų tvarkytuvas > papildyti pavadinti masyvo konstanta

Spustelėkite gerai, tada pasirinkite bet kurią eilutę su trijų tuščių langelių, ir įveskite = ketvirtis1.

Rodomas toks rezultatas:

Pavadinti masyvo konstantos naudojimas formulėje, pvz., = ketvirtis1, kur ketvirtis1 apibrėžtu = {"Sausis", "Vasaris", "kovas"}

Jei norite, kad rezultatus pašalinis vertikaliai, o ne horizontaliai, galite naudoti =TRANSPONUOTI(ketvirtis1).

Jei norite, kad būtų rodomas sąrašas 12 mėnesių, pvz., galite naudoti kurdami finansinė ataskaita, galite kurti vieną iš šių metų seka funkcija. Apie šią funkciją tvarkingas yra, net jei rodoma tik mėnesį, yra neleistina data už jo, kuriuos galite naudoti kitiems skaičiavimams. Šiuose pavyzdžiuose darbalapiuose pavadintas masyvo konstanta ir greitai pavyzdžio duomenų rinkinį rasite darbaknygės pavyzdys.

=TEXT(Date(Year(Today()),SEQUENCE(1,12),1),"mmm")

Naudoti tekstą, datą, metus, šiandien, ir seka funkcijų derinį sukurti dinaminį sąrašą 12 mėnesių

Tai naudojama funkcija "DATE" sukurti dabartinius metus pagal datos, seka sukuria masyvo konstanta nuo 1 iki 12 sausio iki gruodžio, tada funkcija TEXT konvertuoja į "mmm" formatu (saus, Vas, kov, ir kt.). Jei norite Rodyti visą mėnesio pavadinimą, pvz., sausio, galite naudoti "mmmm".

Kai naudojate konstanta su pavadinimu kaip masyvo formulę, nepamirškite įveskite lygybės ženklą, = ketvirtis1, o ne tik ketvirtis1. Jei ne, "Excel" aiškina masyvas kaip teksto eilutė ir formulė neveikia, kaip tikėtasi. Be to, Turėkite omenyje, kad galite naudoti funkcijas, teksto ir skaičių derinys. Tai priklauso nuo to, kaip kūrybiškumą, kurį norite gauti.

Šie pavyzdžiai rodo, kad yra keli būdai, kaip galite įdėti masyvo konstantų naudojimas masyvo formulės. Kai kurie pavyzdžiai naudodamiesi funkcija TRANSPOSE , galite konvertuoti eilutes, stulpelius ir atvirkščiai.

  • Kelių kiekvieno masyvo elemento

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

    Taip pat galite padalinti su (/), įtraukti su (+) ir atėmimas su (-).

  • Masyvo elementų kėlimas kvadratu

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

  • Rasti kvadratinę šaknį kvadratų elementų masyvas

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

  • Vienmatės eilutės transponavimas

    Įveskite =TRANSPOSE(SEQUENCE(1,5))arba =TRANSPOSE({1,2,3,4,5})

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

  • Vienmačio stulpelio transponavimas

    Įveskite =TRANSPOSE(SEQUENCE(5,1))arba = TRANSPOSE ({1; 2; 3; 4; 5})

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

  • Dvimatės konstantos transponavimas

    Įveskite =TRANSPOSE(SEQUENCE(3,4))arba = 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.

  • Masyvo kūrimas pagal esamas reikšmes

    Toliau pateiktame pavyzdyje aiškinama, kaip naudoti masyvo formules, kurti naują masyvo iš esamos masyvo.

    Įveskite =SEQUENCE(3,6,10,10)arba = {10,20,30,40,50,60; 70,80,90,100,110,120; 130,140,150,160,170,180}

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

    Be to, įveskite = D9 #, arba = D9:I11 tuščią langelį. Į 3 x 6 langelių masyvas rodomas su tomis pačiomis reikšmėmis, matote D9:D11. # Ženklą vadinamas išsiliejo diapazono operatorius, ir tai būdas programos "Excel' nurodo visą masyvo diapazonas, vietoj to, kad jį įveskite.

    Informacija apie esamą masyvo naudoti išsipylusios diapazono operatorius (#)

  • Masyvo konstantos kūrimas pagal esamas reikšmes

    Galite paimti išsipylusios masyvo formulės rezultatus ir paversti, jos dalis. Pažymėkite langelį D9, tada paspauskite F2 , kad pereitumėte į redagavimo režimą. Be to, paspauskite F9 , kad konvertuoti langelių nuorodas į reikšmes, kurios "Excel", tada konvertuoja į masyvo konstantą. Paspaudus klavišą Enter, pagal formulę = D9 #, dabar turi būti = {10,20,30; 40,50,60; 70,80,90}.

  • Langelių diapazono simbolių skaičiavimas

    Toliau pateiktame pavyzdyje rodoma, kaip apskaičiuoti langelių diapazono simbolių skaičių. Tai yra tarpų.

    Bendra simbolių diapazoną, ir kitų masyvus, kaip dirbti su teksto eilučių skaičių

    = SUM (LEN(C9:C13))

    Šiuo atveju funkcija "LEN" pateikia kiekvienos teksto eilutės ilgis kiekvieno iš langelių diapazono. Funkcija SUM, tada įtraukia šios reikšmės, kartu ir rodo rezultatą (66). Jei norite gauti vidutinis simbolių skaičių, galite naudoti:

    = AVERAGE (LEN(C9:C13))

  • Ilgiausio C9:C13 diapazono langelio turinį

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

    Ši formulė veikia tik tada, kai duomenų diapazone yra vieno stulpelio langelius.

    Kokios atidžiau pažvelgti į formulę, pradedant nuo vidinių elementų ir darbo į išorę. Funkcija LEN grąžina kiekvieno elementų langelių diapazoną D2: D6. Funkcija MAX apskaičiuoja didžiausią reikšmę tarp tuos elementus, kuri atitinka ilgiausio teksto eilutė, tai yra D3 langelyje.

    Štai kur ko gauti šiek tiek sudėtingas. Funkcija MATCH apskaičiuoja poslinkis langelį, kuriame yra ilgiausio teksto eilutę (santykinę padėtį). Norėdami tai padaryti, reikia trys argumentai: ieškomos reikšmės, lookup masyvo ir atitikties tipas. Funkcija MATCH ieško lookup masyvo nurodytą peržvalgos reikšmės. Šiuo atveju peržvalgos reikšmė yra ilgiausio teksto eilutę:

    MAX(LEN(C9:C13)

    Ši eilutė yra šiame masyve:

    LEN(C9:C13)

    Šiuo atveju match tipo argumentas yra 0. Atitikties tipas gali būti 1, 0 arba -1 reikšmę.

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

    • 0 - pateikia pirmąją reikšmę peržvalgos reikšmė lygi

    • -1 - grąžina mažiausią reikšmę, kuri yra didesnis arba lygus reikšmę, nurodytą peržvalgos

    • Jei praleisite atitikties tipas, "Excel" numanoma 1.

    Galiausiai, funkcija INDEX trunka argumentai: masyvo ir per masyvo eilučių ir stulpelių skaičių. Langelių diapazonas C9:C13 teikia masyvas, funkcija MATCH teikia langelio adresas ir paskutinio argumento (1) nurodo, kad reikšmė, gaunama iš pirmo stulpelio masyvo.

    Jei norite gauti mažiausią teksto eilutės turinį, kurį būtų pakeiskite MAX ankstesniame pavyzdyje MIN.

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

    Šiame pavyzdyje parodyta, kaip rasti tris mažiausias reikšmes į langelių diapazoną, kur sukurta masyvo langelius B9:B18has duomenų pavyzdys: = INT (RANDARRAY(10,1) * 100). Atkreipkite dėmesį, kad RANDARRAY yra kintama funkcija, todėl gausite naują atsitiktinį skaičių rinkinį kiekvieną kartą, kai "Excel" skaičiuoja.

    "Excel" masyvo formulė, skirta rasti n-tąją mažiausią reikšmę: =SMALL(B9#,SEQUENCE(D9))

    Įveskite =SMALL(B9#,SEQUENCE(D9), = mažas (B9:B18, {1; 2; 3})

    Šioje formulėje naudojama masyvo konstanta vertinama funkcija SMALL tris kartus ir grąžinti 3 nariais masyvo, esančius langelius B9:B18, kur 3 yra kintamojo reikšmę langelyje D9. Rasti daugiau reikšmių, galite padidinti funkciją sekos reikšmės, arba įtraukti daugiau argumentų konstanta. Taip pat galite naudoti papildomas funkcijas naudojant šią formulę, pvz., SUM ir AVERAGE. Pvz.:

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

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

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

    Kad didžiausių reikšmių radimas diapazone, galite pakeisti funkcija LARGESMALL funkciją. Be to, šiame pavyzdyje eilutės ir INDIRECT funkcijas.

    Įveskite = didelis (B9 #, eilutės (INDIRECT ("1:3"))), arba = didelis (B9:B18,ROW(INDIRECT("1:3")))

    Šiuo metu, jis gali padėti šiek tiek žinoti apie funkcijas eilutės ir INDIRECT. Norėdami sukurti masyvo iš eilės sveikųjų skaičių, galite naudoti funkciją ROW. Pavyzdžiui, pažymėkite tuščias ir įveskite:

    =ROW(1:10)

    Formulė sukuria stulpelio 10 iš eilės sveikųjų skaičių. Norėdami pamatyti galimą problemą, įterpti eilutę virš diapazonas, kuriame yra masyvo formulė (tai yra virš eilutės 1). "Excel" koreguos eilutės nuorodas, o formulė dabar generuoja sveikųjų skaičių nuo 2 iki 11. Norėdami išspręsti šią problemą, galite pridėti funkcijos INDIRECT formulę:

    = EILUTĖS (INDIRECT ("1:10"))

    Funkcijos INDIRECT naudoja teksto eilutės kaip argumentus (tai yra, kodėl diapazonas 1:10 apsupti kabutėmis). "Excel" neturi koreguoti teksto reikšmes, įterpti eilutes ar kitaip perkelti masyvo formulė. Todėl visada yra funkcija ROW generuoja masyvo sveikųjų skaičių, kurį norite. Taip pat lengvai galite naudoti sekos:

    =SEQUENCE(10)

    Išnagrinėkime formulę, kurią naudojote anksčiau, LARGE (B9 #, eilutės (INDIRECT ("1:3"))) =, pradedant nuo vidinių skliaustus ir darbo į išorę: netiesioginis funkcija pateikia teksto reikšmes, šiuo atveju reikšmės 1 – 3. Funkcija ROW savo ruožtu generuoja trijų langelių stulpelio masyvo. DIDELIS funkcija naudoja reikšmių langelių diapazonas B9:B18, ir ji vertinama tris kartus, vieną kartą už kiekvieną nuorodą, kurį grąžino funkcija ROW. Jei norite rasti daugiau reikšmių, galite pridėti daugiau langelių diapazono funkciją netiesiogiai. Be to, kaip su maža pavyzdžiai, galite naudoti šią formulę su kitomis funkcijomis, pvz., SUM ir AVERAGE.

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

    Programoje "Excel" funkcija SUM neveikia, kai bandote sudėti diapazoną, kuriame yra klaidos reikšmė, pvz., #VALUE! ar #N/A. Šiame pavyzdyje rodoma, kaip sudėti diapazono duomenis, kurie yra klaidų reikšmės:

    Naudoti masyvų spręsti klaidų. Pvz., =SUM(IF(ISERROR(Data),"",Data) bus sudėti diapazoną, pavadintą duomenis, net jei jame yra klaidų, pvz., #VALUE! arba #NA!.

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

    Formulė sukuria naują masyvą, kuriame yra pradinės reikšmės, bet nėra klaidos reikšmių. Pradedant vidinėmis funkcijomis ir veikiančiomis išorėn, funkcija ISERROR ieško klaidų langelių diapazone (Duomenys). Funkcija IF pateikia konkrečią reikšmę, jei jūsų nurodyta sąlyga yra TRUE, ir kitą reikšmę, jei sąlyga yra FALSE. Šiuo atveju ji pateikia tuščias eilutes ("") pagal visas klaidų reikšmes, nes jų sąlygos rezultatas yra TRUE, ir pateikia likusias diapazono (Duomenys) reikšmes, nes jų sąlygos rezultatas yra FALSE, t. y. jose nėra klaidos reikšmių. Funkcija SUM apskaičiuoja filtruoto masyvo sumą.

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

    Šis pavyzdys yra kaip ankstesnę formulę, bet grąžina klaidos reikšmių skaičių diapazono duomenų 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.

Gali tekti reikšmių sumavimas pagal sąlygas.

Galite naudoti masyvų apskaičiuoti pagal tam tikras sąlygas. =SUM(if(Sales>0,sales)) bus sudėti visų reikšmių, didesnių už 0 pardavimai diapazone.

Pvz., ši masyvo formulė sumuoja tik teigiamas sveikųjų skaičių diapazone pardavimo, kuris sudaro langelių E9:E24 aukščiau pateiktame pavyzdyje:

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

Funkcija IF sukuria teigiamas ir klaidingų reikšmių masyvą. Funkcija SUM iš esmės ignoruoja FALSE (klaidinga) reikšmių, nes 0 + 0 = 0. Langelių diapazoną, kurį galite naudoti šią formulę gali būti bet koks simbolių eilutes ir stulpelius.

Taip pat galite sudėti reikšmes, kurios atitinka daugiau nei viena sąlyga. Pavyzdžiui, ši masyvo formulė apskaičiuoja reikšmių, didesnių už 0 ir mažesnis nei 2500:

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

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

Taip pat galite sukurti masyvo formulės, naudokite sąlygą arba tipą. Pavyzdžiui, galite sudėti yra didesnis už 0 ar mažiau nei 2500 reikšmės:

=SUM(if((Sales>0)+(Sales<2500),sales))

Negalima naudoti AND ir arba funkcijas masyvo formulės tiesiogiai, nes šių funkcijų grąžina vieną rezultatą, teisinga arba klaidinga, ir masyvo funkcijos reikalauja masyvai rezultatai. Naudojant logikos rodomas į ankstesnę formulę, galite išspręsti problemą. Kitaip tariant, galite atlikti matematinius veiksmus, pvz., sudėti arba dauginti reikšmes, kurios atitinka arba arba ir sąlygos.

Š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ė lygina dviejų langelių diapazonus, pavadintus manoduomenys ir Tavoduomenys reikšmes ir pateikia skirtumo tarp dviejų skaičių. Jei dviejų diapazonų turinys yra tokie patys, formulė grąžina 0. Norėdami naudoti šią formulę, langelių diapazonų turi būti taip pat dydis ir pačios dimensijos. Pvz., manoduomenys yra 3 eilutės ir stulpeliai 5 diapazonas, Tavoduomenys turi būti 3 eilutės 5 stulpeliai:

=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*(ManoDuomenys<>TavoDuomenys))

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

Ši masyvo formulė pateikia 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 pateikia 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 pateikia pirmosios reikšmės eilutę.

Jei norite pateikti 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 pavyzdyje darbalapio skirtumus tarp duomenų rinkinius .

Š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ą toliau pateiktą lentelę ir įklijuokite jį į tuščio darbalapio A1 langelį.

Pardavimo Asmens

Automobilio Tipas

Skaičius Parduotų

Matavimo vienetų Kaina

Sumos Pardavimo

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 peržiūrėti kupė ir sedanų kiekvieno pardavėjo pardavimo sumos, pažymėkite langelius E2: E11, įveskite formulę = C2: C11 * D2: D11, tada paspauskite Ctrl + Shift + Enter.

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

Paspaudus Ctrl + Shift + Enter, "Excel" įterpia formulę su riestinius skliaustus ({}) ir įterpia formulę egzempliorius kiekvieno pažymėto diapazono langelio. Tai vyksta labai greitai, todėl ką matote stulpelio E kiekvieno automobilio tipo kiekvieno pardavėjo pardavimo suma. Jei jums E2 pasirinkite E3, E4, ir taip toliau, matysite tą pačią formulę, rodoma: {= C2: C11 * D2: D11}.

E stulpelio sumos apskaičiuojamos masyvo formule

  • Vieno langelio masyvo formulės kūrimas

Darbaknygės D13 langelyje įveskite šią formulę ir paspauskite Ctrl + Shift + Enter:

=SUM(C2:C11*D2:D11)

Tokiu atveju, "Excel" Sudaugina reikšmes masyvo (langelio nuo C2 iki D11) ir ją naudoja funkciją SUMkartu įtraukti bendrosios sumos. Rezultatas yra $1,590,000 pardavimų bendra. Čia parodyta, kaip efektyvi šio tipo formulę gali būti. Pavyzdžiui, Tarkime, turite 1 000 duomenų eilutes. Užuot vilkę formulę iki 1 000 eilučių viename langelyje sukurti masyvo formulę galite sudėti dalį arba visą tuos duomenis.

Be to, pastebėsite, kad vieno langelio formulę į langelį D13 yra visiškai nepriklausomas kelių langelių formulės (E2 – E11 langelių formulė). Tai kita masyvo formulių naudojimo privalumas – lankstumas. Galite keisti formules E stulpelio arba panaikinti tą stulpelį iš viso nekeičiant D13 formulę.

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

  • Saugos    Negalima perrašyti dalis kelių langelių masyvo formulės. Pavyzdžiui, spustelėkite langelį E3 ir paspauskite Naikinti. Turite pasirinkti visą langelių diapazoną arba (E2 – E11) ir pakeisti visą masyvo formulė, arba palikti kaip masyvą. Kaip papildomą apsaugos priemonę, turite paspauskite Ctrl + Shift + Enter , kad patvirtintumėte pakeitimus į formulę.

  • Mažesnio dydžio failai    Dažnai galite naudoti vieną masyvo formulę vietoj kelių tarpinių formulių. Pavyzdžiui, darbaknygė, naudoja vieną masyvo formulę stulpelio E rezultatams apskaičiuoti. Jei būtumėte naudoję standartines formules (pvz., =C2*D2, C3*D3, C4*D4…), būtumėte naudoję 11 skirtingų formulių tam pačiam rezultatui apskaičiuoti.

Paprastai masyvo formulės naudoja standartinę formulės sintaksę. Visi jie prasideda lygybės (=) ženklu ir dauguma įtaisytosios "Excel" funkcijas galite naudoti masyvo formulių. Pagrindinis skirtumas yra, naudojant masyvo formulę, paspauskite Ctrl + Shift + Enter , įveskite formulę. Kai tai padarysite, "Excel" įterpia masyvo formulė su riestinius skliaustus, jei rankiniu būdu įvesti riestinių skliaustų formulę, bus konvertuota į teksto eilutę, ir ji neveiks.

Masyvo funkcijos gali būti efektyvus būdas kurti sudėtingas formules. Masyvo formulės =SUM(C2:C11*D2:D11) veikia taip pat, kaip tai: =SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Svarbu: Jei reikia įvesti masyvo formulę, paspauskite Ctrl + Shift + Enter . Tai taikoma vieno langelio ir kelių langelių formules.

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.

  • Negalite keisti masyvo formulės atskiro langelio turinio. Jei norite pabandyti tai padaryti, pažymėkite darbaknygės langelį E3 ir paspauskite „Delete“.

  • 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ę, diapazoną (pvz., E2: E11), tada paspauskite Naikinti.

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

Kartais gali tekti išplėsti masyvo formulę. Esamos masyvo diapazone pažymėkite pirmąjį langelį ir tęskite, kol pažymėjote visą diapazoną, kad norite išplėsti formulę. Paspauskite F2 , kad galėtumėte redaguoti formulę, tada paspauskite CTRL + SHIFT + ENTER , kad patvirtintumėte formulė, kai jau pakoreguojamos formulės diapazono. Norėdami pažymėti visą diapazoną, pradėdami nuo langelio viršutiniame kairiajame masyvo svarbiausia. Viršutiniame kairiajame langelyje yra vienas, kad yra redaguojamas.

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

  • Kartais gali pamiršti, paspauskite Ctrl + Shift + Enter. Tai gali atsitikti net labiausiai patyrusių vartotojų "Excel". Atminkite, kad kiekvieną kartą, kai jūs įveskite arba redaguokite masyvo formulę, paspauskite šį klavišų derinį.

  • Kiti darbaknygės vartotojai gali nesuprasti savo formules. Praktiškai masyvo formulės yra paprastai ne paaiškinimas darbalapyje. Todėl, jei kiti žmonės reikia pakeisti savo darbaknyges, jums turėtų išvengti masyvo formulių arba įsitikinkite, kad tų žmonių nežino apie masyvo formules ir suprasti, kaip juos keisti, jei norite.

  • 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 žinote, jums reikia paspausti Ctrl + Shift + Enter , kurdami masyvo formules. Masyvo konstantų yra dalis masyvo formulės, todėl rašomas konstantas su riestinius skliaustus rankiniu būdu įvesdami jas. Tada naudokite Ctrl + Shift + Enter įveskite 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.

Čia yra masyvas vienoje eilutėje: {1,2,3,4}. Čia yra masyvas yra vienas stulpelis: {1; 2; 3; 4}. O čia – dvi eilutes ir stulpelius masyvo: {1,2,3,4; 5,6,7,8}. Dvi eilutės masyvas, pirmoji eilutė yra 1, 2, 3 ir 4 ir antroje eilutėje yra 5, 6, 7 ir 8. Vieno kabliataškiais dvi eilutes, nuo 4 iki 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ščią darbalapį, pažymėkite langelius nuo A1 iki E1.

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

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

    Šiuo atveju turėtumėte įvesti atidaromąjį ir uždaromąjį riestinius skliaustus ({}) ir "Excel" bus įtraukti antrąjį už jus.

    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 šią formulę ir paspauskite Ctrl + Shift + Enter:

    ={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 šią formulę ir paspauskite Ctrl + Shift + Enter:

    ={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 šią formulę ir paspauskite Ctrl + Shift + Enter:

    =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 atsiranda 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 skliausteliuose yra masyvo konstanta: {1,2,3,4,5}. Atminkite, kad "Excel" nėra rašomas masyvo konstantas su riestinius skliaustus; iš tiesų juos įvesti. Taip pat prisiminti, įtraukę konstantą kaip masyvo formulė, paspauskite Ctrl + Shift + Enter , įveskite 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 išbandyti šią, nukopijuokite funkciją, darbaknygėje, pasirinkite tuščią langelį, įklijuoti formulę į formulės juostą ir paspauskite Ctrl + Shift + Enter. Matysite tą patį rezultatą, kaip jau darėte atlikdami anksčiau pratimas, naudojamas 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š geriausias būdas masyvo konstantų yra jiems vardus. Pavadintas konstantas gali būti daug lengviau naudoti ir jie gali slėpti kai kurias masyvo formulių iš kitų sudėtingumą. Masyvo konstantų pavadinimo ir jį naudoti formulėje, atlikite šiuos veiksmus:

  1. Skirtuko formulės grupėje Apibrėžti pavadinimai spustelėkite Apibrėžti pavadinimą.
    Pasirodo dialogo langas Pavadinimo apibrėžimas .

  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 šią formulę ir paspauskite Ctrl + Shift + Enter.

    = 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 gali būti negalima atskirti tinkamą simboliu. Jei praleisite, kablelis ar kabliataškis, arba galite įdėti vieną netinkamoje vietoje, masyvo konstanta gali tinkamai sukurtas arba gali būti rodomas įspėjimo 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.

Šie pavyzdžiai rodo, kad yra keli būdai, kaip galite įdėti masyvo konstantų naudojimas masyvo formulės. Kai kurie pavyzdžiai naudodamiesi funkcija TRANSPOSE , galite konvertuoti 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 šią formulę ir paspauskite Ctrl + Shift + Enter:

    = {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ę ir paspauskite 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}

    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 šią formulę ir paspauskite Ctrl + Shift + Enter:

    =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 šią formulę ir paspauskite Ctrl + Shift + Enter:

    =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 šią konstantą ir paspauskite Ctrl + Shift + Enter:

    =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 {(atidaromąjį riestinį skliaustelį) prieš įvesdami 10 ir}(uždarantįjį riestinį skliaustą) įvedę 90, nes kuriate skaičių masyvą.

  2. Paspauskite Ctrl + Shift + Enter, kuri įveda šiame masyve skaičių langelių diapazone C8:E10 naudojant masyvo formulę. Savo darbalapyje C8 iki E10 turėtų atrodyti taip:

    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ę ir paspauskite Ctrl + Shift + Enter:

    =C8:E10

    Į 3 x 3 langelių masyvas rodomas langeliuose nuo C1 iki E3 su tomis pačiomis reikšmėmis, matote C8 iki E10.

Masyvo konstantos kūrimas pagal esamas reikšmes

  1. Su langeliais C1:C3 pažymėtas, paspauskite F2, kad pereitumėte į redagavimo režimą.

  2. Paspauskite F9 , kad konvertuoti langelių nuorodas į reikšmes. "Excel" konvertuoja reikšmes į masyvo konstanta. Formulė turi būti = {10,20,30; 40,50,60; 70,80,90}.

  3. Paspauskite Ctrl + Shift + Enter , Norėdami įvesti 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, ir tada paspauskite Ctrl + Shift + Enter , kad matytumėte bendrą simbolių langeliuose A2: A6 (66) skaičių.

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

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

=SUM(LEN(A2:A6))

Tokiu atveju funkciją LEN grąžina kiekvieno teksto eilutės ilgis kiekvieno iš langelių diapazono. Funkcija SUM , tada įtraukia šios reikšmės, kartu ir rodo rezultatą (66).

Rasti n mažiausių reikšmių diapazono

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

  1. Įveskite langelių A1:A11 kai atsitiktinį skaičių.

  2. Pažymėkite langelių nuo C1 iki C3. Šis rinkinys langelių bus masyvo formulės rezultatai.

  3. Įveskite šią formulę ir paspauskite Ctrl + Shift + Enter:

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

Šioje formulėje naudojama įvertinti funkciją SMALL tris kartus ir mažiausią (1), antra mažiausią (2) ir trečia (3) nariais, esančios langelių diapazone a1: A10 rasti daugiau reikšmių masyvo masyvo konstanta, įtraukti daugiau argumentų, konstanta. Taip pat galite naudoti papildomas funkcijas naudojant šią formulę, pvz., SUM ir AVERAGE. Pvz.:

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

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

Rasti n didžiausių reikšmių diapazono

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 D1 iki D3.

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

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

Šiuo metu, jis gali padėti šiek tiek žinoti apie eilutės ir INDIRECT funkcijas. Norėdami sukurti masyvo iš eilės sveikųjų skaičių, galite naudoti funkciją ROW . Pvz., pasirinkite tuščią stulpelį 10 langelių darbaknygėje praktiką, įveskite šią masyvo formulę ir paspauskite Ctrl + Shift + Enter:

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

Apžvelkime formulę, kurią naudojote anksčiau, = didelis (A5:A14,ROW(INDIRECT("1:3"))) , pradedant nuo vidinių skliaustus ir darbo į išorę: funkciją INDIRECT grąžina rinkinio teksto reikšmes, šiuo atveju reikšmės 1 – 3. Funkcija ROW savo ruožtu generuoja trijų langelių stulpelio masyvo. Didelis funkcija naudoja reikšmių langelių diapazonas A5:A14, ir ji vertinama tris kartus, vieną kartą už kiekvieną nuorodą, kurį grąžino funkcija ROW . Reikšmės 3 200, 2 700 ir 2000 pateikiami trijų langelių stulpelio masyvą. Jei norite rasti daugiau reikšmių, galite pridėti daugiau langelių diapazono funkciją INDIRECT .

Kaip anksčiau pavyzdžius, galite naudoti šią formulę su kitomis funkcijomis, pvz., SUM ir AVERAGE.

Ilgiausios tekstinės eilutės radimas langelių diapazone

Eiti atgal į anksčiau teksto eilutės pavyzdžiui, tuščiame langelyje įveskite šią formulę ir paspauskite Ctrl + Shift + Enter:

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

Tekstas "keli langeliai, kurie" rodomas.

Kokios atidžiau pažvelgti į formulę, pradedant nuo vidinių elementų ir darbo į išorę. Funkcija LEN grąžina kiekvieno elementų langelių diapazono a2: A6. Funkcija MAX apskaičiuoja didžiausią reikšmę tarp tuos elementus, kuri atitinka ilgiausio teksto eilutės, kuri yra langelyje A3.

Č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 pateiks didžiausią reikšmę, kuri yra mažesnė nei peržvalgos reikšmė arba jai lygi. Jei nurodysite 0, MATCH pateiks 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.

Galiausiai, funkcija INDEX trunka argumentai: masyvo ir per masyvo eilučių ir stulpelių skaičių. Langelių diapazono a2: A6 teikia masyvą, funkcija MATCH pateikia langelio adresas ir paskutinio argumento (1) nurodo, kad reikšmė, gaunama iš pirmo stulpelio masyvo.

Š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 pradinės reikšmės, bet nėra klaidos reikšmių. Pradedant vidinėmis funkcijomis ir veikiančiomis išorėn, funkcija ISERROR ieško klaidų langelių diapazone (Duomenys). Funkcija IF pateikia konkrečią reikšmę, jei jūsų nurodyta sąlyga yra TRUE, ir kitą reikšmę, jei sąlyga yra FALSE. Šiuo atveju ji pateikia tuščias eilutes ("") pagal visas klaidų reikšmes, nes jų sąlygos rezultatas yra TRUE, ir pateikia likusias diapazono (Duomenys) reikšmes, nes jų sąlygos rezultatas yra FALSE, t. y. jose nėra klaidos 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 pateikia 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ė pateikia 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*(ManoDuomenys<>TavoDuomenys))

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ė pateikia 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 pateikia 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 pateikia pirmosios reikšmės eilutę.

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

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

Patvirtinimas

Šiame straipsnyje dalis buvo remiantis Excel Power vartotojo parašyta Colin Wilcox, ir pritaikyti iš 14 ir 15 Excel 2002 formules, John Walkenbach, buvusio Excel MVP knygoje stulpelių.

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

Dinaminis masyvo formulės vs senstelėjusių CSE masyvo formulės

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

×