Kombinerad användning av sumif (vlookup)
Sumif med VLOOKUP är en kombination av två olika villkorliga funktioner, SUMIF används för att summera cellerna baserat på något villkor som tar argument av intervallet som har data och sedan kriterierna eller villkoret och cellerna att summera, istället för de kriterier vi använder VLOOKUP som kriterier när det finns en stor mängd data tillgänglig i flera kolumner.
SUMIF är en funktion som presenteras i Excel från 2007-versionen för att summera de olika värden som matchar kriterierna. VLOOKUP är en av de bästa formlerna för att samla in data från andra tabeller. När flera villkor och kolumner finns, används sumif (vlookup) för att utföra flera beräkningar på excel-arket. Nackdelen med att SUMIF-funktionen returnerar det enda numret övervinns med VLOOKUP. VLOOKUP hjälper till att returnera alla typer av data från en tabell baserat på matchade kriterier.
Förklaring
SUMIF-funktion: Det är en trigonometri och matematisk funktion att summera värdena när det fastställda villkoret är sant. Summan uppnås baserat på endast ett kriterium.
När vi hanterar SUMIF-funktionen i Excel används följande formel
- Område: Det är intervallet för cellerna som används för att bedöma de fastställda kriterierna
- Kriterier: Det är villkoret att summera värdena. Det kan vara en cellreferens, nummer och en annan excelfunktion. När vi vill kombinera SUMIF och VLOOKUP kommer vlookup-funktionen att anges i stället för kriterierna
- Sum Range: Det är det cellområde som anges för att summera de numeriska värdena.
Nu är formeln modifierad till
Formel = SUMIF (Område, Vlookup (uppslagsvärde, tabellmatris, kolumnindexnummer, (intervalluppslag)), (summaintervall))
- Sökningsvärde: Det anger värdet som ska sökas i en tabell. Det kan vara en referens eller ett värde.
- Tabellmatris: Det är intervallet för tabellen som innehåller två eller fler än två kolumner.
- Kolumnindexnummer: Det är det relativa indexet för en kolumn att specificera för att returnera nödvändiga data från en viss kolumn.
- (Områdesökning): Det är antingen 0 eller 1 att ange om det exakta värdet ska returneras eller det ungefärliga värdet ska returneras. Men det är valfritt för en användare. 0 anger en exakt matchning och 1 anger en ungefärlig matchning.
Hur använder jag SUMIF med VLOOKUP-funktionen?
Den kombinerade användningen av sumif (vlookup) är till hjälp vid sökning av data baserat på de enskilda kriterierna. Dessa används på många sätt i Excel för att utföra beräkningarna genom att söka efter data. Dessa används kollektivt i affärsmiljön för att utföra olika uppgifter för att fatta bra beslut. För att effektivt använda dessa funktioner tillsammans,
Först ska SUMIF-funktionen anges med de två metoderna.
Första metoden: Formeln måste skrivas från tangentbordet enligt bilden nedan.
Andra metoden: SUMIF-funktionen kan infogas från fliken "Formel" som visas i bilden.
När du har angett SUMIF-funktionen anges formeln för VLOOKUP inuti SUMIF-funktionen genom att ersätta elementet 'Kriterier'. Alla parametrar i VLOOKUP inklusive sökningsvärde, tabellmatris, kolumnens indexnummer och intervalluppslag. Dessa ska läggas inom parentes för att undvika fel med formeln. Värdeintervallet som ska summeras ingår i summeringselementen i SUMIF-funktionen. Äntligen tryckte CTRL-, SHIFT- och ENTER-tangenterna tillsammans för att underlätta värdena som en matris.
Exempel
Exempel # 1 - Användning av sumif (vlookup) tillsammans för att bestämma något värde
Detta exempel visar hur man använder sumif (vlookup) tillsammans för att hitta summan av försäljningen under samma månad under olika år. Följande data beaktas för detta exempel som visas på skärmdumpen.
Uppslagstabellen som visas visas som nämnts nedan. Den innehöll referensvärden för månaderna januari till december.
Efter att ha lagt in data i huvudtabellen och uppslagstabellen används SUMIF-funktionen för att bestämma den totala försäljningen som genereras under olika månader på ett år. Här är Lookup-värdet som övervägs månaden. Formeln som kombinerar sumif (vlookup) visas som,
Den totala försäljningen i januari månad bestäms till 17263,3 . När vi ändrade utseendevärdet till en annan månad genereras respektive totala försäljning.
Exempel # 2 - Bestämma summan baserat på matchande kriterier i olika arbetsblad
I det här exemplet tas uppslagstabellen och huvudtabellen i olika ark snarare än ett enda ark. Uppgifterna om uppslagstabellen visas i skärmdumpen nedan.
Huvudtabelldata visas i skärmdumpen nedan.
För att bestämma den totala försäljningen tas namnet på säljaren som uppslagsvärde och anställdas ID används för referensändamål. Formeln anges enligt nedan och få förändringar observeras i formeln jämfört med det första exemplet.
I det här exemplet nämns bara Lookup_table istället för att välja en uppslagsmatris. Genom att trycka på de tre tangenterna inklusive CTRL, SHIFT och ENTER produceras exakta resultat.
När säljarens namn ändras till namn som nämns i uppslaget varieras summan av försäljningen och ger ett nytt resultat.
Fördelar
Följande är fördelarna med att använda dessa funktioner.
- Vi kan enkelt extrahera värdena från en annan tabell för att utföra beräkningar.
- Bestämma summan av värden som presenteras i ett intervall som uppfyller de kriterier som nämns i olika aspekter av ett företag.
Saker att komma ihåg
- När du använder VLOOKUP-funktionen bör kolumnindexnumret inte vara lägre än 1 för att undvika fel.
- Index bör ges till uppslagstabellkolumnerna genom att indikera med siffrorna 1, 2, 3 och så vidare.
- CTRL + SKIFT + ENTER i excel ska användas istället för att ange nyckel eftersom VLOOKUP anges som en matrisformel.
- Definiera två tabeller inklusive Main och Lookup krävs för att extrahera värden och bestämma summan av arrayvärdena.
- SUMIF ger exakta resultat för endast numeriska data, det fungerar inte för en annan typ av data.
