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

 

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

 

 

Във формулата се използват адреси на клетки, като 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. Появява се диалогов прозорец за въвеждане на аргументи.

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

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

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

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

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

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

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

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

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

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

 

Пример 1.

 

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

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

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); 65%; IF (AND (Student2!G3>=5; Student2!G3<5,5); 70%; IF (Student2!G3>5,5; 75%; IF (Student1!F3<$F$3; 60%; 0)))) (фигура 11.7).

 

Фигура 11.7

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

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

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

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

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

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

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 и щракнете бутона <OK>. Таблицата се сортира по низходящ ред според средния успех на студентите.

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 и щракнете бутона <OK>.

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

Обяснения: Функцията YEAR се използва за да се вземе само годината от двете дати. За текущата дата се използва абсолютен адрес, понеже текущата дата е една и съща за всички клетки, съдържащи формулата.

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

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

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

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