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

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