Формуле и функције су крух у Екцелу. Они покрећу скоро све занимљиво и корисно што ћете икада учинити у табели. Овај чланак представља основне концепте које морате знати да бисте знали формуле у Екцелу. Више примера овде .
Шта је формула?
Формула у Екцелу је израз који враћа одређени резултат. На пример:
=1+2 // returns 3
=6/3 // returns 2
Напомена: све формуле у Екцелу морају почети знаком једнакости (=).
Референце ћелија
У горњим примерима, вредности су „тврдо кодиране“. То значи да се резултати неће променити ако поново не измените формулу и не промените вредност ручно. Генерално, ово се сматра лошим обликом јер скрива информације и отежава одржавање табеле.
Уместо тога, користите референце ћелија тако да се вредности могу променити у било ком тренутку. На доњем екрану Ц1 садржи следећу формулу:
=A1+A2+A3 // returns 9
Обратите пажњу на то да за А1, А2 и А3 користимо референце ћелија, ове вредности се могу променити у било ком тренутку и Ц1 ће и даље приказивати тачан резултат.
Све формуле враћају резултат
Све формуле у Екцелу враћају резултат, чак и када је резултат грешка. Испод се користи формула за израчунавање процентуалне промене. Формула враћа тачан резултат у Д2 и Д3, али враћа #ДИВ/0! грешка у Д4, јер је Б4 празан:
Постоје различити начини решавања грешака. У овом случају, можете да наведете вредност која недостаје у Б4, или да „ухватите“ грешку помоћу ИФЕРРОР функција и приказати пријатељскију поруку (или уопште ништа).
Копирајте и залепите формуле
Лепота ћелијских референци је у томе што се оне аутоматски ажурирају када се формула копира на нову локацију. То значи да не морате да уносите исту основну формулу изнова и изнова. На доњем екрану формула у Е1 је копирана у међуспремник помоћу Цонтрол + Ц:
Испод: формула залепљена у ћелију Е2 са Цонтрол + В. Приметите да су се референце ћелија промениле:
Иста формула залепљена у Е3. Адресе ћелија се поново ажурирају:
Релативне и апсолутне референце
Горе наведене референце ћелија се зову у односу референце. То значи да се референца односи на ћелију у којој живи. Горња формула у Е1 је:
=B1+C1+D1 // formula in E1
Буквално, ово значи „ћелија 3 колоне лево“ + „ћелија 2 колоне лево“ + „ћелија 1 колона лево“. Зато, када се формула копира доле у ћелију Е2, она наставља да ради на исти начин.
Релативне референце су изузетно корисне, али постоје случајеви када не желите да се референца ћелије промени. Референца ћелије која се неће променити при копирању назива се апсолутна референца . Да бисте референцу учинили апсолутном, користите симбол долара ($):
=A1 // relative reference =$A // absolute reference
На пример, на доњем екрану желимо да помножимо сваку вредност у колони Д са 10, која је унета у А1. Користећи апсолутну референцу за А1, 'закључавамо' ту референцу тако да се неће променити када се формула копира у Е2 и Е3:
Ево коначних формула у Е1, Е2 и Е3:
=D1*$A // formula in E1 =D2*$A // formula in E2 =D3*$A // formula in E3
Обратите пажњу на позивање на ажурирање Д1 када се формула копира, али референца на А1 се никада не мења. Сада можемо лако променити вредност у А1, а све три формуле се поново израчунавају. Испод се вредност у А1 променила са 10 на 12:
Овај једноставан пример такође показује зашто нема смисла тврдо кодирати вредности у формулу. Чувањем вредности у А1 на једном месту и позивањем на А1 са ан апсолутна референца , вредност се може променити у било ком тренутку и све повезане формуле ће се одмах ажурирати.
Савет: можете да се пребацујете између релативне и апсолутне синтаксе помоћу Ф4 тастер .
Како унети формулу
Да бисте унели формулу:
- Изаберите ћелију
- Унесите знак једнакости (=)
- Унесите формулу и притисните ентер.
Уместо да уносите ћелијске референце, можете да покажете и кликнете, као што је приказано испод. Напомене су означене бојом:
Све формуле у Екцелу морају почети знаком једнакости (=). Нема знака једнакости, нема формуле:
Како променити формулу
Да бисте уредили формулу, имате 3 могућности:
- Изаберите ћелију, уредите је у трака са формулама
- Двапут кликните на ћелију, измените директно
- Изаберите ћелију, притисните Ф2 , уређујте директно
Без обзира коју опцију користите, притисните Ентер да бисте потврдили промене када завршите. Ако желите да откажете и оставите формулу непромењеном, кликните тастер Есцапе.
Видео: 20 савета за унос формула
Шта је функција?
Радећи у Екцелу, чут ћете речи „формула“ и „функција“ које се често користе, понекад наизменично. Они су блиско повезани, али нису потпуно исти. Технички, формула је било који израз који почиње знаком једнакости (=).
С друге стране, функција је формула са посебним именом и наменом. У већини случајева функције имају називе који одражавају њихову намену. На пример, вероватно знате СУМ функција већ враћа збир датих референци:
= SUM (1,2,3) // returns 6 = SUM (A1:A3) // returns A1+A2+A3
Тхе АВЕРАГЕ функција , као што бисте очекивали, враћа просек датих референци:
= AVERAGE (1,2,3) // returns 2
Функције МИН и МАКС враћају минималне и максималне вредности:
= MIN (1,2,3) // returns 1 = MAX (1,2,3) // returns 3
Екцел садржи стотине посебних функција . За почетак погледајте 101 Кључне Екцел функције .
Аргументи функција
Већина функција захтева уносе за враћање резултата. Ови улази се називају „аргументи“. Аргументи функције појављују се иза назива функције, унутар заграда, одвојени зарезима. Све функције захтевају одговарајуће заграде за отварање и затварање (). Образац изгледа овако:
=FUNCTIONNAME(argument1,argument2,argument3)
На пример, ЦОУНТИФ функција броји ћелије које испуњавају критеријуме и узима два аргумента, домет и критеријума :
= COUNTIF (range,criteria) // two arguments
На доњем екрану распон је А1: А5, а критеријум је „црвен“. Формула у Ц1 је:
= COUNTIF (A1:A5,'red') // returns 2
Видео: Како се користи функција ЦОУНТИФ
Нису сви аргументи потребни. Аргументи приказани у угластим заградама нису обавезни. На пример, ИЕАРФРАЦ функција враћа разломљени број година између датума почетка и датума завршетка и узима 3 аргумента:
= YEARFRAC (start_date,end_date,[basis])
Датум почетка и датум завршетка су обавезни аргументи, основа је изборни аргумент. У наставку погледајте пример како користити ИЕАРФРАЦ за израчунавање тренутне старости на основу датума рођења.
Како ући у функцију
Ако знате назив функције, само почните да куцате. Ево корака:
1. Унесите знак једнакости (=) и почните да куцате. Екцел ће приказати одговарајуће функције на основу док куцате:
Када видите жељену функцију на листи, помоћу тастера са стрелицама изаберите (или само наставите да куцате).
2. Унесите тастер Таб да бисте прихватили функцију. Екцел ће довршити функцију:
3. Попуните потребне аргументе:
4. Притисните Ентер да бисте потврдили формулу:
Комбиновање функција (угнежђење)
Многе Екцел формуле користе више функција, а функције могу бити ' угнездио 'једно у другом. На пример, испод имамо датум рођења у Б1 и желимо да израчунамо тренутну старост у Б2:
Тхе ИЕАРФРАЦ функција израчунаће године са датумом почетка и датумом завршетка:
Можемо користити Б1 за датум почетка, а затим користити ТОДАИ функција да наведете датум завршетка:
Када притиснемо Ентер за потврду, добијамо тренутну старост на основу данашњег датума:
= YEARFRAC (B1, TODAY ())
Приметите да користимо функцију ТОДАИ за унос датума завршетка у функцију ИЕАРФРАЦ. Другим речима, функција ТОДАИ може бити угнежђена унутар функције ИЕАРФРАЦ да би обезбедила аргумент датума завршетка. Формулу можемо учинити корак даље и користити ИНТ функција да бисте одсекли децималну вредност:
= INT ( YEARFRAC (B1, TODAY ()))
Овде оригинална формула ИЕАРФРАЦ враћа 20,4 у функцију ИНТ, а функција ИНТ враћа коначни резултат од 20.
Напомене:
- Тренутни датум на горњим сликама је 22. фебруар 2019.
- Угнездио ИФ функције су класичан пример функције гнежђења .
- Тхе ТОДАИ функција је ретка Екцел функција без потребних аргумената.
Кључни закључак: Излаз било које формуле или функције може се унети директно у другу формулу или функцију.
Математички оператори
Доња табела приказује стандардне математичке операторе доступне у Екцелу:
Симбол | Операција | Пример |
---|---|---|
+ | Додатак | = 2 + 3 = 5 |
- | Одузимање | = 9-2 = 7 |
* | Множење | = 6 * 7 = 42 |
/ | Дивизија | = 9/3 = 3 |
^ | Експоненција | = 4 ^ 2 = 16 |
() | Заграде | = (2 + 4) / 3 = 2 |
Логички оператори
Логички оператори пружају подршку за поређења као што су „веће од“, „мање од“ итд. Логички оператори доступни у Екцелу приказани су у доњој табели:
Оператер | Значење | Пример |
---|---|---|
= | Једнако | = А1 = 10 |
Неједнако са | = А110 | |
> | Веће од | = А1> 100 |
< | Мање од | = А1<100 |
> = | Већи или једнак | = А1> = 75 |
<= | Мање или једнако | = А1<=0 |
Видео: Како изградити логичке формуле
Редослед операција
Када решава формулу, Екцел следи редослед који се назива „редослед операција“. Прво се вреднују сви изрази у заградама. Следећи Екцел ће решити све експоненте. Након експонената, Екцел ће извршити множење и дељење, затим сабирање и одузимање. Ако формула укључује спајање , то ће се догодити након стандардних математичких операција. Коначно, Екцел ће проценити логички оператори , ако је присутан.
- Заграде
- Експоненти
- Множење и дељење
- Сабирање и одузимање
- Повезивање
- Логички оператори
Савет: можете користити Оцените функцију гледати Екцел како решава формуле корак по корак.
Претворите формуле у вредности
Понекад желите да се решите формула и оставите само вредности на њиховом месту. Најлакши начин да то учините у Екцелу је да копирате формулу, а затим је залепите користећи Специјално лепљење> Вредности. Ово преписује формуле вредностима које враћају. Можете користити а тастерска пречица за лепљење вредности или користите мени Залепи на картици Почетна на траци.
Видео: Залепите посебне пречице
како израчунати природни лог у екцелу
Шта је следеће?
Испод су водичи који ће вам помоћи да сазнате више о Екцеловим формулама и функцијама. Такође нудимо онлајн видео обука .
- 29 савета за рад са формулама и функције ( видео верзија овде )
- 500 примера формула са потпуним објашњењима
- 101 важна Екцел функција
- Водич кроз све функције програма Екцел (рад у току)
- Грешке у формули Екцел (примери и исправке)
- Критеријуми формуле - 50 примера
- Формуле за условно обликовање
- Како користити Ф9 за отклањање грешака у формули (видео)
- Грешке и поправке у формули Екцел (видео)