În acest articol, vom afla cum să utilizați funcția OFFSET în Excel.
Ce este Funcția OFFSET?
Funcția Offset este o funcție de recuperare a informațiilor. Dacă aveți un tabel imens care conține anumite date și doriți să preluați date din acesta, cel mai bun mod de a face acest lucru este să introduceți o formulă de compensare pentru tabelul din foaia de lucru respectivă sau o altă foaie din același registru de lucru. Astfel, odată ce ați creat tabelul de recuperare offset, trebuie pur și simplu să introduceți datele de exemplu „lună” și veți obține „vânzări” sau „venituri” din luna respectivă din acel tabel.
Parcurgerea tabelului este o altă opțiune, dar ați lua în considerare opțiunea dacă aveți foi și foi de date care conțin toate un tabel de date de 1000 de coloane? Aici offset-ul este combinat cu alte funcții.
Conceptul de tabel pivot vine de la offset. Un tabel pivot este un tabel general și din această combinație specifică de date și grafice se obține pe măsură ce este necesar. De asemenea, tabelele dinamice pot fi create în același mod. Pentru aceste tabele va trebui să creați formula offset excel
Sintaxa OFFSET
| = OFFSET (referință, rânduri, coloane, [înălțime], [lățime]) |
Rânduri - cereți Excel să mute numărul de rânduri pentru a obține informațiile. În acest caz, trebuie să se deplaseze în jurul mesei și, prin urmare, lăsați pur și simplu un spațiu gol notat cu virgulă (,). În caz contrar, pentru a muta o coloană înainte dați valoarea 1 și pentru a muta o coloană înainte dați o valoare -1.
Coloane - aceasta este de a conduce funcția la numărul de coloane. Sistemul de valori este același cu cel pentru rând.
Înălţime - înălțimea mesei, în acest caz A11.
Lăţime - lățimea mesei, în acest caz D11.
După ce configurați funcția de compensare, rularea unei probe este importantă pentru a elimina orice erori.
Să luăm un tabel care conține numele, ID-ul de e-mail, data nașterii și vârsta. Tabelul începe la celula A1, conținând titlul „Nume”. Datele rulează pentru A11. Și rândurile rulează până la D1. Întregul tabel rulează A1: D11. Doriți un sistem de recuperare care să vă obțină numele atunci când introduceți ID-ul de e-mail sau ID-ul de e-mail când introduceți numele împreună cu alte detalii. Creați o formulă satisfăcând cele 5 argumente.
Punctul de referință este celula din care ar trebui să înceapă căutarea. În acest caz, ar trebui să fie A2. Acesta este standardul general.
Exemplu:
De exemplu, dați referință la B4 și la rândul de decalare 0, iar coloana de decalare 1, atunci valoarea din C4 va fi returnată. Confuz? Să avem câteva exemple. Ultimul exemplu de funcție OFFSET spune cum să SUMAȚI dinamic.
Funcția OFFSET pentru a obține valoare din dreapta și din stânga unei celule
Avem această masă.
Acum, dacă vreau să obțin valoare de la 2 celule la B2 (ceea ce înseamnă D2). Voi scrie această formulă OFFSET:
| = OFFSET (B2,0,2) |
Funcția OFFSET va muta 2 celule în dreapta celulei B4 și îi va returna valoarea. Vezi imaginea de mai jos
Dacă vreau să obțin valoare de la 1 celulă rămasă la B2 (ceea ce înseamnă A2). Voi scrie această formulă OFFSET:
| = OFFSET (B2,0, -1) |
Semnul minus (-) indică deplasarea pentru a vă deplasa invers.
Funcția OFFSET pentru a obține valoare de dedesubt și de deasupra unei celule
Deci, din nou în tabelul de mai sus, dacă vreau să obțin valoare de la 2 celule iubite la B3 (ceea ce înseamnă B5). Voi scrie această formulă OFFSET:
| = OFFSET (B2,2,0) |
Dacă vreau să obțin valoare de la 1 celulă rămasă la B2 (ceea ce înseamnă A2). Voi scrie această formulă OFFSET:
| = OFFSET (B2, -2,0) |
- Sfat Pro: Tratați un OFFSET ca indicator al graficului în care Referința este originea, iar rândul și coloana sunt axele x și y.
Funcția OFFSET la Sum Range dinamic
Să vedem acest exemplu.
În tabelul de mai sus Rândul total la sfârșitul tabelului. De-a lungul timpului, veți adăuga rânduri la acest tabel. Apoi, va trebui să modificați intervalul de sumă în formulă. Aici putem lua ajutorul funcției OFFSET pentru a rezuma dinamic. În prezent, în celula C11 avem = SUMĂ (C2: C10). Înlocuiți acest lucru cu formula de mai jos.
| = SUM (C2: OFFSET (C11, -1,0)) |
Această formulă ia primul rând de date și apoi rezumă intervalul peste rândul total.
Compensare pentru obținerea matricei
Funcția OFFSET are două argumente opționale, [înălțime] și [lățime]. Nu le luați de la sine. Când funcția OFFSET oferă argumente [înălțime] și [lățime], returnează o matrice bidimensională. Dacă doar unul dintre ele este trecut ca argument, acesta revine la matricea dimensională de valori.
Dacă introduceți această formulă în orice celulă:
| = SUM (OFFSET (C1,1,0,9,3)) |
Acesta va însuma valorile din intervalul de la C2 la 9 rânduri de mai jos și 3 coloane dreapta.
OFFSET (C1,1,0,9,3): Acest lucru se va traduce în {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}.
Deci, în concluzie, offsetul este o funcție foarte utilă a excel, care vă poate ajuta să rezolvați multe fire sugrumate. Spuneți-mi cum utilizați funcția OFFSET în formula dvs. și unde v-a ajutat cel mai mult.
Funcția de căutare este aceeași cu offset-ul, dar utilizează funcții precum Match, Counta și IF pentru a lucra o masă dinamică.
Folosirea offsetului în Excel necesită abilități și cunoștințe bune despre funcțiile Excels și despre modul în care acestea funcționează împreună. Scoaterea lui fără nicio eroare este cel mai mare obstacol.
Iată toate notele observaționale privind utilizarea formulei.
Note:
- Această formulă funcționează atât cu text cât și cu numere.
- Există cinci argumente ale compensării care trebuie satisfăcute pentru a obține informații fără erori. Dacă scrieți o formulă incorectă, apare o eroare Ref.
Sper că ați înțeles Ce este funcția Excel Offset și cum să o utilizați în Excel. Explorați mai multe articole despre căutarea Excel și comparați valorile folosind formule aici. Dacă ți-au plăcut blogurile noastre, împărtășește-le prietenilor tăi de pe Facebook. Și, de asemenea, ne puteți urmări pe Twitter și Facebook. Ne-ar plăcea să aflăm de la dvs., să ne anunțați cum putem îmbunătăți, completa sau inova munca noastră și să o îmbunătățim. Scrieți-ne pe site-ul de e-mail.
Funcția VLOOKUP pentru a calcula nota în Excel : Pentru a calcula notele IF și IFS nu sunt singurele funcții pe care le puteți utiliza. VLOOKUP este mai eficient și mai dinamic pentru astfel de calcule condiționale. Pentru a calcula notele folosind VLOOKUP putem folosi această formulă.
17 lucruri despre Excel VLOOKUP : VLOOKUP este cel mai frecvent utilizat pentru recuperarea valorilor potrivite, dar VLOOKUP poate face mult mai mult decât atât. Iată 17 lucruri despre VLOOKUP pe care ar trebui să le știți să le utilizați eficient.
Căutați primul text dintr-o listă în Excel : Funcția VLOOKUP funcționează bine cu caractere wildcard. Putem folosi acest lucru pentru a extrage prima valoare text dintr-o listă dată în Excel. Iată formula generică.
CĂUTARE dată cu ultima valoare din listă : Pentru a prelua data care conține ultima valoare folosim funcția LOOKUP. Această funcție verifică celula care conține ultima valoare într-un vector și apoi folosește acea referință pentru a returna data.
Cum se recuperează cel mai recent preț în Excel : Este obișnuit să actualizați prețurile în orice afacere și să utilizați cele mai recente prețuri pentru orice cumpărare sau vânzare. Pentru a prelua cel mai recent preț dintr-o listă din Excel, folosim funcția CĂUTARE. Funcția de căutare obține cel mai recent preț.
Articole populare:
50 Comandă rapidă Excel pentru a vă crește productivitatea : Deveniți mai rapid la sarcina dvs. Aceste 50 de comenzi rapide vă vor face să lucrați și mai rapid pe Excel.
Cum se utilizează funcția IF în Excel : Instrucțiunea IF din Excel verifică starea și returnează o valoare specifică dacă condiția este ADEVĂRATĂ sau returnează o altă valoare specifică dacă este FALSĂ.
Cum se utilizează funcția VLOOKUP în Excel : Aceasta este una dintre cele mai utilizate și populare funcții ale Excel, care este utilizată pentru a căuta valoarea din diferite intervale și foi.
Cum se utilizează funcția COUNTIF în Excel : Numărați valorile cu condiții folosind această funcție uimitoare. Nu trebuie să vă filtrați datele pentru a număra anumite valori. Funcția Countif este esențială pentru a vă pregăti tabloul de bord.
Cum se utilizează funcția SUMIF în Excel : Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori în condiții specifice.