Kaip naudoti dinaminį diapazoną programoje Excel naudojant COUNTIF ir INDIRECT

Turinys:

2024 Autorius: Abigail Brown | [email protected]. Paskutinį kartą keistas: 2023-12-17 06:55

Ką žinoti

Funkcija NETIESIOGINĖ pakeičia langelių nuorodų diapazoną formulėje neredaguojant formulės.
Naudokite INDIRECT kaip COUNTIF argumentą, kad sukurtumėte dinaminį langelių diapazoną, atitinkantį nurodytus kriterijus.
Kriterijai nustatomi naudojant NETIESIOGINĖS funkciją ir skaičiuojami tik kriterijus atitinkantys langeliai.

Šiame straipsnyje paaiškinama, kaip naudoti NETIESIOGINĖS funkciją „Excel“formulėse norint pakeisti formulėje naudojamų langelių nuorodų diapazoną neredaguojant pačios formulės. Taip užtikrinama, kad būtų naudojami tie patys langeliai, net kai keičiasi skaičiuoklė. Informacija taikoma „Excel 2019“, „Excel 2016“, „Excel 2013“, „Excel 2010“, „Excel for Mac“ir „Excel Online“.

Naudokite dinaminį diapazoną naudodami COUNTIF – NETIESIOGINĖS formulę

Funkciją INDIRECT galima naudoti su daugybe funkcijų, kurios kaip argumentą priima langelio nuorodą, pvz., funkcijomis SUM ir COUNTIF.

Naudojant INDIRECT kaip COUNTIF argumentą sukuriamas dinaminis langelių nuorodų diapazonas, kurį funkcija gali suskaičiuoti, jei langelių reikšmės atitinka kriterijus. Tai daroma paverčiant tekstinius duomenis, kartais vadinamus teksto eilute, langelio nuoroda.

Šis pavyzdys pagrįstas aukščiau esančiame paveikslėlyje pateiktais duomenimis. Mokymo programoje sukurta formulė COUNTIF – INDIRECT yra:

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

Šioje formulėje NETIESIOGINĖS funkcijos argumente yra:

Ląstelė nurodo E1 ir E2, kuriose yra tekstiniai duomenys D1 ir D6.
Diabatas operatorius, dvitaškis (:), apsuptas dvigubomis kabutėmis (" "), kuris dvitaškį paverčia tekstu string.
Du ampersandai (&), naudojami dvitaškiui sujungti arba sujungti su langelio nuorodomis E1 ir E2.

Rezultatas INDIRECT konvertuoja teksto eilutę D1:D6 į langelio nuorodą ir perduoda ją COUNTIF funkcijai, kuri turi būti skaičiuojama, jei nuorodos langeliai yra didesni nei 10.

Funkcija INDIRECT priima bet kokią teksto įvestį. Tai gali būti darbalapio langeliai, kuriuose yra teksto arba teksto langelių nuorodos, kurios įvedamos tiesiai į funkciją.

Dinamiškai keiskite formulės diapazoną

Atminkite, kad tikslas yra sukurti formulę su dinaminiu diapazonu. Dinaminį diapazoną galima pakeisti neredaguojant pačios formulės.

Pakeitus tekstinius duomenis, esančius langeliuose E1 ir E2, iš D1 ir D6 į D3 ir D7, funkcijos sumuojamas diapazonas gali būti lengvai pakeistas iš D1:D6 į D3:D7. Tai pašalina poreikį tiesiogiai redaguoti formulę langelyje G1.

Funkcija COUNTIF šiame pavyzdyje skaičiuoja tik langelius, kuriuose yra skaičiai, jei jie didesni nei 10. Net jei keturiuose iš penkių langelių D1:D6 diapazone yra duomenų, tik trijuose langeliuose yra skaičiai. Funkcija nepaiso langelių, kurie yra tušti arba kuriuose yra tekstinių duomenų.

Teksto skaičiavimas naudojant COUNTIF

Funkcija COUNTIF neapsiriboja skaitinių duomenų skaičiavimu. Ji taip pat skaičiuoja langelius, kuriuose yra teksto, patikrindama, ar jie atitinka tam tikrą tekstą.

Norėdami tai padaryti, langelyje G2 įvedama ši formulė:

Nr.

Šioje formulėje funkcija NETIESIOGINĖ nurodo langelius nuo B1 iki B6. Funkcija COUNTIF sumuoja langelių, kurių teksto reikšmė yra dvi, skaičių.

Šiuo atveju rezultatas yra 1.

COUNTA, COUNTBLANK ir NETIESIOGINĖS

Dvi kitos „Excel“skaičiavimo funkcijos yra COUNTA, kuri skaičiuoja langelius, kuriuose yra bet kokio tipo duomenų, ignoruojant tik tuščius arba tuščius langelius, ir COUNTBLANK, kuri skaičiuoja tik tuščius arba tuščius langelius diapazone.

Kadangi abiejų šių funkcijų sintaksė panaši į funkcijos COUNTIF, jas galima pakeisti aukščiau pateiktame pavyzdyje INDIRECT, kad būtų sukurtos šios formulės:

Nr.

Diapazonui D1:D6 COUNTA pateikia 4 atsakymą, nes keturiuose iš penkių langelių yra duomenų. COUNTBLANK pateikia atsakymą 1, nes diapazone yra tik vienas tuščias langelis.

Kodėl naudoti NETIESIOGINĘ funkciją?

Netiesioginės funkcijos naudojimo visose šiose formulėse pranašumas yra tas, kad naujus langelius galima įterpti bet kurioje diapazono vietoje.

Diapazonas dinamiškai keičiasi įvairių funkcijų viduje, o rezultatai atitinkamai atnaujinami.

Be funkcijos NETIESIOGINĖS, kiekvieną funkciją reikės redaguoti, kad būtų įtrauktos visos 7 ląstelės, įskaitant naują.

Netiesioginės funkcijos pranašumai yra tai, kad teksto reikšmes galima įterpti kaip langelių nuorodas ir kad ji dinamiškai atnaujina diapazonus, kai pasikeičia jūsų skaičiuoklė.

Tai labai palengvina bendrą skaičiuoklės priežiūrą, ypač jei tai labai didelės skaičiuoklės.