Utilizați funcția INDIRECT în formulele Excel pentru a modifica intervalul referințelor de celule utilizate într-o formulă fără a fi nevoie să editați formula însăși. Acest lucru asigură utilizarea acelorași celule, chiar și atunci când foaia de calcul se modifică.
Notă: Instrucțiunile din acest articol se aplică Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel pentru Mac și Excel Online.
Utilizați o gamă dinamică cu formula COUNTIF - INDIRECT
Funcția INDIRECT poate fi utilizată cu un număr de funcții care acceptă o referință de celule ca argument, cum ar fi funcțiile SUM și COUNTIF.
Folosind INDIRECT ca argument pentru COUNTIF creează o gamă dinamică de referințe de celule care pot fi numărate de către funcție dacă valorile celulei îndeplinesc un criteriu. Aceasta face acest lucru transformând datele de text, uneori denumite șir de text, într-o referință de celule.
Acest exemplu se bazează pe datele afișate în imaginea de mai sus. Formula "COUNTIF - INDIRECT" creată în tutorial este:
= COUNTIF (INDIRECTE (E1 & ":" & E2), "> 10")
În această formulă, argumentul pentru funcția INDIRECT conține:
- Referințele celulelor E1 și E2, care conțin datele text D1 și D6.
- Operatorul range, colon (:) înconjurat de ghilimele duble (' ') care transformă colonul într-un șir de text.
- Două ampersanduri (&) care se utilizează pentru a concatena sau pentru a se uni, împreună cu referințele celulare E1 și E2.
Rezultatul este că INDIRECT convertește șirul de text D1: D6 într-o referință a celulei și o transmite de-a lungul funcției COUNTIF care trebuie numărată dacă celulele referențiate sunt mai mari de 10.
Funcția INDIRECT acceptă orice intrări de text. Acestea pot fi celule din foaia de lucru care conțin referințe text sau text care sunt introduse direct în funcție.
Modificați dinamic intervalul de formule
Rețineți că obiectivul este de a crea o formulă cu o gamă dinamică. O gamă dinamică poate fi modificată fără editarea formulei în sine.
Prin schimbarea datelor de text aflate în celulele E1 și E2, de la D1 și D6 la D3 și D7, intervalul total al funcției poate fi ușor schimbat de la D1: D6 la D3: D7. Aceasta elimină necesitatea de a edita direct formula din celula G1.
Funcția COUNTIF din acest exemplu contează numai celule care conțin numere dacă sunt mai mari de 10. Chiar dacă patru din cele cinci celule din domeniul D1: D6 conțin date, numai trei celule conțin numere. Celulele care sunt goale sau conțin date de text sunt ignorate de către funcție.
Numără text cu COUNTIF
Funcția COUNTIF nu se limitează la numărarea datelor numerice. De asemenea, numără celule care conțin text prin verificarea dacă se potrivesc cu un anumit text.
Pentru aceasta, se introduce următoarea formulă în celula G2:
= COUNTIF (INDIRECTE (E1 & ":" & E2), "doi")
În această formulă, funcția INDIRECT se referă la celulele B1 la B6. Funcția COUNTIF reprezintă numărul de celule care au valoarea textului Două în ele.
În acest caz, rezultatul este 1.
COUNTA, COUNTBLANK și INDIRECT
Două alte funcții de numărare Excel sunt COUNTA, care numără celule care conțin orice tip de date în timp ce ignoră numai celule goale sau goale și COUNTBLANK, care contează numai celule goale sau goale într-un interval.
Deoarece ambele funcții au o sintaxă similară cu funcția COUNTIF, ele pot fi înlocuite în exemplul de mai sus cu INDIRECT pentru a crea următoarele formule:
= COUNTA (INDIRECTE (E1 & ":" & E2))= COUNTBLANK (INDIRECTE (E1 & ":" & E2)
Pentru intervalul D1: D6, COUNTA returnează un răspuns de 4, deoarece patru din cele cinci celule conțin date. COUNTBLANK returnează un răspuns de 1, deoarece există o singură celulă necompletată în interval. Beneficiul utilizării funcției INDIRECT în toate aceste formule este că celulele noi pot fi inserate oriunde în interval. Gama variază dinamic în interiorul diferitelor funcții, iar rezultatele se actualizează în consecință. Fără funcția INDIRECT, fiecare funcție ar trebui să fie editată pentru a include toate cele 7 celule, inclusiv cea nouă. Beneficiile funcției INDIRECT sunt că valorile de text pot fi inserate ca referințe de celule și că actualizează în mod dinamic intervale ori de câte ori se modifică foaia de calcul. Acest lucru face ca întreținerea globală a foilor de calcul să fie mult mai ușoară, mai ales pentru foile de calcul foarte mari. De ce să folosiți o funcție INDIRECT?
