Šiame straipsnyje mes sužinosime, kaip naudoti „OFFSET“ funkciją „Excel“.
Kas yra OFFSET funkcija?
Ofsetinė funkcija yra informacijos paieškos funkcija. Jei turite didžiulę lentelę su tam tikrais duomenimis ir norite iš jos nuskaityti duomenis, geriausias būdas tai padaryti yra įterpti lentelės poslinkio formulę į tą darbalapį arba kitą tos pačios darbaknygės lapą. Taigi, sukūrę kompensavimo lentelę, turite tiesiog įvesti duomenis, pavyzdžiui, „mėnuo“ ir iš tos lentelės gausite to mėnesio „pardavimus“ arba „pajamas“.
Lentelės peržiūra yra dar viena galimybė, bet ar apsvarstytumėte šią parinktį, jei turite duomenų lapus ir lapus, kuriuose yra 1000 stulpelių duomenų lentelė? Čia poslinkis derinamas su kitomis funkcijomis.
Pivot lentelės sąvoka kilusi iš poslinkio. Suvestinė lentelė yra bendroji lentelė, iš kurios gaunamas konkretus duomenų ir grafikų derinys, kai reikia. Taip pat galima sukurti dinamines lenteles. Šioms lentelėms turėsite sukurti „Excel“ kompensavimo formulę
OFFSET sintaksė
| = OFFSET (nuoroda, eilutės, stulpeliai, [aukštis], [plotis]) |
Eilutės - prašote „Excel“ perkelti eilučių skaičių, kad gautumėte informacijos. Tokiu atveju jis turi judėti aplink stalą ir tiesiog palikti tuščią vietą, pažymėtą kableliu (,). Priešingu atveju, norėdami perkelti stulpelį į priekį, nurodykite reikšmę 1, o prieš tai --1.
Stulpeliai - tai yra funkcija, kuri padidina stulpelių skaičių. Vertių sistema yra tokia pati kaip eilutės.
Aukštis - stalo aukštis, šiuo atveju A11.
Plotis - stalo plotis, šiuo atveju D11.
Nustatę poslinkio funkciją, norint ištaisyti klaidas, svarbu atlikti bandymą.
Paimkime lentelę, kurioje yra vardas, el. Pašto adresas, gimimo data ir amžius. Lentelė prasideda A1 langeliu, kuriame yra antraštė „Pavadinimas“. Duomenys naudojami sakant A11. Ir eilutės eina iki D1. Visa lentelė veikia A1: D11. Norite paieškos sistemos, kuri jums suteiktų vardą, kai įvedate el. Pašto adresą, arba el. Pašto ID, kai įvesite vardą kartu su kita informacija. Formulę sukuriate tenkindami 5 argumentus.
Atskaitos taškas yra langelis, nuo kurio reikia pradėti ieškoti. Šiuo atveju jis turėtų būti A2. Tai yra bendras standartas.
Pavyzdys:
Pavyzdžiui, nurodote B4 ir poslinkio eilutę 0, o poslinkio 1 stulpelį, tada grąžinama vertė iš C4. Paini? Turėkime dar keletą pavyzdžių. Paskutinis OFFSET funkcijos pavyzdys nurodo, kaip dinamiškai sumuoti.
OFFSET funkcija, skirta gauti vertę iš ląstelės dešinės ir kairės pusės
Turime šią lentelę.
Dabar, jei noriu gauti vertę iš 2 langelių tiesiai į B2 (tai reiškia D2). Aš parašysiu šią OFFSET formulę:
| = Poslinkis (B2,0,2) |
Funkcija OFFSET perkelia 2 langelius į dešinę nuo ląstelės B4 ir grąžina jos vertę. Žiūrėkite paveikslėlį žemiau
Jei noriu gauti vertę nuo 1 langelio kairėje iki B2 (tai reiškia A2). Aš parašysiu šią OFFSET formulę:
| = Poslinkis (B2,0, -1) |
Minuso (-) ženklas rodo poslinkį judėti atvirkščiai.
Funkcija OFFSET, skirta gauti vertę iš ląstelės apačios ir viršaus
Taigi dar kartą aukščiau esančioje lentelėje, jei noriu gauti vertę nuo 2 ląstelių belove iki B3 (tai reiškia B5). Aš parašysiu šią OFFSET formulę:
| = Poslinkis (B2,2,0) |
Jei noriu gauti vertę nuo 1 langelio kairėje iki B2 (tai reiškia A2). Aš parašysiu šią OFFSET formulę:
| = Poslinkis (B2, -2,0) |
- Pro patarimas: Laikykite poslinkį kaip grafiko žymeklį, kur nuoroda yra kilmė, o eilutė ir stulpelis yra x ir y ašys.
Funkcija „OFFSET“ dinamiškai sumos diapazonui
Pažiūrėkime šį pavyzdį.
Aukščiau esančioje lentelėje Bendra eilutė lentelės pabaigoje. Laikui bėgant prie šios lentelės pridėsite eilučių. Tada turėsite pakeisti sumos diapazoną formulėje. Čia mes galime pasinaudoti OFFSET funkcijos pagalba, kad dinamiškai susumuotume. Šiuo metu mes turime ląstelėje C11 = SUMA (C2: C10). Pakeiskite tai žemiau pateikta formule.
| = SUMA (C2: OFFSET (C11, -1,0)) |
Ši formulė užima pirmąją duomenų eilutę ir tada apibendrina diapazoną virš bendros eilutės.
Poslinkis masyvui gauti
Funkcija OFFSET turi du pasirenkamus argumentus: [aukštis] ir [plotis]. Nelaikykite jų savaime suprantamu dalyku. Kai pateikiama funkcija OFFSET [aukštis] ir [plotis], ji grąžina dviejų matmenų masyvą. Jei tik vienas iš jų perduodamas kaip argumentas, jis grąžina matmenų reikšmių masyvą.
Jei įvesite šią formulę bet kuriame langelyje:
| = SUMA (OFFS (C1,1,0,9,3)) |
Jame bus sumuojamos vertės, pradedant nuo C2 iki 9 eilučių žemiau ir 3 stulpelių dešinėje.
PAKLAIDA (C1,1,0,9,3): Tai reiškia {8,8,7; 6,1,2; 8,5,8; 5,1,5; 8,3,5; 8,3,9; 8,9,2; 3,5 , 4; 6,6,5}.
Apibendrinant galima pasakyti, kad poslinkis yra labai naudinga „Excel“ funkcija, kuri gali padėti išspręsti daugelį uždusintų gijų. Leiskite man žinoti, kaip naudojate OFFSET funkciją savo formulėje ir kur ji jums labiausiai padėjo.
Paieškos funkcija yra tokia pati kaip poslinkis, tačiau dinaminėms lentelėms kurti naudojamos tokios funkcijos kaip „Match“, „Counta“ ir „IF“.
Norint naudoti ofsetą „Excel“, reikia įgūdžių ir gerų žinių apie „Excel“ funkcijas ir kaip jos veikia kartu. Jį ištraukti be klaidų yra didžiausia kliūtis.
Čia yra visos pastabos, susijusios su formulės naudojimu.
Pastabos:
- Ši formulė veikia tiek su tekstu, tiek su skaičiais.
- Yra penki kompensavimo argumentai, kuriuos reikia patenkinti, kad gautumėte informaciją be klaidų. Jei rašote neteisingą formulę, atsiranda klaida Ref.
Tikimės, kad supratote, kas yra „Excel“ poslinkio funkcija ir kaip ją naudoti „Excel“. Naršykite daugiau straipsnių apie „Excel“ paiešką ir atitikimo vertes naudodami formules čia. Jei jums patiko mūsų tinklaraščiai, pasidalykite jais su draugais „Facebook“. Taip pat galite sekti mus „Twitter“ ir „Facebook“. Mes norėtume išgirsti jūsų nuomonę, praneškite mums, kaip galime patobulinti, papildyti ar naujovinti savo darbą ir padaryti jį geresnį. Rašykite mums el.
Funkcija VLOOKUP, skirta apskaičiuoti „Excel“ pažymį : Norėdami apskaičiuoti pažymius, IF ir IFS nėra vienintelės funkcijos, kurias galite naudoti. Tokiems sąlyginiams skaičiavimams VLOOKUP yra efektyvesnis ir dinamiškesnis. Norėdami apskaičiuoti pažymius naudodami VLOOKUP, galime naudoti šią formulę.
17 dalykų apie „Excel“ VLOOKUP : „VLOOKUP“ dažniausiai naudojamas norint gauti suderintas vertes, tačiau VLOOKUP gali nuveikti daug daugiau. Čia yra 17 dalykų, susijusių su VLOOKUP, kuriuos turėtumėte žinoti, kad galėtumėte efektyviai naudoti.
Ieškokite pirmojo teksto iš „Excel“ sąrašo : Funkcija VLOOKUP puikiai veikia su pakaitos simboliais. Tai galime naudoti norėdami išgauti pirmąją teksto vertę iš nurodyto „Excel“ sąrašo. Čia yra bendra formulė.
LOOKUP data su paskutine sąrašo verte : Norėdami gauti datą, kurioje yra paskutinė vertė, naudojame funkciją LOOKUP. Ši funkcija patikrina langelį, kuriame yra paskutinė vektoriaus reikšmė, ir tada naudoja šią nuorodą datai grąžinti.
Kaip gauti naujausią kainą „Excel“ : Įprasta atnaujinti kainas bet kuriame versle ir naudoti naujausias kainas perkant ar parduodant. Norėdami gauti naujausią kainą iš „Excel“ sąrašo, naudojame funkciją LOOKUP. Funkcija LOOKUP pateikia naujausią kainą.
Populiarūs straipsniai:
50 „Excel“ spartusis klavišas produktyvumui padidinti : Greičiau atlikite savo užduotį. Šie 50 sparčiųjų klavišų leis dar greičiau dirbti naudojant „Excel“.
Kaip naudoti „IF“ funkciją „Excel“ : „Excel“ IF sakinys patikrina sąlygą ir grąžina konkrečią reikšmę, jei sąlyga yra TRUE, arba grąžina kitą konkrečią reikšmę, jei FALSE.
Kaip naudotis „VLOOKUP“ funkcija „Excel“ : Tai yra viena iš dažniausiai naudojamų ir populiariausių „Excel“ funkcijų, naudojama vertei iš įvairių diapazonų ir lapų ieškoti.
Kaip naudoti „COUNTIF“ funkciją „Excel“ : Skaičiuokite vertes su sąlygomis naudodami šią nuostabią funkciją. Norint suskaičiuoti konkrečias vertes, nereikia filtruoti duomenų. Skaitiklio funkcija yra būtina norint paruošti prietaisų skydelį.
Kaip naudotis „SUMIF“ funkcija „Excel“ : Tai dar viena esminė prietaisų skydelio funkcija. Tai padeda apibendrinti vertes konkrečiomis sąlygomis.