Excel je skvělý nástroj pro hlášení, analýzu, organizaci a automatizaci dat. Funkce Excelu velmi pomáhají při práci s daty. Funkce jako COUNTIFS, SUMIFS, VLOOKUP atd. Jsou nejsilnějšími a často používanými funkcemi od jejich vzniku ve světě Excel.
Ačkoli funkce dostupné v Excelu 2016 a starších jsou dostačující pro zpracování jakéhokoli druhu výpočtu a automatizace, ale někdy jsou vzorce složité. Pokud například u některých podmínek nenajdete maximální hodnotu, musíte ve starší verzi Excelu 2016 použít nějaké triky. Tyto druhy drobných, ale důležitých věcí řeší aplikace Excel 2019 a 365.
Existuje více než 10 nových funkcí v Excelu 2019 a 365, které snižují lidské úsilí a složitost vzorců.
1. Funkce MAXIFS
Pokud chcete v Excelu 2016 a starších získat maximální hodnotu v rozsahu, když se shoduje jedna nebo více podmínek, musíte s některými triky použít MAX s IF. To není moc obtížné, ale časově náročné a pro některé matoucí.
Excel 2019 zavádí novou funkci s názvem MAXIFS. Tato funkce vrací maximální hodnotu z pole, když jsou splněny všechny dané podmínky.
Syntaxe funkce je:
| = MAXIFS (max. Rozsah, rozsah_kritérií1, kritéria1, rozsah_kritérií2, kritéria2…) |
Max_range1: Je to numerický rozsah, který obsahuje maximální hodnotu.
Rozsah_kritérií1: Je to rozsah kritérií, který chcete filtrovat, než získáte maximální hodnotu.
Kritéria 1: Jedná se o kritéria nebo filtr, který chcete vložit na rozsah_kritérií, než získáte maximální hodnotu.
Předpokládejme, že potřebujete získat maximální známky ze třídy 3, pak bude vzorec
| = MAXIFS (známky, třída, 3) |
Zde značky jsou pojmenovaný rozsah, který obsahuje značky, a třída je pojmenovaný rozsah, který obsahuje třídu.
Zde si podrobně přečtěte o funkci MAXIFS.
2. Funkce MINIFS
Stejné jako funkce MAXIFS, funkce MINIFS se používá k získání minimální hodnoty z daného rozsahu, když jsou splněny všechny dané podmínky.
Syntaxe funkce je:
| = MINIFS (min. Rozsah, rozsah_kritérií1, kritéria1, rozsah_kritérií2, kritéria2… |
Min_range1: Je to číselný rozsah, který obsahuje minimální hodnotu.
Rozsah_kritérií1: Je to rozsah kritérií, který chcete filtrovat, než získáte minimální hodnotu.
Kritéria 1: Jedná se o kritéria nebo filtr, který chcete zadat na kritériu_rozsah, než získáte minimální hodnotu.
Předpokládejme, že potřebujete získat minimální známky ze třídy 3, pak bude vzorec
| = MINIFY (známky, třída, 3) |
Zde „značky“ je pojmenovaný rozsah, který obsahuje značky, a „třída“ je pojmenovaný rozsah, který obsahuje třídu.
Zde si podrobně přečtěte o funkci MAXIFS.
Chcete -li najít minimální hodnotu v rozsahu s podmínkami v aplikaci Excel 2016 a starší, přečtěte si toto.
3. Funkce IFS
Protože vnořené Ifs mají v našem každodenním pracovním životě zvláštní místo, velmi se nám líbí. Ale pro některé nové studenty je to složité. Vnořené ifs nám umožňují zkontrolovat více podmínek a vrátit jinou hodnotu, když je splněna některá z podmínek. Vzorce se stávají komplexnějšími s funkcí IF stále více.
Excel 2019 a Excel 365 nyní používají funkci IFS. Může zkontrolovat více podmínek a pro každou podmínku vrátit jiné hodnoty.
Syntaxe funkce IFS:
| = IFS (podmínka1, hodnota1_If_True, [podmínka2, hodnota2_If_True],…) |
Podmínka 1:První podmínka.
Hodnota1_If_True: Hodnota, pokud je splněna první podmínka.
[Podmínka2]: Toto je volitelné. Druhá podmínka, pokud nějakou máte.
[Hodnota1_If_True]: Hodnota, pokud je splněna druhá podmínka.
Můžete mít libovolný počet kombinací podmínek a hodnot. Existuje limit, ale nikdy jej nebudete muset dosáhnout.
Řekněme, že musíte studentům udělit známky známkami. U známek více než 80 známka A, B více než 60, C více než 40 a F méně než nebo rovno 40.
| = IFS (A1> 80, "A", A1> 60, "B", A1> 40, "C", A1 <= 40, "F") |
Podrobné vysvětlení funkce IFS naleznete zde.
4. Funkce SWITCH
Funkce přepínače vrací různé hodnoty v závislosti na výsledcích jednoho výrazu. Zní to jako IFS? Je to tak trochu Ve skutečnosti je tato funkce určena k nahrazení jiného druhu vnořených vzorců IF.
Na rozdíl od funkce IFS, která vrací hodnoty na základě TRUE, FALSE; funkce SWITCH vrací hodnoty na základě HODNOT vrácených výrazem.
| = SWITCH (výraz, hodnota1, výsledek1, [výchozí nebo hodnota2, výsledek2], …) |
Výraz: Může to být jakýkoli platný výraz, který vrací některé hodnoty. Odkaz na buňku, vzorec nebo statická hodnota.
Hodnota1, výsledek1: Hodnota a výsledek jsou spárovány. Pokud hodnota vrácenávýraz je hodnota1, pak bude vrácen výsledek1.
[Výchozí nebo hodnota2, výsledek2]: Pokud chcete vrátit nějakou výchozí hodnotu, definujte ji zde. Jinak definujte hodnotu2 a result2. Je to volitelné.
Například pokud máte vzorec, který vrací názvy zvířat. Nyní, v závislosti na vráceném jménu zvířete, chcete vrátit podpisový zvuk tohoto zvířete.
| = SPÍNAČ (A1, „Pes“, „Bow Wow“, „Kočka“, „Mňau“, „Mluví“) |
Zde jsem podrobně vysvětlil funkci SWITCH.
5. Funkce FILTR
Funkce FILTER se používá k filtrování dat na základě některých kritérií. Použili jsme možnost filtrování na domovské kartě v Excelu. Funkce FILTER funguje stejně jako volba filtru. Pouze vrací filtrovaná data pomocí funkce. Tato filtrovaná data lze použít jako zdroj dat pro jiné vzorce.
Syntaxe funkce FILTER je:
| = FILTER (pole, zahrnout, [if_empty]) |
Pole: Toto je pole, které chcete filtrovat. Může být jednorozměrný nebo dvourozměrný.
Zahrnout:Je to filtr, který chcete vložit do pole. Stejně jako barvy = "červená".
[if_empty]:Toto je volitelné. Pokud filtr nic nevrací, definujte libovolný text nebo výraz.
Níže uvedený vzorec vrací všechny plody, jejichž barva je červená.
| = FILTR (ovoce, barva = „červená“, „žádné ovoce nenalezeno“) |
Zde jsou plody a barvy pojmenovány rozsahy, které obsahují názvy plodů a jejich barvy.
Zde si můžete podrobně přečíst o funkci FILTR.
6. Funkce SORT
V Excelu 2016 a starších bylo opravdu složité získat seřazené pole pomocí vzorce. Tento proces je v Excelu 2019 a 365 zjednodušený.
Excel 2019 zavádí funkci SORT. Funkce SORT třídí dané pole ve vzestupném nebo sestupném pořadí podle daného sloupce/řádku.
Syntaxe funkce SORT je:
| = SORT (pole, [sort_index], [sort_order], [by_col]) |
Pole:Jedná se o odkaz na pole nebo rozsah, který chcete seřadit.
[sort_index]:Číslo sloupce v dvourozměrném poli, podle kterého chcete rozsah seřadit. Ve výchozím nastavení je to 1.
[order_order]:Pořadí, podle kterého chcete pole seřadit. Pro vzestup je 1 a pro sestup je -1. Ve výchozím nastavení je to 1.
[by_col]:Pokud chcete řadit horizontální pole, nastavte ho na True (1). Ve výchozím nastavení je pro svislá data nepravdivé (0).
Řekněme, že chcete třídit hodnoty v rozsahu A2: A11 vzestupně. pak bude vzorec.
| = SEŘADIT (A2: A11) |
Zde jsem podrobně vysvětlil funkci SORT.
7. Funkce SORTBY
Funkce SORTBY je podobná funkci SORT. Jediným rozdílem je, že třídící pole nemusí být součástí tříděného pole ve funkci SORTBY.
| = SORTBY (pole, sorting_array1, [pořadí], …) |
Pole:Toto je pole, které chcete řadit.
Sorting_array1:Toto je pole, podle kterého chcete pole seřadit. Dimenze tohoto pole by měla být kompatibilní s pole.
[objednat]:Volitelný. Pokud chcete, aby pořadí bylo sestupné, nastavte jej na -1. Ve výchozím nastavení je vzestupně (1).
Řekněme, že chcete seřadit rozsah A2: A11 podle rozsahu B2: B11, v sestupném pořadí. Potom vzorec v Excelu 2019 nebo 365 bude:
| = SORTBY (A2: A11, B2: B11, -1) |
Podrobně jsem zde vysvětlil funkci SORTBY.
8. JEDINEČNÁ funkce
V Excelu 2016 a starších verzích jsme použili řadu funkcí v kombinaci, abychom získali všechny jedinečné hodnoty z daného seznamu. Použitý vzorec je poměrně složitý a těžko pochopitelný.
Excel 2019 a 365 zavádí jednu jednoduchou JEDINEČNOU funkci, která vrací všechny jedinečné hodnoty z daného pole.
Syntaxe funkce UNIQUE je:
| = UNIQUE (pole, [by_col], [exactly_once]) |
Pole: Pole, ze kterého chcete extrahovat jedinečné hodnoty:
[by_col]: Pokud je pole horizontální, nastavte ho na hodnotu TRUE (1). Ve výchozím nastavení je pro svislá data NEPRAVDA.
[přesně_jedno]: nastavte jej na hodnotu TRUE (1), pokud chcete v poli extrahovat hodnoty, které se vyskytují pouze jednou. Ve výchozím nastavení je FALSE (0) extrahovat všechny jedinečné hodnoty.
Řekněme, že chci získat pouze jednu instanci každé hodnoty z rozsahu A2: A11, pak vzorec bude:
| = JEDINEČNÉ (A2: A11) |
Chcete -li si o funkci UNIQUE přečíst podrobně, můžete kliknout zde.
9. Funkce SEQUENCE
K získání posloupnosti čísel v Excelu 2016 a starších používáme kombinaci funkcí. Řešení funguje, ale je složité.
Excel 2019 a 365 poskytují řešení ve formě funkce SEQUENCE. Funkce sekvence jednoduše vrací řadu čísel.
Syntaxe funkce SEQUENCE je:
| = SEQUENCE (řádky, [sloupce], [začátek], [krok]) |
Řady:Počet řádků, na které chcete sekvenci rozlít.
[sloupec]:Počet sloupců, do kterých chcete sekvenci rozlít. Čísla nejprve vyplní sloupce a poté řádky. Sloupec je volitelný. Ve výchozím nastavení je to 1.
[Start]:Volitelný. Počáteční číslo sekvence. Ve výchozím nastavení je to 1.
[krok]:Toto je přírůstkové číslo pro další číslo. Ve výchozím nastavení je to 1.
Jednoduchým příkladem je získání řady 1 až 10. Vzorec bude:
| = SEKVENCE (10) |
Chcete -li podrobně porozumět funkci SEQUENCE v aplikaci Excel 365, přečtěte si toto.
10. Funkce RANDARRAY
Toto je další vzorec dynamického pole, který vrací pole náhodných čísel. Jedná se o kombinaci funkce RAND a RANDBETWEEN. Můžete získat zlomková náhodná čísla nebo celá čísla. Můžete určit požadovaný počet náhodných čísel. I řádky a sloupce, ve kterých chcete tato čísla distribuovat.
Syntaxe funkce RANDARRAY je:
| = RANDARRAY ([řádky], [sloupce], [min], [max], [celé číslo]) |
Všechny argumenty v této funkci jsou volitelné. Ve výchozím nastavení funguje jako funkce RAND.
[řádky]:Počet čísel, která chcete svisle (počet řádků, které chcete vyplnit).
[sloupce]:Počet čísel, která chcete vodorovně (počet sloupců, které chcete vyplnit).
[min]:Počáteční číslo nebo minimální hodnota náhodného čísla/s.
[max]:Maximální rozsah čísla.
[celé číslo]:Nastavte ji na true, pokud chcete, aby náhodná čísla byla celá čísla. Ve výchozím nastavení je false a vrací zlomková náhodná čísla.
Níže uvedená funkce vrátí po řádcích pět náhodných zlomkových čísel:
| = RANDARRAY (5) |
Zde si podrobně přečtěte o funkci RANDARRAY.
11. Funkce CONCAT
V aplikaci Excel 2016 a starších verzích není snadné zřetězit více než jednu buňku nebo rozsah pomocí jednoho vzorce.
Problém Excelu 2019 a 365 je vyřešen funkcí CONCAT. Funkce může mít jako argumenty více buněk, rozsahů.
Syntaxe funkce CONCAT je:
| = CONCAT (text1, [text2], …) |
Text1: Text1 může být libovolný text nebo rozsah, který chcete spojit.
[text2]: Toto je volitelné. Může to být také libovolný text nebo rozsah.
Řekněme, že pokud chcete spojit každou buňku v rozsahu A2: A11, pak vzorec bude
| = CONCAT (A2: A11) |
Chcete -li podrobně prozkoumat funkci CONCAT, klikněte sem.
12. Funkce TEXTJOIN
Výše uvedená funkce zřetězí všechny buňky v rozsahu, ale nespojí buňky žádným zadaným oddělovačem. Řekněme, že připravujete soubor pro CSV formát, než budete potřebovat zřetězit buňky čárkou. V takovém případě funkce CONCATENATE a CONCAT selžou.
Zde funkce TEXTJOIN funguje úžasně a spojuje dané texty s daným oddělovačem.
| = TEXTJOIN (oddělovač, ignore_empty_cells, text1, [text2], …) |
Oddělovač:Toto je oddělovač, který chcete použít oddělovač mezi jednotlivými texty. Může to být čárka (,), středník (;) nebo cokoli, dokonce i nic.
Ignorovat prázdné buňky:Toto je binární proměnná. Pokud chcete prázdné buňky v rozsahu ignorovat, nastavte ho na hodnotu TRUE, v opačném případě na FALSE prázdné buňky zahrňte.
Text1:Toto je text, ke kterému se chcete připojit. Může jít o jednotlivé texty, buňky nebo celé rozsahy.
Řekněme, že chci zřetězit rozsah A2: A11 čárkou, ignorovat prázdné buňky.
| = TEXTJOIN (",", 1, A2: A11) |
Chcete -li této funkci porozumět podrobně, klikněte sem.
Tento článek byl pouze úvodem do nové funkce Excelu 365 a 2019. Tyto funkce jsem podrobně vysvětlil v samostatných článcích. Chcete -li plně porozumět funkci, můžete kliknout na odkazy dostupné u každé funkce v článku. Existují i další funkce, jako je XLOOKUP, které dosud nebyly vydány.
Pokud máte nějaké pochybnosti ohledně témat aplikace Excel nebo VBA, zeptejte se v sekci komentáře níže. Řekněte nám, jak se můžeme zlepšit. Vážíme si vašeho podnětu a těšíme se na vaši odpověď.
Vytvořit funkci VBA pro návrat pole | Abychom vrátili pole z funkce definované uživatelem, musíme ho deklarovat, když pojmenujeme UDF.
Pole v aplikaci Excel Formul | Zjistěte, jaká pole jsou v aplikaci Excel.
Jak vytvořit funkci definovanou uživatelem prostřednictvím VBA | Zjistěte, jak v aplikaci Excel vytvářet funkce definované uživatelem
Použití funkce definované uživatelem (UDF) z jiného sešitu pomocí jazyka VBA v aplikaci Microsoft Excel | Použijte funkci definovanou uživatelem v jiném sešitu aplikace Excel
Vraťte chybové hodnoty z funkcí definovaných uživatelem pomocí jazyka VBA v aplikaci Microsoft Excel | Zjistěte, jak můžete vrátit chybové hodnoty z funkce definované uživatelem
Oblíbené články:
Rozdělit list aplikace Excel na více souborů na základě sloupce pomocí VBA | Tento kód VBA rozdělil list Excelu na jedinečné hodnoty v zadaném sloupci. Stáhněte si pracovní soubor.
Vypnout varovné zprávy pomocí VBA v aplikaci Microsoft Excel 2016 | K vypnutí varovných zpráv, které přerušují běžící kód VBA, používáme třídu Application.
Přidat a uložit nový sešit pomocí jazyka VBA v aplikaci Microsoft Excel 2016 | K přidání a uložení sešitů pomocí jazyka VBA používáme třídu Workbooks. Workbooks.Add přidává nový sešit snadno, ale…