Как да напиша формули в Excel. Пълна информация за формулите в Excel
Програма на Microsoft Excel Office има много удобни функции, които ви помагат да не правите дълги изчисления, преди да въведете данни в таблици или диаграми. За да изчислите някои стойности, например събиране, изваждане, съотношение и други, можете да използвате първоначално посочените формули. Можете да ги добавите сами или да изберете от списъка на съществуващите. Научете се да вмъквате формула стъпка по стъпка в тази статия.
Отворете Excel. Пред вас ще има работно пространство, уверете се, че е отворен раздел "Начало". В празен ред, разположен между заглавката на програмата и нейната работна област, ще видите поле за въвеждане на формула, ще ви трябва след въвеждане на аргументите, тоест числата, които трябва да бъдат изчислени.
Сега знаете точно как да създадете формула в Excel и да видите обяснението им в съветника за функции.
Ако досега не сте работили с Excel, скоро ще разберете, че това не е просто електронна таблица за въвеждане на числа. Разбира се, в Excel можете просто да преброите сумите в редове и колони, но също така можете да изчислите ипотечните плащания, да решите математически и инженерни проблеми и да намерите най-благоприятните опции в зависимост от дадените променливи.
В Excel всичко това се прави с помощта на формули в клетки. Тези формули се използват за извършване на изчисления и други действия с данните на работния лист. Формулата винаги започва със знак за равенство (\u003d), след което можете да въведете числа, математически оператори (като знаците + и - за събиране и изваждане) и вградени функции на Excel, които значително разширяват възможностите на формулите.
По-долу е даден пример за формула, която умножава 2 по 3 и добавя 5 към резултата, за да получи 11.
Следват примери за формули, които могат да се използват на работни листове.
Части от формула на Excel
Формулата може също да съдържа един или повече елементи като функции, връзки, оператори и константи.
Редът на действията във формулите
В някои случаи редът на оценяване може да повлияе на стойността, върната от формула, така че е важно да разберете стандартния ред на оценяване и да знаете как да го промените, за да получите резултатите, които искате.
Използване на функции и вложени функции във формули на Excel
Функциите са предварително дефинирани формули, които извършват изчисления на дадени стойности - аргументи - в определен ред или модел. Функциите могат да се използват за извършване както на прости, така и на сложни изчисления. всичко функции на Excel може да се види в раздела Формули.
Използване на връзки във формули на Excel
Връзката сочи към клетка или диапазон от клетки в работен лист и казва Microsoft Excel, където се намират стойностите или данните, изисквани от формулата. Използвайки връзки, можете да използвате данни от различни части на работния лист в една формула, както и да използвате стойността на една клетка в множество формули. Освен това можете да зададете връзки към клетки в различни листове на една работна книга или към клетки от други книги. Позоваванията на клетки в други книги се наричат \u200b\u200bвръзки или xrefs.
Използване на имена във формули на Excel
За обозначаване на клетки, диапазони от клетки, формули, константи и електронни таблици в Excel можете да създадете конкретни имена. Името е смислено кратко обозначение, което обяснява целта на препратка към клетка, константа, формула или таблица, тъй като може да бъде трудно да се разбере с един поглед. По-долу са дадени примери за имена и как те могат да бъдат използвани, за да улеснят разбирането на формулите.
Пример 1
Пример 2
Копирайте примерните данни от таблицата по-долу и ги поставете в клетка A1 на новата работен лист на Excel... За да покажете резултатите от формулите, изберете ги и натиснете F2, след което натиснете Enter. Освен това можете да регулирате ширината на колоните според данните, които съдържат.
Забележка: Във формулите в колони C и D конкретното име Продажби се заменя с препратка към диапазона A9: A13, а името Информация за продажбите се заменя с диапазона A9: B13. Ако книгата няма тези имена, формулите в D2: D3 ще върнат грешката #NAME?
|
Примерен тип |
Пример, който не използва имена |
Пример за използване на имена |
Формула и резултат с използване на имена |
|
"\u003d СУММА (A9: A13) |
"\u003d SUM (Продажби) |
SUM (Продажби) |
|
|
"\u003d TEXT (VLOOKUP (MAX (A9: 13), A9: B13,2, FALSE)," dd / mm / yyyy ") |
"\u003d TEXT (VLOOKUP (MAX (Продажби), Информация за продажбите, 2, FALSE)," dd.mm.yyyy ") |
ТЕКСТ (VLOOKUP (MAX (Продажби), Информация за продажбите, 2, FALSE), "dd.mm.yyyy") |
|
|
Дата на продажба |
|||
За повече информация вижте статията Определяне и използване на имена във формули.
Използване на формули на масив и константи на масив в Excel
Формулата на масив може да извърши множество изчисления и след това да върне единична стойност или група от стойности. Формулата на масив обработва множество набори от стойности, наречени аргументи на масив. Всеки аргумент на масив трябва да съдържа еднакъв брой редове и колони. Формулата на масив се създава по същия начин като другите формули, с тази разлика, че използвате CTRL + SHIFT + ENTER за въвеждане на формулата. Някои вградени функции са формули на масиви и трябва да бъдат въведени като масиви за правилни резултати.
Константите на масива могат да се използват вместо препратки, ако не е необходимо да въвеждате всяка константа в отделна клетка на листа.
Използване на формула на масив за изчисляване на една или повече стойности
Забележка: Когато въведете формула на масив, Excel автоматично го затваря в фигурни скоби (и). Ако се опитате ръчно да въведете фигурни скоби, Excel ще покаже формулата като текст.
Използване на константи на масива
В обикновена формула можете да въведете препратка към клетка със стойност или към самата стойност, наричана още константа. По същия начин можете да въведете препратка към масив или масив от стойности, съдържащи се в клетки (понякога наричани константа на масив) във формула на масив. Формулите на масива приемат константи по същия начин като другите формули, но константите на масива трябва да се въвеждат в определен формат.
Константите на масива могат да съдържат числа, текст, булеви стойности като TRUE или FALSE или стойности за грешки като # N / A. Константата на един масив може да съдържа стойности от различни типове, например (1,3,4; TRUE, FALSE, TRUE). Числата в константите на масива могат да бъдат цели, десетични или експоненциални. Текстът трябва да бъде затворен в двойни кавички, например „Вторник“.
Уверете се, че са изпълнени следните изисквания при форматиране на константи на масива.
Константите са затворени в къдрави скоби ( { } ).
Колоните са разделени със запетаи ( , ). Например, за да представите стойностите 10, 20, 30 и 40, въведете (10,20,30,40). Тази константа на масива е матрица 1 на 4 и съответства на референция от един ред и четири колони.
Стойностите на клетките от различни редове са разделени с точка и запетая ( ; ). Например, за да представите стойностите 10, 20, 30, 40 и 50, 60, 70, 80 в клетки една под друга, можете да създадете константа на масива 2 на 4: (10,20,30, 40; 50, 60,70,80).
Изтриване на формула
Заедно с формулата се изтриват и резултатите от нейното изчисление. Можете обаче да изтриете самата формула и да оставите резултата от нейното изчисление като стойност в клетката.
Използване на дебело черво ( : ), препратките към първата и последната клетки в диапазона са разделени. Например: A1: A5.
Посочени задължителни аргументи
Функция може да има задължителни и незадължителни аргументи (последните са посочени в синтаксиса с квадратни скоби). Трябва да се въведат всички необходими аргументи. Също така, опитайте се да не въвеждате твърде много аргументи.
Формулата съдържа не повече от 64 нива на влагане на функции
Нивата на влагане не могат да надвишават 64.
Имената на книгите и листовете са затворени в единични кавички
Ако имената на листовете или работните книги, които препращате към клетки или стойности, съдържат не азбучни знаци, трябва да ги приложите в единични кавички " ).
Посочен е пътят до външни книги
Въведени номера без форматиране
Не използвайте знаци за долар, когато въвеждате числа във формула, тъй като те се използват за обозначаване на абсолютни референции. Например вместо стойността $1000 трябва да влезете 1000 .
Важно: Изчислените резултати от формули и някои функции на работния лист на Excel могат да се различават леко на компютри с x86 или x86-64 Windows и базирани на ARM компютри с Windows RT.
От дълго време целият поток на документи се осъществява с помощта на компютри и специални приложни програми. Като правило почти всички организации, студенти и т.н., за въвеждане или извършване на изчисления, използват офис софтуерен пакет, разработен от Microsoft. За да направите висококачествени таблици, в които стойностите ще бъдат преизчислени автоматично според определени правила, трябва да проучите задълбочено формулиExcel с примериза опростяване на изчисленията.
Някои клетки в таблицата се изчисляват въз основа на статистически данни, които се въвеждат в други клетки. Например, ако е необходимо да се изчисли цената на няколко продукта, продадени във фактура, количеството се умножава по цената и резултатът е желаната стойност. ОТ електронни таблици много по-лесно е да направите това, защото като направите един шаблон и след това промените някои статистически стойности, се извършва преизчисление. За да бъде резултатът правилен, трябва задълбочено да знаете как правилно да въвеждате формули, защото в противен случай изчислението ще бъде неправилно. Нека разгледаме няколко примера за прости формули, които се използват най-често, докато те ясно ще демонстрират правилността на тяхното компилиране.
За най-простия пример, нека изчислим сумата от две стойности. За да направите това, в клетка A1 напишете числото 5, а в B1 числото 3. За да се изчисли автоматично сумата, напишете следната комбинация от знаци в клетка C1, която ще бъде формулата: \u003d SUM (A1; В1).
В резултат на това изчислената стойност ще се покаже на екрана. Разбира се, не е трудно да се изчислят такива стойности, но ако броят на членовете е например около 10, е доста трудно да се направи. Когато промените една от въведените стойности, резултатът ще бъде преизчислен автоматично.
За да не се объркате къде и каква формула е изписана, точно над таблицата се показва ред, в който е записана стойността на клетката (или статистическа, или формула за изчисление).
За да усложним задачата, въвеждаме дробни числа в клетки А1 и В1, които са много по-трудни за изчисляване. В резултат C1 ще бъде преизчислен и ще бъде показана правилната стойност.
В Excel можете да правите не само събиране, но и други аритметични операции: изваждане, умножение, деление. Формулата винаги трябва да започва със символа "\u003d" и да посочва така наречените координати на клетките (в този пример това са А1 и В1).
Създавайте формули сами
В горния пример формулата \u003d SUM (A1; B1) ви позволява да добавите две стойности. Символът "\u003d" се разбира като начало на формула, SUM е служебна дума, която обозначава изчислението на сумата, A1 и B1 са координатите на статични стойности, които действат като термини и те трябва да бъдат разделени с точка и запетая ";".
Помислете за пример за добавяне на три клетки A1, B1, C1. За това е написана почти идентична формула, но третата клетка е допълнително посочена в скоби. В резултат на това е необходимо да напишете следната комбинация \u003d SUM (A1; B1; C1) в клетка D1, за да извършите изчислението.
Ако има доста термини, които отиват в един ред (колона), например 10, формулата може да бъде опростена, като се посочи диапазон от координати. В резултат формулата ще изглежда така: \u003d SUM (A1: A10). Като клетка за показване на желания резултат изберете клетка B6. В резултат на това ще получим резултата, показан на екранната снимка.
За да се умножат всички посочени стойности в колоната, е необходимо да напишете PRODUCT във формулата вместо SUM.
Забележка: ако потребителят трябва да извърши добавяне или друга аритметична операция с няколко колони и редове, можете да зададете координатите диагонално противоположни и ще се изчисли масив от стойности (за да се изчисли произведението на стойностите от 10 реда в колони A, B, C, формулата ще изглежда така: \u003d ПРОДУКТ (A1: C10)).
Комбинирани формули
Електронните таблици са в състояние да извършват не само прости аритметични изчисления, но и сложни математически изчисления.
Например, нека изчислим сумата от стойностите и я умножим по коефициент 1,4, ако по време на добавянето стойността е по-малка от 90. Ако резултатът е по-голям от 90, тогава трябва да се умножи по 1,5. В този случай формулата вече съдържа няколко оператора и изглежда много по-сложна: \u003d IF (SUM (A1: C1)<90;СУММ(А1:С1)*1,4;СУММ(А1:С1)*1,5).
Тук вече са използвани две функции вместо една: IF и SUM. Първата команда е условен израз, който работи по следния начин:
- ако сумата от три стойности (А1: С1) е по-голяма от 90, умножението се извършва по 1,4, т.е. по първия коефициент, посочен след условието;
- ако (A1: C1) е по-малко от 90 (както в нашия даден пример), тоест условието не е изпълнено, тази стойност се умножава по втория коефициент.
В нашия пример има само две функции, но може да има много повече, програмата е практически неограничена в това.
Вградени функции в електронни таблици
За да не предписвате официални думи, можете да използвате специалната възможност да инсталирате функцията, без да я набирате. Тоест, за да изчислите сумата от стойности, можете да използвате тази функция и да изберете конкретно действие за посочения диапазон от стойности. За това има специален бутон, показан на екранната снимка.
Струва си да се отбележи, че там има доста функции и много от тях може да не са необходими, но някои от тях ще се използват редовно.
Всички функции са категоризирани, за да улеснят намирането на тази, която искате.
Функция VLOOKUP
Ако таблицата се състои от няколкостотин реда и не винаги е възможно да се намери желаната позиция, можете да използвате функцията VLOOKUP, която ще търси и връща резултата в клетката, в която е записана формулата.
Например, ако трябва да намерим фамилното име, на което е присвоен сериен номер 007, трябва да следваме стъпките, които са ясно показани на екранната снимка.
Стойността за търсене се избира като първи аргумент. Обхватът на стойностите за търсене е избран като втори аргумент. Числото 2 показва, че стойността от втората колона трябва да се покаже в тази клетка.
Резултатът е следният резултат.
Закръгляване на стойности
Excel може да закръгли точно, за да опрости изчисленията. За това има специална функция "ROUNDUP". Например, ако трябва да закръглим числото, посочено в клетка A1, въведете в съседната формула \u003d ROUNDUP (A1; 0), където вторият аргумент показва до какъв ред на десетичните знаци е досадно закръгляване (ако посочите нула, общата стойност ще бъде цяло число). Резултатът е следният резултат.
Както можете да видите от екранната снимка, е настъпило закръгляване. За да се изпълни в по-малък, има функция "ОБОБРАЗВАНЕ".
И въпреки че на пръв поглед изглежда, че електронните таблици на Excel са доста трудни за разбиране, трябва само да работите с тях за кратко време и да смятате, че това твърдение не е съвсем правилно. Освен това ползите от тях са трудни за надценяване.
Както знаете, редакторът на електронни таблици на Excel първоначално е проектиран да извършва математически, алгебрични и други изчисления, за които се използва въвеждането на специални формули. Въпреки това, в светлината на това как да напишем формула в Excel, трябва да се има предвид, че повечето от операциите за въвеждане са коренно различни от всичко, което е обичайно да се използва в ежедневието. Тоест самите формули имат малко по-различна форма и използват напълно различни оператори, които се използват при писане на стандартни изчисления.
Нека разгледаме въпроса как да напишем формула в Excel, като използваме няколко прости примера, без да засягаме сложни операции, за които трябва да проучите програмата достатъчно дълбоко, за да я разберете. Но дори предварителните познания ще дадат на всеки потребител разбиране на основните принципи на използване на формули за различни случаи.
Как да напиша формула в Excel: основни понятия
Така че въвеждането на стойности на формули в програмата е малко по-различно от стандартните операции, използваните символи и използваните оператори. Когато решавате проблема как да напишете формула в Excel, е необходимо да надградите основните понятия, които се използват в почти всички компютърни системи.
Факт е, че машината не разбира въвеждането на комбинация като "2 x 2" или поставяне на общ компонент извън скобите ("2 + 2) 5"). За това са предвидени няколко типа символи, които са представени в таблицата по-долу, без да се броят логическите оператори.
В този случай приоритетът на извършване на операции започва от степента и завършва с добавяне и изваждане. Освен това, въпреки че Excel може да се използва като обикновен калкулатор, като правило за изчисления трябва да посочите номера на клетки или диапазони от клетки. От само себе си се разбира, че форматът на данните във всяка такава клетка трябва да бъде зададен на подходящия (поне цифров).
Сума и разлика
Как да напиша формула за сума или разлика в Excel? И така, нека започнем с най-простото, когато трябва да изчислите сумата. В реда с формули (освен това за всички операции) първо се въвежда знак за равенство, след което се въвежда желаната формула. В случай на конвенционален калкулатор, можете да посочите за инсталираната клетка "\u003d 2 + 2".
Ако сумирането се извършва за стойности, въведени директно в други клетки (например А1 и А2), формулата става "\u003d А1 + А2". Не е необичайно да се използват скоби, за да се използват допълнителни оператори. За разликата - същото нещо, само с минус вместо плюс.
Когато трябва да посочите броя на клетките или техния обхват, може да се използва специална команда за сбор (в руската версия "SUM", на английски - SUM). Когато се посочват няколко клетки, това изглежда така: "\u003d SUM (A1; A2)", за диапазон - "SUM (A1: A10)", при условие че трябва да изчислите сумата от всички числа в клетките от първата до десетата. По принцип, ако зададете активната клетка, която се намира непосредствено след последната в колоната с началните стойности, не е необходимо да въвеждате формулата, а просто щракнете върху бутона за автоматично сумиране.
Умножение, деление и степенуване
Сега нека видим как да напишем формула за умножение или деление в Excel. Редът е същият като при въвеждане на сума или разлика, само операторите се различават.
За произведение се използва формата "\u003d A1 * A2", за коефициент - "A1 / A2". Между другото, това са абсолютно същите команди, които могат да бъдат намерени при използване на стандартен калкулатор на Windows.
Символът "^" се използва за степенуване. За стойността в клетка A1, която например трябва да бъде на квадрат, се прилага формулата "\u003d A1 ^ 2".
Процентни изчисления
С интерес, ако не се докоснете до сложни изчисления, всичко също е просто. Как да напиша формула с проценти в Excel?
Достатъчно е да въведете формула от формата "\u003d A1 * 5%", след което ще получите точно тези пет процента от стойността в съответната клетка.
Използване на въвеждане на формула въз основа на избор на клетка
Но всичко това е свързано с ръчно задаване или т. Нар. Директно въвеждане на формули (директно или директно въвеждане). Всъщност понякога е полезно да използвате мишката и клавиша Ctrl.
Докато задържате бутона на мишката, можете просто да изберете желаните клетки, като първо въведете необходимото изчисление в лентата с формули. Клетките ще бъдат добавени директно към лентата с формули. Но в зависимост от вида на формулата понякога скобите ще трябва да се въвеждат ръчно.
Абсолютни, относителни и смесени типове клетки
Трябва да се отбележи отделно, че програмата може да използва няколко типа клетки, да не говорим за данните, които те съдържат.
Абсолютната клетка е непроменена и се обозначава като $ A $ 1, относителна е препратка към обичайното местоположение (A1), смесена - има комбинация от препратки към абсолютни и относителни клетки ($ A1 или A $ 1). Обикновено такива формати се използват при създаване на формули, когато данните са включени в различни листове на книгата или дори в различни файлове. 
VLOOKUP формули
И накрая, нека видим как да напишем формула VLOOKUP в Excel. Тази техника ви позволява да вмъквате данни от един диапазон в друг. В този случай методът е донякъде подобен на този, използван при решаването на проблема как да се напише формулата "Условие" в Excel, която използва символите, показани в горната таблица.
По принцип такива изчисления са като обикновен филтър, приложен към колони, където искате да филтрирате само точни стойности, а не приблизителни стойности.
В тази опция, първо чрез "Съветник за функции", се използва диапазонът от стойности на оригиналната (първата) таблица, вторият диапазон е посочен в полето "Таблица" с фиксиране на съдържанието (F4), след това колоната е посочено число и стойността „FALSE“ е зададена в полето за изглед на интервал, ако наистина, когато филтрирате, наистина трябва само да получите точни, а не приблизителни стойности. По правило такива формули се използват по-скоро в склад или счетоводство, когато не е възможно да се инсталират специализирани софтуерни продукти.
Заключение
Остава да се каже, че тук не са описани всички формули, които могат да се използват в редактора на електронни таблици на Excel. Това, така да се каже, е само основите. Всъщност, ако се задълбочите в тригонометрията или изчислявате логаритми, матрици или дори тензорни уравнения, всичко изглежда много по-сложно. Но за да се проучи всичко това, е необходимо да се проучи задълбочено ръководството за самия редактор. И тук не става въпрос за факта, че в Excel, въз основа на променящите се данни, можете да създавате дори най-простите логически игри. Като пример можем да посочим същата „змия“, която първоначално нямаше нищо общо с редактора на електронни таблици, но беше възпроизведена от ентусиасти в тяхната област в Excel.
В останалото трябва ясно да се разбере, че след изучаване на примитивни формули или действия с данни, тогава ще бъде възможно лесно овладяване на по-сложни изчисления, да речем със създаването на кръстосани препратки, използването на различни видове скриптове или VB скриптове и др. Всичко това отнема време, така че ако искате да изучите програмата и всичките й възможности до максимум, ще трябва да се потите над теоретичната част.