Domov › Telefon › Jak zadávat vzorce v excelu. Naučte se zadávat vzorce v Excelu

Jak zadávat vzorce v excelu. Naučte se zadávat vzorce v Excelu

Jak vytvořit vzorec v Excelu, krok za krokem.

Chcete vědět, jak trvale vydělávat peníze online od 500 rublů denně?
Stáhněte si mou knihu zdarma
=>>

Program jako Excel je určen k vytváření různých tabulek. Zejména je možné provádět výpočetní operace pomocí speciálních vzorců.

V tomto případě mohou vzorce používané v Excelu obsahovat sčítání, odčítání, násobení nebo dělení. Každá akce má své vlastní operátory rovnic.

Znak „*“ se tedy používá pro násobení, „/“ pro dělení, „-“ pro odčítání, „+“ pro sčítání a speciální znaménko vkládání, které lze umocnit, je označeno jako „^“.

Nejdůležitějším bodem při používání vzorců v Excelu je však to, že každý vzorec musí začínat znakem „=“.
Mimochodem, pro usnadnění výpočtů můžete použít adresy buněk a poskytnout hodnoty, které jsou v nich dostupné.

Jak vytvořit vzorec v Excelu, návod

V tomto článku se můžete blíže podívat na vytváření vzorců v Excelu.

Vytvořte jednoduchý vzorec

Chcete-li vytvořit jednoduchý vzorec, nejprve vložte rovnítko a poté zapište jeho hodnoty. Například ve sloupci „A“ máte na prvním řádku hodnotu 14, ve sloupci „B“ na stejném řádku je „38“ a jejich součet ve sloupci „E“ musíte vypočítat také na prvním řádku.

Chcete-li to provést, vložte do posledního sloupce rovnítko a označte A1 + B1, poté stiskněte tlačítko „Enter“ na klávesnici a získejte odpověď. To znamená, že nejprve v E1 je získán záznam „=A1+B1“. Jakmile stisknete „Enter“, objeví se částka rovna 52.

Mimochodem, pokud kliknete na výslednou hodnotu, řádek s názvem f(x) umístěný nad tabulkou zobrazí vzorec použitý pro výpočet.

Právě zde je v případě potřeby nejlepší upravit vzorce. Pokud nepotřebujete provádět změny, na levé straně f(x) klikněte na křížek.

Pokud se v buňce zobrazí „#######“, není třeba se obávat. To znamená, že ve sloupci prostě není dostatek místa pro zobrazení celého obsahu buňky. Chcete-li to opravit, zvětšete jeho šířku.

Použití odkazů na buňky

Nejjednodušší možností pro vytváření vzorců je práce s odkazy na buňky. Jak s tím pracovat? Velmi jednoduché.

Nejprve tedy vložte rovnítko na místo, kde by měla být konečná hodnota, a poté klikněte na buňky jednu po druhé s uvedenými číselnými hodnotami a umístěním znamének mezi ně označte vše potřebné.

Nakonec stiskněte „Enter“.

Řekněme například, že máte 120 v B2, 900 v B3 a výsledek odečítání v B4. Při použití odkazů na buňky tedy nejprve umístíte znak „=“ na B4, poté kliknete na B2, buňka by se měla rozsvítit novou barvou.

Poté vložte znak „*“ a klikněte na B3. To znamená, že v B4 dostanete vzorec: = B2*B3. Po stisknutí "Enter" získáte výsledek.

Mimochodem, když zadáte jakoukoli hodnotu do buňky B2 nebo B3, výsledek zapsaný v B4 se automaticky změní.

Zkopírujte vzorec

Pokud máte v tabulce několik sloupců, které používají stejný vzorec, lze pro zjednodušení úlohy provádět výpočty pouze v jednom a poté jej zkopírovat a použít na zbytek tabulky nebo sloupce.

Máte například produkt, znáte jeho množství a jednotkovou cenu, stačí si spočítat náklady. Chcete-li to provést, vložte do buňky s cenou rovnítko a množství z první buňky, kde je uvedena hodnota, vynásobte cenou za jednotku zboží.

Pokud je tento vzorec použitelný pro celou tabulku, pak pro jeho zkopírování ukažte na pravý dolní roh a místo bílého plus byste měli vidět tenké černé plus.

Je třeba ji posunout dolů ve sloupci nákladů. Dojde tak k plně automatickému výpočtu ceny zboží.
To znamená, že nástroj jako tenká černá ikona plus umožňuje kopírovat data z buňky a aplikovat je v celém sloupci.

Což je také mnohem pohodlnější než počítání hodnot pro každý řádek postupně a provádění stejné akce.

Vzorce

Obecně platí, že pokud vyvstane otázka, jak vytvořit vzorec v aplikaci Excel, pak, jak vidíte, není těžké to udělat. K získání požadovaného výsledku však potřebujete přesnou znalost vzorce, který by měl být použit k vyřešení problému nebo k provedení výpočtů.

A těch je poměrně hodně a proto je těžké vše vědět nebo si zapamatovat. Vývojáři se o tento problém postarali a pro zjednodušení práce s Excelem shromáždili všechny vzorce, které lze v tomto programu použít, do jedné sekce.

Když se podíváte na úplně horní řádek, kde je uvedena položka jako „Domů“, posunete se trochu doprava, najdete sloupec „Vzorec“.

Zde si v případě potřeby můžete vybrat jeden nebo jiný vzorec pro provádění výpočtů. To znamená, že v tabulce vložte rovnítko do požadovaného řádku, najděte vzorec, který potřebujete, například částku.

Výsledek získáte po řádcích. Zatáhněte za roh s výsledkem součtu dolů a vzorec se automaticky použije na všechny vyplněné sloupce.

Jak vytvořit vzorec v Excelu, shrnutí

Jak vidíte, vytváření vzorců v Excelu je pohodlné a rychlé. Program sám vše spočítá a zanalyzuje. Přirozeně je obtížné vměstnat a popsat všechny funkce tohoto programu do jednoho článku. Ale vývojáři se snažili udělat vše pro vaše pohodlí.

Když se objeví otázka, klikněte na horní panel „Co chcete dělat?“ a zadejte do vyhledávání akci, kterou chcete provést. Program vám v této části poskytne nápovědu. Naučte se tedy Excel a používejte tento program a všechny jeho funkce naplno. Hodně štěstí a úspěchů.

Nezapomeňte se přihlásit k odběru blogu, protože zde najdete odpovědi na mnoho zajímavých otázek. A také budete vždy vědět o vydání nových článků.

Dobré vědět:

P.S. Přikládám screenshot mých výdělků v affiliate programech. A připomínám, že si takto může vydělat každý, dokonce i začátečník! Hlavní je dělat to správně, což znamená učit se od těch, kteří už vydělávají peníze, tedy od profesionálů v internetovém byznysu.

Získejte seznam osvědčených affiliate programů v roce 2018, které platí peníze!

Stáhněte si zdarma kontrolní seznam a hodnotné bonusy
=>> “Nejlepší affiliate programy roku 2018”

Vzorce v Excelu jsou jeho hlavní podstatou, proto tento program vytvořila společnost Microsoft. Vzorce umožňují vypočítat hodnoty buněk na základě dat z jiných buněk, a pokud se zdrojová data změní, výsledek výpočtů v buňce, kde je vzorec zapsán, se automaticky přepočítá!

Vytváření vzorců v Excelu

Podívejme se, jak vzorce fungují na nejjednodušším příkladu – součtu dvou čísel. Nechte číslo 2 zadat do jedné buňky Excel a 3 do druhé. Je nutné, aby se součet těchto čísel objevil ve třetí buňce.

Součet 2 a 3 je samozřejmě 5, ale 5 nemusíte zadávat ručně do další buňky, jinak se ztratí význam výpočtů v Excelu. Do buňky se součtem musíte zadat součtový vzorec a výsledek pak program dopočítá automaticky.

V příkladu vypadá výpočet jednoduše, ale když jsou čísla velká nebo zlomková, bez vzorce se prostě neobejdete.

Vzorce v Excelu mohou obsahovat aritmetické operace (sčítání +, odčítání -, násobení *, dělení /), souřadnice zdrojových datových buněk (jak jednotlivě, tak rozsah) a výpočetní funkce.

Zvažte vzorec pro součet čísel ve výše uvedeném příkladu:

SOUČET(A2;B2)

Každý vzorec začíná rovnítkem. Pokud chcete přidat vzorec do buňky ručním zápisem, pak by měl být tento znak zapsán jako první.

Dále je v příkladu funkce SUM, což znamená, že je potřeba sečíst nějaká data a již v závorce funkce oddělené středníkem jsou uvedeny některé argumenty, v tomto případě souřadnice buněk (A2 a B2), jehož hodnoty je třeba sečíst a výsledek umístit do buňky, kde je zapsán vzorec. Pokud potřebujete přidat tři buňky, můžete do funkce SUM napsat tři argumenty a oddělit je středníkem, například:

SOUČET(A4;B4;C4)

Když potřebujete přidat velký počet buněk, zadání každé z nich ve vzorci zabere spoustu času, takže místo jednoduchého výpisu můžete použít určení rozsahu buněk:

SOUČET(B2:B7)

Rozsah buněk v Excelu je určen pomocí souřadnic první a poslední buňky oddělených dvojtečkou. Tento příklad přidá hodnoty buněk počínaje buňkou B2 do buňky B7.

Funkce ve vzorcích lze spojovat a kombinovat, jak potřebujete, abyste získali požadovaný výsledek. Úkolem je například sečíst tři čísla a podle toho, zda je výsledek menší než 100 nebo více, vynásobit součet koeficientem 1,2 nebo 1,3. Následující vzorec pomůže vyřešit problém:

KDYŽ(SOUČET(A2:C2)

Pojďme analyzovat řešení problému podrobněji. Byly použity dvě funkce IF a SUM. Funkce KDYŽ má vždy tři argumenty: první je podmínka, druhý je akce, pokud je podmínka pravdivá, třetí je akce, pokud je podmínka nepravdivá. Připomínáme, že argumenty jsou odděleny středníky.

POKUD(podmínka; pravda; nepravda)

Podmínka udává, že součet rozsahu buněk A2:C2 je menší než 100. Pokud je během výpočtu podmínka splněna a součet buněk v rozsahu je roven např. 98, Excel provést akci uvedenou ve druhém argumentu funkce KDYŽ, tj. SOUČET(A2:C2)*1,2. Pokud součet překročí číslo 100, pak se provede akce ve třetím argumentu funkce IF, tzn. SOUČET(A2:C2)*1,3.

Vestavěné funkce v Excelu

V Excelu je obrovské množství funkcí a vyznat se ve všem je prostě nemožné. Některé často používané se dají zapamatovat, některé ale budete potřebovat jen občas a jejich název a hlavně formu nahrávky si zapamatujete jen velmi těžko.

Excel má ale standardní způsob vkládání funkcí s jejich kompletním seznamem. Pokud chcete do buňky přidat funkci, klikněte na buňku a v hlavním menu vyberte vložit funkci. Program zobrazí seznam funkcí a vy můžete vybrat tu, která je nezbytná k vyřešení problému.

Chcete-li vložit funkci do Excelu 2007, vyberte v hlavní nabídce položku „Vzorce“ a klikněte na ikonu „Vložit funkci“ nebo stiskněte kombinaci kláves Shift+F3 na klávesnici.

V aplikaci Excel 2003 se funkce vkládá pomocí nabídky "Vložit" -> "Funkce". Stejným způsobem funguje i kombinace kláves Shift+F3.

V buňce, na kterou byl umístěn kurzor, se objeví rovnítko a v horní části listu se objeví okno „Průvodce funkcí“.

Funkce v Excelu jsou rozděleny do kategorií. Pokud víte, do které kategorie může vaše zamýšlená funkce patřit, vyberte podle ní výběr. V opačném případě vyberte „Úplný abecední seznam“. Program zobrazí všechny dostupné funkce v seznamu funkcí.

Procházejte seznam a pomocí myši zvýrazněte název funkce, která vás zajímá. Hned pod seznamem se objeví jeho záznamová forma, požadované argumenty a stručný popis, který vám vysvětlí účel funkce. Když najdete, co potřebujete, klikněte na tlačítko „OK“ a pokračujte k zadání argumentů.

V okně argumentů jsou pole s názvem „Číslo 1“, „Číslo 2“ atd. Musí být vyplněny souřadnicemi buněk (nebo rozsahů), ve kterých chcete převzít data. Můžete vyplnit ručně, ale mnohem pohodlnější je kliknout na ikonu tabulky na konci pole, abyste označili zdrojovou buňku nebo rozsah.

Okno argumentů bude mít zjednodušenou formu. Nyní musíte kliknout na první zdrojovou buňku s daty a poté znovu na ikonu tabulky v okně argumentů.

Pole „Číslo 1“ bude vyplněno souřadnicemi vybrané buňky. Stejný postup by měl být proveden pro pole „Číslo 2“ a pro následující pole, pokud máte více než dva argumenty funkce.

Po vyplnění všech argumentů můžete zobrazit náhled výsledku výpočtu výsledného vzorce. Chcete-li jej zobrazit v buňce na listu, klikněte na tlačítko „OK“. V uvažovaném příkladu obsahuje buňka D2 součin čísel v buňkách B2 a C2.

Uvažovaný způsob vložení funkce je univerzální a umožňuje přidat libovolnou funkci z obecného seznamu standardních funkcí Excelu.

Jako

Ahoj!

Mnoho lidí, kteří nepoužívají Excel, si ani neumí představit, jaké možnosti tento program poskytuje! Jen si pomyslete: automaticky přidávejte hodnoty z jednoho vzorce do druhého, vyhledejte potřebné řádky v textu, přidejte podle podmínky atd. - obecně v podstatě miniprogramovací jazyk pro řešení „úzkých“ problémů (abych byl upřímný, sám jsem Excel dlouho nepovažoval za program a téměř nikdy jsem ho nepoužíval)...

V tomto článku chci ukázat několik příkladů, jak můžete rychle vyřešit každodenní kancelářské úkoly: něco přidat, něco odečíst, vypočítat součet (včetně s podmínkou), nahradit hodnoty z jedné tabulky do druhé atd. To znamená, že tento článek bude něco jako mini návod, jak se naučit, co k práci potřebujete (přesněji začít používat Excel a cítit plnou sílu tohoto produktu!).

Je možné, že kdybych podobný článek četl před 15-17 lety, začal bych používat Excel mnohem rychleji (a ušetřil bych si spoustu času na řešení „jednoduchých“ problémů). (poznámka: jak tomu teď rozumím)úkoly)...

Poznámka: Všechny screenshoty níže jsou z Excelu 2016 (jako nejnovějšího dnes).

Mnoho začínajících uživatelů si po spuštění Excelu klade jednu zvláštní otázku: "No, kde je tabulka?" Mezitím jsou všechny buňky, které vidíte po spuštění programu, jeden velký stůl!

Nyní k tomu hlavnímu: libovolná buňka může obsahovat text, nějaké číslo nebo vzorec. Například níže uvedený snímek obrazovky ukazuje jeden ilustrativní příklad:

vlevo: buňka (A1) obsahuje prvočíslo "6". Vezměte prosím na vědomí, že když vyberete tuto buňku, na řádku vzorců (Fx) se jednoduše zobrazí číslo "6".
vpravo: v buňce (C1) je také jednoduché číslo „6“, ale pokud vyberete tuto buňku, uvidíte vzorec "=3+3" - to je důležitá funkce v Excelu!

Jen číslo (vlevo) a vypočítaný vzorec (vpravo)

Jde o to, že Excel umí počítat jako na kalkulačce, když vyberete nějakou buňku a pak napíšete vzorec, například "=3+5+8" (bez uvozovek). Výsledek nemusíte psát – Excel si ho sám vypočítá a zobrazí v buňce (jako v buňce C1 v příkladu výše)!

Ale můžete psát do vzorců a přidávat nejen čísla, ale také čísla, která již byla vypočtena v jiných buňkách. Na níže uvedeném snímku obrazovky jsou v buňkách A1 a B1 čísla 5 a 6. V buňce D1 chci získat jejich součet - vzorec můžete napsat dvěma způsoby:

nejprve: „=5+6“ (není moc pohodlné, představte si, že v buňce A1 - naše číslo se také vypočítá podle nějakého jiného vzorce a změní se. Nebudete pokaždé místo 5 dosazovat nové číslo?!);
druhá: „=A1+B1“ - ale to je ideální možnost, jednoduše přidáme hodnoty buněk A1 a B1 (i když jsou v nich čísla!)

Přidávání buněk, které již obsahují čísla

Rozložení vzorce do dalších buněk

Ve výše uvedeném příkladu jsme sečetli dvě čísla ve sloupcích A a B v prvním řádku. Ale máme 6 řádků a nejčastěji v reálných úlohách je potřeba do každého řádku přidat čísla! Chcete-li to provést, můžete:

do řádku 2 napište vzorec "=A2+B2", do řádku 3 - "=A3+B3" atd. (toto je dlouhé a zdlouhavé, tato možnost se nikdy nepoužívá);
vyberte buňku D1 (která již má vzorec), poté přesuňte ukazatel myši do pravého rohu buňky tak, aby se objevil černý křížek (viz snímek obrazovky níže). Poté podržte levé tlačítko a roztáhněte vzorec na celý sloupec. Pohodlné a rychlé! (Poznámka: pro vzorce (kopírování a vkládání) můžete také použít kombinace Ctrl+C a Ctrl+V).

Mimochodem, věnujte pozornost skutečnosti, že Excel sám vkládal vzorce do každého řádku. To znamená, že pokud nyní vyberete buňku, řekněme D2, uvidíte vzorec "=A2+B2" (tj. Excel automaticky nahradí vzorce a okamžitě vytvoří výsledek) .

Jak nastavit konstantu (buňku, která se při kopírování vzorce nezmění)

Dost často je ve vzorcích (když je kopírujete) vyžadováno, aby se nějaká hodnota neměnila. Řekněme jednoduchý úkol: převést ceny v dolarech na rubly. Hodnota rublu je uvedena v jedné buňce, v mém příkladu níže je to G2.

Dále do buňky E2 napište vzorec „=D2*G2“ a získejte výsledek. Pokud ale vzorec natáhneme, jako jsme to udělali dříve, neuvidíme výsledek v dalších řádcích, protože Excel vloží vzorec „D3*G3“ na řádek 3, „D4*G4“ na řádek 4 atd. Potřebujeme, aby G2 zůstala G2 všude...

Chcete-li to provést, jednoduše změňte buňku E2 - vzorec bude vypadat jako "=D2*$G$2". Tito. znak dolaru $ - umožňuje zadat buňku, která se při kopírování vzorce nezmění (tj. dostaneme konstantu, příklad níže)...

Jak vypočítat částku (vzorce SUM a SUMIFS)

Vzorce můžete samozřejmě skládat ručně zadáním „=A1+B1+C1“ atd. Excel má ale rychlejší a pohodlnější nástroje.

Jedním z nejjednodušších způsobů, jak přidat všechny vybrané buňky, je použít volbu automatické součty (Excel napíše vzorec sám a vloží ho do buňky).

Nejprve vyberte buňky (viz snímek obrazovky níže);
Dále otevřete sekci „Vzorce“;
Dalším krokem je kliknutí na tlačítko "AutoSum". Výsledek sčítání se objeví pod buňkami, které jste vybrali;
pokud vyberete buňku s výsledkem (v mém případě je to buňka E8) - pak uvidíte vzorec "=SUM(E2:E7)" .
tedy psaní vzorce "=SUM(xx)", kde místo toho xx vložte (nebo vyberte) libovolné buňky, můžete číst širokou škálu rozsahů buněk, sloupců, řádků...

Dost často při práci nepotřebujete jen součet celého sloupce, ale součet určitých řádků (tedy selektivně). Předpokládejme jednoduchý úkol: potřebujete získat výši zisku z nějakého pracovníka (samozřejmě přehnané, ale příklad je více než skutečný).

Ve své tabulce použiji pouze 7 řádků (pro přehlednost), ale skutečná tabulka může být mnohem větší. Předpokládejme, že musíme vypočítat všechny zisky, které „Sasha“ vydělal. Jak bude vzorec vypadat:

"=SUMIFS(F2:F7 ;A2:A7 ;"Sasha") " - (poznámka: věnujte pozornost uvozovkám u podmínky - měly by být jako na obrázku níže, a ne jak jsem teď napsal na svém blogu). Všimněte si také, že Excel při zadávání začátku vzorce (například „SUM...“) sám navrhuje a nahrazuje možné možnosti – a v Excelu jsou stovky vzorců!;
F2:F7 je rozsah, ve kterém budou čísla z buněk sčítána (sčítána);
A2:A7 je sloupec, podle kterého bude naše podmínka kontrolována;
„Sasha“ je podmínka, budou přidány ty řádky, ve kterých je „Sasha“ ve sloupci A (pozor na orientační snímek obrazovky níže).

Poznámka: podmínek může být několik a lze je zkontrolovat pomocí různých sloupců.

Jak spočítat počet řádků (s jednou, dvěma nebo více podmínkami)

Poměrně typický úkol: počítat ne součet v buňkách, ale počet řádků, které splňují nějakou podmínku. No, například, kolikrát se jméno „Sasha“ objeví v tabulce níže (viz snímek obrazovky). Pochopitelně 2x (ale to je proto, že tabulka je příliš malá a bere se jako ilustrativní příklad). Jak to vypočítat pomocí vzorce? Vzorec:

"=COUNTIF(A2:A7;A2)“ – Kde:

A2:A7- rozsah, ve kterém budou řádky kontrolovány a počítány;
A2- je nastavena podmínka (všimněte si, že můžete napsat podmínku jako „Sasha“, nebo můžete jednoduše zadat buňku).

Výsledek je zobrazen na pravé straně obrazovky níže.

Nyní si představte pokročilejší úkol: musíte spočítat řádky, kde se objeví jméno „Sasha“ a kde se ve sloupci AND objeví číslo „6“. Při pohledu do budoucna řeknu, že existuje pouze jeden takový řádek (snímek obrazovky s příkladem níže).

Vzorec bude vypadat takto:

=COUNTIFS(A2:A7 ;A2 ;B2:B7 ;"6") (poznámka: věnujte pozornost uvozovkám - měly by být jako na obrázku níže, a ne jako moje), kde:

A2:A7;A2- první rozsah a podmínka vyhledávání (podobně jako ve výše uvedeném příkladu);

B2:B7 ;"6"- druhý rozsah a podmínka vyhledávání (všimněte si, že podmínka může být nastavena různými způsoby: buď zadejte buňku, nebo jednoduše napište text/číslo v uvozovkách).

Jak vypočítat procento z částky

To je také poměrně častá otázka, se kterou se často setkávám. Obecně, pokud si dokážu představit, vzniká nejčastěji - kvůli tomu, že lidé jsou zmatení a nevědí, co procenta hledají (a obecně tématu procent dobře nerozumí ( ačkoli já sám nejsem velký matematik, a přesto ... )).

Nejjednodušší způsob, ve kterém je prostě nemožné se splést, je použít pravidlo „čtverce“ neboli proporce. Celý bod je znázorněn na níže uvedeném snímku obrazovky: pokud máte celkovou částku, řekněme, že v mém příkladu je toto číslo 3060 - buňka F8 (tj. jde o 100% zisk a část z něj vytvořil „Sasha“, je třeba najít který... ).

V poměru bude vzorec vypadat takto: =F10*G8/F8(tj. křížkem: nejprve vynásobíme dvě známá čísla diagonálně a poté vydělíme zbývajícím třetím číslem). V zásadě je při použití tohoto pravidla téměř nemožné se v procentech splést.

Vlastně zde končím tento článek. Nebojím se říci, že po zvládnutí všeho, co je napsáno výše (a je zde uvedeno pouze „pět“ vzorců), se budete moci samostatně učit Excel, listovat v nápovědě, sledovat, experimentovat a analyzovat. Řeknu ještě více, vše, co jsem popsal výše, pokryje mnoho úkolů a umožní vám vyřešit ty nejběžnější, nad kterými si často lámete hlavu (pokud neznáte možnosti Excelu), a ne vědět, jak to udělat rychleji...

Vzorec je matematický výraz, který je vytvořen pro výpočet výsledku a který může záviset na obsahu jiných buněk. Vzorec v buňce může obsahovat data, odkazy na jiné buňky a také označení akcí, které je třeba provést.

Použití odkazů na buňky umožňuje přepočítat výsledky vzorců, když se změní obsah buněk zahrnutých ve vzorcích.

V Excelu začínají vzorce znakem =. Závorky () lze použít k definování pořadí matematických operací.

Excel podporuje následující operátory:

Aritmetické operace:
- sčítání (+);
- násobení (*);
- zjištění procenta (%);
- odčítání(-);
- divize(/);
- exponent (^).
Srovnávací operátory:
- = rovný;
- < меньше;
- > více;
- <= меньше или равно;
- >= větší nebo rovno;
- <>ne rovné.
Telekomunikační operátoři:
- : rozsah;
- ; sdružení;
- & operátor pro spojování textů.

Tabulka 22. Příklady vzorců

Cvičení

Vložte vzorec -25-A1+AZ

Předem zadejte libovolná čísla do buněk A1 a A3.

Vyberte požadovanou buňku, například B1.
Začněte zadávat vzorec se znaménkem =.
Zadejte číslo 25 a poté operátor (znaménko -).
Zadejte odkaz na první operand, například kliknutím na požadovanou buňku A1.
Zadejte následující operátor (znaménko +).
Klikněte do buňky, která je druhým operandem ve vzorci.
Doplňte vzorec stisknutím klávesy Vstupte. V buňce B1 dostanete výsledek.

Autosumace

Tlačítko AutoSum- ∑ lze použít k automatickému vytvoření vzorce, který sečte plochu bezprostředně sousedících buněk vlevo v tomto řádku a přímo vyšší v tomto sloupci.

Vyberte buňku, do které chcete umístit výsledek součtu.
Klikněte na tlačítko AutoSum - ∑ nebo stiskněte kombinaci kláves Alt+=. Excel rozhodne, kterou oblast zahrnout do součtového rozsahu, a zvýrazní ji tečkovaným pohyblivým rámečkem, který se nazývá ohraničení.
Klikněte Vstupte přijměte oblast, kterou Excel vybral, nebo pomocí myši vyberte novou oblast a stiskněte Enter.

Funkce AutoSum se automaticky transformuje při přidání nebo odstranění buněk v oblasti.

Cvičení

Vytvoření tabulky a výpočet pomocí vzorců

Zadejte číselná data do buněk, jak je uvedeno v tabulce. 23.

	A	V	S	D	B	F
1
2		Magnólie	Lilie	Fialový	Celkový
3	Vyšší	25	20	9
4	Sekundární speciál	28	23	21
5	Odborná škola	27	58	20
PROTI	Ostatní	8	10	9
7	Celkový
8	Bez vyšší

Tabulka 23. Tabulka původních dat

Vyberte buňku B7, ve které se bude počítat vertikální součet.
Klikněte na tlačítko AutoSum - ∑ nebo klikněte Alt+=.
Opakujte kroky 2 a 3 pro buňky C7 a D7.

Vypočítejte počet zaměstnanců bez vysokoškolského vzdělání (pomocí vzorce B7-VZ).

Vyberte buňku B8 a zadejte znaménko (=).
Klepněte na buňku B7, která je prvním operandem ve vzorci.
Zadejte znaménko (-) na klávesnici a klikněte do buňky V3, což je druhý operand ve vzorci (vzorec bude zadán).
Klikněte Vstupte(výsledek bude vypočítán v buňce B8).
Opakujte kroky 5-8 pro výpočet pomocí příslušných vzorců v buňkách C8 a 08.
Uložte soubor s názvem Education_employees.x1s.

Tabulka 24.Výsledek výpočtu

	A	B	S	D	E	F
1	Rozdělení zaměstnanců podle vzdělání
2		Magnólie	Lilie	Fialový	Celkový
3	Vyšší	25	20	9
4	Sekundární speciál	28	23	21
5	Odborná škola	27	58	20
6	Ostatní	8	10	9
7	Celkový	88	111	59
8	Bez vyšší	63	91	50

Duplikujte vzorce pomocí značky výplně

Plochu buňky (buňku) lze násobit pomocí značka výplně. Jak je znázorněno v předchozí části, úchyt výplně je kontrolní bod v pravém dolním rohu vybrané buňky.

Často je nutné reprodukovat nejen data, ale i vzorce obsahující adresní odkazy. Proces replikace vzorců pomocí úchytu výplně umožňuje zkopírovat vzorec a současně změnit odkazy na adresy ve vzorci.

Vyberte buňku obsahující vzorec, který chcete replikovat.
Táhnout značka výplně správným směrem. Vzorec bude replikován ve všech buňkách.

Tento proces se obvykle používá při kopírování vzorců v řádcích nebo sloupcích obsahujících stejný typ dat. Při replikaci vzorců pomocí značky výplně se mění tzv. relativní adresy buněk ve vzorci (relativní a absolutní vazby budou podrobně popsány níže).

Cvičení

Replikace vzorců

1. Otevřete soubor Employee_Education.x1s.

Do buňky E3 zadejte vzorec pro automatické sčítání buněk =SUM(VZ:03).
Zkopírujte vzorec přetažením úchytu výplně do buněk E4:E8.
Podívejte se, jak se ve výsledných vzorcích mění relativní adresy buněk (Tabulka 25) a soubor uložte.

	A	V	S	D	E	F
1	Rozdělení zaměstnanců podle vzdělání
2		Magnólie	Lilie	Fialový	Celkový
3	Vyšší	25	20	9	=SUM(VZ:03)
4	Sekundární speciál	28	23	21	=SUM(B4:04)
5	Odborná škola	27	58	20	=SUM(B5:05)
6	Ostatní	8	10	9	=SUM(B6:06)
7	Celkový	88	111	58	=SUM(B7:07)
8	Bez vyšší	63	91	49	=SUM(B8:08)

Tabulka 25. Změna adres buněk při replikaci vzorců

Relativní a absolutní reference

Vzorce, které implementují výpočty v tabulkách, používají k adresování buněk takzvané odkazy. Odkaz na buňku může být relativní nebo absolutní.

Používání relativních odkazů je podobné jako udávání směru jízdy na ulici – „jdi tři bloky na sever, pak dva bloky na západ“. Dodržování těchto pokynů z různých výchozích míst povede k různým cílům.

Například vzorec, který sečte čísla ve sloupci nebo řádku, je pak často zkopírován pro jiná čísla řádků nebo sloupců. Takové vzorce používají relativní odkazy (viz předchozí příklad v tabulce 25).

Absolutní odkaz na buňku nebo oblast buněk bude vždy odkazovat na stejnou adresu řádku a sloupce. Ve srovnání se směry ulic to bude něco takového: "Jděte na křižovatku Arbat a Boulevard Ring." Bez ohledu na to, kde začnete, povede na stejné místo. Pokud vzorec vyžaduje, aby adresa buňky zůstala při kopírování nezměněna, musí být použit absolutní odkaz (formát záznamu $A$1). Když například vzorec vypočítává zlomky celkové částky, odkaz na buňku obsahující celkovou částku by se při kopírování neměl změnit.

Znak dolaru ($) se objeví před odkazem na sloupec i odkazem na řádek (například $C$2 postupným stisknutím klávesy F4 přidáte nebo odeberete znaménko před číslem sloupce nebo řádku v odkazu (C$2 nebo $C2). - tzv. smíšené odkazy).

Vytvořte tabulku podobnou té níže.

Tabulka 26. Výpočet platu

Do buňky СЗ zadejte vzorec pro výpočet Ivanovova platu = В1*ВЗ.

Při replikaci vzorce z tohoto příkladu s relativními odkazy se v buňce C4 zobrazí chybová zpráva (#HODNOTA!), protože relativní adresa buňky B1 se změní a vzorec =B2*B4 se zkopíruje do buňky C4;

Nastavte absolutní odkaz na buňku B1 umístěním kurzoru do řádku vzorců na B1 a stisknutím klávesy F4 Vzorec v buňce C3 bude vypadat =$B$1*B3.
Zkopírujte vzorec do buněk C4 a C5.
Uložte soubor (Tabulka 27) pod názvem Mzda.xls.

Tabulka 27. Výsledky výpočtu platu

Názvy ve vzorcích

Názvy ve vzorcích se pamatují snadněji než adresy buněk, takže místo absolutních odkazů můžete použít pojmenované obory (jedna nebo více buněk). Při vytváření jmen je třeba dodržovat následující pravidla:

jména nesmí obsahovat více než 255 znaků;
jména musí začínat písmenem a mohou obsahovat jakýkoli znak kromě mezery;
názvy by neměly být podobné odkazům, jako je VZ, C4;
názvy by neměly používat funkce Excelu, jako např SOUČET POKUD atd.

V nabídce Vložit, Jméno Existují dva různé příkazy pro vytváření pojmenovaných oblastí: Vytvořit a Přiřadit.

Tým Vytvořit umožňuje zadat (zadat) požadovaný název ( pouze jeden), Přiřadit příkaz používá štítky umístěné na listu jako názvy oblastí (umožňuje vytvořit několik jmen najednou).

Vytvoření názvu

Vyberte buňku B1 (tabulka 26).
Vyberte z nabídky Příkaz Vložit, Název (Vložit, Název) Přiřadit (Definovat).
Zadejte jméno Hodinová sazba a klikněte na OK.
Vyberte buňku B1 a ujistěte se, že pole názvu říká Hodinová sazba.

Vytváření více jmen

Vyberte buňky VZ:C5 (tabulka 27).
Vyberte z nabídky Příkaz Vložit, Název (Vložit, Název) Vytvořit (Vytvořit), objeví se dialogové okno Vytvořte jména(obr. 88).
Ujistěte se, že je přepínač v levém sloupci zaškrtnutý a klikněte OK.
Vyberte buňky VZ:NZ a ujistěte se, že pole názvu říká Ivanov.

Rýže. 88. Dialogové okno Vytvořit jména

Místo absolutního odkazu můžete do vzorce vložit název.

V řádku vzorců umístěte kurzor na místo, kam chcete přidat název.
Vyberte z nabídky Vložit, Název (Vložit, Název) příkaz Vložit (Vložit), Zobrazí se dialogové okno Vložit jména.

Vyberte požadovaný název ze seznamu a klikněte na OK.

Chyby ve vzorcích

Pokud při zadávání vzorců nebo dat dojde k chybě, ve výsledné buňce se zobrazí chybová zpráva. Prvním znakem všech chybových hodnot je znak #. Hodnoty chyb závisí na typu chyby.

Excel nedokáže rozpoznat všechny chyby, ale ty, které jsou zjištěny, musí být možné opravit.

Chyba # # # # se objeví, když se zadané číslo nevejde do buňky. V tomto případě byste měli zvětšit šířku sloupce.

Chyba #DIV/0! se objeví, když se vzorec pokusí dělit nulou. Nejčastěji k tomu dochází, když je dělitel odkazem na buňku, který obsahuje prázdnou nebo prázdnou hodnotu.

Chyba #N/A! je zkratka pro výraz "undefined data". Tato chyba označuje, že vzorec používá prázdný odkaz na buňku.

Chyba #NAME? se objeví, když byl název použitý ve vzorci odstraněn nebo nebyl dříve definován. Chcete-li opravit, určit nebo opravit název datové oblasti, název funkce atd.

Chyba #EMPTY! se objeví, když existuje průsečík mezi dvěma oblastmi, které ve skutečnosti nemají společné buňky. Chyba nejčastěji označuje, že došlo k chybě při zadávání odkazů na rozsahy buněk.

Chyba #NUMBER! se objeví, když funkce s číselným argumentem používá nesprávný formát argumentu nebo hodnotu.

Chyba #HODNOTA! se objeví, když vzorec používá neplatný argument nebo typ operandu. Například místo číselné nebo logické hodnoty pro operátor nebo funkci byl zadán text.

Kromě uvedených chyb se při zadávání vzorců může objevit kruhový odkaz.

Kruhový odkaz nastane, když vzorec přímo nebo nepřímo obsahuje odkazy na vlastní buňku. Kruhový odkaz může způsobit zkreslení ve výpočtech na listu, a proto je ve většině aplikací považován za chybu. Když zadáte kruhový odkaz, zobrazí se varovná zpráva (Obrázek 89).

Chcete-li chybu opravit, odstraňte buňku, která způsobila kruhový odkaz, upravte nebo zadejte vzorec znovu.

Funkce v Excelu

Složitější výpočty v excelových tabulkách se provádějí pomocí speciálních funkcí (obr. 90). Po výběru příkazu je k dispozici seznam kategorií funkcí Funkce v nabídce Vložit (Vložit, Funkce).

Finanční funkce provádějí takové výpočty, jako je výpočet výše platby za půjčku, výše platby zisku z investic atd.

Funkce Datum a čas umožňují pracovat s hodnotami data a času ve vzorcích. Pomocí funkce můžete například ve vzorci použít aktuální datum DNES.

Rýže. 90. Průvodce funkcí

Matematické funkce fungují jednoduše a složité matematické výpočty, jako je výpočet součtu rozsahu buněk, absolutní hodnoty čísla, zaokrouhlování čísel atd.

Statistické funkce vám umožní provádět statistickou analýzu dat. Můžete například určit průměr a rozptyl vzorku a mnoho dalšího.

Databázové funkce lze použít k provádění výpočtů a výběru záznamů na základě podmínek.

Textové funkce poskytnout uživateli možnost zpracovávat text. Pomocí této funkce můžete například zřetězit více řetězců PŘIPOJIT.

Logické funkce jsou navrženy pro testování jedné nebo více podmínek. Například funkce IF umožňuje určit, zda je zadaná podmínka pravdivá, a vrací jednu hodnotu, pokud je podmínka pravdivá, a jinou, pokud je nepravdivá.

Funkce Kontrola vlastností a hodnot jsou určeny k určení dat uložených v buňce. Tyto funkce kontrolují hodnoty v buňce podle podmínky a vrací hodnoty v závislosti na výsledku PRAVDA nebo NEPRAVDA.

Chcete-li provádět tabulkové výpočty pomocí vestavěných funkcí, doporučujeme použít Průvodce funkcí. Když vyberete příkaz, zobrazí se dialogové okno Průvodce funkcí Funkce v nabídce Vložit nebo stisknutím tlačítka , na standardním panelu nástrojů. Během dialogu s průvodcem musíte zadat argumenty vybrané funkce, abyste to udělali, musíte vyplnit pole v dialogovém okně odpovídajícími hodnotami nebo adresami buněk tabulky.

Cvičení

Vypočítejte průměrnou hodnotu pro každý řádek v souboru Education.xls.

Vyberte buňku F3 a klikněte na tlačítko Průvodce funkcí.
V prvním dialogovém okně Průvodce funkcí z kategorie Statistika vyberte funkci PRŮMĚRNÝ, klikněte na tlačítko Další.
Druhé dialogové okno Průvodce funkcí musí poskytovat argumenty. Vstupní kurzor je ve vstupním poli prvního argumentu. V tomto poli jako číslo argumentu! zadejte adresu rozsahu B3:D3 (obr. 91).
Klikněte OK.
Výsledný vzorec zkopírujte do buněk F4:F6 a uložte soubor (tabulka 28).

Rýže. 91. Zadání argumentu v Průvodci funkcí

Tabulka 28. Tabulka výsledků výpočtů pomocí průvodce funkcí

	A	V	S	D	E	F
1	Rozdělení zaměstnanců podle vzdělání
2		Magnólie	Lilie	Fialový	Celkový	Průměrný
3	Vyšší	25	20	9	54	18
4	Sekundární speciál	28	23	21	72	24
8	Odborná škola	27	58	20	105	35
PROTI	Ostatní	8	10	9	27	9
7	Celkový	88	111	59	258	129

Chcete-li zadat rozsah buněk do okna Průvodce funkcí, můžete tento rozsah zakroužkovat myší na listu tabulky (v příkladu B3:D3). Pokud okno Průvodce funkcí pokrývá požadované buňky, můžete dialogové okno přesunout. Po výběru rozsahu buněk (B3:D3) se kolem něj objeví běžící tečkovaný rámeček a v poli argumentu se automaticky objeví adresa vybraného rozsahu buněk.

Excel je nezbytný, když potřebujete uspořádat, zpracovat a uložit velké množství informací. Pomůže to zautomatizovat výpočty, takže budou jednodušší a spolehlivější. Vzorce v Excelu umožňují provádět libovolně složité výpočty a okamžitě získávat výsledky.

Jak napsat vzorec v Excelu

Než se to naučíte, je třeba pochopit několik základních principů.

Každý začíná znakem „=“.
Hodnoty a funkce buněk se mohou účastnit výpočtů.
Operátory se používají jako známé matematické symboly pro operace.
Když vložíte záznam, buňka standardně zobrazí výsledek výpočtu.
Design si můžete prohlédnout v řádku nad tabulkou.

Každá buňka v Excelu je nedělitelná jednotka s vlastním identifikátorem (adresou), který je označen písmenem (číslo sloupce) a číslem (číslo řádku). Adresa se zobrazí v poli nad tabulkou.

Jak tedy vytvořit a vložit vzorec v Excelu? Postupujte podle následujícího algoritmu:

Označení Význam

Přidání
- Odečítání
/ Divize
* Násobení

Pokud potřebujete zadat číslo místo adresy buňky, zadejte ji z klávesnice. Chcete-li zadat záporné znaménko ve vzorci aplikace Excel, klikněte na "-".

Jak zadávat a kopírovat vzorce v Excelu

Zadávají se vždy po kliknutí na „=“. Co ale dělat, když podobných výpočtů existuje mnoho? V tomto případě můžete jeden určit a pak jej jednoduše zkopírovat. Chcete-li to provést, zadejte vzorec a poté jej „natáhněte“ v požadovaném směru, abyste jej vynásobili.
Umístěte ukazatel nad buňku, kterou chcete zkopírovat, a přesuňte ukazatel myši do pravého dolního rohu (nad čtverec). Měl by mít podobu jednoduchého kříže se stejnými stranami.

Stiskněte levé tlačítko a přetáhněte.

Uvolněte, když potřebujete zastavit kopírování. V tuto chvíli se zobrazí výsledky výpočtu.

Můžete ho také natáhnout doprava.
Přesuňte ukazatel na sousední buňku. Uvidíte stejný záznam, ale s různými adresami.

Při kopírování tímto způsobem se čísla řádků zvýší, pokud dojde k posunu dolů, nebo se zvýší čísla sloupců, pokud se posune doprava. Tomu se říká relativní adresování.
Do tabulky zadáme hodnotu DPH a vypočítáme cenu včetně daně.

Cena včetně DPH je kalkulována jako cena*(1+DPH). Zadáme sekvenci do první buňky.

Zkusme záznam zkopírovat.

Výsledek byl zvláštní.

Zkontrolujeme obsah ve druhé buňce.

Jak je vidět, při kopírování se posunula nejen cena, ale i DPH. A potřebujeme, aby tato buňka zůstala pevná. Pojďme to ukotvit absolutním odkazem. Chcete-li to provést, přesuňte ukazatel na první buňku a klikněte na adresu B2 v řádku vzorců.

Stiskněte F4. K adrese bude připojen znak „$“. To je znak absolutní buňky.

Nyní po zkopírování zůstane adresa B2 nezměněna.
Pokud jste omylem zadali data do nesprávné buňky, jednoduše je přesuňte. Chcete-li to provést, přesuňte ukazatel myši na libovolný okraj, počkejte, až bude myš vypadat jako kříž se šipkami, stiskněte levé tlačítko a táhněte. Na požadovaném místě jednoduše uvolněte manipulátor.

Použití funkcí pro výpočty

Excel nabízí velké množství funkcí, které jsou rozděleny do kategorií. Úplný seznam můžete zobrazit kliknutím na tlačítko Fx vedle řádku vzorce nebo otevřením sekce „Vzorce“ na panelu nástrojů.

Pojďme se bavit o některých funkcích.

Jak nastavit vzorce "Pokud" v Excelu

Tato funkce umožňuje nastavit podmínku a provést výpočet v závislosti na její pravdivosti či nepravdivosti. Pokud je například množství prodaného zboží více než 4 balení, měli byste zakoupit více.
Pro vložení výsledku v závislosti na podmínce přidáme do tabulky další sloupec.

V první buňce pod záhlavím tohoto sloupce nastavte ukazatel a klikněte na položku „Boolean“ na panelu nástrojů. Zvolme funkci "Pokud".

Stejně jako při vkládání jakékoli funkce se vám otevře okno pro vyplnění argumentů.

Naznačíme stav. Chcete-li to provést, klikněte na první řádek a vyberte první buňku „Prodáno“. Dále vložte znak „>“ a uveďte číslo 4.

Do druhého řádku napíšeme „Nákup“. Tato zpráva se zobrazí u položek, které byly vyprodané. Poslední řádek může zůstat prázdný, protože pokud je podmínka nepravdivá, neprovedeme žádnou akci.

Klikněte na OK a zkopírujte záznam pro celý sloupec.

Aby se v buňce nezobrazovalo „FALSE“, spusťte funkci znovu a opravte ji. Umístěte ukazatel na první buňku a stiskněte Fx vedle řádku vzorců. Vložte kurzor na třetí řádek a mezi uvozovky vložte mezeru.

Poté OK a znovu zkopírujte.

Nyní vidíme, který produkt by měl být zakoupen.

Text vzorce v Excelu

Tato funkce umožňuje použít formát na obsah buňky. V tomto případě je jakýkoli typ dat převeden na text, což znamená, že je nelze použít pro další výpočty. Přidejme sloupec pro formátování součtu.

Do první buňky zadejte funkci (tlačítko „Text“ v části „Vzorce“).

V okně argumentů označíme odkaz na buňku celkového množství a nastavíme formát na „#rub“.

Klikněte na OK a zkopírujte.

Pokud se pokusíme tuto částku použít ve výpočtech, zobrazí se nám chybová zpráva.

"VALUE" znamená, že výpočty nelze provést.
Příklady formátů můžete vidět na snímku obrazovky.

Vzorec data v Excelu

Excel nabízí mnoho možností pro práci s daty. Jeden z nich, DATE, umožňuje sestavit datum ze tří čísel. To se hodí, pokud máte tři různé sloupce – den, měsíc, rok.

Umístěte ukazatel na první buňku čtvrtého sloupce a vyberte funkci ze seznamu Datum a čas.

Podle toho uspořádejte adresy buněk a klepněte na OK.

Zkopírujte záznam.

AutoSum v Excelu

V případě, že potřebujete sečíst velké množství dat, Excel nabízí funkci SUM. Spočítejme si například částku za prodané zboží.
Umístěte ukazatel do buňky F12. Vypočítá celkovou částku.

Přejděte na panel Vzorce a klikněte na AutoSum.

Excel automaticky zvýrazní nejbližší číselný rozsah.

Můžete vybrat jiný rozsah. V tomto příkladu provedl Excel vše správně. Klepněte na tlačítko OK. Věnujte pozornost obsahu buňky. Funkce SUM byla nahrazena automaticky.

Při vkládání rozsahu se zadává adresa první buňky, dvojtečka a adresa poslední buňky. ":" znamená "Vezměte všechny buňky mezi první a poslední. Pokud potřebujete uvést více buněk, oddělte jejich adresy středníkem:
SOUČET (F5;F8;F11)

Práce v Excelu se vzorci: příklad

Řekli jsme vám, jak vytvořit vzorec v Excelu. To jsou znalosti, které se mohou hodit i v běžném životě. Můžete spravovat svůj osobní rozpočet a kontrolovat výdaje.

Snímek obrazovky ukazuje vzorce, které se zadávají pro výpočet částek příjmů a výdajů, a také výpočet zůstatku na konci měsíce. Přidejte listy do sešitu pro každý měsíc, pokud nechcete všechny listy na jednom. Chcete-li to provést, jednoduše klikněte na „+“ ve spodní části okna.

Chcete-li list přejmenovat, poklepejte na něj a zadejte název.

Stůl lze vyrobit ještě detailněji.
Excel je velmi užitečný program a jeho výpočty poskytují téměř neomezené možnosti.