Definování a vytváření vzorce

V této lekci uvádíme základní pravidla pro vytváření vzorců a použití funkcí. Máme pocit, že jedním z nejlepších způsobů, jak se učit, je praxe, proto uvádíme několik příkladů a podrobně je vysvětlíme. Témata, která pokryjeme, zahrnují:

ŠKOLNÍ NAVIGACE

Proč potřebujete vzorce a funkce?
Definování a vytváření vzorce
Relativní a absolutní odkaz na buňky a formátování
Užitečné funkce, které byste měli vědět
Vyhledávání, grafy, statistiky a kontingenční tabulky

řádky a sloupce
Příklad matematické funkce: SUM ()
operátoři
přednost operátora
příklad finanční funkce: PMT (), úvěrová platba
pomocí funkce "string" ("řetězec" je zkratka pro "řetězec textu") uvnitř vzorce a funkce vnoření

Vzorce jsou směs "funkcí", "operátorů" a "operandů". Než zapíšeme několik vzorců, musíme vytvořit funkci, ale než budeme moci vytvořit funkci, musíme nejprve pochopit řádkovou a sloupcovou notaci.

Řádky a sloupce

Chcete-li pochopit, jak psát vzorce a funkce, potřebujete vědět o řádcích a sloupcích.

Řádky probíhají vodorovně a sloupce probíhají vertikálně. Vzpomínáte si na to, co je, co si myslíte o sloupku, který drží střechu - sloupy jdou nahoru a dolů, takže řádky jdou zleva doprava.

Sloupce jsou označeny písmeny; řádek podle čísel. První buňka v tabulce je A1 znamená sloupec A, řádek 1. Sloupce jsou označeny jako A-Z. Když abeceda běží, Excel umístí další písmeno vpřed: AA, AB, AC ... AZ, BA, BC, BC atd..

Příklad: Funkce Sum ()

Nyní ukážeme, jak používat funkci.

Funkce používáte tak, že je zadáte přímo nebo pomocí průvodce funkcí. Průvodce funkcí se otevře při výběru funkce z nabídky "Formule" z "Knihovny funkcí". V opačném případě můžete zadat = v buňce a praktická rozevírací nabídka vám umožní vybrat funkci.

Průvodce vám řekne, jaké argumenty je třeba poskytnout pro každou funkci. Poskytuje také odkaz na online pokyny, pokud potřebujete pomoc s pochopením toho, co funkce dělá a jak ji používat. Například pokud zadáte = sumu do buňky, in-line průvodce vám ukáže, jaké argumenty jsou požadovány pro funkci SUM.

Když zadáte nějakou funkci, průvodce je vložený nebo přímo na prsty. Když vyberete funkci z nabídky "Formule", průvodce je vyskakovací okno. Zde je pop-up průvodce pro funkci SUM ().

Pro naši první funkci použijeme SUM (), který přidává seznam čísel.

Předpokládejme, že máme tuto tabulku obsahující plány pro rozpočet na dovolenou pro rodinu:

Pro výpočet celkových nákladů můžete napsat = b2 + b3 + b4 + b5, ale je snadnější použít funkci SUM ().

V aplikaci Excel vyhledejte symbol Σ v levém horním rohu obrazovky aplikace Excel a vyhledejte tlačítko AutoSum (matematici používají pro přidání řady čísel řecké písmeno Σ).

Pokud je kurzor pod čísly rodinného rozpočtu, Excel je dostatečně chytrý, aby věděl, že chcete shrnout seznam čísel, na kterém jste umístili kurzor, a tak zvýrazní čísla.

Stiskněte tlačítko "enter" pro přijetí rozsahu vybraného aplikací Excel nebo použijte kurzor pro změnu vybraných buněk.

Pokud se podíváte na to, co aplikace Excel vložila do tabulky, uvidíte, že tato funkce byla napsána:

V tomto vzorce Excel sumuje čísla z B2 do B9. Všimněte si, že jsme měli pokoj pod řádek 5, abyste mohli přidat do rozpočtu na rodinnou dovolenou - náklady jistě stoupají, protože seznam dětí o tom, co chtějí dělat a kde chtějí jít, roste déle!

Matematické funkce nefungují s písmeny, takže pokud vložíte písmena do sloupce, výsledek se zobrazí jako "#NAME?", Jak je znázorněno níže.

#NÁZEV? znamená, že existuje nějaká chyba. Mohlo by to být mnoho věcí, včetně:

špatná reference buněk
pomocí písmen v matematických funkcích
vynechání požadovaných argumentů
nesprávné jméno funkce pravopisu
nelegální matematické operace jako divize 0

Nejjednodušší způsob výběru argumentů ve výpočtu je použití myši. Můžete přidat nebo odebrat ze seznamu argumentů do funkce zvětšením nebo zmenšením pole, které aplikace Excel kreslí při pohybu myší nebo klepnutím na jinou buňku.

Klikli jsme na horní část náměstí čerpaného společností Excel, abychom z rozpočtu vybrali "letenky". Můžete vidět symbol křížových vláken, který můžete kreslit, aby byl vybraný rozsah větší nebo menší.

Stiskněte tlačítko "enter" pro potvrzení výsledků.

Operátory výpočtu

Existují dva typy operátorů: matematika a srovnání.

Matematický operátor	Definice
+	přidání
-	odčítání nebo negace, např. 6 * -1 = -6
* *	násobení
/	divize
%	procent
^	exponent, např. 2⁴= 2 ^ 4 = 2 * 2 * 2 * 2 = 16

Existují další operátoři, kteří se netýkají matematiky, jako je "&", což znamená spojit dva konce řetězce (připojit end-to-end). Například, = "Excel" & "je Fun" se rovná "Excel je zábava".

Nyní se podíváme na srovnávací operátory.

Operátor srovnání	Definice
=	se rovná např. 2 = 4 nebo "b" = "b"
>	větší než např. 4> 2 nebo "b"> "a"
<	méně než např. 2 < 4 or “a” < “b”
> =	větší nebo roven - jiným způsobem, jak to myslet, je> = prostředky buď > nebo =.
<=	méně než nebo rovnající se.
	není rovno např. 46

Jak můžete vidět výše, operátoři porovnání pracují s čísly a textem.

Poznámka: Pokud zadáte hodnotu "a"> "b" do buňky, zobrazí se "FALSE", protože "a" není větší než "b". "B" > "B" nebo "B"> "a."

Přednost objednávky operátora

Přednost objednávky je myšlenkou z matematiky. Aplikace Excel musí dodržovat stejná pravidla jako matematika. Toto téma je komplikovanější, tak dejte si dech a dejme se.

Přednost objednávky znamená pořadí, v němž počítač vypočte odpověď. Jak jsme vysvětlili v lekci 1, plocha kruhu je πr², který je stejný jako π * r * r. to je ne (πr)².

Takže při sestavování vzorce musíte pochopit prioritu objednávky.

Obecně lze říci toto:

Aplikace Excel nejprve vyhodnotí položky v závorkách, které pracují dovnitř.
Potom použije pravidla přednosti pořadí matematiky.
Když mají dvě položky stejnou přednost, Excel pracuje zleva doprava.

Přednost matematických operátorů je uvedena níže, v sestupném pořadí.

( a )	Pokud se používají závorky, přepsají normální pravidla přednosti. To znamená, že aplikace Excel provede nejprve tento výpočet. Vysvětlujeme to níže.
-	Negace, např. -1. Toto je stejné jako vynásobení čísla číslem -1. -4 = 4 * (-1)
%	Procento znamená násobení hodnotou 100. Např. 0,003 = 0,3%.
^	Exponent, např. 10 ^ 2 = 100
* a /	Vynásobte a dělejte. Jak mohou mít dva operátoři stejnou přednost? To jen znamená, že pokud má vzorec dva další operátory se stejnou předností, výpočet se provede zleva doprava.
+ a -	Sčítání a odčítání.

Existují další pravidla přednosti týkající se řetězců a referenčních operátorů. Momentálně se budeme držet toho, co jsme právě pokryli. Nyní se podívejme na některé příklady.

Příklad: Výpočet plochy kruhu

Oblast kruhu je= PI () * poloměr ^ 2.

Podíváme-li se na tabulku výše, vidíme, že exponenti přicházejí před násobením. Takže počítač nejprve vypočítá poloměr ^ 2 a potom se násobí, že výsledek Pi.

Příklad: Výpočet zvýšení platu

Řekněme, že váš šéf rozhodne, že děláte skvělou práci a on nebo ona vám dá 10% zvýšení! Jak byste vypočítala svůj nový plat??

Nejprve nezapomeňte, že násobení je před přidáním.

Je to = plat + plat * 10% nebo je to = plat + (plat * 10%)?

Předpokládejme, že váš plat je 100 USD. S nárůstem o 10% bude váš nový plat:

= 100 + 100 * 10% = 100 + 10 = 110

Můžete také napsat takto:

= 100 + (100 x 10%) = 100 + 10 = 110

Ve druhém případě je jasné použití pořadí předností pomocí závorek. Nezapomeňte, že před jinými operacemi se hodnotí závady.

Mimochodem, snadnější způsob psaní je = plat * 110%

Vzpěry mohou být navzájem vnořené. Takže, když píšeme (3 + (4 * 2)), pracujeme zevnitř na druhou stranu, nejdříve vypočítáme 4 * 2 = 8, pak přidáme 3 + 8, abychom dostali 11.

Několik dalších příkladů

Zde je další příklad: = 4 * 3 / 2. Jaká je odpověď?

Z pravidel uvedených v tabulce vidíme, že * a / mají stejnou přednost. Takže Excel pracuje zleva doprava, 4 * 3 = 12 nejprve, pak rozdělí, že o 2 dostaneme 6.

Opět můžete toto vyjádřit písemně = (4 * 3) / 2

A co = 4 + 3 * 2?

Počítač vidí operátory * a +. Takže podle pravidel přednosti (multiplikace přichází před přidáním) vypočítá nejprve 3 * 2 = 6, pak přidá 4 pro získání 10.

Pokud byste chtěli změnit pořadí priority, napíšete = (4 + 3) * 2 = 14.

A co tohle = -1 ^ 3?

Pak odpověď je -3, protože počítač vypočtený = (-1) ^ 3 = -1 * -1 * -1 = -1.

Nezapomeňte, že záporné časy negativní jsou pozitivní a negativní časy pozitivní jsou negativní. Můžete to vidět takto (-1 * -1) * -1 = 1 * -1 = -1.

Takže existuje několik příkladů matematického pořadí a přednosti, doufáme, že vám pomůže vyjasnit několik věcí o tom, jak Excel provádí výpočty (a to je pravděpodobně dost matematiky, které by mohlo trvat i pro vás).

Příklad: Úhrada funkční půjčky (PMT)

Podívejme se na příklad výpočtu výpůjčky.

Začněte vytvořením nového listu.

Formátujte čísla dolarovými znaky a použijte nulové desetinné časy, protože momentálně nemají zájem o centy, protože na tom, když hovoříte o dolaru, nezáleží moc (v další kapitole se věnujeme podrobnému formátování čísel). Chcete-li například formátovat úrokovou sazbu, klikněte pravým tlačítkem myši na buňku a klikněte na tlačítko "formátovat buňky". Zvolte procentní podíl a použijte dvě desetinná místa.

Podobně formátujte ostatní buňky pro "měnu" namísto procentního podílu a vyberte "číslo" pro termín půjčky.

Nyní máme:

Přidejte funkci SUM () na "celkové" měsíční výdaje.

Poznámka: hypotéka buňka není zahrnuto do součtu. Aplikace Excel neví, že chcete zahrnout toto číslo, protože zde není žádná hodnota. Takže opatrně rozšiřte funkci SUM () na vrchol buď kurzorem nebo zadáním E2, kde se uvádí, že E3 zahrnuje hypotéku v součtu.

Umístěte kurzor do platební buňky (B4).

V nabídce Vzorce vyberte rozbalovací položku "Finanční" a vyberte funkci PMT. Průvodce se objeví:

Použijte kurzor pro výběr "sazby", "nper" (termín půjčky), "Pv" ("současná hodnota" nebo výše úvěru). Všimněte si, že musíte rozdělit úrokovou sazbu o 12, protože úroky jsou vypočítávány měsíčně. Také musíte vynásobit dobu úvěru v letech o 12, abyste získali termín půjčky v měsících. Stisknutím tlačítka "OK" uložte výsledek do tabulky.

Všimněte si, že platba je zobrazena jako záporné číslo: -1013.37062. Chcete-li, aby to bylo pozitivní a přidejte do měsíčních výdajů, ukažte na hypotéční buňku (E2). Zadejte "= -" a poté pomocí kurzoru přejděte na platební pole. Výsledná vzorec je = -B4.

Nyní tabulka vypadá takto:

Vaše měsíční výdaje jsou $ 1,863 - Ouch!

Příklad: Textová funkce

Zde ukážeme, jak používat funkce uvnitř vzorce a textové funkce.

Předpokládejme, že máte seznam studentů, jak je uvedeno níže. Jméno a příjmení jsou v jednom poli odděleny čárkou. Musíme dát poslední a pevné jména do samostatných buněk. Jak to uděláme?

Chcete-li tento problém vyřešit, musíte použít algoritmus, tj. Krok za krokem k tomu.

Například, podívejte se na "Washington, George." Postup rozdělit to do dvou slov by byl:

Vypočítejte délku řetězce.
Najít pozici čárky (to ukazuje, kde končí jedno slovo a začne druhé slovo).
Zkopírujte levou stranu řetězce až na čárku.
Zkopírujte pravou stranu řetězce z čárky na konec.

Podívejme se, jak to udělat s "George Washingtonem" krok za krokem v Excelu.

Vypočítejte délku řetězce s funkcí = LEN (A3) - výsledek je 18.
Nyní najděte polohu čárky zadáním této funkce = FIND (",", A3 ") - výsledek je 11.
Nyní odeberte levou stranu řetězce až do čárky a vytvořte tuto vnořenou vzorec pomocí výsledků z kroku 1: = LEVÉ (A3, FIND (",", A3) -1). Poznámka: Musíme odečíst 1 z délky, protože FIND udává polohu čárky.

Zde je to, co všechno vypadá, když jsou všechny funkce umístěny dohromady ve vzorci. V buňce B3 vidíte, že tento vzorec přebírá všechny informace z buňky A3 a do něj vkládá "Washington".

Takže máme "Washington", teď musíme dostat "George". Jak to uděláme??

Všimněte si, že bychom mohli uložit výsledek z kroku 1 do buňky sám, řekněme B6, pak napsat jednoduchější vzorec = LEVÝ (A3, B6-1). Ale to využívá jednu buňku pro přerušovaný krok.

Nezapomeňte na pozici čárky nebo jej znovu vypočítat.
Vypočítejte délku řetězce.
Počítat znaky od konce řetězce na čárku.

Vezměte počet znaků z kroku 3 a odečtěte jeden, abyste vynechali čárku a mezeru.

Udělejte to krok za krokem.

Zhora je to = FIND (",", A3 ")
Délka řetězce je = LEN (A3)
Budete muset použít několik matematických čísel, abyste zjistili počet znaků: LEN (A3) - FIND (",", A3) - 1
Pravá strana řetězce, kterou chceme, je = RIGHT (A3, LEN (A3)) - FIND ("," A3)

Tabulka by nyní měla vypadat podobně jako snímek obrazovky níže. Zkopírovali jsme vzorky jako text do dolní části tabulky, abychom usnadnili čtení a prohlížení.

Ten byl trochu obtížný, ale stačí napsat tyhle vzorce jednou.

Další informace ...

To končí dnešní hodinou. Měli byste mít nyní poměrně pevné porozumění formám a funkcím, řádkům a sloupcům a způsobu, jakým to vše může být využito prostřednictvím několika konkrétních příkladů.

Další informace naleznete v lekci 3, budeme diskutovat o odkazu na buňku a formátování, stejně jako o přesunutí a kopírování vzorců, takže nemusíte přepisovat každý vzorec znovu a znovu!