Садржај
Шта су динамички низови
У септембру 2018, Мицрософт је објавио ажурирање које додаје потпуно нову алатку у Мицрософт Екцел: динамичке низове и 7 нових функција за рад са њима. Ове ствари, без претеривања, радикално мењају сву уобичајену технику рада са формулама и функцијама и забрињавају, буквално, сваког корисника.
Размотрите једноставан пример да објасните суштину.
Претпоставимо да имамо једноставну табелу са подацима о градовима-месецима. Шта ће се догодити ако одаберемо било коју празну ћелију на десној страни листа и унесемо у њу формулу која се не везује за једну ћелију, већ одмах за опсег?
У свим претходним верзијама Екцел-а, након клика на унети добили бисмо садржај само једне прве ћелије Б2. Како другачије?
Па, или би било могуће умотати овај опсег у неку врсту функције агрегирања као што је =СУМ(Б2:Ц4) и добити укупни зброј за то.
Ако су нам биле потребне сложеније операције од примитивне суме, као што је издвајање јединствених вредности или Топ 3, онда бисмо морали да унесемо формулу као формулу низа користећи пречицу на тастатури Цтрл+смена+унети.
Сада је све другачије.
Сада након уноса такве формуле, можемо једноставно кликнути на унети – и као резултат одмах добијете све вредности уКСНУМКСбуКСНУМКСб на које смо упућивали:
Ово није магија, већ нови динамички низови које сада има Мицрософт Екцел. Добродошли у нови свет 🙂
Особине рада са динамичким низовима
Технички, цео наш динамички низ се чува у првој ћелији Г4, испуњавајући потребни број ћелија десно и доле својим подацима. Ако изаберете било коју другу ћелију у низу, онда ће веза у траци формуле бити неактивна, показујући да се налазимо у једној од „дете“ ћелија:
Покушај брисања једне или више „подређених“ ћелија неће довести до ничега – Екцел ће их одмах поново израчунати и попунити.
У исто време, можемо безбедно да се позивамо на ове „детеће“ ћелије у другим формулама:
Ако копирате прву ћелију низа (на пример, од Г4 до Ф8), онда ће се цео низ (његове референце) кретати у истом правцу као у редовним формулама:
Ако треба да померимо низ, онда ће бити довољно да померимо (мишем или комбинацијом Цтрл+X, Цтрл+V), опет само прва главна ћелија Г4 – после ње ће бити пребачена на ново место и цео наш низ ће се поново проширити.
Ако је потребно да се негде другде на листу упутите на креирани динамички низ, онда можете да користите специјални знак # („фунта“) после адресе његове водеће ћелије:
На пример, сада можете лако да направите падајућу листу у ћелији која се односи на креирани динамички низ:
Грешке динамичког низа
Али шта се дешава ако нема довољно простора за проширење низа или ако на његовом путу постоје ћелије које су већ заузете другим подацима? Упознајте фундаментално нову врсту грешака у Екцел-у – #ТРАНСФЕР! (#ПРОПУСТИ!):
Као и увек, ако кликнемо на икону са жутим дијамантом и знаком узвика, добићемо детаљније објашњење извора проблема и брзо ћемо пронаћи ћелије које ометају:
Сличне грешке ће се појавити ако низ оде са листа или погоди спојену ћелију. Ако уклоните препреку, све ће се одмах исправити у ходу.
Динамички низови и паметне табеле
Ако динамички низ указује на „паметну“ табелу креирану пречицом на тастатури Цтрл+T или Почетна – Формат као табела (Почетна — Формат као табела), онда ће наследити и свој главни квалитет – аутоматско одређивање величине.
Када додајете нове податке на дно или на десно, паметна табела и динамички опсег ће се такође аутоматски проширити:
Међутим, постоји једно ограничење: не можемо користити референцу динамичког опсега у форумима унутар паметне табеле:
Динамички низови и друге функције Екцел-а
У реду, кажеш. Све ово је занимљиво и смешно. Нема потребе, као раније, да ручно растежете формулу са референцом на прву ћелију оригиналног опсега надоле и удесно и све то. И то је све?
Не баш.
Динамички низови нису само још један алат у Екцел-у. Сада су уграђени у само срце (или мозак) Мицрософт Екцел-а – његовог механизма за израчунавање. То значи да и друге Екцел формуле и функције које су нам познате сада такође подржавају рад са динамичким низовима. Хајде да погледамо неколико примера да бисмо вам дали представу о дубини промена које су се десиле.
Транспосе
За транспоновање опсега (замена редова и колона) Мицрософт Екцел је увек имао уграђену функцију ТРАНСП (ТРАНСПОРУЧИ). Међутим, да бисте га користили, прво морате исправно да изаберете опсег за резултате (на пример, ако је унос био опсег од 5×3, онда морате да изаберете 3×5), затим унесите функцију и притисните тастер комбинација Цтрл+смена+унети, јер би могао да ради само у режиму формуле низа.
Сада можете само да изаберете једну ћелију, унесете исту формулу у њу и кликнете на нормалну унети – динамички низ ће све урадити сам:
Таблица множења
Ово је пример који сам дао када су ме замолили да визуелизујем предности формула низа у Екцел-у. Сада, да бисте израчунали целу Питагорину табелу, довољно је стати у прву ћелију Б2, унети ту формулу која множи два низа (вертикални и хоризонтални скуп бројева 1..10) и једноставно кликнути на унети:
Лепљење и конверзија кућишта
Низови се не могу само множити, већ и залепити заједно са стандардним оператором & (амперсандом). Претпоставимо да треба да издвојимо име и презиме из две колоне и исправимо велика и мала слова у оригиналним подацима. Ово радимо са једном кратком формулом која формира цео низ, а затим примењујемо функцију на њега ПРОПНАЦХ (ПРАВИЛНО)да средим регистар:
Закључак Топ 3
Претпоставимо да имамо гомилу бројева из којих желимо да изведемо прва три резултата, распоређујући их у опадајућем редоследу. Сада се то ради по једној формули и, опет, без икакве Цтрл+смена+унети као пре:
Ако желите да се резултати не постављају у колону, већ у ред, довољно је да у овој формули двотачке (разделник редова) замените тачком и зарезом (разделник елемената унутар једног реда). У енглеској верзији Екцел-а, ови сепаратори су тачке и зарези.
ВЛООКУП издваја више колона одједном
Функције ВПР (ВЛООКУП) сада можете повући вредности не из једне, већ из неколико колона одједном - само наведите њихове бројеве (у било ком жељеном редоследу) као низ у трећем аргументу функције:
ОФФСЕТ функција враћа динамички низ
Једна од најзанимљивијих и најкориснијих (после ВЛООКУП) функција за анализу података је функција ОДЛАГАЊЕ (ОФСЕТ), коме сам својевремено посветио једно цело поглавље у својој књизи и један чланак овде. Тешкоћа у разумевању и савладавању ове функције је увек била то што је као резултат враћала низ (опсег) података, али ми то нисмо могли да видимо, јер Екцел још увек није знао како да ради са низовима ван кутије.
Сада је овај проблем у прошлости. Погледајте како сада, користећи једну формулу и динамички низ које враћа ОФФСЕТ, можете издвојити све редове за дати производ из било које сортиране табеле:
Хајде да погледамо њене аргументе:
- A1 – почетна ћелија (референтна тачка)
- ПОИСКПОЗ(Ф2;А2:А30;0) – прорачун померања од почетне ћелије надоле – до првог пронађеног купуса.
- 0 – померање „прозора“ удесно у односу на почетну ћелију
- СЧЕТЕСЛИ(А2:А30;Ф2) – обрачун висине враћеног „прозора” – број редова на којима има купуса.
- 4 — величина „прозора“ хоризонтално, тј. излаз 4 колоне
Нове функције за динамичке низове
Поред подршке механизму динамичког низа у старим функцијама, неколико потпуно нових функција је додато у Мицрософт Екцел, изоштрених посебно за рад са динамичким низовима. Конкретно, ово су:
- РАЗРЕД (ВРСТА) – сортира опсег улаза и производи динамички низ на излазу
- СОРТПО (СОРТИРАЈ ПО) – може сортирати један опсег по вредностима из другог
- ФИЛТЕР (ФИЛТЕР) – преузима редове из изворног опсега који испуњавају наведене услове
- УНИК (ЈЕДИНСТВЕНА) – издваја јединствене вредности из опсега или уклања дупликате
- СЛМАССИВЕ (РАНДАРРАИ) – генерише низ случајних бројева дате величине
- АФТЕРБУРТХ (НИЗ) — формира низ од низа бројева са датим кораком
Више о њима - мало касније. Они су вредни посебног чланка (а не једног) за пажљиво проучавање 🙂
Закључци
Ако сте прочитали све горе написано, онда мислим да већ схватате размере промена које су се десиле. Толико ствари у Екцел-у сада може да се уради лакше, лакше и логичније. Морам признати да сам мало шокиран колико ће сада чланака морати да се исправљају овде, на овом сајту и у мојим књигама, али сам спреман да то урадим с лакоћом.
Сумирајући резултате, плусес динамичке низове, можете написати следеће:
- Можете заборавити на комбинацију Цтрл+смена+унети. Екцел сада не види разлику између „обичних формула“ и „формула низа“ и третира их на исти начин.
- О функцији СУМПРОДУЦТ (СУМПРОИЗВОД), који се раније користио за унос формула низа без Цтрл+смена+унети можете и заборавити – сада је довољно лако СУМ и унети.
- Паметне табеле и познате функције (СУМ, ИФ, ВЛООКУП, СУМИФС, итд.) сада такође у потпуности или делимично подржавају динамичке низове.
- Постоји компатибилност уназад: ако отворите радну свеску са динамичким низовима у старој верзији Екцел-а, они ће се претворити у формуле низа (у витичастим заградама) и наставити да раде у „старом стилу“.
Нашао неки број минуси:
- Не можете избрисати појединачне редове, колоне или ћелије из динамичког низа, тј. живи као један ентитет.
- Не можете сортирати динамички низ на уобичајен начин Подаци – сортирање (Подаци — сортирање). Сада постоји посебна функција за ово. РАЗРЕД (ВРСТА).
- Динамички опсег се не може претворити у паметни сто (али можете направити динамички опсег на основу паметног стола).
Наравно, ово није крај и сигуран сам да ће Мицрософт наставити да побољшава овај механизам у будућности.
Где могу да преузмем?
И на крају, главно питање 🙂
Мицрософт је први пут најавио и показао преглед динамичких низова у Екцелу још у септембру 2018. на конференцији Запалити. У наредних неколико месеци, прво је било темељног тестирања и увођења нових функција мачке запосленима самог Мајкрософта, а затим и на тестерима волонтерима из круга Оффице Инсајдера. Ове године, ажурирање које додаје динамичке низове почело је постепено да се уводи редовним претплатницима на Оффице 365. На пример, добио сам га тек у августу са својом претплатом на Оффице 365 Про Плус (месечно циљано).
Ако ваш Екцел још увек нема динамичке низове, али заиста желите да радите са њима, постоје следеће опције:
- Ако имате претплату на Оффице 365, можете једноставно да сачекате док ово ажурирање не стигне до вас. Колико брзо ће се то десити зависи од тога колико често се ажурирања испоручују вашој канцеларији (једном годишње, једном у шест месеци, једном месечно). Ако имате корпоративни рачунар, можете затражити од администратора да подеси ажурирања која ће се чешће преузимати.
- Можете се придружити редовима оних волонтера за тестирање Оффице Инсидерс – тада ћете бити први који ће добити све нове функције и функције (али постоји шанса за повећање грешака у Екцел-у, наравно).
- Ако немате претплату, већ самосталну верзију Екцел-а у кутији, онда ћете морати да сачекате до објављивања следеће верзије Оффице-а и Екцел-а најмање 2022. године. Корисници таквих верзија добијају само безбедносна ажурирања и исправке грешака, а све нове „добре“ сада иду само претплатницима на Оффице 365. Тужно али истинито 🙂
У сваком случају, када се динамички низови појаве у вашем Екцел-у – након овог чланка, бићете спремни за то 🙂
- Шта су формуле низа и како их користити у Екцел-у
- Прозор (опсег) сумирање помоћу функције ОФФСЕТ
- 3 начина за транспоновање табеле у Екцел-у