Aplikace Microsoft Excel má některé skvělé triky a pomocí vzorce SUM a INDIRECT jsou dynamické rozsahové vzorce jen dva způsoby, jak snadno manipulovat s daty, které máte.
SUM - INDIRECT Přehled vzorků
Použití funkce INDIRECT v formulářích aplikace Excel usnadňuje změnu rozsahu odkazů na buňky použitých ve vzorci, aniž by bylo nutné upravovat vzorec samotný.
INDIRECT lze použít s řadou funkcí, které přijmou odkaz na buňky jako argument, například funkce OFFSET a SUM.
V posledně uvedeném případě pomocí funkce INDIRECT jako argument pro funkci SUM můžete vytvořit dynamický rozsah odkazů na buňky, který se pak přidá k funkci SUM.
Nepřímé to dělá tím, že odkazuje na data v buňkách nepřímo prostřednictvím mezilehlého umístění.
Příklad: SUM - NEPŘÍMÝ Vzorec použitý k celkovému dynamickému rozsahu hodnot
Tento příklad je založen na datech uvedených na obrázku výše.
Vzorec SUM - NEPŘÍMÝ, který byl vytvořen pomocí kroků uvedených níže, je:
= SUM (NEPŘÍMÉ ("D" & E1 & ": D" & E2))
V tomto vzorci obsahuje argument vnorené funkce INDIRECT odkazy na buňky E1 a E2. Čísla v těchto buňkách 1 a 4, ve spojení se zbytkem argumentu INDIRECTu, tvoří buňky odkazů D1 a D4.
V důsledku toho je rozsah čísel, který je součástí funkce SUM, údajem obsaženým v rozsahu buněk D1 až D4 - což je 50.
Změnou čísel umístěných v buňkách E1 a E2; Rozsah, který se má uvést, lze však snadno měnit.
Tento příklad nejprve použije výše uvedený vzorec k doplnění dat v buňkách D1: D4 a poté změní rozsah sečtených hodnot na hodnotu D3: D6 bez úpravy vzorce v buňce F1.
01 z 03
Zadání vzorce - Možnosti
Možnosti pro zadání vzorce zahrnují:
- zadáním výše uvedeného vzorce přímo do buňky F1 a stisknutím klávesy Enter na klávesnici
- zadání funkce INDIRECT jako argument pomocí dialogového okna funkce SUM
Většina funkcí v aplikaci Excel má dialogové okno, které umožňuje zadávat jednotlivé argumenty funkce na samostatném řádku, aniž byste se museli starat o syntaxi .
V tomto případě lze dialogové okno funkce SUM použít k zjednodušení vzorce do určité míry. Protože funkce INDIRECT je vnořena uvnitř SUM, funkce INDIRECT a její argumenty musí být stále zadány ručně.
Níže uvedené kroky pomocí dialogového okna SUM zadejte vzorec.
Zadání výukového data
Data buňky D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4- Zadejte následující údaje do buněk D1 až E2
Spuštění SUM - INDIRECT Formula - otevření dialogu SUM funkce
- Klikněte na buňku F1 - zde se zobrazí výsledky tohoto příkladu
- Klikněte na kartu Vzorce v nabídce pásu karet
- Z rozevíracího pásu zvolte Math & Trig a otevřete rozevírací seznam funkcí
- Klepnutím na SUM v seznamu otevřete dialogové okno funkce
02 z 03
Zadání funkce INDIRECT - Klepnutím zobrazíte větší obrázek
Neutriktický vzorec musí být zadán jako argument pro funkci SUM.
V případě vnořených funkcí aplikace Excel neumožňuje otevření dialogové okno druhé funkce a zadá její argumenty.
Proto musí být funkce INDIRECT zadána ručně v řádku Number1 v dialogovém okně funkce SUM.
- V dialogovém okně klikněte na řádek Číslo1
- Zadejte následující funkci INDIRECT: INDIRECT ("D" a E1 & ": D" a E2)
- Klepnutím na tlačítko OK dokončete funkci a zavřete dialogové okno
- Číslo 50 by se mělo zobrazit v buňce F1, protože to je součet dat umístěných v buňkách D1 až D4
- Když klepnete na buňku F1, zobrazí se ve vzorci nad tabulkou úplný vzorec = SUM (INDIRECT ("D" & E1 & ": D" & E2)
Zrušení funkce INDIRECT
Abychom vytvořili dynamický rozsah ve sloupci D pomocí funkce INDIRECT, musíme písmeno D v argumentu funkce INDIRECT s čísly obsaženými v buňkách E1 a E2.
To je dosaženo následujícím způsobem:
- symbol ampersand ( & ) se používá ke spojení nebo spojování textových dat (v tomto případě písmeno D) s odkazem na buňky (E1 a E2)
- dále textová data, která jsou spojena s odkazy na buňky, musí být obklopena dvojitými uvozovkami ( "" )
- nakonec jsou koncové body rozsahu odděleny dvojtečkou (:)
Počáteční bod rozsahu je tedy definován znaky: "D" a E1 .
Druhá sada znaků: ": D" a E2 kombinuje dvojtečku s koncovým bodem. To se děje, protože dvojtečka je textový znak, a proto musí být zahrnut do uvozovek.
Třetí ampersand ve středu se používá k spojování obou částí do jednoho argumentu :
"D" & E1 & ": D" & E203 ze dne 03
Dynamické změny rozsahu SUM funkce
Celý bod tohoto vzorce je usnadnit změnu rozsahu, který je určen funkcí SUM, aniž by bylo nutné upravit argument funkce.
Zahrnutím funkce INDIRECT ve vzorci změníte čísla v buňkách E1 a E2 změníte rozsah buněk čtených funkcí SUM.
Jak je vidět na obrázku výše, výsledkem je také odpověď vzorce nacházející se v buňce F1, která se mění, protože sečte nový rozsah dat.
- Klikněte na buňku E1
- Zadejte číslo 3
- Stiskněte klávesu Enter na klávesnici
- Klikněte na buňku E2
- Zadejte číslo 6
- Stiskněte klávesu Enter na klávesnici
- Odpověď v buňce F1 by se měla změnit na 90 - což je součet čísel obsažených v buňkách D3 až D6
- Dále testujte vzorec tak, že změníte obsah buněk B1 a B2 na libovolná čísla mezi 1 a 6
INDIRECT a #REF! Hodnota chyby
#REF! hodnota chyby se zobrazí v buňce F1, pokud argument funkce INDIRECT:
- není platný odkaz na buňku
- obsahuje externí odkaz na jiný sešit a tento sešit není otevřený
- odkazuje na rozsah buněk mimo limity listu (řádek 1,048,576 nebo sloupec XFD)