Excel-formel: Dynamiskt namnområde med INDEX -

Innehållsförteckning

Generisk formel

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

Sammanfattning

Ett sätt att skapa ett dynamiskt namnområde i Excel är att använda INDEX-funktionen. I det visade exemplet definieras det namngivna intervallet "data" med följande formel:

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

som löser sig till intervallet $ A $ 2: $ A $ 10.

Obs: denna formel är avsedd att definiera ett namngivet intervall som kan användas i andra formler.

Förklaring

Denna sida visar ett exempel på ett dynamiskt namngivet intervall skapat med INDEX-funktionen tillsammans med COUNTA-funktionen. Dynamiska namngivna områden utvidgas automatiskt och dras in när data läggs till eller tas bort. De är ett alternativ till att använda en Excel-tabell, som också ändrar storlek när data läggs till eller tas bort.

INDEX-funktionen returnerar värdet vid en given position i ett intervall eller matris. Du kan använda INDEX för att hämta enskilda värden eller hela rader och kolumner i ett intervall. Vad som gör INDEX särskilt användbart för dynamiska namngivna områden är att det faktiskt returnerar en referens. Det betyder att du kan använda INDEX för att konstruera en blandad referens som $ A $ 1: A100.

I det visade exemplet definieras det namngivna intervallet "data" med följande formel:

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

som löser sig till intervallet $ A $ 2: $ A $ 10.

Hur de här formlerna fungerar

Observera först att denna formel består av två delar som sitter på vardera sidan av områdesoperatören (:). Till vänster har vi startreferensen för intervallet, hårdkodat som:

$A$2

Till höger är slutreferensen för intervallet, skapat med INDEX så här:

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

Här matar vi INDEX hela kolumn A för matrisen och använder sedan COUNTA-funktionen för att räkna ut den "sista raden" i intervallet. COUNTA fungerar bra här eftersom det finns 10 värden i kolumn A, inklusive en rubrikrad. COUNTA returnerar därför 10, vilket går direkt till INDEX som radnummer. INDEX returnerar sedan en referens till $ A $ 10, den senast använda raden i intervallet:

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

Så det slutliga resultatet av formeln är detta intervall:

$A$2:$A$10

Ett tvådimensionellt intervall

Ovanstående exempel fungerar för ett endimensionellt intervall. För att skapa ett tvådimensionellt dynamiskt område där antalet kolumner också är dynamiska kan du använda samma tillvägagångssätt, utökat så här:

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

Som tidigare används COUNTA för att räkna ut "lastrow", och vi använder COUNTA igen för att få "lastcolumn". Dessa levereras till index som radnummer respektive kolumnnummer.

För matrisen levererar vi dock hela kalkylbladet, inmatat som alla 1048576 rader, vilket gör att INDEX kan returnera en referens i ett 2D-utrymme.

Obs! Excel 2003 stöder endast 65535 rader.

Fastställande av sista raden

Det finns flera sätt att bestämma den sista raden (sista relativa positionen) i en uppsättning data, beroende på strukturen och innehållet i data i kalkylbladet: