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

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

Svarīgi! :  Šis raksts ir mašīntulkots, skatiet atrunu. Lūdzu, skatiet šī raksta versiju angļu valodā šeit jūsu informācijai.

Lai kļūtu Excel prasmīgā lietotāja, jums ir jāzina, kā izmantot masīva formulas, kuras var veikt aprēķinus, ko nevar izdarīt, izmantojot-masīva formulu. Šajā rakstā ir pamatojoties uz kolonnas Excel prasmīgā lietotāja raksta Colin Wilcox un pielāgots no 14 un 15 Excel 2002 Formulas, raksta John Walkenbach Excel MVP grāmatu nodaļas sēriju.

Informācija par masīva formulām

Masīva formulas bieži dēvē par CSE (Ctrl+Shift+Enter) formulām, jo, lai pabeigtu formulu, ir jānospiež taustiņu kombinācija Ctrl+Shift+Enter, nevis tikai taustiņš Enter.

Kāpēc izmantot masīva formulas?

Ja programmā Excel esat izmantojis formulas, jūs zināt, ka varat veikt dažas samērā sarežģītas darbības. Piemēram, varat aprēķināt aizņēmuma kopējās izmaksas noteiktā laika periodā. Masīva formulas var izmantot, lai veiktu sarežģītus uzdevumus, piemēram:

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

Īss ievads par masīviem un masīva formulām

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, vērtību kolonnu vai vērtību rindu un kolonnu kombināciju. Masīva formulas var atgriezt vienu vai vairākus rezultātus. Piemēram, kādā šūnu diapazonā var izveidot masīva formulu, un šo masīva formulu var izmantot, lai aprēķinātu kolonnu vai rindu starpsummas. Masīva formulu var ievietot arī vienā šūnā, lai aprēķinātu vienu vērtību. Masīva formulu, kas ietver vairākas šūnas, sauc par vairākšūnu formulu, bet masīva formulu vienā šūnā, sauc par vienšūnas formulu.

Nākamās sadaļas piemēros parādīts, kā izveidot vairākšūnu un vienšūnas masīva formulas.

Izmēģiniet!

Š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

Pārlūkprogrammā ir iegulta darbgrāmata. Kaut gan tajā ir datu piemēri, ņemiet vērā, ka iegultā darbgrāmatā nevar izveidot vai mainīt masīva formulas — tam ir nepieciešama programma Excel. Atbildes varat iegūt iegultajā darbgrāmatā. Tajā ir pieejams arī informatīvs teksts par to, kā darbojas masīva formula. Lai iegūtu pilnīgu informāciju par masīva formulām, aplūkojiet darbgrāmatu programmā Excel.

Vairākšūnu masīva formulas izveide
  1. Kopējiet tālāk esošo tabulu un ielīmējiet to tukšas Excel darblapas šūnā A1.

    Pār
    devējs

    Automašīnas
    tips

    Pārdotais
    daudzums

    Vienības
    cena

    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)

  2. Lai redzētu kupeju un sedanu kopējo pārdošanas apjomu katram pārdevējam, atlasiet E2:E11, ievadiet formulu =C2:C11*D2:D11 un nospiediet taustiņu kombināciju Ctrl+Shift+Enter.

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

Šo darbgrāmatu var lejupielādēt, noklikšķinot uz zaļās Excel pogas melnajā joslā darbgrāmatas apakšējā daļā. Pēc tam varat atvērt failu programmā Excel, atlasīt šūnas, kurās ietvertas masīva formulas, un nospiest taustiņu kombināciju Ctrl+Shift+Enter, lai formula darbotos.

Ja veicat darbu programmā Excel, pārliecinieties, vai Lapa1 ir aktīva, un pēc tam atlasiet šūnas E2:E11. Nospiediet F2 un esošajā šūnā E2 ierakstiet formulu =C2:C11*D2:D11. Ja nospiedīsit taustiņu Enter, redzēsit, ka formula tiek ievadīta tikai šūnā E2 un parāda 165000. Tāpēc, kad formula ir ierakstīta, nospiediet taustiņu kombināciju Ctrl+Shift+Enter, nevis tikai taustiņu Enter. Tagad rezultāti būs redzami šūnās E2:E11. Ņemiet vērā, ka formulu joslā formula tiek parādīta kā {=C2:C11*D2:D11}. Pēc tā varat zināt, ka tā ir masīva formula, kā parādīts tālāk esošajā tabulā.

Ja nospiedīsit taustiņu kombināciju Ctrl+Shift+Enter, programma Excel šo formulu iekļaus figūriekavās ({ }) un ievietos šīs 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ā F10 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ā G11 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ā G11.

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 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 ir tas, ka ir jānospiež taustiņu kombinācija Ctrl+Shift+Enter, lai apstiprinātu formulas izmaiņas.

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

Masīva formulas sintakse

Parasti masīva formulās tiek izmantota standarta formulu sintakse. Tās visas sākas ar vienādības zīmi (=), un masīva formulās var izmantot lielāko daļu iebūvēto Excel funkciju. Galvenā atšķirība ir tā, ka, izmantojot masīva formulu, tās ievadīšanai ir jānospiež taustiņu kombinācija Ctrl+Shift+Enter. To darot, programma Excel masīva formulu iekļauj figūriekavās. Ja rakstīsit tās manuāli, formula tiks pārvērsta par teksta virkni un nedarbosies.

Masīva funkcijas ir tiešām efektīvs veids, kā izveidot sarežģītu formulu. 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).

Masīva formulu ievadīšana un mainīšana

Svarīgi!    Nospiediet taustiņu kombināciju Ctrl+Shift+Enter ikreiz, kad ir jāievada vai jārediģē masīva formula. Š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 (piemēram, =C2:C11*D2:D11), nospiediet taustiņu Delete un pēc tam nospiediet taustiņu kombināciju Ctrl+Shift+Enter.

  • Vairākšūnu masīva formulā nevar ievietot tukšas šūnas, kā arī nevar izdzēst atsevišķas šūnas.

Masīva formulas paplašināšana

Dažkārt masīva formula ir jāpaplašina. Šī darbība nav sarežģīta, bet jāatceras iepriekšējā sadaļā minētie noteikumi.

Šīs darblapas rindās no 12 līdz 17 mēs pievienojām vēl pāris pārdošanas datu rindu. Tagad mēs vēlamies atjaunināt masīva formulas tā, lai tajās tiktu iekļautas šīs papildu rindas.

Veiciet šīs darbības datorprogrammā Excel (pēc darbgrāmatas lejupielādes savā datorā).

Masīva formulas paplašināšana
  1. Kopējiet visu tabulu Excel darblapas šūnā A1.

    Pār
    devējs

    Automašīnas
    tips

    Pārdotais
    daudzums

    Vienības
    cena

    Pārdošanas
    apjoms

    Balodis

    Sedans

    5

    33000

    165000

    Kupeja

    4

    37000

    148000

    Goba

    Sedans

    6

    24000

    144000

    Kupeja

    8

    21000

    168000

    Bērziņš

    Sedans

    3

    29000

    87000

    Kupeja

    1

    31000

    31000

    Kalns

    Sedans

    9

    24000

    216000

    Kupeja

    5

    37000

    185000

    Skujiņa

    Sedans

    6

    33000

    198000

    Kupeja

    8

    31000

    248000

    Siliņš

    Sedans

    2.

    27000

    Kupeja

    3

    30000

    Vanags

    Sedans

    4

    22000

    Kupeja

    1

    41000

    Zvaniņa

    Sedans

    5

    32000

    Kupeja

    3

    36000

    Gala summa

  2. Atlasiet šūnu E18, ievadiet gala summas formulu =SUM(C2:C17*D2:D17) šūnā A20 un nospiediet taustiņu kombināciju Ctrl+Shift+Enter.
    Rezultātam jābūt 2 131 000.

  3. Atlasiet šūnu diapazonu, kurā ir pašreizējā masīva formula (E2:E11), kā arī tukšās šūnas (E12:E17), kas atrodas blakus jaunajiem datiem. Citiem vārdiem sakot, atlasiet šūnas E2:E17.

  4. Nospiediet taustiņu F2, lai pārslēgtos rediģēšanas režīmā.

  5. Formulu joslā mainiet C11 uz C17, mainiet D11 uz D17 un nospiediet taustiņu kombināciju Ctrl+Shift+Enter.
    Programma Excel atjaunina šūnās E2–E11 esošo formulu un jaunajās šūnās E12–E17 ievieto formulas instanci.

  6. Šūnā F17 ierakstiet masīva formulu = SUM(C2:C17*D2*D17), lai tā attiektos uz šūnām rindās 2–17, un pēc tam nospiediet taustiņu kombināciju Ctrl+Shift+Enter, lai ievadītu masīva formulu.
    Jaunajai gala summai vajadzētu būt 2 131 000.

Masīva formulu izmantošanas trūkumi

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.

Uz lapas sākumu

Informācija par masīva konstantēm

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.

Uz lapas sākumu

Viendimensijas un divdimensiju konstanšu izveide

Nākamās darbības ļaus patrenēties, veidojot horizontālu, vertikālu, kā arī divdimensiju konstanti.

Horizontālas konstantes izveide

  1. Izmantojiet iepriekšējo piemēru darbgrāmatu vai izveidojiet jaunu.

  2. Atlasiet šūnas A1–E1.

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

    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.

Masīva konstantes sintakse

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

Elementi, ko var izmantot konstantēs

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.

Masīva konstanšu nosaukumdošana

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:

  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.

Ar masīva konstantēm saistīto problēmu novēršana

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.

Masīva konstanšu darbība

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

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

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

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

Uz lapas sākumu

Vienkāršu masīva formulu izmantošana

Š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ā 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

  1. Kad atlasītas šūnas C1:C3, nospiediet taustiņu F2, lai pārslēgtos uz rediģēšanas režīmu.
    Masīva formulai joprojām jābūt = C8:E10.

  2. 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. Formulai tagad jābūt ={10,20,30;40,50,60;70,80,90}, tāpat kā C8:E10.

  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.

  2. Atlasiet šūnu A9 un nospiediet taustiņu kombināciju Ctrl+Shift+Enter, lai redzētu kopējo rakstzīmju skaitu šūnās A2:A6 (66).

  3. Atlasiet šūnu A12 un nospiediet taustiņu kombināciju Ctrl+Shift+Enter, lai redzētu garākās šūnas saturu A2:A6 (šūna A3).

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)

Šī šūnā A9 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 rezultāts (66) tiek parādīts šūnā A9, kurā atrodas šī formula.

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. Atlasiet šūnas no A16 līdz A18.
    Šajā šūnu kopā būs masīva formulas atgrieztie rezultāti.

  2. Formulu joslā ierakstiet šo formulu un nospiediet taustiņu kombināciju Ctrl+Shift+Enter:

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

Šūnās A16–A18 tiek rādītas vērtības 400, 475 un 500.

Šī 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, kā arī diapazons A12:A14 jāpapildina ar atbilstošu rezultātu šūnu daudzumu. Kopā ar šo formulu var izmantot arī tādas papildfunkcijas kā SUM vai AVERAGE. Piemēram:

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

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

  2. Formulu joslā ierakstiet šo formulu un nospiediet taustiņu kombināciju Ctrl+Shift+Enter:

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

Šūnās A1–A3 tiek rādītas vērtības 3200, 2700 un 2000.

Š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, savā vingrinājumu darbgrāmatā atlasiet tukšu 10 šūnu kolonnu, šūnās A5:A14 ierakstiet š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 saskatītu iespējamo problēmu, ievietojiet rindu virs diapazona, kurā ir šī masīva formula (tas ir virs 1. rindas). Programma Excel koriģē rindu atsauces, un formula ģenerē veselus skaitļus 2–11. Lai novērstu šo problēmu, formulā tiek pievienota funkcija 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.

Izpētīsim iepriekš izmantoto formulu — =LARGE(A5:A14,ROW(INDIRECT("1:3"))) — virzienā no iekšējām iekavām uz āru: funkcija INDIRECT atgriež teksta vērtību kopu, šajā gadījumā vērtības 1–3. Funkcija ROW ģenerē trīs šūnu kolonnu masīvu. Funkcija LARGE izmanto šūnu diapazonā A5:A14 esošās vērtības, kā arī tiek novērtēta trīs reizes (katrai funkcijas ROW atgrieztajai atsaucei). Vērtības 3200, 2700 un 2000 tiek atgrieztas trīs šūnu kolonnu masīvā. Ja vēlaties atrast papildu vērtības, funkcijai INDIRECT pievienojiet lielāku šūnu diapazonu.

Turklāt šo formulu var izmantot kopā ar citām funkcijām, piemēram, SUM vai AVERAGE.

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

Šī formula darbojas tikai tad, ja datu diapazonā ir tikai viena šūnu kolonna. Lapā3, šūnā A16 ierakstiet šo formulu un nospiediet taustiņu kombināciju Ctrl+Shift+Enter:

=INDEX(A6:A9,MATCH(MAX(LEN(A6:A9)),LEN(A6:A9),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. Funkcija LEN atgriež katra šūnu diapazona A6:A9 elementa garumu. Funkcija MAX aprēķina šo elementu lielāko vērtību, kas atbilst garākajai teksta virknei, kas atrodas šūnā A7.

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

un šī virkne atrodas šajā masīvā:

LEN( A6:A9 )

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 paņem šos argumentus: masīvu un šī masīva rindas un kolonnas numuru. Šūnu diapazons A6:A9 nodrošina masīvu, funkcija MATCH nodrošina šūnas adresi, bet pēdējais arguments (1) norāda, ka vērtība nāk no masīva 1. kolonnas.

Uz lapas sākumu

Sarežģītu masīva formulu izmantošana

Š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*( Mani_dati <> Jūsu_dati ))

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

Uz lapas sākumu

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.

Piezīme : Mašīntulkošanas atruna. Šo rakstu ir tulkojusi datorsistēma bez cilvēka iejaukšanās. Microsoft piedāvā šos mašīntulkojumus, lai palīdzētu angliski nerunājošajiem lietotājiem izmanot saturu par Microsoft produktiem, pakalpojumiem un tehnoloģijām. Tā kā šis raksts ir mašīntulkots, tajā var būt leksikas, sintakses un gramatikas kļūdas.

Skatiet arī

Formulu pārskats

Paplašiniet savas 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.

×