Vzorce poľa – pokyny a príklady

Vzorce poľa – pokyny a príklady

Ak sa chcete stať pokročilým používateľom Excelu, musíte vedieť, ako sa používajú vzorce polí umožňujúce vykonávanie výpočtov, ktoré sa nedajú urobiť obyčajnými vzorcami. Nasledujúci článok vychádza zo série článkov Excel Power User (Pokročilý používateľ programu Excel), ktoré napísal Colin Wilcox, a zo 14. a 15. kapitoly knihy Excel 2002 Formulas (Vzorce v Exceli 2002), ktorú napísal John Walkenbach – nositeľ ocenenia MVP (Most Valuable Professional) pre Excel.

Informácie o vzorcoch poľa

Vzorce poľa sú niekedy označované ako vzorce CSE, pretože sa na ich zadávanie používa namiesto stlačenia klávesu Enter kombinácia klávesov Ctrl + Shift + Enter.

Prečo používať vzorce poľa?

Ak máte skúsenosti s používaním vzorcov v programe Excel, viete, že pomocou nich môžete vykonávať niektoré pomerne zložité operácie. Môžete napríklad vypočítať celkové náklady na pôžičku za určitý počet rokov. Vzorce poľa slúžia na vykonávanie komplexných úloh, napríklad:

  • spočítanie znakov, ktoré obsahuje rozsah buniek,

  • sčítanie iba tých čísel, ktoré vyhovujú určitým podmienkam, napríklad najnižších hodnôt v rozsahu alebo čísel, ktoré sa nachádzajú medzi dolnou a hornou hranicou,

  • sčítanie každej n-tej hodnoty v rozsahu hodnôt.

Stručný úvod k poliam a vzorcom poľa

Vzorec poľa je vzorec, ktorým môžete vykonať viacero výpočtov pre položky v určitom poli. Poľom môžu byť hodnoty v riadku, v stĺpci alebo kombinácia riadkov, stĺpcov a hodnôt. Vzorce poľa môžu vrátiť viacero výsledkov alebo iba jeden výsledok. Vzorec poľa môžete napríklad umiestniť do rozsahu buniek a použiť ho na výpočet stĺpca alebo riadka medzisúčtov. Môžete ho tiež umiestniť do jednej bunky a vypočítať jednu hodnotu. Vzorec poľa, ktorý sa nachádza vo viacerých bunkách, sa nazýva vzorec poľa s viacerými bunkami. Vzorec poľa v jednej bunke sa nazýva vzorec poľa s jednou bunkou.

V ďalšej časti sú uvedené príklady, ako sa vytvárajú vzorce poľa s jednou a viacerými bunkami.

Vyskúšajte si to.

V tomto cvičení je uvedený spôsob, ako sa používajú vzorce poľa s jednou a viacerými bunkami na výpočet množiny výsledkov predaja. V prvých krokoch sa použije vzorec s viacerými bunkami na výpočet medzisúčtov a v druhej skupine krokov vzorec s jednou bunkou na výpočet celkového súčtu.

Vzorec poľa s viacerými bunkami

Napriek tomu, že zošit vložený do prehliadača obsahuje vzorové údaje, nemôžete v ňom vytvárať alebo meniť vzorce poľa. Dá sa to len v programe Excel. Vo vloženom zošite môžete vidieť výsledky a vysvetlenia fungovania daného vzorca poľa, ale na to, aby ste vzorce poľa mohli skutočne využiť, budete musieť zobraziť zošit v programe Excel.

Vytvorenie vzorca poľa s viacerými bunkami

  1. Skopírujte celú nižšie uvedenú tabuľku a prilepte ju do bunky A1 v prázdnom excelovom hárku.

    Predajca

    Typ
    auta

    Počet
    predaných kusov

    Jednotková
    cena

    Celkový
    predaj

    Kollár

    Sedan

    5

    33 000

    Kupé

    4

    37 000

    Rybárik

    Sedan

    6

    24 000

    Kupé

    8

    21 000

    Kubovčík

    Sedan

    3

    29 000

    Kupé

    1

    31 000

    Klčo

    Sedan

    9

    24 000

    Kupé

    5

    37 000

    Grešák

    Sedan

    6

    33 000

    Kupé

    8

    31 000

    Vzorec (celkový súčet)

    Celkový súčet

    =SUM(C2:C11*D2:D11)

    =SUM(C2:C11*D2:D11)

  2. Ak chcete zobraziť celkový predaj kupé a sedanov pre jednotlivých predajcov, vyberte rozsah buniek E2:E11, zadajte vzorec =C2:C11*D2:D11 a stlačte kombináciu klávesov Ctrl + Shift + Enter.

  3. Ak chcete zobraziť celkový súčet predaja, vyberte bunku F11, zadajte vzorec =SUM(C2:C11*D2:D11) a stlačte kombináciu klávesov Ctrl + Shift + Enter.

Tento zošit môžete stiahnuť kliknutím na zelené tlačidlo Excel na čiernom paneli v dolnej časti zošita. Potom môžete súbor otvoriť v Exceli, vybrať bunky obsahujúce vzorce poľa a aktivovať ich kombináciou klávesov Ctrl + Shift + Enter.

Ak pracujete v Exceli, uistite sa, že je aktívny Hárok1 a potom vyberte bunky E2:E11. Stlačte kláves F2 a zadajte vzorec =C2:C11*D2:D11 do aktuálnej bunky E2. Keď stlačíte kláves Enter, zistíte, že sa vloží vzorec len do bunky E2 a zobrazí hodnotu 165000. Po napísaní vzorca stlačte namiesto samotného klávesu Enter kombináciu klávesov Ctrl + Shift + Enter. Teraz sa výsledky zobrazia v bunkách E2:E11. Všimnite si, že v riadku vzorcov sa vzorec zobrazí ako: {=C2:C11*D2:D11}. Podľa toho zistíte, že to je vzorec poľa, ako je to znázornené v nasledovnej tabuľke.

Excel uzavrie vzorec zloženými zátvorkami ({ }) a umiestni inštanciu vzorca do všetkých buniek vybratého rozsahu. Deje sa to veľmi rýchlo, takže v stĺpci E uvidíte hodnotu celkového predaja všetkých typov áut pre všetkých predajcov. Ak vyberiete stĺpec E2, potom E3, E4 atď., zobrazí sa ten istý vzorec:{=C2:C11*D2:D11}.

Súčty v stĺpci E sú vypočítavané vzorcom poľa

Vytvorenie vzorca poľa s jednou bunkou

Do bunky F10 v zošite zadajte nasledujúci vzorec a stlačte kombináciu klávesov Ctrl + Shift + Enter:

=SUM(C2:C11*D2:D11)

V tomto prípade sa v programe Excel vynásobia hodnoty v poli (rozsah buniek C2 až D11) a potom sa použije funkcia SUM na ich sčítanie. Výsledkom je celkový súčet predaja s hodnotou 1 590 000 $. Tento príklad ilustruje, ako môže byť tento vzorec užitočný. Predpokladajme napríklad, že máte 1 000 riadkov údajov. Vytvorením vzorca poľa v jednej bunke môžete sčítať všetky alebo len časť z týchto údajov a nemusíte potiahnuť vzorec nadol cez 1 000 riadkoch.

Všimnite si tiež, že vzorec s jednou bunkou (v bunke G11) je úplne nezávislý od vzorca s viacerými bunkami (vzorec v bunkách E2 až E11). Poukazuje to na ďalšiu výhodu používania vzorcov polí – na flexibilitu. Môžete zmeniť vzorce v stĺpci E alebo odstrániť tento stĺpec, pričom vzorec v bunke G11 to neovplyvní.

Medzi výhody vzorcov poľa tiež patrí:

  • Konzistentnosť    Ak kliknite na bunku E2 alebo ktorúkoľvek bunku pod ňou, zobrazí sa rovnaký vzorec, čo môže pomôcť zabezpečiť väčšiu presnosť.

  • Bezpečnosť    Súčasť vzorca poľa s viacerými bunkami nie je možné prepísať. Kliknite napríklad na bunku E3 a stlačte kláves Delete. Musíte buď vybrať celý rozsah buniek (E2 až E11) a zmeniť vzorec pre celé pole, alebo nechať pole bez zmeny. Ďalším bezpečnostným opatrením je, že na potvrdenie zmeny vzorca je potrebné stlačiť kombináciu klávesov Ctrl + Shift + Enter.

  • Menšia veľkosť súborov    Často je možné použiť jeden vzorec poľa a nemusí sa tak použiť viacero vzorcov medzivýpočtov. Napríklad v tomto zošite sa používa jeden vzorec poľa na výpočet výsledkov v stĺpci E. Ak by ste použili štandardné vzorce (napríklad =C2*D2, C3*D3, C4*D4…), museli by ste na dosiahnutie rovnakého výsledku použiť 11 rôznych vzorcov.

Syntax vzorca poľa

Vo vzorcoch poľa sa vo všeobecnosti používa štandardná syntax vzorcov. Všetky začínajú znamienkom rovnosti (=) a môžete v nich použiť väčšinu vstavaných funkcií programu Excel. Kľúčový rozdiel v používaní vzorca poľa spočíva v tom, že na zadanie vzorca je potrebné stlačiť klávesy Ctrl+Shift+Enter. Keď tak spravíte, program Excel uzavrie vzorec poľa zloženými zátvorkami. Ak tieto zátvorky zadáte manuálne, vzorec sa skonvertuje na textový reťazec a nebude funkčný.

Funkcie poľa sú skutočne efektívnym spôsobom, ako vytvoriť zložitý vzorec. Vzorec poľa =SUM(C2:C11*D2:D11) spĺňa rovnakú funkciu ako vzorec =SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Zadávanie a zmena vzorcov poľa

Dôležité    Na zadanie alebo úpravu vzorca poľa je potrebné stlačiť kombináciu klávesov Ctrl + Shift + Enter. Toto sa vzťahuje na vzorce s jednou bunkou aj s viacerými bunkami.

Pri práci so vzorcami s viacerými bunkami je potrebné dodržiavať aj tieto pravidlá:

  • Rozsah buniek, ktoré majú obsahovať výsledky, vyberte pred zadaním vzorca. Spravili ste tak, keď ste vytvorili vzorec poľa s viacerými bunkami a vybrali bunky E2 až E11.

  • Vo vzorci poľa nie je možné zmeniť obsah jednej bunky. Ak to chcete vyskúšať, vyberte bunku E3 v zošite a stlačte kláves Delete. Program Excel zobrazí správu o tom, že nie je možné zmeniť časť poľa.

  • Môžete presunúť alebo odstrániť celý vzorec poľa, ale nie jeho časť. Inak povedané, ak chcete zmenšiť vzorec poľa, musíte najskôr odstrániť existujúci vzorec a potom začať znova.

  • Ak chcete odstrániť vzorec poľa, vyberte celý vzorec (napríklad =C2:C11*D2:D11), stlačte kláves Delete a potom stlačte kombináciu klávesov Ctrl + Shift + Enter.

  • Do vzorca poľa s viacerými bunkami nie je možné vložiť prázdne bunky, ani z neho bunky odstrániť.

Rozbalenie vzorca poľa

Niekedy môže byť potrebné rozbaliť vzorec poľa. Tento proces nie je zložitý, ale musíte mať na pamäti pravidlá uvedené v predchádzajúcej časti.

V tomto hárku sme do riadkov 12 až 17 pridali ďalšie riadky s údajmi o predaji. Teraz chceme aktualizovať vzorce poľa, aby zahŕňali aj tieto dodatočné riadky.

Tento postup je potrebné vykonať v Exceli v počítači (potom, čo si zošit stiahnete do počítača).

Rozbalenie vzorca poľa

  1. Skopírujte celú tabuľku do bunky A1 v excelovom hárku.

    Predajca

    Typ
    auta

    Počet
    predaných kusov

    Jednotková
    cena

    Celkový
    predaj

    Kollár

    Sedan

    5

    33 000

    165 000

    Kupé

    4

    37 000

    148 000

    Rybárik

    Sedan

    6

    24 000

    144 000

    Kupé

    8

    21 000

    168 000

    Kubovčík

    Sedan

    3

    29 000

    87 000

    Kupé

    1

    31 000

    31 000

    Klčo

    Sedan

    9

    24 000

    216 000

    Kupé

    5

    37 000

    185 000

    Grešák

    Sedan

    6

    33 000

    198 000

    Kupé

    8

    31 000

    248 000

    Toth

    Sedan

    2

    27 000

    Kupé

    3

    30 000

    Wang

    Sedan

    4

    22 000

    Kupé

    1

    41 000

    Young

    Sedan

    5

    32 000

    Kupé

    3

    36 000

    Celkový súčet

  2. Vyberte bunku E18, zadajte vzorec pre Celkový súčet =SUM(C2:C17*D2:D17) do bunky A20 a stlačte kombináciu klávesov Ctrl + Shift + Enter.
    Výsledkom by malo byť 2 131 000.

  3. Vyberte rozsah buniek, ktorý obsahuje aktuálny vzorec poľa (E2:E11), a prázdne bunky (E12:E17), ktoré sa nachádzajú vedľa nových údajov. Inak povedané, vyberte bunky E2:E17.

  4. Stlačením klávesu F2 prejdite do režimu úprav.

  5. V riadku vzorcov zmeňte hodnotu C11 na C17, hodnotu D11 zmeňte na D17 a stlačte kombináciu klávesov Ctrl + Shift + Enter.
    V Exceli sa aktualizuje vzorec v bunkách E2 až E11 a táto inštancia vzorca sa umiestni do nových buniek E12 až E17.

  6. Zadajte vzorec poľa = SUM(C2:C17*D2*D17) do bunky F17 tak, aby odkazoval na bunky od riadku 2 po riadok 17, a stlačením kombinácie klávesov Ctrl + Shift + Enter zadajte vzorec poľa.
    Nový celkový súčet by teraz mal byť 2 131 000.

Nevýhody používania vzorcov poľa

Vzorce poľa sú skvelé, ale tiež majú niekoľko nevýhod:

  • Môžete zabudnúť stlačiť klávesy Ctrl + Shift + Enter. Môže sa to stať aj najskúsenejším používateľom Excelu. Nezabudnite, že túto kombináciu klávesov musíte použiť pri každom zadaní alebo úprave vzorca poľa.

  • Ďalší používatelia zošita nemusia vzorcom rozumieť. Vzorce poľa väčšinou nie sú v hárku vysvetlené, takže ak vaše zošity budú upravovať iné osoby, buď vzorce poľa nepoužite, alebo sa uistite, že títo používatelia vzorce polí poznajú a v prípade potreby vedia, ako ich meniť.

  • Veľké vzorce poľa môžu v závislosti od rýchlosti spracovania a pamäte počítača spomaliť výpočty.

Na začiatok stránky

Informácie o konštantách poľa

Konštanty poľa sú súčasťou vzorcov poľa. Konštanty poľa môžete vytvoriť zadaním zoznamu položiek a uzavretím tohto zoznamu zloženými zátvorkami ({ }), napríklad:

={1;2;3;4;5}

Už určite viete, že pri vytváraní vzorcov poľa musíte stlačiť kombináciu klávesov Ctrl + Shift + Enter. Keďže konštanty poľa sú súčasťou vzorcov poľa, konštanty sa uzatvárajú zloženými zátvorkami manuálne. Celý vzorec potom zadáte stlačením kombinácie klávesov Ctrl + Shift + Enter.

Ak oddelíte položky bodkočiarkami, vytvorí sa vodorovné pole (riadok). Ak oddelíte položky zvislými čiarami, vytvorí sa zvislé pole (stĺpec). Ak chcete vytvoriť dvojrozmerné pole, oddeľte položky v každom riadku bodkočiarkami a každý riadok oddeľte zvislou čiarou.

Pole v jednom riadku: {1;2;3;4}. Pole v jednom stĺpci: {1|2|3|4}. Pole s dvoma riadkami a dvoma stĺpcami: {1;2;3;4|5;6;7;8}. V poli s dvoma riadkami čísla 1, 2, 3 a 4 označujú prvý riadok, 5, 6, 7 a 8 označujú druhý. Zvislá čiara oddeľuje dva riadky medzi číslami 4 a 5.

Podobne ako pri vzorcoch poľa, aj konštanty poľa môžete použiť vo väčšine vstavaných funkcií, ktoré sú v programe Excel k dispozícii. V nasledujúcej časti je uvedený spôsob, ako sa vytvárajú jednotlivé typy konštánt a ako sa tieto konštanty používajú vo funkciách programu Excel.

Na začiatok stránky

Vytvorenie jednorozmerných a dvojrozmerných konštánt

V nasledujúcom postupe si vyskúšate vytvorenie vodorovných, zvislých a dvojrozmerných konštánt.

Vytvorenie vodorovnej konštanty

  1. Použite zošit z predchádzajúceho príkladu alebo vytvorte nový zošit.

  2. Vyberte bunky A1 až E1.

  3. Do riadka vzorcov zadajte nasledujúci vzorec a stlačte kombináciu klávesov Ctrl + Shift + Enter:

    ={1;2;3;4;5}

    V tomto prípade by ste mali zadať ľavú a pravú zloženú zátvorku ({ }).

    Zobrazí sa nasledujúci výsledok.

    Vodorovná konštanta poľa vo vzorci

Vytvorenie zvislej konštanty

  1. Vyberte v zošite päť buniek v stĺpci.

  2. Do riadka vzorcov zadajte nasledujúci vzorec a stlačte kombináciu klávesov Ctrl + Shift + Enter:

    ={1|2|3|4|5}

    Zobrazí sa nasledujúci výsledok.

    Zvislá konštanta poľa vo vzorci poľa

Vytvorenie dvojrozmernej konštanty

  1. Vyberte v zošite blok buniek so šírkou štyri stĺpce a výškou tri riadky.

  2. Do riadka vzorcov zadajte nasledujúci vzorec a stlačte kombináciu klávesov Ctrl + Shift + Enter:

    ={1;2;3;4|5;6;7;8|9;10;11;12}

    Zobrazí sa nasledujúci výsledok:

    Dvojrozmerná konštanta poľa vo vzorci poľa

Použitie konštánt vo vzorcoch

Nasleduje jednoduchý príklad, kde sú použité konštanty:

  1. Vytvorte nový hárok v ukážkovom zošite.

  2. Do bunky A1 zadajte číslo 3, číslo 4 zadajte do bunky B1, číslo 5 do bunky C1, číslo 6 do bunky D1 a číslo 7 do bunky E1.

  3. Do bunky A3 zadajte nasledujúci vzorec a stlačte kombináciu klávesov Ctrl + Shift + Enter:

    =SUM(A1:E1*{1;2;3;4;5})

    Všimnite si, že program Excel uzavrie konštantu ďalšími zátvorkami, pretože ste ju zadali ako vzorec poľa.

    Vzorec poľa s konštantou poľa

    V bunke A3 sa zobrazí hodnota 85.

V nasledujúcej časti je vysvetlené, ako vzorec funguje.

Syntax konštanty poľa

Vzorec, ktorý ste práve použili, obsahuje niekoľko častí.

Syntax vzorca poľa s konštantou poľa

1. Funkcia

2. Uložené pole

3. Operátor

4. Konštanta poľa

Posledným prvkom vo vnútri zátvoriek je konštanta poľa: {1;2;3;4;5}. Majte na pamäti, že program Excel konštanty poľa zloženými zátvorkami neuzatvorí – zadávate ich vy. Nezabudnite tiež po pridaní konštanty do vzorca poľa zadať vzorec stlačením kombinácie klávesov Ctrl + Shift + Enter.

Keďže v programe Excel sa najskôr vykonávajú operácie pre výrazy uzavreté zátvorkami, ďalšími dvoma spracovanými prvkami sú hodnoty uložené v zošite (A1:E1) a operátor. V tomto bode vzorec vynásobí hodnoty uloženého poľa príslušnými hodnotami konštanty. Predstavuje to ekvivalent vzorca:

=SUM(A1*1;B1*2;C1*3;D1*4;E1*5)

Napokon sa funkciou SUM spočítajú hodnoty a v bunke A3 sa zobrazí hodnota 85:

Ak sa chcete vyhnúť použitiu uloženého poľa a chcete zachovať celú operáciu v pamäti, nahraďte uložené pole inou konštantou poľa:

=SUM({3;4;5;6;7}*{1;2;3;4;5})

Ak to chcete vyskúšať, skopírujte funkciu, vyberte v zošite prázdnu bunku, prilepte vzorec do riadka vzorcov a stlačte kombináciu klávesov Ctrl + Shift + Enter. Zobrazí sa rovnaký výsledok ako v predchádzajúcom cvičení, kde ste použili vzorec poľa:

=SUM(A1:E1*{1;2;3;4;5})

Prvky, ktoré možno používať v konštantách

Konštanty poľa môžu obsahovať čísla, text, logické hodnoty (ako sú TRUE a FALSE) a chybové hodnoty (ako je #NEDOSTUPNÝ). Môžete použiť čísla v celočíselnom, desatinnom alebo vedeckom formáte. Ak použijete text, musíte ho uzavrieť úvodzovkami (").

Konštanty poľa nemôžu obsahovať ďalšie polia, vzorce ani funkcie. Inak povedané, môžu obsahovať iba text a čísla, ktoré sú oddelené bodkočiarkami a zvislými čiarami. Ak zadáte vzorec napríklad {1;2;A1:D4} alebo {1;2;SUM(Q2:Z8)}, zobrazí sa upozorňujúce hlásenie. Numerické hodnoty tiež nemôžu obsahovať znak percenta, znak dolára, čiarky ani zátvorky.

Pomenovanie konštánt poľa

Medzi najlepšie spôsoby, ako používať konštanty poľa, je ich pomenovať. Pomenované konštanty sa dajú oveľa ľahšie používať a môžu skryť zložitosť vzorcov poľa pre iných používateľov. Ak chcete pomenovať konštantu poľa a použiť ju vo vzorci, postupujte nasledovne:

  1. Na karte Vzorce v skupine Definované názvy kliknite na položku Definovať názov.
    Zobrazí sa dialógové okno Nový názov.

  2. Do poľa Názov zadajte reťazec Kvartál1.

  3. Do poľa Odkaz na zadajte nasledujúcu konštantu (nezabudnite manuálne zadať zložené zátvorky):

    ={"Január";"Február";"Marec"}

    Obsah dialógového okna by mal teraz vyzerať takto:

    Dialógové okno Úprava názvu so vzorcom

  4. Kliknite na tlačidlo OK and vyberte riadok s troma prázdnymi bunkami.

  5. Zadajte nasledujúci vzorec a stlačte kombináciu klávesov Ctrl + Shift + Enter.

    =Kvartál1

    Zobrazí sa nasledujúci výsledok.

    Pomenované pole zadané ako vzorec

Ak použijete pomenovanú konštantu ako vzorec poľa, nezabudnite zadať znamienko rovnosti. Ak tak nespravíte, v programe Excel sa takéto pole interpretuje ako textový reťazec a vzorec nebude fungovať podľa očakávaní. Majte tiež na pamäti, že môžete použiť kombináciu textu a čísel.

Riešenie problémov s konštantami poľa

Ak konštanty poľa nefungujú, zamerajte sa na nasledujúce problémy:

  • Niektoré prvky možno nie sú oddelené správnymi znakmi. Ak vynecháte bodkočiarku alebo zvislú čiaru, prípadne ju umiestnite na nesprávne miesto, konštanta poľa sa nemusí vytvoriť správne alebo sa môže zobraziť upozorňujúce hlásenie.

  • Možno ste vybrali rozsah buniek, ktorý nezodpovedá počtu prvkov v konštante. Ak napríklad vyberiete v stĺpci šesť buniek pre konštantu s piatimi bunkami, v prázdnej bunke sa zobrazí chybová hodnota #NEDOSTUPNÝ. Ak naopak vyberiete málo buniek, program Excel vynechá hodnoty, ktoré nemajú zodpovedajúcu bunku.

Používanie konštánt poľa

V nasledujúcich príkladoch je uvedených niekoľko spôsobov, ako možno použiť konštanty poľa vo vzorcoch poľa. V niektorých príkladoch sa používa funkcia TRANSPOSE na konvertovanie riadkov na stĺpce a naopak.

Vynásobenie všetkých položiek v poli

  1. Vytvorte nový hárok a potom blok prázdnych buniek so šírkou štyri stĺpce a výškou tri riadky.

  2. Zadajte nasledujúci vzorec a stlačte kombináciu klávesov Ctrl + Shift + Enter:

    ={1;2;3;4|5;6;7;8|9;10;11;12}*2

Umocnenie položiek v poli

  1. Vyberte blok prázdnych buniek so šírkou štyri stĺpce a výškou tri riadky.

  2. Zadajte nasledujúci vzorec poľa a stlačte kombináciu klávesov Ctrl + Shift + Enter:

    ={1;2;3;4|5;6;7;8|9;10;11;12}*{1;2;3;4|5;6;7;8|9;10;11;12}

    Inou možnosťou je zadať nasledujúci vzorec, kde sa používa operátor ^(znak vsuvky):

    ={1;2;3;4|5;6;7;8|9;10;11;12}^2

Transponovanie jednorozmerného riadka

  1. Vyberte v stĺpci päť prázdnych buniek.

  2. Zadajte nasledujúci vzorec a stlačte kombináciu klávesov Ctrl + Shift + Enter:

    =TRANSPOSE({1;2;3;4;5})

Aj keď ste zadali vodorovnú konštantu poľa, funkciou TRANSPOSE sa konštanta poľa skonvertuje na stĺpec.

Transponovanie jednorozmerného stĺpca

  1. Vyberte v riadku päť prázdnych buniek.

  2. Zadajte nasledujúci vzorec a stlačte kombináciu klávesov Ctrl + Shift + Enter:

    =TRANSPOSE({1|2|3|4|5})

Aj keď ste zadali zvislú konštantu poľa, funkciou TRANSPOSE sa konštanta skonvertuje na riadok.

Transponovanie dvojrozmernej konštanty

  1. Vyberte blok buniek so šírkou tri stĺpe a výškou štyri riadky.

  2. Zadajte nasledujúcu konštantu a stlačte kombináciu klávesov Ctrl + Shift + Enter:

    =TRANSPOSE({1;2;3;4|5;6;7;8|9;10;11;12})

Funkciou TRANSPOSE sa skonvertujú všetky riadky na stĺpce.

Na začiatok stránky

Používanie základných vzorcov poľa

Táto časť obsahuje príklady základných vzorcov poľa.

Vytvorenie polí a konštánt polí z existujúcich hodnôt

V nasledujúcom príklade je vysvetlený spôsob, akým sa používajú vzorce poľa na vytvorenie prepojení medzi rozsahmi buniek v rôznych hárkoch. Obsahuje tiež postup na vytvorenie konštanty poľa z rovnakej množiny hodnôt.

Vytvorenie poľa z existujúcich hodnôt

  1. V excelovom hárku vyberte bunky v rozsahu C8:E10 a zadajte tento vzorec:

    ={10;20;30|40;50;60|70;80;90}

    Pred číslo 10 nezabudnite zadať znak {(ľavú zloženú zátvorku) a za číslo 90 zadajte znak }(pravú zloženú zátvorku), pretože vytvárate pole čísel.

  2. Stlačte kombináciu klávesov Ctrl + Shift + Enter, ktorou toto pole čísel zadáte do rozsahu buniek C8:E10 pomocou vzorca poľa.
    V hárku by mal rozsah C8 až E10 vyzerať takto:

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Vyberte rozsah buniek C1 až E3.

  4. Do riadka vzorcov zadajte nasledujúci vzorec a stlačte kombináciu klávesov Ctrl + Shift + Enter:

    =C8:E10

    V bunkách C1 až E3 sa zobrazí pole s rozsahom 3x3 bunky, pričom jeho hodnoty budú rovnaké ako tie, ktoré sa zobrazujú v bunkách C8 až E10.

Vytvorenie konštanty poľa z existujúcich hodnôt

  1. Vyberte bunky C1:C3 a stlačte kláves F2, aby sa prepol režim úprav.
    Vzorec poľa by mal stále byť = C8:E10.

  2. Stlačením klávesu F9 skonvertujte odkazy na bunky na hodnoty. Excel skonvertuje hodnoty na konštantu poľa. Vzorec by teraz mal byť ={10;20;30|40;50;60|70;80;90}, presne tak, ako v prípade buniek C8:E10.

  3. Stlačením kombinácie klávesov Ctrl + Shift + Enter zadajte konštantu poľa ako vzorec poľa.

Spočítanie znakov v rozsahu buniek

Nasledujúci príklad ilustruje spôsob, akým sa spočítajú znaky (vrátane medzier) v rozsahu buniek.

  1. Skopírujte celú tabuľku a vložte ju do do bunky A1.

  2. Vyberte bunku A9, stlačte kombináciu klávesov Ctrl + Shift + Enter a zobrazí sa celkový počet znakov v bunkách A2:A6 (66).

  3. Vyberte bunku A12, stlačte kombináciu klávesov Ctrl + Shift + Enter a zobrazí sa obsah najdlhšej bunky v rozsahu A2:A6 (bunka A3).

Údaje

Toto je

skupina buniek, ktoré

sa spájajú

a vytvárajú

jednu vetu.

Všetky znaky v rozsahu A2:A6

=SUM(LEN(A2:A6))

Obsah najdlhšej bunky (A3)

=INDEX(A2:A6;MATCH(MAX(LEN(A2:A6));LEN(A2:A6);0);1)

Nasledujúci vzorec použitý v bunke A9 spočíta celkový počet znakov (66) v bunkách A2 až A6.

=SUM(LEN(A2:A6))

V tomto prípade vráti funkcia LEN dĺžku všetkých textových reťazcov vo všetkých bunkách v rozsahu. Funkcia SUM potom tieto hodnoty spočíta a zobrazí výsledok (66) v bunke, ktorá obsahuje vzorec, čiže v bunke A9.

Vyhľadanie n najmenších hodnôt v rozsahu

Tento príklad ilustruje, ako vyhľadať tri najmenšie hodnoty v rozsahu buniek.

  1. Označte bunky A16 až A18.
    V týchto bunkách sa zobrazia výsledky, ktoré sa vypočítajú vzorcom poľa.

  2. Do riadka vzorcov zadajte nasledujúci vzorec a stlačte kombináciu klávesov Ctrl + Shift + Enter:

    =SMALL(A5:A14;{1|2|3})

V bunkách A16 až A18 sa zobrazia hodnoty 400, 475 a 500.

V tomto vzorci sa používa konštanta poľa na trojité vykonanie funkcie SMALL a vrátenie najmenšej (1), druhej najmenšej (2) a tretej najmenšej (3) hodnoty v poli, ktoré je definované bunkami A1:A10. Ak chcete vyhľadať ďalšie hodnoty, pridajte do konštanty ďalšie argumenty a ekvivalentný počet buniek výsledkov k rozsahu buniek A12:A14. V tomto vzorci môžete použiť aj ďalšie funkcie, ako je napríklad funkcia SUM alebo AVERAGE. Napríklad:

=SUM(SMALL(A 5 :A1 4 ;{1|2|3}))

=AVERAGE(SMALL(A 5 :A1 4 ;{1|2|3}))

Vyhľadanie n najväčších hodnôt v rozsahu

Ak chcete vyhľadať najväčšie hodnoty v rozsahu, môžete nahradiť funkciu SMALL funkciou LARGE. V nasledujúcom príklade sa navyše používajú funkcie ROW a INDIRECT.

  1. Vyberte bunky A1 až A3.

  2. Do riadka vzorcov zadajte nasledujúci vzorec a stlačte kombináciu klávesov Ctrl + Shift + Enter:

    =LARGE(A5:A14;ROW(INDIRECT("1:3")))

V bunkách A1 až A3 sa zobrazia hodnoty 3 200, 2 700 a 2 000.

Na tomto mieste môže byť užitočné dozvedieť sa niečo o funkciách ROW a INDIRECT. Funkcia ROW sa používa na vytvorenie poľa za sebou idúcich celých čísel. V cvičnom zošite vyberte desať prázdnych buniek stĺpca, do buniek A5 až A14 zadajte tento vzorec poľa a stlačte klávesy Ctrl+Shift+Enter:

=ROW(1:10)

Tento vzorec vytvorí stĺpec s desiatimi za sebou idúcimi celými číslami. Ak chcete vidieť potenciálny problém, vložte riadok nad rozsah, ktorý obsahuje vzorec poľa (čiže nad riadok 1). Program Excel prispôsobí odkazy na riadky a vzorec vygeneruje celé čísla od 2 do 11. Ak chcete odstrániť tento problém, zadajte do vzorca funkciu INDIRECT:

=ROW(INDIRECT("1:10"))

Vo funkcii INDIRECT sa používajú ako argumenty textové reťazce (bunky 1 až 10 sú preto uzavreté úvodzovkami). Program Excel neprispôsobí textové hodnoty po vložení riadkov ani pri inom premiestnení vzorca poľa. Výsledkom je, že funkciou ROW sa vždy vygeneruje požadované pole celých čísel.

Preskúmajme vyššie použitý vzorec – =LARGE(A5:A14;ROW(INDIRECT("1:3"))) – začínajúc od vnútorných po vonkajšie zátvorky: funkcia INDIRECT vráti množinu textových hodnôt, v tomto prípade hodnoty 1 až 3. Funkcia ROW vygeneruje stĺpcové pole s troma bunkami. Funkcia LARGE použije hodnoty v rozsahu buniek A5:A14 a vykoná sa trikrát, t. j. raz pre každý odkaz vrátený funkciou ROW. Do stĺpcového poľa s troma bunkami sa vrátia hodnoty 3 200, 2 700 a 2 000. Ak chcete vyhľadať ďalšie hodnoty, pridajte do funkcie INDIRECT väčší rozsah buniek.

V tomto vzorci môžete použiť aj ďalšie funkcie, napríklad SUM alebo AVERAGE.

Vyhľadanie najdlhšieho textového reťazca v rozsahu buniek

Tento vzorec je funkčný len v prípade, ak rozsah údajov obsahuje jeden stĺpec buniek. Do bunky A16 v hárku Hárok3 zadajte nasledujúci vzorec a stlačte kombináciu klávesov Ctrl + Shift + Enter:

=INDEX(A6:A9;MATCH(MAX(LEN(A6:A9));LEN(A6:A9);0);1)

V bunke A16 sa zobrazí text: Skupina buniek, ktorá.

Preskúmajme vzorec od vnútorných prvkov po vonkajšie. Funkcia LEN vráti dĺžky všetkých položiek v rozsahu buniek A6:A9. Funkcia MAX vypočíta, ktorá z týchto hodnôt je najvyššia, t. j. ktorý textový reťazec je najdlhší. V tomto prípade je to bunka A7.

Na tomto mieste je vzťah trochu zložitejší. Funkcia MATCH vypočíta relatívnu pozíciu bunky, ktorá obsahuje najdlhší textový reťazec. Na vykonanie tejto úlohy sú potrebné tri argumenty: hľadaná hodnota, hľadané pole a typ zhody. Funkcia MATCH vyhľadá v hľadanom poli hľadanú hodnotu. V tomto prípade predstavuje hľadanú hodnotu najdlhší textový reťazec:

(MAX(LEN( A6 : A9 ))

a tento reťazec sa nachádza v tomto poli:

LEN( A6:A9 )

Argument typu zhody je 0. Typ zhody môže mať hodnoty 1, 0 alebo -1. Ak zadáte hodnotu 1, funkcia MATCH vráti najväčšiu hodnotu, ktorá je menšia alebo rovnaká ako hľadaná hodnota. Ak zadáte hodnotu 0, funkcia MATCH vráti prvú hodnotu, ktorá sa presne zhoduje s hľadanou hodnotou. Ak zadáte hodnotu -1, funkcia MATCH vyhľadá najmenšiu hodnotu, ktorá je väčšia alebo rovnaká ako zadaná hľadaná hodnota. Ak typ zhody vynecháte, program Excel pracuje s hodnotou 1.

Nakoniec sa vo funkcii INDEX použijú nasledujúce argumenty: pole a číslo riadka a stĺpca v tomto poli. Rozsah buniek A6:A9 poskytuje pole, funkcia MATCH poskytuje adresu bunky a posledný argument (1) určuje, že hodnota pochádza z prvého stĺpca poľa.

Na začiatok stránky

Používanie zložitejších vzorcov poľa

Táto časť obsahuje príklady zložitejších vzorcov poľa.

Sčítanie rozsahu, ktorý obsahuje chybové hodnoty

Funkcia SUM v programe Excel nefunguje, ak sa pokúsite vypočítať súčet rozsahu, ktorý obsahuje chybové hodnoty, napríklad hodnoty #NEDOSTUPNÝ. Tento príklad popisuje, ako vypočítať súčet hodnôt v rozsahu s názvom Údaje, ktorý obsahuje chyby.

=SUM(IF(ISERROR(Údaje);"";Údaje))

Tento vzorec vytvorí nové pole, ktoré obsahuje pôvodné hodnoty bez chybových hodnôt. Začíname popisovať od vnútorných funkcií smerom von: funkcia ISERROR najskôr vyhľadá chyby v rozsahu buniek (Údaje). Funkcia IF vráti určitú hodnotu, ak sa pre zadanú podmienku vypočíta hodnota TRUE, a inú hodnotu, ak sa vypočíta hodnota FALSE. V tomto prípade sa vrátia prázdne reťazce ("") pre všetky chybové hodnoty, pretože sa pre ne vypočítala hodnota TRUE, a zvyšné hodnoty sa vrátia z rozsahu (Údaje), pretože sa pre ne vypočítala hodnota FALSE, čo znamená, že neobsahujú žiadne chybové hodnoty. Funkcia SUM potom vypočíta celkový súčet pre filtrované pole.

Spočítanie počtu chybových hodnôt v rozsahu

Vzorec v tomto príklade sa podobá predošlému vzorcu, ale chybové hodnoty v rozsahu s názvom Údaje neodfiltruje, ale vráti ich počet:

=SUM(IF(ISERROR(Údaje);1;0))

Týmto vzorcom sa vytvorí pole, ktoré obsahuje hodnotu 1 pre bunky, ktoré obsahujú chybové hodnoty, a hodnotu 0 pre bunky, ktoré neobsahujú chyby. Tento vzorec môžete zjednodušiť a dosiahnuť rovnaký výsledok odstránením tretieho argumentu pre funkciu IF:

=SUM(IF(ISERROR(Údaje);1))

Ak nezadáte argument, funkcia IF vráti hodnotu FALSE, ak bunka neobsahuje chybovú hodnotu. Vzorec môžete ešte viac zjednodušiť:

=SUM(IF(ISERROR(Údaje)*1))

Táto verzia funguje, pretože TRUE*1=1 a FALSE*1=0.

Súčet hodnôt na základe podmienok

Niekedy môže byť potrebné sčítať hodnoty na základe podmienok. Nasledujúcim vzorcom poľa sa napríklad sčítajú iba celé kladné čísla v rozsahu s názvom Predaj:

=SUM(IF(Predaj>0;Predaj))

Funkcia IF vytvorí pole kladných čísel a hodnôt FALSE. Funkcia SUM ignoruje hodnoty FALSE, pretože 0+0=0. Rozsah buniek použitý v tomto vzorci môže obsahovať ľubovoľný počet riadkov a stĺpcov.

Môžete tiež sčítať hodnoty, ktoré spĺňajú viac ako jednu podmienku. Nasledujúcim vzorcom poľa sa napríklad sčítajú hodnoty väčšie ako 0 a rovné alebo menšie ako 5:

=SUM((Predaj>0)*(Predaj<=5)*(Predaj))

Majte na pamäti, že vzorec vráti chybu, ak rozsah obsahuje bunky, ktoré neobsahujú číslo.

Môžete tiež vytvoriť vzorce poľa, ktoré používajú podmienku typu OR. Môžete napríklad sčítať hodnoty, ktoré sú menšie ako 5 a väčšie ako 15:

=SUM(IF((Predaj<5)+(Predaj>15);Predaj))

Funkcia IF vyhľadá hodnoty menšie ako 5 a väčšie ako 15 a potom sa tieto hodnoty zadajú do funkcie SUM.

Funkcie AND a OR nie je možné použiť priamo vo vzorcoch poľa, pretože tieto funkcie vrátia jeden výsledok, a to TRUE alebo FALSE, pričom funkcie poľa požadujú polia výsledkov. Tento problém môžete vyriešiť logikou uvedenou v predchádzajúcom vzorci. Inak povedané, vykonáte matematickú operáciu (napríklad sčítanie alebo násobenie) pre hodnoty, ktoré vyhovujú podmienke OR alebo AND.

Výpočet priemernej hodnoty s vylúčením nulových hodnôt

V tomto príklade je uvedený spôsob, ako odstrániť nuly z rozsahu, keď potrebujete vypočítať priemernú hodnotu v rozsahu. Vo vzorci sa používa rozsah údajov s názvom Predaj:

=AVERAGE(IF(Predaj<>0;Predaj))

Funkcia IF vytvorí pole nenulových hodnôt a tieto hodnoty sa potom zadajú do funkcie AVERAGE.

Spočítanie rozdielov medzi dvoma rozsahmi buniek

Nasledujúcim vzorcom poľa sa porovnajú hodnoty v dvoch rozsahoch buniek s názvami MojeÚdaje a VašeÚdaje a vráti sa počet rozdielov medzi nimi. Ak sú hodnoty týchto dvoch rozsahov rovnaké, vzorec vráti hodnotu 0. Ak chcete použiť tento vzorec, rozsahy buniek musia mať rovnakú veľkosť a rozmer (napríklad ak má bunka MojeÚdaje rozsah 3 riadky a 5 stĺpcov, bunka VašeÚdaje musí mať tiež rozsah 3 riadky a 5 stĺpcov):

=SUM(IF( MojeÚdaje =VašeÚdaje;0;1))

Týmto vzorcom sa vytvorí nové pole s rovnakou veľkosťou ako porovnávané rozsahy. Funkciou IF sa vyplní toto pole hodnotami 0 a 1 (0 pre nezhodné a 1 pre zhodné bunky). Funkcia SUM vráti súčet hodnôt poľa.

Tento vzorec môžete zjednodušiť nasledujúcim spôsobom:

=SUM(1*( MojeÚdaje <> VašeÚdaje ))

Podobne ako vzorec, ktorý počíta chybové hodnoty v rozsahu, je tento vzorec funkčný, pretože TRUE*1=1 a FALSE*1=0.

Vyhľadanie umiestnenia maximálnej hodnoty v rozsahu

Nasledujúci vzorec poľa vráti číslo riadka, kde sa v jednostĺpcovom rozsahu s názvom Údaje nachádza maximálna hodnota.

=MIN(IF(Údaje=MAX(Údaje);ROW(Údaje);""))

Pomocou funkcie IF sa vytvorí nové pole, ktoré zodpovedá rozsahu Údaje. Ak zodpovedajúca bunka obsahuje maximálnu hodnotu rozsahu, nové pole bude obsahovať číslo tohto riadka. V opačnom prípade bude pole obsahovať prázdny reťazec (""). Funkcia MIN použije nové pole ako druhý argument a vráti najmenšiu hodnotu, ktorá zodpovedá číslu riadka s maximálnou hodnotou v rozsahu Údaje. Ak rozsah Údaje obsahuje identické maximálne hodnoty, vzorec vráti riadok prvej hodnoty.

Ak chcete vrátiť aktuálnu adresu bunky s maximálnou hodnotou, použite nasledujúci vzorec:

=ADDRESS(MIN(IF(Údaje=MAX(Údaje);ROW(Údaje);""));COLUMN(Údaje))

Na začiatok stránky

Potrebujete ďalšiu pomoc?

Vždy sa môžete opýtať odborníka v komunite technikov pre Excel, získať podporu v rámci komunity lokality Answers alebo navrhnúť novú funkciu či vylepšenie na lokalite Excel User Voice.

Pozrite tiež

Prehľad vzorcov

Rozšírte svoje zručnosti
Preskúmať školenie
Buďte medzi prvými, ktorí získajú nové funkcie
Pridajte sa k insiderom pre Office

Boli tieto informácie užitočné?

Ďakujeme za vaše pripomienky!

Ďakujeme vám za pripomienky. Pravdepodobne vám pomôže, ak vás spojíme s pracovníkom podpory pre Office.

×