Formula Excel: Interval denumit dinamic cu OFFSET -

Formula generică

=OFFSET(origin,0,0,COUNTA(range),COUNTA(range))

rezumat

O modalitate de a crea o gamă dinamică denumită cu o formulă este utilizarea funcției OFFSET împreună cu funcția COUNTA. Intervalele dinamice sunt, de asemenea, cunoscute sub numele de intervale de extindere - se extind și se contractă automat pentru a găzdui date noi sau șterse.

Notă: OFFSET este o funcție volatilă, ceea ce înseamnă că recalculează cu fiecare modificare a unei foi de lucru. Cu o mașină modernă și un set de date mai mic, acest lucru nu ar trebui să provoace o problemă, dar este posibil să vedeți performanțe mai scăzute la seturile de date mari. În acest caz, luați în considerare construirea unui interval denumit dinamic cu funcția INDEX.

În exemplul prezentat, formula utilizată pentru intervalul dinamic este:

=OFFSET(B5,0,0,COUNTA($B$5:$B$100),COUNTA($B$4:$Z$4))

Explicaţie

Această formulă utilizează funcția OFFSET pentru a genera un interval care se extinde și se contractă prin ajustarea înălțimii și lățimii pe baza numărului de celule care nu sunt goale.

Primul argument din OFFSET reprezintă prima celulă din date (originea), care în acest caz este celula B5. Următoarele două argumente sunt compensări pentru rânduri și coloane și sunt furnizate ca zero.

Ultimele două argumente reprezintă înălțimea și lățimea. Înălțimea și lățimea sunt generate din mers folosind COUNTA, ceea ce face ca referința rezultată să fie dinamică.

Pentru înălțime, folosim funcția COUNTA pentru a număra valori ne-goale în intervalul B5: B100. Aceasta presupune că nu există valori goale în date și nu există valori dincolo de B100. COUNTA returnează 6.

Pentru lățime, folosim funcția COUNTA pentru a număra valorile ne-goale din intervalul B5: Z5. Acest lucru nu presupune celule de antet și nici anteturi dincolo de Z5. COUNTA returnează 6.

În acest moment, formula arată astfel:

=OFFSET(B5,0,0,6,6)

Cu aceste informații, OFFSET returnează o referință la B5: G10, care corespunde unei game de 6 rânduri înălțime cu 6 coloane.

Notă: Intervalele utilizate pentru înălțime și lățime trebuie ajustate pentru a se potrivi cu aspectul foii de lucru.

Variație cu referințe complete pentru coloane / rânduri

De asemenea, puteți utiliza referințe complete pentru coloane și rânduri pentru înălțime și lățime, astfel:

=OFFSET($B$5,0,0,COUNTA($B:$B)-2,COUNTA($4:$4))

Rețineți că înălțimea este ajustată cu -2 pentru a lua în considerare valorile antetului și titlului din celulele B4 și B2. Avantajul acestei abordări este simplitatea gamelor din COUNTA. Dezavantajul provine din coloanele și rândurile pline de dimensiuni uriașe - trebuie să aveți grijă să preveniți valorile errante în afara intervalului, deoarece acestea pot elimina cu ușurință numărul.

Determinarea ultimului rând

Există mai multe moduri de a determina ultimul rând (ultima poziție relativă) dintr-un set de date, în funcție de structura și conținutul datelor din foaia de lucru: