Definovanie a vytváranie vzorca

V tejto lekcii vám predstavíme základné pravidlá pre vytváranie vzorcov a používanie funkcií. Cítime jeden z najlepších spôsobov, ako sa naučiť, je prostredníctvom praxe, a preto poskytneme niekoľko príkladov a podrobne ich vysvetlíme. Témy, ktoré pokryjeme, zahŕňajú:

ŠKOLSKÁ NAVIGÁCIA

Prečo potrebujete vzorce a funkcie?
Definovanie a vytváranie vzorca
Relatívna a absolútna bunková referencia a formátovanie
Užitočné funkcie, ktoré by ste mali vedieť
Vyhľadávanie, grafy, štatistiky a kontingenčné tabuľky

riadkov a stĺpcov
Príklad matematickej funkcie: SUM ()
prevádzkovatelia
priorita operátora
napríklad finančná funkcia: PMT (), úverová platba
pomocou funkcie "reťazec" ("string" je skratka pre "reťazec textu") vo vnútri vzorca a funkcie hniezdenia

Vzorce sú zmesou "funkcií", "operátorov" a "operandov". Predtým, ako napíšeme niekoľko vzorcov, potrebujeme vytvoriť funkciu, ale skôr než budeme môcť vytvoriť funkciu, musíme najprv pochopiť riadkovú a stĺpcovú notáciu.

Riadky a stĺpce

Ak chcete pochopiť, ako písať vzorce a funkcie, potrebujete vedieť o riadkoch a stĺpcoch.

Riadky prebiehajú vodorovne a stĺpce prebiehajú vertikálne. Pamätajte si, čo je to, čo si myslíte o stĺpci, ktorý drží strop - stĺpce idú hore a smerom doľava doprava.

Stĺpce sú označené písmenami; riadkov číslami. Prvá bunka v tabuľke je A1, čo znamená stĺpec A, riadok 1. Stĺpce sú označené ako A až Z. Keď sa abeceda rozbehne, Excel umiestni ďalšie písmeno dopredu: AA, AB, AC ... AZ, BA, BC, BC, atď..

Príklad: Suma funkcie ()

Teraz už ukážeme, ako používať funkciu.

Funkcie používate tak, že ich zadáte priamo alebo pomocou sprievodcu funkciami. Sprievodca funkciami sa otvorí, keď buď vyberiete funkciu z ponuky "Formuláre" z "Knižnice funkcií". V opačnom prípade môžete zadať = v bunke a praktická rozbaľovacia ponuka vám umožní vybrať funkciu.

Sprievodca vám povie, aké argumenty musíte poskytnúť pre každú funkciu. Poskytuje tiež odkaz na online pokyny, ak potrebujete pomôcť pochopiť, čo funkciu robí a ako ju používať. Napríklad, ak zadáte hodnotu = sumu do bunky, in-line sprievodca vám ukáže, aké argumenty sú potrebné pre funkciu SUM.

Keď zadáte niektorú z funkcií, sprievodca je priamo alebo priamo na prstoch. Keď vyberiete funkciu z ponuky "Formuláre", sprievodca je vyskakovací okienko. Tu je pop-up sprievodca pre funkciu SUM ().

Pre našu prvú funkciu použite SUM (), ktorá pridáva zoznam čísel.

Predpokladajme, že táto tabuľka obsahuje plány na rozpočet na dovolenku vašej rodiny:

Na výpočet celkových nákladov by ste mohli písať = b2 + b3 + b4 + b5, ale je ľahšie použiť funkciu SUM ().

V programe Excel vyhľadajte symbol at v ľavom hornom rohu obrazovky programu Excel, aby ste našli tlačidlo AutoSum (matematici používajú grécke písmeno Σ pre pridanie série čísel).

Ak je kurzor pod číslom rodinného rozpočtu, program Excel je dosť chytrý na to, aby vedel, že chcete zhrnúť zoznam čísiel, nad ktorými ste umiestnili kurzor, a tak zvýrazní čísla.

Stlačte tlačidlo "enter", ak chcete prijať rozsah zvolený v programe Excel, alebo pomocou kurzorov zmeňte, ktoré bunky sú vybraté.

Ak sa pozriete na to, čo aplikácia Excel vložila do tabuľky, môžete vidieť, že táto funkcia bola napísaná:

V tomto vzore Excel sumuje čísla od B2 do B9. Upozorňujeme, že sme opustili nejaký priestor pod riadkom 5, aby ste mohli zvýšiť rozpočet na rodinnú dovolenku - náklady sa určite zvýšia, pretože zoznam detí, čo chcú robiť a kde chcú ísť, rastie dlhšie!

Funkcie matematiky nefungujú s písmenami, takže ak zadáte písmená do stĺpca, výsledok sa zobrazí ako "#NAME?", Ako je uvedené nižšie.

#NÁZOV? znamená, že existuje nejaký druh chyby. Mohlo by to byť niekoľko vecí vrátane:

zlý odkaz na bunku
pomocou písmen v matematických funkciách
vynechanie požadovaných argumentov
nesprávne meno funkcie pravopisu
nelegálne matematické operácie, ako je delenie 0

Najjednoduchší spôsob výberu argumentov vo výpočte je použitie myši. Môžete pridať alebo odstrániť zo zoznamu argumentov na funkciu rozšírením alebo zmenšovaním krabice, ktorú aplikácia Excel kreslí pri pohybe myši alebo kliknutím na inú bunku.

Klikli sme na horný okraj námestia, ktorý nakreslila spoločnosť Excel, aby sa z rozpočtu dostali "letenky". Môžete vidieť symbol krížových vlasov, ktorý môžete nakresliť, aby sa zvolený rozsah zväčšil alebo zmenšil.

Stlačte "enter" na potvrdenie výsledkov.

Operátori výpočtu

Existujú dva typy operátorov: matematika a porovnanie.

Matematický operátor	definícia
+	pridanie
-	odčítanie alebo negovanie, napríklad 6 * -1 = -6
*	násobenie
/	delenie
%	percento
^	exponent, napr. 2⁴= 2 ^ 4 = 2 * 2 * 2 * 2 = 16

Existujú aj iní operátori, ktorí nesúvisia s matematikou, ako je "&", čo znamená spojiť dva konce reťazca (pripojiť end-to-end). Napríklad, = "Excel" & "je Fun" sa rovná "Excel je zábava".

Teraz sa pozrieme na porovnávacích operátorov.

Operátor porovnania	definícia
=	sa rovná napríklad 2 = 4 alebo "b" = "b"
>	väčší ako napr. 4> 2 alebo "b"> "a"
<	menej ako napríklad 2 < 4 or “a” < “b”
> =	väčší alebo rovný - iným spôsobom, ako myslieť na to, je> = znamená buď > alebo =.
<=	menší alebo rovný.
	nie je rovné napríklad číslu 46

Ako vidíte vyššie, porovnávatelia pracujú s číslami a textom.

Ak zadáte hodnotu "a"> "b" do bunky, bude to znamenať "FALSE", pretože "a" nie je väčšie ako "b." "B" po abecede "a" > "B" alebo "B"> "a."

Prednosť objednávateľa operátora

Prednosť objednávky je myšlienka z matematiky. Program Excel musí dodržiavať rovnaké pravidlá ako matematika. Táto téma je oveľa komplikovanejšia, takže si dych a poďme sa ponoriť.

Prednosť objednávky znamená poradie, v ktorom počítač vypočíta odpoveď. Ako sme vysvetlili v Lekcii 1, oblasť kruhu je πr², ktorý je rovnaký ako π * r * r. to je nie (Πr)².

Takže musíte pochopiť prioritu objednávky pri písaní vzorca.

Vo všeobecnosti môžete povedať toto:

Aplikácia Excel najprv hodnotí položky v zátvorkách, ktoré pracujú dovnútra.
Potom používa pravidlá prednosti objednávky matematiky.
Keď majú dve položky rovnakú prednosť, program Excel funguje zľava doprava.

Prednosť matematických operátorov je uvedená nižšie, v zostupnom poradí.

(a)	Keď sa používajú zátvorky, prepisujú sa normálnym pravidlám priority. To znamená, že program Excel vykoná tento výpočet ako prvý. Vysvetľujeme to nižšie.
-	Negácia, napr. -1. To je rovnaké ako násobenie čísla o -1. -4 = 4 * (-1)
%	Percento znamená násobenie o 100. Napr. 0,003 = 0,3%.
^	Exponent, napríklad 10 ^ 2 = 100
* a /	Násobiť a rozdeliť. Ako môžu mať dvaja operátori rovnakú prioritu? Znamená to len to, že ak má vzorec ešte dvoch operátorov s rovnakou prednosťou, výpočet sa vykoná zľava doprava.
+ a -	Pridanie a odčítanie.

Existujú ďalšie pravidlá týkajúce sa reťazcov a referenčných operátorov. Momentálne sa budeme držať toho, čo sme práve pokryli. Teraz sa pozrime na niektoré príklady.

Príklad: Výpočet plochy kruhu

Oblasť kruhu je= PI () * polomer ^ 2.

Pri pohľade na vyššie uvedenú tabuľku vidíme, že exponenty prichádzajú pred násobením. Počítač najprv vypočíta polomer ^ 2 a potom sa násobí tak, že výsledok Pi.

Príklad: Výpočet zvýšenia platu

Povedzme, že váš šéf rozhodne, že robíte skvelú prácu a on alebo ona vám dá 10% zvýšenie! Ako by ste vypočítali váš nový plat??

Po prvé, nezabudnite, že násobenie prichádza pred pridaním.

Je to = mzda + mzda * 10% alebo je to = mzda + (mzda * 10%)?

Predpokladajme, že váš plat je 100 USD. S 10% -ným zvýšením bude váš nový plat:

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

Môžete tiež napísať takto:

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

V druhom prípade je jasné poradie priority pomocou zátvoriek. Nezabudnite, že pred každou ďalšou operáciou sa hodnotí zátvorky.

Mimochodom, jednoduchší spôsob zápisu je: = plat * 110%

Zuby môžu byť navzájom vnorené. Takže, keď píšeme (3 + (4 * 2)), práca zvnútra von, najprv vypočíta 4 * 2 = 8, potom pridajte 3 + 8 a získajte 11.

Niekoľko ďalších príkladov

Tu je ďalší príklad: = 4 * 3 / 2. Čo je odpoveď?

Z pravidiel uvedených v tabuľke vidíme, že * a / majú rovnakú prednosť. Takže Excel pracuje zľava doprava, najprv 4 * 3 = 12, potom rozdeľuje to o 2 a získa 6.

Znova by ste to mohli urobiť explicitne písaním = (4 * 3) / 2

A čo = 4 + 3 * 2?

Počítač vidí operátory * a +. Takže podľa pravidiel prednosti (násobenie prichádza pred pridaním) vypočítava najprv 3 * 2 = 6, potom pridá 4 a získa 10.

Ak chcete zmeniť poradie priority, napíšete = (4 + 3) * 2 = 14.

A čo toto = -1 ^ 3?

Potom odpoveď je -3, pretože počítač vypočítaný = (-1) ^ 3 = -1 * -1 * -1 = -1.

Pamätajte, že záporné časy negatívne sú pozitívne a záporné časy pozitívne sú negatívne. Môžete to vidieť takto (-1 * -1) * -1 = 1 * -1 = -1.

Takže existuje niekoľko príkladov matematického poradia a priority, dúfame, že vám pomôže objasniť niekoľko vecí o tom, ako Excel vykonáva výpočty (a to je pravdepodobne dosť matematiky, aby vydržal celý život pre niektorých z vás).

Príklad: Platba úveru na funkciu (PMT)

Pozrime sa na príklad na výpočet úverovej platby.

Začnite vytvorením nového pracovného hárka.

Formátujte čísla znakmi dolára a používajte nulové desatinné miesta, pretože momentálne nemáme záujem o centy, pretože vôbec nezáleží na tom, ak hovoríte o dolároch (v ďalšej kapitole skúmame podrobnejšie formátovanie čísel). Napríklad, ak chcete formátovať úrokovú sadzbu, kliknite pravým tlačidlom myši na bunku a kliknite na "formáty buniek". Zvoľte percento a použite dve desatinné miesta.

Podobne naformátujte ostatné bunky pre "menu" namiesto percenta a vyberte "číslo" pre termín úveru.

Teraz máme:

Pridajte funkciu SUM () na "celkové" mesačné výdavky.

Poznámka: hypotéka bunka nie je zahrnutá do celku. Program Excel nevie, že chcete zahrnúť toto číslo, pretože tam nie je žiadna hodnota. Takže dávajte pozor, aby ste rozšírili funkciu SUM () na vrchol buď pomocou kurzorov alebo zadaním E2, kde sa hovorí, že E3 obsahuje hypotéku v súčte.

Vložte kurzor do platobnej bunky (B4).

V ponuke Vzorce vyberte rozbaľovaciu položku "Finančné" a potom vyberte funkciu PMT. Spustí sa sprievodca:

Použite kurzor na výber "sadzby", "nper" (termín úveru), "Pv" ("súčasná hodnota" alebo výška úveru). Všimnite si, že musíte rozdeliť úrokovú sadzbu o 12, pretože úroky sa vypočítavajú mesačne. Tiež musíte vynásobiť pôžičku v rokoch 12 rokov, aby ste získali termín úveru v mesiacoch. Stlačením tlačidla "OK" uložte výsledok do tabuľky.

Všimnite si, že platba je uvedená ako záporné číslo: -1013.37062. Ak chcete, aby bol pozitívny a pridajte ho do mesačných výdavkov, ukážte na hypotéku (E2). Zadajte "= -" a potom použite kurzor na zobrazenie platobného poľa. Výsledný vzorec je = -B4.

Tabuľkový procesor takto vyzerá takto:

Vaše mesačné výdavky sú 1,863 dolárov - Ouch!

Príklad: Textová funkcia

Tu ukážeme, ako používať funkcie vnútri vzorca a textové funkcie.

Predpokladajme, že máte zoznam študentov, ako je uvedené nižšie. Prvé meno a priezvisko je v jednom poli oddelené čiarkou. Musíme vložiť posledné a pevné názvy do samostatných buniek. Ako to urobíme??

Na riešenie tohto problému je potrebné použiť algoritmus - t.j. krok za krokom na to.

Pozrite sa napríklad na "Washington, George". Postup rozdelenia na dve slová by bol:

Vypočítajte dĺžku reťazca.
Nájdite polohu čiarky (to ukazuje, kde končí jedno slovo a začína druhé slovo).
Skopírujte ľavú stranu reťazca až do čiarky.
Skopírujte pravú stranu reťazca od čiarky na koniec.

Poďme diskutovať, ako to urobiť s "George Washington" krok za krokom v programe Excel.

Vypočítajte dĺžku reťazca s funkciou = LEN (A3) - výsledkom je 18.
Teraz nájdite polohu čiarky zadaním tejto funkcie = FIND (",", A3 ") - výsledok je 11.
Teraz vezmite ľavú stranu reťazca až do čiarky a vytvorte tento vnorený vzorec pomocou výsledku z kroku 1: = LEVÝ (A3, FIND (",", A3) -1). Poznámka: Musíme odčítať 1 z dĺžky, pretože FIND dáva polohu čiarky.

Tu je to, čo všetko vyzerá, keď sú všetky funkcie spojené do vzorca. V bunke B3 môžete vidieť, že tento vzorec obsahuje všetky informácie z bunky A3 a do nej vložia údaje "Washington".

Takže máme "Washington", teraz musíme dostať "Georgea". Ako to urobíme?

Všimnite si, že by sme mohli uložiť výsledok z kroku 1 do bunky samy, povedzme, B6, potom napíšte jednoduchší vzorec = LEVÝ (A3, B6-1). Ale to používa jednu bunku pre prerušovaný krok.

Pamätajte na polohu čiarky alebo ju znova vypočítajte.
Vypočítajte dĺžku reťazca.
Spočítajte znaky od konca reťazca do čiarky.

Zoberte počet znakov z kroku 3 a odčítajte jeden, aby ste vynechali čiarku a medzeru.

Urobme to krok za krokom.

Zhora je to = FIND (",", A3 ")
Dĺžka reťazca je = LEN (A3)
Ak chcete zistiť počet znakov, ktoré chcete vykonať, musíte použiť niektoré matematické vzorce: = LEN (A3) - FIND (",", A3) - 1
Pravá strana reťazca, ktorú chceme, je = RIGHT (A3, LEN (A3)) - FIND ("," A3)

Tabuľka by mala teraz vyzerať podobne ako na obrázku nižšie. Formuláre sme skopírovali ako text do spodnej časti tabuľky, aby sme ich mohli ľahšie čítať a prezerať.

Tá bola trochu ťažká, ale stačí iba raz napísať tieto vzorce.

Nasleduje…

Toto končí dnešnú lekciu. Mali by ste mať dosť pevné porozumenie o formách a funkciách, riadkoch a stĺpcoch a o tom, ako sa dá všetko použiť prostredníctvom niekoľkých konkrétnych príkladov.

Po ďalšom príprave v časti Lekcia 3 budeme diskutovať o referencii a formátovaní buniek, ako aj o pohybe a kopírovaní vzorcov, takže nemusíte opakovať každý vzorec znova a znova!