Avanserte formler i Excel (Innholdsfortegnelse)
- Introduksjon til avanserte formler i Excel
- Eksempler på avansert formel i Excel
Introduksjon til avanserte formler i Excel
Når du når mellomnivået i Excel, må du jobbe hardt for å komme deg videre til det avanserte nivået. For å komme videre til det avanserte nivået, må du være klar over noen av de ofte brukte avanserte formlene. I denne artikkelen vil jeg dekke de 10 avanserte formlene alle de utmerkede elevene må vite. Følg denne artikkelen for å lære og utforske.
Eksempler på avansert formel i Excel
La oss forstå hvordan du bruker de avanserte formlene i Excel med noen eksempler.
Du kan laste ned denne avanserte formlene Excel-malen her - avanserte formler Excel-malenEksempel 1 - Delvis VLOOKUP-funksjon
Du må ha møtt en situasjon der VLOOKUP kaster opp en feil, selv om det var en liten glipp av matche i oppslagets verdi. Hvis du for eksempel søker etter lønnen til navnet Abhishek Sinha, og hvis du bare har Abhishek, kan ikke VLOOKUP hente dataene.
Ved å bruke stjerne-tegn i hver ende av oppslagsverdien kan vi imidlertid hente dataene for delvis oppslagsverdi.
I tabell 1 har jeg fullt navn og lønn, men i tabell 2 har jeg bare delnavn og trenger å finne lønnen til hver ansatt.
Trinn 1: Åpne VLOOKUP-formelen i celle E3. Før du velger oppslagverdi, sett en stjerne (*) på hver side av oppslagsverdien.
Trinn 2: Som vanlig kan du fullføre VLOOKUP-formelen nå, så får vi resultatene.
Etter bruk av formelen ovenfor vises utgangen nedenfor.
Den samme formelen brukes i andre celler.
Eksempel # 2 - TELLER med operatørsymboler
Du må ha brukt COUNTIF & IFS-funksjonen for å telle tingene i listen. Vi kan også telle basert på operatørsymboler som større enn (>) mindre enn (<) og likhetstegn (=).
Nå kan du se på dataene nedenfor for eksempel. Hvis du vil telle det totale antall fakturaer for regionen SØR etter datoen 10. januar 2018, hvordan teller du?
Eksempel 3 - IF-tilstand med AND & OR-betingelser
Logiske funksjoner er en del av våre daglige aktiviteter. Du må mestre dem for å komme videre til neste nivå. Hvis du beregner bonus basert på flere forhold, må du hekke OG eller ELLER kondisjonere med IF-tilstand for å få jobben gjort.
Anta at du trenger å beregne bonusbeløpet for hver avdeling basert på avdelingen og år med service du trenger disse funksjonene. Basert på kriteriene nedenfor må vi beregne bonusen.
Hvis tjenesten er over 4 år og avdelingen enten er salgs- eller supportbonus er 50000 eller ellers er bonusen 25000.
Bruk formelen nedenfor for å få bonusbeløpet.
Etter bruk av formelen ovenfor, blir utdataene som er vist nedenfor.
Samme formel som gjelder i celle E3 til E9.
Eksempel # 4 - TEKST-funksjon for å gjøre deg på vei dynamisk
La oss si at du opprettholder et daglig salgstabell, og at du må oppdatere tabellen hver dag. I begynnelsen av tabellen har du en overskrift som sier “Konsoliderte salgsdata fra DD-MM-ÅÅÅÅ til DD-MM-ÅÅÅÅ”. Når og når tabellen oppdateres, må du endre kursdato. Er det ikke en frustrerende oppgave å gjøre det samme igjen og igjen? Vi kan gjøre denne overskriften dynamisk ved å bruke TETX, MIN og Max funksjon sammen med sammenkoblet operatørsymbol ampersand (&).
Eksempel 5 - INDEX + MATCH + MAX for å finne den høyeste selger
Du har en liste over salgspersoner og salget de har gjort mot navnet sitt. Hvordan forteller du hvem som er den beste eller høyeste selgeren på listen? Vi har selvfølgelig flere andre teknikker for å fortelle resultatet, men denne funksjonen kan returnere den høyeste selgeren fra partiet.
Bruk funksjonen nedenfor for å få det høyeste selgernavnet.
Etter bruk av formelen ovenfor, blir utdataene som er vist nedenfor.
Eksempel 6 - Få et antall unike verdier fra listen
Hvis du har mange dupliserte verdier og er pålagt å fortelle hvor mange unike verdier det er i hvordan forteller du det? Du kan fortelle ved å fjerne duplikatverdien, men dette er ikke den dynamiske måten å fortelle det unike verdistallet.
Ved hjelp av denne array-funksjonen kan vi fortelle de unike verdiene fra partiet.
Bruk formelen nedenfor for å få en unik verdiliste.
Eksempel 7 - Bruk navngitt område for å gjøre dropdown dynamisk
Hvis du ofte jobber med en rullegardinliste og gjør rullegardinlisten oppdatert, må du gå tilbake til kildelisten for å slette eller legge til verdier. Etter det må du komme tilbake til nedtrekkscellene og oppdatere området for nedtrekkslisten igjen.
Hvis du imidlertid kan opprette navngitt område for rullegardinlisten, kan du gjøre rullegardinlisten dynamisk og oppdatert.
Lag et navneområde som vist på bildet nedenfor.
Gå nå til rullegardinmenyen og åpne rullegardinmenyen.
I kildetrykk på F3-tasten viser den alle definerte navn, velg navnet på rullegardinlisten.
OK, en rullegardinliste er klar, og den vil oppdatere verdiene automatisk når det er endring i nedtrekkslisteområdet.
Eksempel 8 - Bli kvitt feilverdier ved å bruke IFERROR-funksjonen
Jeg er sikker på at du har opplevd feilverdier mens du jobber med VLOOKUP, divisjonberegninger. Å håndtere disse feilverdiene er kjedelig oppgave. Men vi kan bli kvitt disse feilverdiene ved å bruke IFERROR-funksjonen i formelen.
Etter bruk av formelen ovenfor, blir utdataene som er vist nedenfor.
Samme formel som brukes i andre celler.
Eksempel # 9 - Bruk PMT-funksjon for å lage ditt eget EMI-diagram
I dag er ikke EMI-alternativet noe rart for oss alle. I Excel kan vi estimere vårt eget EMI-diagram ved å bruke PMT-funksjon. Følg trinnene nedenfor for å lage ditt eget diagram.
Bruk formelen nedenfor i celle B4 for å få EMI-mengden.
Eksempel # 10 - INDEX + MATCH som et alternativ til VLOOKUP-funksjon
Jeg håper du er klar over begrensningen i VLOOKUP-funksjonen. En hovedbegrensning er at VLOOKUP kan hente dataene fra venstre til høyre, ikke fra høyre til venstre. Ikke hele tiden er dataene godt organisert og klar til bruk. Ofte er verdikolonnen vi ser etter på venstre side av oppslagsverdien, så VLOOKUP klarer ikke å hjelpe i disse tilfellene.
En kombinasjon av INDEX + MATCH-funksjonen fungerer som et alternativ til VLOOKUP-funksjonen.
Etter bruk av formelen ovenfor vises utgangen nedenfor.
Den samme formelen brukes i andre celler.
Basert på produkt-ID må vi hente ut salgsverdier. I hovedtabellen er produkt-ID på høyre side av salgskolonnen. Så VLOOKUP kan ikke hente dataene. Bruk formelen nedenfor for å hente dataene.
Anbefalte artikler
Dette er en guide til avanserte formler i Excel. Her diskuterer vi hvordan du bruker avanserte formler i Excel sammen med praktiske eksempler og nedlastbar Excel-mal. Du kan også gå gjennom andre foreslåtte artikler -
- Hvordan bruke Excel INDEX-funksjon?
- Avansert Excel | Databasefunksjon
- Eksempler på TRIM-formel i Excel
- Veiledning for Excel OFFSET-formel