Si të përdorni një gamë dinamike në Excel me COUNTIF dhe INDIRECT

2024 Autor: Abigail Brown | [email protected]. E modifikuara e fundit: 2023-12-17 07:01

Çfarë duhet të dini

• Funksioni INDIRECT ndryshon gamën e referencave të qelizave në një formulë pa modifikuar formulën.
• Përdor INDIRECT si argument për COUNTIF për të krijuar një gamë dinamike qelizash që plotësojnë kriteret e specifikuara.
• Kriteret vendosen nga funksioni INDIRECT dhe numërohen vetëm qelizat që plotësojnë kriteret.

Ky artikull shpjegon se si të përdoret funksioni INDIRECT në formulat e Excel për të ndryshuar gamën e referencave të qelizave të përdorura në një formulë pa pasur nevojë të modifikoni vetë formulën. Kjo siguron që të njëjtat qeliza të përdoren, edhe kur tabela juaj ndryshon. Informacioni vlen për Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel për Mac dhe Excel Online.

Përdor një gamë dinamike me COUNTIF - Formula INDIRECT

Funksioni INDIRECT mund të përdoret me një numër funksionesh që pranojnë një referencë qelize si argument, siç janë funksionet SUM dhe COUNTIF.

Përdorimi i INDIRECT si argument për COUNTIF krijon një gamë dinamike referencash qelizash që mund të numërohen nga funksioni nëse vlerat e qelizave plotësojnë një kriter. Ai e bën këtë duke i kthyer të dhënat e tekstit, nganjëherë të referuara si një varg teksti, në një referencë qelize.

Ky shembull bazohet në të dhënat e paraqitura në imazhin e mësipërm. Formula COUNTIF - INDIRECT e krijuar në tutorial është:

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

Në këtë formulë, argumenti për funksionin INDIRECT përmban:

• Referencat e qelizave E1 dhe E2, të cilat përmbajnë të dhënat e tekstit D1 dhe D6.
• Operatori i diapazonit, dy pika (:) i rrethuar me thonjëza të dyfishta (" ") që e kthen dy pika në tekst varg.
• Dy ampersand (&) që përdoren për të lidhur ose bashkuar dy pika me referencat e qelizave E1 dhe E2.

Rezultati është se INDIRECT konverton vargun e tekstit D1:D6 në një referencë qelize dhe e kalon atë në funksionin COUNTIF për t'u numëruar nëse qelizat e referuara janë më të mëdha se 10.

Funksioni INDIRECT pranon çdo hyrje teksti. Këto mund të jenë qeliza në fletën e punës që përmbajnë tekst ose referenca të qelizave tekstuale që futen drejtpërdrejt në funksion.

Ndrysho në mënyrë dinamike diapazonin e Formulës

Mos harroni, qëllimi është të krijoni një formulë me një gamë dinamike. Një interval dinamik mund të ndryshohet pa modifikuar vetë formulën.

Duke ndryshuar të dhënat e tekstit të vendosura në qelizat E1 dhe E2, nga D1 dhe D6 në D3 dhe D7, diapazoni i përmbledhur nga funksioni mund të ndryshohet lehtësisht nga D1:D6 në D3:D7. Kjo eliminon nevojën për të redaktuar drejtpërdrejt formulën në qelizën G1.

Funksioni COUNTIF në këtë shembull numëron qelizat që përmbajnë numra vetëm nëse janë më të mëdhenj se 10. Edhe pse katër nga pesë qelizat në intervalin D1:D6 përmbajnë të dhëna, vetëm tre qeliza përmbajnë numra. Qelizat që janë bosh ose përmbajnë të dhëna teksti injorohen nga funksioni.

Numërimi i tekstit me COUNTIF

Funksioni COUNTIF nuk është i kufizuar në numërimin e të dhënave numerike. Ai numëron gjithashtu qelizat që përmbajnë tekst duke kontrolluar nëse ato përputhen me një tekst të caktuar.

Për ta bërë këtë, formula e mëposhtme futet në qelizën G2:

=COUNTIF(INDIREKT(E1&":"&E2), "dy")

Në këtë formulë, funksioni INDIRECT referon qelizat B1 në B6. Funksioni COUNTIF arrin në total numrin e qelizave që kanë vlerën e tekstit dy në to.

Në këtë rast, rezultati është 1.

COUNTA, COUNTBLANK dhe INDIRECT

Dy funksione të tjera të numërimit të Excel janë COUNTA, e cila numëron qelizat që përmbajnë çdo lloj të dhënash duke injoruar vetëm qelizat bosh ose boshe, dhe COUNTBLANK, e cila numëron vetëm qelizat bosh ose boshe në një interval.

Meqenëse të dy këta funksione kanë sintaksë të ngjashme me funksionin COUNTIF, ato mund të zëvendësohen në shembullin e mësipërm me INDIRECT për të krijuar formulat e mëposhtme:

=COUNTA(INDIREKT(E1&":"&E2))
=COUNTBLANK(INDIREKT(E1&":"&E2)
Për diapazonin D1:D6, COUNTA kthen një përgjigje prej 4, pasi katër nga pesë qelizat përmbajnë të dhëna. COUNTBLANK kthen një përgjigje prej 1 pasi ka vetëm një qelizë bosh në interval.

Pse të përdorni një funksion INDIRECT?
Përfitimi i përdorimit të funksionit INDIRECT në të gjitha këto formula është se qelizat e reja mund të futen kudo në interval.
Diapazoni zhvendoset në mënyrë dinamike brenda funksioneve të ndryshme dhe rezultatet përditësohen në përputhje me rrethanat.





Pa funksionin INDIRECT, çdo funksion do të duhej të modifikohej për të përfshirë të 7 qelizat, duke përfshirë edhe atë të re.
Përfitimet e funksionit INDIRECT janë se vlerat e tekstit mund të futen si referenca qelizash dhe se ai përditëson në mënyrë dinamike diapazonin sa herë që ndryshon fleta juaj e llogaritjes.
Kjo e bën mirëmbajtjen e përgjithshme të fletëllogaritjes shumë më të lehtë, veçanërisht për fletëllogaritëse shumë të mëdha.