змест
Што такое дынамічныя масівы
У верасні 2018 года Microsoft выпусціла абнаўленне, якое дадае ў Microsoft Excel цалкам новы інструмент: дынамічныя масівы і 7 новых функцый для працы з імі. Гэтыя рэчы, без перабольшання, кардынальна мяняюць усю звыклую тэхніку працы з формуламі і функцыямі і хвалююць, літаральна, кожнага карыстальніка.
Разгледзім просты прыклад для тлумачэння сутнасці.
Дапусцім, у нас ёсць простая табліца з дадзенымі па гарадах-месяцах. Што будзе, калі вылучыць любую пустую ячэйку справа на аркушы і ўвесці ў яе формулу, якая спасылаецца не на адну ячэйку, а адразу на дыяпазон?
Ва ўсіх папярэдніх версіях Excel пасля націску на Уводзіць мы атрымаем змесціва толькі адной першай ячэйкі B2. Як інакш?
Ну, ці можна было б абгарнуць гэты дыяпазон у якую-небудзь агрэгавальную функцыю накшталт =SUM(B2:C4) і атрымаць для гэтага агульны вынік.
Калі нам патрэбныя больш складаныя аперацыі, чым прымітыўная сума, такія як выманне унікальных значэнняў або Топ-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 выцягвае адразу некалькі слупкоў
функцыі ВПР (ВПР) цяпер вы можаце выцягваць значэння не з аднаго, а адразу з некалькіх слупкоў - проста пакажыце іх нумары (у любым жаданым парадку) у выглядзе масіва ў трэцім аргументе функцыі:
Функцыя OFFSET, якая вяртае дынамічны масіў
Адной з самых цікавых і карысных (пасля VLOOKUP) функцый для аналізу дадзеных з'яўляецца функцыя ВЫКАРЫСТАННЕ (ЗРУШЭННЕ), якому я ў свой час прысвяціў цэлы раздзел у сваёй кнізе і артыкул тут. Цяжкасць у разуменні і асваенні гэтай функцыі заўсёды заключалася ў тым, што яна ў выніку вяртала масіў (дыяпазон) даных, але мы не маглі яго ўбачыць, таму што Excel яшчэ не ведаў, як працаваць з масівамі з скрынкі.
Цяпер гэтая праблема ў мінулым. Паглядзіце, як зараз, выкарыстоўваючы адзіную формулу і дынамічны масіў, які вяртае OFFSET, вы можаце атрымаць усе радкі для дадзенага прадукту з любой адсартаванай табліцы:
Давайце паглядзім на яе аргументы:
- A1 – пачатковая клетка (пункт адліку)
- ПОИСКПОЗ(F2;A2:A30;0) – разлік зруху ад пачатковай клеткі ўніз – да першай знойдзенай капусты.
- 0 – зрух «акна» ўправа адносна пачатковай клеткі
- СЧЭТЫ(A2:A30;F2) – разлік вышыні вяртанага “акна” – колькасці радкоў, дзе ёсць капуста.
- 4 — памер «акна» па гарызанталі, г. зн. вывесці 4 слупкі
Новыя функцыі для дынамічных масіваў
Акрамя падтрымкі механізму дынамічнага масіва ў старых функцыях, у Microsoft Excel было дададзена некалькі цалкам новых функцый, заточаных спецыяльна для працы з дынамічнымі масівамі. У прыватнасці, гэта:
- GRADE (сартаваць) – сартуе ўваходны дыяпазон і стварае дынамічны масіў на выхадзе
- САРТПО (САРТАВАЦЬ ПА) – можа сартаваць адзін дыяпазон па значэннях з іншага
- Фільтр (ФІЛЬТР) – здабывае радкі з зыходнага дыяпазону, якія адпавядаюць зададзеным умовам
- УНІК (УНІКАЛЬНЫ) – здабывае унікальныя значэнні з дыяпазону або выдаляе дублікаты
- МАСІВЫ (выпадковы шэраг) – генеруе масіў выпадковых лікаў зададзенага памеру
- ПАСЛЯРОДЫ (ПАСЛЯДОЎНАСЦЬ) — фарміруе масіў з паслядоўнасці лікаў з зададзеным крокам
Больш падрабязна пра іх – крыху пазней. Яны вартыя асобнага артыкула (і не адной) для ўважлівага вывучэння 🙂
Высновы
Калі вы прачыталі ўсё напісанае вышэй, то, думаю, вы ўжо ўсьведамляеце маштаб зьменаў, якія адбыліся. Так шмат рэчаў у Excel цяпер можна рабіць прасцей, лягчэй і лагічней. Я павінен прызнацца, што я крыху шакаваны тым, колькі артыкулаў цяпер трэба будзе выправіць тут, на гэтым сайце і ў маіх кнігах, але я гатовы зрабіць гэта з лёгкім сэрцам.
Падводзячы вынікі, плюсы дынамічныя масівы, вы можаце напісаць наступнае:
- Аб спалучэнні можна забыцца Ctrl+Зрух+Уводзіць. Цяпер Excel не бачыць розніцы паміж «звычайнымі формуламі» і «формуламі масіваў» і разглядае іх аднолькава.
- Аб функцыі SUMPRODUCT (SUMPRODUCT), які раней выкарыстоўваўся для ўводу формул масіва без Ctrl+Зрух+Уводзіць вы таксама можаце забыцца - цяпер гэта досыць проста SUM и Уводзіць.
- Разумныя табліцы і знаёмыя функцыі (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 у скрынцы, то вам давядзецца пачакаць як мінімум да выпуску наступнай версіі Office і Excel у 2022 годзе. Карыстальнікі такіх версій атрымліваюць толькі абнаўленні бяспекі і выпраўленні памылак, а ўсе новыя «прыналежнасці» цяпер дастаюцца толькі падпісчыкам Office 365. Сумна, але праўда 🙂
У любым выпадку, калі ў вашым Excel з'явяцца дынамічныя масівы - пасля гэтага артыкула вы будзеце да гэтага гатовыя 🙂
- Што такое формулы масіваў і як іх выкарыстоўваць у Excel
- Сумаванне вокны (дыяпазону) з дапамогай функцыі OFFSET
- 3 спосабу транспанавання табліцы ў Excel