Excel formula: Dinamiski nosauktais diapazons ar OFFSET -

Vispārēja formula

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

Kopsavilkums

Viens veids, kā ar formulu izveidot dinamisku nosaukto diapazonu, ir izmantot funkciju OFFSET kopā ar funkciju COUNTA. Dinamiskos diapazonus sauc arī par paplašinošiem diapazoniem - tie automātiski paplašinās un saraujas, lai uzņemtu jaunus vai dzēstus datus.

Piezīme: OFFSET ir nepastāvīga funkcija, kas nozīmē, ka tā tiek pārrēķināta ar katru izmaiņu darblapā. Izmantojot mūsdienīgu mašīnu un mazāku datu kopu, tam nevajadzētu radīt problēmas, taču jūs varat redzēt lēnāku veiktspēju lielās datu kopās. Tādā gadījumā apsveriet iespēju izveidot dinamisku nosaukto diapazonu ar funkciju INDEX.

Parādītajā piemērā dinamiskā diapazona formula ir šāda:

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

Paskaidrojums

Šī formula izmanto funkciju OFFSET, lai izveidotu diapazonu, kas paplašinās un saraujas, pielāgojot augstumu un platumu, pamatojoties uz tukšo šūnu skaitu.

OFFSET pirmais arguments apzīmē datu pirmo šūnu (izcelsmi), kas šajā gadījumā ir šūna B5. Nākamie divi argumenti ir rindu un kolonnu nobīdes, un tie tiek piegādāti kā nulle.

Pēdējie divi argumenti norāda augstumu un platumu. Augstums un platums tiek ģenerēti, izmantojot COUNTA, kas padara iegūto atsauci dinamisku.

Augstumam mēs izmantojam funkciju COUNTA, lai skaitītu tukšas vērtības diapazonā B5: B100. Datos netiek pieņemtas tukšas vērtības un vērtības, kas pārsniedz B100. COUNTA atgriež 6.

Platumam mēs izmantojam funkciju COUNTA, lai skaitītu tukšas vērtības diapazonā B5: Z5. Tiek pieņemts, ka nav galvenes šūnu un virsrakstu, kas pārsniedz Z5. COUNTA atgriež 6.

Šajā brīdī formula izskatās šādi:

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

Izmantojot šo informāciju, OFFSET atgriež atsauci uz B5: G10, kas atbilst diapazona 6 rindu augstumam ar 6 kolonnām pāri.

Piezīme. Augstumam un platumam izmantotie diapazoni ir jāpielāgo, lai tie atbilstu darblapas izkārtojumam.

Variācija ar pilnu sleju / rindu atsaucēm

Augstumam un platumam varat izmantot arī pilnu kolonnu un rindu atsauces:

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

Ņemiet vērā, ka augstums tiek noregulēts ar -2, lai ņemtu vērā galvenes un nosaukuma vērtības šūnās B4 un B2. Šīs pieejas priekšrocība ir diapazonu vienkāršība COUNTA iekšpusē. Trūkums ir milzīgo pilna izmēra kolonnu un rindu lielums - jārūpējas, lai nepieļautu kļūdainas vērtības ārpus diapazona, jo tās var viegli izmest skaitīšanu.