Prenesite primer delovnega zvezka
Ta vadnica prikazuje, kako uporabljati Excel INDIRECT funkcija v Excelu za ustvarjanje sklica na celico iz besedila.
INDIRECT Pregled funkcije
Funkcija INDIRECT Ustvari sklic na celico iz besedilnega niza.
(Upoštevajte, kako so prikazani vnosi formule)
INDIRECT funkcija Sintaksa in vhodi:
| 1 | = INDIRECT (ref_text, C1) |
ref_text - Niz, ki predstavlja sklic na celico ali referenco območja. Niz je lahko v formatu R1C1 ali A1 ali pa je imenovan obseg.
a1 - NEOBVEZNO: Označuje, ali je referenca v formatu R1C1 ali A1. FALSE za R1C1 ali TRUE / Opusti za A1.
Kaj je funkcija INDIRECT?
S funkcijo INDIRECT lahko podate besedilni niz in računalnik ta niz razlaga kot dejansko referenco. To lahko uporabite za sklicevanje na obseg na istem listu, drugem listu ali celo v drugem delovnem zvezku.
POZOR: Funkcija INDIRECT je ena od hlapnih funkcij. Večino časa, ko delate v preglednici, bo računalnik ponovno preračunal formulo le, če so vnosi spremenili svoje vrednosti. Hlapna funkcija pa se preračuna vsak čas, ko spremenite katero koli celico. Bodite previdni, da zagotovite, da ne boste povzročili velikega časa ponovnega izračuna zaradi pretirane uporabe hlapnih funkcij ali številnih celic, ki so odvisne od rezultata hlapne funkcije.
Ustvarite referenco celice
Recimo, da želite pridobiti vrednost iz A2, vendar se prepričajte, da je vaša formula ostane na A2 ne glede na vstavljanje/odstranjevanje novih vrstic. Lahko bi napisali formulo
| 1 | = INDIRECT ("A2") |
Upoštevajte, da je argument znotraj naše funkcije besedilni niz »A2« in ne sklic na celico. Ker je to besedilni niz, vam ni treba navesti absolutnega sklica, kot je $ A $ 2. Besedilo se ne bo nikoli spremenilo, zato bo ta formula vedno kazala na A2, ne glede na to, kam se premakne.
Neposredna številka vrstice
Besedilne nize in vrednosti lahko združite iz celic skupaj. Namesto da bi pisali »A2«, kot smo to storili prej, lahko iz celice B2 vzamemo številčno vrednost in jo uporabimo v formuli. Formulo bi zapisali tako
| 1 | = INDIRECT ("A" & B2) |
Simbol »&« se tukaj uporablja za združevanje besedilnega niza »A« z vrednostjo iz celice B2. Če bi bila vrednost B2 trenutno 10, bi se naša formula tako glasila
| 123 | = INDIRECT ("A" & 10)= INDIRECT ("A10")= A10 |
INDIRECT vrednost stolpca
Lahko se tudi povežete v sklic na stolpec. Tokrat recimo, da vemo, da želimo zajeti vrednost iz vrstice 10, vendar želimo spremeniti, iz katerega stolpca potegniti. Želeno črko stolpca bomo vstavili v celico B2. Naša formula bi lahko izgledala tako
| 1 | = NEPOSREDNO (B2 in "10") |
Če je vrednost B2 "G", potem naša formula tako ocenjuje
| 123 | = INDIRECT ("G" & 10)= INDIRECT ("G10")= G10 |
POSREDNO slog r1c1
V našem prejšnjem primeru smo morali uporabiti črko za označevanje sklica na stolpec. To je zato, ker smo uporabljali tako imenovano referenciranje v slogu A1. V slogu A1 so stolpci podani s črko, vrstice pa s številkami. Absolutne reference so označene z "$" pred postavko, za katero želimo, da ostane absolutna.
V r1c1 se tako vrstice kot stolpci zaženejo s številko. Absolutno sklicevanje na a1 bi zapisali kot
| 1 | = R1C1 |
To lahko preberete kot "Vrstica 1, stolpec 1". Relativne reference so podane z oklepaji, vendar številka označuje položaj glede na celico s formulo. Če bi torej pisali formulo v celico A10 in se moramo sklicevati na A1, bi formulo zapisali
| 1 | = R [-9] C |
To lahko preberete kot »Celica ima 9 vrstic navzgor, vendar v istem stolpcu.
Razlog za to je lahko v tem, da lahko INDIRECT podpira uporabo zapisa r1c1. Razmislite o prejšnjem primeru, ko smo pridobivali vrednost iz vrstice 10, vendar smo želeli spremeniti stolpec. Namesto da bi dali črko, recimo, da smo v celico B2 vnesli številko. Naša formula bi potem lahko izgledala tako
| 1 | = INDIRECT ("R10C" & B2, FALSE) |
2 smo izpustilind argument do sedaj. Če je ta argument izpuščen ali True, bo funkcija ocenjena s slogom A1. Ker je napačno, bo ocenilo v r1c1. Predpostavimo, da je vrednost B2 5. Naša formula bo tako ocenila
| 12 | = INDIRECT ("R10C5", FALSE)= 10 E E $ 10 |
POSREDNE razlike z A1 proti r1c1
Se spomnite, da smo prejšnjič pokazali, da ker je bila vsebina te formule besedilni niz, se ni nikoli spremenila?
| 1 | = INDIRECT ("A2") |
Ta formula bo vedno gledala celico A2, ne glede na to, kje formulo premaknete. V r1c1, ker lahko relativni položaj navedete z oklepaji, to pravilo ne ostane dosledno. Če to formulo postavite v celico B2
| 1 | = INDIRECT ("RC [-1]") |
Gledala bo celico A2 (ker je stolpec A levo od stolpca B). Če kopirate to formulo v celico B3, bo besedilo v njej ostalo enako, vendar bo INDIRECT zdaj gledal v celico A3.
INDIRECT z imenom lista
Ime lista lahko združite tudi v INDIREKTNE reference. Pomembno pravilo, ki si ga morate zapomniti, je, da okrog imen postavite enojne narekovaje, ime lista pa morate ločiti od sklica celice s klicajem.
Recimo, da smo imeli to nastavitev, kjer navajamo ime lista, vrstico in stolpec.
Naša formula, da vse to združimo v sklic, bi izgledala tako:
| 1 | = INDIRECT ("'" & A2 & "'!" & B2 & C2) |
Naša formula bo nato ocenjena tako:
| 123 | = INDIRECT ("'" & "Sheet2" & "'!" & "B" & "5")= INDIRECT ("'" List2'! B5 ")= 'List2'! B5 |
Tehnično, ker beseda »List2« nima nobenih presledkov, nimamo potreba enojnih narekovajev. Popolnoma velja, če napišete kaj takega
| 1 | = List2! A2 |
Vendar pa ne škodi postaviti narekovajev, ko jih ne potrebujete. Najboljša praksa je, da jih vključite, da bo vaša formula lahko obravnavala primere, kjer bodo morda potrebni.
POSREDNO na drug delovni zvezek
Omenili bomo tudi, da lahko INDIRECT ustvari sklic na drug delovni zvezek. Omejitev je, da INDIRECT ne bo pridobival vrednosti iz zaprtega delovnega zvezka, zato je ta posebna uporaba omejena. Če je delovni zvezek, na katerega kaže INDIRECT, neodprt, bo funkcija vrgla »#REF!« napaka.
Sintaksa pri pisanju imena delovnega zvezka je, da mora biti v oglatih oklepajih. Uporabimo to nastavitev in poskusimo pridobiti vrednost iz celice C7.
Naša formula bi bila
| 1 | = INDIRECT ("'[" & A2 & "]" & B2 & "'! C7") |
Še enkrat bodite pozorni na umestitev enojnih narekovajev, oklepajev in klicaja. Naša formula bo nato ocenjena tako:
| 123 | = INDIRECT ("'[" & "Sample.xlsx" & "]" & "Povzetek" & "'! C7")= INDIRECT ("'[Sample.xslx] Povzetek'! C7")= '[Vzorec.xlsx] Povzetek'! C7 |
INDIRECT za izgradnjo dinamičnega razpona
Ko imate velik nabor podatkov, je pomembno, da formule poskusite optimizirati, tako da ne opravijo več dela, kot je potrebno. Na primer, namesto da bi se sklicevali na ves stolpec A, bi se morda radi sklicevali na natančno število celic na našem seznamu. Razmislite o naslednji postavitvi:
V celico B2 smo postavili formulo
| 1 | = COUNTA (A: A) |
Funkcijo COUNTA računalnik zelo enostavno izračuna, saj preprosto preveri, koliko celic v stolpcu A ima neko vrednost, v nasprotju s tem, da mora opraviti kakršna koli logična preverjanja ali matematične operacije.
Zdaj zgradimo našo formulo, ki bo seštela vrednosti v stolpcu A, vendar se želimo prepričati, da gleda samo natančno območje z vrednostmi (A2: A5). Formulo bomo zapisali kot
| 1 | = SUM (INDIREKTNO ("A2: A" & B2)) |
Naš INDIRECT bo zajel številko 5 iz celice B2 in ustvaril sklic na obseg A2: A5. SUM lahko nato uporabi to območje za svoj izračun. Če v celico A6 dodamo drugo vrednost, se bo število v B2 posodobilo, naša formula SUM pa se bo samodejno posodobila in vključila to novo vrednost.
POZOR: Z uvedbo tabel v Office 2007 je veliko učinkovitejše shranjevanje podatkov v tabelo in uporaba strukturnih referenc, ne pa oblikovanje formule, ki smo jo uporabili v tem primeru zaradi nestanovitne narave INDIRECT. Lahko pa so primeri, ko morate ustvariti seznam elementov in ne morete uporabiti tabele.
Dinamični grafikoni z INDIRECT
Vzemimo prejšnji primer in naredimo še en korak. Namesto da napišemo formulo za vsoto vrednosti, bomo ustvarili imenovano območje. Ta obseg bi lahko poimenovali »MyData« in se nanj skliceval
| 1 | = INDIRECT ("A2: A" & COUNTA ($ A: $ A)) |
Upoštevajte, da smo to umestili v imenovano območje, zato smo sklicevanje na B2 zamenjali in namesto tega neposredno vnesli funkcijo COUNTA.
Zdaj, ko imamo to imenovano območje, bi ga lahko uporabili v grafikonu. Ustvarili bomo prazen grafikon vrstic in nato dodali niz podatkov. Za vrednosti serije lahko napišete nekaj takega
| 1 | = List1! MyData |
Ta grafikon bo zdaj uporabil to referenco za vrednosti ploskev. Ker je v stolpec A dodanih več vrednosti, se bo INDIRECT nanašalo na vse večji obseg, naš grafikon pa se bo še naprej posodabljal z vsemi novo dodanimi vrednostmi.
Dinamično preverjanje podatkov z INDIRECT
Pri zbiranju vnosov uporabnikov je včasih treba izbrati eno možnost izbire, odvisno od prejšnje izbire. Razmislite o tej postavitvi, kjer naš prvi stolpec uporabniku omogoča izbiro med sadjem, zelenjavo in mesom.
V 2nd stolpec, ne želimo imeti velikega seznama, ki prikazuje vse možne izbire, saj smo stvari že nekoliko zožili. Ustvarili smo torej 3 druge sezname, ki izgledajo takole:
Nato bomo vsakemu dodelili te sezname v imenovano območje. To pomeni, da bo vse sadje v območju, imenovanem "Sadje", zelenjava pa v "Zelenjava" itd.
Nazaj v tabeli smo pripravljeni za nastavitev preverjanja podatkov v 2nd stolpec. Ustvarili bomo preverjanje vrste seznama z vnosom:
| 1 | = NEPOSREDNO (A2) |
INDIRECT bo prebral izbiro v stolpcu A in si ogledal ime kategorije. Določili smo obsege s temi imeni, zato bo INDIRECT to ime prevzel in ustvaril sklic na želeni obseg.
Dodatne opombe
S funkcijo INDIRECT ustvarite sklic na celico iz besedila.
Najprej ustvarite niz besedila, ki predstavlja sklic na celico. Niz mora biti v običajni črki in številki stolpca v slogu A1 (M37) ali v slogu R1C1 (R37C13). Referenco lahko vnesete neposredno, običajno pa se sklicujete na celice, ki določajo vrstice in stolpce. Nazadnje vnesite referenčno obliko celice, ki jo izberete. TRUE ali Opuščeno za referenco v slogu A1 ali FALSE za slog R1C1.
Pri delu s formulami INDIRECT boste morda želeli uporabiti Funkcija ROW da dobite številko vrstice sklica ali STOLPEK Funkcija da dobite številko stolpca (ne črko) sklica.
Vrnite se na seznam vseh funkcij v Excelu
POSREDNO v Google Preglednicah
Neposredna funkcija deluje v Google Preglednicah popolnoma enako kot v Excelu:
