„Excel OFFSET“ funkcija (pavyzdys + vaizdo įrašas)
Kada naudoti „Excel OFFSET“ funkciją?
„Excel OFFSET“ funkcija gali būti naudojama, kai norite gauti nuorodą, kuri kompensuoja nurodytą eilučių ir stulpelių skaičių nuo pradžios taško.
Ką grąžina
Ji grąžina nuorodą, į kurią nurodo OFFSET funkcija.
Sintaksė
= OFFSET (nuoroda, eilutės, stulpeliai, [aukštis], [plotis])
Įvesties argumentai
- nuoroda - Nuoroda, kurią norite kompensuoti. Tai gali būti langelio nuoroda arba gretimų langelių diapazonas.
- eilutės - eilučių, kurias reikia kompensuoti, skaičių. Jei naudojate teigiamą skaičių, jis atsveria žemiau esančias eilutes, o jei naudojamas neigiamas skaičius, tada jis kompensuoja aukščiau esančias eilutes.
- cols - stulpelių, kuriuos reikia kompensuoti, skaičių. Jei naudojate teigiamą skaičių, jis atsveria dešinėje esančius stulpelius, o jei naudojamas neigiamas skaičius, jis atsveria kairėje esančius stulpelius.
- [aukštis] - tai skaičius, nurodantis grąžintos nuorodos eilučių skaičių.
- [plotis] - tai skaičius, nurodantis stulpelių skaičių grąžintoje nuorodoje.
„Excel OFFSET“ funkcijos pagrindų supratimas
„Excel OFFSET“ funkcija galbūt yra viena painiausių „Excel“ funkcijų.
Paimkime paprastą šachmatų žaidimo pavyzdį. Yra šachmatų figūra, vadinama bokštu (taip pat žinoma kaip bokštas, markizė ar rektorius).
[Vaizdo leidimas: nuostabi Vikipedija]
Dabar, kaip ir visos kitos šachmatų figūros, bokštas turi fiksuotą kelią, kuriuo jis gali judėti lentoje. Jis gali eiti tiesiai (į dešinę/kairę arba aukštyn/žemyn lentą), bet negali eiti įstrižai.
Pavyzdžiui, tarkime, kad „Excel“ turime šachmatų lentą (kaip parodyta žemiau), tam tikrame laukelyje (šiuo atveju D5) bokštas gali eiti tik keturiomis paryškintomis kryptimis.
Jei dabar paprašysiu jūsų paimti bokštą iš dabartinės padėties į geltonai paryškintą, ką pasakysite bokštui?
Jūs paprašysite, kad jis žengtų du žingsnius žemyn ir du žingsnius į dešinę … ar ne?
Ir tai yra arti to, kaip veikia OFFSET funkcija.
Dabar pažiūrėkime, ką tai reiškia „Excel“. Noriu pradėti nuo langelio D5 (kur yra bokštas), tada eiti dviem eilutėmis žemyn ir dviem stulpeliais į dešinę ir gauti vertę iš ten esančio langelio.
Formulė būtų tokia:
= OFFSET (nuo ko pradėti, kiek eilučių žemyn, kiek stulpelių dešinėje)
Kaip matote, formulė J1 langelyje aukščiau pateiktame pavyzdyje yra = OFFSET (D5,2,2).
Jis prasidėjo nuo D5, o po to nukrito dviem eilėmis žemyn ir dviem stulpeliais į dešinę ir pasiekė langelį F7. Tada ji grąžino F7 langelio vertę.
Pirmiau pateiktame pavyzdyje mes apžvelgėme funkciją OFFSET su 3 argumentais. Tačiau galima naudoti dar du neprivalomus argumentus.
Pažvelkime į paprastą pavyzdį čia:
Tarkime, kad norite naudoti nuorodą į langelį A1 (geltona spalva) ir norite nurodyti visą diapazoną, pažymėtą mėlyna spalva (C2: E4) formulėje.
Kaip tai padarytumėte naudodami klaviatūrą? Pirmiausia eikite į langelį C2, tada pasirinkite visas C2: E4 ląsteles.
Dabar pažiūrėkime, kaip tai padaryti naudojant OFFSET formulę:
= Poslinkis (A1,1,2,3,3)
Jei šią formulę naudosite langelyje, ji grąžins #VALUE! klaida, bet jei pateksite į redagavimo režimą ir pasirinksite formulę ir paspausite F9, pamatysite, kad ji grąžina visas mėlyna spalva paryškintas reikšmes.
Dabar pažiūrėkime, kaip veikia ši formulė:
= Poslinkis (A1,1,2,3,3)
- Pirmasis argumentas yra langelis, kuriame jis turėtų prasidėti.
- Antrasis argumentas yra 1, kuris nurodo „Excel“ grąžinti nuorodą, kuri buvo 1 eilute NUOLAIDA.
- Trečias argumentas yra 2, kuris nurodo „Excel“ grąžinti nuorodą, kuri buvo 2 stulpeliais NUOSTATA.
- Ketvirtasis argumentas yra 3. Tai nurodo, kad nuoroda turi apimti 3 eilutes. Tai vadinama aukščio argumentu.
- Penktasis argumentas yra 3. Tai nurodo, kad nuoroda turi apimti 3 stulpelius. Tai vadinama pločio argumentu.
Dabar, kai turite nuorodą į langelių diapazoną (C2: E4), galite jį naudoti kitose funkcijose (pvz., SUM, COUNT, MAX, AVERAGE).
Tikimės, kad gerai išmanote „Excel OFFSET“ funkcijos naudojimą. Dabar pažvelkime į keletą praktinių OFFSET funkcijos naudojimo pavyzdžių.
„Excel OFFSET“ funkcija - pavyzdžiai
Pateikiame du „Excel OFFSET“ funkcijos naudojimo pavyzdžius.
1 pavyzdys - paskutinio užpildyto langelio paieška stulpelyje
Tarkime, kad stulpelyje yra duomenų, kaip parodyta žemiau:
Norėdami rasti paskutinį stulpelio langelį, naudokite šią formulę:
= OFFSET (A1, COUNT (A: A) -1,0)
Šioje formulėje daroma prielaida, kad nėra jokių reikšmių, išskyrus tas, kurios parodytos, ir šiose ląstelėse nėra tuščio langelio. Jis veikia skaičiuodamas bendrą užpildytų ląstelių skaičių ir atitinkamai kompensuoja langelį A1.
Pavyzdžiui, šiuo atveju yra 8 reikšmės, taigi COUNT (A: A) grąžina 8. Mes pakeičiame langelį A1 7, kad gautume paskutinę vertę.
2 pavyzdys. Dinaminio išskleidžiamojo meniu sukūrimas
Galite išplėsti 1 pavyzdyje pateiktas koncepcijos laidas, kad sukurtumėte dinaminius pavadintus diapazonus. Tai gali būti naudinga, jei formulėse naudojate nurodytus diapazonus arba kuriate išskleidžiamuosius meniu ir greičiausiai pridėsite/ištrinsite duomenis iš nuorodos, kuri sudaro pavadintą diapazoną.
Štai pavyzdys:
Atminkite, kad pridėjus arba pašalinus metus, išskleidžiamasis meniu prisitaiko automatiškai.
Taip atsitinka, nes formulė, naudojama išskleidžiamajam meniu, yra dinamiška ir nustato bet kokį pridėjimą ar ištrynimą ir atitinkamai koreguoja diapazoną.
Štai kaip tai padaryti:
- Pasirinkite langelį, į kurį norite įterpti išskleidžiamąjį meniu.
- Eikite į Duomenys -> Duomenų įrankiai -> Duomenų patvirtinimas.
- Dialogo lango Duomenų tikrinimas skirtuko Nustatymai išskleidžiamajame meniu pasirinkite Sąrašas.
- Šaltinyje įveskite šią formulę: = OFFSET (A1,0,0, COUNT (A: A), 1)
- Spustelėkite Gerai.
Pažiūrėkime, kaip veikia ši formulė:
- Pirmieji trys funkcijos OFFSET argumentai yra A1, 0 ir 0. Tai iš esmės reiškia, kad ji grąžintų tą patį atskaitos langelį (kuris yra A1).
- Ketvirtasis argumentas yra dėl aukščio ir čia funkcija COUNT grąžina bendrą sąrašo elementų skaičių. Daroma prielaida, kad sąraše nėra tuščių vietų.
- Penktasis argumentas yra 1, o tai reiškia, kad stulpelio plotis turi būti vienas.
Papildomi užrašai:
- OFFSET yra nepastovi funkcija (naudokite atsargiai).
- Jis perskaičiuojamas kiekvieną kartą, kai atidaroma „Excel“ darbaknygė arba kai darbalapyje suaktyvinamas skaičiavimas. Tai gali pailginti apdorojimo laiką ir sulėtinti jūsų darbaknygę.
- Jei aukščio arba pločio reikšmė praleidžiama, ji laikoma atskaitos verte.
- Jei eilutės ir cols yra neigiami skaičiai, tada poslinkio kryptis yra atvirkštinė.
„Excel OFFSET“ funkcijos alternatyvos
Dėl kai kurių „Excel OFFSET“ funkcijos apribojimų daugelis nori apsvarstyti alternatyvas:
- Funkcija INDEX: INDEX funkcija taip pat gali būti naudojama norint grąžinti langelio nuorodą. Spustelėkite čia, kad pamatytumėte pavyzdį, kaip sukurti dinaminį pavadintą diapazoną naudojant funkciją INDEX.
- „Excel“ lentelė: jei „Excel“ lentelėje naudojate struktūrines nuorodas, jums nereikia jaudintis dėl naujų duomenų pridėjimo ir poreikio koreguoti formules.
„Excel OFFSET“ funkcija - vaizdo įrašo pamoka
- „Excel VLOOKUP“ funkcija.
- „Excel HLOOKUP“ funkcija.
- „Excel INDEX“ funkcija.
- „Excel“ netiesioginė funkcija.
- „Excel MATCH“ funkcija.
