Դինամիկ զանգվածներ Excel-ում

Ինչ են դինամիկ զանգվածները

2018 թվականի սեպտեմբերին Microsoft-ը թողարկեց թարմացում, որը Microsoft Excel-ում ավելացնում է բոլորովին նոր գործիք՝ Dynamic Arrays և 7 նոր գործառույթներ դրանց հետ աշխատելու համար։ Այս բաները, առանց չափազանցության, արմատապես փոխում են բանաձևերի և գործառույթների հետ աշխատելու բոլոր սովորական տեխնիկան և մտահոգում բառացիորեն յուրաքանչյուր օգտագործողին:

Դիտարկենք պարզ օրինակ՝ էությունը բացատրելու համար։

Ենթադրենք, մենք ունենք պարզ աղյուսակ՝ քաղաք-ամիսների տվյալներով։ Ի՞նչ կլինի, եթե թերթի աջ կողմում ընտրենք որևէ դատարկ բջիջ և մուտքագրենք բանաձև, որը կապվում է ոչ թե մեկ բջիջի, այլ անմիջապես տիրույթի:

Excel-ի բոլոր նախորդ տարբերակներում, սեղմելուց հետո Մտնել մենք կստանանք միայն մեկ առաջին B2 բջիջի բովանդակությունը: Էլ ինչպե՞ս։

Դե, կամ հնարավոր կլիներ այս միջակայքը փաթաթել ինչ-որ ագրեգացիոն ֆունկցիայի մեջ, ինչպիսին է =SUM(B2:C4) և ստանալ դրա համար ընդհանուր գումար:

Եթե ​​մեզ անհրաժեշտ լինեին ավելի բարդ գործողություններ, քան պարզունակ գումարը, ինչպես օրինակ՝ եզակի արժեքների արդյունահանումը կամ Top 3-ը, ապա մենք պետք է մուտքագրեինք մեր բանաձևը որպես զանգվածի բանաձև՝ օգտագործելով ստեղնաշարի դյուրանցումը: Ctrl+Հերթափոխություն+Մտնել.

Հիմա ամեն ինչ այլ է։

Այժմ նման բանաձև մուտքագրելուց հետո մենք կարող ենք պարզապես սեղմել Մտնել – և արդյունքում անմիջապես ստացեք բոլոր արժեքները uXNUMXbuXNUMXb, որոնց մենք անդրադարձել ենք.

Սա կախարդանք չէ, այլ նոր դինամիկ զանգվածներ, որոնք այժմ ունի Microsoft Excel-ը: Բարի գալուստ նոր աշխարհ 🙂

Դինամիկ զանգվածների հետ աշխատելու առանձնահատկությունները

Տեխնիկապես, մեր ամբողջ դինամիկ զանգվածը պահվում է առաջին G4 բջիջում՝ լրացնելով անհրաժեշտ թվով բջիջներ դեպի աջ և ներքև իր տվյալներով։ Եթե ​​ընտրեք զանգվածի որևէ այլ բջիջ, ապա բանաձևի տողում հղումն անգործուն կլինի՝ ցույց տալով, որ մենք գտնվում ենք «երեխա» բջիջներից մեկում.

Մեկ կամ մի քանի «երեխա» բջիջները ջնջելու փորձը ոչ մի բանի չի հանգեցնի. Excel-ը անմիջապես կվերահաշվարկի և լրացնելու դրանք:

Միևնույն ժամանակ, մենք կարող ենք ապահով կերպով անդրադառնալ այս «մանկական» բջիջներին այլ բանաձևերով.

Եթե ​​պատճենեք զանգվածի առաջին բջիջը (օրինակ՝ G4-ից F8), ապա ամբողջ զանգվածը (նրա հղումները) կշարժվի նույն ուղղությամբ, ինչ սովորական բանաձևերում.

Եթե ​​մեզ անհրաժեշտ լինի զանգվածը տեղափոխել, ապա այն բավական կլինի տեղափոխել (մկնիկի օգնությամբ կամ դրանց համակցությամբ Ctrl+X, Ctrl+V), կրկին միայն առաջին հիմնական բջիջը G4 – դրանից հետո այն կտեղափոխվի նոր տեղ, և մեր ամբողջ զանգվածը նորից կընդլայնվի:

Եթե ​​Ձեզ անհրաժեշտ է թերթի որևէ այլ տեղ հղում կատարել ստեղծված դինամիկ զանգվածին, ապա կարող եք օգտագործել հատուկ նիշը # («ֆունտ») նրա առաջատար բջիջի հասցեից հետո.

Օրինակ, այժմ դուք կարող եք հեշտությամբ ստեղծել բացվող ցուցակ մի բջիջում, որը վերաբերում է ստեղծված դինամիկ զանգվածին.

Դինամիկ զանգվածի սխալներ

Բայց ի՞նչ տեղի կունենա, եթե զանգվածը ընդլայնելու համար բավարար տարածք չկա, կամ եթե դրա ճանապարհին արդեն կան բջիջներ, որոնք զբաղված են այլ տվյալների կողմից: Հանդիպեք սկզբունքորեն նոր տեսակի սխալների Excel-ում. #ՓՈԽԱՆՑՈՒՄ! (#Թափել!):

Ինչպես միշտ, եթե սեղմենք դեղին ադամանդով և բացականչական նշանով պատկերակի վրա, մենք ավելի մանրամասն բացատրություն կստանանք խնդրի աղբյուրի մասին և կարող ենք արագ գտնել խանգարող բջիջները.

Նմանատիպ սխալներ տեղի կունենան, եթե զանգվածը դուրս գա թերթից կամ հարվածի միացված բջիջին: Եթե ​​դուք հեռացնեք խոչընդոտը, ապա ամեն ինչ անմիջապես կուղղվի թռիչքի ժամանակ:

Դինամիկ զանգվածներ և խելացի սեղաններ

Եթե ​​դինամիկ զանգվածը ցույց է տալիս «խելացի» աղյուսակը, որը ստեղծվել է ստեղնաշարի դյուրանցմամբ Ctrl+T կամ Գլխավոր – Ձևաչափել որպես աղյուսակ (Տուն — Ձևաչափել որպես աղյուսակ), այնուհետև այն կժառանգի նաև իր հիմնական որակը՝ ավտոմատ չափերը։

Ներքևում կամ աջ կողմում նոր տվյալներ ավելացնելիս խելացի աղյուսակը և դինամիկ տիրույթը նույնպես ինքնաբերաբար կձգվեն.

Այնուամենայնիվ, կա մեկ սահմանափակում. մենք չենք կարող օգտագործել դինամիկ տիրույթի հղում խելացի աղյուսակի ներսում գտնվող ֆորումներում.

Դինամիկ զանգվածներ և Excel-ի այլ հնարավորություններ

Լավ, դու ասում ես: Այս ամենը հետաքրքիր և զվարճալի է։ Կարիք չկա, ինչպես նախկինում, ձեռքով ձգել բանաձևը՝ հղում կատարելով սկզբնական տիրույթի առաջին բջիջին ներքև և աջ և այդ ամենը: Եվ այսքանո՞վ:

Ոչ այնքան:

Դինամիկ զանգվածները Excel-ի մեկ այլ գործիք չեն: Այժմ դրանք ներդրված են Microsoft Excel-ի սրտում (կամ ուղեղում)՝ դրա հաշվարկման շարժիչը: Սա նշանակում է, որ Excel-ի այլ բանաձևեր և գործառույթներ, որոնք մեզ ծանոթ են այժմ, նույնպես աջակցում են դինամիկ զանգվածների հետ աշխատելուն: Եկեք նայենք մի քանի օրինակների՝ ձեզ պատկերացում կազմելու տեղի ունեցած փոփոխությունների խորության մասին:

Փոխակերպել

Շրջանակ (տողեր և սյունակներ փոխանակելու համար) Microsoft Excel-ը միշտ ներկառուցված գործառույթ է ունեցել ՏՐԱՆՍՊ (ՏՐԱՆՍՊՈԶԵԼ). Այնուամենայնիվ, այն օգտագործելու համար նախ պետք է ճիշտ ընտրել արդյունքների միջակայքը (օրինակ, եթե մուտքագրումը եղել է 5×3 միջակայք, ապա դուք պետք է ընտրած լինեիք 3×5), ապա մուտքագրեք գործառույթը և սեղմեք կոճակը: համադրություն Ctrl+Հերթափոխություն+Մտնել, քանի որ այն կարող էր աշխատել միայն զանգվածային բանաձևի ռեժիմում:

Այժմ կարող եք պարզապես ընտրել մեկ բջիջ, մուտքագրել նույն բանաձևը և սեղմել նորմալի վրա Մտնել – դինամիկ զանգվածն ամեն ինչ ինքն իրեն կանի.

Բազմապատկման աղյուսակ

Սա այն օրինակն է, որը ես բերում էի, երբ ինձ խնդրեցին պատկերացնել զանգվածների բանաձևերի առավելությունները Excel-ում: Այժմ, ամբողջ Պյութագորասի աղյուսակը հաշվարկելու համար բավական է կանգնել B2 առաջին բջիջում, այնտեղ մուտքագրել երկու զանգված, որը բազմապատկում է երկու զանգված (ուղղահայաց և հորիզոնական թվերի շարք 1..10) և պարզապես սեղմել Մտնել:

Սոսնձում և պատյանների փոխակերպում

Զանգվածները կարող են ոչ միայն բազմապատկվել, այլև սոսնձվել ստանդարտ օպերատորի հետ և (իմպերսանդ): Ենթադրենք, որ մենք պետք է հանենք անունն ու ազգանունը երկու սյունակից և ուղղենք ցատկման գործը սկզբնական տվյալների մեջ: Մենք դա անում ենք մեկ կարճ բանաձևով, որը կազմում է ամբողջ զանգվածը, այնուհետև կիրառում ենք ֆունկցիան դրա վրա ՊՐՈՊՆԱԽ (ՊԱՏՇԱՃ)գրանցամատյանը կարգի բերելու համար.

Եզրակացություն Վերև 3

Ենթադրենք, մենք ունենք թվերի փունջ, որոնցից ցանկանում ենք դուրս բերել լավագույն երեք արդյունքները՝ դրանք դասավորելով նվազման կարգով։ Այժմ դա արվում է մեկ բանաձևով և կրկին առանց որևէ բանաձևի Ctrl+Հերթափոխություն+Մտնել ինչպես նախկինում.

Եթե ​​ցանկանում եք, որ արդյունքները տեղադրվեն ոչ թե սյունակում, այլ անընդմեջ, ապա բավական է այս բանաձևի երկու կետերը (տողերի բաժանարար) փոխարինել ստորակետով (տարրերի բաժանարար մեկ տողի մեջ)։ Excel-ի անգլերեն տարբերակում այս բաժանիչները համապատասխանաբար ստորակետեր և ստորակետեր են:

VLOOKUP-ը միաժամանակ մի քանի սյունակներ է հանում

Ֆունկցիաներ VPR- ը (VLOOKUP) այժմ դուք կարող եք արժեքներ քաշել ոչ թե մեկից, այլ միանգամից մի քանի սյունակից, պարզապես նշեք դրանց թվերը (ցանկացած կարգով) որպես զանգված ֆունկցիայի երրորդ արգումենտում.

OFFSET ֆունկցիան վերադարձնում է դինամիկ զանգված

Տվյալների վերլուծության համար ամենահետաքրքիր և օգտակար (VLOOKUP-ից հետո) ֆունկցիաներից մեկը ֆունկցիան է ՕՏԱՐՈՒՄ (ՕՖՍԵՏ), որին ժամանակին նվիրել էի իմ գրքի մի ամբողջ գլուխ և մի հոդված այստեղ։ Այս ֆունկցիան հասկանալու և յուրացնելու դժվարությունը միշտ եղել է այն, որ արդյունքում այն ​​վերադարձրել է տվյալների զանգված (տիրույթ), բայց մենք չենք կարող տեսնել այն, քանի որ Excel-ը դեռ չգիտեր, թե ինչպես աշխատել արկղից դուրս գտնվող զանգվածների հետ:

Հիմա այս խնդիրն անցյալում է։ Տեսեք, թե ինչպես այժմ, օգտագործելով մեկ բանաձև և OFFSET-ով վերադարձված դինամիկ զանգված, կարող եք ցանկացած տեսակավորված աղյուսակից հանել տվյալ արտադրանքի բոլոր տողերը.

Եկեք նայենք նրա փաստարկներին.

• A1 - մեկնարկային բջիջ (հղման կետ)
• ПОИСКПОЗ(F2;A2:A30;0) – մեկնարկային բջիջից դեպի ներքև՝ առաջին հայտնաբերված կաղամբի տեղաշարժի հաշվարկը:
• 0 – «պատուհանի» տեղաշարժը մեկնարկային բջիջի նկատմամբ դեպի աջ
  
• СЧЁТЕСЛИ(A2:A30;F2) – վերադարձված «պատուհանի» բարձրության հաշվարկ – գծերի քանակը, որտեղ կա կաղամբ:
• 4 — «պատուհանի» չափը հորիզոնական, այսինքն՝ 4 սյունակ

Նոր գործառույթներ դինամիկ զանգվածների համար

Բացի հին գործառույթներում դինամիկ զանգվածի մեխանիզմին աջակցելուց, Microsoft Excel-ում ավելացվել են մի քանի բոլորովին նոր գործառույթներ, որոնք սրվել են հատուկ դինամիկ զանգվածների հետ աշխատելու համար: Մասնավորապես, դրանք են.

• GRADE (ՏԵՍԱԿԱՎՈՐՈՒՄ) – տեսակավորում է մուտքային տիրույթը և արտադրում է դինամիկ զանգված ելքի վրա
  
• SORTPO (ԴԱՍԱՎՈՐԵԼ ԸՍՏ) - կարող է տեսակավորել մեկ միջակայքը մյուսից արժեքներով
  
• Ֆիլտր (ԶԻՏՐ) – առբերում է տողերը աղբյուրի տիրույթից, որոնք համապատասխանում են նշված պայմաններին
• ՅՈՒՆԻԿ (ԵՔՆԱԿԱՆ) – հանում է եզակի արժեքներ տիրույթից կամ հեռացնում կրկնօրինակները
  
• SLMASSIVE (ՌԱՆԴԱՐՐԱՅ) – առաջացնում է տվյալ չափի պատահական թվերի զանգված
• ՀԵՏԱԾՆՈՒՆԴ (ՀԵՐԹԱԿԱՆՈՒԹՅՈՒՆ) — տրված քայլով թվերի հաջորդականությունից զանգված է կազմում
  

Ավելին նրանց մասին – մի ​​փոքր ուշ: Նրանք արժե առանձին հոդված (և ոչ մեկ) խոհուն ուսումնասիրության համար 🙂

Եզրակացություններ

Եթե ​​կարդացել եք վերևում գրված ամեն ինչ, ապա կարծում եմ, որ արդեն գիտակցում եք տեղի ունեցած փոփոխությունների մասշտաբները։ Excel-ում այնքան շատ բաներ այժմ կարելի է անել ավելի հեշտ, հեշտ և ավելի տրամաբանական: Պետք է խոստովանեմ, որ ես մի փոքր ցնցված եմ, թե քանի հոդված այժմ պետք է ուղղել այստեղ, այս կայքում և իմ գրքերում, բայց ես պատրաստ եմ դա անել թեթեւ սրտով:

Ամփոփելով արդյունքները՝ Պլյուսները դինամիկ զանգվածներ, կարող եք գրել հետևյալը.

• Դուք կարող եք մոռանալ համադրության մասին Ctrl+Հերթափոխություն+Մտնել. Excel-ն այժմ տարբերություն չի տեսնում «սովորական բանաձևերի» և «զանգվածի բանաձևերի» միջև և նույն կերպ է վերաբերվում դրանց:
• Գործառույթի մասին SUMPRODUCT- ը (SUMPRODUCT), որը նախկինում օգտագործվում էր առանց զանգվածի բանաձևեր մուտքագրելու համար Ctrl+Հերթափոխություն+Մտնել Դուք կարող եք նաև մոռանալ. այժմ դա բավական հեշտ է Ամբողջ и Մտնել.
• Խելացի աղյուսակները և ծանոթ գործառույթները (SUM, IF, VLOOKUP, SUMIFS և այլն) այժմ նաև ամբողջությամբ կամ մասամբ աջակցում են դինամիկ զանգվածներին:
• Կա հետընթաց համատեղելիություն. եթե Excel-ի հին տարբերակում դինամիկ զանգվածներով աշխատանքային գիրք բացեք, դրանք կվերածվեն զանգվածի բանաձևերի (գանգուր փակագծերով) և կշարունակեն աշխատել «հին ոճով»:

Գտնվել է ինչ-որ համար մինուսները:

• Դուք չեք կարող ջնջել առանձին տողեր, սյունակներ կամ բջիջներ դինամիկ զանգվածից, այսինքն՝ այն ապրում է որպես մեկ ամբողջություն:
• Դուք չեք կարող դասավորել դինամիկ զանգվածը սովորական ձևով Տվյալների տեսակավորում (Տվյալներ — Դասավորել). Այժմ դրա համար հատուկ գործառույթ կա: GRADE (ՏԵՍԱԿԱՎՈՐՈՒՄ).
• Դինամիկ միջակայքը չի կարող վերածվել խելացի սեղանի (բայց դուք կարող եք դինամիկ միջակայք կազմել՝ հիմնվելով խելացի սեղանի վրա):
  

Իհարկե, սա վերջը չէ, և ես վստահ եմ, որ Microsoft-ը կշարունակի կատարելագործել այս մեխանիզմը ապագայում:

Որտեղ կարող եմ ներբեռնել:

Եվ վերջապես հիմնական հարցը 🙂

Microsoft-ը առաջին անգամ հայտարարեց և ցուցադրեց դինամիկ զանգվածների նախադիտումը Excel-ում դեռ 2018 թվականի սեպտեմբերին համաժողովում: Բոցավառվել. Հաջորդ մի քանի ամիսների ընթացքում տեղի ունեցավ նոր գործառույթների մանրակրկիտ փորձարկում և ներդրում, առաջին հերթին Կատուներ Microsoft-ի աշխատակիցները, այնուհետև Office Insiders-ի շրջանակից կամավոր փորձարկողների վրա: Այս տարի դինամիկ զանգվածներ ավելացնող թարմացումը սկսեց աստիճանաբար տարածվել Office 365-ի սովորական բաժանորդների համար: Օրինակ, ես այն ստացել եմ միայն օգոստոսին՝ իմ Office 365 Pro Plus (ամսական նպատակային) բաժանորդագրությամբ:

Եթե ​​ձեր Excel-ը դեռ չունի դինամիկ զանգվածներ, բայց դուք իսկապես ցանկանում եք աշխատել դրանց հետ, ապա կան հետևյալ տարբերակները.

• Եթե ​​ունեք Office 365 բաժանորդագրություն, կարող եք պարզապես սպասել, մինչև այս թարմացումը ձեզ հասնի: Թե որքան արագ դա տեղի կունենա, կախված է նրանից, թե որքան հաճախ են թարմացումները առաքվում ձեր գրասենյակ (տարին մեկ անգամ, վեց ամիսը մեկ, ամիսը մեկ անգամ): Եթե ​​դուք ունեք կորպորատիվ համակարգիչ, կարող եք խնդրել ձեր ադմինիստրատորին կարգավորել թարմացումները՝ ավելի հաճախ ներբեռնելու համար:
  
• Դուք կարող եք միանալ Office Insiders-ի այդ թեստային կամավորների շարքերին, այնուհետև դուք առաջինը կստանաք բոլոր նոր հնարավորություններն ու գործառույթները (բայց, իհարկե, Excel-ում խելագարության ավելացման հնարավորություն կա):
• Եթե ​​դուք չունեք բաժանորդագրություն, այլ Excel-ի առանձին տուփով տարբերակ, ապա ստիպված կլինեք սպասել մինչև 2022 թվականին Office-ի և Excel-ի հաջորդ տարբերակի թողարկումը, առնվազն: Նման տարբերակների օգտատերերը ստանում են միայն անվտանգության թարմացումներ և սխալների շտկում, և բոլոր նոր «լավությունները» այժմ գնում են միայն Office 365-ի բաժանորդներին: Տխուր, բայց իրական 🙂
  

Ամեն դեպքում, երբ ձեր Excel-ում հայտնվեն դինամիկ զանգվածներ, այս հոդվածից հետո դուք պատրաստ կլինեք դրան 🙂

• Ինչ են զանգվածային բանաձևերը և ինչպես օգտագործել դրանք Excel-ում
• Պատուհանի (միջակայքի) գումարում՝ օգտագործելով OFFSET ֆունկցիան
• Excel-ում աղյուսակը փոխադրելու 3 եղանակ