Använd INDIRECT-funktionen i Excel-formler för att ändra intervallet av cellreferenser som används i en formel utan att behöva redigera formeln själv. Detta säkerställer att samma celler används, även när kalkylbladet ändras.
Notera: Instruktioner i denna artikel gäller Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel för Mac och Excel Online.
Använd ett dynamiskt område med COUNTIF - INDIRECT-formeln
INDIRECT-funktionen kan användas med ett antal funktioner som accepterar en cellreferens som ett argument, till exempel SUM- och COUNTIF-funktionerna.
Genom att använda INDIRECT som argument för COUNTIF skapas ett dynamiskt intervall av cellreferenser som kan räknas av funktionen om cellvärdena uppfyller kriterier. Det gör detta genom att vrida textdata, ibland hänvisad till som en textsträng, till en cellreferens.
Detta exempel är baserat på de data som visas i bilden ovan. Den COUNTIF - INDIRECT-formel som skapades i handledningen är:
= COUNTIF (INDIREKT (E1 & ":" & E2), "> 10")
I denna formel innehåller argumentet för INDIRECT-funktionen:
- Cellreferensema E och E2, som innehåller textdata D1 och D6.
- Serien operatören, tjocktarmen (:) omgiven av dubbla citattecken (' ') som vrider kolon i en textsträng.
- Två ampersands (&) som används för att sammanfoga eller sammanfoga kolon med cellreferenserna E1 och E2.
Resultatet är att INDIRECT konverterar textsträngen D1: D6 till en cellreferens och skickar den vidare till COUNTIF-funktionen som ska räknas om de refererade cellerna är större än 10.
INDIRECT-funktionen accepterar alla textinmatningar. Dessa kan vara celler i arbetsbladet som innehåller text- eller textcellreferenser som skrivs direkt in i funktionen.
Dynamiskt Ändra formelens omfång
Kom ihåg att målet är att skapa en formel med ett dynamiskt intervall. Ett dynamiskt intervall kan ändras utan att själva formeln ändras.
Genom att ändra textdata i cellerna E1 och E2, från D1 och D6 till D3 och D7, kan intervallet som uppnås med funktionen enkelt ändras från D1: D6 till D3: D7. Detta eliminerar behovet att direkt redigera formeln i cell G1.
COUNTIF-funktionen i det här exemplet räknar bara med celler som innehåller siffror om de är större än 10. Även om fyra av de fem cellerna i intervallet D1: D6 innehåller data innehåller endast tre celler siffror. Celler som är tomma eller innehåller textdata ignoreras av funktionen.
Räkna text med COUNTIF
COUNTIF-funktionen är inte begränsad till att räkna numeriska data. Det räknar också celler som innehåller text genom att kontrollera om de matchar en viss text.
För att göra detta matas följande formel i cell G2:
= COUNTIF (INDIREKT (E1 & ":" & E2), "två")
I denna formel refererar INDIRECT-funktionen cellerna B1 till B6. COUNTIF-funktionen anger antalet celler som har textvärdet två i dem.
I detta fall är resultatet 1.
COUNTA, COUNTBLANK och INDIRECT
Två andra Excel-count-funktioner är COUNTA, vilket räknas med celler som innehåller någon typ av data medan du ignorerar bara tomma eller tomma celler och COUNTBLANK, som endast räknas tomma eller tomma celler i ett intervall.
Eftersom båda dessa funktioner har liknande syntax till COUNTIF-funktionen kan de ersättas med ovanstående exempel med INDIRECT för att skapa följande formler:
= ANTALV (INDIREKT (E1 & ":" & E2))= ANTAL.TOMMA (INDIREKT (E1 & ":" & E2)
För intervallet D1: D6 returnerar COUNTA ett svar på 4, eftersom fyra av de fem cellerna innehåller data. COUNTBLANK returnerar ett svar på 1 eftersom det bara finns en tom cell i intervallet. Fördelen med att använda INDIRECT-funktionen i alla dessa formler är att nya celler kan införas var som helst inom intervallet. Området växlar dynamiskt inuti de olika funktionerna, och resultaten uppdateras därefter. Utan INDIRECT-funktionen skulle varje funktion behöva redigeras för att inkludera alla 7 celler, inklusive den nya. Fördelarna med INDIRECT-funktionen är att textvärden kan infogas som cellreferenser och att det dynamiskt uppdaterar intervaller när ditt kalkylblad ändras. Detta gör det mycket enklare för kalkylarket, särskilt för mycket stora kalkylblad. Varför använd en INDIRECT-funktion?
