#66 (2/3) Групиране (нинджа с Office 365)

    Тук не е за хора със слаби нерви и за хора, които са на ниво IF() и Vlookup():) Та бременни и деца под 18 да не четат:) Шегувам се;) Точно младежите са тези, които може до пенсионирането си да изучат всички неща в Excel:) Но се съмнявам в това, ако Microsoft продължат да бълват новости със същата скорост.

    Та както се вижда от заглавието за да реализирате това решение трябва да разполагате с нов меч. Трябва да имате инсталиран Office 365, който да е обновен поне към края на 2022 година!

Стъпка 1: В клетка A1 въвеждате формулата : 

=REDUCE({"Направление"\"Часове на тръгване"}; SORT(UNIQUE(Data[Направление])); LAMBDA(acc; town; VSTACK(acc; HSTACK(town; TEXTJOIN(", ";;SORT(UNIQUE(CHOOSECOLS(FILTER(Data; Data[Направление]=town);1))))))))

и ....

това е! 😮 

    Всичко е готово барабар със заглавията на колоните и динамично ще се опреснява, ако променяте таблицата с първични данни! Няма нужда от копиране, акробатики с Ctrl+Shift+Enter и т.н.! Найс а? 💪

Резултат от формулата

    Само дето тази формула е много, ма много различна от базовия курс по Excel. Но спокойно. Ако не ме обхване пак творческия мързел лека полека ще ви помагам да усвоявате новите неща. Не се шашкайте. Дори да не разберете напълно някои неща, ще има по-леки теми, в които функциите ще бъдат използвани в по-леки за разбиране сценарии.

    Ще се опитам да "разглобя" тази дълга формула и обясня отделните и парчета и тяхното взаимодействие.

    Забележка: Прочетете внимателно статията за разделителите #41 Разделители и масиви за да няма изненади, породени от различните настройки на Windows. Най-добре изтеглете примерната работна книга за да видите как изглеждат формулите при вас (при зареждане на раб. книга се отчитат локалните настройки и формулите  се настройват спрямо тези настройки!).

    Дисекция 0: Данните се намират в таблица с име Data. Таблицата има две колонки: "Час на тръгване" и "Направление". Важно е да се отбележи реда на колонките, който е важен за правилната работа на формулата!! 

    Дисекция 1:  =REDUCE({"Направление"\"Часoве на тръгване"}; SORT( UNIQUE(Data[Направление])) ; LAMBDA(.......) )

    Функцията Reduce() служи за натрупване на резултатът от обработката на елементи на масив в една променлива (нарича се "акумулатор"). За всеки елемент от масива се изпълнява определена функция. Функцията Redice има три параметъра:

    - начална стойност на акумулатора. В нашия случай това е заглавния ред на резултата - масив от две КОЛОНИ (виж отново #41!!): {"Направление"\"Часoве на тръгване"}

-     масив от стойности, които се обработват. В нашия случай подаваме сортиран списък, съдържащ уникалните направления. SORT( UNIQUE(Data[Направление])).

Резултат на Sort(Unique())

- действие, което обработва елементите от масива. Декларирането на действието става чрез функцията Lamda().

Дисекция 2: LAMBDA(acc; town; VSTACK())

    Функцията Lambda служи за деклариране на функция! :):):) Спокойно! Ще имаме отделна тема за това. В общи линии, Lambda описва входните параметри и самото действие за обработването на входа. Reduce изисква да се опише функция с ДВА входни параметъра:
    - входен параметър, съдържащ текущата стойност на акумулатора  (в нашия случай съм го кръстил "acc")
    - входен параметър, задържащ стойността на текущия елемент на входния масив, който обработваме. В нашия случай съм кръстил този параметър "town" (може би по-правилното име е "destination", но името няма значение!).
    Последния параметър на Lambda е самото действие, което ще се изпълнява за всеки елемент  на входния масив (всяко направление).

Дисекция 3: VSTACK(acc; HSTACK(town; TEXTJOIN()))

Обработка на елементите на масива

    За всеки град се изпълнява VStack. Функцията служи за добавяне на елемент(и) ПОД елементите на даден масив. Т.е. се извършва В(ето от къде е V-то)ертикално слепване на масиви. В нашия случай към текущото състояние (стойността на акумулатора acc) се добавя един ред за направлението. Редът се състои от две клетки: направлението (town) и часовете за тръгване (получават се чрез функцията TextJoin). Функцията HStack слепва масиви Х(H)оризонтално!  Т.е. за всяко направление правим масив от две клетки (направление и часове) и получения масив(ред) го долепяме под елементите на текущото решение!

    Дисекция 4: TEXTJOIN(",  ";; SORT(UNIQUE(CHOOSECOLS( FILTER(Data; Data[Направление]=town); 1))))

Тази част от функцията служи за получаване на клетката с часовете на тръгване.

- FILTER(Data; Data[Направление]=town)  - филтрира само редовете, които са за текущото направление. Резултатът е масив от две колонки!

- CHOOSECOLS(FILTER();1) - връща само първата колонка (колонката с часовете на тръгване). За това е важно да се знае подредбата на входните данни!.

- SORT(UNIQUE(CHOOSECOLS()))- премахва дублажите и сортира. Ако сте сигурни, че във входните данни няма дублирани часове в едно направление, може да махнете Unique!

- TEXTJOIN(",  ";; SORT()) - Слепва (като текст) данните от масива като за разделител използва запетая (първия параметър на функцията). Втория параметър на функцията е начина за работа с празните елементи на масива и използваме стойността по подразбиране. За това има два разделителя (;;)! (Честно казано, това не е добър стил и по-добре явно да задавате стойност на параметрите!).

Ми това е дисекцията на формулата. Кой разбрал, разбрал:) Който иска да пита да пише коментар или да ми пише мейл:)

    Ъпгрейд: По принцип е тъпа (дали?) идея да генерирате и заглавието на самата таблица. За това може да изтриете (дроп-нете) първия ред от резултата! Но тогава си губи и смисъл да задавате и конкретна начална стойност на акумулатора! Ето и "подобрения" вариант на функцията и нейния резултат:

=DROP(REDUCE("";SORT(UNIQUE(Data[Направление]));LAMBDA(acc; town; VSTACK(acc; HSTACK(town; TEXTJOIN(", ";; SORT(UNIQUE(CHOOSECOLS(FILTER(Data; Data[Направление]=town);1))))))));1)

Резултат от "подобрения" вариант

П.П. Работната книга с решенията се намира на  адрес работна книга GroupBy.