Gebruik de functie INDIRECT in Excel-formules om het bereik van celverwijzingen in een formule te wijzigen zonder de formule zelf te hoeven bewerken. Dit zorgt ervoor dat dezelfde cellen worden gebruikt, zelfs wanneer uw spreadsheet verandert.
Notitie: Instructies in dit artikel zijn van toepassing op Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel voor Mac en Excel Online.
Gebruik een dynamisch bereik met de COUNTIF - INDIRECT-formule
De INDIRECTE functie kan worden gebruikt met een aantal functies die een celverwijzing accepteren als een argument, zoals de functies SOM en AANTAL.ALS.
Als u INDIRECT gebruikt als het argument voor COUNTIF, wordt een dynamisch bereik van celverwijzingen gemaakt dat kan worden geteld door de functie als de celwaarden voldoen aan een criterium. Dit gebeurt door tekstgegevens, soms ook wel een tekstreeks genoemd, om te zetten in een celverwijzing.
Dit voorbeeld is gebaseerd op de gegevens in de bovenstaande afbeelding. De COUNTIF - INDIRECT-formule die in de zelfstudie is gemaakt, is:
= AANTAL.ALS (INDIRECT (E1 & ":" & E2), "> 10")
In deze formule bevat het argument voor de INDIRECTE functie:
- De cel verwijst naar E1 en E2, die de tekstgegevens D1 en D6 bevatten.
- De bereikoperator, de dubbele punt (:) omringd door dubbele aanhalingstekens (' ') die de dubbele punt in een tekstreeks veranderen.
- Twee ampersands (&) die worden gebruikt om de dubbele punt samen te voegen of samen te voegen met de celverwijzingen E1 en E2.
Het resultaat is dat INDIRECT de tekststring D1: D6 converteert naar een celverwijzing en deze doorgeeft aan de AANTAL.ALS-functie om te worden geteld als de cellen waarnaar verwezen wordt, groter zijn dan 10.
De INDIRECTE functie accepteert tekstinvoer. Dit kunnen cellen in het werkblad zijn die tekst- of tekstcelverwijzingen bevatten die rechtstreeks in de functie worden ingevoerd.
Dynamisch de reeks van de formule wijzigen
Onthoud dat het doel is om een formule met een dynamisch bereik te maken. Een dynamisch bereik kan worden gewijzigd zonder de formule zelf te bewerken.
Door de tekstgegevens in cellen E1 en E2, van D1 en D6 tot D3 en D7, te wijzigen, kan het bereik van de functie eenvoudig worden gewijzigd van D1: D6 naar D3: D7. Dit elimineert de noodzaak om de formule direct in cel G1 te bewerken.
De functie AANTAL.ALS in dit voorbeeld telt alleen cellen met getallen als deze groter zijn dan 10. Hoewel vier van de vijf cellen in het bereik van D1: D6 gegevens bevatten, bevatten slechts drie cellen getallen. Cellen die leeg zijn of tekstgegevens bevatten, worden door de functie genegeerd.
Tekst tellen met AANTAL.ALS
De AANTAL.ALS-functie is niet beperkt tot het tellen van numerieke gegevens. Het telt ook cellen die tekst bevatten door te controleren of ze overeenkomen met een bepaalde tekst.
Om dit te doen, wordt de volgende formule ingevoerd in cel G2:
= AANTAL.ALS (INDIRECT (E1 & "" & E2), "twee")
In deze formule verwijst de INDIRECTE functie naar cellen B1 tot B6. De AANTAL.ALS-functie is het aantal cellen dat de tekstwaarde heeft twee in hen.
In dit geval is het resultaat 1.
COUNTA, COUNTBLANK en INDIRECT
Twee andere Excel-telfuncties zijn COUNTA, waarmee cellen worden geteld die elk type gegevens bevatten, terwijl alleen lege of lege cellen worden genegeerd, en COUNTBLANK, dat alleen lege of lege cellen in een bereik meetelt.
Aangezien beide functies dezelfde syntax hebben als de AANTAL.ALS-functie, kunnen ze in het bovenstaande voorbeeld worden vervangen door INDIRECT om de volgende formules te maken:
= COUNTA (INDIRECT (E1 & ":" & E2))= AANTAL.LEGE.CELLEN (INDIRECT (E1 & ":" & E2)
Voor het bereik D1: D6 geeft AANTAL een antwoord van 4, omdat vier van de vijf cellen gegevens bevatten. COUNTBLANK retourneert een antwoord van 1 omdat er slechts één lege cel in het bereik is. Het voordeel van het gebruik van de INDIRECTE functie in al deze formules is dat nieuwe cellen overal in het bereik kunnen worden ingevoegd. Het bereik verschuift dynamisch binnen de verschillende functies en de resultaten worden dienovereenkomstig bijgewerkt. Zonder de INDIRECTE functie, zou elke functie moeten worden bewerkt om alle 7 cellen, inclusief de nieuwe, op te nemen. De voordelen van de INDIRECTE functie zijn dat tekstwaarden kunnen worden ingevoegd als celverwijzingen en dat dynamisch reeksen worden bijgewerkt wanneer uw werkblad wordt gewijzigd. Dit maakt het algemene onderhoud van spreadsheets veel eenvoudiger, vooral voor zeer grote spreadsheets. Waarom een INDIRECTE functie gebruiken?
