#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;"Отличен")

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

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

#043 Как се коригират скали и има ли "Тъп 0" като оценка:)

Преди време получих писмо (и чинно му отговорих):) Но реших, че си заслужава да напиша отговора и в тема за да съм полезен и на други. Ето писмото:

Попаднах случайно на блога ти за трикове в Ексел, докато търсех информация за функцията CHOOSE. Изглежда интересен и полезен, но аз съм още начинаещ. Та в този смисъл в последно време се сблъсквам доста често с Ексел. Озадачи ме следното: =CHOOSE(O4-1;"Слаб";"Среден";"Добър";"Мн.добър";"Отличен").

Защо от О4 изваждаме една единица.

Лесно се намира в помощната информация на Еxcel, че Choose служи за преобразуване на числова стойност. Първия параметър е стойността, а останалите са отговорите в зависимост от нейната стойност. Отговорите са подредени по следния начин "Отговор при стойност на първия параметър ЕДНО", "Отговор при стойност на първия параметър ДВЕ" и т.н. Т.е. скалата на отговрите е 1,2,3,4,5.... Да разгледаме скалата от оценките... Тя е 2,3,4,5,6... Т.е. по някаквъ начин ние трябва да изравним двете скали:

Вариант 1: Корекция на отговорите. Създаваме ШЕСТ отговора. Този вариант е по-скоро за илюстрация и по-добре не го помнете:)

=CHOOSE(А1;"";"Слаб";"Среден";"Добър";"Мн.добър";"Отличен") 

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

Вариант 2: Корекция на скалата с оценките . Тук НАМАЛЯВАМЕ оценката да се преобразуват в числови стойности от ЕДНО до ПЕТ! И задаваме ПЕТ отговора.

=CHOOSE(А1-1;"Слаб";"Среден";"Добър";"Мн.добър";"Отличен") (това е примерът от писмото)

Ако оценката е 2 ще стане едно и ще се върне първия отговор ("Слаб"). При три ще стане две и ще се върне втория отговор и т.н... 

Да продължим със следния казус. Един учител по мое време имаше малко по-различна скала за оценяване. Тя беше следната "Тъп 0", "Лош 1", "Слаб 2", "Среден 3", "Добър 4", "Мн. добър 5", "Отличен 6":):)

Тук скалата на оценките (стойността според която ще връщаме резултат) започва от 0!! Тук помага сама втория вариант на решение от предходния пример:

=CHOOSE(А1+1;"Тъп";"Лош";"Слаб";"Среден";"Добър";"Мн.добър";"Отличен")

Тук изравняването се извършва чрез ДОБАВЯНЕ на единица към оценката за да се преобразува в поредица запозвачваща от едно. При оценка 0 ще върне ПЪРВИЯ отговор ("Тъп") и т.н.

Мисля че сте разбрали, че трябва стойностите на израза да се преобразува в поредица 1,2,3,4....

Да видим сега малко "забавления" за напреднали:) Малко решения на същия пример (с "нормални оценки") решена чрез други функции. Идеята е да имате богат арсенал от оръжия за да се по-гъвкави в различни ситуации.

Вариант 1: Vlookup с "вграден" масив.

=VLOOKUP(A2;{2\"Слаб";3\"Среден";4\"Добър";5\"Мн. Добър";6\"Отличен"};2;FALSE)

(вижте #041!). Разбира се може да го направите и по "бабешкия" начин, като направите отделна табличка с оценките и "словом" на отделен лист. И да търсите с Vlookup в нея.

Вариант 2: Комбинация Index/Match.

=INDEX({"Слаб";"Среден";"Добър";"Мн. Добър";"Отличен"};MATCH(A1;{2\3\4\5\6};0))

 (вижте темите за Index и  Match)

Вариант 3: CSE вариант на предходната формула с автоматично генериран масив

от числа. (въвежда се с Ctrl+Shift+Enter! без {} около формулата!)

{=INDEX({"Слаб";"Среден";"Добър";"Мн.Добър";"Отличен"};MATCH(A2;ROW(INDIRECT("2:6"));0))}

 Вариант 4: Любимия на всеки даскал физкултурник преквалифицирал се като информатик:)

(хич не съм му фен на този вариант, но всеки ученик (и студент) го въртят на шиш докато не го научи да го прави по този начин):):)

=IF(A1=2;"Слаб";IF(A1=3;"Среден";IF(A1=4;"Добър";IF(A1=5;"Мн. Добър";"Отличен")))) 

Та опитайте се да ги разберете тези варианти (мога и още да ви дам):) Всеки си има плюсчета и минусчета:) И така никога няма да  получите "Тъп 0":)

Успех:)

#40 Месец в тримесечие или логика срещу математика

Който не е обяснявал Excel той не се е сблъсквал с проблема да се обяснят логическите функции. Много студенти смятат, че научвайки функцията IF са големи гении. По някога има и различно решение, но друг е въпросът дали е по-лесно за обяснение.

Задача: Да се преобразува месец в тримесечие.

Месец в тримесечие

Решение 1: Може би най-завъртяния начин е само с IF :)
В клетката B2: =IF(A2=1;1;IF(A2=2;1;IF(A2=3;1;IF(A2=4;2;IF(A2=5;2;IF(A2=6;2;IF(A2=7;3;IF(A2=8;3;IF(A2=9;3;4)))))))))

Тази формула не се нуждае от коментар:) Показва няколко неща. Че студента знае IF и че има здрави нерви да напише такава дълга формула;)

Решение 2: Малко "по-културна" логическа формула:)
=IF(OR(A2=1;A2=2;A2=3);1;IF(OR(A2=4;A2=5;A2=6);2;IF(OR(A2=7;A2=8;A2=9);3;4)))
Малко по-къса и по разбираема:) И студента знае OR:)

 Решение 3: Е все пак освен проверка за равенство може да се използват и други знаци:)
 =IF(A2<4;1;IF(A2<7;2;IF(A2<10;3;4)))



 Решение 4: До тук с логиката:) Сега да седнем и да помислим каква е връзката между номера на тримесечието и номера на месеца. За да илюстрирам това ще направя една помощна табличка...

Месец разделен на 3

Както се вижда в тази таблица съм разделил номера на месеца три (все пак става дума за ТРИмесечие):):) В различен цвят съм дал различните тримесечия. Би трябвало да ви "светне", че по някакъв начин трябва да закръгляме. В по-предна тема дискутирах закръглянето и ви препоръчах да се запознаете с всички възможности в Excel. Ако бяхте послушали съвета ми вече щяхте да сте разбрали че ни трябва закръгляне към СЛЕДВАЩОТО цяло число (когато числото е дробно!) . Това е функцията RoundUP!

Ето решението за примера:  =ROUNDUP(A2/3;0)

Чиста математика:) Без логика:) Тук е момента да се запитаме кое е по-лесно за обяснение (не по-кратко)?! Честно казано не знам!! Забелязал съм че на хората е еднакво трудна (лесна) и математиката и логиката!

:)

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

Решение 5: =CHOOSE(A2;1;1;1;2;2;2;3;3;3;4;4;4)

Решение 6: ={INDEX({1;1;1;2;2;2;3;3;3;4;4;4};MATCH(A2;ROW(INDIRECT("1:12"));0))}

CSE Функция! Въвежда се без {} но с помощта на Ctrl+Shift+Enter!! Ако искаме да не е CSE "пакетираме" Match със Sumproduct!

=INDEX({1;1;1;2;2;2;3;3;3;4;4;4}; SUMPRODUCT(MATCH(A2;ROW(INDIRECT("1:12"));0)))

Решение 7: =INDEX({1;2;3;4};MATCH(A2;{1;4;7;10};1))
Тук използваме друг вид търсене в Match (обърнете внимание на втория параметър в Match!). Красота:)

Решение 8: Горните две решения за любителите на Vlookup:):)


=VLOOKUP(A2;{1\1;2\1;3\1;4\2;5\2;6\2;7\3;8\3;9\3;10\4;11\4;12\4};2;FALSE)

=VLOOKUP(A2;{1\1;4\2;7\3;10\4};2;TRUE) 

Тук имам нещо за казване, но ще го напиша в отделна тема:) Защото тази формула на първо четене може да ви доведе до главоболие:):):) Обърнете внимание, че втората формула има друг начин за търсене!