Vzorce poľa – pokyny a príklady

Vzorce poľa – pokyny a príklady

Poznámka: Radi by sme vám čo najrýchlejšie poskytovali najaktuálnejší obsah Pomocníka vo vašom vlastnom jazyku. Táto stránka bola preložená automaticky a môže obsahovať gramatické chyby alebo nepresnosti. Naším cieľom je, aby bol tento obsah pre vás užitočný. Dali by ste nám v dolnej časti tejto stránky vedieť, či boli pre vás tieto informácie užitočné? Tu nájdete anglický článok pre jednoduchú referenciu.

Vzorec poľa je vzorec, ktorý môže vykonávať viacero výpočtov na jednej alebo viacerých položkách v poli. Pole môžete vymyslieť ako riadok alebo stĺpec hodnôt alebo kombináciu riadkov a stĺpcov hodnôt. Vzorce poľa môžu vrátiť viacero výsledkov alebo jeden výsledok.

Počnúc aktualizáciou september 2018 pre Office 365môže akýkoľvek vzorec, ktorý vráti viacero výsledkov, automaticky vyliať ich nadol alebo do okolitých buniek. Táto zmena v správaní je doplnená aj o niekoľko nových dynamických funkcií poľa. Dynamické vzorce poľa, bez ohľadu na to, či používajú existujúce funkcie alebo funkcie dynamického poľa, je potrebné zadať len do jednej bunky a potom potvrdiť stlačením klávesu Enter. Skôr staršie vzorce poľa vyžadujú najprv výber celého výstupného rozsahu a potom potvrdenie vzorca stlačením kombinácie klávesov CTRL + SHIFT + ENTER. Bežne sa označujú ako vzorce VVN .

Vzorce poľa môžete použiť na vykonávanie zložitých úloh, ako napríklad:

  • Rýchlo vytvorte vzorové množiny údajov.

  • Spočítajte počet znakov obsiahnutých v rozsahu buniek.

  • Spočítajte iba čísla, ktoré spĺňajú určité podmienky, ako sú napríklad najnižšie hodnoty v rozsahu, alebo čísla, ktoré spadajú medzi hornú a dolnú hranicu.

  • Súčet každých n-té hodnoty v rozsahu hodnôt.

V nasledujúcich príkladoch sa uvádza, ako vytvoriť vzorce poľa s viacerými bunkami a s jednou bunkou. Tam, kde je to možné, sme zahrnuli príklady s niektorými dynamickými funkciami poľa a existujúcimi vzorcami poľa zadanými ako dynamické aj staršie polia.

Stiahnite si naše príklady

Stiahnite si vzorový zošit so všetkými príkladmi vzorca poľa v tomto článku.

Toto cvičenie vám ukáže, 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

    Funkcia multi-cell Array v bunke H10 = F10: F19 * G10: G19 na výpočet počtu vozňov predávaných jednotkovou cenou

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

    Po stlačení klávesu Entersa výsledky rozleje na bunky H10: H19 buniek. Všimnite si, že rozliatie je zvýraznené orámovaním, keď vyberiete ľubovoľnú bunku v rozsahu rozliatia. Môžete si všimnúť, že vzorce v bunkách H10: H19 buniek sú sivé. Sú to len odkazy, takže ak chcete upraviť vzorec, budete musieť vybrať bunku H10, kde sa hlavný vzorec žije.

  • Vzorec poľa s jednou bunkou

    Vzorec poľa s jednou bunkou na výpočet celkového súčtu s = SUM (F10: F19 * G10: G19)

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

    V tomto prípade Excel vynásobí hodnoty v poli (rozsah buniek F10 až G19) a potom použije funkciu SUM na sčítanie súčtov spolu. 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 Single-Cell v bunke H20 je úplne nezávislý od vzorca s viacerými bunkami (vzorec v bunkách H10 až H19 buniek). Poukazuje to na ďalšiu výhodu používania vzorcov polí – na flexibilitu. Ostatné vzorce v stĺpci H môžete zmeniť bez toho, aby to malo vplyv na vzorec v H20. Môže to byť aj dobrá prax na to, aby sa vám páčili nezávislé súčty, pretože to pomáha overiť presnosť výsledkov.

  • Dynamické vzorce poľa ponúkajú aj tieto výhody:

    • Konzistentnosť.    Ak kliknete na ktorúkoľvek z buniek v poli H10 dole, zobrazí sa rovnaký vzorec. Konzistencia môže pomôcť zabezpečiť väčšiu presnosť.

    • Bezpečnosť    Nie je možné prepísať súčasť vzorca poľa s viacerými bunkami. Kliknite napríklad na položku H11 buniek a stlačte kláves DELETE. Excel nezmení výstup poľa. Ak ju 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. Príklad predaja v aute napríklad používa jeden vzorec poľa na výpočet výsledkov v stĺpci E. Ak ste použili štandardné vzorce, ako napríklad = F10 * G10, F11 * G11, F12 * G12 atď., mali by ste použiť 11 rôznych vzorcov na výpočet rovnakých výsledkov. To nie je veľký problém, ale čo keby ste mali tisíce riadkov na celkový súčet? Potom môže byť veľký rozdiel.

    • Efektívnosť.    Funkcie poľa môžu byť účinným spôsobom na vytvorenie zložitých vzorcov. Vzorec poľa = SUM (F10: F19 * G10: G19) je rovnaký ako tento: = SUM (F10 * G10; F11 * G11, F12 * G12, F13 * G13, F14 * G14, F15 * G15, F16 * G16, F17 * G17, F18 * G18, F19 * G19

    • Presahujú    Dynamické vzorce poľa sa automaticky rozleje do výstupného rozsahu. Ak sa vaše zdrojové údaje nachádzajú v excelovej tabuľke, vzorce dynamických polí sa pri pridávaní alebo odstraňovaní údajov automaticky pridajú do veľkosti.

    • #SPILL! chyba    Dynamické polia zaviedli #SPILL! error, čo znamená, že plánovaný rozsah rozliatia je z nejakého dôvodu blokovaný. Keď riešenie zablokovania vyriešite, vzorec sa automaticky rozleje.

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, môžete ohraničiť položky v každom riadku čiarkami a ohraničiť každý riadok bodkočiarkami.

V nasledujúcom postupe si vyskúšate vytvorenie vodorovných, zvislých a dvojrozmerných konštánt. Zobrazia sa príklady s použitím funkcie SEQUENCE na automatické generovanie konštanty poľa, ako aj manuálne zadané konštanty poľa.

  • Vytvorenie vodorovnej konštanty

    Použite zošit z predchádzajúceho príkladu alebo vytvorte nový zošit. Vyberte ľubovoľnú prázdnu bunku a zadajte = SEQUENCE (1; 5). Funkcia SEQUENCE zostaví jeden riadok o 5 stĺpcových polí rovnaký ako = {1, 2, 3, 4, 5}. Zobrazí sa nasledujúci výsledok:

    Vytvorenie vodorovnej konštanty poľa s = SEQUENCE (1; 5) alebo = {1; 2; 3; 4; 5}

  • Vytvorenie zvislej konštanty

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

    Vytvorenie zvislej konštanty poľa s = SEQUENCE (5) alebo = {1; 2; 3; 4; 5}

  • Vytvorenie dvojrozmernej konštanty

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

    Vytvorenie 3 riadka o 4 konštanty stĺpcového poľa s postupnosťou = SEQUENCE (3; 4)

    Môžete tiež zadať: or = {1, 2, 3, 4, 5, 6, 7, 8; 9, 10, 11, 12}, ale budete chcieť venovať pozornosť tomu, kde sa dajú bodkočiarky v porovnaní s čiarkami.

    Ako môžete vidieť, možnosť POSTUPnosti ponúka značné výhody pri manuálnom zadávaní hodnôt konštanty poľa. V prvom rade vám ušetrí svoj časový úsek, ale môže pomôcť aj pri znižovaní chýb z manuálneho zadania. Je tiež jednoduchšie čítať, a to najmä v prípade, že bodkočiarky sa dajú ťažko rozlišovať z oddeľovačov čiarkami.

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

Do bunky D9 sme zadali = SEQUENCE (1; 5; 3; 1), ale môžete zadať aj 3, 4, 5, 6 a 7 v bunkách A9: H9. Na konkrétnom výbere čísla nie je nič zvláštne, jednoducho sme si vybrali niečo iné ako 1-5 na diferenciáciu.

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

Použite konštanty poľa vo vzorcoch. V tomto príklade sme použili = SUM (D9: H (* SEKVENCIa (1; 5))

Funkcia SEQUENCE vybuduje ekvivalent konštanty poľa {1, 2, 3, 4, 5}. Keďže Excel vykonáva operácie na výrazoch uzavretých v zátvorkách ako prvý, ďalšie dva prvky, ktoré vstupujú do hry, sú hodnoty buniek v bunke 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)

Nakoniec funkcia SUM spočíta hodnoty a vráti 85.

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

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

Prvky, ktoré môžete použiť 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 je napríklad #N/A. Môžete použiť čísla v celočíselných, desatinných a vedeckých formátoch. Ak zahrniete text, musíte ho obklopiť ú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.

Jedným z najlepších spôsobov použitia konštánt poľa je ich pomenovanie. 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 vzorce _GT_ 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 teraz malo vyzerať takto:

Pridanie pomenovanej konštanty poľa zo vzorcov > definovaných názvov > Správca názvov > nové

Kliknite na tlačidlo OKa potom vyberte ľubovoľný riadok s troma prázdnymi bunkami a zadajte = reťazec kvartál1.

Zobrazí sa nasledujúci výsledok:

Použite konštantu pomenovaného poľa vo vzorci, napríklad = reťazec kvartál1, kde reťazec kvartál1 bol definovaný ako = {"január", "február", "marec"}

Ak chcete, aby sa výsledky rozliali zvislo namiesto vodorovne, môžete použiť =transponovať(reťazec kvartál1).

Ak chcete zobraziť zoznam 12 mesiacov, ako by ste mohli použiť pri zostavovaní finančného výkazu, môžete založiť jeden z aktuálneho roka pomocou funkcie SEQUENCE. Úhledná vec o tejto funkcii je, že napriek tomu, že sa zobrazuje len mesiac, je platný dátum, ktorý sa za ním môže použiť v iných výpočtoch. Tieto príklady nájdete v pomenovaných konštantách a rýchlych vzorových hárkoch s Množina údajov v ukážkovom zošite.

= TEXT (dátum (rok (dnes ()), POSTUPnosť (1; 12); 1); "mmm")

Vytvorenie dynamického zoznamu 12 mesiacov pomocou kombinácie textu, dátumu, roka, DNEŠKa a SEKVENČNých funkcií

Pomocou funkcie DATE môžete vytvoriť dátum na základe aktuálneho roka, sekvencia vytvorí konštantu poľa od 1 do 12 za január až december, potom funkcia text skonvertuje formát zobrazenia na "mmm" (Jan, február, marec atď.). Ak chcete zobraziť celý názov mesiaca, napríklad január, použite výraz mmmm.

Ak použijete pomenovanú konštantu ako vzorec poľa, nezabudnite zadať znak rovnosti, ako je to v = reťazec kvartál1, nie len reťazec kvartál1. 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í. Na záver Majte na pamäti, že môžete použiť kombinácie funkcií, textu a čísel. Všetko záleží od toho, ako kreatívny chcete získať.

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. Niektoré príklady používajú funkciu transponovať na konvertovanie riadkov na stĺpce a naopak.

  • Viacero položiek v poli

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

    Môžete tiež rozdeliť s (/), pridať s (+) a odčítať sa (-).

  • Umocnenie položiek v poli

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

  • Vyhľadanie odmocniny v poli s hranatými položkami

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

  • Transponovanie jednorozmerného riadka

    Enter = transponovať (sekvencia (1; 5))alebo = transponovať ({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

    Enter = transponovať (postupnosť (5; 1))alebo = transponovať ({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

    Enter = transponovať (postupnosť (3; 4))alebo = transponovať ({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 poľa z existujúcich hodnôt

    V nasledujúcom príklade je vysvetlené, ako používať vzorce poľa na vytvorenie nového poľa z existujúceho poľa.

    Enter = SEQUENCE (3; 6;10; 10) alebo = {10; 20; 30; 40; 50; 60; 70; 80; 90100110120; 130140150160170180}

    Pred zadaním čísla 10 a} (pravá zložená zátvorka) zadajte do poľa 180 typ {(ľavá zložená zátvorka), pretože vytvárate pole čísel.

    Potom zadajte = D9 #alebo = D9: I11 v prázdnej bunke. Zobrazí sa 3 x 6 pole buniek s rovnakými hodnotami, ktoré sa zobrazujú v bunke D9: D11. Znak # sa nazýva rozdaný operátor rozpusteného rozsahua Excel's spôsob, ako odkazovať na celý rozsah poľa namiesto toho, aby ste ho museli zadávať.

    Použitie operátora vypusteného rozsahu (#) na odkazovanie na existujúce pole

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

    Môžete využiť výsledky vypusteného vzorca poľa a skonvertovať ho na jeho súčasti. Vyberte bunku D9, potom stlačením klávesu F2 prejdite 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 mal byť = {10; 20; 30; 40; 50; 60; 70; 80; 90}.

  • Spočítanie znakov v rozsahu buniek

    Nasledujúci príklad znázorňuje, ako spočítať počet znakov v rozsahu buniek. Toto zahŕňa medzery.

    Počítanie celkového počtu znakov v rozsahu a ďalších polí na prácu s textovými reťazcami

    = SUM (LEN (C9: C13))

    V tomto prípade funkcia len vráti dĺžku každého textového reťazca v každej bunke v rozsahu. Funkcia SUM potom spočíta tieto hodnoty spolu a zobrazí výsledok (66). Ak by ste chceli 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 (MAXIMÁLNY (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.

    Pozrime sa bližšie na vzorec, počnúc vnútornými prvkami a prácou smerom von. Funkcia len vráti dĺžku každej položky v rozsahu buniek D2: D6. Funkcia max vypočíta najväčšiu hodnotu medzi týmito položkami, ktorá zodpovedá najdlhšiemu textovému reťazcu, ktorý je v bunke D3.

    Na tomto mieste je vzťah trochu zložitejší. Funkcia Match vypočíta offset (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 (C9: C13)

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

    LEN (C9: C13)

    Argument typ zhody v tomto prípade je 0. Typ zhody môže byť hodnota 1, 0 alebo-1.

    • 1 – vráti najväčšiu hodnotu, ktorá je menšia alebo rovná vyhľadávaniu Val

    • 0 – vráti prvú hodnotu, ktorá sa presne rovná vyhľadávanej hodnote

    • -1-Vráti najmenšiu hodnotu, ktorá je väčšia alebo rovná zadanej vyhľadávacej hodnote

    • Ak typ zhody vynecháte, program Excel pracuje s hodnotou 1.

    Funkcia index nakoniec vykoná tieto argumenty: pole a číslo riadka a stĺpca v rámci tohto poľa. 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 v poli.

    Ak chcete získať obsah najmenšieho textového reťazca, môžete nahradiť hodnotu MAX vo vyššie uvedenom príklade o min.

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

    V tomto príklade je znázornené, ako nájsť tri najmenšie hodnoty v rozsahu buniek, pričom pole vzorových údajov v bunkách B9: B18has bolo vytvorené s: = int (RANDARRAY(10; 1) * 100). Všimnite si, že RANDARRAY je prchavá funkcia, preto sa pri každom výpočte programu Excel zobrazí nová množina náhodných čísel.

    Vzorec poľa programu Excel na vyhľadanie najmenšej hodnoty: = SMALL (B9 #; SEQUENCE (D9))

    Enter = Small (B9 #; SEQUENCE (D9); = Small (B9: B18; {1; 2; 3})

    Tento vzorec používa konštantu poľa na vyhodnotenie malej funkcie trikrát a vráti najmenších 3 členov v poli, ktoré je obsiahnuté v bunkách B9: B18, kde 3 je hodnota premennej v bunke D9. Ak chcete vyhľadať ďalšie hodnoty, môžete zvýšiť hodnotu funkcie SEQUENCE alebo pridať ďalšie argumenty do konštanty. 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ýznamnejšie hodnoty v rozsahu, môžete malú funkciu nahradiť veľkou funkciou. V nasledujúcom príklade sa navyše používajú funkcie ROW a INDIRECT.

    Enter = large (B9 #; Row (nepriame ("1:3"))))alebo = large (B9: B18; Row (nepriame ("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. Vyberte napríklad položku prázdne 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 riadkov a vzorec teraz vygeneruje celé čísla od 2 do 11. Ak chcete odstrániť tento problém, zadajte do vzorca funkciu INDIRECT:

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

    Funkcia INDIRECT používa textové reťazce ako argumenty (preto je rozsah 1:10 obklopený ú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. Dalo by sa jednoducho použiť POSTUPnosť:

    = SEQUENCE (10)

    Pozrime sa na vzorec, ktorý ste predtým použili – = LARGE (B9 #; ROW (nepriame ("1:3"))) – počnúc vnútornými zátvorkami a pracovným smerom von: funkcia INDIRECT vráti množinu textových hodnôt, v tomto prípade hodnoty 1 až 3. Funkcia ROW zasa vygeneruje pole stĺpcov s troma bunkami. Veľká funkcia používa hodnoty v rozsahu buniek B9: B18 a vyhodnotí sa trikrát raz pre každý odkaz vrátený funkciou ROW. Ak chcete vyhľadať ďalšie hodnoty, pridajte do nepriamej funkcie väčší rozsah buniek. A nakoniec, rovnako ako v malých príkladoch, môžete použiť tento vzorec s inými funkciami, ako napríklad SUM a AVERAGE.

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

    Funkcia SUM v Exceli nefunguje pri pokuse o sčítanie rozsahu obsahujúceho chybovú hodnotu, ako je napríklad #VALUE. alebo #N/A. V tomto príklade sa dozviete, ako zhrnúť hodnoty v rozsahu s názvom údaje obsahujúce chyby:

    Použite polia na riešenie chýb. Napríklad = SUM (IF (ISERROR (údaje); ""; údaje) spočíta rozsah s názvom údaje aj v prípade, že obsahuje chyby, napríklad #VALUE. alebo #NA!.

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

    Tento príklad je podobný predchádzajúcemu vzorcu, ale vráti počet chybových hodnôt v rozsahu s názvom údaje namiesto ich filtrovania:

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

Polia môžete použiť na výpočet na základe určitých podmienok. = SUM (IF (Sales>0; predaj)) spočíta všetky hodnoty väčšie ako 0 v rozsahu, ktorý sa nazýva predaj.

Tento vzorec poľa napríklad sčíta len kladné celé čísla v rozsahu s názvom predaj, ktorý reprezentuje bunky E9: chyby E24 vo vyššie uvedenom príklade:

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

Funkcia IF vytvorí pole s kladnými a falošnými hodnotami. 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. Tento vzorec poľa napríklad vypočíta hodnoty väčšie ako 0 a menšie ako 2500:

= SUM ((Sales>0) * (Sales<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 spočítať hodnoty väčšie ako 0 alebo menšie ako 2500:

= SUM (IF ((Sales>0) + (Sales<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. Inými slovami, vykonávate matematické operácie, ako je napríklad sčítanie alebo násobenie hodnôt, ktoré spĺňajú podmienky alebo podmienky.

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 rovnakú dimenziu. Ak sú napríklad údaje o rozsahu 3 riadky o 5 stĺpcov, vašeúdaje musí byť aj 3 riadky o 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 * (MyData<>YourData))

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 v ukážkovom zošite na rozdieloch medzi hárkami množiny údajov .

Toto cvičenie vám ukáže, 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ú tabuľku nižšie a prilepte ju do bunky a1 v prázdnom hárku.

Predaj Osoba

Car (auto ) Zadajte výraz

Číslo Predávajú

Jednotka Price (cena )

Celkový počet 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 bunky E2: E11, zadajte vzorec = C2: C11 * D2: D11a potom stlačte kombináciu klávesov CTRL + SHIFT + ENTER.

  2. Ak chcete zobraziť celkový súčet všetkých predajov, vyberte bunku F11, zadajte vzorec = SUM (C2: C11 * D2: D11)a potom stlačte kombináciu klávesov CTRL + SHIFT + ENTER.

Ak stlačíte kombináciu klávesov CTRL + SHIFT + ENTER, Excel ohraničí vzorec pomocou zložených zátvoriek ({}) a vloží 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}

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

  • Vytvorenie vzorca poľa s jednou bunkou

Do bunky D13 zošita zadajte nasledujúci vzorec a stlačte kombináciu klávesov CTRL + SHIFT + ENTER:

=SUM(C2:C11*D2:D11)

V tomto prípade Excel vynásobí hodnoty v poli (rozsah buniek C2 až D11) a potom použije funkciu SUMna sčítanie súčtov spolu. 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 úplne odstrániť daný stĺpec bez toho, aby to malo vplyv na vzorec v D13.

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

  • Konzistentnosť.    Ak kliknete na ktorúkoľvek z buniek zo E2 smerom nadol, zobrazí sa rovnaký vzorec. Konzistencia 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 položku Cell 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 pridanú bezpečnostnú mieru musíte stlačením kombinácie klávesov CTRL + SHIFT + ENTER potvrdiť všetky zmeny 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 vzorce poľa používajú štandardnú syntax vzorca. Všetky začínajú znakom rovnosti (=) a môžete použiť väčšinu vstavaných funkcií Excelu vo vzorcoch poľa. Hlavným rozdielom je, že pri použití vzorca poľa stlačte kombináciu klávesov CTRL + SHIFT + ENTER a zadajte vzorec. Keď to urobíte, Excel zobrazí vzorec poľa s zloženými zátvorkami – Ak manuálne zadáte zložené zátvorky, vzorec sa skonvertuje na textový reťazec a nebude fungovať.

Funkcie poľa môžu byť účinným spôsobom na vytvorenie 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).

Dôležité: Ak potrebujete zadať vzorec poľa, stlačte 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ý 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 alebo odstrániť bunky.

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, do ktorého chcete rozšíriť vzorec. Stlačením klávesu F2 Upravte vzorec a potom stlačením kombinácie klávesov CTRL + SHIFT + ENTER potvrďte vzorec po úprave rozsahu vzorca. Kľúčom je výber celého rozsahu, ktorý sa začína v ľavej hornej bunke poľa. Ľavá horná bunka je tá, ktorá sa upraví.

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

  • Môžete občas zabudnúť stlačiť kombináciu klávesov 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 rozumieť vašim vzorcom. Vzorce poľa v praxi zvyčajne nie sú vysvetlené v hárku. Ak však ostatní používatelia potrebujú upraviť zošity, mali by ste sa vyhnúť vzorcom poľa alebo sa uistiť, že ľudia vedia o všetkých vzorcoch poľa a ako ich v prípade potreby zmeniť.

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

Teraz už viete, že pri vytváraní vzorcov poľa je potrebné 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. Potom môžete použiť kombináciu klávesov CTRL + SHIFT + ENTER na zadanie celého vzorca.

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.

Tu je 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 dva riadky je prvý riadok 1, 2, 3 a 4 a druhý riadok je 5, 6, 7 a 8. 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. V riadku 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ť začiatočné a koncové zátvorky ({}) a Excel pridá druhú množinu.

    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. V riadku 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. V riadku 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 potom 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.

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. Zapamätajte si tiež, že po pridaní konštanty k vzorca poľa stlačíte kombináciu klávesov CTRL + SHIFT + ENTER a zadajte vzorec.

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 prázdnu bunku v zošite, prilepte vzorec do riadka vzorcov a potom 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.

Jedným z najlepších spôsobov použitia konštánt poľa je ich pomenovanie. 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 Definovanie názvu .

  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.

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 čiarku alebo bodkočiarku, alebo ak ste ho umiestnili na nesprávne miesto, konštanta poľa sa nemusí vytvoriť správne alebo sa môže Zobraziť výstražné 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. Niektoré príklady používajú funkciu transponovať 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.

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, čím sa zadá toto pole čísel v 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 potom stlačte kombináciu klávesov CTRL + SHIFT + ENTER:

    =C8:E10

    V bunkách C1 až E3 sa zobrazí pole 3x3 buniek s rovnakými hodnotami, ktoré sa zobrazujú v C8 až E10.

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

  1. Ak ste vybrali bunky C1: C3, stlačením klávesu F2 sa prepnete 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 byť = {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 a potom 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 a potom stlačte kombináciu klávesov CTRL + SHIFT + ENTER , čím zobrazíte obsah najdlhší buniek A2: A6 (bunka a3).

Nasledujúci vzorec sa používa 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 spočíta tieto hodnoty spolu 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. Zadajte niekoľko náhodných čísel do buniek a1: A11.

  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 stlačte kombináciu klávesov CTRL + SHIFT + ENTER:

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

Tento vzorec používa konštantu poľa na vyhodnotenie malej funkcie trikrát a vráti najmenšiu (1), druhú najmenšiu (2) a tretiu najmenšiu (3) členov v poli, ktoré je obsiahnuté v bunkách a1: A10 na vyhľadanie ďalších hodnôt, pridáte ďalšie argumenty konštanty. 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. V riadku vzorcov zadajte tento vzorec a potom stlačte kombináciu klávesov CTRL + SHIFT + ENTER:

    = LARGE (A1: A10; RIADOK (NEPRIAMY ("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. Vyberte napríklad prázdny stĺpec 10 buniek v pracovnom zošite, zadajte tento vzorec poľa a potom stlačte kombináciu klávesov 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 predtým použili – = large (A5: A14; Row (nepriame ("1:3"))) – počnúc vnútornými zátvorkami a pracovným smerom von: Funkcia INDIRECT vráti množinu textových hodnôt, v tomto prípade hodnoty 1 až 3. Funkcia Row v časti Turn generuje tri bunkové stĺpcové pole. Veľké funkcie používajú hodnoty v rozsahu buniek A5: A14 a vyhodnotia sa trikrát raz pre každý odkaz vrátený funkciou Row . Hodnoty 3200, 2700 a 2000 sa vrátia do poľa troch buniek stĺpcový. Ak chcete vyhľadať ďalšie hodnoty, pridajte do nepriamej funkcie väčší rozsah buniek.

Rovnako ako v predchádzajúcich príkladoch môžete použiť tento vzorec s inými funkciami, ako napríklad Sum a Average.

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

Vráťte sa na predchádzajúci príklad textového reťazca, do prázdnej bunky zadajte nasledujúci 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 "banda buniek, ktoré".

Pozrime sa bližšie na vzorec, počnúc vnútornými prvkami a prácou smerom von. Funkcia len vráti dĺžku každej položky v rozsahu buniek A2: A6. Funkcia Max vypočíta najväčšiu hodnotu medzi týmito položkami, ktorá zodpovedá najdlhšiemu textovému reťazcu, ktorý je v bunke 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 v poli.

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 * (MyData<>YourData))

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

Potvrdenie

Časti tohto článku boli založené na sérii excelových stĺpcov Power User, ktoré napísal Colin Wilcox, a boli prispôsobené z kapitol 14 a 15 vzorcov Excelu 2002, knihy, ktorú napísal John Walkenbach, bývalý Excel MVP.

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ž

Dynamické polia a správanie polí s presahujúcimi údajmi

Dynamické vzorce poľa verzus vzorce poľa staršieho VVN

FILTER (funkcia)

RANDARRAY (funkcia)

SEQUENCE (funkcia)

SINGLE (funkcia)

SORT (funkcia)

SORTBY (funkcia)

UNIQUE (funkcia)

Chyba #PRESAHOVANIE! v Exceli

Prehľad vzorcov

Rozšírte svoje zručnosti práce s balíkom Office
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.

×