Ako robiť výpočty v Exceli. Ako zobraziť kroky výpočtu vzorca

Za posledné desaťročie sa počítač stal nepostrádateľným nástrojom v účtovníctve. Zároveň je jeho uplatnenie rôznorodé. V prvom rade ide samozrejme o použitie účtovného programu. Dnes sa vyvinulo pomerne veľa softvéru, špecializovaného (1C, Info-Accountant, BEST atď.) aj univerzálneho, ako napríklad Microsoft Office. V práci a v každodennom živote musíte často robiť množstvo rôznych výpočtov, udržiavať viacriadkové tabuľky s číselnými a textovými informáciami, vykonávať najrôznejšie výpočty s údajmi a možnosti tlače. Na vyriešenie množstva ekonomických a finančných problémov je vhodné využiť početné možnosti tabuľkových procesorov. V tomto ohľade uvažujme o výpočtových funkciách MS Excel.
Vladimír ŠEROV, Ph.D., Oľga TITOVÁ

Zdroj: Časopis "Účtovník a počítač" č.4 2004

Ako každá iná tabuľka, aj MS Excel je určený predovšetkým na automatizáciu výpočtov, ktoré sa zvyčajne robia na kúsku papiera alebo pomocou kalkulačky. V praxi sa v odborných činnostiach stretávame s pomerne zložitými výpočtami. Preto si povieme viac o tom, ako nám Excel pomáha automatizovať ich vykonávanie.

Na označenie akejkoľvek akcie, ako je sčítanie, odčítanie atď., sa vo vzorcoch používajú operátory.

Všetci operátori sú rozdelení do niekoľkých skupín (pozri tabuľku).

OPERÁTOR VÝZNAM PRÍKLAD


ARITMETICKÉ OPERÁTORY
+ (znamienko plus)Doplnenie=A1+B2
- (znamienko mínus)Odčítanie Unary mínus=A1-B2 =-B2
/(lomka)divízie=A1/B2
*(hviezda)Násobenie= A1*B2
% (znak percenta)Percento=20%
^ (veko)Umocňovanie= 5^3 (5 až 3. mocnina)


POROVNÁVACIE OPERÁTORY
= Rovná sa=AK(A1=B2,"Áno","Nie")
> Viac=IF(A1>B2;A1;B2)
< Menej=IF(AKV2;B2;A1)
>= <= Väčšie alebo rovné Menšie než alebo rovné=IF(A1>=B2,A1,B2) =IF(AK=B2,B2,A1)
<> Nerovná sa=IF(A1<>B2;"Nerovná sa")


TEXTOVÝ OPERÁTOR
&(ampersand)Kombinovanie sekvencií znakov do jednej sekvencie znakov= "Hodnota bunky B2 je: "&B2


ADRESOVAŤ OPERÁTOROV
rozsah (dvojbodka)Vzťahuje sa na všetky bunky medzi a vrátane hraníc rozsahu=SUM(A1:B2)
Reťazenie (bodkočiarka)Odkaz na zlúčenie buniek rozsahu=SUM(A1:B2;SZ;D4:E5)
Priesečník (medzera)Odkaz na bunky spoločného rozsahu=CUMM(A1:B2C3D4:E5)

Aritmetické operátory sa používajú na reprezentáciu základných matematických operácií s číslami. Výsledkom aritmetickej operácie je vždy číslo. Porovnávacie operátory sa používajú na označenie operácií, ktoré porovnávajú dve čísla. Výsledkom porovnávacej operácie je logická hodnota TRUE alebo FALSE.

Excel používa na výpočty vzorce. Pomocou vzorcov môžete napríklad sčítať, násobiť a porovnávať údaje tabuľky, t. j. vzorce by sa mali použiť, keď potrebujete zadať (automaticky vypočítať) vypočítanú hodnotu do bunky hárka. Zadávanie vzorca začína symbolom „=“ (rovná sa). Práve tento znak odlišuje zadávanie vzorcov od zadávania textu alebo jednoduchej číselnej hodnoty.

Pri zadávaní vzorcov môžete použiť bežné číselné a textové hodnoty. Pripomíname, že číselné hodnoty môžu obsahovať iba čísla 0 až 9 a špeciálne znaky: (plus, mínus, lomka, zátvorky, bodka, čiarka, percentá a znaky dolára). Textové hodnoty môžu obsahovať ľubovoľné znaky. Treba poznamenať, že textové výrazy použité vo vzorcoch musia byť uzavreté v dvojitých úvodzovkách, napríklad „konštantná1“. Okrem toho môžete vo vzorcoch použiť odkazy na bunky (aj vo forme názvov) a množstvo funkcií, ktoré sú navzájom prepojené operátormi.

Odkazy sú adresy buniek alebo rozsahy buniek zahrnuté vo vzorci. Odkazy na bunky sa špecifikujú bežným spôsobom, teda v tvare A1, B1, C1. Napríklad, aby sme dostali súčet buniek A1 a A2 v bunke A3, stačí zadať vzorec =A1+A2 (obr. 1).

Pri zadávaní vzorca možno odkazy na bunky zadávať znak po znaku priamo z latinskej klávesnice, ale často je oveľa jednoduchšie ich určiť pomocou myši. Ak chcete napríklad zadať vzorec =A1+B2, musíte urobiť nasledovné:

Vyberte bunku, do ktorej chcete zadať vzorec;

Začnite zadávať vzorec stlačením klávesu „=“ (rovná sa);

Kliknite na bunku A1;

Zadajte symbol „+“;

Kliknite na bunku B2;

Dokončite zadávanie vzorca stlačením klávesu Enter.

Rozsah buniek predstavuje určitú obdĺžnikovú oblasť pracovného hárka a je jednoznačne určený adresami buniek umiestnených v opačných rohoch rozsahu. Tieto dve súradnice, oddelené „:“ (dvojbodkou), tvoria adresu rozsahu. Ak chcete napríklad získať súčet buniek v rozsahu C3:D7, použite vzorec =SUM(C3:D7).

V špeciálnom prípade, keď rozsah pozostáva výlučne z niekoľkých stĺpcov, napríklad od B do D, je jeho adresa napísaná v tvare B:D. Podobne, ak rozsah pozostáva výlučne z riadkov 6 až 15, potom má adresu 6:15. Okrem toho môžete pri písaní vzorcov použiť spojenie niekoľkých rozsahov alebo buniek a oddeliť ich symbolom „;“. (bodkočiarka), napríklad C3:D7; E5;F3:G7.

Úpravu už zadaného vzorca je možné vykonať niekoľkými spôsobmi:

Dvojitým kliknutím ľavým tlačidlom myši na bunku upravíte vzorec priamo v tejto bunke;

Vyberte bunku a stlačte kláves F2 (obr. 2);

Vyberte bunku presunutím kurzora na riadok vzorcov a kliknutím ľavým tlačidlom myši.

V dôsledku toho sa program prepne do režimu úprav, počas ktorého môžete vykonať potrebné zmeny vo vzorci.

Pri vypĺňaní tabuľky je zvykom nastaviť výpočtové vzorce iba pre „prvý“ (počiatočný) riadok alebo „prvý“ (počiatočný) stĺpec a zvyšok tabuľky vyplniť vzorcami pomocou režimov kopírovania alebo vypĺňania. Vynikajúci výsledok sa dosiahne použitím vzorcov automatického kopírovania pomocou automatického dopĺňania.

Pripomeňme si, ako správne implementovať režim kopírovania. Môžu existovať rôzne možnosti (a tiež problémy).

Je potrebné myslieť na to, že pri kopírovaní sa adresy transponujú. Keď kopírujete vzorec z jednej bunky do druhej, Excel reaguje odlišne na vzorce s relatívnymi a absolútnymi odkazmi. Pri relatívnych Excel štandardne transponuje adresy v závislosti od pozície bunky, do ktorej sa vzorec skopíruje.

Napríklad je potrebné pridať riadok po riadku hodnoty stĺpcov A a B (obr. 8) a výsledok umiestniť do stĺpca C. Ak skopírujete vzorec =A2+B2 z bunky C2 do bunky C3* (a nižšie C), potom Excel sám skonvertuje adresy vzorcov ako =A3+B3 (atď.). Ak však potrebujete umiestniť vzorec, povedzme, z C2 do bunky D4, vzorec už bude vyzerať ako =B4+C4 (namiesto požadovaného =A4+B4), a preto bude výsledok výpočtov nesprávny! Inými slovami, venujte zvláštnu pozornosť procesu kopírovania a v prípade potreby upravte vzorce manuálne. Mimochodom, samotné kopírovanie z C2 do C3 sa vykonáva takto:

1) vyberte bunku C2, z ktorej potrebujete skopírovať vzorec;

2) kliknite na tlačidlo „Kopírovať“ na paneli s nástrojmi alebo na klávesy Ctrl+C, alebo z ponuky vyberte „Upraviť ® Kopírovať“;

3) vyberieme bunku C3, do ktorej skopírujeme vzorec;

4) stlačte tlačidlo „Prilepiť“ na paneli s nástrojmi alebo klávesy Ctrl+V alebo cez ponuku „Upraviť ® Prilepiť“ a stlačte Enter.

Pozrime sa na režim automatického dopĺňania. Ak potrebujete presunúť (skopírovať) vzorec do niekoľkých buniek (napríklad v C3:C5) nadol v stĺpci, potom je to pohodlnejšie a jednoduchšie: zopakujte predchádzajúcu postupnosť akcií až po krok 3 výberu bunky C3, potom presuňte kurzor myši na začiatočnú bunku rozsahu ( C3), stlačte ľavé tlačidlo myši a bez uvoľnenia ho potiahnite nadol do požadovanej poslednej bunky rozsahu. V našom prípade je to bunka C5. Potom uvoľnite ľavé tlačidlo myši, presuňte kurzor na tlačidlo „Vložiť“ na paneli nástrojov a stlačte ho a potom stlačte Enter. Excel sám prevedie adresy vzorcov v rozsahu, ktorý sme vybrali, na zodpovedajúce adresy riadkov.

Niekedy je potrebné skopírovať iba číselnú hodnotu bunky (rozsahu buniek). Ak to chcete urobiť, musíte urobiť nasledovné:

1) vyberte bunku (rozsah), z ktorej chcete kopírovať údaje;

2) kliknite na tlačidlo „Kopírovať“ na paneli nástrojov alebo vyberte „Upraviť ® Kopírovať“ z ponuky;

3) vyberte bunku (vľavo hore v novom rozsahu), do ktorej sa údaje skopírujú;

4) z ponuky vyberte „Edit ® Paste Special“ a stlačte Enter.

Pri kopírovaní vzorcov s nimi počítač okamžite vykonáva výpočty, čím vytvára rýchly a jasný výsledok.

:: Funkcie v Exceli

Funkcie v Exceli výrazne uľahčujú výpočty a interakciu s tabuľkami. Najčastejšie používanou funkciou je sčítanie hodnôt buniek. Pripomeňme, že sa nazýva SUM a argumenty sú rozsahy čísel, ktoré sa majú sčítať.

V tabuľke často potrebujete vypočítať súčet pre stĺpec alebo riadok. Na tento účel ponúka Excel funkciu automatického súčtu, ktorá sa vykonáva kliknutím na tlačidlo („AutoSum“) na paneli nástrojov.

Ak zadáme sériu čísel, umiestnime pod ne kurzor a dvakrát klikneme na ikonu automatického súčtu, čísla sa sčítajú (obr. 3).

V najnovšej verzii programu sa napravo od ikony automatického súčtu nachádza tlačidlo zoznamu, ktoré umožňuje namiesto sčítania vykonávať množstvo často používaných operácií (obr. 4).

:: Automatické výpočty

Niektoré výpočty je možné vykonať bez zadávania vzorcov. Urobme malú lyrickú odbočku, ktorá môže byť užitočná pre mnohých používateľov. Ako viete, tabuľkový procesor vďaka svojmu pohodlnému rozhraniu a výpočtovým možnostiam môže úplne nahradiť výpočty pomocou kalkulačky. Prax však ukazuje, že značná časť ľudí, ktorí pri svojej činnosti aktívne využívajú Excel, má na pracovnej ploche kalkulačku na vykonávanie medzivýpočtov.

Ak chcete vykonať operáciu sčítania dvoch alebo viacerých buniek v Exceli, aby ste získali dočasný výsledok, musíte vykonať aspoň dve operácie navyše – nájsť miesto v aktuálnej tabuľke, kde bude súčet umiestnený, a aktivovať automatické súčet operácie. A až potom môžete vybrať tie bunky, ktorých hodnoty je potrebné sčítať.

Preto bola od Excelu 7.0 do tabuľky zabudovaná funkcia automatického výpočtu. Teraz majú tabuľky Excelu schopnosť rýchlo vykonávať niektoré matematické operácie automaticky.

Ak chcete vidieť výsledok priebežného súčtu, jednoducho vyberte požadované bunky. Tento výsledok sa odráža aj v stavovom riadku v spodnej časti obrazovky. Ak sa tam suma nezobrazuje, presuňte kurzor na stavový riadok v spodnej časti rámu, kliknite pravým tlačidlom myši a v rozbaľovacej ponuke vedľa riadku Čiastka stlačte ľavé tlačidlo myši. Okrem toho v tejto ponuke na stavovom riadku môžete vybrať rôzne možnosti pre vypočítané výsledky: súčet, aritmetický priemer, počet prvkov alebo minimálnu hodnotu vo vybranom rozsahu.

Napríklad pomocou tejto funkcie vypočítame súčet hodnôt pre rozsah B3:B9. Vyberte čísla v rozsahu buniek B3:B9. Upozorňujeme, že v stavovom riadku umiestnenom v spodnej časti pracovného okna sa objavil nápis Sum=X, kde X je číslo rovné súčtu zvolených čísel v rozsahu (obr. 5).

Ako vidíte, výsledky obvyklého výpočtu pomocou vzorca v bunke B10 a automatického výpočtu sú rovnaké.

:: Sprievodca funkciou

Okrem súčtovej funkcie Excel umožňuje spracovávať dáta pomocou ďalších funkcií. Ktorýkoľvek z nich je možné zadať priamo do riadka vzorcov pomocou klávesnice, avšak na zjednodušenie zadávania a zníženie počtu chýb má Excel „Sprievodcu funkciou“ (obr. 6).

Dialógové okno „Sprievodcovia“ môžete vyvolať príkazom „Vložiť ® funkciu“, kombináciou klávesov Shift+F3 alebo tlačidla na štandardnom paneli nástrojov.

Prvý dialóg „Sprievodcu funkciami“ je usporiadaný tematicky. Po výbere kategórie sa v dolnom okne zobrazí zoznam názvov funkcií obsiahnutých v tejto skupine. Napríklad funkciu SUM() nájdete v skupine „Matematická“ a v skupine „Dátum a čas“ sú funkcie DAY(), MONTH(), YEAR(), DNES().

Okrem toho si Excel na urýchlenie výberu funkcií „pamätá“ názvy 10 naposledy použitých funkcií v príslušnej skupine. Všimnite si prosím, že v spodnej časti okna je zobrazený stručný odkaz na účel funkcie a jej argumenty. Ak kliknete na tlačidlo Pomocník v spodnej časti dialógového okna, Excel otvorí sekciu Pomocník.

Predpokladajme, že je potrebné vypočítať odpisy majetku. V tomto prípade by ste mali do oblasti vyhľadávania funkcií zadať slovo „odpisy“. Program vyberie všetky funkcie na odpisovanie (obr. 7).

Po vyplnení príslušných polí funkcie sa vypočítajú odpisy majetku.

Často je potrebné pridať čísla, ktoré spĺňajú nejakú podmienku. V tomto prípade by ste mali použiť funkciu SUMIF. Pozrime sa na konkrétny príklad. Povedzme, že musíte vypočítať výšku provízie, ak hodnota nehnuteľnosti presahuje 75 000 rubľov. Využívame na to údaje z tabuľky závislosti provízií od hodnoty nehnuteľnosti (obr. 8).

Naše kroky v tomto prípade sú nasledovné. Umiestnite kurzor do bunky B6, tlačidlom spustite „Sprievodcu funkciou“, v kategórii „Matematická“ vyberte funkciu SUMIF, nastavte parametre ako na obr. 9.

Upozorňujeme, že ako rozsah na kontrolu podmienky vyberieme rozsah buniek A2:A6 (hodnota vlastnosti) a ako rozsah súčtu B2:B6 (provízie) a podmienka vyzerá takto (>75000). Výsledkom nášho výpočtu bude 27 000 rubľov.

:: Dajme bunke názov

Na uľahčenie práce má Excel možnosť priradiť názvy jednotlivým bunkám alebo rozsahom, ktoré sa potom dajú použiť vo vzorcoch rovnako ako bežné adresy. Ak chcete rýchlo pomenovať bunku, vyberte ju, umiestnite ukazovateľ do poľa názvu na ľavej strane riadka vzorcov, kliknite na tlačidlo myši a zadajte názov.

Pri priraďovaní mien musíte pamätať na to, že môžu pozostávať z písmen (vrátane ruskej abecedy), čísel, bodiek a podčiarkovníkov. Prvý znak v názve musí byť písmeno alebo podčiarkovník. Názvy nemôžu mať rovnaký vzhľad ako odkazy na bunky, napríklad 100 Z$ alebo R1C1. Názov môže obsahovať viac ako jedno slovo, ale medzery nie sú povolené. Podčiarkovníky a bodky možno použiť ako oddeľovače slov, napríklad Daň z predaja alebo Prvý štvrťrok. Názov môže obsahovať až 255 znakov. V tomto prípade sú veľké a malé písmená vnímané rovnako.

Na vloženie názvu do vzorca môžete použiť príkaz „Vložiť ® Názov ® Vložiť“ výberom požadovaného názvu v zozname mien.

Je užitočné zapamätať si, že názvy v Exceli sa používajú ako absolútne odkazy, to znamená, že ide o typ absolútneho adresovania, čo je výhodné pri kopírovaní vzorcov.

Názvy v Exceli je možné definovať nielen pre jednotlivé bunky, ale aj pre rozsahy (vrátane nesusediacich). Ak chcete priradiť názov, jednoducho zvýraznite rozsah a potom zadajte názov do poľa názvu. Okrem toho na špecifikovanie názvov rozsahov obsahujúcich hlavičky je vhodné použiť špeciálny príkaz „Vytvoriť“ v ponuke „Vložiť ® názov“.

Ak chcete odstrániť meno, vyberte ho v zozname a kliknite na tlačidlo „Odstrániť“.

Keď vytvoríte vzorec, ktorý odkazuje na údaje z pracovného hárka, na identifikáciu údajov môžete použiť hlavičky riadkov a stĺpcov. Ak napríklad hodnotám stĺpca priradíte názov názvu stĺpca (obr. 10),

potom na výpočet celkovej sumy pre stĺpec „Provízia“ použite vzorec =SUM(Provízia) (obr. 11).

:: Ďalšie funkcie Excelu - šablóny

MS Excel obsahuje sadu šablón - excelové tabuľky, ktoré sú určené na analýzu ekonomickej činnosti podniku, vyhotovovanie faktúr, pracovných príkazov a dokonca aj na účtovanie osobného rozpočtu. Môžu byť použité na automatizáciu riešenia často sa vyskytujúcich problémov. Môžete tak vytvárať dokumenty na základe šablón „Zálohový výkaz“, „Faktúra“, „Objednávka“, ktoré obsahujú formuláre dokladov používaných v obchodnej činnosti. Tieto formuláre sa nelíšia od štandardných vo vzhľade a pri tlači a jediné, čo musíte urobiť, aby ste dostali dokument, je vyplniť jeho polia.

Ak chcete vytvoriť dokument na základe šablóny, vykonajte príkaz „Vytvoriť“ z ponuky „Súbor“ a potom vyberte požadovanú šablónu na karte „Riešenia“ (obr. 12).

Šablóny sa skopírujú na disk počas bežnej inštalácie Excelu. Ak sa šablóny nezobrazia v dialógovom okne Nový dokument, spustite inštalačný program Excelu a nainštalujte šablóny. Podrobné informácie o inštalácii šablón nájdete v téme „Inštalácia komponentov balíka Microsoft Office“ v Pomocníkovi programu Excel.

Ak chcete napríklad vytvoriť viacero finančných dokladov, vyberte šablónu „Finančné šablóny“ (obr. 13).

Táto skupina šablón obsahuje formuláre pre nasledujúce dokumenty:

Cestovný certifikát;
. predbežná správa;
. platobný príkaz;
. faktúra;
. faktúra;
. splnomocnenie;
. prichádzajúce a odchádzajúce objednávky;
. platby za telefón a elektrinu.

Vyberte formulár, ktorý potrebujete vyplniť, zadajte všetky potrebné údaje a vytlačte ho. V prípade potreby je možné dokument uložiť ako bežnú tabuľku programu Excel.

Excel umožňuje používateľovi vytvárať šablóny dokumentov sami, ako aj upravovať existujúce.

Formuláre dokumentov sa však môžu časom zmeniť a potom sa existujúca šablóna stane nepoužiteľnou. Okrem toho do šablón, ktoré sa dodávajú s Excelom, by bolo dobré vopred zadať také trvalé informácie, ako sú informácie o vašej organizácii a manažérovi. Nakoniec možno budete musieť vytvoriť svoju vlastnú šablónu: napríklad plánovacie oddelenie bude s najväčšou pravdepodobnosťou potrebovať šablóny na prípravu odhadov a výpočtov a účtovné oddelenie bude s najväčšou pravdepodobnosťou potrebovať formulár faktúry s logom vašej organizácie.

Pre takéto prípady Excel, ako aj mnohé iné programy, ktoré pracujú s elektronickými dokumentmi, poskytuje možnosť vytvárať a upravovať šablóny pre často používané dokumenty. Excel šablóna je špeciálny zošit, ktorý môžete použiť ako šablónu na vytvorenie ďalších zošitov rovnakého typu. Na rozdiel od bežného excelového zošita, ktorý má príponu *.xls, má súbor šablóny príponu *.xlt.

Pri vytváraní dokumentu na základe šablóny Excel automaticky vytvorí jeho pracovnú kópiu s príponou *.xls, pričom na koniec názvu dokumentu pridá sériové číslo. Pôvodná šablóna zostáva nedotknutá a môže byť následne znovu použitá.

Na automatické zadanie dátumu môžete použiť nasledujúci spôsob: do bunky dátumu zadajte funkciu DNES, po ktorej sa zobrazí aktuálny deň v mesiaci, mesiac a rok.

Samozrejme, pri práci s bežnými excelovými zošitmi môžete použiť všetky uvažované akcie na šablónach.

Microsoft Excel nie je len veľká tabuľka, ale aj ultramoderná kalkulačka s množstvom funkcií a schopností. V tejto lekcii sa naučíme, ako ho používať na určený účel.

Všetky výpočty v Exceli sa nazývajú vzorce a všetky začínajú znakom rovnosti (=).

Napríklad chcem vypočítať súčet 3+2. Ak kliknem na ktorúkoľvek bunku a do nej napíšem 3+2 a potom stlačím na klávesnici tlačidlo Enter, tak sa nič nevypočíta - do bunky sa zapíše 3+2. Ale ak napíšem =3+2 a stlačím Enter, tak sa všetko vypočíta a zobrazí sa výsledok.

Pamätajte na dve pravidlá:

Všetky výpočty v Exceli začínajú znakom =

Po zadaní vzorca je potrebné stlačiť tlačidlo Enter na klávesnici

A teraz o znameniach, s ktorými budeme počítať. Nazývajú sa tiež aritmetické operátory:

Doplnenie

Odčítanie

* násobenie

/ divízia. Existuje aj palica naklonená v opačnom smere. Nám to teda nevyhovuje.

^ umocňovanie. Napríklad 3^2 sa číta ako tri na druhú (na druhú mocninu).

% percent. Ak dáme toto znamienko za číslo, potom je deliteľné 100. Napríklad 5 % bude 0,05.
Pomocou tohto znaku môžete vypočítať úrok. Ak potrebujeme vypočítať päť percent z dvadsiatich, vzorec bude vyzerať takto: =20*5%

Všetky tieto znaky sú na klávesnici buď hore (nad písmenami spolu s číslami) alebo vpravo (v samostatnom bloku tlačidiel).

Ak chcete vytlačiť znaky v hornej časti klávesnice, musíte stlačiť a podržať tlačidlo označené Shift a spolu s ním stlačiť tlačidlo s požadovaným znakom.

Teraz skúsme počítať. Povedzme, že potrebujeme pridať číslo 122596 s číslom 14830. Ak to chcete urobiť, kliknite ľavým tlačidlom myši na ľubovoľnú bunku. Ako som už povedal, všetky výpočty v Exceli začínajú znakom „=“. To znamená, že v bunke musíte vytlačiť =122596+14830

A aby ste dostali odpoveď, musíte stlačiť tlačidlo Enter na klávesnici. Potom už bunka nebude obsahovať vzorec, ale výsledok.

Teraz venujte pozornosť tomuto hornému poľu v Exceli:

Toto je "Formula Bar". Potrebujeme to na kontrolu a zmenu našich vzorcov.

Kliknite napríklad na bunku, v ktorej sme práve vypočítali sumu.

A pozrite sa na riadok vzorcov. Ukáže presne, ako sme túto hodnotu získali.

To znamená, že v riadku vzorcov nevidíme samotné číslo, ale vzorec, pomocou ktorého bolo toto číslo získané.

Skúste napísať číslo 5 do inej bunky a stlačiť Enter na klávesnici. Potom kliknite na túto bunku a pozrite sa na riadok vzorcov.

Keďže sme toto číslo jednoducho vytlačili a nevypočítali sme ho pomocou vzorca, bude len v riadku vzorcov.

Ako správne počítať

Tento spôsob „počítania“ sa však spravidla nepoužíva tak často. Existuje pokročilejšia možnosť.

Povedzme, že máme takúto tabuľku:

Začnem prvou pozíciou „Syr“. Kliknem do bunky D2 a napíšem rovná sa.

Potom kliknem na bunku B2, keďže jej hodnotu potrebujem vynásobiť C2.

Napíšem znak násobenia *.

Teraz kliknem na bunku C2.

A nakoniec stlačím tlačidlo Enter na klávesnici. Všetky! Bunka D2 poskytuje požadovaný výsledok.

Kliknutím na túto bunku (D2) a pohľadom na riadok vzorcov môžete vidieť, ako bola táto hodnota získaná.

Vysvetlím to pomocou rovnakej tabuľky ako príkladu. Teraz je v bunke B2 zadané číslo 213, vymažem ho, napíšem iné číslo a stlačím Enter.

Pozrime sa na bunku s množstvom D2.

Výsledok sa zmenil. Stalo sa to preto, že sa zmenila hodnota v B2. Náš vzorec je predsa nasledovný: =B2*C2

To znamená, že program Microsoft Excel vynásobí obsah bunky B2 obsahom bunky C2, nech už je táto hodnota akákoľvek. Urobte si vlastný záver :)

Pokúste sa vytvoriť rovnakú tabuľku a vypočítajte súčet vo zvyšných bunkách (D3, D4, D5).

Vzorec je matematický výraz, ktorý je vytvorený na výpočet výsledku a ktorý môže závisieť od obsahu iných buniek. Vzorec v bunke môže obsahovať údaje, odkazy na iné bunky a tiež označenie akcií, ktoré je potrebné vykonať.

Použitie odkazov na bunky umožňuje prepočítať výsledky vzorcov, keď sa zmení obsah buniek zahrnutých vo vzorcoch.

V Exceli sa vzorce začínajú znakom =. Zátvorky () možno použiť na definovanie poradia matematických operácií.

Excel podporuje nasledujúce operátory:

  • Aritmetické operácie:
    • pridanie (+);
    • násobenie (*);
    • zistenie percent (%);
    • odčítanie (-);
    • rozdelenie(/);
    • exponent (^).
  • Porovnávacie operátory:
    • = rovné;
    • < меньше;
    • > viac;
    • <= меньше или равно;
    • >= väčšie alebo rovné;
    • <>nerovná sa.
  • Telekomunikační operátori:
    • : rozsah;
    • ; únie;
    • & operátor zreťazenia textu.

Tabuľka 22. Príklady vzorcov

Cvičenie

Vložte vzorec -25-A1+AZ

Vopred zadajte ľubovoľné čísla do buniek A1 a A3.

  1. Vyberte požadovanú bunku, napríklad B1.
  2. Začnite zadávať vzorec znakom =.
  3. Zadajte číslo 25 a potom operátor (znamienko –).
  4. Zadajte odkaz na prvý operand, napríklad kliknutím na požadovanú bunku A1.
  5. Zadajte nasledujúci operátor (znamienko +).
  6. Kliknite do bunky, ktorá je druhým operandom vo vzorci.
  7. Doplňte vzorec stlačením klávesu Zadajte. V bunke B1 dostanete výsledok.

Autosummácia

Tlačidlo AutoSum- ∑ možno použiť na automatické vytvorenie vzorca, ktorý sčítava plochu bezprostredne susediacich buniek vľavo v tomto riadku a priamo vyššie v tomto stĺpci.

  1. Vyberte bunku, do ktorej chcete umiestniť výsledok súčtu.
  2. Kliknite na tlačidlo AutoSum - ∑ alebo stlačte kombináciu klávesov Alt+=. Excel rozhodne, ktorú oblasť zahrnie do rozsahu súčtu, a zvýrazní ju bodkovaným pohyblivým rámčekom, ktorý sa nazýva orámovanie.
  3. Kliknite Zadajte akceptujete oblasť, ktorú Excel vybral, alebo pomocou myši vyberte novú oblasť a potom stlačte kláves Enter.

Funkcia AutoSum sa automaticky transformuje, keď sú bunky pridané alebo odstránené v rámci oblasti.

Cvičenie

Vytvorenie tabuľky a výpočet pomocou vzorcov

  1. Zadajte číselné údaje do buniek, ako je uvedené v tabuľke. 23.
A IN S D B F
1
2 Magnolia Lily fialový Celkom
3 Vyššie 25 20 9
4 Sekundárny špeciál 28 23 21
5 Odborná škola 27 58 20
V Iné 8 10 9
7 Celkom
8 Bez vyššieho

Tabuľka 23. Tabuľka pôvodných údajov

  1. Vyberte bunku B7, v ktorej sa vypočíta vertikálny súčet.
  2. Kliknite na tlačidlo AutoSum - ∑ alebo kliknite Alt+=.
  3. Opakujte kroky 2 a 3 pre bunky C7 a D7.

Vypočítajte počet zamestnancov bez vysokoškolského vzdelania (pomocou vzorca B7-VZ).

  1. Vyberte bunku B8 a zadajte znak (=).
  2. Kliknite na bunku B7, ktorá je prvým operandom vo vzorci.
  3. Zadajte znamienko (-) na klávesnici a kliknite do bunky V3, čo je druhý operand vo vzorci (vzorec sa zadá).
  4. Kliknite Zadajte(výsledok sa vypočíta v bunke B8).
  5. Opakujte kroky 5 až 8 a vypočítajte pomocou príslušných vzorcov v bunkách C8 a 08.
  6. Uložte súbor s názvom Education_employees.x1s.

Tabuľka 24.Výsledok výpočtu

A B S D E F
1 Rozdelenie zamestnancov podľa vzdelania
2 Magnolia Lily fialový Celkom
3 Vyššie 25 20 9
4 Sekundárny špeciál 28 23 21
5 Odborná škola 27 58 20
6 Iné 8 10 9
7 Celkom 88 111 59
8 Bez vyššieho 63 91 50

Duplikovanie vzorcov pomocou značky výplne

Plochu bunky (bunku) možno vynásobiť použitím značka naplnenia. Ako je uvedené v predchádzajúcej časti, rukoväť výplne je kontrolný bod v pravom dolnom rohu vybratej bunky.

Často je potrebné reprodukovať nielen údaje, ale aj vzorce obsahujúce adresné odkazy. Proces replikácie vzorcov pomocou rukoväte výplne vám umožňuje skopírovať vzorec a súčasne zmeniť odkazy na adresy vo vzorci.

  1. Vyberte bunku obsahujúcu vzorec, ktorý chcete replikovať.
  2. Potiahnite značka naplnenia správnym smerom. Vzorec bude replikovaný vo všetkých bunkách.

Tento proces sa zvyčajne používa pri kopírovaní vzorcov v rámci riadkov alebo stĺpcov obsahujúcich rovnaký typ údajov. Pri replikácii vzorcov pomocou značky výplne sa menia takzvané relatívne adresy buniek vo vzorci (relatívne a absolútne prepojenia budú podrobne popísané nižšie).

Cvičenie

Replikácia vzorcov

1.Otvorte súbor Employee_Education.x1s.

  1. Do bunky E3 zadajte vzorec pre automatické sčítanie buniek =SUM(VZ:03).
  2. Skopírujte vzorec potiahnutím rukoväte výplne do buniek E4:E8.
  3. Pozrite sa, ako sa vo výsledných vzorcoch menia relatívne adresy buniek (tabuľka 25) a súbor uložte.
A IN S D E F
1 Rozdelenie zamestnancov podľa vzdelania
2 Magnolia Lily fialový Celkom
3 Vyššie 25 20 9 =SUM(VZ:03)
4 Sekundárny špeciál 28 23 21 =SUM(B4:04)
5 Odborná škola 27 58 20 =SUM(B5:05)
6 Iné 8 10 9 =SUM(B6:06)
7 Celkom 88 111 58 =SUM(B7:07)
8 Bez vyššieho 63 91 49 =SUM(B8:08)

Tabuľka 25. Zmena adries buniek pri replikácii vzorcov

Relatívne a absolútne referencie

Vzorce, ktoré implementujú výpočty v tabuľkách, používajú na adresovanie buniek takzvané odkazy. Odkaz na bunku môže byť príbuzný alebo absolútne.

Používanie relatívnych referencií je podobné ako uvádzanie smeru jazdy na ulici – „choďte tri bloky na sever, potom dva bloky na západ“. Dodržiavanie týchto inštrukcií z rôznych štartovacích miest povedie k rôznym cieľom.

Napríklad vzorec, ktorý sčítava čísla v stĺpci alebo riadku, sa potom často skopíruje pre iné čísla riadkov alebo stĺpcov. Takéto vzorce používajú relatívne odkazy (pozri predchádzajúci príklad v tabuľke 25).

Absolútny odkaz na bunku alebo oblasť buniek bude vždy odkazovať na rovnakú adresu riadka a stĺpca. V porovnaní so smermi ulíc to bude asi takto: „Choďte na križovatku Arbat a Boulevard Ring“. Bez ohľadu na to, kde začnete, povedie to na rovnaké miesto. Ak vzorec vyžaduje, aby adresa bunky zostala pri kopírovaní nezmenená, potom sa musí použiť absolútny odkaz (formát záznamu $A$1). Napríklad, keď vzorec vypočítava zlomky celkového množstva, odkaz na bunku obsahujúcu celkové množstvo by sa pri kopírovaní nemal zmeniť.

Znak dolára ($) sa zobrazí pred odkazom na stĺpec aj odkazom na riadok (napríklad $C$2 postupným stlačením klávesu F4 pridáte alebo odstránite znak pred číslom stĺpca alebo riadku v odkaze (C$2 alebo $C2). - takzvané zmiešané odkazy).

  1. Vytvorte tabuľku podobnú tej nižšie.

Tabuľka 26. Výpočet mzdy

  1. Do bunky SZ zadajte vzorec na výpočet Ivanovovej mzdy =B1*VZ.

Pri replikácii vzorca z tohto príkladu s relatívnymi odkazmi sa v bunke C4 zobrazí chybové hlásenie (#HODNOTA!), pretože sa zmení relatívna adresa bunky B1 a vzorec =B2*B4 sa skopíruje do bunky C4;

  1. Nastavte absolútny odkaz na bunku B1 umiestnením kurzora do riadka vzorcov na B1 a stlačením klávesu F4 Vzorec v bunke C3 bude vyzerať ako =$B$1*B3.
  2. Skopírujte vzorec do buniek C4 a C5.
  3. Uložte súbor (tabuľka 27) pod názvom Mzda.xls.

Tabuľka 27. Výsledky výpočtu platu

Názvy vo vzorcoch

Názvy vo vzorcoch sú ľahšie zapamätateľné ako adresy buniek, takže namiesto absolútnych odkazov môžete použiť pomenované rozsahy (jedna alebo viac buniek). Pri vytváraní mien je potrebné dodržiavať nasledujúce pravidlá:

  • mená môžu obsahovať najviac 255 znakov;
  • mená musia začínať písmenom a môžu obsahovať akýkoľvek znak okrem medzery;
  • názvy by nemali byť podobné odkazom, ako napríklad VZ, C4;
  • mená by nemali používať funkcie Excelu ako napr SÚČET AK a tak ďalej.

Na jedálnom lístku Vložiť, Meno Existujú dva rôzne príkazy na vytváranie pomenovaných oblastí: Create a Assign.

Tím Vytvoriť vám umožňuje zadať (zadať) požadovaný názov ( len jeden), Priradiť príkaz používa menovky umiestnené na pracovnom hárku ako názvy oblastí (umožňuje vytvárať niekoľko mien naraz).

Vytvorenie názvu

  1. Vyberte bunku B1 (tabuľka 26).
  2. Vyberte z ponuky Príkaz Vložiť, Názov (Vložiť, Názov) Priradiť (Definovať).
  3. Zadajte svoje meno Hodinová sadzba a kliknite na tlačidlo OK.
  4. Vyberte bunku B1 a uistite sa, že pole názvu hovorí Hodinová sadzba.

Vytváranie viacerých mien

  1. Vyberte bunky VZ:C5 (tabuľka 27).
  2. Vyberte z ponuky Príkaz Vložiť, Názov (Vložiť, Názov) Vytvoriť (Vytvoriť), zobrazí sa dialógové okno Vytvorte mená(obr. 88).
  3. Uistite sa, že je začiarknutý prepínač v ľavom stĺpci a kliknite OK.
  4. Vyberte bunky VZ:NZ a uistite sa, že pole názvu hovorí Ivanov.

Ryža. 88. Dialógové okno Vytvoriť mená

Namiesto absolútneho odkazu môžete do vzorca vložiť názov.

  1. V riadku vzorcov umiestnite kurzor na miesto, kam chcete pridať názov.
  2. Vyberte z ponuky Insert, Name (Insert, Name) command Paste (Paste), Zobrazí sa dialógové okno Vložiť mená.
  1. Vyberte požadovaný názov zo zoznamu a kliknite na tlačidlo OK.

Chyby vo vzorcoch

Ak sa pri zadávaní vzorcov alebo údajov vyskytne chyba, vo výslednej bunke sa zobrazí chybové hlásenie. Prvým znakom všetkých chybových hodnôt je symbol #. Hodnoty chýb závisia od typu chyby.

Excel nedokáže rozpoznať všetky chyby, ale tie, ktoré sú zistené, musí byť možné opraviť.

Chyba # # # # sa objaví, keď sa zadané číslo nezmestí do bunky. V tomto prípade by ste mali zväčšiť šírku stĺpca.

Chyba #DIV/0! sa objaví, keď sa vzorec pokúsi deliť nulou. Najčastejšie sa to stane, keď je deliteľ odkazom na bunku, ktorý obsahuje nulovú alebo prázdnu hodnotu.

Chyba #N/A! je skratka pre výraz „nedefinované údaje“. Táto chyba naznačuje, že vzorec používa prázdny odkaz na bunku.

Chyba #NAME? sa zobrazí, keď bol názov použitý vo vzorci odstránený alebo nebol predtým definovaný. Ak chcete opraviť, určiť alebo opraviť názov oblasti údajov, názov funkcie atď.

Chyba #EMPTY! sa objaví, keď existuje priesečník medzi dvoma oblasťami, ktoré v skutočnosti nemajú spoločné bunky. Chyba najčastejšie naznačuje, že sa vyskytla chyba pri zadávaní odkazov na rozsahy buniek.

Chyba #NUMBER! sa objaví, keď funkcia s číselným argumentom používa nesprávny formát argumentu alebo hodnotu.

Chyba #HODNOTA! sa zobrazí, keď vzorec používa neplatný argument alebo typ operandu. Napríklad namiesto číselnej alebo logickej hodnoty operátora alebo funkcie bol zadaný text.

Okrem uvedených chýb sa pri zadávaní vzorcov môže objaviť kruhový odkaz.

Kruhový odkaz nastane, keď vzorec priamo alebo nepriamo obsahuje odkazy na vlastnú bunku. Kruhový odkaz môže spôsobiť skreslenie vo výpočtoch pracovného hárka, a preto sa vo väčšine aplikácií považuje za chybu. Keď zadáte kruhový odkaz, zobrazí sa varovné hlásenie (Obrázok 89).

Ak chcete chybu opraviť, odstráňte bunku, ktorá spôsobila kruhový odkaz, upravte alebo znova zadajte vzorec.

Funkcie v Exceli

Zložitejšie výpočty v tabuľkách programu Excel sa vykonávajú pomocou špeciálnych funkcií (obr. 90). Po výbere príkazu je k dispozícii zoznam kategórií funkcií Funkcia v ponuke Vložiť (Vložiť, Funkcia).

Finančné funkcie vykonávajú také výpočty, ako je výpočet výšky platby za úver, výšky platby zisku z investícií atď.

Funkcie Dátum a Čas vám umožňujú pracovať s hodnotami dátumu a času vo vzorcoch. Pomocou funkcie môžete napríklad použiť aktuálny dátum vo vzorci DNES.

Ryža. 90. Sprievodca funkciou

Matematické funkcie fungujú jednoducho a zložité matematické výpočty, ako napríklad výpočet súčtu rozsahu buniek, absolútnej hodnoty čísla, zaokrúhľovania čísel atď.

Štatistické funkcie umožňujú vykonávať štatistickú analýzu údajov. Môžete napríklad určiť priemer a rozptyl vzorky a oveľa viac.

Databázové funkcie možno použiť na vykonávanie výpočtov a výber záznamov na základe podmienok.

Textové funkcie poskytnúť používateľovi možnosť spracovania textu. Pomocou tejto funkcie môžete napríklad spojiť viacero reťazcov PRIPOJIŤ.

Logické funkcie sú určené na testovanie jednej alebo viacerých podmienok. Napríklad funkcia IF umožňuje určiť, či je zadaná podmienka pravdivá, a vráti jednu hodnotu, ak je podmienka pravdivá, a inú, ak nie je pravdivá.

Funkcie Kontrola vlastností a hodnôt sú určené na určenie údajov uložených v bunke. Tieto funkcie kontrolujú hodnoty v bunke podľa podmienky a vracajú hodnoty v závislosti od výsledku Pravda alebo lož.

Na vykonanie tabuľkových výpočtov pomocou vstavaných funkcií odporúčame použiť Sprievodcu funkciami. Keď vyberiete príkaz, zobrazí sa dialógové okno Sprievodca funkciou Funkcia v ponuke Vložiť alebo stlačením tlačidla , na štandardnom paneli nástrojov. Počas dialógu so sprievodcom musíte zadať argumenty vybranej funkcie, aby ste to urobili, musíte vyplniť polia v dialógovom okne zodpovedajúcimi hodnotami alebo adresami buniek tabuľky.

Cvičenie

Vypočítajte priemernú hodnotu pre každý riadok v súbore Education.xls.

  1. Vyberte bunku F3 a kliknite na tlačidlo Sprievodca funkciou.
  2. V prvom dialógovom okne Sprievodcu funkciou v kategórii Štatistika vyberte funkciu PRIEMERNÝ, kliknite na tlačidlo Ďalej.
  3. Druhé dialógové okno Sprievodcu funkciou musí poskytnúť argumenty. Vstupný kurzor je vo vstupnom poli prvého argumentu. V tomto poli ako číslo argumentu! zadajte adresu rozsahu B3:D3 (obr. 91).
  4. Kliknite OK.
  5. Skopírujte výsledný vzorec do buniek F4:F6 a uložte súbor (tabuľka 28).

Ryža. 91. Zadanie argumentu v Sprievodcovi funkciou

Tabuľka 28. Tabuľka výsledkov výpočtov pomocou sprievodcu funkciou

A IN S D E F
1 Rozdelenie zamestnancov podľa vzdelania
2 Magnolia Lily fialový Celkom Priemerná
3 Vyššie 25 20 9 54 18
4 Sekundárny špeciál 28 23 21 72 24
8 Odborná škola 27 58 20 105 35
V Iné 8 10 9 27 9
7 Celkom 88 111 59 258 129

Ak chcete zadať rozsah buniek do okna Sprievodca funkciami, môžete použiť myš na zakrúžkovanie tohto rozsahu na pracovnom hárku tabuľky (v príklade B3:D3). Ak okno Sprievodca funkciou pokrýva požadované bunky, môžete dialógové okno presunúť. Po výbere rozsahu buniek (B3:D3) sa okolo neho objaví bežiaci bodkovaný rámik a v poli argumentov sa automaticky zobrazí adresa vybraného rozsahu buniek.

Ak potrebujete pracovať s percentami, určite vás poteší, že Excel má nástroje na uľahčenie vašej úlohy.

Na výpočet mesačných percent zisku a strát môžete použiť Excel. Či už ide o zvýšenie nákladov alebo percento predaja medzimesačne, vždy budete mať prehľad o kľúčových metrikách svojej firmy. Excel vám s tým pomôže.

Naučíte sa tiež pracovať s percentami na pokročilej úrovni, počítať aritmetický priemer, ako aj percentilovú hodnotu, čo môže byť v mnohých prípadoch užitočné.

Tento tutoriál vás naučí, ako vypočítať percentá v Exceli krok za krokom. Pozrime sa na niekoľko percentuálnych vzorcov, funkcií a tipov v Exceli na príklade pracovného hárku o obchodných výdavkoch a školského hárku so známkami.

Získate zručnosti, ktoré vám umožnia šikovne pracovať s percentami v Exceli.

Demo video

Celý tutoriál si môžete pozrieť v ukážke vyššie alebo si prečítať podrobný popis nižšie. Ak chcete začať, stiahnite si bezplatné zdrojové súbory: Použijeme ich počas cvičení.

1. Zadávanie údajov do Excelu

Prilepte nasledujúce informácie (alebo otvorte stiahnutý súbor " predobrazy.xlsx", ktorý sa nachádza v zdrojových súboroch tohto návodu). Obsahuje hárok Výdavky. Hárok Známky použijeme neskôr.

Percento údajov pracovného hárka

Vypočítajte percentuálny nárast

Povedzme, že očakávate, že sa vaše výdavky v budúcom roku zvýšia o 8 % a chcete tieto čísla vidieť.

Predtým, ako začnete písať vzorce, je užitočné vedieť, že v Exceli môžete vykonávať výpočty s použitím znaku percenta (20 %) aj desatinných miest (0,2 alebo len 0,2). Symbol percenta pre Excel je len formátovanie.

Chceme vidieť konečnú sumu, nielen výšku rastu.

Krok 1

V cele A18 písať s 8% rast. Pretože táto bunka obsahuje text aj číslo, Excel bude predpokladať, že celá bunka obsahuje text.

Krok 2

Kliknite Tab, potom v cele B18 napíšte nasledujúci vzorec: =B17 * 1,08

Alebo môžete použiť nasledujúci vzorec: =B17 * 108 %

Výsledná suma bude 71 675, ako je uvedené nižšie:

Vypočítajte percentuálny nárast v Exceli

3. Výpočet zníženia úrokov

Možno si myslíte, že výdavky naopak klesnú o 8 %. Ak chcete vidieť výsledok, vzorec bude podobný. Začnite tým, že ukážete celkovú zníženú sumu, nielen výšku zníženia.

Krok 1

V cele A19, píšte s 8 % znížením.

Krok 2

Kliknite Tab, potom v cele B19 napíšte nasledujúci vzorec: = B17 * 0,92

Alebo môžete vzorec napísať inak: =B17 * 92 %

Výsledok bude 61,057.

Vypočítajte percentuálne zníženie v Exceli

Krok 3

Ak potrebujete možnosť zmeniť percento zvýšenia alebo zníženia ceny, môžete vzorce napísať takto:

Vzorec pre 8% rast v bunke B18 bude = B17 + B17 * 0,08

Krok 4

Vzorec na zníženie o 8 % v bunke B19 bude =B17 – B17 * 0,08

V týchto vzorcoch môžete jednoducho zmeniť 0,08 na akékoľvek iné číslo, aby ste získali výsledok s inou percentuálnou veľkosťou.

4. Výpočet úrokovej sadzby

Teraz prejdime k vzorcu na výpočet úrokovej sadzby. Čo ak chcete vedieť, koľko je títo 8%? Ak to chcete urobiť, vynásobte celkovú sumu B17 o 8 percent.

Krok 1

V cele A20 písať 8 % z celkovej sumy.

Krok 2

Stlačte kláves Tab a v cele B20 napíšte nasledujúci vzorec: =B17 * 0,08

Alebo môžete napísať tento vzorec takto: =B17 * 8%

Výsledok bude 5,309.

Výpočet percent v Exceli

5. Vykonávanie zmien bez úpravy vzorcov

Ak chcete zmeniť percento bez úpravy vzorcov, musíte ho umiestniť do samostatnej bunky. Začnime s názvami riadkov.

Krok 1

Do bunky A22 napíšte Zmeny. Stlačte kláves Zadajte.

Krok 2

Do bunky A23 napíšte Najväčšie množstvo. Stlačte kláves Zadajte.

Krok 3

Napíšte do bunky A24 Najmenšie množstvo. Stlačte kláves Zadajte.

Na liste by to malo vyzerať takto:

Excelový hárok s ďalšími riadkami

Teraz pridajme percentá a vzorce napravo od nadpisov.

Krok 4

V cele B22 písať 8% . Stlačte kláves Zadajte.

Krok 5

V cele B23 napíšte nasledujúci vzorec, ktorý vypočíta celkovú sumu pridaním 8%: =B17 + B17 * B22

Krok 6

V cele B24 napíšte nasledujúci vzorec, ktorý vypočíta súčet mínus 8 %: =B17 * (100 % - B22)

Ak v bunke B22 Ak napíšete 8 %, Excel automaticky naformátuje túto bunku ako percento. Ak napíšete 0,08 alebo 0,08, Excel uloží bunku tak, ako je. Formát bunky môžete kedykoľvek zmeniť na percentuálny formát kliknutím na tlačidlo na páse s nástrojmi Percentuálny štýl:

Tlačidlo Percent Style a jeho činnosť

Tip: Čísla môžete formátovať aj ako percentá pomocou klávesovej skratky Ctrl + Shift + % pre Windows resp Command + Shift + % pre Ma c.

6. Výpočet zmien úrokových sadzieb

Všeobecné pravidlo na výpočet percentuálnych zmien je nasledovné:

=(nová hodnota - stará hodnota) / nová hodnota

Ak je január prvým mesiacom, potom sa úroková sadzba nemení. Prvá zmena oproti januáru bude vo februári a vedľa februárových údajov napíšeme nasledovné:

Krok 1

Krok 2

Excel zobrazí výsledok ako zlomok, takže kliknite na tlačidlo Percentný štýl na páse s nástrojmi (alebo použite klávesovú skratku vyššie), aby sa číslo zobrazilo v percentách.

Tlačidlo Percent Style na zobrazenie v percentách

Teraz, keď máme percentuálny rozdiel medzi februárom a januárom, môžeme automaticky vyplniť stĺpec vzorca, aby sme zistili percentuálnu zmenu za zostávajúce mesiace.

Krok 3

Umiestnite kurzor myši na bodku v pravom dolnom rohu bunky, ktorá obsahuje hodnotu -7 %.

Bodka automatického dopĺňania v Exceli

Krok 4

Keď sa ukazovateľ myši zmení na nitkový kríž, urobte to dvojité kliknutie myš.

Ak nepoznáte funkciu automatického dokončovania, prečítajte si o technike 3 v mojom článku o technikách programu Excel:

Krok 5

Vyberte bunku B3(s nadpisom „Suma“).

Krok 6

Bod ukazovateľ myši na bodka automatického dopĺňania a potiahnite ju o jednu bunku doprava do bunky C3. Tým sa duplikuje názov spolu s jeho formátovaním.

Krok 7

V cele C3 nahradiť názov za % zmeny.

Všetky zmeny v percentách sú vypočítané

7. Výpočet percenta z celkovej sumy

Poslednou technikou na tomto pracovnom hárku je výpočet percenta z celkového počtu za každý mesiac. Predstavte si toto percento ako koláčový graf, pričom každý mesiac tvorí jeden sektor a súčet všetkých sektorov je 100 %.

Nezáleží na tom, či to vypočítate pomocou Excelu alebo na papieri, použije sa nasledujúca jednoduchá operácia delenia:

Hodnota jednej položky/celková suma

Ktoré musia byť uvedené v percentách.

V našom prípade vydelíme každý mesiac celkovou sumou, ktorá je na konci stĺpca B.

Krok 1

Vyberte bunku C3 a vykonať automatické dopĺňanie jedna bunka, až D3.

Krok 2

Zmeňte text v bunke D3 na % z celkovej sumy.

Krok 3

V cele D4 zatiaľ napíšte tento vzorec nestláčajte Enter: =B4/B17

Krok 4

Pred stlačením klávesu enter sa musíme uistiť, že pri automatickom dopĺňaní nedochádza k žiadnym chybám. Keďže budeme automaticky vypĺňať stĺpce nadol, deliteľ (momentálne B17) by sa nemal meniť. Koniec koncov, ak sa zmení, všetky výpočty od februára do decembra budú nesprávne.

Uistite sa, že textový kurzor je stále v bunke pred oddeľovačom "B17".

Krok 5

Stlačte kláves F4(na Macu - Fn+ F4).

Takže pred hodnoty stĺpca a riadka vložíte znak dolára ($) a deliteľ bude vyzerať takto: 17 $ B$. $ B znamená, že stĺpec B sa nezmení na stĺpec C atď., ale $17 znamená, že linka číslo 17 sa nezmení na linku 18 atď.

Krok 6

Uistite sa, že vzorec skončí takto: = B4 / $ B $ 17.

Vzorec pre percento z celkovej sumy

Krok 7

Teraz môžete stlačiť kláves Enter a vykonať automatické dopĺňanie.

Kliknite Ctrl + Zadajte(na Macu - Príkaz+ ↩) na zadanie vzorca bez presunu kurzora myši do bunky nižšie.

Krok 8

Kliknite na tlačidlo Percentný štýl na páse s nástrojmi alebo použite klávesovú skratku Ctrl+ Shift+ % (Príkaz+ Shift+ %).

Krok 9

Prejdite myšou nad bodkou automatického dopĺňania v bunke D4.

Krok 10

Keď sa kurzor zmení na nitkový kríž, urobte to dvojité kliknutie myšou automaticky vyplniť bunky nižšie vzorcami.

Teraz sa pre každý mesiac zobrazí hodnota jeho percenta z celkovej sumy.

Dokončené percento z celkovej sumy

8. Percentuálne poradie

Hodnotenie hodnôt v percentách je štatistická metóda. Určite to poznáte zo školy, kde sa počíta GPA žiakov a umožňuje ich to zoradiť v percentách. Čím vyššie skóre, tým vyššie percento.

Zoznam čísel (v našom prípade skóre) sa nachádza v skupine buniek, ktoré sa v Exceli nazývajú pole. Na poliach nie je nič zvláštne, preto ich nebudeme definovať. To je jednoducho to, čo Excel nazýva rozsah buniek, ktoré používate vo vzorci.

V Exceli existujú dve funkcie na výpočet percentuálneho poradia. Jeden z nich obsahuje počiatočné a koncové hodnoty poľa a druhý nie.

Pozrite sa na druhú kartu listu: známky.

Percentuálne hodnotenie v Exceli - Score Sheet

Tu je zoznam 35 priemerných skóre, zoradených vo vzostupnom poradí. Prvá vec, ktorú chceme vedieť, je percentuálne hodnotenie pre každý GPA. Na to použijeme funkciu =PERCENTRANK.INC. "INC" v tejto funkcii znamená "vrátane", pretože bude zahŕňať prvý a posledný bod v zozname. Ak chcete vylúčiť prvé a posledné skóre poľa, použite funkciu =PERCENTRANK.EXC.

Funkcia má dva povinné argumenty a vyzerá takto: =PERCENTRANK.INC(pole, záznam)

  • pole(pole) je skupina buniek, ktoré tvoria zoznam (v našom prípade B3:B37)
  • vstup(hodnota) je ľubovoľná hodnota alebo bunka v zozname

Krok 1

Vyberte prvú bunku v zozname C3.

Krok 2

Napíšte nasledujúci vzorec, ale Nestláčajte kláves Enter: =PERCENTRANK.INC(B3:B37

Krok 3

Tento rozsah musí byť konštantný, aby sme mohli použiť automatické dopĺňanie na všetky bunky zhora nadol.

Takže stlačte kláves F4(na Macu - Fn+ F4), ak chcete k hodnotám pridať znak dolára.

Vzorec by mal vyzerať takto: =PERCENTRANK.INC($B$3:$B$37

Krok 4

V každej bunke v stĺpci C uvidíme percentuálne poradie zodpovedajúcej hodnoty v stĺpci B.

Kliknite na bunku B3 a zatvorte zátvorky.

Konečný vzorec je: =PERCENTRANK.INC($B$3:$B$37,B3)

Funkcia percentuálneho hodnotenia

Teraz môžeme zadať a formátovať hodnoty.

Krok 5

Ak je to potrebné, vyberte bunku znova C3.

Krok 6

Vykonať dvojité kliknutie myš zapnutá bod automatického dopĺňania bunky C3. Tým sa automaticky vyplnia bunky stĺpca nižšie.

Krok 7

Kliknite na pás s nástrojmi Percentný štýl naformátovať stĺpec ako percento. V dôsledku toho by ste mali vidieť nasledovné:

Dokončená a naformátovaná funkcia percentuálneho hodnotenia

Takže, ak máte dobré známky a váš GPA je 3,98, potom ste na 94. percentile.

9. Výpočet percentilu.

Pomocou funkcií PERCENTIL môžete vypočítať percentil. Pomocou tejto funkcie môžete zadať percentuálnu veľkosť a získať hodnotu z poľa, ktoré zodpovedá tomuto percentu. Ak v rozsahu nie je presná hodnota, Excel zobrazí hodnotu, ktorá „mala“ byť v zozname.

V akých prípadoch je to potrebné? Napríklad, ak sa hlásite na univerzitný program, ktorý prijíma iba študentov v rozsahu 60 percentilov. A musíte zistiť, aké priemerné skóre zodpovedá 60%. Pri pohľade na zoznam vidíme, že 3,22 je 59 % a 3,25 je 62 %. Pomocou funkcie =PERCENTIL.INC zistíme presnú odpoveď.

Vzorec je zostavený takto: =PERCENTILE.INC(pole, percentuálne hodnotenie)

  • pole(pole) je rozsah buniek, ktoré tvoria zoznam (ako v predchádzajúcom príklade, B3:B37)
  • hodnosť(poradie) je percento (alebo desatinné číslo od 0 do 1 vrátane)

Rovnako ako pri funkcii hodnotenia percent, môžete použiť funkciu =PERCENTILE.EXC, ktorá vylučuje prvú a poslednú hodnotu poľa, ale v našom prípade to nie je potrebné.

Krok 1

Vyberte bunku pod zoznamom B39.

Krok 2

Prilepte do nej nasledujúci vzorec: =PERCENTILE.INC(B3:B37,60%)

Keďže nepotrebujeme automatické dopĺňanie, nie je potrebné, aby bolo pole nemenné.

Krok 3

Na páse s nástrojmi raz stlačte kláves Znížiť desatinné číslo(Decrease Places) na zaokrúhlenie čísla na dve desatinné miesta.

Výsledkom je, že v tomto poli bude 60. percentil mať priemerné skóre 3,23. Teraz viete, aké známky sú potrebné na prijatie do programu.

Výsledok percentilovej funkcie

Záver

Percentá nie sú vôbec zložité a Excel ich počíta podľa rovnakých pravidiel matematiky, aké by ste použili, keby ste počítali na papieri. Excel tiež používa spoločné poradie operácií, keď váš vzorec zahŕňa sčítanie, odčítanie a násobenie:

  1. Zátvorky
  2. Exponent
  3. Násobenie
  4. divízie
  5. Doplnenie
  6. Odčítanie

Pamätám si túto objednávku pomocou mnemotechnickej frázy: Sk azal St epan: Myseľ ora, D Ahoj DC ovim IN ora.

V súčasnosti je pomerne populárny tabuľkový editor Microsoft Office Excel. S jeho pomocou môžete vykonávať rôzne matematické výpočty, stačí si vybrať správny vzorec a určiť formát hodnôt. Na to, ako správne skladať vzorce, sa pozrieme v tomto článku. Budete potrebovať:

- Osobný počítač;

— Program Microsoft Office Excel (objednajte si ho u nás so zľavou!).

Inštrukcie

  1. Každý vzorec zadaný do tabuľky MO Excel musí začínať znakom rovnosti. Pomocou tohto znaku program rozpozná zadané informácie ako vzorec a nie ako hodnotu tabuľky.
  • Na definovanie matematických operácií použite nasledujúce symboly:
  • — „/“ — operácia delenia;
  • — „*“ — operácia násobenia;
  • — „^“ — pozdvihnutie na určitú moc
  • — „+“ – operácia pridávania;
  • — „-“ — operácia odčítania.

Ak potrebujete vypočítať výsledok výpočtu 3 vynásobený 2 a výsledok na druhú, vzorec by mal mať nasledujúci tvar: =(3*2)^2. Po zadaní vzorca stlačte kláves Enter, Excel zobrazí výsledok vo vybranej bunke.

3. Ak potrebujete do vzorca zahrnúť údaje z aktuálnej tabuľky, vložte do vzorca odkaz na bunku s týmito hodnotami. Ak potrebujete vypočítať hodnoty vynásobenia čísla, ktoré je v bunke A1, číslom z bunky A2, vzorec by mal vyzerať takto: =A1*A2. Ak chcete použiť odkaz, stačí umiestniť kurzor na požadované miesto vo vzorci a kliknúť na požadovanú bunku.


4. Pre zložitejšie výpočty poskytuje editor možnosť vloženia funkcie. Ak ho chcete použiť, vyberte nástroj „Sprievodca funkciou“. Ak potrebujete vypočítať priemer v konkrétnych bunkách, zadajte znamienko rovnosti do bunky, kde sa má zobraziť výsledok. Potom kliknite na ikonu „Vložiť funkciu“, nachádza sa v blízkosti vstupného poľa vzorca.


6. Kliknite na tlačidlo OK. Zobrazí sa okno s nastaveniami pre túto funkciu, budete musieť zadať rozsah buniek na výpočet. Ak vyberiete inú funkciu, nastavenia pre ňu sa budú líšiť.

7. Kliknite na OK a výsledok výpočtu funkcie sa zobrazí v určitej bunke.

Toto sú len základné pojmy o možnostiach Excelu a v budúcnosti ich budete môcť študovať hlbšie.

Video: Výpočty v Exceli



 

Môže byť užitočné prečítať si: