Brug et dynamisk område i Excel med COUNTIF og INDIREKTE

Brug funktionen INDIREKTE i Excel-formler til at ændre rækken af ​​cellereferencer, der bruges i en formel uden at skulle redigere selve formlen. Dette sikrer, at de samme celler bruges, selv når dit regneark ændres.

Instruktionerne i denne artikel gælder for Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel til Mac og Excel Online.

Brug et dynamisk område med formlen COUNTIF – INDIRECT

Funktionen INDIREKTE kan bruges med en række funktioner, der accepterer en cellereference som et argument, såsom funktionerne SUM og ANTAL.HVIS.

Brug af INDIREKTE som argumentet for COUNTIF skaber et dynamisk område af cellereferencer, der kan tælles af funktionen, hvis celleværdierne opfylder et kriterium. Det gør det ved at omdanne tekstdata, nogle gange omtalt som en tekststreng, til en cellereference.

Dette eksempel er baseret på dataene vist på billedet ovenfor. Formlen COUNTIF – INDIRECT oprettet i selvstudiet er:

=COUNTIF(INDIRECT(E1&":"&E2),">10")

data-type=”kode”>

I denne formel indeholder argumentet for funktionen INDIREKTE:

• Cellereferencerne E1 og E2, som indeholder tekstdataene D1 og D6.
• Områdeoperatøren, kolon (:) omgivet af dobbelte anførselstegn (” “), der gør kolon til en tekststreng.
• To og-tegn (&), der bruges til at sammenkæde eller forbinde tyktarmen med cellereferencerne E1 og E2.

Resultatet er, at INDIREKTE konverterer tekststrengen D1:D6 til en cellereference og sender den videre til COUNTIF-funktionen, der skal tælles, hvis de refererede celler er større end 10.

INDIREKTE-funktionen accepterer alle tekstinput. Det kan være celler i regnearket, der indeholder tekst eller tekstcellereferencer, der indtastes direkte i funktionen.

Skift dynamisk formlens rækkevidde

Husk, målet er at skabe en formel med et dynamisk område. Et dynamisk område kan ændres uden at redigere selve formlen.

Ved at ændre tekstdataene i cellerne E1 og E2, fra D1 og D6 til D3 og D7, kan det samlede område af funktionen nemt ændres fra D1:D6 til D3:D7. Dette eliminerer behovet for direkte at redigere formlen i celle G1.

Funktionen COUNTIF i dette eksempel tæller kun celler, der indeholder tal, hvis de er større end 10. Selvom fire af de fem celler i området D1:D6 indeholder data, indeholder kun tre celler tal. Celler, der er tomme eller indeholder tekstdata, ignoreres af funktionen.

Tæller tekst med COUNTIF

COUNTIF-funktionen er ikke begrænset til at tælle numeriske data. Den tæller også celler, der indeholder tekst, ved at kontrollere, om de matcher en bestemt tekst.

For at gøre dette indtastes følgende formel i celle G2:

=COUNTIF(INDIRECT(E1&":"&E2),"two")

data-type=”kode”>

I denne formel refererer INDIREKTE-funktionen til cellerne B1 til B6. Funktionen COUNTIF summerer antallet af celler, der har tekstværdien to i dem.

I dette tilfælde er resultatet 1.

COUNTA,COUNTBLANK og INDIREKTE

To andre Excel-tællefunktioner er COUNTA, som tæller celler, der indeholder enhver type data, mens der kun ignoreres tomme eller tomme celler, og COUNTBLANK, som kun tæller tomme eller tomme celler i et område.

Da begge disse funktioner har lignende syntaks som COUNTIF-funktionen, kan de erstattes i ovenstående eksempel med INDIREKTE for at skabe følgende formler:

=COUNTA(INDIRECT(E1&":"&E2))
=COUNTBLANK(INDIRECT(E1&":"&E2)

For området D1:D6 returnerer COUNTA et svar på 4, da fire af de fem celler indeholder data. COUNTBLANK returnerer et svar på 1, da der kun er én tom celle i området.

Hvorfor bruge en INDIREKTE funktion?

Fordelen ved at bruge funktionen INDIREKTE i alle disse formler er, at nye celler kan indsættes hvor som helst i området.

Området skifter dynamisk inde i de forskellige funktioner, og resultaterne opdateres i overensstemmelse hermed.

Uden INDIREKTE-funktionen ville hver funktion skulle redigeres for at inkludere alle 7 celler, inklusive den nye.

Fordelene ved INDIREKTE-funktionen er, at tekstværdier kan indsættes som cellereferencer, og at den dynamisk opdaterer intervaller, når dit regneark ændres.

Dette gør den overordnede vedligeholdelse af regneark meget lettere, især for meget store regneark.