Екцел

Екцел формуле и функције

Excel Formulas Functions

Формуле и функције су крух у Екцелу. Они покрећу скоро све занимљиво и корисно што ћете икада учинити у табели. Овај чланак представља основне концепте које морате знати да бисте знали формуле у Екцелу. Више примера овде .





Шта је формула?

Формула у Екцелу је израз који враћа одређени резултат. На пример:

 
=1+2 // returns 3

Пример основне формуле - 1 + 3 = 3





 
=6/3 // returns 2

Пример основне формуле - 6 /3 = 2

Напомена: све формуле у Екцелу морају почети знаком једнакости (=).



Референце ћелија

У горњим примерима, вредности су „тврдо кодиране“. То значи да се резултати неће променити ако поново не измените формулу и не промените вредност ручно. Генерално, ово се сматра лошим обликом јер скрива информације и отежава одржавање табеле.

Уместо тога, користите референце ћелија тако да се вредности могу променити у било ком тренутку. На доњем екрану Ц1 садржи следећу формулу:

 
=A1+A2+A3 // returns 9

Формула са референцама ћелија

Обратите пажњу на то да за А1, А2 и А3 користимо референце ћелија, ове вредности се могу променити у било ком тренутку и Ц1 ће и даље приказивати тачан резултат.

Све формуле враћају резултат

Све формуле у Екцелу враћају резултат, чак и када је резултат грешка. Испод се користи формула за израчунавање процентуалне промене. Формула враћа тачан резултат у Д2 и Д3, али враћа #ДИВ/0! грешка у Д4, јер је Б4 празан:

Резултат формуле може бити грешка

Постоје различити начини решавања грешака. У овом случају, можете да наведете вредност која недостаје у Б4, или да „ухватите“ грешку помоћу ИФЕРРОР функција и приказати пријатељскију поруку (или уопште ништа).

Копирајте и залепите формуле

Лепота ћелијских референци је у томе што се оне аутоматски ажурирају када се формула копира на нову локацију. То значи да не морате да уносите исту основну формулу изнова и изнова. На доњем екрану формула у Е1 је копирана у међуспремник помоћу Цонтрол + Ц:

Формула у Е1 копирана у међуспремник

Испод: формула залепљена у ћелију Е2 са Цонтрол + В. Приметите да су се референце ћелија промениле:

Формула у Е1 залепљена у Е2

Иста формула залепљена у Е3. Адресе ћелија се поново ажурирају:

Формула у Е1 залепљена у Е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 на једном месту и позивањем на А1 са ан апсолутна референца , вредност се може променити у било ком тренутку и све повезане формуле ће се одмах ажурирати.

Савет: можете да се пребацујете између релативне и апсолутне синтаксе помоћу Ф4 тастер .

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

Да бисте унели формулу:

  1. Изаберите ћелију
  2. Унесите знак једнакости (=)
  3. Унесите формулу и притисните ентер.

Уместо да уносите ћелијске референце, можете да покажете и кликнете, као што је приказано испод. Напомене су означене бојом:

Унос формуле са референцама тачке и клика

Све формуле у Екцелу морају почети знаком једнакости (=). Нема знака једнакости, нема формуле:

Заборављено уношење знака једнакости значи да нема формуле, само текст

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

Да бисте уредили формулу, имате 3 могућности:

  1. Изаберите ћелију, уредите је у трака са формулама
  2. Двапут кликните на ћелију, измените директно
  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:

Потребна је формула за израчунавање тренутне старости у Б2

Тхе ИЕАРФРАЦ функција израчунаће године са датумом почетка и датумом завршетка:

ИЕАРФРАЦ ће израчунати године са датумом почетка и датумом завршетка

Можемо користити Б1 за датум почетка, а затим користити ТОДАИ функција да наведете датум завршетка:

Б1 за датум почетка, функција ТОДАИ за испоруку датума завршетка

Када притиснемо Ентер за потврду, добијамо тренутну старост на основу данашњег датума:

 
= YEARFRAC (B1, TODAY ())

ИЕАРФРАЦ и ТОДАИ функције за израчунавање тренутне старости

Приметите да користимо функцију ТОДАИ за унос датума завршетка у функцију ИЕАРФРАЦ. Другим речима, функција ТОДАИ може бити угнежђена унутар функције ИЕАРФРАЦ да би обезбедила аргумент датума завршетка. Формулу можемо учинити корак даље и користити ИНТ функција да бисте одсекли децималну вредност:

 
= INT ( YEARFRAC (B1, TODAY ()))

ГОДИНА и ДАНАС унутар ИНТ -а

Овде оригинална формула ИЕАРФРАЦ враћа 20,4 у функцију ИНТ, а функција ИНТ враћа коначни резултат од 20.

Напомене:

  1. Тренутни датум на горњим сликама је 22. фебруар 2019.
  2. Угнездио ИФ функције су класичан пример функције гнежђења .
  3. Тхе ТОДАИ функција је ретка Екцел функција без потребних аргумената.

Кључни закључак: Излаз било које формуле или функције може се унети директно у другу формулу или функцију.

Математички оператори

Доња табела приказује стандардне математичке операторе доступне у Екцелу:

Симбол Операција Пример
+ Додатак = 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

Видео: Како изградити логичке формуле

Редослед операција

Када решава формулу, Екцел следи редослед који се назива „редослед операција“. Прво се вреднују сви изрази у заградама. Следећи Екцел ће решити све експоненте. Након експонената, Екцел ће извршити множење и дељење, затим сабирање и одузимање. Ако формула укључује спајање , то ће се догодити након стандардних математичких операција. Коначно, Екцел ће проценити логички оператори , ако је присутан.

  1. Заграде
  2. Експоненти
  3. Множење и дељење
  4. Сабирање и одузимање
  5. Повезивање
  6. Логички оператори

Савет: можете користити Оцените функцију гледати Екцел како решава формуле корак по корак.

Претворите формуле у вредности

Понекад желите да се решите формула и оставите само вредности на њиховом месту. Најлакши начин да то учините у Екцелу је да копирате формулу, а затим је залепите користећи Специјално лепљење> Вредности. Ово преписује формуле вредностима које враћају. Можете користити а тастерска пречица за лепљење вредности или користите мени Залепи на картици Почетна на траци.

Видео: Залепите посебне пречице

како израчунати природни лог у екцелу

Шта је следеће?

Испод су водичи који ће вам помоћи да сазнате више о Екцеловим формулама и функцијама. Такође нудимо онлајн видео обука .

Аутор Даве Брунс


^