ТЕМА 4 Създаване и поддържане на база данни със средствата на EXCEL. Сортиране и филтриране на данните

ТЕМА 4

Създаване и поддържане на база данни със средствата на EXCEL. Сортиране и филтриране на данните

1. 
   Организиране и управление на списъци
   

Списъкът(list) представлява таблица от данни, съхранени в работен лист, организирани в колони от полета и редове от записи.

Всички списъци в Excel имат някои общи черти:

- първият ред в списъка съдържа имената на колоните;

- клетките в една и съща колона съдържат сходни данни;

- клетките в един и същ ред съдържат взаимосвързани данни.

В една страница е добре да има само един списък, защото някои от възможностите на Excel (например филтриране) могат да бъдат използвани само ако в съответната страница има по един списък във всеки момент.

За да бъде автоматично и правилно разпознат от Excel, списъкът трябва да бъде отделен от всички останали данни в работната таблица поне с по един празен ред и една празна колона.

Списъкът трябва да бъде конструиран така, че всички редове да имат сходни елементи в еднаквите колони. При въвеждане на данните не трябва да се вмъкват интервали в началото на клетките - те ще повлияят при работата с данните. Препоръчително е, в таблицата, която ще се използва като база от данни да няма празни и слети (merge cells) клетки, за да не настъпят проблеми при сортиране и филтриране на данните.Добре е да се поддържа фиксиран брой полета (колони) за списъка. Допуска се при работа със списък да се променя броя на редовете, при добавяне, изтриване или пренареждане на записите в него.

Формулярите за данни са потребителски ориентирано средство за управление на данните (добавяне, търсене, редактиране и изтриване на записи) в списъците..

Основни елементи на формуляра са: заглавие, полета за данни и командни бутони.

Заглавие на формуляра – заглавна лента в горната част на диалоговия прозорец, която показва името на работната страница, в която са данните.

Полета за данни – имената на полетата се създават от имената на колонките в списъка. Появяват се подредени в реда, в който са в списъка, отляво надясно. Максималният брой на полетата, които могат да бъдат показвани в един формуляр е 32.

Съдържанието на полетата се показва по два начина:

• 
  полета достъпни за промени - в които данните се визуализират в прозорчета за редактиране (Edit Box) и съхраняват входни данни. Дължината им е еднаква и се определя от ширината на най-широката колона в списъка;
  
• 
  резултатни полета – без възможност за редактиране. Това са случаите, когато в полето е въведена формула и във формуляра за данни се показва резултатът от изчислението на формула, или когато входните данни са защитени.
  

• 
  добавяне на нов запис-New:
  
• 
  търсене на записи-Find Prev, Find Next;
  
• 
  задаване на критерии Criteria;
  
• 
  изтриване на запис-Delete;
  
• 
  възстановяване на записа-Restore;
  
• 
  затваряне на формуляра-Close.
  

1. Избира се произволна клетка от списъка;

2. Избира се командата Data/Form. Извежда се формуляр, съдържащ данните от първия запис на списъка;

Преглеждането на записите става с лентата за превъртане или чрез бутони Find Next (преход към следващия) и Find Prev (преход към предишния). Всяко поле в текущия запис може да бъде редактирано, стига да не е защитено или резултатно, т.е. да съдържа формула. Редакцията се извършва непосредствено в прозорчето за редактиране, а актуализирането на данните в списъка става или при преминаване към друг запис или с натискане на Enter.

След въвеждане на всички записи в базата данни се натиска клавиш Esc или бутона Close. Работната книга се записва с File/Save.

Когато трябва Excel да изчислява определено поле по формула, тази формула трябва да бъде въведена в съответното поле на първия запис. След това се избират двата реда (имената на полетата и примерния запис) и се създава формуляр. Excel копира формулата от първия запис във всеки нов запис, който се въвежда с формуляра.

Изтриване на избран запис става натискане на бутон Delete. Изтрит запис не може да бъде възстановен.

За придвижване в базата данни се извършва следното:

1. Избира се произволна клетка от БД

2. Извежда се формуляр на базата данни посредством Data/Form

3.Използва се някой от посочените в таблица 4 клавиш или клавишна комбинация в зависимост от случая

Действие	Резултат
Натискане на , Enter, стрелката в долната част на лентата за придвижване или бутона Find Next.	Преминава към същото поле на следващия запис в базата данни.
Натискане на , Shift+Enter, стрелката в горната част на лентата за придвижване или бутона Find Prev.	Преминава към същото поле на предишния запис на базата данни.
Натискане PgDn	Преминава с десет реда напред.
Натискане PgUp	Преминава с десет реда назад.
Натискане Ctrl+, Ctrl+PgUp или преместване на плъзгача на лентата за придвижване най-горе.	Преминава към първия запис в базата данни
Натискане Ctrl+, Ctrl+PgDn или преместване на плъзгача на лентата за придвижване най-долу.	Преминава към последния запис на базата данни.

Таблица 4 Клавиши за придвижване
1.3. Търсене на записи с използване на форма

Извършва се чрез последователно преглеждане на записите един по един или чрез задаване на критерий за търсене.

1. Избира се произволна клетка от списъка

2. Избира се командата Data/Form.

3. Избира се бутона Criteria от формуляра. Извежда се празен формуляр, в който всички полета са достъпни за потребителя. Във всяко едно от тях той може да въведе условие (критерий) за търсене, включително и за резултатните полета.

Критерият за търсене може да бъде точна стойност (число или текст) или да бъде от тип сравнение посочен в таблица 5:

оператор	значение
=	равно
>	по-голямо
>=	по-голямо или равно
<	по-малко
<=	по-малко или равно
<>	различно

Ако бъдат въведени критерии в няколко полета едновременно, се дефинира съставен критерий - връзката между отделните критерии е от типа And.

За да се смени критерия за търсене, първо се изчистват въведените критерии, като след избора на Data/Form се натиснат последователно бутоните Criteria и Clear. След това се избират подходящите полета и се заменя съдържанието им с нови условия. Ако се използват същите полета, няма нужда да се натиска бутона Clear.

Ако е необходимо връщане към избрания запис, без да се използва въведения нов критерий, се натиска бутона Form, който се появява на мястото на бутона Criteria след натискането му.

Всички записи които отговарят на зададения критерий могат да се прегледат един по един чрез бутоните Find Next и Find Prev. Връщането в режим на преглед и редакция става с бутон Form.

Възможно е да се извърши редактиране на полетата на текущия запис. Ако се премине на следващо поле от същия запис и не е натиснат клавиш Enter е възможно възстановяването на всички променени полета от текущи запис с натискането на клавиш Restore.

Добавянето на записи се извършва се с бутон New. Показва се нов празен формуляр и се предоставя възможност за въвеждане на нови данни. Придвижването между прозорчетата за редактиране (Edit box) става с клавиш Tab (напред) и Shift+Tab (назад) или директно с мишката.

Въвеждането на данни приключва с натискане на Enter. Excel добавя записа в края на списъка, като копира в него всички формати и формули на предходния запис. Ако Excel открие данни в реда непосредствено под последния запис се появява съобщение Cannot extend list or database.

Ако е включена защитата на таблицата с командата Tools/Protection, добавянето на записи е невъзможно В този случай бутонът New не е активен.

Изтриването на текущия запис се извършва с бутон Delete. Изтрит запис не може да бъде възстановен.

Записи от защитена таблица не могат да бъдат изтрити.

2. Сортиране на данни в списък

Сортирането означава пренареждане на записите в желаната от потребителя последователност. Осигурена е възможност за подреждане на записите едновременно по три признака (полета) съподчинени помежду си.

За всеки признак се дефинира начинът за сортиране Ascending (възходящ ред) или Descending (низходящ ред).

При сортиране във възходящ ред Excel спазва следната подредба:

• 
  първо числата, от най-малкото отрицателно към най-голямото положително, а датите и часовете - от по-ранните към по-късните.;
  
• 
  после текстовете, като първо се сортират числата, въведени като текст, а после символите в съответствие с вътрешното им представяне;
  
• 
  след тях логическите стойности FALSE и TRUE;
  
• 
  накрая празните клетки.
  

1. Избира се клетка от базата данни.

2. Избира се командата Data/Sort. Excel избира клетките със записите на базата данни (без реда с имената на полетата) и отваря диалогов прозорец Sort. По-подразбиране в Sort by е избрано първото поле от базата данни и е включен радио бутона Ascending (възходящ ред);

3. Избира се в Sort by признакът с най-висок приоритет - името на полето, по което се предвижда първо да се сортират записите на базата данни. Ако първото поле съдържа повтарящи се стойности и трябва да се зададе как да се подреждат един спрямо друг дублиращите се записи в него, се избира второто поле за сортиране от списъка Then by и ако е необходимо, се включва радио бутона Descending (низходящ ред) в дясно от него.

Ако се налага може да бъде избрано и трето поле за сортиране в Then by и след това да се зададе начина на подреждане.

Опцията Header row се активира автоматично, когато първият ред от таблицата с БД съдържа имена на колони (полета в БД) – в този случай тези заглавни имена не участват в сортирането. Ако се селектира опция No header row, тогава в сортирането се включва и първия ред от списъка с БД – т.е. той ще участва в сортирането.

Допълнителни условия относно начина на сортиране могат да се зададат в диалоговия прозорец Sort Options, който се отваря след натискането на бутон Options.

Sort Top To Bottom – сортиране на записите (редовете на списъка);

Sort Left To Right – преподреждане на полетата (колонките) в записите.

Стандартно Excel не прави разлика между главни и малки букви при сортировката. За да се прави разлика се избира опцията Case Sensitive.

Падащ списък First key sort order – съдържа освен стандартно установен начин за сортиране Normal и възможност за подреждане на данните в ред, дефиниран от потребителя. Обикновено този ред отразява някаква логическа последователност – имена на дните в седмицата, имената на месеците и т.н. Тези последователности са дефинирани предварително чрез командата Tools/Options/Custom Lists.

Направените дефиниции в диалоговите прозорци Sort и Sort Options се съхраняват и са валидни докато не бъдат променени или докато не бъде избран друг списък за сортиране.

Филтрирането е бърз и лесен начин за намиране и работа с определено подмножество данни от списъка с бази данни. Филтрирането се осъществява по критерий, зададен от потребителя - всички записи, които удовлетворяват зададения критерий се показват едновременно на екрана и са достъпни за последваща обработка, а останалите се скриват.

1. 
   Избира се клетка от базата данни.
   
2. 
   Изпълнява се командата Data/Filter/AutoFilter за автоматично филтри-ране или Data/Filter/Advanced Filter - усъвършенствано филтриране.
   

С избиране на командата Data/Filter/ AutoFilter се появява по един бутон-стрелка в името на всяка колона. Всяка стрелка отваря падащ списък, от който може да бъде избран филтриращия критерий. Списъкът съдържа всички стойности, срещащи се в съответната колона. Изборът на една от тях предизвиква скриване на всички записи, които не съдържат избраната стойност. Премахването на филтриращият критерий става чрез опцията All, при което всички скрити записи се показват.

Допълнителни възможности за автоматично филтриране предоставят опциите Top 10 и Custom. Top 10 е приложима само спрямо колони, които съдържат числови стойности (числа или дати). Тя предоставя възможност да бъдат показвани определен брой записи, които имат в избраната си колона най-големите (Top), или най-малките (Bottom) стойности. Броят на записите се задава от потребителя.

Опцията Custom AutoFilter позволява да се задават потребителски критерии от тип сравнение. Дефинирането на критерия става в диалоговия прозорец Custom AutoFilter. В него от падащ списък се избира операторът за сравнение – напр. equals (равно на) или is greater than (по-голямо от) и т.н., а в полето вдясно от избрания оператор за сравнение се задава граничната стойност, с която се сравнява. Стойността може да бъде избрана от падащ списък или да бъде въведена директно. Могат да бъдат едновременно дефинирани два критерия за избраното поле (колона), свързани помежду си с връзка And или Or. Връзката AND позволява дефинирането на интервал [min,max] за даденото име на поле. В резултат на това се показват само тези записи, които са в интервала. Връзката OR е полезна, когато се търсят записите, които имат едната от две различни стойности в съответната колона (поле).

Командата AutoFilter може да бъде прилагана неколкократно както над една и съща колона (поле), така и над различни колони. По този начин се филтрират вече филтрирани данни, т.е. връзката между последователно прилаганите филтриращи критерии е от типа AND.

Ако потребителят желае да извърши филтриране само по определени колони (полета), тогава имената на тези колони се избират предварително. След задаване на командата AutoFilter само за тях ще се показват бутоните-стрелка. Ако е необходимо стрелка да се появи само в етикета на една колона е необходимо да се избере името на колоната и първата клетка под нея.

По сравнение с автоматичното, усъвършенстваното филтриране Advanced Filter предлага три допълнителни възможности:

• 
  задаване на условие “или” между полетата
  
• 
  копиране на данните, които удовлетворяват дефинирания критерий (т.е. възможност за генериране на справки);
  
• 
  дефиниране на т. н. изчислителни критерии, като. в качеството на критерий се използва резултатът от изчисляването на формула.
  

Тъй като имената на колоните в списъка в областта за критерий и в областта за изход трябва да бъдат съвършено еднакви, добре е подготовката на двете области да се извършва с копиране, като по този начин се осигурява пълна идентичност на имената.

Усъвършенстваното филтриране дава възможност за използване на два типа филтриращи критерии:

1. 
   от тип сравнение (сравнителни критерии);
   
2. 
   изчислителни критерии.
   

Чрез сравнителни критерии се дефинират два вида условия:

• 
  за съвпадение (точна стойност);
  
• 
  тип сравнение.
  

• 
  поредица от символи (текст, число, данни) които се търсят в съответната колона;
  
• 
  математически израз за сравнение.
  

• 
  ако условията трябва да бъдат изпълнени едновременно (връзка AND) те се разполагат на един ред;
  
• 
  ако поне едно от условията трябва да бъде изпълнено (връзка OR) те се разполагат на съседни редове един под друг.
  

Изчислителният критерий е валидна формула, в която операнди могат да бъдат адреси на клетки, които са “външни” за списъка. Той започва със знак “=” и представлява израз от тип сравнение. Критерият-формула трябва да касае поне една колона в списъка и да включва адреса на първата клетка с данни от тази колона. При филтрирането формулата се преизчислява последователно за всички записи в списъка. Резултатът от нейното калкулиране е TRUE –ако дефинираното условие е изпълнено и False –в противен случай. Записите, за които резултатът е TRUE се показват на екрана. За да се осигури преизчисляването на формулата за всеки запис, необходимо е обръщенията към клетките в списъка да бъдат относителни, напр. =В2>2.

Гъвкавостта на заявката за търсене може да се осигури ако на потребителя се предостави възможност сам да дефинира различни ограничителни условия, без за това да бъде необходимо да се пренастройва изчислителния критерий. Това може да стане като във формулата-критерий вместо константи се използват абсолютни адреси на клетки (например ако се използва формулата =AND(J2>$A$16,J2<$B$16 ще се покажат на екрана тези записи, в които J2 (от колона J от базата данни) трябва да бъде в диапазона, определен от граничните стойности, въведени в клетки A16 и B16. Потребителят може да променя стойностите в клетките A16 и B16, като по този начин в една и съща формула ще могат да бъдат дефинирани различни условия.

Изчислителните критерии също могат да бъдат използвани самостоятелно или в комбинация. Начинът, по който са разположени в критериалната област (на един или на съседни редове) определя типа на връзката – AND или OR. Изчислителните критерии могат да бъдат комбинирани и със сравнителни критерии в една и съща критериална област.

• 
  формулата трябва да адресира поне една колона в списъка. Като правило в нея се записва относителният адрес на първата клетка в тази колона. Когато във формулата на изчислителен критерий Excel срещне относителен адрес на клетка, той последователно настройва този адрес към всяка клетка в съответната колона и оценява резултатът от преизчислението на формулата. Абсолютни адреси се използват за адресиране на клетки, чието положение не се мени в процеса на оценяването.
  
• 
  даването на етикети на изчислителният критерий в критериалната област е задължително. Той не трябва да съвпада с име на колона от списъка на данни. В противен случай Excel няма да използва формулата като изчислителен критерий, а ще използва върнатата от формулата стойности като сравнителен критерий. Това би довело до неочаквани и неверни резултати.