Popis 10 najboljih naprednih Excel formula i funkcija
# 1 - VLOOKUP formula u Excelu
Ova napredna excel funkcija jedna je od najčešće korištenih formula u excelu. To je uglavnom zbog jednostavnosti ove formule i njezine primjene u traženju određene vrijednosti iz drugih tablica, koja ima jednu zajedničku varijablu u tim tablicama. Pretpostavimo da imate dvije tablice s detaljima o plaći i imenu zaposlenika u tvrtki, a primarni stupac je ID zaposlenika. Želite primati plaću iz tablice B u tablici A.
VLOOKUP možete koristiti kao dolje.
Rezultat će biti u donjoj tablici kada primijenimo ovu naprednu Excel formulu u drugim ćelijama stupca Plaća zaposlenika.
Povucite Formulu do ostatka stanica.
Tri su glavna ograničenja VLOOKUP-a:
- Ne možete imati primarni stupac s desne strane stupca za koji želite popuniti vrijednost iz druge tablice. U ovom slučaju stupac Plaća zaposlenika ne može biti ispred ID-a zaposlenika.
- U dupliciranim vrijednostima u primarnom stupcu u tablici B, prva će se vrijednost popuniti u ćeliji.
- Ako u bazu podataka umetnete novi stupac (npr. Umetnite novi stupac prije plaće zaposlenika u tablici B), izlaz formule može se razlikovati na temelju položaja koji ste naveli u formuli (u gornjem slučaju rezultat bi bilo prazno)
# 2 - INDEX formula u Excelu
Ova napredna excel formula koristi se za dobivanje vrijednosti ćelije u datoj tablici određivanjem broja redaka, stupaca ili oboje. Pr. Da biste dobili ime zaposlenika na 5 -og promatranja, Ispod je podataka.
Naprednu Excel formulu možemo koristiti kao u nastavku:
Ista INDEX formula može se koristiti za dobivanje vrijednosti duž reda. Kada koristite broj retka i stupca, sintaksa bi izgledala otprilike ovako:
Gornja formula vratila bi "Rajesh Ved."
Napomena: Ako ste umetnuli novi redak u 5 -og reda, formula će se vratiti „Chandan Kale”. Stoga bi izlaz ovisio o bilo kakvim promjenama koje se događaju u tablici podataka tijekom vremena.
# 3 - Formula za podudaranje u Excelu
Ova napredna formula u Excelu vraća broj retka ili stupca kada se u zadanom rasponu nalazi određeni niz ili broj. U donjem primjeru pokušavamo pronaći “Rajesh Ved” u stupcu Ime zaposlenika.
Formula bi bila sljedeća:
Funkcija MATCH vratila bi 5 kao vrijednost.
3 rd argument koristi se za točno podudaranje. Također možete koristiti +1 i -1 na temelju svojih zahtjeva.
Napomena: Može se kombinirati INDEX i MATCH kako bi se prevladalo ograničenje VLOOKUP-a.
# 4 - AKO I Formula u Excelu
Mnogo je slučajeva kada treba stvoriti zastave na temelju nekih ograničenja. Svi smo upoznati s osnovnom sintaksom IF-a. Koristimo ovu naprednu excel IF funkciju za stvaranje novog polja na temelju nekih ograničenja na postojećem polju. Ali što ako trebamo uzeti u obzir više stupaca prilikom stvaranja zastave. Pr. U donjem slučaju želimo označiti sve zaposlenike čija je plaća veća od 50 tisuća, ali ID zaposlenika veći od 3.
U takvim bismo slučajevima koristili IF I. U nastavku pronađite snimak zaslona za isti.
Rezultat bi se vratio kao 0.
Možemo imati mnogo uvjeta ili ograničenja za stvaranje zastave na temelju više stupaca pomoću I.
# 5 - AKO ILI Formula u Excelu
Slično tome, također možemo koristiti funkciju OR u excelu umjesto AND ako trebamo zadovoljiti jedan od mnogih uvjeta.
U gornjim slučajevima, ako se ispuni bilo koji od uvjeta, imat ćemo stanicu napunjenu kao još 1 0. Također možemo zamijeniti 1 ili 0 nekim podnizima s dvostrukim navodnicima (“”).
# 6 - SUMIF formula u Excelu
U nekim analizama možda ćete trebati filtrirati neka zapažanja prilikom primjene funkcije zbroja ili brojanja. U takvim slučajevima spašavamo ovu naprednu excel SUMIF funkciju u excelu. Filtrira sva zapažanja na temelju određenih uvjeta danih u ovoj naprednoj Excel formuli i sažima ih. Pr. Što ako želimo znati zbroj plaća samo onih zaposlenika koji imaju ID zaposlenika veći od 3.
Primjenom SUMIFS formule:
Formula daje rezultate kao 322000.
Također možemo računati broj zaposlenika u organizaciji koji imaju ID zaposlenika veći od 3 kada upotrebljavamo COUNTIF umjesto SUMIF.
# 7 - CONCATENATE Formula u Excelu
Ova napredna funkcija excel jedna je od formula koja se može koristiti s više varijanti. Ova napredna excel formula pomaže nam da spojimo nekoliko tekstualnih nizova u jedan tekstni niz. Primjerice, ako želimo prikazati ID zaposlenika i ime zaposlenika u jednom stupcu.
Za to možemo koristiti ovu formulu CONCATENATE.
Gornja formula rezultirat će "1Aman Gupta".
Možemo imati još jednu varijantu stavljanjem jedne crtice između ID-a i NAME-a. Pr. CONCATENATE (B3, ”-“, C3) rezultirat će „1-Aman Gupta“. To također možemo koristiti u VLOOKUP-u kada je LOOKUP u excel vrijednosti mješavina više od jedne varijable.
# 8 - LIJEVA, SREDINA i DESNA formula u Excelu
Ovu naprednu Excel formulu možemo koristiti za izdvajanje određenog niza iz zadanog niza. Mogao bi se koristiti na temelju naših zahtjeva. Pr. Ako iz imena zaposlenika želimo izdvojiti prvih 5 znakova, možemo upotrijebiti LIJEVU formulu u excelu s nazivom stupca i drugim parametrom kao 5.
Izlaz je dan u nastavku:
Primjena RIGHT formule u excelu je također ista. Samo bismo lik gledali s desne strane niza. Međutim, u slučaju MID funkcije u Excelu, moramo dati početni položaj potrebnog tekstualnog niza i duljinu niza.
# 9 - OFFSET formula u Excelu
Ova napredna excel funkcija s kombinacijom drugih funkcija kao što su ZBIR ili PROSJEČNA, mogla bi dati dinamičan dodir izračunima. Najbolje se koristi u slučajevima kada ubacujemo kontinuirane retke u postojeću bazu podataka. OFFSET Excel pruža nam raspon u kojem moramo spomenuti referentnu ćeliju, broj redaka i stupaca. Pr. Ako želimo izračunati prosjek prvih 5 zaposlenih u tvrtki u kojoj imamo plaću zaposlenika poredanu prema ID-u zaposlenika, možemo učiniti sljedeće. Izračun u nastavku uvijek će nam dati plaće.
- Dat će nam zbroj plaća prvih 5 zaposlenika.
# 10 - TRIM formula u Excelu
Ova napredna excel formula koristi se za čišćenje nevažnih razmaka od teksta. Pr. Ako želimo ukloniti razmake na početku nekog imena, možemo ga koristiti upotrebom funkcije TRIM u excelu kao što je dolje prikazano:
Rezultat bi bio "Chandan Kale" bez ikakvog prostora prije Chandana.
