Seznam nejlepších 10 pokročilých vzorců a funkcí aplikace Excel
# 1 - Vzorec VLOOKUP 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 použití 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 s podrobnostmi 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.
Můžete použít VLOOKUP 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:
- Vpravo od sloupce, pro který chcete vyplnit hodnotu z jiné tabulky, nemůžete mít primární sloupec. V takovém případě nemůže být sloupec Plat zaměstnance před ID zaměstnance.
- V duplikovaných hodnotách v primárním sloupci v tabulce B bude první hodnota naplněna v buňce.
- Pokud vložíte nový sloupec do databáze (Ex Vložte 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ýsledek 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 název zaměstnance na 5 th pozorování, Níže je uveden dat.
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í výraz „Rajesh Ved“.
Poznámka: Pokud vložíte další řádek do 5. ročník řá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 je v daném rozsahu konkrétní řetězec nebo číslo. V níže uvedeném příkladu se snažíme najít „Rajesh Ved“ ve sloupci Název zaměstnance.
Vzorec bude uveden níže:
Funkce MATCH vrátí hodnotu 5.
3 rd argumentace 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í 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 při použití COUNTIF namísto SUMIF.
# 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.
K tomu můžeme použít tento vzorec CONCATENATE.
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 k extrakci konkrétního dílčího řetězce z daného řetězce. Mohl by být použit 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 vzorce RIGHT v aplikaci Excel je také stejná. Je to jen to, že bychom se dívali na postavu 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 PRŮMĚR, může výpočtům dát dynamický nádech. Nejlépe se to používá v případech, kdy vkládáme spojité řádky do existující databáze. OFFSET Excel nám poskytuje 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.
