Excel OFFSET funkcija - Primeri formul + BREZPLAČEN video

Excel OFFSET funkcija (primer + video)

Kdaj uporabiti funkcijo Excel OFFSET

Excel OFFSET funkcijo lahko uporabite, če želite dobiti referenco, ki odmakne določeno število vrstic in stolpcev od začetne točke.

Kaj vrača

Vrne sklic, na katerega kaže funkcija OFFSET.

Sintaksa

= OFFSET (referenca, vrstice, stolpci, [višina], [širina])

Argumenti vnosa

• referenca - Referenca, od katere želite izravnati. To je lahko referenca celice ali niz sosednjih celic.
• vrstice - število vrstic za odmik. Če uporabite pozitivno število, se premakne v spodnje vrstice, če pa uporabite negativno število, se premakne v zgornje vrstice.
• cols - število stolpcev za odmik. Če uporabite pozitivno število, se odmika od stolpcev na desni, če pa uporabite negativno število, se odmika od stolpcev na levi.
• [višina] - to je število, ki predstavlja število vrstic v vrnjenem sklicu.
• [premer] - to je število, ki predstavlja število stolpcev v vrnjenem sklicu.

Razumevanje osnov funkcije Excel OFFSET

Excel OFFSET funkcija je morda ena najbolj zmedenih funkcij v Excelu.

Vzemimo preprost primer šahovske igre. V šahu je del, imenovan Rook (znan tudi kot stolp, markiz ali rektor).

[Vljudnost slike: Čudovita Wikipedia]

Tako ima kot vse druge šahovske figure tudi topa fiksno pot, po kateri se lahko premika po tabli. Lahko gre naravnost (v desno/levo ali navzgor/navzdol po deski), ne pa diagonalno.

Denimo, na primer, da imamo šahovsko tablo v Excelu (kot je prikazano spodaj), v danem polju (v tem primeru D5) lahko torek hodi le v štirih označenih smereh.

Če vas prosim, da prestavite Roka s trenutnega položaja na tistega, označenega z rumeno, kaj boste povedali topu?

Od njega boste zahtevali dva koraka navzdol in dva koraka v desno … kajne?

In to je približek delovanja funkcije OFFSET.

Zdaj pa poglejmo, kaj to pomeni v Excelu. Želim začeti s celico D5 (kjer je Rook), nato pa pojdim dve vrstici navzdol in dva stolpca v desno in iz tam pridobim vrednost.

Formula bi bila naslednja:
= OFFSET (od kje začeti, koliko vrstic navzdol, koliko stolpcev na desni)

Kot lahko vidite, je formula v zgornjem primeru v celici J1 = OFFSET (D5,2,2).

Začelo se je pri D5, nato pa je šlo dve vrstici navzdol in dva stolpca na desno ter doseglo celico F7. Nato je vrnilo vrednost v celico F7.

V zgornjem primeru smo funkcijo OFFSET pogledali s tremi argumenti. Obstajata pa lahko še dva izbirna argumenta.

Poglejmo preprost primer tukaj:

Recimo, da želite uporabiti sklic na celico A1 (v rumeni barvi) in se želite sklicevati na celotno območje, označeno z modro barvo (C2: E4) v formuli.

Kako bi to naredili s tipkovnico? Najprej pojdite v celico C2 in nato izberite vse celice v C2: E4.

Zdaj pa poglejmo, kako to storiti s formulo OFFSET:

= OFFSET (A1,1,2,3,3)

Če uporabite to formulo v celici, bo vrnila #VALUE! napaka, če pa vstopite v način urejanja in izberete formulo ter pritisnete F9, boste videli, da vrne vse vrednosti, označene z modro barvo.

Zdaj pa poglejmo, kako deluje ta formula:

= OFFSET (A1,1,2,3,3)

• Prvi argument je celica, kjer se mora začeti.
• Drugi argument je 1, ki Excelu pove, naj vrne sklic, ki je bil za 1 vrsticO OFFSET.
• Tretji argument je 2, ki Excelu pove, naj vrne sklic, ki je bil za 2 stolpca OFFSET.
• Četrti argument je 3. To določa, da mora sklic zajemati 3 vrstice. To se imenuje argument višine.
• Peti argument je 3. To določa, da mora sklic zajemati 3 stolpce. To se imenuje argument širine.

Zdaj, ko se sklicujete na obseg celic (C2: E4), ga lahko uporabite v drugih funkcijah (na primer SUM, COUNT, MAX, AVERAGE).

Upajmo, da dobro razumete uporabo funkcije Excel OFFSET. Zdaj pa poglejmo nekaj praktičnih primerov uporabe funkcije OFFSET.

Excel OFFSET funkcija - primeri

Tu sta dva primera uporabe Excel OFFSET funkcije.

Primer 1 - Iskanje zadnje zapolnjene celice v stolpcu

Recimo, da imate v stolpcu nekaj podatkov, kot je prikazano spodaj:

Zadnjo celico v stolpcu poiščite po naslednji formuli:

= OFFSET (A1, COUNT (A: A) -1,0)

Ta formula predvideva, da razen prikazanih vrednosti ni in te celice nimajo prazne celice. Deluje tako, da šteje skupno število celic, ki so napolnjene, in ustrezno premakne celico A1.

Na primer, v tem primeru je 8 vrednosti, zato COUNT (A: A) vrne 8. Celico A1 premaknemo za 7, da dobimo zadnjo vrednost.

Primer 2 - Ustvarjanje dinamičnega spustnega menija

Konceptne razstave v primeru 1 lahko razširite, da ustvarite dinamična imenovana območja. Te so lahko koristne, če uporabljate imenovane obsege v formulah ali ustvarjate spustne menije in boste verjetno dodali/izbrisali podatke iz sklica, ki določa imenovani obseg.

Tukaj je primer:

Upoštevajte, da se spustni meni samodejno prilagodi, ko se leto doda ali odstrani.

To se zgodi, ko je formula, ki se uporablja za ustvarjanje spustnega menija, dinamična in identificira morebitno dodajanje ali brisanje ter ustrezno prilagodi obseg.

Evo, kako to storiti:

• Spustni meni izberite celico, kamor želite vstaviti.
• Pojdite na Podatki -> Podatkovna orodja -> Preverjanje podatkov.
• V pogovornem oknu Preverjanje podatkov na zavihku Nastavitve v spustnem meniju izberite Seznam.
• V vir vnesite naslednjo formulo: = OFFSET (A1,0,0, COUNT (A: A), 1)
• Kliknite V redu.

Poglejmo, kako deluje ta formula:

• Prvi trije argumenti funkcije OFFSET so A1, 0 in 0. To v bistvu pomeni, da bi vrnila isto referenčno celico (to je A1).
• Četrti argument je za višino in tukaj funkcija COUNT vrne skupno število elementov na seznamu. Predpostavlja, da na seznamu ni praznih mest.
• Peti argument je 1, kar pomeni, da mora biti širina stolpca ena.

Dodatne opombe:

• OFFSET je hlapna funkcija (uporabljajte previdno).
  • Ponovno izračuna, kadar je delovni zvezek Excel odprt ali kadar se na delovnem listu sproži izračun. To bi lahko podaljšalo čas obdelave in upočasnilo vaš delovni zvezek.
• Če je vrednost višine ali širine izpuščena, se vzame kot vrednost referenčne vrednosti.
• Če vrstice in cols so negativna števila, potem je smer odmika obrnjena.

Alternative funkcije Excel OFFSET

Zaradi nekaterih omejitev funkcije Excel OFFSET bi radi razmislili o drugih možnostih:

• Funkcija INDEX: Funkcijo INDEX lahko uporabite tudi za vrnitev sklica na celico. Kliknite tukaj, če si želite ogledati primer ustvarjanja dinamičnega poimenovanega obsega s funkcijo INDEX.
• Excel Tabela: Če uporabljate strukturirane sklice v Excelovi tabeli, vam ni treba skrbeti, da bodo dodani novi podatki in da je treba prilagoditi formule.

Excel OFFSET funkcija - video vadnica

• Excel VLOOKUP funkcija.
• Excel HLOOKUP funkcija.
• Excel INDEX funkcija.
• Excel INDIRECT funkcija.
• Excel MATCH funkcija.