Excel je vynikajúci nástroj na vytváranie prehľadov, analýzu, organizáciu a automatizáciu údajov. Funkcie programu Excel veľmi pomáhajú pri práci s údajmi. Funkcie ako COUNTIFS, SUMIFS, VLOOKUP atď. Sú najsilnejšími a najčastejšie používanými funkciami od ich vzniku vo svete Excel.
Aj keď funkcie dostupné v Exceli 2016 a staršom stačia na to, aby dokázali vykonať akýkoľvek druh výpočtu a automatizácie, ale niekedy sú vzorce zložité. Ak napríklad pri niektorých podmienkach nenájdete maximálnu hodnotu, musíte v staršej verzii Excelu 2016 použiť niekoľko trikov. Tieto druhy drobných, ale dôležitých vecí sú vyriešené v Exceli 2019 a 365.
V Exceli 2019 a 365 je k dispozícii viac ako 10 nových funkcií, ktoré znižujú ľudské úsilie a zložitosť vzorcov.
1. Funkcia MAXIFS
V Exceli 2016 a staršom, ak chcete získať maximálnu hodnotu v rozsahu, keď sa zhoduje jedna alebo viac podmienok, musíte pri niektorých trikoch použiť MAX s IF. Nie je to nič ťažké, ale niekomu to zabrať čas.
Excel 2019 predstavuje novú funkciu s názvom MAXIFS. Táto funkcia vráti maximálnu hodnotu z poľa, keď sú splnené všetky dané podmienky.
Syntax funkcie je:
| = MAXIFS (max. Rozsah, rozsah_kritérií1, kritérium1, rozsah_kritérií2, kritérium2 …) |
Max_rozsah1: Je to číselný rozsah, ktorý obsahuje maximálnu hodnotu.
Rozsah kritérií_1: Ide o rozsah kritérií, ktorý chcete filtrovať pred získaním maximálnej hodnoty.
Kritériá 1: Ide o kritériá alebo filter, ktoré chcete vložiť do rozsahu_kritérií pred získaním maximálnej hodnoty.
Predpokladajme, že potrebujete získať maximálne známky z triedy 3, potom bude vzorec
| = MAXIFS (známky, trieda, 3) |
Tu sú značky pomenovaný rozsah, ktorý obsahuje značky, a trieda je pomenovaný rozsah, ktorý obsahuje triedu.
Podrobne si prečítajte o funkcii MAXIFS tu.
2. Funkcia MINIFS
Rovnako ako funkcia MAXIFS, aj funkcia MINIFS sa používa na získanie minimálnej hodnoty z daného rozsahu, ak sú splnené všetky dané podmienky.
Syntax funkcie je:
| = MINIFS (min. Rozsah, rozsah_kritérií1, kritérium1, rozsah_kritérií2, kritérium2 … |
Min_range1: Je to číselný rozsah, ktorý obsahuje minimálnu hodnotu.
Rozsah kritérií_1: Ide o rozsah kritérií, ktorý chcete filtrovať pred získaním minimálnej hodnoty.
Kritériá 1: Ide o kritériá alebo filter, ktoré chcete vložiť do rozsahu_kritérií pred získaním minimálnej hodnoty.
Predpokladajme, že musíte získať minimálne známky z triedy 3, potom bude vzorec
| = MINIFS (známky, trieda, 3) |
Tu „značky“ predstavujú pomenovaný rozsah, ktorý obsahuje značky, a „trieda“ je pomenovaný rozsah obsahujúci triedu.
Podrobne si prečítajte o funkcii MAXIFS tu.
Ak chcete nájsť minimálnu hodnotu z rozsahu s podmienkami v Exceli 2016 a starších, prečítajte si toto.
3. Funkcia IFS
Pretože vnorené Ifs majú v našom každodennom pracovnom živote špeciálne miesto, veľmi sa nám to páči. Ale pre niektorých nových študentov je to zložité. Vnorené ifs nám umožňujú skontrolovať viac podmienok a vrátiť inú hodnotu, keď je splnená ktorákoľvek z týchto podmienok. Vzorce sú stále komplexnejšie s funkciou IF stále viac.
Excel 2019 a Excel 365 teraz používajú funkciu IFS. Môže kontrolovať viacero podmienok a pre každú podmienku vrátiť rôzne hodnoty.
Syntax funkcie IFS:
| = IFS (podmienka1, hodnota1_If_True, [podmienka2, hodnota2_If_True], …) |
Podmienka 1:Prvá podmienka.
Hodnota1_If_True: Hodnota, ak je splnená prvá podmienka.
[Podmienka2]: Toto je voliteľné. Druhá podmienka, ak nejakú máte.
[Hodnota1_If_True]: Hodnota, ak je splnená druhá podmienka.
Môžete mať ľubovoľný počet kombinácií podmienok a hodnôt. Existuje limit, ale nikdy ho nebudete musieť dosiahnuť.
Povedzme, že musíte žiakom udeliť známky tam. Pre známky viac ako 80, stupeň A, B pre viac ako 60, C pre viac ako 40 a F pre menej ako alebo rovných 40.
| = IFS (A1> 80, "A", A1> 60, "B", A1> 40, "C", A1 <= 40, "F") |
Podrobné vysvetlenie funkcie IFS nájdete tu.
4. Funkcia SWITCH
Funkcia prepínača vracia rôzne hodnoty v závislosti od výsledkov jedného výrazu. Znie to ako IFS? To je druh. V skutočnosti je táto funkcia na nahradenie iného druhu vnorených vzorcov IF.
Na rozdiel od funkcie IFS, ktorá vracia hodnoty na základe TRUE, FALSE; funkcia SWITCH vracia hodnoty založené na HODNOTÁCH vrátených výrazom.
| = SWITCH (výraz, hodnota1, výsledok1, [predvolené alebo hodnota2, výsledok2], …) |
Výraz: Môže to byť ľubovoľný platný výraz, ktorý vracia niektoré hodnoty. Odkaz na bunku, vzorec alebo statická hodnota.
Hodnota1, výsledok1: Hodnota a výsledok sa spárujú. Ak hodnota vrátenávýraz je hodnota1, potom sa vráti výsledok1.
[Predvolená hodnota alebo hodnota2, výsledok2]: Ak chcete vrátiť predvolenú hodnotu, definujte ju tu. Inak definujte hodnotu2 a výsledok2. Je to voliteľné.
Ak máte napríklad vzorec, ktorý vracia názvy zvierat. Teraz, v závislosti od vráteného mena zvieraťa, chcete vrátiť podpisový zvuk tohto zvieraťa.
| = SPÍNAČ (A1, „Pes“, „Bow Wow“, „Mačka“, „Mňau“, „Hovorí“) |
Tu som podrobne vysvetlil funkciu SWITCH.
5. Funkcia FILTER
Funkcia FILTER sa používa na filtrovanie údajov na základe niektorých kritérií. Použili sme možnosť filtra na domovskej karte v programe Excel. Funkcia FILTER funguje rovnako ako voľba filtra. Jednoducho vráti filtrované údaje pomocou funkcie. Tieto filtrované údaje je možné použiť ako zdroj údajov pre iné vzorce.
Syntax funkcie FILTER je:
| = FILTER (pole, zahrnúť, [if_empty]) |
Pole: Toto je pole, ktoré chcete filtrovať. Môže byť jednorozmerný alebo dvojrozmerný.
Zahrnúť:Je to filter, ktorý chcete vložiť do poľa. Páči sa mi, farby = "červená".
[if_empty]:Toto je voliteľné. Ak filter nič nevracia, definujte ľubovoľný text alebo výraz.
Nasledujúci vzorec vráti všetky plody, ktorých farba je červená.
| = FILTER (ovocie, farba = „červená“, „nenašlo sa žiadne ovocie“) |
Tu sú ovocie a farba pomenované rady, ktoré obsahujú názvy plodov a ich farby.
Podrobne si o funkcii FILTER môžete prečítať tu.
6. Funkcia SORT
V Exceli 2016 a staršom bolo skutočne náročné získať usporiadané pole pomocou vzorca. Tento postup je v Exceli 2019 a 365 zjednodušený.
Excel 2019 uvádza funkciu Triediť. Funkcia SORT zoradí dané pole vzostupne alebo zostupne podľa daného stĺpca/riadka.
Syntax funkcie SORT je:
| = SORT (pole, [sort_index], [sort_order], [by_col]) |
Pole:Je to odkaz na pole alebo rozsah, ktorý chcete triediť.
[sort_index]:Číslo stĺpca v dvojrozmernom poli, podľa ktorého chcete zoradiť rozsah. Štandardne je to 1.
[order_order]:Poradie, podľa ktorého chcete zoradiť pole. Pre stúpanie je 1 a pre zostup je -1. Štandardne je to 1.
[by_col]:Ak chcete zoradiť horizontálne pole, nastavte ho na True (1). V predvolenom nastavení je pre vertikálne údaje nepravdivé (0).
Povedzme, že chcete zoradiť hodnoty v rozsahu A2: A11 vzostupne. potom bude vzorec.
| = Triediť (A2: A11) |
Tu som podrobne vysvetlil funkciu SORT.
7. Funkcia SORTBY
Funkcia SORTBY je podobná funkcii SORT. Jediným rozdielom je, že triediace pole nemusí byť súčasťou triedeného poľa vo funkcii SORTBY.
| = SORTBY (pole, sorting_array1, [poradie], …) |
Pole:Toto je pole, ktoré chcete triediť.
Sorting_array1:Toto je pole, podľa ktorého chcete pole zoradiť. Rozmer tohto poľa by mal byť kompatibilný s formátom pole.
[objednať]:Voliteľné. Ak chcete, aby bolo poradie zostupné, nastavte ho na -1. Štandardne je vzostupný (1).
Povedzme, že chcete zoradiť rozsah A2: A11 podľa rozsahu B2: B11, v zostupnom poradí. Potom vzorec v Exceli 2019 alebo 365 bude:
| = SORTBY (A2: A11, B2: B11, -1) |
Podrobne som tu vysvetlil funkciu SORTBY.
8. JEDINEČNÁ funkcia
V Exceli 2016 a staršom sme použili niekoľko funkcií v kombinácii, aby sme z daného zoznamu získali všetky jedinečné hodnoty. Použitý vzorec je dosť zložitý a ťažko zrozumiteľný.
Excel 2019 a 365 zavádzajú jednu jednoduchú UNIKÁTNU funkciu, ktorá vracia všetky jedinečné hodnoty z daného poľa.
Syntax funkcie UNIQUE je:
| = JEDINEČNÉ (pole, [podľa_col], [presne_jedno]) |
Array: Pole, z ktorého chcete extrahovať jedinečné hodnoty:
[by_col]: Ak je pole horizontálne, nastavte ho na hodnotu TRUE (1). Štandardne je pre vertikálne údaje FALSE.
[exactly_once]: nastavte ho na hodnotu TRUE (1), ak chcete extrahovať hodnoty, ktoré sa v poli vyskytujú iba raz. Štandardne je extrahovanie všetkých jedinečných hodnôt FALSE (0).
Povedzme, že chcem získať iba jednu inštanciu každej hodnoty z rozsahu A2: A11, potom vzorec bude:
| = JEDINEČNÉ (A2: A11) |
Kliknite sem a prečítajte si podrobne o funkcii UNIQUE.
9. Funkcia SEQUENCE
Na získanie postupnosti čísel v programe Excel 2016 a starších používame kombináciu funkcií. Riešenie funguje, ale je zložité.
Excel 2019 a 365 poskytujú riešenie vo forme funkcie SEQUENCE. Funkcia sekvencie jednoducho vráti sériu čísel.
Syntax funkcie SEQUENCE je:
| = SEQUENCE (riadky, [stĺpce], [začiatok], [krok]) |
Riadky:Počet riadkov, do ktorých chcete rozliať sekvenciu.
[stĺpec]:Počet stĺpcov, do ktorých chcete rozliať sekvenciu. Čísla najskôr vyplnia stĺpce a potom riadky. Stĺpec je voliteľný. Štandardne je to 1.
[začiatok]:Voliteľné. Počiatočné číslo sekvencie. Štandardne je to 1.
[krok]:Toto je prírastkové číslo pre ďalšie číslo. Štandardne je to 1.
Jednoduchým príkladom je získanie série od 1 do 10. Vzorec bude:
| = SEKVENCIA (10) |
Ak chcete podrobne porozumieť funkcii SEQUENCE v programe Excel 365, prečítajte si to.
10. Funkcia RANDARRAY
Toto je ďalší vzorec dynamického poľa, ktorý vracia pole náhodných čísel. Je to kombinácia funkcie RAND a RANDBETWEEN. Môžete získať zlomkové náhodné čísla alebo celé čísla. Môžete určiť požadovaný počet náhodných čísel. Dokonca aj riadky a stĺpce, v ktorých chcete tieto čísla distribuovať.
Syntax funkcie RANDARRAY je:
| = RANDARRAY ([riadky], [stĺpce], [min], [max], [celé číslo]) |
Všetky argumenty v tejto funkcii sú voliteľné. Štandardne funguje ako funkcia RAND.
[riadky]:Počet čísel, ktoré chcete zvisle (počet riadkov, ktoré chcete vyplniť).
[stĺpce]:Počet čísel, ktoré chcete vodorovne (počet stĺpcov, ktoré chcete vyplniť).
[min]:Počiatočné číslo alebo minimálna hodnota náhodného čísla/s.
[max]:Maximálny rozsah čísla.
[celé číslo]:Nastavte ju na pravda, ak chcete, aby náhodné čísla boli celé čísla. Štandardne je nepravdivý a vracia zlomkové náhodné čísla.
Nasledujúca funkcia vráti po riadkoch päť náhodných zlomkových čísel:
| = RANDARRAY (5) |
Podrobne si prečítajte o funkcii RANDARRAY tu.
11. Funkcia CONCAT
V programe Excel 2016 a staršom nie je ľahké zreťaziť viac ako jednu bunku alebo rozsah pomocou jedného vzorca.
Problém Excel a 365 je problém vyriešený funkciou CONCAT. Funkcia môže brať ako argumenty viacero buniek alebo rozsahov.
Syntax funkcie CONCAT je:
| = CONCAT (text1, [text2], …) |
Text1: Text1 môže byť ľubovoľný text alebo rozsah, ktorý chcete spojiť.
[text2]: Toto je voliteľné. Môže to byť aj ľubovoľný text alebo rozsah.
Povedzme, že ak chcete zreťaziť každú bunku v rozsahu A2: A11, potom bude vzorec
| = CONCAT (A2: A11) |
Kliknite sem, ak chcete podrobne preskúmať funkciu CONCAT.
12. Funkcia TEXTJOIN
Vyššie uvedená funkcia zreťazí všetky bunky v rozsahu, ale nespája bunky so zadaným oddeľovačom. Povedzme, že pripravujete súbor pre formát CSV, ako budete potrebovať na zreťazenie buniek čiarkou. V takom prípade funkcie CONCATENATE a CONCAT zlyhajú.
Tu funkcia TEXTJOIN funguje zázračne a spája dané texty s daným oddeľovačom.
| = TEXTJOIN (oddeľovač, ignore_empty_cells, text1, [text2], …) |
Oddeľovač:Toto je oddeľovač, ktorý chcete použiť oddeľovač medzi jednotlivými textami. Môže to byť čiarka (,), bodkočiarka (;) alebo čokoľvek, dokonca nič.
Ignorovať_prázdne_cely:Toto je binárna premenná. Ak chcete ignorovať prázdne bunky v rozsahoch, nastavte ho na hodnotu TRUE. Ak chcete prázdne bunky zahrnúť, nastavte na hodnotu FALSE.
Text1:Toto je text, ku ktorému sa chcete pripojiť. Môžu to byť jednotlivé texty, bunky alebo celé rozsahy.
Povedzme, že chcem spojiť rozsah A2: A11 s čiarkou, ignorujúc prázdne bunky.
| = TEXTJOIN (",", 1, A2: A11) |
Ak chcete podrobne porozumieť tejto funkcii, kliknite sem.
Tento článok bol len úvodom do novej funkcie Excelu 365 a 2019. Tieto funkcie som podrobne vysvetlil v samostatných článkoch. Kliknutím na odkazy dostupné pre každú funkciu v článku porozumiete tejto funkcii úplne. Existujú aj ďalšie funkcie, ako napríklad XLOOKUP, ktoré ešte neboli vydané.
Ak máte akékoľvek pochybnosti týkajúce sa tém programu Excel alebo VBA, opýtajte sa ich v sekcii komentárov nižšie. Povedzte nám, ako sa môžeme zlepšiť. Ceníme si váš návrh a tešíme sa na vašu odpoveď.
Vytvorte funkciu VBA na vrátenie poľa | Ak chcete vrátiť pole z funkcie definovanej používateľom, musíme ho deklarovať pri pomenovaní UDF.
Polia v programe Excel Formul | Zistite, aké polia sú v programe Excel.
Ako vytvoriť funkciu definovanú používateľom prostredníctvom VBA | Naučte sa vytvárať v programe Excel funkcie definované používateľom
Použitie funkcie definovanej používateľom (UDF) z iného zošita pomocou jazyka VBA v programe Microsoft Excel | Používateľom definovanú funkciu použite v inom zošite programu Excel
Vráťte chybové hodnoty z funkcií definovaných používateľom pomocou jazyka VBA v programe Microsoft Excel | Zistite, ako môžete vrátiť chybové hodnoty z funkcie definovanej používateľom
Populárne články:
Rozdeľte hárok programu Excel do viacerých súborov na základe stĺpca pomocou VBA | Tento kód VBA rozdelil základný list programu Excel na jedinečné hodnoty v zadanom stĺpci. Stiahnite si pracovný súbor.
Vypnúť výstražné správy pomocou VBA v programe Microsoft Excel 2016 | Na vypnutie výstražných správ, ktoré prerušujú spustený kód VBA, používame triedu Application.
Pridať a uložiť nový zošit pomocou jazyka VBA v programe Microsoft Excel 2016 | Na pridávanie a ukladanie zošitov pomocou jazyka VBA používame triedu zošity. Zošity. Pridajte nový zošit ľahko, ale…