Provádění výpočtů v excelu. Jak vypočítat v aplikaci Microsoft Excel

Zdrojová data a vzorce pro výpočet výsledných hodnot se zadávají do tabulek. Jakákoli změna ve zdrojových datech způsobí automatickou změnu výsledků. Tabulkové procesory se používají k provádění matematických, finančních, statistických a technických výpočtů. Například: výpočet mezd, výpočet nájemného atd.

Hlavní výhody MS Excel:

1. Stejně jako ostatní tabulky se na obrazovce Excelu zobrazí mřížka řádků a sloupců – eBookúčetnictví, do kterého se údaje zapisují. Zavolá se každá buňka mřížky (průsečík řádku a sloupce). buňka. Do buněk se obvykle zadávají čísla, ale můžete tam umístit i popisný text, například záhlaví řádků a sloupců.

2. Excel může provádět výpočty a výstupní výsledky. Jakákoli buňka může ukládat výsledky výpočtů provedených s obsahem jakékoli jiné buňky tabulky.

3. Obsah buňky lze snadno změnit. A nejlepší na tom je, že pokud změníte číslo, které se používá ve výpočtu někde jinde v tabulce, výsledek tohoto výpočtu se automaticky změní.

4. Data lze uspořádat tak, aby byla snadno čitelná na obrazovce i v tisku. Můžete umístit nadpisy do středu, zvýraznit text tučně a kurzíva, obrys důležitých buněk s tučným okrajem a mnoho dalšího. Ale pozor, nepřehánějte to! Příliš mnoho velký počet Formátování prvků může učinit tabulku obtížnější než čitelnou.

5. Excel umí vytvářet různé tabulky a grafy, které umožňují přehlednější prezentaci dat (obr. 1.2). Tyto grafy a diagramy se snadno sestavují a umožňují rychlejší analýzu informací; vypadají přesvědčivěji než les sloupců čísel

Základem struktury listu Excel je sloupců, které jsou uspořádány shora dolů a, linky, přecházející zleva doprava. Sloupce jsou označeny písmeny a řádky čísly. Záhlaví sloupců jsou písmena A, B, C... a záhlaví řádků jsou čísla 1, 2, 3 atd. Na průsečíku každého sloupce a každého řádku je malý obdélník tzv buňka. Každá buňka má svou adresu, která je určena odpovídajícím sloupcem a řádkem. Například na průsečíku sloupce C a řádku 3 se nachází buňka C3.

Můžete pracovat s jednou buňkou nebo můžete vybrat několik buněk a pracovat s nimi současně. Zavolá se kolekce několika vybraných buněk rozsah.

Pracovní list- Toto je ve skutečnosti tabulka, hlavní typ dokumentu používaného v Excelu k ukládání a manipulaci s daty. Skládá se z buněk uspořádaných do sloupců a řádků a je vždy součástí sešitu.

Pracovní sešity jsou soubory MS Excel, které mohou obsahovat jeden nebo více listů.

Hlavní prvky okna Excelu (obr. 9.1):

1. Pole názvu hlavního okna.
2. Lišta nabídek.
3. Ovládací tlačítka
4. Panely nástrojů.
5. Název pole.
6. Formulová čára.
7. Stavový řádek
8. Pole názvu okna sešitu (podřízené okno Excelu).
9. Aktivní list sešitu.
10. Tlačítko pro výběr celého listu.
11. Aktivní (aktuální) buňka.
12. Název sloupce.
13. Název řetězce.

Rýže. 9.1 Prvky pracovního listu

Buňka je hlavním prvkem tabulky, pouze ona může obsahovat jakékoli informace. Buňka tabulky může obsahovat:

text;
číslo;
datum;
vzorec.

Obvykle samotné vzorce nejsou na obrazovce vidět. Místo vzorců se v odpovídajících buňkách zobrazují výsledky výpočtů pomocí těchto vzorců. Kdykoli se změní zdrojová data, vzorce se automaticky přepočítají.

Struktura Excel buňky:

Úroveň 1 obsahuje obraz viditelný na obrazovce (tj. formátovaný text) nebo výsledek výpočtu vzorce).
2. úroveň obsahuje formáty buněk (formát čísel, fonty, přepínač indikující, zda se má buňka zobrazit nebo ne, typ rámce, ochrana buňky).
Úroveň 3 obsahuje vzorec, který se může skládat z textu, čísel nebo vestavěných funkcí.
4. úroveň obsahuje název buňky, tento název lze použít ve vzorcích jiných buněk, přičemž poskytuje absolutní adresování této buňky.
Úroveň 5 obsahuje poznámky pro tuto buňku (volný text). Pokud buňka obsahuje poznámku, pak vpravo horní roh objeví se červený čtvereček (tečka).

Obr.9.2. Úrovně buněk

Blok buněk je skupina buněk.

Vstupní řádek - pruh pod panely nástrojů Microsoft Excel, který se používá k zadávání nebo úpravě hodnot nebo vzorců v buňkách nebo grafech. Zobrazí konstantní hodnotu nebo vzorec aktivní buňky. Chcete-li zadat data, vyberte buňku, zadejte data a klikněte na tlačítko se zeleným „zaškrtnutím“ nebo stiskněte ENTER. Data se zobrazují v řádku vzorců, když je píšete.

Pole názvu je na levém konci vstupního řádku. Pomocí pole názvu pojmenujte aktivní buňku nebo vybraný blok. Chcete-li to provést, klikněte na pole názvu, zadejte tam jméno a stiskněte ENTER. Takové názvy lze použít při psaní vzorců nebo při konstrukci diagramů. Pole názvu můžete také použít k přeskočení na pojmenovanou buňku nebo blok. Chcete-li to provést, otevřete seznam a vyberte z něj požadované jméno.

Data v Excelu se zobrazují na obrazovce ve specifickém formátu. Ve výchozím nastavení se informace zobrazují ve formátu Obecné. Můžete změnit formát prezentace informací ve vybraných buňkách (Formát | Buňky).

Výpočty v Excelu. Vzorce a funkce

Vzorec v Excelu je posloupnost znaků začínající rovnítkem „=“. Tato sekvence znaků může zahrnovat konstantní hodnoty, odkazy na buňky, názvy, funkce nebo operátory. Výsledkem vzorce je nová hodnota, která se vypíše jako výsledek výpočtu vzorce pomocí existujících dat.

Pokud se změní hodnoty v buňkách odkazovaných ve vzorcích, výsledek se automaticky změní.

Odkaz jednoznačně identifikuje buňku nebo skupinu buněk v listu. Odkazy označují, které buňky obsahují hodnoty, které je třeba použít jako argumenty vzorce. Pomocí odkazů můžete ve vzorci použít data na různých místech listu a také můžete použít hodnotu stejné buňky v několika vzorcích.

Používání jmen poskytuje následující výhody:

Vzorce, které používají názvy, jsou snadněji pochopitelné a zapamatovatelné než vzorce, které používají odkazy na buňky.
Například vzorec „=Aktiva-pasiva“ je mnohem jasnější než vzorec „=F6-D6“.
Když změníte strukturu listu, stačí aktualizovat odkazy na jednom místě - v definici názvů a všechny vzorce, které používají tyto názvy, budou používat správné odkazy.
Jakmile je název definován, lze jej použít kdekoli v sešitu. Ke všem názvům z libovolného listu lze přistupovat pomocí pole se jménem na levé straně řádku vzorců.
Můžete také definovat speciální názvy, jejichž rozsah je omezen na aktuální list. To znamená, že tyto názvy lze použít pouze na listu, ve kterém jsou definovány. Takové názvy se nezobrazí v poli Název řádku vzorců nebo v dialogovém okně Přiřadit název, pokud je aktivní jiný list v sešitu.
Excel automaticky generuje názvy na základě záhlaví řádků a sloupců listu. Detailní informace o vytváření takových jmen je obsaženo v kapitole „Databáze“.
Jakmile je název definován, můžete:
Nahraďte všechny odpovídající odkazy tímto názvem na všech místech listu.
Rychle přejděte na pojmenovaný odkaz, nahraďte odkazy, vložte odkaz do vzorce pomocí pole názvu na řádku vzorců.

Jakmile je vzorec zadán do buňky, můžete jej přesouvat, kopírovat nebo rozprostírat po bloku buněk. Když vzorec přesunete na nové místo v tabulce, odkazy ve vzorci se nezmění a buňka, kde byl vzorec, se uvolní.

Při kopírování vzorců je nutné spravovat změny adres buněk nebo odkazů. Za tímto účelem se před symboly adresy buňky nebo odkazu umístí symboly „$“. Změní se pouze ty atributy adresy buňky, které nepředchází symbol „$“. Pokud před všechny atributy adresy buňky vložíte symbol „$“, pak se při zkopírování vzorce odkaz nezmění.

Absolutní adresy se při přesouvání vzorců nemění, ale v relativních adresách dochází k posunu o výši převodu.

Funkce v Excelu se používají k provádění standardních výpočtů v sešitech. Hodnoty, které se používají k vyhodnocení funkcí, se nazývají argumenty. Hodnoty vrácené funkcemi jako odpověď se nazývají výsledky. Kromě vestavěných funkcí můžete ve výpočtech, které se vytvářejí pomocí nástrojů aplikace Excel, používat vlastní funkce.

Chcete-li použít funkci, musíte ji zadat jako součást vzorce v buňce listu. Posloupnost, ve které se musí objevit symboly použité ve vzorci, se nazývá syntaxe funkce. Všechny funkce používají stejná základní pravidla syntaxe. Pokud porušíte pravidla syntaxe, Excel zobrazí zprávu, že je ve vzorci chyba.

Pokud se funkce objeví na samém začátku vzorce, musí před ní být znaménko rovná se, jako v každém jiném vzorci.

Argumenty funkce se zapisují do závorek bezprostředně za názvem funkce a oddělují se od sebe středníkem „;“. Závorky umožňují Excelu určit, kde začíná a končí seznam argumentů. Argumenty musí být umístěny v závorkách. Pamatujte, že při psaní funkce musí existovat otevírací a uzavírací závorky a mezi název funkce a závorky byste neměli vkládat mezery.

Argumenty mohou být čísla, text, logické hodnoty, pole, chybové hodnoty nebo odkazy. Argumenty mohou být buď konstanty, nebo vzorce. Tyto vzorce mohou naopak obsahovat další funkce. Funkce, které jsou argumentem jiné funkce, se nazývají vnořené. Vzorce aplikace Excel mohou používat až sedm úrovní vnořených funkcí.

Pro usnadnění použití jsou funkce v Excelu rozděleny do kategorií: funkce pro správu databáze a seznamů, funkce data a času, DDE/Externí funkce, inženýrské funkce, finanční, informační, logické, prohlížecí a propojovací funkce. Kromě toho jsou přítomny následující kategorie funkcí: statistické, textové a matematické.

Pomocí textových funkcí je možné zpracovávat text: extrahovat znaky, najít ty, které potřebujete, psát znaky na přesně definované místo v textu a mnoho dalšího.

Pomocí funkcí data a času můžete vyřešit téměř jakýkoli problém související s datem nebo časem (například určení věku, výpočet pracovních zkušeností, určení počtu pracovních dnů v libovolném časovém úseku).

Logické funkce pomáhají vytvářet složité vzorce, které v závislosti na splnění určitých podmínek budou provádět různé druhy zpracování dat.

Excel je široce zastoupen matematické funkce. Můžete například provádět různé operace s maticemi: násobit, najít inverzní, transponovat.

Pomocí statistických funkcí je možné provádět statistické modelování. Dále je možné využít prvky faktorové a regresní analýzy.

Excel umí řešit optimalizační problémy a používat Fourierovu analýzu. Excel zejména implementuje rychlý algoritmus Fourierovy transformace, pomocí kterého můžete sestavit amplitudové a fázové spektrum.

Excel obsahuje více než 400 vestavěných funkcí. Proto není vždy vhodné přímo zadávat názvy funkcí a hodnoty vstupních parametrů do vzorce z klávesnice. Excel má speciální opravný prostředek pro práci s funkcemi - Function Wizard. Při použití tohoto nástroje budete nejprve vyzváni k výběru požadovanou funkci ze seznamu kategorií a poté vás dialogové okno vyzve k zadání vstupních hodnot.

Průvodce funkcí se vyvolá příkazem Vložit | Funkce nebo kliknutím na tlačítko Průvodce funkcí

Chyby ve vzorcích

Pokud vzorec není zpracován správně, aplikace Microsoft Excel zobrazí chybu. Důvody chyb mohou být velmi různé:

##### - výsledek zpracování vzorce se nevejde do buňky nebo výsledek provedení vzorce pracujícího s daty a časy je záporné číslo.
#HODNOTA! - je použit neplatný argument nebo typ operandu.
#DIV/0! - vzorec se pokouší dělit nulou.
#NÁZEV? - Excel nemůže rozpoznat název použitý ve vzorci.
#N/A - nedefinovaná data (nejčastěji se vyskytuje, pokud jsou nesprávně definovány argumenty funkce).
#ODKAZ! - Používá se neplatný odkaz na buňku (například buňky, na které vzorec odkazuje, byly smazány).
#ČÍSLO! - Vrácená číselná hodnota je příliš velká nebo příliš malá na to, aby byla reprezentována v aplikaci Microsoft Excel (rozsah zobrazených čísel je -10307 až 10307).
#PRÁZDNÝ! - je určen průnik dvou oblastí, které ve skutečnosti nemají společné buňky.

K chybám může dojít nejen kvůli nesprávnému zpracování vzorce, ale chyba může být obsažena v buňce, na kterou vzorec odkazuje.

Popis různých funkcí Excelu

Funkce	Syntax	Popis	Příklad
SOUČET	SUM(Rozsah![; Rozsah2; ... ])	Vypočítá součet obsahů buněk v určených rozsazích	CYMM(D2:D4) SUM(02:04; F2:F4)
PRŮMĚRNÝ	AVERAGE(Rozsah![; Rozsah2; ... ])	Vypočítá aritmetický průměr obsahu buněk v určených rozsazích	PRŮMĚR (B2:B11)
KOLO	ROUND(číslo, místa]	Vrátí hodnotu získanou zaokrouhlením čísla na zadaný počet číslic.	ROUND(E8;2) ROUND(SUM(EZ:E7);2)
OKRVVERH	OKRVVE RH(číslo; přesnost)	Vrátí hodnotu vyplývající ze zaokrouhlení čísla nahoru na zadanou přesnost.	OKRVERH(351,10) 360 OKRVERCH(353,100) 400 OKRVERCH(125300,-1000) 126000 OKRVERH(23,345,0,1) 23,4 OKRVERCH(7,513,0,01) 7,513,0,01
OKRVNIZ	OKRVNIZ(číslo; přesnost)	Vrátí hodnotu vyplývající ze zaokrouhlení čísla dolů na zadanou přesnost. Funkci lze použít k zaokrouhlení zlomkových čísel. V tomto případě parametr přesnost určuje, na jakou číslici má být zaokrouhlení provedeno.	OKRVNIZ(351;10) 350 OKRVNIZ(353;100) 300 OKRVNIZ(125300;1000) 125000 OKRVNIZ(2,447;001) 2,44 OKRVNIZ(2,99;1) 2
OST AT	REMAINDER(Dividenda; Dělitel)	Vypočítá zbytek, když je jedno číslo děleno druhým
CELÝ	INTEGER (výraz)	Vrátí celočíselnou část hodnoty výrazu	INTEGER(D5/B1) INTEGER(F5/3)
MAX	MAX(Rozsah![; Rozsah2, ... ])	Vrátí maximální hodnotu zadaných rozsahů	MAX(B2:B4)
MIN	MIN(Rozsah![; Rozsah2; ... ])	Vrátí minimální hodnotu zadaných rozsahů	MIN(S2:S4)
ŠEK	COUNT (rozsah)	Vrátí počet buněk v zadaném rozsahu, které obsahují čísla, včetně buněk vypočítaných vzorcem	C4ET(D3:D10) C4ET(D3:E10)
COUNTIF	COUNTIF(rozsah, kritérium)	Spočítá počet buněk v rozsahu, které splňují zadané kritérium. Jako podmínku můžete použít číslo, řetězec znaků nebo výraz operátoru tvarové konstanty. Operátor je jedním z matematických porovnávacích operátorů: - více; < - меньше; >= - větší nebo rovno; <= - меньше или равно; = - rovno; o - nerovná se. Konstanta - počet nebo řetězec znaků, kritérium musí být uzavřeno do uvozovek.	COUNTIF(B2:B10; "<>0") - spočítá počet buněk v rozsahu B2:su, jejichž obsah se nerovná nule COUNTIF (C2: SI; ">1000") - spočítá počet buněk v rozsahu B2:su, jejichž obsah je větší než 1000
PRÁZDNÝ	PRÁZDNÝ(buňka]	Pokud je buňka prázdná, funkce EMPTY vrátí logickou hodnotu TRUE	PRÁZDNÝ(C2)
LI	IF(Podmínka; Hodnota!; Hodnota2)	V závislosti na hodnotě podmínky vrací hodnotu! nebo Hodnota2	IF(P5>500;0,1;0) pokud je obsah buňky D5 větší než 500, pak je funkční hodnota 0,1, jinak (pokud je D5 menší nebo rovna 500) je funkční hodnota nula.
VÝBĚR	SELECT(Index; Prvek!; Prvek2; . . .)	Vrátí prvek seznamu, jehož číslo je zadáno jako první parametr funkce. Prvek seznamu může být číslo, znakový řetězec nebo rozsah. Maximální částka prvky seznamu jsou 29	SELECT(02;В2:В10; С2:С10; D2:D10) SELECT(WEEKDAY(DNES 0.2);"PO";"ÚT";"ST";"ČT";"Pá";"SO"; "VS ")
VYHLEDÁVÁNÍ	MATCH(hodnota, rozsah, typ)	Vyhledá hodnotu v rozsahu. Parametr type určuje metodu shody	MATCH(1500;C2:C20;1)
INDEX	INDEX(RangeRow, ItemNumber) INDEX(RangeColumn, ItemNumber) INDEX(RangeArea, Řádek, sloupec])	Vrátí prvek rozsahu. Pokud je rozsahem řádek nebo sloupec, pak funkce vrátí prvek "se zadaným číslem. Pokud je rozsahem oblast, pak hodnotou funkce je prvek umístěný na průsečíku zadaného řádku a sloupce	INDEX(A2:A4; 2) INDEX(A2:E2; 2) INDEX(A2:C4; 2;2)
ROK	ROK (datum)	Vrátí rok zadaného data. Název buňky tabulky se obvykle používá jako parametr funkce rok.	ROK (B2)
DEN	DEN (datum)	Vrátí den zadaného data. Název buňky tabulky se obvykle používá jako parametr funkce DAY.	DEN(B2) DEN(DNES())
VŠEDNÍ DEN	WEEKDAY (Datum [; Typ])	Vrátí číslo dne v týdnu zadaného data. Parametr type určuje den, kterým týden začíná. Pokud parametr není zadán nebo je roven 1, je prvním dnem v týdnu neděle. Pokud je parametr Typ 2, pak je prvním dnem v týdnu pondělí	WEEKDAY(B2) WEEKDAY(W2;2) WEEKDAY(TODAY();2)
MĚSÍC	MĚSÍC (datum)	Vrátí číslo měsíce zadaného data. Název buňky tabulky se obvykle používá jako parametr funkce MONTH. Chcete-li získat název měsíce, můžete použít funkci SELECT	MĚSÍC(B2) MĚSÍC(DNES())
DNES	DNES()	Vrátí aktuální datum. Obsah buňky obsahující funkci DNES se aktualizuje při každém otevření tabulky	DNES()
TDATE()	TDATE()	Vrátí aktuální datum a čas. Obsah buňky obsahující funkci TDATE se automaticky aktualizuje při každém otevření dokumentu. Pokud musí být obsah buňky aktualizován bezprostředně před tiskem tabulky, musíte stisknutím klávesy aktivovat proces přepočítávání vzorců. Aby buňka obsahující funkci TDATE zobrazovala pouze čas (jako v příkladu výše), musí být formát této buňky nastaven na TIME.	TDATE()

Série

Excel vyvinul mechanismus pro zadávání datových řad (řad). Řady rozumíme datové řady konstruované podle určitého zákona. Data navíc nemusí být číselná, může to být i formalizovaný text. Vytváření sérií je možné několika způsoby, samostatnou podsekcí vytváření sérií je Progrese.

Chcete-li sestavit libovolnou řadu, spusťte Edit | Vyplňte | Postup.

Typy progrese:

Aritmetický
Geometrický
Termíny
automatické doplňování

Kromě postupu lze série vytvářet několika dalšími způsoby bez použití nabídky, jsou uvedeny níže.

První způsob

Do buňky se zavede první člen řady. Přesuňte ukazatel myši na černý čtverec v pravém dolním rohu vybrané buňky (v tomto okamžiku bílý kříž zčerná) a stiskněte levé tlačítko myši. Podržte tlačítko myši a vyberte část řádku nebo sloupce, kterou chcete. Vybraná oblast bude vyplněna daty.

Tento jednoduchý proces zadávání je možný pouze tehdy, když je do buňky vložen jeden z prvků existujícího seznamu. Seznamy se vytvářejí v souboru | Parametry na kartě Seznamy.

Druhý způsob

Datovou řadu můžete sestavit jiným způsobem, pokud určíte krok konstrukce. Chcete-li to provést, musíte ručně zadat druhý termín budoucího řádku, označit obě buňky a poté pomocí pravého dolního rohu výběru pokračovat ve výběru do požadované oblasti. První dvě ručně zadané buňky definují krok datové řady.

Tato metoda je nejvhodnější pro vytváření jednoduchých řad (jako je aritmetický postup).

Třetí způsob

Třetí metoda je nejuniverzálnější pro vytváření řad libovolného digitálního typu, aritmetických a geometrických posloupností, mocninných řad a dalších komplexních řad.

Chcete-li to provést, zadejte jeho počáteční hodnotu do první buňky řady, zadejte vzorec definující tuto řadu do druhé buňky řady a stiskněte ENTER. Poté pomocí pravého dolního rohu výběru pokračujte ve výběru do požadované oblasti, podobně jako v první a druhé metodě.

Jak bylo uvedeno výše, třetí metoda je nejuniverzálnější.

Tabulky a grafy

Grafy slouží k vizuální prezentaci dat v tabulce. V praxi se nejčastěji používají histogramy a grafy. Na histogramu jsou data znázorněna jako sloupce (obr. 9.3), na grafu jako tečky spojené čarami.

Rýže. 9.3

K zobrazení procesů v čase se obvykle používají grafy. Histogramy jsou vhodné pro vizuální znázornění vztahu mezi veličinami.

Čára v grafu nebo skupina sloupců histogramu zobrazují řadu dat – obsah několika sousedních buněk tabulky. V jednom grafu můžete zobrazit více datových řad.

Kromě histogramů a grafů jsou široce používány koláčové grafy, které jsou vhodné pro vizuální znázornění vztahu veličin, které tvoří celek. Například koláčový graf zobrazený na Obr. 9.4 dává jasnou představu o struktuře rodinných výdajů.

Prezentace dat v grafické podobě umožňuje řešit širokou škálu problémů. Hlavní výhodou takové prezentace je přehlednost. Trend ke změně je dobře patrný z grafů. Můžete dokonce určit rychlost, s jakou se trend mění. Různé poměry, růst, vzájemný vztah různých procesů – to vše lze snadno vidět na grafech.

Rýže. 9.4

Existují tři typy grafů:

pravidelný;
s akumulací;
normalizované.

Chcete-li vytvořit diagram, použijte Vložit | Graf nebo klepněte na tlačítko Průvodce grafem

Chcete-li upravit graf:

Poklepejte na libovolné místo v grafu.
Příkaz Formát symbolu legendy nastavuje barvu, styl a tloušťku čáry.
Chcete-li naformátovat objekt grafu, klikněte na něj pravé tlačítko myši a ze zobrazeného seznamu vyberte příkaz, který potřebujete naformátovat.
Chcete-li nahradit jednu datovou řadu v grafu jinou, použijte příkaz Formátovat řadu.
Změnou čar grafu můžete změnit data na listu.
Příkaz Typ grafu umožňuje změnit typ existujícího grafu.
Příkaz Automatický formát nejen změní typ grafu, ale také nastaví standardní parametry grafu.
Příkaz 3D pohled změní prostorovou orientaci diagramu.

Formátování grafu

Formátování grafu se týká procesu změny vzhled diagramy. Během procesu formátování můžete například změnit písmo, kterým je zobrazen nadpis grafu, barvu prvků grafu (sloupce grafu, čáry grafu), vzhled čar mřížky atd. Následující prvky lze rozlišené na grafu (obr. 9.5):

oblast mapy;
oblast mapy;
titul;
hodnotová osa;
název hodnotové osy;
osa kategorie;
legenda;
datové řady.

Rýže. 9.5

Formátování grafu se provádí formátováním jednotlivých prvků.

Základní formáty

Nejběžnější formáty používané v tabulkách aplikace Excel jsou obecné, číselné, peněžní, finanční a datumové. Níže je Stručný popis tyto formáty.

Obecný formát

Všechny buňky v novém listu jsou nastaveny na obecný formát. V tomto formátu se čísla zobrazují tak, jak je uživatel zadal. Pokud je hodnota buňky zlomkové číslo (zadané uživatelem nebo vypočítané podle vzorce), pak počet zobrazených zlomkových číslic závisí jak na šířce buňky, tak na vlastnostech písma použitého k zobrazení obsahu buňky. Zobrazení zlomkových hodnot zaokrouhlí zlomkové číslice, které nelze zobrazit, protože buňka není dostatečně široká.

Formát čísel

Nejuniverzálnější je formát čísel (obr. 9.6). - Na rozdíl od obecného formátu vám číselný formát umožňuje nastavit počet zobrazených desetinných míst (počet desetinných míst). Při zobrazování zlomkových čísel se zaokrouhlování provádí podle zadaného počtu zlomkových číslic. Pokud je například formát nastaven na zobrazení dvou zlomkových čísel, bude číslo 567,897 reprezentováno jako 567,90. Stejné číslo se zobrazí jako 568, pokud nastavíte formát bez zobrazení zlomkových číslic (nastavte počítadlo Počet desetinných míst na nulu).

Rýže. 9.6 Vyberte číselný formát, můžete upravit jeho vlastnosti

Formát měny

Formát měny se používá k reprezentaci hodnot označujících peněžní množství. Když je číslo zobrazeno v peněžním formátu, za číslem se zobrazí označení měnové jednotky. Kromě toho jsou pro usnadnění vnímání odděleny skupiny číslic čísel. Pro formát měny můžete nastavit počet desetinných číslic, jednotku měny a způsob zobrazení záporných hodnot.

Zlomková čísla zobrazená ve formátu měny jsou zaokrouhlena podle zadaného počtu desetinných číslic. Zaokrouhlování se provádí podle známého pravidla: je-li hodnota číslice, kterou je třeba vyhodit, menší než pět, pak se zahodí, jinak se hodnota předchozí číslice zvýší o jednu. Pokud musí být vyřazeno několik bitů, pak se výše uvedené pravidlo použije postupně na všechny bity, které musí být vyřazeny, počínaje tím nejméně významným.

Finanční formát

Finanční formát umožňuje reprezentovat peněžní hodnoty. Stejně jako u peněžního formátu, i u finančního formátu můžete nastavit počet číslic zlomkové části a vybrat peněžní jednotku.

datum

Pokud je v buňce tabulky datum, můžete změnit jeho formát zobrazení (obr. 9.7).

Rýže. 9.7 Způsob zobrazení data musí být vybrán v seznamu Typ

Existují plné a zkrácené formáty data. Úplný formát odráží den, měsíc a rok. Zkrácený formát může představovat den a měsíc, někdy pouze měsíc nebo den. Ještě jednou pozor na oddělovací znak, který se používá při psaní dat. V Rusku o to jde.

Zájem

Když zobrazíte obsah buňky ve formátu procent, skutečná hodnota buňky se vynásobí 100 a za číslem se zobrazí znak procenta. Pokud například napíšete do buňky číslo 0,2 a nastavíte formát na Procento, pak se v této buňce zobrazí 20,00 %.

Grafika

Ilustraci můžete umístit na povrch listu sešitu. Může to být například logo společnosti na ceníku nebo hlavičkovém papíře. Ilustrace lze připravit předem pomocí různých grafické editory, jako je Microsoft Paint, nebo kreslit přímo v Excelu.

Zpracování dat

Excel je účinný nástroj pro analýzu široké škály dat. Ve většině případů předchází analýze předběžné zpracování dat: třídění, vzorkování (filtrace), výpočet mezilehlých (celkových za období) množství, průměrných hodnot, odchylek, sestavení kontingenčních tabulek.

Poměrně často se jako databáze používají excelové tabulky. Typickým příkladem jsou tabulky, které obsahují informace o zaměstnancích, zákaznících, sortimentu, prodejích atp.

Řazení

Pro usnadnění použití jsou informace v tabulkách obvykle uspořádány podle nějakého kritéria (seřazeny). Například seznam zaměstnanců je obvykle uspořádán abecedně.

Proces přeskupování řádků, buněk sloupců nebo řádků za účelem jejich uspořádání podle nějakého kritéria se nazývá řazení. Excel umožňuje seřadit jak buňky v jednom sloupci nebo řádku, tak celé řádky. Při řazení buněk se mění pouze obsah seřazeného sloupce nebo řádku, obsah ostatních buněk se nemění. Při třídění strun se přeskupují celé řetězce.

Při řazení řádků je jako kritérium použit obsah buněk jednoho ze sloupců tabulky, řádky jsou uspořádány podle obsahu buněk tohoto konkrétního klíčového sloupce (obr. 9.8).

Rýže. 9.8 Řádky tabulky jsou seřazeny podle obsahu sloupce Příjmení

Rozlišuje se vzestupné a sestupné řazení. Řádky tabulky jsou seřazeny vzestupně, pokud je obsah buňky klíčového sloupce následujícího řádku větší nebo roven obsahu buňky klíčového sloupce předchozího řádku. Řádky tabulky jsou seřazeny v sestupném pořadí, pokud je obsah buňky klíčového sloupce následujícího řádku menší nebo roven obsahu buňky klíčového sloupce předchozího řádku.

Nejjednodušší způsob, jak seřadit řádky tabulky ve vzestupném nebo sestupném pořadí, je použít jako klíčový sloupec první (zcela vlevo) sloupec.

Chcete-li seřadit řádky, musíte tyto řádky vybrat a kliknout na jedno z tlačítek Seřadit vzestupně nebo Seřadit sestupně.

Rýže. 9.9 Tlačítko pro provedení třídění

Excel umožňuje seřadit řádky podle obsahu libovolného více (ale ne více než tří) sloupců. V obecný případ, abyste mohli seřadit řádky tabulky, musíte tyto řádky vybrat a vybrat příkaz Seřadit z nabídky Data.

Filtry

Často uživatele nezajímají všechny informace v databázi, ale konkrétní výběr. Například informace o výdajích v lednu, seznam studentů ve skupině 221/2 atp.

Hledání potřebných informací se provádí výběrem záznamů, které splňují výběrové kritérium. Ve většině případů je kritériem výběru, aby se obsah pole rovnal určité hodnotě. Například kritériem pro výběr záznamů obsahujících informace o studentech ve skupině 221/2 je, že obsah pole Skupina je roven řádku 221/1. Kromě porovnávání rovnosti můžete při výběru záznamů použít další porovnávací operace. Například větší než, větší nebo rovno, menší než, menší nebo rovno. Použití těchto operací nám umožňuje formulovat výběrové kritérium méně rigidně.

Proces výběru záznamů z databáze, které splňují dané kritérium, se nazývá filtrování a kritérium (podmínka dotazu) se nazývá filtr.

Kontingenční tabulka

Kontingenční tabulka je tabulka, která shrnuje a analyzuje data z jedné nebo více tabulek. Zdrojová data pro kontingenční tabulku mohou být v seznamu na jednom listu sešitu, na více listech, v externí databázi nebo v jiné kontingenční tabulce. Změnou struktury tabulky můžete získat různé souhrnné výpisy stejných zdrojových tabulek.

Kontingenční tabulka se skládá z:

Pole stránky- toto je pole vašeho počátečního seznamu, do kterého byste měli shrnout. Toto pole je prezentováno jako rozevírací seznam, jehož prvky jsou hodnoty odpovídajícího sloupce ve zdrojovém seznamu. Při výběru určitou hodnotu ze seznamu se kontingenční tabulka přepočítá, aby zobrazila součty spojené s danou hodnotou.

Řetězcové pole je pole zdrojového seznamu umístěné v oblasti orientace řádku kontingenční tabulky. Hodnoty řádkového pole jsou hodnoty odpovídajícího pole ve zdrojovém seznamu.

Sloupcové pole je pole zdrojového seznamu umístěné v oblasti sloupců kontingenční tabulky. Hodnoty pro toto pole jsou převzaty z odpovídajícího sloupce ve zdrojové tabulce.

Datové pole je pole zdrojového seznamu, jehož hodnoty jsou zdrojem dat pro výpočty v kontingenční tabulce. Pokud existuje pouze jedno datové pole, nachází se v oblasti výběru tabulky. Pokud existuje několik takových polí, je jim přidělen samostatný sloupec s názvem Data.

Datová oblast- Toto je část kontingenční tabulky, která obsahuje souhrnná data. Buňky datové oblasti zobrazují součty datových polí pro položky polí řádků a sloupců odpovídající hodnotě pole stránky. Všechny zobrazené hodnoty odpovídají původním údajům.

Pro vytváření kontingenčních tabulek je k dispozici speciální nástroj - Průvodce kontingenční tabulkou (Data - Kontingenční tabulka).

Kontingenční tabulka se vyrábí ve třech krocích:

1. V prvním kroku Průvodce kontingenční tabulkou zjistí, kde je obsažen zdroj dat pro kontingenční tabulku.

2. Ve druhém kroku se určí samotný zdroj dat. Zde musíte určit souvislý rozsah, který obsahuje zdrojový seznam.

3. Zapnuto poslední stadium Průvodce zjistí umístění kontingenční tabulky.

V důsledku toho je přidán list se šablonou kontingenční tabulky a okno Pivot Table Field List, kde jsou uvedeny názvy polí zdrojové tabulky (obr. 9.10), aby se šablona kontingenční tabulky proměnila v kontingenční tabulky, musíte určit, jaká data ze zdrojové tabulky se mají odrazit v oblastech řádků, sloupců a která data ze zdrojové tabulky jsou datovými prvky kontingenční tabulky.

Rýže. 9.10 Nabídka karty kontingenční tabulky

Po přetažení všech uvedených polí se vygeneruje kontingenční tabulka.

Otázky k posouzení úrovně vašich znalostí

Co je to tabulka?
Hlavní výhody Excelu?
Jaká je základní struktura listu Excel?
Co je pracovní list a sešit?
Vyjmenujte hlavní prvky okna Excelu?
Co je buňka?
Struktura buňky?
Co je vzorec v Excelu?
Co znamená symbol $ v odkazech?
Jak Excel reaguje na chyby?
Co znamená funkce IF?
K čemu slouží diagramy?
Co je datová řada?
Jaké jsou hlavní typy grafů používaných v Excelu?
III. Technika měření a výpočetní vzorce. I. Účel práce: Seznámit se se smykovými a torzními deformacemi a metodami stanovení smykového modulu na základě torzní deformace.
III. Technika měření a výpočetní vzorce. I. Účel práce: experimentálně otestovat základní zákon dynamiky rotačního pohybu pomocí Oberbeckova kyvadla a určit moment setrvačnosti
III. Technika měření a výpočetní vzorce. Závaží jsou zavěšena na niti přehozené přes kladku a na každé ze závaží působí dvě síly: gravitace a napínací síla nitě.
III. Hlavní pravomoci a funkce řídících orgánů, divizí

Skládá se z. Je čas vyzkoušet program v akci, naučíte se pracovat v Excelu. Vytvoříme nový dokument, zadáme do něj data, provedeme jednoduché výpočty a sestavíme časový diagram. Po zvládnutí této lekce budete umět pracovat v Excelu a provádět jednoduché úkoly. Bude to první sebevědomý krok k profesionálnímu používání Excelu.

Počáteční údaje: měsíční objemy prodeje zboží v roce 2015, jednotkové náklady zboží, tržby za rok 2014 na měsíční bázi. Začněme výpočty.

Jak vytvořit dokument Excel

Pokud aplikace Excel ještě není spuštěna, klepněte na tlačítko Start - Všechny programy - Microsoft Office a ve složce, která se otevře – Excel. Program bude spuštěn. V okně, které se otevře, vyberte Prázdný sešit.

Vytvořte nový dokument aplikace Excel

Pokud je program již spuštěn, stisknutím kombinace kláves Ctrl+N vytvořte nový sešit.

Vyplnění excelového listu daty

V důsledku toho máte nový prázdný dokument s aktivním listem. "List 1". Vyplňte jej počátečními údaji. Začneme hlavičkou, tzn. Rozhodneme se o struktuře tabulky. Nechť jsou v prvním sloupci měsíce v roce.

Klikněte levým tlačítkem na buňku A1, aktivuje se pro vyplnění. Napište: "Měsíc"
Podobně do sloupce B1 napíšeme „Příjmy 2015“
C1 – „Prodej“
D1 – „Cena“
E1 – „Příjmy 2014“

Čepice je připravena.

Nyní napíšeme názvy měsíců do sloupce A. Umístěte kurzor do buňky A2, napište „leden“ a stiskněte Enter. Kurzor tabulky se posune o jeden řádek dolů. Pro vyplnění následujících měsíců použijeme funkci automatického vyplňování Excelu. Vraťte kurzor do buňky A2 a najeďte myší na čtvereček v pravém dolním rohu aktivní buňky. Současně se kurzor změní na černý kříž. Podržte levé tlačítko myši a přetáhněte kurzor dolů, jako byste natahovali nápis „Leden“ dolů. Excel automaticky zapíše nové měsíce do každého následujícího řádku.

Dokud nepustíte myš, program ukazuje nápovědu o tom, který měsíc bude v příslušné buňce. Podívejte, na obrázku jsem prodloužil seznam měsíců až do května. Pokud posunete kurzor o 11 řádků dolů, v popisku se zobrazí „prosinec“. Pusťte myš a „voilá“, jedním mávnutím ruky jsme si zapsali celých 12 měsíců!

Automatické vyplňování buněk v MS Excel

Nyní vyplníme množství a ceny. Umístěte kurzor do buňky C2, zapište množství a stiskněte Enter na klávesnici. Výběr se přesune do C3 a tam zapíšeme i množství. Plníme tedy všechny prodeje až do prosince (C13). Udělejme totéž s cenami ve sloupci D. V našem příkladu ceny obsahují kopejky. V Russified verzi Excelu jsou odděleny od celé části čárkou. V původní verzi - tečka. Vyplníme také výnos za rok 2014. Takto bude vypadat dokončená datová tabulka:

Tabulka je vyplněna počátečními údaji

Výpočty v Excelu

Důležité! Všechny vzorce v Excelu začínají znaménkem «=» . Pokud je první znak v buňce tento znak, program pochopí, že bude následovat vypočítaný vzorec.

Aktivujeme buňku B2 a spočítáme tržby v lednu 2015. Pojďme psát «=» a klikněte levým tlačítkem myši na buňku C2 a nastavte odkaz na lednové prodeje. Dále vložíme znaménko násobení «*» a klikněte na D2 – odkaz na cenu produktu. Stiskněte Enter a uvidíte, že výsledek výpočtů se zobrazí ve vybrané buňce a vzorec, který jsme zadali, se zobrazí v řádku vzorců pro tuto buňku. Excel extrahuje hodnotu z C2 a vynásobí ji hodnotou v D2. Pokud některý z nich změníte, vzorec se okamžitě přepočítá. Více informací o pravidlech výpočtu jsem napsal v článcích a. To vše je tam podrobněji popsáno.

Vzorec a výsledek výpočtu

Rozšiřme tento vzorec na všechny řádky tabulky, jako jsme to udělali s vyplňováním měsíců. Klikněte na čtvereček v pravém dolním rohu buňky se vzorcem. Výsledek – program vypočítal příjmy za všechny měsíce. Upozorňujeme, že při přetahování se adresy odkazů ve vzorci mění. Například v buňce B12 máme vzorec: =C12*D12, nikoli =C2*D2. Je to proto, že jsme použili .

Nyní spočítejme rozdíl mezi tržbami v roce 2014 a 2015. Nazvěme sloupec F – „Rozdíl“. Způsob zápisu vzorců se nemění, jen spočítáme =B2-E2 a vzorec rozšíříme na všechny měsíce. Vidíme například, že v lednu 2015 se naše tržby zvýšily o 7,5 jednotek. méně než v roce 2014.

Excelové grafy

Zbývá poslední úkol – sestavit časový diagram a vizuálně posoudit dynamiku prodeje (problematiku diagramů jsem podrobněji rozebral v ).

Pro sestavení grafu je potřeba vybrat rozsah (čtení) dat, na základě kterých bude křivka sestavena. Najeďte kurzorem myši na buňku A2. Podržte levé tlačítko a přetáhněte kurzor do buňky B13. Poté uvolněte tlačítko myši. Pokud jste vše udělali správně, rozsah A2:B13 bude zvýrazněn tmavou barvou.

Na základě vybraných dat vytvoříme graf. Chcete-li to provést, přejděte na pásu karet na kartu Vložit a tam - Grafy - Vložit graf - Graf se značkami. Na obrazovce se objeví graf změn příjmů podle měsíce roku 2015. Program použil první vybraný sloupec pro popisky podél vodorovné osy a druhý pro hodnoty podél svislé osy.

Výsledek výpočtu

Ukládání výpočtů

Chcete-li náš dokument uložit, stiskněte kombinaci kláves Ctrl+S, v okně, které se otevře, klikněte na možnost Recenze. Otevře se standardní okno pro ukládání, kde je třeba zadat umístění souboru a nastavit jeho název. Stisknutím tlačítka Uložit , potvrďte vytvoření nového souboru.

Vzorce v Excelu jsou jednou z nejdůležitějších výhod tohoto editoru. Díky nim se vaše schopnosti při práci s tabulkami několikanásobně zvyšují a jsou omezeny pouze vašimi dosavadními znalostmi. Můžeš dělat cokoliv. Excel vám přitom pomůže na každém kroku – speciální tipy jsou téměř v každém okně.

Chcete-li vytvořit jednoduchý vzorec, postupujte podle následujících pokynů:

Aktivujte libovolnou buňku. Klikněte na řádek pro zadání vzorce. Dejte rovnítko.

Zadejte libovolný výraz. Lze použít jako čísla

V tomto případě jsou postižené buňky vždy zvýrazněny. To se děje proto, abyste se svým výběrem neudělali chybu. Chybu je snazší vidět vizuálně než v textové podobě.

Z čeho se vzorec skládá?

Vezměme si jako příklad následující výraz.

Skládá se z:

symbol „=“ – začíná jím jakýkoli vzorec;
funkce "SUM";
argument funkce "A1:C1" (in v tomto případě toto je pole buněk "A1" až "C1");
operátor „+“ (dodatek);
odkazy na buňku "C1";
operátor „^“ (umocňování);
konstanta "2".

Pomocí operátorů

Operátory v editoru Excel označují, které operace je třeba provést se zadanými prvky vzorce. Výpočet probíhá vždy ve stejném pořadí:

závorky;
vystavovatelé;
násobení a dělení (v závislosti na posloupnosti);
sčítání a odčítání (také v závislosti na pořadí).

Aritmetický

Tyto zahrnují:

sčítání – „+“ (plus);

=2+2

negace nebo odčítání – „-“ (mínus);

=2-2 =-2

Pokud před číslo vložíte „mínus“, bude mít zápornou hodnotu, ale v absolutní hodnotě zůstane úplně stejné.

násobení - "*";

=2*2

dělení "/";

=2/2

procent "%";

=20%

umocnění – „^“.

=2^2

Porovnávací operátory

Tyto operátory se používají k porovnání hodnot. Operace vrátí hodnotu TRUE nebo FALSE. Tyto zahrnují:

znak „rovná se“ – „=“;

=C1=D1

znak „větší než“ – „>“;

=C1>D1

znak "menší než" - "<»;

=C1

znaménko „větší než nebo rovno“ – „>=“;

=C1>=D1

znak „menší nebo rovno“ – „<=»;

=C1<=D1

znak „nerovná se“ – „<>».

=C1<>D1

Operátor zřetězení textu

Pro tento účel se používá speciální znak „&“ (ampersand). Pomocí něj můžete spojovat různé fragmenty do jednoho celku - stejný princip jako u funkce „CONNECT“. Zde jsou nějaké příklady:

Pokud chcete sloučit text v buňkách, musíte použít následující kód.

=A1&A2&A3

Abyste mezi ně vložili jakýkoli symbol nebo písmeno, musíte použít následující konstrukci.

=A1&","&A2&","&A3

Sloučit můžete nejen buňky, ale i běžné symboly.

"Auto"&"mobil"

Jakýkoli jiný text než odkazy musí být citován. Jinak vzorec vygeneruje chybu.

Upozorňujeme, že použité uvozovky jsou přesně stejné jako na snímku obrazovky.

K definování odkazů lze použít následující operátory:

pro vytvoření jednoduchého odkazu na požadovaný rozsah buněk stačí označit první a poslední buňku této oblasti a mezi nimi symbol „:“;
pro spojení odkazů se používá znak „;“;
pokud je nutné určit buňky, které jsou v průsečíku několika rozsahů, pak se mezi odkazy umístí „mezera“. V tomto případě se zobrazí hodnota buňky „C7“.

Protože pouze to spadá pod definici „průniku množin“. Toto je název přidělený tomuto operátoru (mezera).

Pomocí odkazů

Při práci v editoru Excel můžete používat různé typy odkazů. Většina začínajících uživatelů však ví, jak používat pouze ty nejjednodušší z nich. Naučíme vás správně zadávat odkazy všech formátů.

Jednoduché odkazy A1

Tento typ se zpravidla používá nejčastěji, protože je mnohem pohodlnější skládat než ostatní.

sloupce – od A do XFD (ne více než 16384);
linky – od 1 do 1048576.

Zde jsou nějaké příklady:

buňka na průsečíku řádku 5 a sloupce B je „B5“;
rozsah buněk ve sloupci B od řádku 5 do řádku 25 je „B5:B25“;
rozsah buněk v řádku 5 počínaje sloupcem B až F je „B5:F5“;
všechny buňky v řádku 10 jsou „10:10“;
všechny buňky v řádcích 10 až 15 jsou „10:15“;
všechny buňky ve sloupci B jsou „B:B“;
všechny buňky ve sloupcích B až K jsou „B:K“;
Rozsah buněk B2 až F5 je „B2-F5“.

Někdy vzorce využívají informace z jiných listů. Funguje následovně.

=SUM(List2!A5:C5)

Druhý list obsahuje následující informace.

Pokud je v názvu listu mezera, musí být ve vzorci uvedena v jednoduchých uvozovkách (apostrofech).

=SUM("List číslo 2"!A5:C5)

Absolutní a relativní vazby

Excel editor pracuje se třemi typy odkazů:

absolutní;
relativní;
smíšený.

Pojďme se na ně podívat blíže.

Všechny výše uvedené příklady odkazují na relativní adresy buněk. Tento typ je nejoblíbenější. Hlavní praktickou výhodou je, že editor během migrace změní reference na jinou hodnotu. V souladu s tím, kam přesně jste tento vzorec zkopírovali. Pro výpočet bude vzat v úvahu počet buněk mezi starou a novou pozicí.

Představte si, že potřebujete roztáhnout tento vzorec přes celý sloupec nebo řádek. Písmena a čísla v adresách buněk nebudete ručně měnit. Funguje následovně.

Zadáme vzorec pro výpočet součtu prvního sloupce.

=SUM(B4:B9)

Stiskněte klávesové zkratky Ctrl + C. Chcete-li přenést vzorec do sousední buňky, musíte tam jít a stisknout Ctrl + V.

Pokud je tabulka velmi velká, je lepší kliknout na pravý dolní roh a bez uvolnění prstu přetáhnout ukazatel na konec. Pokud je dat málo, pak je kopírování pomocí horkých kláves mnohem rychlejší.

Nyní se podívejte na nové vzorce. Index sloupce se automaticky změnil.

Pokud chcete, aby při přenosu vzorců zůstaly zachovány všechny vazby (tedy aby se automaticky neměnily), musíte použít absolutní adresy. Jsou označeny jako "$B$2".

=SUM($B$4:$B$9)

V důsledku toho vidíme, že nedošlo k žádným změnám. Všechny sloupce zobrazují stejné číslo.

Tento typ adresy se používá, když je potřeba opravit pouze sloupec nebo řádek a ne všechny najednou. Lze použít následující konstrukce:

$D1, $F5, $G3 – pro upevnění sloupků;
D$1, F$5, G$3 – pro upevnění řad.

S takovými vzorci pracujte pouze v případě potřeby. Pokud například potřebujete pracovat s jedním konstantním řádkem dat, ale měnit pouze sloupce. A co je nejdůležitější, pokud se chystáte vypočítat výsledek v různých buňkách, které nejsou umístěny na stejné lince.

Faktem je, že když vzorec zkopírujete na jiný řádek, čísla v odkazech se automaticky změní na počet buněk z původní hodnoty. Pokud použijete smíšené adresy, vše zůstane na svém místě. To se provádí následovně.

Použijme následující výraz jako příklad.

= B$4

Přesuňme tento vzorec do jiné buňky. Nejlépe ne na dalším nebo jiném řádku. Nyní vidíte, že nový výraz obsahuje stejný řádek (4), ale jiné písmeno, protože to bylo jediné, které bylo relativní.

3D odkazy

Pojem „trojrozměrný“ zahrnuje ty adresy, ve kterých je uveden rozsah listů. Příklad vzorce vypadá takto.

=SUM(List1:List4!A5)

V tomto případě bude výsledek odpovídat součtu všech buněk „A5“ na všech listech, počínaje 1 až 4. Při sestavování takových výrazů musíte dodržet následující podmínky:

takové odkazy nelze použít v polích;
je zakázáno používat trojrozměrné výrazy tam, kde dochází k průniku buněk (například operátor „mezera“);
Při vytváření vzorců s 3D adresami můžete použít následující funkce: AVERAGE, STDEV, STDEV.V, AVERAGE, STDEV, STDEV.Y, SUM, COUNTA, COUNT, MIN, MAX, MINA, MAX, VARVE, PRODUCT, VARIANCE, VAR. a DISPA.

Pokud tato pravidla porušíte, uvidíte nějakou chybu.

Odkazy ve formátu R1C1

Tento typ odkazu se liší od „A1“ tím, že číslo není přiřazeno pouze řádkům, ale také sloupcům. Vývojáři se rozhodli nahradit běžné zobrazení touto možností pro pohodlí v makrech, ale lze je použít kdekoli. Zde je několik příkladů takových adres:

R10C10 – absolutní odkaz na buňku, která se nachází na desátém řádku desátého sloupce;
R – absolutní vazba na aktuální (ve kterém je uveden vzorec) vazba;
R[-2] – relativní vazba k přímce, která se nachází o dvě pozice nad touto;
R[-3]C je relativní odkaz na buňku, která je umístěna o tři pozice výše v aktuálním sloupci (kam jste se rozhodli zapsat vzorec);
RC je relativní odkaz na buňku, která se nachází pět buněk vpravo a pět řádků pod aktuální buňkou.

Použití jmen

Excel umožňuje vytvářet vlastní jedinečné názvy pro pojmenování rozsahů buněk, jednotlivých buněk, tabulek (regulárních a kontingenčních), konstant a výrazů. Zároveň pro editora není žádný rozdíl při práci se vzorci - rozumí všemu.

Můžete použít názvy pro násobení, dělení, sčítání, odčítání, výpočet úroků, koeficienty, odchylka, zaokrouhlování, DPH, hypotéka, úvěr, odhad, časové rozvrhy, různé formuláře, slevy, platy, délka služby, výplata anuity, práce s VPR vzorce , „VSD“, „MEDZIPRODUKT. VÝSLEDKY“ a tak dále. To znamená, že si můžete dělat, co chcete.

Existuje pouze jedna hlavní podmínka - tento název musíte definovat předem. Jinak se o tom Excel nic nedozví. To se provádí následovně.

Vyberte sloupec.
Vyvolejte kontextovou nabídku.
Vyberte "Přiřadit jméno".

Zadejte požadovaný název pro tento objekt. V tomto případě musíte dodržovat následující pravidla.

Pro uložení klikněte na tlačítko „OK“.

Stejným způsobem můžete přiřadit název buňce, textu nebo číslu.

Informace v tabulce můžete použít jak pomocí názvů, tak pomocí běžných odkazů. Takto vypadá standardní verze.

A pokud místo adresy „D4:D9“ zkusíte vložit naše jméno, uvidíte nápovědu. Stačí napsat pár znaků a uvidíte, co sedne (z databáze jmen) nejvíce.

V našem případě je vše jednoduché - „sloupec_3“. Představte si, že takových jmen budete mít velké množství. Nebudete si moci zapamatovat vše zpaměti.

Používání funkcí

Existuje několik způsobů, jak vložit funkci do Excelu:

ručně;
pomocí panelu nástrojů;
pomocí okna Vložit funkci.

Pojďme se na jednotlivé metody podívat blíže.

V tomto případě je vše jednoduché - pomocí rukou, vlastních znalostí a dovedností zadáváte vzorce do speciálního řádku nebo přímo do buňky.

Pokud v této oblasti nemáte pracovní zkušenosti, je lepší použít zpočátku jednodušší metody.

V tomto případě je nutné:

Přejděte na kartu "Vzorce".
Klikněte na libovolnou knihovnu.
Vyberte požadovanou funkci.

Ihned poté se objeví okno Argumenty a funkce s již vybranou funkcí. Jediné, co musíte udělat, je zadat argumenty a uložit vzorec pomocí tlačítka „OK“.

Průvodce nahrazením

Můžete jej aplikovat následovně:

Aktivujte libovolnou buňku.
Klikněte na ikonu „Fx“ nebo použijte klávesovou zkratku SHIFT + F3.

Ihned poté se otevře okno „Vložit funkci“.
Zde uvidíte velký seznam různých funkcí seřazených podle kategorií. Kromě toho můžete použít vyhledávání, pokud nemůžete najít položku, kterou potřebujete.

Jediné, co musíte udělat, je zadat nějaké slovo, které může popsat, co chcete dělat, a editor se pokusí zobrazit všechny vhodné možnosti.

Vyberte funkci z poskytnutého seznamu.
Chcete-li pokračovat, musíte kliknout na tlačítko „OK“.

Poté budete požádáni, abyste specifikovali "Argumenty a funkce". Můžete to udělat ručně nebo jednoduše vybrat požadovaný rozsah buněk.
Chcete-li použít všechna nastavení, musíte kliknout na tlačítko „OK“.

V důsledku toho uvidíme číslo 6, i když to již bylo jasné, protože předběžný výsledek je zobrazen v okně „Argumenty a funkce“. Data se okamžitě přepočítají, když se změní některý z argumentů.

Použití vnořených funkcí

Jako příklad použijeme vzorce s logickými podmínkami. K tomu budeme muset přidat nějaký druh tabulky.

Poté postupujte podle následujících pokynů:

Klikněte na první buňku. Vyvolejte okno „Vložit funkci“. Vyberte funkci "Pokud". Pro vložení klikněte na „OK“.

Pak budete muset vytvořit nějaký logický výraz. Musí být zapsán do prvního pole. Můžete například přidat hodnoty tří buněk do jednoho řádku a zkontrolovat, zda je součet větší než 10. Pokud je „pravda“, uveďte text „Větší než 10“. Pro falešný výsledek – „méně než 10“. Poté klikněte na „OK“ pro návrat do pracovního prostoru.

Ve výsledku vidíme následující - editor ukázal, že součet buněk ve třetím řádku je menší než 10. A to je správně. To znamená, že náš kód funguje.

=IF(SUM(B3:D3)>10,"Více než 10","Méně než 10")

Nyní musíte nakonfigurovat následující buňky. V tomto případě náš vzorec jednoduše sahá dále. Chcete-li to provést, musíte nejprve najet kurzorem na pravý dolní roh buňky. Po změně kurzoru musíte kliknout levým tlačítkem a zkopírovat jej úplně dolů.

V důsledku toho editor přepočítá náš výraz pro každý řádek.

Jak můžete vidět, kopírování bylo docela úspěšné, protože jsme použili relativní odkazy, o kterých jsme hovořili dříve. Pokud potřebujete přiřadit adresy k argumentům funkce, použijte absolutní hodnoty.

Můžete to udělat několika způsoby: pomocí řádku vzorců nebo speciálního průvodce. V prvním případě je vše jednoduché - klikněte do speciálního pole a ručně zadejte potřebné změny. Ale psát tam není úplně pohodlné.

Jediné, co můžete udělat, je zvětšit vstupní pole. Chcete-li to provést, stačí kliknout na uvedenou ikonu nebo stisknout kombinaci kláves Ctrl + Shift + U.

Stojí za zmínku, že toto je jediný způsob, pokud ve vzorci nepoužíváte funkce.

Používáte-li funkce, je vše mnohem jednodušší. Chcete-li upravit, musíte postupovat podle následujících pokynů:

Aktivujte buňku se vzorcem. Klikněte na ikonu "Fx".

Poté se objeví okno, ve kterém můžete velmi pohodlným způsobem změnit argumenty funkce, které potřebujete. Navíc zde můžete přesně zjistit, jaký bude výsledek přepočtu nového výrazu.

Pro uložení provedených změn použijte tlačítko „OK“.

Chcete-li odstranit výraz, proveďte následující:

Klikněte na libovolnou buňku.

Klikněte na tlačítko Smazat nebo Backspace. V důsledku toho bude buňka prázdná.

Přesně stejného výsledku dosáhnete pomocí nástroje „Vymazat vše“.

Možné chyby při vytváření vzorců v editoru Excel

Níže jsou uvedeny nejčastější chyby uživatelů:

Výraz používá obrovské množství hnízdění. Nemělo by jich být více než 64;
vzorce označují cesty k externím knihám bez úplné cesty;
Otevírací a zavírací konzoly jsou umístěny nesprávně. To je důvod, proč jsou v editoru na řádku vzorců všechny závorky zvýrazněny jinou barvou;

názvy knih a listů se neuvádějí v uvozovkách;
čísla jsou použita ve špatném formátu. Pokud například potřebujete zadat 2 000 $, musíte jednoduše zadat 2 000 a vybrat příslušný formát buňky, protože symbol $ program používá pro absolutní odkazy;

Požadované argumenty funkce nejsou zadány. Všimněte si, že volitelné argumenty jsou uzavřeny v hranatých závorkách. Vše bez nich je nezbytné, aby vzorec správně fungoval;

Rozsahy buněk jsou zadány nesprávně. K tomu musíte použít operátor „:“ (dvojtečka).

Chybové kódy při práci se vzorci

Při práci se vzorcem se mohou zobrazit následující možnosti chyb:

#HODNOTA! – tato chyba označuje, že používáte nesprávný datový typ. Pokoušíte se například použít text místo číselné hodnoty. Excel samozřejmě nebude schopen vypočítat součet mezi dvěma frázemi;
#NÁZEV? – taková chyba znamená, že jste udělali překlep v názvu funkce. Nebo se pokoušíte zadat něco, co neexistuje. To nemůžeš. Kromě toho může být problém i v něčem jiném. Pokud jste si jisti názvem funkce, zkuste se na vzorec podívat blíže. Možná jste zapomněli závorku. Kromě toho musíte vzít v úvahu, že fragmenty textu jsou uvedeny v uvozovkách. Pokud vše ostatní selže, zkuste sestavit výraz znovu;
#ČÍSLO! – zobrazení takové zprávy znamená, že máte problém s argumenty nebo výsledkem vzorce. Číslo se například ukázalo jako příliš obrovské nebo naopak malé;
#DIV/0! – tato chyba znamená, že se pokoušíte napsat výraz, ve kterém dojde k dělení nulou. Excel nemůže přepsat pravidla matematiky. Proto je zde i takové jednání zakázáno;
#N/A! – editor může zobrazit tuto zprávu, pokud některá hodnota není k dispozici. Pokud například použijete funkce SEARCH, SEARCH, MATCH a Excel nenajde hledaný fragment. Nebo neexistují vůbec žádná data a vzorec nemá s čím pracovat;
Pokud se pokoušíte něco vypočítat a Excel zapíše slovo #REF!, pak argument funkce používá nesprávný rozsah buněk;
#PRÁZDNÝ! – tato chyba se objeví, pokud máte nekonzistentní vzorec s překrývajícími se rozsahy. Přesněji, pokud ve skutečnosti žádné takové buňky (které jsou náhodou na průsečíku dvou rozsahů) neexistují. Tato chyba se často vyskytuje náhodou. V argumentu stačí nechat jednu mezeru a editor to bude vnímat jako speciální operátor (o tom jsme mluvili dříve).

Když upravíte vzorec (buňky jsou zvýrazněné), uvidíte, že se ve skutečnosti neprotínají.

Někdy můžete vidět spoustu # znaků, které zcela vyplňují šířku buňky. Ve skutečnosti zde není žádná chyba. To znamená, že pracujete s čísly, která se do dané buňky nevejdou.

Chcete-li zobrazit hodnotu tam obsaženou, stačí změnit velikost sloupce.

Kromě toho můžete použít formátování buněk. Chcete-li to provést, musíte provést několik jednoduchých kroků:

Vyvolejte kontextovou nabídku. Vyberte Formát buněk.

Zadejte typ jako "Obecné". Chcete-li pokračovat, použijte tlačítko „OK“.

Díky tomu bude Excel editor schopen toto číslo převést do jiného formátu, který se do tohoto sloupce vejde.

Příklady použití vzorců

Editor Microsoft Excel vám umožňuje zpracovávat informace jakýmkoli způsobem, který vám vyhovuje. Jsou k tomu všechny potřebné podmínky a příležitosti. Podívejme se na několik příkladů vzorců podle kategorií. To vám usnadní pochopení.

Chcete-li vyhodnotit matematické schopnosti aplikace Excel, musíte provést následující kroky.

Vytvořte tabulku s některými podmíněnými daty.

Pro výpočet částky zadejte následující vzorec. Pokud chcete přidat pouze jednu hodnotu, můžete použít operátor sčítání („+“).

=SUM(B3:C3)

Kupodivu v editoru Excelu nemůžete používat funkce. Pro odečítání se používá obvyklý operátor „-“. V tomto případě bude kód následující.

=B3-C3

Abyste mohli určit, kolik procent je první číslo od druhého, musíte použít tuto jednoduchou konstrukci. Pokud chcete odečíst několik hodnot, budete muset pro každou buňku zadat „mínus“.

=B3/C3%

Všimněte si, že symbol procent je umístěn na konci, nikoli na začátku. Navíc při práci s procenty není potřeba dodatečně násobit 100. To se děje automaticky.

Excel může přidávat na základě několika podmínek najednou. Můžete vypočítat součet buněk v prvním sloupci, jejichž hodnota je větší než 2 a menší než 6. A stejný vzorec lze nastavit i pro druhý sloupec.

=SUMIFS(B3:B9,B3:B9,">2"B3:B9,"<6") =SUMIFS(C3:C9,C3:C9,">2"C3:C9,"<6")
Můžete také spočítat počet prvků, které splňují nějakou podmínku. Necháme například Excel spočítat, kolik máme čísel větších než 3.

=COUNTIF(B3:B9;">3") =COUNTIF(C3:C9,">3")
Výsledek všech vzorců bude následující.

Matematické funkce a grafy

Pomocí Excelu můžete vypočítat různé funkce a na jejich základě vytvářet grafy a poté provádět grafickou analýzu. Takové techniky se zpravidla používají v prezentacích.

Jako příklad si zkusme sestavit grafy pro exponent a nějakou rovnici. Pokyny budou následující:

Vytvoříme tabulku. V prvním sloupci budeme mít počáteční číslo "X", ve druhém - funkci "EXP", ve třetím - zadaný poměr. Bylo by možné udělat kvadratický výraz, ale pak by výsledná hodnota prakticky zmizela na pozadí exponenciály na grafu.

Jak jsme řekli dříve, růst exponentu nastává mnohem rychleji než u obyčejné kubické rovnice.

Tímto způsobem lze graficky znázornit jakoukoli funkci nebo matematický výraz.

Vše popsané výše je vhodné pro moderní programy z let 2007, 2010, 2013 a 2016. Starý editor Excel je výrazně horší, pokud jde o možnosti, množství funkcí a nástrojů. Pokud otevřete oficiální nápovědu od společnosti Microsoft, uvidíte, že navíc označují, ve které verzi programu se tato funkce objevila.

Ve všech ostatních ohledech vše vypadá téměř úplně stejně. Jako příklad si spočítejme součet několika buněk. K tomu potřebujete:

Uveďte některá data pro výpočet. Klikněte na libovolnou buňku. Klikněte na ikonu "Fx".

Vyberte kategorii „Matematická“. Najděte funkci „SUM“ a klikněte na „OK“.

Můžete zkusit přepočítat v jakémkoli jiném editoru. Proces bude probíhat úplně stejně.

Závěr

V tomto tutoriálu jsme mluvili o všem, co souvisí se vzorci v editoru Excel, od těch nejjednodušších až po velmi složité. Každá část byla doplněna podrobnými příklady a vysvětleními. To se provádí s cílem zajistit, aby informace byly přístupné i úplným figurínám.

Pokud se vám něco nedaří, znamená to, že někde děláte chybu. Možná máte chybně napsané výrazy nebo nesprávné odkazy na buňky. Hlavní věc je pochopit, že vše je třeba zavést velmi opatrně a opatrně. Navíc všechny funkce nejsou v angličtině, ale v ruštině.

Kromě toho je důležité si uvědomit, že vzorce musí začínat symbolem „=“ (rovná se). Mnoho začínajících uživatelů na to zapomíná.

Soubor příkladů

Abychom vám usnadnili pochopení dříve popsaných vzorců, připravili jsme speciální demo soubor, ve kterém byly zkompilovány všechny výše uvedené příklady. Můžete to udělat z našich webových stránek zcela zdarma. Pokud při tréninku použijete hotovou tabulku se vzorci na základě vyplněných dat, dosáhnete výsledků mnohem rychleji.

Video návod

Pokud vám náš popis nepomohl, zkuste se podívat na níže přiložené video, které podrobněji vysvětluje hlavní body. Možná děláte všechno správně, ale něco vám chybí. S pomocí tohoto videa byste měli porozumět všem problémům. Doufáme, že vám podobné lekce pomohly. Sledujte nás častěji.

Někdy, abychom našli chybu ve vzorci nebo porozuměli principu fungování, je nutné provést jeho výpočet krok za krokem. V závislosti na potřebách a složitosti výpočtu můžete použít různé metody a vzorec můžete také rychle převést na výsledek, který vytvoří. Podívejme se na několik příkladů krok za krokem výpočtu vzorce v Excelu.

Převod vzorce na výsledek výpočtu

Pokud nepotřebujete vzorec v buňce, ale výsledek jeho výpočtu, můžete samozřejmě výsledek zkopírovat a vložit do stejné buňky v režimu uložení pouze textu, ale existuje rychlejší způsob. Jednoduše vyberte vzorec a stiskněte klávesu F9. Kdy by se takový výpočet mohl hodit? Existuje několik možností, nápadným příkladem by byla práce s funkcemi aplikace Excel, které vracejí náhodné hodnoty, nebo protože ve výchozím nastavení je aplikace Excel nakonfigurována pro automatický výpočet, hodnoty funkcí se neustále aktualizují a pomocí této jednoduché metody můžete rychle zaznamenávat výsledek výpočtu.

Převod části vzorce na výsledek výpočtu

Pokud vyberete pouze část vzorce a stisknete klávesu F9, pak se tato část převede na výsledek a vypočítá se vše, co ve vzorci zůstane. Stejně jako v předchozím příkladu můžete opravit výpočet jedné z funkcí Excelu, které tímto způsobem generují náhodné hodnoty, ale tato transformace je vhodná i pro vzorce, které nemění své hodnoty při každém zobrazení listu Excel. je přepočítán.

Výpočet složitého vzorce krok za krokem

Nejzajímavější funkcí je bezesporu postupný výpočet složitého vzorce v Excelu, když potřebujete najít chybu ve výpočtech.

Co je složitý vzorec v Excelu?

Komplexní vzorce jsou vzorce, které zpravidla obsahují několik funkcí, které zase nemusí být nejjednodušší na pochopení, ale jsou velmi užitečné. Ukázkovým příkladem je velmi oblíbená funkce

Excel se používá nejen k vytváření tabulek. Pomocí tohoto programu můžete provádět různé výpočty. Tento nástroj je velmi praktický při vytváření libovolné databáze v Excelu. Vestavěná kalkulačka zkrátí čas a usnadní práci. K provádění výpočtů potřebujete znát základní pojmy Excelu.

Vzorce a adresy buněk

Vzorec je rovnost, kterou lze použít k sčítání, odečítání, násobení a dělení hodnot přímo v tabulce. Aby byly výpočty pohodlnější, měli byste si být dobře vědomi pojmu „adresa buňky“. Adresa je označení jednotlivé buňky tabulky. Obrázek ukazuje malou tabulku sestávající ze šesti řádků a čtyř sloupců. Každá buňka má svou vlastní adresu. Označuje se latinským písmenem a číslem. Například buňka „Mléko“ má adresu „A4“. To znamená, že „A“ je číslo sloupce a „4“ je číslo řádku.

Aplikace vzorců ve výpočtech

Nyní můžete začít studovat vzorce. Vzorec obsahuje rovnítko, „+“, „-“, „/“ a „*“. Chcete-li vytvořit jakoukoli rovnici, musíte vybrat buňku a napsat do ní funkci. Pokud například potřebujete vypočítat celkové náklady na čtyři bochníky chleba, umístěte kurzor na buňku D3 a napište znaménko „rovná se“. Dále byste měli napsat vzorec z adres buněk nebo jednoduše čísel se znaménkem násobení. Zvažme nejjednodušší metodu - zjištění celkových nákladů pomocí známých čísel. Chcete-li to provést, zadejte do buňky „=4*15“ a stiskněte „Enter“.

Nyní se podíváme na univerzální metodu pro hledání různých hodnot. Když při psaní vzorce používáte adresy buněk, můžete změnit hodnoty v jiných buňkách. V tomto případě Excel automaticky přepočítá hodnotu ve sloupci, kde je vzorec zapsán. Chcete-li to provést, místo číselných hodnot zadejte do buňky D3 údaje adresy. Množství chleba má adresu „B3“ a cena jednoho bochníku je „C3“. Nyní tyto adresy zadáme do sloupce, kde bude proveden výpočet.

Při psaní vzorce jsou buňky zvýrazněny. To vám umožní správně zadat adresy hodnot. Další důležitou výhodou vytvoření vzorce pomocí názvů řádků a sloupců je to, že vypočítá všechny hodnoty najednou. To znamená, že pokud zadáte vzorec do buňky „D3“ a stisknete „Enter“, Excel automaticky vypočítá celkové náklady na všechny ostatní produkty.