Blog

Intervale dinamice în Excel: când să folosești OFFSET sau un tabel

Actualizează totalurile și opțiunile listelor derulante când adaugi rânduri. Vezi cum funcționează OFFSET și COUNTA, ce se întâmplă cu un ID gol și când să alegi un tabel Excel.

Intervalul evidențiat se extinde în jos când se adaugă un rând.

Adaugi un produs la lista din Excel, dar totalul și opțiunile din lista derulantă rămân neschimbate. Dacă referința este în continuare A2:A10, rândul 11 rămâne în afara intervalului. Un interval dinamic îi permite aplicației Excel să determine capătul listei fără să modifici formulele de fiecare dată.

Calcularea înălțimii și lățimii cu OFFSET

Dacă datele sunt contigue, numără antetele și ID-urile ca să dai funcției OFFSET înălțimea și lățimea. Creează exemplul în Sheet1 și introdu formula de total pe altă foaie. Păstrează antetele în japoneză exact cum sunt scrise pentru ca referințele structurate de mai jos să funcționeze.

Celulă Coloana A Coloana B Coloana C
Rândul 1 商品ID (ID de produs) 商品名 (nume de produs) 数量 (cantitate)
Rândul 2 A01 Pix 4
Rândul 3 A02 Caiet 6
=SUM(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$1000),COUNTA(Sheet1!$A$1:$Z$1)))

Pornește de la A1 fără decalaj. Numărul de celule completate din A dă înălțimea, iar numărul de antete de pe primul rând dă lățimea. Rezultatul este A1:C3, cu trei rânduri și trei coloane și totalul 10. Adaugă A03, Riglă și 2 la rândul 4. Intervalul devine A1:C4 și totalul 12. ID-urile sunt text, iar zonele numărate nu conțin alte date. Referința Microsoft pentru OFFSET

Șterge doar ID-ul din A3. În A se numără trei celule: antetul A1 și ID-urile din A2 și A4. De aceea, OFFSET returnează primele trei rânduri, A1:C3. Cantitatea 6 de pe rândul 3 rămâne în sumă, dar 2 de pe rândul 4 iese din interval. Totalul revine la 10.

Celula A3 este goală. Intervalul OFFSET A1:C3 include 6 de pe rândul 3 și exclude 2 de pe rândul 4, cu totalul 10.
Conturul verde arată intervalul A1:C3 returnat de OFFSET în Excel pentru Windows.

Un antet gol la mijloc poate lăsa și ultimele coloane în afara intervalului. COUNTA numără celulele cu formule care returnează un șir gol (""). Păstrează antetele și ID-urile contigue într-un registru existent și verifică limitele și celulele goale. Dacă este prezent ID-ul, o cantitate goală nu schimbă înălțimea. Referința Microsoft pentru COUNTA

Când antetele încep pe rândul al doilea

Dacă primul rând conține titlul raportului, începe tabelul în A2 și numără de la rândul 2 pentru a exclude titlul. Mutând exemplul cu un rând în jos, obții A2:C4 pentru antete și două produse. Totalul rămâne 10.

=SUM(OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1000),COUNTA(Sheet1!$A$2:$Z$2)))

Folosirea unui tabel care se extinde la adăugarea de rânduri

Pentru o listă nouă, selectează intervalul cu antete și alege Inserare → Tabel. Numește-l Items. Formula următoare adună cantitățile dintr-o celulă din afara tabelului. Intervalul de însumare crește la adăugarea de rânduri. Ghidul Microsoft pentru referințe structurate

=SUM(Items[数量])

Însumarea doar a rândurilor cu ID de produs

După conversia A1:C4 în tabelul Items, formula anterioară adună 4 + 6 + 2 și returnează 12, chiar dacă A3 e goală. Pentru a însuma doar rândurile cu ID, folosește SUMIF cu condiția ca ID-ul să nu fie gol. În exemplu, rândurile 2 și 4 dau 6 (4 + 2). Referința Microsoft pentru SUMIF

=SUMIF(Items[商品ID],"<>",Items[数量])

Sincronizarea listei derulante cu tabelul

Pentru a selecta nume de produse pe altă foaie, deschide Formule → Manager de nume în Excel pentru desktop. Creează ItemChoices cu domeniul întregului registru și setează =Items[商品名] în Se referă la. Pentru celula de intrare, alege Listă la Validarea datelor și scrie =ItemChoices la Sursă. Antetul rămâne exclus, iar rândurile noi de la sfârșitul tabelului adaugă opțiuni. Completează numele din fiecare rând nou pentru a evita opțiunile goale. Ghidul Microsoft pentru crearea unei liste derulante