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

Съдържание

Обща формула

=$A$1:INDEX($A:$A,lastrow)

Обобщение

Един от начините за създаване на динамичен именуван диапазон в Excel е използването на функцията INDEX. В показания пример посоченият диапазон "данни" се определя от следната формула:

=$A$2:INDEX($A:$A,COUNTA($A:$A))

което се разрешава до диапазона $ A $ 2: $ A $ 10.

Забележка: тази формула има за цел да дефинира именен диапазон, който може да се използва в други формули.

Обяснение

Тази страница показва пример за динамичен именен диапазон, създаден с функцията INDEX заедно с функцията COUNTA. Динамичните именувани диапазони автоматично се разширяват и свиват, когато данните се добавят или премахват. Те са алтернатива на използването на таблица на Excel, която също преоразмерява при добавяне или премахване на данни.

Функцията INDEX връща стойността на дадена позиция в диапазон или масив. Можете да използвате INDEX за извличане на отделни стойности или цели редове и колони в диапазон. Това, което прави INDEX особено полезно за динамични именувани диапазони, е, че всъщност връща препратка. Това означава, че можете да използвате INDEX за изграждане на смесена препратка като $ A $ 1: A100.

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

=$A$2:INDEX($A:$A,COUNTA($A:$A))

което се разрешава до диапазона $ A $ 2: $ A $ 10.

Как работи тази формула

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

$A$2

Вдясно е крайната препратка за диапазона, създадена с INDEX по следния начин:

INDEX($A:$A,COUNTA($A:$A))

Тук подаваме INDEX в цялата колона A за масива, след което използваме функцията COUNTA, за да разберем „последния ред“ в диапазона. COUNTA работи добре тук, защото в колона А има 10 стойности, включително заглавен ред. Следователно COUNTA връща 10, което влиза директно в INDEX като номер на реда. След това INDEX връща препратка към $ A $ 10, последния използван ред в диапазона:

INDEX($A:$A,10) // resolves to $A$10

И така, крайният резултат от формулата е този диапазон:

$A$2:$A$10

Двуизмерен обхват

Горният пример работи за едноизмерен диапазон. За да създадете двуизмерен динамичен диапазон, където броят на колоните също е динамичен, можете да използвате същия подход, разширен по следния начин:

=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))

Както преди, COUNTA се използва за изчисляване на "lastrow" и ние използваме COUNTA отново, за да получим "lastcolumn". Те се предоставят на индекса съответно като row_num и column_num.

За масива обаче предоставяме пълния работен лист, въведен като всички 1048576 реда, което позволява на INDEX да върне препратка в 2D пространство.

Забележка: Excel 2003 поддържа само 65535 реда.

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

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