Zoznam top 10 pokročilých vzorcov a funkcií programu Excel
# 1 - Vzorec VLOOKUP v programe Excel
Táto pokročilá funkcia programu Excel je jedným z najbežnejšie používaných vzorcov v programe Excel. Je to hlavne kvôli jednoduchosti tohto vzorca a jeho aplikácii pri vyhľadávaní určitej hodnoty z iných tabuliek, ktorá má v týchto tabuľkách jednu spoločnú premennú. Predpokladajme, že máte dve tabuľky s podrobnosťami o plate a mene zamestnanca spoločnosti, pričom primárnym stĺpcom je ID zamestnanca. Chcete získať plat z tabuľky B v tabuľke A.
Môžete použiť VLOOKUP ako je uvedené nižšie.
Výsledkom bude táto tabuľka, keď použijeme tento rozšírený vzorec programu Excel v iných bunkách stĺpca Plat zamestnancov.
Potiahnite vzorec do zvyšku buniek.
Existujú tri hlavné vymedzenia VLOOKUPU:
- V pravej časti stĺpca, pre ktorý chcete vyplniť hodnotu z inej tabuľky, nemôžete mať primárny stĺpec. V takom prípade nemôže byť stĺpec Plat zamestnanca pred ID zamestnanca.
- V duplikovaných hodnotách v primárnom stĺpci tabuľky B sa do bunky zaplní prvá hodnota.
- Ak vložíte nový stĺpec do databázy (Ex Vložte nový stĺpec pred Plat zamestnanca v tabuľke B), výstup vzorca sa môže líšiť na základe pozície, ktorú ste vo vzorci uviedli (V takom prípade bude výsledok by bolo prázdne)
# 2 - INDEX vzorec v programe Excel
Tento pokročilý vzorec programu Excel sa používa na získanie hodnoty bunky v danej tabuľke zadaním počtu riadkov, stĺpcov alebo oboch. Pr. Ak chcete získať názov zamestnanca na 5 th pozorovanie, Nižšie je uvedený dát.
Môžeme použiť pokročilý vzorec programu Excel, ako je uvedené nižšie:
Rovnaký vzorec INDEX možno použiť na získanie hodnoty v riadku. Pri použití čísla riadka aj stĺpca by syntax vyzerala asi takto:
Vyššie uvedený vzorec vráti výraz „Rajesh Ved“.
Poznámka: Ak vložíte ďalší riadok do 5. ročník riadok, vzorec vráti "Chandan Kale." Výstup by teda závisel od akýchkoľvek zmien, ku ktorým dôjde v tabuľke údajov v priebehu času.
# 3 - ZÁLOHOVÝ vzorec v programe Excel
Tento pokročilý vzorec programu Excel vráti číslo riadku alebo stĺpca, ak je v danom rozsahu konkrétny reťazec alebo číslo. V nasledujúcom príklade sa snažíme nájsť „Rajesh Ved“ v stĺpci Meno zamestnanca.
Vzorec bude uvedený nižšie:
Funkcia MATCH vráti hodnotu 5.
Pre presnú zhodu sa používa 3. argument. Môžete tiež použiť +1 a -1 na základe vašich požiadaviek.
Poznámka: Jeden môže kombinovať INDEX a MATCH prekonať obmedzenie VLOOKUP.
# 4 - IF AND Formula v programe Excel
Existuje veľa prípadov, keď je potrebné vytvoriť príznaky založené na určitých obmedzeniach. Všetci poznáme základnú syntax IF. Túto pokročilú funkciu programu IF používame na vytvorenie nového poľa založeného na obmedzeniach existujúceho poľa. Čo však v prípade, keď pri vytváraní príznaku potrebujeme zohľadniť viac stĺpcov. Pr. V nasledujúcom prípade chceme označiť všetkých zamestnancov, ktorých plat je vyšší ako 50 000, ale ich ID je väčšie ako 3.
V takýchto prípadoch by sme použili IF AND. Nižšie nájdete ten istý obrázok obrazovky.
Výsledok by sa vrátil ako 0.
Na vytvorenie príznaku založeného na viacerých stĺpcoch pomocou AND môžeme mať veľa podmienok alebo obmedzení.
# 5 - AK ALEBO Vzorec v programe Excel
Podobne môžeme použiť aj funkciu OR v programe Excel namiesto AND, ak potrebujeme splniť jednu z podmienok mnohých.
Vo vyššie uvedených prípadoch, ak je splnená ktorákoľvek z podmienok, bude mať bunka naplnená 1 iná 0. Môžeme tiež nahradiť 1 alebo 0 niektorými podreťazcami dvojitými úvodzovkami („“).
# 6 - SUMIF vzorec v programe Excel
Pri niektorých analýzach bude možno potrebné filtrovať niektoré pozorovania, keď použijete funkciu súčtu alebo počtu. V takýchto prípadoch je táto pokročilá funkcia SUMIF programu Excel v programe Excel na našej záchrane. Filtruje všetky pozorovania na základe určitých podmienok uvedených v tomto rozšírenom vzorci programu Excel a sumarizuje ich. Pr. Čo ak chceme vedieť súčet platov iba tých zamestnancov, ktorí majú ID zamestnanca väčšie ako 3.
Aplikáciou vzorca SUMIFS:
Vzorec vráti výsledky ako 322000.
Môžeme tiež spočítať počet zamestnancov v organizácii, ktorá má ID zamestnanca väčšie ako 3, keď použijete COUNTIF namiesto SUMIF.
# 7 - KONCATENOVAŤ vzorec v programe Excel
Táto pokročilá funkcia programu Excel je jedným zo vzorcov, ktoré sa dajú použiť s viacerými variantmi. Tento pokročilý vzorec programu Excel nám pomáha spojiť niekoľko textových reťazcov do jedného textového reťazca. Napr., Ak chceme zobraziť ID zamestnanca a meno zamestnanca v jednom stĺpci.
Môžeme na to použiť tento vzorec KONCATENÁTU.
Výsledkom vyššie uvedeného vzorca bude „1Aman Gupta“.
Môžeme mať ešte jednu variantu vložením jedinej pomlčky medzi ID a NAME. Pr. Výsledkom KONCATENÁTU (B3, „-“, C3) bude „1-Aman Gupta“. Môžeme to tiež použiť vo VLOOKUP, keď je HĽADAŤ v excelovej hodnote zmes viac ako jednej premennej.
# 8 - ĽAVÝ, STREDNÝ a PRAVÝ vzorec v programe Excel
Tento pokročilý vzorec programu Excel môžeme použiť na extrahovanie konkrétneho podreťazca z daného reťazca. Môže byť použitý na základe našich požiadaviek. Pr. Ak chceme z názvu zamestnanca extrahovať prvých 5 znakov, môžeme v programe Excel použiť LEVÝ vzorec s názvom stĺpca a druhým parametrom ako 5.
Výstup je uvedený nižšie:
Rovnaké je aj použitie vzorec RIGHT v programe Excel. Je to tak, že by sme sa na postavu pozerali sprava od reťazca. V prípade funkcie MID v programe Excel však musíme uviesť začiatočnú pozíciu požadovaného textového reťazca a dĺžku reťazca.
# 9 - offsetový vzorec v programe Excel
Táto pokročilá funkcia programu Excel s kombináciou ďalších funkcií, ako je SUM alebo AVERAGE, môže dať výpočtom dynamický nádych. Najlepšie sa to používa v prípadoch, keď vkladáme súvislé riadky do existujúcej databázy. OFFSET Excel nám poskytuje rozsah, v ktorom musíme spomenúť referenčnú bunku, počet riadkov a stĺpcov. Pr. Ak chceme vypočítať priemer prvých 5 zamestnancov v spoločnosti, kde máme plat zamestnancov zoradený podľa ID zamestnanca, môžeme urobiť nasledovné. Nasledujúci výpočet nám dá vždy plat.
- Dá nám to súčet platov prvých 5 zamestnancov.
# 10 - TRIM vzorec v programe Excel
Tento pokročilý vzorec programu Excel sa používa na vyčistenie nedôležitých medzier v texte. Pr. Ak chceme odstrániť medzery na začiatku nejakého názvu, môžeme to použiť pomocou funkcie TRIM v programe Excel, ako je uvedené nižšie:
Výsledný výstup by bol „Chandan Kale“ bez medzery pred Chandanom.
