I denne artikkelen lærer vi hvordan du bruker OFFSET -funksjonen i Excel.
Hva er OFFSET -funksjon?
Offset -funksjon er en informasjonsinnhentingsfunksjon. Hvis du har en enorm tabell som inneholder visse data og du vil hente data fra den, er den beste måten å gjøre det på å sette inn en forskyvningsformel for tabellen i regnearket eller et annet ark i den samme arbeidsboken. Således, når du har opprettet offsethentingstabellen, må du bare skrive inn dataene, for eksempel "måned", og du vil få "salget" eller "inntekten" for den måneden fra den tabellen.
Å gå gjennom tabellen er et annet alternativ, men vil du vurdere alternativet hvis du har ark og ark med data som alle inneholder en 1000 kolonne datatabell? Det er her offset kombineres med andre funksjoner.
Konseptet med svingbord kommer fra forskyvning. En pivottabell er en generell tabell, og fra den spesifikke kombinasjonen av data og grafer oppnås når og når det er nødvendig. Også dynamiske tabeller kan opprettes på samme måte. For disse tabellene må du lage excel -forskyvningsformelen
Syntaks for OFFSET
| = FORSKYTT (referanse, rader, kolonner, [høyde], [bredde]) |
Rader - du ber excel om å flytte antall rader for å få informasjonen. I dette tilfellet må det bevege seg rundt bordet og så bare la et tomt stå med et komma (,). Ellers, for å flytte en kolonne fremover, gi verdi som 1 og for å flytte en kolonne før, gi verdien -1.
Kolonner - dette er å drive funksjonen til antall kolonner. Verdisystemet er det samme som for rad.
Høyde - høyden på bordet, i dette tilfellet A11.
Bredde - bredden på bordet, i dette tilfellet D11.
Etter at du har konfigurert offset -funksjonen, er det viktig å kjøre en prøveperiode for å fjerne eventuelle feil.
La oss ta en tabell som inneholder navn, e -post -ID, fødselsdato og alder. Tabellen begynner ved A1 -celle, som inneholder overskriften ‘Navn’. Dataene kjører for si A11. Og radene går til D1. Hele tabellen kjører A1: D11. Du vil ha et hentesystem som får deg navnet når du skriver inn e -post -ID -en, eller e -post -ID -en når du skriver inn navnet sammen med andre detaljer. Du lager en formel ved å tilfredsstille de 5 argumentene.
Referansepunktet er cellen som oppslaget skal starte fra. I dette tilfellet bør det være A2. Det er den generelle standarden.
Eksempel:
For eksempel gir du referanse til B4 og forskyvning rad 0, og forskyvningskolonne 1, så blir verdien fra C4 returnert. Forvirrende? La oss ha noen flere eksempler. Siste eksempel på OFFSET -funksjon forteller hvordan du summerer dynamisk.
OFFSET -funksjon for å få verdi fra høyre og venstre på en celle
Vi har dette bordet.
Nå hvis jeg vil få verdi fra 2 celle rett til B2 (som betyr D2). Jeg vil skrive denne OFFSET -formelen:
| = OFFSET (B2,0,2) |
OFFSET -funksjonen flytter 2 celler til høyre for celle B4 og returnerer verdien. Se bildet nedenfor
Hvis jeg vil få verdi fra 1 celle igjen til B2 (som betyr A2). Jeg vil skrive denne OFFSET -formelen:
| = OFFSET (B2,0, -1) |
Minus (-) tegn indikerer forskyvning for å flytte reversert.
OFFSET -funksjon for å få verdi nedenfra og over en celle
Så igjen i tabellen ovenfor, hvis jeg vil få verdi fra 2 celle til B3 (som betyr B5). Jeg vil skrive denne OFFSET -formelen:
| = OFFSET (B2,2,0) |
Hvis jeg vil få verdi fra 1 celle igjen til B2 (som betyr A2). Jeg vil skrive denne OFFSET -formelen:
| = OFFSET (B2, -2,0) |
- Profftips: Behandle en OFFSET som en grafpeker der referanse er opprinnelse og rad og kolonne er x- og y -aksen.
OFFSET -funksjon for å summe dynamisk
La oss se dette eksemplet.
I tabellen ovenfor Total rad i enden av tabellen. Over tid vil du legge til rader i denne tabellen. Deretter må du endre sumområdet i formelen. Her kan vi ta hjelp av OFFSET -funksjonen for å summere dynamisk. For tiden har vi i celle C11 = SUM (C2: C10). Erstatt dette med formelen nedenfor.
| = SUMME (C2: OFFSET (C11, -1,0)) |
Denne formelen tar bare den første dataraden og summerer deretter området over den totale raden.
Offset for å få matrise
OFFSET -funksjonen har to valgfrie argumenter, [høyde] og [bredde]. Ikke ta dem for gitt. Når OFFSET -funksjonen har [høyde] og [bredde] argumenter, returnerer den en todimensjonal matrise. Hvis bare ett av dem sendes som argument, returnerer det på dimensjonale verdier.
Hvis du skriver inn denne formelen i en hvilken som helst celle:
| = SUMME (OFFSET (C1,1,0,9,3)) |
Den summerer verdier i området fra C2 til 9 rader nedenfor og 3 kolonner til høyre.
OFFSET (C1,1,0,9,3): Dette vil oversette til {8,8,7; 6,1,2; 8,5,8; 5,1,5; 8,3,5; 8,3,9; 8,9,2; 3,5 , 4; 6,6,5}.
Så avslutningsvis er offset en veldig nyttig funksjon av excel som kan hjelpe deg med å løse mange kvalt tråder. Gi meg beskjed om hvordan du bruker OFFSET -funksjonen i formelen din og hvor det hjalp deg mest.
Oppslagsfunksjonen er den samme som forskyvning, men bruker funksjoner som Match, Counta og IF for å arbeide med en dynamisk tabell.
Å bruke offset i Excel krever dyktighet og god kunnskap om Excels -funksjoner og hvordan de fungerer sammen. Å trekke den av uten feil er den største hindringen.
Her er alle observasjonsnotatene om bruk av formelen.
Merknader:
- Denne formelen fungerer med både tekst og tall.
- Det er fem forskyvningsargumenter som må tilfredsstilles for å få en feilfri informasjon. Hvis du skriver en feil formel, oppstår det en Ref -feil.
Håper du har forstått hva som er Excel Offset -funksjon og hvordan du bruker den i Excel. Utforsk flere artikler om Excel -oppslag og match verdier ved å bruke formler her. Hvis du likte bloggene våre, del den med vennene dine på Facebook. Og du kan også følge oss på Twitter og Facebook. Vi vil gjerne høre fra deg, gi oss beskjed om hvordan vi kan forbedre, utfylle eller innovere arbeidet vårt og gjøre det bedre for deg. Skriv til oss på e -post.
VLOOKUP -funksjon for å beregne karakter i Excel : Å beregne karakterer IF og IFS er ikke de eneste funksjonene du kan bruke. VLOOKUP er mer effektiv og dynamisk for slike betingede beregninger. For å beregne karakterer ved hjelp av VLOOKUP kan vi bruke denne formelen.
17 ting om Excel VLOOKUP : VLOOKUP brukes oftest for å hente samsvarende verdier, men VLOOKUP kan gjøre mye mer enn dette. Her er 17 ting om VLOOKUP som du bør vite for å bruke effektivt.
SLÅ OPP den første teksten fra en liste i Excel : VLOOKUP -funksjonen fungerer fint med jokertegn. Vi kan bruke dette til å trekke ut den første tekstverdien fra en gitt liste i excel. Her er den generiske formelen.
OPPLAGSDATO med siste verdi i listen : For å hente datoen som inneholder den siste verdien bruker vi LOOKUP -funksjonen. Denne funksjonen søker etter cellen som inneholder den siste verdien i en vektor, og bruker deretter referansen til å returnere datoen.
Hvordan hente siste pris i Excel : Det er vanlig å oppdatere priser i enhver virksomhet, og å bruke de nyeste prisene for kjøp eller salg er et must. For å hente den siste prisen fra en liste i Excel bruker vi LOOKUP -funksjonen. LOOKUP -funksjonen henter den siste prisen.
Populære artikler:
50 Excel -snarvei for å øke produktiviteten : Bli raskere på oppgaven din. Disse 50 snarveiene får deg til å jobbe enda raskere med Excel.
Slik bruker du IF -funksjonen i Excel : IF -setningen i Excel sjekker tilstanden og returnerer en bestemt verdi hvis betingelsen er SANN eller returnerer en annen spesifikk verdi hvis FALSE.
Slik bruker du VLOOKUP -funksjonen i Excel : Dette er en av de mest brukte og populære funksjonene i excel som brukes til å slå opp verdi fra forskjellige områder og ark.
Slik bruker du COUNTIF -funksjonen i Excel : Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke å filtrere dataene dine for å telle spesifikke verdier. Countif -funksjonen er avgjørende for å forberede dashbordet.
Slik bruker du SUMIF -funksjonen i Excel : Dette er en annen viktig funksjon i instrumentbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.