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

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

успех

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

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

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

#032 Функция SubTotal и защо е по-добра от Sum

Да погледнем #030 в реда за обобщение. Там намираме не Sum и Average ами функцията SubTotal. Да се замислим защо Excel използва нея а не "класическите" функции. SubTotal не се преподава, но е доста по-мощна и гъвкава от Sum и т.н. Та в общи линии първия параметър е число показващо каква е използваната функция а втория е самата зона. Ето стойностите на първия параметър:

Функция_ном (включва скрити стойности)	Функция_ном (игнорира скрити стойности)	Функция
1	101	AVERAGE
2	102	COUNT
3	103	COUNTA
4	104	MAX
5	105	MIN
6	106	PRODUCT
7	107	STDEV
8	108	STDEVP
9	109	SUM
10	110	VAR
11	111	VARP

Вижда се, че имаме две възможности за всяка функция. СЪС и БЕЗ да се включват скритите стойности! Тук се крие силата на тази функция!  По време на правенето или използването  на една таблица "скриваме" редове или колонки както с помощта да Hide или с помощта на филтрите. Сега е моментът да се замислите че SUM смята ВСИЧКО което му е подадено като параметър без да го е грижа какво се вижда и какво не! Т.е. "това което виждате може да НЕ е това което се сумира"!:) За разлика от SUM/Average и т.н. SubTotal ви дава право на избор!

=SubTotal(9,A1:A100) е пълен аналог на =SUM(A1:A100) сумирайки независимо дали са видими, докато =SubTotal(109,A1:A100) ще зависи кои клетки от зоната са видими! Този "малък" на пръв поглед нюанс може да ви създаде главоболия:) Забелязал съм, че рядко се набляга на този "дефект" на класическите функции и сякаш никога няма да мине през акъла на някой да скрива или филтрира:):)

Както казах обаче при таблиците в реда за обобщаване Excel "мъдро" слага правилните функции (т.е. SubTotal с отчитане на скритите редове). Ако не искате това ще се наложи да подмените предложените от Excel функции с "вашите" любими такива:) Чара на SubTotal е че може да работи и като класическа функция, за това просто сменяте първия параметър и сте ОК:)

NB! Да бъдем коректни не винаги е възможно използването на SubTotal! Тя не възприема така наречените 3D зони (зони които са между няколко работни листа). Например =SUM(Sheet1:Sheet4!A1) няма как да я подмените с SubTotal! За това не бързайте да погребвате Sum, Average и т.н. :):)

Като бонус ето как се прави табличка за демонстрация възможностите на SubTotal:)

Табличка за SubTotal

Условие: Когато потребителя пипа C1 и C2 да се вижда правилния резултат.

0. Подготовка. Колонките D, E и F са помощни и спокойно може да са на друг лист да не загрозяват пейзажа:) Или просто ги скрийте когато приключите с настройките и видите, че всичко е ОК

1. Дефиниране на имена. Понеже все забравям да направя тема за именуване сега ще пиша много:( Трябват ни три имена "Функции", "Всички", "Видими" за данните в помощните колонки. Най-бързия начин е да изберете клетките от D1 до F12 (данните с имената!) и да изпълните командата Formulas/Define Names/From Selection и да посочите че използвате и заглавния ред и първата колона. Така ще получим освен трите имена на колонките и имената "Average" за първите две стойности, "Count" за вторите и т.н.

Създаване на имена

Резултат от именуването (проверка чрез Formulas/NameManager)

Тук има един малък проблем. Името "Функции" сочи към данните а ние искаме да сочи към имената на функциите. За това трябва да оправим този проблем като редактираме името от Name Manager.

Редактиране на името "Функции"

2. Контрол на C1 и C2. За да може потребителя да избира стойности за контролиране на C1 използваме списъка "Функции",  а за C2 зоната E1:F1. (Вижте #002).

Контрол на C2

3. Формула в C3. Ще дам два варианта да е по-весело:) И да имате теми за размисъл и четене:)

Вариант 1: =SUBTOTAL(INDEX(INDIRECT(C2),MATCH(C1,Функции,0)),A1:A24)

Тук извличаме номера на функцията чрез Index:

INDIRECT(C2) - "Обръща" съдържанието на клетка C2 в зона. (Всички/Видими). Така получаваме зоната от която ще извличаме числото.

MATCH(C1,Функции,0) -  Тук намираме редът от който извличаме търсейки съдържанието на C1 в зоната Функции (за това и се наложи корекция!)

Вариант 2: =SUBTOTAL(INDIRECT(C1) INDIRECT(C2),A1:A28) 

 

Извличането на стойността е в израза:

INDIRECT(C1) INDIRECT(C2) - Това е странен израз. Две зони разделени с интервал. Тук хитростта е че се намира "сечението" на двете зони. Зоната C1 (това е хоризонталната зона на съответната функция) определя реда, Зоната C2 пък представяла колоната. Например ако сме избрали Max и Видими  се търси сечението между E5:F5 (зона Max) и F2:F12 (зона Видими). Сечението (общото между двете зони) е клетка F5. Която и съдържа това което ни трябва!

Явно и тема за зоните трябва да понапиша:)

Успех със SubTotal:)

#019 Контролни списъци които зависят един от друг (Adv)

Както вече споменах в тази тема ще разгледам по-сложен контрол с помощта на списъци които зависят един от друг. Ако не сте прочели #018 (и свързаните с нея теми) го направете сега. Методът използван в #018 има два недостатъка. 

Първият недостатък в използвания в #018 е във фактът, че ако в първия контролен списък има текст с интервали не е възможно да създадем именувана област съдържаща интервали. Този проблем се решава лесно. Ако в първия списък имаме стойност например "малко дете", създаваме именувана област с име "малкодете" (без интервали!). След което видоизменяме контролиращия списък (в примера #018 клетка B2) като =indirect(substitute(B1," ",""). Просто използваме вече дискутирана функция Substitute за премахване на интервалите. Елементарно;)

Вторият проблем е по-сложен и изисква малко по-сложни действия. Проблемът е, че първия списък е статичен. Потребителя не може да въвежда стойности в него. Когато направим и първия контролен списък динамичен се оказва, че няма как да използваме Indirect по простата причина, че при разработката на приложението ние не знаем какво ще реши да въведе потребителя в първия списък (и респективно да създадем съответната именувана област) :( 

Пример: Да се оценяват студенти в зависимост от различни методи за оценки.

Оценките да зависят от избрания метод (държава)

Оценките на студентите (жълтите клетки) да са съобразени избрания метод за оценяване в клетката B1. Потребителя да може да добавя и редактира начините за оценяване в отделна област.

Да предположим, че областта за видовете оценки започва от клетка F1. (Hint! По-добре е самата област да бъде на друг лист!)

За за въвеждане на видовете оценявания на начало клетка F1

В посочената зона потребителя ще въвежда в първия ред с думи типът на оценяването и вертикално разрешените стойности. Дал съм примери. За повече информация може да надзърнете на адрес http://en.wikipedia.org/wiki/Grade_%28education%29 (Типът оценяване от колонка L няма да го намерите там):):)  Както се вижда различните начини на оценяване имат различен брой разрешени стойности.

1. Създаване на динамична именувана област за видовете оценявания. Тук се използва техниката от #001. За име на областта задайте "GradeType" а за Refers to: =OFFSET(Sheet1!$F$1,0,0,1,COUNTA(Sheet1!$1:$1)-2). Областта е хоризонтална. (NB! това "магическо" оцветено в червено -2 е заради фактът че на първи ред имаме две пълни клетки в повече (А1 и В1)!! Ето за това и препоръчвам зоната да бъде някъде отделно където няма да има такива "смущения"!)

Команда Formulas/Define name

2. Създаване на именувана област за оценяване. Тази област трябва да е динамична вертикално в зависимост от броя на оценките и зависи от стойността на B1.  Задава се име на областта "Grade" и сочи към формулата :

=OFFSET(Sheet1!$F$1,1,MATCH(Sheet1!$B$1,GradeType,0)-1, COUNTA(OFFSET(Sheet1!$F$1,1,MATCH(Sheet1!$B$1,GradeType,0)-1,100,1)))

Не се плашете много:) Вече говорихме за Offset. Сигурно скоро ще има тема и за Match. В случая Match намира колонката на съответния тип оценяване в зависимост от съдържанието на B1.

MATCH(Sheet1!$B$1,GradeType,0)-1 (NB! -1 е защото Offset Брои от НУЛА!).

Часта : COUNTA(OFFSET(Sheet1!$F$1,1,MATCH(Sheet1!$B$1,GradeType,0)-1,100,1)))

намира колко пълни клетки има вертикално.   Тъй като трябва да броим в някаква област предварително задаваме област от 100 (множко е) реда и с CountA намираме реалния брой редове. 

3. Контрол на клетката с видовете оценявания. За клетката B1 се изпълнява Data Validation с контрол по списък =Gradetype след което се избират жълтите клетки и се задава DataValidation по списъка Grade.

Това е:) "Само" три стъпки:)

#018 Контролни списъци които зависят един от друг (easy)

Отново ще дискутираме темата за DataValidation с помощта на списъци. Този път ще се спра на темата как да направим списък който се влияе от друг списък. Няма да е зле отново да прочетете #001, #002 и #004.

Първия пример е доста статичен (скучен бих казал), но в доста голям брой случаи в моята практика използваната  техника върши работа.

Свързани списъци

Да направим списък за контрол, който да се променя в зависимост от избрания пол.

Стъпка 1. Контролираме въвежданите стойности в полето Пол. Понеже стойностите са константни може да използваме Data Validation с твърдо зададени стойности. Избираме B1 активираме Data/Data Validation и въвеждаме стойностите в списъка.

Статични стойности за контрол

Стъпка 2. Създаваме списъците за контрол. Въвеждаме данните за облеклата на мъжа и жената. След което се избират данните за мъжа и се задава име  на областта  "мъж" (точно каквато е стойността за валидиране!). По аналогичен начин се създава и именувана област "жена" с облеклата за жената. NB! Не забравяйте да натискате Enter след като въведете името на избраните клетки!

Област с мъжките дрехи

Област с женските дрехи

Стъпка 3. Валидиране на клетките в зависимост от стойността на пола. Избираме клетките които ще контролираме (в случая само клетка B2). Активираме командата Data Validation. Избора се контрол по списък и в полето Source въвеждаме =Indirect(B1).

Свързан списък

Това е:)  Сега когато се избере пол в клетката B1  видовете дрехи се променят. Този пример има недостък, че зоните "мъж" и "жена" са статични. Т.е. ако добавим нови стойности ще се наложи да правим промяна в името. За да направите примера още по лесен за потребителя, вместо посочения от мен начин за именуване използвайте динамично именуване (описано в #001) при създаване на областта "мъж" и на областта "жена" (разбира се няма да бъде A1 ами D1 (Е1) като начало на зона).  Така потребителя сам лесно може да си дописва дрехи в съответната област и тя сама ще се разширява. Успех:)

Цялата "магия" се крие във функцията Indirect. Тя служи за преобразуване съдържанието на клетката B1 в адрес (в случая именувана зона). Както вече обърнах внимание е важно името на зоните да бъде същото както е името на стойностите в B1! Мисля, че сте се досетили, че ако стойностите в първия списък са повече за всяка от тях трябва да създадете съответната именувана област!

Следващата тема ще бъде малко по-сложен начин за свързани списъци:)

#004 Контролиране с уникален избор

#Често са ме питали как става контролиране на стойности от списък, от който дадена стойност отпада ако вече е избрана. Т.е. една стойност може да бъде избрана само един път.

Ето пример:

Да се контролира зоната A10:F10 с единичен избор от елементите от списъка в колонка I

Стъпка 1. Подготовка. Както се вижда контролираната зона е непрекъсната. Може да бъде и вертикална. Разбра се може да бъде прекъсната и "разхвърляна", но това би утежнило формулите.

Стъпка 2. Помощна колонка 1 (Колонка J). В тази колонка ще отбелязваме само елементите на списъка, които все още не са избрани. За целта в клетка J1 въвеждаме следната формула: =IF(COUNTIF($A$10:$F$10,I1) < 1,ROW(I1),""). Където A10:F10 е контролираната зона и се нуждае от настройка за вашия пример. (Hint! Ако искате даден елемент да бъде избиран не един ами 2,3 или повече пъти просто частта от формулата "<1" я променяте на "<2" , "<3" и т.н.)

Размножете ("дръпнете") формулата надолу.

Стъпка 3. Помощна колонка 2 (Колонка K). В тази колонка ще се показват сбито само нужните елементи. За целта в клетката К1 въведете следната формула: =IFERROR(OFFSET($I$1:$I$15,SMALL($J$1:$J$15,ROW(I1))-1,0,1,1),""). Размножете формулата надолу.

Стъпка 4. Дефиниране на вертикален динамичен блок. Създайте динамично име Vlist (описал съм процеса в отделна тема) със следната формула:
=OFFSET(Sheet1!$K$1,0,0,COUNT(Sheet1!$J:$J),1)

Динамична област

(NB!. Тук има малък "трик". Обърнете внимание, че данните се "вземат" от помощната колонка "К", а се броят ЧИСЛАТА (използва се Count а не CountA!) от колонка J!. Това се налага поради факта, че независимо, че не се виждат стойности в колонката К, клетките до края са запълнени с формули и Count в тази колонка винаги ще върне 15!)

Стъпка 5. Контролиране на областта с помощта на динамичния списък. Избират се клетките, които ще контролираме (в примера A10:F10) и се изпълнява командата Data/Data Validation (има отделна тема)

Контрол на областта. Предварително изберете всички клетки!

Това е:) Enjoy

#002 Валидиране със списък

Доста добра възможност да се ограничи потребителят да въвежда грешни данни е те да му се предоставят във вид на списък... Няма да се спирам в детайли на командата Data/Data validation (ALT+A,V,V), а ще разгледам само режима на разрешени данни List.

В полето source може да има:
1. Стойности. NB! трябва да се използва подходящият за случая списъчен разделител. Виж първата тема!

Стойности

2. Зона от клетки

Зона от клетки

3. Дефинирано име. Hint! Най-лесен начин за избор на дефинирано име е използването на клавиша F3). Тук може да се използват динамични области обяснени в отделна тема.

Именована зона (може и динамично разширяваща се)

В резултат на използването на този вид контрол на въвеждането, в избраните клетки се появява списък за избор на стойности.

Списък с разрешените стойности в клетката която се валидира

#001 Динамични имена

Този "трик" често е полезен и ще го използвам и в други примери... С него се илюстрира как се декларира име на зона, която сама се разширява при попълване на данни в нея... Често такива зони се използват за падащи списъци при контрол на въвеждането....
Проблем: Да се създаде именувана зона която да се разширява (свива) при допълване (изтриване) на данни....

Вариант 1: Вертикална зона

Вертикална зона

Създаваме име с командата Formula/Define Name (или Ctrl+F3, New) като за име въвеждаме Vlist, а в полето refers to въвеждаме:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

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

Забележки: Sheet1 е листа в който е списъкът, A1 е началната клетка на списъка

Вариант 2: Хоризонтална зона

Хоризонтална зона

При добавяне на ново име задаваме hlist и в полето Refers To въвеждаме :

=OFFSET(Sheet1!$A$1,0,0,1,COUNTA(Sheet1!$1:$1))

Ако списъкът започва от клетка A1.

Да се има предвид следният "бъг":)! Посочените формули броят всички пълни клетки в колоната (реда) и разширяват зоната спрямо началната клетка. Проблемът възниква ако се въведат данни на разстояние от последната запълнена клетка. В този случай зоната се разширява с празни клетки!