Nu crezi că ar fi grozav dacă ar putea modifica intervalul de date Excel, de exemplu, grafice sau diagrame, pentru a adăuga noi rânduri sau coloane. De fapt, capacitatea de a crea exista game dinamice, și este pus în aplicare prin intermediul funcției OFFSET.
Funcția OFFSET returnează o referință la o gamă de un anumit loc cu o înălțime și lățime predeterminată.
Ce înseamnă? De exemplu, aveți o serie de date care este utilizat pentru a construi graficul, dar numărul de linii este atât de mare încât graficul devine ilizibil utilizatorilor. Calea de ieșire din situația va arăta doar anumite părți ale datelor. De exemplu, utilizatorii vor trebui să specifice ultimul trimestru sau un an și funcția OFFSET pentru a trimite o nouă gamă de date pentru a construi o diagramă.
Sintaxa formula OFFSET
Formula OFFSET este după cum urmează:
Și, bineînțeles, toate argumentele se pot referi la alte celule. Acest lucru înseamnă că puteți scrie formula = OFFSET (A1, D1, D2, D3, D4), care va returna o serie de
- Decalate de la celula A1
- În liniile D1 și D2 de coloane
- Dimensiunea liniilor D3 și D4 a coloanei
Figura prezintă un exemplu de funcționare a formulei OFFSET pentru o mai bună înțelegere.
De ce să folosiți formula OFFSET?
De ce pur și simplu nu se poate specifica în mod explicit numele gamei, de exemplu, A1: C4?
Iată câteva motive:
Descrierea interactivă formula OFFSET
Formula OFFSET este destul de greu de înțeles, astfel încât un tabel interactiv a fost creat, astfel încât să puteți înțelege mai bine cum funcționează. Modificarea 5 argumente formulă, Excel va evidenția intervalul pe care l-ați selectat. După câteva minute de răsfăț, veți înțelege mai bine formula.
Practicarea cu formula OFFSET - valoarea medie a ultimei săptămâni
Să presupunem că lucrăm în calitatea serviciilor a fabricii pentru producerea de batoane de ciocolată. Unul dintre indicatorii de performanță (KPI), care avem nevoie pentru a urmări este bare defecte. Fișa de colectare a datelor va arăta în felul următor.
Deci, cum putem presupune că căsătoria medie într-o săptămână?
Dacă presupunem că datele sunt în intervalul B2: B23, atunci trebuie să scrie = AVERAGE (B2: B23).
Dar avem nevoie pentru a menține această formulă este schimbată zilnic.
Folosind formula OFFSET putem găsi o dată media și nu a reveni la această problemă.
Să examinăm modul în care această formulă
- Pentru a calcula valoarea medie a ultimei săptămâni, trebuie să tragem in ultimele 7 rânduri, cu toate set de date.
- Acolo a venit de la formula COUNTA (B2: B300) -7, care contorizează numărul de valori, și apoi, din cifra rezultată se scade 7.
- Formula OFFSET se mută de la B2 celula la celula de săptămâna trecută.
- Pe GIFCA arată modul în care gama dinamică de lucrări.
Ați putea fi, de asemenea, interesat în următoarele articole
Renat, vă mulțumesc foarte mult pentru descrierea funcției!
Ați putea explica, de asemenea, metoda care a fost folosită pentru a crea un tabel interactiv. În special, mă interesează o parte „ROWS = (diap_smesch I12)», care returnează un «gol», în cazul în care celula este în afara intervalului, iar «1» dacă în interior. Prima dată când văd utilizarea denumirii de matrice printr-un spațiu cu o referință la o celulă ar dori să înțeleagă ceea ce este mecanismul din spatele ei. Mulțumesc mult.
Renat, este unul dintre trucuri Excel. Funcția ROWS vă permite să spun numărul de rânduri care se află la intersecția a două matrice (în acest caz, matrice și celule). Gap indică faptul că avem nevoie este intersecția
Am citit cu mare interes articol despre formula OFFSET.
Foarte interesant și informativ! mai ales a placut
la utilizarea OFFSET pentru a calcula media în ultima săptămână.
Doar o mare! Acesta a fost mult timp în căutarea pentru ceva de genul asta, un simplu
și elegant. Vă mulțumim pentru munca grea.
Spune-mi, este posibil să rescrie cumva formula astfel
că a luat în considerare în calculele (ignorirovala-?) celule goale?
Am încercat să aplice formula la o serie de numere, în care
există yacheyki.Primenitelno goale, de exemplu, în articolul dumneavoastră - este ca și cum
lipsit, de exemplu, numărul 98 în celula B20. Rezultatul a fost un fel de nonsens, cu formula
Se pare a fi luate în considerare, dar a dat o valoare greșită. Cum pot rezolva această problemă,
această formulă a lucrat cu o serie de numere, în care există celule goale?
Eu personal nu vin în minte nimic, am încercat în acest fel și că, nimic nu
Nu funcționează. Poate că va ajuta la rezolvarea acestei probleme?
Bună după-amiază pentru a determina poziția ultimului element din gama, aveți nevoie de o matrice continuă (fără celule goale). Dacă totul este încă în intervalul de lacune, rolul unei astfel de matrice poate juca o coloană cu date. Apoi, formula devine: = AVERAGE (OFFSET (B2; COUNTA (A2: A300) -7; 0, 7, 1))
Bine ai venit Renat! Vă mulțumim pentru ajutor, noua formulă funcționează perfect,
toate luate în considerare în mod corespunzător, inclusiv în prezența celulelor goale. Doar ce am
și avea nevoie. Vă mulțumim din nou pentru ajutor.
Utilizați formula pentru 2 zile, funcționează bine, vă mulțumesc din nou
pentru ajutor.
În timpul funcționării, cu formula a apărut o singură problemă asociată cu caracteristica
aceste date, pe baza cărora am construi un tabel. Cumva am ratat din vedere atunci când
pentru prima dată, a apelat la tine pentru ajutor. Poate va ajuta să-l rezolve acum?
Tu scrie:“... Pentru a calcula valoarea medie a ultimei săptămâni,
trebuie să tragem in ultimele 7 rânduri, cu toate set de date.
Acolo a venit de la formula COUNTA (B2: B300) -7, care contorizează numărul
valori și apoi scăzute din cifre primite 7. „Ie In ambele formula
exemple de realizare, seturi offset pentru un anumit număr de rânduri 7, în acest caz,
și le-au calculează media. În cazul meu, în cazul în care există celule goale într-un interval,
o situație poate apărea atunci când aceste celule sunt goale, deoarece
intervale între datele sa constatat că, în unele cazuri 1 celulă și
alte celule 5,10 și chiar 24 (!) pot fi goale. Am confruntat astăzi
cu această opțiune, nu mă așteptam la așa ceva. Și, toate acestea sunt în continuă schimbare.
În astfel de circumstanțe, calculul este în intervalul, constând dintr-un număr de
linii, foarte inconfortabil. Conturi încă la lopata întreaga coloană de mână.
În acest caz, mult mai bine ar fi să ia în considerare o gamă constând
nu dintr-un anumit număr de celule (7), așa cum se face cu tine, și gama constând din
a unui număr de numai umplere a celulelor (7). Asta, ținând cont de gol
Celulele dintre ele se pot ocupa 7,10,20 ... orice număr de celule, le număra
rând din ultima umplut până la al șaptelea, de exemplu, se află în intervalul B.
Am nevoie de această formulă. Poate că, pe baza formulei dumneavoastră, v-ar putea crea
ceva de genul asta? Aș fi foarte recunoscător pentru tine.
Cu referire la exemplul din articolul dumneavoastră și soluții luând în considerare pentru o serie de gol
ai celula deja am sugerat, secvența de acțiuni ar putea arata asa
mod. COUNTA efectua prin coloana A, cu date, deoarece nu spații. apoi, acest lucru
coloană selectați numai acele date, pentru care, în coloana B este umplut celule. alegere
un anumit număr de celule din coloana B, de exemplu ultimele 7, și deja așteaptă cu nerăbdare să-i
MEDIE. Atunci când această structură întreg, desigur, ar trebui să funcționeze la fel ca formulă,
În cazul în care vor fi adăugate celula A și noile valori. Ceva de genul asta.
Dar cum toate acestea pentru a alege și se potrivesc? Cu ajutorul CDF, IF, lucra cu baze de date
date, în cazul în care coloana B desemnate ca baza de date, sau chiar a unor funcții și recepții?
Aici cunoștințele mele nu este suficient, aștept cu nerăbdare să ajutorul tau.
Vladislav, în cazul în care aveți nevoie pentru a găsi următorul după valoarea maximă, utilizați cea mai mare, care returnează cel mai mare element solicitat în matrice. Mai multe informații despre această caracteristică poate fi găsită în articolul despre funcția cea / cel mai puțin
Alo Ajutor cu o problemă.
Este esențial ca atunci când managerul selectează zi a lunii - în coloana adiacentă din lista drop-down au fost disponibile numai pentru acele mașini care sunt marcate ca „disponibile“ în ziua (și chiar necesar ca liniile goale nu au fost în lista verticală)
Pentru a face acest lucru, am făcut o listă cu mașinile și posibilitatea ca o persoană manual prin salturi remarcat care dintre ele vor fi disponibile (deoarece este necesar ca a fost posibil pentru ziua de azi și, de exemplu, de 3 zile în avans sunt disponibile autoturisme). Apoi a făcut o altă masă în contextul zilelor în care mașinile sunt transportate fără un spațiu disponibil pentru fiecare zi a intervalului numit creat (folosind OFFSET la cele din urmă nu a existat nici celule goale lista drop-down).
Ranges în fiecare zi numit D1, D2 ... D31.
Și acum, când încerc să atașați benzile până în prezent, folosind INDIRECTE, eroarea cade în sursa (așa cum am înțeles, pentru că INDIRECTE, și OFFSET nu sunt compatibile?)
Ce altceva mai pot gândi la un fel puteți înregistra imediat în sursa cu formula OFFSET că, atunci când selectați o zi a lunii - tocmai a ieșit lista de mașini disponibile ale zilei.
Fără a OFFSET toate se transformă, dar apoi celulele goale din listă.
Aș fi foarte recunoscător pentru ajutor.
aici sunt imaginile pentru o lungă perioadă de timp pentru a merge.
Este în prezent achiziționarea oricărui echipament necesar să se producă brusc, plus o calitate, cu alte cuvinte, actul în sine pe datele favorabile privind banii în cauză este, de asemenea, conținut excelent bun. Prezentat tehnologia de ventilație magazin oferă klimatizernoy sistem de alimentare a diferitelor clădiri mai ales în aceste condiții. climat de Master Firma in Kiev indiciilor casete conditionat creează o vânzare, transport, orientare, reparare, demontare, curățare și diagnosticare a echipamentelor de aer condiționat achiziționate la prețuri mici și încă garanție pentru aprobare. În acest web-showroom abonați master climatice câștigă de fezabilitate pentru alocarea de produs real recunoscut ca companiile Panasonic Est, NeoClima, Cooper Hunter, Mitsubishi, Olma, LEBERG, care pot fi comandate din capitala prețurilor ușor de înțeles. Expertul climatice utilizează implementarea sistemelor de ventilare și încălzire, care asigură normale sreduvozduh posodeystvuet dispuse în afară de dlitelnuyudolgosrochnuyu de acțiune pentru perioada lungă de utilizare. Apel la Expertul climatice și selectați pentru speciile manager de clienți ales în întregime pe un contract profitabil.
Îmi cer scuze, dar, în opinia mea, nu dreptate. Sunt asigurat. Trimite un mesaj pentru mine în PM, vom comunica.