Norādījumi par masīva formulām un piemēri

Norādījumi par masīva formulām un piemēri

Piezīme.: Mēs vēlamies sniegt jums visjaunāko palīdzības saturu jūsu valodā, cik vien ātri to varam. Šī lapa ir tulkota automatizēti, un tajā var būt gramatiskas kļūdas un neprecizitātes. Mūsu nolūks ir šo saturu padarīt jums noderīgu. Vai lapas lejasdaļā varat mums pavēstīt, vai informācija jums noderēja? Ērtai atsaucei šeit ir šis raksts angļu valodā.

Masīva formula ir formula, kas var veikt vairākus aprēķinus ar vienu vai vairākiem masīva vienumiem. Varat iedomāties masīvu kā vērtību rindu vai kolonnu vai vērtību rindu un kolonnu kombināciju. Masīva formulas var tikt atjaunotas no vairākiem rezultātiem vai viena rezultāta.

Sākot ar 2018. septembra atjauninājumu sistēmai Office 365, visas formulas, kas var atjaunot vairākus rezultātus, automātiski izspiedīs tās uz leju vai pāri blakus esošajās šūnās. Šīs uzvedības izmaiņas ir arī pievienotas vairākas jaunas dinamiskās masīva funkcijas. Dinamiskās 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 pēc tam tā tiek apstiprināta, nospiežot taustiņu Enter. Agrāk mantotajām masīva formulām vispirms ir jāatzīmē viss izvades diapazons, pēc tam jāapstiprina formula ar taustiņu kombināciju CTRL + SHIFT + ENTER. Tās parasti sauc par PMP formulām.

Masīva formulas var izmantot, lai veiktu sarežģītus uzdevumus, piemēram:

  • Ātri izveidojiet datu kopu paraugus.

  • Saskaitītu šūnu diapazonā esošo rakstzīmju skaitu.

  • Summētu tikai tos skaitļus, kas atbilst noteiktiem nosacījumiem, piemēram, diapazona zemākajām vērtībām, vai skaitļus, kas atrodas starp augšējo un apakšējo robežu.

  • Summētu katru n. vērtību vērtību diapazonā.

Šajos piemēros parādīts, kā izveidot vairāku šūnu un vienšūnas masīva formulas. Ja iespējams, mēs esam iekļāvuši piemērus ar dažām dinamiskajām masīva funkcijām, kā arī esošajām masīva formulām, kas ievadītas gan dinamiskajiem, gan Mantotajiem masīviem.

Lejupielādējiet mūsu piemērus

Lejupielādējiet piemēru darbgrāmatu ar visiem masīva formulas piemēriem šajā rakstā.

Š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

    Vairāku šūnu masīva funkcija šūnā H10 = F10: F19 * G10: G19, lai aprēķinātu to automobiļu skaitu, kas tiek pārdoti pēc vienības cenas

  • Šeit mēs aprēķinām pārdošanas kopsummu un sedanus katram pārdevējam, ievadot = F10: F19 * G10: G19 šūnā H10.

    Nospiežot taustiņu Enter, tiek parādīti rezultāti, kas tiek nobīdīti uz šūnām H10: H19. Ievērojiet, ka izplūduma diapazons ir iezīmēts ar apmali, kad atlasāt jebkuru šūnu, kas atrodas noplūdumā. Iespējams, pamanīsit arī to, ka šūnās H10: H19 ir pelēkotas formulas. Tās ir tikai atsauces, tāpēc, ja vēlaties pielāgot formulu, ir jāatlasa šūna H10, kur tiek rādīta šablona formula.

  • Masīva formula ar vienu šūnu

    Vienas šūnas masīva formula, lai aprēķinātu gala summu ar = SUM (F10: F19 * G10: G19)

    Darbgrāmatas piemēra šūnā H20 ierakstiet vai kopējiet un ielīmējiet = SUM (F10: F19 * G10: G19)un pēc tam nospiediet taustiņu Enter.

    Šajā gadījumā Excel reizina vērtības masīvā (šūnu diapazons ar F10 līdz G19) un pēc tam izmanto funkciju SUM, lai saskaitītu kopsummas kopā. 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. Ņemiet arī vērā, ka vienas šūnas formula šūnā H20 ir pilnīgi neatkarīga no vairāku šūnu formulas (formula, kas ir no H10 līdz H19). Tā ir vēl viena masīva formulu izmantošanas priekšrocība — elastība. Citas kolonnas H formulas var mainīt, neietekmējot formulu H20. Tā var būt arī paraugprakse, lai tiktu rādītas neatkarīgas kopsummas, jo tas palīdz validēt rezultātu precizitāti.

  • Dinamiskās masīva formulas piedāvā arī šādas priekšrocības:

    • Konsekvence.    Ja noklikšķināsit uz kādas no H10 šūnām uz leju, redzēsit vienu un to pašu formulu. Šāda konsekvence palīdz nodrošināt lielāku precizitāti.

    • Drošība.    Vairāku šūnu masīva formulas komponentu nevar pārrakstīt. Piemēram, noklikšķiniet uz šūnas H11 un pēc tam nospiediet taustiņu DELETE. Programma Excel nemainīs masīva izvadi. Lai to mainītu, atlasiet masīvā vai šūnā H10 augšējo kreiso šūnu.

    • Mazāks faila lielums.    Vairāku starpformulu vietā bieži vien var izmantot vienu masīva formulu. Piemēram, automašīnas pārdošanas piemērā tiek izmantota viena masīva formula, lai aprēķinātu rezultātus kolonnā E. Ja esat lietojis standarta formulas, piemēram, = F10 * G10, F11 * G11, F12 * G12 utt., jūs izmantojāt 11 dažādas formulas, lai aprēķinātu tādus pašus rezultātus. Tas vēl nav viss, bet kā rīkoties, ja jums ir tūkstošiem rindu kopā? Pēc tam tas var radīt lielu atšķirību.

    • Efektivitāte.    Masīva funkcijas var būt efektīvs veids, kā izveidot sarežģītas formulas. Masīva formula = SUM (F10: F19 * G10: G19) ir tas pats, kas: = SUM (F10 * G10, F11 * G11, F12 * G12, F13 * G13, F14 * G14, F15 * G15, F16 * G16, F17 * G17, F18 * G18

    • Pārsniedz    Dinamiskās masīva formulas automātiski izplūst izvades diapazonā. Ja avota dati ir Excel tabulā, dinamiskās masīva formulas tiek automātiski mainītas, kad pievienojat vai noņemat datus.

    • #SPILL! kļūdas    Dinamiskie masīvi ieviesa #SPILL! kļūdu, kas norāda, ka kādu iemeslu dēļ ir bloķēts paredzētais sekundārais diapazons. Atrisinājot bloķēšanu, formula tiek automātiski izlieta.

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, vienumus katrā rindā norobežo ar komatiem un nosakiet katru rindu ar semikoliem.

Nākamās darbības ļaus patrenēties, veidojot horizontālu, vertikālu, kā arī divdimensiju konstanti. Lai automātiski ģenerētu masīva konstantes, kā arī manuāli ievadītas masīva konstantes, mēs parādām piemērus, izmantojot funkciju Sequence .

  • Horizontālas konstantes izveide

    Izmantojiet iepriekšējo piemēru darbgrāmatu vai izveidojiet jaunu. Atlasiet jebkuru tukšu šūnu un ievadiet = secību (1,5). Funkcija SEQUENCE izveido 1 rindu ar 5 kolonnu masīvu tādā pašā veidā = {1, 2, 3, 4, 5}. Tiek parādīts šāds rezultāts:

    Horizontālās masīva konstantes ar = SEKVENCE (1,5) vai = {1, 2, 3, 4, 5} izveide

  • Vertikālas konstantes izveide

    Atlasiet jebkuru tukšu šūnu, kas atrodas zem tās, un ievadiet = Sequence (5)vai = {1; 2; 3; 4; 5}. Tiek parādīts šāds rezultāts:

    Izveidojiet vertikālu masīva konstanti ar = SEQUENCE (5) vai = {1; 2; 3; 4; 5}

  • Divdimensiju konstantes izveide

    Atlasiet jebkuru tukšu šūnu ar telpu pa labi un zem tās, un ievadiet = secību (3, 4). Tiek rādīts šāds rezultāts:

    3 rindas izveide par 4 kolonnu masīva konstanti, izmantojot = SEQUENCE (3, 4)

    Varat arī ievadīt: or = {1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12}, taču jūs vēlaties pievērst uzmanību tam, kur ievietojat semikolu un komatus.

    Kā redzat, opcija secība sniedz ievērojamas priekšrocības, salīdzinot ar manuāli ievadot masīva konstantes vērtības. Galvenokārt tas ietaupa jūsu laiku, bet tas var arī palīdzēt samazināt kļūdas no manuālas ievadīšanas. To ir vieglāk lasīt, jo īpaši tad, ja semikols var būt grūti izšķirams no komata atdalītājiem.

Lūk, piemērs, kurā tiek izmantotas masīva konstantes kā daļa no lielākas formulas. Parauga darbgrāmatā atveriet konstanti formulu darblapā vai izveidojiet jaunu darblapu.

Šūnā D9 ievadījāt = Sequence (1,5, 3, 1), bet varat ievadīt arī 3, 4, 5, 6 un 7 šūnās A9: H9. Konkrētajai numuru atlasei nav nekā īpaša, mēs vienkārši izvēlējāmies kaut ko citu, nevis 1-5.

Šūnā E11 ievadiet = SUM (D9: H9 * secību (1,5))vai = SUM (D9: H9 * {1, 2, 3, 4, 5}). Formulas tiek atjaunotas 85.

Izmantojiet masīva konstantes formulās. Šajā piemērā mēs izmantojām = SUM (D9: H (* secība (1,5))

Funkcija SEQUENCE veido masīva konstantes ekvivalentu {1, 2, 3, 4, 5}. Tā kā programma Excel veic darbības izteiksmēs, kas ir iekļautas iekavās, vispirms nākamie divi elementi, kas tiek atskaņoti, ir šūnu vērtības šūnā D9: H9 un reizināšanas 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 (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 vērtības un atgriež 85.

Lai izvairītos no tā, vai tiek izmantots glabātais masīvs un darbība tiktu veikta pilnībā atmiņā, varat to aizstāt ar citu masīva konstanti:

= SUM (1, 5, 3, 1) * secība (1,5))vai = SUM ({3, 4, 5, 6, 7})

Elementi, ko var izmantot masīva konstantes

  • Masīva konstantes var ietvert skaitļus, tekstu un loģiskās vērtības (piemēram, TRUE un FALSE) un kļūdu vērtības, piemēram, #N/A. Varat izmantot skaitļus vesels skaitlis, decimāldaļa, kā arī zinātniskā formātā. Ja iekļaujat tekstu, tas ir jāietver 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 nosaukt tos. 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 Ceturksnis1. 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:

Nosauktas masīva konstantes pievienošana no formulām > definēti nosaukumi > name Manager > New

Noklikšķiniet uz Labiun pēc tam atlasiet jebkuru rindu ar trim tukšām šūnām un ievadiet = Ceturksnis1.

Tiek parādīts šāds rezultāts:

Formulā izmantojiet nosaukumu masīva konstante, piemēram, = Ceturksnis1, kur Ceturksnis1 ir definēta kā = {"janvāris", "februāris", "marts"}

Ja vēlaties, lai rezultāti tiktu izplūdumi vertikāli, nevis horizontāli, varat izmantot =TRANSPOSE(Ceturksnis1).

Ja vēlaties parādīt sarakstu ar 12 mēnešiem, piemēram, varat izmantot, veidojot finanšu pārskatu, jūs varat veidot vienu no pašreizējā gada ar secības funkciju. Šī funkcija ir ļoti vienkārša, jo, lai gan tiek rādīts tikai mēnesis, ir spēkā esošs datums, ko var izmantot citos aprēķinos. Šie piemēri ir atrodami masīva konstantes un ātro paraugu datu kopu darblapās, kas ir darbgrāmatā, piemēram,.

= TEXT (DATE (YEAR (TODAY ()), SEQUENCE (1, 12), 1), "MMM")

Izmantojiet teksta, datuma, gada, TODAY un secības funkciju kombināciju, lai izveidotu dinamisku 12 mēnešu sarakstu

Tiek izmantota Funkcija DATE , lai izveidotu datumu, kura pamatā ir pašreizējais gads, secība izveido masīva konstanti no 1 līdz 12 janvārim no janvāra līdz decembrim, pēc tam funkcija TEXT konvertē parādīšanas formātu uz "MMM" (Jan, Feb, Mar utt.). Ja vēlaties rādīt pilnu mēneša nosaukumu, piemēram, janvāris, jūs izmantosit "MMMM".

Ja izmantojat nosauktu konstanti kā masīva formulu, atcerieties ievadīt vienādības zīmi kā = Ceturksnis1, ne tikai Ceturksnis1. Ja to neizdarīsit, programma Excel šo masīvu uztvers kā teksta virkni, un formula nedarbosies tā, kā bijāt cerējis. Visbeidzot, atcerieties, ka varat izmantot funkciju, teksta un skaitļu kombinācijas. Tas viss ir atkarīgs no tā, cik radoši vēlaties saņemt.

Nākamajos piemēros parādīti daži veidi, kā masīva formulās var izmantot masīva konstantes. Dažos piemēros ir izmantota funkcija TRANSPOSE , lai konvertētu rindas uz kolonnām un otrādi.

  • Vairāku vienumu sakārtojums

    ENTER = Sequence (1; 12) * 2vai = {1, 2, 3, 4, 5, 6, 7, 8; 9, 10, 11, 12} * 2

    Varat arī dalīt ar (/), pievienot ar (+) un atņemt ar (-).

  • Masīva elementu kāpināšana

    ENTER = Sequence (1; 12) ^ 2vai = {1, 2, 3, 4, 5, 6, 7, 8; 9, 10, 11, 12} ^ 2

  • Kvadrātā esošo vienumu kvadrātsaknes atrašana masīvā

    ENTER =SQRT(secība (1, 12) ^ 2)vai = SQRT ({1, 2, 3, 4; 5, 6, 7, 8; 9, 10, 11, 12} ^ 2)

  • Viendimensijas rindas apmainīšana vietām

    ENTER = apmainīt ((1,5))vai = apmainīt vietām ({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

    ENTER = apmainīt (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

    ENTER = apmainīt (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

    Šajā piemērā izskaidrots, kā izmantot masīva formulas, lai izveidotu jaunu masīvu no esoša masīva.

    ENTER = sekvence (3, 6, 10, 10)vai = {10, 20, 30, 40, 50, 60; 70; 80; 90100110120; 130140150160170180}

    Pārliecinieties, vai pirms 10 ievadāt {(atverošās figūriekavas), un} (aizverošās figūriekavas) pēc 180 ievadīšanas, jo tiek veidots skaitļu masīvs.

    Pēc tam ievadiet = D9 #vai = D9: i daļa11 tukšā šūnā. Tiek parādīts 3 x 6 šūnu masīvs ar tām pašām vērtībām, kas redzamas šūnā D9: D11. # Zīmi sauc par izplūdusā diapazona operatoru, un tas ir Excel's veids, kā izveidot atsauci uz visu masīva diapazonu, nevis to rakstīt.

    Izgāztā diapazona operatora (#) izmantošana, lai nosauktu esošu masīvu

  • Masīva konstantes izveide no esošajām vērtībām

    Izlietai paredzētā masīva formulas rezultātus varat izmantot, lai tos konvertētu tās komponentu daļās. Atlasiet šūnu D9 un pēc tam nospiediet taustiņu F2 , lai pārslēgtos uz rediģēšanas režīmu. Pēc tam nospiediet taustiņu F9 , lai konvertētu šūnu atsauces uz vērtībām, kuras programma Excel pārvērš par masīva konstanti. Nospiežot taustiņu Enter, formula = D9 #, tagad ir = {10, 20, 30, 40, 50, 60; 70; 80; 90}.

  • Rakstzīmju skaitīšana šūnu diapazonā

    Tālāk sniegtajā piemērā ir parādīts, kā saskaitīt rakstzīmju skaitu šūnu diapazonā. Tajā ir iekļautas atstarpes.

    Kopējais rakstzīmju skaits diapazonā un citi masīvi darbam ar teksta virknēm

    = SUM (LEN (C9: C13))

    Šajā gadījumā Funkcija LEN atgriež katras diapazona šūnas garumu katrā teksta virknē. Funkcija SUM saskaita kopā šīs vērtības un parāda rezultātu (66). Ja vēlaties iegūt vidējo rakstzīmju skaitu, varat izmantot:

    = AVERAGE (LEN (C9: C13))

  • Garākās šūnas saturs diapazonā C9: C13

    = 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. Funkcija LEN atgriež katra vienuma garumu no šūnu diapazona D2: D6. Funkcija MAX aprēķina lielāko vērtību starp šiem vienumiem, kas atbilst garākajai teksta virknei, kas ir šū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 garā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)

    Argumenta atbilstības tips šajā gadījumā ir 0. Atbilstības tips var būt vērtība 1, 0 vai-1.

    • 1. Atgriež lielāko vērtību, kas ir mazāka vai vienāda ar uzmeklēšanas val

    • 0 — atgriež pirmo vērtību, kas ir tieši vienāda ar uzmeklējamo vērtību

    • -1 — atgriež 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.

    Visbeidzot funkcija INDEX veic šādus argumentus: masīvs un rindas un kolonnas numurs šajā masīvā. Šūnu diapazons C9: C13 nodrošina masīvu, funkcija MATCH nodrošina šūnas adresi, un beigu arguments (1) norāda, ka vērtība ir ņemta no masīva pirmās kolonnas.

    Ja vēlaties iegūt mazākās teksta virknes saturu, iepriekš norādītajā piemērā ir jāaizvieto ar min.

  • Diapazona n mazāko vērtību atrašana

    Šajā piemērā ir parādīts, kā atrast trīs mazākās vērtības šūnu diapazonā, kur šūnu parauga datu masīvs B9: B18has ir izveidots ar: = Int (RANDARRAY(10,1) * 100). Ņemiet vērā, ka RANDARRAY ir svārstīga funkcija, līdz ar to ikreiz, kad tiek aprēķināta programma Excel, tiks parādīts jauns gadījumskaitļu kopums.

    Excel masīva formula, lai atrastu n-to mazāko vērtību: = SMALL (B9 #, SEQUENCE (D9))

    ENTER = Small (B9 #, Sequence (D9), = Small (B9: B18, {1; 2; 3})

    Šajā formulā izmantota masīva konstante, lai novērtētu mazo funkciju trīs reizes un atgrieztu mazākos 3 dalībniekus masīvā, kas atrodas šūnās B9: B18, kur 3 ir mainīga vērtība šūnā D9. Lai atrastu citas vērtības, varat palielināt funkcijas SEQUENCE vērtību vai pievienot vairāk argumentu konstantei. 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 atrastu lielāko vērtību diapazonā, varat aizstāt mazo funkciju ar lielu funkciju. Turklāt nākamajā piemērā tiek izmantotas funkcijas ROW un INDIRECT.

    ENTER = Large (B9 #, ROW (netiešā ("1:3")) vai = Large (B9: B18, rinda (netiešā ("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 un ievadiet:

    =ROW(1:10)

    Formula izveido 10 secīgu veselu skaitļu kolonnu. Lai skatītu iespējamu problēmu, ievietojiet rindu virs diapazona, kurā ir masīva formula (tas ir, virs 1. rindas). Programma Excel pielāgo rindu atsauces, un formula tagad izveido veselus skaitļus no 2 līdz 11. Lai novērstu šo problēmu, formulai ir jāpievieno netieša funkcija:

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

    Netiešās funkcijās tiek izmantotas teksta virknes kā argumenti (tāpēc diapazonam 1:10 ir 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. Varat vienkārši izmantot secību:

    = INSERT (10)

    Aplūkosim iepriekš lietoto formulu — = LARGE (B9 #, ROW (netiešs ("1:3")))) — sākot no iekšējām iekavām un strādājot uz āru: funkcija netiešā atgriež teksta vērtību kopu, šajā gadījumā vērtības no 1 līdz 3. Funkcija ROW savukārt ģenerē trīs šūnu kolonnas masīvu. Funkcija LARGE izmanto vērtības šūnu diapazonā B9: B18, un tā tiek novērtēta trīs reizes, vienreiz katrai atsaucei, ko atgriež funkcija ROW. Ja vēlaties atrast vairāk vērtību, pievienojiet lielāku šūnu diapazonu netiešajai funkcijai. Visbeidzot, tāpat kā ar mazajiem piemēriem, varat izmantot šo formulu kopā ar citām funkcijām, piemēram, SUM un 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ļūdas vērtība, piemēram, #VALUE! vai #N/A. Šajā piemērā ir parādīts, kā summēt vērtības diapazonā ar nosaukumu dati, kuros ir kļūdas:

    Izmantojiet masīvus, lai risinātu kļūdas. Piemēram, = SUM (IF (ISERROR (dati), "", dati) saskaita diapazonu ar nosauktajiem datiem pat tad, ja tajā ir kļūdas, piemēram, #VALUE! vai #NA!.

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

Masīvus var izmantot, lai aprēķinātu atkarībā no noteiktiem nosacījumiem. = SUM (IF (Sales>0, Sales)) summēs visas vērtības, kas lielākas par 0 diapazonā no pārdošanas apjoma.

Piemēram, šī masīva formula saskaita tikai pozitīvos veselos skaitļus diapazonā Sales, kas attēlo šūnu E9: E24 iepriekšminētajā piemērā:

=SUM(IF(Pārdošana>0,Pārdošana))

Funkcija IF izveido pozitīvu un aplamu 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, šajā masīva formulā tiek aprēķinātas vērtības, kas lielākas par 0 un mazākas par 2500:

= SUM ((Sales>0) * (Sales<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, varat summēt vērtības, kas ir lielākas par 0 vai mazākas par 2500:

= SUM (IF ((Sales>0) + (Sales<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, jūs veicat matemātiskas darbības, piemēram, saskaitīšanu vai reizināšanu ar vērtībām, kas atbilst nosacījumam OR vai AND.

Š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. Lai izmantotu šo formulu, šūnu diapazoniem ir jābūt vienāda lieluma un ar vienu un to pašu dimensiju. Piemēram, ja MyData ir 3 rindu diapazons ar 5 kolonnām, YourData jābūt arī 3 rindām par 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 varat atrast darbgrāmatas parauga darblapā par atšķirībām 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 visu tabulu un ielīmējiet to tukšā darblapā šūnā a1.

Pārdošanas apjoms Persona

Automašīnas Ierakstiet

Skaitlis Pārdots

Vienība Price (cena )

Kopsummu Pārdošanas apjoms

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)

  1. Lai skatītu atvadu un sedanu kopējo pārdošanas apjomu katram pārdevējam, atlasiet šūnas E2: E11, ievadiet formulu = C2: C11 * D2: D11un nospiediet taustiņu kombināciju CTRL + SHIFT + ENTER.

  2. 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, programma Excel aptver formulu ar figūriekavām ({}) un ievieto formulas instanci 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}

Masīva formula aprēķina kopsummu kolonnā E

  • Vienšūnas masīva formulas izveide

Darbgrāmatas šūnā D13, ierakstiet šo formulu un nospiediet taustiņu kombināciju CTRL + SHIFT + ENTER:

=SUM(C2:C11*D2:D11)

Šajā gadījumā Excel reizina vērtības masīvā (šūnu diapazons no C2 līdz D11) un pēc tam izmanto funkciju SUM, lai saskaitītu kopsummas kopā. 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.

Ņemiet arī vērā, ka vienas šūnas formula šūnā D13 ir pilnīgi neatkarīga no vairāku šūnu formulas (formula šūnās E2 līdz E11). Tā ir vēl viena masīva formulu izmantošanas priekšrocība — elastība. Varat mainīt formulas kolonnā E vai arī izdzēst šo kolonnu, neietekmējot formulu 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 pēc tam nospiediet taustiņu Delete. 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. Kā papildu drošības līdzeklis nospiediet taustiņu kombināciju CTRL + SHIFT + ENTER , lai apstiprinātu izmaiņas formulā.

  • Mazāks faila lielums.    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.

Vispārīgi masīva formulās tiek izmantota standarta formulas sintakse. Tās visas sākas ar vienādības zīmi (=), un masīva formulās varat izmantot lielāko daļu iebūvēto Excel funkciju. Galvenā atšķirība ir tā, ka, izmantojot masīva formulu, nospiediet taustiņu kombināciju CTRL + SHIFT + ENTER , lai ievadītu savu formulu. Veicot šo darbību, programma Excel iekļauj masīva formulu ar figūriekavām, ja figūriekavas tiek ievadītas manuāli, tā tiek konvertēta par teksta virkni, un tā nedarbojas.

Masīva funkcijas var būt efektīvs veids, kā izveidot sarežģītas formulas. 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 , kad nepieciešams 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 izdzēstu masīva formulu, atlasiet visu formulu diapazonu (piemēram, E2: E11), pēc tam nospiediet taustiņu Delete.

  • No vairāku šūnu masīva formulas nevar ievietot tukšas šūnas vai izdzēst šūnas.

Dažkārt masīva formula ir jāpaplašina. Atlasiet pirmo šūnu esošā masīva diapazonā un turpiniet, līdz esat atlasījis visu diapazonu, kurā vēlaties paplašinā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 koriģēšanas. Atslēga ir atlasīt visu diapazonu, sākot ar masīva augšējo kreiso šūnu. Augšējā kreisajā šūnā ir tā, kas tiek rediģēta.

Masīva formulas ir lieliskas, bet tām var būt daži trūkumi:

  • Dažreiz var 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 citām personām ir jāmodificē jūsu darbgrāmatas, jums vajadzētu izvairīties no masīva formulām vai nodrošināt, lai šīs personas zinātu par masīva formulām un saprastu, kā tās mainīt, ja nepieciešams.

  • 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 jūs zināt, 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 izmantojiet taustiņu kombināciju 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 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 ir 1, 2, 3 un 4, bet 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

  1. Tukšā darblapā atlasiet šūnas no a1 līdz E1.

  2. Formulu joslā ierakstiet šo formulu un nospiediet taustiņu kombināciju CTRL + SHIFT + ENTER:

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

    Šajā gadījumā ir jāraksta atverošās un aizverošās figūriekavas ({}), un Excel pievienos otro kopu jūsu vietā.

    Tiek rādīts šāds rezultāts.

    Horizontāla masīva konstante formulā

Vertikālas konstantes izveide

  1. Darbgrāmatā atlasiet piecu šūnu kolonnu.

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

    Vertikāla masīva konstante masīva formulā

Divdimensiju konstantes izveide

  1. Darbgrāmatā atlasiet šūnu bloku, kas sastāv no četrām kolonnām un trim rindām.

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

    Divdimensiju masīva konstante masīva formulā

Konstanšu lietošana formulās

Tagad aplūkosim vienkāršu piemēru, kurā tiek izmantotas konstantes:

  1. Piemēra darbgrāmatā izveidojiet jaunu darblapu.

  2. Šūnā A1 ierakstiet 3 un pēc tam ierakstiet 4 šūnā B1, 5 šūnā C1, 6 šūnā D1 un 7 šūnā E1.

  3. Šū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.

    Masīva formula ar masīva konstanti

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

Masīva formulas ar masīva konstanti sintakse

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. Ņemiet arī vērā, ka pēc tam, kad pievienojat konstanti masīva formulu, nospiediet taustiņu kombināciju CTRL + SHIFT + ENTER , lai ievadītu 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 funkciju, atlasiet tukšu šūnu savā darbgrāmatā, ielīmējiet šo formulu formulu joslā 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 nosaukt tos. 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:

  1. Cilnes Formulas grupā Definētie nosaukumi noklikšķiniet uz Definēt nosaukumu.
    Tiek atvērts dialoglodziņš nosaukuma definēšana .

  2. Lodziņā Nosaukums ierakstiet Ceturksnis1.

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

    Dialoglodziņš Nosaukuma rediģēšana ar formulu

  4. Noklikšķiniet uz Labi un atlasiet trīs tukšu šūnu rindu.

  5. Ierakstiet šo formulu un nospiediet taustiņu kombināciju CTRL + SHIFT + ENTER.

    =Ceturksnis1

    Tiek rādīts šāds rezultāts.

    Formulas veidā ievadīts nosaukts masīvs

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. Ja izlaižat komatu vai semikolu vai ievietojat to nepareizā vietā, masīva konstante, iespējams, nav izveidota pareizi, vai arī var tikt parā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 ir izmantota funkcija TRANSPOSE , lai konvertētu rindas uz kolonnām un otrādi.

Reizināšana ar katru masīva elementu

  1. Izveidojiet jaunu darblapu un atlasiet tukšu šūnu bloku, kas sastāv no četrām kolonnām un trim rindām.

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

  1. Atlasiet tukšu šūnu bloku, kas sastāv no četrām kolonnām un trim rindām.

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

  1. Atlasiet piecu tukšu šūnu kolonnu.

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

  1. Atlasiet piecu tukšu šūnu rindu.

  2. Ievadiet š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

  1. Atlasiet šūnu bloku, kas sastāv no trim kolonnām un četrām rindām.

  2. Ievadiet š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

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

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

  3. Atlasiet šūnu diapazonu C1–E3.

  4. Formulu joslā ievadiet šo formulu un nospiediet taustiņu kombināciju CTRL + SHIFT + ENTER:

    =C8:E10

    3x3 šūnu masīvs tiek rādīts šūnās C1 – E3 ar tām pašām vērtībām, kas tiek rādītas C8 līdz E10.

Masīva konstantes izveide no esošajām vērtībām

  1. Ja ir atlasīta opcija šūnas C1: C3, nospiediet taustiņu F2 , lai pārslēgtos uz rediģēšanas režīmu. 

  2. Nospiediet taustiņu F9 , lai konvertētu šūnu atsauces uz vērtībām. Programma Excel šīs vērtības pārvērš par masīva konstanti. Formulai tagad jābūt = {10, 20, 30; 40; 50; 60; 70; 80; 90}.

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

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

  2. Atlasiet šūnu A8 un pēc tam nospiediet taustiņu kombināciju CTRL + SHIFT + ENTER , lai redzētu kopējo rakstzīmju skaitu šūnās A2: A6 (66).

  3. 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 tiek izmantota šāda formula: Kopējais rakstzīmju skaits (66), kas atrodas šūnās A2 – A6.

=SUM(LEN(A2:A6))

Šajā gadījumā funkcija LEN atgriež katras diapazona šūnas teksta virknes garumu. Funkcija Sum saskaita kopā šī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.

  1. Ievadiet dažus gadījumskaitļu skaitļus šūnās A1: A11.

  2. Atlasiet šūnas C1 līdz C3. Šajā šūnu kopā būs masīva formulas atgrieztie rezultāti.

  3. Ievadiet šo formulu un nospiediet taustiņu kombināciju CTRL + SHIFT + ENTER:

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

Šajā formulā izmantota masīva konstante, lai novērtētu mazo funkciju trīs reizes un atgrieztu mazāko (1), otro mazāko (2) un trešo mazāko (3) dalībnieku masīvā, kas atrodas šūnās A1: A10, lai atrastu citas vērtības, ir jāpievieno vairāk argumentu konstante. 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.

  1. Atlasiet šūnas D1 līdz D3.

  2. Formulu joslā ievadiet šo formulu un nospiediet taustiņu kombināciju CTRL + SHIFT + ENTER:

    = LARGE (A1: A10, RINDA (NETIEŠA ("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, praksē darbgrāmatā atlasiet tukšu kolonnu ar 10 šūnām, ievadiet šo masīva formulu un nospiediet taustiņu kombināciju CTRL + SHIFT + ENTER:

=ROW(1:10)

Formula izveido 10 secīgu veselu skaitļu kolonnu. Lai skatītu iespējamu problēmu, ievietojiet rindu virs diapazona, kurā ir masīva formula (tas ir, virs 1. rindas). Programma Excel pielāgo rindu atsauces, un formula izveido veselus skaitļus no 2 līdz 11. Lai novērstu šo problēmu, formulai ir jāpievieno netieša funkcija:

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

Aplūkosim iepriekš lietoto formulu — = Large (A5: A14, ROW (netiešā ("1:3"))) — sākot no iekšējām iekavām un strādājot ārpus tās: funkcija INDIRECT atgriež teksta vērtību kopu , šajā gadījumā vērtības no 1 līdz 3. Funkcija Row savukārt ģenerē trīs šūnu kolonnveida masīvu. Funkcija Large izmanto vērtības šūnu diapazonā A5: A14, un tā tiek novērtēta trīs reizes, vienu reizi katrai atsaucei, ko atgriež funkcija Row . Vērtības 3200, 2700 un 2000 tiek atgrieztas trīs šūnu kolonnveida masīvā. Ja vēlaties atrast vairāk vērtību, pievienojiet lielāku šūnu diapazonu netiešajai funkcijai.

Tāpat kā iepriekšējos piemēros šo formulu var izmantot kopā ar citām funkcijām, piemēram, Sum un Average.

Garākās teksta virknes atrašana šūnu diapazonā

Pāriešana atpakaļ uz iepriekšējo teksta virkni piemēram, tukšā šūnā ierakstiet šo formulu un nospiediet taustiņu kombināciju CTRL + SHIFT + ENTER:

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

Tiek parādīts teksts "šūnu ķekars, kas".

Izpētīsim šo formulu, sākot no iekšējiem elementiem virzienā uz āru. Funkcija LEN atgriež visu šūnu diapazonā A2: A6 esošo vienumu garumu. Funkcija Max aprēķina lielāko vērtību starp šiem vienumiem, kas atbilst garākajai teksta virknei, kas ir šū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.

Visbeidzot funkcija index veic šādus argumentus: masīvs un rindas un kolonnas numurs šajā masīvā. Šūnu diapazons a2: A6 nodrošina masīvu, funkcija Match nodrošina šūnas adresi, un beigu arguments (1) norāda, ka vērtība ir ņemta no masīva pirmās 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))

Apstiprinājumu

Šī raksta daļas ir balstītas uz Excel Power User kolonnu sērijas, kuras raksta Kolins Wilcox, un pielāgotas no Excel 2002 formulu nodaļas 14 un 15.

Vai nepieciešama papildu palīdzība?

Vienmēr varat pajautāt speciālistiem Excel Tech kopienā, saņemt atbalstu Answers kopienā vai ieteikt jaunu līdzekli vai uzlabojumu vietnē Excel User Voice.

Skatiet arī

Dinamiskie masīvi un masīvu izplešanās

Dinamiskās masīva formulas vs. Legacy PMP masīva formulas

Funkcija FILTER

Funkcija RANDARRAY

Funkcija SEQUENCE

Funkcija SINGLE

Funkcija SORT

Funkcija SORTBY

Funkcija UNIQUE

Kļūdas #SPILL! programmā Excel

Formulu pārskats

Paplašiniet savas Office prasmes
Iepazīties ar apmācību
Esiet pirmais, kas saņem jaunās iespējas
Pievienoties Office Insider programmai

Vai šī informācija bija noderīga?

Paldies par jūsu atsauksmēm!

Paldies par atsauksmēm! Šķiet, ka varētu būt noderīgi sazināties ar kādu no mūsu Office atbalsta aģentiem.

×