#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