Prehľad vzorcov v Exceli
Oboznámte sa s vytváraním vzorcov a používaním vstavaných funkcií na vykonávanie výpočtov a riešenie problémov.
Dôležité: Vypočítané výsledky vzorcov a niektoré funkcie excelových hárkov sa v počítačoch s Windowsom používajúcich architektúru x86 alebo x86-64 a počítačoch s Windowsom RT používajúcich architektúru ARM môžu mierne líšiť. Ďalšie informácie o odlišnostiach.
Dôležité: V tomto článku sa venujeme funkcii XLOOKUP a funkcii VLOOKUP, ktoré sú podobné. Vyskúšajte novú funkciu XLOOKUP, vylepšenú verziu funkcie VLOOKUP, ktorá funguje v ľubovoľnom smere a predvolene vráti presné zhody, vďaka čomu sa používa jednoduchšie a pohodlnejšie ako predchádzajúca verzia.
Vytvorenie vzorca odkazujúceho na hodnoty v iných bunkách
-
Vyberte bunku.
-
Zadajte znamienko rovnosti =.
Poznámka: Vzorce v Exceli sa vždy začínajú znamienkom rovnosti.
-
Vyberte bunku alebo zadajte jej adresu do vybratej bunky.
-
Zadajte operátor. Ak chcete použiť napríklad odčítanie, zadajte operátor –.
-
Vyberte ďalšiu bunku alebo zadajte jej adresu do vybratej bunky.
-
Stlačte kláves Enter. Výsledok výpočtu sa zobrazí v bunke so vzorcom.
Zobraziť vzorec
Vzorec sa po zadaní do bunky zobrazuje aj v riadku vzorcov.
-
Ak chcete zobraziť vzorec v riadku vzorcov, vyberte bunku.
Zadanie vzorca obsahujúceho vstavanú funkciu
-
Vyberte prázdnu bunku.
-
Zadajte znamienko rovnosti = a potom zadajte funkciu. Ak chcete napríklad vypočítať celkový predaj, zadajte =SUM.
-
Zadajte ľavú zátvorku (.
-
Vyberte rozsah buniek a potom zadajte pravú zátvorku ).
-
Stlačením klávesu Enter zobrazte výsledok.
Stiahnutie zošita s kurzom o vzorcoch
Zostavili sme zošit Začíname so vzorcami, ktorý si môžete stiahnuť. Ak Excel ešte len začínate používať, alebo aj v prípade, že s ním už máte nejaké skúsenosti, v tomto kurze sa môžete oboznámiť s najbežnejšími excelovými vzorcami. Obsahuje príklady z reálneho sveta a užitočné vizuálne prvky, ktoré vám pomôžu naučiť sa používať funkcie Sum, Count, Average a Vlookup ako profesionál.
Podrobné informácie o vzorcoch
V jednotlivých častiach uvedených nižšie si môžete pozrieť ďalšie informácie o konkrétnych prvkoch vzorcov.
Vzorec môže obsahovať tieto súčasti: funkcie, odkazy, operátory a konštanty.
Časti vzorca
1. Funkcie: Funkcia PI() vráti hodnotu pí: 3,142...
2. Odkazy: Odkaz A2 vráti hodnotu bunky A2.
3. Konštanty: Číselné alebo textové hodnoty zadané priamo do vzorca, napríklad číslo 2.
4. Operátory: Operátor ^ (znak mocniny) umocní číslo na príslušnú mocninu a operátor * (hviezdička) vynásobí čísla.
Konštanta je hodnota, ktorá sa nevypočítava – vždy zostáva rovnaká. Napríklad dátum 9.10.2008, číslo 210 alebo text „Štvrťročné výnosy“ sú všetko konštanty. Konštantou nie je výraz ani hodnota, ktorá je výsledkom výrazu. Ak namiesto odkazov na bunky použijete vo vzorci konštanty (napríklad: =30+70+110), výsledok sa zmení, iba ak upravíte vzorec. Vo všeobecnosti je vhodné umiestniť konštanty do samostatných buniek, kde ich môžete podľa potreby zmeniť, a potom odkazovať na tieto bunky vo vzorci.
Odkaz definuje bunku alebo rozsah buniek v hárku a oznamuje Excelu, kde má hľadať hodnoty alebo údaje, ktoré chcete vo vzorci použiť. Pomocou odkazov možno v jednom vzorci použiť údaje uložené v rôznych častiach hárka, alebo možno hodnotu jednej bunky použiť vo viacerých vzorcoch. Odkazovať sa môže aj na bunky v iných hárkoch jedného zošita a na iné zošity. Odkazy na bunky v iných zošitoch sa nazývajú prepojenia alebo externé odkazy.
-
Štýl odkazu A1
Excel predvolene používa štýl odkazu A1, ktorý odkazuje na stĺpce označené písmenami (od A po XDF, celkovo 16 384 stĺpcov) a na riadky označené číslami (od 1 do 1 048 576). Písmená sa nazývajú nadpisy stĺpcov a čísla nadpisy riadkov. Ak chcete odkazovať na bunku, zadajte písmeno stĺpca a číslo riadka. Napríklad B2 odkazuje na bunku, ktorá sa nachádza v stĺpci B a riadku 2.
Odkaz na
Použite
Bunku v stĺpci A a v riadku 10
A10
Rozsah buniek v stĺpci A a v riadkoch od čísla 10 po číslo 20
A10:A20
Rozsah buniek v riadku číslo 15 a v stĺpcoch od písmena B po písmeno E
B15:E15
Všetky bunky v riadku číslo 5
5:5
Všetky bunky od riadka číslo 5 po riadok číslo 10
05:10:00
Všetky bunky v stĺpci H
H:H
Všetky bunky od stĺpca H po stĺpec J
H:J
Rozsah buniek od stĺpca A po stĺpec E a od riadka číslo 10 po riadok číslo 20
A10:E20
-
Vytvorenie odkazu na bunku alebo rozsah buniek v inom hárku toho istého zošita
V nasledujúcom príklade funkcia AVERAGE vypočíta priemernú hodnotu z rozsahu buniek B1:B10 v hárku s názvom Marketing v tom istom zošite.
1. Odkazuje sa na hárok s názvom Marketing
2. Odkazuje sa na rozsah buniek od B1 až po B10
3. Výkričník (!) oddeľuje odkaz na hárok od odkazu na rozsah buniek
Poznámka: Ak hárok, na ktorý sa odkazuje, obsahuje medzery alebo čísla, musíte pred názov hárka a zaň pridať apostrof ('), napríklad ='123'! A1 alebo ='Január Revenue'! A1.
-
Rozdiel medzi absolútnymi, relatívnymi a zmiešanými odkazmi
-
Relatívne odkazy. Relatívny odkaz vo vzorci, napríklad A1, je založený na relatívnej pozícii bunky obsahujúcej vzorec a bunky, na ktorú odkaz odkazuje. Ak sa zmení pozícia bunky obsahujúcej vzorec, zmení sa aj príslušný odkaz. V prípade skopírovania alebo vyplnenia vzorca do viacerých riadkov alebo stĺpcov sa odkaz automaticky upraví. Podľa predvoleného nastavenia sa v nových vzorcoch používajú relatívne odkazy. Ak napríklad skopírujete alebo vyplníte relatívny odkaz z bunky B2 do bunky B3, odkaz =A1 sa automaticky upraví na =A2.
Skopírovaný vzorec s relatívnym odkazom
-
Absolútne odkazy. Absolútny odkaz vo vzorci, napríklad $A$1, vždy odkazuje na bunku na určitom mieste. Ak sa zmení pozícia bunky obsahujúcej vzorec, absolútny odkaz zostane rovnaký. V prípade skopírovania alebo vyplnenia vzorca do viacerých riadkov alebo stĺpcov sa absolútny odkaz neupraví. V predvolenom nastavení sa v nových vzorcoch používajú relatívne odkazy, takže ich môže byť potrebné prepnúť na absolútne odkazy. Ak napríklad skopírujete alebo vyplníte absolútny odkaz z bunky B2 do bunky B3, odkaz zostane v oboch bunkách rovnaký: =$A$1.
Skopírovaný vzorec s absolútnym odkazom
-
Zmiešané odkazy. Zmiešaný odkaz má buď absolútny stĺpec a relatívny riadok alebo absolútny riadok a relatívny stĺpec. Absolútny odkaz na stĺpec má tvar $A1, $B1, atď. Absolútny odkaz na riadok má tvar A$1, B$1, atď. Ak sa zmení pozícia bunky obsahujúcej vzorec, relatívny odkaz sa zmení a absolútny odkaz zostane rovnaký. V prípade skopírovania alebo vyplnenia vzorca do viacerých riadkov alebo stĺpcov, sa relatívny odkaz automaticky upraví, absolútny odkaz sa neupraví. Ak napríklad skopírujete alebo vyplníte zmiešaný odkaz z bunky A2 do bunky B3, odkaz =A$1 sa upraví na =B$1.
Skopírovaný vzorec so zmiešaným odkazom
-
-
Priestorový štýl odkazu
Jednoduchšie odkazovanie viacerých hárkov Ak chcete analyzovať údaje v tej istej bunke alebo rozsahu buniek na viacerých hárkoch zošita, použite priestorový odkaz. Priestorový odkaz zahŕňa odkaz na bunku alebo rozsah, pred ktorým je rozsah názvov hárkov. Program Excel použije všetky hárky uložené medzi prvým a posledným názvom v odkaze. Napríklad pomocou vzorca =SUM(Hárok2:Hárok13!B5) sčíta všetky hodnoty v bunke B5 vo všetkých hárkoch v rozsahu Hárok2 až Hárok13.
-
Priestorové odkazy je možné použiť na odkazovanie na bunky v iných hárkoch, na definovanie názvov a vytváranie vzorcov pomocou nasledujúcich funkcií: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV.P, STDEV.S, STDEVA, STDEVPA, VAR.P, VAR.S, VARA a VARPA.
-
Priestorové odkazy nie je možné použiť vo vzorcoch poľa.
-
Priestorové odkazy nie je možné použiť s operátor prieniku (medzera), ani vo vzorcoch používajúcich implicitný prienik.
Čo sa stane pri presúvaní, kopírovaní, vkladaní alebo odstraňovaní hárkov Nasledujúce príklady vysvetľujú, čo sa stane pri presunutí, skopírovaní, vložení alebo odstránení hárkov, ktoré sú zahrnuté do priestorového odkazu. Pri príkladoch je použitý vzorec =SUM(Hárok2:Hárok6!A2:A5), ktorý počíta súčet buniek A2 až A5 v hárkoch 2 až 6.
-
Vloženie alebo skopírovanie. Ak medzi hárky 2 a 6 (v našom príklade krajné hárky) vložíte alebo skopírujete iné hárky, program Excel zahrnie do výpočtov všetky hodnoty v bunkách A2 až A5 v pridaných hárkoch.
-
Odstrániť Ak odstránite hárky, ktoré sa nachádzajú medzi hárkami 2 a 6, program Excel odstráni z výpočtu príslušné hodnoty.
-
Premiestniť Ak hárky, ktoré sa nachádzajú medzi hárkami 2 a 6, premiestnite mimo daného rozsahu hárkov, program Excel odstráni z výpočtu príslušné hodnoty.
-
Presunutie koncového bodu. Ak hárok 2 alebo 6 premiestnite na iné miesto v tom istom zošite, program Excel upraví výpočet tak, aby sa prispôsobil novému rozsahu hárkov medzi nimi.
-
Odstránenie koncového bodu. Ak odstránite hárky 2 a 6, program Excel upraví výpočet tak, aby sa prispôsobil rozsahu hárkov medzi nimi.
-
-
Štýl odkazu R1C1
V tomto štýle odkazu sú riadky aj stĺpce zošita číslované. Štýl odkazu R1C1 sa používa hlavne pri výpočtoch umiestnenia riadkov a stĺpcov v makrách. Pri štýle R1C1 udáva Excel umiestnenie bunky číslom riadka za písmenom R a číslom stĺpca za písmenom C.
Odkaz
Význam
R[-2]C
relatívny odkaz na bunku v tom istom stĺpci a o dva riadky vyššie
R[2]C[2]
Relatívny odkaz na bunku o dva riadky nižšie a o dva stĺpce doprava
R2C2
Absolútny odkaz na bunku v druhom riadku a druhom stĺpci
R[-1]
Relatívny odkaz na celý riadok nad aktívnou bunkou
Polomer
Absolútny odkaz na aktuálny riadok
Pri zaznamenávaní makra program Excel zaznamená niektoré príkazy použitím štýlu odkazu R1C1. Ak napríklad zaznamenáte príkaz, napríklad výberom tlačidla Automatický súčet na vloženie vzorca, ktorý pridá rozsah buniek, Excel zaznamená vzorec pomocou štýlu R1C1, nie štýlu A1, odkazov.
Štýl odkazu R1C1 môžete zapnúť alebo vypnúť začiarknutím alebo zrušením začiarknutia políčka Štýl odkazu R1C1 v sekcii Práca so vzorcami v kategórii Vzorce v dialógovom okne Možnosti. Ak chcete zobraziť toto dialógové okno, vyberte kartu Súbor .
Potrebujete ďalšiu pomoc?
Vždy sa môžete opýtať odborníka v komunite Excel Tech Community alebo získať podporu v komunitách.
Pozrite tiež
Prepínanie medzi relatívnymi, absolútnymi a zmiešanými odkazmi na funkcie
Použitie výpočtových operátorov vo vzorcoch Excelu
Poradie, v ktorom sa v programe Excel vykonávajú operácie vo vzorcoch
Použitie funkcií a vnorených funkcií vo vzorcoch Excelu
Definovanie a používanie názvov vo vzorcoch
Vzorce poľa – pokyny a príklady
Zabránenie vzniku nefunkčných vzorcov
Vyhľadanie a oprava chýb vo vzorcoch