Първият ред на всяка таблица се нарича антетка/хедър, наименование на колони/
Списъците (Lists) представляват таблици, изградени от последователно подредени записи (records) със сходно съдържание и еднаква дължина. Всеки запис се състои от полета (fields), всяко едно от които притежава свое собствено уникално име. Един списък може да съдържа до 32 полета. Списъците могат да бъдат разглеждани като прости бази данни, състоящи се от една таблица.
Филтриране означава да бъдат издирени и изобразени на екрана записи от таблицата/списъка/, които удовлетворяват предварително зададени условия.
Филтрирането е бърз и лесен начин за намиране на подмножество от данни в един диапазон и работа с него. Филтрираният диапазон показва само редовете, които отговарят на критериите, зададени за една колона. За разлика от сортирането, филтрирането не пренарежда диапазона. Филтрирането временно скрива редовете, които не искате да се показват.
Филтрирането включва в себе си операцията за търсене.
Excel предлага два вида филтри – Автоматичен филтър/AutoFilter/ и Разширен филтър/Advanced Filter/. Те биват задействани съответно чрез командите Data/Filter/AutoFilter и Data/Filter/Advanced Filter.
Автоматичен филтър/ AutoFilter/
Автофилтрирането включва филтър по избор за прости критерии
Когато използвате командата Автофилтриране, вдясно от етикетите на колоните на филтрирания диапазон се появяват стрелки за автофилтриране .
Microsoft Excel отбелязва филтрираните елементи със син цвят.
Процедурата за работа с AutoFilter е следната:
1. Маркирва се списъка, заедно с 1-вия ред/антетката, хедера/ и се подава командата Data/Filter/AutoFilter. В Excel 2003, ако таблицата е била дефинирана предварително като списък (чрез командата Data/List/Create List), достатъчно е да щракнем някъде вътре в него. В резултат в антетката, в десните краища на полетата й, се появяват стрелкови бутони. Щракването на даден бутон отваря под него кадър, съдържащ опции.
2. Щраква се онзи бутон, според чието поле ще става филтрирането. В резултат под бутона се отваря кадърът с опции. Например, в колоната Доход на списъка се съдържат различни стойности, а нас ни интересуват само записите, в които стойността е 160.44 лв. Тогава в кадъра трябва да се избере опцията 160.44 лв. В резултат Excel ще скрие всички записи, като на екрана ще остави само онези, които отговарят на условието - доходът да е равен на 160.44 лв.
За да бъде възстановен първоначалният вид на списъка, може да се подаде командата Data/Filter/Show All или да се щракне повторно стрелковия бутон и в кадъра да се избере опцията (All).
3. След приключване на филтрацията премахването на стрелковите бутони се извършва чрез повторно подаване на командата Data/Filter/AutoFilter.
В кадъра с опциите се съдържат и опциите (Top 10...) и (Custom...). В Excel 2003 са добавени и опциите Sort Ascending и Sort Descending, посредством които може да се предприема сортиране на записите съответно във възходящ или низходящ ред на стойностите, съдържащи се в полето на стрелковия бутон.
В разглеждания списък, ако щракнем стрелковия бутон на полето Доход и в кадъра под него изберем опцията (Top 10…), ще се отвори прозорецът от фиг.7. Той служи за задаване на условие за филтриране. В първия списък на прозореца се съдържат две опции – Top и Bottom. С тяхна помощ се указват най-общо следните клаузи, които подлежат на допълнително уточняване посредством останалите два списъка на прозореца:
• Top. Да се извлекат записите, в които доходът е най-висок.
• Bottom. Да се извлекат записите, в които доходът е най-нисък.
Фиг.7
Посредством втория списък се задава броят на записите, които да бъдат извлечени на базата на някоя от зададените опции Top или Bottom. Например за разглеждания списък, ако сме избрали Top и зададем 2, ще бъдат извлечени записите с доходи 200.25 лв. и 346.00 лв.
В третия списък на прозореца от фиг.7 се съдържат опциите Items и Percent. При избран Items филтрирането на доходите протича, така както бе посочено по-горе. При Percent стойността от втория списък на прозореца се разглежда като процент от записите, които следва да бъдат извлечени. Например при опция Top и 40 ще бъдат извлечени 40% от записите, съдържащи най-високи доходи. За разглеждания от нас списък, това ще бъдат 2 записа (0.4 * 5 = 2) - втория и третия. При Bottom и 60 ще бъдат показани 3 записа – първия и последните два.
Опцията (Custom…) от кадрите на стрелковите бутони позволява дефиниране на условия, които освен оператори за сравнение съдържат и една от логическите функции AND или OR. На фиг.8 е показан пример, в който е зададено условие за извличане на записи, в които доходът е по-нисък или равен на 80 лв. или по-висок или равен на 200 лв.
Фиг.8
Разширен филтър/Advanced Filter/
Процедурата за работа с Разширен/Advanced/ Filter е следната:
1.Създават се предварително 3 области, като за целта 1 –вия ред на таблицата/антетката/ се копира на още 2 места:
• Входна област – цялата таблица или част от нея, която ще филтрираме. Включва и първия ред от таблицата.
• Област на критерия – включва първата копирана антетка и условието на критерия.
• Изходна област – включва втората копирана антетка и показва областта, в която ще се копират записите.
2. Съставя се условието, според което ще протече филтрацията и същото се въвежда в избрана от потребителя област на таблицата в близост до списъка – обикновено горе вдясно от него или над него. Какви могат да бъдат условията и как те се въвеждат се разглежда подробно по-долу, след описание на процедурата.
Фиг.9
2. Активира се някоя от клетките на списъка или се маркирва целия списък, заедно с 1-вия ред/антетката/, след което се подава командата Data/Filter/Advanced Filter. В резултат Advanced Filter извежда прозореца от фиг.9. В него се съдържат два радиобутона – “Filter the list, in-place” и “Copy to another location”. С тяхна помощ се указва, къде да бъде разположен резултатът от филтрацията – в мястото, където е разположен списъка (“Filter the list, in-place”) или на друго място (“Copy to another location”).
Полето “List range:” на прозореца служи за указване координатите на входната област, съдържаща списъка, включително хедера му.
В полето “Criteria range:” потребителят посочва координатите на областта на критерия, в която той, съгласно първата стъпка от процедурата, предварително трябва да е въвел условието за филтриране.
Полето “Copy to:” бива използвано тогава, когато желаем резултатът от филтрацията да се разположи извън областта, заемана от списъка. В него се указват координатите на областта, в която да бъде изобразен резултатът. За да може полето “Copy to:” да бъде използвано, трябва да е бил активиран радиобутонът “Copy to another location”. Иначе полето остава недостъпно, а резултатът от филтрацията се разполага в мястото, заемано от самия списък. В този случай, след филтрацията, за да бъде възстановено отново изображението на списъка, трябва да се подаде командата Data/Filter/Show All.
Опцията “Unique records only” (фиг.9), когато е активирана, задава режим, в който, при наличие на записи, съдържащи еднакви данни в колоната, според която протича филтрацията, да бъде изобразен в резултата само един от записите, а не цяла група записи. Например при студенти с еднакъв успех да бъде извлечен записа само на един от тях.
След като приключи работата с прозореца от фиг.9 щраква се [OK] и Advanced Filter ще осъществи желаната филтрация.
Задаване на условия за филтрация в Advanced Filter
Условията биват три вида – прости, съставни и изчислими. Простите съдържат само един оператор за сравнение и само едно име на поле. Съставните условия могат да включват повече оператори за сравнение и повече имена на полета и да реализират логическите операции AND и/или OR. Изчислимите условия ползват резултати от някакви изчисления, извършени преди да бъде осъществена филтрацията.
Задаване на прости условия
Нека зададем условие за извличане на записите на онези студенти, на които доходът е по-голям или равен на 180 лв. Приемаме да разположим условието в клетките F1:F2 (фиг.10).
. . . . . . F . . .
1 Доход
2 >= 180 лв
.
Фиг.10
Името в първия ред на условието трябва да съвпада абсолютно точно с името на полето от списъка, за което е съставено условието. За избягване на евентуални грешки от въвеждане желателно е името на условието да се копира от списъка посредством командите Copy и Paste.
В съответствие със стъпка 2 от описаната по-горе процедура координатите F1:F2 трябва да бъдат въведени в полето “Criteria range:” на прозореца от фиг.9.
При филтрация по зададен текст-еталон са в сила следните правила:
• Когато условието се състои само от текст, без каквито и да било оператори за сравнение, извличат се всички записи, в които текстът на филтрираното поле съвпада с текста-еталон. Например, ако текстът-еталон е Стоян Димов (фиг.11), ще бъде изведен само записа на студента Стоян Димов. За извършване на филтрацията е необходимо в полето “Create range:” (фиг.9) да се посочи областта F1:F2.
• Когато текстът-еталон се състои само от една единствена буква, извличат се всички записи, в които текстът на филтрираното поле започва с тази буква. Например, за да бъдат извлечени записите на всички студенти, чиито малки имена започват с буквата „С”, текстът-еталон трябва да бъде С.
. . . . . . F . . .
1 Студент
2 Стоян Димов
.
Фиг.11
• Въвеждането на оператор за сравнение пред текста-еталон, предизвиква сравняване на текста от филтрираното поле с текста-еталон и извличане на онези записи, които удовлетворяват условието. Например, ако във втората клетка на условието от фиг.11 бъде записано > Ганка Вичева, ще бъдат извлечени записите, в които филтрираният текст започва с буква, чийто код е по-голям от този на буквата „Г”, т.е. текст, който започва с буква, която в азбуката се намира след буквата „Г”. В кодовата таблица на символите кодовете на всички малки букви се намират след кодовете на големите букви.
А какво следва да се прави, когато операторът за сравнение трябва да е (=). Excel би възприел условието като формула! За избягване на недоразумението, условието следва да се запише без оператора (=), като например Стоян Димов или пък заедно с оператора, но по специфичен начин, като например =“=Стоян Димов”. Ако във втората клетка на условието от фиг.11 въведем =“=Стоян Димов”, в нея Excel ще изпише =Стоян Димов и ще бъдат извлечени записите на всички студенти с имена Стоян Димов. От разглеждания списък (фиг.2) ще бъде извлечен само един запис, тъй като в него се съдържа само един студент с такова име.
Excel допуска в текстовете-еталони използване на глобалните символи-заместители (*) и (?). Например, за да бъдат извлечени записите на всички студенти, чиито фамилни имена са Димов, условието може да бъде записано по два начина - като * Димов или като = “ = * Димов”. А, за да бъдат извлечени записите на всички студенти, чиито фамилни имена започват с буквата „Д”, условието трябва да се запише като *Д.
Задаване на съставни условия
Съставните условия включват повече имена на полета и повече оператори за сравнение и позволяват използване на логическите операции AND и OR. Обикновено те заемат повече редове и колони.
Реализация на логическата операция AND
Условията, свързани помежду си с операцията AND, следва да се разполагат в съседство, едно до друго, заемайки едни и същи редове. Например условието: „Да се извлекат записите, в които доходът е по-голям или равен на 180 лв. и успехът е по-голям от 4.50”, се записва по начина, показан на фиг.12.
. . . . . . G H . . .
1 Доход Успех
2 >= 180 лв > 4.50
.
Фиг.12
За извършване на филтрацията е необходимо в полето “Create range:” (фиг.9) да се посочи областта G1:H2.
Условието: „Да бъдат извлечени записите на студентите с фамилно име Димов, чиито доход е по-голям или равен на 180 лв. и чиито успех е по-голям от 4.50”, се записва така, както това е показано на фиг.13.
. . . . . . F G H . . .
1 Студент Доход Успех
2 * Димов >= 180 лв > 4.50
.
Фиг.13
За извършване на филтрацията е нужно в полето “Create range:” (фиг.9) да се посочи областта F1:H2.
Реализация на логическата операция OR
Условия, касаещи едно и също поле
Когато условията са повече, но касаят едно и също поле и същите трябва да бъдат свързани помежду си с операцията OR, те се разполагат едно под друго, както това е показано на фиг.14. Например условието: „Да бъдат показани записите, в които доходът е по-голям от 200 лв. или по-малък от 100 лв.”, се записва така, както това е показано на фиг.14.
. . . . . . F . . .
1 Доход
2 > 200 лв
3 < 100 лв
.
Фиг.14
За извършване на филтрацията е необходимо в полето “Create range:” (фиг.9) да се посочи областта F1:F3.
Условия, касаещи повече полета
Когато условията са повече, но касаят различни полета и същите трябва да бъдат свързани помежду си с операцията OR, те трябва да се разполагат едно до друго, като имената на полетата трябва да са на един и същ ред, а изразите да заемат различни, но съседни редове. Например условието: „Да се извлекат записите, в които доходът е по-голям или равен на 180 лв. или успехът е по-голям от 4.50”, се записва така, както това е показано на фиг.15.
. . . . . . G H . . .
1 Доход Успех
2 >= 180 лв
3 > 4.50
.
Фиг.15
За извършване на филтрацията е необходимо в полето “Create range:” (фиг.9) да се посочи областта G1:H3.
Реализация на условия, съдържащи едновременно операциите AND и OR
Условието: „Да се извлекат записите, които отговарят на следните условия:
доходът да бъде по-голям от 200 лв. и успехът по-голям от 4.00
или
доходът да е по-малък от 100 лв. и успехът да е по-голям от 2.00.”
се реализира по начина показан на фиг.16. Всяко условие, съдържащо операцията AND, се записва на един ред, а свързването им с операцията OR се постига, като същите се разположат едно след друго в съседни редове.
. . . . . . G H . . .
1 Доход Успех
2 > 200 лв > 4.00
3 < 100 лв > 2.00
.
Фиг.16
За извършване на филтрацията е необходимо в полето “Create range:” (фиг.9) да се посочи областта G1:H3.
Работа с повече съставни условия
Когато при работа с един и същ списък, потребителят използва в различно време различни условия, целесъобразно е всички условия да бъдат предварително въведени в близост до списъка. На фиг.17 са показани две такива условия. Не е задължително условията да се намират в съседни колони.
В примера от фиг.17, когато потребителят реши да филтрира по условието от колона F, той трябва да посочи в полето “Create range:” (фиг.9) областта F2:F3. Когато пожелае да филтрира по второто условие, в посоченото поле той трябва да укаже областта G1:G2.
. . . . . . F G . . .
1 Студент Доход
2 Стоян Димов >= 180 лв
.
Фиг.17
Представлява интерес да бъде отбелязано, че когато условията са разположени в съседни колони по начина, показан на фиг.17, потребителят би могъл при необходимост да ги използва колективно за осъществяване на филтрация, реализирайки помежду им операцията AND. За целта е достатъчно в полето “Create range:” (фиг.9) той да посочи областта F1:G2.
Изчислими условия
Изчислими условия са такива, които за осъществяване на филтрацията използват резултати от някакви извършени преди това изчисления. При тяхното съставяне следва да се спазват следните правила:
• Първият ред на условието не трябва да съдържа име на поле от списъка. Той може да бъде оставен празен или в него да се впише някакъв друг текст.
• В референциите към клетки/области извън списъка, които съдържат резултати от изчисления, трябва да се използва абсолютна адресация.
• В референциите към клетки от списъка трябва да се използва относителна адресация.
Нека разгледаме пример, в който се поставя условието от разглеждания списък (фиг.2) да се извлекат записите, в които успехът надхвърля средния успех, постигнат от всички студенти. Решението е показано на фиг.18.
. . . . . . F G H . . .
1 Силни студенти: 4.48
2 TRUE
.
Фиг.18
В клетката H1 се въвежда формулата = MEDIAN(C4:C9), а в клетката F2 условието = C4 > $H$1. Условието е изчислимо, тъй като ползва изчисления средният успех от всички студенти. То включва частта $H$1, представляваща абсолютна референция към клетката H1, в която се съдържа резултатът от изчислението. Формулата от клетката H1 би могла да се намира във всяка друга клетка.
В клетката F1 е въведен поясняващ текст, който не е задължителен. Клетката би могла да бъде оставена празна.
За извършване на филтрацията е необходимо в полето “Create range:” (фиг.9) да се посочи областта F1:F2.
