Šajā rakstā mēs uzzināsim, kā lietot funkciju OFFSET programmā Excel.
Kas ir OFFSET funkcija?
Ofseta funkcija ir informācijas izguves funkcija. Ja jums ir milzīga tabula, kurā ir noteikti dati, un vēlaties no tās izgūt datus, labākais veids, kā to izdarīt, ir ievietot tabulas nobīdes formulu šajā darblapā vai citā lapā tajā pašā darbgrāmatā. Tādējādi, tiklīdz esat izveidojis nobīdes izguves tabulu, jums vienkārši jāievada dati, piemēram, “mēnesis”, un no šīs tabulas jūs iegūsit tā mēneša “pārdošanas” vai “ienākumus”.
Tabulas apskate ir vēl viena iespēja, bet vai jūs apsvērtu šo iespēju, ja jums ir datu lapas un lapas, kurās ir 1000 kolonnu datu tabula? Šeit kompensācija tiek apvienota ar citām funkcijām.
Pivot tabulas jēdziens nāk no nobīdes. Pivot tabula ir vispārīga tabula, un no tās tiek iegūta īpaša datu un diagrammu kombinācija, kad un kad tas ir nepieciešams. Tādā pašā veidā var izveidot arī dinamiskas tabulas. Šīm tabulām jums būs jāizveido Excel kompensācijas formula
OFFSET sintakse
| = OFFSET (atsauce, rindas, kolonnas, [augstums], [platums]) |
Rindas - jūs lūdzat Excel pārvietot rindu skaitu, lai iegūtu informāciju. Šādā gadījumā tam ir jāpārvietojas pa visu galdu un vienkārši jāatstāj tukša vieta, kas apzīmēta ar komatu (,). Pretējā gadījumā, lai pārvietotu kolonnu uz priekšu, norādiet vērtību kā 1 un, lai pārvietotu kolonnu iepriekš, norādiet vērtību -1.
Kolonnas - tas ir, lai funkciju palielinātu līdz kolonnu skaitam. Vērtību sistēma ir tāda pati kā rindai.
Augstums - galda augstums, šajā gadījumā A11.
Platums - galda platums, šajā gadījumā D11.
Pēc nobīdes funkcijas iestatīšanas izmēģinājuma veikšana ir svarīga, lai novērstu visas kļūdas.
Pieņemsim tabulu, kurā ir vārds, e -pasta ID, dzimšanas datums un vecums. Tabula sākas ar šūnu A1, kurā ir virsraksts “Nosaukums”. Dati darbojas, piemēram, A11. Un rindas iet līdz D1. Visa tabula iet A1: D11. Jūs vēlaties izguves sistēmu, kas piešķir jums vārdu, ievadot e -pasta ID, vai e -pasta ID, ievadot vārdu kopā ar citu informāciju. Jūs izveidojat formulu, izpildot 5 argumentus.
Atskaites punkts ir šūna, no kuras jāsāk uzmeklēšana. Šajā gadījumā tam vajadzētu būt A2. Tas ir vispārējais standarts.
Piemērs:
Piemēram, jūs norādāt atsauci uz B4 un nobīdes rindu 0, un nobīdes kolonnu 1, tad vērtība no C4 tiks atgriezta. Mulsina? Ļaujiet mums sniegt vēl dažus piemērus. Pēdējais OFFSET funkcijas piemērs parāda, kā dinamiski summēt.
OFFSET funkcija, lai iegūtu vērtību no šūnas labās un kreisās puses
Mums ir šī tabula.
Tagad, ja es vēlos iegūt vērtību no 2 šūnām līdz B2 (kas nozīmē D2). Es uzrakstīšu šo OFFSET formulu:
| = Nobīde (B2,0,2) |
OFFSET funkcija pārvietos 2 šūnas pa labi no šūnas B4 un atgriezīs tās vērtību. Skatiet attēlu zemāk
Ja es vēlos iegūt vērtību no 1 šūnas pa kreisi uz B2 (kas nozīmē A2). Es uzrakstīšu šo OFFSET formulu:
| = Nobīde (B2,0, -1) |
Mīnusa zīme (-) norāda nobīdi, lai pārvietotos otrādi.
OFFSET funkcija, lai iegūtu vērtību no šūnas apakšas un virs tās
Tātad atkal iepriekšējā tabulā, ja es vēlos iegūt vērtību no 2 šūnu belove līdz B3 (kas nozīmē B5). Es uzrakstīšu šo OFFSET formulu:
| = Nobīde (B2,2,0) |
Ja es vēlos iegūt vērtību no 1 šūnas pa kreisi uz B2 (kas nozīmē A2). Es uzrakstīšu šo OFFSET formulu:
| = Nobīde (B2, -2,0) |
- Pro padoms: Izskatiet OFFSET kā grafika rādītāju, kur atsauce ir izcelsme, un rinda un kolonna ir x un y ass.
OFFSET funkcija līdz summas diapazonam dinamiski
Apskatīsim šo piemēru.
Iepriekšējā tabulā Kopējā rinda tabulas beigās. Laika gaitā šai tabulai pievienosit rindas. Tad jums būs jāmaina summas diapazons formulā. Šeit mēs varam izmantot funkciju OFFSET, lai dinamiski summētu. Pašlaik šūnā C11 mums ir = SUMMA (C2: C10). Aizstājiet to ar zemāk esošo formulu.
| = SUMMA (C2: nobīde (C11, -1,0)) |
Šī formula vienkārši aizņem pirmo datu rindu un pēc tam summē diapazonu virs kopējās rindas.
Nobīde masīva iegūšanai
Funkcijai OFFSET ir divi izvēles argumenti: [augstums] un [platums]. Neuztveriet tos kā pašsaprotamus. Ja tiek nodrošināta funkcija OFFSET [augstums] un [platums], tā atgriež divdimensiju masīvu. Ja tikai viens no tiem tiek nodots kā arguments, tas atgriežas vērtību dimensiju masīvā.
Ja jebkurā šūnā ierakstāt šo formulu:
| = SUMMA (NOBĪDE (C1,1,0,9,3)) |
Tajā tiks apkopotas vērtības diapazonā, sākot no C2 līdz 9 rindām zemāk un 3 kolonnas pa labi.
NOBĪDE (C1,1,0,9,3): Tas nozīmē {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}.
Visbeidzot, nobīde ir ļoti noderīga Excel funkcija, kas var palīdzēt atrisināt daudzus nožņaugtus pavedienus. Ļaujiet man zināt, kā jūs izmantojat funkciju OFFSET savā formulā un kur tā jums visvairāk palīdzēja.
Uzmeklēšanas funkcija ir tāda pati kā nobīde, bet dinamiskas tabulas izveidošanai izmanto tādas funkcijas kā Match, Counta un IF.
Lai izmantotu nobīdi programmā Excel, ir nepieciešamas prasmes un labas zināšanas par Excel funkcijām un to, kā tās darbojas kopā. Izvilkšana bez kļūdām ir lielākais šķērslis.
Šeit ir visas novērošanas piezīmes par formulas izmantošanu.
Piezīmes:
- Šī formula darbojas gan ar tekstu, gan cipariem.
- Ir pieci nobīdes argumenti, kas jāapmierina, lai iegūtu informāciju bez kļūdām. Ja rakstāt nepareizu formulu, rodas kļūda Ref.
Ceru, ka sapratāt, kas ir Excel nobīdes funkcija un kā to izmantot programmā Excel. Izpētiet citus rakstus par Excel uzmeklēšanu un atbilstības vērtībām, izmantojot formulas šeit. Ja jums patika mūsu emuāri, kopīgojiet to ar saviem draugiem Facebook. Un arī jūs varat sekot mums Twitter un Facebook. Mēs labprāt uzklausītu jūsu viedokli, dariet mums zināmu, kā mēs varam uzlabot, papildināt vai ieviest jauninājumus mūsu darbā un uzlabot to jūsu labā. Rakstiet mums e -pasta vietnē.
Funkcija VLOOKUP, lai aprēķinātu atzīmi programmā Excel : Lai aprēķinātu pakāpes, IF un IFS nav vienīgās funkcijas, kuras varat izmantot. VLOOKUP ir efektīvāks un dinamisks šādiem nosacītiem aprēķiniem. Lai aprēķinātu pakāpes, izmantojot VLOOKUP, mēs varam izmantot šo formulu.
17 lietas par Excel VLOOKUP : VLOOKUP visbiežāk tiek izmantots, lai izgūtu atbilstošas vērtības, taču VLOOKUP var paveikt daudz vairāk. Šeit ir 17 lietas par VLOOKUP, kas jums jāzina, lai efektīvi izmantotu.
MEKLĒT pirmo tekstu no saraksta programmā Excel : Funkcija VLOOKUP lieliski darbojas ar aizstājējzīmēm. Mēs to varam izmantot, lai izvilktu pirmo teksta vērtību no dotā saraksta programmā Excel. Šeit ir vispārīgā formula.
MEKLĒŠANAS datums ar pēdējo vērtību sarakstā : Lai izgūtu datumu, kurā ir pēdējā vērtība, mēs izmantojam funkciju LOOKUP. Šī funkcija pārbauda šūnu, kurā ir pēdējā vektora vērtība, un pēc tam izmanto šo atsauci, lai atgrieztu datumu.
Kā iegūt jaunāko cenu programmā Excel : Parasti jebkurā biznesā tiek atjauninātas cenas, un pirkumiem vai pārdošanai obligāti jāizmanto jaunākās cenas. Lai iegūtu jaunāko cenu no Excel saraksta, mēs izmantojam funkciju LOOKUP. Funkcija LOOKUP iegūst jaunāko cenu.
Populāri raksti:
50 Excel saīsne produktivitātes paaugstināšanai : Ātrāk izpildiet savu uzdevumu. Šie 50 īsceļi ļaus jums strādāt vēl ātrāk programmā Excel.
Kā lietot funkciju IF programmā Excel : IF priekšraksts programmā Excel pārbauda nosacījumu un atgriež noteiktu vērtību, ja nosacījums ir TRUE, vai atgriež citu noteiktu vērtību, ja FALSE.
Kā lietot funkciju VLOOKUP programmā Excel : Šī ir viena no visbiežāk izmantotajām un populārākajām Excel funkcijām, kas tiek izmantota, lai meklētu vērtību no dažādiem diapazoniem un lapām.
Kā lietot funkciju COUNTIF programmā Excel : Saskaitiet vērtības ar nosacījumiem, izmantojot šo apbrīnojamo funkciju. Jums nav jāfiltrē dati, lai saskaitītu noteiktas vērtības. Skaitītāja funkcija ir būtiska, lai sagatavotu informācijas paneli.
Kā lietot funkciju SUMIF programmā Excel : Šī ir vēl viena būtiska informācijas paneļa funkcija. Tas palīdz apkopot vērtības noteiktos apstākļos.