Komplexní funkce v Excelu. Návod k použití vzorců Excelu s příklady

finanční funkce provádět 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. Můžete například použít ve vzorci dnešní datum, pomocí funkce DNES.

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 umožňují provádět statistickou analýzu dat. Můžete například určit průměr a rozptyl vzorku a mnoho dalšího.

Funkce databáze lze použít k provádění výpočtů a výběru záznamů podle 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 a jsou navrženy pro testování jedné nebo více podmínek. Například funkce LI umožňuje určit, zda je zadaná podmínka pravdivá, a vrací jednu hodnotu, pokud je podmínka pravdivá, a jinou hodnotu, pokud je nepravdivá.

Funkce Kontrola vlastností a hodnot jsou určeny k definování dat uložených v buňce. Tyto funkce kontrolují hodnoty v buňce podle podmínky a vracejí hodnoty v závislosti na výsledku. SKUTEČNÝ nebo LEŽÍCÍ.

Pro 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 je nutné nastavit argumenty vybrané funkce, k tomu je nutné vyplnit pole v dialogovém okně odpovídajícími hodnotami nebo adresami buněk tabulky.

Klasifikace funkcí v Excelu:

1. hlavolam (-li pokud chyba, a, nebo, pravda, nepravda, ne)

2. matematický(abc, acos, acosh, asin, asinh, atan, atan2, atanh, cos, cosh, exp, ln, log, log10, sin, sinh, tan, tanh, stupně, dwfaktor, znamení, kořen, rootpi, mbr, mopred , multinom, násobení, lichý, kývnutí, nok, okrup, okrdown, zaokrouhlený, zaokrouhlený nahoru, zaokrouhlený dolů, zaokrouhlený, zbytek, otr, pí, prod, mezisoučty, radiány, římská, řada.součty, náhodný mezi, náhodný, stupeň, součty , sumif , sumif, sumq, sumdif, sumprod, sumdif, sumsummq, faktor, celé číslo, kvocient, sudý, numbercomb)

3. statistický(fdisp, fdisp, pearson, ztest, betaobr, betarasp, binomrasp, weibull, pravděpodobnost, gammalog, gammaarray, gammarasp, hypergeomet, disp, dispa, disp, disp, trust, quadrotkl, kvartil, qpearson, covar, correl, critbinom , lineární, lognorm, lognorm, lognormdisp, max, max, medián, min, min, režim, max, min, sklon, normalizovat, normmove, normdisp, normsmove, normstrasp, subbinomdisp, intercept, perest, percentil, predikce, procentrang, poisson , hodnost, výška, šikmost, šmrnc, srgeom, avg, avg, avgif, avgif, srotkl, stdv, stdv, stdvp, stdvp, stonuh, studsp, studvp, skóre, scoreif, scoreif, skóre, countblanks, trend, ttest, cutaverage , fisher, fischerobr, ftest, chi2obr, chi2disp, chi2test, frekvence, exprasp, kurtosis)

4. finanční(amoruv, amorum, apl, acc, bez potvrzení, bs, vsd, ddob, trvání, kupón dní, kupón dní, kupón dní po, příjem, příjem, splácení kumulativního příjmu, nominální hodnota, celkový příjem, všeobecná platba, osplt, plt , přijaté, platba úroku, prplt, ps, puo, rovný šek, ruble.des, rubl.fraction, sleva, sazba, fuo, cena, pricekchek, pricepervnereg, pricerepayment, priceafternereg, pricediscount , číslo kuponu, netindoh, netnz, chps, účinek)

5. text(buttext, dlstr, nahradit, hodnota, kódový znak, levý znak, najít, vytisknout znak, opakovat, nahradit, hledat, pravý znak, velké písmeno, propnach, pstr, rubl, mezera, symbol, shoda, malá písmena, zřetězit, t, text, pevné)

6. inženýrství(bessel.i, bessel.j, bessel.k, bessel.y, okt.in.dv, okt.in.des, okt.hex, dv.8, dv.des, dv.in .hex, delta, desítkové .oct, decimal.in, decimal.hex, dfosh, complex, imaginární abs, imaginární cos, imaginární exp, imaginární ln, imaginární log10, imaginární log2 , imaginární hřích, imaginární argument, imaginární věc, imaginární dělení, imaginární kořen, imaginární rozdíl , imaginární rozdíl, imaginární konjugát, imaginární stupeň, imaginární součet, imaginární část, práh, transformace, foch , hex.8, hex.motor, hex.dec)

7. analytická(cubevalue, cubemultiplier, cubeitem, cubeitemproperty, cubenumbermultiplier, cubeelement, cubeelementkeep)

8. datum a čas(timevalue, time, year, date, datevalue, datemonth, day, dayweek, days360, zlomokrok, endmonth, month, minute, weeknom, weekday, today, thdate, hour, networkdays)

10. kontrola vlastností a hodnot(elogic, end, enetext, none, eosh, eerror, eempty, eslink, etext, even, number, inform, nd, type, error type, h, cell)

11. práci s databází(bddisp, bddisp, bdprod, bdsum, b-extract, bcount, bdcount, dmax, dmin, dsrvalue, dstandotl, dstandotclp)

Klasifikace funkcí umožňuje jejich kategorizaci. Nenechte se zastrašit tak velkým množstvím funkcí – ostatně to opět potvrzuje vynikat velmi funkční program. Každé z těchto funkčních slov je vyhrazeným slovem pro program, tzn. po napsání kteréhokoli z nich program již ví, co má dělat.

Každý vzorec, který napíšete, může přirozeně kombinovat několik funkcí jedné nebo dokonce několika kategorií najednou.

Microsoft Excel, software, tabulky, obsahuje bohatou sadu vzorců a funkcí pro výpočty a další pracovní listy. Funkce a vzorce jsou dva důležité pojmy v Excelu:

  • Funkce Excel je vestavěný program, který provádí konkrétní operaci na množině daných hodnot. Příklady funkcí Excelu jsou SUM, SUMPRODUCT, VLOOKUP, AVERAGE atd.
  • Excel vzorce jsou výrazy, které používají funkce Excelu k provádění výpočtů na základě daných parametrů.

Pro nové studenty Excelu je důležité těmto pojmům porozumět. Pokud chcete ovládat Excel, budete si muset pořídit dobrý tým funkcí a Excel vzorce. V tomto článku se dozvíme více o tom, jak se funkce a vzorce používají v aplikaci Microsoft Excel.

Pojem vzorce a funkce

Pro pochopení těchto pojmů si uveďme velmi jednoduchý příklad. Řekněme, že máte dvě buňky, které obsahují číselné hodnoty. A chcete zobrazit součet těchto hodnot ve třetí buňce. Jak víte, že tyto hodnoty nemusíte přidávat ručně, vše, co potřebujete, je vzorec Excel.

  • Buňka A1 = 23
  • Buňka A2 = 57
  • Chceme součet těchto dvou v buňce B4

K provedení tohoto úkolu můžeme snadno použít funkci SUM v následujícím vzorci v buňce B4:

=SUM(A1;A2)

Když napíšete tento vzorec do buňky B4 a stisknete ENTER, v B4 se objeví součet (23 + 57 = 80):

Co se tedy dozvíme například z tohoto jednoduchého vzorce?

  • Vzorec aplikace Excel můžete napsat do libovolné buňky nebo řádku vzorců
  • Vzorec Excel začíná na rovnat se znamení (=)
  • Jakmile přijde rovnítko Funkce Excel(např. SUM)
  • Za názvem funkce je potřeba napsat parametry do závorek
  • Když stisknete klávesu Enter, vzorec se spustí a výsledek se zobrazí v buňce

Většina vzorců aplikace Excel může jako parametry přijímat adresu buňky. Pokud jako parametr zadáte odkaz na buňku, funkce automaticky vybere hodnotu zadanou v této buňce.

Typy funkcí Excelu

Microsoft Excel přichází se stovkami funkcí. Tyto funkce lze rozdělit do několika kategorií podle úkolů, které plní. Příklady takových kategorií jsou:

  1. Matematika a trigonometrie: Tyto funkce provádějí matematické a trigonometrické výpočty. Například: SUM, FLOOR, LOG, ROUND, SQRT, ASIN, ATAN atd.
  2. Statistické funkce: provádět úkoly související se statistickými výpočty: Například: WORLD, COUNT, TRED, VAR atd.
  3. Logické funkce: dávat výsledky založené na logických konstrukcích jako IF, AND, OR, NOT atd.
  4. Textové funkce: provádět úkoly související s manipulací s řetězci. Například CONCAT, CHAR, LOWER, UPPER, TRIM atd.
  5. Funkce data a času: provádět výpočty data a času. Například DATEDIFF, nyní, dnes, rok atd.
  6. Funkce databáze: poskytovat výsledky, přístup k databázi

Pro získání kompletní seznam Vzorce kategorií můžete vidět na webu Microsoft Office.

vnořené funkce

Excel umožňuje používat funkce vnořeným způsobem. Zhuštěné funkce Excelu umožňují předat výsledek jedné funkce jako parametr jiné funkci. Proto Excel usnadňuje použití vzorce. Zpočátku můžete mít pocit, že vnořování funkcí dělá vzorec složitější - Ale brzy si uvědomíte, že vnořování vám ve skutečnosti usnadňuje život!

Níže je uveden příklad vnořených funkcí:

=KDYŽ(PRŮMĚR(F2:F5) > 50, SOUČET(G2:G5), 0)

Výše uvedený vzorec vypadá složitě, ale není. Tento vzorec lze snadno číst zleva doprava. Říká, že pokud se ukáže, že průměrná hodnota rozsahu buněk (F2:F5) je větší než 50, pak vypočítejte součet rozsahu buněk (G2:G5), jinak vrátí nulu.

Zde se výstup funkce AVERAGE stane vstupem pro funkci IF. Říkáme tedy, že funkce AVERAGE je vnořena do funkce IF.

Funkční parametry

Funkce Excelu fungují na vstupu, který zadáte jako parametry. Za názvem funkce a v závorkách je uveden seznam parametrů oddělených čárkami. Existují dva typy parametrů funkce:

  • Doslovné parametry: Když zadáte čísla nebo textový řetězec jako parametr. Například vzorec =SUM(10;20) ve výsledku vám dá 30.
  • Odkaz na možnosti buňky: místo doslovného čísla/textu poskytujeme adresu buňky, která obsahuje hodnotu, se kterou má funkce pracovat. Například, =SUM(A1;A2)

Toto byl základní návod o vzorcích a funkcích Excelu. Doufáme, že studenti snadno porozumí. Pokud k tomu máte nějaké dotazy, neváhejte nás kontaktovat v sekci komentářů. Děkujeme, že používáte TechWelkin!

V editoru Funkce Excelu jsou klíčovou součástí celého programu. Drtivá většina uživatelů o nich přitom neví prakticky nic. Na začátku, kdy se teprve učíte, se doporučuje použít speciálního průvodce, se kterým se práce s funkcemi několikrát usnadní. V tomto článku se blíže podíváme na proces tvorby vzorců a nejzákladnějších funkcí, které se používají v mnoha profesích.

Chcete-li to provést, musíte provést následující kroky.

  1. Vyberte libovolnou buňku. Kliknutím na ikonu vyvoláte okno "Vložit funkci". Klikněte na rozevírací seznam a vyberte požadovanou kategorii.

  1. Poté vyberte požadovanou funkci. Jako příklad zvažte "COUNTIF". Ihned poté se vám zobrazí krátká informace o vybrané položce. Pro podrobnou nápovědu budete muset kliknout na zadanou funkci. Pro pokračování klikněte na "OK".

  1. Poté budete požádáni o zadání "Function Arguments". Pole se budou vždy měnit v závislosti na zvoleném vzorci. PROTI tento případ musíte zadat rozsah buněk a kritérium pro počítání.

  1. Přejděte na první pole. Vyberte požadovaný počet buněk.

  1. Ve druhém poli můžete jako příklad zadat dvě prázdné uvozovky. Tento záznam znamená prázdný text. To znamená, že v buňce by nemělo být nic obsaženo.

Upozornění: všechny argumenty musí být vyplněny úplně. Vzorec nebude fungovat, pokud je jedno z polí prázdné.

Poté klikněte na „OK“.

  1. Díky tomu uvidíte nějaké číslo. Tato hodnota bude odpovídat počtu buněk, které splňují vaše kritéria. V tomto případě jsme vybrali 14 prázdných buněk.

  1. Pokud provedete nějaké změny, výsledek funkce se okamžitě změní.

Tento vzorec lze upravit ručně. K tomu nemusíte používat okno Vložit funkci. Stačí kliknout na požadovanou buňku a přejít na řádek vzorců.

Li daný řetězec se vám zdá malý a nepohodlný, musíte stisknout horké klávesy Ctrl + Shift + U . Díky tomu se jeho výška několikrát zvýší.

Chcete-li se vrátit do předchozího režimu, musíte zopakovat kombinaci kláves Ctrl + Shift + U.

Velikost řádku vzorců zůstane stejná i po zavření Excelu. Nastavení bude fungovat při každém spuštění i mimo něj. Mění se pouze ručně.

Stojí za zmínku, že k vytváření vzorců není nutné používat speciálního průvodce. Tento kód stačí zkopírovat nebo napsat sami.

COUNTIF(C3:C16;””)

Volitelně můžete zadat libovolný rozsah a kritéria, která potřebujete pro výběr.

Matematické a goniometrické funkce

Tato část vzorců je velmi rozsáhlá. Dá se rozdělit do několika skupin. Usnadní vám to navigaci.

Většina začátečníků si myslí, že existuje pouze jeden způsob, jak zaokrouhlit číslo. Ale tak to vůbec není. Existuje na to asi 15 metod. Podívejme se na každou z nich.

Pro začátek si vytvoříme takovou vizuální tabulku. Zde můžeme zaokrouhlit stejné číslo do jednoho sloupce s několika funkcemi. Tabulkové zobrazení zjednoduší proces porovnání.

  1. Přejděte na první buňku v této tabulce. Vyvolejte okno "Vložit funkci". Vyberte kategorii "Matematika". Tam najděte položku "ROUND" a klikněte na "OK".

  1. Zadejte adresu buňky, kde se nachází vaše číslo. Poté vyplňte pole „Číslice_čísla“. Určuje počet desetinných míst za desetinnou čárkou. Klikněte na "OK" pro uložení.

  1. Díky tomu uvidíte následující výsledek.

  1. Poté najeďte myší na pravý dolní roh této buňky. Budete se muset změnit vzhled ukazatel myši. Klikněte levým tlačítkem a bez uvolnění prstu přejeďte na konec stolu.

  1. V důsledku toho uvidíte následující.

  1. Opakujte výše uvedené kroky pro zbývající funkce.

Tyto funkce mají následující účel:

  • ROUND - zaokrouhlí zadanou číslici na určitý počet desetinných míst. Princip fungování je úplně stejný, jak se učí zaokrouhlovat ve škole;
  • ROUNDDOWN – zaokrouhluje dolů na nejbližší (modulo) menší hodnotu. V tomto případě jsou všechny ostatní znaky po zadané přesnosti vyřazeny. V našem případě se z 1,598 stala pouze 1,59. I když podle pravidel matematiky by to mělo být 1,6;
  • ROUNDUP - zaokrouhlí nahoru na nejbližší (modulo) vyšší hodnotu. Princip činnosti je přesně stejný jako u "ROUNDDOWN";
  • ROUND - zaokrouhlení čísla na nejbližší násobek hodnoty, která je násobkem toho, co je uvedeno v poli "přesnost". V naší tabulce jsou všechny výsledky násobky čísla 2. Právě to bylo uvedeno ve druhém parametru;
  • ROUNDUP - princip činnosti je naprosto stejný jako u funkce ROUND. Pouze v tomto případě dojde k zaokrouhlení na nejbližší větší, nikoli na násobek;
  • OKRVNIZ - totéž, jen v menším směru;
  • OTBR - danou funkci zahodí celou zlomkovou část až do zadaného počtu znaků;
  • INTEGER – Zaokrouhlí na nejbližší nejmenší číslo. V tomto případě zůstane pouze celá část;
  • EVEN - funkce vrátí nejbližší sudé celé číslo;
  • ODD - Funkce vrací nejbližší liché celé číslo.

Jak vidíte, mnoho vzorců je navzájem analogických.

Funkce, které končí na „MAT“, mají třetí doplňkový parametr. Pokud to není uvedeno, pak je princip fungování naprosto stejný jako u funkcí bez předpony „MAT“.

Abychom tuto knihovnu funkcí demonstrovali, vytvořte trochu jinou tabulku. Vyplníme úplně stejně.

Tyto funkce umožňují (syntaxe je uvedena speciálně pro naši tabulku):

  • SUM - vypočítá součet všech buněk, které jsou zahrnuty v zadaném rozsahu;
=SUM(C4:C16)
  • SUMIF - vypočítá součet všech buněk, které jsou zahrnuty v zadaném rozsahu a splňují určitou podmínku;
=SUMIF(C4:C16;">3")
  • SUMIFS - vypočítat součet všech buněk, které jsou zahrnuty v určeném rozsahu a splňují několik specifických podmínek;
=SUMIFS(C4:C16;C4:C16;">3";C4:C16;"<7")
  • SUMPRODUCT - výpočet součinu buněk z každého řádku zadaného rozsahu.
=SUMPRODUCT(B4:B16,C4:C16)
  • SUMSQ - vypočítá součet druhých mocnin zadaných argumentů. Můžete použít jak jednoduchá čísla, tak velká pole.
=SUMSQ(B4:B16,C4:C16)
  • SUMMRESQ – vypočítá součet rozdílů druhých mocnin zadaných polí. Používá se následující vzorec.

=SUMDIFF(B4:B16;C4:C16)
  • SUMMSUMMQ – vypočítá součet součtů druhých mocnin zadaných polí. Používá se následující vzorec.

=SUMMSUMMQ(B4:B16;C4:C16)
  • SUMMQDIFF – vypočítá součet druhých mocnin rozdílů zadaných datových polí. Při výpočtech se používá následující vzorec.

=SUMXDIFF(B4:B16,C4:C16)

Upozornění: ve všech výše uvedených případech se počet prvků pole musí shodovat.

Výsledek bude následující.

Díky těmto vzorcům může i konvice najít požadovaný výsledek nebo vytvořit finanční zprávu. Abyste tyto nástroje mohli používat, nemusíte být ekonom ani programátor. Stačí umět rusky. Angličtina není potřeba. Všechny funkce mluví samy za sebe.

Tato funkce je složitější – jde o úkol z programu ústavu. Proto to zvážíme samostatně. Vrací součet komplexní mocninné řady, nikoli běžné pole. V tomto případě vzorec používá velké množství argumentů. Vypadá to příliš děsivě, ale srozumitelněji než Laplaceova rovnice.

Ruční výpočet řady součtů je ještě obtížnější. Proto je editor Excel žádaný, protože je mnohem jednodušší ho používat, než přemýšlet mozkem. A zároveň k rozhodnutí dojde okamžitě, i když na podmínce něco změníte.

Jako příklad pro argument X vezměte 1 prvek 1. pole. Stupeň N začne od 1 s dalším krokem 1. Vezmeme všechny buňky druhého pole jako koeficienty.

Výsledek bude obrovský, protože jsme pro poslední argument zvolili mnoho hodnot. Pokud by jich bylo málo, pak by se číslo snížilo, ale stále by bylo největší v tabulce.

V tomto případě je vše mnohem jednodušší. Využívá standardní školní goniometrické vzorce. A budou se jmenovat téměř úplně stejně. Tyto zahrnují:

  • COS je kosinus úhlu;
  • COSH je hyperbolický kosinus úhlu;
  • COT je kotangens úhlu;
  • COTH je hyperbolický kotangens úhlu;
  • CSC je kosekans úhlu;
  • CSCH je hyperbolický kosekans úhlu;
  • SEC je sečna úhlu;
  • SECH je hyperbolická sečna úhlu;
  • SIN je sinus úhlu;
  • SINH je hyperbolický sinus úhlu;
  • TAN je tangens úhlu;
  • TANH je hyperbolický tangens úhlu.

Vezměte prosím na vědomí, že při výpočtu je třeba zadat úhel v radiánech, nikoli pouze číslo. Pro převod čísla na radiány se používá stejnojmenná funkce. Budeme o tom mluvit podrobně o něco dále.

Vzorec pro kosinus vypadá například takto.

=COS(RADIANS(C2))

Pomocí výše popsaných funkcí můžete sestavit následující tabulku.

Můžete zkontrolovat shodu v tabulce a zjistit, že všechny hodnoty jsou správné. A mínus je tam, kde má být, ale stále je tam takové malé procento chyb.

Tam, kde je velmi velké číslo nebo chyba „dělení nulou“, neexistuje žádná tabulková hodnota. Přesněji řečeno, taková hodnota neexistuje. Ale editor se stále snaží převést vzorec podle návrhu.

Takto vypadá tabulka v učebnicích matematiky. Excel nezobrazuje odmocniny - okamžitě bere v úvahu konečnou číselnou hodnotu.

Kromě toho existují také inverzní funkce. A to:

  • ACOS je úhlový kosinus čísla;
  • ACOSH je hyperbolický inverzní kosinus čísla;
  • ACOT - arkus tangens čísla (funguje od roku 2013);
  • ACOTH - hyperbolický inverzní tangens čísla (funguje od roku 2013);
  • ASIN je arkussinus čísla;
  • ASINH je hyperbolický inverzní sinus čísla;
  • ATAN je arkus tangens čísla;
  • ATANH je hyperbolická inverzní tangens čísla.

Pro správné zobrazení výsledku je potřeba použít funkci "STUPNĚ". Výsledek bude následující. Můžete také zkontrolovat shodu s tabulkovými hodnotami. Kontrola ukáže, že automatické doplňování dat bylo správné.

V některých případech se zobrazí chyba „#NUMBER“. Je to důsledek skutečnosti, že tyto hodnoty neexistují. Například ve vzorci ACOSH lze použít číslo větší nebo rovné 1. A v naší tabulce jsou hodnoty analyzovány od -1 – to je v této funkci nepřijatelné. Někde naopak - rozsah hodnot může být od -1 do 1 a ne více. To znamená, že všechny naše empirické výsledky jsou brány v úvahu s ohledem na pravidla matematiky.

Takové vzorce obvykle studenti často používají k vytváření prezentací nebo k provádění různých laboratorních prací, když jsou požádáni, aby zpracovali nějakou funkci. To znamená vykreslit data do tabulky na základě nějakého matematického výrazu.

Jako příklad si zkusme nakreslit graf exponenciálního růstu pro dané hodnoty.

  1. Sestavíme tabulku a uvedeme potřebné údaje. Můžete jej vyplnit libovolnými čísly. Nebude to znamenat nic kritického.

  1. Do první buňky druhého sloupce vložte následující vzorec.
=EXP(B4)

Poté jej duplikujeme ve zbytku buněk (zatáhněte za roh prvního výsledku).

  1. Poté zvýrazněte všechny své hodnoty v tabulce (zvýraznění záhlaví je povoleno). Přejděte na kartu "Vložit" a klikněte na ikonu "Graf". Vyberte některou z navrhovaných možností rozvržení.

  1. Výsledek nebude zcela správný, protože tato čísla nemáme. V tomto případě došlo k automatickému rozdělení hodnot.

  1. Klikněte pravým tlačítkem na diagram. V zobrazené nabídce klikněte na položku „Vybrat data“.

  1. Klikněte na tlačítko "Změnit".

  1. V okně, které se objeví, budete muset zadat rozsah buněk, které potřebujeme. Pokračujte kliknutím na „OK“.

  1. Pak to samé.

  1. Nyní distribuce probíhá tak, jak má. Všechny hodnoty jsou na svém místě. V takových chvílích je velmi důležitá uživatelská kontrola nad tím, co se děje, protože Excel není dokonalý.

Jak vidíte, růst funkce není vůbec lineární – roste velmi rychle. Malý nárůst je pozorován pouze na začátku. Sestavení takového grafu je poměrně jednoduché.

Chcete-li demonstrovat tyto vzorce, musíte přidat další tabulku.

Pomocí funkce ROMAN jsme byli schopni zapsat tato čísla jako římská čísla. Potom pomocí "ARABIC" jsme byli schopni vrátit normální vzhled pro nás. Navíc k výměně došlo z předchozí transformace. U programu Excel je jedno, co bude tato buňka obsahovat – vzorec, statický nebo proměnný text. Proto je praktický potenciál funkcí prostě neuvěřitelný.

Trigonometrie používá následující převodní funkce (příklady byly uvedeny výše):

  • DEGREES - převod radiánů na stupně;
  • RADIANS - převod stupňů na radiány.

Kromě toho existuje další zajímavý a užitečný nástroj pro převod různých čísel z nějaké číselné soustavy do desítkové. K demonstraci této funkce je třeba přidat ještě jednu tabulku.

Poté budete muset zadat následující vzorce.

=DES(C6;16) =DES(C9,2)

Nejprve je uvedeno číslo buňky a poté původní číselný systém. Výsledek bude takový.

Aritmetické funkce

Tyto vzorce zahrnují:

  • SOUČIN - násobení dvou čísel;
  • OSTAT - odstranění celočíselné části z dělení;
  • POWER - zvýšení zadaného čísla na požadovaný výkon;
  • PARTICULAR - získání celé části z dělení čísla;
  • EXP - zvýšení exponentu na zadanou mocninu;
  • ROOT - získání kladného kořene ze zadaného čísla.

Tyto funkce se vyučují ve středních třídách. Nemusíte kvůli tomu hledat žádné shrnutí – to vše by mělo být ve vaší hlavě. I když máte mezery ve znalostech, tyto informace by měly být zachovány.

Pro jednoduché odečtení dvou hodnot stačí použít obvyklý symbol mínus ("-"). Neexistuje pro to žádná speciální funkce.

miscellanea

  • Kromě toho existuje mnoho dalších matematických funkcí, které je obtížné spojit do jedné podmíněné skupiny.
  • ABS je modul zadané hodnoty;
  • AGGREGATE – výraz agregovaného seznamu (více viz oficiální nápověda);
  • FAKTOR - výpočet faktoriálu zadaného čísla;
  • GCD - hledání největšího společného dělitele;
  • LCM - hledání nejmenšího společného násobku;
  • MOPRED - umožňuje najít determinant matice datového pole;
  • MULTIP - maticový součin čísel dvou polí;
  • PI - zadání čísla "pi" do vzorce;
  • RAND - náhodný výběr čísla od 0 do 1;
  • RANDBETWEEN - náhodná hodnota mezi zadanými čísly;
  • ZNAK - umožňuje určit znaménko zadané hodnoty;
  • SUBTOTAS - Konsolidace hodnot a shrnutí seznamu nebo databáze.

Informační funkce

Tyto vzorce jsou v podstatě nástrojem pro analýzu dat. Je docela snadné je zapsat. Jejich účel je následující:

  • EMPLAND - kontrola buňky na přítomnost nějaké hodnoty;
  • KONEC - kontrola buňky na chyby #N/A;
  • INUMERY - kontrola shody hodnoty s číselným formátem;
  • ISERROW - kontrola případných chyb;
  • ISTEXT - funkce vrátí hodnotu true, pokud argument obsahuje textovou hodnotu;
  • ENETEXT - podobná kontrola, pouze obráceně;
  • EOSH - funkce vrátí pravdivý výsledek, pokud buňka obsahuje jinou chybu než #N/A;
  • pro kontrolu sudé nebo liché hodnoty jsou vzorce SUDÉ a NELICHÉ;
  • EFORMULA - kontroluje přítomnost vzorce v zadané buňce.

Existuje ale složitější funkce, o které stojí za to mluvit samostatně.

Pomocí tohoto nástroje můžete zjistit všechny potřebné informace o zadané buňce. V tomto případě musíte použít požadovaný parametr - "Typ informací", pomocí kterého můžete zjistit:

  • barva;
  • adresa;
  • sloupec;
  • a mnohem víc.

Další podrobnosti naleznete na webu společnosti Microsoft.

Tyto konstrukce se používají k sestavení velkých a složitých vzorců.

  • A je pravdivé, pokud jsou splněny všechny podmínky;
  • NEBO - true, pokud je splněna alespoň jedna podmínka;

K analýze různých podmínek se používají následující funkce:

  • IF - pro kontrolu jedné události;
  • PODMÍNKY - stejné, jen s obrovským množstvím podmínek.

Poslední z výše uvedených se objevil pouze v editoru Excelu 2016. Dříve se používala možnost „KDYŽ“.

Příkladem je následující tabulka.

V tomto případě byly použity dvě funkce najednou: "IF" a "OR".

=KDYŽ(NEBO(D3="První";D3="Druhé");100;0)

Pro kontrolu fungování vzorce můžete použít konstrukci s "IFERROR". Pokud je vše správně sestaveno, pak uvidíte výsledek výpočtů. V opačném případě uvidíte zadanou hodnotu v textové podobě.

Funkce odkazů a vyhledávání

  • SELECT - výběr nějaké hodnoty ze seznamu (pole) dat;
  • COLUMN - zobrazí číslo sloupce zadané buňky;
  • GET.PIVOTO.TABLE.DATA – zobrazí data uložená v sestavě kontingenční tabulky;
  • HPR - vyhledávání v datovém poli;
  • INDEX - výběr nějaké hodnoty podle dodatečného indexu v zadaném rozsahu buněk;
  • NEPŘÍMÉ - získání odkazu, který byl původně nastaven na textovou hodnotu;
  • VIEW - hledání hodnot v poli;
  • MATCH - hledání pozice zadaného textu nebo hodnoty v určitém rozsahu buněk;
  • OFFSET – offset spoje vzhledem k zadané spojce;
  • LINE - vrátí číslo řádku v zadané referenci;
  • TRANSPOSE - transpozice datového pole;
  • VLOOKUP - vyhledá hodnotu v jednom poli a získá data z buňky v nalezeném řádku v konkrétním seznamu dat.

Funkce databáze

V této sekci je poměrně dost vzorců. Zvažte několik nejzákladnějších a nejoblíbenějších. Tyto zahrnují:

  • BIZVELCH - vyhledá záznam v databázi, který odpovídá zadané podmínce výběru;
  • BDSUMM - součet všech čísel, která jsou v zadaném poli a splňují určité podmínky;
  • DMIN - vyhledá minimální hodnotu mezi celým vzorkem dat z databáze;
  • DMAX - vyhledá maximální hodnotu mezi celým vzorkem dat z databáze;

Tato část vzorců je velmi zajímavá a užitečná. S jejich pomocí můžete rychle analyzovat některé informace nebo vytáhnout určitá data od určitého data.

  • DEN - určuje se den v zadaném datu;
  • MĚSÍC - určuje se měsíc v uvedeném datu;
  • ROK - určuje se rok v uvedeném datu;
  • DNES - zobrazení aktuálního data;
  • NUMWEEK - zobrazí číslo týdne na základě zadaného data;
  • DNES - zobrazení aktuálního data a aktuálního času;
  • HODINA - určuje, jaká hodina je uvedena v konkrétní datum;
  • MINUTY - určuje, kolik minut je uvedeno v konkrétním datu;
  • SEKUND - určuje, kolik sekund je uvedeno v určitém datu;
  • WEEKDAY - vypočítá pořadové číslo dne v týdnu (odpočítávání začíná od neděle, ne od pondělí).

Kromě toho existují složitější vzorce. Tyto zahrnují:

  • RAZNDAT - počítá se počet let, měsíců a dnů mezi zadanými daty;
  • DNY - počítá se počet dní mezi zadanými daty (funkce se objevila v roce 2013);
  • NETWORKDAYS - počítá se počet pracovních dnů mezi zadanými daty;
  • WEEKDAY - převod obyčejného data v číselném formátu na pořadové číslo týdne;
  • WORKDAY - zobrazuje datum, které je před nebo po zadaném počtu dní.

Více o posledním vzorci si můžete přečíst na oficiálních stránkách Microsoftu.

Syntaxe této funkce je následující.

A příklady jsou docela jednoduché.

Pro práci s textovými informacemi v editoru Excel existuje obrovská sada různých funkcí. Mezi nejoblíbenější patří:

  • CONNECT - v tomto případě jsou různé kusy spojeny do jednoho plnohodnotného textu;
  • PŘESNĚ - kontrola dvou hodnot pro úplnou vzájemnou shodu;
  • FIND, FINDB - hledání fragmentu v jiném textu (funkce hledá velká a malá písmena);
  • SEARCH, POISKB - podobné vyhledávání, pouze nerozlišuje malá a velká písmena;
  • LEFT, LEVB - kopírování prvních znaků řetězce (v jednom případě se výpočet provádí znak po znaku a ve druhém případě po bytech);
  • RIGHT, RIGHTB - stejný význam, pouze počítáno z pravé strany;
  • DLSTR, LONGB - počet znaků v řádku;
  • PSTR, PSTRB - zkopírování fragmentu požadovaného počtu znaků ze zadané pozice pro sestavu;
  • REPLACE, REPLACEB - nahrazení určitých znaků v textovém řetězci;
  • SUBSTITUTE - nahrazení jednoho textu jiným;
  • TEXT - převod čísla do textového formátu;
  • UNITE - spojení různých textových fragmentů do jednoho celku (v tomto případě je vložen nějaký ukazatel).

Poslední zadaný vzorec se objevil v nejnovější verzi Microsoft Excel 2016. Jeho syntaxe je následující.

Tato část vzorců je mnohem složitější než ty popsané dříve. Abychom se netrápili s výběrem dat, použijeme jako příklad oficiální nápovědu Microsoftu.

PMT

Video návod

Pro některé uživatele je obtížné porozumět pokynům v textové podobě. Speciálně pro ně jsme připravili videonávod s podrobným popisem všech výše diskutovaných akcí.

Úvod

1: Microsoft Excel

1.1 Koncepce a možnosti MS Excel

1.2 Základní prvky okna MS Excel

1.4 Možné chyby při používání funkcí ve vzorcích

2: Analýza dat. Pomocí skriptů

2.1 Analýza dat v MS Excel

2.2 Scénáře

2.3 Příklad výpočtu vnitřní míry návratnosti investic

Závěr

Bibliografie

Úvod

Microsoft Office, nejoblíbenější rodina produktů kancelářského softwaru, obsahuje nové verze známých aplikací, které podporují internetové technologie a umožňují vytvářet flexibilní internetová řešení.

Microsoft Office je rodina softwarových produktů společnosti Microsoft, která spojuje světově nejoblíbenější aplikace do jediného prostředí ideálního pro práci s informacemi. Microsoft Office obsahuje textový procesor Microsoft Word, tabulky Microsoft Excel, prezentační a prezentační nástroj Microsoft PowerPoint a novou aplikaci Microsoft Outlook. Všechny tyto aplikace tvoří Standard Edition sady Microsoft Office. Professional Edition také obsahuje Microsoft Access DBMS.

Microsoft vynikat- program určený k uspořádání dat v tabulce pro dokumentaci a grafickou prezentaci informací.

MSExcel se používá k vytváření složitých dokumentů, ve kterých potřebujete:

používat stejná data v různých listech;

Změňte a obnovte připojení.

Výhodou MSExcelu je, že program pomáhá zpracovávat velké množství informací. Sešity MSExcel poskytují možnost ukládat a organizovat data, vypočítat součet hodnot v buňkách. MsExcel poskytuje širokou škálu metod pro snadné pochopení informací.

V dnešní době je důležité, aby každý člověk znal a měl dovednosti v práci s aplikacemi Microsoft Office, neboť moderní svět je přesycen obrovským množstvím informací, se kterými stačí umět pracovat.

Tento kurz podrobněji představí aplikaci MSExcel, její vlastnosti a možnosti. Stejně jako využití scénářů s jejich praktickou aplikací.

1. Microsoft vynikat

1.1 . Microsoft vynikat . Koncepty a možnosti

Tabulkový kalkulátor MS Excel (tabulkové kalkulátory) je jednou z nejčastěji používaných aplikací sady MS Office, mocný nástroj ve schopných rukou, který výrazně zjednodušuje rutinní každodenní práci. Hlavním účelem MS Excel je řešení téměř jakýchkoli výpočtových problémů, jejichž vstupní data lze prezentovat ve formě tabulek. Použití tabulek usnadňuje práci s daty a umožňuje získat výsledky bez programovacích výpočtů. V kombinaci s programovacím jazykem Visual Basic for Application (VBA) se tabulkový procesor MS Excel stává univerzálním a umožňuje řešit vůbec jakýkoli problém bez ohledu na jeho povahu.

Funkce tabulek je schopnost používat vzorce k popisu vztahu mezi hodnotami různých buněk. Výpočet podle zadaných vzorců se provádí automaticky. Změna obsahu libovolné buňky vede k přepočítání hodnot všech buněk, které jsou s ní spojeny pomocí vztahů vzorců, a tím k aktualizaci celé tabulky podle změněných dat.

Hlavní vlastnosti tabulek:

1. provádění stejného typu složitých výpočtů na velkých souborech dat;

2. automatizace konečných výpočtů;

3. řešení problémů výběrem hodnot parametrů;

4. zpracování (statistická analýza) výsledků experimentů;

5. hledání optimálních hodnot parametrů (řešení optimalizačních problémů);

6. příprava tabulkových dokumentů;

7. konstrukce diagramů (včetně souhrnných) podle dostupných údajů;

8. tvorba a analýza databází (seznamů).

1.2. Základní prvky okna MS Excel

Hlavní prvky pracovního okna jsou:

1. Záhlaví (obsahuje název programu) s tlačítky pro ovládání okna programu a okna dokumentu (Minimalizovat, Minimalizovat do okna nebo Rozbalit na celou obrazovku, Zavřít);

2. Lišta hlavní nabídky (každá položka nabídky je souborem příkazů spojených společným funkčním zaměřením) plus okno pro vyhledávání informací nápovědy.

3. Panely nástrojů (Standardní, Formátování atd.).

4. Řádek vzorců, který obsahuje jako prvky pole Název a tlačítko Vložit funkci (fx), je určen pro zadávání a úpravu hodnot nebo vzorců v buňkách. Pole Název zobrazuje adresu aktuální buňky.

5. Pracovní plocha (aktivní pracovní list).

6. Posuvníky (vertikální a horizontální).

7. Sada štítků (listových štítků) pro pohyb mezi listy.

8. Stavový řádek.

1.3 Struktura tabulkových procesorů

Soubor vytvořený pomocí MS Excel se nazývá sešit. Můžete vytvořit tolik sešitů, kolik dovolí dostupná paměť na odpovídajícím paměťovém zařízení. Můžete otevřít tolik sešitů, kolik jich bylo vytvořeno. Aktivním sešitem však může být pouze jeden aktuální (otevřený) sešit.

Sešit je sada listů, z nichž každý má tabulkovou strukturu. V okně dokumentu je zobrazen pouze aktuální (aktivní) list, se kterým se pracuje. Každý list má název, který se zobrazí na kartě listu ve spodní části okna. Pomocí zkratek můžete přepínat na další listy obsažené ve stejném sešitu. Chcete-li list přejmenovat, poklepejte na jeho záložku a nahraďte starý název novým nebo provedením následujících příkazů: Nabídka Formát, Řádek listu v seznamu nabídek, Přejmenovat. Nebo umístěním ukazatele myši na zástupce aktivního listu, klepněte pravým tlačítkem myši a poté v zobrazené kontextové nabídce klepněte na řádek Přejmenovat a proveďte přejmenování. Do sešitu můžete přidávat (vkládat) nové listy nebo mazat nepotřebné. Vložení listu lze provést spuštěním příkazu nabídky Vložit, řádek List v seznamu položek nabídky. List bude vložen před aktivní list. Výše uvedené akce lze provádět také pomocí kontextového menu, které se aktivuje stisknutím pravého tlačítka myši, jehož ukazatel by měl být nastaven na kartě odpovídajícího listu. Chcete-li vyměnit listy, musíte umístit ukazatel myši na záložku přesouvaného listu, stisknout levé tlačítko myši a přetáhnout záložku na požadované místo.

List (tabulka) se skládá z řádků a sloupců. Sloupce jsou titulovány velkými latinskými písmeny a dále kombinacemi dvou písmen. List obsahuje celkem 256 sloupců pojmenovaných A až IV. Řádky jsou postupně číslovány od 1 do 65536.

Buňky tabulky se tvoří na průsečíku sloupců a řádků. Jsou to minimální prvky pro ukládání dat. Každá buňka má svou vlastní adresu. Adresa buňky se skládá z názvu sloupce a čísla řádku, na jehož průsečíku se buňka nachází, například A1, B5, DE324. Adresy buněk se používají při psaní vzorců, které určují vztah mezi hodnotami umístěnými v různých buňkách. V aktuálním čase může být aktivní pouze jedna buňka, která se aktivuje kliknutím na ni a je zvýrazněna rámečkem. Tento rámec v Excelu funguje jako kurzor. Operace zadávání dat a editace se provádějí vždy pouze v aktivní buňce.

Na data umístěná v sousedních buňkách, které tvoří obdélníkovou oblast, lze ve vzorcích odkazovat jako na celek. Skupina buněk ohraničená obdélníkovou oblastí se nazývá rozsah. Nejčastěji používané obdélníkové rozsahy jsou tvořeny na průsečíku skupiny po sobě jdoucích řádků a skupiny po sobě jdoucích sloupců. Rozsah buněk je označen zadáním adresy první buňky a adresy poslední buňky rozsahu oddělených dvojtečkou, například B5:F15. Výběr oblasti buněk lze provést diagonálním přetažením ukazatele myši z jedné rohové buňky do protější buňky. Rámeček aktuální (aktivní) buňky se rozšíří a pokryje celý vybraný rozsah.

Pro urychlení a zjednodušení výpočetní práce poskytuje Excel uživateli výkonný aparát funkcí listu, který umožňuje provádět téměř všechny možné výpočty.

Celkově MS Excel obsahuje přes 400 funkcí listu (vestavěné funkce). Všechny jsou podle účelu rozděleny do 11 skupin (kategorií):

1. finanční funkce;

2. funkce data a času;

3. aritmetické a goniometrické (matematické) funkce;

4. statistické funkce;

5. funkce vazeb a substitucí;

6. databázové funkce (analýza seznamů);

7. textové funkce;

8. logické funkce;

9. informační funkce (kontrola vlastností a hodnot);

10.inženýrské funkce;

11.vnější funkce.

Zápis jakékoli funkce do buňky listu musí začínat symbolem rovná se (=). Pokud je funkce použita jako součást jakékoli jiné komplexní funkce nebo ve vzorci (mega-vzorci), pak se před tuto funkci (vzorec) zapíše symbol rovná se (=). Volání libovolné funkce se provádí zadáním jejího názvu následovaného argumentem (parametrem) nebo seznamem parametrů v závorkách. Přítomnost závorek je povinná, slouží jako znamení, že použitý název je názvem funkce. Parametry seznamu (argumenty funkcí) jsou odděleny středníkem (;). Jejich počet by neměl přesáhnout 30 a délka vzorce obsahujícího libovolný počet volání funkcí by neměla přesáhnout 1024 znaků. Při psaní (zadávání) vzorce se doporučuje psát všechny názvy malými písmeny, poté se správně zadané názvy zobrazí velkými písmeny.

1.4 Možné chyby při používání funkcí ve vzorcích

Všechny vzorce v Excelu začínají na rovnítko"="." Ony fungovat s čísly, textem, názvy buněk, funkcemi. Hlavní rozdíly z běžných matematických vzorců - vzorec v Excelu je uveden na jednom řádku, místo proměnných jsou použity názvy buněk. Pro výpočty se používají následující operace:

  • přidání – «+»;
  • odčítání – «-»;
  • násobení – «*»;
  • divize – «/»;
  • erekce k moci - "^".

Používá se k označení pořadí akcí. závorky. Zde jsou příklady správných vzorců:

Příklad jednoduché výpočty:

Aplikace funkcí

K automatizaci výpočtů se používají různé metody. funkcí. Lze je vložit stisknutím tlačítka " Funkce vložení". Alternativní možnost - stisknutí kombinace Shift+F3(pro notebooky Shift+fn+F3). Zobrazí se dialogové okno s výzvou k výběru kategorie. Dále je definována konkrétní funkce, nastaveny její argumenty a „ OK».

Tady příklad výpočtu kroku odmocnina z čísla. Vyvoláno dialogové okno, vybrána sekce " Matematický", Dále " VYKOŘENIT»:

zeptal se argument(v tomto případě je to B1):

kliknuto " OK»:

Matematické výpočty

Jednou z nejžádanějších matematických funkcí je SOUČET, je navržen tak, aby sčítal hodnoty buněk. SUM může pracovat s celým číslem rozsah:

SOUČET(B1:B3)

Zde jsou přidány B1, B2 a B3.

Také platí součet jednotlivých buňky:

SOUČET(B1;B3)

A tady přidali B1 a B3.

Analogový SUM - PRODUKTY, umožňuje násobit hodnoty. Příklad násobení rozsahu:

PRODUKT(B1:B3)

Logické funkce

V kapitole " hlavolam» existují nástroje, se kterými lze pracovat logický hodnoty. Nejjednodušší možností je přiřadit buňce hodnotu SKUTEČNÝ nebo LEŽÍCÍ:

Umět převrátit obsah:

LI umožňuje stavět složité struktury. Aplikováno v tomto formátu:

KDYŽ(logický_výraz, hodnota1, hodnota2)

Jako logický výraz můžete použít jednu z operací porovnání:

  • se rovná – «=»;
  • více – «>»;
  • méně – «<»;
  • více nebo stejné – «>=»;
  • menší nebo stejný – «<=»;
  • ne rovné – «<>».

Příklady srovnávací operace: 2<3, B1<>B4, F5>=10.

Příklad použití LI:

IF(2>=1;10;20)

Zpracování textu

Excel má také nástroje pro jednoduché operace s textovými hodnotami. DLSTR vrátí délku textového argumentu, například =LSTR("Volha teče do Kaspického moře") vrátí 31.

NALÉZT hledá jeden text v druhém a se vracíčíslo pozice prvního výskytu. Pokud zadáme =FIND("ac";"Vasily";1), dostaneme 2.

NÁHRADNÍ nahradí jeden fragment v textu jiným. =NAHRADIT("Všechno je v pořádku!"; "e"; "e") dá výsledek "Všechno je v pořádku!". Pokud zadáte prázdný řetězec jako třetí argument, pak bude fragment jednoduše odstraněn z celého textu.

Chcete-li spojit více řádků do jednoho, můžete použít PŘIPOJIT. =CONCATENATE("Dobrý";"den") vytvoří malou dvouslovnou frázi.

datum a čas

Excel má mnoho užitečných nástrojů pro práci s časy a daty. V níže uvedeném příkladu bylo aktuální datum umístěno do A1 pomocí vzorce =DNES() a poté jej rozdělit na jednotlivé části. Za tímto účelem jsme podali žádost návrhy=DEN(A1), =MĚSÍC(A1), =ROK(A1). Výsledek:

Chcete-li získat aktuální čas, zadejte =DATUM(), poté změňte formát buňky pravým tlačítkem myši ("Formát buněk ..." -> "Číslo" -> "Čas") a vyberte vhodnou reprezentaci. Je také možné extrahovat součásti z aktuálního času (pomocí SEKUND, MINUT, HODIN).