Program Microsoft Excel má niekoľko skvelých trikov a použitie vzorcov dynamického rozsahu SUM a INDIRECT je len dvoma spôsobmi, ako ľahko manipulovať s údajmi, ktoré máte k dispozícii.
SUM - NEPRIAMY Prehľad vzorcov
Pomocou funkcie INDIRECT v formulároch programu Excel sa jednoducho mení rozsah odkazov na bunky použitý vo vzorci bez toho, aby ste museli upravovať samotný vzorec.
INDIRECT môže byť použitá s množstvom funkcií, ktoré akceptujú odkaz na bunku ako argument, ako sú funkcie OFFSET a SUM.
V druhom prípade pomocou funkcie INDIRECT ako argument pre funkciu SUM môžete vytvoriť dynamický rozsah odkazov na bunky, ktorý sa potom pridáva do funkcie SUM.
Neprimerané to robí odkazom na údaje v bunkách nepriamo prostredníctvom strednej polohy.
Príklad: SUM - NEPRIAMY Formulár používaný na vytvorenie dynamického rozsahu hodnôt
Tento príklad je založený na údajoch uvedených na obrázku vyššie.
Vzorec SUM - NEPRIAME vytvorený pomocou nižšie uvedených tutoriálnych krokov je:
= SUM (NEPRIAME ("D" a E1 & ": D" & E2))
V tomto vzore obsahuje argument vnorenej funkcie INDIRECT odkazy na bunky E1 a E2. Čísla v týchto bunkách, 1 a 4, kombinované so zvyškom argumentu INDIRECT, tvoria odkazy na bunky D1 a D4.
V dôsledku toho je rozsah čísel, ktorý je celkovou hodnotou funkcie SUM, údajmi obsiahnutými v rozsahu buniek D1 až D4 - čo je 50.
Zmenou čísiel umiestnených v bunkách E1 a E2; rozsah, ktorý sa má dosiahnuť, sa dá ľahko zmeniť.
Tento príklad najskôr použije vyššie uvedený vzorec na doplnenie údajov v bunkách D1: D4 a potom zmení rozsah sumy na hodnotu D3: D6 bez úpravy vzorca v bunke F1.
01 z 03
Zadanie vzorca - Možnosti
Možnosti zadávania vzorca zahŕňajú:
- zadaním vyššie uvedeného vzorca priamo do bunky F1 a stlačením klávesu Enter na klávesnici
- zadaním funkcie INDIRECT ako argument pomocou dialógového okna funkcie SUM
Väčšina funkcií v programe Excel má dialógové okno, ktoré vám umožňuje zadávať jednotlivé argumenty funkcie na samostatnom riadku bez toho, aby ste sa museli obávať syntaxe .
V tomto prípade môže byť dialógové okno funkcie SUM použité na zjednodušenie vzorca do určitej miery. Keďže funkcia INDIRECT je vnorená do SUM, funkcia INDIRECT a jej argumenty musia byť stále zadané ručne.
Nasledujúce kroky používajú dialógové okno SUM pre zadanie vzorca.
Zadanie údajov výučby
Bunkové dáta D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4- Do buniek D1 až E2 zadajte nasledujúce údaje
Spustenie SUM - INDIRECT Formula - Otvorenie dialógového okna funkcie SUM
- Kliknite na bunku F1 - tu sa zobrazia výsledky tohto príkladu
- Kliknite na kartu Vzorce v ponuke pásky
- Zvoľte možnosť Math & Trig z kariet a otvorte rozbaľovací zoznam funkcií
- Kliknutím na SUM v zozname otvoríte dialógové okno funkcie
02 z 03
Zadanie funkcie INDIRECT - Kliknutím zobrazíte väčší obrázok
Negatívny vzorec musí byť zadaný ako argument pre funkciu SUM.
V prípade vnorených funkcií program Excel nepovoľuje otvorenie dialógového okna druhej funkcie a zadá jeho argumenty.
Funkcia INDIRECT sa preto musí zadávať ručne v riadku Číslo 1 dialógového okna Funkcia SUM.
- V dialógovom okne kliknite na riadok Číslo1
- Zadajte nasledujúcu funkciu INDIRECT: INDIRECT ("D" a E1 & ": D" & E2)
- Kliknutím na tlačidlo OK dokončite funkciu a zatvorte dialógové okno
- Číslo 50 by sa malo objaviť v bunke F1, pretože ide o súčet údajov nachádzajúcich sa v bunkách D1 až D4
- Po kliknutí na bunku F1 sa na formulári nad pracovným hárkom zobrazí formulár = SUM (INDIRECT ("D" & E1 & ": D" & E2)
Zlomenie funkcie INDIRECT
Aby sme vytvorili dynamický rozsah v stĺpci D pomocou metódy INDIRECT, musíme písmeno D v argumentu funkcie INDIRECT kombinovať s číslami v bunkách E1 a E2.
Toto sa dosahuje nasledujúcim spôsobom:
- ampersand ( & ) sa používa na zlučovanie alebo spojenie textových dát (v tomto prípade písmena D) s odkazom na bunku (E1 a E2)
- ďalej textové dáta, ktoré sú zlučované s odkazmi na bunky, musia byť obklopené dvojitými úvodzovkami ( "" )
- Nakoniec sú koncové body rozsahu oddelené dvojbodkou (:)
Preto je začiatočný bod rozsahu definovaný znakmi: "D" a E1 .
Druhá sada znakov: ": D" & E2 kombinuje dvojbodku s koncovým bodom. Toto sa deje, pretože dvojbodka je textový znak, a preto musí byť súčasťou uvozoviek.
Tretí ampersand v strede sa používa na spojenie dvoch častí do jedného argumentu :
"D" & E1 & ": D" & E203 z 03
Dynamické zmeny rozsahu SUM funkcie
Celý bod tohto vzorca je jednoducho zmeniť rozsah, ktorý je celkom určený funkciou SUM, bez toho, aby ste museli upravovať argument funkcie.
Zahrnutím funkcie INDIRECT do vzorca sa zmenou čísel v bunkách E1 a E2 zmení rozsah buniek čítaných funkciou SUM.
Ako je možné vidieť na obrázku vyššie, výsledkom je aj odpoveď vzorca nachádzajúca sa v bunke F1, ktorá sa mení, pretože dopĺňa nový rozsah údajov.
- Kliknite na bunku E1
- Zadajte číslo 3
- Stlačte kláves Enter na klávesnici
- Kliknite na bunku E2
- Zadajte číslo 6
- Stlačte kláves Enter na klávesnici
- Odpoveď v bunke F1 by sa mala zmeniť na 90 - čo je súčet čísel obsiahnutých v bunkách D3 až D6
- Ďalej testujte vzorec zmenou obsahu buniek B1 a B2 na ľubovoľné čísla medzi 1 a 6
INDIRECT a #REF! Hodnota chyby
#REF! hodnota chyby sa zobrazí v bunke F1, ak argument INDIRECT funkcie:
- nie je platný odkaz na bunku
- obsahuje externý odkaz na iný zošit a tento pracovný zošit nie je otvorený
- sa vzťahuje na rozsah buniek mimo limitu pracovného hárka (riadok 1 048 576 alebo stĺpec XFD)