I den här artikeln lär vi oss hur du använder OFFSET -funktionen i Excel.
Vad är OFFSET -funktion?
Offset -funktion är en informationshämtningsfunktion. Om du har en enorm tabell som innehåller vissa data och du vill hämta data från den, är det bästa sättet att göra det att infoga en förskjutningsformel för tabellen i det kalkylbladet eller ett annat blad i samma arbetsbok. Så snart du har skapat offsethämtningstabellen måste du helt enkelt ange data till exempel "månad" och du kommer att få "försäljning" eller "inkomst" för den månaden från den tabellen.
Att gå igenom tabellen är ett annat alternativ, men skulle du överväga alternativet om du har ark och datablad som alla innehåller en 1000 -kolumndatatabell? Det är här offset kombineras med andra funktioner.
Begreppet vridbord kommer från offset. En pivottabell är en allmän tabell och från den specifika kombinationen av data och diagram erhålls när och när det behövs. Även dynamiska tabeller kan skapas på samma sätt. För dessa tabeller måste du skapa excel -offsetformeln
Syntax för OFFSET
| = OFFSET (referens, rader, kolumner, [höjd], [bredd]) |
Rader - du ber excel att flytta antalet rader för att få informationen. I det här fallet måste den flytta runt bordet och lämna bara ett tomt markerat med ett komma (,). Annars, för att flytta en kolumn framåt ge värde som 1 och för att flytta en kolumn före ge ett värde -1.
Kolumner - detta är för att driva funktionen till antalet kolumner. Värdesystemet är samma som för rad.
Höjd - bordets höjd, i detta fall A11.
Bredd - bordets bredd, i detta fall D11.
När du har ställt in offset -funktionen är det viktigt att köra en test för att undanröja eventuella fel.
Låt oss ta en tabell som innehåller namn, e -post -id, födelsedatum och ålder. Tabellen börjar vid A1 -cell och innehåller rubriken "Namn". Data körs för säg A11. Och raderna löper till D1. Hela bordet körs A1: D11. Du vill ha ett hämtningssystem som får dig namnet när du anger e -post -id, eller e -post -id när du anger namnet tillsammans med andra detaljer. Du skapar en formel genom att uppfylla de fem argumenten.
Referenspunkt är cellen från vilken uppslagningen ska börja. I detta fall bör det vara A2. Det är den allmänna standarden.
Exempel:
Till exempel anger du B4 och offset rad 0, och offset kolumn 1 returneras värde från C4. Förvirrande? Låt oss ta några fler exempel. Sista exemplet på OFFSET -funktionen berättar hur man summerar dynamiskt.
OFFSET -funktion för att få värde från höger och vänster på en cell
Vi har det här bordet.
Nu om jag vill få värde från 2 cell direkt till B2 (vilket betyder D2). Jag kommer att skriva denna OFFSET -formel:
| = OFFSET (B2,0,2) |
OFFSET -funktionen flyttar 2 celler till höger om cell B4 och returnerar dess värde. Se bild nedan
Om jag vill få värde från 1 cell kvar till B2 (vilket betyder A2). Jag kommer att skriva denna OFFSET -formel:
| = OFFSET (B2,0, -1) |
Minustecken (-) anger att förskjutningen för att flytta omvänd.
OFFSET -funktion för att få värde underifrån och över en cell
Så igen i tabellen ovan, om jag vill få värde från tvåceller till B3 (vilket betyder B5). Jag kommer att skriva denna OFFSET -formel:
| = OFFSET (B2,2,0) |
Om jag vill få värde från 1 cell kvar till B2 (vilket betyder A2). Jag kommer att skriva denna OFFSET -formel:
| = OFFSET (B2, -2,0) |
- Proffstips: Behandla en OFFSET som en grafpekare där referensen är ursprung och rad och kolumn är x- och y -axeln.
OFFSET -funktion för att summera intervall dynamiskt
Låt oss se detta exempel.
I tabellen ovan Total rad i slutet av tabellen. Med tiden kommer du att lägga till rader i den här tabellen. Då måste du ändra summan i formeln. Här kan vi ta hjälp av OFFSET -funktionen för att summera dynamiskt. För närvarande i cell C11 har vi = SUMMA (C2: C10). Ersätt detta med nedanstående formel.
| = SUMMA (C2: OFFSET (C11, -1,0)) |
Denna formel tar bara den första dataraden och summerar sedan intervallet över den totala raden.
Offset för att få Array
OFFSET -funktionen har två valfria argument, [höjd] och [bredd]. Ta dem inte för givet. När OFFSET -funktionen tillhandahålls [höjd] och [bredd] argument returnerar den en tvådimensionell matris. Om bara en av dem skickas som argument returneras den på en dimensionell uppsättning värden.
Om du skriver in denna formel i någon cell:
| = SUMMA (OFFSET (C1,1,0,9,3)) |
Det summerar värden i intervallet från C2 till 9 rader nedan och 3 kolumner till höger.
OFFSET (C1,1,0,9,3): Detta kommer att översättas till {8,8,7; 6,1,2; 8,5,8; 5,1,5; 8,3,5; 8,3,9; 8,9,2; 3,5 , 4; 6,6,5}.
Så sammanfattningsvis är offset en mycket användbar funktion av excel som kan hjälpa dig att lösa många kvävda trådar. Låt mig veta hur du använder OFFSET -funktionen i din formel och var det hjälpte dig mest.
Sökfunktion är samma som offset men använder funktioner som Match, Counta och IF för att arbeta med en dynamisk tabell.
Att använda offset i Excel kräver skicklighet och goda kunskaper om Excels funktioner och hur de fungerar tillsammans. Att ta bort det utan några fel är det största hindret.
Här är alla observationsanteckningar om användning av formeln.
Anmärkningar:
- Denna formel fungerar med både text och siffror.
- Det finns fem argument för offset som måste uppfyllas för att få en felfri information. Om du skriver en felaktig formel uppstår ett Ref -fel.
Hoppas du förstod vad som är Excel Offset -funktion och hur du använder den i Excel. Utforska fler artiklar om Excel -sökning och matchningsvärden med hjälp av formler här. Om du gillade våra bloggar, dela den med dina vänner på Facebook. Och du kan också följa oss på Twitter och Facebook. Vi vill gärna höra från dig, låt oss veta hur vi kan förbättra, komplettera eller förnya vårt arbete och göra det bättre för dig. Skriv till oss på e -postwebbplatsen.
VLOOKUP -funktion för att beräkna betyg i Excel : Att beräkna betyg IF och IFS är inte de enda funktionerna du kan använda. VLOOKUP är mer effektiv och dynamisk för sådana villkorliga beräkningar. För att beräkna betyg med VLOOKUP kan vi använda denna formel.
17 saker om Excel VLOOKUP : VLOOKUP används oftast för att hämta matchade värden men VLOOKUP kan göra mycket mer än så. Här är 17 saker om VLOOKUP som du bör veta för att använda effektivt.
SÖK den första texten från en lista i Excel : VLOOKUP -funktionen fungerar bra med jokertecken. Vi kan använda detta för att extrahera det första textvärdet från en given lista i excel. Här är den generiska formeln.
LOOKUP -datum med sista värdet i listan : För att hämta datumet som innehåller det sista värdet använder vi LOOKUP -funktionen. Denna funktion söker efter cellen som innehåller det sista värdet i en vektor och använder sedan den referensen för att returnera datumet.
Så här hämtar du det senaste priset i Excel : Det är vanligt att uppdatera priser i alla företag och att använda de senaste priserna för köp eller försäljning är ett måste. För att hämta det senaste priset från en lista i Excel använder vi LOOKUP -funktionen. LOOKUP -funktionen hämtar det senaste priset.
Populära artiklar:
50 Excel -genväg för att öka din produktivitet : Bli snabbare på din uppgift. Dessa 50 genvägar får dig att arbeta ännu snabbare med Excel.
Hur man använder IF -funktionen i Excel : IF -satsen i Excel kontrollerar villkoret och returnerar ett specifikt värde om villkoret är SANT eller returnerar ett annat specifikt värde om det är FALSKT.
Så här använder du VLOOKUP -funktionen i Excel : Detta är en av de mest använda och populära Excel -funktionerna som används för att leta upp värde från olika intervall och ark.
Hur man använder funktionen RÄNKNING i Excel : Räkna värden med villkor med denna fantastiska funktion. Du behöver inte filtrera dina data för att räkna specifika värden. Countif -funktionen är avgörande för att förbereda din instrumentpanel.
Hur man använder SUMIF -funktionen i Excel : Detta är en annan viktig instrumentpanel. Detta hjälper dig att summera värden på specifika förhållanden.
