Download eksemplet på projektmappe
Denne vejledning viser, hvordan du bruger Excel OFFSET -funktion i Excel for at oprette en referencekompensation fra en startcelle.
OFFSET Funktionsoversigt
OFFSET -funktionen starter med en defineret cellereference og returnerer en cellereference et specifikt antal rækker og kolonner forskudt fra den originale reference. Referencer kan være en celle eller en række celler. Offset giver dig også mulighed for at ændre størrelsen på referencen for et givet antal rækker/kolonner.
(Bemærk hvordan formelindgangene vises)
IFERROR Funktionssyntaks og input:
| 1 | = OFFSET (reference, rækker, cols, højde, bredde) |
reference - Den oprindelige cellereference, hvorfra du vil opveje.
rækker - Antallet af rækker, der skal forskydes.
cols - Antallet af kolonner, der skal forskydes.
højde - VALGFRIT: Juster antallet af rækker i referencen.
bredde - VALGFRIT: Juster antallet af kolonner i referencen.
Hvad er OFFSET -funktionen?
OFFSET -funktionen er en af de mere kraftfulde regnearksfunktioner, da den kan være ret alsidig i, hvad den skaber. Det giver brugeren mulighed for at definere en celle eller et område i forskellige positioner og størrelser.
FORSIGTIG: OFFSET -funktionen er en af de flygtige funktioner. Det meste af tiden, når du arbejder i dit regneark, genberegner computeren kun en formel, hvis inputene har ændret deres værdier. En flygtig funktion genberegnes imidlertid hver gang du foretager en ændring af en hvilken som helst celle. Forsigtighed bør udvises for at sikre, at du ikke forårsager en stor genberegningstid på grund af overdreven brug af flygtig funktion eller at have mange celler afhængige af resultatet af en flygtig funktion.
Grundlæggende rækkeeksempler
Ved enhver brug af OFFSET -funktionen skal du give et udgangspunkt eller anker. Lad os se på denne tabel for at hjælpe med at forstå dette:
Vi vil bruge "Bob" i celle B3 som vores ankerpunkt. Hvis vi ønskede at få fat i værdien lige under (Charlie), ville vi sige, at vi vil flytte rækken med 1. Vores formel ville se ud som
| 1 | = OFFSET (B3, 1) |
Hvis vi ville rykke op, ville det være et negativt skift. Du kan tænke på dette, da rækketallet falder, så vi skal trække fra. For at få værdien over (Adam) ville vi således skrive
| 1 | = OFFSET (B2, -1) |
Grundlæggende kolonneeksempler
I forlængelse af ideen fra tidligere eksempel tilføjer vi en anden kolonne til vores tabel.
Hvis vi ville have fat i læreren efter Bob, kunne vi bruge formlen
| 1 | = OFFSET (B2, 0, 1) |
I dette tilfælde sagde vi, at vi vil opveje nul rækker (aka blive på samme række), men vi ønsker at opveje 1 kolonne. For kolonner betyder et positivt tal at forskyde til højre, og negative tal at forskyde til venstre.
OFFSET og MATCH
Antag, at du havde flere datakolonner, og du ville give brugeren mulighed for at vælge, hvilken kolonne der skal hentes resultater fra. Du kan bruge INDEX -funktionen, eller du kan bruge OFFSET. Da MATCH returnerer den relative position for en værdi, skal vi sikre os, at ankerpunktet er til venstre for vores første mulige værdi. Overvej følgende layout:
I B2 skriver vi denne formel:
| 1 | = OFFSET (B2, 0, MATCH (A2, $ C $ 1: $ F $ 1, 0)) |
MATCH vil se "Feb" i området C1: F1 og finde det i 2nd celle. OFFSET vil derefter flytte 1 kolonne til højre for B2 og få fat i den ønskede værdi på 9. Bemærk, at OFFSET ikke har noget problem med at bruge den samme celle, der indeholder formlen som ankerpunktet.
BEMÆRK: Denne teknik kan bruges som en erstatning for VLOOKUP eller HLOOKUP, når du vil returnere en værdi fra venstre/over dit opslagsområde. Dette skyldes, at OFFSET kan foretage negative forskydninger.
OFFSET for at få en rækkevidde
Du kan bruge 4th og 5th argumenter i OFFSET -funktionen for at returnere et område frem for kun en enkelt celle. Antag, at du ønskede at opsummere 3 kolonner i denne tabel.
| 1 | = Gennemsnit (OFFSET (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
I F2 har vi valgt navnet på en elev, som vi vil hente deres gennemsnitlige testresultater for. For at gøre dette bruger vi formlen
| 1 | = Gennemsnit (OFFSET (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
MATCH vil søge gennem kolonne A efter vores navn og returnere den relative position, hvilket er 3 i vores eksempel. Lad os se, hvordan dette vil blive evalueret. For det første vil OFFSET gå ned 3 rækker fra A1 og 1 kolonne til ret fra A1. Dette placerer os i celle B3.
| 1 | = Gennemsnit (OFFSET (A1, 3, 1, 1, 3)) |
Dernæst vil vi ændre størrelsen på intervallet. Det nye område vil have B3 som øverste venstre celle. Det vil være 1 række højt og 3 kolonner højt, hvilket giver os området B4: D4.
| 1 | = Gennemsnit (OFFSET (A1,3, 1, 1, 3)) |
Bemærk, at selvom du legitimt kan sætte negative værdier i offset-argumenterne, kan du kun bruge ikke-negative værdier i størrelsesargumenterne.
I slutningen ser vores GENNEMGANGSFunktion:
| 1 | = Gennemsnit (B4: D4) |
Således får vi vores løsning på 86,67
OFFSET med dynamisk SUM
Fordi OFFSET bruges til at finde en reference frem for at pege direkte på cellen, er det mest nyttigt, når du har at gøre med data, der har tilføjet eller slettet rækker. Overvej følgende tabel med et Total i bunden
| 1 | = SUM (B2: B4) |
Hvis vi havde brugt en grundlæggende SUM -formel her på “= SUM (B2: B4)” og derefter indsat en ny række for at tilføje en post til Bill, ville vi have det forkerte svar
Lad os i stedet tænke på, hvordan du løser dette fra Totals synspunkt. Vi vil virkelig have fat i alt fra celle B2 til cellen lige over vores total. Den måde, vi kan skrive dette på i en formel, er at lave en rækkeforskydning på -1. Således bruger vi dette som formlen for vores total i celle B5:
| 1 | = SUM (B2: OFFSET (B5, -1,0)) |
Denne formel gør, hvad vi lige har beskrevet: Start ved B2 og gå til 1 celle over vores samlede celle. Du kan se, hvordan vores total opdateres korrekt efter tilføjelse af Bills data.
OFFSET for at få de sidste N -varer
Lad os sige, at du registrerer månedligt salg, men ønsker at kunne se på de sidste 3 måneder. I stedet for at skulle opdatere dine formler manuelt for at blive ved med at justere, når nye data tilføjes, kan du bruge OFFSET -funktionen med TÆL.
Vi har allerede vist, hvordan du kan bruge OFFSET til at få fat i en række celler. For at bestemme, hvor mange celler vi skal flytte, bruger vi TÆLLE til at finde hvor mange tal er i kolonne B. Lad os se på vores prøvetabel.
| 1 | = SUM (OFFSET ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Hvis vi startede ved B1 og udlignede 4 rækker (antallet af tal i kolonne B), ville vi ende i bunden af vores område, B5. Da OFFSET ikke kan ændre størrelsen med en negativ værdi, skal vi foretage nogle justeringer, så vi ender i B3. Den generelle ligning for dette vil være at gøre
| 1 | TÆL (…) - N + 1 |
Vi tager optællingen af hele kolonnen, trækker fra, hvor mange vi vil returnere (da vi ændrer størrelsen for at få fat i dem), og tilføjer derefter 1 (da vi hovedsageligt starter vores forskydning ved position nul).
Her kan du se, at vi har konfigureret et område for at få summen, gennemsnittet og maks. For de sidste N måneder. I E1 har vi indtastet værdien 3. I E2 er vores formel
| 1 | = SUM (OFFSET ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Det fremhævede afsnit er vores generelle ligning, som vi lige har diskuteret. Vi behøver ikke at opveje nogen kolonner. Vi vil derefter ændre størrelsen på området til at være 3 celler højt (bestemt af værdien i E1) og 1 kolonne bredt. Vores SUM tager derefter dette interval og giver os resultatet af $ 1.850. Vi har også vist, at du kan beregne gennemsnittet af max for det samme område ved blot at skifte den ydre funktion fra SUM til uanset situationen kræver.
OFFSET dynamiske valideringslister
Ved hjælp af den teknik, der er vist i sidste eksempel, kan vi også bygge navngivne intervaller, der kan bruges i datavalidering eller diagrammer. Dette kan være nyttigt, når du vil konfigurere et regneark, men forventer, at vores lister/data ændrer størrelse. Lad os sige, at vores butik begynder at sælge frugt, og vi har i øjeblikket 3 valgmuligheder.
For at lave en rulleliste med datavalidering, som vi kan bruge andre steder, definerer vi det navngivne område MyFruit som
| 1 | = $ A $ 2: OFFSET ($ A $ 1, COUNTA ($ A: $ A) -1, 0) |
I stedet for COUNT bruger vi COUNTA, da vi har at gøre med tekstværdier. På grund af dette vil vores COUNTA dog være et højere, da det vil tælle overskriftscellen i A1 og give en værdi på 4. Hvis vi dog modregnes med 4 rækker, ender vi i celle A5, der er tom. For at justere for dette så trækker vi 1.
Nu hvor vi har fået vores Named Range -opsætning, kan vi opsætte noget datavalidering i celle C4 ved hjælp af en listertype med kilde:
| 1 | = MyFruit |
Bemærk, at rullemenuen kun viser vores tre aktuelle varer. Hvis vi derefter tilføjer flere elementer til vores liste og går tilbage til rullemenuen, viser listen alle de nye emner, uden at vi skal ændre nogen af formlerne.
Forsigtighed ved brug af OFFSET
Som nævnt i begyndelsen af denne artikel er OFFSET en flygtig funktion. Du vil ikke bemærke dette, hvis du bruger det i bare et par celler, men hvis du begynder at have det involveret i hundredvis af beregninger, og du hurtigt vil bemærke, at din computer bruger en mærkbar tid til at genberegne hver gang du foretager ændringer .
Fordi OFFSET ikke direkte navngiver de celler, den ser på, er det sværere for andre brugere at komme forbi senere og ændre dine formler, hvis det er nødvendigt.
I stedet ville det være tilrådeligt at bruge tabeller (introduceret i Office 2007), som tillader strukturelle referencer. Disse hjalp brugerne med at kunne give en enkelt reference, der automatisk justerede i størrelse, efterhånden som nye data blev tilføjet eller slettet.
Den anden mulighed at bruge i stedet for OFFSET er den kraftfulde INDEX -funktion. INDEX lader dig bygge alle de dynamiske områder, vi så i denne artikel, uden at være en flygtig funktion.
Ekstra Noter
Brug OFFSET -funktionen til at returnere en celleværdi (eller et område af celler) ved at udligne et givet antal rækker og kolonner fra en startreference. Når man kun leder efter en enkelt celle, opnår OFFSET -formler det samme formål som INDEX -formlerne ved hjælp af en lidt anden teknik. OFFSET -funktionens virkelige magt ligger i dens evne til at vælge en række celler, der skal bruges i en anden formel.
Når du bruger OFFSET -funktionen, definerer du en startcelle eller et celleområde. Derefter angiver du antallet af rækker og kolonner, der skal forskydes fra den første celle. Du kan også ændre størrelsen på området; tilføje eller trække rækker eller kolonner fra.
Tilbage til listen over alle funktioner i Excel
OFFSET i Google Sheets
OFFSET -funktionen fungerer nøjagtig det samme i Google Sheets som i Excel:
