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

#65 И отново за версиите, лицензите и докуметацията

 В #63 коментирах Excel 2019 и сега е момента да кажа няколко думи за последната версия на Excel. И именно тук е уловката. Коя е последната версия на Excel?! Ако сте обърнали внимание, в шапката на блога съм посочил, че използвам Excel 2021 и Excel 365:

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

Excel 365 - Това е версията с лиценз "под наем". Т. е. плащате определена такса за използване. Много често се греши, че това е онлайн версия на Excel! Да, имате достъп до онлайн версия през portal.office.com, но можете да си инсталирате и десктоп приложение на вашите компютри, която има доста повече функционалности. Новите функционалности се добавят постоянно и никнат като гъби след дъжд. Даже и аз понякога се учудвам кога е цъфнала някоя нова функционалност:) В настоящия момент Excel 365 води с няколко обиколки пред Excel 2021. При Excel 365 има още една тънкост. Ако използвате корпоративен лиценз може да се окаже, че администратора на вашата фирма/група/организация да задържа/блокира обновленията. Или да блокира автоматичните обновления. За това проверете какви са настройките и дали версията съответства (да е близка) до текущата годината и текущия месец (в примера на фигурата се вижда, че версията е за месец август 23-та година). Това става, чрез командата File/Account.

Проверка на настройките и версията

В България на доста места се използва Excel 2021 защото фирмите имат еднократен бюджет за подмяна на софтуер, и тази версия по-лесно се краква ;)  За това на едното си работно място използва 2021, а на другото 365 и ще тествам нещата и на двете. И ще маркирам темите, които изискват Excel 365! За съжаление, може да се окаже, че ако имате по-стара версия, някои от нещата няма да работят при вас. За съжаление, няма как да поддържам и тествам на по-стари от Excel 2021 версии. За щастие, старите трикове са по-универсални и стават и за по-стари версии. Та, ако имате версии преди 2021 с едно на ум четете от #66 нататък!

Официалният списък на функциите в сайта на Microsoft съдържа някои маркировки за версията, в която се появила дадена функция, но тази информация не е пълна и особено за Excel 365, където има нова версия всеки месец няма как да се определи кога точно се е появила. За това препоръчвам обновления на функциите в Excel подържани от Better Solutions . Това е детайлен списък по версии, а за Excel 365 имаме и годината на поява на дадената функция. В сайта има и друга полезна информация за работа с MS Office. Препоръчвам този сайт.

## Завръщането на джедаите и нинджите! ;)

    Изненада!!:) Ето ме и мен. Изпратиха ми няколко линка, в които хвалят моя блог и се замислих, че е време да понапиша това онова. Погледнах старите теми и се почувствах като динозавър. В блога ми няма грам за динамичните масиви (е, в последната статия ви „предупреждавам“ за тях), нито за новите функции като XLookup, Let и възможностите да пишете собствени функции без VBA с Lamda! Няма трикове с Power Query. Excel така мръдна последните години, че „триковете“, които съм описал, се правят с новите функции и инструменти за секунди. Е, ако ги знаете да ги използвате де. Защото все още за връх на Excel “хакерството“ се смята Vlookup😊 Та дръжте си шапките, че ви чакат нови приключения и забавления, ако сте любители на Excel и анализа на данни. П.П. За радост или съжаление, освен мързел нямам оправдание за това, че спрях да пиша😊 За което се извинявам.