Sisu
Palju õnne! Jõudsite maratoni viimasele päevale 30 Exceli funktsiooni 30 päevaga. See on olnud pikk ja huvitav teekond, mille jooksul olete õppinud palju kasulikku Exceli funktsioonide kohta.
Maratoni 30. päeval pühendame funktsiooni uurimisele KAUDSEID (INDIRECT), mis tagastab tekstistringiga määratud lingi. Selle funktsiooniga saate luua sõltuvaid ripploendeid. Näiteks riigi ripploendist valimine määrab, millised valikud linna rippmenüüs kuvatakse.
Niisiis, vaatame lähemalt funktsiooni teoreetilist osa KAUDSEID (KAUDNE) ja uurige selle rakendamise praktilisi näiteid. Kui teil on lisateavet või näiteid, jagage neid kommentaarides.
Funktsioon 30: KAUDNE
funktsioon KAUDSEID (INDIRECT) tagastab tekstistringiga määratud lingi.
Kuidas saate funktsiooni KAUDNE kasutada?
Alates funktsioonist KAUDSEID (INDIRECT) tagastab tekstistringiga antud lingi, saate seda kasutada selleks, et:
- Loo mittenihutav esialgne link.
- Loo viide staatilisele nimega vahemikule.
- Looge link lehe, rea ja veeru teabe abil.
- Looge nihutamata arvude massiiv.
Süntaks KAUDNE (INDIRECT)
funktsioon KAUDSEID (INDIRECT) on järgmise süntaksiga:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_text (link_lahtrisse) on lingi tekst.
- a1 – kui võrdne väärtusega TRUE (TRUE) või pole määratud, siis kasutatakse lingi stiili A1; ja kui FALSE (FALSE), siis stiil R1C1.
Püünised INDIRECT (INDIRECT)
- funktsioon KAUDSEID (INDIRECT) arvutatakse uuesti, kui Exceli töölehel olevad väärtused muutuvad. See võib teie töövihikut oluliselt aeglustada, kui funktsiooni kasutatakse paljudes valemites.
- Kui funktsioon KAUDSEID (INDIRECT) loob lingi teisele Exceli töövihikule, see töövihik peab olema avatud või valem teatab veast #REF! (#LINK!).
- Kui funktsioon KAUDSEID (INDIRECT) viitab vahemikule, mis ületab ridade ja veerude piirangut, siis teatab valem veast #REF! (#LINK!).
- funktsioon KAUDSEID (INDIRECT) ei saa viidata dünaamilise nimega vahemikule.
Näide 1: looge mittenihutav alglink
Esimeses näites sisaldavad veerud C ja E samu numbreid, nende summad on arvutatud funktsiooni abil SUM (SUM) on samuti samad. Valemid on aga veidi erinevad. Lahtris C8 on valem järgmine:
=SUM(C2:C7)
=СУММ(C2:C7)
Lahtris E8 funktsioon KAUDSEID (INDIRECT) loob lingi alguslahtrile E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Kui sisestate lehe ülaossa rea ja lisate jaanuari (jaanuari) väärtuse, siis veerus C olev summa ei muutu. Valem muutub, reageerides rea lisamisele:
=SUM(C3:C8)
=СУММ(C3:C8)
Siiski funktsioon KAUDSEID (INDIRECT) fikseerib E2 alguslahtriks, seega kaasatakse jaanuar automaatselt veeru E kogusummade arvutamisse. Lõpplahter on muutunud, kuid alguslahter ei ole mõjutatud.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Näide 2: link staatilise nimega vahemikku
funktsioon KAUDSEID (INDIRECT) saab luua viite nimega vahemikule. Selles näites moodustavad sinised lahtrid vahemiku NumList. Lisaks luuakse veeru B väärtustest ka dünaamiline vahemik NumListDyn, olenevalt selles veerus olevate numbrite arvust.
Mõlema vahemiku summa saab arvutada, andes funktsioonile lihtsalt selle nime argumendina SUM (SUM), nagu näete lahtrites E3 ja E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Selle asemel, et trükkida funktsiooni vahemiku nimi SUM (SUM), saate viidata töölehe ühte lahtrisse kirjutatud nimele. Näiteks kui nimi NumList on kirjutatud lahtrisse D7, siis on lahtri E7 valem järgmine:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Kahjuks funktsioon KAUDSEID (INDIRECT) ei saa luua dünaamilise ulatuse viidet, nii et kui kopeerite selle valemi alla lahtrisse E8, kuvatakse viga #REF! (#LINK!).
Näide 3: looge link lehe, rea ja veeru teabe abil
Saate hõlpsasti luua lingi rea- ja veerunumbrite põhjal ning kasutada funktsiooni teise argumendi jaoks väärtust FALSE (FALSE) KAUDSEID (KAUDNE). Nii luuakse stiililink R1C1. Selles näites lisasime lingile lisaks lehe nime – 'MinuLingid'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
Näide 4: looge mittenihutav arvude massiiv
Mõnikord peate Exceli valemites kasutama arvude massiivi. Järgmises näites tahame arvutada veerus B 3 suurimat arvu. Arvu saab sisestada valemisse, nagu tehakse lahtris D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Kui vajate suuremat massiivi, siis tõenäoliselt ei taha te valemisse kõiki numbreid sisestada. Teine võimalus on funktsiooni kasutamine ROW (ROW), nagu on tehtud lahtrisse D5 sisestatud massiivivalemis:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
Kolmas võimalus on funktsiooni kasutamine ROW (STRING) koos KAUDSEID (INDIRECT), nagu on tehtud massiivi valemiga lahtris D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Kõigi kolme valemi tulemus on sama:
Kui aga read sisestatakse lehe ülaossa, tagastab teine valem vale tulemuse, kuna valemis olevad viited muutuvad koos rea nihutamisega. Nüüd tagastab valem kolme suurima arvu keskmise asemel suuruselt 3., 4. ja 5. numbri keskmise.
Funktsioonide kasutamine KAUDSEID (KAUDNE), säilitab kolmas valem õiged reaviited ja jätkab õige tulemuse näitamist.