Kaip naudotis „Excel OFFSET“ funkcija -

Santrauka

„Excel OFFSET“ funkcija pateikia nuorodą į diapazoną, sukurtą iš penkių įėjimų: (1) pradžios taško, (2) eilutės poslinkio, (3) stulpelio poslinkio, (4) aukščio eilutėse, (5) pločio stulpeliai. OFFSET yra naudinga formulėse, kurioms reikalingas dinaminis diapazonas.

Tikslas

Sukurkite atskaitos poslinkį nuo pradinio taško

Grąžinimo vertė

Langelio nuoroda.

Sintaksė

= OFFSET (nuoroda, eilutės, stulpeliai, (aukštis), (plotis))

Argumentai

  • atskaitos taškas - pradinis taškas, pateikiamas kaip langelio nuoroda arba diapazonas.
  • eilutės - eilučių skaičius, kurį reikia kompensuoti žemiau pradinės atskaitos.
  • stulpeliai - stulpelių skaičius, perkeliamas dešinėje nuo pradinės nuorodos.
  • aukštis - (nebūtina) Grąžintos nuorodos eilutės aukštis.
  • plotis - (neprivaloma) Pateiktos nuorodos plotis stulpeliuose.

Versija

„Excel 2003“

Naudojimo užrašai

„Excel OFFSET“ funkcija pateikia dinaminį diapazoną, sukurtą iš penkių įėjimų: (1) pradžios taško, (2) eilutės poslinkio, (3) stulpelio poslinkio, (4) aukščio eilutėse, (5) pločio stulpeliuose.

Pradinis taškas ( pamatinis argumentas) gali būti viena langelis arba langelių diapazonas. Į eilutės ir COLS argumentai ląstelių skaičius "kompensuoti" iš pradinio taško. Kad aukštis ir plotis argumentai yra pasirenkamas ir nustatyti diapazono, kad yra sukurtos dydį. Kai aukštis ir plotis praleidžiami, pagal numatytuosius nustatymus jie turi atskaitos aukštį ir plotį .

Pavyzdžiui, nuoroda C5, prasidedanti A1, nuoroda yra A1, eilutės yra 4 ir stulpeliai yra 2:

=OFFSET(A1,4,2) // returns reference to C5

Jei norite nurodyti C1: C5 iš A1, nuoroda yra A1, eilutės yra 0, stulpeliai yra 2, aukštis yra 5 ir plotis yra 1:

=OFFSET(A1,0,2,5,1) // returns reference to C1:C5

Pastaba: plotis gali būti praleistas, nes pagal numatytuosius nustatymus jis bus 1.

Įprasta matyti, kad OFFSET yra suvyniotas į kitą funkciją, kuri tikisi diapazono. Pavyzdžiui, į SUM C1: C5, prasidedantį A1:

=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5

Pagrindinis OFFSET tikslas yra leisti formulėms dinamiškai prisitaikyti prie turimų duomenų ar vartotojo įvesties. Funkcija OFFSET gali būti naudojama kuriant dinaminį pavadintą diagramų ar suvestinių lentelių diapazoną, siekiant užtikrinti, kad šaltinio duomenys visada būtų atnaujinami.

Pastaba: „Excel“ dokumentuose teigiama, kad aukštis ir plotis negali būti neigiami, tačiau neigiamos vertės gerai veikė nuo 1990-ųjų pradžios. „Google“ skaičiuoklių funkcija OFFSET neleis neigiamos aukščio ar pločio argumentų vertės.

Pavyzdžiai

Toliau pateiktuose pavyzdžiuose parodyta, kaip galima sukonfigūruoti OFFSET, kad būtų pateikti skirtingi diapazonai. Šie ekranai buvo padaryti naudojant „Excel 365“, todėl OFFSET pateikia dinaminį masyvą, kai rezultatas yra daugiau nei vienas langelis. Senesnėse „Excel“ versijose galite naudoti klavišą F9, kad patikrintumėte rezultatus, gautus iš OFFSET.

1 pavyzdys

Žemiau esančiame ekrane mes naudojame OFFSET, kad grąžintume trečiąją vertę (kovo mėn.) Antrame stulpelyje (Vakarai). H4 formulė yra:

=OFFSET(B3,3,2) // returns D6

2 pavyzdys

Žemiau esančiame ekrane mes naudojame OFFSET, kad grąžintume paskutinę vertę (birželio mėn.) Trečiajame stulpelyje (šiaurė). H4 formulė yra:

=OFFSET(B3,6,3) // returns E9

3 pavyzdys

Žemiau mes naudojame OFFSET, kad grąžintume visas vertes trečiame stulpelyje (šiaurė). H4 formulė yra:

=OFFSET(B3,1,3,6) // returns E4:E9

4 pavyzdys

Žemiau mes naudojame OFFSET, kad grąžintume visas gegužės mėnesio vertes (penkta eilutė). H4 formulė yra:

=OFFSET(B3,5,1,1,4) // returns C8:F8

5 pavyzdys

Žemiau mes naudojame OFFSET, kad grąžintume balandžio, gegužės ir birželio vertes Vakarų regionui. H4 formulė yra:

=OFFSET(B3,4,2,3,1) // returns D7:D9

6 pavyzdys

Žemiau mes naudojame OFFSET, kad grąžintume balandžio, gegužės ir birželio vertes Vakarams ir Šiaurei. H4 formulė yra:

=OFFSET(B3,4,2,3,2) // returns D7:E9

Pastabos

  • OFFSET pateikia tik nuorodą, joks langelis nėra perkeltas.
  • Tiek eilutės, tiek stulpeliai gali būti pateikiami kaip neigiami skaičiai, kad būtų pakeista jų įprasta poslinkio kryptis - neigiamos stulpeliai pasislenka į kairę, o neigiamos eilutės - aukščiau.
  • OFFSET yra „nepastovi funkcija“ - ji perskaičiuos kiekvieną pakeitimą darbalapyje. Dėl nepastovių funkcijų didesnės ir sudėtingesnės darbaknygės gali veikti lėtai.
  • OFFSET rodys #REF! klaidos reikšmė, jei poslinkis yra už darbalapio krašto.
  • Kai aukštis ar plotis nenurodomi, naudojami atskaitos aukštis ir plotis .
  • OFFSET galima naudoti su bet kuria kita funkcija, kuri tikisi gauti nuorodą.
  • „Excel“ dokumentuose sakoma, kad aukštis ir plotis negali būti neigiami, tačiau neigiamos vertės veikia.

Susiję vaizdo įrašai

Kaip sukurti dinaminį pavadintą diapazoną naudojant OFFSET Šiame vaizdo įraše apžvelgsime, kaip sukurti dinaminį pavadintą diapazoną naudojant funkciją OFFSET - dažniausiai naudojamą būdą sukurti dinaminių pavadinimų diapazoną naudojant formulę.

Įdomios straipsniai...