Преминаване към основното съдържание

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

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

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

Например следващата формула умножава 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. Оператори: Операторът ^ (карета) повдига число на степен, а операторът * (звездичка) умножава числа.

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

Константите са стойности, които не се изчисляват; винаги остава същото. Например дата 10/9/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 за уеб извършва операции във формули

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

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

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

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

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

Оператор

Описание

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

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

, (запетая)

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

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

%

Процент

^

Степенуване

* и /

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

+ и –

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

&

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

=
< >
<=
>=
<>

Сравняване

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

За да промените реда на изчисление, поставете в скоби частта от формулата, която трябва да бъде изчислена първа. Например следната формула генерира 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)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Стил на препратки по подразбиране    По подразбиране Excel за уеб използва стила на препратки a1, който препраща към колони с букви (от а до 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.

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

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

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

Стил на препратки 3D

Удобно препращане към множество работни листове    Ако искате да анализирате данните в една и съща клетка или диапазон от клетки от множество работни листове в работна книга, използвайте 3D препратка. 3D препратката включва препратка към клетката или диапазона, предшествана от диапазон от имена на работни листове. Excel за уеб използва всеки работен лист, съхранен между началната и крайната имена на препратката. Например =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.

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

  • Изтриване     Ако изтриете листове между Sheet2 и Sheet6, Excel за уеб премахва техните стойности от изчислението.

  • Преместите    Ако премествате листовете между Sheet2 и Sheet6 до местоположение извън адресирания диапазон от листове, Excel за уеб премахва техните стойности от изчислението.

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

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

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

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

Препратка

Значение

R[-2]C

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

R[2]C[2]

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

R2C2

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

R[-1]

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

R

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

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

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

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

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

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

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

Препратка

=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 за уеб понякога създава дефинирано име вместо вас, например когато зададете област за печат.

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

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

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

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

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

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

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

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

Excel за уеб не поддържа създаването на формули за масиви. Можете да гледате резултатите от формули за масиви, създадени в настолното приложение 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}.

Забележка:  Тази страница е преведена чрез автоматизация и може да съдържа граматически грешки и несъответствия. Нашата цел е това съдържание да ви бъде полезно. Можете ли да ни кажете дали информацията е била полезна за вас? Ето статията на английски за справка.

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

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

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

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

×