Tabulkové tabulky Excelu (obsah)
- Tabulkové vzorce v Excelu
- Jak používat tabulkové formáty v Excelu?
Tabulkové vzorce v Excelu
Tabulka je plná vzorců. Zaprvé se nemusíte zaměňovat s tabulkou a obě tabulky jsou stejné. V tomto článku budeme hovořit o nejdůležitějších vzorcích v Excelu a jak je používáme v našich každodenních činnostech.
Jak používat tabulkové formáty v Excelu?
Tabulkové vzorce v Excelu jsou velmi jednoduché a snadno se používají. Toto je průvodce formulacemi v Excelu s podrobnými příklady tabulkových vzorců. Podívejme se, jak používat tabulkové formule v Excelu s několika příklady.
SUMPRODUCT Formula v tabulkovém procesoru Excel - Příklad č. 1
Pokud chceme provést výpočet jednotkové ceny * prodané jednotky, provedeme individuální výpočet a nakonec sečteme celkovou částku, abychom získali celkový prodej. Podívejte se například na níže uvedený příklad.
Nejprve vypočítáme celkový prodej vynásobením prodaných jednotek jednotkovou cenou, jak jsem viděl na obrázku níže.
Nakonec přidáme celkový prodej, abychom získali konečnou celkovou prodejní částku.
To zahrnuje mnoho kroků k získání konečné částky prodeje. Pomocí funkce SUMPRODUCT však můžeme získat součet v jediném vzorci samotném.
V prvním poli vyberte rozsah Jednotky prodané .
Musíme provést násobení, takže zadejte symbol hvězdičky (*) a vyberte Jednotková cena.
Ach ano, máme konečný výsledek v jednom vzorci v jediné buňce samotné. Jak je to v pohodě?
Vzorec k SUM Top N hodnot v tabulce Excel - Příklad # 2
Všichni pracujeme na funkci SUM v tabulkách Excelu v Excelu den co den v tabulkách Excelu, to pro nás není divná věc. Jak ale sčítáme top 3 nebo top 5 hodnot nebo top X hodnot?
Vypadá to, že nová věc, že? Ano, můžeme SUM Top N hodnot ze seznamu. Podívejte se například na níže uvedená data.
Díky kombinaci funkce SUM & LARGE v tabulkovém procesoru Excel můžeme sčítat nejvyšší hodnoty n ze seznamu. Funkce LARGE nám pomáhá najít nejvyšší největší hodnotu.
Abych našel top 3 hodnoty, použil jsem pro tuto funkci SUM vzorec funkce SUM. Zadal jsem ještě jednu funkci, LARGE, abych získal nejvyšší N hodnoty. Funkce LARGE v tabulkovém procesoru Excelu může vrátit pouze jednu největší, aby nalezla nejvyšší hodnoty N. Musíme zadat čísla v složených závorkách ( ( ). Nyní LARGE vrátí nejvyšší 3 největší hodnoty a funkce SUM přidá tato 3 čísla a dá nám součet.
Podobně, pokud chci součet top 5 hodnot ze seznamu v Excel Excel, musím zadat čísla do 5 v složených závorkách. Nyní LARGE vrátí prvních 5 největších hodnot a funkce SUM přidá těchto 5 čísel a dá součet.
SUMIF vzorec se symboly operátora v tabulce Excel - Příklad č. 3
Při práci s tabulkou Excelu jste museli pracovat s funkcí SUMIF a jste také v pořádku. Kritéria však můžeme předat také do funkčního vzorce SUMIF se symbolem operátora. Příklad viz níže uvedený příklad.
Musíme udělat sčítání všech hodnot, které jsou větší než 400. Pro jistotu musíme použít funkci SUMIF, ale v kritériích musíme zadat symbol operátora větší než (>) a uvést kritéria jako 400.
Ano, musíme zadat symbol operátora kritérií ve dvojitých uvozovkách (““) a ostatní kritéria kombinovat se symbolem ampersand (&). Nyní jej vzorec přečte jako větší než 400 a vrátí součet.
Označil jsem všechny hodnoty, které jsou větší než 400, žlutou barvou, můžete je přidat ručně, dostanete stejný součet jako 2190.
Tabulka pro všechny druhy výpočtů v tabulkovém procesoru Excel - Příklad č. 4
Často musíme udělat součet hodnot, často potřebujeme počet, často průměr čísel a mnoho dalších věcí. Jak se vyrovnáte se všemi těmito požadavky v jednom vzorci?
Předpokládejme níže, jsou data, která máte v tabulce Excel.
Krok 1: Převeďte tento rozsah na tabulku stisknutím kláves Ctrl + T.
Krok 2: Umístěte kurzor dovnitř tabulky> přejděte na Návrh > Ve skupinovém rámečku Možnosti stylu tabulky zaškrtněte možnost Celkový řádek.
Krok 3: Nyní máme na konci tabulky celkem řádek tabulky.
Krok 4: Pokud však pozorujete, nejedná se pouze o vzorec, ale o rozevírací seznam. Kliknutím na rozevírací seznam zobrazíte další informace.
Pomocí této rozbalovací nabídky můžeme použít průměr, počet, najít maximální hodnotu, najít minimální hodnotu a mnoho dalších věcí. Na základě výběru, který provedeme z rozbalovací nabídky, se zobrazí výsledky.
AVERAGEIF vzorec v tabulkovém procesoru Excel - Příklad č. 5
Funkce AVERAGE pro nás není divná věc. V okamžiku, kdy potřebujeme průměr hodnot, použijeme vzorec funkce AVERAGE v tabulkovém procesoru Excel a získáme výsledek. Pokud například potřebuji průměr z níže uvedených čísel, použiji bych funkci AVERAGE.
Co když budeme potřebovat Průměr pouze za čtvrtletí Q1, jak to uděláme? Můžeme dělat manuální práci, ale my všichni nenávidíme manuální práci, že?
Není čeho se obávat, máme funkci nazvanou AVERAGEIF v tabulkovém procesoru Excel.
Rozsah není nic jiného než náš rozsah čtvrtletí, takže vyberte rozsah čtvrtletí od A2 do A19.
Kritéria je to, co chcete v tomto rozsahu dělat. Naším kritériem je Q1.
Průměrný rozsah není nic jiného než sloupec prodeje, který vybere sloupec Prodej a uzavře závorku. Dostaneme pouze průměr Q1.
IFERROR vzorec, jak se vyhnout chybám v tabulkovém procesoru Excel - Příklad č. 6
Určitě jste narazili na mnoho chyb při používání vzorců. Často musíme tyto chyby ignorovat a tyto chybové hodnoty vymazat podle našich vlastních hodnot. Podívejte se například na níže uvedené ilustrace.
Na obrázku výše jsme dostali chybové hodnoty. Chci převést všechny chybové hodnoty na Nenalezeno. Použijte níže uvedený vzorec a získejte výsledek.
Pokud VLOOKUP vrátí chybové hodnoty, IFERROR jej převede na textovou hodnotu Nenalezeno.
Funkce TEXT spojující hodnoty data v tabulkovém procesoru Excel - Příklad č. 7
Pomocí funkce CONCATENATE můžeme spojit mnoho dat buněk do jedné. Toto však nefunguje podobným způsobem, pokud kombinujete data s textem v tabulkovém procesoru Excel. Podívejte se na obrázek níže.
Když jsem spojil buňky A1 a A2, dostal jsem výsledek těchto dvou hodnot buněk. Datum však není ve správném formátu, aby byl tento formát perfektní, musíme použít funkci TEXT, abychom mohli použít náš formát data.
Níže je uveden vzorec, díky kterému je tato věta správná.
Funkce TEXT spojující časové hodnoty v tabulkovém procesoru Excel - Příklad č. 8
Stejně jako jsme dostali nesprávný formát pro datum podobně jako pro časový formát, dostáváme také nesprávný formát.
Musíme předat časovou buňku s funkcí TEXT a převést ji do formátu „hh: mm: ss“ v tabulkovém procesoru Excel.
Co si pamatovat
- Pomocí funkce CONCATENATE můžeme spojit mnoho dat buněk do jedné.
- Abychom správně nastavili formát data, musíme použít funkci TEXT, abychom mohli použít náš formát data.
- Pomocí funkce SUMPRODUCT můžeme získat součet v jediném vzorci samotném.
Tuto tabulku vzorců Excelu si můžete stáhnout - tabulku vzorců Excelu
Doporučené články
Toto byl průvodce tabulkovými formáty v Excelu. Zde jsme diskutovali o různých vzorcích tabulky v Excelu, Jak používat tabulky vzorců v Excelu spolu s praktickými příklady a stahovatelnou šablonou Excel. Můžete také projít naše další doporučené články-
- Základní vzorce v Excelu
- Vzorec pro rozdělení v Excelu
- Top 25 pokročilých vzorců a funkcí v Excelu
- Průvodce automatickým vyplněním v Excelu
- Rozšířené vzorce v Excelu