Liste over topp 10 avanserte Excel-formler og funksjoner
# 1 - VLOOKUP Formula i Excel
Denne avanserte excel-funksjonen er en av de mest brukte formlene i excel. Det er hovedsakelig på grunn av enkelheten til denne formelen og dens anvendelse i å slå opp en viss verdi fra andre tabeller, som har en felles variabel på tvers av disse tabellene. Anta at du har to tabeller med detaljer om selskapets ansattes lønn og navn med ansattes ID som en primær kolonne. Du vil få lønnen fra tabell B i tabell A.
Du kan bruke VLOOKUP som nedenfor.
Det vil resultere i tabellen nedenfor når vi bruker denne avanserte Excel-formelen i andre celler i kolonnen Arbeidslønn.
Dra formelen til resten av cellene.
Det er tre hovedavgrensninger av VLOOKUP:
- Du kan ikke ha en primær kolonne til høyre for kolonnen som du vil fylle ut verdien fra en annen tabell for. I dette tilfellet kan ikke lønnskolonnen være før ansatt-ID.
- I de dupliserte verdiene i primærkolonnen i tabell B blir den første verdien fylt ut i cellen.
- Hvis du setter inn en ny kolonne i databasen (Eks Sett inn en ny kolonne før ansattes lønn i tabell B), kan utgangen av formelen være forskjellig basert på en posisjon som du har nevnt i formelen (I ovennevnte tilfelle vil resultatet ville være blank)
# 2 - INDEX Formula i Excel
Denne avanserte Excel-formelen brukes til å hente verdien av en celle i en gitt tabell ved å spesifisere antall rader, kolonner eller begge deler. Eks. For å få navnet på en ansatt på 5 th observasjon, er dataene nedenfor.
Vi kan bruke den avanserte Excel-formelen som nedenfor:
Den samme INDEX-formelen kan brukes til å få verdi langs raden. Når du bruker både rad- og kolonnenummer, vil syntaksen se ut slik:
Ovennevnte formel vil returnere "Rajesh Ved."
Merk: Hvis du setter inn en ny rad i den femte raden, vil formelen returnere "Chandan Kale." Derfor vil produksjonen avhenge av eventuelle endringer som skjer i datatabellen over tid.
# 3 - MATCH-formel i Excel
Denne Excel-avanserte formelen returnerer rad- eller kolonnetallet når det er en spesifikk streng eller nummer i det gitte området. I eksemplet nedenfor prøver vi å finne “Rajesh Ved” i kolonnen Ansatt navn.
Formelen vil være som gitt nedenfor:
MATCH-funksjonen vil returnere 5 som verdien.
Den 3 rd argumentet brukes for nøyaktig match. Du kan også bruke +1 og -1 basert på dine krav.
Merk: Man kan kombinere INDEX og MATCH for å overvinne begrensningen av VLOOKUP.
# 4 - HVIS OG Formel i Excel
Det er mange tilfeller når man trenger å lage flagg basert på noen begrensninger. Vi er alle kjent med den grunnleggende syntaksen til IF. Vi bruker denne avanserte excel IF-funksjonen til å lage et nytt felt basert på noen begrensninger på et eksisterende felt. Men hva om vi må bruke flere kolonner når vi lager et flagg. Eks. I nedenstående tilfelle ønsker vi å markere alle ansatte med lønn som er større enn 50 000, men ansattes ID større enn 3.
Vi vil bruke IF OG i slike tilfeller. Vennligst finn under skjermbildet for det samme.
Resultatet ble 0.
Vi kan ha mange betingelser eller begrensninger for å lage et flagg basert på flere kolonner ved hjelp av AND.
# 5 - HVIS ELLER Formel i Excel
På samme måte kan vi også bruke OR-funksjonen i excel i stedet for AND hvis vi trenger å tilfredsstille en av betingelsene til mange.
I de ovennevnte tilfellene, hvis noen av en betingelse blir oppfylt, vil vi ha cellepopulert som 1 annen 0. Vi kan også erstatte 1 eller 0 med noen understrenger med doble anførselstegn ("").
# 6 - SUMIF Formula i Excel
I noen analyser kan det hende du må filtrere noen observasjoner når du bruker sum- eller tellefunksjonen. I slike tilfeller er denne avanserte excel SUMIF-funksjonen i excel vår redning. Den filtrerer alle observasjonene basert på visse forhold gitt i denne avanserte Excel-formelen og oppsummerer dem. Eks. Hva om vi bare vil vite lønnen til bare de ansatte som har ansattes ID som er større enn 3.
Ved å bruke SUMIFS-formelen:
Formelen returnerer resultatene som 322000.
Vi kan også telle antall ansatte i organisasjonen som har ansattes ID større enn 3 når vi bruker COUNTIF i stedet for SUMIF.
# 7 - CONCATENATE Formula i Excel
Denne excel avanserte funksjonen er en av formlene som kan brukes med flere varianter. Denne avanserte Excel-formelen hjelper oss å koble flere tekststrenger til en tekststreng. Eks. Hvis vi vil vise ansattes ID og ansattes navn i en enkelt kolonne.
Vi kan bruke denne CONCATENATE-formelen til å gjøre det.
Ovenstående formel vil resultere i "1Aman Gupta".
Vi kan ha en variant til ved å sette en enkelt bindestrek mellom ID og NAME. Eks. CONCATENATE (B3, ”-“, C3) vil resultere i “1-Aman Gupta”. Vi kan også bruke dette til VLOOKUP når LOOKUP i excel-verdi er en blanding av mer enn en variabel.
# 8 - VENSTRE, MIDDEL og HØYRE formel i Excel
Vi kan bruke denne avanserte Excel-formelen for å trekke ut et spesifikt underlag fra en gitt streng. Den kan brukes basert på våre krav. Eks. Hvis vi ønsker å trekke ut de 5 første tegnene fra ansattes navn, kan vi bruke VENSTRE formel i excel med kolonnenavnet og den andre parameteren som 5.
Resultatet er gitt nedenfor:
Anvendelsen av RIGHT-formelen i Excel er også den samme. Det er bare det at vi ser på karakteren fra høyre streng. Imidlertid, når det gjelder en MID-funksjon i Excel, må vi gi startposisjonen til den nødvendige tekststrengen og lengden på strengen.
# 9 - OFFSET-formel i Excel
Denne avanserte excel-funksjonen med en kombinasjon av andre funksjoner som SUM eller GJENNOMSNITT, kan gi et dynamisk preg på beregningene. Den brukes best i tilfeller når vi setter sammenhengende rader i en eksisterende database. OFFSET Excel gir oss et område der vi trenger å nevne referansecelle, antall rader og kolonner. Eks. Hvis vi ønsker å beregne gjennomsnittet av de fem første ansatte i selskapet der vi har lønn for ansatte sortert etter ansattes ID, kan vi gjøre følgende. Beregningen nedenfor vil alltid gi oss lønn.
- Det vil gi oss summen av lønn til de 5 første ansatte.
# 10 - TRIM-formel i Excel
Denne avanserte Excel-formelen brukes til å rydde opp i uviktige mellomrom fra teksten. Eks. Hvis vi vil fjerne mellomrom i begynnelsen av et navn, kan vi bruke det ved å bruke TRIM-funksjonen i excel som nedenfor:
Den resulterende produksjonen ville være "Chandan Kale" uten plass før Chandan.
