Preuzmite primjer radne knjige
Ovaj vodič prikazuje kako se koristi Excel INDIREKTNA funkcija u Excelu za stvaranje referencije ćelije od teksta.
INDIREKTAN Pregled funkcija
Funkcija INDIRECT Stvara referencu ćelije iz tekstualnog niza.
(Obratite pažnju na to kako se pojavljuju unosi formule)
INDIRECT funkcija Sintaksa i ulazi:
| 1 | = INDIREKTNO (ref_text, C1) |
ref_text - Niz koji predstavlja referencu ćelije ili referencu raspona. Niz može biti u R1C1 ili A1 formatu ili može biti imenovani raspon.
a1 - OPCIONALNO: Pokazuje je li referenca u R1C1 ili A1 formatu. FALSE za R1C1 ili TRUE / Izostavljeno za A1.
Što je INDIREKTNA funkcija?
Funkcija INDIRECT omogućuje vam davanje tekstualnog niza i da računalo interpretira taj niz kao stvarnu referencu. To se može koristiti za referenciranje raspona na istom listu, drugom listu ili čak drugoj radnoj knjizi.
OPREZ: INDIREKTNA funkcija jedna je od hlapivih funkcija. Većinu vremena dok radite u proračunskoj tablici, računalo će ponovno izračunati formulu samo ako su unosi promijenili svoje vrijednosti. Nestabilna funkcija, međutim, preračunava se svaki vrijeme kada promijenite bilo koju ćeliju. Treba biti oprezan kako ne biste uzrokovali veliko vrijeme ponovnog izračuna zbog pretjerane uporabe hlapivih funkcija ili zbog toga što mnoge ćelije ovise o rezultatu isparljive funkcije.
Izradite referencu ćelije
Recite da želite dohvatiti vrijednost iz A2, ali želite biti sigurni da je vaša formula ostaje na A2 bez obzira na to da li se novi redovi ubacuju/uklanjaju. Mogli biste napisati formulu
| 1 | = INDIREKTNO ("A2") |
Imajte na umu da je argument unutar naše funkcije tekstualni niz "A2", a ne referenca ćelije. Također, budući da je ovo tekstualni niz, nema potrebe navoditi apsolutnu referencu poput $ A $ 2. Tekst se nikada neće promijeniti, pa će stoga ova formula uvijek pokazivati na A2, bez obzira na to gdje se premjestila.
INDIREKTAN broj retka
Možete povezati tekstualne nizove i vrijednosti iz ćelija zajedno. Umjesto pisanja "A2" kao što smo prethodno radili, možemo uzeti numeričku vrijednost iz ćelije B2 i upotrijebiti je u našoj formuli. Formulu bismo napisali poput
| 1 | = INDIREKTNO ("A" & B2) |
Simbol “&” ovdje se koristi za spajanje tekstualnog niza “A” s vrijednošću iz ćelije B2. Dakle, ako je vrijednost B2 trenutno 10, tada bi naša formula glasila ovako
| 123 | = INDIREKTNO ("A" & 10)= INDIREKTNO ("A10")= A10 |
INDIRECT vrijednost stupca
Također možete spojiti u referenci stupca. Ovaj put recimo da znamo da želimo dohvatiti vrijednost iz retka 10, ali želimo biti u mogućnosti promijeniti iz kojeg se stupca povući. Slovo stupca koje želimo stavit ćemo u ćeliju B2. Naša formula bi mogla izgledati ovako
| 1 | = INDIREKTNO (B2 i "10") |
Ako je vrijednost B2 "G", tada naša formula tako procjenjuje
| 123 | = INDIREKTNO ("G" & 10)= INDIREKTNO ("G10")= G10 |
INDIREKTAN stil r1c1
U našem prethodnom primjeru morali smo upotrijebiti slovo za označavanje reference stupca. To je zato što smo koristili ono što je poznato kao referenciranje u stilu A1. U stilu A1, stupci su dati slovom, a redovi brojevima. Apsolutne reference označene su znakom “$” prije stavke za koju želimo da ostane apsolutna.
U r1c1, i retci i stupci pokreću se pomoću broja. Apsolutna referenca na a1 napisala bi se kao
| 1 | = R1C1 |
Ovo možete pročitati kao "Red 1, stupac 1". Relativne reference date su u zagradama, ali broj označava položaj u odnosu na ćeliju s formulom. Dakle, ako smo pisali formulu u ćeliju A10 i morali smo se pozvati na A1, napisali bismo formulu
| 1 | = R [-9] C |
Ovo možete pročitati kao „Ćelija 9 redaka gore, ali u istom stupcu.
Razlog zbog kojeg bi ovo moglo biti korisno je taj što INDIRECT može podržati upotrebu zapisa r1c1. Razmotrimo prethodni primjer gdje smo dohvaćali vrijednost iz retka 10, ali smo htjeli moći promijeniti stupac. Umjesto da damo slovo, recimo da smo stavili broj u ćeliju B2. Naša bi formula tada mogla izgledati ovako
| 1 | = INDIREKTNO ("R10C" & B2, FALSE) |
Izostavili smo 2nd svađa do sada. Ako je ovaj argument izostavljen ili True, funkcija će procijeniti pomoću stila A1. Budući da je lažno, to će se procijeniti u r1c1. Pretpostavimo da je vrijednost B2 5. Naša formula će to ovako ocijeniti
| 12 | = INDIREKTNO ("R10C5", FALSE)= 10 E E $ 10 |
IZREDNE razlike s A1 vs r1c1
Sjećate se da smo prethodno pokazali da budući da je sadržaj ove formule tekstualni niz, nikada se nije promijenio?
| 1 | = INDIREKTNO ("A2") |
Ova će formula uvijek gledati u ćeliju A2, bez obzira na to gdje formulu premjestite. U r1c1, budući da relativni položaj možete naznačiti pomoću zagrada, ovo pravilo ne ostaje dosljedno. Ako ovu formulu postavite u ćeliju B2
| 1 | = INDIREKTNO ("RC [-1]") |
Gledati će ćeliju A2 (budući da je stupac A jedan lijevo od stupca B). Ako kopirate ovu formulu u ćeliju B3, unutrašnji tekst će ostati isti, ali INDIRECT će sada gledati ćeliju A3.
INDIRECT s nazivom lista
Također možete kombinirati naziv lista u svoje IZREDNE reference. Važno pravilo koje treba zapamtiti je da oko imena postavite pojedinačne navodnike, a naziv lista morate odvojiti od reference ćelije uzvičnikom.
Recimo da smo imali ovu postavku, gdje navodimo naziv lista, redak i stupac.
Naša formula za kombiniranje svega ovoga u referencu izgledala bi ovako:
| 1 | = INDIREKTNO ("'" & A2 & "'!" & B2 & C2) |
Naša će se formula tada ocijeniti ovako:
| 123 | = INDIREKTNO ("'" & "List2" & "'!" & "B" & "5")= INDIRECT ("'" List2'! B5 ")= 'List2'! B5 |
Tehnički, budući da riječ "List 2" nema nikakvih razmaka, nemamo potreba jednostruki navodnici. Potpuno je valjano napisati nešto poput
| 1 | = List2! A2 |
Međutim, ne škodi staviti navodnike kad vam ne trebaju. Najbolja je praksa da ih uključite kako bi vaša formula mogla obraditi instance gdje bi mogle biti potrebne.
INDIREKTNO u drugu radnu knjigu
Također ćemo spomenuti da INDIRECT može stvoriti referencu na drugu radnu knjigu. Ograničenje je da INDIRECT neće dohvatiti vrijednosti iz zatvorene radne knjige, pa je ova upotreba ograničene praktičnosti. Ako se radna knjiga na koju pokazuje INDIRECT neotvorena, funkcija će baciti "#REF!" pogreška.
Sintaksa pri pisanju naziva radne knjige je da mora biti u uglatim zagradama. Upotrijebimo ovu postavku i pokušajmo dohvatiti vrijednost iz ćelije C7.
Naša formula bila bi
| 1 | = INDIREKTNO ("'[" & A2 & "]" & B2 & "'! C7") |
Opet, obratite pozornost na postavljanje pojedinačnih navodnika, zagrada i uskličnika. Naša će se formula tada ocijeniti ovako:
| 123 | = INDIRECT ("'[" & "Sample.xlsx" & "]" & "Sažetak" & "'! C7")= INDIRECT ("'[Sample.xslx] Sažetak'! C7")= '[Uzorak.xlsx] Sažetak'! C7 |
INDIRECT za izgradnju dinamičkog raspona
Kad imate veliki skup podataka, važno je pokušati optimizirati formule tako da ne rade više posla nego što je potrebno. Na primjer, umjesto da upućujemo na cijeli stupac A, možda bismo htjeli samo referencirati točan broj ćelija na našem popisu. Uzmite u obzir sljedeći izgled:
U ćeliju B2 smjestili smo formulu
| 1 | = COUNTA (A: A) |
Računalu je funkcija COUNTA vrlo jednostavna za izračunavanje, jer jednostavno provjerava koliko ćelija u stupcu A ima neku vrijednost, za razliku od toga da mora raditi bilo kakve logičke provjere ili matematičke operacije.
Sada, izgradimo našu formulu koja će zbrajati vrijednosti u stupcu A, ali želimo biti sigurni da gleda samo točan raspon s vrijednostima (A2: A5). Napisat ćemo formulu kao
| 1 | = ZBIR (INDIREKTNO ("A2: A" & B2)) |
Naš INDIRECT će zgrabiti broj 5 iz ćelije B2 i stvorit će referencu na raspon A2: A5. SUM tada može koristiti ovaj raspon za svoj izračun. Ako u ćeliju A6 dodamo još jednu vrijednost, tada će se broj u B2 ažurirati, a naša formula SUM također će se automatski ažurirati kako bi uključila tu novu vrijednost.
OPREZ: Uvođenjem tablica u sustav Office 2007 mnogo je učinkovitije pohraniti vaše podatke u tablicu i koristiti strukturnu referencu, a ne graditi formulu koju smo koristili u ovom primjeru zbog promjenjive prirode INDIRECT. Međutim, oni mogu biti slučajevi u kojima morate stvoriti popis stavki, a ne možete koristiti tablicu.
Dinamički grafikon s INDIRECT
Uzmimo prethodni primjer i učinimo još jedan korak. Umjesto da napišemo formulu koja će nam dati zbroj vrijednosti, mi ćemo stvoriti Imenovani raspon. Taj bismo raspon mogli nazvati “MyData” i pozivati ga
| 1 | = INDIREKTNO ("A2: A" & COUNTA ($ A: $ A)) |
Imajte na umu da smo, budući da ovo stavljamo u Named Range, zamijenili referencu na B2 i umjesto toga izravno stavili funkciju COUNTA.
Sada kada imamo taj imenovani raspon, mogli bismo ga koristiti u grafikonu. Izradit ćemo prazan grafikon, a zatim dodati niz podataka. Za vrijednosti serije možete napisati nešto poput
| 1 | = List1! MyData |
Grafikon će sada koristiti ovu referencu za vrijednosti grafikona. Kako se u stupac A dodaju više vrijednosti, INDIRECT će se odnositi na sve veći raspon, a naš će se grafikon nastaviti ažurirati sa svim novo dodanim vrijednostima.
Dinamička provjera podataka s INDIRECT
Pri prikupljanju unosa od korisnika, ponekad postoji potreba za odabirom jednog izbora ovisno o prethodnom izboru. Razmislite o ovom rasporedu, gdje naš prvi stupac omogućuje korisniku da bira između voća, povrća i mesa.
U 2nd stupcu, ne želimo imati veliki popis koji prikazuje sve moguće izbore, jer smo već malo suzili stvari. Dakle, stvorili smo još 3 popisa koji izgledaju ovako:
Zatim ćemo dodijeliti svaki od njih ove popise u imenovani raspon. Tj., Sve će voće biti u asortimanu zvanom "Voće", a povrće u "Povrću" itd.
Natrag u našu tablicu, spremni smo za postavljanje provjere valjanosti podataka u 2nd stupac. Izradit ćemo provjeru vrste popisa s unosom:
| 1 | = INDIREKTNO (A2) |
INDIRECT će pročitati izbor napravljen u stupcu A i vidjeti naziv kategorije. Definirali smo raspone s tim nazivima, pa će INDIRECT tada uzeti taj naziv i stvoriti referencu na željeni raspon.
dodatne napomene
Upotrijebite funkciju INDIRECT za stvaranje referencije ćelije iz teksta.
Prvo stvorite niz teksta koji predstavlja referencu ćelije. Niz mora biti u uobičajenom slovu stupca i broju retka u stilu A1 (M37) ili u stilu R1C1 (R37C13). Referencu možete upisati izravno, ali obično ćete referencirati ćelije koje definiraju retke i stupce. Na kraju unesite koji referentni format ćelije odaberete. TRUE ili Izostavljeno za referencu u stilu A1 ili FALSE za stil R1C1.
Tijekom rada s INDIRECT formulama možda ćete htjeti koristiti ROW funkcija da biste dobili broj retka reference ili COLUMN funkcija da biste dobili broj stupca (ne slova) reference.
Povratak na popis svih funkcija u Excelu
IZREDNO u Google tablicama
INDIREKTNA funkcija radi potpuno isto u Google tablicama kao i u Excelu:
