Laadige alla töövihiku näide
See õpetus näitab, kuidas Exceli kaudne funktsioon Excelis, et luua tekstist lahtri viide.
KAUDSE Funktsiooni ülevaade
Funktsioon INDIRECT Loob tekstistringist lahtri viite.
(Pange tähele, kuidas valemisisendid ilmuvad)
Funktsioon INDIRECT Süntaks ja sisendid:
| 1 | = KAUDNE (ref_text, C1) |
ref_text - Lahtrit või vahemiku viidet tähistav string. String võib olla vormingus R1C1 või A1 või nimega vahemik.
a1 - VALIKULINE: näitab, kas viide on vormingus R1C1 või A1. VÄÄR R1C1 puhul või TÕENE / Välistatud A1 puhul.
Mis on kaudne funktsioon?
Funktsioon INDIRECT võimaldab teil anda tekstistringi ja lasta arvutil seda stringi tõlgendada tegeliku viitena. Seda saab kasutada samale lehele, teisele lehele või isegi teisele töövihikule viitamiseks.
ETTEVAATUST. Funktsioon INDIRECT on üks lenduvatest funktsioonidest. Kui arvutustabelis töötate, arvutab arvuti enamasti valemi ümber ainult siis, kui sisendid on oma väärtusi muutnud. Lenduv funktsioon aga arvutab uuesti iga kui muudate mõnda lahtrit. Tuleb olla ettevaatlik, et vältida lenduvate funktsioonide liigse kasutamise või lenduvate funktsioonide tulemusest sõltuvate lahtrite tõttu liiga palju ümberarvestusaega.
Looge lahtri viide
Ütle, et soovite väärtuse hankida A2 -st, kuid veenduge, et teie valem jääb A2 -l, olenemata uute ridade lisamisest/eemaldamisest. Võite kirjutada valemi
| 1 | = KAUDNE ("A2") |
Pange tähele, et meie funktsioonis olev argument on tekstistring „A2”, mitte lahtriviide. Samuti, kuna see on tekstistring, pole vaja näidata absoluutset viidet, näiteks $ A $ 2. Tekst ei muutu kunagi ja seega osutab see valem alati A2 -le, olenemata sellest, kuhu see teisaldatakse.
KAUDNE rea number
Saate tekstistringid ja väärtused lahtritest kokku liita. Selle asemel, et kirjutada "A2", nagu me varem tegime, võime võtta lahtrist B2 numbrilise väärtuse ja kasutada seda oma valemis. Kirjutame välja sellise valemi
| 1 | = KAUDNE ("A" ja B2) |
Sümbolit „&” kasutatakse siin tekstistringi „A” ühendamiseks lahtri B2 väärtusega. Niisiis, kui B2 väärtus oleks praegu 10, siis loeks meie valem seda järgmiselt
| 123 | = KAUDSED ("A" ja 10)= KAUDNE ("A10")= A10 |
Veeru kaudne väärtus
Veeruviites saate ka ühendada. Ütleme seekord, et me teame, et tahame väärtust realt 10 hankida, kuid soovime muuta seda, millisest veerust tõmmata. Paneme soovitud veerutähe lahtrisse B2. Meie valem võiks välja näha selline
| 1 | = KAUDNE (B2 & "10") |
Kui B2 väärtus on “G”, siis meie valem hindab nii
| 123 | = KAUDSED ("G" ja 10)= KAUDNE ("G10")= G10 |
Kaudne r1c1 stiil
Eelmises näites pidime veerule viitamiseks kasutama tähte. Seda seetõttu, et kasutasime A1 -stiilis viitamist. Stiilis A1 on veerud tähistatud tähega ja read ridadega. Absoluutsed viited on tähistatud tähega „$” enne üksust, mida soovime absoluutsena hoida.
R1c1 -s alustatakse nii ridade kui ka veergude kasutamist numbriga. Absoluutne viide a1 -le kirjutatakse järgmiselt
| 1 | = R1C1 |
Saate seda lugeda rida 1, veerg 1. Suhtelised viited on esitatud sulgudes, kuid number näitab asukohta valemiga raku suhtes. Seega, kui kirjutasime lahtrisse A10 valemi ja peame viitama A1 -le, kirjutaksime valemi
| 1 | = R [-9] C |
Saate seda lugeda järgmiselt: „Lahtrit 9 rida ülespoole, kuid samas veerus.
Sellest võib abi olla selles, et INDIRECT toetab r1c1 märke kasutamist. Mõelge eelmisele näitele, kus me toome väärtuse realt 10, kuid soovisime veergu muuta. Tähe andmise asemel ütleme, et panime lahtrisse B2 numbri. Meie valem võib siis välja näha
| 1 | = KAUDNE ("R10C" ja B2, VÄÄR) |
Me jätsime 2 vahelend vaidlus siiani. Kui see argument on välja jäetud või tõene, hindab funktsioon A1 stiili. Kuna see on vale, hinnatakse seda r1c1 -s. Oletame, et B2 väärtus on 5. Meie valem hindab seda nii
| 12 | = KAUDNE ("R10C5", VÄÄR)= 10 dollarit |
KAUDSED erinevused A1 ja r1c1 vahel
Pidage meeles, et me näitasime, et kuna selle valemi sisu oli tekstistring, ei muutunud see kunagi?
| 1 | = KAUDNE ("A2") |
See valem vaatab alati lahtrit A2, olenemata sellest, kuhu valemit liigutate. Kuna r1c1 -s saate sulgude abil suhtelist positsiooni näidata, ei jää see reegel järjepidevaks. Kui asetate selle valemi lahtrisse B2
| 1 | = KAUDNE ("RC [-1]") |
See vaatab lahtrit A2 (kuna veerg A asub veerust B vasakul). Kui kopeerite selle valemi lahtrisse B3, jääb sees olev tekst samaks, kuid kaudne vaatab nüüd lahtrit A3.
KAUDSED lehe nimega
Lehe nime saate ka oma kaudseteks viideteks kombineerida. Oluline reegel, mida meeles pidada, on see, et nimede ümber peaksite paigutama ühtsed jutumärgid ja lehe nimi tuleb lahtri viitest eraldada hüüumärgiga.
Oletame, et meil oli see seadistus, kus me ütleme oma lehe nime, rea ja veeru.
Meie valem kõigi nende viitamiseks ühendamiseks näeks välja selline:
| 1 | = KAUDSED ("" "& A2 &" '! "& B2 & C2) |
Seejärel hinnatakse meie valemit järgmiselt:
| 123 | = KAUDSED ("'" & "Sheet2" & "'!" & "B" & "5")= KAUDNE ("" "Leht2 '! B5")= 'Leht2'! B5 |
Tehniliselt, kuna sõnal „Sheet2” pole tühikuid, pole meil seda vaja üksikud jutumärgid. On täiesti õige kirjutada midagi sellist
| 1 | = Leht2! A2 |
Siiski ei tee haiget jutumärkide paigutamine, kui neid pole vaja. Parim tava on need lisada, et teie valem saaks hakkama eksemplaridega, kus neid võib vaja minna.
KAUDSED teisele töövihikule
Mainime ka seda, et INDIRECT saab luua viite mõnele teisele töövihikule. Piirang on see, et INDIRECT ei too suletud töövihikust väärtusi, seega on selle konkreetse kasutamise praktilisus piiratud. Kui töövihik, millele INDIRECT osutab, on avamata, viskab funktsioon "#REF!" viga.
Töövihiku nime kirjutamisel on süntaks, et see peab olema nurksulgudes. Kasutagem seda seadistust ja proovime väärtust tuua lahtrist C7.
Meie valem oleks
| 1 | = KAUDSED ("'[" & A2 & "]" & B2 & "'! C7") |
Jällegi pöörake tähelepanu üksikute jutumärkide, sulgude ja hüüumärgi paigutusele. Seejärel hinnatakse meie valemit järgmiselt:
| 123 | = KAUDSED ("'[" & "Sample.xlsx" & "]" & "Kokkuvõte" & "'! C7")= KAUDNE ("'[Sample.xslx] Kokkuvõte'! C7")= '[Sample.xlsx] Kokkuvõte'! C7 |
KAUDSED dünaamilise ulatuse loomiseks
Kui teil on suur andmekogum, on oluline proovida valemeid optimeerida, et need ei teeks rohkem tööd kui vaja. Näiteks selle asemel, et viidata kogu veerule A, võiksime lihtsalt viidata meie loendis olevate lahtrite täpsele arvule. Kaaluge järgmist paigutust:
Lahtrisse B2 oleme paigutanud valemi
| 1 | = COUNTA (A: A) |
Funktsiooni COUNTA on arvutil väga lihtne arvutada, kuna see lihtsalt kontrollib, kui palju lahtris A olevaid lahtreid on mingi väärtus, mitte loogikakontrolli või matemaatilisi toiminguid.
Nüüd ehitame oma valemi, mis võtab kokku veeru A väärtused, kuid tahame veenduda, et see vaatab ainult väärtuste täpset vahemikku (A2: A5). Kirjutame oma valemi järgmiselt
| 1 | = SUM (KAUDNE ("A2: A" ja B2)) |
Meie INDIRECT haarab lahtrist B2 numbri 5 ja loob viite vahemikule A2: A5. Seejärel saab SUM seda vahemikku oma arvutamiseks kasutada. Kui lisame lahtrisse A6 veel ühe väärtuse, värskendatakse numbrit B2 ja värskendatakse automaatselt ka meie SUM valemit, et lisada see uus väärtus.
ETTEVAATUST. Office 2007 tabelite kasutuselevõtuga on palju tõhusam salvestada oma andmed tabelisse ja kasutada struktuurilist viidet, mitte koostada selles näites kasutatud valemit INDIRECTi ebastabiilsuse tõttu. Kuid need võivad olla juhtumid, kus peate looma üksuste loendi ja te ei saa tabelit kasutada.
Dünaamiline kaardistamine INDIRECT -iga
Võtame eelmise näite ja astume veel ühe sammu. Selle asemel, et kirjutada valem, mis annaks meile väärtuste summa, loome nimega vahemiku. Võiksime seda vahemikku nimetada „MyData” ja lasta sellel viidata
| 1 | = KAUDSED ("A2: A" & COUNTA ($ A: $ A)) |
Pange tähele, et kuna me paneme selle nimega vahemikku, vahetasime viite B2 -le ja panime selle asemel funktsiooni COUNTA otse.
Nüüd, kui meil on see nimega vahemik, saaksime seda diagrammis kasutada. Loome tühja joondiagrammi ja seejärel lisame andmesarja. Seeria väärtuste jaoks võite kirjutada midagi sellist
| 1 | = Leht1! Minuandmed |
Diagramm kasutab nüüd seda viidet graafiku väärtustele. Kui veergu A lisatakse rohkem väärtusi, viitab INDIRECT üha suuremale vahemikule ja meie diagramm on jätkuvalt ajakohane kõigi uute lisandväärtustega.
Dünaamiline andmete valideerimine INDIRECT abil
Kasutajatelt sisendi kogumisel on mõnikord vaja teha ühe valiku valiku valik, mis sõltub eelmisest valikust. Kaaluge seda paigutust, kus meie esimene veerg võimaldab kasutajal valida puuviljade, köögiviljade ja liha vahel.
2 -snd veergu, ei taha me, et meil oleks suur nimekiri, mis näitaks kõiki võimalikke valikuid, kuna oleme asju juba pisut kitsendanud. Seega oleme loonud veel kolm loendit, mis näevad välja sellised:
Järgmisena määrame igaüks neist need nimekirjad nimega vahemikku. St kõik puuviljad jäävad vahemikku nimega “Puuviljad” ja köögiviljad “Köögiviljad” jne.
Tagasi oma tabelisse oleme valmis seadistama andmete valideerimise 2nd veerg. Loome loendi tüübi valideerimise, sisestades:
| 1 | = KAUDNE (A2) |
INDIRECT loeb veerus A tehtud valiku ja näeb kategooria nime. Oleme määratlenud nende nimedega vahemikud, nii et INDIRECT võtab selle nime ja loob viite soovitud vahemikule.
lisamärkmed
Funktsiooni INDIRECT abil saate tekstist lahtri viite luua.
Looge esmalt tekstistring, mis tähistab lahtri viidet. String peab olema kas tavalises A1-stiilis veeru tähes ja rea numbris (M37) või R1C1-stiilis (R37C13). Saate viite otse sisestada, kuid tavaliselt viitate lahtrid, mis määravad read ja veerud. Lõpuks sisestage valitud lahtri viitevorming. TRUE või välja jäetud A1-stiili viite korral või FALSE R1C1-stiilis.
INDIRECT Valemitega töötades võiksite kasutada Funktsioon ROW viite reanumbri või Veerg Funktsioon viite veerunumbri (mitte tähe) saamiseks.
Tagasi kõigi Exceli funktsioonide loendisse
KAUDSED Google'i arvutustabelites
Funktsioon INDIRECT töötab Google'i arvutustabelites täpselt samamoodi nagu Excelis:
