2.4. Използване на формули в Excel

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

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

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

Например, ако в клетка А1 е записано числото 4, а в клетка А2 числото 5, то при запис в друга клетка на формулата „=А1+А2” и натискане на ‘Enter’ ще се изчисли резултат 9.Освен по горния начин, препратката може да се формира от номера на реда и номера на колоната, например препратка „C2” е еквивалентна на „R2C3”(ред 2, колона 3). Този стил на препратки трябва да е разрешен от меню Excel options – Formulas – R1C1 reference style.  

            Синтаксисът при използване на клетка (препратка) от друга книга и страница е :    'D:\Data\[Book2.xls]Sheet3'!B2       

Въвеждането на формулав клетка трябва да започва със знака “=”. ( За съвместимост с други електронни таблици EXCEL интерпретира като формули и изрази започващи с “+” и “-“ и функции предхождани от символа “@”. 

При въвеждането на формули в клетка, набраният текст се дублира в полето за формула (formula bar). Обикновено въвеждането и редактирането на формулите (при избрана клетка) е по-удобно да се извършва в това поле.

Максималната дължина на формулите е 1024 символа.

Преминаването на нов ред при въвеждане на формула става с ‘Alt-Enter’.

            При копиране на формули с препратки Excel извършва автоматична промяна на препратките като запазва относителното им положение спрямо положението на клетката с формулата.Ако при копиране искаме да запазим препратките непроменени използваме т.н. абсолютно адресиране. За да имаме абсолютно адресиране по ред и/или колона трябва пред съответната компонента на адреса – име на препратка, която не искаме да се промени, да изпишем символа “$”. Например: $А1 (при копиране ще се промени само номера на реда) , $А$3 (при копиране адресът няма да се промени).

Таблица за приоритета на изпълнение на операторите във формулите

Оператор      Описание                               Приоритет на изпълнение

 

Унарни оператори(само с един аргумент)

-           отрицание(действа на операнда отдясно) 1 (най-висок приоритет)

%         процент  (действа на операнда отляво)      2

Математични оператори

^          повдигане на степен                                     3

*          умножение                                                    4

/           деление                                                         4

+          събиране                                                       5

-           изваждане                                                     5

Текстови оператори

&         конкатенация                                   6

Оператори за сравнение

=          равно на                                                        7

<          по-малко от                                                   7

>          по-голямо от                                                 7

<=        по-малко или равнона                                7

>=        по-голямо или равно на                              7

<>        не е равно на                                                 7

За промяна на приоритет на изпълнение се използват скоби.

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

Пример:  Изразът „=2.7/(11*3.3*4/3)” може да се запише без скоби във вида ”=2.7/11/3.3/4*3”.

Как ще се запише без скоби изразът „= ((-7)^4)/(3*4)*(12/1.5)” ?

Проверете се дали правилно сте го записали.

Обърнете внимание на разликата в резултата при пресмятане на изразите:

=+5 – 2^2      и         = -2^2 + 5

 

Копиране на формулитее възможно по няколко различни начина, чрез : двойно щракване, влачене с мишката на долния десен ъгъл на избраната формула,избиране на диапазона в който ще се копира формулата преди набирането и завършване с C – Enter,, използване на менюто Home àfill…

Използване на препратки във формулите:

ROUNDUP(n,dp); ROUNDDOWN(n,dp);

EVEN(n), ODD(n) – закр. до най-близкото четно/ нечетно встрани от нулата;

TRUNC(n, prec) – маха десетичните знаци след prec.

INT(n) – закръглява надолу до най близкото цяло;

Други често използвани функции са:
            PRODUCT(n1,n2,…) – връща произведението на аргументите;

            MOD(n,d) – връща остатъка от деленето (делене по модул). Знакът е като на делителя;

            CUOTIENT(n,d) – връща цялата част при деленето;

ABS(n) – връща модула на числото;

          *Ако някои функции липсват проверете дали е инсталиран съответния пакет, например: “Excel options-> Add-ins - Analysis_toolpack”

 

Съобщения за грешки:

##### - тясна колона или отрицателна дата или време;

#VALUE!  - неподходящ аргумент или операнд;

#DIV/0! – делене на нула (или на съдържанието на празна клетка);

#NAME? – недефинирано име на променлива във формула;

 #N/A – липсват данни (за функция или формула) (Напр. празна клетка);

#REF! – невалиден адрес (Напр. формула използва за аргумент клетката си);

 #NUM! – невалидна числена стойност ( във формула или функция);

#NULL! – получава се когато сечението на области не съдържа клетки (областите не се пресичат).

           

2.6. Използване на имена

            За да дадете име на клетка или група клетки, те трябва да са избрани. От менюто избирате Formulas-> Defined names-> Define name и въвеждате името в диалоговия прозорец. Друг по-бърз вариант е след избирането на клетките да запишете името в полето за адрес.

          Имената могат да имат до 255 символа. Започват с буква или “_”.

 

Важно!: Имената не трябва да дублират адреси на клетки, т.е. неподходящи са имена като А1 и C7!

            За отделни клетки или групи от клетки имената могат да се дават автоматично от съседни за редовете или колоните клетки (колоните или редовете от имена–етикети трябва да са избрани заедно с клетките или групите от клетки). Използваме: Formulas->Defined Names->Createfrom selection.

            Имената по подразбиране са глобални – за цялата работна книга.

            За да създадем локално име, валидно само за работната страница, при дефинирането му в полето name, трябва да впишем и името на страницата. Пр.: ‘Sheet2’!MyName. На друга страница сега можем да използваме същото име за друга клетка.

            За да се видят имената на диапазоните в таблицата трябва увеличението да се намали под 40%. (Меню ‘View → Zoom’)

            При построяването на графики (в реда за “ x(y) values” на подменю “series”) вместо диапазони могат да се използват имена.Синтаксисът е :   =Book1.xls!MyName . Могат да се използват и обекти от други книги. Синтаксисът съвпада с този за обръщане към съдържанието на клетка (виж “Използване на формули”, препратка).

            За избиране на клетки от именувани диапазони се използва функцията “=INDEX(DataRange,nr,nc)”. Ако имаме само ред или колона, броят разделители е същия, а позицията на аргументите се запазва. Пр.: “Index(DataRange, ,4)”

            Присвояване на имена на константи (литерали).

Пр.: ‘Formulas’->’Defined names’->’Definename’. В поле ‘Name’ записваме ‘g’, а в поле ‘Reference’ формулата ‘= 9.81’. ( без апострофите)

            Даване на имена на формули: В полето ‘Reference’се набира формулата. Пр. ‘=sum(sheet1!$I$13:$I$15)’. За глобална дефиниция изпускаме sheet1. Можем да използваме и относително адресиране.

 

2.7.Работа с масиви и с масиви-формули

            Масивите могат да бъдат едно и двумерни. Могат да бъдат манипулирани колективно (напр. от функциите за работа с матрици) или поелементно (чрез стандартните алгебрични операции).

            Excelподдържа два режима на обработка на масиви - работата с масиви-формули и с копирани формули.

- при масивите-формули имаме гарантиране на еднаквостта на формулите за всички елементи;

- защита на отделни елементи на масива от промяна.

* За избиране на създаден масив действаме в следната последователност:

            1.Избира се една клетка от масива; 2.”Home (Editing)->GoTo->Special->[X] current Array”

или      1.Избира се една клетка от масива. 2.C+”/”

* За трансформиране на масив в копирана формула : 1/избира се масива ; 2/отива се на реда за редакцияна формули; 3/C-Ent.

* Редактиране на цял масив:

            1/избира се масива; 2/редактира се (F2)  в полето на формулата; 3/C-Sh-Ent

Примери:

>Алгебрични действия с масиви. Умножаване на две колонки поелементно:

Пример:

            1.Записваме по 6 числа в колонки A и B:

 

                                    A  B  C  D  E  F …

                        1          3  7

                        2          2  4

                        3          1  3

                        4          4  2

                        5          5  1

                        6          7  -1

                        …

            2.Избираме мястото за връщане на резултата - колонка  с размер 6х1(напр.C1:C6)и набираме формулата “= A1:A7*B1:B7” (пряко или като избираме с мишката).

            3.Натискаме комбинацията C-Sh-Ent

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

Стойностите в клетки C1:C6 вече не могат да бъдат променени поединично. (Опитайте!). За да преобразуваме масива от формули в отделни формули трябва да използваме техниката спомената по-горе.

>Масивите-константи се ограничават с фигурни скоби. Елементите в редовете на масива се отделят със запетаи, а редовете от точка със запетая.

            >>сума на елементите на масив

            =sum({0.3,0.4,0.6});     (тук може и без C-Sh-Ent)

            >>скаларно произведение на вектори

            =sum({0.3,0.4,0.6}*{3,2,1})  (тук може без C-Sh-Ent)

             >Създаване на двумерен масив формула.

                        - за отделяне на редовете се използва символа “;“

            >>Пр. въвеждаме го като формула:            1.Избираме правоъгълен диапазон 4х3. 2.Набираме „={1,2,3;4,5,6;7,8,9;3,2,1}”, натискаме  C-Sh-Ent

                        >>Можем да формираме матрица3x3, в която всеки ред е от елементите на първия вектор умножени по елемента от същия ред на втория вектор, който сега трябва да е записан като вектор стълб:

={0.3,0.4,0.6}*{3;2;1}  (тук с C-Sh-Ent)

            >Създаване на масив с име САМО в паметта.

            1.Insert->Name->Define 2.В полето Name записваме името(напр. MatrA).3 .В полето Reference въвеждаме масива като формула (вж. по-горе)

За копиране на масив от паметта в работния лист: 1. Избираме диапазона. 2.(= името) + C-Sh-Ent;

            >Използване на масива в други формули:

 „=transpose(MatrA)” + C-Sh-Ent

Упражнения:

1.Пресметнете инерчния момент спрямо оста Z на системата от м.т., чиито координати и маси са дадени в таблицата:

I=Σ(x^2+y^2)*m

x          y             m

0.3       1.2       1.5

1.8       2          0.1

4.5       0.1       2

2.3       4.0       1

0.3       1.5       0.7

0.7       1.7       0.8

- извършете пресмятанията с една формула;

            Пр.:{=SUM((J14:J17^2+K14:K17^2)*L14:L17)}

>Функции, които използват за аргументи масиви и връщат масиви:

MINVERSE(array) намира обратната матрица;

MMULT(ar1;ar2) пресмята произведението на матрици;

Функцията MDETERM(array) пресмята детерминантата на съответната матрица.

--------------------

 LINEST(y_ser;x_ser) + C-Sh-Ent; Връща коефициентите на mx + b , като трябва да сме избрали две клетки в ред.

TREND(y1:y2;x1:x2;newx1:newx2) + C-Sh-Ent. Трябва да е избран диапазона за връщане на новите стойности на y.

Упражнение: (решаване на системи от линейни уравнения)

                        1. Намерете пресечната точка на правите зададени с уравненията:

            2x + 3y = 8

            7x - 5y = -3

            Т.е. за кои стойности на x и y се удовлетворява системата уравнения. Можем да използваме формулите на Крамер x=D_x/D и y=D_y/D. (D=-31, x=1, y=2). Тук D_x е детерминантата, в която стълбът от коефициенти пред х е заместен със свободните членове.

Или да интерпретираме системата като матрично равенство АX=B. /По-елегантен начин/ Пр.:{=MMULT(MINVERSE(G32:H33);I32:I33)}

2. Намерете пресечната точка на равнините зададени с уравненията:

3x + 4y + 2z = 5

5x – 6y – 4z = -3

-4x + 5y + 3z = 1

D=12,Dy=-24,Dx=12,Dz=60

 

3. Решете системата:

            3x + 7y – 2z +4u =3

            -3x – 2y +6z -4u =11

            5x +5y -3z +2u =6

            2x +6y -5z + 3u=0

D=-303, Dx=-303, Dy=-606, Dz=-303, Du=909

 

2.8.Функции за обработка на текст

            =char(n) и  =code(“character”) са обратни една на друга функции и връщат символ или ANSI кода на символа.

            CONCATENATE(text1;text2;…)  съединява текстови низове. Аргументите могат да са адреси на клетки или имена. Текстовите низове трябва да са в кавички.

            & - замества Concatenate() , като се поставя между текстовите низове;

            EXACT(t1,t2) – сравнява текстовете и връща булева стойност дали съвпадат;

            LEN(t) – връща броя символи в текста t.

            LEFT(t,n) – връща левите n символа на t.

            RIGHT(t,n) – работи аналогично.

            LEFTB и RIGHTB се използват за двубайтови символи – например китайски или други).

            MID(t,start_num, num_chars) – връща num_chars, от t, започвайки от start_num.

            SEARCH(B)(find_text,within_text,start_num) – връща абсолютната позиция на find_text  в низа within_text, като търсенето е започнато от start_number позицията; допуска маски; не различава големи и малки символи.

            FIND(find_text,within_text,start_num) – не допуска маски; различава големи и малки символи.

Пример: Двукратната употреба, като търсим първи и втори интервал, може да ни позволи да отделим презимената от пълното име;

REPLACE(old_text,start_num,num_char,new_text)

VALUE(t1) – конвертира текст в число. Трябва да е възможно.

TEXT(value; format_text) – конвертира число в текст. Форматът се задава с изрази от вида “#.###Е+#” или само “#” …  .Подробно може да се види в помощната система. (Еквиваленти на # sa ?, 0 …)

 

2.9.Логически функции

1.Използване на условната функция IF:

=IF(logical_test;value_if_true;value_if_false);

- логически тест е всеки израз, който връща логическите константи TRUE или FALSE

- може да е резултат от операция за сравнение (=,<,>,<=,>=,<>)

- резултат от изпълнение на друга функция (Напр. EXACT(t1,t2));

- втори и трети аргумент могат да бъдат числа, текст, булеви стойности, формули;

- и тук са възможни най-много 7 нива на влагане;

Примери: 1: Запишете формула, която да записва с думи оценките по числените им стойност. Например, ако оценката е 3.5 формулата да връща текста „добър”;

            2. Оценете числото π по относителната площ на сектора представляващ четвъртина от  кръг с единичен радиус спрямо площта на единичен квадрат. / Упътване: Използваме генератора за случайни числа. Определяме частта на точките, които са в кръга ( … if ((x^2+y^2)<1, 1,0)  и т.н.) спрямо общия им брой в квадрата със страна 1./

            2. AND(l1,l2,….,l30) (най-много 30 логически теста) – връща TRUE, ако ВСИЧКИ аргументи са TRUE.

                        Примери: 1.Дали точката (x,y) лежи в правоъгълника (x1,y1;x2,y2);

            Решение: IF(AND(x>x1;x<x2;y>y1;y<y2);”in”; “out”);

                        2. В триъгълник зададен с трите си върха;

- Аргументите могат да са имена на клетки съдържащи формули.

3.OR(l1,l2,…)  - връща TRUE, ако поне един аргумент е TRUE;

 - Пример: като Пример 1.

4.NOT(l) – инвертира логическата стойност (TRUE <->FALSE).