Masīva formula ir tāda formula, kas ar vienu vai vairākiem masīva elementiem var veikt vairākus aprēķinus. Masīvu varat iedomāties kā vērtību rindu vai kolonnu, vai vērtību rindu un kolonnu kombināciju. Masīva formulas var atgriezt vienu vai vairākus rezultātus.
Sākot ar Microsoft 365 2018. gada septembra atjauninājumu jebkura formula, kas var parādīt vairākus rezultātus, automātiski tos sadalīs pa zemāk vai sānos esošajās šūnās. Šīs darbības izmaiņas papildina arī vairākas jaunas dinamiskā masīva funkcijas. Dinamiskā masīva formulas, neatkarīgi no tā, vai tās izmanto esošās funkcijas vai dinamiskās masīva funkcijas, ir jāievada tikai vienā šūnā un jāapstiprina, nospiežot taustiņu Enter. Agrākām mantotajām formulām vispirms ir jāatlasa viss izvades diapazon un pēc tam jāapstiprina formula ar taustiņu kombināciju Ctrl+Shift+Enter. Šīs formulas bieži sauc par CSE formulām.
Masīva formulas var izmantot, lai veiktu sarežģītus uzdevumus, piemēram:
-
Jūs varat ātri veidot parauga datu kopas.
-
Saskaitītu šūnu diapazonā esošo rakstzīmju skaitu.
-
Summētu tikai tos skaitļus, kas atbilst noteiktiem nosacījumiem, piemēram, diapazona mazākās vērtības vai vērtības, kas ir starp norādīto augšējo un apakšējo robežu.
-
Summētu katru vērtību diapazona n. vērtību.
Nākamās sadaļas piemēros parādīts, kā izveidot vairākšūnu un vienšūnas masīva formulas. Kur iespējams, norādījām dinamisko masīvu funkciju piemērus, kā arī esošās masīvu formulas, kas ievadītas gan kā dinamiskie, gan kā mantotie masīvi.
Lejupielādējiet mūsu piemērus
Lejupielādējiet parauga darbgrāmatu ar visu šajā rakstā minēto masīva formulu piemēriem.
Šajā vingrinājumā parādīts, kā izmantot vairākšūnu un vienšūnas masīva formulas, lai aprēķinātu pārdošanas rādītāju kopu. Pirmajā darbību kopā tiek izmantota vairākšūnu formula, lai aprēķinātu starpsummu kopu. Otrajā kopā tiek izmantota vienšūnas formula, lai aprēķinātu kopsummu.
-
Vairākšūnu masīva formula
-
Šajā piemērā tiek aprēķināts katra pārdevēja kopējais pārdoto sedanu un kupeju skaits, ievadot formulu =F10:F19*G10:G19 šūnā H10.
Nospiežot taustiņu Enter, jūs redzēsit rezultātus šūnās H10:H19. Pievērsiet uzmanību tam, ka izplešanās diapazons ir izcelts ar malu, kad jūs atlasāt jebkuru no izplešanās diapazona šūnām. Pievērsiet uzmanību ar tam, ka formulas šūnās H10:H19 ir pelēkotas. Tās ir norādītas tikai atsaucei, tāpēc, ja vēlaties pielāgot formulu, jums būs jāatlasa šūna H10, kurā atrodas galvenā formula.
-
Vienšūnas masīva formula
Parauga darbgrāmatā šūnā H20 ierakstiet vai nokopējiet un ielīmējiet =SUM(F10:F19*G10:G19), un pēc tma nospiediet taustiņu Enter.
Šajā gadījumā programma Excel sareizina masīva vērtības (šūnu diapazons F10–G19) un pēc tam izmanto funkciju SUM, lai saskaitītu šīs summas. Rezultātā tiek iegūta pārdošanas apjoma kopsumma 1 590 000 EUR.
Šajā piemērā redzams, cik jaudīga var būt šāda veida formula. Piemēram, iedomājieties, ka jums ir 1000 datu rindu. Daļu vai visus šos datus var sasummēt, izveidojot masīva formulu vienā šūnā, nevis velkot formulu pa 1000 rindām. Turklāt ievērojiet, ka vienšūnas formula šūnā H20 ir pilnībā neatkarīga no vairākšūnu formulas (formula šūnās H10–H19). Tā ir vēl viena masīva formulu izmantošanas priekšrocība — elastība. Varat mainīt formulas kolonnā H vai pat izdzēst šo kolonnu, neietekmējot formulu šūnā G20. Jums var noderēt arī atsevišķi parādītas kopsummas kā šeit, jo tas palīdz jums pārbaudīt rezultātu precizitāti.
-
Dinamiskām masīva formulām ir arī šādas priekšrocības:
-
Konsekvence Noklikšķinot uz jebkuras šūnas, kas atrodas zem H10, redzat to pašu formulu. Šāda konsekvence palīdz nodrošināt lielāku precizitāti.
-
Drošība Vairākšūnu masīva formulas komponentu nevar pārrakstīt. Piemēram, noklikšķiniet uz šūnas H11 un nospiediet taustiņu Delete. Excel nemainīs masīva izvadi. Lai tu mainītu, jums ir jāatlasa masīva augšējā kreisā rūtiņa jeb rūtiņa H10.
-
Mazāka lieluma faili Vairāku starpformulu vietā bieži vien var izmantot vienu masīva formulu. Piemēram, šajā automašīnu tirdzniecības piemērā viena masīva formula tiek izmantota, lai aprēķinātu rezultātus kolonnā E. Ja tiktu izmantotas parastās formulas, piemēram, =F10*G10, F11*G11, F12*G12 utt., šo pašu rezultātu aprēķināšanai būtu jāizmanto 11 dažādas formulas. Tas nav nekas sarežģīts, bet kā būtu, ja jums būtu jātiek galā ar tūkstošiem rindu? Tad tam var būt liela nozīme.
-
Efektivitāte Masīva funkcijas var izmantot kā efektīvu risinājumu sarežģītu formulu izveidei. Masīva formula =SUM(F10:F19*G10:G19) ir tāda pati kā šī formula: =SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).
-
Izplešanās Dinamisko masīvu formulu izvēršanās notiek automātiski izvades diapazonā. Ja jūsu avota dati ir Excel tabulā, tad, jums pievienojot vai noņemot datus no masīva diapazona, masīvs automātiski maina lielumu.
-
#SPILL! kļūda Dinamiskais masīvs parādīja #SPILL! kļūdu, kas norāda uz to, ka paredzētais izplešanās diapazons ir bloķēts nezināma iemesla dēļ. Pēc bloķēšanas iemesla novēršanas formula automātiski izpletīsies.
-
Masīva konstantes ir masīva formulu komponents. Masīva konstantes tiek veidotas, ievadot elementu sarakstu un manuāli to iekļaujot figūriekavās ({ }), piemēram, šādi:
={1\2\3\4\5} vai ={“janvāris”\“februāris”\“marts”}
Atdalot elementus ar komatiem, tiek izveidots horizontāls masīvs (rinda). Atdalot elementus ar semikoliem, tiek izveidots vertikāls masīvs (kolonna). Lai izveidotu divdimensiju masīvu, katras rindas elementi jums ir jāatdala ar komatiem, bet rindas ir jāatdala ar semikoliem.
Nākamās darbības ļaus patrenēties, veidojot horizontālu, vertikālu, kā arī divdimensiju konstanti. Mūsu piemēros tiks izmantota SEQUENCE funkcija, lai automātiski ģenerētu masīvu konstantes, kā arī manuāli ievadītās masīvu konstantes.
-
Horizontālas konstantes izveide
Izmantojiet iepriekšējo piemēru darbgrāmatu vai izveidojiet jaunu. Atlasiet tukšu šūnu un ievadiet =SEQUENCE(1,5). SEQUENCE funkcija veido 1 rindas un 5 kolonnu masīvu tāpat kā ={1\2\3\4\5}. Tiek rādīts šāds rezultāts:
-
Vertikālas konstantes izveide
Atlasiet jebkur tukšo šūnu ar brīvu vietu zem tas un ievadiet =SEQUENCE(5) vai ={1;2;3;4;5}. Tiek rādīts šāds rezultāts:
-
Divdimensiju konstantes izveide
Atlasiet jebkuru tukšo šūnu ar brīvu vietu labajā pusē un zem tās, un ievadiet =SEQUENCE(3,4). Tiek rādīts šāds rezultāts:
Tāpat jūs varat ievadīt: vai ={1\2\3\4;5\6\7\8;9\10\11\12}, bet jums būs jāpievērš uzmanība komatu un kolu lietojumam.
Kā redzat, SEQUENCE opcija piedāvā būtiskas priekšrocības, salīdzinot ar manuālu masīva konstantes vērtību ievadīšanu. Pirmkārt, tā taupa jūsu laiku un palīdz samazināt manuālās ievades kļūdas. Šo formulu ir arī vieglāk lasīt, jo kolus mēdz būt grūti atšķirt no komatiem.
Šajā piemērā masīva konstantes tiek izmantotas kā daļa no lielākas formulas. Parauga darbgrāmatā dodieties uz darblapu Konstante formulā vai izveidojiet jaunu darblapu.
Šūnā D9 tika ievadīts =SEQUENCE(1,5,3,1), bet jūs varat ievadīt arī 3, 4, 5, 6 un 7 šūnās A9:H9. Skaitļu izvēlei nav nekādas nozīmes, daudzveidībai izvēlējāmies ko atšķirīgu no 1-5.
Šūnā E11 ievadiet =SUM(D9:H9*SEQUENCE(1,5)) vai =SUM(D9:H9*{1\2\3\4\5}). Formulu rezultāts ir 85.
Funkcija SEQUENCE izveido masīva konstantes ekvivalentu {1\2\3\4\5}. Excel vispirms veic darbības ar vienādojumiem iekavās, tāpēc nākamie divi elementi ir šūnu vērtības D9:H9 un reizinātājs (*). Šajā brīdī formulā tiek sareizinātas iekļautā masīva vērtības ar atbilstošajām konstantes vērtībām. Tas atbilst šai formulai:
=SUM(D9*1,E9*2,F9*3,G9*4,H9*5) vai =SUM(3*1,4*2,5*3,6*4,7*5)
Visbeidzot funkcija SUM saskaita šīs vērtības, iegūstot summā 85.
Lai izvairītos no iekļautā masīva izmantošanas un paturētu darbību tikai atmiņā, varat aizstāt to ar citu masīva konstanti:
=SUM(SEQUENCE(1,5,3,1)*SEQUENCE(1,5)) vai =SUM({3\4\5\6\7}*{1\2\3\4\5})
Elementi, ko var izmantot masīvu konstantēs
-
Masīvu konstantes var saturēt skaitļus, tekstu, loģiskās vērtības (piemēram, TRUE un FALSE), kā arī kļūdu vērtības, piemēram, #N/A. Šos skaitļus var izmantot veselu skaitļu, decimālajā vai zinātniskajā formātā. Ievietojot tekstu, tas ir jāieraksta pēdiņās (“teksts”).
-
Masīva konstantēs nevar iekļaut papildu masīvus, formulas un funkcijas. Citiem vārdiem sakot, tajās var iekļaut tikai tekstu vai skaitļus, kas tiek atdalīti ar komatiem vai semikoliem. Ja ievadāt kādu formulu, piemēram, {1\2\A1:D4} vai {1\2\SUM(Q2:Z8)}, programma Excel parāda brīdinājuma ziņojumu. Turklāt skaitliskās vērtības nedrīkst saturēt procentu zīmes, dolāru zīmes, komatus un iekavas.
Viens no labākajiem veidiem, kā izmantot masīva konstantes, ir piešķirt tām nosaukumus. Nosauktās konstantes ir ērtāk izmantot, kā arī citiem lietotājiem ir vieglāk izprast masīva formulas, kurās tās tiek izmantotas. Lai masīva konstantei piešķirtu nosaukumu un to izmantotu formulā, rīkojieties šādi:
Dodieties uz Formulas > Definētie nosaukumi > Definēt nosaukumu. Lodziņā Nosaukums ierakstiet Quarter1. Lodziņā Attiecas uz ievadiet šādu konstanti (atcerieties par manuālu figūriekavu rakstīšanu):
={"Janvāris"\"Februāris"\"Marts"}
Tagad dialoglodziņam ir jāizskatās šādi:
Noklikšķiniet OK un atlasiet jebkuru rindu ar trim tukšām šūnām, un pēc tam ierakstiet =Quarter1.
Tiek rādīts šāds rezultāts:
Ja vēlaties, lai rezultāti izplestos par vertikāli nevis horizontāli, izmantojiet =TRANSPOSE(Quarter1).
Ja vēlaties, lai tiktu parādīts 12 mēnešu saraksts, kas var būt lietderīgs, piemēram, finanšu pārskata izveidei, jūs varat izmantot SEQUENCE funkciju, lai izveidotu šī gada mēnešu sarakstu. Šīs funkcijas priekšrocība ir tāda, ka arī tad, kad tiek rādīts tikai mēnesis, tam ir derīga datu vērtība, kuru varat izmantot citos aprēķinos. Varat apskatīt šos piemērus parauga darbgrāmatas darblapās Nosaukta masīva konstante un Ātrā parauga datu kopa.
=TEXT(DATE(YEAR(TODAY()),SEQUENCE(1,12),1),“mmm”)
Šeit tiek izmantota DATE funkcija, lai izveidotu datumu atbilstoši šim gadam, SEQUENCE izveido masīva konstanti no 1 līdz 12, no janvāra līdz decembrim, bet TEXT funkcija pārveido rādīšanas formātu par “mmm” (jan, feb, mar, utt.). Ja vēlaties redzēt pilnu mēneša nosaukumu, piemēram, janvāris, izmantojiet “mmmm”.
Ja kā masīva formulu izmantojat nosauktu konstanti, neaizmirstiet ievadīt vienādības zīmi, piemēram, =Quarter1 nevis tikai Quarter1.. Ja to neizdarīsit, programma Excel šo masīvu uztvers kā teksta virkni, un formula nedarbosies tā, kā bijāt cerējis. Atcerieties, ka varat izmantot funkciju, teksta un skaitļu kombinācijas. Viss ir atkarīgs no jūsu radošuma.
Nākamajos piemēros parādīti daži veidi, kā masīva formulās var izmantot masīva konstantes. Dažos piemēros tiek izmantota funkcija TRANSPOSE, kas rindas pārvērš par kolonnām un pretēji.
-
Reizināšana ar katru masīva elementu
Ievadiet =SEQUENCE(1,12)*2 vai ={1\2\3\4;5\6\7\8;9\10\11\12}*2
Jūs varat veikt dalīšanu (/), saskaitīšanu (+) un atņemšanu (-).
-
Masīva elementu kāpināšana
Ievadiet =SEQUENCE(1,12)^2 vai ={1\2\3\4;5\6\7\8;9\10\11\12}^2
-
Kvadrātsaknes atrašana masīva vienumiem
Ievadiet =SQRT(SEQUENCE(1,12)^2)vai =SQRT({1\2\3\4;5\6\7\8;9\10\11\12}^2)
-
Viendimensijas rindas apmainīšana vietām
Ievadiet =TRANSPOSE(SEQUENCE(1,5)) vai =TRANSPOSE({1\2\3\4\5})
Lai arī tika ievadīta horizontāla masīva konstante, funkcija TRANSPOSE to pārvērš par kolonnu.
-
Viendimensijas kolonnas apmainīšana vietām
Ievadiet =TRANSPOSE(SEQUENCE(5,1)) vai =TRANSPOSE({1;2;3;4;5})
Lai arī tika ievadīta vertikāla masīva konstante, funkcija TRANSPOSE to pārvērš par rindu.
-
Divdimensiju konstantes apmainīšana vietām
Ievadiet =TRANSPOSE(SEQUENCE(3,4)) vai =TRANSPOSE({1\2\3\4;5\6\7\8;9\10\11\12})
Funkcija TRANSPOSE katru rindu pārvērš par kolonnu sēriju.
Šajā sadaļā sniegti vienkāršu masīva formulu piemēri.
-
Masīva izveide no esošajām vērtībām
Piemēros tālāk tiek paskaidrots, kā izmantot masīva formulas, lai no esošā masīva izveidotu jaunu masīvu.
Ievadiet =SEQUENCE(3,6,10,10) vai ={10\20\30\40\50\60;70\80\90\100\110\120;130\140\150\160\170\180}
Pārliecinieties, vai pirms 10 ievadāt { (atverošo iekavu) un pēc 180 } (noslēdzošo iekavu), jo tiek veidots skaitļu masīvs.
Pēc tam tukšā šūnā ievadiet =D9# vai =D9:I11. Parādīsies 3 x 6 šūnu masīvs ar tādām pašām vērtībām kā D9:D11. Zīme # tiek saukta par izplešanās diapazona operatoru, un ar tās palīdzību Excel atsaucas uz visu masīva diapazonu bez vajadzības to rakstīt.
-
Masīva konstantes izveide no esošajām vērtībām
Jūs varat pārvērst izplešanās masīva formulas rezultātus par to atsevišķajiem komponentiem. Atlasiet šūnu D9 un nospiediet F2, lai pārietu uz rediģēšanas režīmu. Pēc tam nospiediet F9, lai pārveidotu šūnu atsauces par vērtībām, kuras Excel pārvērtīs par masīva konstanti. Pēc taustiņa Enter nospiešanas formulai =D9#, būtu jābūt ={10\20\30;40\50\60;70\80\90}.
-
Rakstzīmju skaitīšana šūnu diapazonā
Nākamajā piemērā parādīts, kā šūnu diapazonā saskaitīt rakstzīmes. Tai skaitā arī atstarpes.
=SUM(LEN(C9:C13))
Šajā gadījumā LEN funkcija atgriež katras diapazona šūnas teksta virknes garumu. Funkcija SUM saskaita šīs vērtības un parāda rezultātu (66). Ja vēlaties uzzināt vidējo rakstzīmju skaitu, varat izmantot:
=AVERAGE(LEN(C9:C13))
-
Diapazona C9:C13 garākās šūnas saturs
=INDEX(C9:C13,MATCH(MAX(LEN(C9:C13)),LEN(C9:C13),0),1)
Šī formula darbojas tikai tad, ja datu diapazonā ir tikai viena šūnu kolonna.
Izpētīsim šo formulu, sākot no iekšējiem elementiem virzienā uz āru. LEN funkcija parāda katra vienuma garumu šūnu diapazonā D2:D6. MAX funkcija aprēķina lielāko vērtību šajos vienumos, kas atbilst garākajai teksta virknei, kas atrodas šūnā D3.
Šeit viss kļūst mazliet sarežģītāks. Funkcija MATCH aprēķina tās šūnas nobīdi (relatīvo pozīciju), kurā ir visgarākā teksta virkne. Lai to paveiktu, tai ir nepieciešami trīs argumenti: uzmeklējamā vērtība, uzmeklēšanas masīvs un atbilstības tips. Funkcija MATCH uzmeklēšanas masīvā meklē norādīto uzmeklēšanas vērtību. Šajā gadījumā uzmeklēšanas vērtība ir garākā teksta virkne:
MAX(LEN(C9:C13)
un šī virkne atrodas šajā masīvā:
LEN(C9:C13)
Atbilstības tipa arguments šajā gadījumā ir 0. Atbilstības tipa vērtība var būt 1, 0, vai -1.
-
Atbilstības tipa vērtība 1 parāda lielāko vērtību, kas ir mazāka par vai vienāda ar uzmeklējamo vērtību
-
Atbilstības tipa vērtība 0, MATCH atgriež pirmo vērtību, kas precīzi atbilst uzmeklējamajai vērtībai.
-
Atbilstības tipa vērtība -1 atrod mazāko vērtību, kas ir lielāka vai vienāda ar norādīto uzmeklējamo vērtību.
-
Ja atbilstības tips tiek izlaists, programma Excel pieņem, ka vērtība ir 1.
INDEX funkcija izmanto šos argumentus: masīvs, kolonnas un rindas numurs šajā masīvā. Šūnu diapazons C9:C13nodrošina masīvu, bet MATCH funkcija nodrošina sūnu adreses, un gala arguments (1) norāda, ka vērtība nāk no pirmās masīva kolonnas.
Ja vēlaties iegūt īsākās teksta virknes saturu, jums iepriekš norādītajā piemērā MAX ir jānomaina uz MIN.
-
-
Diapazona n mazāko vērtību atrašana
Šajā piemērā ir parādīts, kā atrast mazākās vērtības šūnu diapazonā, kur parauga datu masīvs šūnās B9:B18 tika izveidots ar: =INT(RANDARRAY(10,1)*100). Ņemiet vērā, ka RANDARRAY ir mainīga funkcija, kas nozīmē, ka katrā Excel aprēķinu veikšanas reizē iegūsiet jaunu nejaušu skaitļu kopu.
Ievadiet =SMALL(B9#,SEQUENCE(D9), =SMALL(B9:B18,{1;2;3})
Šī formula izmanto masīva konstanti, lai novērtētu SMALL funkciju trīs reizes un parādītu 3 mazākos vienumus masīvā, kas atrodas šūnās B9:B18, kur 3 ir mainīgā vērtība šūnā D9. Lai atrastu vairāk vērtību, jūs varat palielināt SEQUENCE funkcijas vērtību vai pievienot konstantei argumentus. Kopā ar šo formulu var izmantot arī tādas papildfunkcijas kā SUM vai AVERAGE. Piemēram:
=SUM(SMALL(B9#,SEQUENCE(D9))
=AVERAGE(SMALL(B9#,SEQUENCE(D9))
-
Diapazona n lielāko vērtību atrašana
Lai diapazonā atrastu lielākās vērtības, funkciju SMALL var aizstāt ar funkciju LARGE. Turklāt nākamajā piemērā tiek izmantotas funkcijas ROW un INDIRECT.
Ievadiet =LARGE(B9#,ROW(INDIRECT("1:3"))) vai =LARGE(B9:B18,ROW(INDIRECT("1:3")))
Šajā brīdī noderētu nelielas zināšanas par funkciju ROW un INDIRECT. Funkciju ROW var izmantot, lai izveidotu secīgu veselu skaitļu masīvu. Piemēram, atlasiet tukšu šūnu un ievadiet:
=ROW(1:10)
Formula izveidots 10 secīgu veselu skaitļu kolonnu. Lai atrastu potenciālo problēmu, ievietojiet rindu virs diapazona, kurā atrodas masīva formula (virs 1. rindas). Excel pielāgos rindu atsauces, bet jaunā formula tagad ģenerēs veselus skaitļus no 2 līdz 11. Lai novērstu šo problēmu, jums ir jāpapildina formula ar funkciju INDIRECT:
=ROW(INDIRECT("1:10"))
Funkcija INDIRECT kā argumentus izmanto teksta virknes (tāpēc diapazons 1:10 ir iekļauts pēdiņās). Ievietojot rindas, programma Excel nekoriģē teksta vērtības un nepārvieto masīva formulu. Tāpēc funkcija ROW vienmēr ģenerē nepieciešamo veselo skaitļu masīvu. Tikpat vienkārši jūs varat izmantot SEQUENCE:
=SEQUENCE(10)
Apskatīsim formulu, ko jūs izmantojāt iepriekš — =LARGE(B9#,ROW(INDIRECT("1:3"))) — sākot no iekšējās iekavas un uz āru: funkcija INDIRECT parāda teksta vērtību kopu, kas šajā gadījumā ir vērtības no 1 līdz 3. ROW funkcija izveido trīs šūnu kolonnas masīvu. LARGE funkcija izmanto B9:B18 šūnu diapazona vērtības un tiek novērtēta trīs reizes — pa vienai reizei katrai ROW funkcijas parādītajai atsaucei. Ja vēlaties uzzināt vairāk vērtību, funkcijai INDIRECT pievienojiet lielāku šūnu diapazonu. Tāpat kā piemēros ar SMALL, šo formulu var izmantot kopā ar citām funkcijām, piemēram, SUM vai AVERAGE.
-
Tāda diapazona summa, kurā ir kļūdu vērtības
Funkcija SUM programmā Excel nedarbojas, ja mēģināt summēt diapazonu, kurā ir kāda kļūdas vērtība, piemēram, #VALUE! vai #N/A. Šajā piemērā redzams, kā summēt vērtības diapazonā Dati, kurā ir kļūdas:
-
=SUM(IF(ISERROR(Dati);"";Dati))
Šī formula izveido jaunu masīvu, kurā ir sākotnējās vērtības, bet nav kļūdas vērtību. Sākot ar iekšējām funkcijām virzienā uz āru: funkcija ISERROR šūnu diapazonā (Dati) meklē kļūdas. Funkcija IF atgriež noteiktu vērtību, ja norādītais nosacījums tiek novērtēts kā PATIESS, bet citu vērtību, ja tas tiek novērtēts kā APLAMS. Šajā gadījumā visām kļūdas vērtībām tiek atgriezta tukša virkne (""), jo to vērtējums ir vērtība PATIESS, kā arī atgriež atlikušās diapazona (Dati) vērtības, jo to novērtējums ir vērtība APLAMS, kas nozīmē to, ka tajās nav kļūdas vērtību. Pēc tam funkcija SUM aprēķina filtrētā masīva kopsummu.
-
Kļūdas vērtību skaita noteikšana diapazonā
Šis piemērs ir līdzīgs iepriekšējai formulai, bet tiek atgriezts kļūdas vērtību skaits diapazonā Dati, nevis notiek tā filtrēšana:
=SUM(IF(ISERROR(Dati);1;0))
Šī formula izveido masīvu, kurā šūnām ar kļūdu ir vērtība 1, bet šūnām bez kļūdas ir vērtība 0. Noņemot funkcijas IF trešo argumentu, šo formulu var vienkāršot un iegūt to pašu rezultātu:
=SUM(IF(ISERROR(Dati);1))
Ja netiek norādīts šis arguments, funkcija IFatgriež APLAMS, ja šūnā nav kļūdas vērtības. Šo formulu var pat vēl vienkāršot:
=SUM(IF(ISERROR(Dati)*1))
Šāda versija darbojas tāpēc, ka PATIESS*1=1 un APLAMS*1=0.
Iespējams, ka vērtības ir jāsummē atbilstoši kādiem nosacījumiem.
Piemēram, šī masīva formula summē tikai pozitīvos veselos skaitļus, kas iekļauti diapazonā Pārdošana, kas atspoguļo šūnas E9:E24 augstāk sniegtajā piemērā:
=SUM(IF(Pārdošana>0,Pārdošana))
Funkcija IF izveido pozitīvo un aplamo vērtību masīvu. Funkcija SUM būtībā ignorē aplamās vērtības, jo 0+0=0. Šajā formulā izmantojamajā šūnu diapazonā var būt jebkāds rindu un kolonnu daudzums.
Varat arī summēt vērtības, kas atbilst vienam vai vairākiem nosacījumiem. Piemēram, šī masīva formula aprēķina vērtības, kas ir lielākas par 0 UN mazākas par 2500:
=SUM((Pārdošana>0)*(Pārdošana<2500)*(Pārdošana))
Atcerieties, ka šī formula atgriež kļūdu, ja diapazonā ir vismaz viena šūna, kurā nav skaitliskas vērtības.
Varat arī veidot masīva formulas, kas izmanto kādu nosacījumu VAI. Piemēram, jūs varat summēt vērtības, kas ir lielākas par 0 VAI mazākas par 2500:
=SUM(IF((Pārdošana>0)+(Pārdošana<2500),Pārdošana))
Masīva formulās funkcijas AND un OR tieši izmantot nevar, jo tās atgriež vienu rezultātu (PATIESS vai APLAMS), bet masīva funkcijai nepieciešams rezultātu masīvs. Šo problēmu var apiet, izmantojot iepriekšējā formulā redzamo loģiku. Citiem vārdiem sakot, ar vērtībām, kas atbilst nosacījumam VAI vai UN, tiek veiktas matemātiskās operācijas: saskaitīšana vai reizināšana.
Šajā piemērā redzams, kā no diapazona izņemt nulles, ja šajā diapazonā ir jāaprēķina vidējā vērtība. Formulā tiek izmantots datu diapazons Pārdošana:
=AVERAGE(IF(Pārdošana<>0;Pārdošana))
Funkcija IF izveido to vērtību masīvu, kas nav 0, un nodod šīs vērtības funkcijai AVERAGE.
Šī masīva formula salīdzina divu šūnu diapazonu (Mani_dati un Jūsu_dati) vērtības un atgriež atrasto atšķirību skaitu. Ja abu diapazonu saturs ir vienāds, formula atgriež 0. Šūnu diapazoniem ir jābūt vienāda lieluma un izmēra, lai izmantotu šo formulu. Piemēram, ja MyData diapazons ir 3 rindas un 5 kolonnas, tad YourData arī ir jābūt 3 rindām un 5 kolonnām.
=SUM(IF(Mani_dati=Jūsu_dati;0;1))
Formula izveido jaunu masīvu ar tādiem pašiem izmēriem, kāds ir salīdzināmajiem diapazoniem. Funkcija IF šo masīvu aizpilda ar vērtībām 0 un 1 (0, ja šūnas neatbilst, bet 1, ja tās ir vienādas). Pēc tam funkcija SUM atgriež masīva vērtību summu.
Šo formulu var vienkāršot šādi:
=SUM(1*(MyData<>YourData))
Līdzīgi formulai, kas diapazonā saskaita kļūdas vērtības, šī formula darbojas tāpēc, ka PATIESS*1=1, bet APLAMS*1=0.
Šī masīva formula atgriež vienas kolonnas diapazona Dati maksimālās vērtības rindas numuru:
=MIN(IF(Dati=MAX(Dati);ROW(Dati);""))
Funkcija IF izveido jaunu masīvu, kas atbilst diapazonam Dati. Ja atbilstošajā šūnā ir diapazona maksimālā vērtība, masīvā tiek norādīts rindas numurs. Pretējā gadījumā masīvā ir tukša virkne (“”). Funkcija MIN izmanto jauno masīvu kā otro argumentu un atgriež mazāko vērtību, kas atbilst diapazona Dati maksimālās vērtības rindas numuram. Ja diapazonā Dati ir identiskas maksimālās vērtības, formula atgriež pirmās vērtības rindu.
Ja vēlaties atgriezt maksimālās vērtības faktisko šūnas adresi, izmantojiet šo formulu:
=ADDRESS(MIN(IF(Dati=MAX(Dati);ROW(Dati);""));COLUMN(Dati))
Līdzīgus piemērus atradīsiet parauga darbgrāmatas darblapā Atšķirības starp datu kopām.
Šajā vingrinājumā parādīts, kā izmantot vairākšūnu un vienšūnas masīva formulas, lai aprēķinātu pārdošanas rādītāju kopu. Pirmajā darbību kopā tiek izmantota vairākšūnu formula, lai aprēķinātu starpsummu kopu. Otrajā kopā tiek izmantota vienšūnas formula, lai aprēķinātu kopsummu.
-
Vairākšūnu masīva formula
Kopējiet tālāk esošo tabulu un ielīmējiet to tukšas darblapas šūnā A1.
|
Pārdevējs |
Automašīnas veids |
Pārdotais daudzums |
Vienības cena |
Pārdots kopā |
|---|---|---|---|---|
|
Balodis |
Sedans |
5 |
33000 |
|
|
Kupeja |
4 |
37000 |
||
|
Goba |
Sedans |
6 |
24000 |
|
|
Kupeja |
8 |
21000 |
||
|
Bērziņš |
Sedans |
3 |
29000 |
|
|
Kupeja |
1 |
31000 |
||
|
Kalns |
Sedans |
9 |
24000 |
|
|
Kupeja |
5 |
37000 |
||
|
Skujiņa |
Sedans |
6 |
33000 |
|
|
Kupeja |
8 |
31000 |
||
|
Formula (gala summa) |
Gala summa |
|||
|
'=SUM(C2:C11*D2:D11) |
=SUM(C2:C11*D2:D11) |
-
Lai redzētu kupeju un sedanu kopējo pārdošanas apjomu katram pārdevējam, atlasiet šūnas E2:E11, ievadiet formulu =C2:C11*D2:D11 un nospiediet taustiņu kombināciju Ctrl+Shift+Enter.
-
Lai redzētu pārdošanas kopsummu, atlasiet šūnu F11, ievadiet formulu =SUM(C2:C11*D2:D11) un nospiediet taustiņu kombināciju Ctrl+Shift+Enter.
Nospiežot taustiņu kombināciju Ctrl+Shift+Enter, Excel ievietos formulu iekavās ({ }) un ievietos vienu formulas kopiju katrā atlasītā diapazona šūnā. Tas notiek ļoti ātri, tāpēc kolonnā E redzat katra pārdevēja katram automašīnu tipam atbilstošo pārdošanas apjomu. Ja atlasīsit E2, pēc tam E3, E4 utt., redzēsit, ka tiek parādīta šī pati formula {=C2:C11*D2:D11}.
-
Vienšūnas masīva formulas izveide
Darbgrāmatas šūnā D13 ierakstiet tālāk minēto formulu un pēc tam nospiediet taustiņu kombināciju Ctrl+Shift+Enter:
=SUM(C2:C11*D2:D11)
Šajā gadījumā programma Excel sareizina masīva vērtības (šūnu diapazons C2–D11) un pēc tam izmanto funkciju SUM, lai saskaitītu šīs summas. Rezultātā tiek iegūta pārdošanas apjoma kopsumma 1 590 000 EUR. Šajā piemērā redzams, cik jaudīga var būt šāda veida formula. Piemēram, iedomājieties, ka jums ir 1000 datu rindu. Daļu vai visus šos datus var sasummēt, izveidojot masīva formulu vienā šūnā, nevis velkot formulu pa 1000 rindām.
Turklāt ievērojiet, ka vienšūnas formula šūnā D13 ir pilnībā neatkarīga no vairākšūnu formulas (formula šūnās E2–E11). Tā ir vēl viena masīva formulu izmantošanas priekšrocība — elastība. Varat mainīt formulas kolonnā E vai pat izdzēst šo kolonnu, neietekmējot formulu šūnā D13.
Masīva formulām ir arī šādas priekšrocības:
-
Konsekvence Noklikšķinot uz jebkuras šūnas, kas atrodas zem E2, redzat to pašu formulu. Šāda konsekvence palīdz nodrošināt lielāku precizitāti.
-
Drošība Vairākšūnu masīva formulas komponentu nevar pārrakstīt. Piemēram, noklikšķiniet uz šūnas E3 un nospiediet taustiņu Dzēst. Jums ir jāatlasa viss šūnu diapazons(E2–E11) un jāmaina visa masīva formula, vai jāatstāj šis masīvs, kā ir. Papildu drošībai jums ir jānospiež taustiņu kombinācija Ctrl+Shift+Enter, lai apstiprinātu jebkādas formulas izmaiņas.
-
Mazāka lieluma faili Vairāku starpformulu vietā bieži vien var izmantot vienu masīva formulu. Piemēram, šajā darbgrāmatā viena masīva formula tiek izmantota, lai aprēķinātu rezultātus kolonnā E. Ja tiktu izmantotas parastās formulas (piemēram, =C2*D2, C3*D3, C4*D4 utt.), to pašu rezultātu iegūšanai būtu jāizmanto 11 atšķirīgas formulas.
Kopumā masīvu formulas izmanto standarta formulu sintaksi. Visas šīs formulas sākas ar vienādības zīmi (=), un masīvu formulās var izmantot gandrīz jebkuru iebūvēto Excel funkciju. Galvenā atšķirība ir tāda, ka darbā ar masīva formulu jums ir jānospiež taustiņu kombinācija Ctrl+Shift+Enter, lai ievadītu formulu. Veicot šo darbību, Excel ieliek masīva formulu iekavās — ja iekavas ierakstīsiet manuāli, jūsu formula tiks pārveidota par teksta virkni un nedarbosies.
Masīva funkcijas var izmantot kā efektīvu risinājumu sarežģītu formulu izveidei. Masīva formula =SUM(C2:C11*D2:D11) ir tāda pati kā šī: =SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).
Svarīgi!: Nospiediet taustiņu kombināciju Ctrl+Shift+Enter, ja vēlaties ievadīt masīva formulu. Šis noteikums attiecas gan uz vienšūnas, gan uz vairākšūnu formulām.
Ikreiz, kad strādājat ar vairākšūnu formulām, ņemiet vērā:
-
Šūnu diapazons, kurā tiks iekļauti rezultāti, ir jāatlasa pirms formulas ievadīšanas. Tas tika paveikts, kad izveidojāt vairākšūnu masīva formulu, atlasot šūnas E2–E11.
-
Nevar mainīt atsevišķas masīva formulas šūnas saturu. Lai mēģinātu to izdarīt, darbgrāmatā atlasiet šūnu E3 un nospiediet taustiņu Delete. Programma Excel parāda ziņojumu, kurā teikts, ka masīva daļu nevar mainīt.
-
Var pārvietot vai izdzēst visu masīva formulu, bet nevar pārvietot vai izdzēst tās daļu. Citiem vārdiem sakot, lai sašaurinātu masīva formulu, vispirms ir jāizdzēš esošā formula un pēc tam izveide ir jāsāk no jauna.
-
Lai dzēstu masīva formulu, atlasiet visu formulas diapazonu (piemēram, E2:E11), un pēc tam nospiediet Dzēst.
-
Vairākšūnu masīva formulā nevar ievietot tukšas šūnas, kā arī nevar izdzēst atsevišķas šūnas.
Dažkārt masīva formula ir jāpaplašina. Atlasiet pirmo šūnu esošajā masīva diapazonā un turpiniet līdz atlasīsit visu diapazonu, kurā vēlaties pagarināt formulu. Nospiediet taustiņu F2, lai rediģētu formulu, pēc tam nospiediet taustiņu kombināciju CTRL+SHIFT+ENTER, lai apstiprinātu formulu pēc formulas diapazona pielāgošanas. Galvenais ir atlasīt visu diapazonu, sākot no masīva augšējās kreisās šūnas. Augšējā kreisā šūna ir tā šūna, kas tiek rediģēta.
Masīva formulas ir lieliskas, bet tām var būt daži trūkumi:
-
Dažkārt varat aizmirst nospiest taustiņu kombināciju Ctrl+Shift+Enter. Tas var gadīties pat vispieredzējušākajiem programmas Excel lietotājiem. Neaizmirstiet to nospiest ikreiz, kad ievadāt vai rediģējat masīva formulu.
-
Citi jūsu darbgrāmatas lietotāji var nesaprast jūsu formulas. Parasti masīva formulas darblapā netiek vispārīgi izskaidrotas. Tāpēc, ja citiem lietotājiem ir jāmodificē jūsu darbgrāmatas, izvairieties no masīva formulu izmantošanas vai gādājiet, lai šie lietotāji būtu informēti par masīva formulām un, ja nepieciešams, zinātu, kā tās mainīt.
-
Atkarībā no datora datu apstrādes ātruma un atmiņas daudzuma apjomīgas masīva formulas var palēnināt aprēķinu veikšanu.
Masīva konstantes ir masīva formulu komponents. Masīva konstantes tiek veidotas, ievadot elementu sarakstu un manuāli to iekļaujot figūriekavās ({ }), piemēram, šādi:
={1\2\3\4\5}
Tagad esam izskaidrojuši, ka veidojot masīva formulas, ir jānospiež taustiņu kombinācija Ctrl+Shift+Enter. Tā kā masīva konstantes ir masīva formulu komponenti, tās figūriekavās tiek iekļautas manuāli (rakstot figūriekavas). Pēc tam jāizmanto taustiņu kombinācija Ctrl+Shift+Enter, lai ievadītu visu formulu.
Atdalot elementus ar komatiem, tiek izveidots horizontāls masīvs (rinda). Atdalot elementus ar semikoliem, tiek izveidots vertikāls masīvs (kolonna). Lai izveidotu divdimensiju masīvu, katras rindas elementi jums ir jānorobežo ar komatiem, bet rindas ir jānorobežo ar semikoliem.
Šeit ir parādīts masīvs vienā rindā: {1\2\3\4}. Šeit ir parādīts masīvs vienā kolonnā: {1;2;3;4}. Šeit ir divu rindu un četru kolonnu masīvs: {1\2\3\4;5\6\7\8}. Divu rindu masīvā pirmā rinda 1, 2, 3 un 4, un otrā rinda ir 5, 6, 7 un 8. Starp 4 un 5 abas rindas atdala viens semikols.
Līdzīgi kā ar masīva formulām masīva konstantes var izmantot kopā ar lielāko daļu programmā Excel iebūvēto funkciju. Nākamajās sadaļās izskaidrots, kā izveidot katra veida konstantes un kā tās izmantot kopā ar programmas Excel funkcijām.
Nākamās darbības ļaus patrenēties, veidojot horizontālu, vertikālu, kā arī divdimensiju konstanti.
Horizontālas konstantes izveide
-
Tukšā darblapā atlasiet šūnas no A1 līdz E1.
-
Formulu joslā ierakstiet šo formulu un nospiediet taustiņu kombināciju Ctrl+Shift+Enter:
={1\2\3\4\5}
Šajā gadījumā jums ir jāuzraksta sākuma un beigu iekavas ({ }), bet Excel pievienos otru kopu jūsu vietā.
Tiek rādīts šāds rezultāts.
Vertikālas konstantes izveide
-
Darbgrāmatā atlasiet piecu šūnu kolonnu.
-
Formulu joslā ierakstiet šo formulu un nospiediet taustiņu kombināciju Ctrl+Shift+Enter:
={1;2;3;4;5}
Tiek rādīts šāds rezultāts.
Divdimensiju konstantes izveide
-
Darbgrāmatā atlasiet šūnu bloku, kas sastāv no četrām kolonnām un trim rindām.
-
Formulu joslā ierakstiet šo formulu un nospiediet taustiņu kombināciju Ctrl+Shift+Enter:
={1\2\3\4;5\6\7\8;9\10\11\12}
Tiek rādīts šāds rezultāts:
Konstanšu lietošana formulās
Tagad aplūkosim vienkāršu piemēru, kurā tiek izmantotas konstantes:
-
Piemēra darbgrāmatā izveidojiet jaunu darblapu.
-
Šūnā A1 ierakstiet 3 un pēc tam ierakstiet 4 šūnā B1, 5 šūnā C1, 6 šūnā D1 un 7 šūnā E1.
-
Šūnā A3 ierakstiet šo formulu un nospiediet taustiņu kombināciju Ctrl+Shift+Enter:
=SUM(A1:E1*{1\2\3\4\5})
Ievērojiet, ka programma Excel šo konstanti iekļauj vēl vienās figūriekavās, jo to ievadījāt kā masīva formulu.
Šūnā A3 tiek parādīta vērtība 85.
Nākamajā sadaļā izskaidrots, kā šī formula darbojas.
Tikko izmantotajā formulā ir vairākas daļas.
1. Funkcija
2. Iekļautais masīvs
3. Operators
4. Masīva konstante
Pēdējais iekavās iekļautais elements ir masīva konstante: {1\2\3\4\5}. Atcerieties, ka programma Excel masīva konstantes neiekļauj figūriekavās; jums tās ir jāieraksta. Atverieties arī to, ka pēc konstantes pievienošanas masīva formulai ir jānospiež taustiņu kombinācija Ctrl+Shift+Enter, lai ievadītu šo formulu.
Tā kā programma Excel vispirms veic darbības ar iekavās iekļautajām izteiksmēm, nākamie divi elementi, kas tiek izmantoti, ir darbgrāmatā saglabātās vērtības (A1:E1) un operators. Šajā brīdī formulā tiek sareizinātas iekļautā masīva vērtības ar atbilstošajām konstantes vērtībām. Tas atbilst šai formulai:
=SUM(A1*1;B1*2;C1*3;D1*4;E1*5)
Visbeidzot funkcija SUM saskaita šīs vērtības, un šūnā A3 tiek rādīta summa 85.
Lai izvairītos no iekļautā masīva izmantošanas un darbība tiktu veikta tikai atmiņa, aizstājiet iekļauto masīvu ar citu masīva konstanti:
=SUM({3\4\5\6\7}*{1\2\3\4\5})
Lai to izmēģinātu, kopējiet šo funkciju, atlasiet tukšu darbgrāmatas šūnu, formulu joslā ielīmējiet formulu un nospiediet taustiņu kombināciju Ctrl+Shift+Enter. Tiek rādīts tas pats rezultāts, kas iepriekšējā vingrinājumā, izmantojot masīva formulu:
=SUM(A1:E1*{1\2\3\4\5})
Masīva konstantēs var iekļaut skaitļus, tekstu, loģiskās vērtības (piemēram, PATIESS un APLAMS), kā arī kļūdu vērtības (piemēram, #N/A). Šos skaitļus var izmantot veselu skaitļu, decimālajā vai zinātniskajā formātā. Ievietojot tekstu, tas ir jāiekļauj pēdiņās (").
Masīva konstantēs nevar iekļaut papildu masīvus, formulas un funkcijas. Citiem vārdiem sakot, tajās var iekļaut tikai tekstu vai skaitļus, kas tiek atdalīti ar komatiem vai semikoliem. Ja ievadāt kādu formulu, piemēram, {1\2\A1:D4} vai {1\2\SUM(Q2:Z8)}, programma Excel parāda brīdinājuma ziņojumu. Turklāt skaitliskās vērtības nedrīkst saturēt procentu zīmes, dolāru zīmes, komatus un iekavas.
Viens no labākajiem veidiem, kā izmantot masīva konstantes, ir piešķirt tām nosaukumus. Nosauktās konstantes ir ērtāk izmantot, kā arī citiem lietotājiem ir vieglāk izprast masīva formulas, kurās tās tiek izmantotas. Lai masīva konstantei piešķirtu nosaukumu un to izmantotu formulā, rīkojieties šādi:
-
Cilnes Formulas grupā Definētie nosaukumi noklikšķiniet uz Definēt nosaukumu. Tiek atvērts dialoglodziņš Definēt nosaukumu.
-
Lodziņā Nosaukums ierakstiet Ceturksnis1.
-
Lodziņā Attiecas uz ievadiet šādu konstanti (atcerieties par manuālu figūriekavu rakstīšanu):
={"Janvāris"\"Februāris"\"Marts"}
Dialoglodziņa saturs tagad izskatās šādi:
-
Noklikšķiniet uz Labi un atlasiet trīs tukšu šūnu rindu.
-
Ierakstiet šo formulu un nospiediet taustiņu kombināciju Ctrl+Shift+Enter.
=Ceturksnis1
Tiek rādīts šāds rezultāts.
Ja kā masīva formulu izmantojat nosauktu konstanti, neaizmirstiet ievadīt vienādības zīmi. Ja to neizdarīsit, programma Excel šo masīvu uztvers kā teksta virkni, un formula nedarbosies tā, kā bijāt cerējis. Visbeidzot, paturiet prātā, ka varat izmantot teksta un skaitļu kombinācijas.
Ja masīva konstantes nedarbojas, izpētiet, vai nav radušās šādas problēmas:
-
Iespējams, ka daži elementi nav atdalīti ar pareizo rakstzīmi. Izlaižot kādu komatu vai semikolu, kā arī ievietojot to nepareizā vietā, iespējams, ka masīva konstante netiek izveidota pareizi vai tiek rādīts brīdinājuma ziņojums.
-
Iespējams, ka esat atlasījis šūnu diapazonu, kas neatbilst konstantes elementu skaitam. Piemēram, ja atlasāt sešu šūnu kolonnu, lai izmantotu ar piecu šūnu konstanti, tukšajā šūnā tiek rādīta kļūdas vērtība #N/A. Bet, ja atlasāt pārāk maz šūnu, programma Excel izlaiž vērtības, kurām nav atbilstošās šūnas.
Nākamajos piemēros parādīti daži veidi, kā masīva formulās var izmantot masīva konstantes. Dažos piemēros tiek izmantota funkcija TRANSPOSE, kas rindas pārvērš par kolonnām un pretēji.
Reizināšana ar katru masīva elementu
-
Izveidojiet jaunu darblapu un atlasiet tukšu šūnu bloku, kas sastāv no četrām kolonnām un trim rindām.
-
Ierakstiet šo formulu un nospiediet taustiņu kombināciju Ctrl+Shift+Enter.
={1\2\3\4;5\6\7\8;9\10\11\12}*2
Masīva elementu kāpināšana
-
Atlasiet tukšu šūnu bloku, kas sastāv no četrām kolonnām un trim rindām.
-
Ierakstiet šo masīva formulu un nospiediet taustiņu kombināciju 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}
Vai ievadiet šādu masīva formulu, kurā tiek izmantots jumtiņa operators (^):
={1\2\3\4;5\6\7\8;9\10\11\12}^2
Viendimensijas rindas apmainīšana vietām
-
Atlasiet piecu tukšu šūnu kolonnu.
-
Ierakstiet šo formulu un nospiediet taustiņu kombināciju Ctrl+Shift+Enter.
=TRANSPOSE({1\2\3\4\5})
Lai arī tika ievadīta horizontāla masīva konstante, funkcija TRANSPOSE to pārvērš par kolonnu.
Viendimensijas kolonnas apmainīšana vietām
-
Atlasiet piecu tukšu šūnu rindu.
-
Ierakstiet šo formulu un nospiediet taustiņu kombināciju Ctrl+Shift+Enter:
=TRANSPOSE({1;2;3;4;5})
Lai arī tika ievadīta vertikāla masīva konstante, funkcija TRANSPOSE to pārvērš par rindu.
Divdimensiju konstantes apmainīšana vietām
-
Atlasiet šūnu bloku, kas sastāv no trim kolonnām un četrām rindām.
-
Ierakstiet šo konstanti un nospiediet taustiņu kombināciju Ctrl+Shift+Enter:
=TRANSPOSE({1\2\3\4;5\6\7\8;9\10\11\12})
Funkcija TRANSPOSE katru rindu pārvērš par kolonnu sēriju.
Šajā sadaļā sniegti vienkāršu masīva formulu piemēri.
Masīvu un masīva konstanšu izveide no esošajām vērtībām
Nākamajā piemērā izskaidrots, kā izmantot masīva formulas, lai izveidotu saites starp šūnu diapazoniem dažādās darblapās. Tajā tiek arī parādīts, kā no šīs pašas vērtību kopas izveidot masīva konstanti.
Masīva izveide no esošajām vērtībām
-
Excel darblapā atlasiet šūnas C8:E10 un ievadiet šo formulu:
={10\20\30;40\50\60;70\80\90}
Pārliecinieties, vai pirms 10 ievadāt { (atverošo iekavu) un pēc 90 } (noslēdzošo iekavu), jo tiek veidots skaitļu masīvs.
-
Nospiediet taustiņu kombināciju Ctrl+Shift+Enter, kas ievada šo skaitļu masīvu šūnu diapazonā C8:E10, izmantojot masīva formulu. Jūsu darblapā šūnām no C8 līdz E10 jāizskatās šādi:
10
20
30
40
50
60
70
80
90
-
Atlasiet šūnu diapazonu C1–E3.
-
Formulu joslā ierakstiet šo formulu un nospiediet taustiņu kombināciju Ctrl+Shift+Enter:
=C8:E10
Šūnās C1 līdz E3 tiks parādīts 3x3 šūnu masīvs ar šūnās C8:E10 iekļautajām vērtībām.
Masīva konstantes izveide no esošajām vērtībām
-
Kad atlasītas šūnas C1:C3, nospiediet taustiņu F2, lai pārslēgtos uz rediģēšanas režīmu.
-
Nospiediet taustiņu F9, lai šūnu atsauces pārvērstu par vērtībām. Programma Excel šīs vērtības pārvērš par masīva konstanti. Tagad formulai ir jābūt ={10\20\30;40\50\60;70\80\90}.
-
Nospiediet taustiņu kombināciju Ctrl+Shift+Enter, lai masīva konstanti ievadītu kā masīva formulu.
Rakstzīmju skaitīšana šūnu diapazonā
Nākamajā piemērā parādīts, kā šūnu diapazonā saskaitīt rakstzīmes (ieskaitot atstarpes).
-
Kopējiet visu tabulu un ielīmējiet darblapas šūnā A1.
Dati
Šis ir
šūnu kopums, kas
ir apvienots,
lai veidotu
vienu teikumu.
Kopējais rakstzīmju skaits šūnās A2:A6
=SUM(LEN(A2:A6))
Garākās šūnas saturs (A3)
=INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)
-
Atlasiet šūnu A8 un nospiediet taustiņu kombināciju Ctrl+Shift+Enter, lai redzētu kopējo rakstzīmju skaitu šūnās A2:A6 (66).
-
Atlasiet šūnu A10 un nospiediet taustiņu kombināciju Ctrl+Shift+Enter, lai redzētu garākās šūnas saturu A2:A6 (šūna A3).
Šī šūnā A8 izmantotā formula saskaita kopējo rakstzīmju skaitu (66) no šūnas A2 līdz A6.
=SUM(LEN(A2:A6))
Šajā gadījumā funkcija LEN atgriež katras diapazona šūnas teksta virknes garumu. Funkcija SUM saskaita šīs vērtības un parāda rezultātu (66).
Diapazona n mazāko vērtību atrašana
Šajā piemērā parādīts, kā šūnu diapazonā atrast trīs mazākās vērtības.
-
Šūnās A1:A11 ievadiet nejaušus skaitļus.
-
Atlasiet šūnas no C1 līdz C3. Šajā šūnu kopā būs masīva formulas atgrieztie rezultāti.
-
Ierakstiet šo formulu un nospiediet taustiņu kombināciju Ctrl+Shift+Enter:
=SMALL(A1:A11,{1;2;3})
Šī formula izmanto masīva konstanti, lai trīs reizes novērtētu funkciju SMALL un atgrieztu vismazāko (1), otro mazāko (2) un trešo mazāko (3) šūnās A1:A10 iekļautā masīva elementu. Lai atrastu papildu vērtības, konstantei ir jāpievieno papildu argumenti. Kopā ar šo formulu var izmantot arī tādas papildfunkcijas kā SUM vai AVERAGE. Piemēram:
=SUM(SMALL(A1:A10,{1\2\3})
=AVERAGE(SMALL(A1:A10,{1\2\3})
Diapazona n lielāko vērtību atrašana
Lai diapazonā atrastu lielākās vērtības, funkciju SMALL var aizstāt ar funkciju LARGE. Turklāt nākamajā piemērā tiek izmantotas funkcijas ROW un INDIRECT.
-
Atlasiet šūnas no D1 līdz D3.
-
Formulu joslā ierakstiet šo formulu un nospiediet taustiņu kombināciju Ctrl+Shift+Enter:
=LARGE(A1:A10,ROW(INDIRECT("1:3")))
Šajā brīdī noderētu nelielas zināšanas par funkciju ROW un INDIRECT. Funkciju ROW var izmantot, lai izveidotu secīgu veselu skaitļu masīvu. Piemēram, atlasiet 10 šūnas garu tukšo kolonnu savā mācību darbgrāmatā, ievadiet šo masīva formulu un nospiediet taustiņu kombināciju Ctrl+Shift+Enter:
=ROW(1:10)
Formula izveidots 10 secīgu veselu skaitļu kolonnu. Lai atrastu potenciālo problēmu, ievietojiet rindu virs diapazona, kurā atrodas masīva formula (virs 1. rindas). Excel pielāgos rindu atsauces, bet jaunā formula ģenerēs veselus skaitļus no 2 līdz 11. Lai novērstu šo problēmu, jums ir jāpapildina formula ar funkciju INDIRECT:
=ROW(INDIRECT("1:10"))
Funkcija INDIRECT kā argumentus izmanto teksta virknes (tāpēc diapazons 1:10 ir iekļauts pēdiņās). Ievietojot rindas, programma Excel nekoriģē teksta vērtības un nepārvieto masīva formulu. Tāpēc funkcija ROW vienmēr ģenerē nepieciešamo veselo skaitļu masīvu.
Apskatīsim formulu, ko izmantojāt iepriekš — =LARGE(A5:A14,ROW(INDIRECT("1:3"))) — sākot no iekšējām iekavām un uz āru: funkcija INDIRECT parāda teksta vērtību kopu, kas šajā gadījumā ir vērtības no 1 līdz 3. ROW funkcija izveido trīs šūnu kolonnas masīvu. LARGE funkcija izmanto A5:A14 šūnu diapazona vērtības un tiek novērtēta trīs reizes — pa vienai reizei katrai ROW funkcijas parādītajai atsaucei. Vērtības 3200, 2700 un 2000 tiek parādītas trīs šūnu kolonnas masīvā. Ja vēlaties uzzināt vairāk vērtību, funkcijai INDIRECT pievienojiet lielāku šūnu diapazonu.
Tāpat kā iepriekšējos piemēros, varat izmantot šo formulu kopā ar citām funkcijām, piemēram, SUM vai AVERAGE.
Garākās teksta virknes atrašana šūnu diapazonā
Atgriezieties pie iepriekšējā teksta virknes parauga, ievadiet tālāk norādīto formulu tukšā šūnā un nospiediet taustiņu kombināciju Ctrl+Shift+Enter:
=INDEX(A2:A6,MATCH(MAX(LEN(A2:A6)),LEN(A2:A6),0),1)
Šūnā A16 tiek rādīts teksts "šūnu kopa, kas".
Izpētīsim šo formulu, sākot no iekšējiem elementiem virzienā uz āru. LEN funkcija parāda katra vienuma garumu šūnu diapazonā A2:A6. MAX funkcija aprēķina lielāko vērtību šajos vienumos, kas atbilst garākajai teksta virknei, kas atrodas šūnā A3.
Šeit viss kļūst mazliet sarežģītāks. Funkcija MATCH aprēķina tās šūnas nobīdi (relatīvo pozīciju), kurā ir visgarākā teksta virkne. Lai to paveiktu, tai ir nepieciešami trīs argumenti: uzmeklējamā vērtība, uzmeklēšanas masīvs un atbilstības tips. Funkcija MATCH uzmeklēšanas masīvā meklē norādīto uzmeklēšanas vērtību. Šajā gadījumā uzmeklēšanas vērtība ir garākā teksta virkne:
(MAX(LEN(A2:A6))
un šī virkne atrodas šajā masīvā:
LEN(A2:A6)
Atbilstības tipa arguments ir 0. Atbilstības tipa vērtība var būt 1, 0, vai -1. Ja tiek norādīta vērtība 1, MATCH atgriež lielāko vērtību, kas ir mazāka vai vienāda ar uzmeklējamo vērtību. Ja tiek norādīta vērtība 0, MATCH atgriež pirmo vērtību, kas precīzi atbilst uzmeklējamajai vērtībai. Ja tiek norādīta vērtība -1, MATCH atrod mazāko vērtību, kas ir lielāka vai vienāda ar norādīto uzmeklējamo vērtību. Ja atbilstības tips tiek izlaists, programma Excel pieņem, ka vērtība ir 1.
INDEX funkcija izmanto šos argumentus: masīvs, kolonnas un rindas numurs šajā masīvā. Šūnu diapazons A2:A6 nodrošina masīvu, bet MATCH funkcija nodrošina sūnu adreses, un gala arguments (1) norāda, ka vērtība nāk no pirmās masīva kolonnas.
Šajā sadaļā sniegti sarežģītu masīva formulu piemēri.
Tāda diapazona summa, kurā ir kļūdu vērtības
Funkcija SUM programmā Excel nedarbojas, ja mēģināt summēt diapazonu, kurā ir kāda kļūdas vērtība, piemēram, #N/A. Šajā piemērā redzams, kā summēt vērtības diapazonā Dati, kurā ir kļūdas.
=SUM(IF(ISERROR(Dati);"";Dati))
Šī formula izveido jaunu masīvu, kurā ir sākotnējās vērtības, bet nav kļūdas vērtību. Sākot ar iekšējām funkcijām virzienā uz āru: funkcija ISERROR šūnu diapazonā (Dati) meklē kļūdas. Funkcija IF atgriež noteiktu vērtību, ja norādītais nosacījums tiek novērtēts kā PATIESS, bet citu vērtību, ja tas tiek novērtēts kā APLAMS. Šajā gadījumā visām kļūdas vērtībām tiek atgriezta tukša virkne (""), jo to vērtējums ir vērtība PATIESS, kā arī atgriež atlikušās diapazona (Dati) vērtības, jo to novērtējums ir vērtība APLAMS, kas nozīmē to, ka tajās nav kļūdas vērtību. Pēc tam funkcija SUM aprēķina filtrētā masīva kopsummu.
Kļūdas vērtību skaita noteikšana diapazonā
Šis piemērs ir līdzīgs iepriekšējai formulai, bet tiek atgriezts kļūdas vērtību skaits diapazonā Dati, nevis notiek tā filtrēšana:
=SUM(IF(ISERROR(Dati);1;0))
Šī formula izveido masīvu, kurā šūnām ar kļūdu ir vērtība 1, bet šūnām bez kļūdas ir vērtība 0. Noņemot funkcijas IF trešo argumentu, šo formulu var vienkāršot un iegūt to pašu rezultātu:
=SUM(IF(ISERROR(Dati);1))
Ja netiek norādīts šis arguments, funkcija IFatgriež APLAMS, ja šūnā nav kļūdas vērtības. Šo formulu var pat vēl vienkāršot:
=SUM(IF(ISERROR(Dati)*1))
Šāda versija darbojas tāpēc, ka PATIESS*1=1 un APLAMS*1=0.
Vērtību summēšana pēc nosacījumiem
Iespējams, ka vērtības ir jāsummē atbilstoši kādiem nosacījumiem. Piemēram, šī masīva formula summē tikai pozitīvos veselos skaitļus, kas iekļauti diapazonā Pārdošana:
=SUM(IF(Pārdošana>0,Pārdošana))
Funkcija IF izveido pozitīvo un aplamo vērtību masīvu. Funkcija SUM būtībā ignorē aplamās vērtības, jo 0+0=0. Šajā formulā izmantojamajā šūnu diapazonā var būt jebkāds rindu un kolonnu daudzums.
Varat arī summēt vērtības, kas atbilst vienam vai vairākiem nosacījumiem. Piemēram, šī masīva formula aprēķina vērtības, kas lielākas par 0 un mazākas vai vienādas ar 5:
=SUM((Pārdošana>0)*(Pārdošana<=5)*(Pārdošana))
Atcerieties, ka šī formula atgriež kļūdu, ja diapazonā ir vismaz viena šūna, kurā nav skaitliskas vērtības.
Varat arī veidot masīva formulas, kas izmanto kādu nosacījumu VAI. Piemēram, varat summēt vērtības, kas ir mazākas par 5 un lielākas par 15:
=SUM(IF((Pārdošana<5)+(Pārdošana>15),Pārdošana))
Funkcija IF atrod visas vērtības, kas ir mazākas par 5 vai lielākas par 15, un nodod tās funkcijai SUM.
Masīva formulās funkcijas AND un OR tieši izmantot nevar, jo tās atgriež vienu rezultātu (PATIESS vai APLAMS), bet masīva funkcijai nepieciešams rezultātu masīvs. Šo problēmu var apiet, izmantojot iepriekšējā formulā redzamo loģiku. Citiem vārdiem sakot, ar vērtībām, kas atbilst nosacījumam VAI vai UN, tiek veiktas matemātiskās operācijas: saskaitīšana vai reizināšana.
Vidējās vērtības aprēķināšana, neņemot vērā nulles
Šajā piemērā redzams, kā no diapazona izņemt nulles, ja šajā diapazonā ir jāaprēķina vidējā vērtība. Formulā tiek izmantots datu diapazons Pārdošana:
=AVERAGE(IF(Pārdošana<>0;Pārdošana))
Funkcija IF izveido to vērtību masīvu, kas nav 0, un nodod šīs vērtības funkcijai AVERAGE.
Atšķirību skaitīšana divos šūnu diapazonos
Šī masīva formula salīdzina divu šūnu diapazonu (Mani_dati un Jūsu_dati) vērtības un atgriež atrasto atšķirību skaitu. Ja abu diapazonu saturs ir vienāds, formula atgriež 0. Lai izmantotu šo formulu, šūnu diapazonu izmēriem un dimensiju skaitam ir jāsakrīt (piemēram, ja Mani_dati ir diapazons ar 3 rindām un 5 kolonnām, arī diapazonā Jūsu_dati ir jābūt 3 rindām un 5 kolonnām):
=SUM(IF(Mani_dati=Jūsu_dati;0;1))
Formula izveido jaunu masīvu ar tādiem pašiem izmēriem, kāds ir salīdzināmajiem diapazoniem. Funkcija IF šo masīvu aizpilda ar vērtībām 0 un 1 (0, ja šūnas neatbilst, bet 1, ja tās ir vienādas). Pēc tam funkcija SUM atgriež masīva vērtību summu.
Šo formulu var vienkāršot šādi:
=SUM(1*(MyData<>YourData))
Līdzīgi formulai, kas diapazonā saskaita kļūdas vērtības, šī formula darbojas tāpēc, ka PATIESS*1=1, bet APLAMS*1=0.
Diapazona maksimālās vērtības atrašanās vietas atrašana
Šī masīva formula atgriež vienas kolonnas diapazona Dati maksimālās vērtības rindas numuru:
=MIN(IF(Dati=MAX(Dati);ROW(Dati);""))
Funkcija IF izveido jaunu masīvu, kas atbilst diapazonam Dati. Ja atbilstošajā šūnā ir diapazona maksimālā vērtība, masīvā tiek norādīts rindas numurs. Pretējā gadījumā masīvā ir tukša virkne (""). Funkcija MIN izmanto jauno masīvu kā otro argumentu un atgriež mazāko vērtību, kas atbilst diapazona Dati maksimālās vērtības rindas numuram. Ja diapazonā Dati ir identiskas maksimālās vērtības, formula atgriež pirmās vērtības rindu.
Ja vēlaties atgriezt maksimālās vērtības faktisko šūnas adresi, izmantojiet šo formulu:
=ADDRESS(MIN(IF(Dati=MAX(Dati);ROW(Dati);""));COLUMN(Dati))
Atsauce
Daļa no šī raksta ir balstīta uz Excel prasmīgo lietotāju slejām, kuru autors ir Kolins Vilkokss (Colin Wilcox), kā arī izmantota informācija no grāmatas Excel 2002 Formulas, kuras autors ir bijušais Excel MVP Džons Volkenbahs (John Walkenbach), 14. un 15. nodaļas.
Vai nepieciešama papildu palīdzība?
Vienmēr varat pajautāt speciālistam Excel tech kopienā vai saņemt atbalstu kopienās.
Skatiet arī
Dinamiskie masīvi un masīvu izplešanās
Dinamiskās masīvu formulas pret mantotajām CSE masīvu formulām
