Изчисления в Excel

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

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

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

Excel поддържа следните оператори:

• Аритметични операции:
  • добавяне (+);
  • умножение (*);
  • намиране на процента (%);
  • изваждане (-);
  • деление (/);
  • показател (^).
• Оператори за сравнение:
  • = равен;
  • < меньше;
  • > повече;
  • <= меньше или равно;
  • >= по-голямо или равно;
  • <>не е равно.
• Телекомуникационни оператори:
  • : диапазон;
  • ; съюз;
  • & оператор за присъединяване към текст.

Таблица 22. Примери за формули

Упражнение

Въведете формула -25-A1+AZ

Въведете предварително всякакви числа в клетки A1 и A3.

1. Изберете желаната клетка, например B1.
2. Започнете да въвеждате формула със знака =.
3. Въведете числото 25, последвано от оператора (знак -).
4. Въведете препратка към първия операнд, например като щракнете върху желаната клетка A1.
5. Въведете следния оператор (знак +).
6. Щракнете върху клетката, която е вторият операнд във формулата.
7. Завършете въвеждането на формулата, като натиснете клавиша Въведете. В клетка B1 вземете резултата.

Автосумиране

Бутон Автосума (AutoSum)- ∑ може да се използва за автоматично създаване на формула, която сумира площта на съседните клетки, които са директно налявов този ред и директно по-високв тази колона.

1. Изберете клетката, в която искате да поставите резултата от сумирането.
2. Щракнете върху бутона AutoSum - ∑ или натиснете клавишната комбинация Alt+=. Excel ще реши коя област да включи в обхвата на сумиране и ще я подчертае с пунктирана подвижна кутия, наречена рамка.
3. Кликнете Въведетеза да приемете областта, която Excel е избрал, или използвайте мишката, за да изберете нова област и след това натиснете Enter.

Функцията AutoSum автоматично се трансформира, когато клетките се добавят и премахват в областта.

Упражнение

Създаване на таблица и пресмятане по формули

1. Въведете числови данни в клетките, както е показано в таблицата. 23.

Таблица 23. Таблица с първоначални данни

1. Изберете клетка B7, в която ще се изчисли вертикалната сума.
2. Щракнете върху бутона AutoSum - ∑ или щракнете Alt+=.
3. Повторете стъпки 2 и 3 за клетки C7 и D7.

Изчислете броя на служителите без висше образование (по формулата B7-B3).

1. Изберете клетка B8 и въведете знака (=).
2. Щракнете върху клетка B7, която е първият операнд във формулата.
3. Въведете знака (-) на клавиатурата и щракнете върху клетката OT, която е вторият операнд във формулата (формулата ще бъде въведена).
4. Кликнете Въведете(в клетка B8 резултатът ще бъде изчислен).
5. Повторете стъпки 5-8, за да изчислите съответните формули в клетки C8 и 08.
6. Запазете файла с името Education_Employees.x1s.

Таблица 24Резултат от изчислението

Дублиране на формули с помощта на манипулатора за попълване

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

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

1. Изберете клетката, съдържаща формулата за копиране.
2. Плъзнете маркер за запълванев правилната посока. Формулата ще бъде дублирана във всички клетки.

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

Упражнение

Репликация на формули

1. Отворете файла Education_Employees.x1s.

1. Въведете в клетка E3 формулата за автоматично сумиране на клетки = SUM (OT: 03).
2. Плъзнете и пуснете манипулатора за попълване, за да копирате формулата в клетки E4:E8.
3. Вижте как се променят относителните адреси на клетки в получените формули (Таблица 25) и запазете файла.

Таблица 25. Промяна на адресите на клетки при репликиране на формули

Относителни и абсолютни връзки

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

Използването на относителни препратки е подобно на посочване на посоката на движение по улицата - "отидете три пресечки на север, след това две пресечки на запад." Следването на тези инструкции от различни начални места ще доведе до различни дестинации.

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

Абсолютна препратка към клетка. Областта на клетка Go винаги ще препраща към същия адрес на ред и колона. В сравнение с посоките на улиците ще бъде нещо подобно: „Отидете до кръстовището на Арбат и Булевардния пръстен“. Независимо от началната точка, това ще доведе до едно и също място. Ако формулата изисква адресът на клетката да остане непроменен при копиране, тогава трябва да се използва абсолютна препратка (формат на запис $A$1). Например, когато формула изчислява части от общата сума, препратката към клетката, съдържаща общата сума, не трябва да се променя при копиране.

Знакът за долар ($) ще се появи както преди препратка към колона, така и препратка към ред (напр. $C$2). Последователното натискане на F4 ще добави или премахне знак преди номера на колоната или реда в препратката (C$2 или $C2 - така наречените смесени връзки).

1. Създайте таблица, подобна на тази по-долу.

Таблица 26. Платежна ведомост

1. В клетката SZ въведете формулата за изчисляване на заплатата на Иванов \u003d V1 * VZ.

При репликиране на формулата от този пример с относителни препратки в клетка C4 се появява съобщение за грешка (#VALUE!), тъй като относителният адрес на клетка B1 ще се промени и формулата =B2*B4 ще бъде копирана в клетка C4;

1. Задайте абсолютната препратка към клетка B1, като поставите курсора в лентата за формули върху B1 и натиснете клавиша F4.Формулата в клетка C3 ще изглежда като =$B$1*B3.
2. Копирайте формулата в клетки C4 и C5.
3. Запазете файла (Таблица 27) под името Заплата.xls.

Таблица 27. Резултати от заплатите

Имена във формули

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

• имената могат да бъдат с дължина до 255 знака;
• имената трябва да започват с буква и могат да съдържат произволен знак с изключение на интервал;
• имената не трябва да изглеждат като връзки, като OT, C4;
• имената не трябва да използват функции на Excel като напр СУМА АКОи така нататък.

В менюто Вмъкване, ИмеИма две различни команди за създаване на именувани области: Създаване и Присвояване.

Екип Create ви позволява да зададете (въведете) необходимото име ( само един), Задайте командаизползва етикетите, поставени в работния лист, като имена на области (разрешено е да се създава няколко имена наведнъж).

Създаване на име

1. Изберете клетка B1 (Таблица 26).
2. Изберете от менюто Вмъкване, име (Вмъкване, име) команда Присвояване (дефиниране).
3. Въведете вашето име Почасова ставка и щракнете върху OK.
4. Изберете клетка B1 и се уверете, че полето за име казва часова ставка.

Създаване на множество имена

1. Изберете клетки ВЗ:С5 (Таблица 27).
2. Изберете от менюто Вмъкване, Име (Вмъкване, Име) команда Създаване (Създаване), ще се появи диалогов прозорец. Създайте имена(фиг. 88).
3. Уверете се, че радиобутонът в колоната вляво е отметнат и щракнете Добре.
4. Маркирайте клетките OT:NW и се уверете, че полето за име казва Иванов.

Ориз. Фигура 88. Диалогов прозорец за създаване на имена

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

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

1. Изберете желаното име от списъка и щракнете върху OK.

Грешки във формулите

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

Excel може да разпознае далеч не всички грешки, но откритите трябва да могат да се коригират.

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

Грешка #DIV/0!се появява, когато се прави опит за деление на нула във формула. Това най-често се случва, когато делителят е препратка към клетка, която съдържа нула или празна стойност.

Грешка #N/A!е съкращение за „недефинирани данни“. Тази грешка показва използването на препратка към празна клетка във формулата.

Грешка #NAME?се появява, когато име, използвано във формула, е било премахнато или не е било предварително дефинирано. За да коригирате, дефинирате или коригирате името на областта с данни, името на функцията и т.н.

Грешка #ПРАЗЕН!се появява, когато е посочено пресичането на две области, които всъщност нямат общи клетки. Най-често грешката показва, че е направена грешка при въвеждане на препратки към диапазон от клетки.

Грешка #NUMBER!се появява, когато във функция с числов аргумент се използва невалиден формат или стойност на аргумент.

Грешка #VALUE! се появява, когато във формула се използва невалиден аргумент или тип операнд. Например въвежда се текст вместо числова или булева стойност за оператор или функция.

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

Циркулярна препратка възниква, когато формула включва пряко или непряко препратки към собствената си клетка. Циркулярната препратка може да причини изкривяване в изчисленията на работния лист и следователно се счита за грешка в повечето приложения. При въвеждане на кръгова препратка се появява предупредително съобщение (фиг. 89).

За да коригирате грешката, изтрийте клетката, която е причинила кръговата препратка, редактирайте или въведете отново формулата.

Функции в Excel

По-сложните изчисления в таблиците на Excel се извършват с помощта на специални функции (фиг. 90). Списъкът с функционални категории е достъпен, когато изберете командата функцияв менюто Вмъкване (Вмъкване, Функция).

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

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

Ориз. 90. Помощник за функции

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

Статистически функцииви позволяват да извършвате статистически анализ на данните. Например, можете да определите средната стойност и дисперсията на извадка и много повече.

Функции на бази данниможе да се използва за извършване на изчисления и за избор на записи по условие.

Текстови функциипредоставят на потребителя възможност за обработка на текст. Например, можете да свържете множество низове с помощта на функцията СВЪРЗВАНЕ.

Булевите функции са предназначени да тестват едно или повече условия. Например функцията IFви позволява да определите дали определеното условие е вярно и връща една стойност, ако условието е вярно, и друга стойност, ако е невярно.

Функции Проверка на свойства и стойностиса предназначени да дефинират данните, съхранявани в клетката. Тези функции проверяват стойностите в клетката според условието и връщат стойностите в зависимост от резултата. Истина или лъжа.

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

Упражнение

Изчисляване на средната стойност за всеки ред във файла Education.xls.

1. Маркирайте клетка F3 и щракнете върху бутона Съветник за функции.
2. В първия диалогов прозорец на Съветника за функции от категорията Статистически изберете функция СРЕДНО АРИТМЕТИЧНО, щракнете върху бутона По-нататък.
3. Аргументите трябва да бъдат дадени във втория диалогов прозорец на съветника за функции. Курсорът за въвеждане е в полето за въвеждане на първия аргумент. В това поле като номер на аргумент! въведете адреса на диапазона B3:D3 (фиг. 91).
4. Кликнете Добре.
5. Копирайте получената формула в клетки F4:F6 и запазете файла (Таблица 28).

Ориз. 91 Въвеждане на аргумент в съветника за функции

Таблица 28. Таблица с резултати от изчисления с помощта на съветника за функции

За да въведете диапазон от клетки в прозореца на съветника за функции, можете да кръгнете този диапазон в работния лист на таблицата (в примера B3:D3). Ако прозорецът на съветника за функции покрива желаните клетки, можете да преместите диалоговия прозорец. След като изберете диапазон от клетки (B3:D3), около него ще се появи текуща пунктирана рамка и адресът на избрания диапазон от клетки автоматично ще се появи в полето за аргумент.