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 je vzorec, ktorý môžete vykonať viacero výpočtov na jednej alebo viacerých položiek v poli. Môžeme si pole ako riadok alebo stĺpec hodnôt alebo kombináciou riadky a stĺpce hodnoty. Vzorce poľa môžete vrátiť viacero výsledkov alebo jeden výsledok.

Začínajúcu reťazcom September 2018 aktualizácie pre Office 365, vzorec, ktorý sa môže vrátiť viac výsledkov sa automaticky dôjsť ich nadol alebo naprieč do susedných buniek. Táto zmena správania sprevádza niekoľko nových funkcií dynamické pole. Dynamické vzorcov, či už používate existujúce funkcie alebo funkcie dynamické pole, stačí možno zadať do jednej bunky a potom potvrdené stlačením klávesu Enter. Predtým, staršie vzorce vyžadujú najprv vyberiete celú výstup rozsah a potom potvrdenie vzorec s funkciou Kombináciu klávesov Ctrl + Shift + Enter. Bežne sa označuje ako vzorce CSE .

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

  • Rýchlo vytvoriť vzorky množiny údajov.

  • Zistenie počtu znakov v rozsahu buniek.

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

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

Nasledujúce príklady zobrazujú ako vytvoriť vzorce poľa s viacerými bunkami a jednou bunkou. Ak je to možné, uvádzame príklady s niektorými z funkcie dynamické pole, ako aj existujúce vzorce poľa zadať ako dynamické a staršie polia.

Stiahnite si naše príklady

Stiahnuť vzorový zošit s všetky pole vzorec príklady v tomto článku.

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

  • Vzorec poľa s viacerými bunkami

    Funkcia poľa s viacerými bunkami v bunke H10 = F10:F19 * G10:G19 na výpočet počtu automobilov predal jednotková cena

  • Tu sme výpočet celkový predaj kupé a sedanov pre jednotlivých predajcov zadaním = F19:F19 * G10:G19 v bunke H10.

    Po stlačení klávesu Enter sa zobrazí výsledky rozliať nadol H10:H19 buniek. Všimnite si, že únikom rozsah je zvýraznený s orámovaním po výbere ľubovoľnú bunku v rozsahu únikom. Môžete si všimnúť, že sú sivé vzorcov v bunkách H10:H19. Sú tu len pre informáciu, takže ak chcete upraviť vzorec, je potrebné vybrať bunku H10, bydlisko predlohy vzorec.

  • Vzorec poľa s jednou bunkou

    Jednou bunkou vzorec vypočíta celkový súčet s =SUM(F10:F19*G10:G19)

    V bunke H20 vzorového 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 sa používa funkcia SUM na sčítanie súčty spolu. Výsledkom je celkový súčet $1,590,000 predaja.

    Tento príklad zobrazuje, ako účinná tento typ vzorca môže byť. Predpokladajme, že máte 1 000 riadkov údajov. Časť alebo všetky tieto údaje môžete sčítať vytvorením vzorec v jednej bunke namiesto presúvania vzorec až 1 000 riadkov. Všimnite si tiež, že jednej bunky vzorec v bunke H20 je úplne prepojený s viacerými bunkami vzorec (vzorec v bunkách H10 až H19). Toto je ďalšou výhodou používania vzorcov poľa – flexibilitu. Môžete zmeniť inými vzorcami v stĺpci H bez ovplyvnenia vzorec vo výraze H20. Tiež môže byť vhodné, aby sa nezávislé súhrny takto, pretože pomáha Overte správnosť výsledky.

  • Dynamické vzorcov ponúka aj tieto výhody:

    • Súlad    Ak kliknete na niektorú z buniek z H10 smerom nadol, pozrite si tému ten istý vzorec. Že súlad môže pomôcť zabezpečiť vyššiu presnosť.

    • Bezpečnosť    Nie je možné prepísať súčasť vzorca poľa s viacerými bunkami. Napríklad, kliknite na bunku H11 a stlačte kláves Delete. Excel sa nezmení poľa výstup. Ak chcete zmeniť, musíte vybrať ľavej hornej bunky v poli alebo v bunke H10.

    • Menšia veľkosť súborov    Jedným vzorcom poľa často môžete použiť namiesto niekoľko pomocného vzorcov. Napríklad auto predaja príklade sa používa jeden vzorec na výpočet výsledkov v stĺpci E. Ak by ste použili štandardné vzorce, ako napríklad = F10 * G10, F11 * G11, F12 * G12, atď., by ste použili 11 rôznych vzorcov na výpočet rovnaké výsledky. To nie je veľký problém, ale čo robiť, ak ste mali tisícky riadkov k celkovému? Potom môžete to urobiť veľký rozdiel.

    • Efektívnosti    Funkcie poľa môže byť účinný spôsob na vytváranie zložitých vzorcov. Pole vzorca =SUM(F10:F19*G10:G19) je rovnaká ako takto: = SUM(F10*G10,F11*G11,F12*G12,F13*G13,F14*G14,F15*G15,F16*G16,F17*G17,F18*G18,F19*G19).

    • Rozliatia    Dynamické vzorce sa automaticky dôjsť do výstupnej oblasti. Ak zdroj údajov v excelovej tabuľke, potom dynamické pole vzorcov automaticky zmení veľkosť pri pridaní alebo odstránení údajov.

    • #SPILL! chyby    Dynamické polia zavádza #SPILL! chyby, čo naznačuje, že plánované únikom rozsah je blokovaný z nejakého dôvodu. Pri riešení zablokovanie, vzorec sa automaticky dôjsť.

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 ste oddeľte položky pomocou čiarky, vytvoríte vodorovné pole (riadka). Ak položky oddeľte pomocou bodkočiarky, vytvorte zvislé pole (stĺpec). Ak chcete vytvoriť dvojrozmerné pole, ohraničujú položiek v každom riadku čiarkami a ohraničujú každý riadok bodkočiarkou.

Tieto postupy vyskúšate si vytvorenie vodorovných, zvislých a dvojrozmerných konštánt. Sme vám ukážeme príklady použitia funkcie postupnosť na automatické generovanie konštanty poľa, ako aj manuálne zadanie konštanty 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 postupnosť nadväzuje 1 riadok o 5 pole stĺpca nezhoduje = {1,2,3,4,5}. Zobrazí sa nasledujúci výsledok:

    Vytvoriť vodorovnú konštantu poľa s =SEQUENCE(1,5) alebo = {1,2,3,4,5}

  • Vytvorenie zvislej konštanty

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

    Vytvorenie zvislú konštantu 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 ním a zadajte =SEQUENCE(3,4). Zobrazí sa nasledujúci výsledok:

    Vytvorenie 3 riadku podľa konštanta poľa 4 stĺpce s =SEQUENCE(3,4)

    Môžete tiež zadať: alebo = {1,2,3,4 5,6,7,8; 9,10,11,12}, ale budete chcieť venovať pozornosť umiestnenia bodkočiarky verzus čiarky.

    Ako vidíte, postupnosť možnosť ponúka značné výhody oproti manuálnym zadaním svojho konštanty poľa. Primárne, šetrí čas, ale tiež môže pomôcť, zníženie chýb z manuálne zadanie. Tiež je čitateľnejší, najmä ak bodkočiarky môže byť ťažké odlíšiť od oddeľovačmi čiarkou.

Tu je príklad, že používa pole konštánt ako súčasť väčších vzorec. Vo vzorovom zošite, prejdite na konštanty vo vzorci hárka alebo vytvorte nový hárok.

V bunke D9 nahráme =SEQUENCE(1,5,3,1), ale môžete tiež zadať 3, 4, 5, 6 a 7 do bunky A9:H9. Nie je nič zvláštne na tento výber konkrétneho čísla, vybrali sme inú hodnotu než 1-5 pre odlíšenie.

V bunke E11, zadajte = SUM (D9:H9*SEQUENCE(1,5)), alebo = SUM (D9:H9* {1,2,3,4,5}). Vzorce vrátia 85.

Použitie konštánt poľa vo vzorcoch. V tomto príklade sme použili = SUM (D9:H(*SEQUENCE(1,5))

Funkcia SEKVENCIA vytvorí ekvivalent poľa konštantnej {1,2,3,4,5}. Pretože program Excel vykonáva operácie vo výrazoch uzavretá zátvorkami najskôr, ďalšie dve prvky, ktoré nadobudnú prehrávania sú hodnoty buniek v D9:H9 a násobenia (*). V tomto bode vzorec vynásobí hodnoty v uložené pole zodpovedajúce hodnoty v konštante. Ide o ekvivalent:

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

Napokon, funkcia SUM sčíta hodnoty, a vráti 85.

Ak chcete vyhnúť použitiu uloženého poľa a zachovať operáciu úplne 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é môžete použiť v konštante poľa

  • Konštanty poľa môže obsahovať čísla, text, logické hodnoty (napríklad TRUE a FALSE) a chybové hodnoty, ako napríklad #nedostupný. Čísla môžete použiť vo formátoch celé číslo, počet desatinných miest a vedecké. Ak zahrniete textu, musíte ju obklopujú v úvodzovkách ("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.

Jednou z najlepších spôsobov, ako používať konštanty poľa je pomenovať. Pomenované konštanty môže byť oveľa jednoduchšie použiť, a môžete skryť, niektoré zložitosť vzorcoch poľa od ostatných. Pomenovanie konštanty poľa a použite vo vzorci, postupujte takto:

Prejdite na 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 mal teraz vyzerať takto:

Pridanie pomenovaných konštante z vzorce > definované názvy > Správca názvov > nové

Kliknite na tlačidlo OK, a potom vyberte ľubovoľný riadok s troma prázdnymi bunkami a zadajte = Štvrťrok1.

Zobrazí sa nasledujúci výsledok:

Použitie pomenovaných konštante vo vzorci, napríklad = reťazec kvartál1, kde bol definovaný reťazec kvartál1 = {"Január", "Február", "marec"}

Ak chcete výsledky prelievať vertikálne nie vo vodorovnom smere, môžete použiť =TRANSPONOVAŤ(reťazec kvartál1).

Ak chcete zobraziť zoznam 12 mesiacov, ako môžete použiť pri vytváraní finančný výkaz, môžete založiť jednorazové v aktuálnom roku s funkciou postupnosť. Čistú vec o tejto funkcii je, že aj napriek tomu zobrazuje len mesiac, je platný dátum, za ním, ktoré môžete použiť v iných výpočtoch. V vzorového zošita nájdete v týchto príkladoch v hárku s názvom konštanta poľa a rýchle vzorových údajov .

=TEXT(Date(Year(Today()),SEQUENCE(1,12),1),"mmm")

Použiť kombináciu funkcií textu, dátumu, rok, v súčasnosti a postupnosť vybudovať dynamický zoznam 12 mesiacov

Používa funkcie DATE na vytvorenie dátumu v aktuálnom roku, SEKVENCIA vytvorí konštanty poľa od 1 do 12 za január až December, potom Funkcia TEXT skonvertuje na reťazec "mmm" formát zobrazenia (január, február, marec, atď.). Ak chcete zobraziť celý názov mesiaca, napríklad január, by ste použili "mmmm".

Ak chcete použiť pomenovaná konštanta ako vzorec poľa, nezabudnite zadať znak rovnosti, rovnako ako v = Štvrťrok1, nielen reťazec kvartál1. Ak nie, program Excel interpretuje ako textový reťazec poľa a vzorec nebude fungovať podľa očakávaní. Napokon, nezabúdajte, že môžete použiť kombinácie funkcií, textu a čísel. Závisí to všetko ako kreatívne, ktorému chcete pridať.

Tieto príklady ukazujú niekoľko spôsobov, v ktorom môžete dať konštánt poľa vo vzorcoch poľa. Niekoľko príkladov sa používa funkcia TRANSPOSE na konvertovanie riadkov na stĺpce a naopak.

  • Viacero jednotlivých položiek v poli

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

    Môžete rozdeliť s (/), pridajte so (+) a odčítanie s (-).

  • Umocnenie položiek v poli

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

  • Zistenie druhej odmocniny mocnín 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 stĺpce.

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žívať vzorce poľa vytvoriť nové pole 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}

    Nezabudnite zadať {(ľavá zložená zátvorka) pred číslo 10 a} (pravá zložená zátvorka) po zadaní 180, pretože vytvárate pole čísel.

    Následne zadajte = D9 #alebo = D9:I11 do prázdnej bunky. S rovnakými hodnotami, ktoré sa zobrazujú v rozsah D9: D11 sa zobrazí pole s 3 x 6 buniek. Znak # sa nazýva vytiekla operátor rozsahua je to spôsob programu Excel odkaze na rozsah celého poľa zadania ju.

    Použitie operátora rozliatym rozsahu (#) na odkaz existujúceho poľa

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

    Môžete používať výsledky rozliatym vzorec a konvertovanie, na jeho súčasti. Vyberte bunku D9 a potom stlačením klávesu F2 prejdite do režimu úprav. Následným stlačením klávesu F9 previesť odkazov na bunky na hodnoty, ktoré program Excel potom skonvertuje konštanty poľa. Po stlačení klávesu Enter, vzorec = D9 #, by teraz = {10,20,30; 40,50,60; 70,80,90}.

  • Spočítanie znakov v rozsahu buniek

    Nasledujúci príklad zobrazuje ako spočítať počet znakov v rozsahu buniek. Patria sem aj medzery.

    Celkový počet znakov v rozsahu a ostatné polia na prácu s textové reťazce

    = SUM (LEN(C9:C13))

    V tomto prípade Funkcia LEN vráti dĺžku každý textový reťazec vo všetkých buniek v rozsahu. Funkcia SUM potom spolu pridá tieto hodnoty 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(MAX(LEN(C9:C13)),LEN(C9:C13),0),1)

    Tento vzorec funguje len v prípade, že rozsah údajov obsahuje aspoň jeden stĺpec buniek.

    Poďme Podrobný pohľad na vzorec, začínajúc od vnútorné prvky a práci smerom von. Funkcia LEN vráti dĺžku každá z položiek v rozsahu buniek D2: D6. Funkcia MAX vypočíta najväčšiu hodnotu medzi tieto položky, ktorá zodpovedá najdlhšieho textového reťazca, ktorý je umiestnený v bunke D3.

    Tu sa veci trochu zložité. Funkcia MATCH vypočíta posun (relatívnu pozíciu) na bunku obsahujúcu najdlhšieho textového reťazca. Tak, že vyžaduje sa tri argumenty: hľadanou hodnotou, vyhľadávacie pole a typ zhody. Funkcia MATCH vyhľadá vyhľadávacie pole pre určené hľadanou hodnotou. V tomto prípade je za hľadanou hodnotou najdlhšieho textového reťazca:

    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ť hodnotou 1, 0 alebo hodnotu-1.

    • 1 - vráti najväčšiu hodnotu, ktorá je menšia alebo rovná sa vyhľadávacie pole val

    • 0 - vráti prvú hodnotu presne rovná za hľadanou hodnotou

    • -1 - Vráti najmenšiu hodnotu, ktorá je väčšie alebo rovné určené hľadanou hodnotou

    • Ak vynecháte typ zhody, program Excel predpokladá 1.

    Napokon, funkcia INDEX má tieto argumenty: poľa a číslo riadka a stĺpca v rámci tohto poľa. Rozsah buniek C9:C13 poskytuje poľa, funkcia MATCH obsahuje adresu bunky a záverečný argument (1) určuje, či hodnota pochádza z prvého stĺpca v poli.

    Ak by ste chceli získať obsah najmenšiu textový reťazec, ktorý by nahradiť MAX v uvedenom príklade MIN.

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

    Tento príklad ukazuje, ako vyhľadať tri najmenšie hodnoty v rozsahu buniek, ak pole vzorové údaje v bunkách B9:B18has bola vytvorená pomocou: = INT (RANDARRAY(10,1) * 100). Všimnite si, že RANDARRAY je nestále funkcie, takže budete mať vždy, keď program Excel vypočíta novú množinu náhodných čísel.

    Program Excel vzorec na vyhľadanie n-tého najmenšia hodnota: =SMALL(B9#,SEQUENCE(D9))

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

    V tomto vzorci sa používa konštanty poľa vypočítať funkcia SMALL trikrát a vráťte najmenšiu 3 členov v poli, ktorá je obsiahnutá v bunkách B9:B18, kde sa nachádza 3 premennej hodnotu v bunke D9. Ak chcete vyhľadať ďalšie hodnoty, môžete zvýšiť hodnotu vo funkcii postupnosť alebo pridať ďalšie argumenty na konštanta. Pomocou tohto vzorca, ako napríklad SUM alebo AVERAGEmôžete použiť aj ďalšie funkcie. Prí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ť funkcia SMALL LARGE (funkcia). Okrem toho nasledujúci príklad používa riadka a funkcia INDIRECT funkcie.

    Zadajte = veľké (B9 #, riadok (INDIRECT ("1:3"))), alebo = veľké (B9:B18,ROW(INDIRECT("1:3")))

    V tomto bode môže pomôcť vedieť niečo o funkciách riadok a funkcia INDIRECT. Ak chcete vytvoriť pole po sebe nasledujúcich celých čísel, môžete použiť funkcie ROW. Napríklad, vyberte možnosť vyprázdniť a zadajte:

    =ROW(1:10)

    Vzorec vytvorí stĺpec s 10 po sebe nasledujúcich celé čísla. Ak chcete zobraziť potenciálne problémy, vložiť riadok nad rozsah, ktorý obsahuje vzorec poľa (teda nad riadok 1). Program Excel upraví odkazov na riadok a vzorec teraz generuje celé čísla 2 až 11. Ak chcete vyriešiť tento problém, pridáte funkcia INDIRECT vzorec:

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

    Funkcia INDIRECT používa textové reťazce ako argumenty funkcie (čo je dôvod, prečo rozsah 1:10 sa nachádza v úvodzovkách). Excel nebude meniť textové hodnoty Ak vložiť riadky alebo inak presúvate vzorec poľa. Výsledkom funkcie ROW vždy generuje rad celých čísel, ktoré chcete. Môžete jednoducho použiť postupnosť:

    =SEQUENCE(10)

    Pozrime sa na vzorec, ktorý ste predtým použili – LARGE (B9 #, riadok (INDIRECT ("1:3"))) = – začínajúc od vnútorných zátvoriek a práci smerom von: funkcia INDIRECT vráti množinu textových hodnôt v tomto prípade hodnoty 1 až 3. Funkcia ROW generuje pole s tromi bunky stĺpca. Funkcia LARGE používa hodnoty v rozsahu buniek B9:B18 a sa vyhodnotí trikrát jedenkrát pre každý vráti funkcia ROW odkaz. Ak chcete zistiť viac hodnôt, funkcia INDIRECT pridáte väčší rozsah buniek. Nakoniec, ako s malé príklady, 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, ktorý obsahuje chybové hodnoty, napríklad #VALUE! alebo #nedostupný. Tento príklad zobrazuje ako súčet hodnôt v rozsahu s názvom údaje, ktoré neobsahujú chyby:

    Použitie polí na riešenie chýb. Napríklad =SUM(IF(ISERROR(Data),"",Data) bude sčítanie rozsahu s názvom údaje aj v prípade, že obsahuje chyby, ako je 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

    V tomto príklade je ako predošlému vzorcu, ale vráti počtu chybových hodnôt v rozsahu s názvom údaje neodfiltruje:

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

Budete musieť súčet hodnôt na základe podmienok.

Polia môžete použiť na výpočet na základe určitých podmienok. =SUM(if(Sales>0,Sales)) sa súčet všetkých hodnôt väčší ako 0 v rozsahu s názvom predaj.

Napríklad nasledujúci vzorec sčíta len kladné celé čísla v rozsahu s názvom predaj, ktorý predstavuje bunky E9:E24 v uvedenom príklade:

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

Funkcia IF vytvorí pole hodnôt kladné a false. Funkcia SUM v podstate ignoruje hodnoty false, pretože 0 + 0 = 0. Rozsah buniek, ktoré používate v tomto vzorci môžu pozostávať z ľubovoľného počtu riadkov a stĺpcov.

Môžete tiež súčet hodnôt, ktoré spĺňajú viacero podmienok. Napríklad nasledujúci vzorec vypočíta hodnoty väčšie ako 0 a menší než 2 500:

=SUM((Sales>0)*(Sales<2500)*(Sales))

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žite typ alebo podmienky. Môžete napríklad súčet hodnôt, ktoré sú väčšie ako 0 alebo menej ako 2 500:

=SUM(if((Sales>0)+(Sales<2500),Sales))

Nie je možné použiť funkcie AND a alebo funkcií vo vzorcoch poľa priamo, pretože tieto funkcie vrátia jeden výsledok, TRUE alebo FALSE a funkcie poľa vyžadujú polia výsledkov. Problém môžete obísť pomocou logiky uvedené v predchádzajúcej vzorec. Inými slovami, vykonávate matematické operácie ako sčítanie alebo násobenie na hodnoty, ktoré spĺňajú alebo alebo a stav.

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úci vzorec obsahuje porovnanie hodnôt v dvoch rozsahy buniek s názvom Mojeúdaje a vašeúdaje a vráti počet rozdiely medzi týmito dvoma. Ak obsah dvoma rozsahmi sú identické, vzorec vráti hodnotu 0. Ak chcete použiť tento vzorec, rozsahy buniek musia byť rovnaká veľkosť a tú istú dimenziu. Napríklad, ak Mojeúdaje je rozsah 3 riadkov a stĺpcov 5, vašeúdaje musia byť tiež 3 riadkov 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.

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

Nájdete tu podobné príklady vo vzorovom zošite v hárku rozdiely medzi množiny údajov .

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

  • Vzorec poľa s viacerými bunkami

Skopírujte celú nižšie uvedenú tabuľku a prilepte ich do bunky A1 prázdneho hárka.

Predaj Osoby

Auto Typ

Číslo Predané

Jednotka 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, výber buniek E2:E11, zadajte vzorec = C2: C11 * D2: D11, a potom 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, program Excel uzavrie vzorec s funkciou zložených zátvorkách ({}) a vloží inštanciu vzorec v každej bunke vybratého rozsahu. Veľmi rýchlo, je to tak, čo sa zobrazí v stĺpci E je celková čiastka predaja pre každý typ auta pre jednotlivých predajcov. Ak vyberte E2 a potom vyberte položku E3, E4 a podobne, uvidíte, či sa zobrazuje 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

V bunke D13 zošit, zadajte nasledujúci vzorec a potom stlačte Kombináciu klávesov Ctrl + Shift + Enter:

=SUM(C2:C11*D2:D11)

V tomto prípade program Excel vynásobí hodnoty v poli (rozsah buniek C2 až D11) a potom sa používa funkcia SUMna sčítanie súčty spolu. Výsledkom je celkový súčet $1,590,000 predaja. Tento príklad zobrazuje, ako účinná tento typ vzorca môže byť. Predpokladajme, že máte 1 000 riadkov údajov. Časť alebo všetky tieto údaje môžete sčítať vytvorením vzorec v jednej bunke namiesto presúvania vzorec až 1 000 riadkov.

Všimnite si tiež, že jednej bunky vzorec v bunke D13 je úplne prepojený s viacerými bunkami vzorec (vzorec do bunky E2 až E11). Toto je ďalšou výhodou používania vzorcov poľa – flexibilitu. Môže zmeniť vzorce v stĺpci E alebo odstránenie stĺpca úplne, bez ovplyvnenia vzorec v D13.

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

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

  • Bezpečnosť    Nie je možné prepísať súčasť vzorca poľa s viacerými bunkami. Napríklad, kliknite na bunku E3 a stlačte kláves Delete. Je potrebné vybrať celý rozsah buniek (E2 až E11) a zmeňte vzorec pre celú škálu alebo ponechajte pole, ako je. Pridané bezpečnostných dôvodov, budete musieť stlačte Kombináciu klávesov Ctrl + Shift + Enter a potvrďte zmeny vzorec.

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

Vzorce poľa vo všeobecnosti použiť štandardné syntax vzorca. Všetky začínajú znakom rovnosti (=) a môžete použiť väčšinu vstavaných funkcií programu Excel vo vzorcoch poľa. Kľúčové rozdiel niekedy spôsobuje, že pri používaní vzorca poľa, stlačte Kombináciu klávesov Ctrl + Shift + Enter a zadajte vzorec. Keď to urobíte, program Excel uzavrie vzorec poľa s trakmi – Ak zadáte zložené zátvorky manuálne, vzorec sa skonvertujú na textového reťazca a nebude fungovať.

Funkcie poľa môže byť účinný spôsob na vytváranie zložitých vzorcov. Pole vzorec =SUM(C2:C11*D2:D11) je rovnaká ako toto: =SUM(C2*D2,C3*D3,C4*D4,C5*D5,C6*D6,C7*D7,C8*D8,C9*D9,C10*D10,C11*D11).

Dôležité: Vždy, keď je potrebné zadať vzorec poľa, stlačte Kombináciu klávesov Ctrl + Shift + Enter. Tento postup sa vzťahuje na jednej bunky a viacerými bunkami vzorce.

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

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

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

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

  • Ak chcete odstrániť vzorec poľa, vyberte celý vzorec rozsahu (napríklad E2:E11) a potom stlačte kláves Delete.

  • Nie je možné vložiť prázdne bunky do alebo odstránenie buniek z vzorca poľa s viacerými bunkami.

Niekedy možno budete musieť rozbalenie vzorca poľa. Vyberte prvú bunku v rozsahu existujúce pole, a pokračujte, kým ste vybrali celý rozsah, že chcete rozšíriť vzorec. Stlačením klávesu F2 Upravte vzorec a potom stlačte Kombináciu klávesov CTRL + SHIFT + ENTER potvrďte vzorec, keď ste upravili vzorca rozsahu. Kľúč je vyberte celý rozsah, počnúc ľavej hornej bunky v poli. Ľavej hornej bunky je ten, ktorý dostane upravovať.

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

  • Niekedy zabudnete, stlačte Kombináciu klávesov Ctrl + Shift + Enter. Môže sa stať aj skúsených používateľov programu Excel. Nezabudnite, že vždy, keď je zadať alebo upraviť vzorec poľa kombinácia klávesov.

  • Ostatní používatelia zošita nemusia pochopiť vzorce. V hárku v praxi nie sú vo všeobecnosti vysvetlené vzorce poľa. Preto, či ostatní ľudia potrebujú na úpravu zošitov, máte buď vyhnúť vzorce poľa alebo uistite sa, že ľuďom vedieť o všetky vzorce a pochopiť, ako zmeniť ich, ak potrebujete.

  • 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, viete, že budete potrebovať pri vytváraní vzorcov, stlačte Kombináciu klávesov Ctrl + Shift + Enter. Pretože konštanty poľa sú súčasťou vzorcov poľa, ktoré priestorový konštanty s trakmi zadaním manuálne. Potom použijete Kombináciu klávesov Ctrl + Shift + Enter zadajte celý vzorec.

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 poľom v jedinom riadku: {1,2,3,4}. Tu je pole v jednom stĺpci: {1; 2; 3; 4}. Tu je pole dva riadky a stĺpce štyri: {1,2,3,4; 5,6,7,8}. V poli dvoch riadkov je prvý riadok 1, 2, 3 a 4 a druhý riadok predstavuje 5, 6, 7 a 8. Jednoduchá bodkočiarka oddeľuje dva riadky medzi 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 ste mali zadať ľavú a pravú zloženú zátvorku ({}) a Excel pridá druhý súbor za vás.

    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ý prvok v zátvorkách sa konštanta poľa: {1,2,3,4,5}. Nezabudnite, že Excel nie priestorový konštánt poľa s trakmi; skutočne ste ich zadali. Nezabudnite, že po pridaní konštanty do vzorca poľa, stlačte Kombináciu klávesov Ctrl + Shift + Enter 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})

Pokúsiť túto funkciu kopírovať, vyberte prázdnu bunku v zošite, vložte vzorec do riadka vzorcov a potom stlačte Kombináciu klávesov Ctrl + Shift + Enter. Rovnaký výsledok sa zobrazí, ako ste to urobili v starších výkonu, ktoré používajú 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.

Jednou z najlepší spôsob, ako používať konštanty poľa je pomenovať. Pomenované konštanty môže byť oveľa jednoduchšie použiť, a môžete skryť, niektoré zložitosť vzorcoch poľa od ostatných. Pomenovanie konštanty poľa a použite vo vzorci, postupujte takto:

  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:

    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 potom 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 nie je možné oddeliť znakom správne. Ak vynecháte čiarkou alebo bodkočiarkou, alebo ak vložíte disk zlom mieste, konštanty poľa môže vytvoriť správne alebo môže zobraziť hlásenie s upozornením.

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

Tieto príklady ukazujú niekoľko spôsobov, v ktorom môžete dať konštánt poľa vo vzorcoch poľa. Niekoľko príkladov sa používa funkcia TRANSPOSE na konvertovanie riadkov na stĺpce a naopak.

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

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

  2. Zadajte nasledujúci vzorec a 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 a potom 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, aby sa prejde do tohto poľa čísla v rozsahu buniek C8:E10 s použitím vzorca poľa. V hárku, C8 až E10 by mal vyzerať takto:

    10

    20

    30

    40

    50

    60

    70

    80

    90

  3. Vyberte rozsah buniek C1 až E3.

  4. Zadajte nasledujúci vzorec do riadka vzorcov a potom stlačte Kombináciu klávesov Ctrl + Shift + Enter:

    =C8:E10

    Pole s 3 x 3 bunky sa zobrazí v bunkách C1 až E3 s rovnakými hodnotami, ktoré sa zobrazujú v bunkách C8 až E10.

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

  1. S bunkami s C1: C3 vybraté, stlačením klávesu F2 prejdite do režimu úprav.

  2. Stlačením klávesu F9 konvertovať odkazov na bunky na hodnoty. Excel konvertuje hodnoty konštanty poľa. Vzorec by teraz = {10,20,30; 40,50,60; 70,80,90}.

  3. Stlačením Kombinácie klávesov Ctrl + Shift + Enter zadajte konštanty 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 zobrazte celkový počet znakov v bunkách A2: A6 (66).

  3. Vyberte bunky A10, a potom stlačte Kombináciu klávesov Ctrl + Shift + Enter a zobrazte obsah najdlhšej bunky rozsahu a2: A6 (bunka A3).

Sa používa nasledujúci vzorec do bunky A8 vypočíta celkový počet znakov (66) v bunkách A2 až A6.

=SUM(LEN(A2:A6))

V tomto prípade funkcia LEN vráti dĺžku každý textový reťazec vo všetkých buniek v rozsahu. Funkcia SUM potom spolu pridá tieto hodnoty a zobrazí výsledok (66).

Vyhľadanie n najmenšie hodnoty v rozsahu

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

  1. Zadajte niekoľko náhodných čísel v bunkách A1:A11.

  2. Vyberte bunky C1 až C3. Nastavenie buniek bude obsahovať výsledky vrátené po vzorec 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štanty poľa na vyhodnotenie funkcie SMALL trikrát a vráti najmenšiu (1), druhý najmenší (2) a tretia najmenšia (3) členov v poli, ktorá je obsiahnutá v bunkách a1: A10 na vyhľadanie viacerých hodnôt, môžete pridať ďalšie argumenty funkcie konštanty. Pomocou tohto vzorca, ako napríklad SUM alebo AVERAGEmôžete použiť aj ďalšie funkcie. Príklad:

= SUM (MALÉ (A1: A10; {1,2,3})

= AVERAGE (MALÉ (A1: A10; {1,2,3})

Vyhľadanie n najväčšie hodnoty 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;ROW(INDIRECT("1:3")))

V tomto bode môže pomôcť trochu vedieť o riadok a funkcia INDIRECT funkcií. Ak chcete vytvoriť pole po sebe nasledujúcich celých čísel, môžete použiť funkcie ROW . Napríklad, vyberte prázdny stĺpec 10 buniek v praxi zošita, zadajte nasledujúci vzorec a 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.

Poďme sa pozrieť na vzorec, ktorý ste predtým použili – = LARGE (A5:A14,ROW(INDIRECT("1:3"))) – začínajúc od vnútorných zátvoriek a práci smerom von: Funkcia INDIRECT vráti množinu textových hodnôt v tomto prípade hodnoty 1 až 3. Funkcia ROW generuje tri bunky stĺpcovej poľa. Funkcia LARGE používa hodnoty v rozsahu buniek A5:A14 a sa vyhodnotí trikrát jedenkrát pre každý vráti funkcia ROW odkaz. Hodnoty 3 200, 2 700 a 2 000 sa vrátia do troch buniek stĺpcovej poľa. Ak chcete zistiť viac hodnôt, funkcia INDIRECT pridáte väčší rozsah buniek.

Ako s 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

Prejdite späť na predtým napríklad reťazec textu, zadajte nasledujúci vzorec do prázdnej bunky a stlačte Kombináciu klávesov Ctrl + Shift + Enter:

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

Text ": skupina buniek, ktoré" sa zobrazí.

Poďme Podrobný pohľad na vzorec, začínajúc od vnútorné prvky a práci smerom von. Funkcia LEN vráti dĺžku každá z položiek v rozsahu buniek A2: A6. Funkcia MAX vypočíta najväčšiu hodnotu medzi tieto položky, ktorá zodpovedá najdlhšieho textového reťazca, 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.

Napokon, funkcia INDEX má tieto argumenty: poľa a číslo riadka a stĺpca v rámci tohto poľa. Rozsah buniek A2: A6 poskytuje poľa, funkcia MATCH obsahuje adresu bunky a záverečný argument (1) určuje, či 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*(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))

Potvrdenie

Časti tohto článku na základe série Excel Power User stĺpce napísaný Colin Wilcox a prevzatý z kapitoly 14 a 15 2002 Excelu knihy napísaný John Walkenbach, bývalý MVP Excel.

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é vzorcov verzus staršie vzorce CSE

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.

×