Ladda ner exempelarbetsboken
Denna handledning visar hur du använder Excel OFFSET -funktion i Excel för att skapa en referensförskjutning från en initial cell.
OFFSET Funktionsöversikt
OFFSET -funktionen börjar med en definierad cellreferens och returnerar en cellreferens ett specifikt antal rader och kolumner förskjutna från den ursprungliga referensen. Referenser kan vara en cell eller ett cellintervall. Offset låter dig också ändra storlek på referensen ett visst antal rader/kolumner.
(Lägg märke till hur formelinmatningarna ser ut)
IFERROR -funktion Syntax och ingångar:
| 1 | = OFFSET (referens, rader, cols, höjd, bredd) |
referens - Den ursprungliga cellreferensen från vilken du vill kompensera.
rader - Antalet rader som ska kompenseras.
cols - Antalet kolumner som ska kompenseras.
höjd - VALFRITT: Justera antalet rader i referensen.
bredd - VALFRITT: Justera antalet kolumner i referensen.
Vad är OFFSET -funktionen?
OFFSET -funktionen är en av de mer kraftfulla kalkylarkfunktionerna eftersom den kan vara ganska mångsidig i vad den skapar. Det ger användaren möjlighet att definiera en cell eller ett område i olika positioner och storlekar.
FÖRSIKTIGHET: OFFSET -funktionen är en av de flyktiga funktionerna. För det mesta när du arbetar i ditt kalkylblad beräknar datorn bara en formel om ingångarna har ändrat deras värden. En flyktig funktion räknar emellertid om varje när du gör en ändring i en cell. Försiktighet bör iakttas för att säkerställa att du inte orsakar en stor omräkningstid på grund av överdriven användning av flyktig funktion eller att många celler är beroende av resultatet av en flyktig funktion.
Grundläggande radexempel
Vid varje användning av OFFSET -funktionen måste du ge en utgångspunkt eller ett ankare. Låt oss titta på den här tabellen för att förstå detta:
Vi kommer att använda "Bob" i cell B3 som vår ankare. Om vi ville ta värdet strax under (Charlie), skulle vi säga att vi vill flytta raden med 1. Vår formel skulle se ut
| 1 | = OFFSET (B3, 1) |
Om vi ville växla upp skulle det vara ett negativt skifte. Du kan tänka på detta eftersom radnumret minskar, så vi måste subtrahera. Således, för att få värdet ovan (Adam), skulle vi skriva
| 1 | = OFFSET (B2, -1) |
Grundläggande kolumnexempel
Om vi fortsätter tanken från föregående exempel lägger vi till en annan kolumn i vårt bord.
Om vi ville ta läraren för Bob kunde vi använda formeln
| 1 | = OFFSET (B2, 0, 1) |
I det här fallet sa vi att vi vill kompensera noll rader (aka stanna på samma rad) men vi vill kompensera 1 kolumn. För kolumner betyder ett positivt tal att förskjutas till höger och negativa tal för att förskjuta till vänster.
OFFSET och MATCH
Anta att du hade flera datakolumner och att du ville ge användaren möjlighet att välja vilken kolumn han vill hämta resultat från. Du kan använda INDEX -funktionen, eller så kan du använda OFFSET. Eftersom MATCH kommer att returnera den relativa positionen för ett värde måste vi se till att förankringspunkten är till vänster om vårt första möjliga värde. Tänk på följande layout:
I B2 skriver vi denna formel:
| 1 | = OFFSET (B2, 0, MATCH (A2, $ C $ 1: $ F $ 1, 0)) |
MATCH kommer att se "Feb" i intervallet C1: F1 och hitta den i 2nd cell. OFFSET kommer sedan att flytta 1 kolumn till höger om B2 och fånga det önskade värdet 9. Observera att OFFSET inte har några problem med att använda samma cell som innehåller formeln som förankringspunkten.
OBS: Denna teknik kan användas som ersättning för VLOOKUP eller HLOOKUP när du vill returnera ett värde från vänster/ovanför ditt uppslagningsintervall. Detta beror på att OFFSET kan göra negativa kompensationer.
OFFSET för att få en räckvidd
Du kan använda 4th och 5th argument i OFFSET -funktionen för att returnera ett område snarare än bara en enda cell. Antag att du ville summera tre kolumner i den här tabellen.
| 1 | = MEDEL (OFFSET (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
I F2 har vi valt namnet på en elev som vi vill hämta deras genomsnittliga testresultat för. För att göra detta använder vi formeln
| 1 | = MEDEL (OFFSET (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
MATCH kommer att söka igenom kolumn A efter vårt namn och returnera den relativa positionen, vilket är 3 i vårt exempel. Låt oss se hur detta kommer att utvärderas. Först kommer OFFSET att gå ner 3 rader från A1 och 1 kolumn till höger från A1. Detta placerar oss i cell B3.
| 1 | = MEDEL (OFFSET (A1, 3, 1, 1, 3)) |
Därefter kommer vi att ändra storlek på intervallet. Det nya sortimentet kommer att ha B3 som cell längst upp till vänster. Det kommer att vara 1 rad högt och 3 kolumner högt, vilket ger oss intervallet B4: D4.
| 1 | = MEDEL (OFFSET (A1,3, 1, 1, 3)) |
Observera att även om du legitimt kan sätta negativa värden i offset-argumenten, kan du bara använda icke-negativa värden i dimensioneringsargumenten.
I slutet ser vår AVERAGE -funktion:
| 1 | = MEDEL (B4: D4) |
Således får vi vår lösning på 86,67
OFFSET med dynamisk SUM
Eftersom OFFSET används för att hitta en referens, snarare än att peka på cellen direkt, är det mest användbart när du har att göra med data som har rader tillagda eller raderade. Tänk på följande tabell med en summa längst ner
| 1 | = SUMMA (B2: B4) |
Om vi hade använt en grundläggande SUM -formel här på "= SUMMA (B2: B4)" och sedan infogat en ny rad för att lägga till en post för Bill, skulle vi ha fel svar
Låt oss istället tänka på hur man löser detta ur totals synvinkel. Vi vill verkligen ta tag i allt från cell B2 till cellen precis över vår summa. Hur vi kan skriva detta i en formel är att göra en radförskjutning på -1. Således använder vi detta som formeln för vår totala i cell B5:
| 1 | = SUMMA (B2: OFFSET (B5, -1,0)) |
Denna formel gör vad vi just beskrev: börja med B2 och gå till 1 cell ovanför vår totala cell. Du kan se hur vår summa uppdateras korrekt efter att vi lagt till Bills data.
OFFSET för att få de sista N -artiklarna
Låt oss säga att du registrerar månadsförsäljning men vill kunna titta på de senaste 3 månaderna. I stället för att behöva uppdatera dina formler manuellt för att fortsätta justera när ny data läggs till, kan du använda OFFSET -funktionen med RÄCKA.
Vi har redan visat hur du kan använda OFFSET för att fånga ett antal celler. För att avgöra hur många celler vi behöver skifta använder vi COUNT för att hitta hur många tal finns i kolumn B. Låt oss titta på vår provtabell.
| 1 | = SUMMA (OFFSET ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Om vi började vid B1 och kompenserade 4 rader (antalet siffror i kolumn B) hamnade vi längst ner i vårt sortiment, B5. Men eftersom OFFSET inte kan ändra storlek med ett negativt värde måste vi göra några justeringar så att vi hamnar i B3. Den allmänna ekvationen för detta kommer att vara att göra
| 1 | RÄKNA (…) - N + 1 |
Vi tar räkningen av hela kolumnen, subtraherar hur många vi än vill returnera (eftersom vi ska ändra storlek för att ta tag i dem) och lägger sedan till 1 (eftersom vi i huvudsak börjar vår förskjutning vid position noll).
Här kan du se att vi har ställt in ett intervall för att få summan, genomsnittet och max för de senaste N månaderna. I E1 har vi angett värdet 3. I E2 är vår formel
| 1 | = SUMMA (OFFSET ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Det markerade avsnittet är vår allmänna ekvation som vi just diskuterade. Vi behöver inte kompensera några kolumner. Vi ska sedan ändra storlek på intervallet till att vara 3 celler högt (bestämt av värdet i E1) och 1 kolumn brett. Vår SUMM tar sedan detta intervall och ger oss resultatet av $ 1.850. Vi har också visat att du kan beräkna genomsnittet av max för samma intervall genom att helt enkelt byta den yttre funktionen från SUM till vad situationen kräver.
OFFSET dynamiska valideringslistor
Med den teknik som visas i det senaste exemplet kan vi också bygga Named Range som kan användas i datavalidering eller diagram. Detta kan vara till hjälp när du vill konfigurera ett kalkylblad men förväntar dig att våra listor/data ändrar storlek. Låt oss säga att vår butik börjar sälja frukt, och vi har för närvarande tre val.
För att göra en rullgardinsmeny för datavalidering som vi kan använda någon annanstans, definierar vi namnet MyFruit som
| 1 | = $ A $ 2: OFFSET ($ A $ 1, COUNTA ($ A: $ A) -1, 0) |
Istället för COUNT använder vi COUNTA eftersom vi har att göra med textvärden. På grund av detta kommer dock vår COUNTA att vara en högre eftersom den kommer att räkna rubrikcellen i A1 och ge ett värde på 4. Om vi dock kompenseras av 4 rader hamnar vi i cell A5 som är tom. För att justera för detta, subtraherar vi 1.
Nu när vi har vår Named Range -inställning kan vi konfigurera viss datavalidering i cell C4 med hjälp av en listtyp, med källa:
| 1 | = MyFruit |
Observera att rullgardinsmenyn endast visar våra tre aktuella objekt. Om vi sedan lägger till fler objekt i vår lista och går tillbaka till rullgardinsmenyn, visar listan alla nya objekt utan att vi behöver ändra någon av formlerna.
Varning vid användning av OFFSET
Som nämnts i början av denna artikel är OFFSET en flyktig funktion. Du kommer inte att märka detta om du använder det i bara några celler, men om du börjar ha det inblandat i hundratals beräkningar och du snabbt kommer att märka att din dator spenderar en märkbar tid omräknas varje gång du gör några ändringar .
Eftersom OFFSET inte direkt nämner de celler det tittar på är det svårare för andra användare att komma förbi senare och ändra dina formler om det behövs.
Istället skulle det vara lämpligt att använda tabeller (introducerade i Office 2007) som tillåter strukturella referenser. Dessa hjälpte användarna att kunna ge en enda referens som automatiskt justerades i storlek när nya data lades till eller raderades.
Det andra alternativet att använda istället för OFFSET är den kraftfulla INDEX -funktionen. INDEX låter dig bygga alla dynamiska intervall vi såg i den här artikeln utan att vara en flyktig funktion.
Ytterligare anmärkningar
Använd OFFSET -funktionen för att returnera ett cellvärde (eller ett cellintervall) genom att kompensera ett givet antal rader och kolumner från en startreferens. När man bara letar efter en enda cell uppnår OFFSET -formler samma syfte som INDEX -formlerna med en något annorlunda teknik. OFFSET -funktionens verkliga kraft ligger i dess förmåga att välja ett cellintervall som ska användas i en annan formel.
När du använder OFFSET -funktionen definierar du en initial startcell eller cellområde. Sedan anger du antalet rader och kolumner som ska kompenseras från den initiala cellen. Du kan också ändra storlek på intervallet; lägga till eller subtrahera rader eller kolumner.
Återgå till listan över alla funktioner i Excel
OFFSET i Google Kalkylark
OFFSET -funktionen fungerar exakt samma i Google Kalkylark som i Excel:
