Prenesite primer delovnega zvezka
Ta vadnica prikazuje, kako uporabljati Excel OFFSET funkcija v Excelu za ustvarjanje referenčnega odmika iz začetne celice.
OFFSET Pregled funkcije
Funkcija OFFSET se zažene z definirano referenco celice in vrne referenco celice določeno število vrstic in stolpcev, odmikanih od prvotnega referenca. Sklici so lahko ena celica ali obseg celic. Odmik vam omogoča tudi spreminjanje velikosti sklica za določeno število vrstic/stolpcev.
(Upoštevajte, kako so prikazani vnosi formule)
Sintaksa in vnosi funkcij IFERROR:
| 1 | = OFFSET (referenca, vrstice, stolpci, višina, širina) |
sklic - Začetna referenca celice, od katere želite odmik.
vrstice - Število vrstic za odmik.
cols - Število stolpcev za odmik.
višina - NEOBVEZNO: prilagodite število vrstic v sklicu.
premer - NEOBVEZNO: prilagodite število stolpcev v sklicu.
Kaj je funkcija OFFSET?
Funkcija OFFSET je ena močnejših funkcij preglednic, saj je lahko zelo vsestranska pri ustvarjanju. Uporabniku daje možnost, da določi celico ali obseg v različnih položajih in velikostih.
POZOR: Funkcija OFFSET je ena od hlapnih funkcij. Večino časa, ko delate v preglednici, bo računalnik ponovno preračunal formulo le, če so vnosi spremenili svoje vrednosti. Hlapna funkcija pa se preračuna vsak čas, ko spremenite katero koli celico. Bodite previdni, da zagotovite, da ne boste povzročili velikega časa ponovnega izračuna zaradi pretirane uporabe hlapnih funkcij ali številnih celic, ki so odvisne od rezultata hlapne funkcije.
Osnovni primeri vrstic
Pri vsaki uporabi funkcije OFFSET morate določiti izhodišče ali sidro. Poglejmo to tabelo, da bi to razumeli:
Kot sidrišče bomo v celici B3 uporabili »Bob«. Če bi radi zgrabili vrednost tik pod (Charlie), bi rekli, da želimo vrstico premakniti za 1. Naša formula bi izgledala tako
| 1 | = OFFSET (B3, 1) |
Če bi se želeli premakniti navzgor, bi bil to negativen premik. O tem si lahko mislite, ker se število vrstic zmanjšuje, zato moramo odšteti. Tako bi zapisali, da bi dobili zgornjo vrednost (Adam)
| 1 | = OFFSET (B2, -1) |
Primeri osnovnih stolpcev
Nadaljujemo idejo iz prejšnjega primera, v našo tabelo bomo dodali še en stolpec.
Če bi radi ujeli učitelja za Boba, bi lahko uporabili formulo
| 1 | = OFFSET (B2, 0, 1) |
V tem primeru smo dejali, da želimo premakniti nič vrstic (ali ostati v isti vrstici), vendar želimo premakniti 1 stolpec. Pozitivno število za stolpce pomeni odmik v desno, negativno pa levo.
OFFSET in MATCH
Recimo, da imate več stolpcev podatkov in želite dati uporabniku možnost, da izbere, iz katerega stolpca bo prišel do rezultatov. Uporabite lahko funkcijo INDEX ali pa OFFSET. Ker bo MATCH vrnil relativni položaj vrednosti, se moramo prepričati, da je sidrna točka levo od naše prve možne vrednosti. Razmislite o naslednji postavitvi:
V B2 bomo zapisali to formulo:
| 1 | = OFFSET (B2, 0, MATCH (A2, $ C $ 1: $ F $ 1, 0)) |
MATCH bo videti "Feb" v območju C1: F1 in ga našel v 2nd celica. OFFSET bo nato pomaknil 1 stolpec desno od B2 in zajel želeno vrednost 9. Upoštevajte, da OFFSET nima težav z uporabo iste celice, ki vsebuje formulo kot sidrišče.
OPOMBA: To tehniko lahko uporabite kot nadomestek za VLOOKUP ali HLOOKUP, če želite vrniti vrednost iz leve/nad območje iskanja. To je zato, ker lahko OFFSET naredi negativne odmike.
OFFSET, da dobite obseg
Uporabite lahko 4th in 5th argumenti v funkciji OFFSET za vrnitev obsega in ne samo ene celice. Recimo, da želite v tej tabeli sešteti 3 stolpce.
| 1 | = POVPREČNO (ODSTOP (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
V F2 smo izbrali ime študenta, za katerega želimo pridobiti njegove povprečne ocene testov. Za to bomo uporabili formulo
| 1 | = POVPREČNO (ODSTOP (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
MATCH bo skozi stolpec A poiskal naše ime in vrnil relativni položaj, ki je v našem primeru 3. Poglejmo, kako se bo to ovrednotilo. Najprej bo potekal OFFSET dol 3 vrstice od A1 in 1 stolpec do prav od A1. To nas uvršča v celico B3.
| 1 | = POVPREČNO (ODSTOP (A1, 3, 1, 1, 3)) |
Nato bomo spremenili obseg. Novi obseg bo imel B3 kot zgornjo levo celico. To bo 1 vrstica visoko in 3 stolpci visoko, kar nam daje razpon B4: D4.
| 1 | = POVPREČNO (ODSTOP (A1,3, 1, 1, 3)) |
Upoštevajte, da lahko v argumente za odmik legitimno vnesete negativne vrednosti, vendar lahko v argumentih velikosti uporabite samo negativne vrednosti.
Na koncu funkcija AVERAGE vidi:
| 1 | = POVPREČNO (B4: D4) |
Tako dobimo rešitev 86,67
OFFSET z dinamičnim SUM
Ker se OFFSET uporablja za iskanje sklica in ne za neposredno usmerjanje na celico, je najbolj koristen, ko imate opravka s podatki, ki imajo dodane ali izbrisane vrstice. Razmislite o naslednji tabeli z vsoto na dnu
| 1 | = SUM (B2: B4) |
Če bi tukaj uporabili osnovno formulo SUM "= SUM (B2: B4)" in nato vstavili novo vrstico, da bi dodali zapis za Bill, bi imeli napačen odgovor
Namesto tega razmislimo, kako to rešiti z vidika Total. Resnično želimo zagrabiti vse od celice B2 do celice malo nad našo celoto. To lahko zapišemo v formulo tako, da naredimo zamik vrstice -1. Tako to uporabljamo kot formulo za naš vsoto v celici B5:
| 1 | = SUM (B2: OFFSET (B5, -1,0)) |
Ta formula naredi to, kar smo pravkar opisali: začnite pri B2 in pojdite na 1 celico nad našo celotno celico. Vidite lahko, kako se po vnosu Billovih podatkov naš skupni rezultat pravilno posodobi.
OFFSET, da dobite zadnjih N elementov
Recimo, da beležite mesečno prodajo, vendar si želite ogledati zadnje 3 mesece. Namesto da morate ročno posodabljati formule, da se bodo lahko prilagajali, ko bodo dodani novi podatki, lahko uporabite funkcijo OFFSET z COUNT.
Pokazali smo že, kako lahko uporabite OFFSET za zajem različnih celic. Če želite ugotoviti, koliko celic moramo premakniti, bomo s številom COUNT našli številke so v stolpcu B. Poglejmo našo vzorčno tabelo.
| 1 | = SUM (ZMON ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Če bi začeli pri B1 in premaknili 4 vrstice (štetje številk v stolpcu B), bi končali na dnu našega območja, B5. Ker pa OFFSET ne moremo spremeniti velikosti z negativno vrednostjo, moramo narediti nekaj prilagoditev, da končamo v B3. Splošna enačba za to bo ustrezna
| 1 | COUNT (…) - N + 1 |
Vzamemo štetje celotnega stolpca, odštejemo toliko, kolikor jih želimo vrniti (ker jim bomo spremenili velikost, da jih zgrabimo), nato pa dodamo 1 (saj v bistvu začnemo odmik pri položaju nič).
Tukaj si lahko ogledate, da smo nastavili obseg za vsoto, povprečje in največ v zadnjih N mesecih. V E1 smo vnesli vrednost 3. V E2 je naša formula
| 1 | = SUM (ZMON ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Poudarjen odsek je naša splošna enačba, o kateri smo pravkar razpravljali. Ni nam treba izravnati nobenega stolpca. Nato bomo obseg spremenili na 3 celice (določeno z vrednostjo v E1) in 1 stolpec v širino. Naš SUM potem zavzame ta razpon in nam da rezultat 1.850 USD. Pokazali smo tudi, da lahko izračunate povprečje max tega istega območja, tako da preprosto preklopite zunanjo funkcijo iz SUM na vse, kar situacija zahteva.
Seznami dinamičnih potrditev OFFSET
S tehniko, prikazano v zadnjem primeru, lahko sestavimo tudi imenovana območja, ki bi jih lahko uporabili pri preverjanju podatkov ali grafikonih. To je lahko v pomoč, če želite nastaviti preglednico, vendar pričakujete, da se bodo naši seznami/podatki spremenili. Recimo, da naša trgovina začenja prodajati sadje in trenutno imamo 3 izbire.
Če želite narediti spustni meni Validacija podatkov, ki ga lahko uporabimo drugje, bomo imenovano območje MyFruit opredelili kot
| 1 | = $ A $ 2: OFFSET ($ A $ 1, COUNTA ($ A: $ A) -1, 0) |
Namesto COUNT uporabljamo COUNTA, saj imamo opravka z besedilnimi vrednostmi. Zaradi tega bo naše število COUNTA eno višje, saj bo štelo glavno celico v A1 in dalo vrednost 4. Če zamaknemo za 4 vrstice, bi končali v celici A5, ki je prazna. Če se želimo temu prilagoditi, odštejemo 1.
Zdaj, ko imamo nastavljeno imenovano območje, lahko v celici C4 nastavimo nekaj preverjanja podatkov z uporabo vrste List z virom:
| 1 | = MyFruit |
Upoštevajte, da spustni meni prikazuje samo naše tri trenutne postavke. Če nato na seznam dodamo več elementov in se vrnemo na spustni meni, bo na seznamu prikazane vse nove postavke, ne da bi nam bilo treba spreminjati katero koli formulo.
Previdnost pri uporabi OFFSET
Kot je bilo omenjeno na začetku tega članka, je OFFSET hlapna funkcija. Tega ne boste opazili, če ga uporabljate le v nekaj celicah, če pa ga začnete vključevati v stotine izračunov, boste hitro opazili, da vaš računalnik porabi opazen čas za preračun vsakič, ko naredite kakršne koli spremembe .
Poleg tega, ker OFFSET ne imenuje neposredno celic, ki jih gleda, drugi uporabniki težje pridejo pozneje in po potrebi spremenijo formule.
Namesto tega bi bilo priporočljivo uporabiti tabele (uvedene v Officeu 2007), ki omogočajo strukturne reference. To je pomagalo uporabnikom, da podajo eno referenco, ki se samodejno prilagodi po velikosti, ko so bili dodani ali izbrisani novi podatki.
Druga možnost za uporabo namesto OFFSET je zmogljiva funkcija INDEX. INDEX vam omogoča, da zgradite vsa dinamična območja, ki smo jih videli v tem članku, ne da bi bila to nestanovitna funkcija.
Dodatne opombe
S funkcijo OFFSET vrnite vrednost celice (ali obseg celic) tako, da premaknete dano število vrstic in stolpcev od začetnega sklica. Kadar iščejo samo eno celico, formule OFFSET z nekoliko drugačno tehniko dosežejo enak namen kot formule INDEX. Resnična moč funkcije OFFSET je v njeni sposobnosti, da izbere obseg celic, ki jih bo uporabil v drugi formuli.
Pri uporabi funkcije OFFSET določite začetno začetno celico ali obseg celic. Nato navedete število vrstic in stolpcev za odmik od te začetne celice. Obseg lahko tudi spremenite; dodajanje ali odštevanje vrstic ali stolpcev.
Vrnite se na seznam vseh funkcij v Excelu
OFFSET v Google Preglednicah
Funkcija OFFSET deluje v Google Preglednicah popolnoma enako kot v Excelu:
