Excel Advanced Formulas (obsah)
- Úvod do pokročilých vzorců v Excelu
- Příklady pokročilého vzorce v Excelu
Úvod do pokročilých vzorců v Excelu
Jakmile dosáhnete střední úrovně v Excelu, musíte tvrdě pracovat, abyste dosáhli pokročilé úrovně. Chcete-li postoupit na pokročilou úroveň, musíte si být vědomi některých často používaných pokročilých vzorců. V tomto článku se budu věnovat top 10 pokročilým vzorcům, které musí znát všichni vynikající studenti. V tomto článku se dozvíte a prozkoumáte.
Příklady pokročilého vzorce v Excelu
Podívejme se, jak používat pokročilé vzorce v Excelu s několika příklady.
Tuto šablonu pro pokročilé formule Excel si můžete stáhnout zde - šablonu pro pokročilé formule ExcelPříklad č. 1 - Částečná funkce VLOOKUP
Museli jste narazit na situaci, kdy VLOOKUP vyvolá chybu, i když ve vyhledávací hodnotě došlo k nepatrné chybě. Například pokud hledáte plat jménem Abhishek Sinha a pokud máte pouze Abhishek, pak VLOOKUP nemůže načíst data.
Pomocí znaku hvězdičky na obou koncích hodnoty vyhledávání však můžeme načíst data pro částečnou hodnotu vyhledávání.
V tabulce 1 mám plné jméno a plat, ale v tabulce 2 mám pouze částečná jména a musím najít plat každého zaměstnance.
Krok 1: Otevřete vzorec VLOOKUP v buňce E3. Před výběrem hodnoty vyhledávání umístěte hvězdičku (*) na obě strany hodnoty vyhledávání.
Krok 2: Jako obvykle můžete nyní dokončit vzorec VLOOKUP a my budeme mít výsledky.
Po použití výše uvedeného vzorce je výstup zobrazen níže.
Stejný vzorec se používá v jiných buňkách.
Příklad č. 2 - COUNTIFS se symboly operátora
K počítání věcí v seznamu musíte použít funkci COUNTIF a IFS. Můžeme také počítat na základě operátorových symbolů, jako je větší než (>) menší než (<) a znaménko rovnosti (=).
Nyní se podívejte například na níže uvedená data. Pokud chcete spočítat celkový počet faktur pro region JIŽNÍ po datu 10. ledna 2018, jak se počítáte?
Příklad č. 3 - POKUD podmínky s podmínkami AND & OR
Logické funkce jsou součástí našich každodenních činností. Musíte je zvládnout, abyste mohli postoupit na další úroveň. Pokud vypočítáváte bonus na základě více podmínek, musíte k splnění úkolu vnořit podmínku AND nebo OR s podmínkou IF.
Předpokládejme, že musíte vypočítat bonusovou částku pro každé oddělení na základě oddělení a let služby, které tyto funkce potřebujete. Na základě níže uvedených kritérií musíme vypočítat bonus.
Pokud služba trvá déle než 4 roky a oddělení je buď prodejní nebo podpůrný bonus je 50000, jinak bonus je 25 000.
Použijte níže uvedený vzorec pro získání bonusové částky.
Po použití výše uvedeného vzorce výstup níže.
Stejný vzorec použitelný v buňce E3 až E9.
Příklad č. 4 - TEXT Funkce, díky níž budete mít dynamiku
Řekněme, že udržujete denní prodejní stůl a musíte tabulku aktualizovat každý den. Na začátku tabulky je jeden nadpis s názvem „Konsolidovaná data o prodeji z DD-MM-RRRR na DD-MM-RRRR“. Při aktualizaci tabulky je třeba změnit datum záhlaví. Není to frustrující úkol dělat to samé znovu a znovu? Tuto hlavičku můžeme učinit dynamickou pomocí funkcí TETX, MIN a Max společně s zřetelnými symboly operátorů ampersand (&).
Příklad č. 5 - INDEX + MATCH + MAX k nalezení nejvyšší prodejní osoby
Máte seznam prodejních osob a prodejů, které provedli proti svému jménu. Jak zjistíte, kdo je nejlepším nebo nejvyšším prodejcem v seznamu? Samozřejmě máme několik dalších technik, které sdělí výsledek, ale tato funkce může vracet nejvyššího prodavače ze šarže.
Použijte níže uvedenou funkci a získejte nejvyšší jméno prodejce.
Po použití výše uvedeného vzorce výstup níže.
Příklad č. 6 - Získejte počet jedinečných hodnot ze seznamu
Pokud máte mnoho duplicitních hodnot a jste povinni sdělit, kolik jedinečných hodnot existuje v tom, jak to říkáte? Můžete to zjistit odstraněním duplicitní hodnoty, ale nejedná se o dynamický způsob sdělování jedinečného počtu hodnot.
Pomocí této funkce pole můžeme zjistit jedinečné hodnoty ze šarže.
Použijte níže uvedený vzorec pro získání seznamu jedinečných hodnot.
Příklad č. 7 - Použijte pojmenovaný rozsah k dynamizaci rozbalovací nabídky
Pokud často pracujete s rozevíracím seznamem a aktualizujete rozevírací seznam, musíte se vrátit do seznamu zdrojů, abyste smazali nebo přidali hodnoty. Poté se musíte vrátit k rozevíracím buňkám a znovu aktualizovat rozsah rozevíracího seznamu.
Pokud však pro svůj rozevírací seznam můžete vytvořit pojmenovaný rozsah, můžete rozevírací seznam učinit dynamickým a aktuálním.
Vytvořte rozsah názvů podle obrázku níže.
Nyní přejděte na rozevírací buňku a otevřete rozevírací dialogové okno.
Ve zdroji stiskněte klávesu F3, zobrazí se všechny definované názvy, vyberte název vašeho rozevíracího seznamu.
Dobře, rozevírací seznam je připraven a bude automaticky aktualizovat hodnoty, když dojde ke změně v rozevíracím seznamu.
Příklad č. 8 - Zbavte se hodnot chyb pomocí funkce IFERROR
Jsem si jist, že jste při práci s výpočty divize VLOOKUP zaznamenali chybové hodnoty. Zpracování těchto chybových hodnot je únavný úkol. Tyto chybové hodnoty se ale můžeme zbavit pomocí funkce IFERROR ve vzorci.
Po použití výše uvedeného vzorce výstup níže.
Stejný vzorec používaný v jiných buňkách.
Příklad č. 9 - Pomocí funkce PMT vytvořte svůj vlastní graf EMI
V dnešní době není možnost EMI pro nás všechny zvláštní. Ve výborné podobě můžeme odhadnout vlastní graf EMI pomocí funkce PMT. Vytvořte si vlastní graf podle níže uvedených kroků.
Použijte níže uvedený vzorec v buňce B4 a získejte částku EMI.
Příklad č. 10 - INDEX + MATCH jako alternativa k funkci VLOOKUP
Doufám, že jste si vědomi omezení funkce VLOOKUP. Jedním z hlavních omezení je, že VLOOKUP dokáže načíst data zleva doprava, nikoli zprava doleva. Ne vždy jsou data dobře organizovaná a připravená k použití. Často hledaný sloupec hodnot je na levé straně hodnoty vyhledávání, takže VLOOKUP v těchto případech nepomůže.
Kombinace funkce INDEX + MATCH slouží jako alternativa k funkci VLOOKUP.
Po použití výše uvedeného vzorce je výstup zobrazen níže.
Stejný vzorec se používá v jiných buňkách.
Na základě ID produktu musíme extrahovat prodejní hodnoty. V hlavní tabulce je ID produktu na pravé straně sloupce prodeje. VLOOKUP nemůže data načíst. K načtení dat použijte níže uvedený vzorec.
Doporučené články
Toto je průvodce pro pokročilé vzorce v Excelu. Zde diskutujeme o tom, jak používat pokročilé vzorce v Excelu spolu s praktickými příklady a šablonou Excel ke stažení. Můžete si také prohlédnout naše další doporučené články -
- Jak používat Excel INDEX?
- Rozšířené Excel | Funkce databáze
- Příklady vzorce TRIM v Excelu
- Průvodce po vzorci Excel OFFSET