Общ преглед на формулите в Excel
Excel прави това, като използва формули в клетки. Формулата извършва изчисления или други действия над данните в работния лист.
Формулата винаги започва със знак за равенство (=), който може да бъде последван от числа, математически оператори (например знак + или - за събиране или изваждане) и вградени функции на Excel, които могат наистина да увеличат възможностите на формулата.
Например следващата формула умножава 2 по 3 и след това добавя 5, което дава в резултат отговора 11.
=2*3+5
Формулата винаги започва със знак за равенство (=), който може да бъде последван от числа, математически оператори (например знак + или - за събиране или изваждане) и вградени функции на Excel, които могат наистина да увеличат възможностите на формулата.
Например следващата формула умножава 2 по 3 и след това добавя 5, което дава в резултат отговора 11.
=2*3+5
Ето някои допълнителни примери за формули, които можете да въвеждате в работен лист:
•
=A1+A2+A3 Събира стойностите в клетки A1 и A2 и A3.
•
=SUM(a1:A10) Използва функцията SUM за връщане на сумата от стойностите в клетки от A1 до A10.
•
=TODAY() Връща текущата дата.
•
=UPPER("hello") Преобразува текста "hello" в "HELLO" с помощта на функцията UPPER.
•
=IF(A1>0) Използва функцията IF, за да провери клетка A1 и да определи дали съдържа стойност, която е по-голяма от 0.
Частите на формула на Excel
Една формула може да съдържа всички или някои от следните елементи: функции, препратки, оператори и константи.
Една формула може да съдържа всички или някои от следните елементи: функции, препратки, оператори и константи.
Частите на формула
•
Функции: Функцията PI() връща стойността на числото пи: 3,142...
•
Препратки: A2 връща стойността в клетката A2.
•
Константи: Стойности на числа или текст, въведени директно във формула, например 2.
•
Оператори: Операторът ^ (карета) повдига число на степен, а операторът * (звездичка) умножава числа.
Използване на константи във формули на Excel
Константата е стойност, която не се изчислява. Тя винаги е една и съща. Например датата 9.10.2008, числото 210 и текстът "Тримесечен доход" са константи. израз или стойност, която се получава от израз, не са константи. Ако в една формула използвате константи вместо препратки към клетки (например =30+70+110), резултатът се променя само ако промените формулата. По принцип е най-добре да поставяте константите в отделни клетки, където те могат да бъдат променени лесно, ако е необходимо, и след това да използвате препратки към тези клетки във формулите.
Използване на оператори за изчисляване във формули на Excel
Операторите задават типа изчисление, което искате да извършите с елементите на формулата. Excel следва общите математически правила при изчисленията т.е. скоби, степени, умножение и делениеи събиране и изваждане. Използването на скоби ви позволява да промените този ред на изчисление.
Има четири различни типа оператори за изчисление: аритметични, за сравнение, текстови и препратки.
Константата е стойност, която не се изчислява. Тя винаги е една и съща. Например датата 9.10.2008, числото 210 и текстът "Тримесечен доход" са константи. израз или стойност, която се получава от израз, не са константи. Ако в една формула използвате константи вместо препратки към клетки (например =30+70+110), резултатът се променя само ако промените формулата. По принцип е най-добре да поставяте константите в отделни клетки, където те могат да бъдат променени лесно, ако е необходимо, и след това да използвате препратки към тези клетки във формулите.
Използване на оператори за изчисляване във формули на Excel
Операторите задават типа изчисление, което искате да извършите с елементите на формулата. Excel следва общите математически правила при изчисленията т.е. скоби, степени, умножение и делениеи събиране и изваждане. Използването на скоби ви позволява да промените този ред на изчисление.
Има четири различни типа оператори за изчисление: аритметични, за сравнение, текстови и препратки.
Ред, в който Excel извършва операциите във формулите
В някои случаи редът, в който се извършва изчисляването, може да повлияе на връщаната от формулата стойност, така че е важно да се разбере как се определя редът и как можете да промените реда, за да получите желаните резултати.
В някои случаи редът, в който се извършва изчисляването, може да повлияе на връщаната от формулата стойност, така че е важно да се разбере как се определя редът и как можете да промените реда, за да получите желаните резултати.
•
Ред на изчисляване. Формулите изчисляват стойностите в определен ред. Формулите в Excel винаги започват със знака за равенство (=). Excel интерпретира знаците след знака за равенство като формула. След знака за равенство следват елементите, които трябва да бъдат изчислени (операндите), например константи или препратки към клетки, които са разделени с оператори за изчисление. Excel изчислява формулите отляво надясно съгласно приоритета на всеки оператор във формулата.
•
Приоритет на операторите във формули на Excel. Ако комбинирате няколко оператора в една формула, Excel извършва операциите в реда, показан в следващата таблица. Ако една формула съдържа оператори с еднакви приоритети - например умножение и деление - Excel изчислява операторите отляво надясно.
•
Използване на скоби във формули на Excel.
За да промените реда на изчисление, поставете в скоби частта от формулата, която трябва да бъде изчислена първа. Например следната формула дава 11, защото Excel извършва умножението преди събирането. Формулата умножава 2 по 3 и след това добавя 5 към резултата.
=5+2*3
Ако обаче използвате скоби, за да промените синтаксиса, Excel първо събира 5 и 2, а след това умножава резултата по 3, като се получава 21.
=(5+2)*3
В следващия пример скобите, които обграждат първата част от формулата, принуждават Excel първо да изчисли B4+25 и след това да раздели резултата на сумата на стойностите в клетките D5, E5 и F5.
=(B4+25)/SUM(D5:F5)
Използване на функции и вложени функции във формули на Excel
Функциите са предварително зададени формули, които извършват изчисления с помощта на конкретни стойности, наречени аргументи, в определен ред или структура. Функциите могат да се използват, за да се извършат прости или сложни изчисления. Можете да намерите всички функции на Excel в раздела "Формули" на лентата:
=5+2*3
Ако обаче използвате скоби, за да промените синтаксиса, Excel първо събира 5 и 2, а след това умножава резултата по 3, като се получава 21.
=(5+2)*3
В следващия пример скобите, които обграждат първата част от формулата, принуждават Excel първо да изчисли B4+25 и след това да раздели резултата на сумата на стойностите в клетките D5, E5 и F5.
=(B4+25)/SUM(D5:F5)
Използване на функции и вложени функции във формули на Excel
Функциите са предварително зададени формули, които извършват изчисления с помощта на конкретни стойности, наречени аргументи, в определен ред или структура. Функциите могат да се използват, за да се извършат прости или сложни изчисления. Можете да намерите всички функции на Excel в раздела "Формули" на лентата:
Синтаксис на функциите на Excel
Следващият пример на функцията ROUND, закръгляваща число в клетка A10, илюстрира синтаксиса на функцията.
Следващият пример на функцията ROUND, закръгляваща число в клетка A10, илюстрира синтаксиса на функцията.
•
Структура. Структурата на функцията започва със знак за равенство (=), последван от името на функцията, отваряща скоба, аргументите на функцията, разделени с точка и запетая, и затваряща скоба.
•
Име на функция. За да видите списъка с наличните функции, щракнете върху някоя клетка и натиснете SHIFT+F3. Това ще стартира диалоговия прозорец Вмъкване на функция.
•
Аргументи. Аргументите могат да бъдат числа, текст, логически стойности като TRUE или FALSE, масиви, стойности за грешка, като напр. #N/A, или препратки към клетки. Аргументът, който зададете, трябва да създаде валидна стойност за този аргумент. Аргументите могат да бъдат също константи, формули или други функции.
•
Пояснение за аргумент. Когато въведете функция, се появява пояснение със синтаксиса и аргументите. Например пояснение се появява, когато въведете =ROUND(. Пояснения се появяват само за вградени функции.
Въвеждане на функции на Excel
Когато създавате формула, която съдържа функция, можете да използвате диалоговия прозорец Вмъкване на функция, за да си помогнете при въвеждането на функции в работния лист. Когато изберете функция от диалоговия прозорец Вмъкване на функция, Excel ще стартира съветник за функции, който показва името на функцията, всеки от нейните аргументи, описание на функцията и на всеки аргумент, текущия резултат от функцията и текущия резултат от цялата формула.
Когато създавате формула, която съдържа функция, можете да използвате диалоговия прозорец Вмъкване на функция, за да си помогнете при въвеждането на функции в работния лист. Когато изберете функция от диалоговия прозорец Вмъкване на функция, Excel ще стартира съветник за функции, който показва името на функцията, всеки от нейните аргументи, описание на функцията и на всеки аргумент, текущия резултат от функцията и текущия резултат от цялата формула.
Използвайте Автодовършване на формули, за да създавате и редактирате формули по-лесно и да минимизирате грешките при въвеждане и синтактичните грешки. След като въведете = (знак за равенство) и началните букви на дадена функция, Excel показва динамичен падащ списък на валидните функции, аргументи и имена, които съответстват на тези букви. След това можете да изберете елемент от падащия списък и Excel ще го въведе вместо вас.
Влагане на функции на Excel
В някои случаи може да се наложи да се използва функция като един от аргументите на друга функция. Например следващата формула използва вложената функция AVERAGE и сравнява резултата със стойността 50.
Влагане на функции на Excel
В някои случаи може да се наложи да се използва функция като един от аргументите на друга функция. Например следващата формула използва вложената функция AVERAGE и сравнява резултата със стойността 50.
•
Функциите AVERAGE и SUM са вложени във функцията IF.
Валидни върнати стойности
Когато една вложена функция се използва като аргумент, вложената функция трябва да връща същия тип стойност, който този аргумент използва. Ако например аргументът връща стойност TRUE или FALSE, вложената функция трябва да връща стойност TRUE или FALSE. Ако не го прави, Excel показва стойност за грешка #VALUE!.
Граници на нивата на влагане
Една формула може да съдържа до седем нива на вложени функции. Когато една функция (ще наричаме тази функция B) се използва като аргумент в друга функция (ще я наричаме функция A), функция B действа като функция от второ ниво. Например функциите AVERAGE и SUM са функции от второ ниво, ако се използват като аргументи на функцията IF. Функция, която е вложена във функцията AVERAGE, е функция от трето ниво и т.н.
Използване формули за масиви и константи за масив в Excel
Една формула за масив може да извършва множество изчисления и след това да върне или единичен резултат, или множество резултати. Формулите за масиви действат върху два или повече набора от стойности, известни като аргументи за масиви. Всеки аргумент за масив трябва да има един и същ брой редове и колони. Формулите за масиви се създават по същия начин, по който се създават и другите формули, но с тази разлика, че за да въведете формулата, трябва да натиснете CTRL+SHIFT+ENTER. Някои от вградените функции са масиви за формули и трябва да се въвеждат като масиви, за да се получи правилен резултат.
Масивите от константи могат да се използват вместо препратки, когато не искате да въвеждате всяка константна стойност в отделна клетка на работния лист.
Използване на формула за масив за изчисляване на единични и съставни резултати
Забележка: Когато въведете формула за масив, Excel автоматично вмъква формулата между { } (фигурни скоби). Ако се опитате да въведете фигурните скоби сами, Excel ще покаже вашата формула като текст.
Когато една вложена функция се използва като аргумент, вложената функция трябва да връща същия тип стойност, който този аргумент използва. Ако например аргументът връща стойност TRUE или FALSE, вложената функция трябва да връща стойност TRUE или FALSE. Ако не го прави, Excel показва стойност за грешка #VALUE!.
Граници на нивата на влагане
Една формула може да съдържа до седем нива на вложени функции. Когато една функция (ще наричаме тази функция B) се използва като аргумент в друга функция (ще я наричаме функция A), функция B действа като функция от второ ниво. Например функциите AVERAGE и SUM са функции от второ ниво, ако се използват като аргументи на функцията IF. Функция, която е вложена във функцията AVERAGE, е функция от трето ниво и т.н.
Използване формули за масиви и константи за масив в Excel
Една формула за масив може да извършва множество изчисления и след това да върне или единичен резултат, или множество резултати. Формулите за масиви действат върху два или повече набора от стойности, известни като аргументи за масиви. Всеки аргумент за масив трябва да има един и същ брой редове и колони. Формулите за масиви се създават по същия начин, по който се създават и другите формули, но с тази разлика, че за да въведете формулата, трябва да натиснете CTRL+SHIFT+ENTER. Някои от вградените функции са масиви за формули и трябва да се въвеждат като масиви, за да се получи правилен резултат.
Масивите от константи могат да се използват вместо препратки, когато не искате да въвеждате всяка константна стойност в отделна клетка на работния лист.
Използване на формула за масив за изчисляване на единични и съставни резултати
Забележка: Когато въведете формула за масив, Excel автоматично вмъква формулата между { } (фигурни скоби). Ако се опитате да въведете фигурните скоби сами, Excel ще покаже вашата формула като текст.
•
Формула за масив, която изчислява единичен резултат Този тип формула за масив може да опрости работата в работния лист чрез заместване на няколко различни формули с една единствена формула за масив.
Следващият пример изчислява общата сума на масив от брой и цени на акции, без да използва ред от клетки за изчисляване и показване на отделните стойности за всяка акция.
Когато въведете формулата ={SUM(B2:D2*B3:D3)} като формула за масив, тя умножава Shares и Price за всяка акция и след това сумира резултатите от тези изчисления.
•
Формула за масив, която изчислява множество резултати
Някои функции за работен лист връщат масиви от стойности или изискват масив от стойности като аргумент. За да изчислите множество резултати с формула за масив, трябва да въведете масива в диапазон от клетки, който има същия брой редове и колони като масивите за аргументи.
Ако например е дадена последователност от числа за три продажби (в колона B) за три последователни месеца (в колона A), функцията TREND определя праволинейните стойности за продажбите. За да се покажат всички резултати от формулата, тя е въведена в три клетки в колона C (C1:C3).
Ако например е дадена последователност от числа за три продажби (в колона B) за три последователни месеца (в колона A), функцията TREND определя праволинейните стойности за продажбите. За да се покажат всички резултати от формулата, тя е въведена в три клетки в колона C (C1:C3).
Когато въведете формулата = TREND (B1:B3;A1:A3) като формула за масив, тя изчислява три отделни резултата (22196, 17079 и 11962) въз основа на стойностите за трите продажби и трите месеца.
Използване на масиви от константи
В една обикновена формула можете да въведете препратка към клетка, съдържаща стойност, или самата стойност, която се нарича константа. По същия начин в една формула за масив можете да въведете препратка към масив или да въведете масива от стойности, съдържащи се в клетките, който аналогично се нарича масив от константи. Формулите за масиви възприемат константите по същия начин, като обикновените формули, но изискват въвеждането на масивите от константи в определен формат.
Масивите от константи могат да съдържат числа, текст, логически стойности, като например TRUE или FALSE, или стойности на грешки като #N/A. Различните типове стойности могат да са в един и същи масив от константи - например {1,3,4;TRUE,FALSE,TRUE}. Числата в масиви от константи могат да са цели, дробни десетични или в научен формат. Текстът трябва да е обграден с двойни кавички - например "Вторник".
Масивите от константи не могат да съдържат препратки към клетки, колони или редове с различна дължина, формули или специалните знаци $ (знак за долар), скоби или % (знак за процент).
Когато форматирате масив от константи:
В една обикновена формула можете да въведете препратка към клетка, съдържаща стойност, или самата стойност, която се нарича константа. По същия начин в една формула за масив можете да въведете препратка към масив или да въведете масива от стойности, съдържащи се в клетките, който аналогично се нарича масив от константи. Формулите за масиви възприемат константите по същия начин, като обикновените формули, но изискват въвеждането на масивите от константи в определен формат.
Масивите от константи могат да съдържат числа, текст, логически стойности, като например TRUE или FALSE, или стойности на грешки като #N/A. Различните типове стойности могат да са в един и същи масив от константи - например {1,3,4;TRUE,FALSE,TRUE}. Числата в масиви от константи могат да са цели, дробни десетични или в научен формат. Текстът трябва да е обграден с двойни кавички - например "Вторник".
Масивите от константи не могат да съдържат препратки към клетки, колони или редове с различна дължина, формули или специалните знаци $ (знак за долар), скоби или % (знак за процент).
Когато форматирате масив от константи:
•
Обграждайте ги във фигурни скоби ( { } ).
•
Отделяйте стойностите от различните колони със запетаи (,). Например за да представите стойностите 10, 20, 30 и 40, въвеждате {10,20,30,40}. Този масив от константи се нарича масив 1 на 4 и е еквивалентен на препратка към масив от 1 ред и 4 колони.
•
Отделяйте стойностите в различните редове с точка и запетая (;). Например за да представите стойностите 10, 20, 30 и 40 в един ред и 50, 60, 70 и 80 в реда непосредствено под него, трябва да въведете масив от константи 2 на 4: {10,20,30,40;50,60,70,80}.
Изтриване на формула
Когато изтривате формула, стойностите, получени като резултат от формулата, също се изтриват. Можете обаче да премахнете само формулата и да оставите стойността, получена като резултат от формулата, която се показва в клетката.
За да изтриете формули, както и техните получените стойности, направете следното:
Когато изтривате формула, стойностите, получени като резултат от формулата, също се изтриват. Можете обаче да премахнете само формулата и да оставите стойността, получена като резултат от формулата, която се показва в клетката.
За да изтриете формули, както и техните получените стойности, направете следното:
•
Изберете клетката или диапазона от клетки, съдържащи формулата.
•
Натиснете DELETE.
За да изтриете формули, без да премахвате получените стойности, направете следното:
•
Изберете клетката или диапазона от клетки, съдържащи формулата.
•
Ако формулата е формула за масив, изберете диапазона от клетки, който съдържа формулата за масив.
Как да изберете диапазон от клетки, който съдържа формулата за масив
1.
Щракнете върху някоя клетка във формулата за масив.
2.
В раздела Начало, в групата Редактиране щракнете върху Намиране и избор, а след това върху Отиване на.
3.
Щракнете върху Специално.
4.
Щракнете върху Текущ масив.
5.
В раздела Начало, в групата Клипборд щракнете върху Копирай. Можете също да натиснете CTRL+C.
6.
В раздела Начало, в групата Клипборд щракнете върху стрелката под Поставяне , а след това върху Поставяне на стойности.
Ако нямате опит с Excel, скоро ще откриете, че той е нещо повече от мрежа, в която въвеждате числа в редове и колони. Със сигурност можете да използвате Excel за намиране на общи суми за колона или ред с числа, но можете и да изчислявате ипотечна вноска, да решавате математически или инженерни задачи или да откривате сценария за най-добър случай, на базата на променливи числа, които вкарвате.
