Формула на Excel: Динамичен именуван диапазон с OFFSET -

Обща формула

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

Обобщение

Един от начините за създаване на динамичен именен диапазон с формула е да се използва функцията OFFSET заедно с функцията COUNTA. Динамичните диапазони са известни още като разширяващи се диапазони - те автоматично се разширяват и свиват, за да побират нови или изтрити данни.

Забележка: OFFSET е променлива функция, което означава, че се преизчислява при всяка промяна в работен лист. С модерна машина и по-малък набор от данни това не би трябвало да създава проблем, но може да видите по-бавна производителност на големи набори от данни. В този случай помислете за изграждане на динамичен именен диапазон с функцията INDEX вместо това.

В показания пример формулата, използвана за динамичния диапазон, е:

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

Обяснение

Тази формула използва функцията OFFSET, за да генерира диапазон, който се разширява и свива чрез регулиране на височината и ширината въз основа на броя на непразни клетки.

Първият аргумент в OFFSET представлява първата клетка в данните (произхода), която в този случай е клетка B5. Следващите два аргумента са отмествания за редове и колони и се предоставят като нула.

Последните два аргумента представляват височина и ширина. Височината и ширината се генерират в движение с помощта на COUNTA, което прави получената референтна динамика.

За височина използваме функцията COUNTA за преброяване на непразни стойности в диапазона B5: B100. Това не предполага празни стойности в данните и стойности извън B100. COUNTA връща 6.

За ширина използваме функцията COUNTA, за да броим непразни стойности в диапазона B5: Z5. Това предполага, че няма заглавни клетки и заглавки извън Z5. COUNTA връща 6.

В този момент формулата изглежда така:

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

С тази информация OFFSET връща препратка към B5: G10, което съответства на диапазон от 6 реда височина на 6 колони.

Забележка: Диапазоните, използвани за височина и ширина, трябва да бъдат коригирани в съответствие с оформлението на работния лист.

Вариант с пълни препратки към колона / ред

Можете също да използвате пълни референции за колони и редове за височина и ширина по следния начин:

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

Имайте предвид, че височината се коригира с -2, за да се вземат предвид стойностите на заглавката и заглавието в клетки B4 и B2. Предимството на този подход е простотата на диапазоните в COUNTA. Недостатъкът е в огромните размери пълни колони и редове - трябва да се внимава да се предотвратят грешни стойности извън диапазона, тъй като те лесно могат да отхвърлят брояча.

Определяне на последния ред

Има няколко начина за определяне на последния ред (последна относителна позиция) в набор от данни, в зависимост от структурата и съдържанието на данните в работния лист: