Excel OFFSET Formula (Vsebina)

  • Kaj je OFFSET Formula v Excelu?
  • Kako uporabljati formulo OFFSET v Excelu?

Kaj je OFFSET Formula v Excelu?

Formula OFFSET v Excelu daje celico ali obseg dinamičnih pravokotnih celic kot izhod, ki je podniz skupnega danega obsega z določenim številom vrstic, stolpcev, višine in širine.

Sintaksa formule OFFSET v Excelu

Pojasnilo funkcije OFFSET v Excelu:

  • sklic: Izhodišče za OFFSET ali lahko rečemo obseg celic, ki se lahko štejejo za bazo za OFFSET.
  • vrstice : Število vrstic v OFFSET pod ali nad referenčno referenco.
  • cols: Število stolpcev za OFFSET desno ali levo od referenčne baze.
  • višina: Število vrstic v vrnjeni referenci.
  • širina: Število stolpcev v vrnjeni referenci.

Višina in širina sta neobvezna parametra za to funkcijo in se lahko uporabljata za določitev velikosti ustvarjene reference.

Kako uporabljati formulo OFFSET v Excelu?

Zdaj se bomo naučili, kako napisati odmično formulo, da dobimo vrednost iskanja. Razjasnimo formulo izravnave z nekaj primeri v Excelu.

To predlogo OFFSET Formula Excel lahko prenesete tukaj - OFFSET Predloga formule Excel

Excel OFFSET Formula - Primer 1

Recimo, da imamo podatke, kot so navedeni spodaj.

  • V celico E2 vnesite naslednjo formulo:

= OFFSET (A1, 11, 1)

  • Ko končate s formulo, pritisnite Enter in si oglejte izhod.

Kar je ta formula storila, se je sklicevala na celico A1 (leto) in se pomaknila za 11 vrstic po A1 (tj. Vrstica št. 12) in nato pridobila vrednost, ki je koordinata 11. vrstice po A1 in prvem stolpcu ( stolpec A = 0 v tem primeru stolpec B = 1. Kot vemo, lahko gremo tako desno kot levo). Kar je 141, 27 milijona dolarjev.

Excel OFFSET Formula - Primer # 2

Zdaj bomo videli še en primer referenčne celice za določeno leto. V celico E3 vstavite naslednjo formulo:

= OFFSET (A1, 11, 0)

  • Ko končate s formulo, pritisnite Enter in si oglejte izhod.

Če ste videli formulo, je enaka prejšnji, edina sprememba je v sklicu na stolpec. Referenca stolpca 0 (nič) pomeni, da bo vrednost zajeta iz stolpca A za 11. vrstico po A1 (to je 2010).

Excel OFFSET Formula - Primer # 3

V tem primeru bomo izračunali vsoto zadnjih N vrstic s pomočjo funkcije Excel OFFSET. Splošna formula za seštevanje zadnjih N številk je:

= SUM (OFFSET (A1, COUNT (A: A), 0, -N))

Če povzamemo zadnjih N opazovanj iz vaših podatkov, lahko uporabite funkcijo SUM skupaj s funkcijo OFFSET in COUNT skupaj. V tem primeru bomo povzeli zadnjih 5 podatkovnih točk. Upoštevajte, da ko rečemo N, gre za posplošitev katere koli celotne vrednosti. Torej, lahko izračunate za zadnje 3, zadnje 4, zadnjih 5 itd., Odvisno od vašega podatkovnega bazena in vaše zahteve.

Naj to storimo za naš nabor podatkov:

  • V celico E2 datoteke excel vnesite naslednjo formulo.

= SUM (OFFSET (B1, COUNT (B: B), 0, -5))

  • Za prikaz izhoda kliknite Enter.

Kot smo že videli, funkcija Excel OFFSET oddaja pravokotna dinamična območja, ko je dana začetno referenco.

  • V tem primeru je začetna referenca podana v smislu B1 (kot prvi argument funkcije OFFSET).
  • Uporabili smo funkcijo COUNT, ker potrebujemo vsoto opazovanj od zadnje vrednosti. Funkcija COUNT pomaga OFFSETu določiti, koliko opazovanj (vrstic) je v stolpcu B. Šteje vse številčne vrednosti v stolpcu B (19 je število).
  • Z štetjem 19 postane formula OFFSET:

= OFFSET (B1, 19, 0, -5)

  • Ta formula začne izravnati pri B1 z 19 vrsticami in istim stolpcem (0). Nato parameter višina -5 pomaga tej funkciji, da razširi območje v smer nazaj za 5 vrstic (glej negativni znak, povezan s 5).
  • Dinamični razpon zadnjih petih opazovanj je podan kot funkcija vnosa v seštevek. Kateri dobiček sešteva tiste iz stolpca B.

Excel OFFSET Formula - Primer # 4

V tem primeru bomo izračunali srednjo prodajo za zadnja 3 leta. Funkcijo OFFSET lahko uporabite kot argument za izračun iste.

  • V celico E2 vstavite naslednjo formulo:

= MEDIAN (OFFSET (B1, COUNT (B: B), 0, -3))

  • Pritisnite Enter za ogled izhoda.

Ta formula deluje na črte, podobne prejšnji.

  • OFFSET Ustvari dinamičen obseg zadnjih treh opazovanj iz stolpca B s pomočjo funkcije COUNT (= OFFSET (A1, 19, 0, -3)).
  • To je mogoče dati kot rezultat za funkcijo MEDIAN. Ta nato opazke razvrsti po naraščajočem ali padajočem vrstnem redu in nato izbere srednjo največjo vrednost, kot vidite v rezultatih (212, 07 USD).

Excel OFFSET Formula - Primer # 5

Tukaj bomo izračunali srednje leto z uporabo funkcije MEDIAN in OFFSET.

  • V celici E3 uporabite naslednjo formulo:

= MEDIAN (OFFSET (A1, COUNT (A: A), 0, -3))

  • Za prikaz izhoda kliknite Enter.

Tu se je zgodilo isto. Formula je zavzela dinamično območje, ki ga je ustvarila funkcija OFFSET s pomočjo COUNT. In ga uporabil kot argument za funkcijo MEDIAN, ki je nato letne vrednosti razvrščala po naraščajočem ali padajočem vrstnem redu in nato dala rezultat kot leto 2017.

Uporaba OFFSET formule je v ustvarjanju dinamičnih razponov. Vedno je bolje delati na dinamičnih razponih namesto na statičnih. Kot morda imate podatke, ki se jih vsakič doda. Na primer, zaposleni mojster. Glavni podatki o zaposlenem se bodo vedno znova povečevali, saj se morajo pridružiti novi zaposleni. Vsakič, ko uporaba iste formule nima nobenega smisla. Namesto tega ustvarite obseg, ki je dinamičen s funkcijo OFFSET in si oglejte čarovnijo. Zagotovo vam bo prihranilo čas.

Stvari, ki jih je treba zapomniti

  • Funkcija OFFSET vrne samo sklic na določen obseg celic v Excelu. Celice ne premika iz svojih prvotnih položajev.
  • Ta funkcija privzeto deluje v zgornjem delu (v smislu vrstic) in na desni strani (v smislu stolpcev). Vendar pa lahko dobite negativne argumente za razveljavitev privzete funkcionalnosti. Tako kot -negativni argument vrstice pomaga izravnati podatke od navzdol navzgor, negativni argument stolpca pa pobota podatke na levi strani.
  • Formula OFFSET lahko upočasni funkcionalnost programa Excel, saj gre za formulo, ki se preračuna ob vsaki spremembi delovnega lista.
  • #REF napaka bo odstranjena, če je obseg zunaj obsega vašega delovnega zvezka.
  • Vsaka druga funkcija, ki pričakuje sklic, lahko v njej uporablja funkcijo OFFSET.

Priporočeni članki

To je vodnik za OFFSET Formulo v Excelu. Tukaj smo razpravljali o uporabi funkcije OFFSET v Excelu skupaj s praktičnimi primeri in naloženo predlogo Excela. Ogledate si lahko tudi druge naše predlagane članke -

  1. Kako zaviti besedilo v Excelu?
  2. Preprost vodnik za razvrščanje v Excelu
  3. LOOKUP funkcija v Excelu
  4. Kako uporabljati FIND funkcijo v Excelu?

Kategorija:

Excel Nasveti