Cum să setați un interval de valori în Excel

Când folosește Microsoft Excel, de foarte multe ori utilizatorul nu știe în avans câte informații vor fi în tabel ca urmare. Prin urmare, nu înțelegem în toate situațiile ce interval ar trebui acoperit. La urma urmei, un set de celule este un concept variabil. Pentru a scăpa de această problemă, este necesar să faceți automat generarea intervalului, astfel încât să se bazeze exclusiv pe cantitatea de date introdusă de utilizator.

Schimbarea automată a intervalelor de celule în Excel

Avantajul intervalelor automate din Excel este că facilitează utilizarea formulelor. În plus, fac posibilă simplificarea semnificativă a analizei datelor complexe care conțin un număr mare de formule, care includ multe funcții. Puteți da un nume acestui interval, iar apoi va fi actualizat automat în funcție de datele pe care le conține.

Cum se face schimbarea automată a intervalului în Excel

Să presupunem că ești un investitor care trebuie să investească într-un obiect. Ca urmare, dorim să obținem informații despre cât puteți câștiga în total pentru tot timpul în care banii vor funcționa pentru acest proiect. Cu toate acestea, pentru a obține aceste informații, trebuie să monitorizăm în mod regulat cât de mult profit total ne aduce acest obiect. Faceți același raport ca în această captură de ecran.

La prima vedere, soluția este evidentă: trebuie doar să însumați întreaga coloană. Dacă în el apar intrări, suma va fi actualizată independent. Dar această metodă are multe dezavantaje:

1. Dacă problema este rezolvată în acest fel, nu va fi posibilă utilizarea celulelor incluse în coloana B în alte scopuri.
2. Un astfel de tabel va consuma multă memorie RAM, ceea ce va face imposibilă utilizarea documentului pe computere slabe.

Prin urmare, este necesar să se rezolve această problemă prin nume dinamice. Pentru a le crea, trebuie să efectuați următoarea secvență de acțiuni:

1. Accesați fila „Formule”, care se află în meniul principal. Va exista o secțiune „Nume definite”, unde există un buton „Atribuiți un nume”, pe care trebuie să facem clic.
2. Apoi, va apărea o casetă de dialog în care trebuie să completați câmpurile așa cum se arată în captură de ecran. Este important de reținut că trebuie să aplicăm funcția = DEPLACARE împreună cu funcţia VERIFICApentru a crea un interval de actualizare automată. 
3. După aceea trebuie să folosim funcția USM, care folosește gama noastră dinamică ca argument. 

După parcurgerea acestor pași, putem vedea cum se actualizează acoperirea celulelor care aparțin intervalului „venit” pe măsură ce adăugăm noi elemente acolo.

Funcția OFFSET în Excel

Să ne uităm la funcțiile pe care le-am înregistrat mai devreme în câmpul „interval”. Folosind funcția ELIMINAREA putem determina cantitatea de interval având în vedere câte celule din coloana B sunt umplute. Argumentele funcției sunt după cum urmează:

1. Porniți celula. Cu acest argument, utilizatorul poate indica care celulă din interval va fi considerată sus, stânga. Va raporta în jos și în dreapta.
2. Interval compensat de rânduri. Folosind acest interval, setăm numărul de celule cu care ar trebui să apară o compensare din celula din stânga sus a intervalului. Puteți folosi nu numai valori pozitive, ci zero și minus. În acest caz, deplasarea poate să nu aibă loc deloc sau va fi efectuată în direcția opusă.
3. Interval compensat de coloane. Acest parametru este similar cu cel anterior, doar că vă permite să setați gradul de deplasare orizontală a intervalului. Aici puteți utiliza, de asemenea, atât valori zero, cât și valori negative.
4. Cantitatea de gamă în înălțime. De fapt, titlul acestui argument ne arată clar ce înseamnă. Acesta este numărul de celule cu care intervalul ar trebui să crească.
5. Valoarea intervalului în lățime. Argumentul este asemănător celui precedent, doar că se referă la coloane.

Nu trebuie să specificați ultimele două argumente dacă nu este necesar. În acest caz, valoarea intervalului va fi doar o celulă. De exemplu, dacă specificați formula =OFFSET(A1;0;0), această formulă se va referi la aceeași celulă cu cea din primul argument. Dacă offset-ul vertical este setat la 2 unități, atunci în acest caz celula se va referi la celula A3. Acum să descriem în detaliu ce înseamnă funcția VERIFICA.

Funcția COUNT în Excel

Folosind funcția VERIFICA determinăm câte celule din coloana B am completat în total. Adică, folosind două funcții, determinăm câte celule din interval sunt umplute și, pe baza informațiilor primite, determinăm dimensiunea intervalului. Prin urmare, formula finală va fi următoarea: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)

Să aruncăm o privire la cum să înțelegem corect principiul acestei formule. Primul argument indică unde începe intervalul nostru dinamic. În cazul nostru, aceasta este celula B2. Alți parametri au coordonate zero. Acest lucru sugerează că nu avem nevoie de decalaje în raport cu celula din stânga sus. Tot ceea ce completăm este dimensiunea verticală a intervalului, pentru care am folosit funcția VERIFICA, care determină numărul de celule care conțin unele date. Al patrulea parametru pe care l-am completat este unitatea. Astfel, arătăm că lățimea totală a intervalului ar trebui să fie de o coloană.

Astfel, folosind funcția VERIFICA utilizatorul poate folosi memoria cat mai eficient prin incarcarea doar a acelor celule care contin unele valori. În consecință, nu vor exista erori suplimentare în lucru asociate cu performanța slabă a computerului pe care va funcționa foaia de calcul.

În consecință, pentru a determina dimensiunea intervalului în funcție de numărul de coloane, trebuie să efectuați o secvență similară de acțiuni, numai în acest caz trebuie să specificați unitatea în al treilea parametru și formula în al patrulea. VERIFICA.

Vedem că cu ajutorul formulelor Excel nu poți automatiza doar calcule matematice. Aceasta este doar o picătură în ocean, dar de fapt vă permit să automatizați aproape orice operațiune care vă vine în minte.

Diagrame dinamice în Excel

Deci, în ultimul pas, am putut crea un interval dinamic, a cărui dimensiune depinde complet de câte celule umplute conține. Acum, pe baza acestor date, puteți crea diagrame dinamice care se vor schimba automat de îndată ce utilizatorul face modificări sau adaugă o coloană sau un rând suplimentar. Secvența acțiunilor în acest caz este următoarea:

1. Selectăm intervalul nostru, după care inserăm o diagramă de tipul „Histogramă cu grupare”. Puteți găsi acest articol în secțiunea „Inserare” din secțiunea „Diagrame – Histogramă”.
2. Facem un clic stânga al mouse-ului pe o coloană aleatorie a histogramei, după care funcția =SERIES() va fi afișată în linia funcției. În captură de ecran puteți vedea formula detaliată. 
3. După aceea, trebuie făcute unele modificări formulei. Trebuie să înlocuiți intervalul după „Sheet1!” la numele intervalului. Aceasta va avea ca rezultat următoarea funcție: =RÂND(Foaie1!$B$1;;Foaie1!venit;1)
4. Acum rămâne să adăugați o nouă înregistrare la raport pentru a verifica dacă diagrama este actualizată automat sau nu.

Să aruncăm o privire la diagrama noastră.

Să rezumam cum am făcut-o. În pasul anterior, am creat un interval dinamic, a cărui dimensiune depinde de câte elemente conține. Pentru a face acest lucru, am folosit o combinație de funcții VERIFICA и ELIMINAREA. Am creat acest interval numit și apoi am folosit referința la acest nume ca interval al histogramei noastre. Ce interval specific să alegeți ca sursă de date în prima etapă nu este atât de important. Principalul lucru este să-l înlocuiți mai târziu cu numele gamei. În acest fel puteți economisi multă memorie RAM.

Intervalele denumite și utilizările lor

Să vorbim acum mai detaliat despre cum să creăm corect intervale denumite și să le folosim pentru a efectua sarcinile care sunt setate pentru utilizatorul Excel.

În mod implicit, folosim adrese de celule obișnuite pentru a economisi timp. Acest lucru este util atunci când trebuie să scrieți un interval o dată sau de mai multe ori. Dacă trebuie utilizat tot timpul sau trebuie să fie adaptiv, atunci ar trebui utilizate intervale denumite. Ele fac crearea formulelor mult mai ușoară și nu va fi atât de dificil pentru utilizator să analizeze formule complexe care includ un număr mare de funcții. Să descriem câțiva dintre pașii implicați în crearea intervalelor dinamice.

Totul începe cu denumirea celulei. Pentru a face acest lucru, pur și simplu selectați-l și apoi scrieți numele de care avem nevoie în câmpul numelui său. Este important să fie ușor de reținut. Există câteva restricții de luat în considerare la denumirea:

1. Lungimea maximă este de 255 de caractere. Acest lucru este suficient pentru a atribui un nume pe care inima ta îl dorește.
2. Numele nu trebuie să conțină spații. Prin urmare, dacă conține mai multe cuvinte, atunci este posibil să le separați folosind caracterul de subliniere.

Dacă mai târziu pe alte foi ale acestui fișier trebuie să afișăm această valoare sau să o aplicăm pentru a efectua calcule suplimentare, atunci nu este nevoie să trecem la prima foaie. Puteți doar să scrieți numele acestei celule interval.

Următorul pas este crearea unui interval numit. Procedura este practic aceeași. Mai întâi trebuie să selectați un interval, apoi să specificați numele acestuia. După aceea, acest nume poate fi folosit în toate celelalte operațiuni de date din Excel. De exemplu, intervalele denumite sunt adesea folosite pentru a defini suma valorilor.

În plus, este posibil să creați un interval denumit utilizând fila Formule folosind instrumentul Set Name. După ce îl selectăm, va apărea o fereastră în care trebuie să alegem un nume pentru gama noastră, precum și să specificăm zona în care se va extinde manual. De asemenea, puteți specifica unde va funcționa această gamă: într-o singură coală sau în întreaga carte.

Dacă un interval de nume a fost deja creat, atunci pentru a-l utiliza, există un serviciu special numit manager de nume. Permite nu numai editarea sau adăugarea de noi nume, ci și ștergerea lor dacă nu mai sunt necesare.

Trebuie avut în vedere faptul că atunci când utilizați intervale denumite în formule, apoi, după ștergere, formulele nu vor fi suprascrise automat cu valorile corecte. Prin urmare, pot apărea erori. Prin urmare, înainte de a șterge un interval numit, trebuie să vă asigurați că nu este utilizat în nicio formulă.

O altă modalitate de a crea un interval numit este să-l obțineți dintr-un tabel. Pentru a face acest lucru, există un instrument special numit „Creează din selecție”. După cum înțelegem, pentru a-l folosi, trebuie mai întâi să selectați intervalul pe care îl vom edita și apoi să setați locul în care avem titlurile. Ca urmare, pe baza acestor date, Excel va procesa automat toate datele, iar titlurile vor fi atribuite automat.

Dacă titlul conține mai multe cuvinte, Excel le va separa automat cu un caracter de subliniere.

Astfel, ne-am dat seama cum să creăm intervale de nume dinamice și cum vă permit să automatizați lucrul cu cantități mari de date. După cum puteți vedea, este suficient să utilizați mai multe funcții și instrumentele programului încorporate în funcționalitate. Nu este nimic complicat, deși poate părea așa pentru un începător la prima vedere.