Naudokite netiesioginę funkciją "Excel" formulėse, kad pakeistumėte formulėje naudojamų ląstelių nuorodų diapazoną, nereikėtų redaguoti pačios formulės. Tai užtikrina, kad tos pačios ląstelės yra naudojamos netgi tada, kai pasikeičia jūsų skaičiuoklė.
Pastaba: Šiame straipsnyje pateikiamos instrukcijos taikomos "Excel 2019", "Excel 2016", "Excel 2013", "Excel 2010", "Excel for Mac" ir "Excel Online".
Naudokite dinaminį diapazoną naudodami COUNTIF - NETIESIOGINĘ formulę
Netiesioginę funkciją galima naudoti su daugybe funkcijų, kurios priima ląstelės nuorodą kaip argumentą, pavyzdžiui, SUM ir COUNTIF funkcijas.
Naudojant INDIRECT kaip COUNTIF argumentą sukurtas dinaminis ląstelių nuorodų asortimentas, kurį galima skaičiuoti pagal funkciją, jei ląstelių vertės atitinka kriterijus. Tai daroma, sukant teksto duomenis, kartais vadinamus teksto eilute, į ląstelės nuorodą.
Šis pavyzdys pagrįstas duomenimis, pateiktais aukščiau esančiame paveikslėlyje. Pamokoje sukurta COUNTIF - NETIESIOGINĖ formulė yra:
= COUNTIF (netiesioginiai (E1 & ":" & E2), "> 10")
Šioje formulėje NETIESIOS funkcijos argumentas yra:
- Ląstelės nuorodos E1 ir E2, kuriose yra tekstiniai duomenys D1 ir D6.
- Diapazono operatorius, dvitaškis (:), apsuptas kabutėmis (' '), kuris paverčia dvitaškį teksto eilute.
- Du ampersandai (&), kurie naudojami susieti arba sujungti dvitaškį su ląstelių nuorodomis E1 ir E2.
Rezultatas yra tas, kad INDIREKTAS konvertuoja teksto eilutę D1: D6 į ląstelės nuorodą ir perduoda jį kartu su COUNTIF funkcija, kuri turi būti skaičiuojama, jei nurodytos ląstelės yra didesnės nei 10.
Netiesioginė funkcija priima bet kokius teksto įvesties būdus. Tai gali būti lakštai darbalapyje, kuriuose yra teksto ar teksto langelių nuorodos, kurios yra tiesiogiai įvedamos į funkciją.
Dinamiškai keiskite Formulės diapazoną
Atminkite, kad tikslas yra sukurti formulę su dinaminiu diapazonu. Dinaminį diapazoną galima keisti be paties formos redagavimo.
Pakeitus tekstinius duomenis, esančius ląstelėse E1 ir E2, iš D1 ir D6 į D3 ir D7, funkcijos diapazonas gali būti lengvai pakeistas iš D1: D6 į D3: D7. Tai pašalina būtinybę tiesiogiai redaguoti formulę ląstelėje G1.
Šiame pavyzdyje naudojama funkcija COUNTIF tik skaičiuoja langelius su skaičiais, jei jie yra didesni nei 10. Nors keturios iš penkių D1: D6 diapazono ląstelių yra duomenų, tik trys langeliuose yra skaičiai. Ląstelės, kurios yra tuščios arba kuriose yra tekstinių duomenų, funkcija ignoruojama.
Teksto skaičiavimas su COUNTIF
COUNTIF funkcija neapsiriboja skaitinių duomenų skaičiavimu. Taip pat skaičiuojami elementai, kuriuose yra teksto, patikrinus, ar jie atitinka tam tikrą tekstą.
Norėdami tai padaryti, langelyje G2 įrašoma ši formulė:
= COUNTIF (netiesioginiai (E1 & ":" & E2), "du")
Šioje formulėje NETIRECT funkcija nurodo langelius nuo B1 iki B6. Funkcija COUNTIF suskaido ląstelių, turinčių teksto reikšmę, skaičių du juose.
Tokiu atveju rezultatas yra 1.
COUNTA, COUNTBLANK ir NETIESIOGINIS
Dvi kitos Excel skaičiavimo funkcijos yra COUNTA, kuriose skaičiuojami ląstelės, kuriose yra bet kokio tipo duomenys, ignoruojant tik tuščias arba tuščias ląsteles, ir COUNTBLANK, kuriame skaičiuojami tik tušti arba tušti langeliai diapazone.
Kadangi abi šios funkcijos turi panašią sintaksę su funkcija COUNTIF, jos gali būti pakeistos į aukščiau pateiktą pavyzdį NETIESIOGINIAI, kad būtų sukurtos šios formulės:
= COUNTA (netiesiogiai (E1 & ":" & E2))= COUNTBLANK (NETIRECT (E1 & ":" & E2)
Jei diapazonas D1: D6, COUNTA grąžina 4 atsakymą, nes keturiose iš penkių ląstelių yra duomenų. COUNTBLANK grąžina 1 atsakymą, nes diapazone yra tik vienas tuščias langelis. Naudojimasis netiesiogine funkcija visose šiose formulėse yra ta, kad naujos ląstelės gali būti įterptos bet kurioje diapazono srityje. Diapazonas dinamiškai keičiasi įvairiose funkcijose, o rezultatai atitinkamai atnaujinami. Be NETIESIOS funkcijos, kiekvieną funkciją reikės redaguoti, įtraukiant visas 7 ląsteles, įskaitant naują. Netiesioginės funkcijos privalumai yra tai, kad teksto vertės gali būti įterptos kaip ląstelių nuorodos ir kad jos dinamiškai atnaujina intervalus, kai pasikeičia skaičiuoklė. Dėl to daug lengviau tvarkyti bendrą skaičiuoklės tvarkymą, ypač labai didelėms skaičiuoklėms. Kodėl reikia naudoti netiesioginę funkciją?
