V tejto lekcii budeme analyzovať, ako vytvoriť komplikovaný vzorec v programe Excel a tiež zvážiť typické chybyz nováčikov užívateľov nepozornosťou. Ak ste nedávno pracovali v programe Excel, odporúčame vám najprv obrátiť sa na lekciu, kde sme diskutovali o vytvorení jednoduchých vzorcov.

Ako vytvoriť komplexný vzorec v Excel

V nasledujúcom príklade budeme preukázať, ako Excel vypočíta komplexné vzorce, spoliehať sa na postup vykonávania operácií. V tomto príklade chceme vypočítať hodnotu dane z predaja pre služby výživy. Aby ste to implementovali, zapíšte nasledujúci výraz do bunky D4: \u003d (D2 + D3) * 0,075. Tento vzorec preloží náklady na všetky pozície na účte a potom znásobuje daň z predaja 7,5% (zaznamenané ako 0,075).

Excel dodržiava poradie akcie a najprv zloží hodnoty v zátvorkách: (44.85+39.90)=$84.75 . Potom vynásobí toto číslo pre daňovú sadzbu: $84.75*0.075 . Výsledkom výpočtovej techniky ukazuje, že daň z predaja bude $6.36 .

Je mimoriadne dôležité zaviesť komplexné vzorce so správnym poradím akcie. V opačnom prípade môžu byť výpočty Excel nepresné. V našom prípade, v neprítomnosti zátvoriek, primárne sa vykonáva násobenie a výsledok bude nesprávny. Zátvorky sú najlepší spôsob, ako Definície postupu pre výpočty v programe Excel.

Vytvorenie komplexných vzorcov pomocou postupu

V nasledujúcom príklade použijeme odkazy v spojení s kvantitatívnymi údajmi na vytvorenie komplexného vzorca, ktorý vypočíta úplné náklady na prenos napájania. Vzorec vypočíta náklady na každú položku menu a potom zložte všetky hodnoty dohromady.

Držiak môžete pridať do ľubovoľného vzoru na zjednodušenie jeho vnímania. Napriek tomu, že v tomto príklade to nezmení výsledok výpočtov, môžeme stále robiť násobenie v zátvorkách. Toto objasňujeme, že sa pred pridaním vykonáva.

Excel nie vždy varuje o chybách vo vzorci, takže musíte samostatne skontrolovať všetky svoje vzorce. Ak chcete zistiť, ako sa dá urobiť, naučte sa overenie výučby.

Ak ste predtým nepracovali s Excel predtým, čoskoro zistíte, že nie je len tabuľka pre zadávanie čísel. Samozrejme, v programe Excel si môžete jednoducho uvažovať o množstvách v riadkoch a stĺpci, ale môžete tiež vypočítať hypotekárne platby, riešiť matematické a inžinierske úlohy a nájsť najpriaznivejšie možnosti v závislosti od špecifikovaných premenných.

V programe Excel sa to všetko realizuje pomocou vzorcov v bunkách. Podľa týchto vzorcov sa vykonajú výpočty a iné akcie s údajmi na hárku. Vzorec vždy začína znamením rovnosti (\u003d), potom, čo môžete zadať čísla, matematické operátory (napríklad značky + a - pre pridanie a odčítanie) a vložené funkcie Excel, významne rozširujúce možnosti vzorcov.

Nižšie je príkladom vzorca, ktorý znásobuje 2 až 3 a pridáva sa do výsledku 5, aby sa získalo 11.

Nižšie sú uvedené príklady vzorcov, ktoré môžu byť použité na listoch.

Časti vzorca Excel

Vzorec môže tiež obsahovať jeden alebo viac takých prvkov ako funkcie, spojenie, operátori a konštanty.

Postup vykonávania činností vo vzorcoch

V niektorých prípadoch môže postup na výpočet ovplyvniť návratovú hodnotu, preto je dôležité získať požadované výsledky na pochopenie štandardného postupu pre výpočty a vedieť, ako ho zmeniť.

Pomocou funkcií a vnorených funkcií v Excel vzorce

Funkcie sú vopred určené vzorce, ktoré vykonávajú výpočty nad určenými hodnotami - argumenty - v určitom poradí alebo osobitným systémom. Použitie funkcií možno vykonať jednoduché aj zložité výpočty. Všetko excel Funkcie Na karte Formula môžete vidieť.

Použitie odkazov v excel vzorcov

Odkaz označuje bunku alebo rozsah listových buniek a prehľadov Microsoft Excel.kde sa nachádza potrebná hodnota vzorca alebo údajov. Používanie odkazov môžete použiť v rovnakom formáte údajov v rôznych častiach listu, ako aj na použitie hodnoty jednej bunky v niekoľkých vzorcoch. Okrem toho môžete určiť odkazy na bunky rôznych listov jednej knihy alebo na bunky z iných kníh. Odkazy na bunky iných kníh sa nazývajú pripojenia alebo externé referencie.

Použite mená v excel vzorcov

Na označenie buniek, bunkových rozsahov, vzorcov, konštanty a tabuľky programu Excel môžete vytvoriť určité mená. Názov je významné krátke označenie, ktoré vysvetľuje účel odkazu na bunku, konštanty, vzorce alebo tabuľky, pretože nie je ľahké pochopiť ich podstatu z jedného pohľadu. Nižšie sú uvedené príklady mien a ukázali, ako ich použitie zjednodušuje pochopenie vzorcov.

Príklad 1.

Príklad 2.

Skopírujte vzorové údaje z nižšie uvedenej tabuľky a vložte ju do bunky A1. lesklý výbor. Ak chcete zobraziť výsledky vzorca, vyberte ich a stlačte kláves F2 a potom tlačidlo ENTER. Okrem toho môžete nastaviť šírku stĺpcov v súlade s údajmi obsiahnutými v nich.

Poznámka: Vo vzorcoch v stĺpcoch C a D, je špecifický názov "predaja" nahradený odkazom na rozsah A9: A13 a názov "Informačné CLUS" sa nahrádza rozsahom A9: B13. Ak kniha nie je tieto mená, vzorce v D2: D3 vráti chybnú stránku #?.

Typ príkladu	Príklad, v ktorom sa názvy nepoužívajú.	Príklad, v ktorom sa používajú názvy.	Vzorec a výsledok pomocou mien
	"\u003d SUMS (A9: A13)	"\u003d Sumy (predaj)	Sumy (predaj)
	"\u003d Text (PRD (max (A9: 13), A9: B13,2, FALSE)," DD / MM / RRRRYY ")	"\u003d Text (PRD (Max (Sales), predaj informácií, 2, false)," dd.mm..yyyy ")	Text (max (predaj), predaj informácií, 2, false), "dd.mm.yyyy")
	Dátum predaja

Ďalšie informácie nájdete v časti Definícia a použitie názvov vo vzorcoch.

Použitie masívneho vzorca a konštánt poľa v programe Excel

Vzorec Masív môže vykonať niekoľko výpočtov a potom vrátiť jednu hodnotu alebo skupinu hodnôt. Formula Masív manipuluje niekoľko súborov hodnôt zvaných argumentov poľa. Každý array by mal obsahovať rovnaký počet riadkov a stĺpcov. Vzorec poľa je vytvorený rovnakým spôsobom ako iné vzorce, s týmto rozdielom, že na zadanie takéhoto vzorca sa používajú vstupy CTRL + SHIFT + SHIFT +. Niektoré vstavané funkcie sú polerové vzorce a získať správne výsledky, mali by byť zadané ako polia.

Namiesto odkazov je možné použiť masívne konštanty, ak nie je potrebné zadať každej trvalej hodnote v oddelená bunka Na hárku.

Používanie vzorec poľa na výpočet jednej alebo viacerých hodnôt

Poznámka: Pri vstupe do vzorca sa pole Excel automaticky uzatvára v kučeravých zátvorkách. Keď sa pokúsite manuálne zadať medzery programu Excel vzor vzorec ako text.

Použite konštanty poľa

V obvyklom vzorci môžete zadať odkaz na bunku s hodnotou alebo samotnou hodnotou, nazývanou aj konštantu. Podobne, v riešení vzorca môžete zadať odkaz na pole alebo pole hodnôt obsiahnutých v bunkách (niekedy sa nazýva konštanta poľa). Formulové vzorce majú konštanty, ako aj iné vzorce, ale konštanty poľa musia byť zadané v konkrétnom formáte.

Konštanty poľa môžu obsahovať čísla, text, logické hodnoty, ako napríklad pravdy alebo nepravdivé alebo hodnoty chýb, ako napríklad # N / D. V jednom poľa môžu byť prítomné hodnoty rôznych typov, napríklad (1,3,4; pravda, leží, pravda). Čísla v konštántoch poľa môžu byť celé číslo, desatinné alebo majú exponenciálny formát. Text musí byť uzavretý v dvojitých úvodzovkách, ako napríklad "utorok".

Pri formátovaní konštanty poľa sa uistite, že sa požiadavky vykonávajú nižšie.

Konštanty sú uzatvorené v kučeravých zátvorkách ( { } ).

Stĺpce sú oddelené čiarkami ( , ). Napríklad, aby ste predstavili hodnoty 10, 20, 30 a 40, zadajte (10,20,30,40). Táto masívna konštanta je rozmerová matica 1 až 4 a zodpovedá odkazu na jeden riadok a štyri stĺpce.

Hodnoty buniek z rôznych línií sú oddelené bodkočiarkou ( ; ). Napríklad, prezentovať hodnoty 10, 20, 30, 40 a 50, 60, 70, 80, ktoré sú umiestnené v každej ďalšom bunkách, môžete vytvoriť masív konštantu s rozmerom 2 až 4: (10,20 , 30,40; 50, 60,70,80).

Odstránenie vzorca

Spolu so vzorcom sú výsledky jeho výpočtu odstránené. Je však možné odstrániť samotný vzorec, takže výsledok jeho výpočtu vo forme hodnoty v bunke.

Pomocou hrubého čreva ( : ) Odkazy na prvé a posledné bunky v rozsahu sú rozdelené. Napríklad: A1: A5..

Povinné argumenty sú uvedené

Funkcia môže mať povinné a voliteľné argumenty (druhé sú uvedené v štvorcových zátvorkách syntaxe). Musíte zadať všetky požadované argumenty. Skúste tiež nevstúpiť príliš veľa argumentov.

Vo vzorci nie viac ako 64 úrovní funkcií hniezdenia

Úrovne hniezdenia nemôžu byť viac ako 64.

Názvy kníh a listov sú uzavreté v jednoduchých úvodzovkách

Ak názvy listov alebo kníh, na bunky alebo hodnoty, v ktorých odkazujete, obsahujú nezáväzné znaky, musia byť uzavreté v jednoduchých úvodzovkách ( " ).

Uvedená cesta k externým knihám

Čísla zavedené bez formátovania

Pri zadávaní čísel vo vzorci nie je možné uviesť príznaky dolárov, pretože sa používajú na označenie absolútnych odkazov. Napríklad namiesto hodnoty $1000 Musíte zadať 1000 .

DÔLEŽITÉ: Vypočítané výsledky vzorcov a niektorých funkcií programu Excel sa môžu mierne líšiť na počítačoch so systémom Windows s Architektúrou X86 alebo X86-64 a počítačmi so systémom Windows RT s architektúrou ARM.

V tomto príspevku, ako vo väčšine článkov blogu, tabuľky Excel I zavolám rozsahy buniek, na ktoré bol príkaz aplikovaný Vložte - tabuľky - tabuľka . Prečítajte si viac o vymenovaní a výhodách takéhoto postupu, môžete si prečítať. Ak ste si nečítali tento príspevok, bude pre vás ťažšie učiť informácie tu načrtnutý.

Teraz sa zameriame podrobne o tom, ako fungujú vzorce v tabuľkách.

Rad výsledkov

Na výpočet konečných údajov v tabuľkách existuje reťazec výsledkov. Ak chcete zobraziť, vyberte ľubovoľnú bunku vo vnútri tabuľky a nainštalujte nádrž na pásku Designer - Parametre štýlu tabuľky - Street reťazcov . Tabuľka sa zobrazí pod tabuľkou. Zvýraznite akúkoľvek bunku na výpočet stĺpca v tomto riadku. V rozbaľovacom zozname vyberte vzorec, ktorý program použije na nesprávny výpočet výsledkov. Napríklad množstvo, minimálne, priemer atď. Kliknite na "iné funkcie ..." na výber inej funkcie.

Riadok výsledkov v programe Excel

Môžete tiež napísať vzorcu sami, aby ste vypočítali výsledok, je napísaný rovnakým spôsobom ako obvyklé vzorce, len namiesto bunkových adries, ich mená sa zobrazujú z hlavičiek tabuľky. Odkazy na bunky označujú myš, samotný program nahradí ich mená.

Pamätajte, že výslovný reťazec spracováva tieto bunky tabuliek, ktoré sú viditeľné na obrazovke. Ak ste použili filter pre niektoré hodnoty tabuľky, výsledky budú prepočítané len pre vybrané údaje.

Nahrávanie vzorcov v stĺpcoch

Ak potrebujete vykonávať výpočty v stĺpcoch tabuľky, je to ešte jednoduchšie ako v obvyklom rozsahu buniek. Vytvorte nový stĺpec v tabuľke. Nastavte kurzor na ľubovoľnú bunku tohto stĺpca a zadajte vzorca. Použite spôsob indikácie odkazov myši na bunky. Program sa namiesto toho zobrazí vo vzorcoch stĺpcov, uľahčí vnímanie vzorca. Kliknite na položku Zadajte a prekvapiť! Excel nielen vypočíta výsledok, ale tiež skopíruje vzorec do všetkých kolónových buniek. Nemusíte ani kopírovať vzorce ručne!

Formuláry v tabuľkách programu Excel

Táto funkcia nie je vždy užitočná, takže môže byť zakázaná. Ak to chcete urobiť, pri vkladaní vzorca kliknite na inteligentnú značku a vyberte položku " Nevytvárajte vypočítané stĺpce automaticky "

Nastavenie Excel tabuľkové vzorce

Odvolanie na tabuľku podľa mena

Máte výzvu na prístup k obsahu tabuľky podľa mena. Ak chcete nastaviť názov tabuľky, zadajte ho v poli na pásku: Designer - Vlastnosti - Názov tabuľky .

Teraz, aby ste vykonali niektoré operácie s údajmi údajov, môžete ho kontaktovať podľa mena. Napríklad náš stôl sa nazýva "ĽUDIA":

• Vzorec \u003d sumy (ľudia) sa pokúsia zhrnúť všetky bunky tabuľky
• Vzorec \u003d súčet (ľudia [sum_name]) Určite množstvo údajov v stĺpci "Sale_ Predaj" tabuľky "Ľudia"
• \u003d Ľudia [@ SUM_NAME] / 100 - Rozdeľte 100 predaja z tej istej čiary

Ak sú čísla neisté, bunkové reťazce so vzorcom a bunkou v tabuľke, Excel zobrazia tradičný odkaz so súradnicami.

Použitie vzorcov v tabuľkách je dokonale automatizované, intuitívne. Hoci mnohí na prvom mieste bude ťažké ich používať, po niekoľkých dňoch budete zvyknúť si a oceníte všetky výhody, ktoré dostanú tabuľky Excel.

Ďakujeme za čítanie tohto príspevku. A v nasledujúcom článku to poviem. Uvidíme sa na stránkach blogu.

Ako viete, Editor Excel Stolný úrad je pôvodne určený na prácu matematických, algebraických a iných výpočtov, pre ktoré sa používa vstup špeciálnych vzorcov. Avšak vo svetle toho, ako registrácia vzorec v programe Excel, je potrebné vziať do úvahy, že väčšina operácií v úvode je radikálne odlišná od všetkého, čo je obvyklé používať v bežnom živote. To znamená, že samotné vzorce majú mierne odlišný druhu a používať na všetkých tých operátorov, ktorí sa používajú pri nahrávaní štandardných výpočtov.

Zvážte otázku, ako zaregistrovať vzorec v programe Excel, na niekoľkých jednoduchých príkladoch, bez toho, aby ovplyvnili komplexné operácie, pochopiť, ktoré je potrebné študovať program hlboko. Ale aj predbežné vedomosti poskytnú akémukoľvek používateľovi pochopenie základných princípov použitia vzorcov pre rôzne prípady.

Ako zaregistrovať vzorec v Excel: Počiatočné koncepty

Zadanie hodnôt vzorca v programe je teda o niečo odlišné od štandardných operácií používaných znakov a používaných operátorov. Pri riešení problému, ako zaregistrovať vzorec v programe Excel, je potrebné odpredať zo základných pojmov, ktoré sa používajú v takmer všetkých počítačových systémoch.

Faktom je, že stroj zavádza kombináciu ako "2 x 2" alebo celkový komponent držiaka ("2 + 2) 5") nerozumie. To umožňuje použitie niekoľkých typov znakov, ktoré sú uvedené v tabuľke nižšie, nepočítajúc logických operátorov.

V tomto prípade sa priorita vykonávania operácií začína v stupni a končí pridaním a odčítaním. Okrem toho, napriek tomu, že Excel môže byť použitý ako pravidelná kalkulačka, spravidla je potrebné špecifikovať čísla buniek alebo ich rozsah. Je samozrejmé, že formát dát v akomkoľvek takomto bunke musí byť nastavený na zodpovedajúce (aspoň numerické).

Množstvo a rozdiel

Ako predpísať súčet sumy alebo rozdielu v programe Excel? Začnime sa s najjednoduchším, keď chcete vypočítať sumu. V reťazci vzorca (navyše pre všetky operácie) sa najprv zavádza znak rovnosti, po ktorom požadovaný vzorec sa hodí. V prípade konvenčnej kalkulačky môžete určiť pre inštalovanú bunku "\u003d 2 + 2".

Ak sa summation uskutočňuje pre hodnoty zadané priamo v iných bunkách (napríklad A1 a A2), vzorec získava formu "\u003d A1 + A2". Pre použitie ďalších operátorov môže byť množstvo v zátvorkách. Pre rozdiel - to isté, len s mínus namiesto plus.

Keď potrebujete špecifikovať čísla buniek alebo ich rozsah, možno použiť špeciálny tím sumy (v ruskej verzii "súčet" v angličtine - súčet). Pri špecifikácii niekoľkých buniek to vyzerá takto: "\u003d množstvá (A1; A2)", pre rozsah - "sumy (A1: A10)", za predpokladu, že je potrebné vypočítať sumu všetkých čísel v bunkách z najprv do desiaty. V zásade, ak nainštalujete aktívnu bunku, ktorá je ihneď po poslednej v stĺpci so zdrojovými hodnotami, nie je možné zadať vzorec, ale jednoducho kliknite na tlačidlo automatického zhrnutia.

Násobenie, rozdelenie a cvičenie

Pozrime sa teraz, ako v programe Excel zaregistrovať vzorec multiplikácie alebo divízie. To isté je rovnaké ako keď je zavedená suma alebo rozdiel, len operátori sa líšia.

Pre produkt sa používa typ "\u003d A1 * A2" pre súkromné \u200b\u200b- "A1 / A2". Týmto spôsobom, presne tie isté príkazy, ktoré možno nájsť pomocou štandardnej kalkulačky Windows.

"^" Symbol sa aplikuje v rozsahu. Pre hodnotu v bunke A1, ktorá je napríklad potrebujete byť zvýšená na námestí, použije sa vzorca "\u003d A1 ^ 2".

Percentuálne výpočty

S percentom, ak nie je ovplyvnené komplexné výpočty, je to tiež jednoduché. Ako zaregistrovať percentuálny vzorec v programe Excel?

Stačí vstúpiť do vzorca formulára "\u003d A1 * 5%", po ktorom dostanete tieto päť percent hodnoty v príslušnej bunke.

Použitie vstupných vzorcov na základe výberu buniek

Ale toto všetko odkazuje na manuálnu úlohu alebo tzv. Priameho vstupného vzorca (priamy alebo priamy vstup). V skutočnosti je niekedy užitočné používať myš a kláves CTRL.

Keď je kľúč stlačiteľný kľúčom, môžete jednoducho vybrať požadované bunky, predklopujúc potrebný výpočet vo vzorci reťazec. Bunky budú pridané priamo do reťazca vzorca. Ale v závislosti od typu vzorca, niekedy budú musieť byť potrebné dať ručne zátvorky.

Absolútne, relatívne a zmiešané typy buniek

Samostatne stojí za zmienku, že niekoľko typov buniek môže byť použité v programe, nehovoriac o údajoch, ktoré obsahujú.

Absolútna bunka je nezmenená a označovaná ako $ A $ 1, príbuzný je odkaz na obvyklé miesto (A1), zmiešané - existuje kombinácia referencií a absolútnej a relatívnej bunky ($ A1 alebo A $ 1). Takéto formáty sa zvyčajne používajú pri vytváraní pri vytváraní údajov o rôznych listoch kníh alebo dokonca v rôznych súboroch, sa podieľajú na vzorcov.

Vzor

Poďme uvidíme, ako v programe Excel registrovať vzorec VLOOKUP. Táto technika vám umožňuje vložiť údaje z jedného rozsahu do druhého. V tomto prípade je metóda trochu podobná tomuto problému, ktorý sa používa pri riešení problému, ako v programe Excel zaregistrovať "stav" vzorec, ktorý používa znaky uvedené v tabuľke vyššie.

Vo všeobecnom zmysle sú takéto výpočty niečo podobné ako jednoduchý filter aplikovaný na stĺpce, keď sa vyžaduje, aby filtrovali len presné, a nie približné hodnoty.

V tomto uskutočnení najprv použite zdrojové (prvé) hodnoty tabuľky (prvé) tabuľky, v poli "Tabuľka", druhý rad s fixáciou obsahu (F4) je zadaný, potom je špecifikované číslo stĺpca a v Intervalové pole, hodnota "lož" je nastavená do skutočnosti, keď je potrebné získať filtrovanie len, a nie približné hodnoty. Takéto vzorce sa spravidla používajú viac v sklade alebo účtovníctve, pri nastavení niektorých špecializovaných softvérových produktov nie je možné.

Záver

Zostáva povedať, že nie všetky vzorce, ktoré môžu byť použité v Editor tabuľky Excel, boli opísané tu. To je, tak hovoriť, len AZA. V skutočnosti, ak ste kopať stále v trigonométri alebo výpočtoch logaritmov, matice alebo dokonca tenzorov rovníc, všetko vyzerá oveľa zložitejšie. Aby bolo možné študovať všetko, je potrebné dôkladne naplánovať sprievodcu samotným editorom. A toto nie je jasné, že v programe Excel na základe meniacich sa údajov môžete vytvoriť aj tie najjednoduchšie logické hry. Ako príklad, ten istý "had", ktorý pôvodne nemal žiadny vzťah k tabuľkovým editorom, ale bol reprodukovaný nadšencami jeho prípadu v programe Excel.

V zvyšku by sa malo jasne pochopiť, že štúdiom primitívnych vzorcov alebo dátových akcií, potom to bude možné ľahko učiť a zložitejšie výpočty, povedzme, s vytvorením krížových referencií s použitím rôznych scenárov alebo vb skriptov, atď. To všetko potrebuje čas, takže ak chcete preskúmať program a všetky svoje schopnosti na maximum, budete musieť potiť cez teoretickú časť.