Intervalul dinamic cu auto-dimensiuni

Ai un tabel cu datele în Excel, dimensiunile care pot fi schimbate, și anume, numărul de linii (coloane), poate crește sau descrește în timpul funcționării? În cazul în care dimensiunea tabelului „plutească“ este necesar să se monitorizeze în mod constant acest punct și pentru a corecta:

Link-uri către rapoarte formule care se referă la masa noastră
sursă variază de tabele de sinteză pe care sunt construite pe masa noastră
sursă variază de parcele de la masa
Intervalele pentru liste derulante care sunt utilizate în tabelul nostru ca sursa de date

Toate această sumă nu va lasa sa te plictisesti;)

Este mult mai ușor și mai corect pentru a crea un domeniu dinamic „de cauciuc“, care se va ajusta în mod automat în mărime de numărul real de rânduri de coloane de date. Pentru a realiza acest lucru, există mai multe moduri.

Metoda 1. Un tabel inteligent

Evidențiați intervalul de celule, și apoi faceți clic pe fila Acasă - Format de masă (Acasă - Format ca tabelul):

Dacă nu aveți nevoie de un design cu dungi, care se adaugă la efectul secundar de masă, acesta poate fi dezactivat pe fila Design care apare (Design). Fiecare tabel astfel creat este numit, care poate fi înlocuită cu o mai confortabil acolo pe tab-ul Design (Design) în numele tabelului (tabelul Nume).

Acum puteți utiliza link-ul dinamic la „masa de inteligent“ nostru:

Astfel de referințe funcționează în mod remarcabil în formulele, de exemplu:

= SUM (Table1 [București]) - calcularea sumei coloanei „București“

= CDF (F5, tabelul 1, 3, 0) - luni tabel de căutare a celulei F5 și livrare St. Petersburg cantitate de ea (adică CDF?)

Astfel de link-uri pot fi folosite cu succes pentru a crea tabele de sinteză. selectarea pe Inserare fila - Rezumat tabel (Insert - Pivot Table), și introducerea de nume de tabel inteligent ca sursă de date:

Dacă selectați o bucată de astfel de tabel (de exemplu, primele două coloane) și pentru a crea o diagramă de orice tip, atunci Append rânduri noi, acestea sunt adăugate automat diagrama.

Când creați un drop-down liste de legături directe cu elemente din tabelul inteligente nu pot fi folosite, dar puteți obține cu ușurință în jurul valorii de această limitare prin utilizarea stealth tactice - Utilizați funcția INDIRECT (INDIRECT). care convertește textul în link-ul:

Metoda 2. Interval dinamic numit

Dacă vă convertiți datele în tabelul de inteligent, indiferent de motiv, nu doriți, puteți folosi o metodă pic mai dificil, dar cu mult mai vizibil și universal - pentru a crea un Excel dinamic interval numit, referindu-se la masa noastră. Apoi, la fel ca în cazul mesei inteligente, puteți utiliza în mod liber numele gamei create în toate formulele, rapoarte, diagrame, etc. Pentru a începe, luați în considerare un exemplu simplu:

Sarcină. face un domeniu dinamic, care ar fi referit la lista orașelor și întindeți-micșora în dimensiune atunci când adăugați noi orașe sau îndepărtarea lor în mod automat.

Avem nevoie de două funcții predefinite Excel disponibile în orice versiune - POIKSPOZ (MECI) pentru a determina ultima celulă în intervalul și Index (Index) pentru a crea o legătură dinamică.

Cautam cele mai noi celule folosind POTRIVIRE

Esența truc este simplu. MECI recapitulează într-o căutare de celule într-un interval de sus în jos și, în teorie, ar trebui să se oprească atunci când constată în apropiere de cea mai mică valoare la țintă. Dacă specificați o valoare pentru dorită cunoscută mai mult decât orice disponibile în tabel, meciul se va ajunge la sfârșitul tabelului, nimic nu se va da și numărul de serie al ultimei celule umplut. Și avem nevoie de ea!

În cazul în care oferta noastră de numere numai, este posibil ca valorile necesare indică numărul care este în mod evident mai mare decât orice disponibile în tabelul de mai jos:

Pentru a vă asigura că puteți utiliza un număr de 9E + 307 (9 înmulțită cu 10 până la 307 de grade, adică 9 307 zerouri) - numărul maxim, care, în principiu, poate funcționa Excel.

Dacă în această coloană, valoarea de text, ca echivalent al unui număr maxim aveți posibilitatea să inserați design pentru a repeta ( „eu“, 255) - un șir de text format din 255 de litere „I“ - ultima literă a alfabetului. Deoarece Excel Solver, de fapt, compară codurile de caractere, orice text din graficul va fi punct de vedere tehnic „mai puțin“ atât de mult „... .Am yayayayaya“ linie:

Forma o legătură folosind INDEX

Acum, că știm poziția ultimei celule non-gol în tabel, necesare pentru a genera un link către întreaga noastră gamă. Pentru a face acest lucru, folosiți funcția:

INDEX (gama num_rând; num_coloană)

Acesta dă conținutul celulei în intervalul numărul rândului și numărul coloanei, adică, o astfel de funcție = INDEX (A1: D5; 3; 4) pe graficul cu orașele și lunile metodei anterioare va da 1,240 - conținutul treilea rând și coloana 4-a, adică Celula D3. Dacă o coloană este doar una, nu puteți specifica numărul său, și anume Formula INDEX (A2: A6, 3) va da "Samara" ultima captură de ecran.

Și aici vine în funcția MECI, pe care am pus în codul pentru a defini în mod dinamic sfârșitul listei:

= $ A $ 2: INDEX ($ A $ 2: $ A $ 100, MECI (REPEAT ( 'I'; 255); A2: A100))

Creați un interval de nume

Rămâne totul pachet într-o singură unitate. Faceți clic pe fila Formule (Formulas), apoi faceți clic pe Manager de Nume (Name Manager). În fereastra care se deschide, faceți clic pe New (Nou). introduceți numele nostru bandă și formula în intervalul (de referință):