Изграждане на формули

 

Най-същественото предимство в използването на ЕТ е възможността за пресмятане с формули на базата на въведени стойности в работния лист както и вградените средства за изчисление (функции), които извършват сложни финансови и статистически пресмятания, помагат при вземане на решения или обработват текст. Използването на вградени функции намалява вероятността за грешки.

 

 

Във формулата се използват адреси на клетки, като C1 или D4, които се обръщат към стойностите на тези клетки. Изпозват се и имена на области от клетки.

 

 

Относителни и абсолютни адреси

 

В повечето случаи при изграждането на формули, се използват относителни адреси на клетки.

Ако се намирате в C5 и от формулата описвате адрес C3, Excel си представя използването на клетка, която е два реда над текущата и четири колони наляво.

Абсолютните адреси са полезни, ако желаете формулата да използва данни от конкретни клетки вместо да ги адаптира към положението на новата клетка.

Абсолютните и относителни адреси могат да се комбинират в смесени адреси. Тогава колоната е абсолютна, а редът относителен или обратното. При въвеждане на $A1, при копиране редът ще се променя, а колоната не.

Sheet1!A1 е обръщение към клетка A1 в лист Sheet1. Ако в името на листа има празни интервали, оградете го с кавички.

Може да се ползват работни листове от други файлове. Адресът на клетката изглежда така: ‘[име на файла]’Sheet1!A1.

 

Оператори за формули

 

Съществуват:

-аритметични оператори: събиране(+), изваждане(-), умножение(*), деление(/), процент(%), степенуване(^);

-текстови оператори: слепване на текст(&);

-оператори за сравнение: равно(=), по-малко(), по-малко и равно(=) и различно(<>).

 

 

Разлика между две дати се описва така: “6/2/1998” - “05/27/1998” и е равно на 6.

 

Ако въведените данни не се разпознават като дата или час, те се приемат като текст, а резултатът от формулата е съобщение за грешка #VALUE. В такъв случай можете да използвате функцията DATE(year;mounth;day) и чрез нея да се извърши изваждането на дати: DATE(1998;06;02) – DATE(1998;05;27).

 

Когато във формулите и функциите използвате адреси на клетки, те не дават информация какво се крие зад данните в тези клетки. Използването на именувана област от клетки прави информацията във формулата по- ясна и по- изразителна. (например =NALICHNO – PRODADENO, ако се касае за изваждане на две количества, като областите от клетки са предварително именувани с Insert/Define/Name – виж Тема 3)

 

Изграждане на функции

 

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

 

Фигура 11.1

 

 

 

Въвеждането на функцията можете да осъществите по няколко начина:

-директно въвеждане (фигура 11.1);

-вмъкване на функция от бутона <f_x> Function Wizard;

-вмъкване на функция чрез менюто Insert/Function/Paste Function (фигура 11.2).

Фигура 11.2

Фигура 11.3

В последните два случая се работи по един и същи начин. В прозореца Paste Function, в полето Function Categoryсе появяват категориите, а в полето Function Name Listпо азбучен ред се изреждат самите функции от актуалната категория. Аргументът може да бъде клетка, област от клетки или друга функция. Изреждат се със запетая или точка и запетая ( в зависимост от настройката на Windows).

След избора на функцията се появява втори прозорец с полета за аргументите, кратко описание на функцията и място за резултата (Formula result=)(фигура 11.3). Едновременно с това тя се изписва и в Formula Bar.

След щракане на бутона <OK>, прозорецът се затваря, а резултатът от изчислението на функцията се появява в клетката.

 

Редактиране на формули и функции

 

Ако формулата е непълна или некоректна, възможно е да се появи съобщение за грешка от типа на #NAMEили #NUM. В някои случаи се локализира онази част от формулата, в която Excel предполага, че се намира грешка.

Може да редактирате вече въведена функция с помощта на Paste Functionили директно да нанесете корекциите в реда за формули.

Ако използвате Paste Function:

-Активирайте клетката, съдържаща функцията.

-Изберете Insert/Function. Появява се диалогов прозорец за въвеждане на аргументи.

-Извършете необходимите корекции.

-Щракнете бутона .

Ако извършвате ръчно корекции:

-Активирайте клетката, съдържаща функцията.

-Извършете необходимите корекции.

-Натиснете <Enter>или щракнете бутона с отметката.

Ако се нуждаете от помощ за прилагане на функциите:

-Изберете Help/Contents&Index

-Щракнете върху Find.

-Въведете Function by Category, а в долното поле двукратно щракнете върху Worksheets functions listed by category. Получава се списък от функции, подредени по категории. Функциите са описани най- подробно със синтаксиса си, съвети и примери за приложение.

 

Пример 1.

 

Заредете файла Student.xls. Активирайте таблица Student2. Изчислете средния успех на всеки студент, името на дисциплината, по която средният успех е максимален и името на дисциплината с минимален среден успех.

Активирайте таблицата Student3. Клетките в колоната %СТИПЕНДИЯ  попълнете в зависимост от дохода на студента и успеха му по следния начин: студент, който има среден доход < от минималната работна заплата, взема социална стипендия в размер на 60% от минималната работна заплата, независимо от успеха; в противен случай при успех >=4,50 и успех=5.00 и успех5.50 – 75%.

РАЗМЕР НА СТИПЕНДИЯ изчислете като %СТИПЕНДИЯ от минималната работна заплата, която се въвежда в отделна клетка.

Класирането определете като класиране според средния успех от Student2.

 

Решение 1

 

1.Изберете File/Open/Student.xls.

2 Щракнете върху Student2 и го активирайте.

3.Полето СРЕДЕН УСПЕХ можете да изчислите по два начина:

3.1.Чрез използване на функцията SUM:

3.1.1.Активирайте клетка G3.

3.1.2.Щракнете върху бутона <AutoSum>. Появява се =SUM(A3:F3).

3.1.3.Нанасете корекции в аргументите B3:F3 (щракате във формулния ред пред A, изтрийте с клавиша <Del> и изпишете B).

3.1.4.Поставете “/” (знак за деление) и 5, т.е. формулата приема вида =SUM(B3:F3)/5.

3.1.5.Натиснете клавиша <Enter> или щракнете мишката върху бутона <Enter>.

3.1.6.В клетка G3 се изчислява средния успех на всеки студент.

3.2.Чрез използване на функцията AVERAGEза изчисляване на средно- аритметична стойност.

3.2.1.Активирайте клетка G3.

3.2.2.Щракнете върху Paste Functionили изберете Insert/Function.

3.2.3.От категорията Most Recently Used (най- често използвани функции) или от Statistical (Статистически), избирате AVERAGE.

3.2.4.Щракнете бутона <OK>.

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

3.2.6.В полето Formula rezults=3,40.

3.2.7.Ако сте съгласни, щракнете бутона <OK>

3.2.8.В клетка G3 се изчислява средния успех.

3.3.Копирайте по познатия начин от Тема4 съдържанието на клетка G3 от G3 до G13. Участващите във формулата адреси са относителни и формулата се изчислява правилно в цялата колона СРЕДЕН УСПЕХ.

4.В клетка A15изпишете  СРЕДЕН УСПЕХ ПО ДИСЦИПЛИНИ.

5.Изберете Format/Cells/Alignment/Wrap Text;в полето Horizontal: Center; в полето Vertical: Centerи щракнете бутона <OK>.

6.1.В клетка B15изпишете ръчно (или по двата по- горе показани начина) =AVERAGE(B3:B13). Областта B3:B13може да бъде указана чрез влачене на показалеца на мишката по нея.

6.2.В клетка B15 получавате 4,181818.

6.3.Копирайте формулата от клетка B15за всяка дисциплина. В дробната част на числата от ред 15 се получават 6 цифри.

 

Фигура 11.4

 

6.4.Маркирайте областта B15 до F15.

6.5.Изберете Format/Cells/Number/Number. В полето Decimal placesзапишете 2 и щракнете бутона <OK> (фигура 11.4).

7.В клетка A16 изпишете: ДИСЦИПЛИНА С МАКС. СР. УСПЕХ и по начина от клетка A15подравнете съдържанието й.

8.В клетка B16изпишете следната сложна формула: =INDEX(B2:F13; 1; MATCH(MAX(B15:F15); B15:F15;0)).

8.1.Функцията MAXвръща като резултат максималната стойност от B15 до F15.

8.2.1.Функцията MATCH(стойност за търсене; област за търсене; тип сравняване (0 за =, 1 за =)), връща като резултат номера на колоната, в която се намира максималния успех.

8.2.2.В разглеждания случай, това е 5 колона.

8.3.Функцията INDEX(област; ред; колона) връща като резултат съдържанието на клетката с адрес (ред,колона) (фигура 11.5).

Фигура 11.5

 

9.В клетка A17изпишете ДИСЦИПЛИНА С МИНИМ. СРЕДЕН УСПЕХ. (Копирайте съдържанието на A16 и редактирайте).

10.В клетка B17изпишете следната формула (или копирайте съдържанието на B16 и редактирайте): =INDEX(B2:F13; 1; MATCH(MIN(B15:F15); B15:F15; 0)). В клетка B17 се изписва ЛААГ (фигура 11.6).

 

Фигура 11.6

11.Активирайте работния лист Student3.

12.1.Активирайте клетка F2.

12.2.В клетка F2изпишете МИНИМ. РАБОТНА ЗАПЛАТА и подравнете съдържанието с Format/ Cells/ Alignment/ Wrap Text; поле Vertical: Center; поле Horizontal: Center; щракнете бутона .

12.3.В клетка F3 въведете минимална работна заплата 60000лв. (от Format/ Cells/ Number/ Currency/ Symbol: лв).

13.Активирайте клетка B3.

14.В клетка B3 изпишете формула с използване на функцията IF(условие; ако условието е вярно; ако условието е грешно) и функцията =AND(условие1, условие2,....), която свързва повече от едно условие и връща като резултат истина ако всички условия са изпълнени и лъжа в противен случай

=IF (AND (Student2!G3>=4,5; Student2!G3=5; Student2!G35,5; 75%; IF (Student1!F3 (фигура 11.7).

 

Фигура 11.7

Бележки по формулата:

14.1.Изписването на Student2!посочва работния лист, където се намира адреса G3.

14.2.За позоваване на минималната работна заплата се използва абсолютен адрес $F$3, понеже минималната работна заплата постоянно се намира в клетка F3.

14.3.При изписване на сложната формула трябва да се внимава: броят на отварящите скоби да е равен на броя на затварящите.

15.Копирайте формулата от B3 до B13.

15.1.Ако в тази област данните се появят като числа 0,60, 0,75 и т. н., маркирайте областта и щракнете бутона .

15.2.Числата се форматират в проценти.

16.1.Активирайте клетка C3.

16.2.Изпишете формулата =B3*$F$3 (в клетка $F$3 е постоянната минимална работна заплата).

16.3.В клетка C3 получавате сумата 0,00.

16.4.Копирайте формулата от C3 до C13. Получавате размера на стипендията на всеки студент.

17.За да попълните колоната КЛАСИРАНЕ ПО УСПЕХ, активирайте Student2.

17.1.Маркирайте таблицата без заглавието и извадките.

17.2.Изберете Edit/Copy.

17.3.Активирайте Sheet4.

17.4.Изберете Edit/Paste.

17.5.Sheet4 преименувайте в Klasir.

 

Фигура 11.8

17.6.Изберете Data /Sort /Sort by: СР. УСПЕХ/ Descendingи щракнете бутона . Таблицата се сортира по низходящ ред според средния успех на студентите.

17.7.Активирайте таблицата Student3.

17.8.Активирайте клетка D3.

17.9.Изпишете функцията: =MATCH(A3; Klasir!$A$2:$A$12; 0) (фигура 11.8).

17.10.В колоната ФАК.НОМЕР на таблица Klasir, функцията MATCH търси поредния факултетен номер от Student3 и връща като резултат на кой относителен ред той се намира. Този ред отговаря на поредността в класирането.

 

Пример 2.

 

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

-ФАМИЛИЯ

-ДАТА НА ПОСТЪПВАНЕ

-ВЪЗРАСТ

-ОСНОВНА ЗАПЛАТА

-ПРОЦЕНТ ВНОСКА

-ВНОСКА

1)Въведете данните за десет служащи като датата на приемане на работа въведете с помощта на функцията DATE.

2)Въведете текущата дата вдясно от заглавието на таблицата с помощта на функцията TODAY.

3)Изчислете вноската с използване на функциите IF, AND и OR по следния начин: ако процента вноска е различен от 0 И ИЛИ трудовият стаж е >2 години ИЛИ възрастта на служителя е >=28 години, вноската се изчислява като процент от основната заплата, в противен случай е 0.

4)Намерете общо приходите в пенсионния фонд.

 

Решение 2

 

1.Ако сте в Excel и сте работили с друга таблица изберете File/ New/ Workbookи щракнете бутона .

2.По описания в предните теми начин, попълнете заглавието на таблицата, имената на колоните и фамилиите на служителите от A2 до A12.

3.Активирайте клетка B3.

4.Щракнете върху Paste Functionили изберете Insert /Function/Paste Function.

5.От Function Categoryизберете Date&Time. От List -  функцията Date и щракнете бутона .

6.Появява се диалогов прозорец, като в полето Year изписвате 1970, в полето Month - 05, а в полето Day – 08. В клетка B3 се изписва 08.05.1970 (възможно е съществуването на друг формат за изписване на дата – 08/05/1970) (фигура 11.9).

7.Копирайте функцията и в останалите клетки, като редактирате аргументите.

8.Клетките ВЪЗРАСТ и ОСНОВНА ЗАПЛАТА попълнете по ваш избор.

9.Активирайте клетка H2. Изпишете ТЕКУЩА ДАТА.

10.Активирайте клетка H3. Изпишете =TODAY().Появява се текущата дата.

11.Активирайте клетка F3.Според условието на задачата изпишете формулата: =IF (AND ( %вноска >0; OR(трудов стаж >=28; възраст >=2)); %вноска*основна заплата; 0).

Реално формулата изглежда така: IF(AND(E3>0; OR(YEAR($H$3) – YEAR(B3)>2; C3>=28)); E3*D3; 0).

12.Маркирайте областта F3:F12и задайте форматиране за лв. (Format/ Cells/ Number/ Currency)

13.Маркирайте колоната ВЪЗРАСТ (C3:C12).

14.Изберете Format/ Cells/ Number/ Custom. В полето Typeизпишете ##”г.” и щракнете бутона . Областта приема форматиране в години.

15.Активирайте клетка A14. Изпишете ОБЩО ПРИХОДИ В ПЕНСИОННИЯ ФОНД.

16.Активирайте клетка F14. Изпишете функцията =SUM(F3:F12) (можете да щракнете AutoSum). В клетка F14се изчисляват общите приходи на пенсионния фонд.

17.Маркирайте областта $A$2:$F$12.

18.Оградете маркираната област с помощта на Format/Borderили щракнете бутона <Borders>.

19.Маркирайте областта $A$14:$F$14. И по същия начин я оградете с избрана линия.

20.Активирайте клетка F14. Оцветете с желан цвят като щракнете бутона Fill Color.

 

Фигура 11.9