#051 Сортировка с формули - втора част

NB! Преди да продължите, моля прочете първата част от темата #050 Сортировка с формули - първа част

II. Сортиране на данните

В тази част ще извършим сортирането на данните... Ето изходните данни върху които ще илюстрираме.

1. Създаване на помощни колони

Колонките C и D са помощни колонки (ПК звучи почти като "пожарен кран":) Разбира се може да ги сложите където си искате в работния лист и да ги скриете (и сложите по-умни имена)! Използвани са знанията от първата част на темата!

Формулата в C2 е:
=COUNTIF($A$2:$A$8;"<="&A2)-COUNTIF(A2:$A$8;"="&A2)+1
Формулата в D2 е:
=COUNTIF($B$2:$B$8;">="&B2)-COUNTIF(B2:$B$8;"="&B2)+1

Умишлено съм обърнал знака във втората формула да е ">="! Така получавам ранговете на заплатите в "обратен" ред (от голяма към малка заплата!). Ако искате заплатите и да са във възходящ ред просто изменете знака във формулата (и не забравяйте да я размножите надолу!)

В резултат на тези формули в двете помощни колонки получаваме ранговете ("нечестни") за имената и заплатите.

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

За по-лесното изграждане на формулите съм създавал имена на области (така и не направих тема за именуването:() :

=$A$2:$A$8 - Имена
=$B$2:$B$8 - Заплати
=$C$2:$C$8 - ПК1
=$D$2:$D$8 - ПК2

NB! В общи линии не умна идея да пишете имената кирилица! За да стане още по-гъвкава таблицата може да използвате динамични имена (има тема!) които да се разширяват при добавяне на данни!

3. Сортировка

След тези подготовки самата сортировка е фасулска работа, ако си спомняте темата за Index/Match;))!

Формулата в А2 е:
=INDEX(Имена;MATCH(ROW(A1);ПК1;0))
Формулата в B2 е:
=INDEX(Заплати;MATCH(ROW(A1);ПК1;0))

NB! Обърнете внимание, че и в двете формули използваме помощна колонка ЕДНО!! Сортировката е според ПЪРВАТА колонка (в случая имената) и за това заплатите трябва да съответстват на имената!

Идеята е просто да търсим 1,2,3,.... в помощната колонка и да извличаме съответното име/заплата. "Броенето" се реализира чрез Row(..). Ако имате колонка в която да има 1,2,3 (примерно номерация) можете да използвате нейното съдържание.

Ако искаме да сортираме по заплати....

Формулата в А2 е:
=INDEX(Имена;MATCH(ROW(A1);ПК2;0))
Формулата в B2 е:
=INDEX(Заплати;MATCH(ROW(A1);ПК2;0))

NB! И на двете места използваме ВТОРА помощна колонка!

Ми това е:) Успех:):)

P.S. И едно предизвикателство :):) Да се тествате сами себе си:) Опитайте се сами да направите сортировка по два ключа (две колонки). Например в сегашния вариант ако имаме две еднакви имена по-напред се слага името което е било по-нагоре в изходните данни. Помислете как да се подрежда по-нагоре името с по-голяма заплата! В примера имаме две еднакви имена Мария Петрова и в нашия случа се подреждат "грешно"!

#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! Мисля, че сте се досетили, че ако стойностите в първия списък са повече за всяка от тях трябва да създадете съответната именувана област!

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

#008 Сортиране при въвеждане на данните

Тук ще покажа как може да се направи така, че данните да се сортират динамично при въвеждане на данни в дадена област.
Ето примерния лист:

Сортировка в "движение"

При промяна  (въвеждане, изтриване или редактиране) на данните в областта A:B те автоматично се появяват е зоната F:G сортирани по факултетен номер.

1. Подготовка. За по-лесно създаване на формулите ще дефинираме две динамични имена (описани в тема #001).
Име FN (факултетен номер) сочещо към =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
Име Danni (факултетен номер и име на студента) сочещо към =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,2)
NB! В случая се вижда че се вади единица от броя на редовете заради заглавията на колонките!
NB! Разликата между двете имена е, че втората област се състои от две колонки (последния параметър)!

2. Формула в клетка F2 - =IFERROR(SMALL(FN,ROW(A1)),"")
Ето анализът на тази формула:

• Променлив брояч. Row(A1). За разлика от примера с ЕГН-то тук искаме във формулата да имаме  стойност която да се се променя от едно до N в зависимост от броя на редовете в които се размножава формулата. (Ще обясня по-късно защо). Има по обикновен вариант поставяйки допълнителна колонка и в нея да се въведе стойността на брояча. Всъщност от израза Row(A1) ни интересува върната стойност 1 (реда на клетката А1) , която ще стане 2,3,4 и т.н. когато формулата се размножи надолу (А1 ще стане А2,А3 и т.н. и съответно и резултатът на Row ще се промени)...NB! Със същия успех вместо A1 може да използваме B1, X1 или която и да е клетка от първи ред.
• Намиране на N-тата по големина стойност... SMALL(FN,ROW(A1)). За първата клетка ще върне най-малката стойност, за втората клетка втората по големина и т.н. Именно за тази цел използвахме брояча. Hint! Ако искате да се подреждат в обратен ред се използва функцията Large!
• "Пакетираме" в IFError защото в даден момент функцията Small ще върне грешка когато в зоната няма вече стойности. (грешка #Num).

3. Формула в клетката G1. =IFERROR(VLOOKUP(F2,Danni,2,FALSE),"")
Запълването на имената става, чрез точно търсене (параметър False) с Vlookup в зоната за въвеждане. Връща се съдържането на втората колонка (в случая името). Пакетирането в IFError вече не го коментирам:)

NB! Имейте предвид, че този прмер ще работи при УНИКАЛНИ стойности в колонката която сортираме (в нашия случай Факултетен номер)! В случай на дублажи Vlookup няма правилно да определи съдържанието на втората колона.

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

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