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

Успех:)