Seznam 10 najboljših naprednih Excelovih formul in funkcij
# 1 - Formula VLOOKUP v Excelu
Ta napredna funkcija excel je ena najpogosteje uporabljenih formul v excelu. To je predvsem posledica preprostosti te formule in njene uporabe pri iskanju določene vrednosti iz drugih tabel, ki ima v teh tabelah eno skupno spremenljivko. Recimo, da imate dve tabeli s podrobnostmi o plači in imenu zaposlenega v podjetju, pri čemer je glavni stolpec ID zaposlenega. Plačo želite dobiti iz tabele B v tabeli A.
VLOOKUP lahko uporabite kot spodaj.
Rezultat bo spodnja tabela, ko bomo to napredno formulo excel uporabili v drugih celicah stolpca Plača zaposlenih.
Povlecite formulo do preostalih celic.
Obstajajo tri glavne razmejitve VLOOKUP:
- Na desni strani stolpca ne morete imeti primarnega stolpca, za katerega želite izpolniti vrednost iz druge tabele. V tem primeru stolpec Plača zaposlenega ne sme biti pred ID-jem zaposlenega.
- V podvojene vrednosti v primarnem stolpcu v tabeli B se prva vrednost vnese v celico.
- Če v bazo podatkov vstavite nov stolpec (Ex Vstavi nov stolpec pred plačo zaposlenega v tabeli B), je lahko rezultat formule drugačen glede na položaj, ki ste ga omenili v formuli (v zgornjem primeru rezultat bi bilo prazno)
# 2 - Formula INDEX v Excelu
Ta napredna formula excel se uporablja za pridobitev vrednosti celice v dani tabeli z določitvijo števila vrstic, stolpcev ali obojega. Napr. Da bi dobili ime zaposlenega na 5 th opazovanja, Spodaj so podatki.
Lahko uporabimo napredno formulo excel, kot je prikazano spodaj:
Enako formulo INDEX lahko uporabimo za pridobivanje vrednosti vzdolž vrstice. Pri uporabi številke vrstic in stolpcev bi bila sintaksa videti nekako tako:
Zgornja formula bi vrnila "Rajesh Ved."
Opomba: Če vstavite drugo vrstico v 5 th vrstico, se bo formula vrne "Chandan Kale." Rezultat bi bil torej odvisen od sprememb, ki se sčasoma zgodijo v podatkovni tabeli.
# 3 - Formula ujemanja v Excelu
Ta napredna formula Excel vrne številko vrstice ali stolpca, če je v danem obsegu določen niz ali številka. V spodnjem primeru poskušamo najti »Rajesh Ved« v stolpcu Ime zaposlenega.
Formula bi bila naslednja:
Funkcija MATCH bi vrnila 5 kot vrednost.
3 rd argument se uporablja za natančno ujemanje. Glede na vaše zahteve lahko uporabite tudi +1 in -1.
Opomba: Za premagovanje omejitev VLOOKUP lahko kombinirate INDEX in MATCH.
# 4 - ČE IN Formula v Excelu
Obstaja veliko primerov, ko je treba ustvariti zastavice na podlagi nekaterih omejitev. Vsi poznamo osnovno sintakso IF. To napredno funkcijo excel IF uporabljamo za ustvarjanje novega polja, ki temelji na določeni omejitvi obstoječega polja. Kaj pa, če moramo pri ustvarjanju zastave upoštevati več stolpcev. Napr. V spodnjem primeru želimo označiti vse zaposlene, katerih plača je večja od 50 tisoč, osebni dokument pa večji od 3.
V takih primerih bi uporabili IF IN. Spodaj je na sliki zaslona za isto.
Rezultat bi vrnil kot 0.
Za ustvarjanje zastavice, ki temelji na več stolpcih z uporabo AND, imamo lahko veliko pogojev ali omejitev.
# 5 - ČE ALI Formula v Excelu
Podobno lahko uporabimo tudi funkcijo OR v excelu namesto AND, če moramo izpolniti enega od pogojev mnogih.
Če bo izpolnjen kateri koli pogoj, bomo celico naselili kot 1 ali 0. Lahko tudi nadomestimo 1 ali 0 z nekaterimi podnizami z dvojnimi narekovaji (“”).
# 6 - Formula SUMIF v Excelu
V nekaterih analizah boste morda morali filtrirati nekatera opazovanja pri uporabi funkcije vsote ali štetja. V takih primerih je na rešitvi ta napredna funkcija excel SUMIF v excelu. Filtrira vsa opažanja na podlagi določenih pogojev, podanih v tej napredni formuli excel, in jih povzame. Napr. Kaj pa, če želimo vedeti vsoto plač samo tistih zaposlenih, ki imajo osebni dokument nad 3.
Z uporabo formule SUMIFS:
Formula vrne rezultate kot 322000.
Pri uporabi funkcije COUNTIF namesto SUMIF lahko štejemo tudi število zaposlenih v organizaciji, ki imajo ID zaposlenega večje od 3.
# 7 - Formula CONCATENATE v Excelu
Ta napredna funkcija excel je ena izmed formul, ki se lahko uporablja z več različicami. Ta napredna formula Excel nam pomaga združiti več besedilnih nizov v en besedilni niz. Na primer, če želimo v enem stolpcu prikazati ID zaposlenega in ime zaposlenega.
Za to lahko uporabimo to formulo CONCATENATE.
Zgornja formula bo imela za posledico "1Aman Gupta".
Lahko imamo še eno različico tako, da med ID in NAME postavimo en vezaj. Napr. CONCATENATE (B3, ”-“, C3) bo povzročil “1-Aman Gupta”. To lahko uporabimo tudi v VLOOKUP, kadar je POGLED v vrednosti excel mešanica več spremenljivk.
# 8 - Formula LEVO, SREDNJO IN DESNO v Excelu
S to napredno formulo Excel lahko uporabimo izvleček določenega niza iz danega niza. Lahko se uporablja na podlagi naših zahtev. Napr. Če želimo iz imena zaposlenega izvleči prvih 5 znakov, lahko uporabimo formulo LEVO v Excelu z imenom stolpca in drugim parametrom kot 5.
Rezultat je podan spodaj:
Tudi uporaba formule DESNO v Excelu je enaka. Samo lik bi gledali na desni del niza. V primeru funkcije MID v Excelu pa moramo navesti začetni položaj zahtevanega besedilnega niza in dolžino niza.
# 9 - Formula OFFSET v Excelu
Ta napredna funkcija excel s kombinacijo drugih funkcij, kot sta SUM ali AVERAGE, lahko daje dinamičen pečat izračunom. Najbolje je uporabiti v primerih, ko v obstoječo bazo podatkov vstavljamo neprekinjene vrstice. OFFSET Excel nam ponuja obseg, kjer moramo omeniti referenčno celico, število vrstic in stolpcev. Napr. Če želimo izračunati povprečje prvih 5 zaposlenih v podjetju, kjer imamo plačo zaposlenih, razvrščeno po ID-ju zaposlenega, lahko naredimo naslednje. Spodnji izračun nam bo vedno plačal.
- Dala nam bo vsoto plač prvih 5 zaposlenih.
# 10 - Formula TRIM v Excelu
Ta napredna formula Excel se uporablja za čiščenje nepomembnih presledkov iz besedila. Napr. Če želimo na začetku imena odstraniti presledke, ga lahko uporabimo s funkcijo TRIM v Excelu, kot je prikazano spodaj:
Rezultat bi bil "Chandan Kale" brez prostora pred Chandanom.
