Avancerade formler i Excel (innehållsförteckning)
- Introduktion till avancerade formler i Excel
- Exempel på avancerad formel i Excel
Introduktion till avancerade formler i Excel
När du når mellannivån i Excel måste du arbeta hårt för att gå vidare till avancerad nivå. För att gå vidare till avancerad nivå måste du vara medveten om några av de ofta använda avancerade formlerna. I den här artikeln kommer jag att täcka de 10 avancerade formlerna som alla excel-elever måste känna till. Följ denna artikel för att lära dig och utforska.
Exempel på avancerad formel i Excel
Låt oss förstå hur du använder de avancerade formlerna i Excel med några exempel.
Du kan ladda ner denna avancerade formel Excel-mall här - avancerade formler Excel mallExempel 1 - Partiell VLOOKUP-funktion
Du måste ha stött på en situation där VLOOKUP kastar upp ett fel även om det fanns en liten miss-match i uppslagningsvärdet. Om du till exempel letar efter lönen för namnet Abhishek Sinha och om du bara har Abhishek kan VLOOKUP inte hämta informationen.
Men med asterisk-tecken i vardera änden av sökvärdet kan vi hämta data för partiellt uppslagningsvärde.
I tabell 1 har jag fullt namn och lön, men i tabell 2 har jag bara partiella namn och behöver hitta lönen för varje anställd.
Steg 1: Öppna VLOOKUP-formeln i cell E3. Innan du väljer uppslagningsvärdet, placera en asterisk (*) på vardera sidan av uppslagningsvärdet.
Steg 2: Som vanligt kan du fylla i VLOOKUP-formeln nu så får vi resultaten.
Efter användning av ovanstående formel visas utgången nedan.
Samma formel används i andra celler.
Exempel 2 - COUNTIFS med operatörssymboler
Du måste ha använt COUNTIF & IFS-funktionen för att räkna sakerna i listan. Vi kan också räkna baserat på operatörssymboler som större än (>) mindre än (<) och lika tecken (=).
Nu kan du titta på nedanstående data till exempel. Om du vill räkna det totala antalet fakturor för regionen SYD efter datumet 10 januari 2018, hur räknar du?
Exempel 3 - IF-tillstånd med AND & OR-villkor
Logiska funktioner är en del av våra dagliga aktiviteter. Du måste behärska dem för att gå vidare till nästa nivå. Om du beräknar bonus baserad på flera villkor måste du häcka OCH eller ELLER villkor med IF-villkor för att få jobbet gjort.
Anta att du behöver beräkna bonusbeloppet för varje avdelning baserat på den avdelning och år av tjänst du behöver för dessa funktioner. Baserat på kriterierna nedan måste vi beräkna bonusen.
Om tjänsten är över fyra år och avdelningen är antingen försäljnings- eller supportbonus är 50000 eller annars är bonusen 25000.
Använd formeln nedan för att få bonusbeloppet.
Efter att ha använt ovanstående formel, visas utgången nedan.
Samma formel som gäller i cell E3 till E9.
Exempel 4 - TEXT-funktion för att göra dig dynamisk
Låt oss säga att du upprätthåller en daglig försäljningstabell och att du måste uppdatera tabellen varje dag. I början av tabellen har du en rubrik som säger "Konsoliderade försäljningsdata från DD-MM-ÅÅÅÅ till DD-MM-ÅÅÅ". När och när tabellen uppdateras måste du ändra rubrikdatum. Är det inte en frustrerande uppgift att göra samma sak om och om igen? Vi kan göra denna rubrik dynamisk genom att använda funktionen TETX, MIN och Max tillsammans med sammankopplad operatörssymbol ampersand (&).
Exempel 5 - INDEX + MATCH + MAX för att hitta den högsta säljaren
Du har en lista över säljare och de försäljningar de gjorde mot deras namn. Hur säger du vem som är den bästa eller högsta säljaren på listan? Naturligtvis har vi flera andra tekniker för att berätta resultatet men denna funktion kan returnera den högsta säljaren från partiet.
Använd funktionen nedan för att få det högsta säljarens namn.
Efter att ha använt ovanstående formel, visas utgången nedan.
Exempel 6 - Få ett antal unika värden från listan
Om du har många duplicerade värden och måste berätta hur många unika värden finns det i hur berättar du det? Du kan berätta genom att ta bort duplikatvärdet, men detta är inte det dynamiska sättet att berätta det unika värdet.
Med hjälp av denna array-funktion kan vi berätta de unika värdena från partiet.
Använd formeln nedan för att få en lista över unika värden.
Exempel 7 - Använd Namnet Range för att göra Dropdown dynamisk
Om du ofta arbetar med en rullgardinslista och gör rullgardinslistan uppdaterad måste du gå tillbaka till källlistan för att radera eller lägga till värden. Efter det måste du komma tillbaka till rullgardinscellerna och uppdatera området för rullgardinslistan igen.
Men om du kan skapa namngivna intervall för din rullgardinslista, kan du göra rullgardinsmen dynamisk och aktuell.
Skapa ett namnområde som visas i bilden nedan.
Gå nu till rullgardinscellen och öppna rullgardinsmenyn.
I källtryck på F3-tangenten visas alla definierade namn, välj namnet på din rullgardinslista.
Ok, en rullgardinslista är klar och den kommer att uppdatera värdena automatiskt när det ändras i rullgardinslistan.
Exempel 8 - Bli av med felvärden med IFERROR-funktion
Jag är säker på att du har upplevt felvärden när du arbetar med VLOOKUP, Division beräkningar. Att hantera dessa felvärden är en tråkig uppgift. Men vi kan bli av med dessa felvärden genom att använda IFERROR-funktionen i formeln.
Efter att ha använt ovanstående formel, visas utgången nedan.
Samma formel som används i andra celler.
Exempel 9 - Använd PMT-funktionen för att skapa ditt eget EMI-diagram
Numera är EMI-alternativet inte en konstig sak för oss alla. I Excel kan vi uppskatta vårt eget EMI-diagram med hjälp av PMT-funktion. Följ stegen nedan för att skapa ditt eget diagram.
Använd nedan formel i cell B4 för att få EMI-beloppet.
Exempel 10 - INDEX + MATCH som ett alternativ till VLOOKUP-funktionen
Jag hoppas att du är medveten om begränsningen av VLOOKUP-funktionen. En viktig begränsning är att VLOOKUP kan hämta data från vänster till höger, inte från höger till vänster. Inte hela tiden är data välorganiserade och redo att användas. Ofta är värdekolumnen vi letar efter till vänster om uppslagningsvärdet, så VLOOKUP misslyckas med att hjälpa i dessa fall.
En kombination av INDEX + MATCH-funktionen fungerar som ett alternativ till VLOOKUP-funktionen.
Efter användning av ovanstående formel visas utgången nedan.
Samma formel används i andra celler.
Baserat på produkt-ID måste vi hämta försäljningsvärden. I huvudtabellen finns produkt-ID till höger om försäljningskolumnen. Så VLOOKUP kan inte hämta data. Använd formeln nedan för att hämta data.
Rekommenderade artiklar
Detta är en guide till avancerade formler i Excel. Här diskuterar vi hur man använder avancerade formler i Excel tillsammans med praktiska exempel och nedladdningsbar Excel-mall. Du kan också gå igenom våra andra föreslagna artiklar -
- Hur använder jag Excel INDEX-funktion?
- Avancerat Excel | Databasfunktion
- Exempel på TRIM-formel i Excel
- Guide till Excel OFFSET-formel