Modifică automat intervalele de celule din Excel

În procesul de lucru cu date în Excel nu este, uneori, cunoscut în prealabil modul în care datele de mult vor fi colectate într-un anumit tabel. Prin urmare, nu putem ști întotdeauna în avans, ceea ce ar trebui să acopere gama de nume. La urma urmelor, cantitatea de date poate varia. Pentru a rezolva această problemă ar trebui să se schimbe automat intervalul numit de celule folosite, în funcție de cantitatea de date introduse.

Cum să Excel pentru a face o schimbare automată a domeniului

Să presupunem că avem un obiect de investiții, pe care dorim să știm profitul total pentru întreaga perioadă de utilizare. În avans, nu putem determina perioada de utilizare a obiectului de investiții. Dar trebuie să monitorizeze în mod constant veniturile totale, ceea ce ne aduce la obiectul de investiții.

Generați raportul privind randamentul obiectului nostru de investiții, așa cum se arată mai jos:

ar putea, ar rezolva această problemă prin adăugarea întregii coloane B, și după vizitarea înregistrează suma totală se va schimba automat. Dar aceasta este un mod incorect de a rezolva problemele din Excel. În primul rând, nu vom putea folosi celula din coloana B pentru a introduce alte date. În al doilea rând, pentru că funcția de însumare este irațional de a consuma mai multă memorie. Acest lucru poate duce la probleme grave atunci când se lucrează cu un document. Soluția cea mai rațională este de a utiliza numele dinamice.

1. Selectați „Formula“ instrument - „Anumite nume“ - „Atribuire nume“.
2. Completați caseta de dialog „Creați un nume“, așa cum se arată. Vă rugăm să rețineți că, în „Range:“ Noi folosim funcția = OFFSET, și într-unul dintre parametrii utilizați funcția = count. Exemplu: = OFFSET (!!! Foaia1 $ B $ 2; 0; 0; COUNT (Foaia1 $ B $ B :!); 1)
   
3. Deplasați cursorul la celulă și introduceți numele funcției D2 = SUM „Venituri“, în parametrii săi.

Acum, umple treptat celula din coloana B, putem urmări modul în care acoperirea de celule numite „venituri“.

Funcția OFFSET în Excel

Să examinăm mai în detaliu caracteristicile pe care le-am introdus în raza de acțiune a câmpului atunci când se creează un nume dinamic.

Funcție = OFFSET definește gama în funcție de numărul de celule umplute în coloana B. 5 = parametrii funcției OFFSET (celula inițială; ofset rânduri interval de mărime, de offset coloane; interval de mărime în mărime înălțime în intervalul de lățime):

1. „Celule primare“ - indică celula din stânga sus, din care se va extinde gama de dinamic, atât în ​​jos și spre dreapta (dacă este necesar).
2. „Offset de-a lungul liniilor“ - parametrul definește cât de multe au nevoie pentru a schimba intervalul de verticală dintr-o celulă inițială (primul parametru). Valorile pot fi zero sau negativ.
3. „Offset de coloana“ - parametrul determină cât de multe trebuie să treacă pe orizontală dintr-o celulă inițială. Valorile pot fi chiar zero sau negativă.
4. „Gama de dimensiuni în înălțime“ - numărul de celule la care doriți să crească intervalul în înălțime. De fapt, numele vorbește de la sine.
5. „Lățimea dreptunghiului Size“ - numărul de celule prin care necesitatea de a crește în lățime din celula de pornire.

Ultimele două funcții sunt parametru opțional. Dacă acestea nu sunt complete, gama va consta din prima celulă. De exemplu: OFFSET = (A1; 0; 0) - este o celula A1, iar parametrul OFFSET = (A1; 2; 0) se referă la A3.

Considerăm acum funcția: = cont, pe care am subliniat în al patrulea parametru al funcției: = OFFSET.

Ce determină funcția COUNT

Funcția = COUNT ($ B: $ B) contorizează automat numărul de celule umplute în coloana B.

Astfel, folosim SCOR = () și = OFFSET () automatiza procesul de formare a unui nume pentru trupa „venituri“, ceea ce face dinamic. Acum ne uităm din nou la formula noastra, care ne sunt date numele de "venituri": = OFFSET (Foaia1 $ B $ 2; 0; 0; COUNT (Foaia1 $ B $ B :!); 1!!!)

Citiți această formulă ar trebui să fie după cum urmează: Primul parametru indică faptul că gama noastră începe să se modifică automat în celula B2. Următorii doi parametri au o valoare de 0, 0 - aceasta înseamnă că gama dinamică nu este deplasată în raport cu celula B2 inițială. Dar aceasta crește numai dimensiunea verticală, după cum reiese din parametrul 4-lea. Este funcția COUNT returnează un număr egal cu numărul de celule umplute în coloana B. Prin urmare, numărul de celule din intervalul vertical este egal cu numărul, care ne va da o funcție de numărare. O lățime a benzii ne întâlnim ultimul parametru 5-lea, în cazul în care este numărul 1.

Datorită contului, am încărcat eficient în memorie numai celule umplute din coloana B, și nu întreaga coloană în întregime. Acest fapt elimină erorile asociate cu memorie atunci când se lucrează cu acest document.

Grafice dinamice în Excel

Avem un nume dinamic, creați acum o diagramă dinamică pentru tipul de raport:

Folosind numele nostru dinamic de „venit“ am fost capabili de a crea în mod automat se modifică diagrama dinamică, care se adaugă și afișează date noi în raport.