Funkcija INDIRECT maina šūnu atsauču diapazonu formulā, nerediģējot formulu.
Izmantojiet INDIRECT kā argumentu COUNTIF, lai izveidotu dinamisku šūnu diapazonu, kas atbilst noteiktiem kritērijiem.
Kritērijus nosaka funkcija INDIRECT, un tiek skaitītas tikai kritērijiem atbilstošās šūnas.
Šajā rakstā ir paskaidrots, kā Excel formulās izmantot funkciju INDIRECT, lai mainītu formulā izmantoto šūnu atsauču diapazonu, nerediģējot pašu formulu. Tas nodrošina, ka tiek izmantotas vienas un tās pašas šūnas pat tad, kad mainās jūsu izklājlapa. Informācija attiecas uz Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel for Mac un Excel Online.
Izmantojiet dinamisko diapazonu ar formulu COUNTIF - INDIRECT
Funkciju INDIRECT var izmantot ar vairākām funkcijām, kas pieņem šūnas atsauci kā argumentu, piemēram, ar funkcijām SUM un COUNTIF.
Izmantojot INDIRECT kā argumentu COUNTIF, tiek izveidots dinamisks šūnu atsauču diapazons, ko funkcija var saskaitīt, ja šūnu vērtības atbilst kritērijiem. Tas tiek darīts, pārvēršot teksta datus, ko dažkārt dēvē par teksta virkni, šūnas atsaucē.
Image
Šis piemērs ir balstīts uz iepriekš attēlā redzamajiem datiem. Apmācībā izveidotā formula COUNTIF - INDIRECT ir:
=SKAITĪT, JA(NETIEŠAIS(E1&":"&E2), ">10")
Šajā formulā INDIRECT funkcijas arguments satur:
Šūna atsaucas uz E1 un E2, kas satur teksta datus D1 un D6.
Diapazona operators, kols (:), ko ieskauj pēdiņas (" "), kas kolu pārvērš tekstā string.
Divas ampersandas (&), ko izmanto, lai savienotu vai savienotu kopā kolu ar šūnu atsaucēm E1 un E2.
Rezultātā INDIRECT pārvērš teksta virkni D1:D6 par šūnas atsauci un nodod to COUNTIF funkcijai, kas jāuzskaita, ja norādītās šūnas ir lielākas par 10.
Funkcija INDIRECT pieņem jebkuru teksta ievadi. Tās var būt darblapas šūnas, kurās ir teksts vai teksta šūnu atsauces, kas tiek ievadītas tieši funkcijā.
Dinamiski mainiet formulas diapazonu
Atcerieties, ka mērķis ir izveidot formulu ar dinamisku diapazonu. Dinamisko diapazonu var mainīt, nerediģējot pašu formulu.
Mainot teksta datus, kas atrodas šūnās E1 un E2, no D1 un D6 uz D3 un D7, funkcijas kopējo diapazonu var viegli mainīt no D1:D6 uz D3:D7. Tas novērš nepieciešamību tieši rediģēt formulu šūnā G1.
Funkcija COUNTIF šajā piemērā uzskaita tikai šūnas, kurās ir skaitļi, ja tie ir lielāki par 10. Lai gan četrās no piecām šūnām diapazonā D1:D6 ir dati, tikai trīs šūnās ir skaitļi. Šūnas, kas ir tukšas vai satur teksta datus, funkcija ignorē.
Teksta skaitīšana ar COUNTIF
Funkcija COUNTIF neaprobežojas tikai ar skaitlisku datu skaitīšanu. Tas arī uzskaita šūnas, kurās ir teksts, pārbaudot, vai tās atbilst noteiktam tekstam.
Lai to izdarītu, šūnā G2 tiek ievadīta šāda formula:
=SKAITĪT, JA(NETIEŠAIS(E1&":"&E2), "divi")
Šajā formulā funkcija INDIRECT atsaucas uz šūnām B1 līdz B6. Funkcija COUNTIF summē to šūnu skaitu, kurās ir teksta vērtība divi.
Šajā gadījumā rezultāts ir 1.
COUNTA, COUNTBLANK un INDIRECT
Divas citas Excel skaitīšanas funkcijas ir COUNTA, kas saskaita šūnas, kurās ir jebkāda veida dati, ignorējot tikai tukšas vai tukšas šūnas, un COUNTBLANK, kas skaita tikai tukšas vai tukšas šūnas diapazonā.
Tā kā abām šīm funkcijām ir līdzīga sintakse funkcijai COUNTIF, tās var aizstāt iepriekš minētajā piemērā ar INDIRECT, lai izveidotu šādas formulas:
=SKAITĪT(NEDIREKTS(E1&":"&E2))
=SKAITSTUŠS(NETIEŠAIS(E1&":"&E2)
Diapazonam D1:D6 COUNTA atgriež atbildi 4, jo četrās no piecām šūnām ir dati. COUNTBLANK atgriež atbildi 1, jo diapazonā ir tikai viena tukša šūna.
Kāpēc izmantot NETIEŠO funkciju?
Funkcijas INDIRECT izmantošanas ieguvums visās šajās formulās ir tāds, ka jaunas šūnas var ievietot jebkurā diapazonā.
Diapazons dinamiski mainās dažādu funkciju iekšienē, un rezultāti attiecīgi tiek atjaunināti.
Image
Bez funkcijas INDIRECT, katra funkcija būtu jārediģē, lai iekļautu visas 7 šūnas, ieskaitot jauno.
Funkcijas INDIRECT priekšrocības ir tādas, ka teksta vērtības var ievietot kā šūnu atsauces un tā dinamiski atjaunina diapazonus ikreiz, kad mainās jūsu izklājlapa.
Tas ievērojami atvieglo kopējo izklājlapu apkopi, īpaši ļoti lielām izklājlapām.
Funkcija COUNTIF programmā Excel uzskaita to šūnu skaitu, kas atbilst noteiktiem kritērijiem, kas var būt noderīgi vairākos gadījumos. Tālāk ir norādīts, kā programmā Excel izmantot COUNTIF ar vienu cirteri
Uzziniet, kā paslēpt un parādīt ritjoslas un kā atiestatīt vertikālās ritjoslas slīdņa diapazonu programmā Microsoft Excel. Atjaunināts, lai iekļautu programmu Excel 2019
Uzziniet, kā programmā Microsoft Excel izveidot, rediģēt un definēt nosauktu diapazonu, kā arī tvēruma un nosaukumu ierobežojumus. Atjaunināts, lai iekļautu programmu Excel 2019
Zvanot pie durvīm, jūs zināt, kad kāds ir pie jūsu durvīm. Bet, ja tie ir pārāk jutīgi vai nav pietiekami jutīgi, jums, iespējams, būs jāzina, kā pielāgot zvana kustības sensora diapazonu
