#68 (1/2) Как се цепи текст ... с функции (или как съм икономисвал шоколад)

    Пишейки #67 се замислих за по-полезни приложения на Let. И се сетих за задачката, за чието решение съм обещавал шоколад на студентите. Казвам "обещавал", защото май никога някой е давал решение. В крайна сметка шоколада го получаваше някоя симпатична студентка, но вече отиваме в много неполиткоректна тематика:)

    Условие: Да се извлече само презимето от три имена на човек. За целта да се използват само функции и да не се използват междинни клетки!

    Както разбирате от условието съм бил голяма скръндза и ми се е свидил шоколад:) Но в крайна сметка идеята е студентите да си мръднат мозъците. Разбира се, задачата не е изключително сложна и когато се покаже решението всеки казва "Ахаа. Елементарно е!". 

    1. Класическо решение. Това е универсално решение, което работи на всички версии на Excel. Това и е "шоколаденото" решение, което съм искал. След малко ще видим, че има и други решения, базирани на по-новите функции в новите версии на Excel.

Ето самото решение:

=MID(B2;FIND(" "; B2)+1;FIND(" "; B2; FIND(" "; B2)+1)-FIND(" "; B2)-1)

    Ето първо на картинка за какво иде реч:

Извличане на презиме

     За извличане на презимето ни трябват неговите "ограничители". Позициите на двата интервала.

- позицията на първия интервал се намира лесно с функцията Find (или Search) - > FIND(" "; B2)

- позицията на втория интервал изисква да се знае позицията на първия интервал и търси СЛЕД него -> FIND(" "; B2; FIND(" "; B2)+1)

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

- началната позиция е позицията на първия интервал + 1 (следващия след интервала символ)

- дължината е позицията на втория минус позицията на първия - 1 (разгледайте добре фигурата защо е така!).

    2. Класическо решение с Let. Решението в точка 1 изисква много внимание, защото е дълго, с много скоби, и това го прави трудно за сглобяване и анализ. Всъщност, това решение го използвам за илюстрация за необходимостта от помощни клетки (колонки), които наричам "помощни колелца за учене на каране на велосипед". Или в сегашния случай от необходимостта от Let! Новият вариант на формулата изглежда така:

=LET(f; FIND(" "; B2); s; FIND(" "; B2; f+1); MID(B2; f+1; s-f-1))

    Дефинирани са две променливи (f и s (ако искате формулата да стане по-разбираема, може да кръстите променливите с по-описателни имена, например first и second), съдържащи позициите на двата интервала. Обърнете внимание, че за дефиницията на s е използвана стойността на f, което намалява възможните грешки! В описанието на една променлива може да участват стойностите на вече дефинирани променливи!

    Както се вижда, това решение е много по структурирано и разбираемо. Не трябва да се забравя факта, че Let се появи чак в Office 2021!! Следващите решения са още по-лесни, но и изискват Office 365!

3. Решение с TextBefore и TextAfter. В Office 365 се появиха множество нови функции за работа с текст. Функциите TextBefore и TextAfter позволяват да се върне част от текста спрямо определен разделител. Извличането на презимето с помощта на тези функции става по следния начин:

 =TEXTBEFORE(TEXTAFTER(B2;" ");" ")

    Чрез функцията TextAfter се извличат презимето и фамилията (текстът след първия интервал), а чрез TextBefore извличаме презимето (текстът преди интервала в презимето и фамилията)!

    Забележка: Ако имаме за задача да извлечем фамилията, това може да стане само чрез еднократно извикване TextAfter:

=TEXTAFTER(B2;" ";2) или =TEXTAFTER(B2;" "; -1)  

Извличаме текста след втория интервал. С отрицателен знак се броят отдясно наляво! Т.е. -1 е последния интервал!

4. Решение с TextSplit. Функцията TextSplit (също нова в Office365!) служи за разделяне на даден текст спрямо разделител или списък от разделители. Резултатът от функцията са ВСИЧКИ елементи на текста. Това, означава, че функцията "протича" извън клетката (ще имаме отделна тема за "протичането"). Ако искаме само част от резултатите (в нашия случай  само презимето) функцията се "пакетира" с Index, ChooseCols, ChooseRows за филтриране на необходимите резултати. 

За извличане на презимето се използва:

=INDEX(TEXTSPLIT(B2;" "); 2)

=CHOOSECOLS(TEXTSPLIT(B2;" ");2)

Забележка: Както се вижда от примерите, функциите Index и ChooseCols/Rows използват "нормално" броене от единица! В нашия случай ни трябва втория елемент на резултата! 

Еми това е! 

П.П. Сега ще трябва да измислям друга задача за да (не) давам шоколад! :)

#67 Да съкращаваме (или удължаваме?!) с Let

    В последните версии се появи странната функция Let. Казвам странна, защото и аз първоначално се замислих защо ми е това. Например, имаме следната формула: =IF(K4>=42;K4;42), чрез която сравняваме стойността на клетка (в случая K4) с някаква стойност (в случая 42) и ако тя е под нея връща тази стойност. Дет се вика "Таквиз формули сме писали хиляди!". И наистина си е нормална функция IF. Но... прави впечатление, че стойността 42 я има два пъти, което може да подразни всеки програмист, който е учен (с бой) да си именува константите. Явно скучаейки, някой програмист в Microsoft е достигнал до извода, че в Excel няма как да се дефинират константи и променливи. И е създал функцията Let, която има следния синтаксис:

 Let(име1;с-ст; име2; с-ст; име3; стойност....; израз).

    Т. е. може да си дефинираме имена, които може да използваме в израза. По този начин формулите стават много по-добре четими и разбираеми. Умишлено не казвам, че функциите стават по-кратки, защото в някои случаи те се удължават. Но време е да се научим да пишем четими формули! И честно ще използвам Let от тук нататък. Формулата, която използвах за пример, може да бъде "опакована" чрез Let по следния начин:

=LET(val;K4;min;42; IF(val>=min; val; min))

    Във функцията се дефинират две имена (val и min), които се използват в израза. Функцията става по-четима и е по-лесно да се коригира, ако се наложи промяна на минималната стойност.

    Пример: Да се изведе годината на раждане на човек според неговия Единен Граждански Номер (ЕГН). Както знаете (или не знаете), че за икономия на цифри за годината на раждане се е наложило да се прави гимнастика с номера на месеца. Ако човек е раждан през XX-ти век (19хх година), номерът на месеца не е променян. Ако е раждан през XIX век към номера на месеца е добавено 20, а ако е раждане след 2000 година - към номера на месеца е добавено 40. 

T.e.

7602 - Човек, раждан през 1976 г. (месец февруари)

8722 - Човек, раждан през 1897 г. (месец февруари)

0242 - Човек, раждан през 2002 г. (месец февруари)

    Та, използвайки Let, ето и самото решение (Данните са в таблица и колонката се казва ЕГН. За това и формулата е с таблични препратки):

=LET(y;VALUE(LEFT([@ЕГН];2));m;VALUE(MID([@ЕГН];3;2));IFS(m>40;y+2000;m>20;y+1800;TRUE;y+1900))

    Дефинирани са две имена (y и m), които съдържат годината (първите две цифри) и месеца (следващите две цифри) от ЕГН-то. Тъй като ЕГН-то е представено като текст, се налага да се използва функцията Value за да се преобразува в числова стойност. Чрез функцията IFS (вижте  https://yuriy-excel.blogspot.com/2018/11/) се извършва получаването на реалната година на раждане.

    Та, когато имате нужда да си направите формулите по-разбираеми или когато имате нужда от помощна колонка, използвайте Let! 

Успех!

#66 (3/3) Групиране (Power Query)

    Остана да видим и последното решение на задачката с групирането на текстови данни. За целта ще използваме Power Query. Инструментът е част от Power BI решенията на Microsoft и освен в Excel го има в Pоwer BI Desktop и други Power BI решения, които имат за задача да импортират и подготвят данни.

Стъпка 1: Заставаме в областта, съдържаща входните данни и изпълняваме командата Data/From Table/Range

Стартиране на Power Query с входни данни от текущата раб. книга

    Стъпка 2: Данните се зареждат в Power Query, но тук е един от случаите в които PQ проявява своята "интелигентност" в повече. След зареждането прави допълнителна стъпка за определяне на типа на данните и решава, че първата колонка трябва да се трансформира в тип Time (време). Вие ще кажете, че това е правилно решение и данните са наистина време, но за по-нататъшната обработка е необходимо данните да си останат като текст. Моят съвет е да следите стъпките и да очаквате неочакаването:) В случая просто трябва да се премахне стъпката Changed Type (чрез X-a пред стъпката).

Добавена стъпка за промяна на типа на колонката

След премахване на стъпката

    Стъпка 3: Изпълнява се команда Home/Group By за колона за групиране се избира колона "Направление", а за колона с данни се избира "Час на тръгване". Обърнете внимание, че за операция е избрана функция Sum!! За съжаление, създателите на инструмента са решили да покажат само основните функции за работа с числа и се налага да се правят "ръчно-(крачни;)" операции!

Команда GroupBy

    Стъпка 4: Както вече писах, в момента формулата е абсолютно грешна и резонно в новата колонка има съобщение за грешки!

Грешка при изпълнение

На ръка трябва малко да "пипнем" формулата в реда за редактиране:) И тя трябва да стане:

= Table.Group(Source, {"Направление"}, {{"Часове на тръгване", each Text.Combine([Час на тръгване],", "), type text}})

    Обърнете внимание, че освен подмяната на функцията  List.Sum с Text.Combine сме добавили и втори параметър ", " към нея!!

    Забележка: За сега нямам намерение да ви правя дисекции на формули писани на езика "М" (езикът използван в Power Query), защото излиза леко извън рамките на блога. Но понеже надали ще направя отделни блогове за Power BI сигурно в един момент ще започна да ви разказвам и за "М" и за "DAX"! :) В интерес на истината можехме просто да направим нова стъпка и да напишем изцяло формулата, но както писах, голяма част от Power BI юнаците са на принципа "щракни и гледай какво става" без да вникват в същността на нещата и че в крайна сметка всички кликове се обръщат в заявки на "М"!

Коригирана формула

    Стъпка 5: Зареждането на преобразуваните данни обратно в Excel се извърша чрез командата File/Close & Load (има и бутон на лентата Home). Обърнете внимание, че когато сте в новосъздадената таблица, освен лентата за работа с таблици, се появява и лентата Query, в която се намират инструментите за редактиране и работа със запитването.

Импортиране на данните

    

    Стъпка 6: Настройки на запитването. В настоящия момент запитването е статично. Т. е. при промяна на входните данни резултатната таблица няма да се опресни! За целта има няколко подхода:

    - да научите потребителите да натискат бутона Refresh от лентата Query. За целта трябва да се намират върху резултатната таблица!

    - Да се направя настройки, улесняващи новаци. За целта се натиска бутона Properties от лентата Query.

    Refresh every ... - Опресняване на всеки хх минути. Т. е. осигуряваме актуални данни след изтичането на определен период. Т. е. казваме на нашия човек "пий едно кафе и виж":) Тук риска е да се губи излишно време, ако няма честа актуализация на входните данни.

    Refresh data when opening file - Да опреснява данните всеки път, когато се отваря файла. Препоръчвам тази опция да е включена.

    Refresh this connection on Refresh All - Да се опреснява запитването при натискането на бутон Refresh All от лента Data. Също е добра идея да включите тази опция, за да има алтернатива за опресняване особено, ако има не само едно запитване!

Ми това е:)

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

П.П.2 Показах ви три варианта за решаване на един проблем. Вие си преценявате кой от подходите ви е най на сърце:) За тренировка опитайте да направите обратната на тази таблица. Т. е. да групирате направленията по час на тръгване. Успех!

#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.

#66 (1/3) Групиране (по динозавърски)

 Да понагазим малко в сериозните неща. Примерът, който ще разгледам е в три серии и ще илюстрира три подхода на работа в Excel, които използвам:

- "динозавърски" - подход, който не използва трикове и често е комбинация от "ръчни" операции и действия и писане на прости, независещи от версията на Excel, функции. Този подход е подходящ за илюстриране на различните възможности на Excel на начинаещи потребители. С ръка на сърцето мога да кажа, че когато бързам, често и аз го използвам. 

- "нинджа" - тук, най-често, се използват дълги функции, които изискват доста добро познаване на тънкостите на Excel. Често тези  трикове са свързани с определена (нова) версия на Excel. Тях ги препоръчвам за напреднали и хора, които искат с примери да разберат новите функции. Надали ще сваляте мадами с тях, но можете да блеснете на някое интервю за работа (или да се окаже, че знаете повече от този, който ви прави интервю, което си е драма;)

- "BI Guy";) - умишлено съкратих Power BI Guy за да стане по-весело и двусмислено;) Та в случая Power BI идва от Business Intelligence и е цял пакет от инструменти и онлайн услуги на Microsoft за лесен бизнес анализ на данни. Тези инструменти са цели паралелни вселени и изискват допълнително усилия за изучаване. Доста съм раздвоен по въпроса за тяхното използване в моя блог. От една страна те са лесни и често са от тип "point and click" (посочи и щракни). Но от друга страна, тези инструменти имат собствени езици (DAX, M) за описание, импортиране и анализ на данните, които са много по различни от Excel. И не знам до колко мястото на тяхното използване е в блог за Excel. За това ще се огранича само със "click" функционалностите им, без да влизам в дълбочина. Освен, ако няма голям интерес от вас към тези инструменти:)

Та преди да ми кажете "ей, това мога с един клик да го направя", изчакайте и трите серии на темата:)

Та към проблемът, който ще решаваме.

 Имаме списък с часове за тръгване на автобуси и искаме да ги групираме по направления
за по-лесен преглед.

Първоначални данни и групирани данни по направления

    Стъпка 0: Тук ще ми трябват допълнителни колонки и за това ще си направя нов работен лист (Dino) и ще копирам данните в него. 

    Стъпка 1: За групирането ще ми трябва да сортирам данните по направление и по час на тръгване. Доста ми е забавно, когато хора, които уж разбират Excel сортират само чрез падащия списък до имената на колонките и се чудят как да сортират по две или повече колонки. За целта се използва командата Sort от лентата Data. Там се задават нивата (в случая две) на сортиране.

Сортировка и сортирани данни

    Стъпка 2: Ще добавя нова колонка "Часове на тръгване" в която ще "натрупвам" часовете за всяко направление. В тази колонка има следната формула:

=IF([@Направление]<>B1;[@[Час на тръгване]];C1 & ", " & [@[Час на тръгване]])

    Забележка: Това е таблична формула и ако не сте ОК със странните записвания може да замените [@Направление] с B2, а [@[Час на тръгване]] с A2!

    В тази формула няма (според мен де) голяма магия. Проверяваме дали текущото направление се различава от предходния ред. Ако е така, означава, че това е първия час на тръгване за съответното направление и се взема директно стойността на колонка А! Ако не е първия час, се взема предишната стойност от колонка C и към нея се добавя запетая и часът на тръгване.

Формула за натрупване на часовете за тръгване по направления

    Стъпка 3: Ще добавя нова колонка, която ще индикира дали това е последния ред от даденото направление. Тук формулата е: 

=IF([@Направление]<>B3;"Да";"Не")

    Тук, за разлика от предходната формула, се сравнява направлението с направлението от СЛЕДВАЩИЯ ред.

Формула за намиране на последния ред за дадено направление

    Стъпка 4: Филтрираме само редовете, които се явяват последни редове за даденото направление. За филтриране използваме филтъра до името на колоната.

Филтриране на данните

    Стъпка 5: Скриваме колонки A и C (десен бутон Hide върху името на колонката).

Скриване на колонка

Ми готово! 😇 Така го правят динозаврите 💚

    П.П. Имайте предвид, че някои от стъпките могат да се направят автоматизирано и като цяло, този вариант на решение изисква ръчна работа при добавяне на нови часове в основната таблица. Но както вече писах, операциите тук са прости, бързо се изпълняват, а формулките са лесни и не изискват много мисловна дейност;) И цялото решение се прави бързо. Но то не е подходящо, ако искате да дадете готов инструмент в ръцете на някой, който хал хабере си няма от Excel. За това четете следващите серии на темата:)

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