Excel OFFSET -funktion (exempel + video)
När ska man använda Excel OFFSET -funktion
Excel OFFSET -funktionen kan användas när du vill få en referens som kompenserar angivet antal rader och kolumner från startpunkten.
Vad den returnerar
Den returnerar referensen som OFFSET -funktionen pekar på.
Syntax
= OFFSET (referens, rader, cols, [höjd], [bredd])
Inmatningsargument
- referens - Referensen som du vill kompensera från. Detta kan vara en cellreferens eller ett område av intilliggande celler.
- rader - antalet rader som ska kompenseras. Om du använder ett positivt tal förskjuts det till raderna nedan, och om ett negativt tal används, förskjuts det till raderna ovan.
- cols - antalet kolumner som ska kompenseras. Om du använder ett positivt tal förskjuts det till kolumnerna till höger, och om ett negativt tal används, förskjuts det till kolumnerna till vänster.
- [höjd] - detta är ett tal som representerar antalet rader i den returnerade referensen.
- [bredd] - detta är ett tal som representerar antalet kolumner i den returnerade referensen.
Förstå grunderna i Excel OFFSET -funktion
Excel OFFSET -funktionen är möjligen en av de mest förvirrande funktionerna i Excel.
Låt oss ta ett enkelt exempel på ett schackspel. Det finns en bit i Schack som kallas en Rook (även känd som ett torn, markis eller rektor).
[Bild med tillstånd: Underbar Wikipedia]
Nu, som alla andra schackpjäser, har en Rook en fast väg som den kan röra sig på på brädet. Det kan gå rakt (till höger/vänster eller upp/ner på brädet), men det kan inte gå diagonalt.
Anta till exempel att vi har ett schackbräde i Excel (som visas nedan), i en given ruta (D5 i det här fallet) kan Rook bara gå i de fyra markerade riktningarna.
Om jag ber dig att ta Rook från dess nuvarande position till den som är markerad med gult, vad kommer du att säga till tornet?
Du kommer att be den ta två steg nedåt och två steg till höger … eller hur?
Och det är en nära närhet till hur OFFSET -funktionen fungerar.
Låt oss nu se vad detta betyder i Excel. Jag vill börja med cellen D5 (som är där Rook är) och sedan gå två rader ner och två kolumner till höger och hämta värdet från cellen där.
Formeln skulle vara:
= OFFSET (från var ska man börja, hur många rader ner, hur många kolumner till höger)
Som du kan se är formeln i exemplet ovan i cell J1 = OFFSET (D5,2,2).
Det började med D5 och gick sedan två rader ner och två kolumner till höger och nådde cellen F7. Det returnerade sedan värdet i cell F7.
I exemplet ovan tittade vi på OFFSET -funktionen med tre argument. Men det finns ytterligare två valfria argument som kan användas.
Låt oss titta på ett enkelt exempel här:
Antag att du vill använda referensen till cell A1 (i gult) och att du vill referera till hela intervallet markerat med blått (C2: E4) i en formel.
Hur skulle du göra det med ett tangentbord? Du skulle först gå till cell C2 och sedan markera alla celler i C2: E4.
Låt oss nu se hur du gör detta med hjälp av OFFSET -formeln:
= OFFSET (A1,1,2,3,3)
Om du använder den här formeln i en cell returnerar den #VÄRDE! fel, men om du kommer in i redigeringsläget och väljer formeln och trycker på F9 ser du att det returnerar alla värden som är markerade med blått.
Låt oss nu se hur denna formel fungerar:
= OFFSET (A1,1,2,3,3)
- Det första argumentet är cellen där den ska börja.
- Det andra argumentet är 1, som uppmanar Excel att returnera en referens som har förskjutits med 1 rad.
- Det tredje argumentet är 2, som uppmanar Excel att returnera en referens som har förskjutits med två kolumner.
- Det fjärde argumentet är 3. Detta anger att referensen ska täcka tre rader. Detta kallas höjdargumentet.
- Det femte argumentet är 3. Detta anger att referensen ska täcka tre kolumner. Detta kallas breddargumentet.
Nu när du har referensen till ett cellområde (C2: E4) kan du använda den inom en annan funktion (som SUMMA, RÄKNA, MAX, GENomsnitt).
Förhoppningsvis har du en bra grundläggande förståelse för att använda Excel OFFSET -funktionen. Låt oss nu titta på några praktiska exempel på att använda OFFSET -funktionen.
Excel OFFSET -funktion - Exempel
Här är två exempel på hur du använder Excel OFFSET -funktionen.
Exempel 1 - Hitta den senast fyllda cellen i kolumnen
Anta att du har några data i en kolumn enligt nedan:
För att hitta den sista cellen i kolumnen, använd följande formel:
= OFFSET (A1, COUNT (A: A) -1,0)
Denna formel förutsätter att det inte finns några värden förutom de som visas och att dessa celler inte har en tom cell i den. Det fungerar genom att räkna det totala antalet celler som fylls och kompenserar cellen A1 därefter.
Till exempel, i det här fallet, finns det 8 värden, så RäKNING (A: A) returnerar 8. Vi förskjuter cellen A1 med 7 för att få det sista värdet.
Exempel 2 - Skapa en dynamisk nedrullning
Du kan utöka konceptvisningarna i exempel 1 för att skapa dynamiska namngivna intervall. Dessa kan vara användbara om du använder de namngivna intervallerna i formler eller för att skapa rullgardinsmenyer och du sannolikt kommer att lägga till/ta bort data från referensen som gör det namngivna intervallet.
Här är ett exempel:
Observera att rullgardinsmenyn justeras automatiskt när året läggs till eller tas bort.
Detta händer eftersom formeln som används för att skapa rullgardinsmenyn är dynamisk och identifierar alla tillägg eller raderingar och justerar intervallet därefter.
Så här gör du:
- Markera cellen där du vill infoga rullgardinsmenyn.
- Gå till Data -> Dataverktyg -> Datavalidering.
- I dialogrutan Datavalidering väljer du Lista på fliken Inställningar från rullgardinsmenyn.
- Ange följande formel i källan: = OFFSET (A1,0,0, COUNT (A: A), 1)
- Klicka på OK.
Låt oss se hur denna formel fungerar:
- De tre första argumenten för OFFSET -funktionen är A1, 0 och 0. Detta betyder i huvudsak att den skulle returnera samma referenscell (som är A1).
- Det fjärde argumentet är för höjd och här returnerar funktionen RÄKTA det totala antalet objekt i listan. Det förutsätter att det inte finns några ämnen i listan.
- Det femte argumentet är 1, vilket indikerar att kolumnbredden ska vara en.
Ytterligare anmärkningar:
- OFFSET är en flyktig funktion (använd med försiktighet).
- Den beräknar om när Excel -arbetsboken är öppen eller när en beräkning utlöses i kalkylbladet. Detta kan öka arbetstiden och sakta ner din arbetsbok.
- Om höjd- eller breddvärdet utelämnas, betraktas det som referensvärdet.
- Om rader och cols är negativa siffror är offsetriktningen omvänd.
Excel OFFSET Funktionsalternativ
På grund av några av begränsningarna i Excel OFFSET -funktionen vill du många överväga alternativ till den:
- INDEX -funktion: INDEX -funktionen kan också användas för att returnera en cellreferens. Klicka här för att se ett exempel på hur du skapar ett dynamiskt namnområde med INDEX -funktionen.
- Excel -tabell: Om du använder strukturerade referenser i Excel -tabellen behöver du inte oroa dig för att ny data läggs till och behovet av att justera formlerna.
Excel OFFSET -funktion - Videohandledning
- Excel VLOOKUP -funktion.
- Excel HLOOKUP -funktion.
- Excel INDEX -funktion.
- Excel INDIRECT -funktion.
- Excel MATCH -funktion.
