Komplekse formler i Excel (Innholdsfortegnelse)
- Introduksjon til komplekse formler i Excel
- Velg Funksjon Server som oppslagsfunksjon
- VLOOKUP MED VELG
- HVIS MED OG & ELLER funksjon
Introduksjon til komplekse formler i Excel
Excel kommer med mange nyttige formler, vi har en kategori finansiell, logisk, tekst, dato og klokkeslett, oppslag og referanser og matematikk og trigg ”. Hver enkelt formel er god nok for mellombrukere der de står rett frem situasjoner, men når du går videre til neste nivå, trenger ferdigheten din å ta høyden, slik at bruk av mer komplekse formler i Excel spiller en viktig rolle i å løse komplekse situasjoner. Ved å huske på denne artikkelen vil vi ta deg gjennom noen av de viktige og sårt tiltrengte komplekse formlene. Les videre.
Velg Funksjon Server som oppslagsfunksjon
Ikke mange av dere har brukt VELG-funksjonen, men dette er veldig nyttig for å fungere som oppslagfunksjon også.
Du kan laste ned denne komplekse formlene Excel-mal her - Komplekse formler Excel-mal. Se for eksempel på dataene nedenfor.
Vi har data om kunder på månedlig basis under denne tabellen, vi har hvert kundetabell. I denne tabellen har jeg i celle B13 opprettet rullegardinlisten for måneden, slik at når denne rullegardinmenyen endres, skulle vi få totalen for den valgte måneden.
Lag en tabell som nedenfor.
Bruk VLOOKUP til cellen D13.
Nå åpner VELG funksjonen i celle B14.
Velg INDEX-nummeret som en VLOOKUP-celle og gjør det til en absolutt referanse.
Velg nå for hvert argument hver måned salgstall.
Lukk beslaget og trykk på Enter-tasten.
Nå kopierer du og limer inn formelen til under celler også.
Nå VELG formelen viser tallene for den valgte måneden.
VLOOKUP MED VALG-funksjon
Vi har sett tradisjonen VLOOKUP, men vi kan kombinere den med VELG funksjon også. For eksempel se på dataene nedenfor.
I celler A1 til B11 har vi et salgstabell og i celle D2 har jeg laget rullegardinlisten over måneder og i celle E2 må vi bruke VLOOKUP og få den valgte måneds salgsverdien. Men problemet med å anvende VLOOKUP er en datastruktur, der oppslagsverdien er til høyre for resultatkolonnen, så VLOOKUP kan ikke hente dataene fra høyre til venstre, i dette tilfellet kan vi bruke VELG-funksjonen.
For å velge tabellarray, må vi åpne CHOOSE-funksjonen her.
I INDEX Number-argumentet i blomst, angir parenteser 1 & 2. Velg nå det første området som månedskolonne.
Velg Salg-kolonnen som det andre området.
Nevn kolonneindeksnummer som 2 og rekkeviddeoppslag som 0.
Så vi fikk resultatet for måneden apr fra bordet. Så når du ikke har en nøyaktig datastruktur kan vi velge, VELG funksjon.
HVIS MED OG & ELLER funksjon
Logiske funksjoner er fulle av logikk for å beregne tingene. Ta en titt på eksemplene nedenfor.
Fra tabellen over må vi beregne bonusen basert på betingelsene nedenfor.
- Hvis tjenesten er mer enn 1, 5 år og hvis avdelingen enten er salg eller support, er bonus 1000.
- Hvis noe annet så er Bonus 3000.
For å beregne bonus må vi bruke IF, AND, & OR sammen. Åpen IF-tilstand i celle D2.
Vi må teste flere forhold, åpne OG funksjon.
Nå er det neste logiske enten salg eller support, for denne åpne OR-tilstanden.
Test om tittelen er salg eller support.
Så hvis alle levert betingelser er SANN, bør bonusen være 10000.
Hvis de medfølgende logiske testene er FALSE, er bonusen 3000.
Lukk braketten og trykk Enter-tasten vi skal få resultatet.
Kopier og lim inn formelen til cellene nedenfor.
Så i henhold til betingelsen fikk vi resultatet. Hvis tjenesten er> 1, 5 år og tittelen er enten salg eller support, vil vi få 10000 eller ellers får vi 3000.
Kompleks formel med jokertegn
VLOOKUP krever en nøyaktig oppslagverdi for å samsvare for å hente dataene. Dette er det tradisjonelle slagordet, men vi kan fremdeles hente dataene ved å bruke partikkeloppslagverdi. For eksempel, hvis oppslagsverdien er "VIVO" og i hovedtabellen hvis det er "VIVO Mobile", kan vi fremdeles samsvare med jokertegn. Vi vil se et av eksemplene nå, nedenfor er eksemplene data.
Vi har oppslagstabell i kolonne A. I kolonne C har vi oppslagverdier, disse oppslagsverdiene er ikke helt de samme som oppslagstabellverdiene. Så vi får se hvordan du bruker VLOOKUP ved hjelp av jokertegn.
Åpne først VLOOKUP-funksjonen i cellen D1.
Det første argumentet er Lookup Value . Et av problemene med oppslagverdi her er at vi ikke har en nøyaktig samsvar, så vi må legge ved denne oppslagsverdien med en stjerne før og etter oppslagsverdien.
Her har vi brukt to stjerne-tegn “*” & C2 & ”*”. Her indikerer stjerne noen ting mellom jokertegnene skal samsvares og returnerer det relaterte resultatet.
Selv om vi bare hadde "Infosys" -stjerne-karakter, matchet verdien i oppslagstabellen og returnerte det eksakte resultatet som "Infosys Ltd".
Tilsvarende fikk vi i celle D6 feilverdi som #VALDE! fordi det ikke er noe ord "Mintra" i oppslagstabellen.
Anbefalte artikler
Dette er en guide til komplekse formler i Excel. Her diskuterer vi noen av de viktige og sårt tiltrengte komplekse formlene med eksempler og Excel-mal. Du kan også gå gjennom andre foreslåtte artikler for å lære mer -
- SØK Formel i Excel (tips og triks)
- Veiledninger om Excel NORMSINV-funksjon
- Navneboks og bruksområder i Excel
- Sammenføyningstrenger i Excel