Kuidas kasutada dünaamilist vahemikku Excelis COUNTIF-i ja INDIRECT-iga

Sisukord:

2024 Autor: Abigail Brown | [email protected]. Viimati modifitseeritud: 2023-12-17 06:45

Mida teada

Funktsioon INDIRECT muudab valemi lahtriviidete vahemikku ilma valemit muutmata.
Kasutage COUNTIF argumendina INDIRECT, et luua dünaamiline lahtrite vahemik, mis vastab määratud kriteeriumidele.
Kriteeriumid kehtestab funktsioon INDIRECT ja loendatakse ainult kriteeriumidele vastavad lahtrid.

Selles artiklis selgitatakse, kuidas kasutada Exceli valemites funktsiooni KUDNE, et muuta valemis kasutatavate lahtriviidete vahemikku ilma valemit ennast redigeerimata. See tagab, et samu lahtreid kasutatakse isegi siis, kui teie arvutustabel muutub. Teave kehtib rakenduste Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel for Mac ja Excel Online kohta.

Kasutage dünaamilist vahemikku valemiga COUNTIF – INDIRECT

Funktsiooni INDIRECT saab kasutada paljude funktsioonidega, mis aktsepteerivad argumendina lahtri viidet, näiteks funktsioonid SUM ja COUNTIF.

INDIRECTi kasutamine COUNTIF-i argumendina loob lahtriviidete dünaamilise vahemiku, mida funktsioon saab loendada, kui lahtri väärtused vastavad kriteeriumidele. See muudab tekstiandmed, mida mõnikord nimetatakse tekstistringiks, lahtriviiteks.

See näide põhineb ülaloleval pildil näidatud andmetel. Õpetuses loodud valem COUNTIF – INDIRECT on:

=LOENDIF(KAUDNE(E1&":"&E2), ">10")

Selles valemis sisaldab funktsiooni INDIRECT argument:

Lahter viitab E1 ja E2, mis sisaldavad tekstiandmeid D1 ja D6.
Vahemiku operaator, koolon (:), mis on ümbritsetud jutumärkidega (" "), mis muudab kooloni tekstiks string.
Kaks ampersandi (&), mida kasutatakse käärsoole ühendamiseks või ühendamiseks lahtriviidetega E1 ja E2.

Tulemus on see, et INDIRECT teisendab tekstistringi D1:D6 lahtri viiteks ja edastab selle loendatavale funktsioonile COUNTIF, kui viidatud lahtrid on suuremad kui 10.

Funktsioon INDIRECT aktsepteerib mis tahes tekstisisestusi. Need võivad olla töölehe lahtrid, mis sisaldavad teksti või tekstilahtriviiteid, mis sisestatakse otse funktsiooni.

Muutke dünaamiliselt valemi vahemikku

Pidage meeles, et eesmärk on luua dünaamilise ulatusega valem. Dünaamilist vahemikku saab muuta ilma valemit ennast muutmata.

Muutes lahtrites E1 ja E2 asuvaid tekstiandmeid D1 ja D6 asemel D3 ja D7, saab funktsiooniga summeeritud vahemikku lihts alt muuta D1:D6-lt D3:D7-le. See välistab vajaduse lahtris G1 valemit otse redigeerida.

Selle näite funktsioon COUNTIF loeb numbreid sisaldavaid lahtreid ainult siis, kui need on suuremad kui 10. Kuigi vahemikus D1:D6 viiest lahtrist neli sisaldavad andmeid, sisaldavad ainult kolm lahtrit numbreid. Funktsioon ignoreerib lahtreid, mis on tühjad või sisaldavad tekstiandmeid.

Teksti loendamine COUNTIF-iga

Funktsioon COUNTIF ei piirdu arvandmete loendamisega. Samuti loendab see teksti sisaldavaid lahtreid, kontrollides, kas need vastavad teatud tekstile.

Selleks sisestatakse lahtrisse G2 järgmine valem:

=LOENDIF(KAUDNE(E1&":"&E2), "kaks")

Selles valemis viitab funktsioon INDIRECT lahtritele B1 kuni B6. Funktsioon COUNTIF liidab kokku lahtrite arvu, mille tekstiväärtus on kaks.

Sel juhul on tulemuseks 1.

COUNTA, COUNTBLANK ja INDIRECT

Kaks muud Exceli loendusfunktsiooni on COUNTA, mis loendab mis tahes tüüpi andmeid sisaldavad lahtrid, jättes tähelepanuta ainult tühjad või tühjad lahtrid, ja COUNTBLANK, mis loendab ainult tühje või tühje lahtreid vahemikus.

Kuna mõlemal funktsioonil on funktsiooni COUNTIF süntaks sarnane, saab need asendada ül altoodud näites INDIRECT-ga, et luua järgmised valemid:

=LOENDA(KADUNE(E1&":"&E2))

=LOENDTÜHI(KAUDNE(E1&":"&E2)

Vahemiku D1:D6 puhul tagastab COUNTA vastuseks 4, kuna viiest lahtrist neli sisaldavad andmeid. COUNTBLANK tagastab vastuseks 1, kuna vahemikus on ainult üks tühi lahter.

Miks kasutada KAUDSET funktsiooni?

Kõigis nendes valemites funktsiooni KUDNE kasutamise eeliseks on see, et uusi lahtreid saab sisestada vahemikku kõikjal.

Valik nihkub dünaamiliselt erinevate funktsioonide sees ja tulemusi värskendatakse vastav alt.

Ilma INDIRECT funktsioonita tuleks redigeerida iga funktsiooni, et hõlmata kõik 7 lahtrit, sealhulgas uus.

Funktsiooni INDIRECT eelised seisnevad selles, et tekstiväärtusi saab sisestada lahtriviidetena ja see värskendab vahemikke dünaamiliselt, kui teie arvutustabel muutub.

See muudab arvutustabeli üldise hoolduse palju lihtsamaks, eriti väga suurte arvutustabelite puhul.