Atsisiųskite darbaknygės pavyzdį
Ši pamoka parodo, kaip naudotis „Excel“ netiesioginė funkcija „Excel“, kad sukurtumėte langelio nuorodą iš teksto.
Netiesioginė funkcijų apžvalga
Funkcija INDIRECT Sukuria langelio nuorodą iš teksto eilutės.
(Atkreipkite dėmesį, kaip atrodo formulės įvestys)
INDIRECT funkcija Sintaksė ir įvestys:
| 1 | = NETIESIOGINIS (ref_text, C1) |
ref_text - Eilutė, vaizduojanti langelio nuorodą arba diapazono nuorodą. Eilutė gali būti R1C1 arba A1 formato arba gali būti pavadintas diapazonas.
a1 - PASIRENKAMA: nurodo, ar nuoroda yra R1C1 ar A1 formato. KLAIDA R1C1 arba TRUE / Praleista A1.
Kas yra netiesioginė funkcija?
Funkcija INDIRECT leidžia suteikti teksto eilutę ir leisti kompiuteriui interpretuoti tą eilutę kaip faktinę nuorodą. Tai gali būti naudojama norint nurodyti diapazoną tame pačiame lape, kitame lape ar net kitoje darbaknygėje.
ATSARGIAI: INDIRECT funkcija yra viena iš nepastovių funkcijų. Dažniausiai dirbant skaičiuoklėje kompiuteris iš naujo apskaičiuos formulę tik tuo atveju, jei įvestys pakeis reikšmes. Tačiau nepastovi funkcija perskaičiuoja kiekvieną kai pakeisite bet kurią langelį. Turėtumėte būti atsargūs, kad nesukeltumėte ilgo perskaičiavimo laiko dėl pernelyg didelio lakiųjų funkcijų naudojimo arba dėl daugybės langelių, priklausančių nuo nepastovios funkcijos rezultato.
Sukurkite langelio nuorodą
Tarkime, kad norite gauti reikšmę iš A2, bet norite įsitikinti, kad jūsų formulė lieka A2, neatsižvelgiant į naujų eilučių įterpimą/pašalinimą. Galite parašyti formulę
| 1 | = NETIESIOGINĖ ("A2") |
Atminkite, kad mūsų funkcijos argumentas yra teksto eilutė „A2“, o ne langelio nuoroda. Be to, kadangi tai yra teksto eilutė, nebūtina nurodyti absoliučios nuorodos, tokios kaip $ A $ 2. Tekstas niekada nesikeis, todėl ši formulė visada nurodys į A2, nesvarbu, kur jis bus perkeltas.
NETIESIOGINIS eilutės numeris
Galite sujungti teksto eilutes ir reikšmes iš langelių kartu. Užuot rašę „A2“, kaip tai darėme anksčiau, mes galime paimti skaitinę vertę iš B2 langelio ir naudoti ją savo formulėje. Mes išrašytume tokią formulę
| 1 | = NETIESIOGINĖ ("A" ir B2) |
„&“ Simbolis čia naudojamas teksto eilutei „A“ sujungti su verte iš langelio B2. Taigi, jei B2 vertė šiuo metu buvo 10, mūsų formulė būtų tokia
| 123 | = NETIESIOGINĖ („A“ ir 10)= NETIESIOGINĖ ("A10")= A10 |
NETIESIOGINĖ stulpelio vertė
Taip pat galite sujungti stulpelio nuorodą. Šį kartą tarkime, kad žinome, kad norime paimti vertę iš 10 eilutės, bet norime, kad galėtume pakeisti, iš kurio stulpelio traukti. Mes įdėsime norimą stulpelio raidę į langelį B2. Mūsų formulė galėtų atrodyti taip
| 1 | = NETIESIOGINIS (B2 ir „10“) |
Jei B2 vertė yra „G“, tada mūsų formulė vertinama taip
| 123 | = NETIESIOGINĖ („G“ ir 10)= NETIESIOGINIS ("G10")= G10 |
NETIESIOGINIS r1c1 stilius
Ankstesniame pavyzdyje stulpelio nuorodai nurodyti turėjome naudoti raidę. Taip yra todėl, kad naudojome vadinamąją A1 stiliaus nuorodą. Naudojant A1 stilių, stulpeliai žymimi raide, o eilutės - skaičiais. Absoliučios nuorodos nurodomos naudojant „$“ prieš elementą, kurį norime palikti absoliučiu.
R1c1 eilutės ir stulpeliai pradedami naudoti naudojant skaičių. Absoliuti nuoroda į a1 būtų parašyta kaip
| 1 | = R1C1 |
Tai galite perskaityti kaip „1 eilutė, 1 stulpelis“. Santykinės nuorodos pateikiamos naudojant skliaustus, tačiau skaičius nurodo vietą lyginant su ląstele su formule. Taigi, jei A10 langelyje rašytume formulę ir turėtume kreiptis į A1, parašytume formulę
| 1 | = R [-9] C |
Tai galite perskaityti taip: „Ląstelė 9 eilutės aukštyn, bet tame pačiame stulpelyje.
Priežastis, dėl kurios tai gali būti naudinga, yra ta, kad INDIRECT gali palaikyti r1c1 žymėjimo naudojimą. Apsvarstykite ankstesnį pavyzdį, kai mes gavome vertę iš 10 eilutės, bet norėjome pakeisti stulpelį. Užuot davę laišką, tarkime, kad į langelį B2 įrašome skaičių. Tada mūsų formulė gali atrodyti taip
| 1 | = NETIESIOGINĖ („R10C“ ir B2, NETIESA) |
Mes praleidome 2antra ginčas iki šiol. Jei šis argumentas praleistas arba tiesa, funkcija bus vertinama naudojant A1 stilių. Kadangi tai klaidinga, ji bus įvertinta r1c1. Tarkime, kad B2 reikšmė yra 5. Mūsų formulė tai įvertins taip
| 12 | = NETIESIOGINĖ ("R10C5", NETIESA)= 10 USD |
Netiesioginiai skirtumai su A1 ir r1c1
Prisiminkite, kad anksčiau parodėme, kad kadangi šios formulės turinys buvo teksto eilutė, ji niekada nepasikeitė?
| 1 | = NETIESIOGINĖ ("A2") |
Ši formulė visada žiūrės į langelį A2, nesvarbu, kur perkelsite formulę. Kadangi r1c1 galite nurodyti santykinę padėtį naudodami skliaustus, ši taisyklė nelieka nuosekli. Jei šią formulę įdėsite į langelį B2
| 1 | = NETIESIOGINIS ("RC [-1]") |
Jis žiūrės į langelį A2 (nes A stulpelis yra vienas kairėje nuo B stulpelio). Jei nukopijuosite šią formulę į langelį B3, tekstas viduje išliks tas pats, tačiau NETIESIOGINIS dabar žiūrės į langelį A3.
NETIESIOGINIS su lapo pavadinimu
Taip pat galite sujungti lapo pavadinimą į savo NETIESIOGINES nuorodas. Svarbi taisyklė, kurią reikia atsiminti, yra ta, kad aplink pavadinimus reikia dėti kabutes, o lapo pavadinimą reikia atskirti nuo langelio nuorodos šauktuku.
Tarkime, kad turėjome šią sąranką, kurioje nurodome lapo pavadinimą, eilutę ir stulpelį.
Mūsų formulė, kaip visa tai sujungti į nuorodą, atrodytų taip:
| 1 | = NETIESIOGINIS ("" "& A2 &" '! "& B2 & C2) |
Tada mūsų formulė bus įvertinta taip:
| 123 | = NETIESIOGINIS ("" "&" Sheet2 "&" '! "&" B "&" 5 ")= NETIESIOGINIS ("" "Sheet2 '! B5")= „Lapas2“! B5 |
Techniškai, kadangi žodis „Sheet2“ neturi tarpų, mes neturime reikia vienos kabutės. Puikiai tinka rašyti kažką panašaus
| 1 | = Lapas2! A2 |
Tačiau nekenkia kabutės, kai jų nereikia. Geriausia juos įtraukti, kad jūsų formulė galėtų apdoroti atvejus, kai jų gali prireikti.
NETIESIOGIAI į kitą darbo knygą
Taip pat paminėsime, kad INDIRECT gali sukurti nuorodą į kitą darbo knygą. Apribojimas yra tas, kad INDIRECT negaus reikšmių iš uždarytos darbaknygės, todėl šio konkretaus naudojimo praktiškumas yra ribotas. Jei darbaknygė, į kurią nurodo INDIRECT, yra neatidaryta, funkcija išmes „#REF!“ klaida.
Sintaksė rašant darbaknygės pavadinimą yra ta, kad ji turi būti laužtiniuose skliaustuose. Naudokime šią sąranką ir pabandykime gauti reikšmę iš langelio C7.
Mūsų formulė būtų
| 1 | = NETIESIOGINIS ("'[" & A2 & "]" & B2 & "'! C7") |
Dar kartą atkreipkite dėmesį į atskirų kabutių, skliaustų ir šauktuko vietą. Tada mūsų formulė bus įvertinta taip:
| 123 | = NETIESIOGINIS ("'[" & "Sample.xlsx" & "]" & "Santrauka" & "'! C7")= NETIESIOGINIS ("'[Sample.xslx] Santrauka'! C7")= '[Pavyzdys.xlsx] Santrauka'! C7 |
Netiesiogiai sukurti dinaminį diapazoną
Kai turite didelį duomenų rinkinį, svarbu pabandyti ir optimizuoti formules, kad jos neatliktų daugiau darbo nei reikia. Pavyzdžiui, užuot nurodę visą A stulpelį, galbūt norėtume nurodyti tik tikslų mūsų sąrašo langelių skaičių. Apsvarstykite šį išdėstymą:
B2 langelyje mes įdėjome formulę
| 1 | = SKAIČIUS (A: A) |
Funkciją COUNTA kompiuteriui labai lengva apskaičiuoti, nes ji tiesiog patikrina, kiek langelių A stulpelyje turi tam tikrą vertę, o ne atlikti loginius patikrinimus ar matematines operacijas.
Dabar kurkime savo formulę, kuri apibendrins A stulpelio reikšmes, tačiau norime įsitikinti, kad ji žiūri tik į tikslų diapazoną su reikšmėmis (A2: A5). Mes parašysime savo formulę kaip
| 1 | = SUMA (NETIESIOGINĖ („A2: A“ ir B2)) |
Mūsų INDIRECT ketina paimti skaičių 5 iš langelio B2 ir sukurs nuorodą į diapazoną A2: A5. Tada SUM gali naudoti šį diapazoną savo skaičiavimams. Jei į langelį A6 pridėsime kitą vertę, tada skaičius B2 bus atnaujintas, o mūsų SUM formulė taip pat bus automatiškai atnaujinta, įtraukiant šią naują vertę.
ĮSPĖJIMAS: „Office 2007“ įvedus lenteles, daug efektyviau saugoti duomenis lentelėje ir naudoti struktūrinę nuorodą, o ne kurti formulę, kurią naudojome šiame pavyzdyje, dėl nepastovaus INDIRECT pobūdžio. Tačiau tai gali būti atvejai, kai jums reikia sukurti elementų sąrašą ir negalite naudoti lentelės.
Dinaminė schema su INDIRECT
Paimkime ankstesnį pavyzdį ir ženkime dar vieną žingsnį. Užuot parašę formulę, kad gautume verčių sumą, mes sukursime pavadintą diapazoną. Šį diapazoną galėtume pavadinti „MyData“ ir nurodyti
| 1 | = NETIESIOGINĖ ("A2: A" ir COUNTA ($ A: $ A)) |
Atminkite, kad kadangi mes įtraukiame tai į pavadintą diapazoną, mes pakeitėme nuorodą į B2 ir vietoj to tiesiogiai įdėjome funkciją COUNTA.
Dabar, kai turime šį pavadintą diapazoną, galėtume jį naudoti diagramoje. Mes sukursime tuščią linijinę diagramą ir pridėsime duomenų seriją. Dėl serijos verčių galite parašyti kažką panašaus
| 1 | = 1 lapas! MyData |
Diagrama dabar naudos šią nuorodą sklypo vertėms. Kai prie A stulpelio pridedama daugiau reikšmių, INDIRECT nurodys vis didesnį diapazoną, o mūsų diagrama ir toliau bus nuolat atnaujinama su visomis naujai pridėtomis vertėmis.
Dinaminis duomenų patvirtinimas naudojant INDIRECT
Renkant vartotojų informaciją, kartais reikia pasirinkti vieną iš pasirinkimo variantų, priklausomai nuo ankstesnio pasirinkimo. Apsvarstykite šį išdėstymą, kuriame mūsų pirmasis stulpelis leidžia vartotojui pasirinkti vaisius, daržoves ir mėsą.
2 -ameantra stulpelyje, mes nenorime turėti didelio sąrašo, rodančio visus galimus pasirinkimus, nes mes jau šiek tiek susiaurinome dalykus. Taigi, mes sukūrėme dar 3 sąrašus, kurie atrodo taip:
Toliau priskirsime kiekvieną šie sąrašus į pavadintą diapazoną. T.y., visi vaisiai bus „Vaisiai“, o daržovės - „Daržovės“ ir kt.
Grįžę į savo lentelę, esame pasirengę nustatyti duomenų patvirtinimą 2antra stulpelis. Mes sukursime sąrašo tipo patvirtinimą, įvesdami:
| 1 | = NETIESIOGINĖ (A2) |
NETIESIOGINIS perskaitys A stulpelyje padarytą pasirinkimą ir pamatys kategorijos pavadinimą. Mes apibrėžėme diapazonus su šiais pavadinimais, todėl INDIRECT paims tą pavadinimą ir sukurs nuorodą į norimą diapazoną.
papildomi užrašai
Naudokite funkciją INDIRECT, kad sukurtumėte langelio nuorodą iš teksto.
Pirmiausia sukurkite teksto eilutę, vaizduojančią langelio nuorodą. Eilutė turi būti įprastoje A1 stiliaus stulpelio raidėje ir eilutės numeryje (M37) arba R1C1 stiliaus (R37C13). Nuorodą galite įvesti tiesiogiai, bet paprastai nurodysite langelius, kurie apibrėžia eilutes ir stulpelius. Galiausiai įveskite pasirinktą langelio nuorodos formatą. TRUE arba praleista A1 stiliaus nuorodai arba FALSE R1C1 stiliui.
Dirbdami su netiesioginėmis formulėmis, galbūt norėsite naudoti EILUTĖ funkcija norėdami gauti nuorodos eilutės numerį arba STulpelio funkcija gauti nuorodos stulpelio numerį (ne raidę).
Grįžkite į visų „Excel“ funkcijų sąrašą
NETIESIOGINIS „Google“ skaičiuoklėse
Funkcija INDIRECT „Google“ skaičiuoklėse veikia taip pat, kaip ir „Excel“:
