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ākas masīva elementu. Varat uzskatīt par masīvu kā rindu vai kolonnu vērtības vai kombinācija rindas un kolonnas vērtību. Masīva formulas var atgriezt vairākus rezultātus vai vienu rezultātu.

Sākot ar Office 365septembrim 2018 atjauninājums, formula, kas var atgriezt vairākus rezultātus automātiski plūst tos uz leju vai pāri uz blakus esošas šūnas. Šīs izmaiņas izturēšanās ir pievienota arī vairākas jaunas dinamiskās masīvu funkcijas. Dinamiskās masīva formulas, vai viņi izmanto esošās funkcijas vai dinamiskās masīvu funkcijas, nepieciešams tikai ievades vienā šūnā, tad apstiprināts, nospiežot taustiņu Enter. Iepriekš, paaudzes masīva formulas, nepieciešams vispirms atlasot visu izvades diapazonā un pēc tam apstiprinot formula ar Taustiņu kombināciju Ctrl + Shift + Enter. Tās esat dēvē par CSE formulām.

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

  • Ātri izveidot parauga datu kopas.

  • Lai saskaitītu rakstzīmes, kas satur šūnu diapazonā.

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

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

Tālāk sniegtajos piemēros parādīts, kā izveidot Vairākšūnu un vienšūnas masīva formulas. Ja iespējams, ir iekļauta piemēri ar dažiem dinamiskās masīvu funkcijas, kā arī esošās masīvu formulas, kas ievadīta kā dinamisku un paaudzes masīvu.

Lejupielādējiet mūsu piemērus

Lejupielādējiet piemēru darbgrāmatu ar visiem masīva formulu piemēri š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ākšūnu masīva funkcija šūnā H10 = F10:F19 * G10:G19 aprēķināt automašīnu skaitli pārdotās vienības cena

  • Šeit mēs esam aprēķinot kupeju un sedanu kopējo pārdošanas apjomu katram pārdevējam, ievadot = F19:F19 * G10:G19 šūnā H10.

    Kad tiek nospiests taustiņš Enter, redzēsit plūst uz leju līdz šūnas H10:H19 rezultātus. Ņemiet vērā, ka izplūde diapazons ir iezīmēta ar pievienotu apmali atlasot jebkuru šūnu diapazonā izplūde. Varat arī ievērot formulās šūnas H10:H19 ir pelēkotas. Tie ir tikai tur Reference, tāpēc, ja vēlaties pielāgot formulu, būs nepieciešams, lai atlasītu šūnu H10, kur atrodas šablona formulu.

  • Vienšūnas masīva formulas

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

    H20 piemērs darbgrāmatas šūnā 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 diapazonu F10 – G19) un pēc tam izmanto funkciju SUM, lai pievienotu kopsummas kopā. Rezultāts ir $1,590,000 pārdošanas kopsummu.

    Šajā piemērā parādīts, kā jaudīga var būt šāda veida formula. Piemēram, pieņemsim, ka jums ir 1000 datu rindas. Daļu vai visus šos datus varat summēt, izveidojot masīva formulu vienā šūnā, tā vietā, lai vilktu formulu uz leju līdz 1000 rindu. Turklāt, ņemiet vērā, ka vienšūnas formulu šūnā H20 ir pilnībā neatkarīga Vairākšūnu formulas (formula šūnās H10 – H19). Šis ir citas priekšrocības, izmantojot masīva formulu — elastību. Citu formulu H kolonnā var mainīties, neietekmējot H20 formulā. Tā var būt arī paraugprakses ir neatkarīga kopsummas, kā parādīts tālāk, jo tā palīdz validētu savus rezultātus precizitāti.

  • Dinamiskā masīva formulām ir arī šādas priekšrocības:

    • Atbilstību    Ja noklikšķināt uz kādas no šūnām no H10 uz leju, tiks parādīti to pašu formulu. Šo konsekvenci var nodrošināt lielāku precizitāti.

    • Drošība    Vairākšūnu masīva formulas izveide komponents nevar pārrakstīt. Piemēram, noklikšķiniet uz šūnas H11 un nospiediet taustiņu Delete. Excel nemaina masīva izvades. Lai mainītu to, ir jāatlasa augšējās kreisās šūnas masīva vai šūnā H10.

    • Mazāks faila lielums.    Vienu masīva formulu var izmantot bieži, nevis vairākas starpposma formulas. Piemēram, automašīnas pārdošanas piemērs izmanto vienu masīva formulu, lai aprēķinātu rezultāti kolonnā E. Ja ir izmantojis standarta formulas, piemēram, = F10 * G10, F11 * G11, F12 * G12, utt., tādu pašu rezultātu aprēķināšanai būtu lietoto 11 dažādas formulas. Tas nav īpašs, bet kā rīkoties, ja jums bija tūkstošiem rindas kopsummas? Pēc tam to var veikt liela atšķirība.

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

    • Izliešanu    Dinamiskā masīva formulas tiks automātiski plūst uz izvades diapazonā. Ja avota datos ir Excel tabulā, tad dinamiskās masīva formulas tiks automātiski mainītu lielumu kad pievienojat vai noņemat datus.

    • #SPILL! kļūdas    Dinamiskā masīviem ieviests #SPILL! kļūdu, kas norāda, ka paredzēto izplūde diapazons ir bloķēta kāda iemesla dēļ. Kad esat izlabojis bloķēšana, izmantojot formulu automātiski plūst.

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

Ja jums atsevišķus vienumus, lietojiet komatus, izveidojiet horizontālu masīvu (rindas). Ja jums atsevišķus vienumus, izmantojot semikolu, varat izveidot vertikālu masīvu (kolonnas). Lai izveidotu divdimensiju masīva, norobežotu vienumus katrā rindā ar komatiem un ierobežotu katru rindu ar semikoliem.

Nākamās darbības ļaus patrenēties, veidojot horizontālu, vertikālu un divdimensiju konstanti. Mēs parādīsim secības funkciju izmantošana, lai automātiski ģenerētu masīva konstantes piemēri, kā arī manuāli ievadīt masīva konstantes.

  • Horizontālas konstantes izveide

    Izmantojiet iepriekšējo piemēru darbgrāmatu vai izveidojiet jaunu darbgrāmatu. Atlasiet tukšu šūnu un ievadiet =SEQUENCE(1,5). Funkcijas secība izveido 1 rindai, 5 kolonnas masīva tāda pati, kā = {1,2,3,4,5}. Tiek rādīts šāds rezultāts:

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

  • Vertikālas konstantes izveide

    Atlasiet tukšu šūnu ar telpas zem tā, un ievadiet =SEQUENCE(5)vai = {1; 2; 3; 4; 5}. Tiek rādīts šāds rezultāts:

    Vertikāla masīva konstantes izveide ar = SEQUENCE(5) vai = {1; 2; 3; 4; 5}

  • Divdimensiju konstantes izveide

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

    Izveidojiet 3 rindas, 4 kolonnas masīva konstante ar =SEQUENCE(3,4)

    Varat ievadīt arī: vai = {1, 2, 3, 4; 5,6,7,8 9,10,11,12}, bet jūs vēlaties pievērst uzmanību kur ievietojat semikoliem salīdzinājumā ar komatiem.

    Kā redzat, secība opcija piedāvā ievērojamu priekšrocībām, salīdzinot manuāli ievadīt savu masīva konstantes vērtību. Galvenokārt, tas ietaupa jūsu laiku, bet to var palīdzēt samazināt kļūdu no manuāla ievadīšana. Arī ir vieglāk lasīt, it kā semikoliem var būt grūti atšķirt no komatu atdalītāji.

Lūk, piemērs, kā lielāks formulas daļa izmanto masīva konstantes. Parauga darbgrāmatā dodieties uz konstante formulā darblapas vai izveidojiet jaunu darblapu.

Šūnā D9 mēs ieraksta =SEQUENCE(1,5,3,1), bet šūnas A9:H9 var arī ievadīt 3, 4, 5, 6 un 7. Nekas īpašs par to, ka skaitļu atlasītus, mēs vienkārši izvēlējāmies kaut ko citu, nevis 1-5 atšķirības.

Ievadiet šūnā E11 = SUM (D9:H9*SEQUENCE(1,5)), vai = SUM (D9:H9* {1,2,3,4,5}). Formulas atgriež 85.

Masīvu konstanšu lietošana formulās. Šajā piemērā mēs izmantojām = SUM (D9:H(*SEQUENCE(1,5))

Funkcijas secība izveido atbilst masīva konstantes {1,2,3,4,5}. Tā kā programma Excel veic darbības izteiksmju iekavās pirmo reizi, divās nākamajās elementi, kas nonāk play ir šūnu vērtības D9:H9 un reizināšanas operatoru (*). Šajā brīdī formula reizina vērtības iekļauto masīvu ar atbilstošo vērtību konstante. Tas ir ekvivalents:

=SUM(D9*1,E9*2,F9*3,G9*4,H9*5)vai =SUM(3*1,4*2,5*3,6*4,7*5)

Visbeidzot, funkciju SUM saskaita vērtības, un atgriež 85.

Lai izvairītos no iekļautā masīva izmantošanas un darbība pilnībā paturiet atmiņas, to var aizstāt 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īva konstantēm

  • Masīvu konstantes var ietvert skaitļus, tekstu, loģiskās vērtības (piemēram, TRUE un FALSE) un kļūdu vērtības, piemēram, # n/a. Vesels skaitlis, decimāldaļu un zinātniskā formātā, varat izmantot skaitļus. Ja ievadāt tekstu, jums būs ietverts 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.

Viena no vislabāk izmantot masīva konstantes ir nosaukt. Nosauktām konstantēm var būt ievērojami vieglāk izmantot, un tās var paslēpt dažus masīvu formulās no citiem sarežģītības. Lai nosaukumu piešķiršana masīva konstantēm un izmantot 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"}

Dialoglodziņu būtu jāizskatās šādi:

Pievienot nosaukumu masīva konstante no Formulas > definētie nosaukumi > nosaukumu pārvaldnieks > jaunus

Noklikšķiniet uz Labiun pēc tam atlasiet jebkuras rindas ar trīs tukšās šūnas un ievadiet = Ceturksnis1.

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

Nosaukta masīva konstantes lietošana formulā, piemēram, = Ceturksnis1, kur Ceturksnis1 ir definēts kā = {"Janvāris", "Februāris", "marts"}

Ja vēlaties, lai rezultāti plūst vertikāli, nevis horizontāli, varat izmantot =apmainīšana vietām(Ceturksnis1).

Ja vēlaties, lai parādītu sarakstu ar 12 mēnešu laikā, piemēram, var izmantot, veidojot finanšu pārskats, varat veidot vienreizējs pašreizējo gadu, izmantojot funkciju secību. Skata poga lieta par šo funkciju, ir tas, ka pat tad, ja tiek rādīts tikai mēnesis, nav derīgs datums, aiz tā, ko var izmantot citos aprēķinos. Varat atrast šajos piemēros darblapās nosaukts masīva konstante un ātrās parauga datu kopu parauga darbgrāmata.

=Text(Date(year(TODAY()),Sequence(1,12),1),"MMM")

Izmantojiet tekstu, kā arī norādīts datums, gads, šodien, un secības funkciju kombinācijas izveidot dinamiskas sarakstu ar 12 mēnešu laikā

Tas tiek izmantota Funkcija DATE , lai izveidotu datumu, pamatojoties uz pašreizējo gadu, secība izveido masīva konstante no 1 līdz 12 no janvāra līdz decembrim, tad funkcija TEXT konvertē parādāmais formāts, lai "mmm" (Jan, Feb, Mar utt.). Ja vēlaties, lai parādītu pilnu mēneša nosaukumu, piemēram, janvāris, jāizmanto "MMM".

Izmantojot nosaukta konstante kā masīva formulu, neaizmirstiet ievadiet vienādības zīmi, kā = Ceturksnis1, ne tikai Ceturksnis1. Ja jums nav Excel saprot masīva kā teksta virkni un savu formulu nedarbosies, kā paredzēts. Visbeidzot, ņemiet vērā, ka varat izmantot funkcijas, teksta un skaitļu kombinācija. Viss ir atkarīgs no tā, kā radošu, kuru vēlaties iegūt.

Tālāk sniegtie piemēri parāda, daži veidi, kā masīva konstanšu izmantošana masīva formulās ievietojat. Daži piemēri, izmantojiet funkcija TRANSPOSE pārvērstu rindas, kolonnas un otrādi.

  • Vairāku katru masīva elementu

    Ievadiet = secību (1,12) * 2, vai = {1, 2, 3, 4; 5,6,7,8; 9,10,11,12} * 2

    Varat arī dalīšana ar (/), pievienošana, izmantojot (+) un atņemšana ar (-).

  • Masīva elementu kāpināšana

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

  • Kvadrātsakne no kvadrātu vienumu atrašana masīva

    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

    Šajā piemērā paskaidrots, kā izmantot masīva formulu, lai izveidotu jaunu masīva no esošās 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}

    Noteikti ierakstiet {(atverošās figūriekavas) pirms 10, un} (noslēdzošo iekavu) pēc tam, kad rakstāt 180, jo tiek veidots skaitļu masīvs.

    Pēc tam ievadiet = D9 #vai = D9:I11 tukšā šūnā. 3 x 6 masīva šūnām tiek parādīta redzat D9:D11 iekļautajām vērtībām. # Zīmi sauc izlijušas diapazona operators, un tas ir programmas Excel veids, kā atsauci uz visu masīva diapazons, tā vietā, lai ierakstiet to.

    Atsauces esošās masīvu izlieta diapazona operators (#) izmantošana

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

    Varat veikt izlieta masīva formulas rezultātu un kas pārvērst tās sastāvdaļas. Atlasiet šūnu D9 un pēc tam nospiediet taustiņu F2 , lai pārslēgtos rediģēšanas režīmā. Pēc tam nospiediet taustiņu F9 , lai pārvērstu šūnu atsauces uz vērtības, kuras programma Excel pēc tam pārvērš par masīva konstanti. Kad tiek nospiests taustiņš Enter, formula = D9 #, būtu tagad = {10,20,30; 40,50,60; 70,80,90}.

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

    Nākamajā piemērā parādīts, kā saskaitīt rakstzīmes šūnu diapazonā. Tas attiecas arī uz atstarpes.

    Saskaitītu kopējo diapazona un citiem masīviem darbam ar teksta virknes rakstzīmju skaitu

    = SUM (LEN(C9:C13))

    Šajā gadījumā Funkcija LEN atgriež katrā teksta virknes garums katrā no šūnu diapazona. Funkciju SUM pēc tam saskaita šīs vērtības un parāda rezultātu (66). Ja vēlaties saņemt vidējo rakstzīmju skaitu, jūs varētu izmantot:

    = AVERAGE (LEN(C9:C13))

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

    =INDEX(C9:C13,MATCH(MAX(LEN(C9:C13)),LEN(C9:C13),0),1)

    Šī formula darbojas tikai tad, ja datu diapazonā ir viena kolonna šūnu.

    Apskatīsim tuvāk formulu, sākot no iekšējo elementus un darbu uz āru. Funkcija LEN atgriež garumu katra vienuma šūnas diapazonā, kas D2:D6. Funkcija MAX aprēķina lielākā vērtība, apstrādājot ar tiem vienumiem, kas atbilst garākās teksta virknes, kas ir šūnā D3.

    Lūk, kur iegūt nedaudz sarežģītas lietas. Funkcija MATCH aprēķina offset (relatīvo pozīciju) no šūnas, kas satur garākās teksta virknes. Lai to izdarītu, ir jāveic trīs argumenti: uzmeklējamā _ vērtība, uzmeklēšanas _ masīvs un atbilstības _ tips. Funkcija MATCH meklē uzmeklēšanas _ masīvs par norādīto uzmeklējamo _ vērtību. Šajā gadījumā uzmeklējamā _ vērtība ir garākās teksta virknes:

    MAX(LEN(C9:C13)

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

    LEN(C9:C13)

    Šajā gadījumā arguments atbilstības _ tips ir 0. Atbilstības _ tips var būt 1, 0 vai -1 vērtību.

    • 1 - atgriež lielāko vērtību, kas ir mazāka vai vienāda ar uzmeklējamo _ val

    • 0 - atgriezta pirmā vērtība ir vienāda ar uzmeklējamo _ vērtību

    • -1 - atgriež mazāko vērtību, kas ir lielāks par vai vienāds ar norādīto uzmeklējamo _ vērtību

    • Ja atbilstības _ tips arguments tiek izlaists, Excel pieņem 1.

    Visbeidzot, funkcija INDEX izmanto šādi argumenti: masīva un rindu un kolonnu numuru atrodas šajā masīvā. Šūnu diapazona C9:C13 nodrošina masīvs, funkcija MATCH nodrošina šūnas adresi un gala arguments (1) norāda, ka vērtība tiek iegūta no masīva pirmo kolonnu.

    Ja vēlaties iegūt saturu mazāko teksta virknes, vai aizstāt MAX iepriekšējā piemērā ar MIN.

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

    Šajā piemērā parādīts, kā atrast trīs mazākās vērtības šūnu diapazonā, kur masīva parauga datu šūnu B9:B18has izveidots ar: = INT (RANDARRAY(10,1) * 100). Ņemiet vērā, ka RANDARRAY ir mainīga funkcija, tāpēc nejauši izvēlētus skaitļus jaunu kopu ikreiz, kad saņemsit Excel aprēķina.

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

    Ievadiet =SMALL(B9#,SEQUENCE(D9), = SMALL (B9:B18, {1, 2, 3})

    Šajā formulā tiek izmantotas masīva konstante, lai novērtētu funkcija SMALL trīs reizes un atgrieztu mazāko 3 dalībnieki masīvā, kas atrodas šūnās B9:B18, kur 3 ir mainīgo vērtību šūnas D9. Lai atrastu papildu vērtības, var palielināt vērtību secība funkcijā vai pievienot papildu argumentus konstante. Papildu funkcijas var izmantot arī ar šo formulu, piemēram, 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ākās vērtības diapazonā, funkcija SMALL varat aizstāt ar funkcija LARGE. Turklāt nākamajā piemērā apskatīts, kā izmantot funkciju rindas un INDIRECT .

    Ievadiet = lielu (B9 # RINDU (INDIRECT ("1:3"))), vai = lielu (B9:B18,ROW(INDIRECT("1:3")))

    Šajā brīdī var palīdzēt zināt mazliet par RINDU un INDIRECT funkcijām. Lai izveidotu masīva secīgas veselo skaitļu, varat izmantot funkciju ROW. Piemēram, atlasiet tukšu un ievadiet:

    =ROW(1:10)

    Formula izveido 10 pēc kārtas veselo skaitļu kolonnu. Lai skatītu iespējamās problēmas, ievietot rindu virs diapazonā, kas satur masīva formulu (t.i., virs rindas 1). Programma Excel maina rindu atsauces un formulu tagad ģenerē veseliem skaitļiem no 2 līdz 11. Lai novērstu šo problēmu, pievienojat funkcija INDIRECT formula:

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

    Funkcija INDIRECT izmanto teksta virknes kā argumentu, (kas ir iemesls, kāpēc pēdiņas ieskauj diapazona 1:10). Excel netiek pielāgots teksta vērtības rindu ievietošana vai citādi pārvietojot masīva formulu. Kā rezultātu, funkcija ROW vienmēr ģenerē veseliem skaitļiem, kuru vēlaties masīva. Jūs varētu izmantot tikpat viegli secībā:

    =SEQUENCE(10)

    Apskatīsim formulu, kas iepriekš izmantojāt — LARGE (B9 #, RINDU (INDIRECT ("1:3"))) = — sākot no iekšējo iekavas un darbu uz āru: funkcija INDIRECT atgriež teksta vērtību kopu šādā gadījumā vērtības 1. – 3. Funkcija ROW, savukārt ģenerē trīs šūnas kolonnā masīva. Funkcija LARGE izmanto vērtības šūnu diapazonā B9:B18, un tas tiek novērtēts trīs reizes, vienreiz par katru atsauce, ko atgriež funkcija ROW. Ja vēlaties atrast papildu vērtības, funkcija INDIRECT pievienot lielāku šūnu diapazonu. Visbeidzot, kā ar nelielu piemērus, varat izmantot šo formulu kopā ar citām funkcijām, piemēram, SUM vai AVERAGE.

  • Tāda diapazona summa, kurā ir kļūdu vērtības

    Excel funkciju SUM nedarbojas, mēģinot izveidot tāda diapazona summa, kurā ir kļūdas vērtība, piemēram, #VALUE! vai # n/a. Šajā piemērā parādīts, kā saskaitīt vērtības diapazona dati, kurā ir kļūdas:

    Izmantot masīvu tikt galā ar kļūdām. Piemēram, =SUM(IF(ISERROR(Data),"",Data) tiks summētu diapazona dati 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ā

    Šajā piemērā ir kā iepriekšējās formulas, bet tā atgriež 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, vajadzēs vērtību summēšana pēc nosacījumiem.

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

Piemēram, šī masīva formula saskaita tikai pozitīvus veselos skaitļus diapazonā realizācija, kas apzīmē šūnas E9:E24 šajā piemērā:

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

Funkcija IF izveido pozitīvās un nepareizu vērtību masīvu. Funkcija SUM būtībā ignorē nepatiesas vērtības, jo 0 + 0 = 0. Šūnu diapazonu, ko izmantot šajā formulā var ietvert rindu un kolonnu skaitu.

Varat arī summēt vērtības, kas atbilst vairāk nekā vienu nosacījumu. Piemēram, šī masīva formula aprēķina vērtības, kas lielākas par 0 un mazāka par 2500:

=SUM((Sales>0)*(Sales<2500)*(Sales))

Atcerieties, ka šī formula atgriež kļūdu, ja diapazonā ir vismaz viena šūna, kurā nav skaitliskas vērtības.

Varat arī izveidot masīva formulas, kuras izmanto veida vai nosacījumu. Piemēram, varat saskaitīt vērtības, kas ir lielāks par 0 vai mazāks par 2500:

=SUM(IF((Sales>0)+(Sales<2500),Sales))

Nevar izmantot funkciju AND un vai funkcijas masīvu formulās tieši, jo šīs funkcijas atgriež vienu rezultātu, patiess vai aplams, un masīva funkcijas pieprasa rezultātu masīvu. Problēmu var apiet, izmantojot loģisko parādīts iepriekšējās formulas. Citiem vārdiem sakot, ko jūs veicat matemātisko darbību, piemēram, saskaitīšanu vai reizināšanu uz vērtībām, kas atbilst vai vai un nosacījumu.

Š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 tiek salīdzinātas vērtības divu šūnu diapazoniem, nosaukta Mani_dati un jūsu _ dati un atgriež atšķirības starp divām skaitu. Vai divus diapazonus saturs ir identiskas, formula atgriež vērtību 0. Lai izmantotu šo formulu, šūnu diapazoni ir jābūt to pašu lielumu un to pašu dimensiju. Piemēram, ja Mani_dati ir 3 rindām, 5 kolonnas diapazons, jūsu _ dati arī jābūt 3 rindām, 5 kolonnas:

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

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

Varat atrast līdzīgi piemēri parauga darbgrāmatas darblapā atšķirības starp datu kopas .

Š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ārdošanas Personas

Automašīnas Tips

Numurs Pārdotais

Vienības Cena

Kopsummas Pārdošanas

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 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 pēc tam 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 pēc tam nospiediet taustiņu Kombināciju Ctrl + Shift + Enter.

Nospiežot taustiņu Kombināciju Ctrl + Shift + Enter, programma Excel ieskauj formula ar figūriekavās ({}) un ievieto formulu instances katrā atlasītā diapazona šūnā. Tas notiek ātri, tādēļ kopējo pārdošanas apjomu katram automašīnas tips katram pārdevējam redzat kolonnā E. Ja atlasiet E2 un pēc tam atlasiet E3, E4, un tā tālāk, redzēsit, to pašu formulu tiek rādīts: {= 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ā programma Excel reizina vērtības masīvā (šūnu diapazonu C2 – D11) un pēc tam izmantota funkcija SUM, lai pievienotu kopsummas kopā. Rezultāts ir $1,590,000 pārdošanas kopsummu. Šajā piemērā parādīts, kā jaudīga var būt šāda veida formula. Piemēram, pieņemsim, ka jums ir 1000 datu rindas. Daļu vai visus šos datus varat summēt, izveidojot masīva formulu vienā šūnā, tā vietā, lai vilktu formulu uz leju līdz 1000 rindu.

Turklāt, ņemiet vērā, ka vienšūnas formulu šūnā D13 ir pilnībā neatkarīga Vairākšūnu formulas (formula šūnās E2 – E11). Šis ir citas priekšrocības, izmantojot masīva formulu — elastību. Nevarēja mainīt formulas kolonnā E vai dzēst kolonnas kopā, neietekmējot D13 formulā.

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 izveide komponents nevar pārrakstīt. Piemēram, noklikšķiniet uz šūnas E3 un nospiediet Dzēst. Jums ir vai nu atlasiet visu šūnu diapazonu (E2 – E11) un mainīt visu masīva formula, vai atstāt masīva, kāds ir. Kā papildu drošības pasākums, jums ir nospiediet taustiņu Kombināciju Ctrl + Shift + Enter , lai apstiprinātu formulu izmaiņām.

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

Kopumā masīva formulās tiek izmantota standarta formulu sintakse. Tās visas sākas ar vienādības zīmi (=), un jūs varat izmantot lielāko daļu iebūvēto Excel funkcijas masīvu formulās. Galvenā atšķirība ir, izmantojot masīva formulu, nospiediet Taustiņu kombināciju Ctrl + Shift + Enter , lai ievadiet savu formulu. Kad tas ir izdarīts, programma Excel šo masīva formulu, ar figūriekavās — ja rakstāt figūriekavas manuāli, savu formulu, tiek konvertēti par teksta virknes, un tā nedarbosies.

Masīvu funkcijas var būt efektīvs veids, kā veidot sarežģītas formulas. Masīva formulas =SUM(C2:C11*D2:D11) ir tāda pati, kā tas: =SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Svarīgi!: Ikreiz, kad tas ir nepieciešams, lai ievadītu masīva formulu, nospiediet taustiņu Kombināciju Ctrl + Shift + Enter . Tas attiecas uz vienšūnas gan 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 formula, atlasiet visu formulas diapazonu (piemēram, E2: E11) un pēc tam nospiediet Delete.

  • Nevar ievietot tukšas šūnas vai izdzēst atsevišķas šūnas Vairākšūnu masīva formulas izveide.

Reizēm, iespējams, vajadzēs masīva formulas paplašināšana. Atlasiet pirmo šūnu diapazonā esošās masīvu un turpiniet, līdz esat atlasījis visu diapazonu, kuru vēlaties izvērst formulu. Nospiediet taustiņu F2 , lai rediģētu formulu un pēc tam nospiediet taustiņu Kombināciju CTRL + SHIFT + ENTER , lai apstiprinātu formulu, pēc tam, kad ir pielāgoti formulas diapazonā. Vislabāk atlasiet visu diapazonu, sākot ar masīva augšējā kreisajā šūnā. Augšējās kreisās šūnas ir tas, kurš tiek rediģēts.

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

  • Jūs laiku pa laikam var aizmirst taustiņu Kombināciju Ctrl + Shift + Enter. Tas var notikt pat Excel visvairāk pieredzējušiem lietotājiem. Atcerieties, ka nospiežot šo taustiņu kombināciju, ikreiz, kad ievadiet vai rediģējiet masīva formulu.

  • Citi lietotāji darbgrāmatai var nesaprast formulas. Praksē masīva formulas parasti nav izskaidrota darblapā. Tāpēc, ja citas personas jāmodificē darbgrāmatām, jums vai nu izvairīties no masīva formulu vai pārliecinieties, vai šīs personas uzzināt par jebkuru masīva formulām un saprast, kā tās mainīt, ja tie ir 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 jums ir nepieciešams, kad masīvu formulas var izveidot, nospiediet taustiņu Kombināciju Ctrl + Shift + Enter . Tā kā masīva konstantes ir komponents masīva formulas, telpiskā konstantes ar figūriekavās, manuāli ievadot tos. 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 vienu rindu: {1, 2, 3, 4}. Šeit ir masīvs vienā kolonnā: {1; 2; 3; 4}. Un šeit ir divas rindas un četras kolonnas masīva: {1, 2, 3, 4; 5,6,7,8}. Masīvā divas rindas pirmajā rindā ir 1, 2, 3 un 4 un otrajā rindā ir 5, 6, 7 un 8. Viena semikolu atdala divas rindas, starp 4. un 5.

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 A1 – E1.

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

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

    Šajā gadījumā jums vajadzētu ievadiet atverošo un aizverošo figūriekavās ({}), un programma Excel pievienos otrā 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ējo elementu iekavās ir masīva konstanti: {1,2,3,4,5}. Atcerieties, ka Excel nav ietverts masīva konstantes ar figūriekavām; faktiski tos rakstāt. Ņemiet vērā pēc pievienošanas konstante masīva formulu, nospiediet Taustiņu kombināciju Ctrl + Shift + Enter ievadīt 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})

Izmēģiniet šo, kopēt funkcijas, atlasiet tukšu šūnu darbgrāmatā, ielīmējiet to formulu joslā formulu un nospiediet taustiņu Kombināciju Ctrl + Shift + Enter. Redzēsit tādu pašu rezultātu kā to darījāt agrāk uzdevums, kas izmanto masīva formula:

=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ākais veids, kā izmantot masīva konstantes ir nosaukt. Nosauktām konstantēm var būt ievērojami vieglāk izmantot, un tās var paslēpt dažus masīvu formulās no citiem sarežģītības. Lai nosaukumu piešķiršana masīva konstantēm un izmantot formulā, rīkojieties šādi:

  1. Cilnes Formulas grupā Definētie nosaukumi noklikšķiniet uz Definēt nosaukumu.
    Tiek parādīts dialoglodziņš Definēt nosaukumu .

  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:

  • Dažus elementus var nav atdalītas ar pareizu rakstzīmju. Ja izlaižat komatu vai semikolu vai tos ievietojat nepareizā vietā, masīva konstantes var tikt izveidots pareizi vai iespējams, tiks 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.

Tālāk sniegtie piemēri parāda, daži veidi, kā masīva konstanšu izmantošana masīva formulās ievietojat. Daži piemēri, izmantojiet funkcija TRANSPOSE pārvērstu rindas, kolonnas 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. 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.

Š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 šajā masīvā skaitļu šūnu diapazons C8: E10, izmantojot masīva formulu. Darblapā, C8: 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 redzat C8: E10 iekļautajām vērtībām parādīts 3x3 šūnu masīvs ar šūnās.

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

  1. Ar šūnām C1: C3 atlasīta, nospiediet taustiņu F2, lai pārslēgtos rediģēšanas režīmā.

  2. Nospiediet taustiņu F9 , lai pārvērstu šūnu atsauces uz vērtības. Excel vērtības pārvērš masīva konstante. Formula tagad vajadzētu 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 šūnas 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 pēc tam nospiediet taustiņu Kombināciju Ctrl + Shift + Enter , lai redzētu garākās šūnas saturu a2: A6 (šūna A3).

Tiek izmantota šāda formula šūnā A8 Saskaita kopējo rakstzīmju skaitu (66) no šūnas A2 līdz A6.

=SUM(LEN(A2:A6))

Šajā gadījumā funkcija LEN atgriež katrā teksta virknes garums katrā no šūnu diapazona. Funkciju SUM pēc tam saskaita šīs vērtības un parāda rezultātu (66).

Atrast n mazākās vērtības diapazonā

Šajā piemērā parādīts, kā šūnu diapazonā atrast trīs mazākās vērtības.

  1. Ievadiet dažus nejauši izvēlētus skaitļus šūnās A1:A11.

  2. Atlasiet šūnas C1 līdz C3. Šis šūnu kopums rīkos rezultāti, kas iegūti, masīva formulu.

  3. Ierakstiet šo formulu un nospiediet taustiņu Kombināciju Ctrl + Shift + Enter:

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

Šī formula tiek izmantota masīva konstante, lai novērtētu funkcija SMALL trīs reizes un atgrieztu mazāko (1), otrais mazāko (2) un trešais mazāko (3) dalībnieki masīvā, kas atrodas šūnās A1: A10, lai atrastu vairāk vērtības, varat pievienot papildu argumentus konstante. Papildu funkcijas var izmantot arī ar šo formulu, piemēram, SUM vai AVERAGE. Piemēram:

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

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

N diapazona 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 – D3.

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

    = LARGE(A1:A10,ROW(INDIRECT("1:3")))

Šajā brīdī var palīdzēt zināt mazliet par RINDU un INDIRECT funkcijām. Lai izveidotu masīva secīgas veselo skaitļu, varat izmantot funkciju ROW . Piemēram, atlasiet tukšā kolonnā 10 šūnu Vingrināšanās darbgrāmatā, 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.

Arī tālāk aprakstītā formula, kas iepriekš izmantojāt — = LARGE (A5:A14,ROW(INDIRECT("1:3"))) — sākot no iekšējo iekavas un darbu uz āru: funkcijas INDIRECT atgriež teksta vērtību kopu šādā gadījumā vērtības 1. – 3. Funkcija ROW , savukārt ģenerē trīs šūnu kolonnveida masīva. Funkcijas LARGE izmanto vērtības šūnu diapazonā A5:A14, un tas tiek novērtēts trīs reizes, vienreiz par katru atsauce, ko atgriež funkcija ROW . Trīs šūnu kolonnveida masīva tiek atgrieztas vērtības 3200, 2700 un 2000. Ja vēlaties atrast papildu vērtības, funkcijas INDIRECT pievienot lielāku šūnu diapazonu.

Kā ar vecāku piemērus, šo formulu var izmantot ar citām funkcijām, piemēram, SUM vai AVERAGE.

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

Dodieties atpakaļ uz teksta virknes 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)

Teksts "šūnu kopa, kas" tiek rādīta.

Apskatīsim tuvāk formulu, sākot no iekšējo elementus un darbu uz āru. Funkcija LEN atgriež garumu katra vienuma šūnu diapazonā A2: A6. Funkcija MAX aprēķina lielākā vērtība, apstrādājot ar tiem vienumiem, kas atbilst garākās teksta virknes, 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 notiek šādi argumenti: masīva un rindu un kolonnu numuru atrodas šajā masīvā. Šūnu diapazonā A2: A6 nodrošina masīvs, funkcija MATCH nodrošina šūnas adresi un gala arguments (1) norāda, ka vērtība tiek iegūta no masīva pirmo kolonnu.

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

Apstiprinājums

Šajā rakstā daļas tika pamatojoties uz kolonnas Excel prasmīgā lietotāja raksta Colin Wilcox un pielāgots no 14 un 15 Excel 2002 Formulas raksta John Walkenbach bijušā Excel MVP grāmatu nodaļas sēriju.

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ā masīva formulas vs. paaudzes CSE 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.

×