Excel Advanced Formulas (Sadržaj)
- Uvod u napredne formule u Excelu
- Primjeri napredne formule u Excelu
Uvod u napredne formule u Excelu
Nakon što dosegnete srednju razinu u excelu, trebate naporno raditi kako biste napredovali na naprednoj razini. Da biste napredovali na naprednoj razini, morate biti svjesni nekih često korištenih naprednih formula. U ovom ću članku pokriti 10 najboljih naprednih formula koje svi učenici koji izvrsno moraju znati. Slijedite ovaj članak da biste naučili i istraživali.
Primjeri napredne formule u Excelu
Razjasnimo kako koristiti napredne formule u Excelu s nekim primjerima.
Ovdje možete preuzeti ovaj napredni obrazac Excel predložak - Napredni obrazac Excel predložakPrimjer br. 1 - Djelomična funkcija VLOOKUP
Sigurno ste naišli na situaciju kada VLOOKUP izbacuje pogrešku čak i ako je došlo do neznatnog promašaja u vrijednosti pretraživanja. Na primjer, ako tražite plaću za ime Abhishek Sinha i ako imate samo Abhishek, onda VLOOKUP ne može donijeti podatke.
Međutim, koristeći znak zvjezdice na bilo kojem kraju vrijednosti pretraživanja, možemo doći do podataka za djelomičnu vrijednost pretraživanja.
U tablici 1 imam puno ime i plaću, ali u tablici 2 imam samo djelomična imena i moram pronaći plaću svakog zaposlenika.
1. korak: Otvorite formulu VLOOKUP u ćeliji E3. Prije odabira vrijednosti pretraživanja, na obje strane vrijednosti pretraživanja stavite zvjezdicu (*).
Korak 2: Kao i obično, sada možete dovršiti VLOOKUP formulu i imat ćemo rezultate.
Nakon korištenja gornje formule, rezultat je prikazan dolje.
Ista se formula koristi u ostalim stanicama.
Primjer br. 2 - COUNTIFS sa simbolima operatora
Za računanje stvari na popisu sigurno ste koristili funkciju COUNTIF i IFS. Također možemo računati na temelju simbola operatera poput većeg od (>) manje od (<) i znaka jednake (=).
Sada pogledajte na primjer podatke u nastavku. Ako želite računati ukupan broj faktura za regiju JUŽNI nakon datuma 10. siječnja 2018., kako računate?
Primjer br. 3 - AKO je uvjet s AND & OR uvjetima
Logičke funkcije dio su naših svakodnevnih aktivnosti. Morate ih savladati za napredak na sljedeću razinu. Ako izračunavate bonus na temelju više uvjeta, morate uložiti AND ili ILI uvjet s IF uvjetom da biste obavili posao.
Pretpostavimo da morate izračunati iznos bonusa za svaki odjel na temelju odjela i godina radnog staža koje su vam potrebne ove funkcije. Na temelju nižih kriterija moramo izračunati bonus.
Ako je usluga veća od 4 godine, a odjel je ili Prodaja ili Podrška, bonus je 50000 ili je bonus 25000.
Primjenite dolje formulu kako biste dobili bonus iznos.
Nakon korištenja gornje formule, rezultat se pokazuje u nastavku.
Ista formula koja se primjenjuje u ćeliji E3 do E9.
Primjer br. 4 - TEXT funkcija radi postizanja dinamike zaglavlja
Recimo da održavate dnevnu prodajnu tablicu i morate je svakodnevno ažurirati. Na početku tablice imate jedan naslov koji kaže "Konsolidirani podaci o prodaji od DD-MM-GGGG do DD-MM-GGGG". Kao i kada se ažurira tablica, morate promijeniti datum naslova. Nije li frustrirajući zadatak isto i ponovno raditi isto? Ovaj zaglavlje možemo učiniti dinamičkim korištenjem funkcije TETX, MIN i Max zajedno s objedinjenim simbolom operatora ampersand (&).
Primjer br. 5 - INDEX + UTAKMICA + MAX za pronalazak najviše prodajne osobe
Imate popis prodajnih osoba i prodaja koje su napravili u ime. Kako reći tko je najbolji ili najviši prodavač na popisu? Naravno, imamo nekoliko drugih tehnika da kažemo rezultat, ali ova funkcija može vratiti najvišeg prodavača iz serije.
Primijenite donju funkciju za dobivanje najvišeg imena prodavača.
Nakon korištenja gornje formule, rezultat se pokazuje u nastavku.
Primjer # 6 - Na popisu dobijete broj jedinstvenih vrijednosti
Ako imate više duplikata i trebate reći koliko jedinstvenih vrijednosti postoji u tome kako reći? Možete to otkriti uklanjanjem duplikata vrijednosti, ali to nije dinamičan način kazivanja broja jedinstvenih vrijednosti.
Pomoću ove funkcije polja možemo odrediti jedinstvene vrijednosti iz serije.
Primijenite dolje formulu kako biste dobili jedinstveni popis vrijednosti.
Primjer br. 7 - Upotrijebite imenovani raspon kako biste učinili padajući izbor dinamičkim
Ako često radite sa padajućim popisom i ažurirate padajući popis potrebno je da se vratite na raspon popisa izvora da biste izbrisali ili dodali vrijednosti. Nakon toga, morate se vratiti padajućim ćelijama i ponovo ažurirati raspon padajućeg popisa.
Međutim, ako možete stvoriti imenovani raspon za padajući popis, možete učiniti padajući popis dinamičnim i ažuriranim.
Napravite raspon imena kao što je prikazano na slici ispod.
Sada idite na padajuću ćeliju i otvorite padajući dijaloški okvir.
U izvoru pritisnite tipku F3, prikazat će sva definirana imena, odaberite ime vašeg padajućeg popisa.
Ok, padajući popis je spreman i automatski će ažurirati vrijednosti kada dođe do promjene raspona padajućeg popisa.
Primjer br. 8 - Oslobodite se vrijednosti pogrešaka pomoću funkcije IFERROR
Siguran sam da ste iskusili vrijednosti pogrešaka tijekom rada s izračunima VLOOKUP-a, odjeljenja. Rukovanje ovim vrijednostima pogrešaka je mučan zadatak. Ali ove vrijednosti pogrešaka možemo riješiti pomoću funkcije IFERROR u formuli.
Nakon korištenja gornje formule, rezultat se pokazuje u nastavku.
Ista formula koja se koristi u ostalim stanicama.
Primjer br. 9 - Pomoću funkcije PMT napravite vlastiti EMI grafikon
Danas EMI opcija nije čudna stvar za sve nas. U excelu možemo procijeniti vlastiti EMI grafikon pomoću PMT funkcije. Slijedite korake u nastavku kako biste stvorili vlastiti grafikon.
Primijenite donju formulu u ćeliji B4 da biste dobili količinu EMI.
Primjer # 10 - INDEX + UTAKMICA kao alternativa VLOOKUP funkciji
Nadam se da ste svjesni ograničenja funkcije VLOOKUP. Jedno od glavnih ograničenja je VLOOKUP da dohvaća podatke s lijeva na desno, a ne s desna na lijevo. Nisu svi podaci dobro organizirani i spremni za upotrebu. Stupac vrijednosti koji tražimo često je s lijeve strane vrijednosti pretraživanja, tako da VLOOKUP u tim slučajevima ne pomaže.
Kombinacija INDEX + MATCH funkcije služi kao alternativa VLOOKUP funkciji.
Nakon korištenja gornje formule, rezultat je prikazan dolje.
Ista se formula koristi u ostalim stanicama.
Na temelju ID-a proizvoda, moramo izdvojiti prodajne vrijednosti. U glavnoj tablici ID proizvoda se nalazi na desnoj strani stupca prodaje. Dakle, VLOOKUP ne može dohvatiti podatke. Za dobivanje podataka koristite donju formulu.
Preporučeni članci
Ovo je vodič za napredne formule u Excelu. Ovdje smo raspravljali o tome kako koristiti napredne formule u Excelu zajedno s praktičnim primjerima i besplatnim Excel predložakom. Možete i pregledati naše druge predložene članke -
- Kako se koristi Excel INDEX funkcija?
- Napredni Excel | Funkcija baze podataka
- Primjeri TRIM Formule u Excelu
- Vodič za Excel OFFSET formulu