Komplekse formler i Excel (indholdsfortegnelse)
- Introduktion til komplekse formler i Excel
- Vælg Funktion Server som opslagsfunktion
- VLOOKUP MED VÆLG
- HVIS MED OG & ELLER funktion
Introduktion til komplekse formler i Excel
Excel leveres med mange nyttige formler, vi har en kategori finansiel, logisk, tekst, dato og tid, opslag & referencer og matematik & udtræk ”. Hver individuelle formel er god nok til mellemliggende brugere, hvor de står lige fremadrettede situationer, men når du skrider videre til det næste niveau, skal din færdighed tage højden, så i denne henseende spiller anvendelse af mere komplekse formler i excel en vigtig rolle i løsningen af komplekse situationer. Husk dette i denne artikel, vi tager dig gennem nogle af de vigtige og meget nødvendige komplekse formler. Læs videre.
Vælg Funktion Server som opslagsfunktion
Ikke mange af jer har brugt VÆLG-funktion, men dette er meget nyttigt at også fungere som opslagsfunktion.
Du kan downloade denne komplekse formler Excel-skabelon her - Komplekse formler Excel-skabelon Se f.eks. På nedenstående data.
Vi har data om kunder på månedsbasis under denne tabel, vi har hver kundetabel. I denne tabel, i celle B13, har jeg oprettet en rulleliste for måneden, så når denne rullemenu ændres, skal vi få summen af den valgte måned.
Opret en tabel som nedenunder.
Anvend VLOOKUP på cellen D13.
Åbn nu VÆLG-funktion i celle B14.
Vælg INDEX-nummer som en VLOOKUP-celle, og gør det til en absolut reference.
Vælg nu for hvert argument hver måned salgsnumre.
Luk beslaget, og tryk på Enter-tasten.
Nu kopieres og indsæt formlen også til nedenunder celler.
Nu VÆLG formlen viser numrene på den valgte måned.
VLOOKUP med VÆLG-funktion
Vi har set traditionen VLOOKUP, men vi kan også kombinere den med VÆLG-funktion. Se f.eks. På nedenstående data.
I cellerne A1 til B11 har vi en salgstabel, og i celle D2 har jeg oprettet rullelisten over måneder, og i celle E2 skal vi anvende VLOOKUP og få den valgte måneds salgsværdi. Men problemet med at anvende VLOOKUP er en datastruktur, hvor opslagets værdi er til højre for resultatsøjlen, så VLOOKUP kan ikke hente dataene fra højre til venstre, i dette tilfælde kan vi bruge VÆLG-funktion.
For at vælge tabellarray skal vi åbne VÆLG-funktionen her.
I argumentet INDEX-nummer i blomst indtaster parenteser 1 & 2. Vælg nu det første interval som kolonne Måned.
Vælg kolonnen Salg som det andet interval.
Nævn kolonneindeksnummer som 2 og rækkeopslag som 0.
Så vi fik resultatet for måneden apr fra tabellen. Så når du ikke har en nøjagtig datastruktur, kan vi vælge, VÆLG funktion.
HVIS MED OG & ELLER funktion
Logiske funktioner er fulde af logik til at beregne tingene. Se på nedenstående eksempeldata.
Fra ovenstående tabel skal vi beregne bonusen baseret på nedenstående betingelser.
- Hvis tjenesten er mere end 1, 5 år, og hvis afdelingen enten er salg eller support, er bonus 1000.
- Hvis der er noget andet, er Bonus 3000.
For at beregne bonus er vi nødt til at anvende IF, AND, & OR sammen. Åben IF-tilstand i celle D2.
Vi er nødt til at teste flere forhold, åbne OG funktion.
Nu er det næste logiske enten Salg eller Support, for denne åbne ELLER betingelse.
Test, om titlen er salg eller support.
Så hvis alle de medfølgende betingelser er SAND, skal bonusen være 10000.
Hvis de medfølgende logiske tests er FALSE, er bonusen 3000.
Luk beslaget, og tryk på Enter-tasten, så skal vi få resultatet.
Kopier og indsæt formlen til nedenstående celler.
Så efter betingelsen fik vi resultatet. Hvis tjenesten er> 1, 5 år, og titlen er enten Salg eller Support, får vi 10000, ellers får vi 3000.
Kompleks formel med jokertegn
VLOOKUP kræver en nøjagtig søgningsværdi, der skal matches for at hente dataene. Dette er det traditionelle slogan, men vi kan stadig hente dataene ved hjælp af partikelopslagværdi. For eksempel, hvis opslagets værdi er "VIVO" og i hovedtabellen, hvis det er "VIVO Mobile", kan vi stadig matche ved hjælp af jokertegn. Vi vil se et af eksemplerne nu, nedenfor er eksemplet data.
Vi har opslagstabel i kolonne A. I kolonne C har vi opslagværdier, disse opslagsværdier er ikke nøjagtigt de samme som opslagstabelværdierne. Så vi vil se, hvordan du anvender VLOOKUP ved hjælp af jokertegn.
Åbn først VLOOKUP-funktionen i cellen D1.
Det første argument er opslagsværdien . Et af problemerne med opslagværdi her er, at vi ikke har et nøjagtigt match, så vi er nødt til at vedlægge denne opslagværdi med en stjerne før og efter opslagværdien.
Her har vi anvendt to stjerne-tegn “*” & C2 & ”*”. Her angiver asterisk noget mellem jokertegnet, der skal matches og returnerer det relaterede resultat.
Selvom vi netop havde ”Infosys” -stjerne, svarede værdien i opslagstabellen og returnerede det nøjagtige resultat som ”Infosys Ltd”.
Tilsvarende fik vi i celle D6 fejlværdi som #VÆRDE! fordi der ikke er noget ord “Mintra” i opslagstabellen.
Anbefalede artikler
Dette er en guide til komplekse formler i Excel. Her diskuterer vi nogle af de vigtige og meget nødvendige komplekse formler med eksempler og Excel-skabelon. Du kan også gennemgå vores andre foreslåede artikler for at lære mere -
- SØG Formel i Excel (Tips & tricks)
- Tutorials om Excel NORMSINV-funktion
- Navnefelt og dens anvendelser i Excel
- Sammenkædede strenge i Excel