Excel OFFSET funkcija (piemērs + video)
Kad izmantot Excel OFFSET funkciju
Funkciju Excel OFFSET var izmantot, ja vēlaties iegūt atsauci, kas no sākuma punkta kompensē norādīto rindu un kolonnu skaitu.
Ko tas atgriež
Tas atgriež atsauci, uz kuru norāda OFFSET funkcija.
Sintakse
= OFFSET (atsauce, rindas, kolonnas, [augstums], [platums])
Ievades argumenti
- atsauce - Atsauce, no kuras vēlaties kompensēt. Tas varētu būt šūnu atsauce vai blakus esošo šūnu diapazons.
- rindas - kompensējamo rindu skaits. Ja izmantojat pozitīvu skaitli, tas tiek kompensēts zemāk esošajām rindām, un, ja tiek izmantots negatīvs skaitlis, tas tiek kompensēts iepriekšējām rindām.
- cols - kompensējamo kolonnu skaits. Ja izmantojat pozitīvu skaitli, tas tiek kompensēts pa labi esošajām kolonnām un, ja tiek izmantots negatīvs skaitlis, tad tas tiek izlīdzināts ar kolonnām pa kreisi.
- [augstums] - tas ir skaitlis, kas apzīmē rindu skaitu atgrieztajā atsaucē.
- [platums] - tas ir skaitlis, kas apzīmē kolonnu skaitu atgrieztajā atsaucē.
Izpratne par Excel OFFSET funkcijas pamatiem
Excel OFFSET funkcija, iespējams, ir viena no mulsinošākajām funkcijām programmā Excel.
Apskatīsim vienkāršu šaha spēles piemēru. Šahā ir gabals, ko sauc par Rook (pazīstams arī kā tornis, marķīze vai rektors).
[Attēla pieklājība: brīnišķīga Wikipedia]
Tagad, tāpat kā visiem pārējiem šaha gabaliem, Rookam ir noteikts ceļš, pa kuru tas var pārvietoties pa dēli. Tas var iet taisni (pa labi/pa kreisi vai uz augšu/uz leju), bet tas nevar iet pa diagonāli.
Piemēram, pieņemsim, ka mums ir šaha dēlis programmā Excel (kā parādīts zemāk), noteiktā lodziņā (šajā gadījumā D5) Rook var iet tikai četros iezīmētajos virzienos.
Tagad, ja es lūgtu jūs pārvietot Rook no pašreizējās pozīcijas uz dzeltenā krāsā iezīmēto, ko jūs teiksiet stūrim?
Jūs lūgsit to veikt divus soļus uz leju un divus pa labi … vai ne?
Un tas ir tuvu tam, kā darbojas funkcija OFFSET.
Tagad redzēsim, ko tas nozīmē programmā Excel. Es vēlos sākt ar šūnu D5 (kur atrodas Rook) un pēc tam iet divas rindas uz leju un divas kolonnas pa labi un iegūt vērtību no šūnas.
Formula būtu šāda:
= OFFSET (no kurienes sākt, cik rindu uz leju, cik kolonnu pa labi)
Kā redzat, formula iepriekšējā piemērā šūnā J1 ir = OFFSET (D5,2,2).
Tas sākās pie D5, pēc tam divas rindas uz leju un divas kolonnas pa labi un sasniedza šūnu F7. Pēc tam tas atdeva vērtību šūnā F7.
Iepriekš minētajā piemērā mēs apskatījām funkciju OFFSET ar 3 argumentiem. Bet var izmantot vēl divus izvēles argumentus.
Apskatīsim vienkāršu piemēru šeit:
Pieņemsim, ka vēlaties izmantot atsauci uz šūnu A1 (dzeltenā krāsā) un vēlaties atsaukties uz visu diapazonu, kas formulā ir iezīmēts zilā krāsā (C2: E4).
Kā jūs to izdarītu, izmantojot tastatūru? Vispirms dodieties uz šūnu C2 un pēc tam atlasiet visas šūnas C2: E4.
Tagad redzēsim, kā to izdarīt, izmantojot OFFSET formulu:
= Nobīde (A1,1,2,3,3)
Ja šūnā izmantojat šo formulu, tā atgriež #VALUE! kļūda, bet, nokļūstot rediģēšanas režīmā un atlasot formulu un nospiežot F9, jūs redzēsit, ka tā atgriež visas vērtības, kas ir iezīmētas zilā krāsā.
Tagad redzēsim, kā šī formula darbojas:
= Nobīde (A1,1,2,3,3)
- Pirmais arguments ir šūna, kur tai jāsākas.
- Otrs arguments ir 1, kas liek programmai Excel atgriezt atsauci, kas ir nobīdīta par 1 rindu.
- Trešais arguments ir 2, kas liek programmai Excel atgriezt atsauci, kas ir nobīdīta par 2 kolonnām.
- Ceturtais arguments ir 3. Tas norāda, ka atsaucei jāaptver 3 rindas. To sauc par augstuma argumentu.
- Piektais arguments ir 3. Tas nosaka, ka atsaucei jāaptver 3 slejas. To sauc par platuma argumentu.
Tagad, kad jums ir atsauce uz šūnu diapazonu (C2: E4), varat to izmantot citās funkcijās (piemēram, SUM, COUNT, MAX, AVERAGE).
Cerams, ka jums ir labas pamatzināšanas par Excel OFFSET funkcijas izmantošanu. Tagad apskatīsim dažus praktiskus piemērus funkcijas OFFSET izmantošanai.
Excel OFFSET funkcija - piemēri
Šeit ir divi Excel OFFSET funkcijas izmantošanas piemēri.
1. piemērs. Pēdējās aizpildītās šūnas atrašana kolonnā
Pieņemsim, ka slejā ir daži dati, kā parādīts zemāk:
Lai atrastu kolonnas pēdējo šūnu, izmantojiet šādu formulu:
= Nobīde (A1, COUNT (A: A) -1,0)
Šī formula pieņem, ka bez vērtībām nav citu vērtību un šajās šūnās nav tukšas šūnas. Tas darbojas, saskaitot kopējo aizpildīto šūnu skaitu un attiecīgi kompensē šūnu A1.
Piemēram, šajā gadījumā ir 8 vērtības, tāpēc COUNT (A: A) atgriež 8. Mēs nobīdām šūnu A1 par 7, lai iegūtu pēdējo vērtību.
2. piemērs - dinamiskas nolaižamās izvēlnes izveide
Jūs varat paplašināt koncepcijas šovus 1. piemērā, lai izveidotu dinamiskus nosauktos diapazonus. Tie varētu būt noderīgi, ja formulās izmantojat nosauktos diapazonus vai veidojat nolaižamos sarakstus un, visticamāk, pievienosit/dzēsīsit datus no atsauces, kas veido nosaukto diapazonu.
Šeit ir piemērs:
Ņemiet vērā, ka nolaižamā izvēlne tiek automātiski pielāgota, kad gads tiek pievienots vai noņemts.
Tas notiek, jo nolaižamās izvēlnes izveidei izmantotā formula ir dinamiska un identificē jebkuru pievienošanu vai dzēšanu un attiecīgi pielāgo diapazonu.
Lūk, kā to izdarīt:
- Atlasiet šūnu, kurā vēlaties ievietot nolaižamo izvēlni.
- Dodieties uz Dati -> Datu rīki -> Datu validācija.
- Dialoglodziņa Datu validācija cilnē Iestatījumi nolaižamajā izvēlnē atlasiet Saraksts.
- Avotā ievadiet šādu formulu: = OFFSET (A1,0,0, COUNT (A: A), 1)
- Noklikšķiniet uz Labi.
Apskatīsim, kā šī formula darbojas:
- Funkcijas OFFSET pirmie trīs argumenti ir A1, 0 un 0. Tas būtībā nozīmē, ka tā atgriež to pašu atsauces šūnu (kas ir A1).
- Ceturtais arguments ir par augstumu, un šeit funkcija COUNT atgriež kopējo vienumu skaitu sarakstā. Tiek pieņemts, ka sarakstā nav tukšu vietu.
- Piektais arguments ir 1, kas norāda, ka kolonnas platumam jābūt vienam.
Papildu piezīmes:
- OFFSET ir gaistoša funkcija (lietojiet piesardzīgi).
- Tas tiek pārrēķināts ikreiz, kad ir atvērta Excel darbgrāmata vai kad darblapā tiek aktivizēts aprēķins. Tas varētu palielināt apstrādes laiku un palēnināt darbgrāmatas darbību.
- Ja augstuma vai platuma vērtība tiek izlaista, tā tiek uzskatīta par atsauces vērtību.
- Ja rindas un cols ir negatīvi skaitļi, tad nobīdes virziens tiek mainīts.
Excel OFFSET funkciju alternatīvas
Dažu Excel OFFSET funkcijas ierobežojumu dēļ daudzi vēlas apsvērt tās alternatīvas:
- Funkcija INDEX: funkciju INDEX var izmantot arī, lai atgrieztu šūnas atsauci. Noklikšķiniet šeit, lai skatītu piemēru, kā izveidot dinamisku nosaukto diapazonu, izmantojot funkciju INDEX.
- Excel tabula: ja Excel tabulā izmantojat strukturētas atsauces, jums nav jāuztraucas par jaunu datu pievienošanu un nepieciešamību pielāgot formulas.
Excel OFFSET funkcija - video apmācība
- Excel VLOOKUP funkcija.
- Excel HLOOKUP funkcija.
- Excel INDEX funkcija.
- Excel netiešā funkcija.
- Excel MATCH funkcija.
