Liste over Top 10 avancerede Excel-formler og -funktioner
# 1 - VLOOKUP-formel i Excel
Denne avancerede excel-funktion er en af de mest anvendte formler i excel. Det skyldes hovedsageligt enkelheden af denne formel og dens anvendelse til at slå en bestemt værdi op fra andre tabeller, som har en fælles variabel på tværs af disse tabeller. Antag at du har to tabeller med detaljer om en virksomheds medarbejderløn og navn med medarbejder-id som en primær kolonne. Du ønsker at få lønnen fra tabel B i tabel A.
Du kan bruge VLOOKUP som nedenfor.
Det vil resultere i nedenstående tabel, når vi anvender denne avancerede excelformel i andre celler i kolonnen Medarbejderløn.
Træk formlen til resten af cellerne.
Der er tre store afgrænsninger af VLOOKUP:
- Du kan ikke have en primær kolonne til højre for den kolonne, som du vil udfylde værdien for fra en anden tabel. I dette tilfælde kan kolonnen medarbejderløn ikke være før medarbejder-id.
- I de duplikerede værdier i den primære kolonne i tabel B bliver den første værdi befolket i cellen.
- Hvis du indsætter en ny kolonne i databasen (Ex Indsæt en ny kolonne før medarbejderløn i tabel B), kan output af formlen være anderledes baseret på en position, som du har nævnt i formlen (I ovenstående tilfælde er resultatet ville være blank)
# 2 - INDEX-formel i Excel
Denne avancerede excelformel bruges til at få værdien af en celle i en given tabel ved at angive antallet af rækker, kolonner eller begge dele. Eks. For at få navnet på en medarbejder på 5 th observation, Nedenfor er de data.
Vi kan bruge den avancerede excelformel som nedenfor:
Den samme INDEX-formel kan bruges til at få værdi langs rækken. Når du bruger både række- og søjlenummer, ser syntaksen ud som sådan:
Ovenstående formel ville returnere "Rajesh Ved."
Bemærk: Hvis du indsætter en anden række i 5 th række, vil formlen returnere ”Chandan Kale.” Derfor vil output afhænge af eventuelle ændringer, der sker i datatabellen over tid.
# 3 - MATCH-formel i Excel
Denne avancerede Excel-formel returnerer række- eller kolonnetallet, når der er en bestemt streng eller et specifikt nummer i det givne interval. I nedenstående eksempel forsøger vi at finde “Rajesh Ved” i kolonnen Medarbejdernavn.
Formlen ville være som angivet nedenfor:
MATCH-funktionen returnerer 5 som værdien.
3 rd argument bruges til det eksakte. Du kan også bruge +1 og -1 baseret på dine krav.
Bemærk: Man kan kombinere INDEX og MATCH for at overvinde begrænsningen af VLOOKUP.
# 4 - HVIS OG Formel i Excel
Der er mange tilfælde, hvor man skal oprette flag baseret på nogle begrænsninger. Vi er alle fortrolige med den grundlæggende syntaks for IF. Vi bruger denne avancerede excel IF-funktion til at oprette et nyt felt baseret på en vis begrænsning på et eksisterende felt. Men hvad nu hvis vi har brug for flere kolonner i betragtning, når vi opretter et flag. Eks. I nedenstående tilfælde vil vi markere alle de ansatte, hvis løn er større end 50K, men medarbejder-ID større end 3.
Vi bruger IF OG i sådanne tilfælde. Find nedenstående skærmbillede for det samme.
Resultatet returneres som 0.
Vi kan have mange betingelser eller begrænsninger for at oprette et flag baseret på flere kolonner ved hjælp af AND.
# 5 - HVIS ELLER Formel i Excel
På samme måde kan vi også bruge OR-funktionen i excel i stedet for AND, hvis vi har brug for at opfylde en af betingelserne for mange.
I ovennævnte tilfælde, hvis en af en betingelse bliver opfyldt, har vi celle udfyldt som 1 anden 0. Vi kan også erstatte 1 eller 0 med nogle understrenge med dobbelt anførselstegn ("").
# 6 - SUMIF-formel i Excel
I nogle analyser er du muligvis nødt til at filtrere nogle observationer, når du anvender sum- eller tællefunktionen. I sådanne tilfælde er denne avancerede excel SUMIF-funktion i excel til vores redning. Det filtrerer alle observationer baseret på visse betingelser i denne avancerede excelformel og opsummerer dem. Eks. Hvad hvis vi kun vil vide summen af lønninger for de ansatte, der har medarbejder-ID større end 3.
Ved at anvende SUMIFS-formlen:
Formlen returnerer resultaterne som 322000.
Vi kan også tælle antallet af medarbejdere i organisationen, der har medarbejder-ID større end 3, når vi bruger COUNTIF i stedet for SUMIF.
# 7 - CONCATENATE Formula i Excel
Denne excel avancerede funktion er en af formlerne, der kan bruges med flere varianter. Denne avancerede Excel-formel hjælper os med at forbinde flere tekststrenge til en tekststreng. Eks. Hvis vi vil vise medarbejder-id og medarbejdernavn i en enkelt kolonne.
Vi kan bruge denne CONCATENATE-formel til at gøre det.
Ovenstående formel vil resultere i “1Aman Gupta”.
Vi kan have en variant mere ved at sætte en enkelt bindestreg mellem ID og NAME. Eks. CONCATENATE (B3, ”-“, C3) vil resultere i “1-Aman Gupta”. Vi kan også bruge dette til VLOOKUP, når LOOKUP i excel-værdi er en blanding af mere end en variabel.
# 8 - VENSTRE, MIDT og HØJRE formel i Excel
Vi kan bruge denne avancerede excelformel til at udtrække et specifikt underlag fra en given streng. Det kunne bruges baseret på vores krav. Eks. Hvis vi ønsker at udtrække de første 5 tegn fra medarbejdernavn, kan vi bruge VENSTRE formel i excel med kolonnenavn og anden parameter som 5.
Outputtet er angivet nedenfor:
Anvendelsen af den HØJRE formel i excel er også den samme. Det er bare, at vi ville se på karakteren fra højre for strengen. I tilfælde af en MID-funktion i Excel skal vi dog angive startpositionen for den krævede tekststreng og længden af strengen.
# 9 - OFFSET-formel i Excel
Denne avancerede excel-funktion med en kombination af andre funktioner såsom SUM eller GEMENSKAB kunne give et dynamisk præg på beregningerne. Det bruges bedst i tilfælde, hvor vi indsætter kontinuerlige rækker i en eksisterende database. OFFSET Excel giver os et interval, hvor vi skal nævne referencecelle, antal rækker og kolonner. Eks. Hvis vi ønsker at beregne gennemsnittet af de første 5 medarbejdere i virksomheden, hvor vi har en løn for medarbejdere sorteret efter medarbejder-id, kan vi gøre følgende. Nedenstående beregning giver os altid løn.
- Det giver os summen af lønningerne for de første 5 ansatte.
# 10 - TRIM-formel i Excel
Denne avancerede excelformel bruges til at rydde op i uvigtige mellemrum fra teksten. Eks. Hvis vi vil fjerne mellemrum i begyndelsen af et navn, kan vi bruge det ved at bruge TRIM-funktionen i excel som nedenfor:
Den resulterende produktion ville være "Chandan Kale" uden plads før Chandan.
