Masyvo formulių gairės ir pavyzdžiai

Š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 langelį A1.

Pardavėjas	Automobilio  tipas	Parduotų  skaičius	Vieneto  kaina	Bendra  pardavimo suma
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ų tipų automobilių pardavimo sumą, pažymėkite langelius E2:E11, įveskite formulę =C2:C11*D2:D11, tada paspauskite klavišus Ctrl+Shift+Enter.

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

Kai paspaudžiate klavišus Ctrl+Shift+Enter, „Excel“ apskliaudžia formulę riestiniais skliaustais ({ }) ir į kiekvieną pažymėto diapazono langelį įterpia formulės egzempliorių. 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}. 

• Vieno langelio masyvo formulės kūrimas

Darbaknygės langelyje D13 įveskite nurodytą formulę, tada paspauskite klavišus 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 D13 yra visiškai nepriklausoma nuo kelių langelių formulės (formulės langeliuose nuo E2 iki E11). Tai dar vienas masyvo formulių naudojimo pranašumas – lankstumas. Galima keisti formules stulpelyje E arba visiškai panaikinti tą stulpelį, nepadarant įtakos langelyje D13 esančiai 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 klavišą Delete. Turite pažymėti visą langelių diapazoną (E2–E11) ir pakeisti viso masyvo formulę, arba palikti masyvą tokį, koks jis yra. Yra papildoma saugos priemonė: norėdami patvirtinti bet kokį formulės keitimą, turite paspausti klavišus Ctrl+Shift+Enter.

• Mažesnio dydžio failai.    Dažnu atveju 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ę formulių sintaksę. Jos visos prasideda lygybės ženklu (=); masyvo formulėse galite naudoti daugumą integruotų „Excel“ funkcijų. Pagrindinis skirtumas yra tas, kad naudojant masyvo formulę, norėdami įvesti formulę, turite paspausti klavišus Ctrl+Shift+Enter. Kai tai padarysite, „Excel“ apsups masyvo formulę riestiniais skliaustais. Jei įvesite riestinius skliaustus rankiniu būdu, formulė bus konvertuota į teksto eilutę ir neveiks.

Masyvo funkcijos gali būti efektyvus sudėtingų formulių kūrimo būdas. 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).

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. Norėdami tai išbandyti, pažymėkite darbaknygės langelį E3 ir paspauskite klavišą „Delete“. „Excel“ rodo pranešimą, kuriame sakoma, kad negalite pakeisti 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 diapazoną (pvz., E2:E11), tada paspauskite klavišą Delete.

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

Kartais reikia išplėsti masyvo formulę. Pažymėkite pirmąjį esamo masyvo diapazono langelį ir tęskite, kol pažymėsite visą diapazoną, į kurį norite išplėsti formulę. Paspauskite klavišą F2 norėdami redaguoti formulę, tada paspauskite CTRL+SHIFT+ENTER, kad patvirtintumėte formulę, kai pakoreguosite formulės diapazoną. Svarbu pažymėti visą diapazoną, pradedant nuo viršutinio kairiojo masyvo langelio. Viršutinis kairysis langelis yra redaguojamas.

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

• Kartais galite pamiršti paspausti Ctrl+Shift+Enter. Taip gali atsitikti net patyrusiems „Excel“ vartotojams. Nepamirškite paspausti šio klavišų derinio kaskart, 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 šie žmonės ž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ą.

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, kai kuriate 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 įvestumėte visa 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 nurodytą formulę ir paspauskite Ctrl+Shift+Enter:

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

   Šiuo atveju turėtumėte įvesti atidaromuosius ir uždaromuosius skliaustus ({ }), o „Excel“ įtrauks antrą rinkinį.

   Rodomas toks rezultatas.

   

Vertikaliosios konstantos kūrimas

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

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

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

   Rodomas toks rezultatas.

   

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

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

   Rodomas toks rezultatas:

   

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

   

   Langelyje A3 bus rodoma reikšmė 85.

Kitame skyriuje paaiškintas formulės veikimas.

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

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 įtraukę konstantą į masyvo formulę, turite paspausti 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 paspauskite Ctrl+Shift+Enter. 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ų 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ą.
   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:

   

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

5. Įveskite nurodytą formulę ir paspauskite Ctrl+Shift+Enter.

   = Ketvirtis1

   Rodomas toks rezultatas.

   

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

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 nurodytą 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 nurodytą 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 nurodytą 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 nurodytą 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 nurodytą 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 { (atidarantįjį riestinį skliaustą) 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 š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 nurodytą 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ę langelius C1:C3 langelius, paspauskite F2, kad įjungtumėte redagavimo režimą. 

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

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

   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 paspauskite Ctrl+Shift+Enter, kad matytumėte visą simbolių skaičių langeliuose A2:A6 (66).

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

Ši formulė, naudojama langelyje A8, skaič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 teksto eilutės ilgį. Funkcija SUM sudės šias reikšmes ir parodys rezultatą (66).

n mažiausių reikšmių radimas diapazone

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

1. Langeliuose A1:A11 įveskite 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 nurodytą formulę ir paspauskite Ctrl+Shift+Enter:

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

Š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ų. Taip pat su šia formule galite naudoti papildomas funkcijas, pvz., SUM arba AVERAGE. Pavyzdžiui:

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

=AVERAGE(SMALL(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 nurodytą formulę ir paspauskite Ctrl+Shift+Enter:

   =LARGE(A1:A10,ROW(INDIRECT("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, pažymėkite tuščią 10 langelių stulpelį mokomojoje darbaknygėje, įveskite nurodytą 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 teksto 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ą.

Išsamiau pažiūrėkime į formulę, kurią naudojome anksčiau – =LARGE(A5:A14,ROW(INDIRECT("1:3"))) – pradėdami nuo vidinių skliaustų ir eidami į išorę: funkcija INDIRECT grąžina tekstinių reikšmių rinkinį, šiuo atveju reikšmes nuo 1 iki 3. Savo ruožtu, funkcija ROW sugeneruoja stulpelių masyvą iš trijų langelių. Funkcija LARGE naudoja reikšmes langelių diapazone A5:A14 ir ji įvertinama tris kartus, po vieną kartą kiekvienai nuorodai, kurią grąžina funkcija ROW. Reikšmės 3200, 2700 ir 2000 yra grąžinamos į stulpelių masyvą iš trijų langelių. Jei norite rasti daugiau reikšmių, į funkciją INDIRECT įtraukite didesnį langelių diapazoną.

Kaip ir ankstesnių pavyzdžių atveju, šią formulę galite naudoti su kitomis funkcijomis, pvz., SUM ir AVERAGE.

Ilgiausios teksto eilutės radimas langelių diapazone

Grįžkite į ankstesnį teksto eilutės pavyzdį, tuščiame langelyje įveskite nurodytą formulę ir paspauskite Ctrl+Shift+Enter:

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

Atsiranda tekstas „keli langeliai, kurie“.

Paanalizuokite formulę pradėdami nuo vidinių elementų. Funkcija LEN grąžina kiekvieno langelių diapazono A2:A6 elemento ilgį. Funkcija MAX apskaičiuoja didžiausią šių elementų reikšmę, kuri atitinka ilgiausią teksto eilutę, kuri yra langelyje A3.

Čia viskas tampa sudėtingiau. Funkcija MATCH apskaičiuoja langelio, kuriame yra ilgiausia teksto eilutė, 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 teksto 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 – langelio vietą, o galutinis argumentas (1) nurodo, kad reikšmė yra iš pirmojo 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 pradinės reikšmės atėmus klaidos reikšmes. Pradedant nuo vidinių funkcijų ir einant į išorę, funkcija ISERROR ieško klaidų langelių diapazone (Duomenys). Funkcija IF pateikia konkrečią reikšmę, jei jūsų nurodyta sąlyga įvertinama kaip TRUE, bei kitą reikšmę, jei sąlyga įvertinama kaip FALSE. Šiuo atveju ji grąžina tuščias eilutes ("") visų klaidų reikšmių atveju, nes jos įvertinamos kaip TRUE ir grąžina likusias reikšmes iš diapazono (Duomenys), nes jos įvertinamos kaip FALSE, tai reiškia, kad jose nėra klaidų reikšmių. Tada funkcija SUM apskaičiuoja sumą filtruotame masyve.

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*(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ė 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))