Exceli kaudne funktsioon - ülevaade
Funktsiooni INDIRECT Excelis saab kasutada siis, kui teil on tekstistringina lahtri või vahemiku viide ja soovite nende viidete väärtusi saada.
Lühidalt - saate kasutada kaudset valemit tagastab tekstistringiga määratud viite.
Selles Exceli õpetuses näitan teile praktiliste näidete abil, kuidas Excelis kaudset funktsiooni kasutada.
Aga enne, kui näitedesse asun, vaatame kõigepealt selle süntaksit.
KAUDNE FUNKTSIOON Süntaks
= KAUDNE (ref_text, [a1])
Sisendargumendid
- ref_text - Tekstistring, mis sisaldab viidet lahtrile või nimega vahemikule. See peab olema kehtiv lahtri viide, vastasel juhul tagastab funktsioon #REF! viga
- [a1] - Loogiline väärtus, mis määrab, millist tüüpi viidet kasutada ref tekst. See võib olla kas TÕENE (tähistab A1 stiiliviidet) või VÄÄR (näitab R1C1-stiilis viidet). Kui see välja jätta, on see vaikimisi TRUE.
lisamärkmed
- INDIRECT on lenduv funktsioon. See tähendab, et see arvutab ümber iga kord, kui Exceli töövihik on avatud või kui töölehel käivitatakse arvutus. See pikendab töötlemisaega ja aeglustab teie töövihikut. Kuigi saate kasutada kaudset valemit väikeste andmekogumitega, millel ei ole kiirust vähe või üldse mitte, näete, et see muudab teie töövihiku aeglasemaks, kui kasutate seda suurte andmekogumitega
- Viitetekst (ref_text) võib olla järgmine:
- Viide lahtrile, mis sisaldab omakorda viidet A1- või R1C1-stiilis viitevormingus.
- Viide lahtrile topeltjutumärkides.
- Nimega vahemik, mis tagastab viite
Näiteid kaudse funktsiooni kasutamise kohta Excelis
Nüüd sukeldume ja vaatame mõningaid näiteid selle kohta, kuidas Excelis funktsiooni INDIRECT kasutada.
Näide 1: kasutage väärtuse toomiseks lahtri viidet
See võtab lahtri viite tekstistringina sisendina ja tagastab selle viite väärtuse (nagu on näidatud allolevas näites):
Lahtri C1 valem on järgmine:
= KAUDNE ("A1")
Ülaltoodud valem võtab lahtri viite A1 sisendargumendiks (jutumärkides tekstistringina) ja tagastab selle lahtri väärtuse, mis on 123.
Kui te nüüd mõtlete, siis miks ma ei kasuta funktsiooni INDIRECT kasutamise asemel lihtsalt = A1, on teil õige küsimus.
Siin on põhjus, miks…
Kui kasutate = A1 või = $ A $ 1, see annab teile sama tulemuse. Kuid kui sisestate rea esimese rea kohale, märkate, et lahtriviited muutuvad automaatselt uue rea arvestamiseks.
Funktsiooni INDIRECT saate kasutada ka siis, kui soovite lahtriviited lukustada nii, et see ei muutuks töölehele ridade/veergude lisamisel.
Näide 2: väärtuse toomiseks kasutage lahtri viiteid lahtris
Seda funktsiooni saate kasutada ka väärtuse toomiseks lahtrist, mille viide on lahtrisse salvestatud.
Ülaltoodud näites on lahtri A1 väärtus 123.
Lahtris C1 on viide lahtrile A1 (tekstistringina).
Kui kasutate funktsiooni INDIRECT ja kasutate argumendina C1 (milles on omakorda tekstistringina lahtri aadress), teisendab see lahtri A1 väärtuse kehtivaks lahtriviiteks.
See omakorda tähendab, et funktsioon viitab lahtrile A1 ja tagastab selles sisalduva väärtuse.
Pange tähele, et te ei pea siin topelt jutumärke kasutama, kuna C1 lahtri viide on salvestatud ainult tekstistringivormingus.
Samuti juhul, kui lahtri C1 tekstistring pole kehtiv lahtri viide, tagastab kaudne funktsioon #REF! viga.
Näide 3: viite loomine lahtris väärtuse abil
Lahtrite viite saate luua ka veeru tähestiku ja rea numbri kombinatsiooni abil.
Näiteks kui lahter C1 sisaldab numbrit 2 ja kasutate valemit = KAUDNE („A” ja C1) siis viitaks see lahtrile A2.
Selle praktiline rakendus võib olla siis, kui soovite luua rakkudele dünaamilise viite mõne muu lahtri väärtuse põhjal.
Kui valemis kasutatav tekstistring annab viite, millest Excel aru ei saa, tagastab see viite vea (#REF!).
Näide 4: Arvutage lahtrite vahemiku SUM
Samuti saate viidata lahtrivahemikule samamoodi nagu ühele lahtrile, kasutades Exceli funktsiooni INDIRECT.
Näiteks, = KAUDNE („A1: A5”) viitaks vahemikule A1: A5.
Seejärel saate kasutada summat SUM summa leidmiseks või funktsiooni LARGE/SMALL/MIN/MAX muude arvutuste tegemiseks.
Nagu funktsioon SUM, saate kasutada ka selliseid funktsioone nagu LARGE, MAX/MIN, COUNT jne.
Näide 5: viite loomine lehele, kasutades funktsiooni INDIRECT
Ülaltoodud näited hõlmasid sama töölehe lahtri viitamist. Samuti saate kasutada kaudset valemit, et viidata mõne muu töölehe või mõne muu töövihiku lahtrile.
Siin on midagi, mida peate teadma teistele lehtedele viitamise kohta.
- Oletame, et teil on tööleht nimega Sheet1 ja lahtris A1 oleva lehe sees on teil väärtus 123. Kui lähete teisele lehele (oletame, Sheet2) ja viidate lehe 1 lahtrile A1, on valem järgmine: = Leht1! A1
Aga…
- Kui teil on tööleht, mis sisaldab kahte või enamat kahte sõna (tühikute vahel) ja kui viitate selle lehe lahtrile A1 teisest lehest, siis oleks valem järgmine: = 'Andmekogum'! A1
Mitme sõna puhul lisab Excel automaatselt lehe jutu algusesse ja lõppu üksikud jutumärgid.
Nüüd vaatame, kuidas luua kaudne funktsioon, mis viitab mõne teise töölehe lahtrile.
Oletame, et teil on leht nimega Andmekogum ja lahtri A1 väärtus on 123.
Sellele lahtrile viitamiseks mõnelt teiselt töölehelt kasutage järgmist valemit:
= KAUDNE ("'Andmekogum'! A1")
Nagu näete, peab viide lahtrile sisaldama ka töölehe nime.
Kui teil on lahtris töölehe nimi (oletame, et A1), saate kasutada järgmist valemit:
= KAUDSED ("" "& A1 &" '! A1 ")
Kui teil on lahtris A1 töölehe nimi ja lahtris A2 lahtri aadress, siis oleks valem järgmine:
= KAUDSED ("" "& A1 &" '! "& A2)
Sarnaselt saate ka valemit muuta, et viidata mõne teise töövihiku lahtrile.
See võib olla kasulik, kui proovite luua kokkuvõtte lehte, mis tõmbab andmed mitmelt erinevalt lehelt.
Samuti pidage meeles, et kui kasutate seda valemit teisele töövihikule viitamiseks, peab see töövihik olema avatud.
Näide 6: Nimetatud vahemikule viitamine INDIRECT valemi abil
Kui olete Excelis loonud nimega vahemiku, saate sellele nimega vahemikule viidata funktsiooni INDIRECT abil.
Oletame näiteks, et teil on kolme õpilase hinded viie õpilase kohta kolmes aines, nagu allpool näidatud.
Selles näites nimetage lahtrid:
- B2: B6: matemaatika
- C2: C6: füüsika
- D2: D6: keemia
Lahtrivahemiku nimetamiseks valige lihtsalt lahtrid ja minge nime kasti, sisestage nimi ja vajutage sisestusklahvi.
Nüüd saate viidata nendele nimevahemikele, kasutades järgmist valemit:
= KAUDNE ("Nimega vahemik")
Näiteks kui soovite teada matemaatika märkide keskmist, kasutage valemit:
= KESKMINE (KAUDNE ("Matemaatika"))
Kui teil on lahtris nimetatud vahemiku nimi (allolevas näites F2 kannab nime Matemaatika), saate seda kasutada otse valemis.
Allolev näide näitab, kuidas arvutada keskmist nimevahemike abil.
Näide 7: sõltuva ripploendi loomine Exceli funktsiooni INDIRECT abil
See on selle funktsiooni suurepärane kasutamine. Selle abil saate hõlpsalt luua sõltuva ripploendi (seda nimetatakse ka tingimuslikuks ripploendiks).
Oletame näiteks, et teil on reas riikide loend ja iga riigi linnade nimed, nagu allpool näidatud.
Sõltuva ripploendi loomiseks peate looma kaks nimevahemikku: A2: A5 nimega US ja B2: B5 nimega India.
Nüüd valige lahter D2 ja looge India ja USA jaoks ripploend. See oleks esimene ripploend, kus kasutajal on võimalus valida riik.
Nüüd sõltuvate ripploendite loomiseks tehke järgmist.
- Valige lahter E2 (lahter, millest soovite saada ripploendi).
- Klõpsake vahekaarti Andmed
- Klõpsake nuppu Andmete valideerimine.
- Valige valideerimiskriteeriumiteks loend ja kasutage allikaväljal järgmist valemit: = KAUDNE ($ D $ 2)
- Klõpsake nuppu OK.
Nüüd, kui sisestate lahtrisse D2 USA, näitab lahtri E2 rippmenüü USA osariike.
Ja kui sisenete lahtrisse D2 Indiasse, näitab lahtri E2 rippmenüü India osariike.
Nii et siin on mõned näited Exceli funktsiooni INDIRECT kasutamiseks. Need näited toimiksid kõigi Exceli versioonide puhul (Office 365, Excel2021-2022/2016/2013/2013)
Loodan, et leidsite selle õpetuse kasulikuks.
- Funktsioon Excel VLOOKUP.
- Funktsioon Excel HLOOKUP.
- Funktsioon Excel INDEX.
- Funktsioon Excel MATCH.
- Funktsioon Excel OFFSET.
