Intervale denumite Excel cu reglare automată, blog-ul needfordata

Numele de funcții sunt în litere majuscule, meniuri și unii termeni sunt scrise cu caractere italice. Doar adaugă la exemplele de text original.

Intervale denumite în Excel - este un instrument de mare. Acestea vă permit să faci lucruri, cum ar fi listele derulante din punctul de validare a datelor. Sau este posibil să se numească datele din intervalul și se referă în continuare la aceasta, mai degrabă decât să indice coordonatele (A1: B5).

Una dintre problemele asociate cu lista susținută - necesitatea de a modifica intervalul de manager de Formula> spațiu de nume după fiecare adăugare / ștergere a liniilor de date în intervalul sursă. Pentru a evita o astfel de situație, este posibil să se creeze un interval dinamic prin aplicarea formulei coordonate în locul specificat în mod rigid. Funcția cel mai frecvent utilizat OFFSET așa cum se arată mai jos. Solicitare «Excel gamă dinamică“ în orice motor de căutare va returna sute de link-uri, dintre care majoritatea sunt variante ale formulei:

OFFSET = (Sheet $ A $ 1, 0, 0, COUNTA ($ A $ A :!), 1)

rânduri Smeschpo: 0 În mod normal, deoarece Poziția de pornire am identificat deja.

coloane Smeschpo: ca de obicei 0, pentru același motiv.

Pitch: numărul de linii de gama noastră (aici este formula, infra.).

Lățime: Numărul de coloane în acest interval (cel puțin 1).

Este clar că funcția OFFSET în sine nu este foarte util pentru scopurile noastre. Dacă aveți de gând să utilizați numărul static, apoi aruncați OFFSET, specificați în mod direct gama. Motivul pentru care folosim OFFSET - posibilitatea de a înlocui parametri (argumente) formule. Ele permit să se obțină o parte dinamică.

O formulă tipică a gamei dinamice, care pot fi găsite pe internet, utilizează COUNT (date numerice) sau COUNTA (pentru text). Ambele aceste caracteristici contoriza numărul de celule non-goale. Dacă luăm în calcul numărul de celule într-o singură care nu sunt goale coloană și de a introduce numărul în setarea de înălțime, vom obține intervalul de la celula de pornire la ultima celulă a înregistrării. Dar este în teorie.

De fapt, dacă citiți cu atenție, vom obține o gamă înălțime în numărul de linii pe care le-am primit de la COUNTA. Dacă intervalul conține celule goale, gama de ieșire va fi mai scurtă decât este necesar, iar ultima celulă a pierdut. Rețineți această subtilitate.

Intervalul dinamic este cel mai frecvent utilizat în următoarele cazuri:

Dacă avem un tabel care modifică numărul de linii, și există formule care folosesc link-ul de la masa, utilizarea gamei dinamice este un pas logic. Să presupunem că avem o „bază de date“, pe care le înregistrează fiecare cerere de client, și dorim să contoriza numărul de cereri specifice ale clientului. Apoi, puteți face ceva de genul:

În primul rând, vom crea un domeniu dinamic folosind numele Manager, apoi utilizați în formula noastră.

Formula pe care am folosit:

O altă variantă de realizare, folosind intervalul dinamic - tabelele rezumat. Noi folosim „de bază“, în exemplul anterior. Vom modifica formula gamei dinamice, deoarece este necesar să se includă antetele coloanelor și să permită a doua coloană. Dorim să obțineți suma totală pentru fiecare client.

Aici sunt datele inițiale:

Dar, după adăugarea de noi linii (nu uitați să faceți clic pe Refresh):

Un al treilea exemplu de realizare a gamei dinamice de utilizare - pentru determinarea intervalului inițial de diagrame. Aceasta afișează graficul, din care intervalul este controlat de date de intrare parametru.

În acest exemplu, două interval dinamic utilizat.

Ultimul exemplu pe care o vom lua în considerare - crearea de liste derulante de instrumentul de validare a datelor. Acest instrument este utilizat atunci când vrem să restricționați un utilizator la introducerea datelor într-o celulă. În coloana A, am introdus un set de date valide, și coloana N instituie liste derulante (modul de a crea nu este considerat-le aici).

După adăugarea de noi linii:

Gama foarte dinamică formulă banală:

În cele din urmă vom da un exemplu de a crea un domeniu dinamic prin funcția INDEX. În general, este de preferat să se aplice funcția nevolatilă. INDEX tocmai acest lucru, dar OFFSET - volatile. Rezultatul aplicării unui indice pe aspectul este la fel ca și OFFSET.

Luați în considerare formula de obținere a gamei dinamice:

REPEAT Design ( 'I'; 255) creează un șir de 255 de litere "I". Atunci când se compară aproape orice text ar fi „mai mică“, iar funcția găsește ultimul rând. Un alt avantaj al utilizării funcției INDEX este insensibil la celulele goale.

Rezultat pentru OFFSET:

Și se comportă INDEX: