vsebina
Na prvi pogled (še posebej ob branju pomoči) funkcija INDIRECT (POSREDNO) izgleda preprosto in celo nepotrebno. Njegovo bistvo je spremeniti besedilo, ki je videti kot povezava, v popolno povezavo. Tisti. če se moramo sklicevati na celico A1, potem lahko običajno naredimo neposredno povezavo (vnesemo enačaj v D1, kliknemo A1 in pritisnemo Enter) ali pa uporabimo INDIRECT za isti namen:
Upoštevajte, da je argument funkcije – sklic na A1 – vnesen v narekovajih, tako da je to dejansko besedilo.
"No, v redu," rečeš. "In kakšna je korist?"
Vendar ne sodite po prvem vtisu - varljiv je. Ta funkcija vam lahko pomaga v številnih situacijah.
Primer 1. Transponiranje
Klasika žanra: obrniti morate navpični dia
utor v vodoravno (transponiranje). Seveda lahko uporabite poseben vložek ali funkcijo TRANSP (PRENOS) v matrični formuli, vendar lahko dobite z našo INDIRECT:
Logika je preprosta: da dobimo naslov naslednje celice, zlepimo črko "A" s posebnim znakom "&" in številko stolpca trenutne celice, ki nam jo daje funkcija STOLPEC (STOLPEC).
Obratni postopek je bolje narediti nekoliko drugače. Ker moramo tokrat oblikovati povezavo do celic B2, C2, D2 itd., je bolj priročno uporabiti način povezave R1C1 namesto klasične "morske bitke". V tem načinu se naše celice razlikujejo le po številki stolpca: B2=R1C2, C2=R1C3, D2=R1C4 in tako naprej
Tukaj nastopi drugi izbirni argument funkcije. INDIRECT. Če je enaka LAŽI (FALSE), potem lahko nastavite naslov povezave v načinu R1C1. Tako lahko enostavno prestavimo vodoravno območje nazaj v navpično:
Primer 2. Seštej po intervalu
En način seštevanja po oknu (razponu) dane velikosti na listu smo že analizirali z uporabo funkcije ODSTRANJEVANJE (ZAMIK). Podoben problem je mogoče rešiti tudi z uporabo INDIRECT. Če moramo povzeti podatke samo iz določenega obsega-obdobja, potem jih lahko zlepimo iz kosov in nato spremenimo v polnopravno povezavo, ki jo lahko vstavimo v funkcijo SUM (SUM):
Primer 3. Spustni seznam pametnih tabel
Včasih Microsoft Excel imen in stolpcev pametnih tabel ne obravnava kot polne povezave. Tako na primer, ko poskušate ustvariti spustni seznam (zavihek Podatki – Preverjanje podatkov) na podlagi stolpca Zaposleni iz pametne mize ljudje dobili bomo napako:
Če povezavo »ovijemo« z našo funkcijo INDIRECT, potem ga bo Excel zlahka sprejel in naš spustni seznam se bo ob dodajanju novih zaposlenih na konec pametne tabele dinamično posodabljal:
Primer 4. Nezlomljive povezave
Kot veste, Excel samodejno popravi referenčne naslove v formulah pri vstavljanju ali brisanju vrstic-stolpcev na listu. V večini primerov je to pravilno in priročno, vendar ne vedno. Recimo, da moramo imena iz imenika zaposlenih prenesti v poročilo:
Če postavite navadne povezave (v prvo zeleno celico vnesite =B2 in jo kopirajte dol), potem ko izbrišete na primer Dašo, bomo dobili #POVEZAVA! napaka v zeleni celici, ki ustreza njej. (#REF!). V primeru uporabe funkcije za ustvarjanje povezav INDIRECT te težave ne bo.
Primer 5: Zbiranje podatkov iz več listov
Recimo, da imamo 5 listov s poročili iste vrste različnih zaposlenih (Mihail, Elena, Ivan, Sergej, Dmitrij):
Predpostavimo, da so oblika, velikost, položaj in zaporedje blaga in mesecev v vseh tabelah enaki – razlikujejo se le številke.
Podatke lahko zbirate z vseh listov (ne seštevajte jih, ampak jih zložite eno pod drugo na “kup”) z eno samo formulo:
Kot lahko vidite, je ideja enaka: prilepimo povezavo na želeno celico danega lista in INDIRECT spremeni v "živo". Za udobje sem nad tabelo dodal črke stolpcev (B,C,D), na desni pa številke vrstic, ki jih je treba vzeti iz vsakega lista.
Pasti
Če uporabljate INDIRECT (POSREDNO) morate se spomniti njegovih slabosti:
- Če se povežete z drugo datoteko (z lepljenjem imena datoteke v oglatih oklepajih, imena lista in naslova celice), potem deluje le, ko je izvirna datoteka odprta. Če ga zapremo, dobimo napako #LINK!
- INDIRECT se ne more sklicevati na dinamični imenovani obseg. Na statiki – ni problema.
- INDIRECT je nestanovitna ali "nestanovitna" funkcija, tj. preračunana je za vsako spremembo v kateri koli celici lista in ne samo za vplivanje na celice, kot pri običajnih funkcijah. To slabo vpliva na zmogljivost in bolje je, da se ne zanesete z velikimi INDIRECT tabelami.
- Kako ustvariti dinamični razpon s samodejnim določanjem velikosti
- Seštevanje čez okno obsega na listu s funkcijo OFFSET