Masyvo formulių gairės ir pavyzdžiai

Masyvo formulių gairės ir pavyzdžiai

Norėdami tapti patyrusiu „Excel“ vartotoju, turite žinoti, kaip naudoti masyvo formules, kurias naudojant atliekami tokie skaičiavimai, kurių negalima atlikti naudojant ne masyvo formules. Šis straipsnis pagrįstas „Excel“ patyrusio vartotojo skiltimis, parašytomis Colino Wilcoxo ir adaptuotomis iš Johno Walkenbacho, „Excel“ MVP, parašytos knygos „Excel 2002“ formulės 14 ir 15 skyrių.

Sužinokite apie masyvo formules

Masyvo formulės yra dažnai nurodomos kaip CSE (Ctrl + Shift + Enter) formulės, nes, norint užbaigti formulę, reikia spustelėti ne tik „Enter“, o Ctrl + Shift + Enter.

Kodėl reikėtų naudoti masyvo formules?

Jei jau esate naudoję programos „Excel“ formules, žinote, kad galite atlikti gana sudėtingas operacijas. Pavyzdžiui, galite apskaičiuoti bendrą paskolos kainą per bet kurį nurodytą metų skaičių. Naudodami jas, galėsite atlikti sudėtingas užduotis, pvz.:

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

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

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

Trumpas įvadas į masyvus ir masyvo formules

Masyvo formulė – tai formulė, galinti atlikti kelis vieno arba kelių masyvo elementų skaičiavimus. Masyvo formulės gali pateikti kelis rezultatus arba vieną rezultatą. Pavyzdžiui, galite kurti masyvo formulę langelių diapazone ir naudoti ją tarpinių sumų stulpeliui arba eilutei apskaičiuoti. Taip pat galite įterpti masyvo formulę į vieną langelį ir apskaičiuoti vieną sumą. Masyvo formulė, esanti keliuose langeliuose, vadinama kelių langelių formule, o viename langelyje esanti masyvo formulė – vieno langelio formule.

Paskesniame skyriuje pateikti pavyzdžiai, vaizduojantys, kaip kurto kelių langelių ir vieno langelio masyvo formules.

Pabandykite!

Š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

Tai į naršyklę įdėtoji darbaknygė. Nors joje yra duomenų pavyzdžių, jums reikia žinoti, kad negalite kurti ar keisti masyvo formulių įdėtojoje darbaknygėje, – jums reikia programos „Excel“. Įdėtojoje darbaknygėje galite peržiūrėti atsakymus ir kai kurį tekstą, paaiškinantį masyvo formulių veikimą, bet norėdami iš tikrųjų įvertinti masyvo formulę, turėsite peržiūrėti darbaknygę „Excel“.

Kelių langelių masyvo formulės kūrimas

  1. Nukopijuokite visą toliau pateiktą lentelę ir įklijuokite ją į programos „Excel“ tuščio darbalapio A1 langelį.

    Pardavėjas

    Automobilio
    tipas

    Parduotų
    skaičius

    Vieneto
    kaina

    Bendra
    pardavimo suma

    Juška

    Sedanas

    5

    33 000

    Kupė

    4

    37 000

    Stankevičius

    Sedanas

    6

    24 000

    Kupė

    8

    21 000

    Dūda

    Sedanas

    3

    29 000

    Kupė

    1

    31 000

    Vilutis

    Sedanas

    9

    24 000

    Kupė

    5

    37 000

    Barkauskas

    Sedanas

    6

    33 000

    Kupė

    8

    31 000

    Formulė (Bendroji suma)

    Bendroji suma

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

    =SUM(C2:C11*D2:D11)

  2. Norėdami matyti bendrą kiekvieno pardavėjo kupė ir sedanų tipų automobilių pardavimo sumą, pažymėkite E2:E11, įveskite formulę =C2:C11*D2:D11, tada paspauskite Ctrl + Shift + Enter.

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

Galite atsisiųsti šią darbaknygę spustelėdami žalią „Excel“ mygtuką, kuris yra juodoje juostoje darbaknygės apatinėje dalyje. Tada galite atidaryti failą programoje „Excel“, pasirinkti langelius su masyvo formulėmis ir spustelėti Ctrl + Shift + Enter, kad paleistumėte formulę.

Jeigu dirbate su „Excel“, įsitikinkite, kad Lapas1 yra aktyvus, tada pažymėkite langelius E2:E11. Spustelėkite F2 ir įveskite formulę =C2:C11*D2:D11 į dabartinį langelį, E2. Jeigu spustelėsite „Enter“, pamatysite, kad formulė įvedama tik langelyje E2, ir rodoma 165000. Geriau, įrašę formulę, spustelėkite Ctrl + Shift + Enter vietoj vieno „Enter“. Dabar pamatysite rezultatus langeliuose E2:E11. Atkreipkite dėmesį, kad formulių juostoje formulė pasirodo kaip {=C2:C11*D2:D11}. Tai nurodo, kad tai masyvo formulė, kaip toliau pateikiamoje lentelėje.

Paspaudus Ctrl + Shift + Enter, programa „Excel“ įterpia formulę į riestinius skliaustus ({ }) ir įterpia formulės egzempliorių į kiekvieną pažymėto diapazono langelį. 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 F10 įrašykite šią formulę, tada paspauskite Ctrl + Shift + Enter:

=SUM(C2:C11*D2:D11)

Šiuo atveju „Excel“ padaugina masyve esančias reikšmes (langelių diapazone nuo C2 iki D11) ir naudoja funkciją SUM, kad sudėtų bendrąsias sumas. 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 G11) yra visiškai nepriklausoma nuo kelių langelių formulės (langeliuose E2–E11 esančios formulės). Tai kitas masyvo formulių naudojimo pranašumas  – lankstumas. Galite keisti formules stulpelyje E arba naikinti tą stulpelį. Šie veiksmai neturės įtakos G11 langelio formulei.

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. Papildoma saugos priemonė – norėdami patvirtinti formulės keitimą, turite paspausti Ctrl + Shift + Enter.

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

Masyvo formulių sintaksė

Paprastai masyvo formulės naudoja standartinę formulių sintaksę. Jos visos pradedamos lygybės (=) ženklu, be to, masyvo formulėse galite naudoti visas įtaisytąsias „Excel“ funkcijas. Pagrindinis skirtumas yra toks: naudodami masyvo formules turite paspausti Ctrl + Shift + Enter, kad galėtumėte įvesti savo formulę. Tai padarius, programa „Excel“ įtraukia masyvo formulę į riestinius skliaustus. Jei įvesite riestinius skliaustus rankiniu būdu, formulė bus konvertuota į tekstinę eilutę ir neveiks.

Masyvo funkcijos yra tikrai efektyvi kompleksinių formulių kūrimo priemonė. 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).

Masyvo formulių įvedimas ir keitimas

Svarbu    Kai norite įvesti arba redaguoti masyvo formulę, paspauskite Ctrl + Shift + Enter. Š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.

  • 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ę (pavyzdžiui, =C2:C11*D2:D11), paspauskite „Delete“, tada – Ctrl + Shift + Enter.

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

Masyvo formulės išplėtimas

Kartais reikia išplėsti masyvo formulę. Procesas yra nesudėtingas, bet turite atsiminti ankstesniame skyriuje išvardytas taisykles.

Šiame darbalapyje mes pridėjome daugiau pardavimo eilučių, eilutės nuo 12 iki 17. Čia mes norime naujinti masyvo formules, kad jose dabar būtų šios naujos eilutės.

Nepamirškite to padaryti programos „Excel“ darbalaukio programoje (atsisiuntę darbaknygę į savo kompiuterį).

Masyvo formulės išplėtimas

  1. Nukopijuokite visą šią lentelę į „Excel“ darbalapio A1 langelį.

    Pardavėjas

    Automobilio
    tipas

    Parduotų
    skaičius

    Vieneto
    kaina

    Bendra
    pardavimo suma

    Juška

    Sedanas

    5

    33 000

    165 000

    Kupė

    4

    37 000

    148 000

    Stankevičius

    Sedanas

    6

    24 000

    144 000

    Kupė

    8

    21 000

    168 000

    Dūda

    Sedanas

    3

    29 000

    87 000

    Kupė

    1

    31 000

    31 000

    Vilutis

    Sedanas

    9

    24 000

    216 000

    Kupė

    5

    37 000

    185 000

    Barkauskas

    Sedanas

    6

    33 000

    198 000

    Kupė

    8

    31 000

    248 000

    Grigas

    Sedanas

    2

    27 000

    Kupė

    3

    30 000

    Vasiliauskas

    Sedanas

    4

    22 000

    Kupė

    1

    41 000

    Navickas

    Sedanas

    5

    32 000

    Kupė

    3

    36 000

    Bendroji suma

  2. Pažymėkite langelį E18 , langelyje A20 įveskite bendrosios sumos formulę = SUM(C2:C17*D2:D17), tada paspauskite Ctrl + Shift + Enter.
    Rezultatas turėtų būti 2 131 000.

  3. Pažymėkite langelių diapazoną, kuriame yra dabartinė masyvo formulė (E2:E11), ir tuščius langelius (E12:E17), esančius greta naujųjų duomenų. Kitaip tariant, pažymėkite langelius E2:E17.

  4. Paspauskite F2, kad perjungtumėte redagavimo režimą.

  5. Formulės juostoje pakeiskite C11 į C17, o D11 – į D17, tada paspauskite Ctrl + Shift + Enter.
    „Excel“ atnaujina langeliuose nuo E2 iki E11 esančią formulę ir įterpia formulės egzempliorių į naujus langelius nuo E12 iki E17.

  6. Įveskite masyvo formulę = SUM(C2:C17*D2*D17) į langelį F17, kad ji nurodytų langelius, esančius eilutėse nuo 2 iki 17, tada spustelėkite Ctrl + Shift + Enter, kad įvestumėte masyvo formulę.
    Nauja bendroji suma turi būti 2 131 000.

Masyvo formulių naudojimo trūkumai

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

  • Galite dažnai pamiršti paspausti Ctrl + Shift + Enter. Nepamirškite paspausti šio klavišų derinio kas kartą, kai įvedate arba redaguojate masyvo formulę.

  • Kiti vartotojai gali nesuprasti jūsų formulių. Paprastai masyvo formulės nedokumentuojamos darbalapyje. Todėl, jei jūsų darbaknyges modifikuos kiti žmonės, turėtumėte vengti masyvo formulių arba įsitikinti, kad tokie vartotojai žino apie bet kurias masyvo formules ir prireikus moka jas keisti.

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

Puslapio viršus

Sužinokite apie masyvo konstantas

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}

Jūs jau žinote, kad turite paspausti Ctrl + Shift + Enter kurdami masyvo formules. Masyvo konstantos yra masyvo formulių komponentas, todėl konstantas reikia įtraukti į riestinius skliaustus rankiniu būdu, t. y. juos įvesti. Tada reikia paspausti Ctrl + Shift + Enter, kad būtų galima įvesti 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.

Puslapio viršus

Vienmačių ir dvimačių konstantų kūrimas

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

Horizontaliosios konstantos kūrimas

  1. Naudokite ankstesnių pavyzdžių darbaknygę arba sukurkite naują.

  2. Pažymėkite langelius nuo A1 iki E1.

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

    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. Langelyje A3 į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.

Masyvo konstantų sintaksė

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 įsidėmėkite, kad įrašę konstantą į masyvo formulę, turite spustelėti Ctrl + Shift + Enter, 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 pamėginti, nukopijuokite funkciją, pažymėkite savo darbalapyje tuščią langelį, įklijuokite formulę į formulių juostą ir spustelėkite Ctrl + Shift + Enter. Pamatysite tą patį rezultatą, kaip ankstesniame pratime, kur buvo naudota masyvo formulė:

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

Elementai, kuriuos galite naudoti konstantose

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

Masyvo konstantų pavadinimas

Vienas iš geriausių būdų naudoti masyvo konstantas yra suteikti joms pavadinimą. 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ą.
    Bus rodomas 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.

Masyvų konstantų trikčių šalinimas

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

  • Kai kurie elementai atskirti netinkamu simboliu. Jei praleisite kablelį arba kabliataškį arba įterpsite jį netinkamoje vietoje, masyvo konstanta gali būti sukurta klaidingai arba gali būti rodomas perspė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.

Masyvo konstantų naudojimas

Toliau pavaizduoti keli būdai, kaip galima naudoti masyvo konstantas masyvo formulėse. Kai kuriuose iš pavyzdžių naudojama funkcija TRANSPOSE eilutėms konvertuoti į 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ą.

Puslapio viršus

Pagrindinių masyvo formulių naudojimas

Š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. Paspaudus Ctrl + Shift + Enter šis skaičių masyvas įvedamas į langelių diapazoną C8:E10 naudojant masyvo formulę.
    Darbalapyje langeliai nuo 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

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

Masyvo konstantos kūrimas pagal esamas reikšmes

  1. Pažymėję C1:C3 langelius paspauskite F2, kad perjungtumėte redagavimo režimą.
    Masyvo formulė turėtų vis tiek būti = C8:E10.

  2. Paspauskite F9, kad konvertuotumėte langelių nuorodas į reikšmes. Programoje „Excel“ reikšmės konvertuojamos į masyvo konstantą. Dabar formulė turėtų būti ={10,20,30;40,50,60;70,80,90}, taip pat kaip C8:E10.

  3. Norėdami įvesti masyvo konstantą kaip masyvo formulę, paspauskite Ctrl + Shift + Enter.

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

  2. Pažymėkite A9 langelį ir paspauskite Ctrl + Shift + Enter, kad matytumėte visą simbolių skaičių langeliuose A2:A6 (66).

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

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)

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

=SUM(LEN(A2:A6))

Šiuo atveju funkcija LEN pateiks kiekvienos kiekvieno diapazono langelio tekstinės eilutės ilgį. Funkcija SUM sudės šias reikšmes ir parodys rezultatą (66) langelyje A9, kuriame yra formulė.

n mažiausių reikšmių radimas diapazone

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

  1. Pažymėkite langelius nuo A16 iki A18.
    Šiame langelių rinkinyje bus rodomi masyvo formulės pateikti rezultatai.

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

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

Reikšmės 400, 475 ir 500 atitinkamai rodomos langeliuose nuo A16 iki A18.

Ši formulė naudoja masyvo konstantą, kad tris kartus įvertintų funkciją SMALL ir pateiktų mažiausią (1), antrą mažiausią (2) ir trečią mažiausią (3) masyvo, esančio langeliuose A1:A10, elementus. Norėdami rasti daugiau reikšmių, turite į konstantą įtraukti daugiau argumentų ir atitinkamą skaičių rezultatų langelių į diapazoną A12:A14. Taip pat su šia formule galite naudoti papildomas funkcijas, pvz., SUM arba AVERAGE. Pavyzdžiui:

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

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

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

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

Reikšmės 3 200, 2 700 ir 2 000 rodomos langeliuose nuo A1 iki A3 atitinkamai.

Č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, savo pavyzdžio darbaknygėje pažymėkite tuščią 10 langelių stulpelį, langeliuose A5:A14 į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ą.

Žvilgtelėkime į formulę, kurią naudojote anksčiau – =LARGE(A5:A14,ROW(INDIRECT("1:3"))) – pradėdami nuo vidinių skliaustų: funkcija INDIRECT pateikia tekstinių reikšmių rinkinį, šiuo atveju – reikšmes nuo 1 iki 3. Funkcija ROW sugeneruoja trijų langelių stulpelio masyvą. Funkcija LARGE naudoja langelių diapazono A1:A10 reikšmes, kurias įvertina tris kartus, po kartą pagal kiekvieną funkcijos ROW pateikiamą nuorodą. Reikšmės 3 200, 2 700 ir 2 000 pateikiamos trijų langelių stulpelio masyve. Jei norite rasti daugiau reikšmių, į funkciją INDIRECT įtraukite didesnį langelių diapazoną.

Šią formulę taip pat galite naudoti su kitomis funkcijomis, pvz., SUM ir AVERAGE.

Ilgiausios tekstinės eilutės radimas langelių diapazone

Ši formulė veikia tik tada, kai duomenų diapazoną sudaro vienas langelių stulpelis. Į Lapas3 įveskite šią formulę langelyje A16, tada spauskite Ctrl + Shift + Enter:

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

Langelyje A16 pasirodo tekstas „keli langeliai, kurie“.

Žvilgtelėkime į formulę pradėdami nuo vidinių elementų. Funkcija LEN pateikia kiekvieno langelių diapazono A6:A9 elemento ilgį. Funkcija MAX apskaičiuoja didžiausią šių elementų reikšmę, kuri atitinka ilgiausią tekstinę eilutę, esančią langelyje C3.

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

Ši eilutė yra šiame masyve:

LEN( A6:A9 )

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.

O funkcija INDEX naudoja šiuos argumentus: masyvas ir jame esantis eilutės bei stulpelio numeris. Langelių diapazonas A6:A9 pateikia masyvą, funkcija MATCH – langelio vietą, o galutinis argumentas (1) nurodo, kad reikšmė yra iš pirmojo masyvo stulpelio.

Puslapio viršus

Išplėstinių masyvo formulių naudojimas

Š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 pateikia jų skirtumų skaičių. Jei dviejų diapazonų turinys yra identiškas, formulė pateikia 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 pateikia 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))

Puslapio viršus

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.

Formulių apžvalga

Tobulinkite savo į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ų.

×