Cum se utilizează un interval dinamic în Excel cu COUNTIF și INDIRECT

Cuprins:

2024 Autor: Abigail Brown | [email protected]. Modificat ultima dată: 2023-12-17 06:59

Ce trebuie să știți

Funcția INDIRECT modifică intervalul de referințe de celule dintr-o formulă fără a edita formula.
Folosiți INDIRECT ca argument pentru COUNTIF pentru a crea un interval dinamic de celule care îndeplinesc criteriile specificate.
Criteriile sunt stabilite de funcția INDIRECT și sunt contorizate numai celulele care îndeplinesc criteriile.

Acest articol explică cum să utilizați funcția INDIRECT în formulele Excel pentru a modifica intervalul de referințe de celule utilizate într-o formulă fără a fi nevoie să editați formula în sine. Acest lucru asigură că aceleași celule sunt utilizate, chiar și atunci când foaia de calcul se modifică. Informațiile se aplică pentru Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel pentru Mac și Excel Online.

Utilizați un interval dinamic cu formula COUNTIF - INDIRECT

Funcția INDIRECT poate fi utilizată cu un număr de funcții care acceptă o referință de celulă ca argument, cum ar fi funcțiile SUM și COUNTIF.

Folosind INDIRECT ca argument pentru COUNTIF creează un interval dinamic de referințe de celule care pot fi numărate de funcție dacă valorile celulei îndeplinesc un criteriu. Face acest lucru transformând datele text, uneori denumite șir de text, într-o referință de celulă.

Acest exemplu se bazează pe datele prezentate în imaginea de mai sus. Formula COUNTIF - INDIRECT creată în tutorial este:

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

În această formulă, argumentul pentru funcția INDIRECT conține:

Referențele celulei E1 și E2, care conțin datele text D1 și D6.
Operatorul intervalului, două puncte (:) înconjurate de ghilimele duble (" ") care transformă două puncte într-un text șir.
Două ampersand (&) care sunt folosite pentru a concatena sau a uni două puncte cu referințele de celule E1 și E2.

Rezultatul este că INDIRECT convertește șirul de text D1:D6 într-o referință de celulă și îl transmite funcției COUNTIF pentru a fi numărat dacă celulele la care se face referire sunt mai mari de 10.

Funcția INDIRECT acceptă orice introducere de text. Acestea pot fi celule din foaia de lucru care conțin referințe text sau celule text care sunt introduse direct în funcție.

Schimbați în mod dinamic intervalul formulei

Nu uitați, scopul este de a crea o formulă cu un interval dinamic. Un interval dinamic poate fi modificat fără a edita formula în sine.

Prin modificarea datelor text situate în celulele E1 și E2, de la D1 și D6 la D3 și D7, intervalul totalizat de funcție poate fi schimbat cu ușurință de la D1:D6 la D3:D7. Acest lucru elimină necesitatea de a edita direct formula din celula G1.

Funcția COUNTIF din acest exemplu numără celulele care conțin numere numai dacă sunt mai mari de 10. Chiar dacă patru din cele cinci celule din intervalul D1:D6 conțin date, numai trei celule conțin numere. Celulele care sunt goale sau care conțin date text sunt ignorate de funcție.

Numărarea textului cu COUNTIF

Funcția COUNTIF nu se limitează la numărarea datelor numerice. De asemenea, numără celulele care conțin text verificând dacă se potrivesc cu un anumit text.

Pentru a face acest lucru, se introduce următoarea formulă în celula G2:

=COUNTIF(INDIRECT(E1&":"&E2), "două")

În această formulă, funcția INDIRECT face referire la celulele B1 la B6. Funcția COUNTIF totalizează numărul de celule care au valoarea textului doi în ele.

În acest caz, rezultatul este 1.

COUNTA, COUNTBLANK și INDIRECT

Alte două funcții de numărare Excel sunt COUNTA, care numără celulele care conțin orice tip de date, ignorând doar celulele goale sau goale, și COUNTBLANK, care numără numai celulele goale sau goale dintr-un interval.

Deoarece ambele aceste 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(INDIRECT(E1&":"&E2))

=COUNTBLANK(INDIRECT(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ă goală în interval.

De ce să folosiți o funcție INDIRECTĂ?

Beneficiul utilizării funcției INDIRECT în toate aceste formule este că celule noi pot fi inserate oriunde în interval.

Intervalul se schimbă dinamic în interiorul diferitelor funcții, iar rezultatele se actualizează în consecință.

Fără funcția INDIRECT, fiecare funcție ar trebui editată pentru a include toate cele 7 celule, inclusiv pe cea nouă.

Avantajele funcției INDIRECT sunt că valorile text pot fi inserate ca referințe de celule și că actualizează dinamic intervalele ori de câte ori foaia de calcul se modifică.

Acest lucru face întreținerea generală a foilor de calcul mult mai ușoară, în special pentru foile de calcul foarte mari.