#64 Динамични масиви и как лесно ще се става нинджа

Едно от най-трудните неща в Excel си остават динамичните списъци. Смятам, че който може да прави сортировка или извличане с помощта на формули е малка нинджа в Excel:) В блога има теми, които засяга въпросите на динамичните списъци (например темата #008 Сортиране при въвеждане на данните). Microsoft в скоро време се канят да направят живота много по-лесен и всеки лесно да стене спец по динамичните функции.
Става въпрос за динамичните масиви. Това чудо наистина е нещо тотално ново (както казват руснаците gamechanger:). За сега трябва да влезете в програмата на тестерите на нови неща (Insider program) за да имате достъп до тези благинки, но дори да не влезете в програмата е добре да се запознаете с тази новост! Това ще ви позволи, когато се появи, най-вероятно първо в Office 365, да сте готови да ползвате този нов нинджа меч:)

В кратце това са функции, които връщат резултат във вид на масив. Хубавото е, че Excel ще покаже резултата без да се налага да натискате комбинацията Ctrl+Shift при въвеждането на тези функции, както е при така наречените CSE функции! Появата на функциите с динамични резултати е чакано отдавна в Excel общността, още повече, че в Google Shteets има вече има сходна функционалност (функции Filter и Sort). Друга е темата, защо се забавиха и  защо позволиха на Google да ги изпреварват баш в електронните таблици. Язък, че се хвалиха, че Excel е най-мощния продукт. Но както и да е. По-добре късно, отколкото никога....

Ето статията в която се обясняват новите функционалности. Preview of Dynamic Arrays in Excel.
MrExcel e подготвил и разпространява БЕЗПЛАТНО до края на година електронна книга (60 страници) за динамичните масиви!! Изтеглете си я от неговия сайт! Excel Dynamic Arrays Straight to the Point. Да имате какво да четете през коледните  празници:):)

Когато пуснат тази функционалност и за простолюдието обещавам да ви подготвя по-дълга статия с примери.

Успех в усвояването на новите оръжия:)

#63 Да вдигнем нивото или трябва ли ни Excel 2019

Длъжен да предупредя, че си инсталирах Excel 2019. Няма да тествам на по-стари версии и може да има проблеми с някой от решенията. Ще се старая да предупреждавам, че съм използвал нови възможности, но може да забравя!:)

Винаги е стоял въпроса дали да се преминава към по-новата версия на даден продукт. И както е в случая дали е по-добре "стационарния" Office  или да се използва Office 365, който е на абонаментен принцип.

Няма да влизам в дълги писания и реклами, но всеки от вариантите си имат плюсове и минуси. Едно от предимствата на Office 365 е че вие получавате веднага всички нови благинки, докато при стационарната версия чакате до следващия тираж (следващата версия).

Какво се появи в Excel 2019 може да прочетете тук: Какво е новото в Excel 2019 за Windows.

За да покажа, че новите благинки  помагат и улесняват ще се спра на функцията IFS.

Всеки преподавал Excel се поти, преподавайки вложени IF-ове, когато имаме няколко последователни условия. Обучаемите смятат, че са открили теорията на относителността, когато научат как се влагат условия. И искат да им пишем шестици за това:):) 

Задача: Да се преобразува числова оценка в оценка с думи

Вариант 1 - Класически IF
Преобразува се с помощта на "класически" вложени условия
Ако условие
  Действие
Иначе условие
   Действие
Иначе  условие
   Действие
Иначе
   Действие

IF(условие; ако е истина; IF(условие; ако е истина; IF(.....;ако е истина; иначе)...)

 =IF(H6<3;"Слаб";IF(H6<3.5;"Среден";IF(H6<"Добър";IF(H6<5.5;"Мн. добър";"Отличен"))))

Вариант 2 - Чрез IFS.
IFS НЕ ИЗИСКВА влагане. Всичко си е линейно и последователно.
IFS(условие; стойност ако е истина; условие; стойност ако е истина....) (за повече вижте синтаксиса в документацията на Microsoft).
Особеното е когато искаме да сложим стойност по подразбиране на края (частта ИНАЧЕ). Има два варианта: да сложим условие, което винаги е истина (например 1=1:) или просто да сложим константата TRUE вместо условието!

=IFS(H6<3;"Слаб";H6<3.5;"Среден";H6<4.5;"Добър";H6<5.5;"Мн. добър";TRUE;"Отличен")

Красиво и лесно! Няма влагане, няма хиляди скоби.

Успех с новите версии:)

#62 Броене на знаци или как прецаквам усилията на колеги

Реших да постна нещо за да не съм капо тази година:) А и да покажа, че съм жив и здрав и поддържам блога. Но и вие сте си виновни, че не ме тормозите и то със задачки, които биха били полезни и за останалите читатели:)

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

Благодаря предварително!!!" 

Младежът директно ми беше изпратил условието:) Барабар с изискванията да се опише всичко. Аз му изпратих само функцията-решение без обяснения и не знам дали го е разбрал, обяснил и дали са му признали решението. Надявам се вече да е минало всичко и реших да напиша малко обяснения по темата.

Задача 1. Да се преброят срещанията на даден символ в текст.

За това имам цяла тема. #017 Търсене и броене със Substitute ! Добре е да си я опресните, но ако на някой не му се чете ето и краткия вариант.

Идеята е следната. Изтрива се търсения символ. Изтриването се извършва чрез заместване (функция Substitute) на символа с "" (празени стринг). След което се намира разликата между дължината на оригиналния текст и новия текст, който е без търсения символ. Разликата показва броя срещания на символа.

Пример: Да се намеря броя появявания на символа "." в текст.

=LEN(A1)-LEN(SUBSTITUTE(A1;".";""))

Задача 2. Да се преброят срещанията на два символа в текст

Вариант 1: По пътя на логиката може да го направим като дублираме формулата.

Пример: Да се намери броя на срещанията на символите "." и "*" в текст.

=LEN(A1)-LEN(SUBSTITUTE(A1;".";""))+LEN(A1)-LEN(SUBSTITUTE(A1;"*";""))

Вариант 1b: Не се изискват две висши да се сетим да обединим двете дължини:

=2*LEN(A1)-LEN(SUBSTITUTE(A1;".";""))-LEN(SUBSTITUTE(A1;"*";""))

Вариант 2: Сега остава да се сетим как да обединим двете резултатни дължини.

=2*LEN(A1)-SUM(LEN(SUBSTITUTE(A1;{".","*"};"")))

Изпращаме МАСИВ, който съдържа двата символа. Грешка е, ако се изпратят като един стринг ".*", защото ще подмени само там, където двата символа са един до друг! Чрез функцията Sum намираме сумата на двата резултата (двете дължини).

Задача 3. Да се преброят нечетните цифри в цяло число (задачата на юнака:)

Би трябвало да се сетили как става "магията":) Просто символите не са 2 ами 5!

=5*LEN(A2)-SUM(LEN(SUBSTITUTE(A2;{1,3,5,7,9};"")))

Excel e достатъчно "умен" и не изисква да се преобразува числото в текст, но ако някой го дразни, че разчитаме на "интелекта" на програма то тогава може да преобразува явно:

=5*LEN(TEXT(A2;"0"))-SUM(LEN(SUBSTITUTE(TEXT(A2;"0");{1,3,5,7,9};"")))

Също така няма смисъл да заграждаме в кавички цифрите, но може и така:

=5*LEN(TEXT(A2;"0"))-SUM(LEN(SUBSTITUTE(TEXT(A2;"0");{"1","3","5","7","9"};"")))

Ми това е:) Решихме задачата на младежа и на една камара бъдещи "потърпевши" от това задание:):) И искрени извинения към колегата (учител или преподавател), който дава това задание. В интерес на истината му завиждам, че има такива обучаеми на които може да даде такава задача, която хич не е лесна.

Успех:)

П.П. Обещавам да не решавам задачите на други хитреци! Не търсете лесния вариант. Няма как чрез StackOverflow и други помощници да се генерира БВП! До време е. Трябват хора, които сами решават проблемите!

#61 Frequеncy и други магии. В търсене на Немо, Дори и най-дългата зона:)

Здравейте!

Бях в режим на мълчание, понеже не ми давате теми за писане, а и нямам време да си ги измислям:)

Скоро ми зададоха задачка да намеря най-дългия последователен брой работни дни. Реших да поровя за различни екзотики и попаднах на един фен на Frequency :) Както има фенове на Sumproduct така има и фенове на различни функции и с тях се опитват да решат всичко. Нещо като Golden Hammer (за повече информация- Law of the instrument (Wikipedia)):)

Та реших да споделя с вас този трик. Може да ви е полезен. В чест и на новата учебна година в Свищов. Да си пожелаем повече бъдещи нинджи:)

Задачата е: Да се изведе най-големия брой от последователно запълнени клетки.

Примерни данни

Формулата е: {=MAX(FREQUENCY(IF(A3:A20<>"";ROW(A3:A20));IF(A3:A20="";ROW(A3:A20))))}
Резултат:4
Найс, а?:) Следва дисекция.

0. Функция Frequency. (Ако сте запознати с нейния начин на работа прескочете точката.)

Връзка към официалната помощна страница. Хелпа на функцията е некадърно преведен (сякаш правен с машинен превод) и объркващ. За да стане кашата пълна;) ето и моето обяснение:

• Функцията връща масив от стойности (array function).
• Има два параметъра, който са масиви или зона от клетки.
• Първият параметър представлява данните, които ще бъдат анализирани.
• Вторият параметър представлява стойностите за групиране.
• Резултатът е масив с дължина по-голяма от големината на втория параметър с едно.
• Резултата представлява масив, съдържащ БРОЯ на елементите от първия масив по-малки или равни от границата.
• Последната стойност в резултатния масив е БРОЯ на елементите по-големи от последната стойност за групиране.

Пример: 

{=FREQUENCY({1,2,3,4,5,6,7};{3,6})}

Резултат: {3;3;1}. Където:

• 3-> броя на числата по-малки или равни на 3 (1,2,3)
• 3-> броя на числата по-малки или равни на 6 (4,5,6)
• 1 -> броя на числата по-големи от 6 (7)

1. Дисекция на формулата

{=MAX(FREQUENCY(IF(A3:A20<>"";ROW(A3:A20));IF(A3:A20="";ROW(A3:A20))))}

• Формулата е CSE (въвежда се с Ctrl+Shift+Enter).

• IF(A3:A20<>"";ROW(A3:A20)). За всички клетки които отговарят на условието (да са пълни) се записва номера на реда. За останалите се запълва false (функцията If умишилено е оставане без трети параметър!). За примерните данни се получава: 

FALSE, FALSE, FALSE, FALSE,FALSE, 8,9, FALSE, 11, 12, 13, 14, FALSE, FALSE, 17,18, 19,20.
Това са данните: 8, 9, 11, 12, 13, 14, 17, 18, 19, 20

• IF(A3:A20="";ROW(A3:A20)). За всички клетки които НЕ отговарят на условието също се записва номера на реда. По този начин се получава огледален масив за групиране:

3,4,5,6,7,FALSE,FALSE,10,FALSE,FALSE,FALSE,FALSE,15,16,FALSE,FALSE,FALSE,FALSE
Това отговаря на: 3, 4, 5, 6, 7, 10, 15, 16

• FREQUENCY({8,9,11,12,13,14,17,18,19,20},{3,4,5,6,7,10,15,16}) . Брои стойностите по-малки или равни на стойностите за групиране. Резултат: 0, 0, 0, 0, 0, 2, 4, 0, 4 (последната стойност в резултата е броя на данните по-големи от 16!)
• Max({0,0,0,0,0,2,4,0,4}) Намира най-голямото число. Резултат 4.

Ми това е :) Просто като президент на щатите:) *упсс... Сори за политическото изказване:)

2. Варианти на формулата

2.1 Да се намери най-дългата поредица от ПРАЗНИ клетки:

{=MAX(FREQUENCY(IF(A3:A20="";ROW(A3:A20));IF(A3:A20<>"";ROW(A3:A20))))}

Резултат: 5

Коментар: Ми нищо различно. Просто обръщаме условията.

2.2. Да се намери най-дългата поредица съдържаща "C".

{=MAX(FREQUENCY(IF(A3:A20="C";ROW(A3:A20));IF(A3:A20<>"C";ROW(A3:A20))))}

Резултат:2

Без коментар;)

2.3 Да се намери най-дългата поредица съдържаща "C" ИЛИ "D".

Тук може да стане голяма беля! Има два подводни камъка, на които лесно може да се натресете. 

Обръщане (отрицание) на логическа операция. Не забравяйте, че освен обръщане на = в <> трябва да обърнете и логическата операция. (Булева алгебра, правила на Де Морган и други неща дето не се учат много много):) Законите на Де Морган.

Та трябва да се усетите, че формулата трябва да е нещо такова:

{=MAX(FREQUENCY(IF(OR(A3:A20="C";A3:A20="D");ROW(A3:A20));IF(AND(A3:A20<>"C";A3:A20<>"D");ROW(A3:A20))))}

Да ама не. Нацелвате втория камък. В CSE формули Or/And малко не бачкат както трябва:) Да не кажа хич;) Трябва да търсите техни заместители. Някъде из другите постове ви обърнах внимание, че Or е "събиране", а And е умножение. И Еврика! Ето ви формулата:

=MAX(FREQUENCY(IF((A3:A20="C")+(A3:A20="D");ROW(A3:A20));IF((A3:A20<>"C")*(A3:A20<>"D");ROW(A3:A20))))

Резултат: 3 

Красиво:)

2.4 Данните са разположени хоризонтално.

{=MAX(FREQUENCY(IF(A29:M29<>"";COLUMN(A29:M29));IF(A29:M29="";COLUMN(A29:M29))))}

Комантар: Вместо функцията Row() се използва Column().

Това е.

3. Заготовки.

Та заготовките, които трябва да запомните са:

• За данни в колона: {Max(Frequency(If (УСЛОВИЕ;Row());If(NOT УСЛОВИЕ;Row()))}
• За данни в ред: {Max(Frequency(If (УСЛОВИЕ;Column());If(NOT УСЛОВИЕ;Column())}
• Формулите се въвеждат с Ctrl+Shift+Enter!

Ми успех в броенето;):)

#60 Интервали и интервал без разделяне

Една бърза тема за тези, които използват Copy/Paste от уеб страници. Попаднаха ми данни имащи следния вид:

Данни

 

 

 

 

 

 

На пръв поглед фасулска работа. Самия Excel показва, че данните в първата клетка са числа, но оформени като текст (зеленото триъгълниче в ляво на клетката). Това позволява тези данни да се преобразуват в числа без проблем.

Преобразуване на данни

 

 

 

 

 В долната клетка нещата изглеждат подозрително. Използвах функции и "трикове" за преобразуване на текст в число. Неуспешно за клетката A3!:( Вижда се, че Excel позволява използването на стойността в математически израз (колонка F), въпреки че не е число (проверката в колонка B)!

Преобразуване на текст в число

За това се заех по-обстойно с втората клетка. Реших да проверя кой е третия символ в клетката. Стори ми се подозрителен:) Това извърших чрез функцията:
 =CODE(MID(A3;3;1)), която върна отговор 160! "Нормалния" интервал има код 32.

Къде е проблема?! Кодът 160 е на символа "non-breakable space". Това е интервал, който се разглежда като символ и се разглежда като част от думата (например 100 км/ч.). В HTML това е символа &nbsp,  а в MS Word се въвежда чрез Ctrl+Shift+Space. Явно в текста който е копиран в Excel е използван този символ. Оказва се, че в много от сайтовете използват този символ за подредба на данните.

Решения:

1. Чрез търсене и замяна

Търсене и замяна

Забележка: Задържа се клавиша Alt и се набира от ЦИФРОВАТА клавиатура (не от основния блок) 0160.

2. Чрез функция

=VALUE(SUBSTITUTE(A3;CHAR(160);""))

Забележка: Между кавичките няма нищо!

Ми това е:)
Успех и умната с интервалите (и с Copy/Paste):):)