Excel OFFSET -funktion (eksempel + video)
Hvornår skal man bruge Excel OFFSET -funktion
Excel OFFSET -funktionen kan bruges, når du vil have en reference, der opvejer det angivne antal rækker og kolonner fra startpunktet.
Hvad den returnerer
Det returnerer den reference, OFFSET -funktionen peger på.
Syntaks
= OFFSET (reference, rækker, cols, [højde], [bredde])
Input argumenter
- reference - Referencen, som du vil udligne. Dette kan være en cellereference eller et område af tilstødende celler.
- rækker - antallet af rækker, der skal forskydes. Hvis du bruger et positivt tal, forskydes det til nedenstående rækker, og hvis der bruges et negativt tal, udlignes det til rækker ovenfor.
- cols - antallet af kolonner, der skal opvejes. Hvis du bruger et positivt tal, forskydes det til kolonnerne til højre, og hvis der bruges et negativt tal, så forskydes det til kolonnerne til venstre.
- [højde] - dette er et tal, der repræsenterer antallet af rækker i den returnerede reference.
- [bredde] - dette er et tal, der repræsenterer antallet af kolonner i den returnerede reference.
Forstå det grundlæggende i Excel OFFSET -funktion
Excel OFFSET -funktionen er muligvis en af de mest forvirrende funktioner i Excel.
Lad os tage et enkelt eksempel på et skakspil. Der er et stykke i Chess kaldet en Rook (også kendt som et tårn, markist eller rektor).
[Billede høflighed: Wonderful Wikipedia]
Nu, som alle de andre skakbrikker, har en Rook en fast sti, hvorpå den kan bevæge sig rundt på brættet. Det kan gå lige (til højre/venstre eller op/ned af brættet), men det kan ikke gå diagonalt.
Antag for eksempel, at vi har et skakbræt i Excel (som vist nedenfor), i en given boks (D5 i dette tilfælde) kan Rook kun gå i de fire fremhævede retninger.
Hvis jeg nu beder dig om at tage Rook fra dens nuværende position til den, der er markeret med gult, hvad vil du så fortælle tårnet?
Du vil bede den om at tage to trin nedad og to trin til højre … ikke sandt?
Og det er en tæt nærhed af, hvordan OFFSET -funktionen fungerer.
Lad os nu se, hvad dette betyder i Excel. Jeg vil starte med cellen D5 (som er hvor Rook er) og derefter gå to rækker ned og to kolonner til højre og hente værdien fra cellen der.
Formlen ville være:
= OFFSET (hvor skal man begynde, hvor mange rækker ned, hvor mange kolonner til højre)
Som du kan se, er formlen i ovenstående eksempel i celle J1 = OFFSET (D5,2,2).
Det startede ved D5 og gik derefter to rækker ned og to kolonner til højre og nåede cellen F7. Det returnerede derefter værdien i celle F7.
I ovenstående eksempel kiggede vi på OFFSET -funktionen med 3 argumenter. Men der er yderligere to valgfrie argumenter, der kan bruges.
Lad os se på et enkelt eksempel her:
Antag, at du vil bruge referencen til celle A1 (i gult), og du vil henvise til hele området markeret med blåt (C2: E4) i en formel.
Hvordan ville du gøre det ved hjælp af et tastatur? Du ville først gå til celle C2 og derefter markere alle cellerne i C2: E4.
Lad os nu se, hvordan du gør dette ved hjælp af OFFSET -formlen:
= OFFSET (A1,1,2,3,3)
Hvis du bruger denne formel i en celle, returnerer den en #VÆRDI! fejl, men hvis du kommer i redigeringstilstand og vælger formlen og trykker på F9, vil du se, at den returnerer alle de værdier, der er markeret med blåt.
Lad os nu se, hvordan denne formel fungerer:
= OFFSET (A1,1,2,3,3)
- Det første argument er cellen, hvor den skal starte.
- Det andet argument er 1, som beder Excel om at returnere en reference, der er blevet forskudt med 1 række.
- Det tredje argument er 2, som beder Excel om at returnere en reference, der er blevet forskudt med 2 kolonner.
- Det fjerde argument er 3. Dette angiver, at referencen skal dække 3 rækker. Dette kaldes højde -argumentet.
- Det femte argument er 3. Dette angiver, at referencen skal dække 3 kolonner. Dette kaldes bredde -argumentet.
Nu hvor du har referencen til en række celler (C2: E4), kan du bruge den inden for en anden funktion (f.eks. SUM, TÆL, MAX, Gennemsnit).
Forhåbentlig har du en god grundlæggende forståelse for at bruge Excel OFFSET -funktionen. Lad os nu se på nogle praktiske eksempler på brug af OFFSET -funktionen.
Excel OFFSET -funktion - eksempler
Her er to eksempler på brug af Excel OFFSET -funktion.
Eksempel 1 - Find den sidste fyldte celle i kolonnen
Antag, at du har nogle data i en kolonne som vist herunder:
For at finde den sidste celle i kolonnen skal du bruge følgende formel:
= OFFSET (A1, COUNT (A: A) -1,0)
Denne formel antager, at der ikke er nogen værdier bortset fra de viste, og disse celler har ikke en tom celle i den. Det fungerer ved at tælle det samlede antal celler, der er fyldt, og opvejer cellen A1 i overensstemmelse hermed.
For eksempel i denne sag er der 8 værdier, så TÆL (A: A) returnerer 8. Vi forskyder cellen A1 med 7 for at få den sidste værdi.
Eksempel 2 - Oprettelse af en dynamisk drop -down
Du kan udvide konceptudstillingerne i eksempel 1 for at oprette dynamiske navngivne områder. Disse kan være nyttige, hvis du bruger de navngivne områder i formler eller til at oprette rullemenuer, og du sandsynligvis vil tilføje/slette data fra den reference, der laver det navngivne område.
Her er et eksempel:
Bemærk, at rullemenuen justeres automatisk, når året tilføjes eller fjernes.
Dette sker, da formlen, der bruges til at oprette rullemenuen, er dynamisk og identificerer enhver tilføjelse eller sletning og justerer området i overensstemmelse hermed.
Sådan gør du dette:
- Vælg den celle, hvor du vil indsætte rullemenuen.
- Gå til Data -> Dataværktøjer -> Datavalidering.
- Vælg Liste i fanen Indstillinger i dialogboksen Datavalidering, fra rullemenuen.
- Indtast følgende formel i kilden: = OFFSET (A1,0,0, COUNT (A: A), 1)
- Klik på OK.
Lad os se, hvordan denne formel fungerer:
- De tre første argumenter for OFFSET -funktionen er A1, 0 og 0. Dette betyder i det væsentlige, at den ville returnere den samme referencecelle (som er A1).
- Det fjerde argument er for højde, og her returnerer funktionen TÆLL det samlede antal elementer på listen. Det forudsætter, at der ikke er emner på listen.
- Det femte argument er 1, hvilket angiver, at kolonnebredden skal være en.
Ekstra Noter:
- OFFSET er en flygtig funktion (brug med forsigtighed).
- Den genberegner hver gang excel -projektmappen er åben, eller når en beregning udløses i regnearket. Dette kan øge behandlingstiden og bremse din projektmappe.
- Hvis værdien for højde eller bredde udelades, betragtes den som referencens værdi.
- Hvis rækker og cols er negative tal, så vendes forskydningsretningen.
Excel OFFSET Funktionsalternativer
På grund af nogle af begrænsningerne i Excel OFFSET -funktionen, vil mange overveje alternativer til den:
- INDEX -funktion: INDEX -funktion kan også bruges til at returnere en cellereference. Klik her for at se et eksempel på, hvordan du opretter et dynamisk navngivet område ved hjælp af INDEX -funktionen.
- Excel -tabel: Hvis du bruger strukturerede referencer i Excel -tabel, behøver du ikke bekymre dig om, at nye data tilføjes og behovet for at justere formlerne.
Excel OFFSET -funktion - Videovejledning
- Excel VLOOKUP -funktion.
- Excel HLOOKUP -funktion.
- Excel INDEX -funktion.
- Excel INDIRECT -funktion.
- Excel MATCH -funktion.
