Vzorce poľa – pokyny a príklady

V tomto cvičení sa dozviete, ako používať vzorce poľa s viacerými bunkami a s jednou bunkou na výpočet množiny údajov o predaji. Prvá množina krokov používa vzorec s viacerými bunkami na výpočet množiny medzisúčtov. Druhá množina používa vzorec s jednou bunkou na výpočet celkového súčtu.

• Vzorec poľa s viacerými bunkami

  

• Tu vypočítavame celkový predaj kupé a sedanov pre každého predajcu zadaním vzorca =F10:F19*G10:G19 do bunky H10.

  Po stlačení klávesu Enter sa výsledky zobrazia smerom nadol do buniek H10:H19. Všimnite si, že rozsah presahu je zvýraznený orámovaním, keď vyberiete ktorúkoľvek bunku v rozsahu presahu. Môžete si tiež všimnúť, že vzorce v bunkách H10:H19 sú zobrazené sivou farbou. Sú tu len na referenciu, takže ak chcete vzorec upraviť, budete musieť vybrať bunku H10, kde sa nachádza hlavný vzorec.

• Vzorec poľa s jednou bunkou

  

  Do bunky H20 vzorového zošita zadajte alebo skopírujte a prilepte vzorec =SUM(F10:F19*G10:G19) a potom stlačte kláves Enter.

  V tomto prípade sa v Exceli vynásobia hodnoty v poli (rozsah buniek F10 až G19) 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 H20) je úplne nezávislý od vzorca s viacerými bunkami (vzorec v bunkách H10 až H19). Poukazuje to na ďalšiu výhodu používania vzorcov polí – na flexibilitu. Môžete zmeniť ostatné vzorce v stĺpci H bez vplyvu na vzorec v bunke H20. Vhodné je tiež mať aj nezávislé súčty, ako sú tieto, pretože to pomáha overiť presnosť výsledkov.

• Medzi výhody vzorcov dynamického poľa tiež patria:

  • Konzistentnosť    Ak kliknite na ktorúkoľvek bunku od H10 smerom nadol, zobrazí sa rovnaký vzorec. Táto konzistentnosť 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 H11 a stlačte kláves Delete. Excel nezmení výstup poľa. Ak ho chcete zmeniť, musíte vybrať bunku v ľavom hornom rohu poľa alebo bunku H10.

  • 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 vo vzorke predaja vozidiel 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 =F10*G10, F11*G11, F12*G12 atď.), museli by ste na dosiahnutie rovnakého výsledku použiť 11 rôznych vzorcov. To nie je veľký problém, ale čo ak by ste chceli spočítať súčet tisícov riadkov? Potom to môže byť veľký rozdiel.

  • Efektívnosť.    Funkcie poľa môžu byť efektívnym spôsobom vytvárania zložitých vzorcov. Vzorec poľa =SUM(F10:F19*G10:G19) je to isté ako tento: =SUM(F10*G10;F11*G11;F12*G12;F13*G13,F14*G14;F15*G15;F16*G16,F17*G17,F18*G18;F19*G19).

  • Presahovanie    Vzorce dynamického poľa sa automaticky zobrazia do výstupného rozsahu. Ak sa zdrojové údaje nachádzajú v excelovej tabuľke, veľkosť vzorcov dynamického poľa sa bude automaticky prispôsobovať podľa pridávania alebo odoberania údajov.

  • Chyba #PRESAH!    Dynamické polia zaviedli chybu #PRESAH!, ktorá znamená, že plánovaný rozsah presahu je z nejakého dôvodu zablokovaný. Po vyriešení zablokovania sa vzorec automaticky zobrazí.

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} alebo ={"Január"\"Február"\"Marec"}

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.

V nasledujúcom postupe si vyskúšate vytvorenie vodorovných, zvislých a dvojrozmerných konštánt. Ukážeme príklady pomocou funkcie SEQUENCE na automatické generovanie konštánt poľa, ako aj manuálne zadaných konštánt poľa.

• Vytvorenie vodorovnej konštanty

  Použite zošit z predchádzajúceho príkladu alebo vytvorte nový zošit. Vyberte prázdnu bunku a zadajte =SEQUENCE(1;5). Funkcia SEQUENCE vytvorí pole 1 riadok krát 5 stĺpcov rovnakým spôsobom ako ={1\2\3\4\5}. Zobrazí sa nasledujúci výsledok:

  

• Vytvorenie zvislej konštanty

  Vyberte ľubovoľnú prázdnu bunku s miestom pod ňou a zadajte =SEQUENCE(5) alebo ={1;2;3;4;5}. Zobrazí sa nasledujúci výsledok:

  

• Vytvorenie dvojrozmernej konštanty

  Vyberte ľubovoľnú prázdnu bunku s miestom napravo a pod ňou a zadajte =SEQUENCE(3;4). Zobrazí sa nasledujúci výsledok:

  

  Môžete tiež zadať: alebo ={1\2\3\4;5\6\7\8;9\10\11\12}, ale dávajte pozor, kam píšete bodkočiarky a kam zvislé čiary.

  Ako vidíte, možnosť SEQUENCE ponúka významné výhody oproti manuálnemu zadávaniu hodnôt konštánt poľa. Predovšetkým vám ušetrí čas, ale môže tiež pomôcť znížiť počet chýb manuálneho zadávania. Je tiež jednoduchšie čitateľná, najmä pretože bodkočiarky a zvislé čiary sa môžu ťažšie rozlišovať.

Tu je príklad, ktorý používa konštanty poľa ako súčasť väčšieho vzorca. Vo vzorovom zošite prejdite na hárok Konštanta vo vzorci alebo vytvorte nový hárok.

Do bunky D9 sme zadali =SEQUENCE(1;5;3;1), ale mohli ste tiež zadať hodnoty 3, 4, 5, 6 a 7 do buniek A9:H9. Na danom výbere čísel nie je nič špeciálne. Na odlíšenie sme len vybrali niečo iné ako 1 až 5.

Do bunky E11 zadajte =SUM(D9:H9*SEQUENCE(1;5))alebo =SUM(D9:H9*{1\2\3\4\5}). Vzorce vrátia hodnotu 85.

Funkcia SEQUENCE vytvorí ekvivalent konštanty poľa {1\2\3\4\5}. Keďže v Exceli sa najskôr vykonávajú operácie pre výrazy uzavreté zátvorkami, ďalšími dvoma spracovanými prvkami sú hodnoty buniek uložené v D9:H9 a operátor násobenia (*). V tomto bode vzorec vynásobí hodnoty uloženého poľa príslušnými hodnotami konštanty. Predstavuje to ekvivalent vzorca:

=SUM(D9*1;E9*2;F9*3;G9*4;H9*5) alebo =SUM(3*1;4*2;5*3;6*4,7*5)

Napokon sa funkciou SUM spočítajú hodnoty a vráti sa hodnota 85.

Ak sa chcete vyhnúť použitiu uloženého poľa a chcete zachovať celú operáciu v pamäti, môžete ho nahradiť inou konštantou poľa:

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

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

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

• 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.

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:

Prejdite na položky Vzorce > Definované názvy > Definovať názov. Do poľa Názov zadajte reťazec Kvartál1. Do poľa Odkaz na zadajte nasledujúcu konštantu (nezabudnite manuálne zadať zložené zátvorky):

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

Dialógové okno by malo vyzerať takto:

Kliknite na tlačidlo OK, potom vyberte ľubovoľný riadok s tromi prázdnymi bunkami a zadajte =Kvartál1.

Zobrazí sa nasledujúci výsledok:

Ak chcete, aby sa výsledky zobrazovali zvislo namiesto vodorovne, môžete použiť vzorec =TRANSPOSE(Kvartál1).

Ak chcete zobraziť zoznam 12 mesiacov, ako by ste ho mohli použiť pri vytváraní finančného výkazu, môžete použiť funkciu SEQUENCE na vytvorenie základu o jedného roka menej ako aktuálny rok. Úhľadné na tejto funkcii je, že napriek tomu, že sa zobrazuje len mesiac, je za ním platný dátum, ktorý môžete použiť v iných výpočtoch. Tieto príklady nájdete v hárkoch Pomenovaná konštanta poľa a Rýchla vzorová množina údajov vo vzorovom zošite.

=TEXT(DATE(YEAR(TODAY()),SEQUENCE(1;12);1);"mmm")

Tento vzorec používa funkciu DATE na vytvorenie dátumu na základe aktuálneho roka. Funkcia SEQUENCE vytvorí konštantu poľa od 1 do 12 pre január až december, potom funkcia TEXT skonvertuje formát zobrazenia na mmm (Jan, Feb, Mar atď.). Ak chcete zobraziť celý názov mesiaca, napríklad január, použite argument mmmm.

Ak použijete pomenovanú konštantu ako vzorec poľa, nezabudnite zadať znamienko rovnosti, napríklad =Kvartál1, nie iba Kvartál1. V opačnom prípade Excel 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ácie funkcií, textu a čísel. Všetko závisí od vašej kreativity.

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 skonvertovanie riadkov na stĺpce a naopak.

• Vynásobenie každej položky v poli

  Zadajte =SEQUENCE(1;12)*2 alebo ={1\2\3\4;5\6\7\8;9\10\11\12}*2

  Môžete tiež deliť pomocou (/), sčítavať pomocou (+) a odčítavať pomocou (-).

• Umocnenie položiek v poli

  Zadajte =SEQUENCE(1;12)^2 alebo ={1\2\3\4;5\6\7\8;9\10\11\12}^2

• Vyhľadanie druhej odmocniny umocnených položiek v poli

  Zadajte =SQRT(SEQUENCE(1;12)^2) alebo =SQRT({1\2\3\4;5\6\7\8;9\10\11\12}^2)

• Transponovanie jednorozmerného riadka

  Zadajte =TRANSPOSE(SEQUENCE(1;5)) alebo =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

  Zadajte =TRANSPOSE(SEQUENCE(5;1)) alebo =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

  Zadajte =TRANSPOSE(SEQUENCE(3;4)) alebo =TRANSPOSE({1\2\3\4;5\6\7\8;9\10\11\12})

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

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

• Vytvorenie poľa z existujúcich hodnôt

  Nasledujúci príklad vysvetľuje, ako použiť vzorce poľa na vytvorenie nového poľa z existujúceho poľa.

  Zadajte =SEQUENCE(3;6;10;10) alebo ={10\20\30\40\50\60;70\80\90\100\110\120;130\140\150\160\170\180}

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

  Potom do prázdnej bunky zadajte =D9#alebo =D9:I11. V bunkách D9 až D11 sa zobrazí pole buniek veľkosti 3 x 6 s rovnakými hodnotami. Znamienko # sa nazýva operátor rozsahu presahu a ide o spôsob odkazovania Excelu na celý rozsah poľa namiesto jeho vypisovania.

  

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

  Výsledky vzorca presahovaného poľa môžete skonvertovať na jeho súčasti. Vyberte bunku D9 a potom stlačením klávesu F2 prepnite do režimu úprav. Potom stlačením klávesu F9 skonvertujte odkazy na bunky na hodnoty, ktoré Excel potom skonvertuje na konštantu poľa. Keď stlačíte kláves Enter, vzorec =D9# by teraz mal byť ={10\20\30;40\50\60;70\80\90}.

• Spočítanie znakov v rozsahu buniek

  Nasledujúci príklad ilustruje spôsob, akým sa spočíta počet znakov v rozsahu buniek. Tento počet zahŕňa aj medzery.

  

  =SUM(LEN(C9:C13))

  V tomto prípade vráti funkcia LEN dĺžku všetkých textových reťazcov v každej bunke v rozsahu. Funkcia SUM potom tieto hodnoty spočíta a zobrazí výsledok (66). Ak chcete získať priemerný počet znakov, môžete použiť:

  =AVERAGE(LEN(C9:C13))

• Obsah najdlhšej bunky v rozsahu C9:C13

  =INDEX(C9:C13;MATCH(MAX(LEN(C9:C13));LEN(C9:C13);0);1)

  Tento vzorec je funkčný len v prípade, ak rozsah údajov obsahuje jeden stĺpec buniek.

  Preskúmajme vzorec od vnútorných prvkov smerom von. Funkcia LEN vráti dĺžku každej položky v rozsahu buniek D2:D6. 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 D3.

  Tu je to trocha zložitejšie. Funkcia MATCH vypočíta relatívnu pozíciu (odsadenie) 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(C9:C13)

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

  LEN(C9:C13)

  Argument typu zhody v tomto prípade je 0. Typ zhody môže mať hodnoty 1, 0 alebo -1.

  • Hodnota 1 vráti najväčšiu hodnotu, ktorá je menšia alebo rovnaká ako hľadaná hodnota.

  • Hodnota 0 vráti prvú hodnotu, ktorá sa presne zhoduje s hľadanou hodnotou.

  • Hodnota -1 vráti najmenšiu hodnotu, ktorá je väčšia alebo rovnaká ako zadaná hľadaná hodnota.

  • Ak typ zhody vynecháte, 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 C9:C13 poskytuje pole, funkcia MATCH poskytuje adresu bunky a posledný argument (1) určuje, že hodnota pochádza z prvého stĺpca poľa.

  Ak by ste chceli získať obsah najmenšieho textového reťazca, v predchádzajúcom príklade by ste funkciu MAX nahradili funkciou MIN.

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

  Tento príklad ilustruje, ako vyhľadať tri najmenšie hodnoty v rozsahu buniek, pričom v bunkách B9:B18 bolo vytvorené pole vzorových údajov pomocou funkcie: =INT(RANDARRAY(10;1)*100). Majte na pamäti, že funkcia RANDARRAY je nestála funkcia, takže pri každom výpočte Excelu získate novú množinu náhodných čísel.

  

  Zadajte =SMALL(B9#;SEQUENCE(D9), =SMALL(B9:B18;{1\2\3})

  Tento vzorec používa konštantu poľa na vyhodnotenie funkcie SMALL trikrát a vráti najmenšie 3 členy v poli, ktoré sa nachádza v bunkách B9:B18, kde 3 je hodnota premennej v bunke D9. Ak chcete nájsť ďalšie hodnoty, môžete zvýšiť hodnotu vo funkcii SEQUENCE alebo pridať ďalšie argumenty ku konštante. V tomto vzorci môžete použiť aj ďalšie funkcie, ako je napríklad funkcia SUM alebo AVERAGE. Napríklad:

  =SUM(SMALL(B9#;SEQUENCE(D9))

  =AVERAGE(SMALL(B9#;SEQUENCE(D9))

• 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.

  Zadajte =LARGE(B9#;ROW(INDIRECT("1:3"))) alebo =LARGE(B9:B18;ROW(INDIRECT("1:3")))

  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. Napríklad vyberte prázdnu bunku a zadajte:

  =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). 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). 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. Rovnako jednoducho môžete použiť funkciu SEQUENCE:

  =SEQUENCE(10)

  Pozrime sa na vzorec, ktorý ste použili predtým – =LARGE(B9#;ROW(INDIRECT("1:3"))) – od vnútornej zátvorky smerom von: Funkcia INDIRECT vráti množinu textových hodnôt, v tomto prípade hodnoty 1 až 3. Funkcia ROW následne vygeneruje pole stĺpca s tromi bunkami. Funkcia LARGE používa hodnoty v rozsahu buniek B9:B18 a trikrát sa vyhodnotí pre každý odkaz vrátený funkciou ROW. Ak chcete nájsť ďalšie hodnoty, do funkcie INDIRECT pridáte väčší rozsah buniek. Nakoniec, rovnako ako v príkladoch s funkciou SMALL, môžete tento vzorec použiť s inými funkciami, ako napríklad SUM a AVERAGE.

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

  Funkcia SUM v Exceli nefunguje, ak sa pokúsite vypočítať súčet rozsahu, ktorý obsahuje chybovú hodnotu, napríklad chybu #HODNOTA! alebo #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.

Niekedy môže byť potrebné sčítať hodnoty na základe podmienok.

Týmto vzorcom poľa sa napríklad sčítajú iba celé kladné čísla v rozsahu s názvom Predaj, ktorý v predchádzajúcom príklade predstavuje bunky E9:E24:

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

Funkcia IF vytvorí pole kladných hodnôt 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. Týmto vzorcom poľa sa napríklad sčítajú hodnoty väčšie ako 0 a menšie ako 2500:

=SUM((Predaj>0)*(Predaj<2500)*(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 väčšie ako 0 ALEBO menšie ako 2 500:

=SUM(IF((Predaj>0)+(Predaj<2500),Predaj))

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 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.

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á rozsah MojeÚdaje veľkosť 3 riadky krát 5 stĺpcov, rozsah VašeÚdaje musí mať tiež veľkosť 3 riadky krát 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.

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))

Podobné príklady nájdete vo vzorovom zošite v hárku Rozdiely medzi množinami údajov.

V tomto cvičení sa dozviete, ako používať vzorce poľa s viacerými bunkami a s jednou bunkou na výpočet množiny údajov o predaji. Prvá množina krokov používa vzorec s viacerými bunkami na výpočet množiny medzisúčtov. Druhá množina používa vzorec s jednou bunkou na výpočet celkového súčtu.

• Vzorec poľa s viacerými bunkami

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

Predajca	Typ  auta	Počet  predaných kusov	Jednotková  cena	Celkový  Predaj
Kollár	Sedan	5	33000
	Kupé	4	37000
Rybárik	Sedan	6	24000
	Kupé	8	21000
Kubovčík	Sedan	3	29000
	Kupé	1	31000
Klčo	Sedan	9	24000
	Kupé	5	37000
Grešák	Sedan	6	33000
	Kupé	8	31000
Vzorec (celkový súčet)			Celkový súčet
=SUM(C2:C11*D2:D11)			=SUM(C2:C11*D2:D11)

1. 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.

2. 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.

Po stlačení kombinácie klávesov Ctrl + Shift + Enter Excel uzavrie vzorec zloženými zátvorkami ({}) a umiestni inštanciu vzorca do každej bunky 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}. 

• Vytvorenie vzorca poľa s jednou bunkou

Do bunky D13 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 Exceli 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 D13) 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 D13 to neovplyvní.

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

• Konzistentnosť    Ak kliknite na ktorúkoľvek bunku od E2 smerom nadol, zobrazí sa rovnaký vzorec. Táto konzistentnosť 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. Ako ďalšie bezpečnostné opatrenie je potrebné stlačením kombinácie klávesov Ctrl + Shift + Enter potvrdiť akúkoľvek zmenu vzorca.

• 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.

Vo všeobecnosti sa vo vzoroche poľa používa štandardná syntax vzorca. Všetky sa začínajú znamienkom rovnosti (=) a môžete v nich použiť väčšinu zo vstavaných funkcií Excelu. Hlavný rozdiel je v tom, že pri použití vzorca poľa sa vzorec zadá stlačením kombinácie klávesov Ctrl + Shift + Enter. Keď to urobíte, Excel uzavrie vzorec poľa v zložených zátvorkách. Ak zložené zátvorky zadáte manuálne, vzorec sa skonvertuje na textový reťazec a nebude fungovať.

Funkcie poľa môžu byť efektívnym spôsobom vytvárania zložitých vzorcov. 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).

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ý rozsah vzorca (napríklad E2:E11) a potom stlačte kláves Delete.

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

Niekedy môže byť potrebné rozbaliť vzorec poľa. Vyberte prvú bunku v existujúcom rozsahu poľa a pokračujte, kým nevyberiete celý rozsah, na ktorý chcete rozšíriť vzorec. Stlačením klávesu F2 upravte vzorec, potom stlačením kombinácie klávesov CTRL + SHIFT + ENTER potvrďte vzorec po úprave rozsahu vzorca. Dôležité je vybrať celý rozsah od bunky v ľavom hornom rohu poľa. Bunka v ľavom hornom rohu je tá, ktorá sa upravuje.

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.

• Ostatní používatelia zošita nemusia vzorcom rozumieť. Vzorce poľa väčšinou nie sú v hárku vysvetlené. Z tohto dôvodu platí, ž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.

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.

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

Vytvorenie vodorovnej konštanty

1. V prázdnom hárku vyberte bunky A1 až E1.

2. Do riadka vzorcov zadajte nasledujúci vzorec a potom 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 ({ }) a Excel pridá druhú súpravu za vás.

   Zobrazí sa nasledujúci výsledok.

   

Vytvorenie zvislej konštanty

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

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

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

   Zobrazí sa nasledujúci výsledok.

   

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 potom 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:

   

Použitie konštánt vo vzorcoch

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

1. Vytvorte nový hárok vo vzorovom 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.

   

   V bunke A3 sa zobrazí hodnota 85.

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

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

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})

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.

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 Definovať 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:

   

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.

   

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.

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.

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 skonvertovanie 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 potom 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 potom 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 potom 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.

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čením klávesu F2 prepnite do režimu úprav. 

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 vyzerať takto: ={10\20\30;40\50\60;70\80\90}.

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.

   Ú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)

2. Vyberte bunku A8, 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 A10, stlačte kombináciu klávesov Ctrl + Shift + Enter a zobrazí sa obsah najdlhšej bunky v rozsahu A2:A6 (bunka A3).

Nasledujúci vzorec použitý v bunke A8 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).

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

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

1. Do buniek A1 až A11 zadajte nejaké náhodné čísla.

2. Vyberte bunky C1 až C3. V týchto bunkách sa zobrazia výsledky, ktoré sa vypočítajú vzorcom poľa.

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

   =SMALL(A1:A11;{1;2;3})

V tomto vzorci sa používa konštanta poľa na trojité vyhodnotenie 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. V tomto vzorci môžete použiť aj ďalšie funkcie, ako je napríklad funkcia SUM alebo AVERAGE. Napríklad:

=SUM(SMALL(A1:A10;{1\2\3})

=AVERAGE(SMALL(A1:A10;{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 D1 až D3.

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

   =LARGE(A1:A10;ROW(INDIRECT("1:3")))

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, 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.

Pozrime sa na vzorec, ktorý ste použili predtým – =LARGE(A5:A14;ROW(INDIRECT("1:3"))) – od vnútornej zátvorky smerom von: Funkcia INDIRECT vráti množinu textových hodnôt, v tomto prípade hodnoty 1 až 3. Funkcia ROW následne vygeneruje stĺcové pole s tromi bunkami. Funkcia LARGE používa hodnoty v rozsahu buniek A5:A14 a trikrát sa vyhodnotí pre každý odkaz vrátený funkciou ROW. Hodnoty 3200, 2700 a 2000 sa vrátia do stĺpcového poľa s tromi bunkami. Ak chcete nájsť ďalšie hodnoty, do funkcie INDIRECT pridajte väčší rozsah buniek.

Ako v predchádzajúcich prípadoch, môžete tento vzorec použiť s inými funkciami, ako napríklad SUM a AVERAGE.

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

Vráťte sa k predchádzajúcemu prípadu s textovým reťazcom, do prázdnej bunky zadajte nasledovný vzorec a stlačte kombináciu klávesov Ctrl + Shift + Enter:

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

Zobrazí sa text „skupina buniek, ktorá“.

Preskúmajme vzorec od vnútorných prvkov smerom von. Funkcia LEN vráti dĺžku každej položky v rozsahu buniek A2:A6. 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 A3.

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(A2:A6))

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

LEN(A2:A6)

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 A2:A6 poskytuje pole, funkcia MATCH poskytuje adresu bunky a posledný argument (1) určuje, že hodnota pochádza z prvého stĺpca 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))