Atsisiųskite darbaknygės pavyzdį
Ši pamoka parodo, kaip naudotis „Excel OFFSET“ funkcija „Excel“, kad sukurtumėte atskaitos poslinkį iš pradinio langelio.
OFFSET funkcijų apžvalga
Funkcija OFFSET prasideda nuo apibrėžtos langelio nuorodos ir grąžina langelio nuorodą nurodytą skaičių eilučių ir stulpelių, nukrypusių nuo pradinės nuorodos. Nuorodos gali būti viena ląstelė arba langelių diapazonas. Poslinkis taip pat leidžia pakeisti nuorodos dydį tam tikru eilučių/stulpelių skaičiumi.
(Atkreipkite dėmesį, kaip atrodo formulės įvestys)
IFERROR funkcijos sintaksė ir įvestys:
| 1 | = OFFSET (nuoroda, eilutės, stulpeliai, aukštis, plotis) |
nuoroda - pradinė langelio nuoroda, kurią norite kompensuoti.
eilutės - Eilių, kurias reikia kompensuoti, skaičius.
cols - Stulpelių, kuriuos reikia kompensuoti, skaičius.
aukščio - PASIRENKAMA: koreguokite nuorodos eilučių skaičių.
plotis - PASIRENKAMA: koreguokite nuorodos stulpelių skaičių.
Kas yra OFFSET funkcija?
Funkcija OFFSET yra viena iš galingesnių skaičiuoklės funkcijų, nes ji gali būti gana universali kuriant. Tai suteikia vartotojui galimybę apibrėžti langelį ar diapazoną įvairiomis padėtimis ir dydžiais.
ATSARGIAI: OFFSET funkcija yra viena iš nepastovių funkcijų. Dažniausiai dirbant skaičiuoklėje kompiuteris iš naujo apskaičiuos formulę tik tuo atveju, jei įvestys pakeis reikšmes. Tačiau nepastovi funkcija perskaičiuoja kiekvieną kai pakeisite bet kurią langelį. Turėtumėte būti atsargūs, kad nesukeltumėte ilgo perskaičiavimo laiko dėl pernelyg didelio lakiųjų funkcijų naudojimo arba dėl daugybės langelių, priklausančių nuo nepastovios funkcijos rezultato.
Pagrindiniai eilutės pavyzdžiai
Kiekvieną kartą naudodami funkciją OFFSET, turite nurodyti atskaitos tašką arba inkarą. Norėdami tai suprasti, pažvelkime į šią lentelę:
B3 langelyje naudosime „Bobą“ kaip tvirtinimo tašką. Jei norėtume paimti žemiau esančią vertę (Charlie), sakytume, kad norime perkelti eilutę 1. Mūsų formulė atrodytų taip
| 1 | = Poslinkis (B3, 1) |
Jei norėtume pereiti aukštyn, tai būtų neigiamas poslinkis. Galite galvoti apie tai, nes eilučių skaičius mažėja, todėl turime atimti. Taigi, norėdami gauti aukščiau esančią vertę (Adomas), rašytume
| 1 | = Poslinkis (B2, -1) |
Pagrindiniai stulpelių pavyzdžiai
Tęsdami ankstesnio pavyzdžio idėją, prie lentelės pridėsime dar vieną stulpelį.
Jei norėtume patraukti mokytoją Bobui, galėtume naudoti formulę
| 1 | = Poslinkis (B2, 0, 1) |
Šiuo atveju mes sakėme, kad norime atsverti nulines eilutes (dar žinomas kaip likti toje pačioje eilutėje), bet norime kompensuoti 1 stulpelį. Stulpeliuose teigiamas skaičius reiškia poslinkį į dešinę, o neigiamas skaičius reiškia poslinkį į kairę.
OFFSET ir MATCH
Tarkime, kad turite kelis duomenų stulpelius ir norite suteikti vartotojui galimybę pasirinkti, iš kurio stulpelio gauti rezultatus. Galite naudoti INDEX funkciją arba OFFSET. Kadangi MATCH grąžins santykinę vertės vietą, turėsime įsitikinti, kad tvirtinimo taškas yra kairėje nuo pirmosios galimos vertės. Apsvarstykite šį išdėstymą:
B2, mes parašysime šią formulę:
| 1 | = OFFSET (B2, 0, MATCH (A2, $ C $ 1: $ F $ 1, 0)) |
„MATCH“ atrodys „vasario“ diapazone C1: F1 ir ras jį 2antra ląstelė. Tada poslinkis perkelia 1 stulpelį į dešinę nuo B2 ir paima norimą vertę 9. Atminkite, kad OFFSET neturi problemų naudojant tą patį langelį, kuriame yra formulė kaip tvirtinimo taškas.
PASTABA: Šią techniką galima naudoti kaip „VLOOKUP“ arba „HLOOKUP“ pakeitimą, kai norite grąžinti vertę iš kairės/virš jūsų paieškos diapazono. Taip yra todėl, kad OFFSET gali padaryti neigiamų poslinkių.
OFFSET, kad gautumėte diapazoną
Galite naudoti 4tūkst ir 5tūkst Funkcijos OFFSET argumentai grąžina diapazoną, o ne tik vieną langelį. Tarkime, kad šioje lentelėje norėjote apibendrinti 3 stulpelius.
| 1 | = VIDUTINIS (NUOSTATA (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
F2 pasirinkome mokinio, kuriam norime gauti vidutinį testo rezultatą, vardą. Norėdami tai padaryti, naudosime formulę
| 1 | = VIDUTINIS (NUOSTATA (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
MATCH ketina ieškoti A stulpelio mūsų vardo ir grąžinti santykinę poziciją, kuri yra 3 mūsų pavyzdyje. Pažiūrėkime, kaip tai bus įvertinta. Pirma, OFFSET eis žemyn 3 eilutės nuo A1 ir 1 stulpelis iki teisingai nuo A1. Tai leidžia mums patekti į B3 langelį.
| 1 | = VIDUTINIS (poslinkis (A1, 3, 1, 1, 3)) |
Toliau mes pakeisime diapazono dydį. Naujajame diapazone viršutinė kairioji ląstelė bus B3. Jis bus 1 eilutės aukščio ir 3 stulpelių aukščio, suteikiant mums diapazoną B4: D4.
| 1 | = VIDUTINIS (poslinkis (A1,3, 1, 1, 3)) |
Atminkite, kad nors jūs galite teisėtai įdėti neigiamas vertes į kompensavimo argumentus, galite naudoti tik neigiamas reikšmes dydžio nustatymo argumentuose.
Pabaigoje mūsų AVERAGE funkcija mato:
| 1 | = VIDUTINIS (B4: D4) |
Taigi mes gauname 86,67 sprendimą
PAKLAIDA su dinamiška SUM
Kadangi OFFSET naudojamas nuorodai rasti, o ne tiesiogiai nukreipti į langelį, tai labiausiai naudinga, kai susiduriate su duomenimis, kurių eilutės pridėtos arba ištrintos. Apsvarstykite šią lentelę, kurios apačioje yra sumos
| 1 | = SUMA (B2: B4) |
Jei čia būtume naudojęsi pagrindine SUM formule „= SUM (B2: B4)“ ir įterptume naują eilutę, kad pridėtume Billo įrašą, turėtume neteisingą atsakymą
Vietoj to, pagalvokime, kaip tai išspręsti „Total“ požiūriu. Mes tikrai norime patraukti viską - nuo ląstelės B2 iki ląstelės šiek tiek virš mūsų sumos. Tai, kaip mes galime tai parašyti formulėje, yra padaryti eilutės poslinkį -1. Taigi, mes naudojame tai kaip formulę B5 ląstelėje:
| 1 | = SUMA (B2: poslinkis (B5, -1,0)) |
Ši formulė daro tai, ką ką tik aprašėme: pradėkite nuo B2 ir eikite į 1 langelį virš mūsų visos ląstelės. Galite pamatyti, kaip pridėjus Bilo duomenis, mūsų bendra suma bus tinkamai atnaujinta.
OFFSET, kad gautumėte paskutinius N elementus
Tarkime, kad registruojate mėnesio pardavimus, bet norite matyti paskutinius 3 mėnesius. Užuot rankiniu būdu atnaujinę formules, kad būtų galima koreguoti, kai pridedami nauji duomenys, galite naudoti funkciją OFFSET su COUNT.
Mes jau parodėme, kaip galite naudoti OFFSET, norėdami paimti daugybę ląstelių. Norėdami nustatyti, kiek ląstelių turime perkelti, mes naudosime COUNT, kad surastume, kiek skaičių yra stulpelyje B. Pažvelkime į mūsų pavyzdinę lentelę.
| 1 | = SUMA (OFFS ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Jei pradėtume nuo B1 ir kompensuotume 4 eilutes (skaičių B stulpelyje), atsidurtume savo diapazono apačioje, B5. Tačiau, kadangi OFFSET negali pakeisti neigiamos vertės, turime šiek tiek pakoreguoti, kad galiausiai atsidurtume B3. Tam bus atlikta bendra lygtis
| 1 | SKAIČIUS (…) - N + 1 |
Mes apskaičiuojame viso stulpelio skaičių, atimame tiek, kiek norime grąžinti (nes pakeisime dydį, kad juos sugriebtume), ir tada pridedame 1 (nes mes iš esmės pradedame poslinkį nuo nulio).
Čia galite pamatyti, kad nustatėme diapazoną, kad gautume paskutinių N mėnesių sumą, vidurkį ir maks. E1 įvedėme vertę 3. E2 mūsų formulė yra
| 1 | = SUMA (OFFS ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Išryškintas skyrius yra mūsų bendroji lygtis, kurią ką tik aptarėme. Mums nereikia keisti jokių stulpelių. Tada pakeisime diapazono dydį, kad jis būtų 3 langelių aukščio (nustatomas pagal E1 reikšmę) ir 1 stulpelio pločio. Tada mūsų SUM paima šį diapazoną ir duoda mums 1 850 USD rezultatą. Mes taip pat parodėme, kad galite apskaičiuoti to paties diapazono maksimumą, tiesiog perjungdami išorinę funkciją iš SUM į bet kokią situaciją.
OFFSET dinaminio patvirtinimo sąrašai
Naudodami paskutiniame pavyzdyje parodytą metodą, taip pat galime sukurti pavadintus diapazonus, kurie galėtų būti naudojami duomenų patvirtinimui ar diagramoms. Tai gali būti naudinga, kai norite nustatyti skaičiuoklę, bet tikitės, kad mūsų sąrašai/duomenys pasikeis. Tarkime, kad mūsų parduotuvė pradeda prekiauti vaisiais ir šiuo metu turime 3 pasirinkimus.
Norėdami sukurti išskleidžiamąjį duomenų patvirtinimo meniu, kurį galime naudoti kitur, apibrėžsime pavadintą diapazoną „MyFruit“
| 1 | = $ A $ 2: OFFSET ($ A $ 1, COUNTA ($ A: $ A) -1, 0) |
Vietoj COUNT, mes naudojame COUNTA, nes susiduriame su teksto vertėmis. Tačiau dėl to mūsų COUNTA bus viena aukštesnė, nes ji suskaičiuos antraštės langelį A1 ir suteiks vertę 4. Jei vis dėlto kompensuosime 4 eilutes, atsidursime tuščiame A5 langelyje. Norėdami tai pritaikyti, atimame 1.
Dabar, kai turime sąranką „Vardinis diapazonas“, galime nustatyti tam tikrą duomenų patvirtinimą langelyje C4 naudodami sąrašo tipą su šaltiniu:
| 1 | = MyFruit |
Atminkite, kad išskleidžiamajame meniu rodomi tik trys mūsų dabartiniai elementai. Jei į savo sąrašą įtraukiame daugiau elementų ir grįžtame į išskleidžiamąjį meniu, sąraše rodomi visi nauji elementai, nekeičiant jokių formulių.
Atsargiai naudojant OFFSET
Kaip minėta šio straipsnio pradžioje, OFFSET yra nepastovi funkcija. To nepastebėsite, jei jį naudosite tik keliose ląstelėse, bet jei pradėsite jį įtraukti į šimtus skaičiavimų ir greitai pastebėsite, kad kompiuteris praleidžia daug laiko perskaičiuodamas kiekvieną kartą, kai atliekate bet kokius pakeitimus .
Be to, kadangi „OFFSET“ tiesiogiai neįvardija langelių, į kuriuos jis žiūri, kitiems vartotojams vėliau sunkiau ateiti ir prireikus pakeisti jūsų formules.
Vietoj to, patartina naudoti lenteles (įdiegtas „Office 2007“), kuriose galima rasti struktūrinių nuorodų. Tai padėjo vartotojams pateikti vieną nuorodą, kurios dydis automatiškai pakoreguotas, kai buvo pridėti arba ištrinti nauji duomenys.
Kitas variantas, kurį galima naudoti vietoj OFFSET, yra galinga INDEX funkcija. „INDEX“ leidžia sukurti visus dinaminius diapazonus, kuriuos matėme šiame straipsnyje, nesukeliant problemų dėl nepastovios funkcijos.
papildomi užrašai
Naudokite funkciją OFFSET, kad grąžintumėte langelio vertę (arba langelių diapazoną), kompensuodami nurodytą eilučių ir stulpelių skaičių nuo pradinės nuorodos. Ieškodami tik vienos ląstelės, OFFSET formulės pasiekia tą patį tikslą kaip ir INDEX formulės, naudodamos šiek tiek kitokią techniką. Tikroji OFFSET funkcijos galia slypi jos gebėjime pasirinkti langelių diapazoną, kuris bus naudojamas kitoje formulėje.
Naudodami funkciją OFFSET, nustatote pradinį pradinį langelį arba langelių diapazoną. Tada nurodote eilučių ir stulpelių, kuriuos reikia atskirti nuo to pradinio langelio, skaičių. Taip pat galite pakeisti diapazono dydį; pridėti arba atimti eilutes ar stulpelius.
Grįžkite į visų „Excel“ funkcijų sąrašą
OFFSET „Google“ skaičiuoklėse
Funkcija OFFSET „Google“ skaičiuoklėse veikia taip pat, kaip ir „Excel“:
