Техники за база данни в Microsoft Excel

СЪДЪРЖАНИЕ

2. Теоретични положения.

2.2. Excel списъци като база данни.

2.3. Проверка на данните, докато въвеждате.

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

2.5. Междинни суми в базата данни.

2.7. Разширен филтър.

3. Редът на работата.

4. Контролни въпроси.

1. ЦЕЛ НА РАБОТАТА

Цели:

  • повишаване нивото на разбиране на темата „Бази данни в Microsoft Excel“;
  • овладяване на специални технически умения за проектиране и използване на релационни бази данни на ниво свободното им използване;
  • развитие на умения за самостоятелна работа и способност за прилагане на придобитите знания на практика при разработване на собствена база данни.

2. ТЕОРЕТИЧНИ ПОЛОЖЕНИЯ

2.1. Общи разпоредби

Предприятията използват различни методи за записване на данни на служителите. Някои организации имат счетоводни дневници, където информацията се въвежда ръчно, други използват класически бази данни за записи на персонала, а трети използват СУБД на Access. Но в повечето случаи в малките предприятия данните за служителите се записват в електронни таблици на Microsoft Excel.

Предложеното ръководство разглежда основните функции, предоставени от Excel за работа с бази данни, използвайки примера на списъка „Служители“, който съдържа информация за служителите на някакво условно предприятие. Такава база данни е полезна поради следните причини:

2.2. Списъци на Excel като база данни

Microsoft Excel разполага с богати вградени инструменти за обработка и анализ на данни. Аналог на проста база данни в Excel е списък.

Списъкът е група от редове на таблица, съдържащи свързани данни.

Отличителна черта на списъка е, че всяка от неговите колони съдържа един и същи тип данни, например списък с фамилни имена, дата на раждане и др. (фиг. 1).

Ако направим аналогия между списък и таблична база данни, то колоните на списъка са полетата на базата данни, а неговите редове са записите. Предполага се, че първият ред на списъка е неговото заглавие и съдържа имената на колоните в списъка. Заглавието трябва да има хоризонтална ориентация върху листа с електронна таблица. Заглавията се използват от Excel при писане на отчети и при търсене и организиране на данни. Шрифтът, размерът на шрифта, подравняването и другите опции за форматиране, присвоени на заглавията на колоните в списъка, трябва да са различни от тези, присвоени на редове с данни. Списъкът не трябва да съдържа празни редове и колони.

2.3. Проверка на данните, докато пишете

Ако няколко потребители работят върху файл едновременно, желателно е да контролирате вида на информацията, която въвеждат, и да минимизирате грешките при въвеждане. В Excel тези условия се проверяват с помощта на функция, наречена проверка на входа. За целта са ви необходими:

  1. Изберете клетките от колоната, за която се задава проверка на входа.
  2. На лентатаДанни в групатаРабота с данни изберете командатаПроверка на данни.
  3. В разделаОпции, в областтаУсловие за проверка изберетеТип данни :Всяка стойност (използва се за отмяна на проверката на въвеждане),Цяло число,Реално,Списък,Дата,Час,Дължина на текста иДруги (формат, за който можете да посочите своя собствена формула, например "m" или "g"). Когато изберете стойност, в долната част на прозореца се появяват допълнителни полета за въвеждане на условия или ограничения - например минимално и максимално допустимистойности.
  4. В разделаВъвеждане на съобщение можете да поставите отметка в квадратчетоПоказване на подсказка, ако клетката е текуща и да въведете съобщение, което да се показва на екрана, когато клетките бъдат избрани.
  5. В разделаСъобщение за грешка можете да поставите отметка в квадратчетоПоказване на съобщение за грешка, за да зададете типа съобщение за грешка, което ще се показва, когато в клетка бъде въведена невалидна стойност.

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

КомандатаСортиране ви позволява да пренаредите записите в различен ред въз основа на стойностите на една или повече колони. Записите се сортират възходящо/низходящо или според реда, избран от потребителя (например по дни от седмицата).

За да сортирате списъка:

  1. Поставете курсора в клетка от списък.
  2. Изпълнете командаСортиране на лентаДанни в групаСортиране и филтриране.
  3. В диалоговия прозорецСортиране изберете полето, по което ще се извърши сортирането; тип сортиране (по стойност, цвят на клетка, цвят на шрифта, икона на клетка) и ред (възходящ, низходящ, персонализиран).

Забележка. Избирането наперсонализиран ред ви позволява да посочите персонализиран ред на сортиране. За да направите това, в диалоговия прозорецсписъци (фиг. 2), изберетеНов списък, в списъкаElements, въведете стойностите \ u200b \ u200bforming персонализиран ред за сортиране (например AOP, FEO, ITC, ITO, MPO), и след това изберетедобавете.

2.5. Междинни суми в DB

За да организирате списъци, използвайте командатаМеждинни суми на лентатаДанни в групатаСтруктура, която ви позволява да:

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

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

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

данни

Ориз. 3. Изглед на списък в изглед на структура

Бутоните, разположени в горния ляв ъгъл, определят броя на нивата на данни, които да се показват. Бутоните с икони "+" и "-" са предназначени за свиване/разгъване на отделни групи.

За да премахнете междинни суми и крайни суми, изпълнете отново командатаМеждинни суми и след това щракнете върху бутонаИзчисти всички.

2.6. Автофилтър

Филтрирането на списъка означава да се показват само онези записи, които отговарят на зададените критерии.

За да зададете или премахнете автоматичния филтър, на лентатаДанни, в групатаСортиране и филтриране изберете командатаФилтър. След това щракнете върху бутона със стрелка до името на всяко поле, за да отворите списъка с неговите елементи и да изберете показаните стойности или да зададете условието за избор. На екрана ще се появят само онези записи, които отговарят на зададеното условие. Ако е необходимо, можете да продължите филтрирането, като щракнете върху бутоните със стрелки в други полета.

Можете да покажете всички записи по филтрираното поле, без да премахвате филтъра, като изберете критерияПремахване на филтър от... в списъка с филтри.

Показване на всички записи за всички полета без премахване на филтъра, командаИзчистване.

За данни от различни типове има допълнителни автофилтри, които са в списъка с критерииТекстови филтри,Числови филтри,Филтри по дата и др.

Ако изберете числово поле (например Възраст) и изберетеЧислови филтри в списъка с критерии, ще се появи списък с допълнителни филтри (фиг. 4), които ви позволяват да:

  • задайте критерия като неравенство – критерииравно на,не е равно,по-голямо от,по-голямо от илиравно на,по-малко от,по-малко отилиравно на,между ;
  • покажете първите N стойности - критериятПърви 10 : след като изберете командатаПърви 10... в списъкаЧислови филтри, трябва да посочите броя на стойностите (N) в прозореца, който се появява, както и метода на изчисление: броя наелементите от списъка,% от броя на елементите ;
  • дефиниране на условие по средната стойност в посочената колона - критерииНад средното,Под средното ;
  • самодефиниран филтър - критерийПерсонализиран филтър.

техники

Ориз. 4. Допълнителни цифрови филтри

Персонализираният филтър ви позволява да задавате критерии от едно или две условия.

Едно просто условие се състои от: име на поле (атрибут); вариант на условие (равно на, не е равно на, по-голямо, по-малко от, по-голямо или равно на, по-малко или равно на; започва с, не започва с, завършва с или не завършва с; съдържа, не съдържа); думи или числа за сравнение.

Сложното условие се състои от две прости, свързани със съюзитеИ илиИЛИ.

Когато пишете стойности в условия за сравнение, можете да използвате заместващи знаци във филтрите (Таблица 1).

Заместващи знаци

Знак

Стойност

всеки един знак

произволен брой знаци

се използват, когато трябва да намерите заместващи символи в текста (символи "?", "*" или "

2.7. Разширен филтър

Разширеният филтър ви позволява да създавате по-сложни условия, включително такива, състоящи се от повече от две условия.

Преди извикването на командатаРазширен филтър е необходимо да се формират критерии. За удобство е по-добре да формирате критериите на отделен лист (можете да му дадете име, например Критерии) и да дадете имената на критериите Kr1, Kr2 и т.н.

Основно правило : ако критериите са свързани чрез операцияИ, тогава те трябва да бъдат поставени на един ред, а акоИЛИ, тогава те трябва да бъдат поставени на различни редове.

След формирането на критерия се извиква разширеният филтър: на лентатаДанни, в групатаСортиране и филтриране, командатаДопълнителни.

Можете да възстановите оригиналния списък, като изберетеИзчистване в групатаДанни в групатаСортиране и филтриране.

3. РЕД ЗА РАБОТА

1. Запознайте се с теоретичните положения.

2. Вземете файл с готова база данни от учител или създайте своя собствена база данни, за да автоматизирате всяка предметна област на човешката дейност (счетоводство за служители в предприятие, отдел персонал, туристическа агенция, център за недвижими имоти, хотел, магазин и др.).

Забележка. Технологията за създаване на конфигурационна база данни с всякаква сложност и обем е представена в Приложение 3. Попълнете базата данни с правдоподобни последователни данни.

4. Използвайки изградения модел на база данни, изпълнете:

  • формират структурата на базата данни;
  • генериране на суми за един или два атрибута;
  • изградете диаграма;
  • формулиране и изпълнение на заявки за избор на данниизползване на автофилтър, докато използвате прост автофилтър по стойност и автофилтър с допълнителни критерии за данни от различни типове (числови, текстови, дата / час), както и персонализиран автофилтър;
  • формулирайте и реализирайте заявки за търсене и избор на данни с помощта на разширен филтър по такъв начин, че създадените критерии да съдържат 2-3 условия, свързани с поне две различни полета, като сред критериите има изчислени.

5. Представя на учителя отчет за свършената работа в електронен или печатен вид. Работата е предназначена за 6 академични часа.

4. КОНТРОЛНИ ВЪПРОСИ

5. РЕФЕРЕНТЕН СПИСЪК

  1. Кошелев В. Excel 2007. Ефективно използване. - М.: Бином. Лаборатория Знание, 2008г.- 544стр
  2. Слетова Л. Excel 2007 - М.: "ЕКСМО", 2007 - 336 страници.
  3. Сурядни А., Глушаков С. Microsoft Excel 2007: урок, 2-ро издание. - М .: AST, 2008 - 416 страници.