Vispārēja formula
=$A$1:INDEX($A:$A,lastrow)
Kopsavilkums
Viens veids, kā izveidot dinamisku nosaukto diapazonu programmā Excel, ir izmantot funkciju INDEX. Parādītajā piemērā nosaukto diapazonu "dati" definē pēc šādas formulas:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
kas norisinās diapazonā $ A $ 2: $ A $ 10.
Piezīme: šī formula ir paredzēta, lai definētu nosaukto diapazonu, ko var izmantot citās formulās.
Paskaidrojums
Šajā lapā parādīts dinamiskā nosaukta diapazona piemērs, kas izveidots, izmantojot funkciju INDEX kopā ar funkciju COUNTA. Dinamiski nosauktie diapazoni automātiski paplašinās un saraujas, kad dati tiek pievienoti vai noņemti. Tie ir alternatīva Excel tabulas izmantošanai, kas mainās arī pēc datu pievienošanas vai noņemšanas.
Funkcija INDEX atgriež vērtību noteiktā diapazona vai masīva pozīcijā. Varat izmantot INDEX, lai izgūtu atsevišķas vērtības vai veselas rindas un kolonnas diapazonā. Īpaši noderīgs rādītājs dinamiskajiem nosauktajiem diapazoniem ir tas, ka tas faktiski atgriež atsauci. Tas nozīmē, ka jūs varat izmantot INDEX, lai izveidotu jauktu atsauci, piemēram, $ A $ 1: A100.
Parādītajā piemērā nosaukto diapazonu "dati" definē pēc šādas formulas:
=$A$2:INDEX($A:$A,COUNTA($A:$A))
kas norisinās diapazonā $ A $ 2: $ A $ 10.
Kā darbojas šīs formulas
Vispirms ņemiet vērā, ka šī formula sastāv no divām daļām, kas atrodas abās diapazona operatora pusēs (:). Kreisajā pusē mums ir diapazona sākuma atsauce, kas kodēta kā:
$A$2
Labajā pusē ir diapazona beigu atsauce, kas izveidota ar INDEX šādi:
INDEX($A:$A,COUNTA($A:$A))
Šeit mēs ievietojam INDEX visu masīva A kolonnu, pēc tam izmantojiet funkciju COUNTA, lai noskaidrotu diapazona "pēdējo rindu". COUNTA šeit darbojas labi, jo A slejā ir 10 vērtības, ieskaitot galvenes rindu. Tāpēc COUNTA atgriež vērtību 10, kas kā rindas numurs tiek ievadīts tieši INDEX. Pēc tam INDEX atgriež atsauci uz $ A $ 10 - pēdējo izmantoto rindu diapazonā:
INDEX($A:$A,10) // resolves to $A$10
Tātad formulas galīgais rezultāts ir šāds diapazons:
$A$2:$A$10
Divdimensiju diapazons
Iepriekš minētais piemērs darbojas viendimensiju diapazonā. Lai izveidotu divdimensiju dinamisko diapazonu, kur arī kolonnu skaits ir dinamisks, varat izmantot to pašu pieeju, kas izvērsta šādi:
=$A$2:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Tāpat kā iepriekš, COUNTA tiek izmantota, lai noskaidrotu “lastrow”, un mēs atkal izmantojam COUNTA, lai iegūtu “lastcolumn”. Tās tiek piegādātas indeksam kā attiecīgi rindas_numurs un kolonnas_numurs.
Tomēr masīvam mēs piegādājam pilnu darblapu, kas ievadīta kā visas 1048576 rindas, kas ļauj INDEX atgriezt atsauci 2D telpā.
Piezīme: Excel 2003 atbalsta tikai 65535 rindas.
Pēdējās rindas noteikšana
Pastāv vairāki veidi, kā noteikt datu kopas pēdējo rindu (pēdējo relatīvo pozīciju) atkarībā no darblapā esošo datu struktūras un satura:
- Pēdējā rinda jauktajos ar tukšajiem datiem
- Pēdējā rinda jauktajos datos bez tukšām vietām
- Pēdējā teksta datu rinda
- Pēdējā skaitlisko datu rinda
