Напредне Екцел формуле | Листа 10 најбољих напредних Екцел функција
Листа 10 најбољих напредних Екцел формула и функција
Овај Напредни образац за Екцел формуле можете преузети овде - Напредни образац за Екцел формуле# 1 - ВЛООКУП формула у програму Екцел
Ова напредна екцел функција једна је од најчешће коришћених формула у екцелу. То је углавном због једноставности ове формуле и њене примене у тражењу одређене вредности из других табела која има једну заједничку променљиву у овим табелама. Претпоставимо да имате две табеле које садрже детаље о плати и имену запосленог у компанији, а примарна колона је ИД запосленог. Желите да примите плату из табеле Б у табели А.
Можете користити ВЛООКУП као доле.
Резултат ће бити табела у наставку када применимо ову напредну Екцел формулу у другим ћелијама колоне Плата запослених.
Превуците Формулу до остатка ћелија.
Три су главна ограничења ВЛООКУП-а:
- Не можете имати примарни ступац десно од колоне за који желите да попуните вредност из друге табеле. У овом случају ступац Плата запосленика не може бити испред ИД-а запосленог.
- У случају дуплираних вредности у примарном ступцу у табели Б, прва вредност ће се попунити у ћелији.
- Ако у базу података уметнете нову колону (Ек Уметните нову колону пре зараде запослених у табели Б), излаз формуле може бити другачији на основу положаја који сте навели у формули (у горњем случају, излаз би било празно)
# 2 - ИНДЕКС формула у Екцелу
Ова напредна Екцел формула се користи за добијање вредности ћелије у датој табели одређивањем броја редова, колона или оба. Пр. Да бисте добили име запосленог на 5. посматрању, испод су подаци.
Напредну Екцел формулу можемо користити као испод:
Иста ИНДЕКС формула се може користити за добијање вредности дуж реда. Када користите број реда и колоне, синтакса би изгледала отприлике овако:
Горња формула би вратила „Рајесх Вед“.
Белешка: Ако уметнете још један ред у податке у 5. реду, формула ће вратити „Цхандан Кале“. Стога би излаз зависио од било каквих промена у табели података током времена.
# 3 - Формула МАТЦХ у програму Екцел
Ова Екцел напредна формула враћа број реда или колоне када постоји подударање одређеног низа или броја у датом опсегу. У доњем примеру покушавамо да пронађемо положај „Рајесх Вед“ у колони Име запосленог.
Формула би била како је дато у наставку:
Функција МАТЦХ би вратила 5 као вредност.
Трећи аргумент се користи за тачно подударање. Такође можете да користите +1 и -1 на основу својих захтева.
Белешка: Може се комбиновати ИНДЕКС и МАТЦХ да би се превазишло ограничење ВЛООКУП-а.
# 4 - АКО И Формула у Екцелу
Постоји много случајева када треба створити заставице на основу неких ограничења. Сви смо упознати са основном синтаксом ИФ. Користимо ову напредну екцел ИФ функцију да бисмо креирали ново поље на основу неких ограничења на већ постојећем пољу. Али шта ако треба да узмемо у обзир више колона приликом стварања заставе. Пр. У доњем случају желимо да означимо све запослене чија је плата већа од 50.000, али ИД запосленог већа од 3.
У таквим случајевима бисмо користили ИФ И. Испод наведите снимак екрана за исти.
Вратило би резултат као 0.
Можемо имати много услова или ограничења за стварање заставе засноване на више колона помоћу АНД.
# 5 - АКО ИЛИ Формула у Екцелу
Слично томе, такође можемо да користимо функцију ОР у екцелу уместо И ако само треба да задовољимо један од услова многих.
У горњим случајевима, ако се испуни било који од услова, ћелија ће се попунити као 1 других 0. Такође можемо заменити 1 или 0 неким поднизима са двоструким наводницима („”).
# 6 - СУМИФ формула у програму Екцел
У неким анализама ће вам можда требати да филтрирате нека запажања приликом примене функције збира или бројања. У таквим случајевима, ова напредна екцел СУМИФ функција у екцелу је на спашавању. Филтрира сва запажања на основу одређених услова датих у овој напредној Екцел формули и сажима их. Пр. Шта ако желимо да знамо суму зарада само оних запослених који имају ИД запосленог већи од 3.
Применом СУМИФС формуле:
Формула даје резултате као 322000.
Такође можемо да избројимо број запослених у организацији који имају ИД радника већи од 3 када користимо ЦОУНТИФ уместо СУМИФ.
# 7 - ЦОНЦАТЕНАТЕ Формула у програму Екцел
Ова напредна функција екцел једна је од формула која се може користити са више варијанти. Ова напредна екцел формула помаже нам да спојимо неколико текстуалних низова у један текстуални низ. Пример, ако желимо да у једној колони прикажемо ИД запосленог и име запосленог.
Ову формулу ЦОНЦАТЕНАТЕ можемо користити само да бисмо то урадили.
Горња формула ће резултирати „1Аман Гупта“.
Можемо имати још једну варијанту стављањем једне цртице између ИД и НАМЕ. Пр. ЦОНЦАТЕНАТЕ (Б3, ”-“, Ц3) резултираће „1-Аман Гупта“. Ово такође можемо користити у ВЛООКУП-у када је ЛООКУП у екцел вредности мешавина више од једне променљиве.
# 8 - ЛЕВА, СРЕДИНА и ДЕСНА формула у Екцелу
Можемо користити ову напредну Екцел формулу ако желимо да издвојимо одређени подниз из датог низа. Поменуте формуле могу се користити на основу наших захтева. Пр. Ако желимо да издвојимо првих 5 знакова из Имена запосленог, можемо користити ЛЕВУ формулу у екцелу са именом колоне и другим параметром као 5.
Излаз је дат у наставку:
Примена ДЕСНЕ формуле у екцелу је такође иста, само бисмо гледали лик са десне стране низа. Међутим, у случају МИД функције у екцелу, морамо дати почетни положај потребног текстуалног низа и дужину низа.
# 9 - ОФФСЕТ формула у Екцелу
Ова напредна екцел функција са комбинацијом других функција као што су СУМ или ПРОСЕЧНА може бити корисна у давању динамичког додира прорачунима. Најбоље се користи у случајевима када убацујемо непрекидне редове у постојећу базу података. ОФФСЕТ Екцел нам даје опсег у којем морамо споменути референтну ћелију, број редова и колона. Пр. Ако желимо да израчунамо просек првих 5 запослених у компанији у којој имамо плату запослених сортирану по ИД-у запосленог, можемо урадити следеће. Израчун у наставку увек ће нам дати плату.
- Даће нам збир плата првих 5 запослених.
# 10 - ТРИМ формула у Екцел-у
Ова напредна екцел формула користи се за чишћење неважних размака од текста. Пр. Ако желимо да уклонимо размаке на почетку неког имена, можемо га користити употребом функције ТРИМ у екцелу као доле:
Резултат би био "Цхандан Кале" без икаквог простора испред Цхандана.
