Obsah
Na prvý pohľad (hlavne pri čítaní pomocníka) funkcia NEPRIAME (NEPRIAME) vyzerá jednoducho a dokonca zbytočne. Jeho podstatou je premeniť text, ktorý vyzerá ako odkaz, na plnohodnotný odkaz. Tie. ak potrebujeme odkazovať na bunku A1, potom môžeme buď zvyčajne vytvoriť priamy odkaz (zadať znamienko rovnosti do D1, kliknúť na A1 a stlačiť Enter), alebo môžeme použiť NEPRIAME na ten istý účel:
Upozorňujeme, že argument funkcie – odkaz na A1 – je zadaný v úvodzovkách, takže tu ide v skutočnosti o text.
"No dobre," poviete. "A aký je prínos?"
Nesúďte však podľa prvého dojmu – klame. Táto funkcia vám môže pomôcť v mnohých situáciách.
Príklad 1. Transpozícia
Klasika žánru: treba otočiť vertikálny pr
drážka do vodorovnej (transponovať). Samozrejme, môžete použiť špeciálnu vložku alebo funkciu TRANSP (TRANSPOSE) vo vzorci poľa, ale vystačíte si s naším NEPRIAME:
Logika je jednoduchá: na získanie adresy ďalšej bunky zlepíme písmeno „A“ so špeciálnym znakom „&“ a číslom stĺpca aktuálnej bunky, ktoré nám funkcia dáva. STĹPIK (STĺpec).
Opačný postup je lepšie urobiť trochu inak. Keďže tentoraz potrebujeme vytvoriť prepojenie na bunky B2, C2, D2 atď., je vhodnejšie použiť režim prepojenia R1C1 namiesto klasickej „námornej bitky“. V tomto režime sa naše bunky budú líšiť iba číslom stĺpca: B2=R1C2C2=R1C3D2 = R1C4 a tak ďalej
Tu prichádza na rad druhý voliteľný argument funkcie. NEPRIAME. Ak sa rovná KLAMEŤ (NEPRAVDA), potom môžete nastaviť adresu odkazu v režime R1C1. Takže môžeme ľahko transponovať horizontálny rozsah späť na vertikálny:
Príklad 2. Súčet podľa intervalu
Už sme analyzovali jeden spôsob sčítania cez okno (rozsah) danej veľkosti na hárku pomocou funkcie LIKVIDÁCIA (POSUN). Podobný problém je možné vyriešiť aj pomocou NEPRIAME. Ak potrebujeme zosumarizovať dáta len z určitého rozsahovo-obdobia, tak ich môžeme zlepiť z kúskov a následne z nich spraviť plnohodnotný odkaz, ktorý môžeme vložiť do funkcie SUM (SÚČET):
Príklad 3. Rozbaľovací zoznam inteligentnej tabuľky
Aplikácia Microsoft Excel niekedy nepovažuje názvy a stĺpce inteligentných tabuliek za úplné odkazy. Napríklad pri pokuse o vytvorenie rozbaľovacieho zoznamu (tab Údaje – Validácia údajov) na základe stĺpca Zamestnanci z inteligentného stola ľudia dostaneme chybu:
Ak odkaz „obalíme“ našou funkciou NEPRIAME, potom to Excel jednoducho prijme a náš rozbaľovací zoznam sa bude dynamicky aktualizovať pri pridávaní nových zamestnancov na koniec inteligentnej tabuľky:
Príklad 4. Nerozbitné odkazy
Ako viete, Excel automaticky opravuje referenčné adresy vo vzorcoch pri vkladaní alebo odstraňovaní riadkov a stĺpcov na hárku. Vo väčšine prípadov je to správne a pohodlné, ale nie vždy. Povedzme, že potrebujeme preniesť mená z adresára zamestnancov do správy:
Ak dáte bežné odkazy (do prvej zelenej bunky zadajte =B2 a skopírujte to), tak keď vymažete napríklad Dášu, dostaneme #ODKAZ! chyba v zelenej bunke, ktorá jej zodpovedá. (#REF!). V prípade použitia funkcie na vytváranie odkazov NEPRIAME nebude taký problém.
Príklad 5: Zhromažďovanie údajov z viacerých hárkov
Predpokladajme, že máme 5 listov so správami rovnakého typu od rôznych zamestnancov (Michail, Elena, Ivan, Sergej, Dmitrij):
Predpokladajme, že tvar, veľkosť, poloha a poradie tovaru a mesiacov vo všetkých tabuľkách sú rovnaké – líšia sa len čísla.
Údaje môžete zbierať zo všetkých hárkov (nesčítajte ich, ale ukladajte pod seba na „hromadu“) iba pomocou jedného vzorca:
Ako vidíte, myšlienka je rovnaká: prilepíme odkaz na požadovanú bunku daného listu a NEPRIAME premení ho na „živý“. Pre pohodlie som nad tabuľku pridal písmená stĺpcov (B, C, D) a napravo - čísla riadkov, ktoré je potrebné vziať z každého listu.
Úskalia
Ak používate NEPRIAME (NEPRIAME) musíte pamätať na jeho slabé stránky:
- Ak vytvoríte odkaz na iný súbor (prilepením názvu súboru do hranatých zátvoriek, názvu listu a adresy bunky), funguje to iba vtedy, keď je otvorený pôvodný súbor. Ak ho zatvoríme, zobrazí sa chyba #LINK!
- INDIRECT nemôže odkazovať na dynamický pomenovaný rozsah. Na statiku – žiadny problém.
- NEPRIAME je prchavá alebo „prchavá“ funkcia, tj prepočítava sa na akúkoľvek zmenu v ktorejkoľvek bunke listu, a nie iba na ovplyvňujúce bunky, ako pri normálnych funkciách. To má zlý vplyv na výkon a je lepšie sa nenechať strhnúť veľkými NEPRIAMEMI tabuľkami.
- Ako vytvoriť dynamický rozsah pomocou automatického nastavenia veľkosti
- Sčítanie cez rozsahové okno na hárku s funkciou OFFSET