Excel Trick to Sum een aantal cellen met een variabel bereik

In Excel kunt u een aantal cellen optellen met een variabel bereik met de INDIRECTE functie. De functie INDIRECT werkt het cellenbereik waarnaar u hebt verwezen automatisch bij zonder de formule zelf handmatig te bewerken. U kunt de INDIRECTE functie gebruiken met een willekeurig aantal Excel-functies, maar de meest voorkomende (en nuttig) is wanneer u de functie SOM gebruikt.

Notitie: De informatie in het artikel is van toepassing op Excel-versies 2019, 2016, 2013, 2010 en Excel voor Mac.

01 van 05

Een aantal cellen optellen met een variabel bereik

U kunt de INDIRECTE functie insluiten als een argument binnen de SOM-functie om een variabel bereik van celverwijzingen te creëren voor de toe te voegen SOM-functie. De functie INDIRECT doet dit door indirect naar het cellenbereik te verwijzen, via een tussenliggende celverwijzing.

Het formaat van beide samen gebruikte functies ziet er als volgt uit:

= SOM (INDIRECT ( "D1": D4)

Hiermee vergrendelt u het bereik dat u wilt starten op D1 en kunt u D4 wijzigen als u cellen in de kolom D invoegt of verwijdert.

U kunt ook andere cellen in de spreadsheet gebruiken om de celverwijzingen te wijzigen. Als u bijvoorbeeld E1 gebruikt om naar de eerste cel van het bereik te verwijzen en E2 om naar de laatste cel van het bereik te verwijzen, ziet de formule er als volgt uit:

= SOM (INDIRECT ("D" & E1 & ": D" & E2))

Door de getallen in cellen E1 en E2 te wijzigen, kunt u het bereik in de formule wijzigen zonder de formule handmatig te hoeven bewerken.

02 van 05

Probeer de SOM en INDIRECTE functies

Maak een spreadsheet om de SUM- en INDIRECTE functies zelf te testen. Begin met het maken van een lege spreadsheet en voer de volgende gegevens in de kolommen D en E in:

Cel Gegevens D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4

Maak vervolgens de formule in cel F1. Hier is hoe:

Selecteer cel F1. Dit is waar het resultaat van dit voorbeeld wordt weergegeven.

kiezen formules.

Kiezen Math & Trig om de vervolgkeuzelijst met functies te openen.

kiezenSOM in de lijst om het dialoogvenster SUM Functieargumenten te openen.

03 of 05
Nest de INDIRECTE functie in de SOM-functie

Voer vervolgens de functie INDIRECT in de SUM-functie in met behulp van dit dialoogvenster.

In de Nummer 1 veld, voer de volgende INDIRECTE functie in:
INDIRECT ( "D" & E1 & ": D" & E2)

kiezen OK om de functie te voltooien en het dialoogvenster te sluiten.

Het cijfer 50 verschijnt in cel F1. Dit is de som van cellen D1 tot D4.

Wanneer u cel F1 selecteert, wordt in het formuleveld het volgende weergegeven:
= SOM (INDIRECT ( "D" & E1 & ": D" & E2))

Wanneer u begrijpt hoe de SUM en geneste INDIRECTE functies moeten worden geformatteerd, kunt u de volledige functie die in stap 4 is weergegeven rechtstreeks in de resultaatcel typen (in dit voorbeeld, cel F1).
04 van 05
Bouw het argument voor de INDIRECTE functie

In het bovenstaande voorbeeld bevinden de verwijzingen naar cellen E1 en E2 zich buiten de aanhalingstekens. Dit, gecombineerd met de & aaneenschakelingssymbool, neemt alles op wat zich binnen de cellen E1 en E2 bevindt in de formule zelf.

Hier is hoe de elementen van de formule werken:

& wordt gebruikt om tekstgegevens samen te voegen (in dit geval de letter D) met een celverwijzing (E1 en E2) die een getalswaarde bevat.

' ' moet tekstgegevens binnen de celverwijzingen omringen (in dit voorbeeld de letter D).

: is een scheidingsteken tussen de eerste cel en de laatste cel in het SUM-bereik.

In het bovenstaande voorbeeld wordt het beginpunt van het bereik bepaald door de tekens: "D" & E1.

Het eindpunt van het bereik wordt bepaald door de tekens: ": D" & E2

De dubbele punt moet worden opgenomen in de aanhalingstekens.

Het derde ampersand in het midden wordt gebruikt om de twee segmenten samen te voegen tot één argument:

"D" & E1 & ": D" & E2 05 of 05
Het SUM-functie variabel bereik

De INDIRECTE functie genest in de SUM-functie maakt het gemakkelijk om het begin en het einde van het bereik van de SOM-functie te veranderen, zonder de functie zelf te hoeven bewerken.

Gebruik de aaneenschakelingstechniek in stap 3 en gebruik de getallen die zijn opgeslagen in cellen E1 en E2 in het werkblad om het celbereik voor de functie SOM te wijzigen.

Test dit in uw eigen spreadsheet door cellen E1 en E2 aan te passen en te kijken hoe de resultaten van de SOM-functie veranderen.

Selecteer cel E1.

Typ het nummer 3.

druk op invoeren.

Selecteer cel E2.

Typ het nummer 6.

druk op invoeren.

Het antwoord in cel F1 verandert in 90. Dit is de som van de getallen in cellen D3 tot D6.

Om de INDIRECTE functie in actie te zien, plaatst u een nieuwe cel in cel D3. Dit verschuift alle andere cellen naar beneden. De nieuwe som is het totaal van cellen D3 tot D7. Met de functie INDIRECTE wordt het hele bereik dynamisch gewijzigd zodat het de volledige lijst bevat die u aanvankelijk hebt ingevoerd, plus de nieuwe cel die u hebt ingevoegd.

De # VERF! Foutwaarde

De # VERF! foutwaarde verschijnt in cel F1 als de INDIRECTE functie:

Bevat een ongeldige celverwijzing.

Bevat een externe verwijzing naar een ander werkblad dat niet bestaat.

Verwijst naar een celbereik buiten de limieten van het werkblad.

Verwijst naar een cel die leeg is.