Dynamic Arrays и SPILL в Excel – как улесняват работата с данни?

Въведение

От 2019 г. насам, с въвеждането на Excel 365, Microsoft направи революция в обработката на данни с dynamic arrays и SPILL функционалността. Тези нововъведения отвориха врати за по-гъвкаво управление на данни, като премахнаха нуждата от сложни формули и ръчно копи-пействане. Днес ще ви разкажа какво точно представляват dynamic arrays и SPILL, какви ползи носят и как можете да ги ползвате за по-лесно обработване на данни.

Какво са Dynamic Arrays и SPILL?

Dynamic arrays са нов вид формули в Excel, които могат да връщат резултат, простиращ се в повече от една клетка. Това означава, че вместо да въвеждаме формула във всяка клетка поотделно или да я копираме надолу, формулата автоматично запълва необходимите клетки със своите резултати при добавяне или изтриване на изходните данни.

А с термина SPILL в Ексел се описва „разливането“ на резултати от dynamic arrays. Когато формулата ви е написана веднъж, резултатът „се разлива“ в свободни клетки надолу или надясно от формулата, колкото е необходимо според изходните данни. Но ако резултатът от формулата срещне съществуващи данни в някоя клетка, Excel ще покаже #SPILL! грешка. Малко като българските футболни треньори, които мрънкат, когато съдията свири фаул, само когато е срещу тях.

Нека да поясня горното с един пример. На таблицата по-долу виждате някакви измислени данни.

Нека предположим, че искате да вземете първите 3 букви от името на града в колона В. Трябва ви за нещо. Това е лесно, нали – в клетка F2 въвеждаме формулата =LEFT(B2,3) и след това я копираме до долу.

Когато се появят данни на ред 25 обаче, трябва да копираме формулата отново.

С динамичните формули вече можем да реферираме цялата област в колона В, че и празните клетки след нея. Използваме LEFT по същия начин, но вместо една клетка, първият аргумент е цялата колона.

Dynamic arrays

Последствията от това са няколко:

1. Резултатът от формулата се „разлива“ (SPILL) до края на колоната с данните. Ексел автоматично си разпознава къде свършват те. Няма нужда от Copy / Paste!

2. При добавяне на нова стойност в колона В – резултатът в колона F се появява без да има нужда да копираме формулата. Вече няма нужда да копираме формулата предварително няколко стотин или хиляди реда надолу „за всеки случай“

Dynamic arrays

3. Появява се т.нар. SPILL area – това е областта, в която Ексел „разлива“ резултата от динамичната формула. Познаваме, че има такава, когато ако кликнем в нея се появява една тънка синя линия около клетките.

Dynamic arrays

Обърнете също така внимание, че формулата в лентата за формули не е черна, а сива. Това е така, защото динамичните формули се въвеждат и редактират само в първата клетка от областта. Съответно, ако искате да изтриете формулата – може да го направите само в първата клетка. Колкото и да биете Delete бутона – няма да успеете да изтриете друга клетка. Просто там няма формула, а само резултат

Вижте какво се случва обаче, ако случайно нещо се изпречи на пътя на разлива:

#SPILL!

#SPILL! е нов вид грешка в Ексел, която означава, че има данни там, където динамична формула иска да върне резултат.

Основните функции с Dynamic Arrays

Истинската сила и полза на динамичните формули се проявява при използването на специалните динамични функции. Първоначално Майкрософт въведоха няколко основни такива: UNIQUE, FILTER, SORT, SORTBY и SEQUENCE. Постепенно добавят още и още, но тук ще се съсредоточим върху базовите.

UNIQUE

Функцията UNIQUE връща уникалните стойности от даден диапазон. С нея можете бързо да видите списък с уникални имена или градове от таблицата.

Пример: Да кажем, че искаме да извлечем уникалните градове, които се срещат в колоната „Град“.

Dynamic arrays

FILTER

С FILTER можете да извлечете само тези редове, които отговарят на конкретни условия.

Пример: Ако искаме да видим всички записи, в които „Сума“ е над 500, използваме FILTER.

Dynamic arrays

SORT

SORT ни помага да подредим данните в дадена последователност.

Пример: Да кажем, че искаме да подредим таблицата по стойностите в колоната „Сума“ в низходящ ред, така че най-големите суми да са най-отгоре.

Dynamic arrays

SORTBY

SORTBY дава възможност за подреждане по стойности от друга колона, различна от тази в самия диапазон. Разликата е, че можем да вземем само определена колона или колони, а не цялата изходна таблица както със SORT

Пример: Ако искаме да вземем само колоната с имена и да я подредим въз основа на стойностите в колоната „Брой“, използваме SORTBY.

Dynamic arrays

Тези примери демонстрират силата на dynamic arrays и как те улесняват работата ни с големи обеми от данни в Excel. Вече може да филтрирате и сортирате с лекота, без нужда от сложни функции или ръчно подреждане.

SEQUENCE

SEQUENCE е последната от базовите динамични функции. Тя просто създава някакава последователност от стойности.

Пример: Ако искаме да добавим колона с пореден номер към оригиналната таблица, можем да използваме SEQUENCE:

Dynamic arrays

Ползи и нови възможности на Dynamic Arrays и SPILL

Dynamic arrays и SPILL позволяват на Excel потребителите да се справят с данни далеч по-бързо и лесно. Какви са основните предимства?

  • Спестяване на време: Dynamic arrays намаляват нуждата от копиране и поставяне на формули – те се „разливат“ автоматично.
  • По-малко грешки: Резултатите се актуализират динамично – промените в първичните данни автоматично обновяват и крайния резултат.
  • Оптимизация на процесите: Вече можете да изпълнявате сложни операции, като филтриране и сортиране, директно с формули без необходимост от ръчни операции.
Публикувано в Формули. Постоянна връзка.