#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":)

Успех:)

#042 Мързеливо въвеждане на дроби

Беше ми зададен въпрос как при въвеждане потребителя да не се налага да въвежда дробен знак. Т.е. автоматично когато въвежда цели числа те да се преобразуват в дробни. Примерно с 4 знака след десетичната точка. Примерно при въвеждане на 10 да се появи 0.0010 при въвеждане на 12345 -> 1.2345 при 123456789  -> 12345.6789 и т.н.

Вариант 1: Без "фокуси" :):) Excel си има такава настройка. Влизате в менюто File/Options (или Офис бутон/Options за Excel 2007) и там в секцията Advanced намирате това което ви трябва:

Автоматично преобразуване на цели числа в дробни

За да ви подскаже, че сте включили този режим, Excel  ви показва в статус лентата "Fixed Decimal" (т.е. ако някой иска да се бъзика с вас ако видите този надпис да знаете от къде е активиран:))))

Надпис на статус лентата

И готово:)

Плюсове: Няма код, няма допълнителна колонка.

Минуси: Проблемът е, че тази настройка е валидна за Excel а не за конкретната работна книга! Т.е. ако изпратите вашата работна книга на ваш познат при който тази настройка не е включена "магията" ще изчезне:( Друго досадно нещо е, че тези настройки важат за всички цели числа които въвеждате (независимо от работна книга, работен лист или зона от клетки).... Т.е. ако искате наистина да въведете 123 трябва да напишете 123. (точка след 123!) . При дробните числа няма проблем. Ако вие въведете 123.4 това число ще бъде въведено в клетката без промяна! Върши работа но е прекалено глобално като действие и не е обвързано с конкретна работна книга.

Вариант 2: "Бабешки" начин:) Въвеждаме помощна колонка:) Примерно ако потребителя въвежда данните в колонка А правим колонка B съдържаща следната формула:

  =A1/10^4  (В интерес на истината варианта =A1/10000 ще е по-бърз, но нали трябва да ви припомням и странични неща. Примерно, че ^ е степенуване ):):)

Плюсове: Локално за работна книга. Което означава, че когато я изпратите на ваш познат ще си работи и при него. Няма код.

Минус: Ми от всякъде погледнато тази допълнителна колонка е грозна. Т.е. не тя ами колонката в която потребителя въвежда данните. Това може да се избегне по два  налична. Или колонката B е скрита (или изобщо е няма и във всяка формула съдържанието на A се дели на 10000). Или колонка А се слага на лист примерно "Въвеждане", а на друг лист се вижда в целия блясък коригираните стойности (т.е. "работната" колонка и останалите колонки които са производни от нея")!

Вариант 3: С "фокус" (разбирайте код):):) Натискате десен бутон на мишката върху името на листа изпълнявате командата View Code и в редактора слагате следния код:

' ------------- Начало на кода -------

Private Sub Worksheet_Change(ByVal Target As Range)
Const div = 10000
If Target.Count > 1 Then Exit Sub
If Not (Intersect(Target, Range("A1:A22")) Is Nothing) Then
   If IsNumeric(Target.Value) Then
   Application.Calculation = xlCalculationManual
   Application.EnableEvents = False
   Target.Value = Target.Value / div
   pplication.Calculation = xlCalculationAutomatic
   Application.EnableEvents = True
   End If
End If
End Sub
' ----- Край на кода ------

В различен цвят съм дал нещата които подлежат на настройка. Както виждате съм изнесъл като константа 10000 за да може лесно да пипате и променяте числото на което делите. Променяте и зоната в зависимост от вашите нужди и сте готови!:) Сега при промяна на клетките от посочената от вас зона ще действа "магията" за останалите клетки и работни листи няма да действа. За да е по-разбираем примера е направен само за промяна на единични клетки в посочената зона! За повече информация вижте другите теми за Target  (#037 и #036)!

Важна особеност е блокирането на събитията при промяна на стойността на клетката от самия макрос (target.value=.........). Защото може да изпаднете във вечен цикъл (в интерес на истината просто Excel при мен "гръмна" когато бях забравил този момент:):) За това е важна двойката оператори:

Application.EnableEvents = False и Application.EnableEvents = True "заграждащи" самото действие. Така ние временно "приспиваме" Excel да не отчита промяната на стойността на клетката от макроса!

Плюсове: Пълен контрол. Свързано е с работната книга и конкретни клетки от нея.  Книгата ще си "носи" със себе си тази функционалност. Няма излишни колонки. Промяна на данните в мястото на въвеждане.

Минус: Наличието на код. Т.е. потребителя трябва да разреши макросите. Което си е досадно. Ако трябва да се ползват новите формати на офис 2007/20010 книгата трябва да бъде записана в XLSM а не XLSX!

Успех:)

#41 Разделители и масиви

В тема #40 на края посочих два "странни" примера. Ето малко разяснения. Всъщност трябва да се извиня, че последно време измених използваните разделители. Това е поради новата ми инсталация на Windows и това, че писах примери за едно помагало. В него трябваше нещата да са с локални настройки на Български. Т.е. десетичен знак ",", а не точка. Това автоматично означава, че параметрите се разделят с ";".

Тук трябва да задълбаем малко в проблемите. За обикновения потребител не представлява проблем да запомни двата разделителя. Но нали все пак тук е блог за трикове!:) В някой от примерите използвам константни масиви (стойности оградени с { }). Ако поразровите темите и прочетете всичко в една от темите аз споменах, че има драма и обещах да разчовъркам. 

При масивите с константи ние имаме два вида разделителя. Разделител на елементите в един ред (така наречения разделител на колони) и разделител на редове. При разделител за десетичен знак ТОЧКА тези разделители са:

разделител за колони -  запетая (",")

разделител за редове - точка и запетая (";")

Това означава че записа {1,2,3;4,5,6} се представя като:

 {1,2,3} като:

{1;2;3} като:

При десетичен разделител ЗАПЕТАЯ разделителите са:

разделител за колони -  обратна наклонена черта -  ("\")

разделител за редове - точка и запетая (";") (същото както при другия вид разделител!)

Горните примери изглеждат така:

• {1\2\3;4\5\6}
• {1\2\3}
• {1;2;3}

Ето табличката за да я имате в компактен вид:

Разделители

Няколко думи за "странните" функции от #40:


=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 не е в зона от клетки (много хора мислят, че това е единствената възможност),  а в константен масив. Ако се разбрали това което писах преди малко този масив изглежда така в "разгърнат" вид:

След което търсим точно и връщаме съдържането на втората колонка. Просто и ясно:)) В другия пример нещата са аналогични, с тази разлика, че Vlookup търси приблизително!

=VLOOKUP(A2;{1\1;4\2;7\3;10\4};2;TRUE)
 
Внимавайте с типовете масиви и разделителите:)

П.П. Длъжен съм да направя уточнението, че това се отнася за таблиците на Excel!  Когато пишете на езика VBA винаги десетичния разделител е ТОЧКА, разделителя на аргументите е ЗАПЕТАЯ!

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

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

#39 Функците Small и Large без дублирани стойности

Описанието на функцията Small е дадено в помощната информация на Excel (може да използвате този адрес http://office.microsoft.com/bg-bg/excel-help/HP010062539.aspx).
За съжаление в информацията не е посочен един малък подводен камък, който  може да ви доведе до главоболие в определени случаи. Става дума за това, че функцията брои стойностите не отчитайки дублиращите стойности за всяка позиция. Ето пример:

Данни за Small

Ако въведем формулата: =SMALL(A1:A10;1) ще видим стойността на най-малката стойност (в случая 1).  Ако въведем формулата =SMALL(A1:A10;2), с тайната надежда да видим ВТОРАТА по големина стойност, ни очаква разочарование. Функцията ще върне пак 1!! Това произтича от факта, че в списъка има две единици. Чак когато зададем =SMALL(A1:A10;3) ще получим това което искаме :(

Този "проблем" се решава със следната помощна табличка:

Намиране на N-тата по големина стойност

Стъпка 1: В колонка C се намират числа от 1 до N.Може с AutoFill или формулата =Row(A1) или =Row()-1 и т.н. ( всеки си има стил и различен подход, и както и да ги въведете (дори и на ръка) никой няма да ви разстреля):):):)

Стъпка 2: В клетката D2 въведете  =SMALL($A$1:$A$10;1). Намира първата най-малка стойност. Тук няма интрига;)  

Стъпка 3: В клетка D3 въведете =SMALL($A$1:$A$10;COUNTIF($A$1:$A$10;"<="&D2)+1)

Размножете формулата като я "дръпнете" надолу. И готовото:) Ако искате да не дава грешка #Num просто я "пакетирайте" с IFError за което си има отделна тема;) 

Няколко думи за "магията". За да се намери N-тото по големина число, трябва да е намери колко е броя на N-1 по големина числа преди него! Примерно за да се намери второто по големина число трябва да се намери колко са преди него (в случая колко единици имаме)! След което към намерената бройка да се добави единица и да се намери чрез Small съответната стойност (в нашия случай 3-тото по големина число). За намирането на третото по големина числа броим колко са преди него като бройка (в нашия случай колко са числата по-малки от 2) В нашия случай намираме броя  на единиците и двойките!. Понеже имаме само една двойка сумата е 3! Т.е. със Small трябва да намерим четвъртата(!) по големина стойност.... И т.н. (опитайте се да проиграете и за останалите числа).

За по-лесното използване на тази таблица, я именуваме (примерно SmallBD).След което може да използваме "прост" Vlookup:):)

Именуване на таблицата

Пример: Да се намери петата по големина стойност: =VLOOKUP(5;SmallBD;2;FALSE). Сменяте само червената цифричка и сте готови:):)

Забележка: По някога може създаването на междинна таблица да не е възможно (въпреки че в случая го препоръчвам горещо) и се налага да се сглоби цялата формула в една клетка. В този случай формулата става доста дълга (и доста объркваща за начинаещи)! Аз ще дам само как се намира втората по големина стойност:
=SMALL($A$1:$A$10;COUNTIF($A$1:$A$10;"<=" & SMALL($A$1:$A$10;1))+1).
За по-големи стойности се въоръжете с търпение и здрави нерви и "сглобявайте":):) Но както споменах това го препоръчвам за майсторите които са разбрали предишните обяснения!

Успех:)

П.П. Всичко казано за функцията Small важи и за функцията Large!

#38 Работа с ЕГН

Реших малко да си поиграя и направих една таблица за работа с ЕГН-та... В нея има доста хитрости и различни техники. Няма да описвам детайли. Тези които искат да пишат на посочения е-mail. Успешно човъркане и учене:):)

Връзка към файла (google.docs)