Ако сте начинаещ в 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. Оператори: Операторът ^ (карета) повдига число на степен, а операторът * (звездичка) умножава числа.
Използване на константи във формули
Константа е стойност, която не се изчислява; винаги остава същата. Например датата 9.10.2008 г., числото 210 и текстът „Тримесечни приходи“ са константи. израз или стойност, получена от израз, не е константа. Ако използвате константи във формула вместо препратки към клетки (например = 30 + 70 + 110), резултатът се променя само ако промените формулата.
Използване на оператори за изчисляване във формули
Операторите задават типа на изчислението, което искате да се извърши за елементите от формулата. Съществува подразбиращ се ред, в който се изпълняват операторите (това следва общи математически правила), но вие можете да го промените с помощта на скоби.
Типове оператори
Има четири различни типа оператори за изчисление: аритметични, за сравнение, текстови и препратки.
Аритметични оператори
За да извършите основните математически операции, като събиране, изваждане, умножение или деление, групиране на числа и получаване на числови резултати, използвайте следните аритметични оператори.
|
Аритметичен оператор |
Значение |
Пример |
|
+ (знак плюс) |
Събиране |
3+3 |
|
- (знак минус) |
Изваждане |
От 3 до 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/A или препратки към клетки. Аргументът, който посочите, трябва да генерира валидна стойност за този аргумент. Аргументите могат да бъдат също константи, формули или други функции.
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, който препраща към колони с букви (от 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 и т.н. Ако позицията на клетката, която съдържа формулата, се промени, относителната препратка се променя, а абсолютната препратка не се променя. Ако копирате или попълвате формулата в друг ред или колона, относителната препратка автоматично се коригира, а абсолютната препратка не се коригира. Ако например копирате или попълвате смесена препратка от клетка А2 към клетка B3, тя се пренастройва от =A$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.
-
Вмъкване или копиране Ако вмъквате или копирате листове между Лист2 и Лист6 (крайните точки в този пример), Excel за уеб включва всички стойности в клетките от A2 до A5 от добавените листове в изчисленията.
-
Изтриване Ако изтриете листове между Лист2 и Лист6, Excel за уеб премахва техните стойности от изчислението.
-
Преместване Ако премествате листове между Лист2 и Лист6 извън адресирания диапазон на листа, Excel за уеб премахва техните стойности от изчислението.
-
Преместване на крайна точка Ако преместите Лист2 или Лист6 на друго място в същата работна книга, Excel за уеб настройва изчислението, така че да побере новия диапазон от листове между тях.
-
Изтриване на крайна точка Ако изтриете Лист2 или Лист6, 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 за уеб, която представлява колекция от данни за конкретна тема, които се съхраняват в записи (редове) и полета (колони). 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}.
