Excel прави много страхотни неща. За да ги видите в целия им блясък, трябва да разберете как да работите с формули на Excel. А формула, може би си спомняте от сънливите часове прекарани в часовете по математика, е начин да се изчисли номера. Например 2+3=5 е формула. Когато въведете формула в клетка, Excel изчислява формулата и показва нейните резултати в клетката. Щракнете върху клетка A3 и въведете =2+3 например и Excel показва числото 5 в клетка A3.
Позоваване на клетки във формули
Освен числата, формулите на Excel могат да се отнасят до съдържанието на различни клетки. Когато формула се отнася до клетка, числото в клетката се използва за изчисляване на формулата. В изображението по-долу, например, клетка A1 съдържа числото 2; клетка A2 съдържа числото 3; и клетка A3 съдържа формулата =A1+A2. Както е показано в клетка A3, резултатът от формулата е 5. Ако промените числото в клетка A1 от 2 на 3, резултатът от формулата в клетка A3 (=A1+A2) става 6, а не 5. Когато формула се отнася до клетка и числото в клетката се променя, резултатът от формулата също се променя.
Проста формула на Excel.
За да видите стойността на използването на препратки към клетки във формули, разгледайте работния лист, показан по-долу. Целта на този работен лист е да проследи бюджета на асоциацията на родителските учители (PTA):
- Колона C, Реален доход, изброява доходите от различни източници.
- Колона D, Прогнозен доход, показва какъв ще бъде доходът от тези източници според членовете на PTA.
- Колона E, Над/Под бюджет, показва как действителните приходи се сравняват с прогнозираните приходи от различните източници.
Използване на формули на Excel в работен лист.
Тъй като цифрите в колоната Реален доход (колона C) се актуализират, цифрите в колоната Над/Под бюджет (колона E) и реда Общ доход (ред 8) се променят моментално. Тези цифри се променят мигновено, защото формулите се отнасят до числата в клетките, а не до непроменени числа (известни като константи ).
Изображението по-долу показва формулите, използвани за изчисляване на данните в работния лист по-горе. В колона E формулите изваждат числата в колона D от числата в колона C, за да покажат къде PTA е над- или под бюджет за различните източници на доходи. В ред 8 можете да видите как функцията SUM се използва за сумиране на клетки в редове от 3 до 7
Формулите, използвани за генериране на числата в работния лист на Excel по-горе.
Excel е забележително добър за актуализиране на препратки към клетки във формули, когато премествате клетки. За да видите колко добър е Excel, помислете какво се случва с адресите на клетки във формулите, когато изтриете ред в работен лист. Ако формула се отнася до клетка C1, но изтриете ред B, ред C става ред B и стойността в клетка C1 променя адресите от C1 на B1. Бихте си помислили, че препратките във формули към клетка C1 биха били остарели, но ще грешите. Excel автоматично коригира всички формули, които се отнасят до клетка C1. Вместо това тези формули се отнасят до клетка B1.
В случай, че ви е любопитно, можете да покажете формули в клетките на работния лист вместо резултатите от формули, като натиснете Ctrl+' (апостроф) или щракнете върху бутона Покажи формули в раздела Формули. (Може да се наложи първо да щракнете върху бутона Formula Auditing, в зависимост от размера на вашия екран.) Щракнете върху бутона Покажи формули втори път, за да видите резултатите от формулата отново.
Позоваването на резултатите от формулите във формулите на Excel 2019
Освен да се позовавате на клетки с числа в тях, можете да се обърнете към резултатите от формула в клетка. Помислете за работния лист по-долу. Целта на този работен лист е да проследи резултата от играчите от баскетболен отбор в три мача:
- Колоната Totals (колона E) показва общия брой точки, отбелязани от всеки играч в трите игри.
- Колоната "Средно" (колона F), използвайки резултатите от формулата в колоната "Общо", определя колко средно е отбелязал всеки играч. Колоната Average прави това, като раздели резултатите в колона E на 3, броя на изиграните игри.
Използването на резултатите от формулата на Excel като други формули.
В този случай Excel използва резултатите от формулите за общо изчисление в колона E, за да изчисли средните точки на игра в колона F.
Оператори във формулите на Excel 2019
Събиране, изваждане и деление не са единствените оператори, които можете да използвате във формули. Таблицата по-долу обяснява аритметичните оператори, които можете да използвате, и клавиша, който натискате, за да въведете всеки оператор. В таблицата операторите са изброени в реда на приоритет.
Аритметични оператори за използване във формули
Предимство |
Оператор |
Примерна формула |
Се завръща |
1 |
% (Процент) |
=50% |
50 процента или 0,5 |
2 |
^ (Показване в степен) |
=50^2 |
50 на втора степен или 2500 |
3 |
* (Умножение) |
=E2*4 |
Стойността в клетка E2, умножена по 4 |
3 |
/ (Отдел) |
=E2/3 |
Стойността в клетка E2 разделена на 3 |
4 |
+ (Допълнение) |
=F1+F2+F3, |
Сумата от стойностите в тези клетки |
4 |
– (Изваждане) |
=G5–8, |
Стойността в клетка G5 минус 8 |
5 |
& (Конкатенация) |
=″Част номер ″&D4 |
Текстът Part No. и стойността в клетка D4 |
6 |
= (равно на) |
=C5=4, |
Ако стойността в клетка C5 е равна на 4, връща TRUE; връща FALSE в противен случай |
6 |
<> (Не е равно на) |
=F3<>9 |
Ако стойността в клетка F3 не е равна на 9, връща TRUE; връща FALSE в противен случай |
6 |
< (по-малко = ""> |
=B9<>< code=""><> |
Ако стойността в клетка B9 е по-малка от стойността в клетка E11, връща TRUE; връща FALSE в противен случай |
6 |
<= (по-малко = "" отколкото = "" или = "" равно = ""> |
=A4<> |
Ако стойността в клетка A4 е по-малка или равна на 9, връща TRUE; връща FALSE в противен случай |
6 |
> (По-голямо от) |
=E8>14 |
Ако стойността в клетка E8 е по-голяма от 14, връща TRUE; връща FALSE в противен случай |
6 |
>= (По-голямо или равно на) |
=C3>=D3 |
Ако стойността в клетка C3 е по-голяма или равна на стойността в клетка D3, връща TRUE; връща FALSE в противен случай |
Друг начин за изчисляване на формула е да се използва функция. Функцията е вградена формула, която идва с Excel. SUM, например, събира числата в клетки. AVG намира средната стойност на различни числа.
Редът на приоритет за формулите на Excel
Когато една формула включва повече от един оператор, редът, в който операторите се появяват във формулата, има голямо значение. Помислете за тази формула:
=2+3*4
Тази формула води ли до 14 (2+[3*4]) или 20 ([2+3]*4)? Отговорът е 14, защото Excel 2019 извършва умножение преди събиране във формули. С други думи, умножението има предимство пред събирането.
Редът, в който се правят изчисления във формула, която включва различни оператори, се нарича ред на приоритет. Не забравяйте да запомните реда на приоритет, когато конструирате сложни формули с повече от един оператор:
Процент (%)
Експоненция (^)
Умножение (*) и деление (/); най-левите операции се изчисляват първо
Събиране (+) и изваждане (-); най-левите операции се изчисляват първо
Конкатенация (&)
Сравнение (<,> <=,>,>= и <>)
За да заобиколите проблема с реда на приоритета, оградете части от формули в скоби. Операциите в скоби се изчисляват преди всички останали части на формула. Например, формулата =2+3*4 е равна на 20, когато е написана по следния начин: =(2+3)*4.