Uw Excel-gegevens veranderen regelmatig, dus het is handig om een dynamisch gedefinieerd bereik te maken dat automatisch wordt vergroot en verkleind tot de grootte van uw gegevensbereik. Laten we eens kijken hoe.
Door een dynamisch gedefinieerd bereik te gebruiken, hoeft u de bereiken van uw formules, grafieken en draaitabellen niet handmatig te bewerken wanneer gegevens veranderen. Dit zal automatisch gebeuren.
Er worden twee formules gebruikt om dynamische bereiken te creëren: OFFSET en INDEX. Dit artikel zal zich richten op het gebruik van de INDEX-functie, aangezien dit een efficiëntere benadering is. OFFSET is een vluchtige functie en kan grote spreadsheets vertragen.
Maak een dynamisch gedefinieerd bereik in Excel
Voor ons eerste voorbeeld hebben we de onderstaande lijst met gegevens met één kolom.
We hebben dit nodig om dynamisch te zijn, zodat als er meer landen worden toegevoegd of verwijderd, het bereik automatisch wordt bijgewerkt.
Voor dit voorbeeld willen we de koptekstcel vermijden. Daarom willen we het bereik $ A $ 2: $ A $ 6, maar dynamisch. Doe dit door op Formules> Naam definiëren te klikken.
Typ “landen” in het veld “Naam” en voer vervolgens de onderstaande formule in het veld “Verwijst naar” in.
=$A$2:INDEX($A:$A,COUNTA($A:$A))
Het typen van deze vergelijking in een spreadsheetcel en vervolgens kopiëren naar het vak Nieuwe naam is soms sneller en gemakkelijker.
Hoe werkt dit?
Het eerste deel van de formule specificeert de startcel van het bereik (A2 in ons geval) en vervolgens volgt de bereikoperator (:).
=$A$2:
Het gebruik van de bereikoperator dwingt de functie INDEX om een bereik te retourneren in plaats van de waarde van een cel. De INDEX-functie wordt dan gebruikt met de COUNTA-functie. COUNTA telt het aantal niet-lege cellen in kolom A (zes in ons geval).
INDEX($A:$A,COUNTA($A:$A))
Deze formule vraagt de functie INDEX om het bereik van de laatste niet-lege cel in kolom A ($ A $ 6) te retourneren.
Het uiteindelijke resultaat is $ A $ 2: $ A $ 6, en vanwege de COUNTA-functie is het dynamisch, omdat het de laatste rij zal vinden. U kunt deze door “landen” gedefinieerde naam nu gebruiken in een gegevensvalidatieregel, formule, diagram of waar we ook maar moeten verwijzen naar de namen van alle landen.
Creëer een tweeweg dynamisch gedefinieerd bereik
Het eerste voorbeeld was alleen dynamisch in hoogte. Met een kleine wijziging en een andere COUNTA-functie kunt u echter een bereik creëren dat zowel qua hoogte als breedte dynamisch is.
In dit voorbeeld gebruiken we de onderstaande gegevens.
Deze keer zullen we een dynamisch gedefinieerd bereik maken, inclusief de headers. Klik op Formules> Naam definiëren.
Typ “verkoop” in het veld “Naam” en voer de onderstaande formule in het veld “Verwijst naar” in.
=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
Deze formule gebruikt $ A $ 1 als de startcel. De INDEX-functie gebruikt vervolgens een bereik van het volledige werkblad ($ 1: $ 1048576) om in te kijken en van terug te keren.
Een van de COUNTA-functies wordt gebruikt om de niet-lege rijen te tellen en een andere wordt gebruikt voor de niet-lege kolommen, waardoor deze in beide richtingen dynamisch wordt. Hoewel deze formule begon vanaf A1, had u elke startcel kunnen specificeren.
U kunt deze gedefinieerde naam (verkoop) nu gebruiken in een formule of als een reeks diagramgegevens om ze dynamisch te maken.