Last ned eksemplet på arbeidsbok
Denne opplæringen viser hvordan du bruker Excel OFFSET -funksjon i Excel for å opprette en referanseforskyvning fra en startcelle.
OFFSET Funksjon Oversikt
OFFSET -funksjonen starter med en definert cellereferanse og returnerer en cellereferanse et spesifisert antall rader og kolonner forskjøvet fra den opprinnelige referansen. Referanser kan være én celle eller en rekke celler. Offset lar deg også endre størrelsen på referansen til et gitt antall rader/kolonner.
(Legg merke til hvordan formelinngangene vises)
IFERROR Funksjon Syntaks og innganger:
| 1 | = OFFSET (referanse, rader, cols, høyde, bredde) |
henvisning - Den første cellereferansen du vil kompensere fra.
rader - Antall rader som skal kompenseres.
cols - Antall kolonner som skal kompenseres.
høyde - VALGFRITT: Juster antall rader i referansen.
bredde - VALGFRITT: Juster antall kolonner i referansen.
Hva er OFFSET -funksjonen?
OFFSET -funksjonen er en av de kraftigere regnearkfunksjonene, da den kan være ganske allsidig i det den lager. Det gir brukeren muligheten til å definere en celle eller et område i en rekke posisjoner og størrelser.
FORSIKTIG: OFFSET -funksjonen er en av de flyktige funksjonene. Mesteparten av tiden når du jobber i regnearket, vil datamaskinen bare beregne en formel på nytt hvis inngangene har endret verdiene. En flyktig funksjon regner imidlertid ut på nytt hver gang du gjør en endring i en hvilken som helst celle. Forsiktighet bør utvises for å sikre at du ikke forårsaker en stor omberegningstid på grunn av overdreven bruk av flyktig funksjon eller at mange celler er avhengige av resultatet av en flyktig funksjon.
Grunnleggende radeksempler
Ved hver bruk av OFFSET -funksjonen må du gi et utgangspunkt eller anker. La oss se på denne tabellen for å forstå dette:
Vi bruker "Bob" i celle B3 som vårt ankerpunkt. Hvis vi ønsket å fange verdien like under (Charlie), ville vi si at vi ønsker å flytte raden med 1. Formelen vår ser ut som
| 1 | = FORSKYTT (B3, 1) |
Hvis vi ønsket å skifte opp, ville det være et negativt skifte. Du kan tenke på dette som radnummeret synker, så vi må trekke fra. Således, for å få verdien over (Adam), ville vi skrive
| 1 | = OFFSET (B2, -1) |
Grunnleggende kolonneeksempler
Ved å fortsette ideen fra forrige eksempel, legger vi til en annen kolonne i bordet vårt.
Hvis vi ønsket å ta læreren til Bob, kunne vi bruke formelen
| 1 | = OFFSET (B2, 0, 1) |
I dette tilfellet sa vi at vi vil kompensere null rader (aka bli på samme rad), men vi vil kompensere 1 kolonne. For kolonner betyr et positivt tall å forskyve til høyre, og negative tall betyr å forskyve til venstre.
OFFSET og MATCH
Anta at du hadde flere kolonner med data, og at du ønsket å gi brukeren muligheten til å velge hvilken kolonne han skulle hente resultater fra. Du kan bruke INDEX -funksjonen, eller du kan bruke OFFSET. Siden MATCH vil returnere den relative posisjonen til en verdi, må vi sørge for at ankerpunktet er til venstre for vår første mulige verdi. Vurder følgende oppsett:
I B2 skriver vi denne formelen:
| 1 | = OFFSET (B2, 0, MATCH (A2, $ C $ 1: $ F $ 1, 0)) |
MATCH kommer til å se "Feb" i området C1: F1 og finne den i 2nd celle. OFFSET vil deretter flytte en kolonne til høyre for B2 og hente ønsket verdi på 9. Merk at OFFSET ikke har noe problem med å bruke den samme cellen som inneholder formelen som ankerpunktet.
MERK: Denne teknikken kan brukes som en erstatning for VLOOKUP eller HLOOKUP når du vil returnere en verdi fra venstre/over oppslagsområdet. Dette er fordi OFFSET kan gjøre negative kompensasjoner.
OFFSET for å få en rekkevidde
Du kan bruke 4th og 5th argumenter i OFFSET -funksjonen for å returnere et område i stedet for bare en enkelt celle. Anta at du ønsket å oppsummere 3 kolonner i denne tabellen.
| 1 | = GJENNOMSNITT (OFFSET (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
I F2 har vi valgt navnet på en elev som vi vil hente gjennomsnittlige testresultater for. For å gjøre dette bruker vi formelen
| 1 | = GJENNOMSNITT (OFFSET (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
MATCH kommer til å søke gjennom kolonne A etter navnet vårt og returnere den relative posisjonen, som er 3 i vårt eksempel. La oss se hvordan dette vil bli evaluert. For det første skal OFFSET gå ned 3 rader fra A1 og 1 kolonne til Ikke sant fra A1. Dette plasserer oss i celle B3.
| 1 | = GJENNOMSNITT (OFFSET (A1, 3, 1, 1, 3)) |
Deretter skal vi endre størrelsen på serien. Det nye området vil ha B3 som cellen øverst til venstre. Det vil være 1 rad høyt og 3 kolonner høyt, noe som gir oss området B4: D4.
| 1 | = GJENNOMSNITT (OFFSET (A1,3, 1, 1, 3)) |
Vær oppmerksom på at mens du legitimt kan sette negative verdier i forskyvningsargumentene, kan du bare bruke ikke-negative verdier i størrelsesargumentene.
På slutten ser gjennomsnittsfunksjonen vår:
| 1 | = GJENNOMSNITT (B4: D4) |
Dermed får vi vår løsning på 86,67
OFFSET med dynamisk SUM
Fordi OFFSET brukes til å finne en referanse, i stedet for å peke på cellen direkte, er det mest nyttig når du har å gjøre med data som har rader lagt til eller slettet. Tenk på følgende tabell med en total nederst
| 1 | = SUMME (B2: B4) |
Hvis vi hadde brukt en grunnleggende SUM -formel her om "= SUMME (B2: B4)" og deretter satt inn en ny rad for å legge til en post for Bill, ville vi ha feil svar
La oss i stedet tenke på hvordan du løser dette fra Totals synspunkt. Vi ønsker virkelig å ta tak i alt fra celle B2 til cellen like over totalen vår. Måten vi kan skrive dette på i en formel er å gjøre en radforskyvning på -1. Dermed bruker vi dette som formelen for vår total i celle B5:
| 1 | = SUMME (B2: OFFSET (B5, -1,0)) |
Denne formelen gjør det vi nettopp beskrev: start på B2 og gå til 1 celle over vår totale celle. Du kan se hvordan vår total blir oppdatert riktig etter at vi har lagt til Bills data.
OFFSET for å få siste N -varer
La oss si at du registrerer månedlige salg, men ønsker å kunne se på de siste 3 månedene. I stedet for å måtte oppdatere formlene manuelt for å fortsette å justere ettersom nye data legges til, kan du bruke OFFSET -funksjonen med TELLING.
Vi har allerede vist hvordan du kan bruke OFFSET til å ta tak i en rekke celler. For å finne ut hvor mange celler vi trenger å skifte, bruker vi TELLING for å finne hvor mange tall er i kolonne B. La oss se på vårt utvalgstabell.
| 1 | = SUM (OFFSET ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Hvis vi begynte på B1 og forskjøvet 4 rader (antallet tall i kolonne B), ville vi havnet nederst i vårt område, B5. Siden OFFSET ikke kan endre størrelsen med en negativ verdi, må vi gjøre noen justeringer slik at vi havner i B3. Den generelle ligningen for dette kommer til å være å gjøre
| 1 | TELL (…) - N + 1 |
Vi tar tellingen av hele kolonnen, trekker fra hvor mange vi vil returnere (siden vi vil endre størrelsen for å ta dem), og legger deretter til 1 (siden vi egentlig starter vår forskyvning på posisjon null).
Her kan du se at vi har konfigurert et område for å få summen, gjennomsnittet og maksimumet for de siste N månedene. I E1 har vi angitt verdien til 3. I E2 er formelen vår
| 1 | = SUMME (OFFSET ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Den fremhevede delen er vår generelle ligning som vi nettopp diskuterte. Vi trenger ikke å kompensere noen kolonner. Vi skal deretter endre størrelsen på området til å være 3 celler høyt (bestemt av verdien i E1) og 1 kolonne bredt. Vår SUMM tar deretter dette området og gir oss resultatet på $ 1.850. Vi har også vist at du kan beregne gjennomsnittet av maks for det samme området ved ganske enkelt å bytte den ytre funksjonen fra SUM til situasjonen krever.
FORSKYTT dynamiske valideringslister
Ved å bruke teknikken vist i siste eksempel, kan vi også bygge navngitte områder som kan brukes i datavalidering eller diagrammer. Dette kan være nyttig når du vil sette opp et regneark, men forventer at listene/dataene våre endrer størrelse. La oss si at butikken vår begynner å selge frukt, og vi har for øyeblikket tre valg.
For å lage en rullegardinmeny for datavalidering som vi kan bruke andre steder, definerer vi det navngitte området MyFruit som
| 1 | = $ A $ 2: OFFSET ($ A $ 1, COUNTA ($ A: $ A) -1, 0) |
I stedet for COUNT bruker vi COUNTA siden vi har å gjøre med tekstverdier. På grunn av dette kommer imidlertid vårt COUNTA til å bli en høyere siden den kommer til å telle toppcellen i A1 og gi en verdi på 4. Hvis vi imidlertid kompenseres med 4 rader, vil vi ende opp i celle A5 som er tom. For å justere for dette, trekker vi 1.
Nå som vi har konfigurert vårt navngitte område, kan vi sette opp noen datavalidering i celle C4 ved å bruke en listtype, med kilde:
| 1 | = MyFruit |
Vær oppmerksom på at rullegardinmenyen bare viser de tre nåværende elementene våre. Hvis vi deretter legger til flere elementer på listen vår og går tilbake til rullegardinmenyen, viser listen alle de nye elementene uten at vi trenger å endre noen av formlene.
Advarsel ved bruk av OFFSET
Som nevnt i begynnelsen av denne artikkelen, er OFFSET en flyktig funksjon. Du vil ikke legge merke til dette hvis du bruker det i bare noen få celler, men hvis du begynner å ha det involvert i hundrevis av beregninger, og du vil raskt legge merke til at datamaskinen din bruker en merkbar tid på å beregne seg hver gang du foretar endringer .
Fordi OFFSET ikke gir navn til cellene det ser på, er det vanskeligere for andre brukere å komme innom senere og endre formlene om nødvendig.
I stedet vil det være lurt å bruke tabeller (introdusert i Office 2007) som tillater strukturelle referanser. Disse hjalp brukerne med å kunne gi en enkelt referanse som automatisk ble justert i størrelse etter hvert som nye data ble lagt til eller slettet.
Det andre alternativet å bruke i stedet for OFFSET er den kraftige INDEX -funksjonen. INDEX lar deg bygge alle de dynamiske områdene vi så i denne artikkelen uten å være en flyktig funksjon.
Ytterligere merknader
Bruk OFFSET -funksjonen til å returnere en celleverdi (eller et celleområde) ved å forskyve et gitt antall rader og kolonner fra en startreferanse. Når du bare ser etter en enkelt celle, oppnår OFFSET -formler det samme formålet som INDEX -formlene, ved å bruke en litt annen teknikk. OFFSET -funksjonens virkelige kraft ligger i dens evne til å velge et celleområde som skal brukes i en annen formel.
Når du bruker OFFSET -funksjonen, definerer du en startcelle eller et celleområde. Deretter angir du antall rader og kolonner som skal forskyves fra den første cellen. Du kan også endre størrelsen på området; legge til eller trekke fra rader eller kolonner.
Gå tilbake til listen over alle funksjoner i Excel
FORSKYTT i Google Regneark
OFFSET -funksjonen fungerer nøyaktig det samme i Google Sheets som i Excel:
