Avancerede formler i Excel (indholdsfortegnelse)
- Introduktion til avancerede formler i Excel
- Eksempler på avanceret formel i Excel
Introduktion til avancerede formler i Excel
Når du når det mellemliggende niveau i Excel, skal du arbejde hårdt for at komme videre til det avancerede niveau. For at komme videre til det avancerede niveau skal du være opmærksom på nogle af de ofte anvendte avancerede formler. I denne artikel vil jeg dække de top 10 avancerede formler, som alle de excel-elever skal kende. Følg denne artikel for at lære og udforske.
Eksempler på avanceret formel i Excel
Lad os forstå, hvordan du bruger de avancerede formler i Excel med nogle eksempler.
Du kan downloade denne avancerede formler Excel-skabelon her - avancerede formler Excel-skabelonEksempel # 1 - Delvis VLOOKUP-funktion
Du skal være stødt på en situation, hvor VLOOKUP kaster en fejl op, selvom der var en lille miss-match i opslagets værdi. For eksempel, hvis du søger efter lønnen for navnet Abhishek Sinha, og hvis du kun har Abhishek, kan VLOOKUP ikke hente dataene.
Imidlertid kan vi ved hjælp af stjerne tegn i hver ende af opslagets værdi hente dataene til delvis opslag værdi.
I tabel 1 har jeg fuldt navn og løn, men i tabel 2 har jeg kun delnavne og har brug for at finde lønnen til hver enkelt medarbejder.
Trin 1: Åbn VLOOKUP-formlen i celle E3. Inden du vælger opslagværdien, skal du sætte en stjerne (*) på hver side af opslagværdien.
Trin 2: Som sædvanligt kan du udfylde VLOOKUP-formlen nu, og vi får resultaterne.
Efter anvendelse af ovenstående formel vises output nedenfor.
Den samme formel bruges i andre celler.
Eksempel # 2 - COUNTIFS med operatørsymboler
Du skal have brugt COUNTIF & IFS-funktionen til at tælle tingene på listen. Vi kan også tælle baseret på operatørsymboler som større end (>) mindre end (<) og lige tegn (=).
Se nu for eksempel nedenstående data. Hvis du vil tælle det samlede antal fakturaer for regionen SYD efter datoen 10. januar 2018, hvordan tæller du?
Eksempel 3 - IF-tilstand med AND & OR-betingelser
Logiske funktioner er en del af vores daglige aktiviteter. Du skal mestre dem for at komme videre til næste niveau. Hvis du beregner bonus baseret på flere betingelser, skal du indlejre AND eller ELLER betingelse med IF-betingelse for at få jobbet gjort.
Antag, at du har brug for at beregne bonusbeløbet for hver afdeling baseret på den afdeling og de år med service, du har brug for disse funktioner. Baseret på nedenstående kriterier er vi nødt til at beregne bonus.
Hvis tjenesten er over 4 år, og afdelingen enten er salgs- eller supportbonus er 50000 eller ellers er bonus 25000.
Anvend nedenstående formel for at få bonusbeløbet.
Efter anvendelse af ovenstående formel er output vist nedenfor.
Samme formel gælder i celle E3 til E9.
Eksempel # 4 - TEKST-funktion, der gør dig i retning af dynamik
Lad os sige, at du opretholder en daglig salgstabel, og at du skal opdatere tabellen hver dag. I begyndelsen af tabellen har du en overskrift, der siger “Konsoliderede salgsdata fra DD-MM-ÅÅÅÅ til DD-MM-ÅÅÅ”. Når og når tabellen opdateres, skal du ændre overskridelsesdatoen. Er det ikke en frustrerende opgave at gøre det samme igen og igen? Vi kan gøre denne overskrift dynamisk ved hjælp af TETX, MIN og Max funktion sammen med sammenkoblet operatørsymbol ampersand (&).
Eksempel 5 - INDEX + MATCH + MAX for at finde den højeste salgsperson
Du har en liste over salgspersoner og de salg de har foretaget mod deres navn. Hvordan fortæller du, hvem der er den bedste eller højeste sælger på listen? Vi har selvfølgelig flere andre teknikker til at fortælle resultatet, men denne funktion kan returnere den højeste sælger fra partiet.
Anvend funktion nedenfor for at få det højeste sælgers navn.
Efter anvendelse af ovenstående formel er output vist nedenfor.
Eksempel 6 - Få et antal unikke værdier fra listen
Hvis du har mange duplikatværdier og skal fortælle, hvor mange unikke værdier der er, hvordan fortæller du det? Du kan fortælle det ved at fjerne duplikatværdien, men dette er ikke den dynamiske måde at fortælle det unikke værdiantal.
Ved hjælp af denne array-funktion kan vi fortælle de unikke værdier fra partiet.
Anvend formlen nedenfor for at få en unik værdiliste.
Eksempel # 7 - Brug navngivet interval for at gøre dropdown dynamisk
Hvis du ofte arbejder med en rulleliste og foretager rullelisten ajour, skal du gå tilbage til kildelisteområdet for at slette eller tilføje værdier. Derefter skal du vende tilbage til rullelisten og igen opdatere rækkevidden for rullelisten.
Hvis du imidlertid kan oprette navngivet interval for din rulleliste, kan du gøre rullelisten dynamisk og opdateret.
Opret et navneområde som vist på billedet herunder.
Gå nu til rullemenuen og åbn rullemenuen.
I kildetryk på F3-tasten viser det alle definerede navne, vælg navnet på din rulleliste.
Ok, en rulleliste er klar, og den opdaterer værdierne automatisk, når der er en ændring i rullelisten.
Eksempel 8 - Slip af fejlværdier ved hjælp af IFERROR-funktion
Jeg er sikker på, at du har oplevet fejlværdier, mens du arbejder med VLOOKUP, Divisionberegninger. Håndtering af disse fejlværdier er en kedelig opgave. Men vi kan slippe af med disse fejlværdier ved at bruge IFERROR-funktion i formlen.
Efter anvendelse af ovenstående formel er output vist nedenfor.
Samme formel, der blev brugt i andre celler.
Eksempel # 9 - Brug PMT-funktion til at oprette dit eget EMI-diagram
I dag er EMI-løsningen ikke en underlig ting for os alle. I Excel kan vi estimere vores eget EMI-diagram ved hjælp af PMT-funktion. Følg nedenstående trin for at oprette dit eget diagram.
Anvend nedenstående formel i celle B4 for at få EMI-mængden.
Eksempel # 10 - INDEX + MATCH som et alternativ til VLOOKUP-funktion
Jeg håber, du er opmærksom på begrænsningen af VLOOKUP-funktionen. En hovedbegrænsning er, at VLOOKUP kan hente dataene fra venstre til højre ikke fra højre til venstre. Ikke hele tiden er dataene velorganiserede og klar til brug. Ofte er den værdikolonne, vi leder efter, der til venstre for søgningsværdien, så VLOOKUP hjælper ikke i disse tilfælde.
En kombination af INDEX + MATCH-funktion fungerer som et alternativ til VLOOKUP-funktion.
Efter anvendelse af ovenstående formel vises output nedenfor.
Den samme formel bruges i andre celler.
Baseret på produkt-ID er vi nødt til at udtrække salgsværdier. I hovedtabellen er produkt-ID til højre for salgskolonnen. Så VLOOKUP kan ikke hente dataene. Brug formlen nedenfor til at hente dataene.
Anbefalede artikler
Dette er en guide til avancerede formler i Excel. Her diskuterer vi, hvordan du bruger avancerede formler i Excel sammen med praktiske eksempler og downloadbar excel-skabelon. Du kan også gennemgå vores andre foreslåede artikler -
- Sådan bruges Excel INDEX-funktion?
- Avanceret Excel | Databasefunktion
- Eksempler på TRIM-formler i Excel
- Vejledning til Excel OFFSET-formel