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

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

успех

#006 Брой на уникалните стойности в зона

В някой примери се налага да се преброят уникалните стойности в група клетки.  Формулата която се използва в случая е:

=SumProduct(1/CountIf(Зона,Зона)). Първия път когато срещнах тази формула ми се видя доста чудата и за това ще се опитам накратко да анализирам как действа с реален пример.

Пример: Да се намери броя на уникалните стойности в зоната A1:A8.

Зона клетки

Отговор: =SUMPRODUCT(1/COUNTIF(A1:A8,A1:A8)). Ако въведете тази формула ще видите верния отговор 5.  Както се вижда формулата работи и за текстови и за числови данни. Зоната не е задължително да се състои от една колона.

1. COUNTIF(A1:A8,A1:A8) - До сега най-вероятно сте използвали CountIF с единична стойност за втория параметър (условието за броенето). Оказва се, че и условието може да бъде зона. Всъщност в този случай функцията се "развива" според втория параметър  (само по втория!) и се получават 8(!) отделни функции...

• COUNTIF(A1:A8,A1) = COUNTIF(A1:A8,"aaa")=3 ("aaa" го има три пъти!)
• COUNTIF(A1:A8,A2) = COUNTIF(A1:A8,"111")=2 ("111" го има два пъти!)
• COUNTIF(A1:A8,A3) = COUNTIF(A1:A8,"bbb")=1 ("bbb" го има само един път!)
• и т.н.

2. В резултат на изпълнението на функцията се получава масив от осем елемента:
{3,2,1,3,1,2,1,3}
3. Върху този масив се прилага операцията 1/{}. Което дава следния резултатен масив:
{1/3, 1/2, 1, 1/3, 1, 1/2, 1, 1/3}. Всъщност това е вид "нормализиране" на стойностите които ги има повече от един път.
4. Върху приложения масив се използва функцията Sumproduct (поради дискутирания "дефект" на Sum при работа с масиви) за намиране сумата на елементите която е 5 (броя на уникалните стойности).

Ето така действа тази "магическа" функция:)