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.
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.
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