#045 Data Validation без Validation!

Ето ви едно малко  по-сложно изпълнение базирано на Data Validation.

Задача: Да се реализира "Pick from Drop-down List" функционалност.

Ха сега... Няколко уводни бележки относно тази функционалност. Тези които знаят за какво иде реч да прескачат абзаца:) Когато се въвеждат стойности и сте натиснали десен бутон на мишката може ви сте видели командата Pick from Drop-down List, която показва списък на въведените до този момент стойности в колоната!

Командата в контекстното меню

Вид на списъка за избор

Това улеснение ни дава бърз начин за избиране от вече въвежданите стойности.

Hint! Вместо да се мотате из контекстното меню същия ефект се постига с натискането на ALT+Стрелка надолу!!!

След като го има защо ни трябва да го правим отново!?  Винаги съм се смятал за мързел без капка мазохистични наклонности:)

Проблем 1: Това не действа за цифрови стойности! Просто не ги показва в списъка.
Проблем 2: Ето едно писмо което получих преди време:

"..... Има едно положение в Ексел 2003, което ме затрудни. Става дума за следния казус:

Имаме таблица, в която трябва да се заключат определени области - колони, в които да се въвежда само след парола. Дотук добре - дефинираме областите в Tools/Protecton/Allow Users to Edit Ranges и слагаме пароли. След това заключваме Sheet-а от Tools/Protection/Protect Sheet. Междувременно използвам Аuto Filter във всяка колона. Това ми позволява да използвам десен бутон и Pick From Drop-down List.

И тук идва проблемът. Докато Sheet-а не беше заключен, това меню съществуваше, в момента в който я заключих, то стана неактивно. ...."

Наистина не работеше и реших да го симулирам:)

Списък в колонка C

Колонките D и E са работни може да ги сложите по-далече (може и на друг лист)! Може даже да ги скриете:)

Стъпка 1: Формула в D2-> =IF(COUNTIF($C$2:C2;C2)=1;ROW(C2);"")

Това е лесно за разбиране. Просто там където за първи път се появява дадена стойност "маркира" реда слагайки номера на реда. Размножавате формулата надолу.

Стъпка 2: Формула в E2 ->  =IFERROR(INDEX(C:C;SMALL(D:D;ROW(A1)));" ")
"Пакетиране" на уникалните стойности. Този номер ни е познат от друг цирк:):)

Стъпка 3: Декларираме име (Formulas/Define Name)  values1 с формула за Refers To:
=OFFSET(Sheet1!$E$1;0;0;50-COUNTIF(Sheet1!$E$1:$E$50;" ");1)

Това се прави за да се извлекат само клетките с конкретна стойност. Обърнете внимание, че ако горната формула върне грешка в клетката се връща ИНТЕРВАЛ! За това не минава номера с CountA.  Произволно е решено, че уникалните стойности са по-малко от 50! Ако прецените може да увеличите тази константа.

Дефиниране на име

 

Стъпка 3: Контрол на колонката C. Избираме клетките и изпълняваме Data/Data Validation

Стъпка 3.1 Избор типа на контрол:

Контрол чрез списък

Тук няма интрига:):) Използваме името за да контролираме данните.

Стъпка 3.2: Изключваме контрола при въвеждане!!!!!!

Премахване на контрола

Най-накрая си дойдохме на думата защо съм сложил това "тъпо" заглавие на темата:):):):) Всъщност Data Validation не прави никакъв контрол! Позволява да въвеждаме стойности които ги няма в списъка! Проверка няма:) Само използваме възможността да показва списък в дадената клетка! Та както виждате (може би за първи път) Data Validation без validation!:) 

Готовооо... Всяко добавено нещо се появява в списъка.... Даже числата......:)

успех

#044 Въвеждане на уникални стойности

Един мини трик за използване на Data validation.

Проблем: Да се въвеждат в дадена зона стойности без повторение.

Предупреждение за дублиране

Решение: Избират се клетките и се изпълнява Data/Data Validation

Контрол чрез формула

Настройка на съобщението за грешка

И готово:) Лесен "трик" за все още неизтрезнелите от Нова Година:)

#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 винаги десетичния разделител е ТОЧКА, разделителя на аргументите е ЗАПЕТАЯ!