seychellesartprojects.org

Seznam nejlepších 10 pokročilých vzorců a funkcí aplikace Excel

Tuto šablonu vzorců pro Excel můžete stáhnout zde - Šablona vzorců pro Excel a další

# 1 - VZHLED VZOREK v aplikaci Excel

Tato pokročilá funkce aplikace Excel je jedním z nejpoužívanějších vzorců v aplikaci Excel. Je to hlavně kvůli jednoduchosti tohoto vzorce a jeho aplikaci při vyhledávání určité hodnoty z jiných tabulek, která má v těchto tabulkách jednu společnou proměnnou. Předpokládejme, že máte dvě tabulky, které obsahují podrobnosti o platu a jménu zaměstnance společnosti, přičemž ID zaměstnance je primárním sloupcem. Chcete získat plat z tabulky B v tabulce A.

VLOOKUP můžete použít níže.

Výsledkem bude tabulka níže, když použijeme tento pokročilý vzorec aplikace Excel v jiných buňkách sloupce Plat zaměstnanců.

Přetáhněte vzorec do zbytku buněk.

Existují tři hlavní vymezení VLOOKUP:

1. Vpravo od sloupce, pro který chcete vyplnit hodnotu z jiné tabulky, nemůžete mít primární sloupec. V tomto případě nemůže být sloupec Plat zaměstnance před ID zaměstnance.
2. V případě duplikovaných hodnot v primárním sloupci v tabulce B bude první hodnota naplněna v buňce.
3. Pokud vložíte nový sloupec do databáze (Ex Vložit nový sloupec před Plat zaměstnance v tabulce B), výstup vzorce se může lišit na základě pozice, kterou jste ve vzorci uvedli (Ve výše uvedeném případě výstup bude prázdné)

# 2 - INDEX vzorec v aplikaci Excel

Tento pokročilý vzorec aplikace Excel se používá k získání hodnoty buňky v dané tabulce zadáním počtu řádků, sloupců nebo obou. Př. Chcete-li získat jméno zaměstnance na 5. pozorování, níže jsou data.

Můžeme použít pokročilý vzorec aplikace Excel, jak je uvedeno níže:

Stejný vzorec INDEX lze použít k získání hodnoty podél řádku. Při použití čísla řádku i sloupce by syntaxe vypadala asi takto:

Výše uvedený vzorec vrátí „Rajesh Ved“.

Poznámka: Pokud do dat v 5. řádku vložíte další řádek, vzorec vrátí „Chandan Kale“. Výstup by tedy závisel na všech změnách, ke kterým v průběhu času dojde v datové tabulce.

# 3 - MATCH vzorec v aplikaci Excel

Tento pokročilý vzorec aplikace Excel vrací číslo řádku nebo sloupce, pokud existuje shoda určitého řetězce nebo čísla v daném rozsahu. V níže uvedeném příkladu se snažíme najít pozici „Rajesh Ved“ ve sloupci Název zaměstnance.

Vzorec bude uveden níže:

Funkce MATCH vrátí hodnotu 5.

Třetí argument se používá pro přesnou shodu. Můžete také použít +1 a -1 na základě vašich požadavků.

Poznámka: Lze kombinovat INDEX a MATCH k překonání omezení VLOOKUP.

# 4 - IF AND Formula v aplikaci Excel

Existuje mnoho případů, kdy je třeba vytvořit příznaky na základě určitých omezení. Všichni známe základní syntaxi IF. Tuto pokročilou funkci excel IF používáme k vytvoření nového pole založeného na nějakém omezení již existujícího pole. Ale co když při vytváření vlajky musíme vzít v úvahu více sloupců. Př. V níže uvedeném případě chceme označit všechny zaměstnance, jejichž plat je vyšší než 50 tis., Ale ID zaměstnance větší než 3.

V takových případech bychom použili IF AND. Níže naleznete stejný snímek obrazovky.

Výsledek by se vrátil jako 0.

Můžeme mít mnoho podmínek nebo omezení k vytvoření příznaku založeného na více sloupcích pomocí AND.

# 5 - IF NEBO vzorec v aplikaci Excel

Podobně můžeme použít funkci OR v aplikaci Excel namísto AND, pokud potřebujeme splnit jednu z podmínek mnoha.

Ve výše uvedených případech, pokud je některá z podmínek splněna, budeme mít buňku naplněnou jako 1 jiná 0. Můžeme také nahradit 1 nebo 0 některými podřetězci s uvozovkami („“).

# 6 - SUMIF vzorec v aplikaci Excel

V některých analýzách možná budete muset filtrovat některá pozorování při použití funkce součet nebo počet. V takových případech je tato pokročilá funkce SUMIF v aplikaci Excel v aplikaci Excel k záchraně. Filtruje všechna pozorování na základě určitých podmínek uvedených v tomto pokročilém vzorci aplikace Excel a shrnuje je. Př. Co když chceme znát součet platů pouze těch zaměstnanců, kteří mají ID zaměstnance větší než 3.

Použitím vzorce SUMIFS:

Vzorec vrátí výsledky jako 322000.

Můžeme také spočítat počet zaměstnanců v organizaci, která má ID zaměstnance větší než 3, když místo SUMIF použijeme COUNTIF.

# 7 - KONCATENOVAT vzorec v aplikaci Excel

Tato pokročilá funkce aplikace Excel je jedním ze vzorců, které lze použít u více variant. Tento pokročilý vzorec aplikace Excel nám pomáhá spojit několik textových řetězců do jednoho textového řetězce. Např. Pokud chceme zobrazit ID zaměstnance a jméno zaměstnance v jednom sloupci.

Můžeme použít tento vzorec CONCATENATE právě k tomu.

Výsledkem výše uvedeného vzorce bude „1Aman Gupta“.

Můžeme mít ještě jednu variantu vložením jediné pomlčky mezi ID a NAME. Př. CONCATENATE (B3, ”-“, C3) bude mít za následek “1-Aman Gupta”. Můžeme to také použít do VLOOKUP, když je VYHLEDÁVÁNÍ v aplikaci Excel hodnota směsi více než jedné proměnné.

# 8 - LEVÝ, STŘEDNÍ a PRAVÝ vzorec v aplikaci Excel

Tento pokročilý vzorec aplikace Excel můžeme použít, pokud chceme z daného řetězce extrahovat určitý dílčí řetězec. Uvedené vzorce lze použít na základě našich požadavků. Př. Pokud chceme extrahovat prvních 5 znaků z názvu zaměstnance, můžeme použít levý vzorec v aplikaci Excel s názvem sloupce a druhým parametrem jako 5.

Výstup je uveden níže:

Aplikace PRAVÉHO vzorce v aplikaci Excel je také stejná, jde jen o to, že bychom se na postavu dívali zprava od řetězce. V případě funkce MID v aplikaci Excel však musíme uvést počáteční pozici požadovaného textového řetězce a délku řetězce.

# 9 - offsetový vzorec v aplikaci Excel

Tato pokročilá funkce aplikace Excel s kombinací dalších funkcí, jako je SUM nebo AVERAGE, může být užitečná při dynamickém doteku výpočtů. Nejlépe se to používá v případech, kdy vkládáme spojité řádky do existující databáze. OFFSET Excel nám dává rozsah, kde musíme zmínit referenční buňku, počet řádků a sloupců. Př. Pokud chceme vypočítat průměr prvních 5 zaměstnanců ve společnosti, kde máme plat zaměstnanců seřazený podle ID zaměstnance, můžeme udělat následující. Níže uvedený výpočet nám dá vždy plat.

• Dá nám součet platů prvních 5 zaměstnanců.

# 10 - TRIM vzorec v aplikaci Excel

Tento pokročilý vzorec aplikace Excel se používá k vyčištění nedůležitých mezer od textu. Př. Pokud chceme odstranit mezery na začátku nějakého jména, můžeme je použít pomocí funkce TRIM v aplikaci Excel, jak je uvedeno níže:

Výsledný výstup by byl „Chandan Kale“ bez prostoru před Chandanem.