Descărcați exemplul de registru de lucru
Acest tutorial arată cum să utilizați fișierul Funcția Excel OFFSET în Excel pentru a crea un decalaj de referință dintr-o celulă inițială.
Prezentare generală a funcției OFFSET
Funcția OFFSET Începe cu o referință de celulă definită și returnează o referință de celulă un număr specificat de rânduri și coloane decalate de referința originală. Referințele pot fi o celulă sau o gamă de celule. De asemenea, Offset vă permite să redimensionați referința la un anumit număr de rânduri / coloane.
(Observați cum apar datele introduse de formulă)
Sintaxa și intrările funcției IFERROR:
| 1 | = OFFSET (referință, rânduri, cols, înălțime, lățime) |
referinţă - Referința inițială a celulei de la care doriți să compensați.
rânduri - Numărul de rânduri de compensat.
cols - Numărul de coloane de compensat.
înălţime - OPȚIONAL: Ajustați numărul de rânduri din referință.
lăţime - OPȚIONAL: Ajustați numărul de coloane din referință.
Ce este funcția OFFSET?
Funcția OFFSET este una dintre cele mai puternice funcții de foi de calcul, deoarece poate fi destul de versatilă în ceea ce creează. Oferă utilizatorului posibilitatea de a defini o celulă sau un interval într-o varietate de poziție și dimensiuni.
ATENȚIE: Funcția OFFSET este una dintre funcțiile volatile. De cele mai multe ori, când lucrați în foaia de calcul, computerul va recalcula o formulă numai dacă intrările și-au schimbat valorile. Cu toate acestea, o funcție volatilă recalculează fiecare de când faceți o modificare la orice celulă. Trebuie folosită precauție pentru a vă asigura că nu provocați un timp mare de recalculare din cauza utilizării excesive a funcției volatile sau a faptului că multe celule depind de rezultatul unei funcții volatile.
Exemple de rând de bază
În fiecare utilizare a funcției OFFSET, trebuie să dați un punct de pornire sau o ancoră. Să analizăm acest tabel pentru a înțelege acest lucru:
Vom folosi „Bob” în celula B3 ca punct de ancorare. Dacă am vrea să obținem valoarea chiar mai jos (Charlie), am spune că vrem să schimbăm rândul cu 1. Formula noastră ar arăta ca
| 1 | = OFFSET (B3, 1) |
Dacă am vrea să ne deplasăm în sus, ar fi o schimbare negativă. Vă puteți gândi la acest lucru, deoarece numărul rândului este în scădere, deci trebuie să scădem. Astfel, pentru a obține valoarea de mai sus (Adam), am scrie
| 1 | = OFFSET (B2, -1) |
Exemple de coloane de bază
Continuând ideea din exemplul anterior, vom adăuga o altă coloană la tabelul nostru.
Dacă am vrea să-l apucăm pe profesor pentru Bob, am putea folosi formula
| 1 | = OFFSET (B2, 0, 1) |
În acest caz, am spus că dorim să compensăm zero rânduri (aka să rămânem pe același rând), dar vrem să compensăm 1 coloană. Pentru coloane, un număr pozitiv înseamnă să compensați spre dreapta, iar numerele negative înseamnă să compensați spre stânga.
OFFSET și MATCH
Să presupunem că aveți mai multe coloane de date și că ați dorit să oferiți utilizatorului posibilitatea de a alege din ce coloană să obțină rezultatele. Puteți utiliza funcția INDEX sau puteți utiliza OFFSET. Deoarece MATCH va returna poziția relativă a unei valori, va trebui să ne asigurăm că punctul de ancorare este la stânga primei noastre valori posibile. Luați în considerare următorul aspect:
În B2, vom scrie această formulă:
| 1 | = OFFSET (B2, 0, MATCH (A2, $ C $ 1: $ F $ 1, 0)) |
MATCH-ul va arăta „Feb” în intervalul C1: F1 și îl va găsi în 2nd celulă. OFFSET va deplasa apoi 1 coloană la dreapta B2 și va prelua valoarea dorită de 9. Rețineți că OFFSET nu are nicio problemă în utilizarea aceleiași celule care conține formula ca punct de ancorare.
NOTĂ: Această tehnică ar putea fi utilizată ca înlocuitor pentru VLOOKUP sau HLOOKUP atunci când doriți să returnați o valoare din stânga / deasupra intervalului de căutare. Acest lucru se datorează faptului că OFFSET poate face compensări negative.
OFFSET pentru a obține o gamă
Puteți utiliza 4a și 5a argumente în funcția OFFSET pentru a returna un interval mai degrabă decât o singură celulă. Să presupunem că ați dorit să însumați 3 coloane în acest tabel.
| 1 | = MEDIE (OFFSET (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
În F2, am selectat numele unui student pentru care dorim să obținem scorurile medii ale testelor. Pentru a face acest lucru, vom folosi formula
| 1 | = MEDIE (OFFSET (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
MATCH-ul va căuta prin coloana A numele nostru și va returna poziția relativă, care este 3 în exemplul nostru. Să vedem cum va fi evaluat acest lucru. În primul rând, OFFSET-ul va merge jos 3 rânduri de la A1 și 1 coloană la dreapta din A1. Acest lucru ne plasează în celula B3.
| 1 | = MEDIE (OFFSET (A1, 3, 1, 1, 3)) |
Apoi, vom redimensiona gama. Noua gamă va avea B3 ca celulă din stânga sus. Va avea 1 rând înălțime și 3 coloane înălțime, oferindu-ne intervalul B4: D4.
| 1 | = MEDIE (OFFSET (A1,3, 1, 1, 3)) |
Rețineți că, deși puteți pune în mod legitim valori negative în argumentele de compensare, puteți utiliza numai valori non-negative în argumentele de dimensionare.
La final, funcția noastră MEDIE vede:
| 1 | = MEDIE (B4: D4) |
Astfel, obținem soluția noastră de 86.67
OFFSET cu SUMĂ dinamică
Deoarece OFFSET este utilizat pentru a găsi o referință, mai degrabă decât să arate direct către celulă, este cel mai util atunci când aveți de-a face cu date care au rânduri adăugate sau șterse. Luați în considerare următorul tabel cu un Total în partea de jos
| 1 | = SUMĂ (B2: B4) |
Dacă am fi folosit aici o formulă SUM de bază „= SUM (B2: B4)” și apoi am fi inserat un rând nou pentru a adăuga o înregistrare pentru Bill, am avea un răspuns greșit
În schimb, să ne gândim cum să rezolvăm acest lucru din punctul de vedere al Totalului. Vrem cu adevărat să apucăm totul, de la celula B2 la celulă chiar peste totalul nostru. Modul în care putem scrie acest lucru într-o formulă este să facem un decalaj de rând de -1. Astfel, îl folosim ca formulă pentru totalul nostru din celula B5:
| 1 | = SUM (B2: OFFSET (B5, -1,0)) |
Această formulă face ceea ce tocmai am descris: începeți de la B2 și mergeți la 1 celulă deasupra celulei noastre totale. Puteți vedea cum, după adăugarea datelor lui Bill, totalul nostru se actualizează corect.
OFFSET pentru a obține ultimele N articole
Să presupunem că înregistrați vânzări lunare, dar doriți să puteți analiza ultimele 3 luni. În loc să trebuiască să vă actualizați manual formulele pentru a continua ajustarea pe măsură ce se adaugă date noi, puteți utiliza funcția OFFSET cu COUNT.
Am arătat deja cum puteți utiliza OFFSET pentru a obține o gamă de celule. Pentru a determina câte celule trebuie să schimbăm, vom folosi COUNT pentru a găsi câte numere sunt în coloana B. Să ne uităm la tabelul nostru eșantion.
| 1 | = SUM (OFFSET ($ B $ 1, COUNT (B: B) - $ E $ 1 + 1,0, $ E $ 1,1)) |
Dacă am începe de la B1 și compensăm 4 rânduri (numărul de numere din coloana B), am ajunge în partea de jos a gamei noastre, B5. Cu toate acestea, deoarece OFFSET nu poate redimensiona cu o valoare negativă, trebuie să facem unele ajustări, astfel încât să ajungem în B3. Ecuația generală pentru aceasta va fi de făcut
| 1 | COUNT (…) - N + 1 |
Luăm numărul de coloane întregi, scădem oricât de multe dorim să le returnăm (deoarece vom redimensiona pentru a le prelua) și apoi adăugăm 1 (întrucât, în esență, începem offsetul la poziția zero).
Aici puteți vedea că am configurat o gamă pentru a obține suma, media și maxima din ultimele N luni. În E1, am introdus valoarea 3. În E2, formula noastră este
| 1 | = SUMĂ (OFFSET ($ B $ 1, COUNT (B: B) - $ E $ 1 + 1,0, $ E $ 1,1)) |
Secțiunea evidențiată este ecuația noastră generală pe care tocmai am discutat-o. Nu trebuie să compensăm nicio coloană. Vom redimensiona intervalul pentru a avea 3 celule înălțime (determinate de valoarea din E1) și 1 coloană lățime. SUMA noastră ia apoi acest interval și ne oferă rezultatul de 1.850 USD. De asemenea, am arătat că puteți calcula media maximă a aceluiași interval prin simpla comutare a funcției externe de la SUM la oricare ar fi situația.
OFFSET liste de validare dinamică
Folosind tehnica prezentată în ultimul exemplu, putem construi, de asemenea, domenii numite care ar putea fi utilizate în validarea datelor sau în diagrame. Acest lucru poate fi util atunci când doriți să configurați o foaie de calcul, dar vă așteptați ca listele / datele noastre să schimbe dimensiunea. Să presupunem că magazinul nostru începe să vândă fructe și că avem în prezent 3 opțiuni.
Pentru a realiza un meniu derulant Validare date pe care îl putem folosi în altă parte, vom defini intervalul numit MyFruit ca
| 1 | = $ A $ 2: OFFSET ($ A $ 1, COUNTA ($ A: $ A) -1, 0) |
În loc de COUNT, folosim COUNTA deoarece avem de-a face cu valori text. Din această cauză, COUNTA va fi una mai mare, deoarece va număra celula de antet în A1 și va da o valoare 4. Dacă totuși compensăm cu 4 rânduri, vom ajunge în celula A5 care este necompletată. Pentru a ajusta acest lucru atunci, scădem 1.
Acum, că avem setarea Named Range, putem configura unele validări de date în celula C4 utilizând un tip List, cu sursă:
| 1 | = Fructul Meu |
Rețineți că meniul derulant afișează doar cele trei articole actuale. Dacă adăugăm mai multe articole la lista noastră și revenim la meniul derulant, lista afișează toate elementele noi fără ca noi să trebuiască să modificăm niciuna dintre formule.
Precauții la utilizarea OFFSET
După cum sa menționat la începutul acestui articol, OFFSET este o funcție volatilă. Nu veți observa acest lucru dacă îl utilizați în doar câteva celule, dar dacă începeți să îl implicați în sute de calcule și veți observa rapid computerul dvs. petrecând o cantitate vizibilă de timp recalculând de fiecare dată când faceți modificări .
În plus, deoarece OFFSET nu numește direct celulele la care se uită, este mai greu pentru ceilalți utilizatori să treacă mai târziu și să schimbe formulele, dacă este necesar.
În schimb, ar fi recomandabil să se utilizeze tabele (introduse în Office 2007) care permit referințe structurale. Acestea au ajutat utilizatorii să poată oferi o singură referință care s-a ajustat automat ca dimensiune pe măsură ce au fost adăugate sau șterse date noi.
Cealaltă opțiune de utilizat în loc de OFFSET este funcția INDEX puternică. INDEX vă permite să construiți toate intervalele dinamice pe care le-am văzut în acest articol fără a fi o funcție volatilă.
Note Aditionale
Utilizați funcția OFFSET pentru a returna o valoare a celulei (sau un interval de celule) prin compensarea unui număr dat de rânduri și coloane dintr-o referință de pornire. Când se caută doar o singură celulă, formulele OFFSET ating același scop ca și formulele INDEX, folosind o tehnică ușor diferită. Puterea reală a funcției OFFSET constă în capacitatea sa de a selecta o gamă de celule care să fie utilizate într-o altă formulă.
Când utilizați funcția OFFSET, definiți o celulă inițială de pornire sau o gamă de celule. Apoi indicați numărul de rânduri și coloane de compensat de la celula inițială. De asemenea, puteți redimensiona gama; adăugați sau scădeți rânduri sau coloane.
Reveniți la lista tuturor funcțiilor din Excel
OFFSET în Foi de calcul Google
Funcția OFFSET funcționează exact la fel în Foi de calcul Google ca în Excel:
