Общ преглед на формулите

Важно : Тази статия е преведена машинно – вижте отказа от отговорност. Английската версия на тази статия за справка можете да намерите тук .

Ако нямате опит с Excel Online, скоро ще откриете, че той е нещо повече от мрежа, в която въвеждате числа в редове и колони. Да, можете да използвате Excel Online за намиране на общи суми за колона или ред с числа, но можете също да изчислявате ипотечна вноска, да решавате математически или инженерни задачи и да откривате сценария за най-добър случай, на базата на променливи числа, които вкарвате.

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

Например следващата формула умножава 2 по 3 и след това добавя 5, което дава в резултат отговора 11.

=2*3+5

Тази формула използва функцията PMT, за да изчисли ипотечна вноска (1073,64 лв.), която се базира на лихвен процент 5 процента (5% делено на 12 месеца е равно на месечния лихвен процент) за период от 30 години (360 месеца), за заем от 200 000 лв:

=PMT(0,05/12;360;200000)

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

  • =A1+A2+A3    Събира стойностите в клетки A1 и A2 и A3.

  • =SQRT(A1)    Използва функцията SQRT, за да върне квадратен корен от стойността в A1.

  • =TODAY()    Връща текущата дата.

  • =UPPER("hello")     Преобразува текста "hello" в "HELLO" с помощта на функцията в работен лист UPPER.

  • =IF(A1>0)    Проверява клетката A1, за да определи дали тя съдържа стойност, която е по-голяма от 0.

Частите на една формула

Една формула може да съдържа всички или някои от следните елементи: функции, препратки, оператори и константи.

Части от формула
Части от формула

1. Функции: Функцията PI() връща стойността на числото пи: 3,142...

2. Препратки: A2 връща стойността в клетката A2.

3. Константи: Стойности на числа или текст, въведени директно във формула, например 2.

4. Оператори: Операторът ^ (карета) повдига число на степен, а операторът * (звездичка) умножава числа.

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

Константата е стойност, която не се изчислява. Тя винаги е една и съща. Например датата 09.10.2008, числото 210 и текстът "Тримесечен доход" са константи. израз или стойност, която се получава от израз, не са константи. Ако в една формула използвате константи вместо препратки към клетки (например =30+70+110), резултатът се променя само ако промените формулата.

Използване на оператори за изчисляване във формули

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

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

Има четири различни типа оператори за изчисление: аритметични, за сравнение, текстови и препратки.

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

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

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

Значение

Пример

+ (знак плюс)

Събиране

3+3

- (знак минус)

Изваждане
Смяна на знака

3–1
–1

* (звездичка)

Умножение

3*3

/ (наклонена черта)

Деление

3/3

% (знак за процент)

Процент

20%

^ (карета)

Степенуване

3^2

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

С помощта на следните оператори можете да сравнявате две стойности. Резултатът от сравнението е логическата стойност – TRUE или FALSE.

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

Значение

Пример

= (знак за равенство)

Е равно на

A1=B1

> (знак “по-голямо”)

По-голямо от

A1>B1

< (знак “по-малко”)

По-малко от

A1<B1

>= (знак "по-голямо или равно")

По-голямо или равно

A1>=B1

<= (знак “по-малко или равно")

По-малко или равно

A1<=B1

<> (знак за неравенство)

Не е равно на

A1<>B1

Оператор за конкатенация (свързване) на текст

Използвайте знака амперсанд (&), за да конкатенирате (свържете) един или повече текстови низове в един общ текст.

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

Значение

Пример

& (амперсанд)

Свързва, или конкатенира, две стойности, създавайки една непрекъсната текстова стойност

"North"&"wind" дава "Northwind"

Оператори за препратки

Комбинират диапазони от клетки за изчисления със следните оператори.

Оператор за препратка

Значение

Пример

: (двоеточие)

Оператор за диапазон, който създава една препратка към всички клетки между две препратки, включително двете препратки.

B5:B15

; (точка и запетая)

Оператор за обединение, който комбинира множество препратки в една препратка

SUM(B5:B15;D5:D15)

(интервал)

Оператор за сечение, който създава една препратка към клетките, които са общи за две препратки

B7:D7 C6:C8

Ред, в който Excel Online извършва операциите във формулите

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

Ред на изчисляване

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

Приоритет на операторите

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

Оператор

Описание

: (двоеточие)

(единичен интервал)

, (запетая)

Оператори за препратки

Смяна на знака (като в –1)

%

Процент

^

Степенуване

* и /

Умножение и деление

+ и –

Събиране и изваждане

&

Свързване на два текстови низа (конкатенация)

=
< >
<=
>=
<>

Сравняване

Използване на скоби

За да промените реда на изчисление, поставете в скоби частта от формулата, която трябва да бъде изчислена първа. Например следната формула дава 11, защото Excel Online извършва умножението преди събирането. Формулата умножава 2 по 3 и след това добавя 5 към резултата.

=5+2*3

Обратно, ако използвате скоби, за да промените синтаксиса, Excel Online първо събира 5 и 2, а след това умножава резултата по 3, като се получава 21.

=(5+2)*3

В следващия пример скобите, които обграждат първата част от формулата, принуждават Excel Online първо да изчисли B4+25 и след това да раздели резултата на сумата на стойностите в клетките D5, E5 и F5.

=(B4+25)/SUM(D5:F5)

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

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

Синтаксисът на функциите

Следващият пример на функцията ROUND, закръгляваща число в клетка A10, илюстрира синтаксиса на функция.

Структура на функция
Структура на функция

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

2. Име на функция. За да видите списъка с наличните функции, щракнете върху някоя клетка и натиснете SHIFT+F3.

3. Аргументи. Аргументите могат да бъдат числа, текст, логически стойности като TRUE или FALSE, масиви, стойности за грешка, като напр. #N/A, или препратки към клетки. Аргументът, който зададете, трябва да създаде валидна стойност за този аргумент. Аргументите могат да бъдат също константи, формули или други функции.

4. Пояснение за аргумент. Когато въведете функция, се появява пояснение със синтаксиса и аргументите. Например пояснение се появява, когато въведете =ROUND(. Пояснения се появяват само за вградени функции.

Въвеждане на функции

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

Използвайте "Автодовършване на формули", за да създавате и редактирате формули по-лесно и да минимизирате грешките при въвеждане и синтактичните грешки. След като въведете = (знак за равенство) и началните букви или превключвател за показване, Excel Online показва под клетката динамичен падащ списък на валидните функции, аргументи и имена, които съответстват на буквите или превключвателя. След това можете да вмъкнете елемент от падащия списък във формулата.

Влагане на функции

В някои случаи може да се наложи да се използва функция като един от аргументите на друга функция. Например следващата формула използва вложената функция AVERAGE и сравнява резултата със стойността 50.

Вложени функции

1. Функциите AVERAGE и SUM са вложени във функцията IF.

Валидни върнати стойности    Когато една вложена функция се използва като аргумент, вложената функция трябва да връща същия тип стойност, който този аргумент използва. Ако например аргументът връща стойност TRUE или FALSE, вложената функция трябва да връща стойност TRUE или FALSE. Ако не го прави, Excel Online показва стойност за грешка #VALUE!.

Граници на нивата на влагане    Една формула може да съдържа до седем нива на вложени функции. Когато една функция (ще наричаме тази функция B) се използва като аргумент в друга функция (ще я наричаме функция A), функция B действа като функция от второ ниво. Например функциите AVERAGE и SUM са функции от второ ниво, ако се използват като аргументи на функцията IF. Функция, която е вложена във функцията AVERAGE, е функция от трето ниво и т.н.

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

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

Стил на препратки A1

Стил на препратки по подразбиране    По подразбиране Excel Online използва стила за препратки A1, който препраща към колони, означени с букви (от A до XFD, общо 16 384 колони) и към редове, означени с номера (от 1 до 1 048 576). Тези букви и числа се наричат заглавия на редове и колони. За да препратите към една клетка, въведете буквата на колоната, последвана от номера на реда. Например B2 препраща към клетката в пресечната точка на колоната B и реда 2.

За да препратите към

Използвайте

Клетката в колона A и ред 10

A10

Диапазонът от клетки в колона A и редове от 10 до 20

A10:A20

Диапазонът от клетки в ред 15 и колони от B до E

B15:E15

Всички клетки в ред 5

5:5

Всички клетки в редовете от 5 до 10

5:10

Всички клетки в колона H

H:H

Всички клетки в колоните от H до J

H:J

Диапазонът от клетки в колоните от A до E и редовете от 10 до 20

A10:E20

Създаване на препратка към друг работен лист    В следващия пример функцията в работен лист AVERAGE изчислява средната стойност за диапазона B1:B10 в работния лист с име "Маркетинг" в същата работна книга.

Пример на препратка към лист
Препратка към диапазон от клетки на друг работен лист в същата работна книга

1. Препраща към работния лист, наречен "Маркетинг"

2. Препраща към диапазона от клетки между B1 и B10, включително

3. Разделя препратката към работен лист от препратката към диапазон от клетки

Разликата между абсолютни, относителни и смесени препратки

Относителни препратки    Относителната препратка към клетка в формула, например A1, се базира на относителното положение на клетката, която съдържа формулата, и клетката, към която сочи препратката. Ако позицията на клетката, която съдържа формулата, се промени, препратката се променя. Ако копирате или попълвате формулата в друг ред или колона, препратката се настройва автоматично. По подразбиране новите формули използват относителни препратки. Ако например копирате или попълвате относителна препратка от клетка B2 към клетка B3, тя автоматично се настройва от =A1 на =A2.

Копирана формула с относителна препратка
Копирана формула с относителна препратка

Абсолютни препратки    Абсолютната препратка към клетка във формула, например $A$1, винаги препраща към клетката с указаното местоположение. Ако позицията на клетката, съдържаща формулата, се промени, абсолютният адрес остава същият. Ако копирате или попълвате формулата в друг ред или колона, абсолютният адрес не се пренастройва. По подразбиране новите формули използват относителни препратки, така че може да се наложи да ги преобразувате в абсолютни препратки. Ако например копирате или попълвате абсолютен адрес от клетка B2 в клетка B3, той остава същият и в двете клетки: =$A$1.

Копирана формула с абсолютна препратка
Копирана формула с абсолютна препратка

Смесени препратки    Смесената препратка има или абсолютна колона и относителен ред, или абсолютен ред и относителна колона. Препратката с абсолютна колона има вида $A1, $B1, и т.н. Препратката с абсолютен ред има вида A$1, B$1, и т. н. Ако позицията на клетката, съдържаща формулата, се промени, относителната препратка се променя, а абсолютната препратка не се променя. Ако копирате или попълвате формулата в друг ред или колона, относителната препратка се пренастройва автоматично, а абсолютната не се пренастройва. Ако например копирате или попълвате смесена препратка от клетка A2 към B3, тя се настройва от =A$1 в =B$1.

Копирана формула със смесена препратка
Копирана формула със смесена препратка

Препратка в стил 3D

Удобно препращане към множество работни листове    Ако искате да анализирате данните в една и съща клетка или диапазон от клетки от множество работни листове в работна книга, използвайте 3D препратка. 3D препратката включва препратка към клетката или диапазона, предшествана от диапазон от имена на работни листове. Excel Online използва всички работни листове, съхранени между началното и крайното име от препратката. Например =SUM(Лист2:Лист13!B5) добавя всички стойности, съдържащи се в клетките B5 на всички работни листове между и включително Лист2 и Лист13.

  • Можете да използвате 3D препратки, за да препращате към клетки в други листове, да определяте имена и да създавате формули с помощта на следните функции: SSUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV.P, STDEV.S, STDEVA, STDEVPA, VAR.P, VAR.S, VARA и VARPA.

  • 3D препратките не могат да се използват във формули с масиви.

  • 3D препратките не могат да се използват с оператор за сечение (единичен интервал) или във формули, които използват неявна пресечна точка.

Какво се случва, когато премествате, копирате, вмъквате или изтривате работни листове    Следващите примери обясняват какво става, когато преместите, копирате, вмъкнете или изтриете работни листове, които са включени в 3D препратка. Примерите използват формулата =SUM(Лист2:Лист6!A2:A5), която сумира клетките от A2 до A5 в работните листове от 2 до 6.

  • Вмъкване или копиране    Ако вмъкнете или копирате листовете между Лист2 и Лист6 (крайните точки в този пример), Excel Online включва в изчисленията всички стойности от клетките между A2 и A5 на добавените листове.

  • Изтриване     Ако изтриете листове между Лист2 и Лист6, Excel Online премахва техните стойности от изчислението.

  • Преместване    Ако преместите листове, намиращи се между Лист2 и Лист6, извън адресирания диапазон в листа, Excel Online премахва техните стойности от изчислението.

  • Преместване на крайна точка    Ако преместите Лист2 или Лист6 на друго място в същата работна книга, Excel Online настройва изчислението, така че да обхване новия диапазон от листове между тях.

  • Изтриване на крайна точка    Ако изтриете Лист2 или Лист6, Excel Online настройва изчислението, така че да обхване диапазона от листове между тях.

Стил на препратки R1C1

Можете също да използвате стил на препратка, при който и редовете, и колоните в работния лист са номерирани. Стилът на препратка R1C1 е полезен за изчисляване на позициите на редовете и колоните в макроси. При стила R1C1 Excel Online показва местоположението на една клетка с "R", последвано от номер на ред, и "C", последвано от номер на колона.

Препратка

Значение

R[-2]C

относителна препратка към клетката, намираща се два реда нагоре в същата колона

R[2]C[2]

Относителна препратка към клетката, намираща се два реда надолу и две колони вдясно

R2C2

Абсолютна препратка към клетката във втория ред и втората колона

R[-1]

Относителна препратка към целия ред над активната клетка

R

Абсолютна препратка към текущия ред

Когато записвате макрос, Excel Online записва някои команди с помощта на стила R1C1. Ако например записвате команда, като щракнете върху бутона Автосумиране, за да вмъкнете формула, която добавя диапазон от клетки, Excel Online записва формулата с помощта на стила R1C1, а не стила A1.

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

Можете да създавате дефинирани имена, за да представяте клетки, диапазони от клетки, формули, константи или таблици на Excel Online. Името е съдържателно съкращение, което прави по-лесно разбирането на предназначението на препратка към клетка, константа, формула или таблица, всяко от които може да е трудно за разбиране от пръв поглед. Следващата информация показва често срещани примери на имена и как използването им във формули може да подобри яснотата и да направи формулите по-лесни за разбиране.

Тип на примера

Пример, който използва диапазони вместо имена

Пример, който използва имена

Препратка

=SUM(A16:A20)

=SUM(Продажби)

Константа

=PRODUCT(A12;9,5%)

=PRODUCT(Цена;Данъчна_ставкаKC)

Формула

=TEXT(VLOOKUP(MAX(A16;A20);A16:B20;2;FALSE),"м/дд/гггг")

=TEXT(VLOOKUP(MAX(Продажби);Инфо_продажби;2;FALSE);"м/дд/гггг")

Таблица

A22:B25

=PRODUCT(Цена;Таблица1[@Данъчна ставка])

Типове имена

Има няколко типа имена, които можете да създадете и използвате.

Дефинирано име    Име, което представя клетка, формула или константна стойност. Можете да създадете ваше собствено дефинирано име. Освен това Excel Online създава понякога дефинирани имена вместо вас, когато например задавате област за печат.

Име на таблица    Име за таблица на Excel Online, която е колекция от данни за конкретна тема, които се съхраняват в записи (редове) и полета (колони). Excel Online създава име по подразбиране на таблица на Excel Online като "Таблица1", "Таблица2" и т.н., всеки път, когато вмъквате таблица на Excel Online, но вие можете да промените тези имена, за да ги направите по-смислени.

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

Вие създавате имена с помощта на Създаване на име от селекция. Можете удобно да създавате имена от съществуващи редове и етикети на колони, като използвате селекция от клетки в работния лист.

Забележка :  По подразбиране имената използват абсолютни препратки към клетки.

Можете да въведете име посредством:

  • Въвеждане    Например въвеждането на името като аргумент на формула.

  • Използване на "Автодовършване на формули"    Използвайте падащия списък на "Автодовършване на формули", където за вас се изброяват автоматично валидни имена.

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

Excel Online не поддържа създаването на формули за масиви. Можете да гледате резултатите от формули за масиви, създадени в настолното приложение Excel, но не можете да ги редактирате или преизчислявате. Ако имате настолното приложение Excel, щракнете върху Отвори в Excel, за да работите с масиви.

Следващият пример на масив изчислява общата сума на масив от брой и цени на акции, без да използва ред от клетки за изчисляване и показване на отделните стойности за всяка акция.

Формула за масив, която изчислява единичен резултат
формула за масив, която изчислява един резултат

Когато въведете формулата ={SUM(B2:D2*B3:D3)} като формула за масив, тя умножава Shares и Price за всяка акция и след това сумира резултатите от тези изчисления.

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

Ако например е дадена последователност от числа за три продажби (в колона B) за три последователни месеца (в колона A), функцията TREND определя праволинейните стойности за продажбите. За да се покажат всички резултати от формулата, тя е въведена в три клетки в колона C (C1:C3).

Формула за масив, която изчислява множество резултати
Формула за масив, която изчислява множество резултати

Когато въведете формулата = TREND (B1:B3;A1:A3) като формула за масив, тя изчислява три отделни резултата (22196, 17079 и 11962) въз основа на стойностите за трите продажби и трите месеца.

Използване на масиви от константи

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

Масивите от константи могат да съдържат числа, текст, логически стойности, като например 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}.

Забележка : Отказ от отговорност за машинен превод: Тази статия е преведена от компютърна система без човешка намеса. Microsoft предлага тези машинни преводи, за да помогне на потребителите, които не говорят английски, да се възползват от съдържанието за продукти, услуги и технологии на Microsoft. Тъй като статията е преведена машинно, е възможно да съдържа грешки в речника, синтаксиса и граматиката.

Разширете уменията си
Преглед на обучението
Получавайте първи новите функции
Присъединете се към участниците в Office Insider

Беше ли полезна тази информация?

Благодарим ви за обратната връзка!

Благодарим ви за вашата обратна връзка. Изглежда, че ще бъде полезно да ви свържем с един от нашите агенти по поддръжката на Office.

×