Komplexa formler i Excel (innehållsförteckning)
- Introduktion till komplexa formler i Excel
- Välj Function Serve as Lookup-funktion
- VLOOKUP MED VÄLJ
- OM MED OCH & ELLER funktion
Introduktion till komplexa formler i Excel
Excel kommer med många användbara formler, vi har en kategori ekonomiska, logiska, text, datum & tid, uppslag & referenser och matematik & trigg ”. Varje enskilda formler är tillräckligt bra för mellananvändare där de möter raka situationer, men när du går vidare till nästa nivå behöver din skicklighet ta höjden så att i detta avseende spelar mer komplexa formler i Excel spelar en viktig roll för att lösa komplexa situationer. Med denna artikel i åtanke tar vi dig igenom några av de viktiga och välbehövliga komplexa formlerna. Läs vidare.
Välj Function Serve as Lookup-funktion
Inte många av er har använt VÄLJ-funktionen, men det är mycket användbart för att fungera som uppslagningsfunktion också.
Du kan ladda ner denna komplexa formler Excel-mall här - Komplexa formler Excel-mall. Se till exempel nedanstående data.
Vi har uppgifter om kunder varje månad under denna tabell, vi har varje kundtabell. I den här tabellen, i cell B13, har jag skapat en rullgardinslista för månaden, så att när denna listruta ändras ska vi få summan av den valda månaden.
Skapa en tabell som nedan.
Applicera VLOOKUP på cellen D13.
Öppna nu VÄLJ-funktionen i cell B14.
Välj INDEX-nummer som en VLOOKUP-cell och gör det till en absolut referens.
Välj nu för varje argument varje månad försäljningsnummer.
Stäng konsolen och tryck på Enter-tangenten.
Kopiera och klistra nu in formeln också till nedanstående celler.
Nu VÄLJ formeln visar numren på den valda månaden.
VLOOKUP med VÄLJ-funktion
Vi har sett tradition VLOOKUP men vi kan också kombinera den med VÄLJ-funktionen. Se till exempel data nedan.
I cellerna A1 till B11 har vi en försäljningstabell och i cell D2 har jag skapat rullgardinsmenyn över månader och i cell E2 måste vi tillämpa VLOOKUP och få det valda månadsförsäljningsvärdet. Men problemet med att tillämpa VLOOKUP är en datastruktur, där uppslagningsvärdet är till höger om resultatkolumnen, så VLOOKUP kan inte hämta data från höger till vänster, i det här fallet kan vi använda VÄLJ-funktionen.
För att välja tabelluppsättningen måste vi öppna CHOOSE-funktionen här.
I argumentet INDEX-nummer i blomma anger parentes 1 & 2. Välj nu det första intervallet som månadskolumn.
Välj kolumnen Försäljning som det andra intervallet.
Nämn kolumnindexnummer som 2 och intervalluppslag som 0.
Så vi fick resultatet för månaden april från bordet. Så när du inte har en exakt datastruktur kan vi välja, VÄLJ funktion.
OM MED OCH & ELLER funktion
Logiska funktioner är fulla av logik för att beräkna saker. Titta på nedanstående exempeldata.
Från tabellen ovan måste vi beräkna bonusen utifrån villkoren nedan.
- Om tjänsten är mer än 1, 5 år och om avdelningen antingen är försäljning eller support så är bonusen 1000.
- Om något annat är bonusen 3000.
För att beräkna bonus måste vi tillämpa IF, AND, & OR tillsammans. Öppet IF-tillstånd i cell D2.
Vi måste testa flera villkor, öppna OCH-funktionen.
Nu är nästa logiska antingen försäljning eller support så för detta öppna ELLER villkor.
Testa om titeln är försäljning eller support.
Så om alla medföljande villkor är Sanna bör bonusen vara 10000.
Om de medföljande logiska testen är FALSE är bonusen 3000.
Stäng konsolen och tryck på Enter-tangenten för att få resultatet.
Kopiera och klistra in formeln till cellerna nedan.
Så enligt villkoret fick vi resultatet. Om tjänsten är> 1, 5 år och titeln är antingen försäljning eller support får vi 10000 eller annars får vi 3000.
Komplex formel med jokertecken
VLOOKUP kräver ett exakt uppslagningsvärde som ska matchas för att hämta data. Detta är det traditionella slogan men vi kan fortfarande hämta data med hjälp av partikeluppslagningsvärde. Om exempelvis uppslagningsvärdet är "VIVO" och i huvudtabellen om det är "VIVO Mobile" kan vi fortfarande matcha med jokertecken. Vi kommer att se ett av exemplen nu, nedan är exempeldata.
Vi har uppslagstabell i kolumn A. I kolumn C har vi uppslagningsvärden, dessa uppslagvärden är inte exakt samma som uppslagstabellvärdena. Så vi kommer att se hur du använder VLOOKUP med vilda kort.
Öppna först VLOOKUP-funktionen i cellen D1.
Det första argumentet är uppslagningsvärdet . Ett av problemen med uppslagningsvärdet här är att vi inte har en exakt matchning så vi måste bifoga detta uppslagsvärde med en asterisk före och efter uppslagningsvärdet.
Här har vi använt två asterisk-tecken “*” & C2 & ”*”. Här indikerar asterisk något mellan jokertecken som ska matchas och returnera det relaterade resultatet.
Även om vi bara hade ”Infosys” asterisk-karaktär matchade värdet i uppslagstabellen och returnerade exakt resultatet som ”Infosys Ltd”.
På cell D6 fick vi på samma sätt felvärde som #VÄRDE! eftersom det inte finns något ord "Mintra" i uppslagstabellen.
Rekommenderade artiklar
Detta är en guide till komplexa formler i Excel. Här diskuterar vi några av de viktiga och välbehövliga komplexa formlerna med exempel och Excel-mall. Du kan också gå igenom våra andra föreslagna artiklar för att lära dig mer -
- SÖK Formel i Excel (tips & tricks)
- Handledning om Excel NORMSINV-funktion
- Namnbox och dess användningsområden i Excel
- Sammanfogade strängar i Excel