Excel Pro Rozhodování,

kromě vzorců je dalším způsobem provádění matematických výpočtů v aplikaci Excel prostřednictvím funkcí. Statistické funkce aplikují matematický proces na skupinu buněk v listu. Funkce SUM se například používá k přidání hodnot obsažených v rozsahu buněk. Seznam běžně používaných statistických funkcí je uveden v tabulce 2.4. Funkce jsou účinnější než vzorce, když aplikujete matematický proces na skupinu buněk. Pokud použijete vzorec pro přidání hodnot v rozsahu buněk, budete muset přidat každé umístění buňky do vzorce po jednom. To může být velmi časově náročné, pokud musíte přidat hodnoty do několika stovek umístění buněk. Pokud však používáte funkci, můžete zvýraznit všechny buňky, které obsahují hodnoty, které chcete sečíst, v jediném kroku. Tato část ukazuje řadu statistických funkcí, které přidáme do sešitu osobního rozpočtu. Kromě demonstrace funkcí, tato část také hodnotí procento celkových výpočtů a použití absolutních referencí.

Tabulka 2.4 Běžně Používané Statistické Funkce

SOUČET funkce se používá, když potřebujete vypočítat součty pro oblast buněk nebo skupiny vybraných buněk na listu. Pokud jde o pracovní list s podrobnostmi o rozpočtu, použijeme funkci SUM pro výpočet součtů v řádku 12. Je důležité si uvědomit, že existuje několik metod pro přidání funkce do listu, které budou demonstrovány po zbytek této kapitoly. Následující text ukazuje, jak lze funkci Přidat do listu zadáním do umístění buňky:

1. klepnutím na kartu detail listu rozpočtu otevřete list.
2. klikněte na buňku C12.
3. zadejte znaménko rovnosti =.
4. zadejte název funkce SUM.
5. zadejte otevřenou závorku (.
6. klikněte na buňku C3 a přetáhněte dolů do buňky C11. Tím se do funkce umístí rozsah C3:C11.
7. zadejte uzavírací závorku).
8. stiskněte klávesu ENTER. Funkce vypočítá celkovou částku za sloupec měsíční útraty, což je 1 496$.

obrázek 2.11 ukazuje vzhled funkce SUM přidané do listu podrobností rozpočtu před stisknutím klávesy ENTER.

jak je znázorněno na obrázku 2.11, funkce SUM byla přidána do buňky C12. Tato funkce je však také potřebná pro výpočet součtů ve sloupcích ročních výdajů a výdajů. Funkci lze zkopírovat a vložit do těchto umístění buněk z důvodu relativního odkazu. Relativní odkazování slouží pro funkce stejnému účelu jako pro vzorce. Následující text ukazuje, jak je dokončen celkový řádek:

1. v listu podrobností o rozpočtu klikněte na buňku C12.
2. klikněte na tlačítko Kopírovat na kartě Domovská stránka pásu karet.
3. zvýrazněte buňky D12 a E12.
4. klikněte na tlačítko Vložit na kartě Domovská stránka pásu karet. To vloží funkci součtu do buněk D12 a E12 a vypočítá součty pro tyto sloupce.
5. klikněte na buňku F11.
6. klikněte na tlačítko Kopírovat na kartě Domovská stránka pásu karet.
7. klepněte na buňku F12 a poté na kartě Domů na pásu karet klepněte na tlačítko Vložit. Protože nyní máme součty v řádku 12, můžeme do tohoto řádku vložit vzorec procentuální změny.

obrázek 2.12 ukazuje výstup funkce SUM, která byla přidána do buněk C12, D12 a E12. Kromě toho byl vzorec procentní změny zkopírován a vložen do buňky F12. Všimněte si, že tato verze rozpočtu plánuje ve srovnání s loňským rokem pokles výdajů o 1,7%.

absolutní reference (výpočet procenta součtů)

datový soubor: pokračujte s osobním rozpočtem CH2.

Od součty byly přidány do řádku 12 Rozpočet, Detail listu, procent z celkového výpočtu mohou být přidány do Sloupce B začátku v buňce B3. Procento celkového výpočtu ukazuje procento pro každou hodnotu ve sloupci roční výdaje s ohledem na celkovou hodnotu v buňce D12. Po vytvoření vzorce však bude nutné před kopírováním a vložením vzorce do ostatních umístění buněk ve sloupci vypnout funkci relativního odkazování aplikace Excel. Vypnutí funkce relativního odkazování aplikace Excel se provádí pomocí absolutního odkazu. Následující kroky vysvětlují, jak se to dělá:

1. v listu s podrobnostmi o rozpočtu klikněte na buňku B3.
2. zadejte znaménko rovnosti =.
3. klikněte na buňku D3.
4. zadejte lomítko /.
5. klikněte na buňku D12.
6. stiskněte klávesu ENTER. Uvidíte, že nástroje pro domácnost představují 16,7% ročního rozpočtu výdajů (viz obrázek 2.14).

Obrázek 2.14 ukazuje vyplněný vzorec, který vypočítává procento, které roční výdaje na služby domácností představují k celkovým ročním výdajům rozpočtu(viz buňka B3). Normálně bychom tento vzorec zkopírovali a vložili do rozsahu B4: B11. Vzhledem k relativnímu odkazování se však obě odkazy na buňky zvýší o jeden řádek, jakmile je vzorec vložen do buněk pod B3. To je v pořádku pro první odkaz na buňku ve vzorci (D3), ale ne pro odkaz na druhou buňku (D12). Obrázek 2.15 ukazuje, co se stane, když vložíme vzorec do rozsahu B4: B12 v jeho aktuálním stavu. Všimněte si, že Excel vytváří kód chyby # DIV / 0. To znamená, že Excel se snaží rozdělit číslo nulou, což je nemožné. Při pohledu na vzorec v buňce B4 vidíte, že první odkaz na buňku byl změněn z D3 na D4. To je v pořádku, protože nyní chceme rozdělit roční výdaje na pojištění celkovými ročními výdaji v buňce D12. Excel však také změnil odkaz na buňku D12 na D13. Protože umístění buňky D13 je prázdné, vzorec vytvoří kód chyby # DIV / 0 .

odstranit dělení nulou chyba je znázorněno na Obrázku 2.15 je třeba dodat, absolutní odkaz na buňku D12 ve vzorci. Absolutní odkaz brání relativnímu odkazu ve změně odkazu na buňku ve vzorci. Toto je také označováno jako uzamčení buňky. Následující vysvětluje, jak je toho dosaženo:

1. Poklepejte na buňku B3.
2. umístěte ukazatel myši před D12 a klikněte. Blikající kurzor by měl být před D v buňce reference D12.
3. stiskněte klávesu F4. Uvidíte znak dolaru ( $ ) přidaný před písmenem sloupce D a číslem řádku 12. Můžete také zadat znaky dolaru před písmenem sloupce a číslem řádku.
4. stiskněte klávesu ENTER.
5. klikněte na buňku B3.
6. klikněte na tlačítko Kopírovat na kartě Domovská stránka pásu karet.
7. zvýrazněte rozsah B4: B11.
8. klikněte na tlačítko Vložit na kartě Domovská stránka pásu karet.

obrázek 2.16 ukazuje procento celkového vzorce s absolutním odkazem přidaným k D12. Všimněte si, že v buňce B4 zůstává odkaz na buňku D12 místo změny na D13, jak je znázorněno na obrázku 2.15. Také uvidíte, že procenta se počítají ve zbytku buněk ve sloupci a chyba dělení nulou je nyní vyloučena.

funkce COUNT

datový soubor: pokračujte s osobním rozpočtem CH2.

další funkcí, kterou přidáme do listu podrobností o rozpočtu, je funkce počítání. Funkce COUNT se používá k určení, kolik buněk v rozsahu obsahuje číselnou položku. Funkce COUNT nebude fungovat pro počítání textu nebo jiných nečíselných položek. Pro List s podrobnostmi o rozpočtu použijeme funkci COUNT k počítání počtu položek, které jsou plánovány ve sloupci roční výdaje (sloupec D). Následující text vysvětluje, jak je funkce COUNT přidána do listu pomocí seznamu funkcí:

1. v listu podrobností o rozpočtu klikněte na buňku D13.
2. zadejte znaménko rovnosti =.
3. zadejte písmeno C.
4. klikněte na šipku dolů na posuvníku seznamu funkcí (viz obrázek 2.17) a najděte počet slov.
5. Poklepejte na počet slov ze seznamu funkcí.
6. zvýrazněte rozsah D3: D11.
7. můžete zadat uzavírací závorku) a poté stiskněte klávesu ENTER nebo jednoduše stiskněte klávesu ENTER a Excel pro vás funkci zavře. Funkce produkuje výstup 9, protože na listu je naplánováno 9 položek.

Obrázek 2.17 zobrazuje pole Seznam funkcí, které se zobrazí po dokončení kroků 2 a 3 pro funkci COUNT. Seznam funkcí poskytuje alternativní metodu pro přidání funkce do listu.

obrázek 2.18 ukazuje výstup Funkce COUNT po stisknutí klávesy ENTER. Funkce počítá počet buněk v rozsahu D3: D11, které obsahují číselnou hodnotu. Výsledek 9 naznačuje, že pro tento rozpočet je plánováno 9 kategorií.

PRŮMĚRNÝ Funkce

další funkce přidáme do Rozpočtu Detail listu je PRŮMĚRNÁ funkce. Tato funkce se používá k výpočtu aritmetického průměru pro skupinu čísel. Pro List s podrobnostmi o rozpočtu použijeme tuto funkci k výpočtu průměru hodnot ve sloupci roční výdaje. Přidáme to do listu pomocí knihovny funkcí. Následující kroky vysvětlují, jak je toho dosaženo:

1. v listu podrobností o rozpočtu klikněte na buňku D14.
2. klikněte na kartu vzorce na pásu karet.
3. klikněte na tlačítko Další funkce ve skupině příkazů Knihovna funkcí.
4. umístěte ukazatel myši na statistickou možnost z rozevíracího seznamu možností.
5. klikněte na průměrný název funkce ze seznamu funkcí, které se objeví v nabídce (viz obrázek 2.19). Otevře se dialogové okno Argumenty funkce.
6. v dialogovém okně Argumenty funkce klikněte na tlačítko Sbalit (viz obrázek 2.20).
7. zvýrazněte rozsah D3: D11.
8. v dialogovém okně Argumenty funkce klikněte na tlačítko Rozbalit (viz obrázek 2.21). Můžete také stisknout klávesu ENTER a získat stejný výsledek.
9. klikněte na tlačítko OK v dialogovém okně Argumenty funkce. Tím se do listu přidá průměrná funkce.

obrázek 2.19 ukazuje, jak je funkce vybrána z knihovny funkcí na kartě Vzorce na pásu karet.

obrázek 2.20 ukazuje dialogové okno Argumenty funkce. Zobrazí se po výběru funkce z knihovny funkcí. Dialogové tlačítko Sbalit se používá ke skrytí dialogového okna, takže v listu lze zvýraznit řadu buněk a poté je přidat do funkce.

Obrázek 2.21 ukazuje, jak oblast buněk může být vybrán z Argumentů Funkce dialogové okno, jakmile je má byly zhroutil.

Obrázek 2.22 zobrazí dialogové okno Argumenty funkce po definování rozsahu buněk pro průměrnou funkci. Dialogové okno zobrazuje výsledek funkce před jejím přidáním do umístění buňky. To vám umožní posoudit výstup Funkce a určit, zda má smysl před přidáním do listu.

obrázek 2.23 ukazuje dokončenou průměrnou funkci v listu podrobností rozpočtu. Výstup z funkce ukazuje, že v průměru můžeme očekávat, že stráví $1,994 pro každou z kategorií uvedených ve Sloupci A rozpočtu. Tento výpočet průměrné výdaje na kategorii lze použít jako ukazatel k určení, které kategorie stojí více či méně než průměrná rozpočtovaná útrata dolarů.

funkce MAX A MIN

datový soubor: pokračujte s osobním rozpočtem CH2.

poslední dvě statistické funkce, které přidáme do listu podrobností o rozpočtu, jsou funkce MAX A MIN. Tyto funkce identifikují nejvyšší a nejnižší hodnoty v řadě buněk. Následující kroky vysvětlují, jak přidat tyto funkce do listu podrobností rozpočtu:

1. v listu podrobností o rozpočtu klikněte na buňku D15.
2. zadejte znaménko rovnosti =.
3. zadejte slovo MIN.
4. zadejte otevřenou závorku (.
5. zvýrazněte rozsah D3: D11.
6. zadejte uzavírací závorku) a stiskněte klávesu ENTER nebo jednoduše stiskněte klávesu ENTER a Excel pro vás funkci zavře. MIN funkce produkuje výstup $ 1,200 ,což je nejnižší hodnota ve sloupci roční výdaje (viz obrázek 2.24).
7. klikněte na buňku D16.
8. zadejte znaménko rovnosti =.
9. zadejte slovo MAX.
10. zadejte otevřenou závorku (.
11. zvýrazněte rozsah D3: D11.
12. zadejte uzavírací závorku) a stiskněte klávesu ENTER nebo jednoduše stiskněte klávesu ENTER a Excel pro vás funkci zavře. Funkce MAX produkuje výstup 3 500$. Toto je nejvyšší hodnota ve sloupci roční výdaje (viz obrázek 2.25).

kopírování a vkládání vzorců (vkládání bez formátů)

datový soubor: pokračujte s osobním rozpočtem CH2.

jak je znázorněno na obrázku 2.25, funkce COUNT, AVERAGE, MIN a MAX shrnují údaje ve sloupci roční výdaje. Také si všimnete, že je zde prostor pro kopírování a vkládání těchto funkcí ve sloupci utratit LYR. To nám umožňuje porovnat, co jsme strávili minulý rok a co plánujeme utratit letos. Normálně bychom tyto funkce jednoduše zkopírovali a vložili do rozsahu E13: E16. Možná jste si však všimli hranice dvouřádkového stylu, která byla použita po obvodu řady B13: E16. Pokud bychom použili příkaz regular Paste, dvojitý řádek na pravé straně rozsahu E13: E16 by byl nahrazen jedním řádkem. Proto použijeme jeden ze speciálních příkazů Vložit k vložení pouze funkcí bez jakéhokoli formátování. Toho lze dosáhnout pomocí následujících kroků:

1. zvýrazněte rozsah D13: D16 v listu s podrobnostmi o rozpočtu.
2. klikněte na tlačítko Kopírovat na kartě Domovská stránka pásu karet.
3. klikněte na buňku E13.
4. klikněte na šipku dolů pod tlačítkem Vložit na kartě Domů na pásu karet.
5. v rozevíracím seznamu tlačítek klikněte na možnost vzorce (viz obrázek 2.26).

obrázek 2.26 ukazuje seznam tlačítek, která se zobrazí po klepnutí na šipku dolů pod tlačítkem Vložit na kartě Domů na pásu karet. Jedna věc, kterou je třeba si uvědomit o těchto možnostech, je, že si je můžete prohlédnout před provedením výběru přetažením ukazatele myši nad možnostmi. Jak je znázorněno na obrázku, když je ukazatel myši umístěn nad tlačítkem vzorce, můžete před provedením výběru vidět, jak se funkce zobrazí. Všimněte si, že dvouřádkový okraj se při zobrazení této možnosti nezmění. Proto se tento výběr provádí namísto možnosti pravidelné pasty.