Keď viete, ako používať nepriamu funkciu v Exceli, môžete získať informácie z iných hárkov, odkazovať na pomenované rozsahy a kombinovať ich s inými funkciami, aby ste vytvorili skutočne všestranný nástroj. Môže to trvať trochu cviku, kým sa s tým vyrovnáte, ale s nepriamou funkciou dokážete viac, než by ste si mysleli.
Pokyny v tomto článku platia pre Excel pre Microsoft 365, Excel 2019 a Excel 2016.
Čo je nepriama funkcia?
Nepriama funkcia je spôsob, ako previesť textový reťazec na odkaz. To znamená, že čerpá informácie z odkazu na inú bunku alebo rozsah. Vytvára odkaz z textu a nemení sa, keď sa bunky, riadky alebo stĺpce zmenia, pridajú alebo odstránia z citovaného rozsahu. Referencie, ktoré vytvára, sa vyhodnocujú v reálnom čase, takže referencia je vždy presná s údajmi, z ktorých čerpá.
Ak sa vám to zdá trochu mätúce, netrápte sa. Nepriamy vzorec môže byť ľahšie pochopiteľný s platnými príkladmi a v praxi. Ak máte pochybnosti, skúste postupovať podľa krokov nižšie a čoskoro to pochopíte.
Používanie nepriamej funkcie s pomenovanými rozsahmi
Pomenované rozsahy v Exceli sú skvelým spôsobom, ako zhromažďovať údaje pod jedným odkazom, a nepriama funkcia uľahčuje získavanie týchto informácií z nich. Tu je návod, ako to urobiť:
-
Otvorte dokument programu Excel s už použitými pomenovanými rozsahmi. V našom príklade máme informácie o predaji rôznych jedál a nápojov, pričom peniaze zarobené za každý deň v týždni sa zbierajú v rámci pomenovaných radov s názvom podľa produktov.
Image -
Vyberte bunku pre svoj pomenovaný rozsah a vložte do nej jednu z nich. V našom príklade sme použili Burgers. Ak chcete, pridajte ďalšie názvy a vyfarbenie.
Image -
Vyberte inú bunku, do ktorej chcete vložiť nepriamy výstup. Keďže chceme sčítať celý objem predaja z týždňa pre konkrétne jedlo, v tomto prípade Burgers, do bunky napíšeme nasledovné:
=SUM(NEPRIAME(G5)
-
Toto označuje funkciu SUM, ktorá použije nepriamu funkciu na čerpanie informácií z pomenovaného rozsahu v bunke G5, v tomto prípade Burgers. Výstup je 3781, celkový predaj hamburgerov za týždeň.
V našom príklade môžeme Burgery v bunke G5 nahradiť limonádou alebo dezertmi, ďalšími dvoma menovanými rozsahmi a výstup sa namiesto toho zmení na ich súčet SUM.
Image
Používanie nepriamej funkcie na viacerých hárkoch
Nepriamy vzorec je ešte silnejší, keď ho použijete na získanie informácií z iných listov. Nemusíte na to používať ani pomenované rozsahy.
- Otvorte svoj excelový dokument s viacerými hárkami alebo ich vytvorte so všetkými potrebnými informáciami.
- V hárku, do ktorého chcete umiestniť nepriamy výstup, vytvorte bunku s názvom hárku, z ktorého chcete čerpať informácie. V našom príklade je to FoodSales.
-
Keďže chceme získať informácie z nášho FoodSales pre celkový počet predaných Burgers, napísali sme nasledovné (nahraďte rozsah buniek a názvy hárkov s vašimi vlastnými):
=SUM(NEPRIAME(B4&"!B4:B10"))
Image - Toto to označuje ako funkciu SUM, pretože sa snažíme nájsť súčet. Potom označí bunku B4 ako referenčný text pre nepriamu funkciu. & spája prvky tejto funkcie, nasleduje úvodzovka a výkričník a potom rozsah buniek, z ktorých chceme čerpať údaje. B4 až B10
- Výstup predstavuje celkový predaj Burgerov za daný týždeň. Keď vytvoríme nový hárok FoodSales2 na nový týždeň s rôznymi číslami, stačí upraviť bunku B4 tak, aby sa hovorilo FoodSales2, aby sme získali údaje o predaji burgerov za daný týždeň.
Používanie nepriamej funkcie s referenciou štýlu R1C1
Pre hárky, ktoré sa neustále rozširujú, kde odkaz, ktorý chcete použiť, nebude vždy v tej istej bunke, možno použiť odkazy na štýl R1C1 s nepriamym vzorcom, ktorý vám poskytne potrebné informácie. Naďalej tu budeme používať naše príklady predaja potravín, ale predstavte si, že je to pre pracovný hárok vyššej úrovne, ktorý sa zameriava na celkové týždenné celkové tržby.
- Otvorte dokument programu Excel so všetkými údajmi, z ktorých chcete čerpať, a vyberte bunku pre výstup nepriamej funkcie. V našom príklade sa pozeráme na celkové mesačné predaje potravín a chceme poznať najaktuálnejšie celkové predaje za daný mesiac.
-
V našom príklade vzorec vyzerá takto:
=NEPRIAME("R12C"&COUNTA(12:12), FALSE)
Image - Nepriama funkcia používa R12 (riadok 12), za ktorým nasleduje C na označenie stĺpca, uzavretého v úvodzovkách. & spája dve časti funkcie dohromady. Funkciu COUNTA používame na spočítanie všetkých neprázdnych buniek v riadku 12 (výberom riadka alebo zadaním pomeru 12:12), za ktorými nasleduje čiarka. FALSE to označuje ako referenciu R1C1.
-
Výstup je potom posledný záznam v našej tabuľke, v tomto prípade 8102 alebo 8 102 USD. Keď nakoniec pridáme aprílové údaje o predaji, najnovšie predajné číslo sa automaticky aktualizuje v reálnom čase.
Image
