I denne artikel lærer vi, hvordan du bruger OFFSET -funktionen i Excel.
Hvad er OFFSET funktion?
Offset -funktion er en informationshentningsfunktion. Hvis du har en enorm tabel, der indeholder bestemte data, og du vil hente data fra den, er den bedste måde at gøre det på ved at indsætte en forskydningsformel for tabellen i dette regneark eller et andet ark i den samme projektmappe. Så snart du har oprettet offsethentningstabellen, skal du blot indtaste dataene for eksempel 'måned', og du får den 'salgs' eller 'indkomst' for den pågældende måned fra den pågældende tabel.
At gå igennem tabellen er en anden mulighed, men ville du overveje muligheden, hvis du har ark og ark med data, der alle indeholder en 1000 kolonne datatabel? Det er her offset kombineres med andre funktioner.
Begrebet drejebord kommer fra forskydning. En pivottabel er en generel tabel, og fra den specifikke kombination af data og grafer opnås efter behov. Også dynamiske tabeller kan oprettes på samme måde. For disse tabeller skal du oprette excel -offsetformlen
Syntaks for OFFSET
| = OFFSET (reference, rækker, kolonner, [højde], [bredde]) |
Rækker - du beder excel om at flytte antallet af rækker for at få informationen. I dette tilfælde skal den bevæge sig rundt om bordet og så ganske enkelt efterlade et tomt markeret med et komma (,). Ellers skal du flytte en kolonne fremad som 1 og flytte en kolonne forud give en værdi -1.
Kolonner - dette er for at drive funktionen til antallet af kolonner. Værdisystemet er det samme som for række.
Højde - bordets højde, i dette tilfælde A11.
Bredde - bordets bredde, i dette tilfælde D11.
Når du har konfigureret offset -funktionen, er det vigtigt at køre en prøveperiode for at fjerne eventuelle fejl.
Lad os tage en tabel, der indeholder navn, e -mail -id, fødselsdato og alder. Tabellen begynder ved A1 -celle, der indeholder overskriften 'Navn'. Dataene kører for sige A11. Og rækkerne løber til D1. Hele tabellen kører A1: D11. Du vil have et hentningssystem, der får dig navnet, når du indtaster e -mail -id'et, eller e -mail -id'et, når du indtaster navnet sammen med andre detaljer. Du opretter en formel ved at opfylde de 5 argumenter.
Referencepunkt er cellen, hvorfra opslaget skal starte. I dette tilfælde skal det være A2. Det er den generelle standard.
Eksempel:
For eksempel angiver du reference til B4 og forskydningsrække 0, og forskydning af kolonne 1 returneres værdi fra C4. Forvirrende? Lad os få nogle flere eksempler. Sidste eksempel på OFFSET -funktion fortæller, hvordan man summerer dynamisk.
OFFSET -funktion for at få værdi fra højre og venstre på en celle
Vi har dette bord.
Nu hvis jeg vil få værdi fra 2 celle lige til B2 (hvilket betyder D2). Jeg vil skrive denne OFFSET -formel:
| = OFFSET (B2,0,2) |
OFFSET -funktionen vil flytte 2 celle til højre for celle B4 og returnere dens værdi. Se billedet herunder
Hvis jeg vil få værdi fra 1 celle tilbage til B2 (hvilket betyder A2). Jeg vil skrive denne OFFSET -formel:
| = OFFSET (B2,0, -1) |
Minus (-) tegn angiver forskydning for at flytte omvendt.
OFFSET -funktion for at få værdi nedenfra og over en celle
Så igen i ovenstående tabel, hvis jeg vil få værdi fra 2 celle til B3 (hvilket betyder B5). Jeg vil skrive denne OFFSET -formel:
| = OFFSET (B2,2,0) |
Hvis jeg vil få værdi fra 1 celle tilbage til B2 (hvilket betyder A2). Jeg vil skrive denne OFFSET -formel:
| = OFFSET (B2, -2,0) |
- Pro tip: Behandl en OFFSET som en grafmarkør, hvor Reference er oprindelse og række og kolonne er x- og y -aksen.
OFFSET -funktion til at summe dynamisk
Lad os se dette eksempel.
I tabellen ovenfor Samlet række i slutningen af tabellen. Over tid vil du tilføje rækker til denne tabel. Derefter skal du ændre suminterval i formel. Her kan vi tage hjælp af OFFSET -funktionen til at summere dynamisk. I øjeblikket har vi i celle C11 = SUM (C2: C10). Erstat dette med nedenstående formel.
| = SUM (C2: OFFSET (C11, -1,0)) |
Denne formel tager bare den første datarække og summerer derefter intervallet over den samlede række.
Offset for at få matrix
OFFSET -funktionen har to valgfrie argumenter, [højde] og [bredde]. Tag dem ikke for givet. Når OFFSET -funktionen har [højde] og [bredde] argumenter, returnerer den et todimensionalt array. Hvis kun en af dem sendes som argument, vender den tilbage på et dimensionelt array af værdier.
Hvis du indtaster denne formel i en hvilken som helst celle:
| = SUM (OFFSET (C1,1,0,9,3)) |
Det summerer værdier i intervallet fra C2 til 9 rækker nedenfor og 3 kolonner til højre.
OFFSET (C1,1,0,9,3): Dette vil oversætte 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å afslutningsvis er offset en meget nyttig Excel -funktion, der kan hjælpe dig med at løse mange kvælede tråde. Lad mig vide, hvordan du bruger OFFSET -funktionen i din formel, og hvor det hjalp dig mest.
Opslagningsfunktion er den samme som offset, men bruger funktioner som Match, Counta og IF til at arbejde med en dynamisk tabel.
Brug af offset i Excel kræver dygtighed og godt kendskab til Excels -funktioner og hvordan de fungerer sammen. At trække det af uden fejl er den største hindring.
Her er alle observationsnotater vedrørende brug af formlen.
Bemærkninger:
- Denne formel fungerer både med tekst og tal.
- Der er fem forskydningsargumenter, der skal opfyldes for at få en fejlfri information. Hvis du skriver en forkert formel, opstår der en Ref -fejl.
Håber du har forstået, hvad er Excel Offset -funktion, og hvordan du bruger den i Excel. Udforsk flere artikler om Excel -opslag og match værdier ved hjælp af formler her. Hvis du kunne lide vores blogs, kan du dele den med dine venner på Facebook. Og også du kan følge os på Twitter og Facebook. Vi vil meget gerne høre fra dig, lad os vide, hvordan vi kan forbedre, supplere eller innovere vores arbejde og gøre det bedre for dig. Skriv til os på e -mail -stedet.
VLOOKUP -funktion til beregning af karakter i Excel : At beregne karakterer IF og IFS er ikke de eneste funktioner, du kan bruge. VLOOKUP er mere effektiv og dynamisk til sådanne betingede beregninger. For at beregne karakterer ved hjælp af VLOOKUP kan vi bruge denne formel.
17 Ting om Excel VLOOKUP : VLOOKUP bruges mest til at hente matchede værdier, men VLOOKUP kan meget mere end dette. Her er 17 ting om VLOOKUP, som du bør vide for at bruge effektivt.
LOOKUP den første tekst fra en liste i Excel : VLOOKUP -funktionen fungerer fint med jokertegn. Vi kan bruge dette til at udtrække den første tekstværdi fra en given liste i excel. Her er den generiske formel.
LOOKUP -dato med sidste værdi på listen : For at hente den dato, der indeholder den sidste værdi, bruger vi LOOKUP -funktionen. Denne funktion søger efter cellen, der indeholder den sidste værdi i en vektor og bruger derefter denne reference til at returnere datoen.
Sådan hentes den seneste pris i Excel : Det er almindeligt at opdatere priser i enhver virksomhed, og brug af de seneste priser for ethvert køb eller salg er et must. For at hente den seneste pris fra en liste i Excel bruger vi LOOKUP -funktionen. LOOKUP -funktionen henter den seneste pris.
Populære artikler:
50 Excel -genvej til at øge din produktivitet : Bliv hurtigere til din opgave. Disse 50 genveje får dig til at arbejde endnu hurtigere i Excel.
Sådan bruges IF -funktionen i Excel : IF -sætningen i Excel kontrollerer betingelsen og returnerer en bestemt værdi, hvis betingelsen er SAND, eller returnerer en anden specifik værdi, hvis FALSK.
Sådan bruges VLOOKUP -funktionen i Excel : Dette er en af de mest anvendte og populære funktioner i excel, der bruges til at slå værdi op fra forskellige områder og ark.
Sådan bruges COUNTIF -funktionen i Excel : Tæl værdier med betingelser ved hjælp af denne fantastiske funktion. Du behøver ikke at filtrere dine data for at tælle bestemte værdier. Countif -funktion er afgørende for at forberede dit dashboard.
Sådan bruges SUMIF -funktionen i Excel : Dette er en anden vigtig instrumentbrætfunktion. Dette hjælper dig med at opsummere værdier på bestemte betingelser.
