Avancerade Excel-formler | Lista över topp 10 avancerade Excel-funktioner

Lista över topp 10 avancerade Excel-formler och funktioner

Du kan ladda ner den här avancerade Excel-formelmallen här - Avancerad Excel-formelmall

# 1 - VLOOKUP-formel i Excel

Denna avancerade Excel-funktion är en av de mest använda formlerna i Excel. Det beror främst på enkelheten i denna formel och dess tillämpning när man letar upp ett visst värde från andra tabeller som har en gemensam variabel över dessa tabeller. Antag att du har två tabeller som innehåller information om ett företags anställdas lön och namn med anställds ID som en primär kolumn. Du vill få lönen från tabell B i tabell A.

Du kan använda VLOOKUP enligt nedan.

Det kommer att resultera i tabellen nedan när vi tillämpar denna avancerade excelformel i andra celler i kolumnen Anställd lön.

Dra formeln till resten av cellerna.

Det finns tre stora avgränsningar av VLOOKUP:

Du kan inte ha en primär kolumn till höger om den kolumn som du vill fylla i värdet för från en annan tabell. I det här fallet kan kolumnen för anställd lön inte vara före anställd-ID.
När det gäller dubblerade värden i den primära kolumnen i tabell B fylls det första värdet i cellen.
Om du infogar en ny kolumn i databasen (Ex Sätt in en ny kolumn före anställdes lön i tabell B), kan utdata för formeln vara annorlunda baserat på en position som du nämnde i formeln (I ovanstående fall kan utdata skulle vara tom)

# 2 - INDEX-formel i Excel

Denna avancerade excelformel används för att få värdet på en cell i en given tabell genom att ange antalet rader, kolumn eller båda. Ex. För att få namnet på en anställd vid den 5: e observationen, nedan är data.

Vi kan använda den avancerade excelformeln enligt nedan:

Samma INDEX-formel kan användas för att få värde längs raden. När du använder både rad- och kolumnnummer skulle syntaxen se ut ungefär så här:

Ovanstående formel skulle returnera "Rajesh Ved".

Obs: Om du infogar en ny rad i data vid 5: e raden, skulle formeln returnera "Chandan Kale". Följaktligen skulle produktionen bero på alla förändringar som händer i datatabellen över tiden.

# 3 - MATCH-formel i Excel

Denna Excel-avancerade formel returnerar raden eller kolumnnumret när det finns en matchning mellan en viss sträng eller ett antal i det angivna intervallet. I exemplet nedan försöker vi hitta positionen för “Rajesh Ved” i kolumnen Anställd namn.

Formeln skulle vara enligt nedan:

MATCH-funktionen skulle returnera 5 som värdet.

Det tredje argumentet används för exakt matchning. Du kan också använda +1 och -1 baserat på dina krav.

Obs: Man kan kombinera INDEX och MATCH för att övervinna begränsningen av VLOOKUP.

# 4 - OM OCH Formel i Excel

Det finns många tillfällen när man behöver skapa flaggor baserat på vissa begränsningar. Vi känner alla till den grundläggande syntaxen för IF. Vi använder denna avancerade excel IF-funktion för att skapa ett nytt fält baserat på vissa begränsningar för ett redan existerande fält. Men vad händer om vi måste använda flera kolumner när vi skapar en flagga. Ex. I nedanstående fall vill vi flagga alla anställda vars lön är större än 50K men anställds ID större än 3.

Vi skulle använda IF OCH i sådana fall. Se nedan för skärmdumpen för detsamma.

Resultatet skulle returneras som 0.

Vi kan ha många villkor eller begränsningar för att skapa en flagga baserad på flera kolumner med AND.

# 5 - OM ELLER Formel i Excel

På samma sätt kan vi också använda ELLER-funktionen i excel istället för OCH om vi bara behöver uppfylla ett av villkoren för många.

I ovanstående fall, om något av ett villkor blir nöjt, kommer vi att fylla i celler som 1 annan 0. Vi kan också ersätta 1 eller 0 med vissa strängar med dubbla citat ("").

# 6 - SUMIF-formel i Excel

I vissa analyser kan du behöva filtrera några observationer när du använder summan eller räknarfunktionen. I sådana fall är denna avancerade excel SUMIF-funktion i excel till vår räddning. Det filtrerar alla observationer baserat på vissa villkor i denna avancerade excelformel och summerar dem. Ex. Vad händer om vi vill veta summan av lönerna endast för de anställda som har anställnings-ID som är större än 3.

Genom att använda SUMIFS-formeln:

Formeln returnerar resultaten som 322000.

Vi kan också räkna antalet anställda i organisationen som har anställnings-ID större än 3 när vi använder COUNTIF istället för SUMIF.

# 7 - CONCATENATE Formula i Excel

Denna excel-avancerade funktion är en av formlerna som kan användas med flera varianter. Denna avancerade excelformel hjälper oss att sammanfoga flera textsträngar i en textsträng. Ex, om vi vill visa anställds-ID och anställds namn i en enda kolumn.

Vi kan använda denna CONCATENATE-formel bara för att göra det.

Ovanstående formel kommer att resultera i “1Aman Gupta”.

Vi kan ha en variant till genom att sätta ett bindestreck mellan ID och NAME. Ex. CONCATENATE (B3, ”-“, C3) kommer att resultera i “1-Aman Gupta”. Vi kan också använda detta i VLOOKUP när LOOKUP i excel-värde är en blandning av mer än en variabel.

# 8 - VÄNSTER, MIDT och HÖGER formel i Excel

Vi kan använda den här avancerade excelformeln Om vi vill extrahera en viss delsträng från en given sträng. De nämnda formlerna kan användas baserat på våra krav. Ex. Om vi vill extrahera de första 5 tecknen från anställds namn kan vi använda VÄNSTER-formeln i excel med kolumnnamnet och den andra parametern som 5.

Resultatet ges nedan:

Tillämpningen av RIGHT-formeln i Excel är också densamma, det är bara att vi skulle titta på karaktären från höger om strängen. I fallet med en MID-funktion i Excel måste vi dock ange startpositionen för den önskade textsträngen och strängens längd.

# 9 - OFFSET-formel i Excel

Denna avancerade excelfunktion med en kombination av andra funktioner som SUM eller AVERAGE kan vara användbar för att ge en dynamisk beräkning av beräkningarna. Det används bäst i fall när vi infogar kontinuerliga rader i en befintlig databas. OFFSET Excel ger oss ett intervall där vi behöver nämna referenscell, antal rader och kolumner. Ex. Om vi vill beräkna genomsnittet av de fem första anställda i företaget där vi har en lön för anställda sorterade efter anställd ID kan vi göra följande. Nedanstående beräkning ger oss alltid löner.

Det ger oss summan av lönerna för de fem första anställda.

# 10 - TRIM-formel i Excel

Den här avancerade excelformeln används för att rensa bort de viktiga utrymmena från texten. Ex. Om vi vill ta bort mellanslag i början av något namn kan vi använda det genom att använda TRIM-funktionen i Excel enligt nedan: