Excel трикове: януари 2012

Ето ви едно малко по-сложно изпълнение базирано на 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!:)

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

успех