Dinamisko masīvu formulas programmā Excel

Dinamiskie masīvi ir lielākās izmaiņas Excel formulās pēdējo gadu laikā. Varbūt lielākās pārmaiņas jebkad. Tas notiek tāpēc, ka dinamiskie masīvi ļauj formulā vienlaikus viegli strādāt ar vairākām vērtībām. Daudziem lietotājiem tā būs pirmā reize, kad viņi saprot un izmanto masīva formulas.

Tas ir liels jauninājums un apsveicamas izmaiņas. Dinamiskie masīvi atrisinās dažas patiešām sarežģītas problēmas programmā Excel un būtiski mainīs veidu, kā tiek veidotas un veidotas darblapas.

Pieejamība

Ir pieejami tikai dinamiskie masīvi un jaunās funkcijas Excel 365 . Programmas Excel 2016 un Excel 2019 nepiedāvā dinamiskā masīva formulu atbalstu. Ērtības labad es izmantošu “Dynamic Excel” (Excel 365) un “Traditional Excel” (2019 vai vecāka), lai atšķirtu tālāk norādītās versijas.

Jaunas funkcijas

Kā daļa no dinamiskā masīva atjauninājuma programmā Excel tagad ir iekļautas 8 jaunas funkcijas, kas tieši izmanto dinamiskos masīvus, lai atrisinātu problēmas, kuras tradicionāli ir grūti atrisināt ar parastajām formulām. Lai iegūtu sīkāku informāciju un piemērus katrai funkcijai, noklikšķiniet uz tālāk esošajām saitēm:

Video: Jaunas dinamiskā masīva funkcijas programmā Excel (apmēram 3 minūtes).

kā padarīt līnijas pārtraukumu

Piezīme: XLOOKUP un XMATCH nebija sākotnējā jauno dinamisko masīvu funkciju grupā, taču tās lieliski darbojas jaunajā dinamiskā masīva dzinējā. XLOOKUP aizstāj VLOOKUP un piedāvā mūsdienīgu, elastīgu pieeju, kas izmanto masīvu priekšrocības. XMATCH ir MATCH funkcijas jauninājums, kas nodrošina jaunas iespējas INDEKSS un MATCH formulas.

Piemērs

Pirms iedziļināties detaļās, apskatīsim vienkāršu piemēru. Zemāk mēs izmantojam jauno UNIKĀLA funkcija lai iegūtu unikālas vērtības no diapazona B5: B15, ar a viens E5 ievadītā formula:

 
= UNIQUE (B5:B15) // return unique values in B5:B15

Rezultāts ir saraksts ar pieciem unikālajiem pilsētu nosaukumiem, kas parādās E5: E9.

Tāpat kā visas formulas, UNIQUE tiks automātiski atjaunināts, mainoties datiem. Tālāk Vankūvera ir aizstājusi Portlendu 11. rindā. UNIQUE rezultāts tagad ietver Vankūveru:

Izliešana - viena formula, daudzas vērtības

Programmā Dynamic Excel formulas, kas atgriež vairākas vērtības, spēle 'šīs vērtības tieši darblapā. Formulu lietotājiem tas uzreiz būs loģiskāk. Tā ir arī pilnīgi dinamiska uzvedība - mainoties avota datiem, izplūdušie rezultāti nekavējoties tiks atjaunināti.

Taisnstūri, kas ietver vērtības, sauc par spēļu klāsts '. Jūs pamanīsit, ka noplūdes diapazonam ir īpaša izcelšana. UNIQUE piemērā iepriekš noplūdes diapazons ir E5: E10.

Mainoties datiem, noplūdes diapazons pēc vajadzības paplašināsies vai samazināsies. Iespējams, tiks pievienotas jaunas vērtības vai pazudīs esošās vērtības. Tādā veidā noplūdes diapazons ir jauna veida dinamiskais diapazons.

Piezīme. Ja noplūdi bloķē citi dati, tiek parādīta kļūda #SPILL. Kad esat atstājis vietu noplūdes diapazonam, formula automātiski izlīs.

Video: Izliešana un noplūdes diapazons

Atsauce uz noplūdes diapazonu

Lai atsauktos uz noplūdes diapazonu, izmantojiet jaukšanas simbolu (#) aiz diapazona pirmās šūnas. Piemēram, lai atsauktos uz UNIQUE iepriekš minētās funkcijas rezultātiem, izmantojiet:

 
=E5# // reference UNIQUE results

Tas ir tas pats, kas atsaukties uz visu noplūdes diapazonu, un jūs redzēsit šo sintaksi, rakstot formulu, kas attiecas uz pilnu noplūdes diapazonu.

Izplūdes diapazona atsauci varat tieši ievadīt citās formulās. Piemēram, lai saskaitītu UNIQUE atgriezto pilsētu skaitu, varat izmantot:

 
= COUNTA (E5#) // count unique cities

Mainoties noplūdes diapazonam, formula atspoguļos jaunākos datus.

Masveida vienkāršošana

Jaunas dinamiskā masīva formulas pievienošana nozīmē, ka dažas formulas var krasi vienkāršot. Šeit ir daži piemēri:

• Iegūstiet un uzskaitiet unikālas vērtības ( pirms tam | pēc )
• Skaitīt unikālas vērtības ( pirms tam | pēc )
• Filtrēt un izvilkt ierakstus ( pirms tam | pēc )
• Izvilkt daļējas atbilstības ( pirms tam | pēc )

Viena spēks

Viena no visspēcīgākajām pieejas “viena formula, daudzas vērtības” priekšrocībām ir mazāka paļaušanās absolūts vai jaukts atsauces. Tā kā dinamiskā masīva formula izklāj rezultātus darblapā, atsauces paliek nemainīgas, bet formula rada pareizus rezultātus.

Piemēram, zemāk mēs izmantojam funkciju FILTER, lai iegūtu ierakstus grupā “A”. Šūnā F5 tiek ievadīta viena formula:

 
= FILTER (B5:D11,B5:B11='a') // references are relative

Ņemiet vērā, ka abi diapazoni ir atbloķētas relatīvās atsauces, taču formula darbojas perfekti.

Tas ir milzīgs ieguvums daudziem lietotājiem, jo ​​tas padara formulas rakstīšanas procesu daudz vienkāršāku. Citu labu piemēru skatiet tālāk redzamajā reizināšanas tabulā.

Ķēdes funkcijas

Lietas kļūst patiešām interesantas, ja apvienojat ķēdē vairāk nekā vienu dinamiskā masīva funkciju. Varbūt vēlaties kārtot UNIQUE atgrieztos rezultātus? Viegli. Vienkārši aptiniet Kārtošanas funkcija ap UNIKĀLO funkciju šādi:

Tāpat kā iepriekš, mainoties avota datiem, automātiski parādās jauni unikāli rezultāti, kas ir labi sakārtoti.

Dzimtā uzvedība

Ir svarīgi saprast, ka dinamiskā masīva uzvedība ir a dzimtā un dziļi integrēta . Kad jebkurš Formula atgriež vairākus rezultātus, šie rezultāti tiks izlietoti vairākās darblapas šūnās. Tas ietver vecākas funkcijas, kas sākotnēji nav paredzētas darbam ar dinamiskiem masīviem.

Piemēram, tradicionālajā Excel, ja mēs dodam LEN funkcija uz diapazons no teksta vērtībām, mēs redzēsim a viens rezultāts. Dynamic Excel, ja mēs piešķiram LEN funkcijai vērtību diapazonu, mēs redzēsim vairākas rezultātus. Šajā zemāk redzamajā ekrānā ir redzama vecā uzvedība kreisajā pusē un jaunā uzvedība labajā pusē:

Šīs ir milzīgas izmaiņas, kas var ietekmēt visu veidu formulas. Piemēram, Funkcija VLOOKUP ir paredzēts vienas vērtības iegūšanai no tabulas, izmantojot kolonnu indeksu. Tomēr, ja programmā Dynamic Excel mēs piešķiram VLOOKUP vairāk nekā vienu kolonnu indeksu, izmantojot masīva konstante kā šis:

 
= VLOOKUP ('jose',F7:H10,{1,2,3},0)

VLOOKUP atgriezīs vairākas kolonnas:

Citiem vārdiem sakot, pat ja VLOOKUP nekad nebija paredzēts, lai atgrieztu vairākas vērtības, tagad to var izdarīt, pateicoties jaunajam formulas dzinējam programmā Dynamic Excel.

Visas formulas

Visbeidzot, ņemiet vērā, ka dinamiskie masīvi darbojas ar visas formulas ne tikai funkcijas . Zemāk redzamajā piemērā šūna C5 satur vienu formulu:

kā ievada grādus Excel

 
=B5:B14*C4:L4

Rezultāts izplatās diapazonā no 10 līdz 10, kas ietver 100 šūnas:

Piezīme. Tradicionālajā programmā Excel varat redzēt vairākus rezultātus, ko atgriež masīva formula izmantojiet F9, lai pārbaudītu formulu . Bet, ja vien jūs neievadāt formulu kā daudzšūnu masīva formula , darblapā tiks parādīta tikai viena vērtība.

Masīvi iet uz galveno

Izlaižot dinamiskos masīvus, vārds “ masīvs 'parādīsies daudz biežāk. Faktiski jūs varat redzēt, ka masīvs un diapazons tiek izmantoti gandrīz savstarpēji aizstājami. Jūs redzēsit Excel masīvus, kas ir ievietoti cirtainās iekavās:

 
{1,2,3} // horizontal array {123} // vertical array

Masīvs ir programmēšanas termins, kas attiecas uz vienumu sarakstu, kas parādās noteiktā secībā. Iemesls, kāpēc masīvi Excel formulās parādās tik bieži, ir tas, ka masīvi var lieliski izsaka vērtības šūnu diapazonā .

Video: Kas ir masīvs?

Masīva operācijas kļūst svarīgas

Tā kā dinamiskās Excel formulas var viegli strādāt ar vairākām vērtībām, masīva darbības kļūs svarīgākas. Termins “masīva darbība” attiecas uz izteiksmi, kas masīvam veic loģisku testu vai matemātisku darbību. Piemēram, zemāk esošā izteiksme pārbauda, ​​vai vērtības B5: B9 ir vienādas ar “ca”

 
=B5:B9='ca' // state = 'ca'

tā kā B5: B9 ir 5 šūnas, rezultāts ir 5 TRUE/FALSE vērtības masīvā:

 
{FALSETRUEFALSETRUETRUE}

Tālāk norādītā masīva darbība pārbauda summas, kas lielākas par 100:

 
=C5:C9>100 // amounts > 100

Pēdējā masīva darbība apvieno testu A un testu B vienā izteiksmē:

 
=(B5:B9='ca')*(C5:C9>100) // state = 'ca' and amount > 100

Piezīme. Excel matemātiskās darbības laikā automātiski piespiež TRUE un FALSE vērtības uz 1 un 0.

Lai to atjaunotu dinamisko masīvu formulās programmā Excel, zemāk redzamais piemērs parāda, kā mēs varam izmantot tieši tādu pašu masīva darbību FILTER funkcijā kā iekļaut arguments:

FILTER atgriež divus ierakstus, kur valsts = 'ca' un summa> 100.

Demonstrācijai skatiet: Kā filtrēt pēc diviem kritērijiem (video).

Jaunas un vecas masīva formulas

Programmā Dynamic Excel nav jāievada masīva formulas, izmantojot taustiņu kombināciju control + shift + enter. Kad tiek veidota formula, programma Excel pārbauda, ​​vai formula var atdot vairākas vērtības. Ja tā, tas tiks automātiski saglabāts kā dinamiskā masīva formula, taču jūs neredzēsit cirtainās breketes. Tālāk redzamajā piemērā ir parādīta tipiskā masīva formula, kas ievadīta programmā Dynamic Excel:

Atverot to pašu formulu tradicionālajā programmā Excel, tiks parādītas cirtainās breketes:

Pārejot citā virzienā, kad programmā Dynamic Excel tiek atvērta “tradicionāla” masīva formula, formulas joslā redzēsiet cirtainās breketes. Piemēram, tālāk redzamajā ekrānā tradicionālā Excel tiek parādīta vienkārša masīva formula:

Tomēr, ja atkārtoti ievadāt formulu bez izmaiņām, cirtainās breketes tiek noņemtas un formula atgriež to pašu rezultātu:

Būtība ir tāda, ka masīva formulas, kas ievadītas ar taustiņu kombināciju Control + Shift + Enter (CSE), joprojām darbojas, lai saglabātu savietojamību, taču jums nevajadzētu ievadīt masīva formulas ar meklētājprogrammu programmā Dynamic Excel.

Raksturs

Ieviešot dinamiskos masīvus, jūs redzēsit, ka @ rakstzīme formulās parādās biežāk. Simbols @ ļauj izmantot uzvedību, kas pazīstama kā “ netiešs krustojums '. Netiešs krustojums ir loģisks process, kurā daudzas vērtības tiek samazinātas līdz vienai vērtībai.

Tradicionālajā programmā Excel netiešs krustojums ir klusa darbība, ko izmanto (ja nepieciešams), lai vienā šūnā samazinātu vairākas vērtības līdz vienam rezultātam. Programmā Dynamic Excel tas parasti nav vajadzīgs, jo darblapā var nokļūt vairāki rezultāti. Kad tas ir nepieciešams, netiešs krustojums tiek izsaukts manuāli ar @ rakstzīmi.

Atverot izklājlapas, tika izveidota vecāka Excel versija, iespējams, redzēsit @ rakstzīmi, kas automātiski pievienota esošajām formulām, kurām ir potenciāls lai atgrieztu daudzas vērtības. Tradicionālajā Excel formula, kas atgriež vairākas vērtības, darblapā netiks izlieta. Simbols @ piespiež to pašu darbību programmā Dynamic Excel, lai formula darbotos tāpat un atgrieztu tādu pašu rezultātu kā sākotnējā Excel versijā.

Citiem vārdiem sakot, tiek pievienots simbols @, lai novērstu to, ka vecāka formula izplūst darblapā vairākus rezultātus. Atkarībā no formulas, iespējams, varēsit noņemt @ rakstzīmi, un formulas uzvedība nemainīsies.

Kopsavilkums

• Dinamiskie masīvi ievērojami atvieglos noteiktu formulu rakstīšanu.
• Tagad ar formulām varat viegli filtrēt atbilstošos datus, kārtot un iegūt unikālas vērtības.
• Dinamiskās masīva formulas var pievienot (ligzdot), lai veiktu tādas darbības kā filtrēšana un kārtošana.
• Formulas, kas atgriež vairāk nekā vienu vērtību, automātiski izplūst.
• Lai ievadītu masīva formulu, nav nepieciešams izmantot Ctrl+Shift+Enter.
• Dinamiskā masīva formulas ir pieejamas tikai programmā Excel 365.